基于NPOI的扩展

using System; using System.Collections.Generic; using System.Linq; using System.Text; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.HSSF.Util; using System.Data; namespace Tools { public static class NPOIHelper { /// <summary> /// 设置表格样式，边框线为黑色实线 /// </summary> /// <param name="hssfworkbook">Excel表格</param> /// <returns></returns> public static ICellStyle CreateCellStyle(IWorkbook workbook) { var style = workbook.CreateCellStyle(); style.BorderBottom = BorderStyle.THIN; style.BorderLeft = BorderStyle.THIN; style.BorderRight = BorderStyle.THIN; style.BorderTop = BorderStyle.THIN; style.TopBorderColor = HSSFColor.BLACK.index; style.BottomBorderColor = HSSFColor.BLACK.index; style.LeftBorderColor = HSSFColor.BLACK.index; style.RightBorderColor = HSSFColor.BLACK.index; style.VerticalAlignment = VerticalAlignment.CENTER; return style; } /// <summary> /// 创建一个普通的Excel表格 /// </summary> /// <param name="sheetName">Sheet名称</param> /// <param name="headers">表格头部，默认取DataTable的ColumnName</param> /// <param name="columnWidths">各列宽度（单位：字符宽度）</param> /// <param name="rowHight">各行高度（单位：磅）</param> /// <param name="rowCount">表格创建行数，默认取DataTable的Rows.Count</param> /// <param name="data">数据</param> /// <returns></returns> public static IWorkbook CreateWorkbook(string sheetName, string[] headers, int[] columnWidths, int rowHight, int rowCount, DataTable data) { var hssfworkbook = new HSSFWorkbook(); CreateSheet(hssfworkbook, sheetName, headers, columnWidths, rowHight, rowCount, data); return hssfworkbook; } /// <summary> /// 创建一个普通的Excel表格 /// </summary> /// <param name="sheetName">Sheet名称</param> /// <param name="headers">表格头部</param> /// <param name="columnWidths">各列宽度（单位：字符宽度）</param> /// <param name="rowHight">各行高度（单位：磅）</param> /// <param name="rowCount">表格创建行数</param> /// <param name="data">数据</param> /// <returns></returns> public static IWorkbook CreateWorkbook(string sheetName, string[] headers, int[] columnWidths, int rowHight, int rowCount, List<List<string>> data) { var hssfworkbook = new HSSFWorkbook(); CreateSheet(hssfworkbook, sheetName, headers, columnWidths, rowHight, rowCount, data); return hssfworkbook; } /// <summary> /// 创建一个普通的sheet表格 /// </summary> /// <param name="workbook">Excel表格</param> /// <param name="sheetName">Sheet名称</param> /// <param name="headers">表格头部，默认取DataTable的ColumnName</param> /// <param name="columnWidths">各列宽度（单位：字符宽度）</param> /// <param name="rowHight">各行高度（单位：磅）</param> /// <param name="rowCount">表格创建行数，默认取DataTable的Rows.Count</param> /// <param name="data">数据</param> /// <returns></returns> public static ISheet CreateSheet(IWorkbook workbook, string sheetName, string[] headers, int[] columnWidths, int rowHight, int rowCount, DataTable data) { List<string> l = null; List<List<string>> d = null; if (data != null && data.Rows.Count > 0) { d = new List<List<string>>(); foreach (DataRow dr in data.Rows) { l = new List<string>(); foreach (object obj in dr.ItemArray) { l.Add(obj.ToZMStringOrEmpty()); } d.Add(l); } if (headers.IsNullOrEmpty()) { var h = new List<string>(); foreach (DataColumn dc in data.Columns) { h.Add(dc.ColumnName); } //for (var i = 0; i < data.Columns.Count; ++i ) //{ // h.Add(data.Columns[i].ColumnName); //} headers = h.ToArray(); } } return CreateSheet(workbook, sheetName, headers, columnWidths, rowHight, rowCount, d); } /// <summary> /// 创建一个普通的sheet表格 /// </summary> /// <param name="workbook">Excel表格</param> /// <param name="sheetName">Sheet名称</param> /// <param name="headers">表格头部</param> /// <param name="columnWidths">各列宽度（单位：字符宽度）</param> /// <param name="rowHight">各行高度（单位：磅）</param> /// <param name="rowCount">表格创建行数</param> /// <param name="data">数据</param> /// <returns></returns> public static ISheet CreateSheet(IWorkbook workbook, string sheetName, string[] headers, int[] columnWidths, int rowHight, int rowCount, List<List<string>> data) { var sheet = workbook.CreateSheet(sheetName); ICellStyle cellStyle = null; ICell cell = null; IRow row = null; var columnCount = 0; // 设置头部 if (headers.HasItem()) { row = sheet.CreateRow(0); row.Height = 500; //新建一个字体样式对象 IFont font = workbook.CreateFont(); //设置字体加粗样式 font.Boldweight = short.MaxValue; cellStyle = CreateCellStyle(workbook); for (var i = 0; i < headers.Length; ++i) { cell = row.CreateCell(i, CellType.STRING); cell.CellStyle = cellStyle; cell.SetCellValue(headers[i]); //使用SetFont方法将字体样式添加到单元格样式中 cell.CellStyle.SetFont(font); cell.CellStyle.Alignment = HorizontalAlignment.CENTER; } columnCount = headers.Length; } // 设置列宽 if (columnWidths.HasItem()) { for (var i = 0; i < columnWidths.Length; ++i) { // 设置表格宽度 sheet.SetColumnWidth(i, columnWidths[i] * 256); } } // 设置数据 if (data.HasItem()) { rowCount = data.Count; foreach (var array in data) { if (array.Count > columnCount) { columnCount = array.Count; } } } cellStyle = CreateCellStyle(workbook); for (var i = 0; i < rowCount; ++i) { row = sheet.CreateRow(sheet.LastRowNum + 1); row.Height = (short)(rowHight * 20); for (var j = 0; j < columnCount; ++j) { cell = row.CreateCell(j, CellType.STRING); cell.CellStyle = cellStyle; try { cell.SetCellValue(data[i][j].ToZMStringOrEmpty()); } catch { cell.SetCellValue(string.Empty); } } } return sheet; } } }

 

转载于:https://www.cnblogs.com/Jarvan-Chan/p/5991763.html

相关资源：基于NPOI的报表引擎ExcelReport.zip