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

  1 using System;
  2 using System.Collections.Generic;
  3 using System.ComponentModel;
  4 using System.Data;
  5 using System.Drawing;
  6 using System.Linq;
  7 using System.Text;
  8 using System.Windows.Forms;
  9 using System.IO;
 10 using System.IO.Packaging;
 11 using System.Xml;
 12 using System.Xml.Linq;
 13 
 14 namespace ExcelPackageOperator
 15 {
 16     public partial class Form1 : Form
 17     {
 18         public Form1()
 19         {
 20             InitializeComponent();
 21 
 22             List<Cell> parsedCells = new List<Cell>();
 23             string fileName = @"C:\Users\jaxu\Desktop\Example.xlsx"; 
 24 
 25             using(Package xlsxPackage = Package.Open(fileName, FileMode.Open, FileAccess.Read))
 26             {
 27                 // load sharedStrings.xml document from Excel package
 28                 PackagePartCollection allParts = xlsxPackage.GetParts();
 29                 PackagePart sharedStringsPart = (from part in allParts
 30                                                  where part.Uri.OriginalString.Equals("/xl/sharedStrings.xml")
 31                                                  select part).Single();
 32 
 33                 // load to XElement from sharedStrings.xml document
 34                 XElement sharedStringsElement = XElement.Load(XmlReader.Create(sharedStringsPart.GetStream())); 
 35 
 36                 // read all fixed text to a dictionary from sharedStrings document
 37                 Dictionary<int, string> sharedStrings = new Dictionary<int, string>(); 
 38                 ParseSharedStrings(sharedStringsElement, sharedStrings); 
 39                 
 40                 // get worksheet 1
 41                 XElement worksheetElement = GetWorksheet(3, allParts); 
 42                 
 43                 IEnumerable<XElement> cells = from c in worksheetElement.Descendants(ExcelNamespaces.excelNamespace + "c") 
 44                                               select c; 
 45                 
 46                 foreach (XElement cell in cells) 
 47                 {
 48                     if (cell.HasElements && cell.Attribute("t") != null && cell.Attribute("t").Value.Equals("s"))
 49                     {
 50                         string cellPosition = cell.Attribute("r").Value;
 51                         int index = IndexOfNumber(cellPosition);
 52                         string column = cellPosition.Substring(0, index);
 53                         int row = Convert.ToInt32(cellPosition.Substring(index, cellPosition.Length - index));
 54                         int valueIndex = Convert.ToInt32(cell.Descendants(ExcelNamespaces.excelNamespace + "v").Single().Value);
 55 
 56                         parsedCells.Add(new Cell(column, row, sharedStrings[valueIndex]));
 57                     }
 58                 } 
 59             }
 60             
 61             //From here is additional code not covered in the posts, just to show it works
 62             foreach (Cell cell in parsedCells)            
 63             {
 64                 this.textBox1.Text += cell.ToString() + "\r\n";
 65                 //Console.WriteLine(cell);            
 66             }
 67         }
 68 
 69         private void ParseSharedStrings(XElement SharedStringsElement, Dictionary<int, string> sharedStrings) 
 70         { 
 71             IEnumerable<XElement> sharedStringsElements = from s in SharedStringsElement.Descendants(ExcelNamespaces.excelNamespace + "si") 
 72                                                           select s; 
 73             
 74             int Counter = 0; 
 75             foreach (XElement sharedString in sharedStringsElements) 
 76             { 
 77                 sharedStrings.Add(Counter, sharedString.Value); Counter++; 
 78             } 
 79         }         
 80         
 81         private XElement GetWorksheet(int worksheetID, PackagePartCollection allParts) 
 82         { 
 83             PackagePart worksheetPart = (from part in allParts 
 84                                          where part.Uri.OriginalString.Equals(String.Format("/xl/worksheets/sheet{0}.xml", worksheetID)) 
 85                                          select part).Single(); 
 86             return XElement.Load(XmlReader.Create(worksheetPart.GetStream())); 
 87         }
 88         
 89         private int IndexOfNumber(string value) 
 90         { 
 91             for (int counter = 0; counter < value.Length; counter++) 
 92             { 
 93                 if (char.IsNumber(value[counter])) 
 94                 { 
 95                     return counter; 
 96                 }
 97             }
 98             
 99             return 0; 
100         }
101     }
102 
103     internal static class ExcelNamespaces 
104     { 
105         internal static XNamespace excelNamespace = XNamespace.Get("http://schemas.openxmlformats.org/spreadsheetml/2006/main"); 
106         internal static XNamespace excelRelationshipsNamepace = XNamespace.Get("http://schemas.openxmlformats.org/officeDocument/2006/relationships");    
107     }
108 
109     public class Cell 
110     {
111         public Cell(string column, int row, string data) 
112         {
113             this.Column = column; 
114             this.Row = row; 
115             this.Data = data; 
116         }
117         
118         public override string ToString() 
119         {
120             return string.Format("{0}:{1} - {2}", Row, Column, Data); 
121         }
122         
123         public string Column 
124         {
125             get; set; 
126         }
127         
128         public int Row 
129         {
130             get; set; 
131         }
132         
133         public string Data 
134         {
135             get; set; 
136         }
137     }
138 }