NPOI Excel Helper
/// <summary> /// NPOIExcel帮助类 /// </summary> public class NPOIExcelHelper { /// <summary> /// DataTable导出到Excel文件 /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strFileName">保存位置</param> public static void DataTableToExcel(DataTable dtSource, string strHeaderText, string strFileName) { ExcelFileSuffixType suffix = default(ExcelFileSuffixType); if (strFileName.IndexOf(".xlsx") > 0) // 2007版本 suffix = ExcelFileSuffixType.xlsx; else if (strFileName.IndexOf(".xls") > 0) // 2003版本 suffix = ExcelFileSuffixType.xls; using (MemoryStream ms = DataTableToExcelStream(dtSource, strHeaderText, suffix)) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } /// <summary> /// DataGridView导出到Excel文件 /// </summary> /// <param name="dtSource">源DataTGridview</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strFileName">保存位置</param> public static void DataGridViewToExcel(DataGridView myDgv, string strHeaderText, string strFileName) { ExcelFileSuffixType suffix = default(ExcelFileSuffixType); if (strFileName.IndexOf(".xlsx") > 0) // 2007版本 suffix = ExcelFileSuffixType.xlsx; else if (strFileName.IndexOf(".xls") > 0) // 2003版本 suffix = ExcelFileSuffixType.xls; using (MemoryStream ms = DataGridViewToExcelStream(myDgv, strHeaderText, suffix)) { using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } /// <summary> /// 将excel文件导入至DataTable /// </summary> /// <param name="strFileName">excel文件全路径</param> /// <returns></returns> public static DataTable ExcelToDataTable(string strFileName) { DataTable dt = new DataTable(); IWorkbook workbook = null; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { #if New_NPOI if (strFileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(file); else if (strFileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(file); #endif #if Old_NPOI workbook = new HSSFWorkbook(file); #endif } ISheet sheet = workbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } return dt; } #region private /// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strHeaderText">excel文件后缀类型</param> private static MemoryStream DataTableToExcelStream(DataTable dtSource, string strHeaderText, ExcelFileSuffixType suffix) { ISheet sheet = null; IWorkbook workbook = null; ICellStyle cellStyle = null; IDataFormat dataFormat = null; #if New_NPOI if (suffix == ExcelFileSuffixType.xls) workbook = new HSSFWorkbook(); else if (suffix == ExcelFileSuffixType.xlsx) workbook = new XSSFWorkbook(); #endif #if Old_NPOI workbook = new HSSFWorkbook(); #endif sheet = workbook.CreateSheet(); cellStyle = workbook.CreateCellStyle(); dataFormat = workbook.CreateDataFormat(); cellStyle.DataFormat = dataFormat.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表，填充表头，填充列头，样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; #if New_NPOI font.IsBold = true; #endif #if Old_NPOI font.Boldweight = 700; #endif headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; #if New_NPOI font.IsBold = true; #endif #if Old_NPOI font.Boldweight = 700; #endif headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 // sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } // headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 if (drValue.Length > 30000) drValue = drValue.Substring(0, 30000); newCell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 System.DateTime dateV; System.DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = cellStyle;//格式化显示 break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; }
MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); #if New_NPOI workbook.Close(); #endif return ms; } /// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="myDgv">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strHeaderText">excel文件后缀类型</param> private static MemoryStream DataGridViewToExcelStream(DataGridView myDgv, string strHeaderText, ExcelFileSuffixType suffix) { ISheet sheet = null; IWorkbook workbook = null; ICellStyle cellStyle = null; IDataFormat dataFormat = null; #if New_NPOI if (suffix == ExcelFileSuffixType.xls) workbook = new HSSFWorkbook(); else if (suffix == ExcelFileSuffixType.xlsx) workbook = new XSSFWorkbook(); #endif #if Old_NPOI workbook = new HSSFWorkbook(); #endif sheet = workbook.CreateSheet(); cellStyle = workbook.CreateCellStyle(); dataFormat = workbook.CreateDataFormat(); cellStyle.DataFormat = dataFormat.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[myDgv.Columns.Count]; foreach (DataGridViewColumn item in myDgv.Columns) { arrColWidth[item.Index] = Encoding.GetEncoding(936).GetBytes(item.HeaderText.ToString()).Length; } for (int i = 0; i < myDgv.Rows.Count; i++) { for (int j = 0; j < myDgv.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(myDgv.Rows[i].Cells[j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataGridViewRow row in myDgv.Rows) { #region 新建表，填充表头，填充列头，样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; #if New_NPOI font.IsBold = true; #endif #if Old_NPOI font.Boldweight = 700; #endif headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; #if New_NPOI font.IsBold = true; #endif #if Old_NPOI font.Boldweight = 700; #endif headStyle.SetFont(font); foreach (DataGridViewColumn column in myDgv.Columns) { headerRow.CreateCell(column.Index).SetCellValue(column.HeaderText); headerRow.GetCell(column.Index).CellStyle = headStyle; //设置列宽 //sheet.SetColumnWidth(column.Index, (arrColWidth[column.Index] + 1) * 256); } // headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); if (row.Index > 0) { foreach (DataGridViewColumn column in myDgv.Columns) { ICell newCell = dataRow.CreateCell(column.Index); string drValue = myDgv[column.Index, row.Index - 1].Value.ToString(); switch (column.ValueType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 System.DateTime dateV; System.DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = cellStyle;//格式化显示 break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } } else { rowIndex--; } #endregion rowIndex++; }
MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); #if New_NPOI workbook.Close(); #endif return ms; } #endregion } /// <summary> /// excel文件后缀格式 /// </summary> enum ExcelFileSuffixType { /// <summary> /// Excel 2007及以上版本支持 /// </summary> xls, /// <summary> /// Excel 2003及以上版本兼容支持 /// </summary> xlsx }
注，测试NPOI版本为：2.5.1.0，包含的文件及依赖：
