Freigeben über

Open XML Dateien aufbauen und verändern (Teil 3)

  • Artikel

Zeichenketten in existierende Excel-Dateien eintragen

In den ersten beiden Teilen haben wir das jeweilige Open XML Dokument neu erzeugt. Während man sich dabei darauf verlassen kann, dass die zu erzeugenden Teile noch nicht vorhanden sind, hat man es beim Einfügen in bestehende Dokumente schwerer. Es muss jedes Mal überprüft werden, ob eine Komponente nicht evtl. doch schon vorhanden ist, da jeder Bezeichner bzw. jede ID nur genau einmal vorkommen darf.

Es soll ein String in eine Zelle eingetragen werden. Der String wird als Shared String eingebettet. Als Übergabeparameter benötigen wir:

  • Name der Arbeitsmappen mit Pfadangabe
  • Name der Tabelle
  • Adresse der Zelle, z.B. “A1”
  • Die einzufügende Zeichenkette

Ausgehend von diesen Parametern könnten Tabelle, Zeile bzw. Zelle noch nicht vorhanden sein. Um das Erzeugen werden wir uns hier kümmern. Die Grundaufgabe ist allerdings das Einfügen der Zeichenkette in die Zelle.

Der Rumpf der Prozedur wäre erst mal folgender. Wir gehen nun Schritt für Schritt durch die Entscheidungen.

 

 private void AddSharedStringsToExistingWorksheet(string FName, 
   string sheetName, string cellRef, string sharedString)
{
   using (SpreadsheetDocument xlDoc = SpreadsheetDocument.Open(FName, true))
   {
     …
   }
}

Das Erste, was beim Einfügen eines Shared Strings gemacht werden muss ist die Prüfung, ob die Shared Strings-Tabelle existiert, also ob ein SharedStringTablePart vorhanden ist. Wenn nicht, wird sie erzeugt.

 

 SharedStringTablePart sstPart = 
   xlDoc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (sstPart == null)   
   sstPart = xlDoc.WorkbookPart.AddNewPart<SharedStringTablePart>();

Dann schauen wir, ob ein Worksheet mit dem vorgegebenen Namen zu finden ist. Dazu müssen wir im Workbook.xml nachschauen, da dort alle enthaltenen Worksheets gelistet sind. Das Markup der workbook.xml kann man einer vorher erstellten Excel-Datei entnehmen:

 <sheets>
  <sheet name="Bilanzen 1998" sheetId="1" r:id="rId1" /> 
  <sheet name="Zusammenfassung" sheetId="2" r:id="rId2" /> 
  <sheet name="Externe Ressourcen" sheetId="3" r:id="rId3" /> 
</sheets>

(Die angegebene Relationship ID (rIdx) kann jeder eindeutige String sein, z.B. auch eine GUID. Excel selbst vergibt durchnummerierte IDs, die sehr einfach zu lesen aber für uns schwierig zu verwalten sind. Das Open XML SDK verwendet deshalb auch GUID-Strukturen.)

Aus diesem Sheet-Element wird über die ID der dazugehörige WorkbookPart ermittelt (GetPartById). Dieser Part beherbergt dann auch das entsprechende Worksheet.
Wenn kein Sheet-Element in der Workbook.xml gefunden wurde, wird ein WorksheetPart eingefügt, das XML Document mit dem Root-Element Worksheet erzeugt und das SheetData-Element angehängt.

 WorkbookPart wbPart = xlDoc.WorkbookPart;
Workbook wb = wbPart.Workbook;
Sheet sheet = (Sheet)wb.GetFirstChild<Sheets>().Elements<Sheet>()
   .Where(s => s.Name == sheetName).FirstOrDefault();
 
WorksheetPart wsPart;
Worksheet ws = null;
if (sheet == null)  
{
  wsPart = InsertWorksheetPart(wbPart, tbWorksheetName.Text);
  ws = new Worksheet(new SheetData());  
}
else  
{
  string rId = sheet.Id.Value;
  wsPart = (WorksheetPart)xlDoc.WorkbookPart.GetPartById(rId); 
  ws = wsPart.Worksheet;  
}

