【小白专用】在 vs 中使用 nuget 安装NPOI,创建Excel

置顶 zgscwxd

已于 2024-01-10 20:20:21 修改

阅读量884

点赞数 8

文章标签： NPOI excel C#

于 2023-12-09 16:20:18 首次发布

本文链接：https://blog.csdn.net/zgscwxd/article/details/134895723

版权

C#操作Excel有多种方法，如通过数据库的方式来读写Excel的OleDb方式，但是OleDb方式需要安装微软office，还可以通过COM组件方式操作Excel，也需要安装微软Excel。如果不想安装微软办公套餐可以使用ClosedXML、EPPlus、NPOI。本文主要是介绍NPOI的常用使用方法。
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

1. OleDb：

这种方式是把整个Excel文件当做一个数据源来进行数据的读取操作。

优点：实现方式简单，读取速度快；

缺点：读取Excel数据的过程不太灵活，对内存的占用比较高，当数据量变的很大时，容易由于内存空间不足导致内存溢出异常。（不过貌似对于今天电脑的硬件配置来说，内存问题不大）

2. Com组件

这种方式是通过Com组件 Microsoft.Office.Interop.Excel.dll实现Excel文件的操作。

优点：读取Excel数据非常灵活，可以实现Excel具有的各种数据处理功能；

缺点：对数据的访问时基于单元格方式实现的，所以读写数据较慢，特别是当数据量较大时，访问效率问题更为突出。另一点是要求本机安装了Microsoft Office组件。

3. NPOI

这种方式是通过NPOI库实现Excel文件操作，可以在没有安装微软Office的情况下使用。

优点：读取Excel数据速度较快，操作方式灵活；

缺点：试了再说！

NPOI中N指代的是.Net，POI是一个完全开源的Java写成的库，能够在没有安装微软Office或者相应环境的情况下读写Excel、Word等微软OLE2组件文档，几乎支持所有的Office97~Office2007的文件格式。所以NPOI就是POI项目的.Net版本。目前NPOI的最新版本是

NPOI支持的文件格式处理xls、xlsx外，还包括doc、ppt、vsd等，功能强大，人称Excel一哥。本文就准备单独谈一谈NPOI对Excel的基本操作。

NuGet安装NPOI

一、环境

工具：VS2022

项目类型：C# Windows 窗体应用(.NET Framework)

框架：.NET Framework 4.8

二、NPOI组件配置

（一）NOPI组件安装：

打开VS，点击“工具”——>“NuGet包管理器(N)”——>【管理解决方案的NuGet程序包(N)...】，搜索NPOI,可以看到安装包，点击下载

这时候，会发现项目的“引用”这块多了些东西，组件安装成功！

1.新建Windows窗体应用程序员项目，重命名为AliWorkbenchWinForms

2.在Form1界面中控件。添加button1、button2控件用于导入、导出excel，label1、label2用来显示导入、导出所需时间，dataGridView1用来查看Excel文件。

6.右键AliWorkbenchWinForms->添加->类

7.将类命名为NPOIExcel.cs->添加

8.为NPOIExcel.cs添加代码

HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


using System.Data;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;



namespace AliWorkbenchWinForms
{
    public class NPOIExcel
    {




