c# 导出excel 组件比较（npoi vs openxml）

c# 导出excel 组件比较（npoi vs openxml）


一、资料：
npoi下载地址：http://npoi.codeplex.com/
openxml 下载地址：https://www.microsoft.com/en-us/download/details.aspx?id=30425
openxml msdn地址：https://msdn.microsoft.com/ZH-CN/library/bb448854.aspx


比较结果：
npoi 可以读写 .xls 和 .xlsx 格式；
openxml 只能读写 xlsx 格式（excel 2007 以后版本），且语法很不友好；


注：npoi 生成不同版本excel 使用的命名空间不一样
.xlsx:需要使用 NPOI.XSSF.UserModel.XSSFWorkbook 类操作
.xls :需要使用 NPOI.HSSF.UserModel.HSSFWorkbook 类操作


创建 npoi 比 openxml 快 3 倍
读取 openxml 比 npoi 快 1 倍
存储 openxml 比 npoi 小 10 倍


二、openxml 对象格式
SpreadsheetDocument
..》WorkbookPart
.........》WorksheetPart
..................》Worksheet
...........................》SheetData
.........》WorksheetPart
..................》Worksheet
...........................》SheetData1
.........》Workbook
..................》Sheets
...........................》Sheet


三、npoi 生成 .xls 和 openxml 比较


使用版本：
Npoi  ：NPOI_2.1.3.1
OpenXml：OpenXml_2.5


生成excel:100列，1000行


创建比较（毫秒）
Npoi  ：2811
OpenXml：10779


读取比较（毫秒）
Npoi  ：4319
OpenXml：1522


存储比较：
Npoi  ：4069 kb
OpenXml：488 kb


结论：使用npoi生成 xls 格式的 excle 比较快 但是非常的占磁盘空间。




四、npoi 生成 .xlsx 和 openxml 比较


使用版本：
Npoi  ：NPOI_2.1.3.1
OpenXml：OpenXml_2.5


生成excel:100列，1000行


创建比较（毫秒）
Npoi  ：13215
OpenXml：13834


读取比较（毫秒）
Npoi  ：9656
OpenXml：2113


存储比较：
Npoi  ：516 kb
OpenXml：516 kb


结论：同样是 xlsx 格式的 excle ，openxml 读取要比 npoi 快。

代码

ExcelNpoi.cs

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


namespace ExcelExport
{
    /// <summary>
    /// 2003 excel操作
    /// </summary>
    public class ExcelNpoi
    {
        public static void Create(string filename,DataSet ds)
        {
            using (FileStream fs =
                new FileStream(filename, FileMode.Create, FileAccess.Write))
            {
                IWorkbook workbook = null; //创建Workbook对象
                if(Regex.IsMatch(filename,".xlsx"))
                    workbook = new XSSFWorkbook(); //2007 excel（.xlsx）
                else
                    new HSSFWorkbook(); //2003 excel (.xlsx)


                for (int s = 0; s < ds.Tables.Count; s++)
                {
                    DataTable dt = ds.Tables[s];
                    var tname = dt.TableName;
                    ISheet sheet = workbook.CreateSheet(tname); //创建工作表
                    //IRow row = sheet.CreateRow(0); //在工作表中添加一行
                    //ICell cell = row.CreateCell(0); //在行中添加一列
                    //cell.SetCellValue("test"); //设置列的内容
                    int index = 0;
                    IRow row = null;
                    ICell cell = null;
                    object val = null;
                    //标题行
                    row = sheet.CreateRow(index++);
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        cell = row.CreateCell(i);
                        cell.SetCellValue(dt.Columns[i].ColumnName);
                    }
                    //内容行
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        row = sheet.CreateRow(index++);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            cell = row.CreateCell(j);
                            val = dt.Rows[i][j];
                            cell.SetCellValue(val.ToString());
                        }
                    }
                }
                workbook.Write(fs);
            }
        }


        public static DataTable GetSheet(string filename,string sheetname = "Sheet1")
        {
            DataTable dt = new DataTable();


            using (FileStream fs =
                new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = null; //从流内容创建Workbook对象
                if (Regex.IsMatch(filename, ".xlsx"))
                    workbook = new XSSFWorkbook(fs); //2007 excel（.xlsx）
                else
                    new HSSFWorkbook(fs); //2003 excel (.xlsx)


                //ISheet sheet = workbook.GetSheetAt(0); //获取第一个工作表
                //IRow row = sheet.GetRow(0); //获取工作表第一行
                //ICell cell = row.GetCell(0); //获取行的第一列
                //string value = cell.ToString(); //获取列的值
                ISheet sheet = null;
                IRow row = null;
                ICell cell = null;
                sheet = workbook.GetSheet(sheetname);
                int index = 0;
                int rows = sheet.LastRowNum;
                //首行标题
                for (; index < rows; index++)
                {
                    row = sheet.GetRow(index);
                    for (int i = 0; i < row.Cells.Count; i++)
                    {
                        cell = row.Cells[i];
                        dt.Columns.Add(cell.StringCellValue);
                    }
                    break;
                }
                //内容行
                DataRow dr = null;
                for (; index < rows; index++)
                {
                    dr = dt.NewRow();
                    row = sheet.GetRow(index);
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        cell = row.Cells[i];
                        dr[i]= cell.StringCellValue;
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }
    }
}

