export Excel to DataTable using NPOI

export datatable to excel c#
c# export to excel
excel to datatable c#
convert datatable to xlsx c#
export to excel in c# web api
import and export excel in asp net core 2.0 razor pages
blazor export to excel
npoi word examples c#

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 ...

Here's about the minimum code you can use to convert an Excel file to a DataSet using NPOI:

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

ISheet sheet = workbook.GetSheetAt(0); // zero-based index of your target sheet
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;
    DataRow dataRow = dt.NewRow();
    dataRow.ItemArray = row.Cells.Select(c => c.ToString()).ToArray();
    dt.Rows.Add(dataRow);
}

Convert C# Datatable To Excel File Using NPOI DLL C#, Description: In this post, we will learn about how to export or convert DataTable data into excel file using NPOI in c#. First, we need to install  We are using NPOI library here, which will help to perform import and export operations. In order to see how to create a .NET Core Web Application with Razor Pages and retrieve data from SQL Server using Entity Framework, you can visit my previous article.

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;
    }
}

NPOI imports Excel to DataTable and exports , "How to export the List into an Excel file" in a generic way using NPOI you can make necessary changes like removing DataTable completely. The best thing I found using NPOI.dll is that it works for both Open Office and MS Excel. Below are the 9 steps we follow for Export to Excel using NPOI DLL. Create new Excel workbook. Create new Excel sheet. (Optional) set the width of the columns. Create a header row. Set the column names in the header row.

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;

}

Export to Excel using NPOI - C# and WEB API, Export to Excel Using NPOI DLL Library. public void WriteExcelWithNPOI(​String extension, DataTable dt). {. // dll refered NPOI.dll and NPOI. One can map the above output details to respective class objects using mapping logic of their choice. Export/Write the data to Excel file using NPOI. We shall now look at creating or writing the data to an Excel file. Here is sample data/object which we want to save it as an Excel file.

You can try this easy way through NPOI:

private DataTable GetDataTableFromExcel(String Path)
    {
        XSSFWorkbook wb;
        XSSFSheet sh;
        String Sheet_name;

        using (var fs = new FileStream(Path, FileMode.Open, FileAccess.Read))
        {
            wb = new XSSFWorkbook(fs);

               Sheet_name= wb.GetSheetAt(0).SheetName;  //get first sheet name
        }
        DataTable DT = new DataTable();
        DT.Rows.Clear();
        DT.Columns.Clear();

        // get sheet
        sh = (XSSFSheet)wb.GetSheet(Sheet_name);

        int i = 0;
        while (sh.GetRow(i) != null)
        {
            // add neccessary columns
            if (DT.Columns.Count < sh.GetRow(i).Cells.Count)
            {
                for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
                {
                    DT.Columns.Add("", typeof(string));
                }
            }

            // add row
            DT.Rows.Add();

            // write row value
            for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
            {
                var cell = sh.GetRow(i).GetCell(j);

                if (cell != null)
                {
                    // TODO: you can add more cell types capatibility, e. g. formula
                    switch (cell.CellType)
                    {
                        case NPOI.SS.UserModel.CellType.Numeric:
                            DT.Rows[i][j] = sh.GetRow(i).GetCell(j).NumericCellValue;
                            //dataGridView1[j, i].Value = sh.GetRow(i).GetCell(j).NumericCellValue;

                            break;
                        case NPOI.SS.UserModel.CellType.String:
                            DT.Rows[i][j] = sh.GetRow(i).GetCell(j).StringCellValue;

                            break;
                    }
                }
            }

            i++;
        }

        return DT;
    }

Export to Excel Using NPOI DLL Library, NPOI Library allows us to Read and Write Excel files using in . static string ReadExcel() { DataTable dtTable = new DataTable(); List<string> rowList = new List<string>(); Export/Write the data to Excel file using NPOI. We are using NPOI DLL for this export which is free to use, refer to NPOI NuGet for more details. More often, we need to develop an export to Excel feature in our applications, many of us usually create boring string builder, then convert it to Excel or use Interop or ITextSharp or NPOI or something else to achieve the same result.

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.

Export to Excel Using NPOI DLL Library, 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  In this article, I have posted the code for exporting to Excel from any data source. I have attached code and required dll in the attachment section. It is one of the ways to write with Excel and at the end of the article I posted links for the other ways to work with Excel.

Read and Write Excel file in .NET Core using NPOI, XLS for .NET, you only need two lines of core code to finish the import or export data from excel to datatable. One is to import an Excel file by the Load method:  In this article, we will see different ways to export data to Excel from a web application. It is very common task for web developers. Here are different approaches to do it in ASP.NET – C#: Approach 1: Using the Excel PIA (primary interop assemblies) to generate a spreadsheet server-side.

Import Data from Excel to Datatable, In this post we will learn about how to export or convert DataTable data into excel file using NPOI in c#. First we need to install these packages  I find NPOI very usefull for working with Excel Files, here is my implementation (Comments are in Spanish, sorry for that): This Method Opens an Excel (both xls or xlsx) file and converts it into a DataTable.

Export Data table to Excel file using NPOI DLL , Description: In this post, we will learn about how to export or convert DataTable data into excel file using NPOI in c#. First, we need to install these packages like NPOI, Newtonsoft for install these packages Nuget link added in this post. After those packages install we need to add some namespace for access NPOI classes and Newtonsoft for convert JSON to List. After all packages and

Comments
  • Does not work with different column sizes in different lines, so the " answered Sep 4 '15 at 9:19 LuckyS" code is better, if you have crazy excel files.
  • @Roger Deep is right, it will skip the NULL cells, so they will have different column index and will be placed into different column then.
  • Read Excel file and based on extension either xls or xlsx convert it to DataTable
  • Careful for excel files that have multiple columns, as this creates the datatable with a number of columns "for (var c = 0; c < colCount; c++) dtExcelTable.Columns.Add(headerRow.GetCell(c).ToString());" and then it has a for loop "for (var j = 0; j < currentRow.Cells.Count; j++)", so you might get exceptions if you have lines with multiple columns total.
  • This example works perfectly with a few changes to handle multiple different column totals. Thank you Rathi.
  • I didn't profile the code, but I think you should cache the currentRow.Cells.Count part, because the property Cells of a Row object creates a List<ICell> object from an inner Dictionary data on every single call. In fact, the property doesn't come from the original Apache POI in Java.
  • This also works very well and it has the "add neccessary columns" check that is absolutely essential for lines that have more columns than other lines. Thank you LuckyS, great code.
  • i tried it but i got Error: OfficeXMLFileException with instead of HSSF i should use XSSF, so i changed HSSFWorkbook to XSSFWorkbook but it doesn't work