首页 > 代码库 > NPOI操作Excel文件
NPOI操作Excel文件
首先,通过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文件内容如下:
NPOI操作Excel文件
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。