C#处理Excel

C#处理Excel

Excel C# NPOI OleDb

前言

最近需要对Excel进行加密解密操作,本身是一个简单的事情,通过 OleDbConnection可以很容易进行操作Excel,或者也可以用第三方dll如NPOI进行操作。

OleDb

用OleDb方法几乎和SqlConnection一模一样,基本上把前缀Sql改成OleDb即可,不过有几个小细节需要注意。
1. 我们可以把一个Excel当成一个数据源
2. 每个sheet可以看成一张表
3. 需要注意的是你在Excel看到的sheet名称后面需要加上$才是真正的sheet名字。

具体操作

我在此简单封装了下,取名为OleDbHelper,代码如下:

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Data;
  4. using System.Data.OleDb;
  6. namespace ExcelDataEncipher
  7. {
  8.     class OleDbHelper
  9.     {
  10.         private readonly string _connstr;
  11.         //当前Sheet集合
  12.         public IEnumerable<string> SheetNames { get; set; }
  13.         //通过在外部传入数据源的名称来连接
  14.         public OleDbHelper(string name)
  15.         {
  16.             _connstr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;ReadOnly=False;HDR=Yes;'", name);
  17.             //这个连接串可以连接2003的,高版本Excel的连接串可能有所变化,由于我需要进行加密解密操作需要修改excel,所以ReadOnly设置为false 
  18.             //同时HDR设置为Yes代表第一行是标题，不做为数据使用 
  19.             this.GetSheetName();//获取所有Sheet名称
  20.         }
  22.         private void GetSheetName()
  23.         {
  24.             using (OleDbConnection conn = new OleDbConnection(_connstr))
  25.             {
  26.                 conn.Open();
  27.                 using (DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "Table"}))
  28.                 {
  29.                     //包含excel中表名的字符串数组  
  30.                     if (dtSheetName != null)
  31.                     {
  32.                         List<string> strTableNames = new List<string>();
  34.                         for (int k = 0; k < dtSheetName.Rows.Count; k++)
  35.                         {
  36.                             strTableNames.Add(dtSheetName.Rows[k]["TABLE_NAME"].ToString());
  37.                         }//获取Sheet名称
  38.                         SheetNames = strTableNames;
  39.                     }
  40.                 }
  41.             }
  42.         }
  44.         public int ExecuteNonQuery(string oleDb, 
  45.             params OleDbParameter[] parameters)
  46.         {
  47.             using (OleDbConnection conn = new OleDbConnection(_connstr))
  48.             {
  49.                 conn.Open();
  50.                 using (OleDbCommand cmd = conn.CreateCommand())
  51.                 {
  52.                     cmd.CommandText = oleDb;
  53.                     cmd.Parameters.AddRange(parameters);
  54.                     return cmd.ExecuteNonQuery();
  55.                 }
  56.             }
  57.         }
  59.         public object ExecuteScalar(string oleDb,
  60.             params OleDbParameter[] parameters)
  61.         {
  62.             using (OleDbConnection conn = new OleDbConnection(_connstr))
  63.             {
  64.                 conn.Open();
  65.                 using (OleDbCommand cmd = conn.CreateCommand())
  66.                 {
  67.                     cmd.CommandText = oleDb;
  68.                     cmd.Parameters.AddRange(parameters);
  69.                     return cmd.ExecuteScalar();
  70.                 }
  71.             }
  72.         }
  74.         public DataTable ExecuteDataTable(string oleDb,
  75.             params OleDbParameter[] parameters)
  76.         {
  77.             using (OleDbConnection conn = new OleDbConnection(_connstr))
  78.             {
  79.                 conn.Open();
  80.                 using (OleDbCommand cmd = conn.CreateCommand())
  81.                 {
  82.                     cmd.CommandText = oleDb;
  83.                     cmd.Parameters.AddRange(parameters);
  85.                     DataSet dataset = new DataSet();
  86.                     OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
  87.                     adapter.Fill(dataset);
  88.                     return dataset.Tables[0];
  89.                 }
  90.             }
  91.         }
  93.         public static object FromDbValue(object value)
  94.         {
  95.             if (value == DBNull.Value)
  96.             {
  97.                 return null;
  98.             }
  99.             else
  100.             {
  101.                 return value;
  102.             }
  103.         }
  105.         public static object ToDbValue(object value)
  106.         {
  107.             if (value == null)
  108.             {
  109.                 return DBNull.Value;
  110.             }
  111.             else
  112.             {
  113.                 return value;
  114.             }
  115.         }
  116.     }
  117. }

