public FileResult Agent_Branch_Region_Export([DataSourceRequest] DataSourceRequest request)
{
if (asset.Export)
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
var listdata = DC_TeleSale_Agent_Region.GetAll();
IEnumerable datas = listdata.ToDataSourceResult(request).Data;
//using (ExcelPackage excelPkg = new ExcelPackage())
FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\DC_TeleSale_Agent_Branch_Region.xlsx"));
var excelPkg = new ExcelPackage(fileInfo);
ExcelWorksheet dataSheet = excelPkg.Workbook.Worksheets["Agent Master"];
ExcelWorksheet dataSheet3 = excelPkg.Workbook.Worksheets["ListUser"];
var listdata3 = dbConn.Select <EmployeeInfo>();
int rowData3 = 1;
foreach (var data in listdata3)
{
rowData3++;
dataSheet3.Cells[rowData3, 1].Value = data.UserName;
}
ExcelWorksheet dataSheet4 = excelPkg.Workbook.Worksheets["listBranch"];
var listdata4 = dbConn.Select <DC_Location_Region>();
int rowData4 = 1;
foreach (var data in listdata4)
{
rowData4++;
dataSheet4.Cells[rowData4, 1].Value = data.RegionName;
}
int rowData = 1;
foreach (DC_TeleSale_Agent_Region data in datas)
{
int i = 1;
rowData++;
dataSheet.Cells[rowData, i++].Value = data.UserName;
dataSheet.Cells[rowData, i++].Value = data.BranchName;
dataSheet.Cells[rowData, i++].Value = data.RegionName;
dataSheet.Cells[rowData, i++].Value = data.RowCreatedTime;
dataSheet.Cells[rowData, i++].Value = data.RowCreatedUser;
if (data.RowLastUpdatedTime.ToString("dd/MM/yyyy") != "01/01/1900")
{
dataSheet.Cells[rowData, i++].Value = data.RowLastUpdatedTime;
}
else
{
dataSheet.Cells[rowData, i++].Value = "";
}
dataSheet.Cells[rowData, i++].Value = data.RowLastUpdatedUser;
}
//Write the workbook to a memory stream
MemoryStream output = new MemoryStream();
excelPkg.SaveAs(output);
//Return the result to the end user
return(File(output.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"DC_TeleSale_Agent_Branch_Region_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx")); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
}
else
{
return(File("", //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"DC_TeleSale_Agent_Branch_Region_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx")); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
}
