NPOI Excel Helper

  • A+
所属分类:.NET技术
摘要

测试NPOI版本为:2.5.1.0

 /// <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++;             }             using (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++;             }             using (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