export Excel to DataTable using NPOI

Question

I want to read Excel Tables 2010 xlsx using NPOI and then export data to DataTables but don't know how to use it. Can anyone show me step by step how to export Excel to Datatable? I have downloaded NPOI.dll, added to reference but don't know what further ...

Answer 1

private static ISheet GetFileStream(string fullFilePath)
{
    var fileExtension = Path.GetExtension(fullFilePath);
    string sheetName;
    ISheet sheet = null;
    switch (fileExtension)
    {
        case ".xlsx":
            using (var fs = new FileStream(fullFilePath, FileMode.Open, FileAccess.Read))
            {
                var wb = new XSSFWorkbook(fs);
                sheetName = wb.GetSheetAt(0).SheetName;
                sheet = (XSSFSheet) wb.GetSheet(sheetName);
            }
            break;
        case ".xls":
            using (var fs = new FileStream(fullFilePath, FileMode.Open, FileAccess.Read))
            {
                var wb = new HSSFWorkbook(fs);
                sheetName = wb.GetSheetAt(0).SheetName;
                sheet = (HSSFSheet) wb.GetSheet(sheetName);
            }
            break;
    }
    return sheet;
}

private static DataTable GetRequestsDataFromExcel(string fullFilePath)
{
    try
    {
        var sh = GetFileStream(fullFilePath);
        var dtExcelTable = new DataTable();
        dtExcelTable.Rows.Clear();
        dtExcelTable.Columns.Clear();
        var headerRow = sh.GetRow(0);
        int colCount = headerRow.LastCellNum;
        for (var c = 0; c < colCount; c++)
            dtExcelTable.Columns.Add(headerRow.GetCell(c).ToString());
        var i = 1;
        var currentRow = sh.GetRow(i);
        while (currentRow != null)
        {
            var dr = dtExcelTable.NewRow();
            for (var j = 0; j < currentRow.Cells.Count; j++)
            {
                var cell = currentRow.GetCell(j);

                if (cell != null)
                    switch (cell.CellType)
                    {
                        case CellType.Numeric:
                            dr[j] = DateUtil.IsCellDateFormatted(cell)
                                ? cell.DateCellValue.ToString(CultureInfo.InvariantCulture)
                                : cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
                            break;
                        case CellType.String:
                            dr[j] = cell.StringCellValue;
                            break;
                        case CellType.Blank:
                            dr[j] = string.Empty;
                            break;
                    }
            }
            dtExcelTable.Rows.Add(dr);
            i++;
            currentRow = sh.GetRow(i);
        }
        return dtExcelTable;
    }
    catch (Exception e)
    {
        throw;
    }
}

Answer 2

You can accomplish you task by doing this.

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.Util.Collections;
using NPOI;
using System.Collections.Generic;
using NPOI.OpenXmlFormats.Spreadsheet;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;

public DataTable xlsxToDT(Stream str)
{
XSSFWorkbook hssfworkbook = new XSSFWorkbook(str);
ISheet sheet = hssfworkbook.GetSheetAt(0);
str.Close();

DataTable dt = new DataTable();
IRow headerRow = sheet.GetRow(0);
IEnumerator rows = sheet.GetRowEnumerator();

int colCount = headerRow.LastCellNum;
int rowCount = sheet.LastRowNum;

for (int c = 0; c < colCount; c++)
    dt.Columns.Add(headerRow.GetCell(c).ToString());

while (rows.MoveNext())
{
    IRow row = (XSSFRow)rows.Current;
    DataRow dr = dt.NewRow();

    for (int i = 0; i < colCount; i++)
    {
        ICell cell = row.GetCell(i);

        if (cell != null)
            dr[i] = cell.ToString();
    }
    dt.Rows.Add(dr);
}
return dt;

}

Answer 3

I edited @Saeb Amini code to allow blank cells.

