WHY Datatable add vaule have IndexOutOfRangeException in the c #

Question

Raw data (EXCEL FILE)

1TEST101    TEST1   YOYO    2015/11/25 1:38 PM
1TEST102    TEST2   YOYO    2015/11/23 9:17 PM
1TEST103    TEST3   YOYO    2015/11/23 9:15 PM
1TEST104    TEST4   YOYO    2015/12/10 6:13 PM
1TEST105    TEST5   YOYO    2015/12/10 2:29 PM
1TEST106    TEST6   YOYO    2015/12/11 11:03 AM
1TEST107    TEST7   YOYO    2015/12/2 8:50 AM
1TEST108    TEST8   YOYO    2015/12/12 12:58 PM
1TEST109    TEST9   YOYO    2015/12/2 8:51 AM
1TEST110    TEST10  YOYO    2015/12/10 2:29 PM
1TEST111    TEST11  YOYO    2015/11/20 8:44 AM
1TEST112    TEST12  YOYO    2015/11/20 8:45 AM
1TEST113    TEST13  YOYO    2015/11/20 8:47 AM
1TEST114    TEST14  YOYO    2015/11/20 8:47 AM
1TEST115    TEST15  YOYO    2015/11/20 8:49 AM
1TEST116    TEST16  YOYO    2015/11/20 9:15 AM

[CODE]

public static DataTable ReadExcelAsTableNPOI(string fileName)
{
    using (FileStream fs = new FileStream(fileName, FileMode.Open))
    {
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        ISheet sheet = (HSSFSheet)wb.GetSheetAt(0);
        DataTable table = new DataTable();
        table.Columns.Add(new DataColumn("Id"));
        table.Columns.Add(new DataColumn("Desc"));
        table.Columns.Add(new DataColumn("USER"));
        table.Columns.Add(new DataColumn("Date"));
        IRow headerRow = sheet.GetRow(0);

        for (int K = (sheet.FirstRowNum); K <= sheet.LastRowNum; K++)
        {
            IRow row = sheet.GetRow(K);
            DataRow dataRow = table.NewRow();

            if (row == null) continue;

            table.Rows.Add(dataRow[K]); // when loop at 5 time , it  have the IndexOutOfRangeException  problem

            for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                if (row.GetCell(j) != null)
                {
                    row.GetCell(j).SetCellType(CellType.String);
                    dataRow[j] = row.GetCell(j) == null ? "0" : row.GetCell(j).StringCellValue; ;
                    string Cellvalue = dataRow[j].ToString();
                    table.Rows[K][j] = Cellvalue;
                }
            }
        }

        return table;
    }
}

I use NPOI to read excel to datatable , but when the program run at table.Rows.Add(dataRow[K]);

it will show "Cannot find column 4." problem.

how to fix this problem, thank you.

jm.gunter · Answer 1 · 2016-09-12T16:31:11.263

0

It looks like your indexing is incorrect. When you call table.Rows.Add(datarow[K]) you are trying to add the Kth element of dataRow as a row in table. You've setup your table to only have 4 columns so when you try to access the 5th column (K=4) you are getting an error.

edited Sep 12 '16 at 16:31

answered Sep 12 '16 at 13:45

jm.gunter

57
6

score 0 · Answer 2 · answered Sep 12 '16 at 16:23

You're trying to add the object from datarow, not the whole row here:

table.Rows.Add(dataRow[K]); // when loop at 5 time , it  have the IndexOutOfRangeException problem

This should be replaced with

table.Rows.Add(dataRow);

IndexOutOfRangeException is caused by the K index value - as the dataRow has only 4 columns, it fails to get 5-th element of array.

WHY Datatable add vaule have IndexOutOfRangeException in the c #

2 Answers2