ABONAMENTE VIDEO REDACȚIA
RO
EN
×
▼ LISTĂ EDIȚII ▼
Numărul 22
Abonament PDF

OpenXML - Noţiuni introductive

Florentina Suciu
Software engineer
@Fortech
PROGRAMARE

Î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).

Excel ca fişier zip

Figura 1 - Elemente ale unui fişier Excel zip

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.

Componente ale fişierelor Excel

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:

  • Foaia (Sheet), declarată în registrul de lucru (Workbook), conţine proprietăţile. De exemplu: nume, un id utilizat pentru sortarea foilor şi un id de relaţie, id care o conectează la WorksheetPart;
  • SheetData care conţine datele efective;

O parte pentru suportarea caracteristicilor cum ar fi protecţia şi filtrarea.

Figura 2 - Componente ale unui document Excel

Librăria OpenXml

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();
}

Crearea unui tabel pivot (Pivot Table)

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);
Figura 4 - Componente necesare pentru a crea un tabel pivot

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.

  • RowFields sunt date statice şi PivotField-ul lor corespunzător are proprietatea Axă (Axis) setată pe "AxisRow";
  • DataFields sunt date calculate (de exemplu totaluri) şi PivotField-ul lor corespunzător are proprietatea DataField setată pe true.

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.

Aplicarea formatării condiţionate

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);

Concluzie

Î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.

LANSAREA NUMĂRULUI 87

Prezentări articole și
Panel: Project management

Marți, 24 Septembrie, ora 18:00
Impact Hub, București

Înregistrează-te

Facebook Meetup

Conferință

Sponsori

  • ntt data
  • 3PillarGlobal
  • Betfair
  • Telenav
  • Accenture
  • Siemens
  • Bosch
  • FlowTraders
  • MHP
  • Connatix
  • UIPatj
  • MetroSystems
  • Globant
  • Colors in projects