Answer

C# Creating Excel Sheet HSSFWorkbook data not populating cor

Andrew Mystery

503

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) ? strBuildFul         lToAddress : 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"); 
        }