Csharp: Export DataTable to Excel using OpenXml 2.5 in asp.net, csharpopenxml

Csharp: Export DataTable to Excel using OpenXml 2.5 in asp.net, csharpopenxml

// Https://www.microsoft.com/en-us/download/details.aspx? Id = 5124 Open xml sdk 2.0 for Microsoft Office // https://www.microsoft.com/en-us/download/details.aspx? Id = 30425 Open xml sdk 2.5 for Microsoft Office // https://github.com/OfficeDev/Open-Xml-Sdk // http://www.codeproject.com/Tips/366446/Export-GridView-Data-to-Excel-using-OpenXml // https://openxmlsdkjs.codeplex.com // reference: WindowsBase. dll c: \ Program Files (x86) \ Reference Assemblies \ Microsoft \ Framework \. NETFramework \ v4.0 \ WindowsBase. dll /// <summary> ///// </summary> internal class ExcelHelper {/// <summary> /// </summary> internal class ColumnCaption {private static string [] Alphabets = {"", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K ", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U ", "V", "W", "X", "Y", "Z"}; private static ColumnCaption instance = null; private List <string> cellHeaders = null; public static ColumnCaption Instance {get {if (instance = null) return new ColumnCaption (); else return ColumnCaption. instance ;}//< summary> ///// </summary> public ColumnCaption () {this. initCollection () ;}//< summary> ///// </summary> private void InitCollection () {cellHeaders = new List <string> (); foreach (string sItem in Alphabets) cellHeaders. add (sItem); foreach (string item in Alphabets) foreach (string sItem in Alphabets) cellHeaders. add (item + sItem);} // <summary> // Returns the column caption for the given row & column index. /// </summary> /// <param name = "rowIndex"> Index of the row. </param> // <param name = "columnIndex"> Index of the column. </param> // <returns> </returns> internal string Get (int rowIndex, int columnIndex) {return this. cellHeaders. elementAt (columnIndex) + (rowIndex + 1 ). toString ();}} /// <summary> /// export /// </summary> /// <param name = "DataTable"> DataTable </param> /// <param name = "sheetname"> worksheet name </param> /// <param name = "filename"> file name </param> /// <returns> </returns> internal string ExportToExcel (dataTable table, string sheetname, string filename) {string excelfile = Path. getTempPath () + filename; using (SpreadsheetDocument excelDoc = SpreadsheetDocument. create (excelfile, DocumentFormat. openXml. spreadsheetDocumentType. workbook) {CreateExcelParts (excelDoc, table, sheetname);} return excelfile ;} /// <summary> ///// </summary> /// <param name = "spreadsheetDoc"> </param> /// <param name = "data "> </param> // <param name =" sheetname "> </param> private void CreateExcelParts (SpreadsheetDocument spreadsheetDoc, dataTable data, string sheetname) {WorkbookPart workbookPart = spreadsheetDoc. addWorkbookPart (); CreateWorkbookPart (workbookPart, sheetname); int workBookPartCount = 1; WorkbookStylesPart workbookStylesPart = workbookPart. addNewPart <WorkbookStylesPart> ("rId" + (workBookPartCount ++ ). toString (); CreateWorkbookStylesPart (workbookStylesPart); WorksheetPart worksheetPart = workbookPart. addNewPart <WorksheetPart> ("rId" + (101 ). toString (); CreateWorksheetPart (workbookPart. worksheetParts. elementAt (0), data); SharedStringTablePart sharedStringTablePart = workbookPart. addNewPart <SharedStringTablePart> ("rId" + (workBookPartCount ++ ). toString (); CreateSharedStringTablePart (sharedStringTablePart, data); workbookPart. workbook. save () ;}/// <summary> // Creates the shared string table part. /// </summary> /// <param name = "sharedStringTablePart"> The shared string table part. </param> /// <param name = "sheetData"> The sheet data. </param> private void CreateSharedStringTablePart (SharedStringTablePart sharedStringTablePart, DataTable sheetData) {UInt32Value stringCount = Convert. toUInt32 (sheetData. rows. count) + Convert. toUInt32 (sheetData. columns. count); SharedStringTable sharedStringTable = new SharedStringTable () {Count = stringCount, UniqueCount = stringCount}; for (int columnIndex = 0; columnIndex <sheetData. columns. count; columnIndex ++) {SharedStringItem sharedStringItem = new SharedStringItem (); Text text = new Text (); text. text = sheetData. columns [columnIndex]. columnName; sharedStringItem. append (text); sharedStringTable. append (sharedStringItem) ;}for (int rowIndex = 0; rowIndex <sheetData. rows. count; rowIndex ++) {SharedStringItem sharedStringItem = new SharedStringItem (); Text text = new Text (); text. text = sheetData. rows [rowIndex] [0]. toString (); sharedStringItem. append (text); sharedStringTable. append (sharedStringItem);} sharedStringTablePart. sharedStringTable = sharedStringTable;} // <summary> // Creates the worksheet part. /// </summary> /// <param name = "worksheetPart"> The worksheet part. </param> /// <param name = "data"> The data. </param> private void CreateWorksheetPart (WorksheetPart worksheetPart, DataTable data) {Worksheet worksheet = new Worksheet () {MCAttributes = new worker () {Ignorable = "x14ac"}; worksheet. addNamespaceDeclaration ("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); worksheet. addNamespaceDeclaration ("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); worksheet. addNamespaceDeclaration ("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); SheetViews sheetViews = new SheetViews (); SheetView sheetView = new SheetView () {WorkbookViewId = (UInt32Value) 0U }; selection selection = new Selection () {ActiveCell = "A1"}; sheetView. append (selection); sheetViews. append (sheetView); PageMargins pageMargins = new PageMargins () {Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D }; sheetFormatProperties sheetFormatPr = new SheetFormatProperties () {DefaultRowHeight = 15D, DyDescent = 0.25D}; SheetData sheetData = new SheetData (); UInt32Value rowIndex = 1U; Row row1 = new Row () {RowIndex = rowIndex ++, Spans = new ListValue <StringValue> () {InnerText = "1:3"}, DyDescent = 0.25D}; for (int columnIndex = 0; columnIndex <data. columns. count; columnIndex ++) {Cell cell Cell = new Cell () {CellReference = ExcelHelper. columnCaption. instance. get (Convert. toInt32 (UInt32) rowIndex)-2), columnIndex), ype = CellValues. string}; CellValue cellValue = new CellValue (); cellValue. text = data. columns [columnIndex]. columnName. toString (). formatCode (); cell. append (cellValue); row1.Append (cell);} sheetData. append (row1); for (int rIndex = 0; rIndex <data. rows. count; rIndex ++) {Row row = new Row () {RowIndex = rowIndex ++, Spans = new ListValue <StringValue> () {InnerText = "1: 3 "}, dyDescent = 0.25D}; for (int cIndex = 0; cIndex <data. columns. count; cIndex ++) {if (cIndex = 0) {Cell cell = new Cell () {CellReference = ExcelHelper. columnCaption. instance. get (Convert. toInt32 (UInt32) rowIndex)-2), cIndex), ype = CellValues. string}; CellValue cellValue = new CellValue (); cellValue. text = data. rows [rIndex] [cIndex]. toString (); cell. append (cellValue); row. append (cell);} else {Cell cell = new Cell () {CellReference = ExcelHelper. columnCaption. instance. get (Convert. toInt32 (UInt32) rowIndex)-2), cIndex), ype = CellValues. string}; CellValue cellValue = new CellValue (); cellValue. text = data. rows [rIndex] [cIndex]. toString (); cell. append (cellValue); row. append (cell) ;}} sheetData. append (row);} worksheet. append (sheetViews); worksheet. append (sheetFormatPr); worksheet. append (sheetData); worksheet. append (pageMargins); worksheetPart. worksheet = worksheet;} // <summary> // Creates the workbook styles part. /// </summary> /// <param name = "workbookStylesPart"> The workbook styles part. </param> private void publish (WorkbookStylesPart workbookStylesPart) {Stylesheet stylesheet = new Stylesheet () {MCAttributes = new external () {Ignorable = "x14ac"}; stylesheet. addNamespaceDeclaration ("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); stylesheet. week ("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); StylesheetExtensionList rows = new week (); StylesheetExtension stylesheetExtension = new StylesheetExtension () {Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"}; stylesheetExtension. addNamespaceDeclaration ("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); DocumentFormat. openXml. office2010.Excel. slicerStyles slicerStyles = new DocumentFormat. openXml. office2010.Excel. slicerStyles () {DefaultSlicerStyle = "SlicerStyleLight1"}; stylesheetExtension. append (slicerStyles); stylesheetExtensionList. append (stylesheetExtension); stylesheet. append (stylesheetExtensionList); workbookStylesPart. stylesheet = stylesheet;} // <summary> // Creates the workbook part. /// </summary> /// <param name = "workbookPart"> The workbook part. </param> private void CreateWorkbookPart (WorkbookPart workbookPart, string sheetName) {Workbook workbook = new Workbook (); Sheets sheets = new Sheets (); Sheet sheet = new Sheet () {Name = sheetName, // worksheet Name SheetId = Convert. toUInt32 (101), Id = "rId" + (101 ). toString ()}; sheets. append (sheet); CalculationProperties calculationProperties = new CalculationProperties () {CalculationId = (UInt32Value) 123456U // some default Int32Value}; workbook. append (sheets); workbook. append (calculationProperties); workbookPart. workbook = workbook ;}/// <summary >///// </summary> public static class Extensions {public static string FormatCode (this string sourceString) {if (sourceString. contains ("<") sourceString = sourceString. replace ("<", "<"); if (sourceString. contains (">") sourceString = sourceString. replace (">", ">"); return sourceString ;}}

　　

