还有什么不能做？——细谈在C#中读写Excel系列文章之三

　　在第二篇文章中我已经向大家详细介绍了Excel 2007以后版本的文件的OpenXML结构。所谓的OpenXML结构从本质上来讲其实就是一个ZIP压缩包，所有基于OpenXML数据结构的文件都一样。这样文件的结构是开放的，你不需要借助于任何第三方的组件就可以读取文件中的数据，或者修改文件的内容。而且所有资源文件诸如图片和多媒体文件都是被编译成一个固定的格式存放在压缩包中，而不是传统形式上的流。另外，以压缩包的形式存储数据也使得文件所占用的空间更小。

　　这些都是它的优点。其实，微软在.NET中已经提供了相应的类库来帮助我们操作OpenXML文件，就像我在第二篇文章中向大家介绍的一样，微软把OpenXML压缩包称之为一个Package，在Package中的文件称作Parts，不同的Parts有自己的Content Type。Content Type可以描述任何内容，如application XML，user XML，images，sounds，videos，或者其它二进制内容。各个Part之间通过一个被称之为relationship的part连接起来，这个part其实也是一种特殊的XML文件，后缀为".rels"，在Package中可以找到它。下图说明了一个Package中各个Part之间的关系，

　　虽然.NET中提供的Package类可以帮助我们非常方便地解析Excel包，但是也有许多的局限性，有的时候你不得不使用一些方法来自己读取包中的内容，或者尝试去修改包中的数据。

　　1. 将Package作为一个标准的zip文件进行解压。

　　2. 找到Package中你想要读取的parts。有许多的方法可以帮助你找到这些parts，你可以解析relationship，也可以直接通过path来定位到某一个文件，不过不太确定path在将来是否会有变化。或者还可以通过part的content type来找到它。

　　3. 读取part中的内容。如果parts是以XML的形式存放的，通过标准的XML类库就可以非常方便地读取到数据。如果是其它形式，如图片、声音或视频文件则也可以通过相应的方法来获取到内容。

　　另一方面，你也可以创建一个基于OpenXML的文件（如采用非COM组件的形式创建一个Excel文件），

　　1. 创建或复制所有必须的parts。通过标准XML类库来创建这些基于XML数据格式的parts，或者从其它package中复制这些parts，或者采用任何其它你所熟悉的方法。

　　2. 创建relationships部分。也就是创建一个后缀为".rels"的特殊XML文件。

　　3. 将整个package压缩成一个zip包，然后将文件的后缀修改成需要的类型（如docx，xlsx，或者pptx等）。只要package中的结构符合要求，修改之后的文件可以直接被打开。当然，该过程可以在完全没有安装office的机器上完成（例如服务器），然后分发给需要的地方使用。

　　所有这一切包括packages，parts，content types，以及relationships都被称之为OpenXML文档，微软将这个叫做Open Packaging Conventions。

　　有关Excel OpenXML的内部结构以及一些比较重要的节点含义在前一篇文章中已经做了一些介绍，除了直接使用.NET类库中的Package类之外，我们当然也可以使用其它的第三方类库或者自己编写代码读取包中的内容，这里有一个.NET的开源类库专门用来操作zip压缩文件。

http://www.icsharpcode.net/OpenSource/SharpZipLib/

　　提供一个程序集下载吧，源代码大家去上面这个网址下载。ICSharpCode.ShareZipLib.zip

　　借用该类库中的ExtractZip()和CreateZip()方法可以帮助我们读取或修改（创建）Excel文件。当然这个类库中的某些代码是使用较早的.NET版本中的语法和对象来编写的，诸如ArratList、Hashtable等，它可能不适合在Silverlight工程中使用（支持Silverlight的.NET Framework与普通的.NET Framework有许多区别并且变化比较频繁），接下来的文章中我会向大家介绍如何在Silverlight工程中使用它。但现在并不妨碍我们在其它类型的工程中使用。

