.NET使用Office Open XML導出大量資料到 Excel

我相信很多人在做項目的都碰到過Excel資料導出的需求，我從最開始使用最原始的HTML拼接（将需要導出的資料拼接成TABLE标簽）到後來happy的使用開源的NPOI, EPPlus等開源元件導出EXCEL，但不久前，我在一個項目碰到一個需求：要将幾個分别有近60多萬的資料源導出到Excel中，我們先不要讨論這個需求本身是否合理，客戶就是要這樣。我先後用NPOI和EPPlus，都發現同一個問題：OutOfMemoryException，我電腦12G記憶體居然不夠用？

的确記憶體溢出了，但記憶體還剩下好幾個G的，就會溢出，我用 .NET做的網站，開發的時候Host應該是Visual Studio安裝的IIS Express, 應該是VS本身的限制，不過在網上查閱資料也沒發現這的确也是困擾一些人的，也沒查到什麼結果，好在還有Google, 躍過牆外，在Stack Overflow上查到資料： OpenXML , 這不是什麼新技: Office 2007在設計的時候, 為了更好的和其它應用程式互動，使用了XML + ZIP技術來實作excel, world, PPT等元件的本地儲存, 我們所使用xlsx, dox, pptx檔案本質上就一個ZIP壓縮包，包内是組織好的XML檔案，也就是說，我們可以通過生成, 修改, 生成合規的XML檔案，再壓縮成ZIP包，這就是一個可以被Office識别的檔案了。

用圖說話：

在園子裡其實也有不少人介紹過 Open XML，我想就多一個視角來介紹Open XML吧，好像也有很長時間沒人寫關于這個博文。

什麼是Office Open XML?

我們來看下維基百科的定義：

Office Open XML (also informally known as OOXML or Microsoft Open XML (MOX)[2) is a zipped, XML-based file format developed by Microsoft[3] for representing spreadsheets, charts, presentations and word processing documents. The format was initially standardized by Ecma (as ECMA-376), and by the ISO and IEC (as ISO/IEC 29500) in later versions.

Starting with Microsoft Office 2007, the Office Open XML file formats have become the default[4] target file format of Microsoft Office.[5][6] Microsoft Office 2010 provides read support for ECMA-376, read/write support for ISO/IEC 29500 Transitional, and read support for ISO/IEC 29500 Strict.[7] Microsoft Office 2013 and Microsoft Office 2016 additionally support both reading and writing of ISO/IEC 29500 Strict.[8]re

refer: https://en.wikipedia.org/wiki/Office_Open_XML

從Office 2007開始，就開始使用XML檔案格式作為Microsoft Office的預設儲存方式，其實我們通常用的NPOI office 2007部分和EPPlus就是使用Open XML來開發的。

為什麼同是使用Open XML， NPOI和EPPLus會出現記憶體溢出的問題？

這兩個開源元件有對Office套件有着很全面的支援，它們會把資料加載到記憶體中一次性處理，如果碰到資料量過大，就很可能遇到這個問題，網上EPPlus在20多萬條資料的就溢出了，NPOI在11多萬的時候就會溢出，這個是和資料的列數和内容有關系，不管怎樣，我們以後可能是會碰到這種大量資料的EXCEL導出，我們不需要很複雜的功能，就是想要導出一個EXCEL清單，這其實是可以做到的。

Open XML怎樣做不會記憶體溢出？

NPOI和EPPlus在導出大量資料的Excel清單時可能會發生記憶體溢出的問題，原因是它們都把資料儲存在記憶體中，因為它們支援各種複雜的功能，那麼簡單的清單，就是數量超大，我們把它通過檔案流寫入磁盤，這個問題就解決了。

如何使用OPEN XML?

我們需要去微軟官網下載下傳OFFICE OPEN XML的SDK，連結: https://www.microsoft.com/en-hk/download/details.aspx?id=30425，推薦使用NuGet在VISULAL STUDIO直接将引用添加到Project。

在GitHub還有一些示例代碼：https://github.com/OfficeDev/Open-XML-SDK

代碼實作

說了這麼多廢話，我們看如何用OPEN XML實作一個EXCEL清單的導出:

從原理上講就是用OpenXML一個一個把标簽寫入本地磁盤。

我截取我寫的導出類的幾個方法來來解釋:

/// <summary>
/// 指定磁盤路徑初始化OpenWorkDoucment
/// </summary>
/// <param name="fileName"></param>
private void OpenWorkDocument(string fileName)
{
     document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);
}

///<summary>
///用datatable作為資料源，實際情況可以根據需要調整
///</summary>
public void AddSheet(DataTable dt, string sheetName)

