NPOIコンポーネントを使用してexcelデータを読み込む

コードを共有し、excelデータをDataTableに読み込みます.

using System;

using System.Collections.Generic;

using System.Data;

using System.IO;

using System.Linq;

using System.Text;

using NPOI.SS.UserModel;

using NPOI.XSSF.UserModel;



namespace ConsoleApp.Utility

{

    public class ExcelBuilder

    {

        /// <summary>

        /// exact excel data into DataTable

        /// </summary>

        /// <param name="excel">excel file name</param>

        /// <param name="index">sheet index </param>

        /// <param name="header"> the first row in excel whether belongs the columns</param>

        /// <returns>DataTable</returns>

        public static DataTable ToDataTable(string excel,int index, bool header)

        {

            DataTable dt = new DataTable(Path.GetFileNameWithoutExtension(excel) + "_Sheet" + index);

            IWorkbook workbook;

            using (FileStream file = new FileStream(excel, FileMode.Open, FileAccess.Read))

            {

                workbook = new XSSFWorkbook(file);

            }

            ISheet sheet = workbook.GetSheetAt(index);

            var rows = sheet.GetRowEnumerator();



            rows.MoveNext();

            IRow row = (XSSFRow)rows.Current;

            

            for (int i = 0; i < row.LastCellNum; i++)

            {

                ICell cell = row.GetCell(i);

                string columnName = header ? cell.StringCellValue : i.ToString();

                dt.Columns.Add(columnName, typeof(string));

            }

            if (!header)

            {

                DataRow first = dt.NewRow();

                for (int i = 0; i < row.LastCellNum; i++)

                {

                    ICell cell = row.GetCell(i);

                    first[i] = cell.StringCellValue;

                }

                dt.Rows.Add(first);

            }

           

            while (rows.MoveNext())

            {

                row = (XSSFRow)rows.Current;

                DataRow dataRow = dt.NewRow();

               

                for (int i = 0; i < row.LastCellNum; i++)

                {

                    ICell cell = row.GetCell(i);

                    dataRow[i] = cell.StringCellValue;

                }

                dt.Rows.Add(dataRow);

            }

            

            return dt;

        }

    }

}

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }