asp.net(C#)之NPOI"操作Excel

1.首先到网上下载"NPOI.DLL"，引用。

2.新建一个操作类“ExcelHelper.cs”：

using System.Collections.Generic;  using System.Data;  using System.IO;  using System.Linq;  using NPOI.HSSF.UserModel;  using NPOI.SS.UserModel;  using NPOI.XSSF.UserModel;    public class ExcelHelper  {      public class x2003      {          #region Excel2003          /// <summary>          /// 将Excel文件中的数据读出到DataTable中(xls)          /// </summary>          /// <param name="file"></param>          /// <returns></returns>          public static DataTable ExcelToTableForXLS(string file)          {              DataTable dt = new DataTable();              using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))              {                  HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);                  ISheet sheet = hssfworkbook.GetSheetAt(0);                    //表头                  IRow header = sheet.GetRow(sheet.FirstRowNum);                  List<int> columns = new List<int>();                  for (int i = 0; i < header.LastCellNum; i++)                  {                      object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);                      if (obj == null || obj.ToString() == string.Empty)                      {                          dt.Columns.Add(new DataColumn("Columns" + i.ToString()));                          //continue;                      }                      else                          dt.Columns.Add(new DataColumn(obj.ToString()));                      columns.Add(i);                  }                  //数据                  for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)                  {                      DataRow dr = dt.NewRow();                      bool hasValue = false;                      foreach (int j in columns)                      {                          dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);                          if (dr[j] != null && dr[j].ToString() != string.Empty)                          {                              hasValue = true;                          }                      }                      if (hasValue)                      {                          dt.Rows.Add(dr);                      }                  }              }              return dt;          }            /// <summary>          /// 将DataTable数据导出到Excel文件中(xls)          /// </summary>          /// <param name="dt"></param>          /// <param name="file"></param>          public static void TableToExcelForXLS(DataTable dt, string file)          {              HSSFWorkbook hssfworkbook = new HSSFWorkbook();              ISheet sheet = hssfworkbook.CreateSheet("Test");                //表头              IRow row = sheet.CreateRow(0);              for (int i = 0; i < dt.Columns.Count; i++)              {                  ICell cell = row.CreateCell(i);                  cell.SetCellValue(dt.Columns[i].ColumnName);              }                //数据              for (int i = 0; i < dt.Rows.Count; i++)              {                  IRow row1 = sheet.CreateRow(i + 1);                  for (int j = 0; j < dt.Columns.Count; j++)                  {                      ICell cell = row1.CreateCell(j);                      cell.SetCellValue(dt.Rows[i][j].ToString());                  }              }                //转为字节数组              MemoryStream stream = new MemoryStream();              hssfworkbook.Write(stream);              var buf = stream.ToArray();                //保存为Excel文件              using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))              {                  fs.Write(buf, 0, buf.Length);                  fs.Flush();              }          }            /// <summary>          /// 获取单元格类型(xls)          /// </summary>          /// <param name="cell"></param>          /// <returns></returns>          private static object GetValueTypeForXLS(HSSFCell cell)          {              if (cell == null)                  return null;              switch (cell.CellType)              {                  case CellType.Blank: //BLANK:                      return null;                  case CellType.Boolean: //BOOLEAN:                      return cell.BooleanCellValue;                  case CellType.Numeric: //NUMERIC:                      return cell.NumericCellValue;                  case CellType.String: //STRING:                      return cell.StringCellValue;                  case CellType.Error: //ERROR:                      return cell.ErrorCellValue;                  case CellType.Formula: //FORMULA:                  default:                      return "=" + cell.CellFormula;              }          }          #endregion      }        public class x2007      {          #region Excel2007          /// <summary>          /// 将Excel文件中的数据读出到DataTable中(xlsx)          /// </summary>          /// <param name="file"></param>          /// <returns></returns>          public static DataTable ExcelToTableForXLSX(string file)          {              DataTable dt = new DataTable();              using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))              {                  XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);                  ISheet sheet = xssfworkbook.GetSheetAt(0);                    //表头                  IRow header = sheet.GetRow(sheet.FirstRowNum);                  List<int> columns = new List<int>();                  for (int i = 0; i < header.LastCellNum; i++)                  {                      object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);                      if (obj == null || obj.ToString() == string.Empty)                      {                          dt.Columns.Add(new DataColumn("Columns" + i.ToString()));                          //continue;                      }                      else                          dt.Columns.Add(new DataColumn(obj.ToString()));                      columns.Add(i);                  }                  //数据                  for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)                  {                      DataRow dr = dt.NewRow();                      bool hasValue = false;                      foreach (int j in columns)                      {                          dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);                          if (dr[j] != null && dr[j].ToString() != string.Empty)                          {                              hasValue = true;                          }                      }                      if (hasValue)                      {                          dt.Rows.Add(dr);                      }                  }              }              return dt;          }            /// <summary>          /// 将DataTable数据导出到Excel文件中(xlsx)          /// </summary>          /// <param name="dt"></param>          /// <param name="file"></param>          public static void TableToExcelForXLSX(DataTable dt, string file)          {              XSSFWorkbook xssfworkbook = new XSSFWorkbook();              ISheet sheet = xssfworkbook.CreateSheet("Test");                //表头              IRow row = sheet.CreateRow(0);              for (int i = 0; i < dt.Columns.Count; i++)              {                  ICell cell = row.CreateCell(i);                  cell.SetCellValue(dt.Columns[i].ColumnName);              }                //数据              for (int i = 0; i < dt.Rows.Count; i++)              {                  IRow row1 = sheet.CreateRow(i + 1);                  for (int j = 0; j < dt.Columns.Count; j++)                  {                      ICell cell = row1.CreateCell(j);                      cell.SetCellValue(dt.Rows[i][j].ToString());                  }              }                //转为字节数组              MemoryStream stream = new MemoryStream();              xssfworkbook.Write(stream);              var buf = stream.ToArray();                //保存为Excel文件              using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))              {                  fs.Write(buf, 0, buf.Length);                  fs.Flush();              }          }            /// <summary>          /// 获取单元格类型(xlsx)          /// </summary>          /// <param name="cell"></param>          /// <returns></returns>          private static object GetValueTypeForXLSX(XSSFCell cell)          {              if (cell == null)                  return null;              switch (cell.CellType)              {                  case CellType.Blank: //BLANK:                      return null;                  case CellType.Boolean: //BOOLEAN:                      return cell.BooleanCellValue;                  case CellType.Numeric: //NUMERIC:                      return cell.NumericCellValue;                  case CellType.String: //STRING:                      return cell.StringCellValue;                  case CellType.Error: //ERROR:                      return cell.ErrorCellValue;                  case CellType.Formula: //FORMULA:                  default:                      return "=" + cell.CellFormula;              }          }          #endregion      }        public static DataTable GetDataTable(string filepath)      {          var dt = new DataTable("xls");          if (filepath.Last()=='s')          {              dt = x2003.ExcelToTableForXLS(filepath);          }          else          {              dt = x2007.ExcelToTableForXLSX(filepath);          }          return dt;      }  }  

3.程序后台主要代码：

using System;  using System.Collections.Generic;  using System.Web;  using System.Web.UI;  using System.Web.UI.WebControls;  using System.Data;  using System.Linq;  public partial class _Default : System.Web.UI.Page   {      protected void Page_Load(object sender, EventArgs e)      {        }      protected void btn_read_03_click(object o, EventArgs e)      {          var dt = ExcelHelper.GetDataTable(Server.MapPath("~/xls_tmp/2003.xls"));          g1.DataSource = dt;          g1.DataBind();      }      protected void btn_read_07_click(object o, EventArgs e)      {          var dt = ExcelHelper.GetDataTable(Server.MapPath("~/xls_tmp/2007.xlsx"));          g1.DataSource = dt;          g1.DataBind();      }      protected void btn_import_03_click(object o, EventArgs e)      {          var name = DateTime.Now.ToString("yyyyMMddhhmmss") + new Random(DateTime.Now.Second).Next(10000);          var path = Server.MapPath("~/xls_down/" + name + ".xls");          var dt = new System.Data.DataTable();          var Columns=Enumerable.Range(1, 10).Select(d => new DataColumn("a"+d.ToString(), typeof(string))).ToArray();          dt.Columns.AddRange(Columns);          for (int i = 0; i < 33333; i++)          {              var id = Guid.NewGuid().ToString();              dt.Rows.Add(id, id, id, id, id, id, id, id, id, id);          }          ExcelHelper.x2003.TableToExcelForXLS(dt, path);          downloadfile(path);      }      protected void btn_import_07_click(object o, EventArgs e)      {          var name = DateTime.Now.ToString("yyyyMMddhhmmss") + new Random(DateTime.Now.Second).Next(10000);          var path = Server.MapPath("~/xls_down/" + name + ".xlsx");          var dt = new System.Data.DataTable();          var Columns = Enumerable.Range(1, 10).Select(d => new DataColumn("a" + d.ToString(), typeof(string))).ToArray();          dt.Columns.AddRange(Columns);          for (int i = 0; i < 33333; i++)          {              var id = Guid.NewGuid().ToString();              dt.Rows.Add(id, id, id, id, id, id, id, id, id, id);          }          ExcelHelper.x2007.TableToExcelForXLSX(dt, path);          downloadfile(path);      }      void downloadfile(string s_path)      {          System.IO.FileInfo file = new System.IO.FileInfo(s_path);          HttpContext.Current.Response.ContentType = "application/ms-download";          HttpContext.Current.Response.Clear();          HttpContext.Current.Response.AddHeader("Content-Type", "application/octet-stream");          HttpContext.Current.Response.Charset = "utf-8";          HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(file.Name, System.Text.Encoding.UTF8));          HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());          HttpContext.Current.Response.WriteFile(file.FullName);          HttpContext.Current.Response.Flush();          HttpContext.Current.Response.Clear();          HttpContext.Current.Response.End();      }  }