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(); }