Dopo il post su come fare query sui file docx, ecco un esempio sugli spreadsheet in formato xlsx.
I discorsi sui Package fatti nel post precedente sono ovviamente identici visto che anche xlsx usa il package OPC. Perciò la versione 1.0 dell'SDK di Office OpenXML continua ad essere di aiuto in quanto contiene i wrapper alle parts specifiche del formato spreadsheet.
I file xlsx sono più strutturati rispetto ad un documento docx. I punti fondamentali sono i seguenti:
- In ogni xlsx esiste un workbook che elenca i fogli (sheet) presenti.
- Ciascuno sheet è rappresentato da un file xml separato all'interno del file xlsx
- In ogni xlsx esiste una tabella delle stringhe più usate chiamata "shared strings". In sostanza la stringa scritta in una cella può essere scritta direttamente nella cella oppure referenziare (tramite un indice numerico) la stringa scritta dentro la tabella delle shared strings.
Chi userà la mia libreria vorrà almeno queste funzionalità:
- I metodi per caricare uno sheet sia per numero (indice zero based) che per nome
- Il metodo per caricare la tabella delle shared strings
- Tutte le funzioni che cercano del testo dovranno farlo sia nelle celle che nella tabella shared strings
Iniziamo quindi referenziando la "DocumentFormat.OpenXML.dll" e creando una nuova classe con alcune dichiarazioni che useremo più avanti, tra cui i namespace di OOXML usati più frequentemente:
1: public class ExcelHelper
2: {
3: public static XNamespace NS_ex = XNamespace.Get("http://schemas.openxmlformats.org/spreadsheetml/2006/main");
4: public static XNamespace NS_r = XNamespace.Get("http://schemas.openxmlformats.org/officeDocument/2006/relationships");
5: private string _FileName;
6:
7: private RafSheetCollection _RafSheets;
8: private IEnumerable<SharedString> _SharedStrings;
9: private XElement _CurrentSheet;
10: public XElement CurrentSheet
11: {
12: get
13: {
14: if (_CurrentSheet == null)
15: LoadSheet(0);
16: return _CurrentSheet;
17: }
18: set { _CurrentSheet = value; }
19: }
20:
21: public ExcelHelper(string FileName)
22: {
23: _FileName = FileName;
24: }
25:
26: ...
In questo momento l'sdk di OpenXML non ci fornisce un metodo per avere l'elenco dei sheet presenti all'interno di un file xlsx. Per questo motivo dichiaro una nuova classe che manterrà queste informazioni:
1: public class RafSheet
2: {
3: public string Name { get; set; }
4: public string SheetId { get; set; }
5: public string Id { get; set; }
6: }
Id rappresenta il relationship id ed è quello più importante. Id non è indispensabile ma lo carico ugualmente visto che il numero di sheet presenti in un file è sempre modesto.
Poiché possono esserci tanti sheet, dichiaro una collection di RafSheet:
1: public class RafSheetCollection : List<RafSheet>
2: {
3: public RafSheetCollection() {}
4:
5: public RafSheetCollection(IEnumerable<RafSheet> Sheets)
6: {
7: foreach (RafSheet Sheet in Sheets)
8: this.Add(Sheet);
9: }
10:
11: public RafSheet this[string Name]
12: {
13: get
14: {
15: foreach (RafSheet sheet in this)
16: {
17: if (string.Compare(sheet.Name, Name, true) == 0)
18: return sheet;
19: }
20: throw new IndexOutOfRangeException();
21: }
22: set
23: {
24: for(int i=0; i<this.Count; i++)
25: {
26: RafSheet sheet = this[i];
27: if (string.Compare(sheet.Name, Name, true) == 0)
28: {
29: this[i] = value;
30: return;
31: }
32: }
33: this.Add(value); // se non lo trova, lo aggiunge
34: }
35: }
36: }
A questa collection ho aggiunto due caratteristiche comode:
- Un costruttore che aggiunge alla propria istanza tutti gli elementi di un'altra collection di sheets
- Un indexer che è in grado di recuperare un RafSheet per nome (get) o aggiungerlo (set)
A questo punto torno a scrivere i metodi della ExcelHelper. È arrivato il momento di caricare lo sheet desiderato.
1: public void LoadSheet(int Sheet)
2: {
3: using (SpreadsheetDocument xlPackage = SpreadsheetDocument.Open(_FileName, true))
4: {
5: WorkbookPart workbook = xlPackage.WorkbookPart;
6:
7: PopulateRafSheets(workbook);
8:
9: OpenXmlPart part = workbook.GetPartById(_RafSheets[Sheet].Id);
10: LoadSheet(part);
11: LoadSharedStrings(workbook);
12: }
13: }
14:
15: public void LoadSheet(string SheetName)
16: {
17: using (SpreadsheetDocument xlPackage = SpreadsheetDocument.Open(_FileName, true))
18: {
19: WorkbookPart workbook = xlPackage.WorkbookPart;
20:
21: PopulateRafSheets(workbook);
22:
23: OpenXmlPart part = workbook.GetPartById(_RafSheets[SheetName].Id);
24: LoadSheet(part);
25: LoadSharedStrings(workbook);
26: }
27: }
Il primo carica il sheet per indice (zero based) mentre il secondo per nome, proprio grazie alle due classi che ho scritto prima.
Come per il docx la tecnica per accedere ai file xml interni è la medesima:
- Aprire il package (_Filename è stato popolato nel costruttore di ExcelHelper)
- Prendere un riferimento alla parte del package che descrive il workbook
- Popolare la nostra collection con le informazioni degli sheets (vedi sotto)
- Recuperare la part (il file xml) relativo allo sheet voluto (per nome o indice)
- Caricare in memoria lo sheet e il file delle shared strings
È arrivato il momento di popolare la collection di RafSheet.
1: private void PopulateRafSheets(WorkbookPart workbook)
2: {
3: if (_RafSheets != null)
4: return;
5:
6: using (StreamReader streamReader = new StreamReader(workbook.GetStream()))
7: {
8: using (XmlReader stream = XmlReader.Create(streamReader))
9: {
10: XElement book = XElement.Load(stream);
11:
12: var v =
13: from b in book.Descendants(NS_ex + "sheets")
14: from s in b.Descendants(NS_ex + "sheet")
15: select new RafSheet()
16: {
17: Name = s.Attribute(XName.Get("name")).Value,
18: Id = s.Attribute(XName.Get("id", NS_r.NamespaceName)).Value,
19: SheetId = s.Attribute(XName.Get("sheetId")).Value
20: };
21:
22: _RafSheets = new RafSheetCollection(v);
23: }
24: }
25: }
Questo metodo riceve il workbook al cui interno è scritto semplicemente l'elenco degli sheet. Una volta caricato in memoria il contenuto xml del workbook, eseguo una query Linq per recuperare nome, id e sheetId di ciascuno sheet.
L'xml del workbook è fatto così:
1: <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
2: <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
3: xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
4: <sheets>
5: <sheet name="Sheet1" sheetId="1" r:id="rId1"/>
6: <sheet name="Sheet2" sheetId="2" r:id="rId2"/>
7: <sheet name="Sheet3" sheetId="3" r:id="rId3"/>
8: </sheets>
9: </workbook>
Resta ancora da caricare la shared string table. Da ricordarsi che le stringhe vengono referenziate per indice (ordine di inserimento) e che non contengono un id. Ancora una volta usiamo Linq per recuperare il testo
1: private void LoadSharedStrings(WorkbookPart workbook)
2: {
3: if (_SharedStrings != null)
4: return;
5:
6: SharedStringTablePart Shared = workbook.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
7: if (Shared == null)
8: {
9: _SharedStrings = new List<SharedString>(); // lista vuota
10: return;
11: }
12:
13: using (StreamReader streamReader = new StreamReader(Shared.GetStream()))
14: {
15: using (XmlReader stream = XmlReader.Create(streamReader))
16: {
17: int index = 0;
18: XElement shs = XElement.Load(stream);
19: _SharedStrings =
20: from si in shs.Descendants(NS_ex + "si")
21: from t in si.Descendants(NS_ex + "t")
22: select new SharedString()
23: {
24: Text = t.Value,
25: Index = index++
26: };
27: }
28: }
29: }
Lo scopo è quello di mantenere a livello di classe una collection di SharedString (IEnumerable<SharedString>) che devo ancora definire. L'importante è che il reference _SharedStrings non sia mai null per evitare le ovvie catastrofiche conseguenze.
Grazie a Linq vado a recuperare tutti gli elementi <t> children di <si> e popolo una mia classe chiamata SharedString che mantiene l'indice e la stringa. La sua definizione è la seguente:
1: public class SharedString
2: {
3: public int Index { get; set; }
4: public string Text { get; set; }
5: public override string ToString()
6: {
7: return "{" + Index + "} \t" + Text;
8: }
9: }
L'override di ToString è una comodità soprattutto durante il debugging.
Per chi si è già stancato ... peccato perché ora arriva il bello. Quanto scritto fino ad ora è la base di qualsiasi query noi vogliamo fare sui contenuti, formattazioni e formule presenti in un file xlsx.
Vediamo l'ultimo metodo che esegue una ricerca di una stringa di testo nelle celle *e* nella shared strings per poi fare l'unione dei risultati:
1: public IEnumerable<CellSearch> FindOccurrenceOf(string text)
2: {
3: var inlinesimple =
4: from d in _CurrentSheet.Descendants(NS_ex + "sheetData")
5: from row in d.Descendants(NS_ex + "row")
6: from c in row.Descendants(NS_ex + "c")
7: from istag in c.Descendants(NS_ex + "is")
8: from t in istag.Descendants(NS_ex + "t")
9: where t.Value.Contains(text)
10: select new CellSearch() { Cell = c.Attribute(XName.Get("r")).Value, Text = t.Value };
11:
12: var inlinerich =
13: from d in _CurrentSheet.Descendants(NS_ex + "sheetData")
14: from row in d.Descendants(NS_ex + "row")
15: from c in row.Descendants(NS_ex + "c")
16: from istag in c.Descendants(NS_ex + "is")
17: from r in c.Descendants(NS_ex + "istag")
18: from t in istag.Descendants(NS_ex + "r")
19: where t.Value.Contains(text)
20: select new CellSearch() { Cell = c.Attribute(XName.Get("r")).Value, Text = t.Value };
21:
22: var shared =
23: from d in _CurrentSheet.Descendants(NS_ex + "sheetData")
24: from row in d.Descendants(NS_ex + "row")
25: from c in row.Descendants(NS_ex + "c")
26: from v in c.Descendants(NS_ex + "v")
27: where c.Attribute(XName.Get("t")).Value == "s"
28: join ss in _SharedStrings on int.Parse(v.Value) equals ss.Index
29: where ss.Text.Contains(text)
30: select new CellSearch() { Cell = c.Attribute(XName.Get("r")).Value, Text = ss.Text };
31:
32: var res = inlinesimple.Union(inlinerich).Union(shared);
33: return res;
34: }
Perché tre query? L'attributo "t" della cella può assumere diversi valori. A noi interessano "inlinestr" nel qual caso le celle contengono direttamente la stringa oppure "s" cioè la cella contiene l'indice della shared string che contiene la stringa.
- La prima query cerca le stringhe nelle celle che contengono il testo e non nella shared string
- La seconda query è come la prima ma le cerca all'interno di un tag <r> che dice che il testo è formattato in modo "rich"
- La terza esegue una join tra il file xml dello sheet e delle shared string
Perciò le celle A1, B1, C1, D1 hanno il testo nella shared string, la cella E1 è inline e rich, la cella F1 è inline:
1: <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
2: xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
3: <sheetData>
4: <row r="1" spans="1:4">
5: <c r="A1" t="s">
6: <v>0</v>
7: </c>
8: <c r="B1" s="1" t="s">
9: <v>1</v>
10: </c>
11: <c r="C1" s="2" t="s">
12: <v>2</v>
13: </c>
14: <c r="D1" s="3" t="s">
15: <v>3</v>
16: </c>
17: <c r="E1" t="inlineStr">
18: <is>
19: <r><t>inline rich string</t></r>
20: </is>
21: </c>
22: <c r="F1" t="inlineStr">
23: <is><t>inline string</t></is>
24: </c>
25: </row>
26: </sheetData>
La tabella shared string è molto semplice:
1: <sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
2: count="4" uniqueCount="4">
3: <si>
4: <t>Normal</t>
5: </si>
6: <si>
7: <t>Bold</t>
8: </si>
9: <si>
10: <t>Italic</t>
11: </si>
12: <si>
13: <t>Underline</t>
14: </si>
15: </sst>
La ricerca è conclusa e i risultati sono finiti in una collection di oggetti così definiti:
1: public class CellSearch
2: {
3: public string Cell { get; set; }
4: public string Text { get; set; }
5: public override string ToString()
6: {
7: return "{" + Cell + "} \t" + Text;
8: }
9: }
Adesso metto il cappellino dell'utente della classe. Per stampare tutte le celle che contengono la stringa "r" sarà sufficiente fare così:
1: ExcelHelper eh = new ExcelHelper(ExcelFileName);
2: eh.LoadSheet(0);
3: var f10 = eh.FindOccurrenceOf("r");
4: foreach(object o in f10)
5: Console.WriteLine(o);
Come avevo già detto nel precedente post, la lettura dei file di Excel 2007 è più complessa rispetto a docx perché le stringhe possono trovarsi in diversi posti ma fatto lo sforzo iniziale, il resto è sotto controllo facilmente. D'altra parte questa struttura consente di risparmiare tantissima memoria su fogli di una certa dimensione, quindi il gioco vale sicuramente la pena.
La verbosità di questo codice è soprattutto per rendere più comodo il suo uso ma ancora una volta non posso definirlo complesso.