基于NPOI对Excel进行简单的操作

1. 关于NPOI

  NPOI是一个开源的用于在C#程序中读写Excel、WORD等微软OLE2组件文档的项目,使用NPOI可以在没有安装Office的情况下对Word或Excel文档进行读写操作。

2. 使用NPOI的好处

  1. C#程序中操作Excel的另外一种方法是使用.NET自带的Excel API,但是这种方法需要运行环境安装微软的Excel,而使用NPOI可以在没有安装Microsoft Office Excel的设备上对Excel进行操作。
  2. 因为服务器一般不安装office这么庞大的办公软件,所以NPOI适合在服务器端生成数据文件。
  3. 几乎支持所有的Office97~Office2007的文件格式。

3.  NPOI使用方法

  NPOI里面的管理层次为:workbook->worksheet->row->cell,类比关系型数据库就是:

NPOI

说明

关系型数据库

workbook

Excel文件

database

worksheet

工作表

table

row

record记录

cell

单元格

field字段

3.1 获取DLL

   方式1:用Nuget命令行安装NPOI引用

Install-Package NPOI -Version 2.4.0

   方式2:直接在VS的Nuget包管理器中搜索“NPOI”获取

3.2 将NPOI的DLL导入工程

using NPOI; //基础辅助库
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

   其中,NPOI 使用 HSSFWorkbook 类来处理 xls,XSSFWorkbook 类来处理 xlsx,它们都继承接口 IWorkbook,因此可以通过 IWorkbook 来统一处理 xls 和 xlsx 格式的文件。(本文以XLSX为例)

3.3 创建和读写Excel

  (1)  创建Excel文件

XSSFWorkbook workbook2007 = new XSSFWorkbook(); //创建xlsx工作簿
HSSFWorkbook workbook2003 = new HSSFWorkbook(); //新建xls工作簿

  Excel规定一个Workbook必须至少带1个Sheet,所以创建Excel之后必须同时创建Sheet的代码才能保证生成的文件正常。

ISheet sheet = workbook2007.CreateSheet("sheet1"); //创建一个Sheet

       创建行和列的方式:

IRow row = sheet.CreateRow(0); //创建一行
ICell cell = row.CreateCell(0); //创建一列

  创建XSSFWorkbook实例之后,要将其写入文件:

FileStreamfile =new FileStream(@"NpoiTest.xlsx", FileMode.Create);
workbook2007.Write(file);
file.Close();
workbook2007.Close();

   之后会在指定目录下生成Excel文件。

  (2)  向Excel文件写入数据

  写数据要遵循一定的顺序,可以概括为:读取(或新建一个工作簿)->获取工作表->对工作表添加行->对每一行添加单元格->对单元格赋值。

XSSFWorkbook workbook2007 = new XSSFWorkbook(); //创建xlsx工作簿
workbook2007.CreateSheet("Sheet1");  //新建1个Sheet工作表
//ISheet sheet = workbook2007.CreateSheet("Sheet1");
HSSFSheet SheetOne = (HSSFSheet)workbook2007.GetSheet("Sheet1"); //获取名称为Sheet1的工作表
//对工作表先添加行,下标从0开始
for (int i = 0; i < 2; i++)
{
    SheetOne.CreateRow(i); //为SheetOne添加2行
    //IRow row = SheetOne.CreateRow(i);
}
//对每一行创建3个单元格
HSSFRow SheetRow = (HSSFRow)SheetOne.GetRow(0);  //获取Sheet1工作表的首行
HSSFCell[] SheetCell = new HSSFCell[3];
for (int i = 0; i < 3; i++)
{
    SheetCell[i] = (HSSFCell)SheetRow.CreateCell(i);  //为第一行创建3个单元格
    //ICell headcell = SheetRow.CreateCell(i);
}
//创建之后就可以赋值了
SheetCell[0].SetCellValue("CellValue");        
SheetCell[1].SetCellValue("CellValue2");
SheetCell[2].SetCellValue("CellValue3");
FileStream file2007 = new FileStream(@"E:\Excel2007.xls", FileMode.Create);
workbook2007.Write(file2007);
file2007.Close();
workbook2007.Close();

//转化为字节数组
//MemoryStream ms = new MemoryStream();
//workbook.Write(ms);
//ms.Flush();
//ms.Position = 0;
//return ms; 

  (3)  读取Excel文件数据

  workbook的写入需要借助于FileStream来打开一个文件流,在创建FileStream的时候,可以传入数据的保存路径和文件名。

  HSSFWorkbook类和XSSFWorkbook类都继承自IWorkbook类,所以在不知道所要读取的Excel文件时xls还是xlsx时,可以使用IWorkbook来声明一个通用的工作簿变量,随后根据传入的文件名判断是xls还是xlsx。

