Open XML Dateien aufbauen und verändern (Teil 5)
Stylesheets erzeugen und verwenden
So, ich hoffe, alle hatten ein einigermaßen geruhsames Weihnachtsfest und die Nachwirkungen des Silvesterparty sind überwunden.
Im letzten Teil haben wir bereits die Zusammenhänge aufgezeigt. Jetzt geht es weiter mit der Implementierung der Routine zum Erzeugen des Stylesheets. Wie schon mehrfach erwähnt muss das nicht immer getan werden. Verwendet werden kann auch ein Stylesheet aus einer vorhandenen Excel-Arbeitsmappe, sofern alle gewünschten Formatvorlagen dort definiert wurden.
Grob betrachtet gliedert sich die Routine in 7 Teile:
static class StylesClass
{
internal static Stylesheet GenerateStylesXml()
{ Stylesheet ssh = new Stylesheet();
#region +++ Add Number Formats +++
#region +++ Add Font Information +++
#region +++ Add Fill Styles +++
#region +++ Add Border Styles +++
#region +++ Add Cell Style Formats +++
#region +++ Add Cell Formats // Master Formatting Records +++
#region +++ Add Cell Styles +++
return ssh;
}
}
Zuerst müssen die Zahlenformate definiert werden, die dann in der Zellformatierung und in Formatvorlagen verwendet werden. Darin wird eine ID verwendet, die jedes einzelne Format eindeutig identifiziert. Da Excel schon eingebaute Formate besitzt, müssen wir hinter deren ID starten. Die Werte von 0 bis 164 sind m.W. für interne Formate reserviert. Neben der ID ist der Formatcode erforderlich, den wir aus Excel selbst kennen.
Mit Hilfe der Object Initializer kann der Code verkürzt werden.
#region +++ Add Number Formats +++
uint NumFmtId = 165;
NumberingFormats nfs = new NumberingFormats();
NumberingFormat nf1 = new NumberingFormat()
{
NumberFormatId = NumFmtId++,
FormatCode = "0"
};
nfs.Append(nf1);
NumberingFormat nf2 = new NumberingFormat()
{
NumberFormatId = NumFmtId++,
FormatCode ="#.##0,00 €;[Red]-#.##0,00 €"
};
nfs.Append(nf2);
NumberingFormat nf3 = new NumberingFormat()
{
NumberFormatId = NumFmtId++,
FormatCode = "0 \"km/h\""
};
nfs.Append(nf3);
NumberingFormat nf4 = new NumberingFormat()
{
NumberFormatId = NumFmtId++,
FormatCode = "dd.mm.yyyy"
};
nfs.Append(nf4);
NumberingFormat nf5 = new NumberingFormat()
{
NumberFormatId = NumFmtId,
FormatCode = "dddd, dd. mmmm yyyy"
};
nfs.Append(nf5);
nfs.Count = (uint)nfs.ChildElements.Count;
ssh.Append(nfs);
#endregion
Nun folgen die Font-Informationen. Da Fonts per Index referenziert werden, ist keine ID nötig. Wenn allerdings derselbe Font zusätzlich jeweils mit dem Schnitt kursiv bzw. fett benötigt wird, so sind 3 verschiedene Font-Einträge erforderlich.
#region +++ Add Font Information +++
Fonts fts = new Fonts();
Font ft = new Font()
{
FontName = new FontName() { Val = "Calibri" },
FontSize = new FontSize() { Val = 11D }
};
fts.Append(ft);
ft = new Font()
{
FontName = new FontName() { Val = "Aharoni" },
FontSize = new FontSize() { Val = 18D }
};
fts.Append(ft);
ft = new Font()
{
FontName = new FontName() { Val = "Bradley Hand ITC" },
FontSize = new FontSize() { Val = 20D },
Bold = new Bold()
};
fts.Append(ft);
ft = new Font()
{
FontName = new FontName() { Val = "Bernard MT Condensed" },
FontSize = new FontSize() { Val = 16D }
};
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
ssh.Append(fts);
#endregion
Die Füllmuster der Zelle werden hier definiert und ebenfalls über den Index referenziert:
#region +++ Add Fill Styles +++
Fills fis = new Fills();
Fill fi =new Fill()
{
PatternFill = new PatternFill() { PatternType = PatternValues.None }
};
fis.Append(fi);
fi =new Fill()
{
PatternFill = new PatternFill() { PatternType = PatternValues.LightGray }
};
fis.Append(fi);
fis.Count = (uint)fis.ChildElements.Count;
ssh.Append(fis);
#endregion
Die Randeinstellungen der Zelle sind hier nur gekürzt dargestellt:
#region +++ Add Border Styles +++
Borders bos = new Borders();
Border bo = new Border() { … };
bos.Append(bo);
bo =new Border()
{
LeftBorder = new LeftBorder() { Style = BorderStyleValues.Thin },
RightBorder = new RightBorder() { Style = BorderStyleValues.Thin },
TopBorder = new TopBorder() { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder() { Style = BorderStyleValues.Thin }
};
bos.Append(bo);
bos.Count = (uint)bos.ChildElements.Count;
ssh.Append(bos);
#endregion
Für die weiter unten mit Namen definierten eingebauten Formatvorlagen werden hier Standardeinstellungen definiert. Diese können jederzeit durch direkte Formatierung (Master Cell Records) überschrieben werden.
#region +++ Add Cell Style Formats +++
CellStyleFormats cstfs = new CellStyleFormats();
CellFormat cfmt = new CellFormat()
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
};
cstfs.Append(cfmt);
cstfs.Count = (uint)cstfs.ChildElements.Count;
ssh.Append(cstfs);
#endregion
Ein Beispiel aus einer Arbeitsmappe zeigt mögliche Definitionen:
Bei den Master Cell Records laufen alle Fäden zusammen. Jeder Zelle bezieht sich auf einen Index in dieser Tabelle. Hier werden die Abweichungen vom Standardformat „Normal“ definiert. Alles, was nicht definiert wurde, wird von „Normal“ übernommen. Über die FormatId wird auf die benannten Formatvorlagen verwiesen.
#region +++ Add Cell Formats // Master Formatting Records +++
CellFormats cfx = new CellFormats();
CellFormat cf; cf = new CellFormat()
{
NumberFormatId = nf1.NumberFormatId,
FontId = 0 ,
FormatId = 0
};
cfx.Append(cf);
cf = new CellFormat()
{
NumberFormatId = nf1.NumberFormatId,
FontId = 0 ,
FormatId = 0
};
cfx.Append(cf);
cf = new CellFormat()
{
NumberFormatId = nf3.NumberFormatId,
FontId = 1 ,
FormatId = 0
};
cfx.Append(cf);
cf = new CellFormat()
{
NumberFormatId = nf5.NumberFormatId,
FontId = 2,
BorderId = 1,
FillId = 1 ,
FormatId = 0
};
Alignment al = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cf.Append(al);
cfx.Append(cf);
cf = new CellFormat()
{
NumberFormatId = nf1.NumberFormatId,
FontId =2 ,
FormatId =0
};
al = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Right
};
cf.Append(al);
cfx.Append(cf);
cf = new CellFormat()
{
NumberFormatId = nf1.NumberFormatId,
FontId = 3 ,
FormatId = 0
};
cfx.Append(cf);
cf =new CellFormat()
{
NumberFormatId = 12,
FontId = 3 ,
FormatId = 0
};
al = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cf.Append(al);
cfx.Append(cf);
cfx.Count = (uint)cfx.ChildElements.Count;
ssh.Append(cfx);
#endregion
Die Definition der Namen benannter Formatvorlagen erfolgt hier. Im Anhang H der ISO/IEC 29500 Formatbeschreibung findet man eine Auflistung der vordefinierten Formate. Die BuildInID verweist darauf. Es muss zumindest die Vorlage „Normal“ definiert werden, da alle Zellen erst mal diese Formatvorlage erhalten.
#region +++ Add Cell Styles +++
CellStyles csts = new CellStyles();
CellStyle cst =new CellStyle()
{
Name ="Normal",
FormatId =0,
BuiltinId =0
};
csts.Append(cst);
csts.Count = (uint)csts.ChildElements.Count;
ssh.Append(csts);
#endregion
Ein Beispiel aus einer Arbeitsmappe zeigt mögliche verwendete Formatvorlagen:
Der in Teil 1 schon einmal rudimentär aufgezeigte Code zum Erstellen der Zellinhalte sieht ausformuliert so aus:
// worksheet.xml erzeugen:
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
ws.AppendChild(sd);
Row r1 =new Row();
r1.RowIndex =1;
// Create InlineString:
r1.AppendChild(CreateCellWithInlineString("A1", "Der Zellwert als String", 0));
// Create Number Entry:
r1.AppendChild(CreateCellWithNumber("B1", "42", 2));
// Create Formula:
r1.AppendChild(CreateCellWithFormula("C1", "B1*5", 1));
// Create SharedString:
r1.AppendChild(CreateCellWithSharedString("D1", "Microsoft", partSharedStrings, 4));
Row r3 =new Row();
r3.RowIndex =3;
r3.AppendChild(CreateCellWithSharedString("A3", "Jens Häupel", partSharedStrings, 4));
r3.AppendChild(CreateCellWithSharedString("B3", "Microsoft", partSharedStrings, 5));
Row r5 =new Row();
r5.RowIndex =5;
// Create Date Entry:
r5.AppendChild(CreateCellWithNumber("A5", "40536", 3));
// Create Fraction:
r5.AppendChild(CreateCellWithNumber("B5", "2.75", 6));
sd.AppendChild(r1);
sd.AppendChild(r3);
sd.AppendChild(r5);
ws.Save(partWS);
Daraus ergibt sich diese Tabelle in Excel. Man sieht die verschiedenen Formatierungenin Bezug auf Zeichensatz, Schriftschnitt und –größe sowie Ausrichtung.
Aber halt, das ist eigentlich nicht das korrekte Ergebnis. Das sieht nämlich so aus:
Hier wird eine Limitierung deutlich: Open XML ist zwar sehr gut geeignet, Daten aus Dateien zu extrahieren oder auch Inhalte zu verändern oder erzeugen, auf eine Sache hat Open XML aber keinen Einfluss: das Rendering. Dafür werden Informationen über Drucker- und Grafikengine benötigt. Letztendlich ist es möglich, z.B. die optimale Breite einer Zelle, ausgehend von einer gegebenen Schrift (incl. Größe, Schnitt und Skalierung) zu berechnen und auch per Open XML einzustellen.
Die exakte Definition, wie die Berechnung zu erfolgen hat, findet sich in Abschnitt 18.3.1.13 der Open XML Dokumentation.
Die Spaltenbreite kann man wiederum setzen, indem einzelne Spalten definiert und der Columns Collection hinzugefügt werden:
Columns cols = new Columns(
new Column() { Min =1, Max =1, Width =12, CustomWidth =true },
new Column() { Min =1, Max =1, Width =12, CustomWidth =true } );
ws.Append(cols);
Ganz am Ende noch ein Blick in die Shared String Tabelle. Obwohl der Begriff „Microsoft“ zweimal verwendet wurde, erscheint er richtigerweise nur einmal als Shared String:
[Fortsetzung folgt]
Comments
Anonymous
June 09, 2011
Hi Jens, vielen Dank für das Beispiel-Projekt und die Erklärungen! Ich habe mir inzwischen ein eigenes kleines Framework zusammen gebastelt, mit dem ich Excel-Dateien erstellen kann. Allerdings wollte ich mir einiges "vereinfachen" und habe deswegen ein Excel mit Formeln ausgestattet, welches ich nur noch einlese und mit Zahlen "bestücke". Eigentlich dachte ich, beim Öffnen der Datei würden alle Formeln neu berechnet (Berechnungsoptionen --> Automatisch ist eingeschaltet) - werden sie aber nicht! Erst wenn ich auf die Zelle gehe, die Maus oben ins Eingabefeld setze und "Return" drücke wird - wie durch Zauberhand - plötzlich eine Zahl berechnet. Ist das ein bekanntes Problem? Oder habe ich etwas vergessen einzugeben? Wenn ich Formeln per Code in ein bestehendes Excel schreibe, tritt dieses Problem nicht auf... Vielen Dank! Viele Grüße MorgaineAnonymous
June 28, 2011
Morgaine, Excel berechnet Formeln, die außerhalb geändert wurde, nur dann automatisch neu, wenn der Value Tag komplett fehlt. Also bei: <c r="B4"> <f>B2+B3</f> <v>9</v> </c> oder <c r="B4"> <f>B2+B3</f> <v></v> </c> wird nicht neu berechnet. Dagegen wird bei <c r="B4"> <f>B2+B3</f> </c> beim Öffnen in Excel neu berechnet. Das "Automatisch berechnen" gilt nur für Änderungen im UI von Excel. Gruß, Jens