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();
}
