247 Star 1.7K Fork 498

yuebon / YuebonCore

 / 详情

Excel帮助类优化

Done
Opened this issue  
2021-05-28 14:21

NPOIHelper.ImportExcel(string strFileName)
当excel后缀为xlsx版本时,读取失败,应该改成以下方案

        public static DataTable ImportExcel(string strFileName)
        {
            //如果是xls,使用HSSFWorkbook;如果是xlsx,使用XSSFWorkbook
            //https://blog.csdn.net/you23hai45/article/details/70196897
            int ii = strFileName.LastIndexOf(".");
            string filetype = strFileName.Substring(ii + 1, strFileName.Length - ii - 1);
            DataTable dt = new DataTable();
            ISheet sheet;
            if ("xlsx" == filetype)
            {
                XSSFWorkbook xssfworkbook;
                using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                {
                    xssfworkbook = new XSSFWorkbook(file);
                }
                sheet = xssfworkbook.GetSheetAt(0);
            }
            else
            {
                HSSFWorkbook hssfworkbook;
                using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
                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);
                if (row.GetCell(row.FirstCellNum) != null && row.GetCell(row.FirstCellNum).ToString().Length > 0)
                {
                    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;

        }

Comments (1)

BoosZhang created任务
BoosZhang set related repository to yuebon/YuebonCore
Expand operation logs
yuebon changed issue state from 待办的 to 已完成

Sign in to comment

Status
Assignees
Milestones
Pull Requests
Successfully merging a pull request will close this issue.
Branches
Planed to start   -   Planed to end
-
Top level
Priority
参与者(2)
1017224 yuebon 1600345795
C#
1
https://gitee.com/yuebon/YuebonNetCore.git
git@gitee.com:yuebon/YuebonNetCore.git
yuebon
YuebonNetCore
YuebonCore

Search