C#NPOI
编辑器:Visual Studio
语言:C#
一、NPOI的引用
1.根据Visual Studio版本安装NuGet.Tools
网址:https://www.nuget.org/downloads
2.工具→NuGet包管理器→程序包管理器控制台
3.输入"Install-Package NPOI",回车
4.此时会发现项目下的“引用”多了NPOI相关引用
二、操作Excel
1.创建新的Excel工作簿
IWorkbook workbook = new XSSFWorkbook();
ISheet worksheet = workbook.CreateSheet("工作表名称");
说明:
XSSFWorkbook操作扩展名为“.xlsx”的工作簿;
HSSFWorkbook操作扩展名为“.xls”的工作簿;
可使用IWorkbook统一定义;
如果需要多次创建workbook、worksheet等变量,可定义为公共变量,不用多次定义。
2.读取现有的Excel工作簿
获取Excel工作簿
FileStream fs;
fs = File.Open(path);//path:读取文件的路径
if (Path.GetExtension(strPath) == ".xls")
{ workbook = new HSSFWorkbook(fs); }
else { workbook = new XSSFWorkbook(fs); }
获取sheet工作表
worksheet = workbook.GetSheetAt(0);//0即为工作簿中的第一张工作表
3.操作sheet工作表
在新工作表中插入值,首先需要创建第一行,然后创建单元格并赋值
IRow row = worksheet.CreatRow(0);//创建行
row.CreateCell(0).SetCellValue("行1列1的单元格的值");
//第二行代码和下面两行代码效果相同
//ICell cell = row.CreateCell(0);
//cell.SetCellValue("行1列1的单元格的值");
获取工作表最后一行
row = worksheet.GetRow(worksheet.LastRowNum);
//worksheet.LastRowNum指最后一行的索引
获取行的最后一个单元格
ICell cell = row.GetCell(row.LastCellNum);
//row.LastCellNum指此行最后一个单元格的索引
设置单元格格式:居中(可以此推导其他属性使用)
ICellStyle cellstyle = workbook.CreateCellStyle();
//水平居中
cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//垂直居中
cellstyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellstyle.WrapText = true;
//把定义好的cellstyle赋值给单元格
row.GetCell(0).CellStyle = cellstyle;
设置列宽:worksheet.SetColumnWidth的第一个参数是要设置的列索引,第二个参数是要设置的宽度(若设置为24个字符宽度,则需256*24;若设置为1个字符宽度,则需256*1)
for (int i = 0; i < num; i++)
{ worksheet.SetColumnWidth(i, 256 * 24); }
4.操作workbook工作簿
若sheet工作表个数不为1,但是想要只保留第一个工作表
if (workbook.NumberOfSheets != 1)
{
for (int i = workbook.NumberOfSheets - 1; i > 0; i--)
{ workbook.RemoveSheetAt(i); }
}
修改sheet工作表名字
workbook.SetSheetName(a, "名字");//a:sheet索引
5.跨workbook工作簿复制sheet工作表
//复制合并的单元格
public static void MergerRegion(ISheet fromSheet, ISheet toSheet)
{
int sheetMergerCount = fromSheet.NumMergedRegions;
for (int i = 0; i < sheetMergerCount; i++)
{ toSheet.AddMergedRegion(fromSheet.GetMergedRegion(i)); }
}
//复制sheet
public static void MyCopySheet(IWorkbook fromWorkbook, IWorkbook toWorkbook, ISheet fromSheet, ISheet toSheet)
{
MergerRegion(fromSheet, toSheet);//合并的单元格
IRow fromRow, toRow;
ICellStyle fromCellStyle, toCellStyle;
CellType fromCellType;
int Column_Num = -1;
for (int i = 0; i <= fromSheet.LastRowNum; i++)
{
if (fromSheet.GetRow(i) != null)
{
fromRow = fromSheet.GetRow(i);
toRow = toSheet.CreateRow(i);
for (int j = 0; j <= fromRow.LastCellNum; j++)
{
if (fromRow.GetCell(j) != null)
{
#region 内容
fromCellType = fromRow.GetCell(j).CellType;
toRow.CreateCell(j).SetCellType(fromRow.GetCell(j).CellType);
if (fromCellType == CellType.Numeric)
{ toRow.GetCell(j).SetCellValue(fromRow.GetCell(j).NumericCellValue); }
switch (fromCellType)
{
case CellType.Boolean: toRow.GetCell(j).SetCellValue(fromRow.GetCell(j).BooleanCellValue); break;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(fromRow.GetCell(j)))
{ toRow.GetCell(j).SetCellValue(fromRow.GetCell(j).DateCellValue); }
else { toRow.GetCell(j).SetCellValue(fromRow.GetCell(j).NumericCellValue); }
break;
case CellType.String: toRow.GetCell(j).SetCellValue(fromRow.GetCell(j).RichStringCellValue); break;
case CellType.Formula: toRow.GetCell(j).SetCellValue(fromRow.GetCell(j).CellFormula); break;
case CellType.Error: toRow.GetCell(j).SetCellValue(fromRow.GetCell(j).ErrorCellValue); break;
}
#endregion
#region 复制单元格格式
fromCellStyle = fromRow.GetCell(j).CellStyle;
toCellStyle = toWorkbook.CreateCellStyle();
if (fromCellStyle != null)
{
toCellStyle.CloneStyleFrom(fromCellStyle);
toRow.GetCell(j).CellStyle = toCellStyle;
}
#endregion
}
}
if (fromRow.LastCellNum > Column_Num) { Column_Num = fromRow.LastCellNum; }
}
}
for (int i = 0; i < Column_Num; i++)
{
toSheet.SetColumnWidth(i, fromSheet.GetColumnWidth(i));//列宽
}
}