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/
//引用： 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 = { "A", "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>
/// 導出
/// </summary>
/// <param name="DataTable">DataTable</param>
/// <param name="sheetname">工作表名</param>
/// <param name="filename">文件名</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 MarkupCompatibilityAttributes() { 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 = new Cell() { CellReference = ExcelHelper.ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), columnIndex), DataType = 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), DataType = 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), DataType = 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 CreateWorkbookStylesPart(WorkbookStylesPart workbookStylesPart)
{
Stylesheet stylesheet = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
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, //工作表名
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, "塗斯博");
dt.Rows.Add(4, "趙雅芝");
dt.Rows.Add(5, " なわち日本語");
dt.Rows.Add(6, "처리한다");
dt.Rows.Add(7, "塗聚文");
dt.Rows.Add(8, "塗聚文");
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 Button1_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 測試無效。
///塗聚文註
/// </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 通不過
Response.Clear();
Response.Buffer = true;
Response.ClearContent();
Response.ClearHeaders();
Response.ContentEncoding = System.Text.Encoding.UTF8;
//Response.AppendHeader("content-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);
//Response.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();
}
