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;        }    }}