public ActionResult District_Read([DataSourceRequest] DataSourceRequest request)
{
using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn())
{
var data = new List <CRM_Location_District>();
if (request.Filters.Any())
{
var where = KendoApplyFilter.ApplyFilter(request.Filters[0], "data.");
data = CRM_Location_District.GetCRM_Location_Districts(where, "DistrictID DESC");
}
else
{
data = CRM_Location_District.GetCRM_Location_Districts("1=1", "DistrictID DESC");
}
var a = new DataSourceResult();
a.Data = data;
a.Total = data.Count();
return(Json(a));
}
}
public FileResult ExportExcel([DataSourceRequest] DataSourceRequest request)
{
if (asset.Export)
{
using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn())
{
var country = new List <CRM_Location_District>();
if (request.Filters.Any())
{
var where = KendoApplyFilter.ApplyFilter(request.Filters[0], "data.");
country = CRM_Location_District.GetCRM_Location_Districts(where, "DistrictID DESC");
}
else
{
country = CRM_Location_District.GetCRM_Location_Districts("1=1", "DistrictID DESC");
}
//using (ExcelPackage excelPkg = new ExcelPackage())
FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\CRM_Location_District.xlsx"));
var excelPkg = new ExcelPackage(fileInfo);
//data sheet
ExcelWorksheet dataSheet = excelPkg.Workbook.Worksheets["CRM_Location_District"];
IEnumerable listData = country.ToDataSourceResult(request).Data;
int rowData = 1;
foreach (CRM_Location_District data in listData)
{
int i = 1;
rowData++;
dataSheet.Cells[rowData, i++].Value = data.DistrictID;
dataSheet.Cells[rowData, i++].Value = data.DistrictName;
dataSheet.Cells[rowData, i++].Value = (!String.IsNullOrEmpty(data.CityID) ? data.CityID : "") + " - " + (!String.IsNullOrEmpty(data.CityName) ? data.CityName : "");
dataSheet.Cells[rowData, i++].Value = data.RegionName;
dataSheet.Cells[rowData, i++].Value = data.CountryName;
dataSheet.Cells[rowData, i++].Value = data.AliasName;
dataSheet.Cells[rowData, i++].Value = data.Active;
dataSheet.Cells[rowData, i++].Value = data.RowCreatedTime.ToString();
dataSheet.Cells[rowData, i++].Value = data.RowCreatedUser;
if (data.RowLastUpdatedTime != null)
{
dataSheet.Cells[rowData, i++].Value = data.RowLastUpdatedTime.ToString();
}
else
{
dataSheet.Cells[rowData, i++].Value = "";
}
dataSheet.Cells[rowData, i++].Value = data.RowLastUpdatedUser;
}
//data sheet
var alias = DC_Location_Region.GetDC_Location_Regions("1=1", "CountryID DESC").ToList();
ExcelWorksheet dataSheetAliasID = excelPkg.Workbook.Worksheets["List"];
IEnumerable listDataAliasID = alias.ToDataSourceResult(request).Data;
int rowDataAliasID = 1;
foreach (DC_Location_Region data in listDataAliasID)
{
int i = 1;
rowDataAliasID++;
dataSheetAliasID.Cells[rowDataAliasID, i++].Value = (!String.IsNullOrEmpty(data.RegionID) ? data.RegionID : "") + " - " + (!String.IsNullOrEmpty(data.RegionName) ? data.RegionName : "");
}
MemoryStream output = new MemoryStream();
excelPkg.SaveAs(output);
string fileName = "CRM_Location_District_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
output.Position = 0;
return(File(output.ToArray(), contentType, fileName));
}
}
else
{
string fileName = "CRM_Location_District_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
return(File("", contentType, fileName));
}
}
