ABONAMENTE VIDEO REDACȚIA
RO
EN
NOU
Numărul 126
Numărul 125 Numărul 124 Numărul 123 Numărul 122 Numărul 121 Numărul 120 Numărul 119 Numărul 118 Numărul 117 Numărul 116 Numărul 115 Numărul 114 Numărul 113 Numărul 112 Numărul 111 Numărul 110 Numărul 109 Numărul 108 Numărul 107 Numărul 106 Numărul 105 Numărul 104 Numărul 103 Numărul 102 Numărul 101 Numărul 100 Numărul 99 Numărul 98 Numărul 97 Numărul 96 Numărul 95 Numărul 94 Numărul 93 Numărul 92 Numărul 91 Numărul 90 Numărul 89 Numărul 88 Numărul 87 Numărul 86 Numărul 85 Numărul 84 Numărul 83 Numărul 82 Numărul 81 Numărul 80 Numărul 79 Numărul 78 Numărul 77 Numărul 76 Numărul 75 Numărul 74 Numărul 73 Numărul 72 Numărul 71 Numărul 70 Numărul 69 Numărul 68 Numărul 67 Numărul 66 Numărul 65 Numărul 64 Numărul 63 Numărul 62 Numărul 61 Numărul 60 Numărul 59 Numărul 58 Numărul 57 Numărul 56 Numărul 55 Numărul 54 Numărul 53 Numărul 52 Numărul 51 Numărul 50 Numărul 49 Numărul 48 Numărul 47 Numărul 46 Numărul 45 Numărul 44 Numărul 43 Numărul 42 Numărul 41 Numărul 40 Numărul 39 Numărul 38 Numărul 37 Numărul 36 Numărul 35 Numărul 34 Numărul 33 Numărul 32 Numărul 31 Numărul 30 Numărul 29 Numărul 28 Numărul 27 Numărul 26 Numărul 25 Numărul 24 Numărul 23 Numărul 22 Numărul 21 Numărul 20 Numărul 19 Numărul 18 Numărul 17 Numărul 16 Numărul 15 Numărul 14 Numărul 13 Numărul 12 Numărul 11 Numărul 10 Numărul 9 Numărul 8 Numărul 7 Numărul 6 Numărul 5 Numărul 4 Numărul 3 Numărul 2 Numărul 1
×
▼ 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.

VIDEO: NUMĂRULUI 126

Sponsori

  • BT Code Crafters
  • Accesa
  • Bosch
  • Betfair
  • MHP
  • Connatix
  • BoatyardX
  • AboutYou
  • Telenav
  • .msg systems
  • Colors in projects