NPOI를 사용하여 Excel을 데이터 테이블로 내보내기

NPOI를 사용하여 Excel을 데이터 테이블로 내보내기

NPOI를 사용하여 Excel Tables 2010 xlsx를 읽고 데이터를 DataTables로 내보내고 싶지만 사용 방법을 모르겠습니다.엑셀을 데이터 테이블로 내보내는 방법을 단계별로 보여줄 수 있는 사람이 있습니까?NPOI.dll을 다운로드하여 참조에 추가했지만 추가 정보가 무엇인지는 모르겠습니다...

다음은 NPOI를 사용하여 Excel 파일을 데이터 세트로 변환하는 데 사용할 수 있는 최소 코드입니다.

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

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를 통해 다음과 같은 쉬운 방법을 시도할 수 있습니다.

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

NPOI는 Excel 파일을 자유롭게 읽을 수 있는 훌륭한 방법이며 버전 2에서는 XLS와 XLSX 파일 형식을 모두 읽을 수 있습니다.

1. 웹 사이트 https://npoi.codeplex.com/ 에서 NPOI의 최신 버전을 확인하십시오.
2. 프로젝트에서 NPOI.dll, NPOI.OOXML.dll 및 NPOI 파일에 대한 참조를 추가합니다.OpenXml4Net.dll.
3. NPOI 사용을 추가합니다.SS.사용자 모델, NPOI 사용.HSSF.사용자 모델, NPOI 사용.XSSF.사용자 모델

이제 다음 코드가 트릭을 합니다. 댓글은 스페인어로 되어 있습니다. 죄송합니다. :-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;
    }

위의 코드는 시트의 첫 번째 행에 열 이름이 있다고 가정합니다.또한 코드는 각 셀의 데이터 유형을 결정하고 이를 ADO 데이터 유형으로 변환하려고 합니다.빈 셀이 null로 변환됩니다.

이것이 당신과 같은 상황에 있는 다른 사람들에게 도움이 되기를 바랍니다.

이렇게 하면 작업을 수행할 수 있습니다.

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;

}

저는 빈 셀을 허용하도록 @Saeb Amini 코드를 편집했습니다.

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;

여기 다운로드 섹션의 Codeplex 웹사이트에는 다음이 있습니다.example packageC# 예문 한 묶음아직 안 해봤으면 한번 해보세요.

이것은 그것의 가장 간단한 예입니다.

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

@Sean의 GREAT Answer와 NPOI의 예를 바탕으로, 저는 다음과 같은 방법을 씁니다.Convert all worksheets in xls file to DataSet그리고.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();
    }

Toxy 프로젝트 - https://github.com/nissl-lab/toxy 에서 Toxy 스프레드시트를 사용해 볼 수 있습니다.Toxy 스프레드시트.ToDataSet()이 필요합니다.

NOPI에 대해 nuget 패키지를 사용할 수 있습니다.

엑셀 읽기

기본 사용:Excel 가져오기 도우미.읽기 엑셀(바이트);

제가 여기서 조금 늦었다는 것은 알지만 다른 사람들에게 도움이 될 수도 있다고 생각합니다.

저는 NPOI 패키지를 사용하여 엑셀 유틸리티를 개발했습니다.

1. 데이터 테이블 또는 데이터 수집 작업을 간편하게 수행
2. Excel의 모든 데이터 테이블/목록 데이터 유형을 그대로 유지하면서 Excel을 반환합니다.

Github 코드 repo.https://github.com/ansaridawood/ .NET-Generic-Excel-Export-샘플/트리/마스터/GenericExcel내보내기/Excel내보내기

코드 설명을 찾으려면 https://www.codeproject.com/Articles/1241654/Export-to-Excel-using-NPOI-Csharp-and-WEB-API 에서 확인할 수 있습니다.

그것은 NPOI DLL을 사용하고 포함할 2개의 cs 파일이 있고 당신은 가도 좋습니다.

다음은 참조용 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);
    }
}

그리고 이것은 두 번째이자 마지막 파일인 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);
                    }
                }
            }
        }
    }
}

자세한 설명은 처음에 제공된 링크를 참조하십시오.

언급URL : https://stackoverflow.com/questions/13333413/export-excel-to-datatable-using-npoi