CODEWITHSUNDEEP
c#exceldatatablenpoi
Uni Le
Uni Le

Reputation: 793

export Excel to DataTable using NPOI

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

Upvotes: 18

Views: 41601

Answers (11)

Tony Qu
Tony Qu

Reputation: 756

You can try ToxySpreadsheet in Toxy project - https://github.com/nissl-lab/toxy. ToxySpreadsheet.ToDataSet() is what you want.

Upvotes: 1

Mohammed Dawood Ansari
Mohammed Dawood Ansari

Reputation: 885

I know I am a little late here but I think it may help others

I have developed an excel utility with the use of the NPOI package, which can

  1. Simply takes your data table or the collection
  2. And Returns you excel while maintaining all the data table/list data type intact in the excel.

Github Code repo.: https://github.com/ansaridawood/.NET-Generic-Excel-Export-Sample/tree/master/GenericExcelExport/ExcelExport

Looking for a code explanation, you can find it here: https://www.codeproject.com/Articles/1241654/Export-to-Excel-using-NPOI-Csharp-and-WEB-API

It uses NPOI DLL and it has 2 cs files to include and then you are good to go

Below is the first file for reference AbstractDataExport.cs:

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;

namespace GenericExcelExport.ExcelExport
{
    public interface IAbstractDataExport
    {
        HttpResponseMessage Export(List exportData, string fileName, string sheetName);
    }

    public abstract class AbstractDataExport : IAbstractDataExport
    {
        protected string _sheetName;
        protected string _fileName;
        protected List _headers;
        protected List _type;
        protected IWorkbook _workbook;
        protected ISheet _sheet;
        private const string DefaultSheetName = "Sheet1";

        public HttpResponseMessage Export
              (List exportData, string fileName, string sheetName = DefaultSheetName)
        {
            _fileName = fileName;
            _sheetName = sheetName;

            _workbook = new XSSFWorkbook(); //Creating New Excel object
            _sheet = _workbook.CreateSheet(_sheetName); //Creating New Excel Sheet object

            var headerStyle = _workbook.CreateCellStyle(); //Formatting
            var headerFont = _workbook.CreateFont();
            headerFont.IsBold = true;
            headerStyle.SetFont(headerFont);

            WriteData(exportData); //your list object to NPOI excel conversion happens here

            //Header
            var header = _sheet.CreateRow(0);
            for (var i = 0; i < _headers.Count; i++)
            {
                var cell = header.CreateCell(i);
                cell.SetCellValue(_headers[i]);
                cell.CellStyle = headerStyle;
            }

            for (var i = 0; i < _headers.Count; i++)
            {
                _sheet.AutoSizeColumn(i);
            }

            using (var memoryStream = new MemoryStream()) //creating memoryStream
            {
                _workbook.Write(memoryStream);
                var response = new HttpResponseMessage(HttpStatusCode.OK)
                {
                    Content = new ByteArrayContent(memoryStream.ToArray())
                };

                response.Content.Headers.ContentType = new MediaTypeHeaderValue
                       ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                response.Content.Headers.ContentDisposition = 
                       new ContentDispositionHeaderValue("attachment")
                {
                    FileName = $"{_fileName}_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"
                };

                return response;
            }
        }

        //Generic Definition to handle all types of List
        public abstract void WriteData(List exportData);
    }
}

and this the second and final file AbstractDataExportBridge.cs:

using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text.RegularExpressions;

namespace GenericExcelExport.ExcelExport
{
    public class AbstractDataExportBridge : AbstractDataExport
    {
        public AbstractDataExportBridge()
        {
            _headers = new List<string>();
            _type = new List<string>();
        }