IWorkbook workbook;
using (FileStream stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
{
    workbook = new HSSFWorkbook(stream);
}

ISheet sheet = workbook.GetSheetAt(0);
DataTable dt = new DataTable(sheet.SheetName);

// write header row
IRow headerRow = sheet.GetRow(0);
foreach (ICell headerCell in headerRow)
{
    dt.Columns.Add(headerCell.ToString());
}

// write the rest
int rowIndex = 0;
foreach (IRow row in sheet)
{
     // skip header row
     if (rowIndex++ == 0) continue;

     // add row into datatable
     var cells = new List<ICell>();
     for (int i = 0; i < dt.Columns.Count; i++)
     {
          cells.Add(row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK));
     }

     // Columns formatted as DateTime will be printed as '01-Jul-2005', 
     // which can be converted to datetime in the SQL server.
     // select cast('01-Jul-2005' as DateTime).
     // In SQL Server we can convert DateTime to whatever string we want, for example
     // select convert(nvarchar(255), cast('01-Jul-2005' as datetime), 112) will print '20050701'.
     // http://www.sqlusa.com/bestpractices/datetimeconversion/
     dt.Rows.Add(cells.Select(c => c.ToString()).ToArray());

     // Datetimes also can be reformatted directly like this :
     // dt.Rows.Add(cells.Select(c =>
     //    c.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(c) 
     //        ? c.DateCellValue.ToString("yyyyMMdd") 
     //        : c.ToString()
     // ).ToArray());

}

//return dt;

Answer 4

NPOI is a great and free way to read Excel files, and now in version 2 you can read both XLS and XLSX file types.

1. Get the latest version of NPOI at their website: https://npoi.codeplex.com/
2. In your project add references to the following files: NPOI.dll, NPOI.OOXML.dll and NPOI.OpenXml4Net.dll.
3. Add the following Usings: using NPOI.SS.UserModel; using NPOI.HSSF.UserModel; using NPOI.XSSF.UserModel;

Now the following code does the trick, comments are in spanish, sorry for that :-p

