NPOI操作Excel文件
2018-06-18 01:05:00来源:未知 阅读 ()
首先,通过NuGet添加NPOI.
NPOI依赖SharpZipLib,通过NuGet添加SharpZipLib.
然后添加NPOI.
添加后项目的引用列表如下:
把DataTable转换成Excel文件。
代码如下:
public static MemoryStream RenderDataTableToExcel(DataTable table) { MemoryStream ms = new MemoryStream(); IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(table.TableName); for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in table.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(table.Rows[rowIndex][column].ToString()); } } workbook.Write(ms); ms.Close(); return ms; }
转换Excel文件内容如下:
Excel文件添加表头
代码:
public static MemoryStream RenderDataTableToExcelWithHeader(DataTable table) { MemoryStream ms = new MemoryStream(); IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(table.TableName); IRow headerRow = sheet.CreateRow(0); foreach (DataColumn column in table.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(string.Format(" {0} ", column.Caption)); } for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++) { IRow dataRow = sheet.CreateRow(rowIndex+1); foreach (DataColumn column in table.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(table.Rows[rowIndex][column].ToString()); } } workbook.Write(ms); ms.Close(); return ms; }
转换Excel文件内容如下:
添加Excel文件添加表头样式
代码:
public static MemoryStream RenderDataTableToExcelWithHeaderRowStyle(DataTable table) { MemoryStream ms = new MemoryStream(); IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(table.TableName); IRow headerRow = sheet.CreateRow(0); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; headStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; headStyle.FillPattern = FillPattern.SolidForeground; IFont font = workbook.CreateFont(); font.FontName = "Microsoft Yahei"; font.FontHeightInPoints = 11; font.IsBold = true; font.Color = HSSFColor.White.Index; headStyle.SetFont(font); headStyle.BorderBottom = BorderStyle.Thin; headStyle.BorderRight = BorderStyle.Thin; headStyle.BorderLeft = BorderStyle.Thin; foreach (DataColumn column in table.Columns) { ICell cell = headerRow.CreateCell(column.Ordinal); cell.SetCellValue(string.Format(" {0} ", column.Caption)); cell.CellStyle = headStyle; } for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++) { IRow dataRow = sheet.CreateRow(rowIndex + 1); foreach (DataColumn column in table.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(table.Rows[rowIndex][column].ToString()); } } workbook.Write(ms); ms.Close(); return ms; }
转换Excel文件内容如下:
添加Excel文件添加数据行样式
代码:
public static MemoryStream RenderDataTableToExcelWithDataRowStyle(DataTable table) { MemoryStream ms = new MemoryStream(); IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(table.TableName); IRow headerRow = sheet.CreateRow(0); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; headStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; headStyle.FillPattern = FillPattern.SolidForeground; IFont font = workbook.CreateFont(); font.FontName = "Microsoft Yahei"; font.FontHeightInPoints = 11; font.IsBold = true; font.Color = HSSFColor.White.Index; headStyle.SetFont(font); headStyle.BorderBottom = BorderStyle.Thin; headStyle.BorderRight = BorderStyle.Thin; headStyle.BorderLeft = BorderStyle.Thin; ICellStyle dataRowEvenStyle = workbook.CreateCellStyle(); dataRowEvenStyle.Alignment = HorizontalAlignment.Center; dataRowEvenStyle.FillForegroundColor = HSSFColor.LightOrange.Index; dataRowEvenStyle.FillPattern = FillPattern.SolidForeground; IFont dataRowEvenFont = workbook.CreateFont(); dataRowEvenFont.FontName = "Microsoft Yahei"; dataRowEvenFont.FontHeightInPoints = 11; dataRowEvenFont.Color = HSSFColor.Blue.Index; dataRowEvenStyle.SetFont(dataRowEvenFont); dataRowEvenStyle.BorderBottom = BorderStyle.Thin; dataRowEvenStyle.BorderRight = BorderStyle.Thin; dataRowEvenStyle.BorderLeft = BorderStyle.Thin; ICellStyle dataRowOddStyle = workbook.CreateCellStyle(); dataRowOddStyle.Alignment = HorizontalAlignment.Center; dataRowOddStyle.FillForegroundColor = HSSFColor.LightGreen.Index; dataRowOddStyle.FillPattern = FillPattern.SolidForeground; IFont dataRowOddFont = workbook.CreateFont(); dataRowOddFont.FontName = "Microsoft Yahei"; dataRowOddFont.FontHeightInPoints = 11; dataRowOddFont.Color = HSSFColor.Black.Index; dataRowOddStyle.SetFont(dataRowOddFont); dataRowOddStyle.BorderBottom = BorderStyle.Thin; dataRowOddStyle.BorderRight = BorderStyle.Thin; dataRowOddStyle.BorderLeft = BorderStyle.Thin; foreach (DataColumn column in table.Columns) { ICell cell = headerRow.CreateCell(column.Ordinal); cell.SetCellValue(string.Format(" {0} ", column.Caption)); cell.CellStyle = headStyle; } for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++) { IRow dataRow = sheet.CreateRow(rowIndex + 1); foreach (DataColumn column in table.Columns) { ICell cell = dataRow.CreateCell(column.Ordinal); cell.SetCellValue(table.Rows[rowIndex][column].ToString()); if (rowIndex % 2 == 0) { cell.CellStyle = dataRowEvenStyle; } else { cell.CellStyle = dataRowOddStyle; } } } workbook.Write(ms); ms.Close(); return ms; }
转换Excel文件内容如下:
Excel文件合并单元格
代码:
public static MemoryStream RenderDataTableToExcelMergedRegion(DataTable table) { MemoryStream ms = new MemoryStream(); IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(table.TableName); IRow headerRow = sheet.CreateRow(0); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; headStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; headStyle.FillPattern = FillPattern.SolidForeground; IFont font = workbook.CreateFont(); font.FontName = "Microsoft Yahei"; font.FontHeightInPoints = 11; font.IsBold = true; font.Color = HSSFColor.White.Index; headStyle.SetFont(font); headStyle.BorderBottom = BorderStyle.Thin; headStyle.BorderRight = BorderStyle.Thin; headStyle.BorderLeft = BorderStyle.Thin; ICellStyle dataRowEvenStyle = workbook.CreateCellStyle(); dataRowEvenStyle.Alignment = HorizontalAlignment.Center; dataRowEvenStyle.FillForegroundColor = HSSFColor.LightOrange.Index; dataRowEvenStyle.FillPattern = FillPattern.SolidForeground; IFont dataRowEvenFont = workbook.CreateFont(); dataRowEvenFont.FontName = "Microsoft Yahei"; dataRowEvenFont.FontHeightInPoints = 11; dataRowEvenFont.Color = HSSFColor.Blue.Index; dataRowEvenStyle.SetFont(dataRowEvenFont); dataRowEvenStyle.BorderBottom = BorderStyle.Thin; dataRowEvenStyle.BorderRight = BorderStyle.Thin; dataRowEvenStyle.BorderLeft = BorderStyle.Thin; ICellStyle dataRowOddStyle = workbook.CreateCellStyle(); dataRowOddStyle.Alignment = HorizontalAlignment.Center; dataRowOddStyle.FillForegroundColor = HSSFColor.LightGreen.Index; dataRowOddStyle.FillPattern = FillPattern.SolidForeground; IFont dataRowOddFont = workbook.CreateFont(); dataRowOddFont.FontName = "Microsoft Yahei"; dataRowOddFont.FontHeightInPoints = 11; dataRowOddFont.Color = HSSFColor.Black.Index; dataRowOddStyle.SetFont(dataRowOddFont); dataRowOddStyle.BorderBottom = BorderStyle.Thin; dataRowOddStyle.BorderRight = BorderStyle.Thin; dataRowOddStyle.BorderLeft = BorderStyle.Thin; foreach (DataColumn column in table.Columns) { ICell cell = headerRow.CreateCell(column.Ordinal); cell.SetCellValue(string.Format(" {0} ", column.Caption)); cell.CellStyle = headStyle; } for (int rowIndex = 0; rowIndex < table.Rows.Count; rowIndex++) { IRow dataRow = sheet.CreateRow(rowIndex + 1); foreach (DataColumn column in table.Columns) { ICell cell = dataRow.CreateCell(column.Ordinal); cell.SetCellValue(table.Rows[rowIndex][column].ToString()); if (rowIndex % 2 == 0) { cell.CellStyle = dataRowEvenStyle; } else { cell.CellStyle = dataRowOddStyle; } } } sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0)); sheet.AddMergedRegion(new CellRangeAddress(3, 4, 0, 0)); sheet.AddMergedRegion(new CellRangeAddress(5, 6, 0, 0)); sheet.AddMergedRegion(new CellRangeAddress(7, 8, 0, 0)); sheet.AddMergedRegion(new CellRangeAddress(9, 10, 0, 0)); workbook.Write(ms); ms.Close(); return ms; }
转换Excel文件内容如下:
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
上一篇:时区转换
- QTableView与Excel之间的文件打开与保存 2020-05-26
- 第七章 1.输入输出与模板 2020-04-04
- 数据结构-线性表 2020-03-28
- 二叉树(1)二叉树基本操作通用接口 2020-02-06
- 向量容器vector操作 2020-02-03
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash