NPOI Excel导入导出

爱被打了一巴掌 2021-12-21 16:49 460阅读 0赞

添加引用

using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.Text;

导入

1  public DataSet ExcelToDataTable(string path)
     2         {
     3             DataSet dataSet1 = new DataSet();
     4             HSSFWorkbook hssfworkbook;
     5             using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
     6             {
     7                 hssfworkbook = new HSSFWorkbook(file); //把xls文件中的数据写入hssfworkbook中
     8 
     9 
    10                 //for (int i = 0; i < hssfworkbook.NumberOfSheets; i++)  //NumberOfSheets是myxls.xls中总共的表数
    11                 //{
         
    12                 ISheet sheet = hssfworkbook.GetSheetAt(0);   //读取当前表数据
    13 
    14                 DataTable dt = new DataTable();
    15 
    16                 for (int j = 0; j <= sheet.LastRowNum; j++)  //LastRowNum 是当前表的总行数
    17                 {
    18                     IRow row = sheet.GetRow(j);  //读取当前行数据
    19                     DataRow dr = dt.NewRow();
    20                     if (row != null)
    21                     {
    22                         //sbr.Append("-------------------------------------\r\n"); //读取行与行之间的提示界限
    23                         for (int k = 0; k < row.LastCellNum; k++)  //LastCellNum 是当前行的总列数
    24                         {
    25                             ICell cell = row.GetCell(k);  //当前表格
    26                             if (cell != null)
    27                             {
    28                                 if (j == 0)
    29                                 {
    30                                     dt.Columns.Add(cell.ToString());
    31                                 }
    32                                 else
    33                                 {
    34                                     RegexHelper regexHelper = new RegexHelper();
    35 
    36                                     if (regexHelper.IsMatch(cell.ToString(), "[0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}"))
    37                                     {
    38                                         dr[k] = cell.DateCellValue.ToString();
    39                                     }
    40                                     else
    41                                     {
    42                                         dr[k] = cell.ToString();
    43                                     }
    44                                     //日期格式判断输出
    45 
    46                                 }
    47                                 //sbr.Append(cell.ToString());   //获取表格中的数据并转换为字符串类型
    48 
    49                             }
    50                         }//for
    51                     }// if (row != null)
    52                     if (j != 0)
    53                     {
    54                         dt.Rows.Add(dr);
    55                     }
    56                 }//for j
    57 
    58                 dataSet1.Tables.Add(dt);
    59 
    60                 //}
    61 
    62             }//using
    63             return dataSet1;
    64         }

导出

1 #region "数据导出"
     2         /// <summary>
     3         /// WinForm DataTable数据导出Excel到本地
     4         /// </summary>
     5         /// <param name="table"></param>
     6         /// <param name="fileName"></param>
     7         public static void SaveToFile(DataTable table, string fileName)
     8         {
     9             using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
    10             {
    11                 MemoryStream ms = RenderToExcel(table);
    12 
    13                 byte[] data = ms.ToArray();
    14 
    15                 fs.Write(data, 0, data.Length);
    16                 fs.Flush();
    17 
    18                 data = null;
    19             }
    20         }
    21         public static MemoryStream RenderToExcel(DataTable table)
    22         {
    23             MemoryStream ms = new MemoryStream();
    24 
    25             using (table)
    26             {
    27                 using (IWorkbook workbook = new HSSFWorkbook())
    28                 {
    29                     using (ISheet sheet = workbook.CreateSheet())
    30                     {
    31                         IRow headerRow = sheet.CreateRow(0);
    32 
    33                         // 处理标题.
    34                         foreach (DataColumn column in table.Columns)
    35                             headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//如果标题没有设置,返回ColumnName的值
    36 
    37 
    38                         int rowIndex = 1;
    39 
    40                         foreach (DataRow row in table.Rows)
    41                         {
    42                             IRow dataRow = sheet.CreateRow(rowIndex);
    43 
    44                             foreach (DataColumn column in table.Columns)
    45                             {
    46                                 dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
    47                             }
    48 
    49                             rowIndex++;
    50                         }
    51 
    52                         workbook.Write(ms);
    53                         ms.Flush();
    54                         ms.Position = 0;
    55                     }
    56                 }
    57             }
    58             return ms;
    59         }
    60 
    61         /// <summary>
    62         /// Web DataTable 数据导出到浏览器客户端
    63         /// </summary>
    64         /// <param name="table"></param>
    65         /// <param name="context"></param>
    66         /// <param name="fileName"></param>
    67         static void RenderToBrowser(DataTable table, HttpContext context, string fileName)
    68         {
    69             MemoryStream ms = RenderToExcel(table);
    70             if (context.Request.Browser.Browser == "IE")
    71                 fileName = HttpUtility.UrlEncode(fileName);
    72             context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
    73             context.Response.BinaryWrite(ms.ToArray());
    74         }
    75 
    76         #endregion

调用 ExcelHelp类就是上面写的自定义类

1  ExcelHelp ExcelHelp = new ExcelHelp();
     2             //导入Excel到datagridview
     3             dataGridView1.DataSource = ExcelHelp.ExcelToDataTable(txtfileurl.Text).Tables[0];
     4             //导出
     5             saveFileDialog1.Filter = "Excel|*.Xls;";
     6             saveFileDialog1.FileName = "测试.Xls";
     7             if (saveFileDialog1.ShowDialog() == DialogResult.OK)
     8             {
     9                 ExcelHelp.SaveToFile(rptDst.Tables["interaction"], saveFileDialog1.FileName);
    10             }

转载于:https://www.cnblogs.com/thirst/p/4154654.html