新西兰服务器

C#如何使用NPOI对Excel数据进行导入导出


C#如何使用NPOI对Excel数据进行导入导出

发布时间:2022-06-08 14:15:51 来源:高防服务器网 阅读:90 作者:iii 栏目:开发技术

本篇内容介绍了“C#如何使用NPOI对Excel数据进行导入导出”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

一、概述

NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目, 使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。

1、操作Excel的类库:

  • NPOI: V2.5.1

  • MyXls: (已停止)

  • Aspose.Cell.dll: 收费

  • EPPlus 5

  • Spire.XLS: 收费

2、引用DLL

使用时需引用需要引用所有5个dll

  • ICSharpCode.SharpZipLib.dll

  • NPOI.dll

  • NPOI.OOXML.dll

  • NPOI.OpenXml4Net.dll

  • NPOI.OpenXmlFormats.dll

程序集构成

二、通过NPOI,将Excel文件导到数据表DataTable

DataTable dt = ImportToTable("00.xls");  if (dt != null)  {      Console.Write(dt.Rows.Count);      Console.ReadKey();  }    public static DataTable ImportToTable(string fileName)  {      DataTable dt = new DataTable();      IWorkbook workbook;      string fileExt = Path.GetExtension(fileName).ToLower();      using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))      {          //XSSFWorkbook 适用XLSX格式,HSSFWorkbook 适用XLS格式          if (fileExt == ".xlsx")          {              workbook = new XSSFWorkbook(fs);          }          else if (fileExt == ".xls")          {              workbook = new HSSFWorkbook(fs);          }          else          {              workbook = null;              return null;          }            ISheet sheet = workbook.GetSheetAt(0);//Sheet总数量:workbook.NumberOfSheets            //表头            IRow header = sheet.GetRow(sheet.FirstRowNum);          for (int i = 0; i < header.LastCellNum; i++)          {              object obj = GetValueType(header.GetCell(i));              if (obj == null || obj.ToString() == string.Empty)              {                  dt.Columns.Add(new DataColumn("Columns" + i.ToString()));              }              else                    dt.Columns.Add(new DataColumn(obj.ToString()));          }          //数据            for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)          {              DataRow dr = dt.NewRow();              bool hasValue = false;              IRow row = sheet.GetRow(i);              for (int j = row.FirstCellNum; j < row.LastCellNum; j++)              {                  dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));                  if (dr[j] != null && dr[j].ToString() != string.Empty)                  {                      hasValue = true;                  }              }              if (hasValue)              {                  dt.Rows.Add(dr);              }          }            return dt;      }    }  ///     /// 获取单元格类型  ///   ///   ///   ///   static object GetValueType(ICell 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;      }  }

四、常见用法:

1、查找

IEnumerator rows = sheet.GetEnumerator();  while (rows.MoveNext())  {      IRow row = (HSSFRow)rows.Current;      ICell cell = row.GetCell(0);      if (cell != null && cell.StringCellValue == "XX")      {          return row.GetCell(1).StringCellValue;      }  }

2、插入图片

IWorkbook workbook = new HSSFWorkbook();    //add picture data to this workbook.  byte[] bytes = System.IO.File.ReadAllBytes(@"00.jpg");  int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);    //create sheet  ISheet sheet = workbook.CreateSheet("Sheet1");    // Create the drawing patriarch.  This is the top level container for all shapes.   IDrawing patriarch = sheet.CreateDrawingPatriarch();    //add a picture  HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3);  IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);    //保存为Excel文件    using (FileStream fs = new FileStream("00_new.xls", FileMode.Create, FileAccess.Write))  {      workbook.Write(fs);  }

五、填充Excel模板

IWorkbook workbook;  using (FileStream fs = new FileStream("模板文件.xls", FileMode.Open, FileAccess.Read))  {      workbook = new HSSFWorkbook(fs);  }    ISheet cloneSheet = workbook.CloneSheet(workbook.GetSheetIndex("Sheet1"));//复制第一个模板Sheet  cloneSheet.ForceFormulaRecalculation = true;  workbook.SetSheetName(workbook.GetSheetIndex(cloneSheet), "SheetClone");//设置新SheetName    cloneSheet.GetRow(4).GetCell(1).SetCellValue("a");//为已经存在的单元格赋值    IRow row = cloneSheet.GetRow(15);  if (row == null)      row = cloneSheet.CreateRow(15);  ICell cell = row.GetCell(7);  if (cell == null)      cell = row.CreateCell(7);  cell.SetCellValue("XX");// 为不存在的单元格,先新建再赋值    cloneSheet.ShiftRows(51, 60, 34);//51-60行(尾部)整体移动34行,腾出更多控件插入多行数据  workbook.RemoveSheetAt(workbook.GetSheetIndex("Sheet1"));//移除原模板Sheet    FileStream fs_new = new FileStream(DateTime.Now.Ticks + ".xls", FileMode.Create);  workbook.Write(fs_new);  fs_new.Close();

六、DataTable导出到Excel文件

1、直接导出到Excel:

调用方式:

ExportToExcel(dt, "00_new.xls");

代码

public static void ExportToExcel(DataTable dt, string fileName)  {      IWorkbook workbook;      string fileExt = Path.GetExtension(fileName).ToLower();      //XSSFWorkbook 适用XLSX格式,HSSFWorkbook 适用XLS格式      if (fileExt == ".xlsx")      {          workbook = new XSSFWorkbook();      }      else if (fileExt == ".xls")      {          workbook = new HSSFWorkbook();      }      else      {          workbook = null;          return;      }        ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);        //表头        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());          }      }        //保存为Excel文件        using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))      {          workbook.Write(fs);      }  }

2、将DataTable导出到Excel:先导出到MemoryStream

public static MemoryStream ExportToExcel(DataTable dt, string HeaderText)  {      var workbook = new HSSFWorkbook();      ISheet sheet = workbook.CreateSheet(string.IsNullOrWhiteSpace(dt.TableName) ? "Sheet1" : dt.TableName);        //右击文件“属性”信息      #region 文件属性信息      {          var dsi = PropertySetFactory.CreateDocumentSummaryInformation();          dsi.Company = "NPOI";          workbook.DocumentSummaryInformation = dsi;            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();          si.Author = "文件作者信息";          si.ApplicationName = "创建程序信息";          si.LastAuthor = "最后保存者信息";          si.Comments = "作者信息";          si.Title = "标题信息";          si.Subject = "主题信息";          si.CreateDateTime = DateTime.Now;          workbook.SummaryInformation = si;      }      #endregion        //格式      var dateStyle = workbook.CreateCellStyle();      var format = workbook.CreateDataFormat();      dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");//日期格式        //取得列宽      var arrColWidth = new int[dt.Columns.Count];      foreach (DataColumn item in dt.Columns)      {          arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;      }      for (var i = 0; i < dt.Rows.Count; i++)      {          for (var j = 0; j < dt.Columns.Count; j++)          {              int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length;              if (intTemp > arrColWidth[j])              {                  arrColWidth[j] = intTemp;              }          }      }      int rowIndex = 0;      foreach (DataRow row in dt.Rows)      {          #region 表头 列头          if (rowIndex == 65535 || rowIndex == 0)          {              if (rowIndex != 0)              {                  sheet = workbook.CreateSheet();//超过65535行,则新建一个Sheet              }                #region 表头及样式              {                  var headerRow = sheet.CreateRow(0);                  headerRow.HeightInPoints = 25;                  headerRow.CreateCell(0).SetCellValue(HeaderText);                  //CellStyle                  ICellStyle headStyle = workbook.CreateCellStyle();                  headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;// 左右居中                      headStyle.VerticalAlignment = VerticalAlignment.Center;// 上下居中                                                                           // 设置单元格的背景颜色(单元格的样式会覆盖列或行的样式)                      headStyle.FillForegroundColor = (short)11;                  //定义font                  IFont font = workbook.CreateFont();                  font.FontHeightInPoints = 20;                  font.Boldweight = 700;                  headStyle.SetFont(font);                  headerRow.GetCell(0).CellStyle = headStyle;                  sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));//合并区域              }              #endregion                  #region 列头及样式              {                  var headerRow = sheet.CreateRow(1);                  //CellStyle                  ICellStyle headStyle = workbook.CreateCellStyle();                  headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;// 左右居中                      headStyle.VerticalAlignment = VerticalAlignment.Center;// 上下居中                                                                           //定义font                  IFont font = workbook.CreateFont();                  font.FontHeightInPoints = 10;                  font.Boldweight = 700;                  headStyle.SetFont(font);                    foreach (DataColumn column in dt.Columns)                  {                      headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);                      headerRow.GetCell(column.Ordinal).CellStyle = headStyle;                      sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);//设置列宽                  }              }              #endregion                rowIndex = 2;//数据行RowIndex为2(表头和列头个占一行)          }          #endregion              #region 内容          var dataRow = sheet.CreateRow(rowIndex);          foreach (DataColumn column in dt.Columns)          {              var newCell = dataRow.CreateCell(column.Ordinal);                string drValue = row[column].ToString();                switch (column.DataType.ToString())              {                  case "System.String"://字符串类型                      newCell.SetCellValue(drValue);                      break;                  case "System.DateTime"://日期类型                      DateTime dateV;                      DateTime.TryParse(drValue, out dateV);                      newCell.SetCellValue(dateV);                        newCell.CellStyle = dateStyle;//格式化显示                      break;                  case "System.Boolean"://布尔型                      bool boolV = false;                      bool.TryParse(drValue, out boolV);                      newCell.SetCellValue(boolV);                      break;                  case "System.Int16"://整型                  case "System.Int32":                  case "System.Int64":                  case "System.Byte":                      int intV = 0;                      int.TryParse(drValue, out intV);                      newCell.SetCellValue(intV);                      break;                  case "System.Decimal"://浮点型                  case "System.Double":                      double doubV = 0;                      double.TryParse(drValue, out doubV);                      newCell.SetCellValue(doubV);                      break;                  case "System.DBNull"://空值处理                      newCell.SetCellValue("");                      break;                  default:                      newCell.SetCellValue("");//设置单元格公式:newCell.SetCellFormula("SUM($B0:$D0)")                      break;              }            }          #endregion            rowIndex++;      }      //自动列宽      for (int i = 0; i <= dt.Columns.Count; i++)          sheet.AutoSizeColumn(i, true);        using (MemoryStream ms = new MemoryStream())      {          workbook.Write(ms);          ms.Flush();          ms.Position = 0;          return ms;      }  }

3、应用

1、Web导出
public static void ExportToExcelByWeb(DataTable dt, string HeaderText, string FileName)  {      HttpContext context = HttpContext.Current;      context.Response.ContentType = "application/vnd.ms-excel";      context.Response.ContentEncoding = Encoding.UTF8;      context.Response.Charset = "UTF-8";      context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(FileName, Encoding.UTF8)));      byte[] data = ExportToExcel(dt, HeaderText).GetBuffer();//Read()方法也可以      context.Response.BinaryWrite(data);//     或者: context.Response.OutputStream.Write(data,0,data.Length)      context.Response.End();  }
2、Winform导出
public static void ExportToExcel(DataTable dt, string HeaderText, string FileName)  {      using (MemoryStream ms = ExportToExcel(dt, HeaderText))      {          using (FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write))          {              byte[] data = ms.ToArray();//跟GetBuffer()对比,速度稍慢,但无空数据              fs.Write(data, 0, data.Length);              fs.Flush();          }      }  }

GridView导出到Excel

Web中的GridView可直接导出到Excel:renderControl()

“C#如何使用NPOI对Excel数据进行导入导出”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注高防服务器网网站,小编将为大家输出更多高质量的实用文章!

[微信提示:高防服务器能助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。

[图文来源于网络,不代表本站立场,如有侵权,请联系高防服务器网删除]
[