private List<SSW.RowDfn> GetRowCollection(string[][] result) { List<SSW.RowDfn> rowCollection = new List<SSW.RowDfn>(); for (int i = 0; i < result.Count(); i++) { string[] dataRow = result[i]; SSW.RowDfn row = new SSW.RowDfn(); List<SSW.CellDfn> cellCollection = new List<SSW.CellDfn>(); for (int j = 0; j < dataRow.Count(); j++) { SSW.CellDfn dataCell = new SSW.CellDfn(); dataCell.Value = dataRow[j]; dataCell.HorizontalCellAlignment = SSW.HorizontalCellAlignment.Left; dataCell.CellDataType = CellDataType.String; cellCollection.Add(dataCell); } row.Cells = cellCollection; rowCollection.Add(row); } return rowCollection; }
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; }
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(); }
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(); }
IEnumerable<RowDfn> GetRowsEnum(TestTable table) { var border = CellStyleBorder.CreateBoxBorder(CellStyleBorder.Thin); var defaultCellStyle = new CellStyleDfn { Border = border, }; var dateCellStyle = new CellStyleDfn { Border = border, NumFmt = new CellStyleNumFmt { formatCode = "yyyy/mm/dd" }, }; int rowCount = table.RowCount; var rows = new List<RowDfn>(); for (int r = 0; r < rowCount; r++) { var datarow = table.GetNextRow(); var cells = new CellDfn[] { new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col1 }, new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col2 }, new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col3 }, new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col4 }, new CellDfn { CellDataType = CellDataType.String, Style = defaultCellStyle, Value = datarow.Col5 }, new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col6 }, new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col7 }, new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col8 }, new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col9 }, new CellDfn { CellDataType = CellDataType.Number, Style = defaultCellStyle, Value = datarow.Col10 }, new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col11 }, new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col12 }, new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col13 }, new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col14 }, new CellDfn { CellDataType = CellDataType.Date, Style = dateCellStyle, Value = datarow.Col15 }, }; var row = new RowDfn { Cells = cells }; //yield return row; rows.Add(row); } return rows.ToArray(); }