ExcelOpenXml.cs

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Data;
using System.Linq;

namespace ExcelExport
{
    public class ExcelOpenXml
    {
        /*
         * excel 对象结构
         * SpreadsheetDocument
         *   》WorkbookPart
	     *       》WorksheetPart
		 *           》Worksheet
		 *            》SheetData
	     *       》WorksheetPart
		 *          》Worksheet
		 *	            》SheetData1
	     *       》Workbook
		 *           》Sheets
		 *	            》Sheet
         */
        public static void Create(string filename, DataSet ds)
        {
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);

            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            Workbook workbook = new Workbook();
            Sheets sheets = new Sheets();
            
            #region 创建多个 sheet 页
            
            //创建多个sheet
            for (int s = 0; s < ds.Tables.Count; s++)
            {
                DataTable dt = ds.Tables[s];
                var tname = dt.TableName;

                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                Worksheet worksheet = new Worksheet();
                SheetData sheetData = new SheetData();

                //创建 sheet 页
                Sheet sheet = new Sheet()
                {
                    //页面关联的 WorksheetPart
                    Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                    SheetId = UInt32Value.FromUInt32((uint)s + 1),
                    Name = tname
                };
                sheets.Append(sheet);

                #region 创建sheet 行
                Row row;
                uint rowIndex = 1;
                //添加表头
                row = new Row()
                {
                    RowIndex = UInt32Value.FromUInt32(rowIndex++)
                };
                sheetData.Append(row);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    Cell newCell = new Cell();
                    newCell.CellValue = new CellValue(dt.Columns[i].ColumnName);
                    newCell.DataType = new EnumValue<CellValues>(CellValues.String);
                    row.Append(newCell);
                }
                //添加内容
                object val = null;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    row = new Row()
                    {
                        RowIndex = UInt32Value.FromUInt32(rowIndex++)
                    };
                    sheetData.Append(row);

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        Cell newCell = new Cell();
                        val = dt.Rows[i][j];
                        newCell.CellValue = new CellValue(val.ToString());
                        newCell.DataType = new EnumValue<CellValues>(CellValues.String);

                        row.Append(newCell);
                    }

                }
                #endregion

                worksheet.Append(sheetData);
                worksheetPart.Worksheet = worksheet;
                worksheetPart.Worksheet.Save();
            }
            #endregion

            workbook.Append(sheets);
            workbookpart.Workbook = workbook;

            workbookpart.Workbook.Save();
            spreadsheetDocument.Close();
        }

        public static DataTable GetSheet(string filename, string sheetName)
        {
            DataTable dt = new DataTable();
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(filename, false))
            {
                WorkbookPart wbPart = document.WorkbookPart;
                //通过sheet名查找 sheet页
                Sheet sheet = wbPart
                    .Workbook
                    .Descendants<Sheet>()
                    .Where(s => s.Name == sheetName)
                    .FirstOrDefault();

                if (sheet == null)
                {
                    throw new ArgumentException("未能找到" + sheetName+" sheet 页");
                }

                //获取Excel中共享表
                SharedStringTablePart sharedStringTablePart = wbPart
                    .GetPartsOfType<SharedStringTablePart>()
                    .FirstOrDefault();
                SharedStringTable sharedStringTable = null;
                if(sharedStringTablePart!=null)
                    sharedStringTable = sharedStringTablePart.SharedStringTable;
                #region 构建datatable

                //添加talbe列,返回列数
                Func<Row,int> addTabColumn = (r) =>
                {
                    //遍历单元格
                    foreach (Cell c in r.Elements<Cell>())
                    {
                        dt.Columns.Add(GetCellVal(c, sharedStringTable));
                    }
                    return dt.Columns.Count;
                };
                //添加行
                Action<Row> addTabRow = (r) =>
                {
                    DataRow dr = dt.NewRow();
                    int colIndex = 0;
                    int colCount = dt.Columns.Count;
                    //遍历单元格
                    foreach (Cell c in r.Elements<Cell>())
                    {
                        if (colIndex >= colCount)
                            break;
                        dr[colIndex++] = GetCellVal(c, sharedStringTable);
                    }
                    dt.Rows.Add(dr);
                };
                #endregion


                //通过 sheet.id 查找 WorksheetPart 
                WorksheetPart worksheetPart 
                    = wbPart.GetPartById(sheet.Id) as WorksheetPart;
                //查找 sheetdata
                SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

                //遍历行
                foreach (Row r in sheetData.Elements<Row>())
                {
                    //构建table列
                    if (r.RowIndex == 1)
                    {
                        addTabColumn(r);
                        continue;
                    }
                    //构建table行
                    addTabRow(r);
                }

            }
            return dt;
        }

        /// <summary>
        /// 获取单元格值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="sharedStringTable"></param>
        /// <returns></returns>
        static string GetCellVal(Cell cell,SharedStringTable sharedStringTable)
        {
            var val = cell.InnerText;

            if (cell.DataType != null)
            {
                switch (cell.DataType.Value)
                {
                    //从共享表中获取值
                    case CellValues.SharedString:
                        if (sharedStringTable != null)
                            val = sharedStringTable
                                .ElementAt(int.Parse(val))
                                .InnerText;
                        break;
                    default:
                        val = string.Empty;
                        break;
                }

            }
            return val;
        }
    }
}

调用部分 TestCreateExcel.cs

using System;
using System.Data;
using System.IO;
using ExcelExport;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace TextExcelExport
{
    [TestClass]
    public class TestCreateExcel
    {
        #region npoi 
        [TestMethod]
        public void TestNpoiCrate()
        {
            var fname = TestData.GetNewExcelFileName(".xls");

            var dt1 = TestData.GetDataTable(tabName: "tab1");
            var dt2 = TestData.GetDataTable(tabName: "tab2");
            DataSet ds = new DataSet();
            ds.Tables.Add(dt1);
            ds.Tables.Add(dt2);

            ExcelNpoi.Create(fname, ds);
            Assert.IsTrue(File.Exists(fname));
        }
        [TestMethod]
        public void TestNpoiRead()
        {
            var fname = TestData.GetFileName("201607261433.xls");
            var dt = ExcelNpoi.GetSheet(fname);
            Assert.IsTrue(File.Exists(fname));
        }
        #endregion

        #region openxml
        [TestMethod]
        public void TestOpenXmlCrate()
        {
            var fname = TestData.GetNewExcelFileName();

            var dt1 = TestData.GetDataTable(tabName: "tab1");
            var dt2 = TestData.GetDataTable(tabName: "tab2");
            DataSet ds = new DataSet();
            ds.Tables.Add(dt1);
            ds.Tables.Add(dt2);

            ExcelOpenXml.Create(fname, ds);

            Assert.IsTrue(File.Exists(fname));
        }
        [TestMethod]
        public void TestOpenXmlRead()
        {
            var fname = TestData.GetFileName("160727-153300.xlsx");
            var dt = ExcelOpenXml.GetSheet(fname,"tab1");
            
            Assert.IsTrue(File.Exists(fname));
        }
        #endregion
    }
}

测试数据 TestData.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace TextExcelExport
{
    public class TestData
    {
        private static string _exportDir = @"D:\temp\excel";

        public static string GetNewExcelFileName(string suffix=".xlsx")
        {
            return  Path.Combine(_exportDir
                , DateTime.Now.ToString("yyMMdd-HHmmss") + suffix);
        }
        public static string GetFileName(string fileName)
        {
            return Path.Combine(_exportDir
                , fileName);
        }

        public static DataTable GetDataTable(int cols=50,int rows=100,string tabName="mytable")
        {
            DataTable dt = new DataTable(tabName);
            for (int i = 0; i < cols; i++)
            {
                dt.Columns.Add("col"+i.ToString("D3"));
            }

            DataRow dr = null;
            for (int i = 0; i < rows; i++)
            {
                dr = dt.NewRow();
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    dr[j] = "val-" + i + "-" + j;
                }
                dt.Rows.Add(dr);
            }
            
            return dt;
        }
    }
}