iot/projects/Infrastructure/Office/ExcelReader.cs

using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.Globalization;
using System.IO;

namespace Infrastructure.Office
{
    public class ExcelReader : IExcelReader
    {
        public DataSet GetDataSet(Stream stream)
        {
            var workBook = new XSSFWorkbook(stream);
            using var dataSet = new DataSet();
            foreach (var sheet in workBook.GetSheets())
            {
                var dataTable = dataSet.Tables.Add(sheet.SheetName);
                if (sheet.LastRowNum > 0)
                {
                    for (int r = 0; r <= sheet.LastRowNum; r++)
                    {
                        var row = sheet.GetRow(r);
                        var dataRow = dataTable.Rows.Add();
                        for (int c = 0; c < row.LastCellNum; c++)
                        {
                            var cell = row.GetCell(c);
                            if (cell != null)
                            {
                                while (dataTable.Columns.Count < c + 1)
                                {
                                    dataTable.Columns.Add().ColumnName = string.IsNullOrEmpty(cell.GetValue().ToString()) ? (c + 1).ToString(CultureInfo.InvariantCulture) : cell.GetValue().ToString();
                                }
                                dataRow[c] = cell.GetValue();
                            }
                        }
                    }
                }
            }
            return dataSet;
        }

        public byte[] GetFile(DataSet dataSet)
        {
            if (dataSet is null)
            {
                throw new ArgumentNullException(nameof(dataSet));
            }
            var workBook = new XSSFWorkbook();
            using MemoryStream ms = new MemoryStream();
            if (dataSet.Tables.Count > 0)
            {
                for (int i = 0; i < dataSet.Tables.Count; i++)
                {
                    var table = dataSet.Tables[i];
                    var sheet = workBook.CreateSheet(table.TableName);
                    if (table.Rows.Count > 0)
                    {
                        var sheethead = sheet.CreateRow(0);
                        for (int h = 0; h < table.Columns.Count; h++)
                        {
                            sheethead.CreateCell(h).SetCellValue(table.Columns[h].ColumnName);
                        }
                        for (int j = 0; j < table.Rows.Count; j++)
                        {
                            var tableRow = table.Rows[j];
                            if (tableRow.ItemArray.Length > 0)
                            {
                                var sheetRow = sheet.CreateRow(j + 1);
                                for (int k = 0; k < tableRow.ItemArray.Length; k++)
                                {
                                    if (table.Columns[k].DataType == typeof(int))
                                    {
                                        var cell = sheetRow.CreateCell(k, NPOI.SS.UserModel.CellType.Numeric);
                                        cell.SetCellValue(Convert.ToInt32(tableRow[k].ToString(), CultureInfo.InvariantCulture));
                                    }
                                    else
                                    {
                                        var cell = sheetRow.CreateCell(k, NPOI.SS.UserModel.CellType.String);
                                        cell.SetCellValue(tableRow[k].ToString());
                                    }
                                }
                            }
                        }
                    }
                }
            }
            else
            {
                workBook.CreateSheet();
            }
            workBook.Write(ms);
            return ms.ToArray();
        }
    }
}