NPOI 2.0版本 如何读取Excel（2007）模板

FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
IWorkbook workbook = WorkbookFactory.Create(file);

总是报错：“NPOI.OpenXmlFormats.Spreadsheet.ThemeDocument”的类型初始值设定项引发异常。 ---> System.InvalidOperationException: 无法生成临时类(result=1)。
error CS0200: 无法对属性或索引器“NPOI.OpenXmlFormats.Dml.CT_RelativeRect.lSpecified”赋值 -- 它是只读的
error CS0200: 无法对属性或索引器“NPOI.OpenXmlFormats.Dml.CT_RelativeRect.tSpecified”赋值 -- 它是只读的
error CS0200: 无法对属性或索引器“NPOI.OpenXmlFormats.Dml.CT_RelativeRect.rSpecified”赋值 -- 它是只读的
error CS0200: 无法对属性或索引器“NPOI.OpenXmlFormats.Dml.CT_RelativeRect.bSpecified”赋值 -- 它是只读的
error CS0200: 无法对属性或索引器“NPOI.OpenXmlFormats.Dml.CT_ShapeProperties.bwModeSpecified”赋值 -- 它是只读的
error CS0200: 无法对属性或索引器“NPOI.OpenXmlFormats.Dml.CT_Transform2D.flipVSpecified”赋值 -- 它是只读的
error CS0200: 无法对属性或索引器“NPOI.OpenXmlFormats.Dml.CT_Transform2D.flipHSpecified”赋值 -- 它是只读的
error CS0200: 无法对属性或索引器“NPOI.OpenXmlFormats.Dml.CT_Transform2D.rotSpecified”赋值 -- 它是只读的

17 个解决方案

#1

FileStream file = new FileStream(@filePath, FileMode.Open, FileAccess.Read);
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);  试试这个可能行不行

#2

HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);

官网说 HSSFWorkbook是针对Excel2003版本的。在最新的2.0中,Workbookfactory会自动根据文件系统的类型返回HSSFWorkbook或XSSFWorkbook(针对2007)。但是我用Workbookfactory，如果模板是2003是没有错误的，一用2007的模板文件就报了上面的错误。

#3

2007 及以上的版本建议使用 openxml，不要用 NPOI

#4

这个问题我刚刚解决
 

  
 using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                xssfworkbook = new XSSFWorkbook(file);
            }


void ConvertToDataTable()
        {
            ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable();


            int t = 0;
            while (rows.MoveNext())
            {
                IRow row = (XSSFRow)rows.Current;
                int cellCount = row.LastCellNum;
               
                DataRow dr = dt.NewRow();

                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);

                    if (t == 0) 
                    {
                        DataColumn column = new DataColumn(cell.ToString());
                        dt.Columns.Add(column);
                    }
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();
                    }
                }
                dt.Rows.Add(dr);
                t++;
            }
            dataSet1.Tables.Add(dt);
        }

#5

好辛苦的那处理分享LZ都不理会

#6

感谢楼上的代码分享，NPOI终于读出了excel2008的数据。

#7

NPOI 2.0 导出大容量数据不好用

XSSFWorkbook对2007版本的EXCEL支持不够好，导出70000条数据时，用时超过了5分钟。

#8

LZ workbook你用workbookfactory创建,那么worksheet呢

#9

Aspose.Cell不用

#10

我用workbook = workbookfactory.create(fs)老报错

Non-negative number required.
Parameter name: value

#11

还在研究~~赶脚和1.2.5区别好大~~

#12

读取07时：错误 程序集“NPOI.OOXML, Version=2.0.1.0, Culture=neutral, PublicKeyToken=0df73ec7942b34e1”所使用的“NPOI, Version=2.0.1.0, Culture=neutral, PublicKeyToken=0df73ec7942b34e1”版本高于所引用的程序集“NPOI, Version=1.2.5.0, Culture=neutral, PublicKeyToken=0df73ec7942b34e1”的版本  求大神帮忙

#13

[e-mail]

#14

楼主你好。我也遇到这种问题了，请问你是怎么解决的呢。看到及时回复我好吗。多谢了！

#15

/// <summary>
        /// 读取Excels
        /// </summary>
        /// <param name="filePath">文件路劲</param>
        /// <returns></returns>
        public static DataTable ReadExcel(string filePath)
        {
            try
            {
                //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                IWorkbook workbook = WorkbookFactory.Create(fs);
                //获取excel的第一个sheet
                ISheet sheet = workbook.GetSheetAt(0);
                DataTable table = new DataTable();
                //获取sheet的首行
                IRow headerRow = sheet.GetRow(0);

                //一行最后一个方格的编号 即总的列数
                int cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }
                //最后一列的标号  即总的行数
                int rowCount = sheet.LastRowNum;

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

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

                    table.Rows.Add(dataRow);
                }

                workbook = null;
                sheet = null;
                return table;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

dll下载地址：http://npoi.codeplex.com/releases/view/115353

#16

楼主怎么解决的啊？

#17

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ReadExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            //2007及以上版本（xlsx）
            using (FileStream fsr = new FileStream("1.xlsx", FileMode.Open, FileAccess.Read))
            //2003 (xls)
           //using (FileStream fsr = new FileStream("2.xls", FileMode.Open, FileAccess.Read))
            {
                //获得工作薄
                IWorkbook wf = WorkbookFactory.Create(fsr);

                //工作薄下的第一张表，遍历所有行
                ISheet sheet = wf.GetSheetAt(0);
                for (int i = 0; i < sheet.LastRowNum + 1; i++)
                {
                    //获得行
                    IRow row = sheet.GetRow(i);
                    //遍历行内所有单元格
                    for (int j = 0; j < row.LastCellNum; j++)
                    {
                        object o = null;
                        //获取单元格
                        ICell cell = row.GetCell(j);
                        if (cell != null)
                        {
                            //判断单元格类型
                            switch (cell.CellType)
                            {
                                case CellType.Blank:
                                    o = cell.StringCellValue;
                                    break;
                                case CellType.Boolean:
                                    o = cell.BooleanCellValue;
                                    break;
                                case CellType.Error:
                                    break;
                                case CellType.Formula:
                                    break;
                                case CellType.Numeric:
                                    o = cell.NumericCellValue;
                                    break;
                                case CellType.String:
                                    o = cell.StringCellValue;
                                    break;
                                case CellType.Unknown:
                                    break;
                                default:
                                    break;
                            }
                        }
                        //控制台输出
                        Console.Write(o + "\t");
                    }
                    //换行
                    Console.WriteLine();
                } 

            }
            
            //程序停一会。
            Console.ReadKey();
        }
    }
}