　　来看一看实际的例子。这里有一个类提供了一些方法用来读取和修改Excel文件中的数据：

  1 using System.Collections.Generic;
  2 using System.Data;
  3 using System.Globalization;
  4 using System.IO;
  5 using System.Text;
  6 using System.Xml;
  7 using ICSharpCode.SharpZipLib.Zip;
  8 
  9 namespace XlsxReadWrite
 10 {
 11     internal static class XlsxRW
 12     {
 13         public static void DeleteDirectoryContents(string directory)
 14         {
 15             var info = new DirectoryInfo(directory);
 16 
 17             foreach (var file in info.GetFiles())
 18             {
 19                 file.Delete();
 20             }
 21 
 22             foreach (var dir in info.GetDirectories())
 23             {
 24                 dir.Delete(true);
 25             }
 26         }
 27 
 28         public static void UnzipFile(string zipFileName, string targetDirectory)
 29         {
 30             new FastZip().ExtractZip(zipFileName, targetDirectory, null);
 31         }
 32 
 33         public static void ZipDirectory(string sourceDirectory, string zipFileName)
 34         {
 35             new FastZip().CreateZip(zipFileName, sourceDirectory, true, null);
 36         }
 37 
 38         public static IList<string> ReadStringTable(Stream input)
 39         {
 40             var stringTable = new List<string>();
 41 
 42             using (var reader = XmlReader.Create(input))
 43             {
 44                 for (reader.MoveToContent(); reader.Read(); )
 45                 {
 46                     if (reader.NodeType == XmlNodeType.Element && reader.Name == "t")
 47                     {
 48                         stringTable.Add(reader.ReadElementString());
 49                     }
 50                 }
 51             }
 52 
 53             return stringTable;
 54         }
 55 
 56         public static void ReadWorksheet(Stream input, IList<string> stringTable, DataTable data)
 57         {
 58             using (var reader = XmlReader.Create(input))
 59             {
 60                 DataRow row = null;
 61                 int columnIndex = 0;
 62                 string type;
 63                 int value;
 64 
 65                 for (reader.MoveToContent(); reader.Read(); )
 66                     if (reader.NodeType == XmlNodeType.Element)
 67                         switch (reader.Name)
 68                         {
 69                             case "row":
 70                                 row = data.NewRow();
 71                                 data.Rows.Add(row);
 72 
 73                                 columnIndex = 0;
 74 
 75                                 break;
 76 
 77                             case "c":
 78                                 type = reader.GetAttribute("t");
 79                                 reader.Read();
 80                                 value = int.Parse(reader.ReadElementString(), CultureInfo.InvariantCulture);
 81 
 82                                 if (type == "s")
 83                                     row[columnIndex] = stringTable[value];
 84                                 else
 85                                     row[columnIndex] = value;
 86 
 87                                 columnIndex++;
 88 
 89                                 break;
 90                         }
 91             }
 92         }
 93 
 94         public static IList<string> CreateStringTables(DataTable data, out IDictionary<string, int> lookupTable)
 95         {
 96             var stringTable = new List<string>();
 97             lookupTable = new Dictionary<string, int>();
 98 
 99             foreach (DataRow row in data.Rows)
100                 foreach (DataColumn column in data.Columns)
101                     if (column.DataType == typeof(string))
102                     {
103                         var value = (string)row[column];
104 
105                         if (!lookupTable.ContainsKey(value))
106                         {
107                             lookupTable.Add(value, stringTable.Count);
108                             stringTable.Add(value);
109                         }
110                     }
111 
112             return stringTable;
113         }
114 
115         public static void WriteStringTable(Stream output, IList<string> stringTable)
116         {
117             using (var writer = XmlWriter.Create(output))
118             {
119                 writer.WriteStartDocument(true);
120 
121                 writer.WriteStartElement("sst", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
122                 writer.WriteAttributeString("count", stringTable.Count.ToString(CultureInfo.InvariantCulture));
123                 writer.WriteAttributeString("uniqueCount", stringTable.Count.ToString(CultureInfo.InvariantCulture));
124 
125                 foreach (var str in stringTable)
126                 {
127                     writer.WriteStartElement("si");
128                     writer.WriteElementString("t", str);
129                     writer.WriteEndElement();
130                 }
131 
132                 writer.WriteEndElement();
133             }
134         }
135 
136         public static string RowColumnToPosition(int row, int column)
137         {
138             return ColumnIndexToName(column) + RowIndexToName(row);
139         }
140 
141         public static string ColumnIndexToName(int columnIndex)
142         {
143             var second = (char)(((int)'A') + columnIndex % 26);
144 
145             columnIndex /= 26;
146 
147             if (columnIndex == 0)
148                 return second.ToString();
149             else
150                 return ((char)(((int)'A') - 1 + columnIndex)).ToString() + second.ToString();
151         }
152 
153         public static string RowIndexToName(int rowIndex)
154         {
155             return (rowIndex + 1).ToString(CultureInfo.InvariantCulture);
156         }
157 
158         public static void WriteWorksheet(Stream output, DataTable data, IDictionary<string, int> lookupTable)
159         {
160             using (XmlTextWriter writer = new XmlTextWriter(output, Encoding.UTF8))
161             {
162                 writer.WriteStartDocument(true);
163 
164                 writer.WriteStartElement("worksheet");
165                 writer.WriteAttributeString("xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
166                 writer.WriteAttributeString("xmlns:r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
167 
168                 writer.WriteStartElement("dimension");
169                 var lastCell = RowColumnToPosition(data.Rows.Count - 1, data.Columns.Count - 1);
170                 writer.WriteAttributeString("ref", "A1:" + lastCell);
171                 writer.WriteEndElement();
172 
173                 writer.WriteStartElement("sheetViews");
174                 writer.WriteStartElement("sheetView");
175                 writer.WriteAttributeString("tabSelected", "1");
176                 writer.WriteAttributeString("workbookViewId", "0");
177                 writer.WriteEndElement();
178                 writer.WriteEndElement();
179 
180                 writer.WriteStartElement("sheetFormatPr");
181                 writer.WriteAttributeString("defaultRowHeight", "15");
182                 writer.WriteEndElement();
183 
184                 writer.WriteStartElement("sheetData");
185                 WriteWorksheetData(writer, data, lookupTable);
186                 writer.WriteEndElement();
187 
188                 writer.WriteStartElement("pageMargins");
189                 writer.WriteAttributeString("left", "0.7");
190                 writer.WriteAttributeString("right", "0.7");
191                 writer.WriteAttributeString("top", "0.75");
192                 writer.WriteAttributeString("bottom", "0.75");
193                 writer.WriteAttributeString("header", "0.3");
194                 writer.WriteAttributeString("footer", "0.3");
195                 writer.WriteEndElement();
196 
197                 writer.WriteEndElement();
198             }
199         }
200 
201         public static void WriteWorksheetData(XmlTextWriter writer, DataTable data, IDictionary<string, int> lookupTable)
202         {
203             var rowsCount = data.Rows.Count;
204             var columnsCount = data.Columns.Count;
205             string relPos;
206 
207             for (int row = 0; row < rowsCount; row++)
208             {
209                 writer.WriteStartElement("row");
210                 relPos = RowIndexToName(row);
211                 writer.WriteAttributeString("r", relPos);
212                 writer.WriteAttributeString("spans", "1:" + columnsCount.ToString(CultureInfo.InvariantCulture));
213 
214                 for (int column = 0; column < columnsCount; column++)
215                 {
216                     object value = data.Rows[row][column];
217 
218                     writer.WriteStartElement("c");
219                     relPos = RowColumnToPosition(row, column);
220                     writer.WriteAttributeString("r", relPos);
221 
222                     var str = value as string;
223                     if (str != null)
224                     {
225                         writer.WriteAttributeString("t", "s");
226                         value = lookupTable[str];
227                     }
228 
229                     writer.WriteElementString("v", value.ToString());
230 
231                     writer.WriteEndElement();
232                 }
233 
234                 writer.WriteEndElement();
235             }
236         }
237     }
238 }

　　1. DeleteDirectoryContents()方法用来清空临时目录的内容，该临时目录被用来存放解压Excel之后的文件。

　　2. UnzipFile()方法直接使用了开源类库中的ExtractZip()方法，将Excel文件解压到临时目录。

　　3. ZipDirectory()方法使用开源类库中的CreateZip()方法将临时目录中的内容重新打包成zip压缩文件。

　　4. ReadStringTable()方法用来读取xl/sharedStrings.xml文件中节点t的内容。其实应该是直接读取si节点的内容，应为并不是所有的si节点都有t子节点。

　　考虑一个情况：在Excel的单元格中，选中一部分内容，如内容中的某一个数字或某一个单词，然后对它单独设置样式。通过这种方法你可以将Excel单元格中的某一部分内容设置为粗体、上标，还可以在单元格内进行换行等。如下图：

　　在Excel单元格内设置的样式被存放到sharedStrings.xml文件中后会变成如下这种形式：

<si>
    <r>
      <t xml:space="preserve">  Short-term investments (including securities loaned 
    of </t>
    </r>
    <r>
      <rPr>
        <b/>
        <sz val="8"/>
        <color rgb="FF404040"/>
        <rFont val="Verdana"/>
        <family val="2"/>
      </rPr>
      <t>$9,999</t>
    </r>
    <r>
      <rPr>
        <sz val="8"/>
        <color rgb="FF404040"/>
        <rFont val="Verdana"/>
        <family val="2"/>
      </rPr>
      <t xml:space="preserve"> and $8,888)</t>
    </r>
  </si>

　　这个时候节点si中的内容就不是单纯的子节点t了。因此，如何解析XML文件需要根据实际情况去考虑，这主要取决于你在Excel文件中存储的内容。

　　5. ReadWorksheet()方法会按照指定的工作表XML文件（如"xl/worksheets/sheet1.xml"）在sharedStrings.xml文件中查找数据，并将结果存放到一个DataTable中。

　　6. CreateStringTables()和WriteStringsTable()方法用来创建一个sharedStrings.xml文件。

　　7. WriteWorksheet()方法用来创建一个工作表XML文件。

　　来看看如何调用：

 1 private void ReadInput(object sender, RoutedEventArgs e)
 2 {
 3     // Get the input file name from the text box.
 4     var fileName = this.inputTextBox.Text;
 5 
 6     // Delete contents of the temporary directory.
 7     XlsxRW.DeleteDirectoryContents(tempDir);
 8 
 9     // Unzip input XLSX file to the temporary directory.
10     XlsxRW.UnzipFile(fileName, tempDir);
11 
12     IList<string> stringTable;
13     // Open XML file with table of all unique strings used in the workbook..
14     using (var stream = new FileStream(Path.Combine(tempDir, @"xl\sharedStrings.xml"),
15         FileMode.Open, FileAccess.Read))
16         // ..and call helper method that parses that XML and returns an array of strings.
17         stringTable = XlsxRW.ReadStringTable(stream);
18 
19     // Open XML file with worksheet data..
20     using (var stream = new FileStream(Path.Combine(tempDir, @"xl\worksheets\sheet1.xml"),
21         FileMode.Open, FileAccess.Read))
22         // ..and call helper method that parses that XML and fills DataTable with values.
23         XlsxRW.ReadWorksheet(stream, stringTable, this.data);
24 }
25 
26 private void WriteOutput(object sender, RoutedEventArgs e)
27 {
28     // Get the output file name from the text box.
29     string fileName = this.outputTextBox.Text;
30 
31     // Delete contents of the temporary directory.
32     XlsxRW.DeleteDirectoryContents(tempDir);
33 
34     // Unzip template XLSX file to the temporary directory.
35     XlsxRW.UnzipFile(templateFile, tempDir);
36 
37     // We will need two string tables; a lookup IDictionary<string, int> for fast searching 
38     // an ordinary IList<string> where items are sorted by their index.
39     IDictionary<string, int> lookupTable;
40 
41     // Call helper methods which creates both tables from input data.
42     var stringTable = XlsxRW.CreateStringTables(this.data, out lookupTable);
43 
44     // Create XML file..
45     using (var stream = new FileStream(Path.Combine(tempDir, @"xl\sharedStrings.xml"),
46         FileMode.Create))
47         // ..and fill it with unique strings used in the workbook
48         XlsxRW.WriteStringTable(stream, stringTable);
49 
50     // Create XML file..
51     using (var stream = new FileStream(Path.Combine(tempDir, @"xl\worksheets\sheet1.xml"),
52         FileMode.Create))
53         // ..and fill it with rows and columns of the DataTable.
54         XlsxRW.WriteWorksheet(stream, this.data, lookupTable);
55 
56     // ZIP temporary directory to the XLSX file.
57     XlsxRW.ZipDirectory(tempDir, fileName);
58 
59     // If checkbox is checked, show XLSX file in Microsoft Excel.
60     if (this.openFileCheckBox.IsChecked == true)
61         System.Diagnostics.Process.Start(fileName);
62 }

　　你所要做的只是对XML进行操作，仅此而以！

　　支持.NET操作zip压缩包的类库应该还有很多，任何一个都可以，因为Excel的OpenXML文件本身就是一个标准的zip压缩包。但是上面的方法还是有一个局限性，那就是需要临时目录来存放解压之后的文件，以及重新打包时所指定的源文件。对普通的Windows应用程序或asp.net应用程序而言这个并没有什么困难，只要权限允许，读写临时目录没有任何问题，但是在Silverlight中则有所不同，因为在Silverlight中读写客户端文件需要比较高的安全级别和认证，这就导致解压文件会有困难，一个简单的方法就是直接在文件的流中进行解压和修改，然后再将流打包成zip文件。下一篇文章中我会向大家介绍如何在Silverlight中使用，以及如何定义一个类来完成Excel文件中某些字符串样式的修改。

posted @ 2012-05-11 07:35  Jaxu  阅读(4128)  评论(17)  编辑  收藏  举报