C#使用NPOI操作Excel

最新推荐文章于 2023-10-15 13:52:52 发布

lddqq

最新推荐文章于 2023-10-15 13:52:52 发布

阅读量315

点赞数

分类专栏： C# 文章标签： excel poi c#

本文链接：https://blog.csdn.net/qq_16987587/article/details/115371148

版权

C# 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

读取Excel

1.NuGet 安装NPOI

2.using NPOI

3.读取Excel代码

 public static Void ReadExcel(string path){
IWorkbook wk;
FileStream fs;
try
 {
 using (fs = File.OpenRead(path)){
 wk = WorkbookFactory.Create(fs); //使用接口，自动识别excel2003/2007格式
  ISheet sheet = wk.GetSheetAt(0);   //读取当前表数据
   for (int i = 0; i <= sheet.LastRowNum; i++){
     IRow row = sheet.GetRow(i);  //读取当前行数据
                        if (row == null)
                        {
                            continue;
                        }
   ICell cell = row.GetCell(0); //读单元格的数据
    string str1 = GetCellStr(cell); //公式也能读取出来
}

}

}
catch (Exception ex)
     {

         throw ex;
     }

}
  public static string GetCellStr(ICell cell)
        {

            string str = string.Empty;
            if (cell == null)
            {
                return str;
            }
            if (cell.CellType == CellType.Formula)
            {
                cell.SetCellType(CellType.String);
                str = cell.StringCellValue.ToString();

            }
            else
            {
                str = cell.ToString();
            }
            return str;
        }

4.NPOI写入Excel文件

 public static void ExportExcel( string savepath) 
{
XSSFWorkbook workbook = new XSSFWorkbook();//创建Workbook对象
 ISheet sheet = workbook.CreateSheet("sheet1");//创建工作表
 sheet.DefaultRowHeight = 20 * 20;

#region 字体
            IFont font1 = workbook.CreateFont();
            font1.IsBold = true;
            font1.FontName = "宋体";
            font1.FontHeightInPoints = 16;

            IFont font2 = workbook.CreateFont();
            font2.IsBold = true;
            font2.FontName = "宋体";
            font2.FontHeightInPoints = 13;
            #endregion

 #region cellStyle
            ICellStyle style1 = workbook.CreateCellStyle();//声明style1对象，设置Excel表格的样式
            style1.Alignment = HorizontalAlignment.Center;
            style1.SetFont(font1);

            ICellStyle style2 = workbook.CreateCellStyle();
            style2.Alignment = HorizontalAlignment.Center;
            style2.SetFont(font2);

            ICellStyle style3 = workbook.CreateCellStyle();
            style3.Alignment = HorizontalAlignment.Center;
            style3.VerticalAlignment = VerticalAlignment.Center;
            #endregion

 //设置列宽
 sheet.SetColumnWidth(4, 100 * 20 * 2);
            sheet.SetColumnWidth(5, 100 * 20 * 2);
            sheet.SetColumnWidth(6, 100 * 20 * 2);
            sheet.SetColumnWidth(8, 100 * 20 * 2);
            sheet.SetColumnWidth(9, 100 * 20 * 2);

sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10)); //第一行合并
            IRow row = sheet.CreateRow(0); //创建row 行
            ICell cel = row.CreateCell(0); //创建列
            cel.SetCellValue("标题");
            cel.CellStyle = style1; //设置样式
 

 IRow row1 = sheet.CreateRow(1); //创建第二行
            ICell cel1 = row1.CreateCell(0); //创建第二行第一列
            cel1.SetCellValue("NO.");//写入值
            cel1.CellStyle = style2; //设置样式

            ICell cel2 = row1.CreateCell(1);
            cel2.SetCellValue("名称");
            cel2.CellStyle = style2;

       IRow rown = sheet.CreateRow(2);//创建第三行
     ICell celn1 = rown.CreateCell(0); //创建第三行第一列
      celn1.SetCellValue("值");

     using (FileStream fs = new FileStream(savepath, FileMode.OpenOrCreate, FileAccess.Write))
            {
                workbook.Write(fs);
                fs.Close();
                workbook.Close();
            }

}