（018）c# excel 导入为 DataTable

.卡

已于 2024-04-27 10:07:13 修改

阅读量937

点赞数

分类专栏： C# 文章标签： NPIO

于 2020-12-18 18:26:36 首次发布

本文链接：https://blog.csdn.net/WGYHAPPY/article/details/111387221

版权

C# 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

文章目录

安装 NPOI
代码

安装 NPOI

在这里插入图片描述

代码

/// <summary>
/// 读取excel。标题行必须有！
/// </summary>
/// <param name="fileName">excel文档路径</param>
/// <param name="sheetIndex">sheet</param>
/// <param name="withHeader">是否有标题行</param>
/// <returns></returns>
public static DataTable ReadExcelToDataTable(string fileName, int sheetIndex)
{
    DataTable dt = new DataTable();

    using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
    {
        IWorkbook workbook;
        workbook = WorkbookFactory.Create(file);
        
        ISheet sheet = workbook.GetSheetAt(0);
        if (sheet == null)
        {
            return dt;
        }
        
        IRow headerRow = sheet.GetRow(sheetIndex);
        if (headerRow == null)
        {
            return dt;
        }
        
        for (int j = 0; j < headerRow.LastCellNum; j++)
        {
            ICell cell = headerRow.GetCell(j);
            dt.Columns.Add(cell.ToString());
        }    
        
        for (int i = 1; i <= sheet.LastRowNum; i++)
        {
            IRow row = sheet.GetRow(i);
            DataRow dataRow = dt.NewRow();

            for (int j = row.FirstCellNum; j < headerRow.LastCellNum; j++)
            {
                if (row.GetCell(j) != null)
                {
                    dataRow[j] = row.GetCell(j).ToString();    
                }
            }
            dt.Rows.Add(dataRow);
        }
    }

    return dt;
}