Excel reader return null value with no reason c#

Question

I've got a little problem with my code use to read values in excel file. I use a loop to read line per line and cells per cells.

The 152 first rows in excel file reads correctly but after the line 153, I get only null values for each cells.

This is my code :

public DataTable parseExcelFile(string path,bool firstLineHeader)
{
    DataTable values = new DataTable("Impayés");
    Excel.Application appExcel = new Excel.Application();
    Excel.Workbook wb = appExcel.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
    Excel.Worksheet sheet = (Excel.Worksheet)wb.Sheets["Feuil1"];
    Excel.Range excelRange = sheet.UsedRange;
    bool premiere = firstLineHeader;
    foreach (Excel.Range row in excelRange.Rows)
    {
        if (!premiere)
        {
            int rowNumber = row.Row;//Récupération du numéro de ligne
            //Récupération des valeurs des cellules de la ligne en cours
            string groupe = Convert.ToString(row.Range["A" + rowNumber.ToString()].Value);
            if (groupe == null)
                MessageBox.Show("Eh c'est vide ! (Ligne "+ rowNumber.ToString() + ")");
            string sousgroupe = Convert.ToString(row.Range["B" + rowNumber.ToString()].Value);
            string codeapporteur = Convert.ToString(row.Range["C" + rowNumber.ToString()].Value);
            string apporteur = Convert.ToString(row.Range["D" + rowNumber.ToString()].Value);
            string dossier = Convert.ToString(row.Range["E" + rowNumber.ToString()].Value);
            string siren = Convert.ToString(row.Range["F" + rowNumber.ToString()].Value);
            string client = Convert.ToString(row.Range["G" + rowNumber.ToString()].Value);
            string adresse = Convert.ToString(row.Range["H" + rowNumber.ToString()].Value);
            string localite = Convert.ToString(row.Range["I" + rowNumber.ToString()].Value);
            string cp = Convert.ToString(row.Range["J" + rowNumber.ToString()].Value);
            string ville = Convert.ToString(row.Range["K" + rowNumber.ToString()].Value);
            string telephone = string.Format("{0:00}{1:00}{2:00}{3:00}{4:00}", row.Range["L" + rowNumber.ToString()].Value, row.Range["M" + rowNumber.ToString()].Value,row.Range["N" + rowNumber.ToString()].Value,row.Range["O" + rowNumber.ToString()].Value,row.Range["P" + rowNumber.ToString()].Value);
            string etat = Convert.ToString(row.Range["Q" + rowNumber.ToString()].Value);
            string bic = Convert.ToString(row.Range["R" + rowNumber.ToString()].Value);
            string iban = Convert.ToString(row.Range["S" + rowNumber.ToString()].Value);
            RIB rib = new RIB(bic, iban);
            string domiciliationrib = Convert.ToString(row.Range["T" + rowNumber.ToString()].Value);
            string bienfinance = Convert.ToString(row.Range["U" + rowNumber.ToString()].Value);
            decimal mt_ht_fi = Convert.ToDecimal(row.Range["V" + rowNumber.ToString()].Value);
            decimal mt_ech = Convert.ToDecimal(row.Range["W" + rowNumber.ToString()].Value);
            string dateapp = Convert.ToString(row.Range["X" + rowNumber.ToString()].Value) + "/" + Convert.ToString(row.Range["Y" + rowNumber.ToString()].Value) + "/" + Convert.ToString(row.Range["Z" + rowNumber.ToString()].Value);
            string terme = Convert.ToString(row.Range["AA" + rowNumber.ToString()].Value);
            string premiereech = Convert.ToString(row.Range["AB" + rowNumber.ToString()].Value) + "/" + Convert.ToString(row.Range["AC" + rowNumber.ToString()].Value) + "/" + Convert.ToString(row.Range["AD" + rowNumber.ToString()].Value);
            string derniereech = Convert.ToString(row.Range["AE" + rowNumber.ToString()].Value) + "/" + Convert.ToString(row.Range["AF" + rowNumber.ToString()].Value) + "/" + Convert.ToString(row.Range["AG" + rowNumber.ToString()].Value);
            int nbimp = MyConverter.ToInt(Convert.ToString(row.Range["AH" + rowNumber.ToString()].Value));
            decimal mt_imp_1 = Convert.ToDecimal(row.Range["AI" + rowNumber.ToString()].Value);
            string dateimpaye1 = Convert.ToString(row.Range["AJ" + rowNumber.ToString()].Value) + "/" + Convert.ToString(row.Range["AK" + rowNumber.ToString()].Value) + "/" + Convert.ToString(row.Range["AL" + rowNumber.ToString()].Value);
            int motifimpaye1 = MyConverter.ToInt(Convert.ToString(row.Range["AM" + rowNumber.ToString()].Value));
            string dateimpaye2 = Convert.ToString(row.Range["AN" + rowNumber.ToString()].Value) + "/" + Convert.ToString(row.Range["AO" + rowNumber.ToString()].Value) + "/" + Convert.ToString(row.Range["AP" + rowNumber.ToString()].Value);
            int motifimpaye2 = MyConverter.ToInt(Convert.ToString(row.Range["AQ" + rowNumber.ToString()].Value));
            string dateimpaye3 = Convert.ToString(row.Range["AR" + rowNumber.ToString()].Value) + "/" + Convert.ToString(row.Range["AS" + rowNumber.ToString()].Value) + "/" + Convert.ToString(row.Range["AT" + rowNumber.ToString()].Value);
            int motifimpaye3 = MyConverter.ToInt(Convert.ToString(row.Range["AU" + rowNumber.ToString()].Value));
            DataRow dr = values.Rows.Add(groupe, sousgroupe, codeapporteur, apporteur, dossier, siren, client, adresse, localite, cp, ville, telephone, etat, bic, iban, domiciliationrib, bienfinance, mt_ht_fi, mt_ech, dateapp, terme, premiereech, derniereech, nbimp, mt_imp_1, dateimpaye1, motifimpaye1, dateimpaye2, motifimpaye2, dateimpaye3, motifimpaye3);
        }
        else
        {
            foreach (Excel.Range eRange in row.Range["A1:AU1"].Cells)
            {
                if (eRange.Value != "TEL2" && eRange.Value != "TEL3" && eRange.Value != "TEL4" && eRange.Value != "TEL5" && eRange.Value != "Col_21" && eRange.Value != "Col_22" && eRange.Value != "Col_25" && eRange.Value != "Col_26" && eRange.Value != "Col_28" && eRange.Value != "Col_29")
                    values.Columns.Add(eRange.Value);
            }
            premiere = false;
        }
    }
    return values;
}

