三种读取Excel方法

最新推荐文章于 2023-10-12 17:32:47 发布

kirawoo

最新推荐文章于 2023-10-12 17:32:47 发布

阅读量2.2k

点赞数

分类专栏： ASP.NET C# Net

C# 同时被 3 个专栏收录

56 篇文章 2 订阅

订阅专栏

ASP.NET

33 篇文章 0 订阅

订阅专栏

Net

21 篇文章 0 订阅

订阅专栏

转自：http://blog.csdn.net/happy09li/article/details/7431967

三种读取Excel方法

---方法一---

[csharp] view plaincopyprint? 
   
 /// <summary>  
          /// 解析Excel，返回DataTable  
          /// </summary>  
          /// <param name="fileName"></param>  
          /// <returns></returns>  
          public static System.Data.DataTable ImpExcel(string fileName)  
         {  
             System.Data.DataTable dt =  new System.Data.DataTable();  
              try  
             {  
                 Microsoft.Office.Interop.Excel.Application app;  
                 Workbooks wbs;  
                 Worksheet ws;  
                 app =  new Microsoft.Office.Interop.Excel.Application();  
                 wbs = app.Workbooks;  
                 wbs.Add(fileName);  
                 ws = (Worksheet)app.Worksheets.get_Item(1);  
                       int rows = ws.UsedRange.Rows.Count;  
                  int columns = ws.UsedRange.Columns.Count;  
                  string bookName = ws.Name;  
                  dt = LoadDataFromExcel(filePath, bookName).Tables[0];  
                  //for (int i = 1; i < rows + 1; i++)  
                  //{  
                  //    DataRow dr = dt.NewRow();  
                  //    for (int j = 1; j <= columns; j++)  
                  //    {  
   
   
                  //        _Excel.Range range = ws.get_Range(app.Cells[i, j], app.Cells[i, j]);  
                  //        range.Select();  
                  //        if (i == 1)  
                  //            dt.Columns.Add("Columns" + j);// dt.Columns.Add(app.ActiveCell.Text.ToString())可以直接用第一行作为列名，单合并单元格后，读取出来后列名相同会报错，所以最好不用  
                  //        dr[j - 1] = app.ActiveCell.Text.ToString();  
                  //    }  
                  //    dt.Rows.Add(dr);  
                  //}  
   
   
                
                  //newdt = dt.Clone();  
                  //for (int i = 0; i < dt.Rows.Count; i++)  
                  //{  
                  //    if (dt.Rows[i][5].ToString() != "" && dt.Rows[i][6].ToString() != "" && dt.Rows[i][7].ToString() != "" && dt.Rows[i][8].ToString() != "" || i == 0)  
                  //    newdt.ImportRow(dt.Rows[i]);  
                  //}  
                 KillProcess(app);  
                  return dt;  
             }  
              catch (Exception ex)  
             {  
                 MessageBox.Show("数据绑定Excel失败! 失败原因："+ex.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);  
                  return dt;  
             }  
         }  

---方法二---

[csharp] view plaincopyprint? 
   
 /// <summary>  
          /// 解析Excel  
          /// </summary>  
          /// <param name="filePath"></param>  
          /// <param name="name"></param>  
          /// <returns></returns>  
          public static DataSet LoadDataFromExcel(string filePath, string name)  
         {  
              try  
             {  
                  string strConn;  
                  //   strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + filePath + ";Extended Properties=Excel 8.0";  
                 strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1'";  
                 OleDbConnection OleConn =  new OleDbConnection(strConn);  
                 OleConn.Open();  
                  string sql = "SELECT * FROM [" + name + "$]";//可是更改Sheet名称，比如sheet2，等等    
                 OleDbDataAdapter OleDaExcel =  new OleDbDataAdapter(sql, OleConn);  
                 DataSet OleDsExcle =  new DataSet();  
                 OleDaExcel.Fill(OleDsExcle, name);  
                 OleConn.Close();  
                  return OleDsExcle;  
             }  
              catch (Exception err)  
             {  
                 MessageBox.Show("数据绑定Excel失败! 失败原因：" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information);  
                  return null;  
             }  
         }  

—————插入Excel

[csharp] view plaincopyprint? 
   
 /// <summary>  
    /// 写入Excel文档  
    /// </summary>  
    /// <param name="Path">文件名称</param>  
    public bool SaveFP2toExcel(string Path)  
   {  
     try  
    {  
      string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";  
     OleDbConnection conn =  new OleDbConnection(strConn);  
     conn.Open();    
     System.Data.OleDb.OleDbCommand cmd=new OleDbCommand ();  
     cmd.Connection =conn;  
      for(int i=0;i<fp2.Sheets [0].RowCount -1;i++)  
     {  
       if(fp2.Sheets [0].Cells[i,0].Text!="")  
      {  
       cmd.CommandText ="INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('"+fp2.Sheets [0].Cells[i,0].Text+ "','"+  
        fp2.Sheets [0].Cells[i,1].Text+"','"+fp2.Sheets [0].Cells[i,2].Text+"','"+fp2.Sheets [0].Cells[i,3].Text+  
         "','"+fp2.Sheets [0].Cells[i,4].Text+"','"+fp2.Sheets [0].Cells[i,5].Text+"')";  
       cmd.ExecuteNonQuery ();  
      }  
     }  
     conn.Close ();  
      return true;  
    }  
     catch(System.Data.OleDb.OleDbException ex)  
    {  
     System.Diagnostics.Debug.WriteLine ("写入Excel发生错误："+ex.Message );  
    }  
     return false;  
   }  

[csharp] view plaincopyprint? 
   
 //新增、修改  
     _Excel.Application app =  new _Excel.ApplicationClass();  
             app.Visible =  false;  
             _Excel.Workbook book = app.Workbooks.Open(UpdateExcelPath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);  
             _Excel.Worksheet sheet = (_Excel.Worksheet)book.ActiveSheet;  
              for (int i = 0; i < dt.Rows.Count; i++)  
             {  
                 sheet.Cells[i + 2, 1] = dt.Rows[i][0].ToString();  
                 sheet.Cells[i + 2, 2] = dt.Rows[i][1].ToString();  
             }  
             book.Save();  
             book.Close(sheet, UpdateExcelPath, System.Type.Missing);  
             app.Quit();  
             System.GC.Collect();  

[csharp] view plaincopyprint? 
   
 ———————修改Excel的值  
 //修改第一行Name的值为张三  
 string strComm = "update [Sheet1$] set Name='张三' WHERE 工号='132'";  
 OleDbConnection myConn =  new OleDbConnection(strConn);  
 myConn.Open();  
 OleDbCommand com =  new OleDbCommand(strComm, myConn);  
 com.ExecuteNonQuery();  
 myConn.Close();  

方法三用NPOI读取

---用流导出Excel

[csharp] view plaincopyprint? 
   
 <span style="font-size:18px;">   private void FileStream()  
         {  
             FileStream fs =  new FileStream(path, FileMode.Create, FileAccess.Write);  
             StreamWriter sw =  new StreamWriter(fs, System.Text.Encoding.GetEncoding("gb2312"));  
              string html = @"C:\Documents and Settings\Administrator\桌面\1.html";  
             sw.WriteLine(File.ReadAllText(html));  
             sw.Close();  
         }</span>  

----------------------------导出

[csharp] view plaincopyprint? 
   
 using _Excel = Microsoft.Office.Interop.Excel;  
 using System.Drawing;  
 using System.Reflection;  
 using System.Windows.Forms;  
 using Microsoft.Office.Interop.Excel;  
          /// <summary>  
          /// DataTable直接导出Excel,此方法会把DataTable的数据用Excel打开,再自己手动去保存到确切的位置  
          /// </summary>  
          /// <param name="dt">要导出Excel的DataTable</param>  
          /// <returns></returns>  
          public static void ExportExcel(System.Data.DataTable table, string savePath, bool isExit)  
         {  
              if (!isExit)//保存路径是否存在  
                 File.Copy(System.Windows.Forms.Application.StartupPath + @"\Excel\Excel.xls", savePath);  
             _Excel.Application app =  new _Excel.ApplicationClass();  
              if (app == null)  
             {  
                  throw new Exception("Excel无法启动");  
             }  
             app.Visible =  false;  
             _Excel.Workbook book = app.Workbooks.Open(savePath, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);  
             _Excel.Worksheet sheet = (_Excel.Worksheet)book.ActiveSheet;  
   
          for (int k = 1; k < ds.Tables[1].Rows.Count + 1; k++)  
                 {  
                     _Excel.Sheets xlSheets = book.Sheets  as Sheets;  
                      //  添加 Sheet  
                     sheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);  
                 }  
            for (int j = 1; j < ds.Tables[1].Rows.Count + 1; j++)  
             {  
      
         _Excel.Range range =  null;               
                sheet = (_Excel.Worksheet)book.Sheets[j];          
                 if (!istrue)                  
              sheet.Name ="123";            
            else                  
              sheet.Name =  "345";  
                 range = sheet.get_Range("A1", "C3");  
                 range.MergeCells =  true;//合并，将1-3行和1-3列合并为一个单元格  
                 range.WrapText =  true;  //自动换行  
                 range.EntireRow.AutoFit();//行高根据内容自动调整  
                 sheet.get_Range("A1", "C3").Borders[XlBordersIndex.xlEdgeBottom].LineStyle = BorderStyle.FixedSingle;//将1-3行和1-3列合并的单元格划下划线，根据[]内属性，可以设置文本上、下、左、右的的边框框  
                 sheet.get_Range("A1", "C3").Borders.LineStyle = BorderStyle.FixedSingle;//给整个合并的单元格加上边框  
                 sheet.get_Range("A1", "C3").Font.Name = "Times New Roman";//设置字体.  
                  sheet.get_Range("A1", "C3").Font.Size = 22;//设置字体大小  
                  range.Font.Bold =  true;//加粗  
                  range.RowHeight = 22;//调行高  
                 rang.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb();//加背景色  
                 range = sheet.get_Range(string.Format("D{0}", 1), string.Format("F{0}", 1));  
                 range.MergeCells =  true;//合并，将3-5列合并为一个单元格  
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;//合并后文本水平居中  
                 range.VerticalAlignment = XlVAlign.xlVAlignCenter;//合并后文本竖直居中   
          單個sheet里求和：  
         Excel.Range range2 = sheet.get_Range("B25", Type.Missing);  
         range2.Formula =  "=SUM(B2:B24)";  
         range2.Calculate();  
   
   rang = (_Excel.Range)sheet.get_Range(string.Format("G{0}", 7), string.Format("G{0}", table.Rows.Count + 7));  
   rang.NumberFormatLocal =  "$#,##0.00"; //设置单元格格式为货币格式   
         跨sheet求和：  
         Excel.Worksheet wsheet1 = (Excel.Worksheet)excelSql.Worksheets.get_Item(1);  
         Excel.Range range3 =wsheet1.get_Range("A23", Type.Missing);  
         range3.Formula =  "=Sheet3!B8+Sheet3!B12";  
         range3.Calculate();  
          sheet.Cells[1, 4] = ds.Tables[1].Rows[0]["FeightCode"].ToString();//给合并的列赋值  
          //循环加载数据  
           int startIndex = 0;  
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)  
                 {  
                      if (ds.Tables[1].Rows[j - 1]["BoxCode"].ToString() == ds.Tables[0].Rows[i]["BoxCode"].ToString())  
                     {  
                     range = sheet.get_Range("b" + (15 + startIndex) + "", "e" + (15 + startIndex) + "");  
                     range.MergeCells =  true;  
                     sheet.Cells[15 + startIndex, 2] = ds.Tables[0].Rows[i]["TypeName"].ToString();  
                     range = (_Excel.Range)sheet.Cells[15 + startIndex, 2];  
                     range.HorizontalAlignment = XlHAlign.xlHAlignCenter;  
                     range.Font.Bold =  true;  
                     range.RowHeight = 22;  
                     sheet.Cells[15 + startIndex, 6] = ds.Tables[0].Rows[i]["Qty"].ToString();  
                     range = (_Excel.Range)sheet.Cells[15 + startIndex, 6];  
                     range.HorizontalAlignment = XlHAlign.xlHAlignCenter;  
                     range.Font.Bold =  true;  
                     range.RowHeight = 22;  
                     sheet.Cells[15 + startIndex, 7] = ds.Tables[0].Rows[i]["UnitPrice"].ToString();  
                     range = (_Excel.Range)sheet.Cells[15 + startIndex, 7];  
                     range.HorizontalAlignment = XlHAlign.xlHAlignCenter;  
                     range.Font.Bold =  true;  
                     range.RowHeight = 22;  
                     sheet.Cells[15 + startIndex, 8] = ds.Tables[0].Rows[i]["Subtotal"].ToString();  
                     range = (_Excel.Range)sheet.Cells[15 + startIndex, 8];  
                     range.HorizontalAlignment = XlHAlign.xlHAlignCenter;  
                     range.Font.Bold =  true;  
                     range.RowHeight = 22;  
                      Image image = Picture.ReadPicture(ds.Tables[0].Rows[row][col].ToString());  
                         range = sheet.get_Range(string.Format("A{0}", row * num + 29), string.Format("A{0}", row * num + 48));  
                         sheet.Shapes.AddPicture(ds.Tables[0].Rows[row][col].ToString(), Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToSingle(range.Left) + 15 + image.Width, Convert.ToSingle(range.Top) + 5, image.Width, image.Height);    //插入图片  
                     range.EntireColumn.AutoFit();  
                     System.Windows.Forms.Application.DoEvents();  
                     startIndex++;  
                     }  
                 }  

[csharp] view plaincopyprint? 
   
             Range ran = (Range)sheet.Cells[1, 1];//Excel导出加下拉框  
             ran.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween,  "1,2,3", "123");  
             ran.Validation.InCellDropdown =  true;  
             ran.Validation.IgnoreBlank =  true;  
             ran.Value2 =  "2";  
                  rang = sheet.get_Range("a" + (table.Rows.Count + 3) + "", "r" + (table.Rows.Count + 3) + "");  
                  rang.Font.Bold =  true;  
                  rang.Interior.Color = System.Drawing.Color.FromArgb(255, 204, 153).ToArgb();//设置单元格背景颜色  
                 sheet.Columns.AutoFit();  
                 sheet.Cells.EntireColumn.AutoFit();  
                 sheet.Columns.EntireColumn.AutoFit();//列宽自适应。  
                  //  sheet.Cells.Borders.LineStyle =BorderStyle.Fixed3D;//设置边框  
                 sheet =  null;  
                 book.Save();  
                 }  
                 book.Close(sheet, savePath, System.Type.Missing);  
                 app.Quit();  
                 app.DisplayAlerts=false;     //保存Excel的时候，不弹出是否保存的窗口直接进行保存  
                 System.GC.Collect();  
                 KillProcess(app);         
    
           /// <summary>  
          /// 导出Excel后，杀死Excel进程  
          /// </summary>  
          /// <param name="app"></param>  
          private static void KillProcess(_Excel.Application app)  
         {  
             IntPtr t =  new IntPtr(app.Hwnd);  
              int k = 0;  
             GetWindowThreadProcessId(t,  out k);  
             System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);  
             p.Kill();  
         }  
         [DllImport("User32.dll", CharSet = CharSet.Auto)]  
          public static extern int GetWindowThreadProcessId(IntPtr hwnd, out   int ID);  

[csharp] view plaincopyprint? 
   
   

[csharp] view plaincopyprint? 
   
   

[csharp] view plaincopyprint? 
   
 C#导出写保护的Excel，设置某些区域可以编辑  
        对Excel操作时，由于使用权限的不同，可能对表格的操作权限也不一样。EXCEL提供了保护工作表以及允许编辑单元格功能。相应的在C#中就可以对Excel表格进行操作。  
 有两种方法可以实现：  
 第一种：  
 主要用Protect（）方法保护工作表，Worksheet.Protection.AllowEditRanges设置允许编辑的单元格。  
 public void CreateExcel()  
     {  
          //创建一个Excel文件  
         Microsoft.Office.Interop.Excel.Application myExcel =  new Microsoft.Office.Interop.Excel.Application();  
         Microsoft.Office.Interop.Excel.Workbook excelWorkbook =  null;  
         Microsoft.Office.Interop.Excel.Worksheet excelSheet =  null;  
         myExcel.Application.Workbooks.Add(true);  
          //让Excel文件可见  
         myExcel.Visible =  true;  
         myExcel.Cells[1, 4] =  "普通报表";  
          //逐行写入数据  
          for (int i = 0; i < 11; i++)  
         {  
              for (int j = 0; j < 7; j++)  
             {  
                  //以单引号开头，表示该单元格为纯文本  
                 myExcel.Cells[2 + i, 1 + j] =  "'" + i;  
             }  
         }  
          try  
         {  
              string excelTemp ="c:\\a.xls";         
              //excelWorkbook = myExcel.Workbooks[1];  
             excelWorkbook = myExcel.ActiveWorkbook;  
             excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkbook.ActiveSheet;  
               
              //设定允许操作的单元格  
             Microsoft.Office.Interop.Excel.AllowEditRanges ranges = excelSheet.Protection.AllowEditRanges;  
             ranges.Add("Information",  myExcel.Application.get_Range("B2", "B2"),  Type.Missing);  
              //保护工作表  
             excelSheet.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing,  
               Type.Missing, Type.Missing, Type.Missing, Type.Missing,  
              Type.Missing, Type.Missing, Type.Missing, Type.Missing,  
               Type.Missing,  true, Type.Missing, Type.Missing);  
              //Realease the com object  
             System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet);  
             excelSheet =  null;  
   
   
              //Save the result to a temp path  
             excelWorkbook.SaveAs(excelTemp, Excel.XlFileFormat.xlWorkbookNormal,   null, null, false, false,  
                                  Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,  
                                  Type.Missing, Type.Missing,Type.Missing,Type.Missing);  
         }  
          catch (Exception ex)  
         {  
              throw;  
         }  
          finally  
         {  
              if (excelWorkbook != null)  
             {  
                 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);  
                 excelWorkbook =  null;  
             }  
              if (myExcel != null)  
             {  
                 myExcel.Workbooks.Close();  
                 myExcel.Quit();  
                 System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);  
                 myExcel =  null;  
             }  
             GC.Collect();  
         }  
     }  
 PS：借用此方法我写了个循环来设定单元格保护，没想到一直在报HRESULT:0x800A03EC 的一个异常，郁闷。  
         经过一番折腾，发现 AllowEditRanges.Add方法的第一个参数名是不能够重复的，写循环的时候没注意。   

[csharp] view plaincopyprint? 
   
 第二种：  
 用locked属性，设置Locked =  false 的区域就可编辑的区域  
 worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[10, 10]).Locked =  false;  
     //保护工作表  
   worksheet.Protect("MyPassword", Type.Missing, Type.Missing, Type.Missing,  
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,  
            Type.Missing, Type.Missing, Type.Missing, Type.Missing,  
            Type.Missing,  true, true, true);  
   
   
   
   
    ///       
        ///   在工作表中插入行，并调整其他行以留出空间     
        ///       
        ///       
   当前工作表     
        ///       
   欲插入的行索引     
        private   void   InsertRows(Excel.Worksheet   sheet,   int   rowIndex)     
       {     
         range   =   (Excel.Range)sheet.Rows[rowIndex,   missing];           
          //object   Range.Insert(object   shift,   object   copyorigin);       
          //shift:   Variant类型，可选。指定单元格的调整方式。可以为下列   XlInsertShiftDirection   常量之一：      
          //xlShiftToRight   或   xlShiftDown。如果省略该参数，Microsoft   Excel   将根据区域形状确定调整方式。      
         range.Insert(Excel.XlInsertShiftDirection.xlShiftDown,   missing);         
       }     
       
        ///       
        ///   在工作表中删除行     
        ///       
        ///       
   当前工作表     
        ///       
   欲删除的行索引     
        private   void   DeleteRows(Excel.Worksheet   sheet,   int   rowIndex)     
       {     
         range   =   (Range)sheet.Rows[rowIndex,   missing];     
         range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);     
       }     
       
        ///       
        ///   退出Excel，并且释放调用的COM资源     
        ///       
        private   void   Dispose()     
       {     
         book.Close(missing,   missing,   missing);     
         app.Workbooks.Close();     
         app.Quit();     
 <span style="white-space:pre">  </span>}  

[csharp] view plaincopyprint? 
   
   

[csharp] view plaincopyprint? 
   
   

[csharp] view plaincopyprint? 
   
 ----------------------------NOPI导出Excel-------------------------------  
 改单元格样式点击打开链接  

[csharp] view plaincopyprint? 
   
 <a target="_blank" href="http://tonyqus.sinaapp.com/tutorial">NPOI教程</a>  
 dll下载地址：<a target="_blank" href="http://download.csdn.net/detail/happy09li/4436160">点击打开链接</a>  
 using NPOI;  
 using NPOI.HPSF;  
 using NPOI.HSSF;  
 using NPOI.HSSF.UserModel;  
 public static void exportExcel(DataTable dtSource, string strFileName)  
         {  
             HSSFWorkbook workbook =  new HSSFWorkbook();  
             HSSFSheet sheet = workbook.CreateSheet();  
              //填充表头     
             HSSFRow dataRow = sheet.CreateRow(0);  
             HSSFCellStyle headStyle = workbook.CreateCellStyle();  
             headStyle.Alignment = CellHorizontalAlignment.CENTER;  
             HSSFFont font = workbook.CreateFont();  
             font.FontHeightInPoints = 20;  
             font.Boldweight = 700;  
             headStyle.SetFont(font);  
               
              foreach (DataColumn column in dtSource.Columns)  
             {  
                 dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);  
                 dataRow.GetCell(column.Ordinal).CellStyle = headStyle;  
                //  dataRow.GetCell(column.Ordinal).c  
                  //设置列宽     
                 //sheet.SetColumnWidth(column.Ordinal,column.Caption.Length*20);   
             }  
              //填充内容     
              for (int i = 0; i < dtSource.Rows.Count; i++)  
             {  
                 dataRow = sheet.CreateRow(i + 1);  
                  for (int j = 0; j < dtSource.Columns.Count; j++)  
                 {  
                     dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());  
                 }  
             }</pre><pre name="code" class="csharp"> for (int m = 0; m < dtData.Columns.Count - 1; m++)  
         {  
            for (int n = 0; n < dtData.Rows.Count - 1; n++)  
           {  
              if (sheet.GetRow(n).GetCell(m).ToString() == sheet.GetRow(n + 1).GetCell(m).ToString() && !string.IsNullOrEmpty(sheet.GetRow(n).GetCell(m).ToString()))  
             {  
               sheet.AddMergedRegion(new NPOI.HSSF.Util.Region(n, m, n + 1, m));  
             }  
              //else if (sheet.GetRow(n).GetCell(m).ToString() == sheet.GetRow(n).GetCell(m + 1).ToString())  
              //{  
              //  sheet.AddMergedRegion(new Region(n, m, n, m + 1));  
              //}  
           }  
         }  
              //保存     
              using (MemoryStream ms = new MemoryStream())  
             {  
                  using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))  
                 {  
                     workbook.Write(fs);  
                 }  
             }  
             workbook.Dispose();     
         }  
   
 <span style="font-family: Arial, Helvetica, sans-serif;"><span style="white-space: normal;">  
   
   
 </span></span>  

kirawoo

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
三种读取Excel方法

转自：http://blog.csdn.net/happy09li/article/details/7431967 三种读取Excel方法---方法一---[csharp] view plaincopyprint?/// /// 解析Excel，返回DataTable
复制链接

扫一扫

专栏目录