SheetData sd = ws.GetFirstChild<SheetData>();

Wichtig ist es zu wissen, dass das o.g. Sheet Element nichts mit dem worksheet Element zu tun hat. Ersteres bezieht sich auf die Auflistung der in der Arbeitsmappe enthaltenen Tabellen (also in der workbook.xml) und letzteres ist die Definition des Inhalts dieser Tabellen (worksheet.xml).

Jetzt haben wir das SheetData-Element, also die Stelle, an der die Tabellenzeilen aufgelistet sind. Die Zelladresse wird hier im Format „A1“ übergeben. Um an die Zeilennummer zu kommen, habe ich eine kleine Extensions Class geschrieben, die die Methode RemoveAllButNumbers für den Typ String bereit stellt.

UInt32Value rowIndex = (UInt32Value)cellRef.RemoveAllButNumbers();

Mit dem so gewonnenen RowIndex wird die Referenz auf die Zeile geholt. Sollte die nicht existieren, erzeugen wir eine neue Zeile. Dabei ist unbedingt zu beachten, dass die Reihenfolge stimmt (aufsteigend). Die Funktion CreateRow weiter unten erledigt das.

 IEnumerable<Row> rows = sd.Elements<Row>()
   .Where(r => r.RowIndex.Value == rowIndex);
Row row = rows.FirstOrDefault();
if (row == null)
  row = CreateNewRow(ref sd, rowIndex);

Dasselbe gilt für die Zelle.

 Cell cell = row.Elements<Cell>()
   .Where(c => c.CellReference == cellRef).FirstOrDefault();
if (cell == null)
  cell = CreateNewCell(ref row, cellRef);

Am Ende wird nur noch der Shared String mit der Zelle verlinkt und das Worksheet gespeichert. Die Funktion dafür wurde schon in Teil 2 beschrieben.

 cell.DataType = CellValues.SharedString;
cell.CellReference = cellRef;
cell.CellValue = new CellValue(
   InsertSharedStringItem(sharedString, sstPart).ToString());
 
ws.Save(wsPart);

Hilfsfunktionen zum Erzeugen einer neuen Zeile bzw. Zelle. Beide müssen in aufsteigender Reihenfolge im Markup erscheinen.

 private Row CreateNewRow(ref SheetData sd, UInt32Value rowIndex)
{
  Row newRow = new Row();
  newRow.RowIndex = rowIndex;
  Row lastRow = (Row)sd.Elements<Row>()
    .Where(r => r.RowIndex < rowIndex).LastOrDefault();
  if (lastRow != null)  
    sd.InsertAfter(newRow, lastRow);
  else
    sd.InsertAt(newRow, 0);
  return newRow;
}

private Cell CreateNewCell(ref Row row, string cellRef)
{
  Cell lastCell = row.Elements<Cell>().Where
     (c => string.Compare(c.CellReference.Value, cellRef, true) < 0)
        .LastOrDefault();

  Cell newCell = new Cell();
  if (lastCell != null)
    row.InsertAfter(newCell, lastCell);
  else
    row.InsertAt(newCell, 0);

  return newCell;
}

Die Extensions Class, welche die Funktion RemoveAllButNumbers für Strings zur Verfügung stellt. Damit werden aus der Zelladresse alle Zeichen herausgenommen, die keine Ziffern darstellen. Das Ergebnis entspricht der Zeilennummer.

 internal static class MyExtensions
{

  internal static UInt32 RemoveAllButNumbers(this string s)
  {
    string res = string.Empty;
    char[] numbers = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' };
    foreach (char c in s)
    {
      if (numbers.Contains(c))
      {
        res += c;
      }
    }
    return UInt32.Parse(res);
  }

}

[ Fortsetzung folgt ]

Comments

  • Anonymous
    December 17, 2010
    Sehr cool, bin schon auf den nächsten Teil gespannt... Die Reihe bringt Licht in meine noch etwas vernebelten Bereiche :-)