/// <summary>
/// Creates the new item cell in the specified worksheet getting defaults from item-* attributes and from the individual cell settings.
/// </summary>
/// <param name="sheet">The worksheet in which to create the cell.</param>
/// <param name="row">Row number.</param>
/// <param name="col">Column number.</param>
/// <param name="configurationElement">Element that contains item-* configuration for the cell.</param>
/// <param name="itemElement">The item element that may contains settings for particular cell.</param>
/// <returns>Created cell.</returns>
private static Cell CreateNewItemCell(Worksheet sheet, int row, int col, XElement configurationElement, XElement itemElement)
{
if (sheet == null)
{
throw new ArgumentNullException("sheet", "sheet cannot be null");
}
if (configurationElement == null)
{
throw new ArgumentNullException("configurationElement", "configurationElement cannot be null");
}
if (itemElement == null)
{
throw new ArgumentNullException("itemElement", "itemElement cannot be null");
}
XElement itemConfigElement = new XElement(configurationElement.Name);
var attribs = from attr in configurationElement.Attributes()
where attr.Name.LocalName.StartsWith("item-", StringComparison.Ordinal) == true
select attr;
foreach (XAttribute attr in attribs)
{
itemConfigElement.Add(new XAttribute(attr.Name.LocalName.Substring(5), attr.Value));
}
MakoPrintXls.OverrideXmlAttributes(itemElement.Parent, itemConfigElement);
MakoPrintXls.OverrideXmlAttributes(itemElement, itemConfigElement);
return(MakoPrintXls.CreateNewCell(sheet, row, col, itemConfigElement));
}
/// <summary>
/// Processes the main table of the output document.
/// </summary>
/// <param name="sheetXml">Input sheet xml.</param>
/// <param name="sheet">Worksheet to operate on.</param>
/// <param name="startingRow">Row number to start.</param>
private static void ProcessTable(XElement sheetXml, Worksheet sheet, int startingRow)
{
var columns = sheetXml.Element("table").Element("configuration").Elements();
int columnIndex = 1;
foreach (XElement column in columns)
{
switch (column.Attribute("type").Value)
{
case "autonumbering":
MakoPrintXls.ProcessAutonumberColumn(sheetXml, sheet, startingRow, columnIndex, column.Name.LocalName);
break;
case "text":
MakoPrintXls.ProcessTextColumn(sheetXml, sheet, startingRow, columnIndex, column.Name.LocalName);
break;
case "decimal":
MakoPrintXls.ProcessDoubleColumn(sheetXml, sheet, startingRow, columnIndex, column.Name.LocalName);
break;
case "money":
MakoPrintXls.ProcessMoneyColumn(sheetXml, sheet, startingRow, columnIndex, column.Name.LocalName);
break;
case "decimal2":
MakoPrintXls.ProcessDecimal2Column(sheetXml, sheet, startingRow, columnIndex, column.Name.LocalName);
break;
}
columnIndex++;
}
}
/// <summary>
/// Processes the column of the output document as double column.
/// </summary>
/// <param name="sheetXml">Input sheet xml.</param>
/// <param name="sheet">Worksheet to operate on.</param>
/// <param name="startingRow">Row number to start.</param>
/// <param name="columnIndex">1-based index of the column.</param>
/// <param name="columnName">Name of the column.</param>
private static void ProcessDoubleColumn(XElement sheetXml, Worksheet sheet, int startingRow, int columnIndex, string columnName)
{
MakoPrintXls.WriteTableHeader(sheetXml, sheet, startingRow, columnIndex, columnName);
var items = sheetXml.Element("table").Element("items").Elements();
XElement configElement = sheetXml.Element("table").Element("configuration").Element(columnName);
int i = 1;
foreach (XElement item in items)
{
if (item.Element(columnName) != null)
{
Cell c = MakoPrintXls.CreateNewItemCell(sheet, startingRow + i++, columnIndex, configElement, item.Element(columnName));
if (item.Element(columnName).Value.Length > 0)
{
c.Value = Convert.ToDouble(item.Element(columnName).Value, CultureInfo.InvariantCulture);
}
else
{
c.Value = null;
}
}
}
}
/// <summary>
/// Writes the header of the main table.
/// </summary>
/// <param name="sheetXml">Input sheet xml.</param>
/// <param name="sheet">Worksheet to operate on.</param>
/// <param name="row">Header's row number.</param>
/// <param name="columnIndex">1-based index of the column.</param>
/// <param name="columnName">Name of the column.</param>
private static void WriteTableHeader(XElement sheetXml, Worksheet sheet, int row, int columnIndex, string columnName)
{
XElement columnDef = sheetXml.Element("table").Element("configuration").Element(columnName);
Cell c = MakoPrintXls.CreateNewCell(sheet, row, columnIndex, columnDef);
//write the value
c.Value = columnDef.Value;
}
/// <summary>
/// Generates an XLS file from the specified xml to the specified output stream.
/// </summary>
/// <param name="xml">Input xml.</param>
/// <param name="output">Output stream.</param>
public static void Generate(string xml, Stream output)
{
XDocument xmlDoc = XDocument.Parse(xml);
XlsDocument xls = new XlsDocument();
foreach (XElement sheetElement in xmlDoc.Root.Elements("sheet"))
{
MakoPrintXls.ProcessSheet(xls, sheetElement);
}
output.Write(xls.Bytes.ByteArray, 0, xls.Bytes.Length);
output.Flush();
}
/// <summary>
/// Processes the column of the output document as autonumbering column.
/// </summary>
/// <param name="sheetXml">Input sheet xml.</param>
/// <param name="sheet">Worksheet to operate on.</param>
/// <param name="startingRow">Row number to start.</param>
/// <param name="columnIndex">1-based index of the column.</param>
/// <param name="columnName">Name of the column.</param>
private static void ProcessAutonumberColumn(XElement sheetXml, Worksheet sheet, int startingRow, int columnIndex, string columnName)
{
MakoPrintXls.WriteTableHeader(sheetXml, sheet, startingRow, columnIndex, columnName);
var items = sheetXml.Element("table").Element("items").Elements();
XElement configElement = sheetXml.Element("table").Element("configuration").Element(columnName);
int i = 1;
foreach (XElement item in items)
{
Cell c = MakoPrintXls.CreateNewItemCell(sheet, startingRow + i, columnIndex, configElement, item);
c.Value = i++;
}
}
/// <summary>
/// Processes the document header of the output document.
/// </summary>
/// <param name="sheetXml">Input sheet xml.</param>
/// <param name="sheet">Worksheet to operate on.</param>
/// <returns><c>true</c> if the header exists; otherwise <c>false</c>.</returns>
private static bool ProcessDocumentHeader(XElement sheetXml, Worksheet sheet)
{
XElement titleElement = sheetXml.Element("header");
if (titleElement != null)
{
int columns = sheetXml.Element("table").Element("configuration").Elements().Count();
Cell c = MakoPrintXls.CreateNewCell(sheet, 1, 1, titleElement);
c.Value = titleElement.Value;
sheet.AddMergeArea(new MergeArea(1, 1, 1, columns));
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Processes the sheet.
/// </summary>
/// <param name="xls">Main XLS document.</param>
/// <param name="sheetXml">Input sheet xml.</param>
private static void ProcessSheet(XlsDocument xls, XElement sheetXml)
{
//default sheet name
string sheetName = "Arkusz";
//set the sheet name if exists
if (sheetXml.Attribute("name") != null)
{
sheetName = sheetXml.Attribute("name").Value;
}
Worksheet sheet = xls.Workbook.Worksheets.Add(sheetName);
int startingRow = 1;
if (MakoPrintXls.ProcessDocumentHeader(sheetXml, sheet))
{
startingRow += 2;
}
MakoPrintXls.ProcessTable(sheetXml, sheet, startingRow);
}
/// <summary>
/// Creates the new cell in the specified worksheet.
/// </summary>
/// <param name="sheet">The worksheet in which to create the cell.</param>
/// <param name="row">Row number.</param>
/// <param name="col">Column number.</param>
/// <param name="configurationElement">Element that contains configuration for the cell.</param>
/// <returns>Created cell.</returns>
private static Cell CreateNewCell(Worksheet sheet, int row, int col, XElement configurationElement)
{
if (sheet == null)
{
throw new ArgumentNullException("sheet", "sheet cannot be null");
}
if (configurationElement == null)
{
throw new ArgumentNullException("configurationElement", "configurationElement cannot be null");
}
Color backgroundColor = null;
if (configurationElement.Attribute("backgroundColor") != null)
{
backgroundColor = Utils.ParseColor(configurationElement.Attribute("backgroundColor").Value);
}
FontWeight?fontWeight = null;
if (configurationElement.Attribute("fontWeight") != null)
{
fontWeight = (FontWeight)Enum.Parse(typeof(FontWeight), configurationElement.Attribute("fontWeight").Value, true);
}
ushort border = 0;
if (configurationElement.Attribute("border") != null)
{
border = Convert.ToUInt16(configurationElement.Attribute("border").Value, CultureInfo.InvariantCulture);
}
HorizontalAlignments horizontalAlignment = HorizontalAlignments.Default;
if (configurationElement.Attribute("horizontalAlignment") != null)
{
horizontalAlignment = (HorizontalAlignments)Enum.Parse(typeof(HorizontalAlignments), configurationElement.Attribute("horizontalAlignment").Value, true);
}
bool italic = false;
UnderlineTypes?underlineType = null;
bool struckOut = false;
if (configurationElement.Attribute("italic") != null && configurationElement.Attribute("italic").Value == "true")
{
italic = true;
}
if (configurationElement.Attribute("underline") != null)
{
underlineType = (UnderlineTypes)Enum.Parse(typeof(UnderlineTypes), configurationElement.Attribute("underline").Value, true);
}
if (configurationElement.Attribute("struckOut") != null && configurationElement.Attribute("struckOut").Value == "true")
{
struckOut = true;
}
FontColor color = FontColor.Black;
if (configurationElement.Attribute("color") != null)
{
color = (FontColor)Enum.Parse(typeof(FontColor), configurationElement.Attribute("color").Value, true);
}
string fontName = null;
if (configurationElement.Attribute("fontName") != null)
{
fontName = configurationElement.Attribute("fontName").Value;
}
ushort?fontSize = null;
if (configurationElement.Attribute("fontSize") != null)
{
fontSize = Convert.ToUInt16(configurationElement.Attribute("fontSize").Value, CultureInfo.InvariantCulture);
}
return(MakoPrintXls.CreateNewCell(sheet, row, col, fontWeight, border, backgroundColor, horizontalAlignment, italic, underlineType, struckOut, color, fontName, fontSize));
}
