Convert XLSX file to DataTable via NPOI

static DataTable ReadExcelConvertToDataTable(string xlsxFileName)
        {
            DataTable dt = new DataTable();
            try
            {
                IWorkbook book; 
                using (FileStream fs = new FileStream(xlsxFileName, FileMode.Open, FileAccess.Read))
                {
                    book = new XSSFWorkbook(fs);
                    ISheet st = book.GetSheetAt(0);
                    int rowsCount = st.LastRowNum; 
                    DataRow dtHeadersRow = dt.NewRow();
                    int colsCount = st.GetRow(0).Cells.Count();

                    var firstRow = st.GetRow(0);
                    for (int i = 0; i < colsCount; i++)
                    {
                        string colName = firstRow.GetCell(i).StringCellValue;
                        DataColumn dc = new DataColumn(colName);
                        dt.Columns.Add(colName);
                    }
                    dt.Rows.Add(dtHeadersRow);
                    for (int i = 0; i <= rowsCount; i++)
                    {
                        DataRow dr = dt.NewRow();                       
                        StringBuilder rowBuilder = new StringBuilder();
                        for (int j = 0; j < colsCount; j++)
                        {
                            ICell cell = st.GetRow(i).GetCell(j);
                            string cellValue = string.Empty;
                            if (cell != null)
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.String:
                                        cellValue = cell.StringCellValue;
                                        break;
                                    case CellType.Numeric:
                                        cellValue = cell.NumericCellValue.ToString();
                                        break;
                                }
                            }
                            dr[j] = cellValue;
                        }
                        dt.Rows.Add(dr);                        
                    }
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.TargetSite);
                return dt;                
            }
            return dt;
        }

 

posted @ 2020-06-26 19:51  FredGrit  阅读(134)  评论(0编辑  收藏  举报