public static void AddWorksheet(SpreadsheetDocument sDoc, WorksheetDfn worksheetData, int tableCounter) { 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, tableCounter), 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(); }
public static void Write(string fileName, WorkbookDfn workbook) { try { if (fileName == null) { throw new ArgumentNullException("fileName"); } if (workbook == null) { throw new ArgumentNullException("workbook"); } FileInfo fi = new FileInfo(fileName); if (fi.Exists) { fi.Delete(); } // create the blank workbook char[] base64CharArray = _EmptyXlsx .Where(c => c != '\r' && c != '\n').ToArray(); byte[] byteArray = System.Convert.FromBase64CharArray(base64CharArray, 0, base64CharArray.Length); File.WriteAllBytes(fi.FullName, byteArray); // open the workbook, and create the TableProperties sheet, populate it using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(fi.FullName, true)) { WorkbookPart workbookPart = sDoc.WorkbookPart; XDocument wXDoc = workbookPart.GetXDocument(); XElement sheetElement = wXDoc .Root .Elements(S.sheets) .Elements(S.sheet) .Where(s => (string)s.Attribute(SSNoNamespace.name) == "Sheet1") .FirstOrDefault(); if (sheetElement == null) { throw new SpreadsheetWriterInternalException(); } string id = (string)sheetElement.Attribute(R.id); sheetElement.Remove(); workbookPart.PutXDocument(); WorksheetPart sPart = (WorksheetPart)workbookPart.GetPartById(id); workbookPart.DeletePart(sPart); XDocument appXDoc = sDoc .ExtendedFilePropertiesPart .GetXDocument(); XElement vector = appXDoc .Root .Elements(EP.TitlesOfParts) .Elements(VT.vector) .FirstOrDefault(); if (vector != null) { vector.SetAttributeValue(SSNoNamespace.size, 0); XElement lpstr = vector.Element(VT.lpstr); lpstr.Remove(); } XElement vector2 = appXDoc .Root .Elements(EP.HeadingPairs) .Elements(VT.vector) .FirstOrDefault(); XElement variant = vector2 .Descendants(VT.i4) .FirstOrDefault(); if (variant != null) { variant.Value = "1"; } sDoc.ExtendedFilePropertiesPart.PutXDocument(); var tableCounter = 0; if (workbook.Worksheets != null) { foreach (var worksheet in workbook.Worksheets) { AddWorksheet(sDoc, worksheet, ++tableCounter); } } workbookPart.WorkbookStylesPart.PutXDocument(); } } catch (Exception e) { Console.WriteLine("Unhandled exception: {0} in {1}", e.ToString(), e.Source); throw e; } }