using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Com.StellmanGreene.PubMed
{
using System.Data;
using System.Globalization;
using System.IO;
using System.Threading;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
///
/// Helper class to read XLS and XLSX files using NPOI
///
class NpoiHelper
{
///
/// Use NPOI to read an XLS or XLSX file into a datatable
///
///
///
///
///
public static DataTable ReadExcelFileToDataTable(string folder, string filename)
{
ISheet sheet;
using (FileStream input = File.OpenRead(folder + "\\" + filename))
{
if (filename.EndsWith(".xlsx"))
{
XSSFWorkbook workbook = new XSSFWorkbook(input);
sheet = workbook.GetSheetAt(0);
}
else
{
HSSFWorkbook workbook = new HSSFWorkbook(input);
sheet = workbook.GetSheetAt(0);
}
}
DataTable dataTable = ConvertISheetToDataTable(sheet);
return dataTable;
}
///
/// Read the contents an NPOI ISheet into a new DataTable object
///
/// ISheet to read
/// DataTable populated with the contents of the ISheet
private static DataTable ConvertISheetToDataTable(ISheet sheet)
{
// Temporarily set the thread culture to avoid conversion issues
// http://stackoverflow.com/questions/15040567/c-xlsx-date-cell-import-to-datatable-by-npoi-2-0
var prevCulture = Thread.CurrentThread.CurrentCulture;
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
try
{
DataTable table = new DataTable();
int rowCount = sheet.LastRowNum;
int colCount = 0;
IRow headerRow = sheet.GetRow(sheet.FirstRowNum);
foreach (ICell cell in headerRow.Cells)
{
string columnName = cell.ToString();
DataColumn column = new DataColumn(columnName);
table.Columns.Add(column);
colCount++;
}
for (int rowNum = (sheet.FirstRowNum) + 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow row = sheet.GetRow(rowNum);
DataRow dataRow = table.NewRow();
int cellNumber = 0;
for (int colNum = 0; colNum < colCount; colNum++)
{
ICell cell = row.GetCell(colNum);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.BOOLEAN:
dataRow[cellNumber] = cell.BooleanCellValue;
break;
case CellType.NUMERIC:
case CellType.FORMULA:
dataRow[cellNumber] = cell.NumericCellValue;
break;
default:
dataRow[cellNumber] = cell.StringCellValue;
break;
}
}
cellNumber++;
}
table.Rows.Add(dataRow);
}
return table;
}
finally
{
Thread.CurrentThread.CurrentCulture = prevCulture;
}
}
}
}