网站建设需要考什么证,河北省唐山市建设规划局的网站,站长之家统计,找别人做网站需要注意什么我相信很多人在做项目的都碰到过Excel数据导出的需求#xff0c;我从最开始使用最原始的HTML拼接#xff08;将需要导出的数据拼接成TABLE标签#xff09;到后来happy的使用开源的NPOI, EPPlus等开源组件导出EXCEL#xff0c;但不久前#xff0c;我在一个项目碰到一个需求…我相信很多人在做项目的都碰到过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?id30425推荐使用NuGet在VISULAL STUDIO直接将引用添加到Project。 在GitHub还有一些示例代码https://github.com/OfficeDev/Open-XML-SDK 代码实现 说了这么多废话我们看如何用OPEN XML实现一个EXCEL列表的导出: 从原理上讲就是用OpenXML一个一个把标签写入本地磁盘。 我截取我写的导出类的几个方法来来解释: /// summary
/// 指定磁盘路径初始化OpenWorkDoucment
/// /summary
/// param namefileName/param
private void OpenWorkDocument(string fileName)
{document SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);
} ///summary///用datatable作为数据源实际情况可以根据需要调整////summarypublic 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 elementListOpenXmlAttribute attributes; //这是我们为什么不会溢出的关键点, 使用XmlWriter写入磁盘OpenXmlWriter writer; WorksheetPart workSheetPart document.WorkbookPart.AddNewPartWorksheetPart();writer OpenXmlWriter.Create(workSheetPart); //使用OpenXML麻烦的地方就是我们要用SDK去拼接XML内容writer.WriteStartElement(new Worksheet());writer.WriteStartElement(new SheetViews()); //sheetViewswriter.WriteStartElement(new SheetView() //sheetView{TabSelected true,WorkbookViewId 0U //这里的下标是从0开始的});//这里是冻结列头别问为什么是A2我试了A1不行Pane pane new Pane(){State new EnumValuePaneStateValues(PaneStateValues.Frozen),VerticalSplit new DoubleValue((double)1),TopLeftCell new StringValue(A2),ActivePane new EnumValuePaneValues(PaneValues.BottomLeft)};//对于一些文档本身的结构的描述我们可以直接把准备属性设置正确直接写入因为描述实例很占用资源小当然我们也可以把描述结点的子节点子子节点都通过WriteStartElememt写入不过很麻烦容易出错writer.WriteStartElement(pane); //Panewriter.WriteEndElement(); //Panewriter.WriteStartElement(new Selection(){Pane new EnumValuePaneValues(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 headerif (rowIndex 0){//create a new list of attributesattributes new ListOpenXmlAttribute();// add the row index attribute to the listattributes.Add(new OpenXmlAttribute(r, null, (rowIndex 1).ToString()));//header startwriter.WriteStartElement(new Row(), attributes);foreach (DataColumn col in dt.Columns){attributes new ListOpenXmlAttribute();//这里注意在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 endwriter.WriteEndElement();rowIndex;}//数据写入我们通过xmlWriter不会触发异常//create a new list of attributesattributes new ListOpenXmlAttribute();// add the row index attribute to the listattributes.Add(new OpenXmlAttribute(r, null, (rowIndex 1).ToString()));//header startwriter.WriteStartElement(new Row(), attributes);foreach (DataColumn col in dt.Columns){attributes new ListOpenXmlAttribute();switch (col.DataType.ToString()){case System.Int32:attributes.Add(new OpenXmlAttribute(s, null, FORMAT_INDEX_INT.ToString()));attributes.Add(new OpenXmlAttribute(t, null, n)); //numberbreak;case System.Double:case System.Decimal:case System.Float:attributes.Add(new OpenXmlAttribute(s, null, FORMAT_INDEX_DEC.ToString())); //header styleattributes.Add(new OpenXmlAttribute(t, null, n)); //numberbreak;default:attributes.Add(new OpenXmlAttribute(s, null, FORMAT_INDEX_STR.ToString())); //header styleattributes.Add(new OpenXmlAttribute(t, null, str)); //stringbreak;}//add the cell reference attributeattributes.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 endwriter.WriteEndElement();rowIndex;}// End SheetDatawriter.WriteEndElement();// End Worksheetwriter.WriteEndElement();writer.Close();if (document.WorkbookPart.Workbook null){document.WorkbookPart.Workbook new Workbook();document.WorkbookPart.Workbook.Append(new Sheets());}//数据写入完成后注册一个sheet引用到workbook.xml, 也就是在excel最下面的sheet namevar 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 - defaultnew FontSize() { Val 11 }),new Font( // Index 1 - headernew FontSize() { Val 11 },new Bold(),new Color() { Rgb FFFFFF }));Fills fills new Fills(new Fill(new PatternFill() { PatternType PatternValues.None }), // Index 0 - defaultnew Fill(new PatternFill() { PatternType PatternValues.Gray125 }), // Index 1 - defaultnew Fill(new PatternFill(new ForegroundColor { Rgb new HexBinaryValue() { Value 0070c0 } }) { PatternType PatternValues.Solid }));Borders borders new Borders(new Border(), // index 0 defaultnew Border( // index 1 black bordernew 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(// defaultnew CellFormat() { FormatId FORMAT_INDEX_DEFUALT },// body stringnew CellFormat { FormatId FORMAT_INDEX_STR, FontId 0, FillId 0, BorderId 1, ApplyBorder true },// body decimalnew CellFormat { FormatId FORMAT_INDEX_DEC, FontId 0, FillId 0, BorderId 1, NumberFormatId 0, ApplyBorder true },//headernew CellFormat { FormatId FORMAT_INDEX_HEADER, FontId 1, FillId 2, BorderId 1, ApplyFill true }, // header// body intnew 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.AddNewPartWorkbookStylesPart();var styleSheet GenerateStylesheet();styleSheet.Save(stylePart);}} 设置样式冻结首行这些都可以简单完成如果需要添加图表什么的还是建议用NPOI, EPPlus等开源方案有图表的excel不会太大。 对于Open XML的介绍就到这里了有什么错误的地方请指正。转载于:https://www.cnblogs.com/efreer/p/8286645.html
