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;
}
UPDATE: If I insert code like
row.GetCell(j).SetCellType(CellType.STRING);
in problem cell I have value like "36496.392581018517". Another cells converted correctly
Second column of your excel file has a date format (12/2/1999
). This format is not recognized by NPOI in your current culture ("ru-RU"). This seems like a bug in NPOI, becuase when this happens, there is no way to read anything from that cell. The only way I came to, is to change the thread's culture before reading the excel file (and change it back after):
private DataTable xlsxToDT(string fileName)
{
var prevCulture = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
try
{
// Put your whole method body here.
}
finally
{
Thread.CurrentThread.CurrentCulture = prevCulture;
}
}