可以发现操作和对数据库操作一模一样

我通过OpenFileDialog选择需要操作的文件

  1. foreach (var filename in openFile.FileNames)
  2. {
  3.     string name = filename;
  4.     _oleDbHelper = new OleDbHelper(name); //获取表
  5.     string sheetName = _oleDbHelper.SheetNames.SingleOrDefault(s => s.Contains("Detail"));//查看名称是否包含Detail的sheet
  6.     DataTable table = null;
  7.     try
  8.     {
  9.         table = _oleDbHelper.ExecuteDataTable(string.Format("select * from [{0}]", sheetName));//将其转换为datatable
  10.     }
  11.     catch (Exception msg)
  12.     {
  13.         throw new Exception(msg.Message);
  14.     }
  16.     if (table.Rows.Count < 0)
  17.     {
  18.         MessageBox.Show("未找到任何记录");
  19.         return;
  20.     }
  21.     for (int i = 0; i < table.Rows.Count; i++)
  22.     {
  23.         ...//处理数据
  24.         try
  25.         {
  26.             ...//处理数据
  27.             _oleDbHelper.ExecuteNonQuery(
  28.                 string.Format(
  29.                     "update [{0}] set [AccountNumber] = '{1}',[AccountName] = '{2}',[Price] = '{3}' where [Id] = '{4}'",
  30.                     sheetName, table.Rows[i]["AccountNumber"], table.Rows[i]["AccountName"], table.Rows[i]["Price"],
  31.                     table.Rows[i]["Id"]));
  32.             //更新excel
  33.         }
  34.         catch (Exception msg)
  35.         {
  36.             throw new Exception(msg.Message);
  37.         }
  38.     }
  39. }

本来通过参数化的形式进行更新,但是一直报错,不知道什么情况
通过OleDb修改excel速度非常的慢,我的excel表 5行28列需要修改其中3列的数据需要1秒多,不知道是不是因为更新一个表格他会刷新整张excel还是为什么

OleDb

NPOI

因为速度实在太慢,我不得不寻找其他的解决方法
NPOI是一个开源的读写Excel、Word等项目,他的优点是不需要在机子上安装office也可以进行操作,而且读写速度还是非常快的。

具体操作