        public override void WriteData<T>(List<T> exportData)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));

            DataTable table = new DataTable();

            foreach (PropertyDescriptor prop in properties)
            {
                var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                _type.Add(type.Name);
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? 
                                  prop.PropertyType);
                string name = Regex.Replace(prop.Name, "([A-Z])", " $1").Trim(); //space separated 
                                                                           //name by caps for header
                _headers.Add(name);
            }

            foreach (T item in exportData)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }

            IRow sheetRow = null;

            for (int i = 0; i < table.Rows.Count; i++)
            {
                sheetRow = _sheet.CreateRow(i + 1);
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    ICell Row1 = sheetRow.CreateCell(j);

                    string type = _type[j].ToLower();
                    var currentCellValue = table.Rows[i][j];

                    if (currentCellValue != null && 
                        !string.IsNullOrEmpty(Convert.ToString(currentCellValue)))
                    {
                        if (type == "string")
                        {
                            Row1.SetCellValue(Convert.ToString(currentCellValue));
                        }
                        else if (type == "int32")
                        {
                            Row1.SetCellValue(Convert.ToInt32(currentCellValue));
                        }
                        else if (type == "double")
                        {
                            Row1.SetCellValue(Convert.ToDouble(currentCellValue));
                        }
                    }
                    else
                    {
                        Row1.SetCellValue(string.Empty);
                    }
                }
            }
        }
    }
}

For a detailed explanation, refer link provided in the beginning.

Upvotes: 0

nercan
nercan

Reputation: 207

You can use nuget package for NOPI

ReadExcel

Basic usage: ExcelImportHelper.ReadExcel(bytes);

Upvotes: 0

Saeb Amini
Saeb Amini

Reputation: 24400

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

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

var sheet = workbook.GetSheetAt(0); // zero-based index of your target sheet
var dataTable = new DataTable(sheet.SheetName);

// write the header row
var headerRow = sheet.GetRow(0);
foreach (var headerCell in headerRow)
{
    dataTable.Columns.Add(headerCell.ToString());
}

// write the rest
for(int i = 1; i< sheet.PhysicalNumberOfRows; i++)
{
    var sheetRow = sheet.GetRow(i);
    var dtRow = dataTable.NewRow();
    dtRow.ItemArray = dataTable.Columns
        .Cast<DataColumn>()
        .Select(c => sheetRow.GetCell(c.Ordinal, MissingCellPolicy.CREATE_NULL_AS_BLANK).ToString())
        .ToArray();
    dataTable.Rows.Add(dtRow);
}

Upvotes: 22

yu yang Jian
yu yang Jian

Reputation: 7171

Base on @Sean 's GREAT Answer and example of NPOI, I write the mothod to Convert all worksheets in xls file to DataSet and Convert DataSet back to xls file : 

    public static DataSet GetDataSetFromXls(string excelFilePath)
    {
        IWorkbook workbook;
        using (FileStream stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
        {
            workbook = new HSSFWorkbook(stream);  //2003 xls
            //workbook = new XSSFWorkbook();  //2007 xlsx
        }

        DataSet ds = new DataSet();
        for (int i = 0; i < workbook.NumberOfSheets; i++)
        {
            ISheet sheet = workbook.GetSheetAt(i); // 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);
            }

            ds.Tables.Add(dt);
        }

        return ds;
    }


    public static void SaveDataSetToXls(DataSet ds, string savedExcelFilePath)
    {
        //IWorkbook workbook = new XSSFWorkbook();
        IWorkbook workbook = new HSSFWorkbook();

        foreach (DataTable dt in ds.Tables)
        {
            ISheet sheet = workbook.CreateSheet(dt.TableName);

            var row0 = sheet.CreateRow(0);//header
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                row0.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName);
            }

            for (int i = 0; i < dt.Rows.Count; i++)//rest
            {
                var row = sheet.CreateRow(1+i);
                for (int j = 0; j < dt.Columns.Count; j++)
                    row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
            }
        }

        FileStream sw = File.Create(savedExcelFilePath);
        workbook.Write(sw);
        sw.Close();
    }

Upvotes: 1

Muflix
Muflix

Reputation: 6778

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;

Upvotes: 2

Shailesh B. Rathi
Shailesh B. Rathi

Reputation: 111

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

Upvotes: 5

BlueMystic
BlueMystic

Reputation: 2287

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.

Upvotes: 3

LuckyS
LuckyS

Reputation: 553

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

Upvotes: 4

WeaselBr
WeaselBr

Reputation: 116

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;

}

Upvotes: 2

Rohit Vats
Rohit Vats

Reputation: 81253

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

Upvotes: 1

Related Questions