C# Creating Excel Sheet HSSFWorkbook data not populating cor

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 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"); 
        }

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Rahul Prasad10y ago
Not sure what's wrong with your code, but you can use Spire.XLS to process excel data from code, which is an easy API allowing you to process Excel file with few lines of code.
http://www.nuget.org/packages/Spire.XLS/
0