C#读取Excel的五种方式体会(三)

  • A+
所属分类:C#

C#读取Excel的五种方式体会(三) - 逐梦科学 - CSDN博客

C#读取Excel的五种方式体会(三)

2014年07月25日 15:35:05 __孤剑__独舞 阅读数 14377

上一篇文章中,采用微软的sdk,来操作,总是感觉用别人的工具要受制于人,既然我们知道了Excel的存储方式,问题便转换成从xml中取出数据,然后放入内存得到我们想要的东西,更重要的是,官方的sdk对xml的读取采用的Document的方式,对于大文件xml执行速度必然降低,同时对内存,数据量都有限制,若有几十亿,好几百T的数据,这种处理方式就很难发挥作用了,所以我们决定自己做一个sdk出来,只是将原来读取xml的方式,改为用xmlReader的方式来读取,采用文件流的方式进行处理。

就不需要将整个的xml文件树完全的读进去,而是按照流的方式进行处理,按照节点一个一个的读取,速度就会更进一步,并且不受数据量以及内存的限制。

网上关于这样的处理的文章较多,大概整理了一下,但是都不全面,在处理的时候还会遇到很多的问题,网上给的代码也只是一个思路,碰到bug,还需要自己去更多了解Excel到xml的转换过程到底是怎么样的,而本文将站在巨人的肩膀上,告诉你,应该注意的细节。首先还是转载下前人的文档,然后在对文档中的问题,进行介绍,注意这些问题是必须要注意的,不然会耗费你大量的时间却找不到自己想要的结果!

最近开发的一个网页查询的项目中,客户提供的数据是多个 Excel 2007 文件,这些文件都很大,有的有十几万行(注意:Excel 2003 文件不能超过 65,536 行)。这些 Excel 2007 文件需要定期批量转换为网页程序可以读取的专用二进制格式文件。我们知道,Microsoft Office System 2007 引入了一个新的文件格式:Office Open XML 格式。她是基于 XML 和 ZIP 归档技术创建的,可以使用任何平台的能够处理 XML 或者 ZIP 文件的工具来访问并且修改文档内容。所以我们就可以使用 Microsoft .NET Framework 2.0 的强大 XML 类库来读取 Excel 2007 文件并转换为网页程序所需的专用二进制格式文件。当然,也可以使用 System.IO.Packaging 名称空间中的类库,但是她位于 .NET Framework 3.0 SDK (WinFX) 的 WindowsBase.dll 中。微软网站上有几篇很有用的文章:“Office (2007) Open XML 文件格式简介”和“如何操作 Office Open XML 格式文档”。

文章出处:http://blog.csdn.net/ddxkjddx/article/details/6060030
下面,就来看看 Excel 2007 Open XML 文件的结构吧:

C#读取Excel的五种方式体会(三)

上图是一个名为 test1.xlsx 的 Excel 2007 文件。我没有 Office 2007 软件,只有正版的 Office 2003 软件。所以需要到微软网站下载一个“Microsoft Office Word、Excel 和 PowerPoint 2007 文件格式兼容包”,就可以在 Office 2003 中编辑 Office Open XML 文档了。test1.xlsx 文件其实是一个 zip 文件。为了分析其结构,我们现在把她解压到 D:/Test/test1/ 目录下。第一个重要的文件是 xl/workbook.xml,如下图所示:

C#读取Excel的五种方式体会(三)

该文件中的每个“”元素都代表 Excel 2007 文件中的一个工作表,工作表的名称就是其“name”属性的值,在上图中是“好人”和“坏人”。然后根据“”元素“r:id”属性的值(如上图中的“rId1”)到 xl/_rels/workbook.xml.rels 文件中寻找相应工作表数据实际存放的 xml 文件,如下图所示:

C#读取Excel的五种方式体会(三)

从图中可以看中,第一个工作表“好人”的数据实际存放在 worksheets/sheet1.xml 文件中,该文件的内容如下图所示:

C#读取Excel的五种方式体会(三)

上图中的“”元素的“ref”属性的值(“B2:C4”)表示该工作表的范围。“”元素表示工作的数据,其子元素“”表示工作表中的一行,“”的子元素“”表示该行中的单元格。如果“”元素有“t”属性的话,“”元素的子元素“”的值就是各工作表共享的字符串的索引。否则的话,“”元素的值就是该单元格的值。各工作表共享的字符串存放在 xl/sharedStrings.xml 文件中,如下图所示:

C#读取Excel的五种方式体会(三)

上图中,“”元素的子元素“”就代表了共享的字符串,其值就是“”元素的子元素“”的值。

下面就看看我们的测试程序吧:

C#读取Excel的五种方式体会(三)

源程序的整体结构如下图所示:

C#读取Excel的五种方式体会(三)

我们先看看 XlsxFile.cs 吧:  using System;
  using System.IO;
  using System.Xml;
  using Skyiv.Ben.Common;

namespace Skyiv.OfficeHelper
  {
    ///

    /// Excel 2007 文件
   ///

   sealed partial class XlsxFile : IDisposable
   {
     string fileName; // Excel 2007 文件的文件名
     Sheet[] sheets;  // Excel 2007 文件的各工作表

FileStream fileStream { get { return new FileStream(fileName, FileMode.Open, FileAccess.Read); } }

///

     /// Excel 2007 文件的构造函数
     ///

     ///Excel 2007 文件的文件名      public XlsxFile(string fileName)
     {
       this.fileName = fileName;
     }

///

     /// Excel 2007 文件的各工作表
     ///

     public Sheet[] Sheets
     {
       get
       {
         if (sheets == null)
         {

using (Stream zs = Zip.GetZipInputStream(fileStream, "xl/workbook.xml"))

{
             // xl/workbook.xml 文件的内容举例如下:
             //
             //  
             //    
             //    
             //  

             //
             XmlDocument xmlDocument = new XmlDocument();
             xmlDocument.Load(zs);

XmlNodeList elms = xmlDocument.DocumentElement["sheets"].ChildNodes;

sheets = new Sheet[elms.Count];
             for (int i = 0; i < elms.Count; i++)
             {
               XmlAttributeCollection attrs = elms[i].Attributes;

sheets[i] = new Sheet(attrs["name"].Value, GetXmlFileName(attrs["r:id"].Value), SharedStrings, fileStream);

}
           }
         }
         return sheets;
       }
    }

///

     /// 根据“标识”给出表示工作表的 XML 文件名
     ///

     ///标识      /// 表示工作表的 XML 文件名
     string GetXmlFileName(string id)
     {
       string value;

using (Stream zs = Zip.GetZipInputStream(fileStream, "xl/_rels/workbook.xml.rels"))

{
         // xl/_rels/workbook.xml.rels 文件的内容举例如下:
         //
         //  
         //  
         //

         XmlDocument xmlDocument = new XmlDocument();
         xmlDocument.Load(zs);

value = XmlHelper.GetElementById(xmlDocument, id).Attributes["Target"].Value;

}
       return value;
     }

public void Dispose()
     {
       if (sheets == null) return;
       foreach (Sheet sheet in sheets) sheet.Dispose();
     }
   }
 }

该程序已经有很详细的注释了。在该程序中用 XmlDocument 类来读 xl/workbook.xml 文件和 xl/_rels/workbook.xml.rels 文件,是因为这两个文件都是非常小的文件。然后再来看看 XlsxFile.SharedStrings.cs 吧:

using System;
  using System.IO;
  using System.Xml;
  using Skyiv.Ben.Common;

namespace Skyiv.OfficeHelper
  {
    partial class XlsxFile
    {
     string[] sharedStrings;

///

     /// Excel 2007 文件中各工作表共享的字符串
     ///

     string[] SharedStrings
     {
       get
       {
         if (sharedStrings == null)
         {
           Stream zs = null;
          try
           {

zs = Zip.GetZipInputStream(fileStream, "xl/sharedStrings.xml"); // 可能引发(FileNotFoundException)

// xl/sharedStrings.xml 文件的内容举例如下:
             //

//   任盈盈

//  
             //
             using (XmlReader reader = XmlReader.Create(zs))
             {
               while (reader.Read()) if (reader.IsStartElement("sst")) break;

sharedStrings = new string[Convert.ToInt32(reader.GetAttribute("uniqueCount"))];

for (int count = 0; ; count++)
               {

reader.Read();                                               // 执行后(reader)的值: or

if (!reader.IsStartElement("si")) break;

reader.ReadStartElement("si");                               // 执行后(reader)的值:  or

sharedStrings[count] = reader.ReadElementString("t").Trim(); // 执行后(reader)的值: or <与同级的元素>

if (reader.NodeType != XmlNodeType.EndElement) reader.ReadToNextSibling("t"); // 执行后(reader)的值:

}
             }
           }
           catch (FileNotFoundException)
           {
             sharedStrings = new string[0]; // 如果没有找到 xl/sharedStrings.xml 文件
           }
           finally
           {
             if (zs != null) zs.Close();
           }
         }
         return sharedStrings;
       }
     }
   }
 }

这下必须用 XmlReader 类来读取 xl/sharedStrings.xml 了,因为这个 xml 文件可能很大。最后是 XlsxFile.Sheet.cs 了:

using System;
   using System.IO;
   using System.Xml;
   using System.Collections.Generic;
   using Skyiv.Ben.Common;

