Hello all,
I am creating an excel sheet that will have multiple rows to display data. The header that I have created populates fine without any issues.
Under the header I am creating a table. The table will have titles and a field that will be populated from the database to the right.
For some reason one of the fields populates without any issues but the other fields do not although I am using the same technique of code. When I debug the program all of the fields are correct on the variables coming from the database. Just they do not get passed onto the excel sheet.
Here is the code I am using to populate one of the fields that works properly –
{
ICell hCell = hrow.GetCell(colPickupTime);
hCell.CellStyle = template_row.GetCell(colPickupTime).CellStyle;
hCell.SetCellValue(!String.IsNullOrEmpty(record.PickupTime) ? record.PickupTime : String.Empty);
}
Here goes the piece of code I use to check the fields –
sheet.GetRow(start_row + 1).GetCell(colPickupPerson).SetCellValue(!String.IsNullOrEmpty(record.CustomerName) ? record.CustomerName : "Customer Name");
I use this piece of code for populating and other fields the same way
{
ICell hCell = hrow.CreateCell(colPickupAddress, CellType.STRING);
hCell.CellStyle = template_row.GetCell(colPickupAddress).CellStyle;
hCell.SetCellValue(!String.IsNullOrEmpty(record.ToAddress1) ? strBuildFullToAddress : String.Empty);
}
Here is the whole file of code and everything for the class
private ActionResult LogSpreadsheet(List<usp_LogResult> list, object[] filterStrs)
{
const int colPickupPerson = 1;
const int colPickupAddress = 3;
const int colPickupTime = 7;
const int colMileage = 9;
const int rowType = 0;
const int rowTenant = 4;
const int rowDriver = rowTenant + 3;
const int rowVIN = rowTenant + 6;
const int start_row = rowTenant + 10;
const int tableRowThree = start_row + 3;
int last_row = start_row + 7;
string file_path = Server.MapPath(@"\Content\ Log-Template.xls");
HSSFWorkbook wk;
using (FileStream fs_in = new FileStream(file_path, FileMode.Open, FileAccess.Read))
{
wk = new HSSFWorkbook(fs_in, true);
}
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = filterStrs[0].ToString();
wk.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = " Log Report";
wk.SummaryInformation = si;
ISheet sheet = wk.GetSheetAt(0);
var patriarch = sheet.CreateDrawingPatriarch();
sheet.GetRow(rowType).GetCell(0).SetCellValue(filterStrs[3].ToString());
sheet.GetRow(rowTenant).GetCell(0).SetCellValue(filterStrs[0].ToString());
sheet.GetRow(rowDriver).GetCell(3).SetCellValue(filterStrs[2].ToString());
sheet.GetRow(rowTenant).GetCell(3).SetCellValue(filterStrs[1].ToString());
IRow template_row = sheet.GetRow(start_row);
var row_index = start_row;
foreach(var record in list)
{
sheet.ShiftRows(row_index + 1, last_row, 1);
IRow hrow = sheet.GetRow(row_index);
hrow.Height = template_row.Height;
IRow thirdDataRow = sheet.GetRow(row_index + 1);
{
ICell hcell = hrow.GetCell(colPickupPerson);
hcell.CellStyle = template_row.GetCell(colPickupPerson).CellStyle;
hcell.SetCellValue(!String.IsNullOrEmpty(record.CustomerName) ? record.CustomerName + "1" : "Customer test");
}
{
ICell hCell = hrow.GetCell(colPickupAddress);
hCell.CellStyle = template_row.GetCell(colPickupAddress).CellStyle;
hCell.CellStyle = cellRowStyle;
hCell.SetCellValue(!String.IsNullOrEmpty(record.FromAddress1) ? strBuildFullFromAddress : String.Empty);
}
{
ICell hCell = hrow.GetCell(colPickupTime);
hCell.CellStyle = template_row.GetCell(colPickupTime).CellStyle;
hCell.SetCellValue(!String.IsNullOrEmpty(record.PickupTime) ? record.PickupTime : String.Empty);
}
{
ICell hCell = thirdDataRow.GetCell(colPickupPerson);
hCell.CellStyle = thirdDataRow.GetCell(colPickupPerson).CellStyle;
hCell.SetCellValue(!String.IsNullOrEmpty(record.DropOffLocation) ? record.DropOffLocation : "DropOff");
}
{
ICell hCell = hrow.CreateCell(colPickupAddress, CellType.STRING);
hCell.CellStyle = template_row.GetCell(colPickupAddress).CellStyle;
hCell.SetCellValue(!String.IsNullOrEmpty(record.ToAddress1) ? strBuildFullToAddress : String.Empty);
}
sheet.ShiftRows(tableRowThree+1, last_row, 0);
{
ICell hCell = hrow.CreateCell(colPickupTime, CellType.STRING);
hCell.CellStyle = template_row.GetCell(colPickupTime).CellStyle;
hCell.SetCellValue(!String.IsNullOrEmpty(record.DropoffTime) ? record.DropoffTime : String.Empty);
}
{
ICell hCell = hrow.CreateCell(colPickupPerson, CellType.STRING);
hCell.CellStyle = template_row.GetCell(colPickupPerson).CellStyle;
hCell.SetCellValue(!String.IsNullOrEmpty(record.InsuranceAuthNumber) ? record.InsuranceAuthNumber : String.Empty);
}
{
ICell hCell = hrow.CreateCell(colPickupPerson, CellType.STRING);
hCell.CellStyle = template_row.GetCell(colPickupPerson).CellStyle;
hCell.SetCellValue(!String.IsNullOrEmpty(record.AWS) ? record.AWS : String.Empty);
}
{
ICell hCell = hrow.CreateCell(colPickupAddress, CellType.STRING);
hCell.CellStyle = template_row.GetCell(colPickupAddress).CellStyle;
string birth = Convert.ToString(record.DateOfBirth);
hCell.SetCellValue(!String.IsNullOrEmpty(birth) ? birth : "");
}
{
ICell hCell = hrow.CreateCell(colMileage, CellType.STRING);
hCell.CellStyle = fourthDataRow.GetCell(colMileage).CellStyle;
hCell.CellStyle = cellRowStyle;
hCell.SetCellValue(record.TotalTripMileage != null ? record.TotalTripMileage.ToString() : String.Empty);
}
{
ICell hCell = hrow.CreateCell(colPickupPerson, CellType.STRING);
hCell.CellStyle = template_row.GetCell(colPickupPerson).CellStyle;
hCell.SetCellValue(!String.IsNullOrEmpty(record.Notes) ? record.Notes : "");
}
sheet.ForceFormulaRecalculation = true;
var memoryStream = new MemoryStream();
wk.Write(memoryStream);
return File(memoryStream.ToArray(), "application/vnd.ms-excel", " Trip-Log.xls");
}