C# NPOI操作Excel汇总-Toy模板网

这篇具有很好参考价值的文章主要介绍了C# NPOI操作Excel汇总。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

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。
C# NPOI操作Excel汇总,c#,excel
然后安装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/npoi
https://github.com/nissl-lab/npoi-examples
https://blog.csdn.net/weixin_44899642/article/details/124687499文章来源地址https://www.toymoban.com/news/detail-528038.html

到了这里，关于C# NPOI操作Excel汇总的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！