/// <Summary> ///// </summary> public partial class WebForm1: System. web. UI. page {DataTable getData () {DataTable dt = new DataTable (); dt. columns. add ("id", typeof (int); dt. columns. add ("name", typeof (string); dt. rows. add (1, "geovindu"); dt. rows. add (2, "geov"); dt. rows. add (3, "SBO"); dt. rows. add (4, ""); dt. rows. add (5, "Japanese"); dt. rows. add (6, "zookeeper"); dt. rows. add (7, "tu juwen"); dt. rows. add (8, "Juwen"); return dt ;} /// <summary> //// </summary> /// <param name = "sender"> </param> /// <param name = "e "> </param> protected void Page_Load (object sender, eventArgs e) {if (! IsPostBack) {BindGrid () ;}/// <summary >///// </summary> private void BindGrid () {this. gridView1.DataSource = getData (); GridView1.DataBind ();} /// <summary> //// </summary> /// <param name = "sender"> </param> /// <param name = "e "> </param> protected void button#click (object sender, eventArgs e) {string rootPath = HttpContext. current. server. mapPath ("~ "). ToString (); string localCopy = "文" + DateTime. now. toString ("yyyyMMddHHmmssfff") + ". xlsx "; // string file = new ExcelHelper (). exportToExcel (getData (), "geovindu", localCopy); File. copy (file, rootPath + localCopy); Response. redirect (HttpUtility. urlEncode (localCopy, System. text. encoding. UTF8 ));}}

　　