IWorkbook workbook = null;  //新建IWorkbook对象
string fileName = "E:\\Excel2007.xls";
FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
    workbook = new XSSFWorkbook(fileStream);  //xlsx数据读入workbook
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
    workbook = new HSSFWorkbook(fileStream);  //xls数据读入workbook
}
ISheet sheet = workbook.GetSheetAt(0);  //获取第一个工作表
IRow row;// = sheet.GetRow(0); //新建当前工作表行数据
for (int i = 0; i < sheet.LastRowNum; i++)  //对工作表每一行
{
    row = sheet.GetRow(i);   //row读入第i行数据
    if (row != null)
    {
        for (int j = 0; j < row.LastCellNum; j++)  //对工作表每一列
        {
            string cellValue = row.GetCell(j).ToString(); //获取i行j列数据
            Console.WriteLine(cellValue);
        }
    }
}
Console.ReadLine();
fileStream.Close();
workbook.Close();

3.4 Excel样式设置

  (1)  设置字体

ICellStyle style = workbook.CreateCellStyle();//创建样式对象
IFont font = workbook.CreateFont(); //创建一个字体样式对象
font.FontName = "方正舒体"; //和excel里面的字体对应
font.Color = new HSSFColor.PIN.Index();//颜色参考NPOI的颜色对照表 (对照表见参考网址(样式设置相关))
font.IsItalic = true; //斜体(下划线?)
font.FontHeightInPoints = 16;//字体大小
font.Boldweight = short.MaxValue;//字体加粗
style.SetFont(font); //将字体样式赋给样式对象
cell.CellStyle = style; //把样式赋给单元格

   (2)  设置单元格前景色

ICellStyle style=workbook.CreateCellStyle();
style.FillForegroundColor = 14; //具体数字代表的颜色看NPOI颜色对照表
style.FillPattern = FillPatternType.SOLID_FOREGROUND;

cellStyle.FillForegroundColor = HSSFColor.Blue.Index;//选中后文字背景
cellStyle.FillBackgroundColor = HSSFColor.Red.Index;//选中后单元格背景
cellStyle.FillPattern = FillPattern.AltBars;

   (3)  设置单元格宽高

row.Height = 30 * 20;    //行高,行高为30
sheet.SetColumnWidth(3, 13 * 256)   //列宽,第4列的列宽为13

   (4)  合并单元格

  单元格合并后,样式以左上角的单元格为准。

sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
//CellRangeAddress四个参数为:起始行,结束行,起始列,结束列

   (5)  单元格水平居中和对齐

style.Alignment = HorizontalAlignment.CENTER;
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//水平对齐
style.VerticalAlignment = VerticalAlignment.Center;//垂直对齐

   (6)  设置公式

cell.CellFormula = "公式"; //公式中不需要写“=”号

   (7)  设置边框

  分别设置上下左右边框样式

style.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style.BorderRight = NPOI.SS.UserModel.BorderStyle.THICK;

  分别设置上下左右边框颜色

style.TopBorderColor = HSSFColor.OliveGreen.Blue.Index;
style.BottomBorderColor = HSSFColor.OliveGreen.Blue.Index;
style.LeftBorderColor = HSSFColor.OliveGreen.Blue.Index;
style.RightBorderColor = HSSFColor.OliveGreen.Blue.Index;

   (8)  其它

cellStyle.WrapText = true; //自动换行
cellStyle.Indention = 0;//缩进 

3.5 用类CSS的方法设置单元格样式

  NPOI.CSS是一个可以在使用NPOI时用类CSS的方法设置单元格样式的NPOI扩展,支持.NET4及以上版本的项目。

   (1)  引用NPOI.CSS.dll

using NPOI.CSS;

   (2)  设置单元格样式

cell.CSS("color:red;font-weight:bold;font-size:11;font-name:宋体;border-type:thin;");

  具体样式参数见参考网址(参考文档3)。

  (网上好像很难找到NPOI.CSS.dll的资源,需要的朋友可以直接在Github(参考文档3)找到源码,然后编译生成。)

 

参考文档:

  1.文档读写相关:

  https://blog.csdn.net/dcrmg/article/details/52356236

  2. 样式设置相关:

  https://www.cnblogs.com/nxxshxf/p/6410797.html

  https://blog.csdn.net/qq_35957944/article/details/85163316

  3. NPOI.CSS属性:

  https://github.com/qihangnet/NPOI.CSS

 


原文地址:https://www.cnblogs.com/imdeveloper/p/10963364.html
转载请注明出处,谢谢!


 

上一篇:Python读取Excel文件,较全面的总结,注意Excel文件有xlsx和xls之分


下一篇:python每日一练:0015题