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

 

原文链接: https://www.cnblogs.com/Fred1987/p/11424963.html

欢迎关注

微信关注下方公众号,第一时间获取干货硬货;公众号内回复【pdf】免费获取数百本计算机经典书籍;

也有高质量的技术群,里面有嵌入式、搜广推等BAT大佬

    ExcelHelper based on NPOI

原创文章受到原创版权保护。转载请注明出处:https://www.ccppcoding.com/archives/402192

非原创文章文中已经注明原地址,如有侵权,联系删除

关注公众号【高性能架构探索】,第一时间获取最新文章

转载文章受原作者版权保护。转载请注明原作者出处!

(0)
上一篇 2023年4月19日 上午9:38
下一篇 2023年4月19日 上午9:38

相关推荐