我的视图由多个表组成，我希望从Excel文件中的视图导出多个表。我当前的功能只帮助我导出1个表。
有谁能帮我完成这段代码，以便可以导出多个表吗？
- 报告VM
public class ReportVM
{
public string ScenName { get; set; }
public int Count { get; set; }
public string CreateTickYes { get; set; }
public int TickYes { get; set; }
public string RegionName { get; set; }
public int RegionCount { get; set; }
public string UserName { get; set; }
public int ChatCountUser { get; set; }
}
- 要导出的操作方法
public FileContentResult DownloadReport(DateTime start, DateTime end)
{
//var uName = User.Identity.Name;
var fileDownloadName = String.Format("Report.xlsx");
const string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
// Pass your ef data to method
ExcelPackage package = GenerateExcelFile(db.Chats.Where(x => System.Data.Entity.DbFunctions.TruncateTime(x.ChatCreateDateTime) >= start && System.Data.Entity.DbFunctions.TruncateTime(x.ChatCreateDateTime) <= end)
.GroupBy(a => a.ScenarioList).Select(b => new ReportVM()
{
ScenName = b.Key,
Count = b.Count()
}).ToList());
var fsr = new FileContentResult(package.GetAsByteArray(), contentType);
fsr.FileDownloadName = fileDownloadName;
return fsr;
}
private static ExcelPackage GenerateExcelFile(IEnumerable<ReportVM> datasource)
{
ExcelPackage pck = new ExcelPackage();
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet 1");
// Sets Headers
ws.Cells[1, 1].Value = "Scenario";
ws.Cells[1, 2].Value = "No.Of Chats";
// Inserts Data
for (int i = 0; i < datasource.Count(); i++)
{
ws.Cells[i + 2, 1].Value = datasource.ElementAt(i).ScenName;
ws.Cells[i + 2, 2].Value = datasource.ElementAt(i).Count;
}
//Sheet2
// Format Header of Table
using (ExcelRange rng = ws.Cells["A1:B1"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.Gold); //Set color to DarkGray
rng.Style.Font.Color.SetColor(Color.Black);
}
return pck;
}
因此，现在它导出表GroubBy＝ScenarioList的数据。我还想在groupBy=用户名中包含另一列。所以当导出数据时，Excel文件应该包含两张图纸。表场景列表为1，表用户名为2。
非常感谢您的帮助。提前谢谢。
您需要创建div/table，将所有表放在它下面，然后使用下面的javascript函数。请在拥有所有数据的同一页面上点击按钮，调用此javascript函数。这对我来说是有效的，我已经在我的项目中使用过了。
function DownloadToExcel() {
var htmls = $("#compareBodyContent")[0].innerHTML; // this main element under which
//all you data
var uri = 'data:application/vnd.ms-excel;base64,';
var template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>';
var base64 = function (s) {
return window.btoa(unescape(encodeURIComponent(s)))
};
var format = function (s, c) {
return s.replace(/{(w+)}/g, function (m, p) {
return c[p];
})
};
var ctx = {
worksheet: 'Worksheet',
table: '<table>' + htmls + '</table>'
}
var compareLink = document.createElement("a");
compareLink.download = "Compare_Test.xls";
compareLink.href = uri + base64(format(template, ctx));
compareLink.click();
}
希望这对你有帮助。如果你对此有任何疑问，请告诉我。