This project is read-only.

.xlsx. Date cell import to DataTable

Feb 23, 2013 at 12:25 PM
Edited Feb 24, 2013 at 8:55 AM
Hi!
I'm tried to convert .xlsx file to DataTable format by using NPOI 2.0 library. It's OK, but I have a problem with convert to string date cell. When I try to use construction like row.GetCell(j).ToString() - it's threw exception "Cannot get numeric value from a text cell". I tried to use DateCellValue property, but it also threw this exception. With other cell formats it's work good.
Function, that I use it's:
private DataTable xlsxToDT(string fileName)
        {
            DataTable table = new DataTable();
            XSSFWorkbook workbook = new XSSFWorkbook(new FileStream(fileName, FileMode.Open, FileAccess.Read));
            ISheet sheet = workbook.GetSheetAt(0);
            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            int rowCount = sheet.LastRowNum;
            for (int i = (sheet.FirstRowNum); i < sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        //EXCEPTION GENERATING IN THIS CODE
                        dataRow[j] = row.GetCell(j).ToString();
                        ////////////////////////////
                    }
                }
                table.Rows.Add(dataRow);
            }
            workbook = null;
            sheet = null;
            return table;
        }
Please help me with my problem.

P.S. - sorry for my English :)
Mar 1, 2013 at 11:25 PM
ICell.ToString should not be rebused. You should determine the type of Cell first by ICell.CellType. Then use different property to get the values like NumericCellValue, DateCellValue and so on.
Mar 2, 2013 at 8:27 AM
No.
Problem was with a CurrentCulture. I'm from Russia, and with my culture this code don't work. Solution of my problem:
private DataTable xlsxToDT(string fileName)
{
    var prevCulture = Thread.CurrentThread.CurrentCulture;
    Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
    try
    {
        // Mine code here
    }
    finally
    {
        Thread.CurrentThread.CurrentCulture = prevCulture;
    }
}