/// <Summary> /// windows 10 Microsoft Edge test is invalid. /// Collect the text into /// </summary> /// <param name = "dt"> </param> /// <param name = "Response"> </param> // <param name = "filename"> </param> public static void Convertexcel (DataTable dt, httpResponse Response, string filename) {// win 10 does not pass through Response. clear (); Response. buffer = true; Response. clearContent (); Response. clearHeaders (); Response. contentEncoding = System. text. encoding. UTF8; // Response. appendHeader ("con Tent-disposition "," attachment; filename=myfile.xlsx "); // Response. addHeader "Content-Disposition", "attachment?filename=myfile.csv" // Response. contentType = "application/vnd. openxmlformats-officedocument.spreadsheetml.sheet "; // Response. addHeader ("content-disposition", "attachment; filename =" + System. web. httpUtility. urlEncode (filename, System. text. encoding. UTF8) + ". xlsx "); // Response. contentType = "Application/ms-excel"; // application/vnd. ms-excel // 2003 // Response. addHeader ("content-disposition", "attachment; filename =" + System. web. httpUtility. urlEncode (filename, System. text. encoding. UTF8) + ". xls "); // xlsx Response. write ("<meta http-equiv = \" Content-Type \ "content = \" text/html; charset = UTF-8 \ "/>"); Response. charset = "UTF-8"; Response. cache. setCacheability (HttpCacheability. noCache); // Re Else se. contentType = "application/vnd. openxmlformats-officedocument.spreadsheetml.sheet "; // 2007 System. IO. stringWriter stringWrite = new System. IO. stringWriter (); // stringWrite. encoding System. web. UI. htmlTextWriter htmlWrite = new System. web. UI. htmlTextWriter (stringWrite); // htmlWrite. encoding System. web. UI. webControls. dataGrid dg = new System. web. UI. webControls. dataGrid (); dg. dataSource = dt; dg. DataBind (); dg. RenderControl (htmlWrite); string style = @ "<! -- Mce: 2 --> "; Response. write (style); Response. output. write (stringWrite. toString (); // Response. write (stringWrite. toString (); Response. flush (); Response. end (); // HttpContext. current. applicationInstance. completeRequest ();}