NPOI读取、写入Excel

原创 Laughing  2017-07-21 10:09  阅读 281 次 评论 2 条

简介

使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的,它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。

代码封装

  1. using NPOI.HSSF.UserModel;  
  2. using NPOI.SS.UserModel;  
  3. using NPOI.XSSF.UserModel;  
  4. using System;  
  5. using System.Collections.Generic;  
  6. using System.Data;  
  7. using System.IO;  
  8. using System.Linq;  
  9. using System.Text;  
  10.   
  11. namespace Genersoft.GS.ZDB.Development.Controller.RO  
  12. {  
  13.     public class NPOIHelper : IDisposable  
  14.     {  
  15.         private string fileName = null//文件名  
  16.         private IWorkbook workbook = null;  
  17.         private FileStream fs = null;  
  18.         private bool disposed;  
  19.   
  20.         public NPOIHelper(string fileName)  
  21.         {  
  22.             this.fileName = fileName;  
  23.             disposed = false;  
  24.         }  
  25.  
  26.         #region 导出Excel 根据datatable的格式导出对应的格式  
  27.         /// <summary>  
  28.         /// 导出Excel 根据datatable的格式导出对应的格式   
  29.         /// </summary>  
  30.         /// <param name="fileName">保存路径</param>  
  31.         /// <param name="dtSource">导出的数据源</param>  
  32.         /// <param name="sheetName">创建的sheet表名称</param>  
  33.         /// <param name="isColumnWritten">是否写入列名作为Excel头</param>  
  34.         /// <returns></returns>  
  35.         public int DataTableToExcel( DataTable dtSource, string sheetName, bool isColumnWritten)  
  36.         {  
  37.             FileStream fs = null;  
  38.             int i = 0;  
  39.             int j = 0;  
  40.             int count = 0;  
  41.             IWorkbook workbook = null;  
  42.             ISheet sheet = null;  
  43.   
  44.             try  
  45.             {  
  46.   
  47.                 using (fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))  
  48.                 {  
  49.                     if (fileName.IndexOf(".xlsx") > 0) // 2007版本  
  50.                         workbook = new XSSFWorkbook();  
  51.                     else if (fileName.IndexOf(".xls") > 0) // 2003版本  
  52.                         workbook = new HSSFWorkbook();  
  53.   
  54.                     if (workbook != null)  
  55.                     {  
  56.                         sheet = workbook.CreateSheet(sheetName);  
  57.                     }  
  58.                     else  
  59.                     {  
  60.                         return -1;  
  61.                     }  
  62.                     ICellStyle dateStyle = workbook.CreateCellStyle();  
  63.                     IDataFormat format = workbook.CreateDataFormat();  
  64.                     dateStyle.DataFormat = format.GetFormat("yyyy/mm/dd");  
  65.                     ICellStyle dateStyleStr = workbook.CreateCellStyle();  
  66.                     IDataFormat formatStr = workbook.CreateDataFormat();  
  67.                     dateStyleStr.DataFormat = formatStr.GetFormat("@");  
  68.                     if (isColumnWritten == true//写入DataTable的列名  
  69.                     {  
  70.                         IRow row = sheet.CreateRow(0);  
  71.                         for (j = 0; j < dtSource.Columns.Count; ++j)  
  72.                         {  
  73.                             row.CreateCell(j).SetCellValue(dtSource.Columns[j].ColumnName);  
  74.                         }  
  75.                         count = 1;  
  76.                     }  
  77.                     else  
  78.                     {  
  79.                         count = 0;  
  80.                     }  
  81.                     foreach (DataRow dr in dtSource.Rows)  
  82.                     {  
  83.                         IRow row = sheet.CreateRow(count);  
  84.                         foreach (DataColumn column in dtSource.Columns)  
  85.                         {  
  86.                             ICell newCell = row.CreateCell(column.Ordinal);  
  87.   
  88.                             string drValue = dr[column].ToString();  
  89.   
  90.                             switch (column.DataType.ToString())  
  91.                             {  
  92.                                 case "System.String"://字符串类型  
  93.                                     newCell.SetCellValue(drValue);  
  94.                                     newCell.SetCellType(CellType.String);  
  95.                                     newCell.CellStyle = dateStyleStr;  
  96.                                     break;  
  97.                                 case "System.DateTime"://日期类型  
  98.                                     DateTime dateV;  
  99.                                     if (!string.IsNullOrEmpty(drValue))  
  100.                                     {  
  101.                                         DateTime.TryParse(drValue, out dateV);  
  102.                                         newCell.SetCellValue(dateV);  
  103.                                     }  
  104.                                     newCell.CellStyle = dateStyle;//格式化显示  
  105.                                     break;  
  106.                                 case "System.Boolean"://布尔型  
  107.                                     bool boolV = false;  
  108.                                     bool.TryParse(drValue, out boolV);  
  109.                                     newCell.SetCellValue(boolV);  
  110.                                      newCell.SetCellType(CellType.Boolean);  
  111.                                     break;  
  112.                                 case "System.Int16"://整型  
  113.                                 case "System.Int32":  
  114.                                 case "System.Int64":  
  115.                                 case "System.Byte":  
  116.                                     if (!string.IsNullOrEmpty(drValue))  
  117.                                     {  
  118.                                         int intV = 0;  
  119.                                         int.TryParse(drValue, out intV);  
  120.                                         newCell.SetCellValue(intV);  
  121.                                         newCell.SetCellType(CellType.Numeric);  
  122.                                     }       
  123.                                     break;  
  124.                                 case "System.Decimal"://浮点型  
  125.                                 case "System.Double":  
  126.                                     if (!string.IsNullOrEmpty(drValue))  
  127.                                     {  
  128.                                         double doubV = 0;  
  129.                                         double.TryParse(drValue, out doubV);  
  130.                                         newCell.SetCellValue(doubV);  
  131.                                         newCell.SetCellType(CellType.Numeric);  
  132.                                     }                                    
  133.                                     break;  
  134.                                 case "System.DBNull"://空值处理  
  135.                                     newCell.SetCellValue("");  
  136.                                     break;  
  137.                                 default:  
  138.                                     newCell.SetCellValue("");  
  139.                                     break;  
  140.                             }  
  141.   
  142.                         }  
  143.                         ++count;  
  144.                     }  
  145.                     for ( i = 0; i <= sheet.LastRowNum; i++)  
  146.                     {  
  147.                         sheet.AutoSizeColumn(i,true);  
  148.                     }  
  149.                     sheet.SetColumnWidth(4, 10 * 256);  
  150.                     sheet.SetColumnWidth(5, 10 * 256);  
  151.                     if (dtSource.Columns.Count >= 13)  
  152.                     {  
  153.                         sheet.SetColumnWidth(13, 10 * 256);  
  154.                     }  
  155.                     workbook.Write(fs); //写入到excel}  
  156.                     return count;  
  157.                 }  
  158.             }  
  159.             catch (Exception ex)  
  160.             {  
  161.                 fs.Dispose();  
  162.                 fs.Close();  
  163.                 throw;  
  164.             }  
  165.   
  166.         }  
  167.         #endregion  
  168.  
  169.         #region 导出Excel 根据datatable的格式导出对应的格式  
  170.         /// <summary>  
  171.         /// 导出Excel 根据datatable的格式导出对应的格式   
  172.         /// </summary>  
  173.         /// <param name="fileName">保存路径</param>  
  174.         /// <param name="dtSource">导出的数据源</param>  
  175.         /// <param name="sheetName">创建的sheet表名称</param>  
  176.         /// <param name="isColumnWritten">是否写入列名作为Excel头</param>  
  177.         /// <returns></returns>  
  178.         public int DataTableToExcelString(DataTable dtSource, string sheetName, bool isColumnWritten)  
  179.         {  
  180.             FileStream fs = null;  
  181.             int i = 0;  
  182.             int j = 0;  
  183.             int count = 0;  
  184.             IWorkbook workbook = null;  
  185.             ISheet sheet = null;  
  186.   
  187.             try  
  188.             {  
  189.   
  190.                 using (fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))  
  191.                 {  
  192.                     if (fileName.IndexOf(".xlsx") > 0) // 2007版本  
  193.                         workbook = new XSSFWorkbook();  
  194.                     else if (fileName.IndexOf(".xls") > 0) // 2003版本  
  195.                         workbook = new HSSFWorkbook();  
  196.   
  197.                     if (workbook != null)  
  198.                     {  
  199.                         sheet = workbook.CreateSheet(sheetName);  
  200.                     }  
  201.                     else  
  202.                     {  
  203.                         return -1;  
  204.                     }  
  205.                     ICellStyle dateStyle = workbook.CreateCellStyle();  
  206.                     IDataFormat format = workbook.CreateDataFormat();  
  207.                     dateStyle.DataFormat = format.GetFormat("yyyy/mm/dd hh:mm:ss");  
  208.                     ICellStyle dateStyleStr = workbook.CreateCellStyle();  
  209.                     IDataFormat formatStr = workbook.CreateDataFormat();  
  210.                     dateStyleStr.DataFormat = formatStr.GetFormat("@");  
  211.                     if (isColumnWritten == true//写入DataTable的列名  
  212.                     {  
  213.                         IRow row = sheet.CreateRow(0);  
  214.                         for (j = 0; j < dtSource.Columns.Count; ++j)  
  215.                         {  
  216.                             row.CreateCell(j).SetCellValue(dtSource.Columns[j].ColumnName);  
  217.                         }  
  218.                         count = 1;  
  219.                     }  
  220.                     else  
  221.                     {  
  222.                         count = 0;  
  223.                     }  
  224.                     foreach (DataRow dr in dtSource.Rows)  
  225.                     {  
  226.                         IRow row = sheet.CreateRow(count);  
  227.                         foreach (DataColumn column in dtSource.Columns)  
  228.                         {  
  229.                             ICell newCell = row.CreateCell(column.Ordinal);  
  230.   
  231.                             string drValue = dr[column].ToString();  
  232.   
  233.                             switch (column.DataType.ToString())  
  234.                             {  
  235.                                 case "System.String"://字符串类型  
  236.                                     newCell.SetCellValue(drValue);  
  237.                                     newCell.CellStyle = dateStyleStr;  
  238.                                     break;  
  239.                                 case "System.DateTime"://日期类型  
  240.                                     DateTime dateV;  
  241.                                     if (!string.IsNullOrEmpty(drValue))  
  242.                                     {  
  243.                                         DateTime.TryParse(drValue, out dateV);  
  244.                                         newCell.SetCellValue(dateV);  
  245.                                     }  
  246.                                     newCell.CellStyle = dateStyle;//格式化显示  
  247.                                     break;  
  248.                                 case "System.Boolean"://布尔型  
  249.                                     bool boolV = false;  
  250.                                     bool.TryParse(drValue, out boolV);  
  251.                                     newCell.SetCellValue(boolV);  
  252.                                     break;  
  253.                                 case "System.Int16"://整型  
  254.                                 case "System.Int32":  
  255.                                 case "System.Int64":  
  256.                                 case "System.Byte":  
  257.                                     if (!string.IsNullOrEmpty(drValue))  
  258.                                     {  
  259.                                         int intV = 0;  
  260.                                         int.TryParse(drValue, out intV);  
  261.                                         newCell.SetCellValue(intV);  
  262.                                         newCell.SetCellType(CellType.Numeric);  
  263.                                     }  
  264.                                     break;  
  265.                                 case "System.Decimal"://浮点型  
  266.                                 case "System.Double":  
  267.                                     if (!string.IsNullOrEmpty(drValue))  
  268.                                     {  
  269.                                         double doubV = 0;  
  270.                                         double.TryParse(drValue, out doubV);  
  271.                                         newCell.SetCellValue(doubV);  
  272.                                         newCell.SetCellType(CellType.Numeric);  
  273.                                     }  
  274.                                     break;  
  275.                                 case "System.DBNull"://空值处理  
  276.                                     newCell.SetCellValue("");  
  277.                                     break;  
  278.                                 default:  
  279.                                     newCell.SetCellValue("");  
  280.                                     break;  
  281.                             }  
  282.   
  283.                         }  
  284.                         ++count;  
  285.                     }  
  286.                     for (i = 0; i < sheet.LastRowNum; i++)  
  287.                     {  
  288.                         sheet.AutoSizeColumn(i, true);  
  289.                     }  
  290.                     workbook.Write(fs); //写入到excel}  
  291.                     return count;  
  292.                 }  
  293.             }  
  294.             catch (Exception ex)  
  295.             {  
  296.                 fs.Dispose();  
  297.                 fs.Close();  
  298.                 throw;  
  299.             }  
  300.   
  301.         }  
  302.         #endregion  
  303.  
  304.         #region 将excel中的数据导入到DataTable中  
  305.         /// <summary>  
  306.         /// 将excel中的数据导入到DataTable中  
  307.         /// </summary>  
  308.         /// <param name="sheetName">excel工作薄sheet的名称</param>  
  309.         /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>  
  310.         /// <returns>返回的DataTable</returns>  
  311.         public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)  
  312.         {  
  313.             ISheet sheet = null;  
  314.             DataTable data = new DataTable();  
  315.             int startRow = 0;  
  316.             try  
  317.             {  
  318.                 fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);  
  319.                 if (fileName.IndexOf(".xlsx") > 0) // 2007版本  
  320.                     workbook = new XSSFWorkbook(fs);  
  321.                 else if (fileName.IndexOf(".xls") > 0) // 2003版本  
  322.                     workbook = new HSSFWorkbook(fs);  
  323.   
  324.                 if (sheetName != null)  
  325.                 {  
  326.                     sheet = workbook.GetSheet(sheetName);  
  327.                     if (sheet == null//如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet  
  328.                     {  
  329.                         sheet = workbook.GetSheetAt(0);  
  330.                     }  
  331.                 }  
  332.                 else  
  333.                 {  
  334.                     sheet = workbook.GetSheetAt(0);  
  335.                 }  
  336.                 if (sheet != null)  
  337.                 {  
  338.                     IRow firstRow = sheet.GetRow(0);  
  339.                     int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数  
  340.   
  341.                     if (isFirstRowColumn)  
  342.                     {  
  343.                         for (int i = firstRow.FirstCellNum; i < cellCount; ++i)  
  344.                         {  
  345.                             ICell cell = firstRow.GetCell(i);  
  346.                             if (cell != null)  
  347.                             {  
  348.                                 string cellValue = cell.StringCellValue;  
  349.                                 if (cellValue != null)  
  350.                                 {  
  351.                                     DataColumn column = new DataColumn(cellValue);  
  352.                                     data.Columns.Add(column);  
  353.                                 }  
  354.                             }  
  355.                         }  
  356.                         startRow = sheet.FirstRowNum + 1;  
  357.                     }  
  358.                     else  
  359.                     {  
  360.                         startRow = sheet.FirstRowNum;  
  361.                     }  
  362.   
  363.                     //最后一列的标号  
  364.                     int rowCount = sheet.LastRowNum;  
  365.                     for (int i = startRow; i <= rowCount; ++i)  
  366.                     {  
  367.                         IRow row = sheet.GetRow(i);  
  368.                         if (row == nullcontinue//没有数据的行默认是null         
  369.   
  370.                         DataRow dataRow = data.NewRow();  
  371.                         for (int j = row.FirstCellNum; j < cellCount; ++j)  
  372.                         {  
  373.                             if (row.GetCell(j) != null//同理,没有数据的单元格都默认是null  
  374.                                 dataRow[j] = row.GetCell(j).ToString();  
  375.                         }  
  376.                         data.Rows.Add(dataRow);  
  377.                     }  
  378.                 }  
  379.   
  380.                 return data;  
  381.             }  
  382.             catch (Exception ex)  
  383.             {  
  384.                 Console.WriteLine("Exception: " + ex.Message);  
  385.                 throw;  
  386.             }  
  387.         }   
  388.         #endregion  
  389.   
  390.         public void Dispose()  
  391.         {  
  392.             Dispose(true);  
  393.             GC.SuppressFinalize(this);  
  394.         }  
  395.   
  396.         protected virtual void Dispose(bool disposing)  
  397.         {  
  398.             if (!this.disposed)  
  399.             {  
  400.                 if (disposing)  
  401.                 {  
  402.                     if (fs != null)  
  403.                         fs.Close();  
  404.                 }  
  405.   
  406.                 fs = null;  
  407.                 disposed = true;  
  408.             }  
  409.         }  
  410.     }  
  411. }  
本文地址:https://www.lisen.me/npoi-read-write-excel.html
版权声明:本文为原创文章,版权归 木子网 所有,欢迎分享本文,转载请保留出处!

发表评论


表情

  1. 今天下午
    今天下午 【队长】 @回复

    呵呵呵呵哒