how to fill excel sheet from database using Aspose total or Aspose Cells giving an Excel template that might contain formulas which should be keept active after filling the Excel document.
after creating a folder in your project where you will have the Excel file that you want to generate and adding the Aspose.Total to your using statments. Create the following method to generate the excel file:
protected void CurrentExcel_Click(object sender, EventArgs e){
//getting the items that will fill the cells(should be different
//than below)
Items searchItems = new SearchItems();
searchItems.ProjectStatusIDs = new List<int> { 24721 };
List<CurrentRecord> resultsRecords =
YourEntity.GetCurrentRecords().OrderBy(c => c.LOCATION).ToList();
// the template Excel file that you will fill
string fileName = "currents_list_Excel_Shortened.xlsx";
//define a workbook that will help you access Excel file cells
Workbook wb = new Workbook(Server.MapPath(string.Format(@"
{0}/{1}", "~/Templates/", fileName)));
//adding worksheet that will be filled
wb.Worksheets.Add(SheetType.Worksheet);
Worksheet ws = wb.Worksheets[0];
ws.Name = "Current Shortened";
try
{
Aspose.Cells.Cells wsCells = ws.Cells;
int x = 8;
foreach (CurrentRecord mwa in resultsRecords)
{
Cell Cell1 = ws.Cells[x, 0];
Cell Cell2 = ws.Cells[x, 1];
Cell Cell3 = ws.Cells[x, 2];
Cell Cell4 = ws.Cells[x, 3];
Cell Cell5 = ws.Cells[x, 4];
Cell Cell6 = ws.Cells[x, 5];
Cell Cell7 = ws.Cells[x, 6];
Cell Cell8 = ws.Cells[x, 7];
Cell Cell9 = ws.Cells[x, 8];
Cell Cell10 = ws.Cells[x, 9];
Cell Cell11 = ws.Cells[x, 10];
Cell Cell12 = ws.Cells[x, 11];
Cell Cell13 = ws.Cells[x, 12];
Cell Cell14 = ws.Cells[x, 13];
// here filling your object properties to the cells which
//should be different than the one below
Cell1.PutValue(mwa.ID + "-" +
mwa.LOCATION);
Cell2.PutValue(mwa.number);
Cell3.PutValue(mwa.Rate + " " + mwa.POSTMILE + " " +
mwa.POSTMILE_KPList);
Cell4.PutValue(mwa.PROJECT_LOCATION_TYPE);
Cell5.PutValue(mwa.RELName.Split(' ')[0] + "/" +
mwa.RECell);
if (mwa.COMPANY_NAME != "")
{
Cell6.PutValue(mwa.COMPANY_NAME.IndexOf('-') != -1 ?
mwa.COMPANY_NAME.Split(' ')[0] :
mwa.COMPANY_NAME.Split(' ')[0] + ' ' +
mwa.COMPANY_NAME.Split(' ')[1]);
}
Cell7.PutValue(mwa.PROJECT_STATUS);
Cell8.PutValue(mwa.PROJECT_LOCATION_WORKING_DAYS);
Cell9.PutValue(mwa.PROJECT_STATUS_PE_DAYS);
Cell10.PutValue(mwa.PROJECT_STATUS_WORK_SUSPENDED == true
? "Yes" : "NO");
Cell11.PutValue(string.Format("{0:0.######}",
mwa.PROJECT_STATUS_WORK_COMPLETED) + "/" +
string.Format("{0:0.######}",
mwa.PROJECT_STATUS_TIME_COMPLETED));
Cell12.PutValue(mwa.M600 != null ? string.Format("{0:d}",
mwa.M600) : "TBD");
Cell13.PutValue(mwa.Contractual != null ? string.Format("
{0:d}", mwa.Contractual) : "TBD");
Cell14.PutValue(mwa.PROJECT_STATUS_UPDATED_EST_COMPLETION
!= null ? string.Format("{0:d}",
mwa.PROJECT_STATUS_UPDATED_EST_COMPLETION) : "TBD");
x++;
}
wb.Save(HttpContext.Current.Response, fileName,
Aspose.Cells.ContentDisposition.Attachment, new
XlsSaveOptions(Aspose.Cells.SaveFormat.Xlsx));
}
catch(Exception ex)
{
throw;
}
}