namespace Skyiv.OfficeHelper
   {
     partial class XlsxFile
    {
      ///

      /// Execl 2007 文件中的工作表
      ///

      public sealed class Sheet : IDisposable
      {
        string[] sharedStrings; // 各工作表共享的字符串
        Stream stream;          // 用于读取本工作表的文件流
        XmlReader reader;       // 用于读取本工作表的 XML 数据读取器
        string dimension;       // 本工作表的范围,如:“A1”、“B2:C4”
        int rowCount;           // 本工作表的有效行数
        string name;            // 本工作表的名称

public string Dimension { get { return dimension; } }
        public int RowCount { get { return rowCount; } }
        public string Name { get { return name; } }

///

        /// 表示 Excel 2007 文件中的工作表的类的构造函数
        ///

        ///本工作表的名称         ///工作表的 XML 文件名         ///各工作表共享的字符串         ///表示整个 Excel 2007 文件的流

public Sheet(string name, string fileName, string[] sharedStrings, Stream fileStream)

{
          this.name = name;
          this.sharedStrings = sharedStrings;
          stream = Zip.GetZipInputStream(fileStream, "xl/" + fileName);
          reader = XmlReader.Create(stream);
          while (reader.Read()) if (reader.IsStartElement("dimension")) break;

dimension = reader.GetAttribute("ref"); // 本工作表的范围:

rowCount = GetRowCount(dimension); // 根据工作表的范围计算有效行数
          while (reader.Read()) if (reader.IsStartElement("sheetData")) break;
        }

///

        /// 读取本工作表的中一行
        ///

        /// 读取的行的各字段的内容。如果已经没有可读的行则返回 null。
        public string[] ReadRow()
        {
          // 表示工作表的 XML 文件(如:xl/worksheets/sheet1.xml)的内容举例如下:
         //
          //  
          //  
          //    
          //    
          //      
          //       1
          //    

          //  

          //

          // 注意:在该 XML 文件中可能省略某些空行和空单元格,而本方法忽略这些空行和空单元格。
          // 但本方法不忽略 XML 文件中的空行“”和空单元格“”。
          if (!reader.IsStartElement("row")) reader.Read();
          if (!reader.IsStartElement("row")) return null; // 没有可读的行
          List list = new List();
          for (; ; )
          {

reader.Read(); // 执行后(reader)的值: or or (other for )

if (!reader.IsStartElement("c")) break; // 没有可读的单元格
            if (reader.IsEmptyElement) list.Add(""); // 空单元格“
            else                                     // “1
            {

string attr = reader.GetAttribute("t"); // 如果“”元素的“t”属性不为空,则“”元素的值指向各工作表共享的字符串

reader.ReadStartElement("c");                            // 执行后(reader)的值: or

list.Add(GetValue(attr, reader.ReadElementString("v"))); // 执行后(reader)的值: or <与同级的元素>

if (reader.NodeType != XmlNodeType.EndElement) reader.ReadToNextSibling("v"); // 执行后(reader)的值:

}
          }
          return list.ToArray();
        }

///

        /// 根据工作表的范围计算有效行数
        ///

        ///工作表的范围,如:“A1”、“B2:C4”         /// 有效行数
        int GetRowCount(string dimension)
        {
          if (string.IsNullOrEmpty(dimension)) return -1;
          string[] ss = dimension.Split(':');
          if (ss.Length == 1) return 1;
          if (ss.Length != 2) return -1;
          return GetRowNumber(ss[1]) - GetRowNumber(ss[0]) + 1;
        }

///

        /// 根据单元格的坐标计算单元格的行号
       ///

       ///单元格的坐标,如“C4”        /// 单元格的行号
       int GetRowNumber(string str)
       {
         int i;
         for (i = 0; i < str.Length; i++) if (char.IsDigit(str, i)) break;
         return int.Parse(str.Substring(i));
       }

///

       /// 给出单元格的值,可能是各工作表共享的字符串
       ///

       ///”元素的“t”属性的值        ///”元素的值        /// 单元格的值
       string GetValue(string attr, string value)
       {
         if (attr != null)
         {
           int index;

if (!int.TryParse(value, out index)) throw new Exception("共享字符串索引(" + value + ")必须是整数");

if (index < 0 || index >= sharedStrings.Length) throw new Exception("共享字符串索引("

+ index + ")必须在(0)到(" + (sharedStrings.Length - 1) + ")之间");
           value = sharedStrings[index];
         }
         return value;
       }

public void Dispose()
       {
         if (reader != null) reader.Close();
         if (stream != null) stream.Close();
         reader = null;
         stream = null;
       }
     }
   }
 }

在这个程序中也是用 XmlReader 类来读取 xl/worksheets/sheet1.xml 文件。

另外可参考的文章:

还有什么不能做?——细谈在C#中读写Excel系列文章之二http://www.cnblogs.com/jaxu/archive/2012/05/10/2493627.html

需要注意的问题:
1.时间的问题,非文本的时间,xml中会自动转换成浮点数存放的,这个浮点数是通过ToOAtime()得到的,这个函数是干嘛的?自己查sdk去。

2.sharedStrings 中并不是完全跟一对一对应关系的,本文的代码中是默认一对一的,其实是错误的,因为当单元格中内容若有颜色,字体,大小属性限制某个字符的,会将这个单元格的内容拆分成多个t标签来存放,具体可以自己测试去,因为涉及到保密,就不提供源码了,处理也很简单,我这里就是告诉下你,这种情况会出问题,要注意!

3.其他的bug暂时还没有发现,后续还会继续介绍!

  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin