ExcelHelper based on NPOI

转载于 2019-08-28 16:46:00 发布 · 114 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

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

本文详细介绍使用NPOI库将DataTable数据导出至Excel文件的方法，并提供了将Excel文件转换为DataTable的代码实现。通过具体代码示例，读者可以学习如何创建Excel工作簿，设置单元格值，以及调整列宽等操作。

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