NPOI是POI的.NET版本,POI是一套用Java写成的库，我们在开发中经常用到导入导出表格、文档的情况，NPOI能够帮助我们在没有安装微软Office的情况下读写Office文件，如xls, doc, ppt等。NPOI采用的是Apache 2.0许可证（poi也是采用这个许可证），这意味着它可以被用于任何商业或非商业项目，我们不用担心因为使用它而必须开放你自己的源代码，所以它对于很多从事业务系统开发的公司来说绝对是很不错的选择。
public partial class NPOIHelper { /// <summary> /// Excel导入 /// </summary> /// <param name="excelPath">Excel路径</param> /// <param name="headRowCount">表头行号</param> /// <param name="dicCols">表头名称对应（例如：跳远--1分钟跳远）</param> /// <returns></returns> public static DataSet GetDataSetFromExcel(string excelPath, int headRowCount, int startRowIndex = 1, Dictionary<string, string> dicCols = null) { try { DataSet ds = new DataSet(); IWorkbook wb = null; using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read)) { wb = WorkbookFactory.Create(fs); //wb = new HSSFWorkbook(fs); } int sheetcount = wb.NumberOfSheets; if (sheetcount <= 0) { return null; } for (int x = 0; x < sheetcount; x++) { DataTable dt = new DataTable(); ISheet sheet = wb.GetSheetAt(x); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); dt.TableName = sheet.SheetName; int cellCount = 0; bool NoData = false; List<IRow> headerRows = new List<IRow>(); for (int i = 0; i < headRowCount; i++) { IRow headerRow = sheet.GetRow(i); if (headerRow == null) { NoData = true; continue; } headerRows.Add(headerRow); cellCount = headerRow.LastCellNum; } //设置表头 for (int j = 0; j < cellCount; j++) { for (int i = 0; i < headerRows.Count; i++) { ICell cell = headerRows[i].GetCell(j); string tabelTitle = cell.ToString(); if (!String.IsNullOrWhiteSpace(tabelTitle)) { if (dicCols != null) { string colTitle = dicCols.Where(p => tabelTitle.Contains(p.Key)).Select(p => p.Value).FirstOrDefault(); if (!string.IsNullOrEmpty(colTitle)) { tabelTitle = colTitle;//默认列名为子列第一列的名称 } } if (!dt.Columns.Contains(tabelTitle))//防止重复录入子列名 { dt.Columns.Add(tabelTitle); } } } } cellCount = dt.Columns.Count; if (NoData) { continue; } for (int i = startRowIndex; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { ICell cell = row.GetCell(j); if (cell == null) { dataRow[j] = null; } else { switch (cell.CellType) { case CellType.Blank: dataRow[j] = null; break; case CellType.Boolean: dataRow[j] = cell.BooleanCellValue; break; case CellType.Numeric: //dataRow[j] = Convert.ToDouble(cell.NumericCellValue); //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型 if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型 { dataRow[j] = cell.DateCellValue; } else//其他数字类型 { dataRow[j] = cell.NumericCellValue; } //dataRow[j] = cell.CellStyle.DataFormat; //if (dataRow[j] != "0") //{ dataRow[j] = cell.DateCellValue; } //else { dataRow[j] = Convert.ToDouble(cell.NumericCellValue); } break; case CellType.String: dataRow[j] = cell.StringCellValue; break; case CellType.Error: dataRow[j] = cell.ErrorCellValue; break; case CellType.Formula: dataRow[j] = Math.Round(cell.NumericCellValue, 2); break; default: dataRow[j] = "=" + cell.CellFormula; break; } } } } dt.Rows.Add(dataRow); } ds.Tables.Add(dt); } return ds; } catch (Exception) { return null; } } } public partial class NPOIHelper { public static bool DelRowFromExcel(string excelPath, int RowIndex) { try { FileStream reade = new FileStream(Path.GetFullPath(excelPath), FileMode.Open, FileAccess.Read, FileShare.ReadWrite); IWorkbook workBook = WorkbookFactory.Create(reade); ISheet workSheet = workBook.GetSheetAt(0); int lastRowNum = workSheet.LastRowNum; workSheet.ShiftRows(RowIndex, RowIndex, 1); FileStream stream = new FileStream(Path.GetFullPath(excelPath), FileMode.Open, FileAccess.Read, FileShare.ReadWrite); workBook.Write(stream); stream.Close(); reade.Close(); return true; } catch { return false; } } } public partial class NPOIHelper { /// <summary> /// Excel导入 /// </summary> /// <param name="excelPath">Excel路径</param> /// <param name="headRowCount">表头行号</param> /// <param name="dicCols">表头名称对应（例如：跳远--1分钟跳远）</param> /// <returns></returns> public static DataSet GetDataSetFromExcel(int headRowStar, string excelPath, int headRowCount, int startRowIndex = 1, Dictionary<string, string> dicCols = null) { try { DataSet ds = new DataSet(); IWorkbook wb = null; using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read)) { wb = WorkbookFactory.Create(fs); //wb = new HSSFWorkbook(fs); } int sheetcount = wb.NumberOfSheets; if (sheetcount <= 0) { return null; } for (int x = 0; x < sheetcount; x++) { DataTable dt = new DataTable(); ISheet sheet = wb.GetSheetAt(x); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); dt.TableName = sheet.SheetName; int cellCount = 0; bool NoData = false; for (int i = headRowStar; i < headRowCount; i++) { IRow headerRow = sheet.GetRow(i); if (headerRow == null) { NoData = true; continue; } cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); string tabelTitle = cell.ToString(); if (!String.IsNullOrWhiteSpace(tabelTitle)) { if (dicCols != null) { string colTitle = dicCols.Where(p => tabelTitle.Contains(p.Key)).Select(p => p.Value).FirstOrDefault(); if (!string.IsNullOrEmpty(colTitle)) { tabelTitle = colTitle;//默认列名为子列第一列的名称 } } if (!dt.Columns.Contains(tabelTitle))//防止重复录入子列名 { dt.Columns.Add(tabelTitle); } } } cellCount = dt.Columns.Count; } if (NoData) { continue; } for (int i = startRowIndex; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { ICell cell = row.GetCell(j); if (cell == null) { dataRow[j] = null; } else { switch (cell.CellType) { case CellType.Blank: dataRow[j] = null; break; case CellType.Boolean: dataRow[j] = cell.BooleanCellValue; break; case CellType.Numeric: //dataRow[j] = Convert.ToDouble(cell.NumericCellValue); //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型 if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型 { dataRow[j] = cell.DateCellValue; } else//其他数字类型 { dataRow[j] = cell.NumericCellValue; } //dataRow[j] = cell.CellStyle.DataFormat; //if (dataRow[j] != "0") //{ dataRow[j] = cell.DateCellValue; } //else { dataRow[j] = Convert.ToDouble(cell.NumericCellValue); } break; case CellType.String: dataRow[j] = cell.StringCellValue; break; case CellType.Error: dataRow[j] = cell.ErrorCellValue; break; case CellType.Formula: dataRow[j] = Math.Round(cell.NumericCellValue, 2); break; default: dataRow[j] = "=" + cell.CellFormula; break; } } } } dt.Rows.Add(dataRow); } ds.Tables.Add(dt); } return ds; } catch (Exception) { return null; } } } /// <summary> /// Excel导出 /// </summary> /// <remarks> /// author:zhujt /// create date:2015-9-11 16:44:41 /// </remarks> public partial class NPOIHelper { #region 初始化 /// <summary> /// 声明 HSSFWorkbook 对象 /// </summary> private static HSSFWorkbook _workbook; /// <summary> /// 声明 HSSFSheet 对象 /// </summary> private static HSSFSheet _sheet; #endregion /// <summary> /// Excel导出 /// </summary> /// <param name="fileName">文件名称 如果为空或NULL，则默认“新建Excel.xls”</param> /// <param name="list"></param> /// <param name="method">导出方式 1：WEB导出（默认）2：按文件路径导出</param> /// <param name="filePath">文件路径 如果WEB导出，则可以为空；如果按文件路径导出，则默认桌面路径</param> public static void Export(string fileName, IList<NPOIModel> list, int method = 1, string filePath = null) { // 文件名称 if (!string.IsNullOrEmpty(fileName)) { if (fileName.IndexOf('.') == -1) fileName += ".xls"; else fileName = fileName.Substring(1, fileName.IndexOf('.')) + ".xls"; } else fileName = "新建Excel.xls"; // 文件路径 if (2 == method && string.IsNullOrEmpty(filePath)) filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); // 调用导出处理程序 Export(list); // WEB导出 if (1 == method) { MvcContext.GetContext().Response.ContentType = "application/vnd.ms-excel"; //设置下载的Excel文件名 MvcContext.GetContext().Response.Headers.Add("Content-Disposition", string.Format("attachment;filename={0}", fileName)); using (MemoryStream ms = new MemoryStream()) { //将工作簿的内容放到内存流中 _workbook.Write(ms); //将内存流转换成字节数组发送到客户端 MvcContext.GetContext().Response.Body.Write(ms.GetBuffer()); MvcContext.GetContext().Response.Body.Flush(); _sheet = null; _workbook = null; } } else if (2 == method) { using (FileStream fs = File.Open(filePath, FileMode.Append)) { _workbook.Write(fs); _sheet = null; _workbook = null; } } } /// <summary> /// 导出方法实现 /// </summary> /// <param name="list"></param> private static void Export(IList<NPOIModel> list) { #region 变量声明 // 初始化 _workbook = new HSSFWorkbook(); // 声明 Row 对象 IRow _row; // 声明 Cell 对象 ICell _cell; // 总列数 int cols = 0; // 总行数 int rows = 0; // 行数计数器 int rowIndex = 0; // 单元格值 string drValue = null; #endregion foreach (NPOIModel model in list) { // 工作薄命名 if (model.sheetName != null) _sheet = (HSSFSheet)_workbook.CreateSheet(model.sheetName); else _sheet = (HSSFSheet)_workbook.CreateSheet(); // 获取数据源 DataTable dt = model.dataSource; // 初始化 rowIndex = 0; // 获取总行数 rows = GetRowCount(model.headerName); // 获取总列数 cols = GetColCount(model.headerName); ICellStyle style = _workbook.CreateCellStyle(); // 循环行数 foreach (DataRow row in dt.Rows) { #region 新建表，填充表头，填充列头，样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) _sheet = (HSSFSheet)_workbook.CreateSheet(); // 构建行 for (int i = 0; i < rows + model.isTitle; i++) { _row = _sheet.GetRow(i); // 创建行 if (_row == null) _row = _sheet.CreateRow(i); for (int j = 0; j < cols; j++) _row.CreateCell(j).CellStyle = bodyStyle(style); } // 如果存在表标题 if (model.isTitle > 0) { // 获取行 _row = _sheet.GetRow(0); // 合并单元格 CellRangeAddress region = new CellRangeAddress(0, 0, 0, (cols - 1)); _sheet.AddMergedRegion(region); // 填充值 _row.CreateCell(0).SetCellValue(model.tableTitle); // 设置样式 _row.GetCell(0).CellStyle = titleStyle; // 设置行高 _row.HeightInPoints = 20; } // 取得上一个实体 NPOIHeader lastRow = null; IList<NPOIHeader> hList = GetHeaders(model.headerName, rows, model.isTitle); // 创建表头 foreach (NPOIHeader m in hList) { var data = hList.Where(c => c.firstRow == m.firstRow && c.lastCol == m.firstCol - 1); if (data.Count() > 0) { lastRow = data.First(); if (m.headerName == lastRow.headerName) m.firstCol = lastRow.firstCol; } // 获取行 _row = _sheet.GetRow(m.firstRow); if (m.firstRow != m.lastRow || m.firstCol != m.lastCol) { // 合并单元格 CellRangeAddress region = new CellRangeAddress(m.firstRow, m.lastRow, m.firstCol, m.lastCol); _sheet.AddMergedRegion(region); } // 填充值 _row.CreateCell(m.firstCol).SetCellValue(m.headerName); } // 填充表头样式 for (int i = 0; i < rows + model.isTitle; i++) { _row = _sheet.GetRow(i); for (int j = 0; j < cols; j++) { _row.GetCell(j).CellStyle = bodyStyle(style); //设置列宽 //_sheet.SetColumnWidth(j, (model.colWidths[j] + 1) * 256); if (model.colWidths[j] > 255) { model.colWidths[j] = 254; } else { _sheet.SetColumnWidth(j, ((model.colWidths[j] + 1) > 40 ? 40 : model.colWidths[j] + 1) * 256); } } } rowIndex = (rows + model.isTitle); } #endregion #region 填充内容 // 构建列 _row = _sheet.CreateRow(rowIndex); foreach (DataColumn column in dt.Columns) { // 添加序号列 if (1 == model.isOrderby && column.Ordinal == 0) { _cell = _row.CreateCell(0); _cell.SetCellValue(rowIndex - rows); _cell.CellStyle = bodyStyle(style); } // 创建列 _cell = _row.CreateCell(column.Ordinal + model.isOrderby); // 获取值 drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 _cell.SetCellValue(drValue); _cell.CellStyle = bodyStyle(style); break; case "System.DateTime"://日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); _cell.SetCellValue(dateV); _cell.CellStyle = dateStyle;//格式化显示 break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); _cell.SetCellValue(boolV); _cell.CellStyle = bodyStyle(style); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); _cell.SetCellValue(intV); _cell.CellStyle = bodyRightStyle; break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); _cell.SetCellValue(doubV); _cell.CellStyle = bodyRightStyle; break; case "System.DBNull"://空值处理 _cell.SetCellValue(""); break; default: _cell.SetCellValue(""); break; } } #endregion rowIndex++; } } } #region 辅助方法 /// <summary> /// 表头解析 /// </summary> /// <remarks> /// author:zhujt /// create date:2015-9-10 19:24:51 /// </remarks> /// <param name="header">表头</param> /// <param name="rows">总行数</param> /// <param name="addRows">外加行</param> /// <param name="addCols">外加列</param> /// <returns></returns> private static IList<NPOIHeader> GetHeaders(string header, int rows, int addRows) { // 临时表头数组 string[] tempHeader; string[] tempHeader2; // 所跨列数 int colSpan = 0; // 所跨行数 int rowSpan = 0; // 单元格对象 NPOIHeader model = null; // 行数计数器 int rowIndex = 0; // 列数计数器 int colIndex = 0; // IList<NPOIHeader> list = new List<NPOIHeader>(); // 初步解析 string[] headers = header.Split(new string[] { "#" }, StringSplitOptions.RemoveEmptyEntries); // 表头遍历 for (int i = 0; i < headers.Length; i++) { // 行数计数器清零 rowIndex = 0; // 列数计数器清零 colIndex = 0; // 获取所跨行数 rowSpan = GetRowSpan(headers[i], rows); // 获取所跨列数 colSpan = GetColSpan(headers[i]); // 如果所跨行数与总行数相等，则不考虑是否合并单元格问题 if (rows == rowSpan) { colIndex = GetMaxCol(list); model = new NPOIHeader(headers[i], addRows, (rowSpan - 1 + addRows), colIndex, (colSpan - 1 + colIndex), addRows); list.Add(model); rowIndex += (rowSpan - 1) + addRows; } else { // 列索引 colIndex = GetMaxCol(list); // 如果所跨行数不相等，则考虑是否包含多行 tempHeader = headers[i].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries); for (int j = 0; j < tempHeader.Length; j++) { // 如果总行数=数组长度 if (1 == GetColSpan(tempHeader[j])) { if (j == tempHeader.Length - 1 && tempHeader.Length < rows) { model = new NPOIHeader(tempHeader[j], (j + addRows), (j + addRows) + (rows - tempHeader.Length), colIndex, (colIndex + colSpan - 1), addRows); list.Add(model); } else { model = new NPOIHeader(tempHeader[j], (j + addRows), (j + addRows), colIndex, (colIndex + colSpan - 1), addRows); list.Add(model); } } else { // 如果所跨列数不相等，则考虑是否包含多列 tempHeader2 = tempHeader[j].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries); for (int m = 0; m < tempHeader2.Length; m++) { // 列索引 colIndex = GetMaxCol(list) - colSpan + m; if (j == tempHeader.Length - 1 && tempHeader.Length < rows) { model = new NPOIHeader(tempHeader2[m], (j + addRows), (j + addRows) + (rows - tempHeader.Length), colIndex, colIndex, addRows); list.Add(model); } else { model = new NPOIHeader(tempHeader2[m], (j + addRows), (j + addRows), colIndex, colIndex, addRows); list.Add(model); } } } rowIndex += j + addRows; } } } return list; } /// <summary> /// 获取最大列 /// </summary> /// <param name="list"></param> /// <returns></returns> private static int GetMaxCol(IList<NPOIHeader> list) { int maxCol = 0; if (list.Count > 0) { foreach (NPOIHeader model in list) { if (maxCol < model.lastCol) maxCol = model.lastCol; } maxCol += 1; } return maxCol; } /// <summary> /// 获取表头行数 /// </summary> /// <param name="newHeaders">表头文字</param> /// <returns></returns> private static int GetRowCount(string newHeaders) { string[] ColumnNames = newHeaders.Split(new char[] { '@' }); int Count = 0; if (ColumnNames.Length <= 1) ColumnNames = newHeaders.Split(new char[] { '#' }); foreach (string name in ColumnNames) { int TempCount = name.Split(new char[] { ' ' }).Length; if (TempCount > Count) Count = TempCount; } return Count; } /// <summary> /// 获取表头列数 /// </summary> /// <param name="newHeaders">表头文字</param> /// <returns></returns> private static int GetColCount(string newHeaders) { string[] ColumnNames = newHeaders.Split(new char[] { '@' }); int Count = 0; if (ColumnNames.Length <= 1) ColumnNames = newHeaders.Split(new char[] { '#' }); Count = ColumnNames.Length; foreach (string name in ColumnNames) { int TempCount = name.Split(new char[] { ',' }).Length; if (TempCount > 1) Count += TempCount - 1; } return Count; } /// <summary> /// 列头跨列数 /// </summary> /// <remarks> /// author:zhujt /// create date:2015-9-9 09:17:34 /// </remarks> /// <param name="newHeaders">表头文字</param> /// <returns></returns> private static int GetColSpan(string newHeaders) { return newHeaders.Split(',').Count(); } /// <summary> /// 列头跨行数 /// </summary> /// <remarks> /// author:zhujt /// create date:2015-9-9 09:17:14 /// </remarks> /// <param name="newHeaders">列头文本</param> /// <param name="rows">表头总行数</param> /// <returns></returns> private static int GetRowSpan(string newHeaders, int rows) { int Count = newHeaders.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).Length; // 如果总行数与当前表头所拥有行数相等 if (rows == Count) Count = 1; else if (Count < rows) Count = 1 + (rows - Count); else throw new Exception("表头格式不正确！"); return Count; } #endregion #region 单元格样式 /// <summary> /// 数据单元格样式 /// </summary> private static ICellStyle bodyStyle(ICellStyle style) { //get //{ //ICellStyle style = _workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; //居中 style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 style.WrapText = true;//自动换行 // 边框 style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; // 字体 IFont font = _workbook.CreateFont(); font.FontHeightInPoints = 10; font.FontName = "宋体"; style.SetFont(font); return style; //} } /// <summary> /// 数据单元格样式 /// </summary> private static ICellStyle bodyRightStyle { get { ICellStyle style = _workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; //居中 style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 style.WrapText = true;//自动换行 // 边框 style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; // 字体 IFont font = _workbook.CreateFont(); font.FontHeightInPoints = 10; font.FontName = "宋体"; style.SetFont(font); return style; } } /// <summary> /// 标题单元格样式 /// </summary> private static ICellStyle titleStyle { get { ICellStyle style = _workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; //居中 style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 style.WrapText = true;//自动换行 IFont font = _workbook.CreateFont(); font.FontHeightInPoints = 14; font.FontName = "宋体"; font.Boldweight = (short)FontBoldWeight.Bold; style.SetFont(font); return style; } } /// <summary> /// 日期单元格样式 /// </summary> private static ICellStyle dateStyle { get { ICellStyle style = _workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; //居中 style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 style.WrapText = true;//自动换行 // 边框 style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; // 字体 IFont font = _workbook.CreateFont(); font.FontHeightInPoints = 10; font.FontName = "宋体"; style.SetFont(font); IDataFormat format = _workbook.CreateDataFormat(); style.DataFormat = format.GetFormat("yyyy-MM-dd"); return style; } } #endregion } /// <summary> /// 实体类 /// </summary> public class NPOIModel { /// <summary> /// 数据源 /// </summary> public DataTable dataSource { get; private set; } /// <summary> /// 要导出的数据列数组 /// </summary> public string[] fileds { get; private set; } /// <summary> /// 工作薄名称数组 /// </summary> public string sheetName { get; private set; } /// <summary> /// 表标题 /// </summary> public string tableTitle { get; private set; } /// <summary> /// 表标题是否存在 1：存在 0：不存在 /// </summary> public int isTitle { get; private set; } /// <summary> /// 是否添加序号 /// </summary> public int isOrderby { get; private set; } /// <summary> /// 表头 /// </summary> public string headerName { get; private set; } /// <summary> /// 取得列宽 /// </summary> public int[] colWidths { get; private set; } /// <summary> /// 构造函数 /// </summary> /// <remarks> /// author:zhujt /// create date:2015-9-10 11:17:54 /// </remarks> /// <param name="dataSource">数据来源 DataTable</param> /// <param name="filed">要导出的字段，如果为空或NULL，则默认全部</param> /// <param name="sheetName">工作薄名称</param> /// <param name="headerName">表头名称 如果为空或NULL，则默认数据列字段 /// 相邻父列头之间用'#'分隔,父列头与子列头用空格(' ')分隔,相邻子列头用逗号分隔(',') /// 两行：序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名 /// 三行：等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值 /// 三行时请注意：列头要重复 /// </param> /// <param name="tableTitle">表标题</param> /// <param name="isOrderby">是否添加序号 0：不添加 1：添加</param> public NPOIModel(DataTable dataSource, string filed, string sheetName, string headerName, string tableTitle = null, int isOrderby = 0) { if (!string.IsNullOrEmpty(filed)) { this.fileds = filed.ToUpper().Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); // 移除多余数据列 for (int i = dataSource.Columns.Count - 1; i >= 0; i--) { DataColumn dc = dataSource.Columns[i]; if (!this.fileds.Contains(dataSource.Columns[i].Caption.ToUpper())) { dataSource.Columns.Remove(dataSource.Columns[i]); } } // 列索引 int colIndex = 0; // 循环排序 for (int i = 0; i < this.fileds.Length; i++) { if (dataSource.Columns.Contains(this.fileds[i])) dataSource.Columns[fileds[i]].SetOrdinal(colIndex++); } //导出未按指定排列顺序问题优化，注释以下代码 --add by wzy 2020/11/27 //for (int i = 0; i < dataSource.Columns.Count; i++) //{ // // 获取索引 // colIndex = GetColIndex(dataSource.Columns[i].Caption.ToUpper()); // // 设置下标 // dataSource.Columns[i].SetOrdinal(colIndex); //} } else { this.fileds = new string[dataSource.Columns.Count]; for (int i = 0; i < dataSource.Columns.Count; i++) { this.fileds[i] = dataSource.Columns[i].ColumnName; } } this.dataSource = dataSource; if (!string.IsNullOrEmpty(sheetName)) this.sheetName = sheetName; if (!string.IsNullOrEmpty(headerName)) this.headerName = headerName; else this.headerName = string.Join("#", this.fileds); if (!string.IsNullOrEmpty(tableTitle)) { this.tableTitle = tableTitle; this.isTitle = 1; } // 取得数据列宽 数据列宽可以和表头列宽比较，采取最长宽度 colWidths = new int[this.dataSource.Columns.Count]; foreach (DataColumn item in this.dataSource.Columns) colWidths[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; // 循环比较最大宽度 for (int i = 0; i < this.dataSource.Rows.Count; i++) { for (int j = 0; j < this.dataSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(this.dataSource.Rows[i][j].ToString()).Length; if (intTemp > colWidths[j]) colWidths[j] = intTemp; } } if (isOrderby > 0) { this.isOrderby = isOrderby; this.headerName = "序号#" + this.headerName; } } /// <summary> /// 获取列名下标 /// </summary> /// <param name="colName">列名称</param> /// <returns></returns> private int GetColIndex(string colName) { for (int i = 0; i < this.fileds.Length; i++) { if (colName == this.fileds[i]) return i; } return 0; } } /// <summary> /// 表头构建类 /// </summary> public class NPOIHeader { /// <summary> /// 表头 /// </summary> public string headerName { get; set; } /// <summary> /// 起始行 /// </summary> public int firstRow { get; set; } /// <summary> /// 结束行 /// </summary> public int lastRow { get; set; } /// <summary> /// 起始列 /// </summary> public int firstCol { get; set; } /// <summary> /// 结束列 /// </summary> public int lastCol { get; set; } /// <summary> /// 是否跨行 /// </summary> public int isRowSpan { get; private set; } /// <summary> /// 是否跨列 /// </summary> public int isColSpan { get; private set; } /// <summary> /// 外加行 /// </summary> public int rows { get; set; } public NPOIHeader() { } /// <summary> /// 构造函数 /// </summary> /// <param name="headerName">表头</param> /// <param name="firstRow">起始行</param> /// <param name="lastRow">结束行</param> /// <param name="firstCol">起始列</param> /// <param name="lastCol">结束列</param> /// <param name="rows">外加行</param> /// <param name="cols">外加列</param> public NPOIHeader(string headerName, int firstRow, int lastRow, int firstCol, int lastCol, int rows = 0) { this.headerName = headerName; this.firstRow = firstRow; this.lastRow = lastRow; this.firstCol = firstCol; this.lastCol = lastCol; // 是否跨行判断 if (firstRow != lastRow) isRowSpan = 1; if (firstCol != lastCol) isColSpan = 1; this.rows = rows; } }
使用示例：
//查询数据 JD_EquipmentWaringBll server = new JD_EquipmentWaringBll(HttpContext); Pagetion page = null; var datalist = server.GetEquimentWaringList(page, search, typename, devicename); if (datalist == null || datalist.Count <= 0) { datalist.Add(new BLL.Equipment.ViewModel.EquimentWaringVM() { EquimentID = "报警ID", EquimentName = "设备名称", WaringContent = "报警内容", WaringID = "设备ID", WaringTime = DateTime.Now, WaringType = "报警类型" }); } //转换为表格 DataTable dt = new CommonUtil().ListToDataTable<EquimentWaringVM>(datalist); //使用IList配置表格列和表头，供NPOI调用 IList<NPOIModel> nlist = new List<NPOIModel>(); var fileld = "EquimentName;WaringContent;WaringTime;WaringType"; var heardname = "设备名称#报警内容#报警时间#报警类型"; //把NPOIModel实体添加到Ilist列表中，多个工作簿矿压加多个实体 nlist.Add(new NPOIModel(dt, fileld, "设备报警导出文件", heardname, "设备报警导出文件")); //设置保存路径 string relativePath = "Upload/Temp/Equipment/"; string savePath = AppContext.BaseDirectory + relativePath; //判断路径是否存在，不存在就创建一个 if (!Directory.Exists(savePath)) { Directory.CreateDirectory(savePath); } //参数准备好后，调用NPOIHelper的Export方法，该方法最后会将文件存在临时储存位置 NPOIHelper.Export("设备报警导出文件", nlist, 2, savePath + "设备报警导出文件"); //文件夹中寻找 if(System.IO.File.Exists(savePath + "设备报警导出文件")) { //转为文件流，先new一个FileStream对象 FileStream fs = new FileStream(savePath + "设备报警导出文件", FileMode.Open, FileAccess.Read); //new一个长度足够的byte数组 byte[] buffur = new byte[fs.Length]; //使用FileStream对象的Read方法读取文件流 fs.Read(buffur, 0, (int)fs.Length); fs.Close(); //删除临时文件 System.IO.File.Delete(savePath + "设备报警导出文件"); lr.Data= File(buffur, "application/vnd.ms-excel;charset=utf-8", "设备报警导出文件"); return lr; } else { var result = new LRReturn(); result.Code = (int)LRReturnStatusCode.BadRequest; result.Message = "导出失败"; return result; }