private DataTable Excel_To_DataTable(string pRutaArchivo, int pHojaIndex)
    {
        // --------------------------------- //
        /* REFERENCIAS:
         * NPOI.dll
         * NPOI.OOXML.dll
         * NPOI.OpenXml4Net.dll */
        // --------------------------------- //
        /* USING:
         * using NPOI.SS.UserModel;
         * using NPOI.HSSF.UserModel;
         * using NPOI.XSSF.UserModel; */
        // --------------------------------- //
        DataTable Tabla = null;
        try
        {
            if (System.IO.File.Exists(pRutaArchivo))
            {

                IWorkbook workbook = null;  //IWorkbook determina se es xls o xlsx              
                ISheet worksheet = null;
                string first_sheet_name = "";

                using (FileStream FS = new FileStream(pRutaArchivo, FileMode.Open, FileAccess.Read))
                {
                    workbook = WorkbookFactory.Create(FS); //Abre tanto XLS como XLSX
                    worksheet = workbook.GetSheetAt(pHojaIndex); //Obtener Hoja por indice
                    first_sheet_name = worksheet.SheetName;  //Obtener el nombre de la Hoja

                    Tabla = new DataTable(first_sheet_name);
                    Tabla.Rows.Clear();
                    Tabla.Columns.Clear();

                    // Leer Fila por fila desde la primera
                    for (int rowIndex = 0; rowIndex <= worksheet.LastRowNum; rowIndex++)
                    {
                        DataRow NewReg = null;
                        IRow row = worksheet.GetRow(rowIndex);
                        IRow row2 = null;

                        if (row != null) //null is when the row only contains empty cells 
                        {
                            if (rowIndex > 0) NewReg = Tabla.NewRow();

                            //Leer cada Columna de la fila
                            foreach (ICell cell in row.Cells)
                            {
                                object valorCell = null;
                                string cellType = "";

                                if (rowIndex == 0) //Asumo que la primera fila contiene los titlos:
                                {
                                    row2 = worksheet.GetRow(rowIndex + 1); //Si es la rimera fila, obtengo tambien la segunda para saber los tipos:
                                    ICell cell2 = row2.GetCell(cell.ColumnIndex);
                                    switch (cell2.CellType)
                                    {
                                        case CellType.Boolean: cellType = "System.Boolean"; break;
                                        case CellType.String: cellType = "System.String"; break;
                                        case CellType.Numeric:
                                            if (HSSFDateUtil.IsCellDateFormatted(cell2)) { cellType = "System.DateTime"; }
                                            else { cellType = "System.Double"; }        break;
                                        case CellType.Formula:
                                            switch (cell2.CachedFormulaResultType)
                                            {
                                                case CellType.Boolean: cellType = "System.Boolean"; break;
                                                case CellType.String: cellType = "System.String"; break;
                                                case CellType.Numeric:
                                                    if (HSSFDateUtil.IsCellDateFormatted(cell2)) { cellType = "System.DateTime"; }
                                                    else { cellType = "System.Double"; }    break;
                                            }
                                            break;
                                        default:
                                            cellType = "System.String"; break;
                                    }

                                    //Agregar los campos de la tabla:
                                    DataColumn codigo = new DataColumn(cell.StringCellValue, System.Type.GetType(cellType));
                                    Tabla.Columns.Add(codigo);
                                }
                                else
                                {
                                    //Las demas filas son registros:
                                    switch (cell.CellType)
                                    {
                                        case CellType.Blank:    valorCell = DBNull.Value; break;
                                        case CellType.Boolean:  valorCell = cell.BooleanCellValue; break;
                                        case CellType.String:   valorCell = cell.StringCellValue; break;
                                        case CellType.Numeric:
                                            if (HSSFDateUtil.IsCellDateFormatted(cell)) { valorCell = cell.DateCellValue; }
                                            else { valorCell = cell.NumericCellValue; } break;
                                        case CellType.Formula:
                                            switch (cell.CachedFormulaResultType)
                                            {
                                                case CellType.Blank:    valorCell = DBNull.Value; break;
                                                case CellType.String:   valorCell = cell.StringCellValue; break;
                                                case CellType.Boolean:  valorCell = cell.BooleanCellValue; break;
                                                case CellType.Numeric:
                                                    if (HSSFDateUtil.IsCellDateFormatted(cell)) { valorCell = cell.DateCellValue; }
                                                    else { valorCell = cell.NumericCellValue; }
                                                    break;
                                            }
                                            break;                                          
                                        default: valorCell = cell.StringCellValue; break;
                                    }
                                    NewReg[cell.ColumnIndex] = valorCell;
                                }
                            }
                        }
                        if (rowIndex > 0) Tabla.Rows.Add(NewReg);
                    }
                    Tabla.AcceptChanges();
                }
            }
            else
            {
                throw new Exception("ERROR 404: El archivo especificado NO existe.");
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return Tabla;
    }

The above code assumes the first row of the sheet has the column names. The code also determines the data Type of each cell and tries to convert it to an ADO data type. Blank cells are converted to null.

Hope this helps you and others in the same situation.

Answer 5

On Codeplex website here in Download section there is example package - a pack of C# examples. Try it, if you haven't yet.

This is simplest example of it -

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

//.....

private void button1_Click(object sender, EventArgs e)
{
    HSSFWorkbook hssfwb;
    using (FileStream file = new FileStream(@"c:\test.xls", FileMode.Open, FileAccess.Read))
    {
        hssfwb= new HSSFWorkbook(file);
    }

    ISheet sheet = hssfwb.GetSheet("Arkusz1");
    for (int row = 0; row <= sheet.LastRowNum; row++)
    {
        if (sheet.GetRow(row) != null) //null is when the row only contains empty cells 
        {
            MessageBox.Show(string.Format("Row {0} = {1}", row, sheet.GetRow(row).GetCell(0).StringCellValue));
        }
    }
}  

Answer 6

You can try ToxySpreadsheet in Toxy project - toxy.codeplex.com. ToxySpreadsheet.ToDataSet() is what you want.