C# NPOI（xlsx相关操作）

1. NPOI

• NPOI：一个.NET库，用于读取和写入Microsoft Office二进制和OOXML文件格式。在这里我用他读写xlsx文件。
• NPOI采用的是Apache 2.0许可证（poi也是采用这个许可证），这意味着它可以被用于任何商业或非商业项目，你不用担心因为使用它而必须开放你自己的源代码，所以它对于很多从事业务系统开发的公司来说绝对是很不错的选择。

2. 资源下载和项目应用

• 在使用NPOI之前要先下载NPOI的库文件，下载地址在文末：
  
• 然后在项目中引用
  

3. 创建带表头的xlsx并写入数据

代码不复杂，但花了我一天的时间。

using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.IO;
using System.Windows.Forms;

namespace LearnNPOI
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


        #region 创建带表头的xlsx文件
        /// <summary>
        /// 创建带表头的xlsx文件
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <param name="xlsxname">文件名称</param>
        /// <param name="sheetnames">Sheet名称/param>
        /// <param name="headername">表头名称</param>
        /// <param name="sheetName">选择写表头的Sheet</param>
        /// <param name="workbook">workbook</param>
        /// <param name="sheet">sheet</param>
        public void CreateXlsx(string pathname, string[] sheetnames,
            string[] headername, string sheetName,out IWorkbook workbook, out ISheet[] sheet)
        {
            string name = pathname;
            //判断该目录下是否存在同名文件，如果存在则不再重新创建
            if (File.Exists(name))
            {
                FileStream file;
                file = new FileStream(name, FileMode.Open, FileAccess.Read);
                workbook = new XSSFWorkbook(file);

                int sheetnum = workbook.NumberOfSheets;
                sheet = new ISheet[sheetnum];
                for (int i = 0; i < sheetnum; i++)
                {
                    sheet[i] = workbook.GetSheet(workbook.GetSheetName(i));
                }
            }
            else
            {
                int num = sheetnames.Length;
                workbook = new XSSFWorkbook();
                sheet = new ISheet[num];
                for (int i = 0; i < num; i++)
                {
                    sheet[i] = workbook.CreateSheet(sheetnames[i]);
                }

                //创建字体
                IFont font = workbook.CreateFont();
                //font.IsItalic = true;//斜体字
                //font.Underline = (byte)FontUnderlineType.DOUBLE;//下划线
                font.Color = HSSFColor.BLACK.index;//颜色
                font.FontHeightInPoints = 13;//字号大小
                font.FontName = "宋体";//字体

                ICellStyle style = workbook.CreateCellStyle();
                style.SetFont(font);

                //创建新的一行
                ISheet sheet1 = workbook.GetSheet(sheetName);
                IRow row1 = sheet1.CreateRow(0);
                //编辑表头
                for (int i = 0; i < headername.Length; i++)
                {
                    sheet1.GetRow(0).CreateCell(i).SetCellValue(headername[i]);
                    sheet1.GetRow(0).GetCell(i).CellStyle = style;
                }

                FileStream sw = File.Create(name);
                workbook.Write(sw);
                sw.Close();
            }
    
            return;
        }
        #endregion

        #region 创建带表头的xlsx文件 缺省值
        /// <summary>
        /// 创建带表头的xlsx文件
        /// </summary>
        /// <param name="path">文件路径(包括文件名称)</param>
        /// <param name="headername">表头名称</param>
        /// <param name="workbook">workbook</param>
        /// <param name="sheet">sheet</param>
        public void CreateXlsxDefault(string pathname, string[] headername,  out IWorkbook workbook, out ISheet[] sheet)
        {
            string name = pathname;
            //判断该目录下是否存在同名文件，如果存在则不再重新创建
            if (File.Exists(name))
            {
                FileStream file;
                file = new FileStream(name, FileMode.Open, FileAccess.Read);
                workbook = new XSSFWorkbook(file);

                int sheetnum = workbook.NumberOfSheets;
                sheet = new ISheet[sheetnum];
                for (int i = 0; i < sheetnum; i++)
                {
                    sheet[i] = workbook.GetSheet(workbook.GetSheetName(i));
                }
            }
            else
            {
                
                workbook = new XSSFWorkbook();
                sheet = new ISheet[3];
                sheet[0] = workbook.CreateSheet("Sheet1");
                sheet[1] = workbook.CreateSheet("Sheet2");
                sheet[2] = workbook.CreateSheet("Sheet3");


                //创建字体
                IFont font = workbook.CreateFont();
                //font.IsItalic = true;//斜体字
                //font.Underline = (byte)FontUnderlineType.DOUBLE;//下划线
                font.Color = HSSFColor.BLACK.index;//颜色
                font.FontHeightInPoints = 13;//字号大小
                font.FontName = "宋体";//字体

                ICellStyle style = workbook.CreateCellStyle();
                style.SetFont(font);

                //创建新的一行
                ISheet sheet1 = workbook.GetSheet("Sheet1");
                IRow row1 = sheet1.CreateRow(0);
                //编辑表头
                for (int i = 0; i < headername.Length; i++)
                {
                    sheet1.GetRow(0).CreateCell(i).SetCellValue(headername[i]);
                    sheet1.GetRow(0).GetCell(i).CellStyle = style;
                }

                FileStream sw = File.Create(name);
                workbook.Write(sw);
                sw.Close();
            }

            return;
        }
        #endregion

        #region 写数据进xlsx
        /// <summary>
        /// 写数据进xlsx
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="path"></param>
        /// <param name="data"></param>
        public void WriteDataInXlsx(IWorkbook workbook, string path, double[] data)
        {
            //判断该目录下是否存在同名文件，如果存在则不再重新创建
            if (File.Exists(path))
            {
                FileStream file;
                file = new FileStream(path, FileMode.Open, FileAccess.Read);
                workbook = new XSSFWorkbook(file);
                ISheet sheet = workbook.GetSheet("Sheet1");

                //创建字体
                IFont font = workbook.CreateFont();
                //font.IsItalic = true;//斜体字
                //font.Underline = (byte)FontUnderlineType.DOUBLE;//下划线
                font.Color = HSSFColor.BLACK.index;//颜色
                font.FontHeightInPoints = 12;//字号大小
                font.FontName = "宋体";//字体

                ICellStyle style = workbook.CreateCellStyle();
                style.SetFont(font);


                int rowNum = sheet.LastRowNum + 1;

                //创建新的一行
                IRow row1 = sheet.CreateRow(rowNum);
                //写入数据
                for (int i = 0; i < data.Length; i++)
                {
                    sheet.GetRow(rowNum).CreateCell(i).SetCellValue(data[i]);
                    sheet.GetRow(rowNum).GetCell(i).CellStyle = style;
                }

                FileStream sw = File.Create(path);
                workbook.Write(sw);
                sw.Close();
            }
            else
            {
                MessageBox.Show("文件不存在，请先创建xlsx文件，然后再存储数据");
            }
            return;
        }
        #endregion



        IWorkbook workbook;
        ISheet[] sheets;

        private void buttonCreateXlsx2_Click(object sender, EventArgs e)
        {
            string[] sheetNames = { "平面度", "轮廓度", "平行度", "厚度" };
            string[] headerNames = { "长度", "宽度", "厚度1", "厚度2", "厚度3", "厚度4" };

            //CreateXlsx(@"D:\文件2.xlsx", sheetNames, headerNames, "平面度", out workbook, out sheets);

            CreateXlsxDefault(@"D:\文件2.xlsx", headerNames, out workbook, out sheets);
        }

        private void buttonWriteData_Click(object sender, EventArgs e)
        {
            double[] data = { 1.2, 1.3, 1.4, 1.5, 1.6, 1.7 };
            WriteDataInXlsx(workbook, @"D:\文件2.xlsx", data);
        } 
    }
}

4. 下载地址

https://mp.csdn.net/mp_download/manage/download/UpDetailed