You must Sign In to post a response.
  • Category: .NET

    C# Creating Excel Sheet HSSFWorkbook data not populating correctly

    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.CellStyle = cellRowStyle;
    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

    {	//strBuildFullToAddress
    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;//2
    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();
    //Populating header works fine
    sheet.GetRow(rowType).GetCell(0).SetCellValue(filterStrs[3].ToString()); // Insurance Type
    sheet.GetRow(rowTenant).GetCell(0).SetCellValue(filterStrs[0].ToString()); // Provider Name
    sheet.GetRow(rowDriver).GetCell(3).SetCellValue(filterStrs[2].ToString()); // Driver Name
    sheet.GetRow(rowTenant).GetCell(3).SetCellValue(filterStrs[1].ToString()); // Ride Dates

    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.CreateCell(colPickupPerson, CellType.STRING);
    ////hCell.CellStyle = secondDataRow.GetCell(colPickupPerson).CellStyle; //template_row
    ////hCell.SetCellValue(!String.IsNullOrEmpty(record.CustomerName) ? record.CustomerName : "Customer name");//(!String.IsNullOrEmpty(hCell.RichStringCellValue.ToString ));//(record.CustomerName) ? record.CustomerName : "Test Name");

    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);
    }
    { //This row works fine without any problems
    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");
    }
    {//strBuildFullToAddress
    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");
    }
  • #763187
    ????????????? ??????????????????????


Sign In to post your comments