public void WriteMapColumns(LayoutColumn map, int sc, int sr) { mc++; if (sc == 0) { writer.WriteElement(new Column() { Min = (uint)mc, Max = (uint)mc, Width = 8, CustomWidth = true }); } sc++; foreach (var column in map) { if (column.Visible) { map.GetVisibleIndex(column, out int c, out int r); c += sc; r += sr; if (column.Count > 0) { WriteMapColumns(column, c, r); } else if (c >= mc) { mc++; writer.WriteElement(new Column() { Min = (uint)mc, Max = (uint)mc, Width = column.Width / 6, CustomWidth = true }); } } } }
public void WriteCellValue(OpenXmlWriter openXmlWriter, string cellValue, int styleIndex, ref int sharedStringMaxIndex, Dictionary <string, int> sharedStringDictionary) { try { List <OpenXmlAttribute> openXmlAttributes = new List <OpenXmlAttribute>(); openXmlAttributes.Add(new OpenXmlAttribute("s", null, styleIndex.ToString())); int parseResult; if (int.TryParse(cellValue, out parseResult)) { openXmlWriter.WriteStartElement(new Cell(), openXmlAttributes); openXmlWriter.WriteElement(new CellValue(cellValue)); openXmlWriter.WriteEndElement(); } else { openXmlAttributes.Add(new OpenXmlAttribute("t", null, "s")); openXmlWriter.WriteStartElement(new Cell(), openXmlAttributes); if (!sharedStringDictionary.ContainsKey(cellValue)) { sharedStringDictionary.Add(cellValue, sharedStringMaxIndex); sharedStringMaxIndex += 1; } openXmlWriter.WriteElement(new CellValue(sharedStringDictionary[cellValue].ToString())); openXmlWriter.WriteEndElement(); } } catch (Exception exception) { LogWriter.LogError("Unable to write cell value to Excel report."); throw exception; } }
private static Cell WriteCell(OpenXmlWriter writer, object value, int rowIndex) { var cell = new Cell(); //cell.CellReference = ""; var attributeListCell = new OpenXmlAttribute[] { }; if (default(object) == value) { cell.DataType = CellValues.String; } else if (typeof(bool).IsInstanceOfType(value)) { cell.DataType = CellValues.Boolean; } else if (typeof(DateTime).IsInstanceOfType(value)) { cell.DataType = CellValues.Date; } else if (typeof(double).IsAssignableFrom(value.GetType())) { cell.DataType = CellValues.Number; } else { cell.DataType = CellValues.String; } writer.WriteStartElement(cell); if (default(object) == value) { var cellValue = new CellValue(""); writer.WriteElement(cellValue); } else if (typeof(CellReference).IsAssignableFrom(value.GetType())) { var cellRef = (CellReference)value; var formula = new DocumentFormat.OpenXml.Spreadsheet.CellFormula(cellRef.formula); writer.WriteElement(formula); var cellValue = new CellValue(cellRef.value); writer.WriteElement(cellValue); // <c r="H2" t="str"> // <f>Manufacturer!A15</f> // <v>30</v> //</c> } else { var cellValue = new CellValue(value.ToString()); writer.WriteElement(cellValue); } writer.WriteEndElement(); return(cell); }
public void AddHeaderRow(params string[] labels) { var row = new Row(labels.Select(x => new Cell { DataType = CellValues.String, CellValue = new CellValue(x) })); _writer.WriteElement(row); }
private void FinishBuilding() { _writer.WriteEndElement(); // end SheetData if (_excelImages.Count > 0) { _writer.WriteElement(GetDrawing()); } _writer.WriteEndElement(); // end Worksheet _writer.Close(); _buildingIsFinished = true; }
public void Initialize() { _writer.WriteStartElement(new SharedStringTable()); // Write initial empty string shared string _sharedStringIdx.Add(string.Empty, 0); _writer.WriteStartElement(new SharedStringItem()); { _writer.WriteElement(new Text { Text = string.Empty }); } _writer.WriteEndElement(); }
static void WriteRandomValuesSAX(string filename, int numRows, int numCols) { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last(); OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart); Row r = new Row(); Cell c = new Cell(); CellValue v = new CellValue("Test"); c.AppendChild(v); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); for (int row = 0; row < numRows; row++) { writer.WriteStartElement(r); for (int col = 0; col < numCols; col++) { writer.WriteElement(c); } writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); } }
public static void AddLine(OpenXmlWriter writer, int row, string[] values) { List <OpenXmlAttribute> attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, row.ToString()) }; writer.WriteStartElement(new Row(), attributes); int col1 = 1; foreach (var cols in values) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(col1) + row), new OpenXmlAttribute("s", "", "1") // Bold (Style 1) }; writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue(cols)); writer.WriteEndElement(); col1++; } writer.WriteEndElement(); }
/// <summary> /// 通过SAX创建工作表 /// </summary> /// <param name="worksheetPart"></param> private void CreateSheetBySAX(WorksheetPart worksheetPart, DrawingsPart drawingsPart) { using (_writer = OpenXmlWriter.Create(worksheetPart)) { _rowIndex = 1; //S: Worksheet _writer.WriteStartElement(new Worksheet()); if (drawingsPart != null) { _writer.WriteElement(new Drawing() { Id = worksheetPart.GetIdOfPart(drawingsPart) }); } InitSheetViews(); InitColumns(); MergeCells(); FillData(); //E: Worksheet _writer.WriteEndElement(); _writer.Close(); } }
public void CreateSharedStringPart(WorkbookPart workbookPart, int sharedStringMaxIndex, Dictionary <string, int> sharedStringDictionary) { try { if (sharedStringMaxIndex > 0) { SharedStringTablePart sharedStringTablePart = workbookPart.AddNewPart <SharedStringTablePart>(); using (OpenXmlWriter openXmlWriter = OpenXmlWriter.Create(sharedStringTablePart)) { openXmlWriter.WriteStartElement(new SharedStringTable()); foreach (var item in sharedStringDictionary) { openXmlWriter.WriteStartElement(new SharedStringItem()); openXmlWriter.WriteElement(new Text(item.Key)); openXmlWriter.WriteEndElement(); } openXmlWriter.WriteEndElement(); } } } catch (Exception exception) { LogWriter.LogError("Unable to create 'SharedStringPart' in Excel report."); throw exception; } }
private void CreateHeaderRow(IEnumerable <ApplicationFieldDefinition> applicationFields, OpenXmlWriter writer, int rowIdx, string applicationName, string schemaId) { var xmlAttributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, rowIdx.ToString(CultureInfo.InvariantCulture)) }; writer.WriteStartElement(new Row(), xmlAttributes); foreach (var applicationField in applicationFields.Where(ShouldShowField())) { var headerStyleId = "8"; if (applicationField.Attribute.StartsWith("#old") && ApplicationConfiguration.ClientName == "hapag" && "r0042ExportExcel".Equals(schemaId)) { headerStyleId = "2"; } //Exporting to Excel, even if field is hidden xmlAttributes = new List <OpenXmlAttribute> { // add new datatype for cell new OpenXmlAttribute("t", null, "str"), // add header style new OpenXmlAttribute("s", null, headerStyleId) }; writer.WriteStartElement(new Cell(), xmlAttributes); writer.WriteElement(new CellValue(GetI18NLabel(applicationField, applicationName))); // this is for Cell writer.WriteEndElement(); } // end Row writer.WriteEndElement(); }
private static void AppendNumericCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer) { // Add a new Excel Cell to our Row writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.Number }); }
private void writeMergedCells(OpenXmlWriter writer) { var mergedCellRanges = new Dictionary <SpreadsheetLocation, System.Drawing.Size>(); foreach (var row in _rows) { for (var i = 0; i < row.Value.RowCells.Count; i++) { var cell = row.Value.RowCells[i]; if (cell.MergedCellsRange != null) { mergedCellRanges[new SpreadsheetLocation(row.Key.RowIndex, row.Key.ColumnIndex + i)] = cell.MergedCellsRange.Value; } } } if (mergedCellRanges.Any()) { writer.WriteStartElement(new MergeCells()); foreach (var cellRange in mergedCellRanges) { var cell1Name = SpreadsheetHelper.ExcelColumnFromNumber(cellRange.Key.ColumnIndex) + cellRange.Key.RowIndex; var cell2Name = SpreadsheetHelper.ExcelColumnFromNumber(cellRange.Key.ColumnIndex + cellRange.Value.Width) + (cellRange.Key.RowIndex + cellRange.Value.Height - 1); var range = cell1Name + ":" + cell2Name; var mergeCell = new MergeCell() { Reference = new StringValue(range) }; writer.WriteElement(mergeCell); } writer.WriteEndElement(); } }
public void CreateSheets(string filename, string sheetname, int sheetid) { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; if (sheetid == 1) { workbookPart.Workbook = new Workbook(); } Workbook workbook = myDoc.WorkbookPart.Workbook; //WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); OpenXmlWriter writer = OpenXmlWriter.Create(workbookPart); writer.WriteStartElement(workbook); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet() { Name = sheetname, SheetId = (uint)sheetid }); // this is for Sheets writer.WriteEndElement(); // this is for Workbook writer.WriteEndElement(); writer.Close(); myDoc.Close(); } }
private void StartReport(WorkbookPart workbookPart, Sheets sheets) { try { WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = sheetIndex, Name = "STIG Discrepancies" }; sheetIndex++; sheets.Append(sheet); _openXmlWriter = OpenXmlWriter.Create(worksheetPart); _openXmlWriter.WriteStartElement(new Worksheet()); WriteReportColumns(); _openXmlWriter.WriteStartElement(new SheetData()); _openXmlWriter.WriteElement(new Row() { Hidden = true }); WriteReportHeaderRow(); } catch (Exception exception) { LogWriter.LogError("Unable to initialize 'STIG Discrepancies' tab."); throw exception; } }
/// <summary> /// 共享字符串 /// </summary> /// <param name="sharedStringTablePart"></param> private void GenerateSharedStringTablePartContent(SharedStringTablePart sharedStringTablePart) { using (OpenXmlWriter writer = OpenXmlWriter.Create(sharedStringTablePart)) { // SharedStringTable 开始 writer.WriteStartElement(new SharedStringTable() { Count = totalCount, UniqueCount = (uint)stringIndexDic.Keys.Count }); foreach (var str in stringIndexDic.Keys) { // SharedStringItem 开始 writer.WriteStartElement(new SharedStringItem()); // 写入 Text writer.WriteElement(new Text(str)); // SharedStringItem 结束 writer.WriteEndElement(); } // SharedStringTable 结束 writer.WriteEndElement(); writer.Close(); } }
private void InsertData(DataTable data) { foreach (DataRow dataRow in data.Rows) { Row r = new Row(); _writer.WriteStartElement(r); foreach (DataColumn col in data.Columns) { Cell c = new Cell { DataType = CellValues.String, CellValue = new CellValue(dataRow[col].ToString()) }; CellFormat format = new CellFormat(); format.BorderId = InsertBorder(GenerateBorder()); c.StyleIndex = InsertCellFormat(_workbookPart, format); _writer.WriteElement(c); } _writer.WriteEndElement(); } //_workbookPart.Workbook.Save(); }
private static void Finish(OpenXmlWriter writer, SpreadsheetDocument xl, WorksheetPart worksheetpart) { // end worksheet writer.WriteEndElement(); writer.Close(); // write root element writer = OpenXmlWriter.Create(xl.WorkbookPart); writer.WriteStartElement(new Workbook()); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet { Name = "Sheet1", SheetId = 1, Id = xl.WorkbookPart.GetIdOfPart(worksheetpart) }); // end Sheets writer.WriteEndElement(); // end Workbook writer.WriteEndElement(); writer.Close(); xl.Close(); }
/// <summary> /// Starts writing sheet element /// </summary> /// <param name="sheetName"> Name of the Sheet. Empty or null sheet name will result in default sheet name.</param> /// <param name="sheetProperties"></param> /// <param name="sheetViewProperties"></param> public void WriteStartSheet(string sheetName = default, OpenExcelSheetProperties sheetProperties = default, OpenExcelSheetViewProperties sheetViewProperties = default, OpenExcelSheetFormatProperties sheetFormatProperties = default) { _colCharacterLengths.Clear(); _rowIdx = _rowIdxReset; var wsPart = _xl.WorkbookPart.AddNewPart <WorksheetPart>(); uint newSheetId = _newSheetId; _workBookWriter.WriteElement(new Sheet() { Name = (string.IsNullOrWhiteSpace(sheetName)) ? $"Sheet{newSheetId}" : sheetName, SheetId = newSheetId, Id = _xl.WorkbookPart.GetIdOfPart(wsPart) }); _workSheetWriter = OpenXmlWriter.Create(wsPart); _workSheetWriter.WriteStartElement(new Worksheet()); WriteSheetProperties(sheetProperties); WriteSheetViewProperties(sheetViewProperties); WriteSheetFormatProperties(sheetFormatProperties); _workSheetWriter.WriteStartElement(new SheetData()); }
/// <summary> /// Writes data to a cell /// </summary> /// <param name="writer">this OpenXmlWriter writer instance</param> /// <param name="cellAddress">The cell address</param> /// <param name="cellValue">The cell value</param> /// <param name="dataType">The data type for the cell</param> public static void WriteCell(this OpenXmlWriter writer, string cellAddress, string cellValue, CellValues dataType) { // default to a standard cell value OpenXmlElement value = new CellValue(cellValue); bool testBool; long testLong; // fix up some values switch (dataType) { // we are handling all strings as inline for performance reasons case CellValues.SharedString: case CellValues.InlineString: dataType = CellValues.InlineString; value = new InlineString(new Text(cellValue)); break; case CellValues.Date: // write the value as a string to the sheet dataType = CellValues.String; break; case CellValues.Number: // this is a safety check as we sometimes get bad values such as N/A in columns if (!long.TryParse(cellValue, out testLong)) { // we default to writing it as a string to be safe dataType = CellValues.InlineString; value = new InlineString(new Text(cellValue)); } break; case CellValues.Boolean: // this is a safety check as we sometimes get bad values such as N/A in columns if (bool.TryParse(cellValue, out testBool)) { value = new CellValue(testBool ? "1" : "0"); } else { // we default to writing it as a string to be safe dataType = CellValues.InlineString; value = new InlineString(new Text(cellValue)); } break; } // write cell xml to the writer writer.WriteStartElement(new Cell() { DataType = dataType, CellReference = cellAddress }); writer.WriteElement(value); writer.WriteEndElement(); }
private void WriteSharedString(XlsxSharedStringCell cell) { var style = !string.IsNullOrEmpty(cell.FormatName) ? Style.CellFormats[cell.FormatName] : "0"; var attributes = new OpenXmlAttribute[] { new OpenXmlAttribute("s", null, style) }.ToList(); attributes.Add(new OpenXmlAttribute("t", null, "s"));//shared string type Writer.WriteStartElement(new Cell(), attributes); if (!ShareStringDictionary.ContainsKey(cell.Value)) { ShareStringDictionary.Add(cell.Value, ShareStringDictionary.Keys.Count()); } //writing the index as the cell value Writer.WriteElement(new CellValue(ShareStringDictionary[cell.Value].ToString())); Writer.WriteEndElement();//cell }
public void WriteRandomValuesSAX(string filename, int numRows, int numCols, int sheetid, int countRows) { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last(); OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart); Row r = new Row(); Cell c = new Cell(); CellValue v = new CellValue("Test"); c.AppendChild(v); var s = new SheetData(); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(s); //writer.WriteStartElement(new Sheet()); for (int row = countRows; row < numRows; row++) { writer.WriteStartElement(r); for (int col = 0; col < numCols; col++) { writer.WriteElement(c); } writer.WriteEndElement(); } //writer.WriteEndElement(); writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); //writer = OpenXmlWriter.Create(myDoc.WorkbookPart); //writer.WriteStartElement(new Workbook()); //writer.WriteStartElement(new Sheets()); //writer.WriteElement(new Sheet() //{ // Name = $"Sheet21{sheetid}", // SheetId = (uint)sheetid, // Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart) //}); //// this is for Sheets //writer.WriteEndElement(); //// this is for Workbook //writer.WriteEndElement(); //writer.Close(); myDoc.Close(); } }
public void Save() { if (!_changed) { return; } if (_ssPart != null) { string originalSSPartId = _wpart.GetIdOfPart(_ssPart); _wpart.DeletePart(originalSSPartId); } SharedStringTablePart newSSPart = _wpart.AddNewPart <SharedStringTablePart>(); using (OpenXmlWriter writer = OpenXmlWriter.Create(newSSPart)) { writer.WriteStartElement(new SharedStringTable()); if (_stringCache == null) { string[] outputList = new string[_indexLookup.Count]; foreach (var i in _indexLookup) { outputList[i.Value] = i.Key; } for (uint idx = 0; idx < outputList.Length; idx++) { writer.WriteStartElement(new SharedStringItem()); writer.WriteElement(new Text(outputList[idx])); writer.WriteEndElement(); } } else { foreach (var i in _stringCache) { writer.WriteStartElement(new SharedStringItem()); writer.WriteElement(new Text(i.Value)); writer.WriteEndElement(); } } writer.WriteEndElement(); } }
private static void AppendTextCell(string cellReference, string cellStringValue, ref OpenXmlWriter writer) { writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String }); }
public void WriteRow(OpenXmlWriter writer, Row row) { writer.WriteStartElement(row); foreach (Cell cell in row) { writer.WriteElement(cell); } writer.WriteEndElement(); }
public static void AppendTextCell(this OpenXmlWriter writer, string cellReference, string cellStringValue) { writer.WriteElement(new Cell { CellValue = new CellValue(cellStringValue), CellReference = cellReference, DataType = CellValues.String }); }
private static void WriteRowCells(OpenXmlWriter oxw, string item) { List <OpenXmlAttribute> oxa = new List <OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("t", null, "str")); oxw.WriteStartElement(new Cell(), oxa); oxw.WriteElement(new CellValue(item)); oxw.WriteEndElement(); }
public static void AppendFormulaCell(this OpenXmlWriter writer, string cellReference, string cellStringValue) { writer.WriteElement(new Cell { CellFormula = new CellFormula(cellStringValue), CellReference = cellReference, DataType = CellValues.Number }); }
private static void WriteAutoFilter(OpenXmlWriter writer, int columnCount) { var autoFilterRange = "A1:" + GetColumnName(columnCount - 1) + "1"; writer.WriteElement(new AutoFilter() { Reference = autoFilterRange }); }
private static void AppendFormulaCell(string cellReference, string cellStringValue, OpenXmlWriter writer) { // Add a new "formula" Excel Cell to our Row writer.WriteElement(new Cell { CellFormula = new CellFormula(cellStringValue), CellReference = cellReference, DataType = CellValues.Number }); }