将数据插入Excel工作表从数据表 [英] Inserting data into an excel sheet from a DataTable
问题描述
我从数据库中提取数据到一个数据表
并通过其绑定到转发
控件显示它。现在,我需要将相同的数据复制到Excel US preadsheet。我如何可以使用相同的数据表
来填充在S preadsheet。请建议。
I'm extracting data from the database into a DataTable
and displaying it by binding it to a Repeater
control. Now I need to copy the same data into an excel spreadsheet. How can i use the same DataTable
to the fill the spreadsheet. Please suggest.
推荐答案
我建议建立一个真正的Excel文件,而不是HTML表格(许多人一样)。为此我诚心诚意推荐 EPPlus
(< A HREF =HTTP://epplus.$c$cplex.com/license相对=nofollow> LGPL许可)
I would suggest to create a real excel-file instead of a html table(what many people do). Therefor i can warmly recommend EPPlus
(LGPL license).
然后,它是简单的。假设你有一个按钮 BtnExportExcel
:
Then it is simple. Assuming that you have a button BtnExportExcel
:
protected void BtnExcelExport_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
try {
var pck = new OfficeOpenXml.ExcelPackage();
var ws = pck.Workbook.Worksheets.Add("Name of the Worksheet");
// get your DataTable
var tbl = GetDataTable();
ws.Cells["A1"].LoadFromDataTable(tbl, true, OfficeOpenXml.Table.TableStyles.Medium6);
foreach (DataColumn col in tbl.Columns) {
if (col.DataType == typeof(System.DateTime)) {
var colNumber = col.Ordinal + 1;
var range = ws.Cells[1, colNumber, tbl.Rows.Count, colNumber];
// apply the correct date format, here for germany
range.Style.Numberformat.Format = "dd.MM.yyyy";
}
}
var dataRange = ws.Cells[ws.Dimension.Address.ToString()];
dataRange.AutoFitColumns();
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=NameOfExcelFile.xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
} catch (Exception ex) {
// log exception
throw;
}
Response.End();
}
这篇关于将数据插入Excel工作表从数据表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!