And this is the example lines of my excel file :

I don't know why it's not working in your code, but can I suggest that instead of looping over each row, you gather all the data in a single line (i.e. object[,] values = excelRange.Value;), then loop over that instead? It will be much faster. — steveo40
– steveo40, Commented Mar 15, 2017 at 14:48
things to try: convert from xls to xlsx. open a blank xlsx and paste everything from the original into a blank sheet without formatting. sometimes excel files are corrupt, especially old xls ones. btw. which number of rows would the usedrange show at runtime? — Cee McSharpface
– Cee McSharpface, Commented Mar 15, 2017 at 15:25
@dlatikay Yes, I put it to know when the problem happen + it's xlsx file — Thomas Rollet
– Thomas Rollet, Commented Mar 15, 2017 at 15:25

user3685427 · Accepted Answer · 2017-03-15 15:44:19Z

1

Try deleting that row 152, or move it up or down in the document, and see if the error follows that row.

If you move that row to 145, and then the program stops at row 145, then we found the source of the problem: it's the data. There is probably an invalid or unreadable character in that row, and it crashes the program. It would be most likely to be a letter in one of the rows that should be a decimal or int.

Also, you are instantiating all your strings, decimals, and ints each time you run the loop. You should declare them all before the loop, then set their values inside the loop. This is a general good practice.

answered Mar 15, 2017 at 15:44

user3685427

2192 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thomas Rollet Over a year ago

Same problem if I move the row to 145 the program crash again at line 153

user3685427 Over a year ago

Interesting. Maybe if you move row 153 as well. Sorry I didn't mention it in my original post, but the problem could be in the later row as well - it just depends on where in the process it is crashing. The slow debugging solution is to put a breakpoint inside the loop and set it with the condition to only stop when it's on iteration 152 or 153 (it will take a try or two to determine which line causes the crash). Then move the breakpoint earlier and later in the loop to determine the code segment that causes the crash.

Thomas Rollet Over a year ago

Same problem if I remove row 153 and 154 and 155, breakpoint don't show any problem, the reader just return null...

Collectives™ on Stack Overflow

Excel reader return null value with no reason c#

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest