ExcelHelper based on NPOI

//Export data to excel via NPOI 

public static void ExportDataTableToExcel(DataTable dataTable, string excelFileName = null)
{
if (dataTable == null)
{
return;
}
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet firstSheet = workbook.CreateSheet();
IRow headerRow = firstSheet.CreateRow(0);
var dtColumns = dataTable.Columns;
var dtColumnsCount = dtColumns.Count;

for (int i = 0; i < dtColumnsCount; i++)
{
string headerName = dtColumns[i].ColumnName;
ICell headerCell = headerRow.CreateCell(i);
headerCell.SetCellValue(headerName);
}

for (int i = 0; i < dataTable.Rows.Count; i++)
{
var dataRow = firstSheet.CreateRow(i + 1);
for (int j = 0; j < dtColumnsCount; j++)
{
ICell dataCell = dataRow.CreateCell(j);
var cellValue = dataTable.Rows[i][j];
dataCell.SetCellValue(cellValue?.ToString());
}
}

for (int i = 0; i < dtColumnsCount; i++)
{
firstSheet.AutoSizeColumn(i);
}

if (string.IsNullOrEmpty(excelFileName))
{
excelFileName = Directory.GetCurrentDirectory() + DateTime.Now.ToString("yyyyMMddmmssffff") + ".xlsx";
}
using (FileStream excelStream = new FileStream(excelFileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(excelStream);
}
}

 

 

 

//Convert excel file to DataTable 

 public static DataTable ConvertExcelToDataTable(string excelFile)
        {
            if(string.IsNullOrEmpty(excelFile))
            {
                return new DataTable();
            }
            IWorkbook workbook;
            DataTable dt = new DataTable();
            using (FileStream excelStream = new FileStream(excelFile, FileMode.Open,FileAccess.Read))
            {
                workbook = new XSSFWorkbook(excelStream);
            }

            ISheet firstSheet = workbook.GetSheetAt(0);
            var columnsCount = firstSheet.GetRow(0).LastCellNum;
            for(int i=0;i<columnsCount;i++)
            {
                dt.Columns.Add();
            } 
            for(int i=1;i<=firstSheet.LastRowNum;i++)
            {
                IRow workbookRow = firstSheet.GetRow(i);
                List<string> stringList = new List<string>();
                for (int j = 0; j < columnsCount; j++)
                {
                    string cellValue = workbookRow.GetCell(j).StringCellValue;
                    stringList.Add(cellValue);
                }

                dt.Rows.Add(stringList.ToArray());
            }
            return dt;
        }