c# - exportar Excel a DataTable usando NPOI

Question

Quiero leer Excells 2010 xlsx usando NPOI y luego exportar datos a DataTables pero no sé cómo usarlo. ¿Alguien puede mostrarme paso a paso cómo exportar Excel a Datatable? He descargado NPOI.dll, agregado a la referencia, pero no sé qué más ...

Answer 1

En el sitio web de Codeplex aquí en la sección de Descargas hay un paquete de ejemplos: un paquete de ejemplos de C #. Pruébalo, si aún no lo has hecho.

Este es el ejemplo más simple de eso

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 2

Puede probar ToxySpreadsheet en el proyecto Toxy - toxy.codeplex.com. ToxySpreadsheet.ToDataSet () es lo que quieres.

Answer 3

Usted puede cumplir su tarea haciendo esto.

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 4

Puede intentarlo de esta manera fácil a través de 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;
    }

Answer 5

NPOI es una forma excelente y gratuita de leer archivos de Excel, y ahora en la versión 2 puede leer los tipos de archivos XLS y XLSX.

1. Obtenga la última versión de NPOI en su sitio web: https://npoi.codeplex.com/
2. En su proyecto, agregue referencias a los siguientes archivos: NPOI.dll, NPOI.OOXML.dll y NPOI.OpenXml4Net.dll.
3. Agregue los siguientes usos: usando NPOI.SS.UserModel; usando NPOI.HSSF.UserModel; usando NPOI.XSSF.UserModel;

Ahora el siguiente código es el truco, los comentarios están en español, lo siento por eso :-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;
    }

El código anterior asume que la primera fila de la hoja tiene los nombres de las columnas. El código también determina el tipo de datos de cada celda e intenta convertirlo a un tipo de datos ADO. Las celdas en blanco se convierten a nulas.

Espero que esto te ayude a ti y a los demás en la misma situación.

Answer 6

Aquí encontrará el código mínimo que puede usar para convertir un archivo de Excel a un DataSet usando 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);
}

Answer 7

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