Does anyone know any simple NuGet package or other way to export data from database to Excel? I have a few tables and each one I'd like to export to other WorkSheet, but in same Excel file.
有谁知道任何简单的NuGet包或从数据库导出数据到Excel的其他方法?我有几个表,每个表我都想导出到其他工作表中,但都在同一个Excel文件中。
Also later I'd like to import data from file Excel to database. For both operations may be different library.
稍后我还想将数据从文件Excel导入数据库。对于这两个操作,可能是不同的库。
I tried already this But it doesn't work.
我已经试过了,但是没用。
@EDIT - solved
@EDIT——解决
Here are some useful links. I hope that someday would help someone :)
这里有一些有用的链接。我希望有一天能帮助某人:
http://epplus.codeplex.com/
http://techbrij.com/export-excel-xls-xlsx-asp-net-npoi-epplus
http://techbrij.com/export-excel-xls-xlsx-asp-net-npoi-epplus
2 个解决方案
#1
1
This Code get from EPPlus and also helpful for your question ::
这段代码来自EPPlus,对您的问题也很有帮助:
private void ExcelExport(DataTable table)
{
using (ExcelPackage packge = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = packge.Workbook.Worksheets.Add("Demo");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(table, true);
//Format the header for column 1-3
using (ExcelRange range = ws.Cells["A1:C1"])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
range.Style.Font.Color.SetColor(Color.White);
}
//Example how to Format Column 1 as numeric
using (ExcelRange col = ws.Cells[2, 1, 2 + table.Rows.Count, 1])
{
col.Style.Numberformat.Format = "#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//Write it back to the client
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=ExcelExport.xlsx");
Response.BinaryWrite(packge.GetAsByteArray());
}
}
#2
0
There are several options as paid, free or client side conversion solution, server side conversion solutions.
有几个选项是付费的,免费的或客户端转换解决方案,服务器端转换解决方案。
You need to take a decision, if you just want to export to excel client side without effecting server. Here is demo (running) solution for you.
如果您只想导出到excel客户端而不影响服务器,那么您需要做出决定。这里是演示(运行)解决方案。
http://www.itorian.com/2015/05/export-table-data-into-excel-file.html
http://www.itorian.com/2015/05/export-table-data-into-excel-file.html
Click on result tab and then click on button "Export table data into excel". Mark this as answer if this helps.
单击“结果”选项卡,然后单击“将表数据导出到excel”按钮。如果有帮助的话,把这个标记为答案。
Edit
编辑
As you mentioned, you need to export tables into different excel pages (but in same file), this will work for you http://www.aspsnippets.com/Articles/Export-DataSet-DataTables-to-multiple-Excel-Sheets-Worksheets-in-ASPNet-using-C-and-VBNet.aspx
如前所述,您需要将表导出到不同的excel页面(但在同一个文件中),这将适用于http://www.aspsnippets.com/articles/export - dataset - datatabls -to-multi - excelsheets - worksheets -in- aspnet - use - cs - vbnet.aspx
#1
1
This Code get from EPPlus and also helpful for your question ::
这段代码来自EPPlus,对您的问题也很有帮助:
private void ExcelExport(DataTable table)
{
using (ExcelPackage packge = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = packge.Workbook.Worksheets.Add("Demo");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(table, true);
//Format the header for column 1-3
using (ExcelRange range = ws.Cells["A1:C1"])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
range.Style.Font.Color.SetColor(Color.White);
}
//Example how to Format Column 1 as numeric
using (ExcelRange col = ws.Cells[2, 1, 2 + table.Rows.Count, 1])
{
col.Style.Numberformat.Format = "#,##0.00";
col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//Write it back to the client
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=ExcelExport.xlsx");
Response.BinaryWrite(packge.GetAsByteArray());
}
}
#2
0
There are several options as paid, free or client side conversion solution, server side conversion solutions.
有几个选项是付费的,免费的或客户端转换解决方案,服务器端转换解决方案。
You need to take a decision, if you just want to export to excel client side without effecting server. Here is demo (running) solution for you.
如果您只想导出到excel客户端而不影响服务器,那么您需要做出决定。这里是演示(运行)解决方案。
http://www.itorian.com/2015/05/export-table-data-into-excel-file.html
http://www.itorian.com/2015/05/export-table-data-into-excel-file.html
Click on result tab and then click on button "Export table data into excel". Mark this as answer if this helps.
单击“结果”选项卡,然后单击“将表数据导出到excel”按钮。如果有帮助的话,把这个标记为答案。
Edit
编辑
As you mentioned, you need to export tables into different excel pages (but in same file), this will work for you http://www.aspsnippets.com/Articles/Export-DataSet-DataTables-to-multiple-Excel-Sheets-Worksheets-in-ASPNet-using-C-and-VBNet.aspx
如前所述,您需要将表导出到不同的excel页面(但在同一个文件中),这将适用于http://www.aspsnippets.com/articles/export - dataset - datatabls -to-multi - excelsheets - worksheets -in- aspnet - use - cs - vbnet.aspx