        /// <summary>
        /// 将excel导入到datatable
        /// </summary>
        /// <param name="filePath">excel路径</param>
        /// <param name="isColumnName">第一行是否是列名</param>
        /// <returns>返回datatable</returns>
        public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
        {
            DataTable dataTable = null;
            FileStream fs = null;
            DataColumn column = null;
            DataRow dataRow = null;
            IWorkbook workbook = null;
            ISheet sheet = null;
            IRow row = null;
            ICell cell = null;
            int startRow = 0;
            try
            {
                using (fs = File.OpenRead(filePath))
                {
                    // 2007版本
                    if (filePath.IndexOf(".xlsx") > 0)
                        workbook = new XSSFWorkbook(fs);
                    // 2003版本
                    else if (filePath.IndexOf(".xls") > 0)
                        workbook = new HSSFWorkbook(fs);

                    if (workbook != null)
                    {
                        sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
                        dataTable = new DataTable();
                        if (sheet != null)
                        {
                            int rowCount = sheet.LastRowNum;//总行数
                            if (rowCount > 0)
                            {
                                IRow firstRow = sheet.GetRow(0);//第一行
                                int cellCount = firstRow.LastCellNum;//列数

                                //构建datatable的列
                                if (isColumnName)
                                {
                                    startRow = 1;//如果第一行是列名，则从第二行开始读取
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        cell = firstRow.GetCell(i);
                                        if (cell != null)
                                        {
                                            if (cell.StringCellValue != null)
                                            {
                                                column = new DataColumn(cell.StringCellValue);
                                                dataTable.Columns.Add(column);
                                            }
                                        }
                                    }
                                }
                                else
                                {
                                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                                    {
                                        column = new DataColumn("column" + (i + 1));
                                        dataTable.Columns.Add(column);
                                    }
                                }

                                //填充行
                                for (int i = startRow; i <= rowCount; ++i)
                                {
                                    row = sheet.GetRow(i);
                                    if (row == null) continue;

                                    dataRow = dataTable.NewRow();
                                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                                    {
                                        cell = row.GetCell(j);
                                        if (cell == null)
                                        {
                                            dataRow[j] = "";
                                        }
                                        else
                                        {
                                            //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
                                            switch (cell.CellType)
                                            {
                                                case CellType.Blank:
                                                    dataRow[j] = "";
                                                    break;
                                                case CellType.Numeric:
                                                    short format = cell.CellStyle.DataFormat;
                                                    //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                                        dataRow[j] = cell.DateCellValue;
                                                    else
                                                        dataRow[j] = cell.NumericCellValue;
                                                    break;
                                                case CellType.String:
                                                    dataRow[j] = cell.StringCellValue;
                                                    break;
                                            }
                                        }
                                    }
                                    dataTable.Rows.Add(dataRow);
                                }
                            }
                        }
                    }
                }
                return dataTable;
            }
            catch (Exception)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                return null;
            }
        }

        /// <summary>
        /// 写入excel
        /// </summary>
        /// <param name="dt">datatable</param>
        /// <param name="strFile">strFile</param>
        /// <returns></returns>
        public static bool DataTableToExcel(DataTable dt, string strFile)
        {
            bool result = false;
            IWorkbook workbook = null;
            FileStream fs = null;
            IRow row = null;
            ISheet sheet = null;
            ICell cell = null;
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    workbook = new XSSFWorkbook();//HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls  XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
                    sheet = workbook.CreateSheet("Sheet0");//创建一个名称为Sheet0的表
                    int rowCount = dt.Rows.Count;//行数
                    int columnCount = dt.Columns.Count;//列数

                    //设置列头
                    row = sheet.CreateRow(0);//excel第一行设为列头
                    for (int c = 0; c < columnCount; c++)
                    {
                        cell = row.CreateCell(c);
                        cell.SetCellValue(dt.Columns[c].ColumnName);
                    }

                    //设置每行每列的单元格,
                    for (int i = 0; i < rowCount; i++)
                    {
                        row = sheet.CreateRow(i + 1);
                        for (int j = 0; j < columnCount; j++)
                        {
                            cell = row.CreateCell(j);//excel第二行开始写入数据
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        }
                    }
                    using (fs = File.OpenWrite(strFile))
                    {
                        workbook.Write(fs);//向打开的这个xls文件中写入数据
                        result = true;
                    }
                }
                return result;
            }
            catch (Exception ex)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                Console.WriteLine(ex.StackTrace + ex.Message);
                return false;
            }
        }

        /// <summary>
        /// Excel导入成Datable
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static String EXCEL_XLS = "application/vnd.ms-excel";
        public static String EXCEL_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        public static DataTable ExcelToTable(string file)
        {
            DataTable dt = new DataTable();
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); }
                else if (fileExt == ".xls")
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else { workbook = null; }

                if (workbook == null) { return null; }
                ISheet sheet = workbook.GetSheetAt(0);

                //表头
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueType(header.GetCell(i));
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// Datable导出成Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file">导出路径(包括文件名与扩展名)</param>
        public static void TableToExcel(DataTable dt, string file)
        {
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
            if (workbook == null) { return; }
            ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

            //表头
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            //数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转为字节数组
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return null;
                case CellType.Boolean: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.String: //STRING:
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;

            }
        }








        //其他功能函数


        public void ReadFromExcelFile(string filePath)
        {
            IWorkbook wk = null;
            string extension = System.IO.Path.GetExtension(filePath);
            try
            {
                FileStream fs = File.OpenRead(filePath);
                if (extension.Equals(".xls"))
                {
                    //把xls文件中的数据写入wk中
                    wk = new HSSFWorkbook(fs);
                }
                else
                {
                    //把xlsx文件中的数据写入wk中
                    wk = new XSSFWorkbook(fs);
                }

                fs.Close();
                //读取当前表数据
                ISheet sheet = wk.GetSheetAt(0);

                IRow row = sheet.GetRow(0);  //读取当前行数据
                                             //LastRowNum 是当前表的总行数-1（注意）
                int offset = 0;
                for (int i = 0; i <= sheet.LastRowNum; i++)
                {
                    row = sheet.GetRow(i);  //读取当前行数据
                    if (row != null)
                    {
                        //LastCellNum 是当前行的总列数
                        for (int j = 0; j < row.LastCellNum; j++)
                        {
                            //读取该行的第j列数据
                            string value = row.GetCell(j).ToString();
                            Console.Write(value.ToString() + " ");
                        }
                        Console.WriteLine("\n");
                    }
                }
            }

            catch (Exception e)
            {
                //只在Debug模式下才输出
                Console.WriteLine(e.Message);
            }
        }








    }
}

9.在Form1.cs中分别双击button1、button2控件，为按钮添加按钮点击事件。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;




using System.Diagnostics;

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

        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {

            Stopwatch sw = new Stopwatch();
            sw.Start();

            OpenFileDialog openfile = new OpenFileDialog();
            openfile.Multiselect = false;
            if (openfile.ShowDialog() == DialogResult.OK)
            {
                //dataGridView1.DataSource = NPOIExcel.ExcelToDataTable("数据表.xlsx", true);//方式1
                dataGridView1.DataSource = NPOIExcel.ExcelToTable(openfile.FileName);//方式2
                sw.Stop();
                label1.Text = sw.ElapsedMilliseconds.ToString("数据导入耗时：" + "0000" + "ms");
                MessageBox.Show("数据导入完成");
            }





        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {

            Stopwatch sw = new Stopwatch();
            sw.Start();
            DataTable dt = DgvToDt(dataGridView1);

            SaveFileDialog save = new SaveFileDialog();
            //设置文件类型
            save.Filter = "Excel表格（*.xls）|*.xls|Excel表格（*.xlsx）|*.xlsx";
            //设置默认文件类型显⽰顺序
            save.FilterIndex = 1;
            //保存对话框是否记忆上次打开的记录
            save.RestoreDirectory = true;
            if (save.ShowDialog() == DialogResult.OK)
            {
                //string localFilePath = save.FileName.ToString(); //获得⽂件路径
                //string fileNameExt =localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取⽂件名，不带路径

                NPOIExcel.TableToExcel(dt, save.FileName);
                sw.Stop();
                label2.Text = sw.ElapsedMilliseconds.ToString("数据导出耗时：" + "0000" + "ms");
                MessageBox.Show("数据导出完成");
            }




        }



        private DataTable DgvToDt(DataGridView dgv)
        {
            DataTable dt = new DataTable();
            //把DataGridView控件数据，转成DataTable
            for (int count = 0; count < dgv.Columns.Count; count++)
            {
                DataColumn dc = new DataColumn(dgv.Columns[count].Name.ToString());
                dt.Columns.Add(dc);
            }
            for (int count = 0; count < dgv.Rows.Count; count++)
            {
                DataRow dr = dt.NewRow();
                for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
                {
                    dr[countsub] = Convert.ToString(dgv.Rows[count].Cells[countsub].Value);
                }
                dt.Rows.Add(dr);
            }

            return dt;
        }  







    }
}

11.点击运行按钮运行项目。可以导入导出Excel文件。