这里我在网上下载了别人的封装好的一个帮助类,略做了修改来适配我的项目

  1. /*******************************************************************
  2.  * 版权所有： 
  3.  * 类 名 称：ExcelHelper
  4.  * 作    者：zk
  5.  * 电子邮箱： 
  6.  * 创建日期：2012/2/25 10:17:21 
  7.  * 修改描述：从excel导入datatable时，可以导入日期类型。
  8.  *           但对excel中的日期类型有一定要求，要求至少是yyyy/mm/dd类型日期； *           
  9.  * 修改描述：将datatable导入excel中，对类型为字符串的数字进行处理，
  10.  *           导出数字为double类型；
  11.  * 
  12.  * 
  13.  * *******************************************************************/
  15. using System;
  16. using System.Collections;
  17. using System.Collections.Generic;
  18. using System.Data;
  19. using System.IO;
  20. using System.Text;
  21. using System.Text.RegularExpressions;
  22. using NPOI.HSSF.UserModel;
  23. using NPOI.SS.Formula.Eval;
  24. using NPOI.SS.UserModel;
  25. using NPOI.SS.Util;
  27. namespace ExcelDataEncipher
  28. {
  29.     public static class ExcelHelper
  30.     {
  31.         //private static WriteLog wl = new WriteLog();a
  32.         #region 从datatable中将数据导出到excel
  33.         /// <summary>
  34.         /// DataTable导出到Excel的MemoryStream
  35.         /// </summary>
  36.         /// <param name="dtSource">源DataTable</param>
  37.         /// <param name="strHeaderText">表头文本</param>
  38.         public static MemoryStream ExportDT(DataTable dtSource, string strHeaderText)
  39.         {
  40.             HSSFWorkbook workbook = new HSSFWorkbook();
  41.             HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;
  43.             #region 右击文件 属性信息
  45.             //{
  46.             //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
  47.             //    dsi.Company = "http://www.yongfa365.com/";
  48.             //    workbook.DocumentSummaryInformation = dsi;
  50.             //    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
  51.             //    si.Author = "柳永法"; //填加xls文件作者信息
  52.             //    si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
  53.             //    si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
  54.             //    si.Comments = "说明信息"; //填加xls文件作者信息
  55.             //    si.Title = "NPOI测试"; //填加xls文件标题信息
  56.             //    si.Subject = "NPOI测试Demo"; //填加文件主题信息
  57.             //    si.CreateDateTime = DateTime.Now;
  58.             //    workbook.SummaryInformation = si;
  59.             //}
  61.             #endregion
  63.             HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
  64.             HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
  65.             dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
  67.             //取得列宽
  68.             int[] arrColWidth = new int[dtSource.Columns.Count];
  69.             foreach (DataColumn item in dtSource.Columns)
  70.             {
  71.                 arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
  72.             }
  73.             for (int i = 0; i < dtSource.Rows.Count; i++)
  74.             {
  75.                 for (int j = 0; j < dtSource.Columns.Count; j++)
  76.                 {
  77.                     int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
  78.                     if (intTemp > arrColWidth[j])
  79.                     {
  80.                         arrColWidth[j] = intTemp;
  81.                     }
  82.                 }
  83.             }
  84.             int rowIndex = 0;
  86.             foreach (DataRow row in dtSource.Rows)
  87.             {
  88.                 #region 新建表，填充表头，填充列头，样式
  90.                 if (rowIndex == 65535 || rowIndex == 0)
  91.                 {
  92.                     if (rowIndex != 0)
  93.                     {
  94.                         sheet = workbook.CreateSheet() as HSSFSheet;
  95.                     }
  97.                     #region 表头及样式
  99.                     {
  100.                         HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
  101.                         headerRow.HeightInPoints = 25;
  102.                         headerRow.CreateCell(0).SetCellValue(strHeaderText);
  104.                         HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
  105.                         headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
  106.                         HSSFFont font = workbook.CreateFont() as HSSFFont;
  107.                         font.FontHeightInPoints = 20;
  108.                         font.Boldweight = 700;
  109.                         headStyle.SetFont(font);
  111.                         headerRow.GetCell(0).CellStyle = headStyle;
  113.                         sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
  114.                         //headerRow.Dispose();
  115.                     }
  117.                     #endregion
  120.                     #region 列头及样式
  122.                     {
  123.                         HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
  126.                         HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
  127.                         headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
  128.                         HSSFFont font = workbook.CreateFont() as HSSFFont;
  129.                         font.FontHeightInPoints = 10;
  130.                         font.Boldweight = 700;
  131.                         headStyle.SetFont(font);
  134.                         foreach (DataColumn column in dtSource.Columns)
  135.                         {
  136.                             headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
  137.                             headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
  139.                             //设置列宽
  140.                             sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
  142.                         }
  143.                         //headerRow.Dispose();
  144.                     }
  146.                     #endregion
  148.                     rowIndex = 2;
  149.                 }
  151.                 #endregion
  153.                 #region 填充内容
  155.                 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
  156.                 foreach (DataColumn column in dtSource.Columns)
  157.                 {
  158.                     HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
  160.                     string drValue = row[column].ToString();
  162.                     switch (column.DataType.ToString())
  163.                     {
  164.                         case "System.String": //字符串类型
  165.                             double result;
  166.                             if (isNumeric(drValue, out result))
  167.                             {
  169.                                 double.TryParse(drValue, out result);
  170.                                 newCell.SetCellValue(result);
  171.                                 break;
  172.                             }
  173.                             else
  174.                             {
  175.                                 newCell.SetCellValue(drValue);
  176.                                 break;
  177.                             }
  179.                         case "System.DateTime": //日期类型
  180.                             DateTime dateV;
  181.                             DateTime.TryParse(drValue, out dateV);
  182.                             newCell.SetCellValue(dateV);
  184.                             newCell.CellStyle = dateStyle; //格式化显示
  185.                             break;
  186.                         case "System.Boolean": //布尔型
  187.                             bool boolV = false;
  188.                             bool.TryParse(drValue, out boolV);
  189.                             newCell.SetCellValue(boolV);
  190.                             break;
  191.                         case "System.Int16": //整型
  192.                         case "System.Int32":
  193.                         case "System.Int64":
  194.                         case "System.Byte":
  195.                             int intV = 0;
  196.                             int.TryParse(drValue, out intV);
  197.                             newCell.SetCellValue(intV);
  198.                             break;
  199.                         case "System.Decimal": //浮点型
  200.                         case "System.Double":
  201.                             double doubV = 0;
  202.                             double.TryParse(drValue, out doubV);
  203.                             newCell.SetCellValue(doubV);
  204.                             break;
  205.                         case "System.DBNull": //空值处理
  206.                             newCell.SetCellValue("");
  207.                             break;
  208.                         default:
  209.                             newCell.SetCellValue("");
  210.                             break;
  211.                     }
  213.                 }
  215.                 #endregion
  217.                 rowIndex++;
  218.             }
  219.             using (MemoryStream ms = new MemoryStream())
  220.             {
  221.                 workbook.Write(ms);
  222.                 ms.Flush();
  223.                 ms.Position = 0;
  224.                 return ms;
  225.             }
  226.         }
  228.         /// <summary>
  229.         /// DataTable导出到Excel文件
  230.         /// </summary>
  231.         /// <param name="dtSource">源DataTable</param>
  232.         /// <param name="strHeaderText">表头文本</param>
  233.         /// <param name="strFileName">保存位置</param>
  234.         public static void ExportDTtoExcel(DataTable dtSource, string strHeaderText, string strFileName)
  235.         {
  236.             using (MemoryStream ms = ExportDT(dtSource, strHeaderText))
  237.             {
  238.                 using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
  239.                 {
  240.                     byte[] data = ms.ToArray();
  241.                     fs.Write(data, 0, data.Length);
  242.                     fs.Flush();
  243.                 }
  244.             }
  245.         }
  246.         #endregion
  248.         #region 从excel中将数据导出到datatable
  249.         /// <summary>读取excel
  250.         /// 默认第一行为标头
  251.         /// </summary>
  252.         /// <param name="strFileName">excel文档路径</param>
  253.         /// <returns></returns>
  254.         public static DataTable ImportExceltoDt(string strFileName,string sheetName,bool includeTitle = true)
  255.         {
  256.             DataTable dt = new DataTable();
  257.             HSSFWorkbook hssfworkbook;
  258.             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
  259.             {
  260.                 hssfworkbook = new HSSFWorkbook(file);
  261.             }
  262.             HSSFSheet sheet = hssfworkbook.GetSheet(sheetName) as HSSFSheet;
  263.             if (sheet == null) return null;
  264.             dt = ImportDt(sheet, 0, includeTitle);
  265.             return dt;
  266.         }
  268.         /// <summary>
  269.         /// 读取excel
  270.         /// </summary>
  271.         /// <param name="strFileName">excel文件路径</param>
  272.         /// <param name="sheet">需要导出的sheet</param>
  273.         /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
  274.         /// <returns></returns>
  275.         public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex)
  276.         {
  277.             HSSFWorkbook workbook;
  278.             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
  279.             {
  280.                 workbook = new HSSFWorkbook(file);
  281.             }
  282.             HSSFSheet sheet = workbook.GetSheet(SheetName) as HSSFSheet;
  283.             DataTable table = new DataTable();
  284.             table = ImportDt(sheet, HeaderRowIndex, true);
  285.             //ExcelFileStream.Close();
  286.             workbook = null;
  287.             sheet = null;
  288.             return table;
  289.         }
  291.         /// <summary>
  292.         /// 读取excel
  293.         /// </summary>
  294.         /// <param name="strFileName">excel文件路径</param>
  295.         /// <param name="sheet">需要导出的sheet序号</param>
  296.         /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
  297.         /// <returns></returns>
  298.         public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex)
  299.         {
  300.             HSSFWorkbook workbook;
  301.             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
  302.             {
  303.                 workbook = new HSSFWorkbook(file);
  304.             }
  305.             HSSFSheet sheet = workbook.GetSheetAt(SheetIndex) as HSSFSheet;
  306.             DataTable table = new DataTable();
  307.             table = ImportDt(sheet, HeaderRowIndex, true);
  308.             //ExcelFileStream.Close();
  309.             workbook = null;
  310.             sheet = null;
  311.             return table;
  312.         }
  314.         /// <summary>
  315.         /// 读取excel
  316.         /// </summary>
  317.         /// <param name="strFileName">excel文件路径</param>
  318.         /// <param name="sheet">需要导出的sheet</param>
  319.         /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
  320.         /// <returns></returns>
  321.         public static DataTable ImportExceltoDt(string strFileName, string SheetName, int HeaderRowIndex, bool needHeader)
  322.         {
  323.             HSSFWorkbook workbook;
  324.             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
  325.             {
  326.                 workbook = new HSSFWorkbook(file);
  327.             }
  328.             HSSFSheet sheet = workbook.GetSheet(SheetName) as HSSFSheet;
  329.             DataTable table = new DataTable();
  330.             table = ImportDt(sheet, HeaderRowIndex, needHeader);
  331.             //ExcelFileStream.Close();
  332.             workbook = null;
  333.             sheet = null;
  334.             return table;
  335.         }
  337.         /// <summary>
  338.         /// 读取excel
  339.         /// </summary>
  340.         /// <param name="strFileName">excel文件路径</param>
  341.         /// <param name="sheet">需要导出的sheet序号</param>
  342.         /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
  343.         /// <returns></returns>
  344.         public static DataTable ImportExceltoDt(string strFileName, int SheetIndex, int HeaderRowIndex, bool needHeader)
  345.         {
  346.             HSSFWorkbook workbook;
  347.             using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
  348.             {
  349.                 workbook = new HSSFWorkbook(file);
  350.             }
  351.             HSSFSheet sheet = workbook.GetSheetAt(SheetIndex) as HSSFSheet;
  352.             DataTable table = new DataTable();
  353.             table = ImportDt(sheet, HeaderRowIndex, needHeader);
  354.             //ExcelFileStream.Close();
  355.             workbook = null;
  356.             sheet = null;
  357.             return table;
  358.         }
  360.         /// <summary>
  361.         /// 将制定sheet中的数据导出到datatable中
  362.         /// </summary>
  363.         /// <param name="sheet">需要导出的sheet</param>
  364.         /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
  365.         /// <returns></returns>
  366.         static DataTable ImportDt(HSSFSheet sheet, int HeaderRowIndex, bool needHeader)
  367.         {
  368.             DataTable table = new DataTable();
  369.             HSSFRow headerRow;
  370.             int cellCount;
  371.             try
  372.             {
  373.                 if (HeaderRowIndex < 0 || !needHeader)
  374.                 {
  375.                     headerRow = sheet.GetRow(0) as HSSFRow;
  376.                     cellCount = headerRow.LastCellNum;
  378.                     for (int i = headerRow.FirstCellNum; i < cellCount; i++)
  379.                     {
  380.                         DataColumn column = new DataColumn(Convert.ToString(i));
  381.                         table.Columns.Add(column);
  382.                     }
  383.                 }
  384.                 else
  385.                 {
  386.                     headerRow = sheet.GetRow(HeaderRowIndex) as HSSFRow;
  387.                     cellCount = headerRow.LastCellNum;
  389.                     for (int i = headerRow.FirstCellNum; i < cellCount; i++)
  390.                     {
  391.                         if (headerRow.GetCell(i) == null)
  392.                         {
  393.                             if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
  394.                             {
  395.                                 DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
  396.                                 table.Columns.Add(column);
  397.                             }
  398.                             else
  399.                             {
  400.                                 DataColumn column = new DataColumn(Convert.ToString(i));
  401.                                 table.Columns.Add(column);
  402.                             }
  404.                         }
  405.                         else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
  406.                         {
  407.                             DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
  408.                             table.Columns.Add(column);
  409.                         }
  410.                         else
  411.                         {
  412.                             DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
  413.                             table.Columns.Add(column);
  414.                         }
  415.                     }
  416.                 }
  417.                 int rowCount = sheet.LastRowNum;
  418.                 for (int i = (HeaderRowIndex + 1); i <= rowCount; i++)
  419.                 {
  420.                     try
  421.                     {
  422.                         HSSFRow row;
  423.                         if (sheet.GetRow(i) == null)
  424.                         {
  425.                             row = sheet.CreateRow(i) as HSSFRow;
  426.                         }
  427.                         else
  428.                         {
  429.                             row = sheet.GetRow(i) as HSSFRow;
  430.                         }
  432.                         DataRow dataRow = table.NewRow();
  434.                         for (int j = row.FirstCellNum; j < cellCount; j++)
  435.                         {
  436.                             try
  437.                             {
  438.                                 if (row.GetCell(j) != null)
  439.                                 {
  440.                                     switch (row.GetCell(j).CellType)
  441.                                     {
  442.                                         case CellType.String:
  443.                                             string str = row.GetCell(j).StringCellValue;
  444.                                             if (!string.IsNullOrEmpty(str))
  445.                                             {
  446.                                                 dataRow[j] = str.ToString();
  447.                                             }
  448.                                             else
  449.                                             {
  450.                                                 dataRow[j] = null;
  451.                                             }
  452.                                             break;
  453.                                         case CellType.Numeric:
  454.                                             if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
  455.                                             {
  456.                                                 dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
  457.                                             }
  458.                                             else
  459.                                             {
  460.                                                 dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
  461.                                             }
  462.                                             break;
  463.                                         case CellType.Boolean:
  464.                                             dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
  465.                                             break;
  466.                                         case CellType.Error:
  467.                                             dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
  468.                                             break;
  469.                                         case CellType.Formula:
  470.                                             switch (row.GetCell(j).CachedFormulaResultType)
  471.                                             {
  472.                                                 case CellType.String:
  473.                                                     string strFORMULA = row.GetCell(j).StringCellValue;
  474.                                                     if (!string.IsNullOrEmpty(strFORMULA))
  475.                                                     {
  476.                                                         dataRow[j] = strFORMULA.ToString();
  477.                                                     }
  478.                                                     else
  479.                                                     {
  480.                                                         dataRow[j] = null;
  481.                                                     }
  482.                                                     break;
  483.                                                 case CellType.Numeric:
  484.                                                     dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
  485.                                                     break;
  486.                                                 case CellType.Boolean:
  487.                                                     dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
  488.                                                     break;
  489.                                                 case CellType.Error:
  490.                                                     dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
  491.                                                     break;
  492.                                                 default:
  493.                                                     dataRow[j] = "";
  494.                                                     break;
  495.                                             }
  496.                                             break;
  497.                                         default:
  498.                                             dataRow[j] = "";
  499.                                             break;
  500.                                     }
  501.                                 }
  502.                             }
  503.                             catch (Exception exception)
  504.                             {
  505.                                 throw  new Exception(exception.Message);
  506.                                 //wl.WriteLogs(exception.ToString());
  507.                             }
  508.                         }
  509.                         table.Rows.Add(dataRow);
  510.                     }
  511.                     catch (Exception exception)
  512.                     {
  513.                         throw new Exception(exception.Message);
  514.                         //wl.WriteLogs(exception.ToString());
  515.                     }
  516.                 }
  517.             }
  518.             catch (Exception exception)
  519.             {
  520.                 throw new Exception(exception.Message);
  521.                 //wl.WriteLogs(exception.ToString());
  522.             }
  523.             return table;
  524.         }
  525.         #endregion
  527.         #region 更新excel中的数据
  529.         /// <summary>
  530.         /// 更新Excel表格
  531.         /// </summary>
  532.         /// <param name="outputFile">需更新的excel表格路径</param>
  533.         /// <param name="sheetname">sheet名</param>
  534.         /// <param name="data"></param>
  535.         /// <param name="saveTitle"></param>
  536.         public static bool UpdateExcel(string oldFile,string newFile, string sheetname,DataTable data,bool saveTitle = true)
  537.         {
  538.             try
  539.             {
  540.                 HSSFWorkbook hssfworkbook;
  541.                 using (FileStream readfile = new FileStream(oldFile, FileMode.Open, FileAccess.Read))
  542.                 {
  544.                     hssfworkbook = new HSSFWorkbook(readfile);
  545.                     ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
  546.                     int currentSheetRowIndex = 0;
  547.                     if (saveTitle) currentSheetRowIndex = 1;
  548.                     for (int i = 0; i < data.Rows.Count; i++, currentSheetRowIndex++)
  549.                     {
  550.                         for (int j = 0; j < data.Columns.Count; j++)
  551.                         {
  552.                             if (sheet1.GetRow(currentSheetRowIndex) == null)
  553.                                 sheet1.CreateRow(currentSheetRowIndex);
  554.                             if (sheet1.GetRow(currentSheetRowIndex).GetCell(j) == null)
  555.                                 sheet1.GetRow(currentSheetRowIndex).CreateCell(j);
  556.                             sheet1.GetRow(currentSheetRowIndex).GetCell(j).SetCellValue(data.Rows[i][j].ToString());
  557.                         }
  559.                     }
  560.                 }
  561.                 using (FileStream writefile = new FileStream(newFile, FileMode.Create, FileAccess.Write))
  562.                 {
  563.                     hssfworkbook.Write(writefile);
  564.                 }
  565.             }
  566.             catch (IOException ioException)
  567.             {
  568.                 throw new IOException(ioException.Message);
  569.             }
  570.             catch (UnauthorizedAccessException unauthorizedAccessException)
  571.             {
  572.                 throw new UnauthorizedAccessException(unauthorizedAccessException.Message);
  573.             }
  574.             catch (Exception ex)
  575.             {
  576.                 throw new Exception(ex.Message);
  577.                 // wl.WriteLogs(ex.ToString());
  578.             }
  579.             return true;
  580.         }
  582.         /// <summary>
  583.         /// 更新Excel表格
  584.         /// </summary>
  585.         /// <param name="outputFile">需更新的excel表格路径</param>
  586.         /// <param name="sheetname">sheet名</param>
  587.         /// <param name="updateData">需更新的数据</param>
  588.         /// <param name="coluids">需更新的列号</param>
  589.         /// <param name="rowid">需更新的开始行号</param>
  590.         public static void UpdateExcel(string outputFile, string sheetname, string[][] updateData, int[] coluids, int rowid)
  591.         {
  592.             FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
  594.             HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
  595.             readfile.Close();
  596.             ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
  597.             for (int j = 0; j < coluids.Length; j++)
  598.             {
  599.                 for (int i = 0; i < updateData[j].Length; i++)
  600.                 {
  601.                     try
  602.                     {
  603.                         if (sheet1.GetRow(i + rowid) == null)
  604.                         {
  605.                             sheet1.CreateRow(i + rowid);
  606.                         }
  607.                         if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
  608.                         {
  609.                             sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
  610.                         }
  611.                         sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
  612.                     }
  613.                     catch (Exception ex)
  614.                     {
  615.                         // wl.WriteLogs(ex.ToString());
  616.                     }
  617.                 }
  618.             }
  619.             try
  620.             {
  621.                 FileStream writefile = new FileStream(outputFile, FileMode.Create);
  622.                 hssfworkbook.Write(writefile);
  623.                 writefile.Close();
  624.             }
  625.             catch (Exception ex)
  626.             {
  627.                 //wl.WriteLogs(ex.ToString());
  628.             }
  629.         }
  631.         /// <summary>
  632.         /// 更新Excel表格
  633.         /// </summary>
  634.         /// <param name="outputFile">需更新的excel表格路径</param>
  635.         /// <param name="sheetname">sheet名</param>
  636.         /// <param name="updateData">需更新的数据</param>
  637.         /// <param name="coluid">需更新的列号</param>
  638.         /// <param name="rowid">需更新的开始行号</param>
  639.         public static void UpdateExcel(string outputFile, string sheetname, double[] updateData, int coluid, int rowid)
  640.         {
  641.             FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
  643.             HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
  644.             ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
  645.             for (int i = 0; i < updateData.Length; i++)
  646.             {
  647.                 try
  648.                 {
  649.                     if (sheet1.GetRow(i + rowid) == null)
  650.                     {
  651.                         sheet1.CreateRow(i + rowid);
  652.                     }
  653.                     if (sheet1.GetRow(i + rowid).GetCell(coluid) == null)
  654.                     {
  655.                         sheet1.GetRow(i + rowid).CreateCell(coluid);
  656.                     }
  658.                     sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]);
  659.                 }
  660.                 catch (Exception ex)
  661.                 {
  662.                     throw new Exception(ex.Message);
  663.                     //wl.WriteLogs(ex.ToString());
  664.                     throw;
  665.                 }
  666.             }
  667.             try
  668.             {
  669.                 readfile.Close();
  670.                 FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
  671.                 hssfworkbook.Write(writefile);
  672.                 writefile.Close();
  673.             }
  674.             catch (Exception ex)
  675.             {
  676.                 //wl.WriteLogs(ex.ToString());
  677.             }
  679.         }
  681.         /// <summary>
  682.         /// 更新Excel表格
  683.         /// </summary>
  684.         /// <param name="outputFile">需更新的excel表格路径</param>
  685.         /// <param name="sheetname">sheet名</param>
  686.         /// <param name="updateData">需更新的数据</param>
  687.         /// <param name="coluids">需更新的列号</param>
  688.         /// <param name="rowid">需更新的开始行号</param>
  689.         public static void UpdateExcel(string outputFile, string sheetname, double[][] updateData, int[] coluids, int rowid)
  690.         {
  691.             FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
  693.             HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
  694.             readfile.Close();
  695.             ISheet sheet1 = hssfworkbook.GetSheet(sheetname);
  696.             for (int j = 0; j < coluids.Length; j++)
  697.             {
  698.                 for (int i = 0; i < updateData[j].Length; i++)
  699.                 {
  700.                     try
  701.                     {
  702.                         if (sheet1.GetRow(i + rowid) == null)
  703.                         {
  704.                             sheet1.CreateRow(i + rowid);
  705.                         }
  706.                         if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null)
  707.                         {
  708.                             sheet1.GetRow(i + rowid).CreateCell(coluids[j]);
  709.                         }
  710.                         sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]);
  711.                     }
  712.                     catch (Exception ex)
  713.                     {
  714.                         //wl.WriteLogs(ex.ToString());
  715.                     }
  716.                 }
  717.             }
  718.             try
  719.             {
  720.                 FileStream writefile = new FileStream(outputFile, FileMode.Create);
  721.                 hssfworkbook.Write(writefile);
  722.                 writefile.Close();
  723.             }
  724.             catch (Exception ex)
  725.             {
  726.                 //wl.WriteLogs(ex.ToString());
  727.             }
  728.         }
  730.         #endregion
  732.         public static int GetSheetNumber(string outputFile)
  733.         {
  734.             int number = 0;
  735.             try
  736.             {
  737.                 FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
  739.                 HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
  740.                 number = hssfworkbook.NumberOfSheets;
  742.             }
  743.             catch (Exception exception)
  744.             {
  745.                 //wl.WriteLogs(exception.ToString());
  746.             }
  747.             return number;
  748.         }
  750.         public static ArrayList GetSheetName(string outputFile)
  751.         {
  752.             ArrayList arrayList = new ArrayList();
  753.             try
  754.             {
  755.                 FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);
  757.                 HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
  758.                 for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)
  759.                 {
  760.                     arrayList.Add(hssfworkbook.GetSheetName(i));
  761.                 }
  762.             }
  763.             catch (Exception exception)
  764.             {
  765.                 //wl.WriteLogs(exception.ToString());
  766.             }
  767.             return arrayList;
  768.         }
  770.         public static bool isNumeric(String message, out double result)
  771.         {
  772.             Regex rex = new Regex(@"^[-]?d+[.]?d*$");
  773.             result = -1;
  774.             if (rex.IsMatch(message))
  775.             {
  776.                 result = double.Parse(message);
  777.                 return true;
  778.             }
  779.             else return false;
  781.         }
  782.     }
  783. }
  2. DataTable table = ExcelHelper.ImportExceltoDt(name,"Detail");//name是excel的路径txtSheetName是sheet名称
  3. success = ExcelHelper.UpdateExcel(name, newFileName, "Detail", table);//name是原excel名称,这里我需要把处理好的文件另存而不去修改源文件所以我加入了一个新的路径,table是需要保存的数据返回值为bool表示成功或失败

通过测试NPOI速度为OLEDB的数十倍,

NPOI

posted @ 2015-07-31 18:19  杰哥很忙  阅读(1416)  评论(0)  编辑  收藏  举报