0

I am using NPOI to read the excel spreadsheet .I am gettting the above mentioned error at code dr["Amount"] = row.GetCell(4). Could somebody tell me what the problem is. 

     XSSFWorkbook xssfwb;
            using (FileStream file = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
            {
                xssfwb = new XSSFWorkbook(file);
            }

            var sheet = xssfwb.GetSheetAt(0); // Change this to the worksheet you want to import.
            var rows = sheet.GetRowEnumerator();
            var dtExcelData = new DataTable();
            var linenumber = 0;
            DataRow dr;


            dtExcelData.Columns.AddRange(new DataColumn[3] { 
            new DataColumn("AccountNumber", typeof(string)),
            new DataColumn("Amount", typeof(decimal)),
            new DataColumn("Sedol",typeof(string)) });
        while (rows.MoveNext())
        {
            var row = (XSSFRow)rows.Current;
            linenumber++;



   if (row.GetCell(0) != null)
            {
                dr = dtExcelData.NewRow();
                dr["AccountNumber"] = row.GetCell(1).ToString();
                dr["Amount"] = row.GetCell(4);
                dr["Sedol"] = row.GetCell(11).ToString();

                dtExcelData.Rows.Add(dr);
            }
        }
user3751248
  • 303
  • 4
  • 8
  • 18
  • If `Amount` is a number and you're sure about its value why don't you use `row.GetCell(4).NumericCellValue`? – Adriano Repetti Jan 28 '15 at 22:22
  • I am geting error Cannot get a numeric value from a text cell – user3751248 Jan 28 '15 at 22:43
  • Then (if you can't set cell format to Number) you have to read it as string and parse it (as suggested by Shannon). Format may (also) be incorrect because of decimal separator (check what your culture info is and what you have inside your sheet). – Adriano Repetti Jan 28 '15 at 23:28
  • I set the datacolumn of databale column amount as string type and then parsed it as decimal as per shanon's suggestion but i am getting an errror Input string was not in correct format. – user3751248 Jan 28 '15 at 23:57
  • If you set to string why do you need to parse? I think one or the other. – Adriano Repetti Jan 29 '15 at 06:54

1 Answers1

1

Looks like you are trying to place an object into a decimal field. You might try

dr["Amount"] = decimal.parse(row.GetCell(4).toString());

to see if that fixes your error.

Shannon
  • 21
  • 3
  • Nope. I get an error Input string was not in the correct format. – user3751248 Jan 28 '15 at 22:34
  • Is the string a value that can be parsed or converted to a decimal format? You might try `Convert.ToDecimal(string) or Convert.ToDecimal(string,culture)` – Shannon Jan 30 '15 at 01:44