读取Excel内容到DataTable

Our sky

已于 2022-03-23 16:42:30 修改

阅读量465

点赞数

分类专栏：后端文章标签： c# .net 开发语言

于 2022-03-16 15:49:46 首次发布

本文链接：https://blog.csdn.net/fei0700/article/details/123528262

版权

NPOI Excel DataTable 读取数据转换

关键词由CSDN通过智能技术生成

后端专栏收录该内容

15 篇文章 0 订阅

订阅专栏

读取Excel文件到DataSet中

using NPOI.SS.UserModel;
    /// <summary>
    /// 读取Excel文件到DataSet中
    /// </summary>
    /// <param name="filePath">文件路径</param>
    /// <returns></returns>
    private DataTable ExcelToDataTable(string filePath)
    {
        DataTable dt = new DataTable();

        HSSFWorkbook hssfworkbook;
        using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
        {
            hssfworkbook = new HSSFWorkbook(file);
        }
        ISheet sheet = hssfworkbook.GetSheetAt(0);
        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

        IRow headerRow = sheet.GetRow(0);
        int cellCount = headerRow.LastCellNum;

        for (int j = 0; j < cellCount; j++)
        {
            ICell cell = headerRow.GetCell(j);
            dt.Columns.Add(cell.ToString());
        }

        for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
        {
            IRow row = sheet.GetRow(i);
            DataRow dataRow = dt.NewRow();

            for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                if (row.GetCell(j) != null)
                    dataRow[j] = row.GetCell(j).ToString();
            }

            dt.Rows.Add(dataRow);
        }
        return dt;
    }