{
            if (dt == null || dt.Rows.Count == 0)
            {
                throw new ArgumentNullException(nameof(dt), "data source can not be null");
            }

            if (document == null)
            {
                throw new ArgumentNullException(nameof(document), "please init document first");
            }


            //this list of attributes will be used when writing a start element
            List<OpenXmlAttribute> attributes;
　　　　　　　//這是我們為什麼不會溢出的關鍵點, 使用XmlWriter寫入磁盤
            OpenXmlWriter writer;  

            WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart<WorksheetPart>();
            writer = OpenXmlWriter.Create(workSheetPart);
　　　       //使用OpenXML麻煩的地方就是我們要用SDK去拼接XML内容
            writer.WriteStartElement(new Worksheet());
            writer.WriteStartElement(new SheetViews()); //sheetViews
            writer.WriteStartElement(new SheetView() //sheetView
            {
                TabSelected = true,
                WorkbookViewId = 0U  //這裡的下标是從0開始的
            });
　　　　　　　 //這裡是當機列頭，别問為什麼是A2，我試了A1不行
            Pane pane = new Pane()
            {
                State = new EnumValue<PaneStateValues>(PaneStateValues.Frozen),
                VerticalSplit = new DoubleValue((double)1),
                TopLeftCell = new StringValue("A2"),
                ActivePane = new EnumValue<PaneValues>(PaneValues.BottomLeft)
            };
　　　　　　　//對于一些文檔本身的結構的描述，我們可以直接把準備屬性設定正确，直接寫入，因為描述執行個體很占用資源小，當然我們也可以把描述結點的子節點，子子節點都通過WriteStartElememt寫入，不過很麻煩，容易出錯
            writer.WriteStartElement(pane); //Pane
            writer.WriteEndElement();   //Pane
            writer.WriteStartElement(new Selection()
            {
                Pane = new EnumValue<PaneValues>(PaneValues.BottomLeft)
            });
            writer.WriteEndElement();   //Selection 關閉标簽
            writer.WriteEndElement(); //sheetView 關閉标簽
            writer.WriteEndElement(); //sheetViews 關閉标簽


            writer.WriteStartElement(new SheetData());
            var rowIndex = 0;
            foreach (DataRow row in dt.Rows)
            {
                //build header
                if (rowIndex == 0)
                {
                    //create a new list of attributes
                    attributes = new List<OpenXmlAttribute>();
                    // add the row index attribute to the list
                    attributes.Add(new OpenXmlAttribute("r", null, (rowIndex + 1).ToString()));
                    //header start
                    writer.WriteStartElement(new Row(), attributes);
                    foreach (DataColumn col in dt.Columns)
                    {
                        attributes = new List<OpenXmlAttribute>();
                        //這裡注意，在Excel在處理字元串的時候，會将所有的字元串儲存到sharedStrings.xml, cell内寫入在sharedString.XML的索引, 屬性t(type)設定為s(str)//我們在導出excel的時候把sharedString.mxl考慮進來會加大複雜程度，是以将t設定為str, 一個不存在的type, excel會直接解析cell内的字串值
                        attributes.Add(new OpenXmlAttribute("t", null, "str"));
                        //通過s指定style樣式的下标
                        attributes.Add(new OpenXmlAttribute("s", null, FORMAT_INDEX_HEADER.ToString()));
                        //能過r指定單元格位置，好像不是必需, 注意這裡下标位置是從1開始的
                        attributes.Add(new OpenXmlAttribute("r", "", string.Format("{0}{1}", GetColumnName(col.Ordinal + 1), rowIndex + 1)));
                        writer.WriteStartElement(new Cell(), attributes);
                        writer.WriteElement(new CellValue(col.ColumnName));
                        writer.WriteEndElement();

                    }
                    //header end
                    writer.WriteEndElement();
                    rowIndex++;
                }

                //資料寫入，我們通過xmlWriter不會觸發異常//create a new list of attributes
                attributes = new List<OpenXmlAttribute>();
                // add the row index attribute to the list
                attributes.Add(new OpenXmlAttribute("r", null, (rowIndex + 1).ToString()));
                //header start
                writer.WriteStartElement(new Row(), attributes);
                foreach (DataColumn col in dt.Columns)
                {
                    attributes = new List<OpenXmlAttribute>();

                    switch (col.DataType.ToString())
                    {
                        case "System.Int32":
                            attributes.Add(new OpenXmlAttribute("s", null, FORMAT_INDEX_INT.ToString()));
                            attributes.Add(new OpenXmlAttribute("t", null, "n")); //number
                            break;
                        case "System.Double":
                        case "System.Decimal":
                        case "System.Float":
                            attributes.Add(new OpenXmlAttribute("s", null, FORMAT_INDEX_DEC.ToString())); //header style
                            attributes.Add(new OpenXmlAttribute("t", null, "n")); //number
                            break;
                        default:
                            attributes.Add(new OpenXmlAttribute("s", null, FORMAT_INDEX_STR.ToString())); //header style
                            attributes.Add(new OpenXmlAttribute("t", null, "str")); //string
                            break;
                    }
                    //add the cell reference attribute
                    attributes.Add(new OpenXmlAttribute("r", null, string.Format("{0}{1}", GetColumnName(col.Ordinal + 1), rowIndex + 1)));
                    writer.WriteStartElement(new Cell(), attributes);
                    writer.WriteElement(new CellValue(row[col.Ordinal].ToString()));
                    writer.WriteEndElement();
                }
                //header end
                writer.WriteEndElement();

                rowIndex++;
            }

            // End SheetData
            writer.WriteEndElement();
            // End Worksheet
            writer.WriteEndElement();
            writer.Close();



            if (document.WorkbookPart.Workbook == null)
            {
                document.WorkbookPart.Workbook = new Workbook();
                document.WorkbookPart.Workbook.Append(new Sheets());
            }
            //資料寫入完成後，注冊一個sheet引用到workbook.xml, 也就是在excel最下面的sheet name
            var sheet = new Sheet()
            {
                Name = !String.IsNullOrWhiteSpace(sheetName) ? sheetName : ("Sheet " + DateTime.Now.ToString("ms")),
                SheetId = UInt32Value.FromUInt32((uint)m_sheetIndex++),
                Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
            };
            document.WorkbookPart.Workbook.Sheets.Append(sheet);
}

//生成Style樣式, 注意下标從0開始, 依次加1, 如果有跳過1直接設定3這樣情況, 可能無法正常解析到樣式
private Stylesheet GenerateStylesheet()
        {
            Stylesheet styleSheet = null;

            Fonts fonts = new Fonts(
                new Font( // Index 0 - default
                    new FontSize() { Val = 11 }

                ),
                new Font( // Index 1 - header
                    new FontSize() { Val = 11 },
                    new Bold(),
                    new Color() { Rgb = "FFFFFF" }

                ));

            Fills fills = new Fills(
                    new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
                    new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default
                    new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "0070c0" } }) { PatternType = PatternValues.Solid })
                );



            Borders borders = new Borders(
                    new Border(), // index 0 default
                    new Border( // index 1 black border
                        new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                        new DiagonalBorder())
                );

            NumberingFormats numbers = new NumberingFormats(
                new NumberingFormat() { NumberFormatId = 0, FormatCode = new StringValue("#,##0.00") },
                new NumberingFormat() { NumberFormatId = 1, FormatCode = new StringValue("0") }
                );


            CellFormats cellFormats = new CellFormats(
                    // default
                    new CellFormat() { FormatId = FORMAT_INDEX_DEFUALT },
                    // body  string
                    new CellFormat { FormatId = FORMAT_INDEX_STR, FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true },
                    // body decimal
                    new CellFormat { FormatId = FORMAT_INDEX_DEC, FontId = 0, FillId = 0, BorderId = 1, NumberFormatId = 0, ApplyBorder = true },
                    //header
                    new CellFormat { FormatId = FORMAT_INDEX_HEADER, FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true }, // header
                    // body  int
                    new CellFormat { FormatId = FORMAT_INDEX_INT, FontId = 0, FillId = 0, BorderId = 1, NumberFormatId = 1, ApplyBorder = true }
                );

            styleSheet = new Stylesheet(numbers, fonts, fills, borders, cellFormats);
            return styleSheet;　　
        }

private void WriteWorkbookStyle()
        {
            if (document != null)
            {
                WorkbookStylesPart stylePart = document.WorkbookPart.AddNewPart<WorkbookStylesPart>();
                var styleSheet = GenerateStylesheet();
                styleSheet.Save(stylePart);
            }

        }

設定樣式，當機首行，這些都可以簡單完成，如果需要添加圖表什麼的，還是建議用NPOI, EPPlus等開源方案，有圖表的excel不會太大。

對于Open XML的介紹就到這裡了，有什麼錯誤的地方，請指正。

.NET使用Office Open XML導出大量資料到 Excel

繼續閱讀

Unity3D中背景音樂和相關音效的添加，背景音樂暫停、停止和播放的設定

Unity遊戲開發背景知識

vs編譯完提示不支援嘗試的執行操作

【C# MySQL】第一次使用C#對MySQL進行操作的心得

C#基礎——與C#的第一次邂逅聲明：IDE：第一個C#果實：Console淺析：總結：

英文月日年日期格式的實作

C# 位元組數組 byte[] 快速比較算法

C# 位元組數組、字元串轉化位元組數組轉字元串字元串轉位元組數組位元組數組轉整數整數轉位元組數組字元串轉位元組字元串轉數值數值轉字元串

C#用Zlib壓縮或解壓縮位元組數組

C# 比較兩個位元組數組是否相等

對于VS中scanf報錯永久解決，建立源檔案自動添加#define _CRT_SECURE_NO_WARNINGS 1

《C#進階程式設計》讀書筆記 -索引

CQ V1.0分詞bates(基于雙數組tire樹)—應該是目前最快的中文分詞算法

C#多線程——前台線程和背景線程

C#+ArcEngine代碼檢測ArcEngine Runtime是否安裝以及是否授權

七牛雲-C#SDK-上傳-前期準備