C#操作Excel有多种方法，如通过数据库的方式来读写Excel的OleDb方式，但是OleDb方式需要安装微软office，还可以通过COM组件方式操作Excel，也需要安装微软Excel。如果不想安装微软办公套餐可以使用ClosedXML、EPPlus、NPOI。本文主要是介绍NPOI的常用使用方法。
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
NuGet安装NPOI
NPO依赖beta版本的SixLabors.Fonts，需要先安装SixLabors.Fonts。
然后安装NPOI：
主要对象
对象 | 对象说明 |
HSSFWorkbook | excel文档对象 工作簿 .xls文件 |
XSSFWorkbook | excel文档对象 工作簿 .xlsx文件 |
HSSFSheet | excel的sheet 工作表 |
HSSFRow | excel的行 |
HSSFName | 名称 |
HSSFDateFomat | 日期格式 |
HSSFHeader | sheet头 |
HSSFFooter | sheet尾 |
HSSFCellStyle | cell样式 |
HSSFDateUtil | 日期 |
HSSFPrintSetup | 打印 |
HSSFErrorConstants | 错误信息表 |
HSSF是Horrible SpreadSheet Format的缩写，通过HSSF，你可以使用纯Java代码来读取、写入、修改Microsoft Excel BIFF(Excel 97-2003, xls)文件。HSSF为读取操作提供了两类API：usermodel和eventusermodel，即"用户模型"和"用户事件模型"。
读写Excel
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace WorkbookFactoryDemo
{
class Program
{
static void Main(string[] args)
{
using (var stream= File.OpenRead("test.xlsx"))
{
IWorkbook workbook = WorkbookFactory.Create(stream);
//workbook.GetSheetAt(0).CopySheet("Folha1");
/*for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
Console.WriteLine(sheet.SheetName);
}*/
using (FileStream fileWriter = new FileStream("TestOutput.xlsx", FileMode.Create, FileAccess.ReadWrite, FileShare.None))
{
workbook.Write(fileWriter, false);
}
Console.ReadLine();
}
}
}
}遍历Excel所有单元格
using System;
using NPOI.SS.UserModel;
namespace ReadAndPrintData
{
class Program
{
static void Main(string[] args)
{
Console.OutputEncoding = System.Text.Encoding.UTF8;
using (var workbook = WorkbookFactory.Create("data.xlsx"))
{
ISheet sheet = workbook.GetSheetAt(0);
foreach (IRow row in sheet)
{
for (var i = 0; i < row.LastCellNum; i++)
{
var cell = row.GetCell(i);
if (cell != null)
{
Console.Write(cell.ToString());
Console.Write("\t");
}
}
Console.WriteLine();
}
}
Console.Read();
}
}
}设置文件属性
using NPOI;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.IO;
namespace CreateCustomProperties
{
class Program
{
static void Main(string[] args)
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
POIXMLProperties props = workbook.GetProperties();
props.CoreProperties.Creator = "NPOI 2.5.1";
props.CoreProperties.Created = DateTime.Now;
if (!props.CustomProperties.Contains("NPOI Team"))
props.CustomProperties.AddProperty("NPOI Team", "Hello World!");
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
}
}设置单元格值：SetCellValue
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
namespace NPOI.Examples.XSSF.SetCellValuesInXlsx
{
class Program
{
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
int x = 1;
for (int i = 1; i <= 15; i++)
{
IRow row = sheet1.CreateRow(i);
for (int j = 0; j < 15; j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
}
}填充日期
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using (IWorkbook workbook = new XSSFWorkbook())
{
ISheet sheet = workbook.CreateSheet("Sheet1");
//increase the width of Column A
sheet.SetColumnWidth(0, 5000);
//create the format instance
IDataFormat format = workbook.CreateDataFormat();
//Chinese date string
ICell cell7 = sheet.CreateRow(6).CreateCell(0);
SetValueAndFormat(workbook, cell7, new DateOnly(2004, 5, 6), format.GetFormat("yyyy年m月d日"));
using (FileStream sw = File.Create("test.xlsx"))
{
workbook.Write(sw, false);
}
}
static void SetValueAndFormat(IWorkbook workbook, ICell cell, DateOnly value, short formatId)
{
//set value for the cell
cell.SetCellValue(value);
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = formatId;
cell.CellStyle = cellStyle;
}设置背景色
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
namespace NPOI.Examples.XSSF.FillBackgroundInXlsx
{
class Program
{
static void Main(string[] args)
{
using (IWorkbook workbook = new XSSFWorkbook())
{
ISheet sheet1 = workbook.CreateSheet("Sheet1");
//fill background
ICellStyle style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = IndexedColors.Blue.Index;
style1.FillPattern = FillPattern.BigSpots;
style1.FillBackgroundColor = IndexedColors.Pink.Index;
sheet1.CreateRow(0).CreateCell(0).CellStyle = style1;
//fill background
ICellStyle style2 = workbook.CreateCellStyle();
style2.FillForegroundColor = IndexedColors.Yellow.Index;
style2.FillPattern = FillPattern.AltBars;
style2.FillBackgroundColor = IndexedColors.Rose.Index;
sheet1.CreateRow(1).CreateCell(0).CellStyle = style2;
//fill background
ICellStyle style3 = workbook.CreateCellStyle();
style3.FillForegroundColor = IndexedColors.Lime.Index;
style3.FillPattern = FillPattern.LessDots;
style3.FillBackgroundColor = IndexedColors.LightGreen.Index;
sheet1.CreateRow(2).CreateCell(0).CellStyle = style3;
//fill background
ICellStyle style4 = workbook.CreateCellStyle();
style4.FillForegroundColor = IndexedColors.Yellow.Index;
style4.FillPattern = FillPattern.LeastDots;
style4.FillBackgroundColor = IndexedColors.Rose.Index;
sheet1.CreateRow(3).CreateCell(0).CellStyle = style4;
//fill background
ICellStyle style5 = workbook.CreateCellStyle();
style5.FillForegroundColor = IndexedColors.LightBlue.Index;
style5.FillPattern = FillPattern.Bricks;
style5.FillBackgroundColor = IndexedColors.Plum.Index;
sheet1.CreateRow(4).CreateCell(0).CellStyle = style5;
//fill background
ICellStyle style6 = workbook.CreateCellStyle();
style6.FillForegroundColor = IndexedColors.SeaGreen.Index;
style6.FillPattern = FillPattern.FineDots;
style6.FillBackgroundColor = IndexedColors.White.Index;
sheet1.CreateRow(5).CreateCell(0).CellStyle = style6;
//fill background
ICellStyle style7 = workbook.CreateCellStyle();
style7.FillForegroundColor = IndexedColors.Orange.Index;
style7.FillPattern = FillPattern.Diamonds;
style7.FillBackgroundColor = IndexedColors.Orchid.Index;
sheet1.CreateRow(6).CreateCell(0).CellStyle = style7;
//fill background
ICellStyle style8 = workbook.CreateCellStyle();
style8.FillForegroundColor = IndexedColors.White.Index;
style8.FillPattern = FillPattern.Squares;
style8.FillBackgroundColor = IndexedColors.Red.Index;
sheet1.CreateRow(7).CreateCell(0).CellStyle = style8;
//fill background
ICellStyle style9 = workbook.CreateCellStyle();
style9.FillForegroundColor = IndexedColors.RoyalBlue.Index;
style9.FillPattern = FillPattern.SparseDots;
style9.FillBackgroundColor = IndexedColors.Yellow.Index;
sheet1.CreateRow(8).CreateCell(0).CellStyle = style9;
//fill background
ICellStyle style10 = workbook.CreateCellStyle();
style10.FillForegroundColor = IndexedColors.RoyalBlue.Index;
style10.FillPattern = FillPattern.ThinBackwardDiagonals;
style10.FillBackgroundColor = IndexedColors.Yellow.Index;
sheet1.CreateRow(9).CreateCell(0).CellStyle = style10;
//fill background
ICellStyle style11 = workbook.CreateCellStyle();
style11.FillForegroundColor = IndexedColors.RoyalBlue.Index;
style11.FillPattern = FillPattern.ThickForwardDiagonals;
style11.FillBackgroundColor = IndexedColors.Yellow.Index;
sheet1.CreateRow(10).CreateCell(0).CellStyle = style11;
//fill background
ICellStyle style12 = workbook.CreateCellStyle();
style12.FillForegroundColor = IndexedColors.RoyalBlue.Index;
style12.FillPattern = FillPattern.ThickHorizontalBands;
style12.FillBackgroundColor = IndexedColors.Yellow.Index;
sheet1.CreateRow(11).CreateCell(0).CellStyle = style12;
//fill background
ICellStyle style13 = workbook.CreateCellStyle();
style13.FillForegroundColor = IndexedColors.RoyalBlue.Index;
style13.FillPattern = FillPattern.ThickVerticalBands;
style13.FillBackgroundColor = IndexedColors.Yellow.Index;
sheet1.CreateRow(12).CreateCell(0).CellStyle = style13;
//fill background
ICellStyle style14 = workbook.CreateCellStyle();
style14.FillForegroundColor = IndexedColors.RoyalBlue.Index;
style14.FillPattern = FillPattern.ThickBackwardDiagonals;
style14.FillBackgroundColor = IndexedColors.Yellow.Index;
sheet1.CreateRow(13).CreateCell(0).CellStyle = style14;
//fill background
ICellStyle style15 = workbook.CreateCellStyle();
style15.FillForegroundColor = IndexedColors.RoyalBlue.Index;
style15.FillPattern = FillPattern.ThinForwardDiagonals;
style15.FillBackgroundColor = IndexedColors.Yellow.Index;
sheet1.CreateRow(14).CreateCell(0).CellStyle = style15;
//fill background
ICellStyle style16 = workbook.CreateCellStyle();
style16.FillForegroundColor = IndexedColors.RoyalBlue.Index;
style16.FillPattern = FillPattern.ThinHorizontalBands;
style16.FillBackgroundColor = IndexedColors.Yellow.Index;
sheet1.CreateRow(15).CreateCell(0).CellStyle = style16;
//fill background
ICellStyle style17 = workbook.CreateCellStyle();
style17.FillForegroundColor = IndexedColors.RoyalBlue.Index;
style17.FillPattern = FillPattern.ThinVerticalBands;
style17.FillBackgroundColor = IndexedColors.Yellow.Index;
sheet1.CreateRow(16).CreateCell(0).CellStyle = style17;
//fill background with gradient color, this only works with NPOI 2.6.0
XSSFCellStyle style18 = (XSSFCellStyle)workbook.CreateCellStyle();
style18.FillPattern = FillPattern.SolidForeground;
var fillidx = (int)style18.GetCoreXf().fillId;
var ctfill = ((XSSFWorkbook)workbook).GetStylesSource().GetFillAt(fillidx).GetCTFill();
ctfill.UnsetPatternFill();
byte[] rgb1 = new byte[3];
rgb1[0] = (byte)0; // red
rgb1[1] = (byte)0; // green
rgb1[2] = (byte)255; // blue
byte[] rgb2 = new byte[3];
rgb2[0] = (byte)255; // red
rgb2[1] = (byte)255; // green
rgb2[2] = (byte)255; // blue
ctfill.gradientFill = new OpenXmlFormats.Spreadsheet.CT_GradientFill();
var ctgradientfill = ctfill.gradientFill;
ctgradientfill.degree = 90;
ctgradientfill.AddNewStop().position = 0;
ctgradientfill.GetStopArray(0).AddNewColor().SetRgb(rgb1);
ctgradientfill.AddNewStop().position = 0.5;
ctgradientfill.GetStopArray(1).AddNewColor().SetRgb(rgb2);
ctgradientfill.AddNewStop().position = 1.0;
ctgradientfill.GetStopArray(2).AddNewColor().SetRgb(rgb1);
sheet1.CreateRow(16).CreateCell(0).CellStyle = style18;
using (FileStream sw = File.Create("test.xlsx"))
{
workbook.Write(sw, false);
}
}
}
}
}设置宽高
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
namespace NPOI.Examples.XSSF.SetWidthAndHeightInXlsx
{
class Program
{
static void Main(string[] args)
{
using (IWorkbook workbook = new XSSFWorkbook())
{
ISheet sheet1 = workbook.CreateSheet("Sheet1");
//set the width of columns
sheet1.SetColumnWidth(0, 50 * 256);
sheet1.SetColumnWidth(1, 100 * 256);
sheet1.SetColumnWidth(2, 150 * 256);
//set the width of height
sheet1.CreateRow(0).Height = 100 * 20;
sheet1.CreateRow(1).Height = 200 * 20;
sheet1.CreateRow(2).Height = 300 * 20;
using (FileStream sw = File.Create("test.xlsx"))
{
workbook.Write(sw, false);
}
}
}
}
}折线图
using NPOI.SS.UserModel;
using NPOI.SS.UserModel.Charts;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.IO;
namespace LineChart
{
class Program
{
const int NUM_OF_ROWS = 3;
const int NUM_OF_COLUMNS = 10;
static void CreateChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor, string serie1, string serie2, bool enableMajorGridline=false)
{
XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor);
chart.SetTitle("Test 1");
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.TopRight;
ILineChartData<double, double> data = chart.ChartDataFactory.CreateLineChartData<double, double>();
// Use a category axis for the bottom axis.
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
leftAxis.Crosses = AxisCrosses.AutoZero;
IChartDataSource<double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
IChartDataSource<double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
IChartDataSource<double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
var s1 = data.AddSeries(xs, ys1);
s1.SetTitle(serie1);
var s2 = data.AddSeries(xs, ys2);
s2.SetTitle(serie2);
chart.Plot(data, bottomAxis, leftAxis);
//add major gridline, available since NPOI 2.5.5
var plotArea = chart.GetCTChart().plotArea;
plotArea.catAx[0].AddNewMajorGridlines();
plotArea.valAx[0].AddNewMajorGridlines();
}
static void Main(string[] args)
{
using (IWorkbook wb = new XSSFWorkbook())
{
ISheet sheet = wb.CreateSheet("linechart");
// Create a row and put some cells in it. Rows are 0 based.
IRow row;
ICell cell;
for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++)
{
row = sheet.CreateRow((short)rowIndex);
for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++)
{
cell = row.CreateCell((short)colIndex);
cell.SetCellValue(colIndex * (rowIndex + 1));
}
}
IDrawing drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor1 = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15);
CreateChart(drawing, sheet, anchor1, "title1", "title2");
IClientAnchor anchor2 = drawing.CreateAnchor(0, 0, 0, 0, 0, 20, 10, 35);
CreateChart(drawing, sheet, anchor2, "s1", "s2", true);
using (FileStream fs = File.Create("test.xlsx"))
{
wb.Write(fs, false);
}
}
}
}
}条形图
using NPOI.SS.UserModel;
using NPOI.SS.UserModel.Charts;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.IO;
namespace BarChart
{
class Program
{
const int NUM_OF_ROWS = 10;
const int NUM_OF_COLUMNS = 2;
private static void CreateChart(ISheet sheet, IDrawing drawing, IClientAnchor anchor, string serieTitle, int startDataRow, int endDataRow, int columnIndex)
{
XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor);
IBarChartData<string, double> barChartData = chart.ChartDataFactory.CreateBarChartData<string, double>();
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.Bottom;
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
bottomAxis.MajorTickMark = AxisTickMark.None;
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
leftAxis.Crosses = AxisCrosses.AutoZero;
leftAxis.SetCrossBetween(AxisCrossBetween.Between);
IChartDataSource<string> categoryAxis = DataSources.FromStringCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, 0, 0));
IChartDataSource<double> valueAxis = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, columnIndex, columnIndex));
var serie = barChartData.AddSeries(categoryAxis, valueAxis);
serie.SetTitle(serieTitle);
chart.Plot(barChartData, bottomAxis, leftAxis);
}
static void Main(string[] args)
{
using (IWorkbook wb = new XSSFWorkbook())
{
ISheet sheet = wb.CreateSheet();
// Create a row and put some cells in it. Rows are 0 based.
IRow row;
ICell cell;
for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++)
{
row = sheet.CreateRow((short)rowIndex);
for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++)
{
cell = row.CreateCell((short)colIndex);
if (colIndex == 0)
cell.SetCellValue("X" + rowIndex);
else
{
var x = colIndex * (rowIndex + 1);
cell.SetCellValue(x * x + 2 * x + 1);
}
}
}
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, 3, 3, 10, 12);
CreateChart(sheet, drawing, anchor, "s1", 0, 9, 1);
using (FileStream fs = File.Create("test.xlsx"))
{
wb.Write(fs, false);
}
}
}
}
}设置打印范围
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.IO;
namespace SetPrintArea
{
class Program
{
static IWorkbook workbook;
static void Main(string[] args)
{
InitializeWorkbook(args);
ISheet sheet = workbook.CreateSheet("Timesheet");
sheet.CreateRow(0).CreateCell(0).SetCellValue("Test");
workbook.SetPrintArea(0, "$A$1:$C$5");
//workbook.SetPrintArea(0, "$A$1:$C$5,$E$9:$I$16"); not working in xls
WriteToFile();
}
static void WriteToFile()
{
string filename = "timesheet.xls";
if (workbook is XSSFWorkbook) filename += "x";
//Write the stream data of workbook to the root directory
using (FileStream file = new FileStream(filename, FileMode.Create))
{
workbook.Write(file);
}
}
static void InitializeWorkbook(string[] args)
{
if (args.Length > 0 && args[0].Equals("-xls"))
workbook = new HSSFWorkbook();
else
workbook = new XSSFWorkbook();
}
}
}参考
https://github.com/nissl-lab/npoihttps://github.com/nissl-lab/npoi-examples
