操作Excel文件之NPOI

# 读取
NPOI、NPOI.OOXML、NPOI.OpenXml4Net、NPOI.OpenXmlFormats
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.IO;
```java

//实例化一个datatable用来存储数据
  DataTable dt = new DataTable();

//指定excel所在路径
  string excelPath = "d:\\1.xlsx";

//以文件流读取excel文件
  FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read);
  //Offic2007以上
  IWorkbook workbook = new XSSFWorkbook(fs);
  //Offic2007以下
  if (excelPath.ToLower().EndsWith(".xls"))
  {
      workbook = new HSSFWorkbook(fs);
  }

//取第一个sheet页的数据
  ISheet sheet = workbook.GetSheetAt(0);

//获取第一行数据
  IRow firstRow = sheet.GetRow(0);
  //获取第一行中数据列的数量
  int columnCount = firstRow.LastCellNum;
  for (int i = firstRow.FirstCellNum; i <= columnCount; i++)
  {
      //获取单元格
      ICell cell = firstRow.GetCell(i);
      if (cell != null)
      {
          //将第一行数据作为表头
          string cellValue = cell.StringCellValue;
          if (!string.IsNullOrEmpty(cellValue))
          {
              dt.Columns.Add(cellValue);
          }
      }
  }
  //获取数据行的数量
  int rowCount = sheet.LastRowNum;
  for (int i = 1; i <= rowCount; i++)
  {
      //获取行
      IRow row = sheet.GetRow(i);
      if (row == null) continue;
      DataRow dataRow = dt.NewRow();
      for (int j = row.FirstCellNum; j <= dt.Columns.Count; j++)
      {
          if (row.GetCell(j) != null)
          {
              //这里减FirstCellNum可以去除空白列数据
              dataRow[j - row.FirstCellNum] = row.GetCell(j).ToString();
          }

}
      dt.Rows.Add(dataRow);
  }
  //资源释放
  workbook.Close();
  //显示到页面
  dataGridView1.DataSource = dt;
```

# 写入
```java
DataTable dt = dataGridView1.DataSource as DataTable;

//指定excel所在路径
   string excelPath = "d:\\2.xlsx";

//以文件流创建excel文件
   FileStream fs = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
   //Offic2007以上
   IWorkbook workbook = new XSSFWorkbook();
   //Offic2007以下
   if (excelPath.ToLower().EndsWith(".xls"))
   {
       workbook = new HSSFWorkbook();
   }
   //创建一个sheet页
   ISheet sheet = workbook.CreateSheet("sheet1");
   
   //创建第一行数据并将表头写进去
   IRow rowHead = sheet.CreateRow(0);
   for (int j = 0; j < dt.Columns.Count; j++)
   {
       rowHead.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName);

}
  
   //将数据逐行写入
   for (int i = 0; i < dt.Rows.Count; i++)
   {
       IRow row = sheet.CreateRow(i + 1);
       for (int j = 0; j < dt.Columns.Count; j++)
       {
           row.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
       }
   }
   workbook.Write(fs);
   //资源释放
   workbook.Close();
```

# 样式
```java

DataTable dt = dataGridView1.DataSource as DataTable;

//指定excel所在路径
   string excelPath = "d:\\2.xlsx";

//以文件流创建excel文件
   FileStream fs = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
   //Offic2007以上
   IWorkbook workbook = new XSSFWorkbook();
   //Offic2007以下
   if (excelPath.ToLower().EndsWith(".xls"))
   {
       workbook = new HSSFWorkbook();
   }
   //创建一个sheet页
   ISheet sheet = workbook.CreateSheet("sheet1");

//创建第一行数据并将表头写进去
   IRow rowHead = sheet.CreateRow(0);
   for (int j = 0; j < dt.Columns.Count; j++)
   {
       rowHead.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName);
   }

//将数据逐行写入
   for (int i = 0; i < dt.Rows.Count; i++)
   {
       IRow row = sheet.CreateRow(i + 1);
       for (int j = 0; j < dt.Columns.Count; j++)
       {
           row.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
       }
   }

//创建样式
   ICellStyle style = workbook.CreateCellStyle();
   //设置单元格居中
   style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
   style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
   //新建一个字体样式对象
   IFont font = workbook.CreateFont();
   //设置字体大小
   font.FontHeightInPoints = 14;
   //设置字体加粗
   font.IsBold = true;
   //设置字体颜色
   font.Color = HSSFColor.Red.Index;
   style.SetFont(font);

//设置行的高度
   rowHead.Height = 800;
   //设置列的宽度
   sheet.SetColumnWidth(0, 5000);

//设置单元格样式
   rowHead.GetCell(2).CellStyle = style;

//设置单元格合并
   //CellRangeAddress参数分别为：起始行，结束行，起始列，结束列
   //索引从0开始
   sheet.AddMergedRegion(new CellRangeAddress(4, 5, 0, 2));
  
   //设置单元格公式
   ICell cell2 = sheet.CreateRow(dt.Rows.Count + 1).CreateCell(2);
   cell2.CellFormula = "C2+C3";

/*以下代码可判断单元格格式是否为公式
   if (rowHead.GetCell(1).CellType == CellType.Formula)
   {
       //判断单元格值是什么类型
       if (HSSFDateUtil.IsCellDateFormatted(rowHead.GetCell(1)))
       {
       }
   }
   */
   //读取图片转为byte
   Image img = Image.FromFile("d:\\c.png");
   MemoryStream ms = new MemoryStream();
   img.Save(ms, ImageFormat.Png);
   byte[] b = ms.ToArray();
   int picIndex=workbook.AddPicture(b, PictureType.PNG);

IDrawing patriarch = sheet.CreateDrawingPatriarch();
   //在第9行第5列插入大小为 高8行宽6列的图片（索引从0开始）
   XSSFClientAnchor anchor = new XSSFClientAnchor
   {
       Row1 = 8,
       Row2 = 16,
       Col1 = 4,
       Col2 = 10
   };
   patriarch.CreatePicture(anchor, picIndex);

workbook.Write(fs);
   fs.Close(); workbook.Close();
```

![](/media/202409/2024-09-30_114625_3038910.09183903661596782.png)