private static string RowColToString(int row, int col)
{
var str = SpreadsheetMLUtil.IntToColumnId(col) + (row + 1).ToString();
return(str);
}
private static void SerializeRow(SpreadsheetDocument sDoc, XmlWriter xw, int rowCount, RowDfn row, out int numColumns)
{
string ns = S.s.NamespaceName;
xw.WriteStartElement("row", ns);
xw.WriteStartAttribute("r");
xw.WriteValue(rowCount);
xw.WriteEndAttribute();
xw.WriteStartAttribute("spans");
xw.WriteValue("1:" + row.Cells.Count().ToString());
xw.WriteEndAttribute();
int cellCount = 0;
foreach (var cell in row.Cells)
{
if (cell != null)
{
xw.WriteStartElement("c", ns);
xw.WriteStartAttribute("r");
xw.WriteValue(SpreadsheetMLUtil.IntToColumnId(cellCount) + rowCount.ToString());
xw.WriteEndAttribute();
if (cell.Bold != null ||
cell.Italic != null ||
cell.FormatCode != null ||
cell.HorizontalCellAlignment != null)
{
xw.WriteStartAttribute("s");
xw.WriteValue(GetCellStyle(sDoc, cell));
xw.WriteEndAttribute();
}
switch (cell.CellDataType)
{
case CellDataType.Boolean:
xw.WriteStartAttribute("t");
xw.WriteValue("b");
xw.WriteEndAttribute();
break;
case CellDataType.Date:
xw.WriteStartAttribute("t");
xw.WriteValue("d");
xw.WriteEndAttribute();
break;
case CellDataType.Number:
xw.WriteStartAttribute("t");
xw.WriteValue("n");
xw.WriteEndAttribute();
break;
case CellDataType.String:
xw.WriteStartAttribute("t");
xw.WriteValue("str");
xw.WriteEndAttribute();
break;
default:
xw.WriteStartAttribute("t");
xw.WriteValue("str");
xw.WriteEndAttribute();
break;
}
if (cell.Value != null)
{
xw.WriteStartElement("v", ns);
xw.WriteValue(cell.Value);
xw.WriteEndElement();
}
xw.WriteEndElement();
}
cellCount++;
}
xw.WriteEndElement();
numColumns = cellCount;
}
private static void UpdateSeries(ChartPart chartPart, ChartData chartData)
{
UpdateEmbeddedWorkbook(chartPart, chartData);
XDocument cpXDoc = chartPart.GetXDocument();
XElement root = cpXDoc.Root;
var firstSeries = root.Descendants(C.ser).FirstOrDefault();
var numRef = firstSeries.Elements(C.val).Elements(C.numRef).FirstOrDefault();
string sheetName = null;
var f = (string)firstSeries.Descendants(C.f).FirstOrDefault();
if (f != null)
{
sheetName = f.Split('!')[0];
}
// remove all but first series
XName chartType = firstSeries.Parent.Name;
firstSeries.Parent.Elements(C.ser).Skip(1).Remove();
var newSetOfSeries = chartData.SeriesNames
.Select((string sn, int si) =>
{
XElement cat = null;
var oldCat = firstSeries.Elements(C.cat).FirstOrDefault();
if (oldCat == null)
{
throw new OpenXmlPowerToolsException("Invalid chart markup");
}
var catHasFormula = oldCat.Descendants(C.f).Any();
if (catHasFormula)
{
XElement newFormula = null;
if (sheetName != null)
{
newFormula = new XElement(C.f, string.Format("{0}!$A$2:$A${1}", sheetName, chartData.CategoryNames.Length + 1));
}
if (chartData.CategoryDataType == ChartDataType.String)
{
cat = new XElement(C.cat,
new XElement(C.strRef,
newFormula,
new XElement(C.strCache,
new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
chartData.CategoryNames.Select((string cn, int ci) =>
{
var newPt = new XElement(C.pt,
new XAttribute("idx", ci),
new XElement(C.v, chartData.CategoryNames[ci]));
return(newPt);
}))));
}
else
{
cat = new XElement(C.cat,
new XElement(C.numRef,
newFormula,
new XElement(C.numCache,
new XElement(C.formatCode, FormatCodes[chartData.CategoryFormatCode]),
new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
chartData.CategoryNames.Select((string cn, int ci) =>
{
var newPt = new XElement(C.pt,
new XAttribute("idx", ci),
new XElement(C.v, chartData.CategoryNames[ci]));
return(newPt);
}))));
}
}
else
{
if (chartData.CategoryDataType == ChartDataType.String)
{
cat = new XElement(C.cat,
new XElement(C.strLit,
new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
chartData.CategoryNames.Select((string cn, int ci) =>
{
var newPt = new XElement(C.pt,
new XAttribute("idx", ci),
new XElement(C.v, chartData.CategoryNames[ci]));
return(newPt);
})));
}
else
{
cat = new XElement(C.cat,
new XElement(C.numLit,
new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
chartData.CategoryNames.Select((string cn, int ci) =>
{
var newPt = new XElement(C.pt,
new XAttribute("idx", ci),
new XElement(C.v, chartData.CategoryNames[ci]));
return(newPt);
})));
}
}
XElement newCval = null;
if (sheetName == null)
{
newCval = new XElement(C.val,
new XElement(C.numLit,
new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
chartData.CategoryNames.Select((string cn, int ci) =>
{
var newPt = new XElement(C.pt,
new XAttribute("idx", ci),
new XElement(C.v, chartData.Values[si][ci]));
return(newPt);
})));
}
else
{
newCval = new XElement(C.val,
new XElement(C.numRef,
sheetName != null ?
new XElement(C.f, string.Format("{0}!${2}$2:${2}${1}", sheetName, chartData.CategoryNames.Length + 1, SpreadsheetMLUtil.IntToColumnId(si + 1))) : null,
new XElement(C.numCache,
sheetName != null ? numRef.Descendants(C.formatCode) : null,
new XElement(C.ptCount, new XAttribute("val", chartData.CategoryNames.Length)),
chartData.CategoryNames.Select((string cn, int ci) =>
{
var newPt = new XElement(C.pt,
new XAttribute("idx", ci),
new XElement(C.v, chartData.Values[si][ci]));
return(newPt);
}))));
}
var serHasFormula = firstSeries.Descendants(C.f).Any();
XElement tx = null;
if (serHasFormula)
{
XElement newFormula = null;
if (sheetName != null)
{
newFormula = new XElement(C.f, string.Format("{0}!${1}$1", sheetName, SpreadsheetMLUtil.IntToColumnId(si + 1)));
}
tx = new XElement(C.tx,
new XElement(C.strRef,
newFormula,
new XElement(C.strCache,
new XElement(C.ptCount, new XAttribute("val", 1)),
new XElement(C.pt,
new XAttribute("idx", 0),
new XElement(C.v, chartData.SeriesNames[si])))));
}
else
{
tx = new XElement(C.tx,
new XElement(C.v, chartData.SeriesNames[si]));
}
XElement newSer = null;
if (chartType == C.area3DChart || chartType == C.areaChart)
{
newSer = new XElement(C.ser,
// common
new XElement(C.idx, new XAttribute("val", si)),
new XElement(C.order, new XAttribute("val", si)),
tx,
firstSeries.Elements(C.spPr),
// CT_AreaSer
firstSeries.Elements(C.pictureOptions),
firstSeries.Elements(C.dPt),
firstSeries.Elements(C.dLbls),
firstSeries.Elements(C.trendline),
firstSeries.Elements(C.errBars),
cat,
newCval,
firstSeries.Elements(C.extLst));
}
else if (chartType == C.bar3DChart || chartType == C.barChart)
{
newSer = new XElement(C.ser,
// common
new XElement(C.idx, new XAttribute("val", si)),
new XElement(C.order, new XAttribute("val", si)),
tx,
firstSeries.Elements(C.spPr),
// CT_BarSer
firstSeries.Elements(C.invertIfNegative),
firstSeries.Elements(C.pictureOptions),
firstSeries.Elements(C.dPt),
firstSeries.Elements(C.dLbls),
firstSeries.Elements(C.trendline),
firstSeries.Elements(C.errBars),
cat,
newCval,
firstSeries.Elements(C.shape),
firstSeries.Elements(C.extLst));
}
else if (chartType == C.line3DChart || chartType == C.lineChart || chartType == C.stockChart)
{
newSer = new XElement(C.ser,
// common
new XElement(C.idx, new XAttribute("val", si)),
new XElement(C.order, new XAttribute("val", si)),
tx,
firstSeries.Elements(C.spPr),
// CT_LineSer
firstSeries.Elements(C.marker),
firstSeries.Elements(C.dPt),
firstSeries.Elements(C.dLbls),
firstSeries.Elements(C.trendline),
firstSeries.Elements(C.errBars),
cat,
newCval,
firstSeries.Elements(C.smooth),
firstSeries.Elements(C.extLst));
}
else if (chartType == C.doughnutChart || chartType == C.ofPieChart || chartType == C.pie3DChart || chartType == C.pieChart)
{
newSer = new XElement(C.ser,
// common
new XElement(C.idx, new XAttribute("val", si)),
new XElement(C.order, new XAttribute("val", si)),
tx,
firstSeries.Elements(C.spPr),
// CT_PieSer
firstSeries.Elements(C.explosion),
firstSeries.Elements(C.dPt),
firstSeries.Elements(C.dLbls),
cat,
newCval,
firstSeries.Elements(C.extLst));
}
else if (chartType == C.surface3DChart || chartType == C.surfaceChart)
{
newSer = new XElement(C.ser,
// common
new XElement(C.idx, new XAttribute("val", si)),
new XElement(C.order, new XAttribute("val", si)),
tx,
firstSeries.Elements(C.spPr),
// CT_SurfaceSer
cat,
newCval,
firstSeries.Elements(C.extLst));
}
if (newSer == null)
{
throw new OpenXmlPowerToolsException("Unsupported chart type");
}
int accentNumber = (si % 6) + 1;
newSer = (XElement)UpdateAccentTransform(newSer, accentNumber);
return(newSer);
});
firstSeries.ReplaceWith(newSetOfSeries);
chartPart.PutXDocument();
}
public static void AddWorksheet(SpreadsheetDocument sDoc, WorksheetDfn worksheetData)
{
Regex validSheetName = new Regex(@"^[^'*\[\]/\\:?][^*\[\]/\\:?]{0,30}$");
if (!validSheetName.IsMatch(worksheetData.Name))
{
throw new InvalidSheetNameException(worksheetData.Name);
}
// throw WorksheetAlreadyExistsException if a sheet with the same name (case-insensitive) already exists in the workbook
string UCName = worksheetData.Name.ToUpper();
XDocument wXDoc = sDoc.WorkbookPart.GetXDocument();
if (wXDoc
.Root
.Elements(S.sheets)
.Elements(S.sheet)
.Attributes(SSNoNamespace.name)
.Select(a => ((string)a).ToUpper())
.Contains(UCName))
{
throw new WorksheetAlreadyExistsException(worksheetData.Name);
}
// create the worksheet with the supplied name
XDocument appXDoc = sDoc
.ExtendedFilePropertiesPart
.GetXDocument();
XElement vector = appXDoc
.Root
.Elements(EP.TitlesOfParts)
.Elements(VT.vector)
.FirstOrDefault();
if (vector != null)
{
int?size = (int?)vector.Attribute(SSNoNamespace.size);
if (size == null)
{
size = 1;
}
else
{
size = size + 1;
}
vector.SetAttributeValue(SSNoNamespace.size, size);
vector.Add(
new XElement(VT.lpstr, worksheetData.Name));
XElement i4 = appXDoc
.Root
.Elements(EP.HeadingPairs)
.Elements(VT.vector)
.Elements(VT.variant)
.Elements(VT.i4)
.FirstOrDefault();
if (i4 != null)
{
i4.Value = ((int)i4 + 1).ToString();
}
sDoc.ExtendedFilePropertiesPart.PutXDocument();
}
WorkbookPart workbook = sDoc.WorkbookPart;
string rId = "R" + Guid.NewGuid().ToString().Replace("-", "");
WorksheetPart worksheetPart = workbook.AddNewPart <WorksheetPart>(rId);
XDocument wbXDoc = workbook.GetXDocument();
XElement sheets = wbXDoc.Descendants(S.sheets).FirstOrDefault();
sheets.Add(
new XElement(S.sheet,
new XAttribute(SSNoNamespace.name, worksheetData.Name.ToString()),
new XAttribute(SSNoNamespace.sheetId, sheets.Elements(S.sheet).Count() + 1),
new XAttribute(R.id, rId)));
workbook.PutXDocument();
string ws = S.s.ToString();
string relns = R.r.ToString();
using (Stream partStream = worksheetPart.GetStream(FileMode.Create, FileAccess.Write))
{
using (XmlWriter partXmlWriter = XmlWriter.Create(partStream))
{
partXmlWriter.WriteStartDocument();
partXmlWriter.WriteStartElement("worksheet", ws);
partXmlWriter.WriteStartElement("sheetData", ws);
int numColumnHeadingRows = 0;
int numColumns = 0;
int numColumnsInRows = 0;
int numRows;
if (worksheetData.ColumnHeadings != null)
{
RowDfn row = new RowDfn
{
Cells = worksheetData.ColumnHeadings
};
SerializeRows(sDoc, partXmlWriter, new[] { row }, 1, out numColumns, out numColumnHeadingRows);
}
SerializeRows(sDoc, partXmlWriter, worksheetData.Rows, numColumnHeadingRows + 1, out numColumnsInRows,
out numRows);
int totalRows = numColumnHeadingRows + numRows;
int totalColumns = Math.Max(numColumns, numColumnsInRows);
if (worksheetData.ColumnHeadings != null && worksheetData.TableName != null)
{
partXmlWriter.WriteEndElement();
string rId2 = "R" + Guid.NewGuid().ToString().Replace("-", "");
partXmlWriter.WriteStartElement("tableParts", ws);
partXmlWriter.WriteStartAttribute("count");
partXmlWriter.WriteValue(1);
partXmlWriter.WriteEndAttribute();
partXmlWriter.WriteStartElement("tablePart", ws);
partXmlWriter.WriteStartAttribute("id", relns);
partXmlWriter.WriteValue(rId2);
TableDefinitionPart tdp = worksheetPart.AddNewPart <TableDefinitionPart>(rId2);
XDocument tXDoc = tdp.GetXDocument();
XElement table = new XElement(S.table,
new XAttribute(SSNoNamespace.id, 1),
new XAttribute(SSNoNamespace.name, worksheetData.TableName),
new XAttribute(SSNoNamespace.displayName, worksheetData.TableName),
new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString()),
new XAttribute(SSNoNamespace.totalsRowShown, 0),
new XElement(S.autoFilter,
new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString())),
new XElement(S.tableColumns,
new XAttribute(SSNoNamespace.count, totalColumns),
worksheetData.ColumnHeadings.Select((ch, i) =>
new XElement(S.tableColumn,
new XAttribute(SSNoNamespace.id, i + 1),
new XAttribute(SSNoNamespace.name, ch.Value)))),
new XElement(S.tableStyleInfo,
new XAttribute(SSNoNamespace.name, "TableStyleMedium2"),
new XAttribute(SSNoNamespace.showFirstColumn, 0),
new XAttribute(SSNoNamespace.showLastColumn, 0),
new XAttribute(SSNoNamespace.showRowStripes, 1),
new XAttribute(SSNoNamespace.showColumnStripes, 0)));
tXDoc.Add(table);
tdp.PutXDocument();
}
}
}
sDoc.WorkbookPart.WorkbookStylesPart.PutXDocument();
sDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
}