În acest articol, încercăm să trasăm o hartă de bază pentru a manipula programatic fişiere xlsx folosind librăria Office Xml. Multe aplicaţii necesită lucrul cu fişierele excel, fie pentru citirea şi importarea datelor din el, fie pentru exportarea datelor într-un raport, astfel că este important să cunoaştem cum să manipulăm programatic fişierele excel.
Începând cu anul 2007, fişierele Excel şi-au schimbat complet structura lor internă. Xls a fost un format de fişier binar proprietar, în timp ce xlsx este un format bazat pe Xml, numit Office Open Xml (OOXML).
Un fişier xlsx este un pachet zip care conţine un fişier xml pentru fiecare parte majoră a unui fişier Excel (foi, stiluri, diagrame, tabele pivot). Dacă doriţi să verificaţi conţinutul unui xlsx, tot ce trebuie să faceţi este să schimbaţi extensia fişierului din xlsx în zip şi apoi să-l dezarhivaţi.
Un document Excel conţine un WorkbookPart central şi părţi separate pentru fiecare foaie de lucru. Pentru a crea un document valid, trebuie să uniţi cinci elemente, Workbook, WorksheetPart, Worksheet, Sheet, SheetData.
Sarcina principală a unui WorkbookPart este de a ţine evidenţa foilor de lucru, setărilor globale şi a componentelor partajate ale registrului de lucru (Workbook). Documentul trebuie să conţină cel puţin o foaie de lucru (Worksheet) care este definită în interiorul unui WorksheetPart. O foaie de lucru are trei secţiuni principale:
O parte pentru suportarea caracteristicilor cum ar fi protecţia şi filtrarea.
Toate clasele necesare pentru a manipula un fişier xlsx pot fi găsite în Open Xml SDK. Mai jos este un exemplu simplu de aplicare a unei sume la o coloană de date.
using (SpreadsheetDocument document =
SpreadsheetDocument.Create(path,
SpreadsheetDocumentType.Workbook))
{
var workbookPart = document.
AddNewPart();
workbookPart.Workbook = new Workbook();
var worksheetPart = document.
AddNewPart();
// create sheet data
var sheetData = worksheetPart.Worksheet.
AppendChild(new SheetData());
// create a row and add a data to it
sheetData.AppendChild(new Row(new Cell() {
CellValue = new CellValue("5"),
DataType = CellValues.Number }));
sheetData.AppendChild(new Row(new Cell() {
CellValue = new CellValue("3"),
DataType = CellValues.Number }));
sheetData.AppendChild(new Row(new Cell() {
CellValue = new CellValue("65"),
DataType = CellValues.Number }));
sheetData.AppendChild(new Row(new Cell() {
CellFormula = new CellFormula("=SUM(A1:A3)"),
DataType = CellValues.Number }));
// save the worksheet
worksheetPart.Worksheet.Save();
// create the sheet properties
var sheetsCount = document.WorkbookPart.Workbook.
Sheets.Count() + 100;
document.WorkbookPart.Workbook.Sheets.
AppendChild(new Sheet()
{
Id = document.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = (uint)document.WorkbookPart.Workbook.
Sheets.Count() + 1,
Name = "MyFirstSheet"
});
// save the workbook
document.WorkbookPart.Workbook.Save();
} Un tabel pivot este un tabel folosit pentru sumarizarea datelor, care poate sorta, calcula sau aplica media automat la datele stocate într-un tabel de date. Un tabel pivot are nevoie de un tabel de date sursă. Vom presupune că avem deja tabelul de date, într-o foaie numită "DataSheet".
Un tabel pivot are patru părţi principale: WorksheetPart, PivotTablePart, PivotTableCacheDefinitionPart şi PivotCacheRecordsPart. De asemenea, trebuie să instanţiem o listă de PivotCaches, cu un PivotCache descendent. În imaginile următoare, puteţi vedea "harta" unui tabel pivot.
Figura 4 - Componente necesare pentru a crea un tabel pivot
var pivotWorksheetPart = document.WorkbookPart.
AddNewPart();
pivotWorksheetPart.Worksheet = new Worksheet();
var pivotTablePart = pivotWorksheetPart.
AddNewPart();
var pivotTableCacheDefinitionPart = pivotTablePart.
AddNewPart();
document.WorkbookPart.AddPart(
pivotTableCacheDefinitionPart);
var pivotTableCacheRecordsPart =
pivotTableCacheDefinitionPart.
AddNewPart();
var pivotCaches = new PivotCaches();
pivotCaches.AppendChild(new PivotCache()
{
CacheId = pivotCacheId,
Id = document.WorkbookPart.
GetIdOfPart(pivotTableCacheDefinitionPart)
});
document.WorkbookPart.Workbook.AppendChild(pivotCaches); 
PivotTablePart descrie layout-ul. Descendentul său, PivotTableDefinition, stochează locaţia tabelului şi PivotFields. Sunt două tipuri de PivotFields (câmpuri pivot): RowFields şi DataFields.
Definiția tabelului pivot trebuie să cunoască id-ul PivotCache-ului pe care l-am definit mai sus.
În definiţia tabelului pivot puteţi specifica formatul în care doriţi să afişaţi tabelul. Acestea pot fi: Compact (setaţi flag-ul compact pe true), Outline (setaţi flag-ul Outline pe true), sau formatul Tabular (tabelar) (setaţi flag-ul GridDropZones pe true).
PivotTableCacheDefinitionPart cu descendentul PivotCacheDefinition, defineşte câmpurile cache (cache fields). Este necesar să declarăm un cache field pentru fiecare coloană din tabel. De asemenea, acesta conţine tipul de sursă cache (cache source type) (ca SourceValues.Worksheet) şi sursa foii de lucru (worksheet source).
PivotCacheRecordsPart trebuie doar să fie definit şi anexat, această parte fiind populată automat cu valorile cache ale tabelului.
Vom prezenta cum se poate aplica datelor formatarea condiţionată, adică să formatăm şi să evidenţiem anumite celule pe baza valorilor lor.
Pentru a face acest lucru, trebuie să definiţi două lucruri. În primul rând, definiţi stilurile pe care doriţi să le aplicaţi celulelor evidenţiate, în special fonturile şi culorile. Stilurile sunt declarate în Stylesheet al părţii registrului de lucru (workbook part).
În pasul următor, trebuie să definiţi regulile cu ajutorul obiectului ConditionalFormatting care are ca descendent un obiect ConditionalFormattingRule. Mai jos puteţi vedea un exemplu în care aplicăm o formatare condiţionată pentru celulele care au o valoare mai mică de 3.
var pivotWorksheetPart =
document.WorkbookPart.AddNewPart();
pivotWorksheetPart.Worksheet = new Worksheet();
var pivotTablePart = pivotWorksheetPart.AddNewPart();
var pivotTableCacheDefinitionPart =
pivotTablePart.
AddNewPart();
document.WorkbookPart.
AddPart(pivotTableCacheDefinitionPart);
var pivotTableCacheRecordsPart =
pivotTableCacheDefinitionPart.
AddNewPart();
var pivotCaches = new PivotCaches();
pivotCaches.AppendChild(new PivotCache()
{
CacheId = pivotCacheId,
Id = document.WorkbookPart.
GetIdOfPart(pivotTableCacheDefinitionPart)
});
document.WorkbookPart.Workbook.AppendChild(pivotCaches); În acest articol am trasat o "hartă" de bază a modului în care se poate naviga prin OpenXML în generarea fişierelor xlsx. Chiar şi atunci când încerci să-l prezinţi cât mai simplu posibil, se poate vedea că şi pentru cele mai simple operaţiuni codul poate şi va deveni complex.
