public void WorksheetDataTablePasteColumnsTemplate() { DataTable dt = new DataTable(); //Manually create table for mockup purposes dt.TableName = "tblParameters"; dt.Columns.Add("intID", typeof(int)); dt.Columns.Add("vchrDescription", typeof(string)); dt.Columns.Add("decPrice", typeof(float)); dt.AcceptChanges(); dt.Rows.Add(new object[] { 1, "Parts", 1.00F }); dt.Rows.Add(new object[] { 2, "Cheque", 1.50F }); dt.Rows.Add(new object[] { 3, "Products", 1.45F }); dt.Rows.Add(new object[] { 4, "Gifts", 0.00F }); dt.Rows.Add(new object[] { 5, "DealerRepair", 2.50F }); //Write to the spreadsheet MemoryStream stream = SpreadsheetReader.Copy(string.Format("{0}\\Templates\\template.xlsx", Directory.GetCurrentDirectory())); SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true); WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1"); WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart); writer.PasteDataTable(dt, "B3", new List <string>(new string[] { "vchrDescription" })); //Save to the memory stream, and then to a file SpreadsheetWriter.Save(doc); SpreadsheetWriter.StreamToFile(string.Format("{0}\\datatabletemplate.xlsx", GetOutputFolder()), stream); }
public void WorksheetDataTablePasteTest() { DataTable dt = new DataTable(); //Manually create table for mockup purposes dt.TableName = "tblParameters"; dt.Columns.Add("intID", typeof(int)); dt.Columns.Add("vchrDescription", typeof(string)); dt.Columns.Add("dteValidFrom", typeof(DateTime)); dt.Columns.Add("decPrice", typeof(float)); dt.Columns.Add("bitFlag", typeof(Boolean)); dt.AcceptChanges(); dt.Rows.Add(new object[] { 1, "Parts", new DateTime(1974, 1, 2), 1.00F, true }); dt.Rows.Add(new object[] { 2, "Cheque", new DateTime(1974, 2, 2), 1.50F, false }); dt.Rows.Add(new object[] { 3, "Products", new DateTime(1974, 3, 2), 1.45F, true }); dt.Rows.Add(new object[] { 4, "Gifts", new DateTime(1974, 4, 2), 0.00F, false }); dt.Rows.Add(new object[] { 5, "DealerRepair", new DateTime(1974, 5, 2), 2.50F, true }); //Write to the spreadsheet MemoryStream stream = SpreadsheetReader.Create(); SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true); WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1"); WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart); SpreadsheetStyle style = SpreadsheetReader.GetDefaultStyle(doc); style.IsBold = true; writer.PasteDataTable(dt, "B3", style); //Save to the memory stream, and then to a file SpreadsheetWriter.Save(doc); SpreadsheetWriter.StreamToFile(string.Format("{0}\\datatable.xlsx", GetOutputFolder()), stream); }
/// <summary> /// 将查询出的DataSet导出为Excel。 /// </summary> /// <param name="dataSet">要导出的DataSet数据集,可以包含多个DataTable数据,使用DataSetName作为导出的文件名</param> public static void ExportDataSet(DataSet dataSet) { if (dataSet == null) { throw new ArgumentException("dataSet"); } if (dataSet.Tables.Count == 0) { return; } MemoryStream stream = SpreadsheetReader.Create(); SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true); //首先清空原有的Sheet var allSheet = doc.WorkbookPart.Workbook.Descendants <Sheet>(); if (allSheet.Count() > 0) { doc.WorkbookPart.Workbook.Sheets.RemoveAllChildren(); } //构造Excel foreach (DataTable dt in dataSet.Tables) { WorksheetPart sheetPart = SpreadsheetWriter.InsertWorksheet(doc, dt.TableName); WorksheetWriter sheetWriter = new WorksheetWriter(doc, sheetPart); //打印列名 int colIndex = 0;//当前第几列,从0开始 foreach (DataColumn col in dt.Columns) { string reference = GetExcelColumnHeader(colIndex) + "1"; sheetWriter.PasteText(reference, col.ColumnName); colIndex++; } sheetWriter.PasteDataTable(dt, "A2"); SpreadsheetWriter.Save(doc); } //Write to response stream System.Web.HttpContext context = System.Web.HttpContext.Current; context.Response.Clear(); string UserAgent = context.Request.ServerVariables["http_user_agent"].ToLower(); string FileName = dataSet.DataSetName; if (UserAgent.IndexOf("firefox") == -1) {//非火狐浏览器 FileName = context.Server.UrlEncode(FileName); } context.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", FileName + ".xlsx")); context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; stream.WriteTo(context.Response.OutputStream); context.Response.End(); }