Link to home
Create AccountLog in
Avatar of Sirdots
Sirdots

asked on

Mvc: How to handle null values

I am reading the content of an excel file and adding it to a collection.

[HttpPost]
        public ActionResult Index(HttpPostedFileBase file)
        {
            const int startRow = 1;

            if (file != null && Path.GetExtension(file.FileName) == ".xlsx")
            {
                IList<PersonalData> exampleDataList = new List<PersonalData>();
                using(var excel = new ExcelPackage(file.InputStream))
                {
                    //open and read the xlsx file.                  
                        //Get the work book in the file
                    ExcelWorkbook workBook = excel.Workbook;
                        if (workBook != null)
                        {
                            if (workBook.Worksheets.Count > 0)
                            {
                                //Get the first worksheet
                                ExcelWorksheet currentWorkSheet = workBook.Worksheets.First();
                 

                                for (int rowNumber = startRow + 1; rowNumber <= currentWorkSheet.Dimension.End.Row; rowNumber++)
                                // read each row from the start of the data (start row + 1 header row) to the end of the spreadsheet.
                                {
                                    object col1Value = currentWorkSheet.Cells[rowNumber, 1].Value;
                                    object col2Value = currentWorkSheet.Cells[rowNumber, 2].Value;
                                    object col3Value = currentWorkSheet.Cells[rowNumber, 3].Value;
                                    object col4Value = currentWorkSheet.Cells[rowNumber, 4].Value;

                                    if ((col1Value != null) && (col2Value != null)) && (col3Value != null)) && (col4Value != null))
                                    {
                                        exampleDataList.Add(new PersonalData {firstname = col1Value.ToString(),lastname = col2Value.ToString(), address=col3Value.ToString(), salary=col4Value.ToString() });
                                    }
                                 
                                }

                                int myresultcount = WriteToDb(exampleDataList);
                            }

                        }                  
                   
                }
            }
                     
            return RedirectToAction("Index");        
        }
            

public class PersonalData
    {
        public int Id { get; set; }
        public string  firstname { get; set; }
        public string lastname { get; set; }
        public string address { get; set; }
        public string salary { get; set; }
    }


Here is the error message I am getting:  object reference not set to an instance of an object


It blows up on this line.

exampleDataList.Add(new PersonalData {firstname = col1Value.ToString(),lastname = col2Value.ToString(), address=col3Value.ToString(), salary=col4Value.ToString() });

Am I doing something wrong here?
Avatar of Beartlaoi
Beartlaoi
Flag of United States of America image

Try using .Value2 instead on those four lines.
object col1Value = currentWorkSheet.Cells[rowNumber, 1].Value2;
object col2Value = currentWorkSheet.Cells[rowNumber, 2].Value2;
object col3Value = currentWorkSheet.Cells[rowNumber, 3].Value2;
object col4Value = currentWorkSheet.Cells[rowNumber, 4].Value2;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of YZlat
YZlat
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Sirdots
Sirdots

ASKER

What does value2 imply? It came up with an error

Error      1      
'OfficeOpenXml.ExcelRange' does not contain a definition for 'Value2'
and no extension method 'Value2' accepting a first argument of type
'OfficeOpenXml.ExcelRange'
could be found (are you missing a using directive or an assembly reference?)
Which version of Excel are you referrencing?