private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.UTF8); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); string cellValue = ""; string cellReference = ""; // Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable. // // We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual // cells of data, we'll know if to write Text values or Numeric cell values. int numberOfColumns = dt.Columns.Count; bool[] IsIntegerColumn = new bool[numberOfColumns]; bool[] IsFloatColumn = new bool[numberOfColumns]; bool[] IsDateColumn = new bool[numberOfColumns]; string[] excelColumnNames = new string[numberOfColumns]; for (int n = 0; n < numberOfColumns; n++) { excelColumnNames[n] = GetExcelColumnName(n); } // // Create the Header row in our Excel Worksheet // uint rowIndex = 1; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dt.Columns[colInx]; AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName, writer); IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int")); IsFloatColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single"); IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime"); } writer.WriteEndElement(); // End of header "Row" // // Now, step through each row of data in our DataTable... // double cellFloatValue = 0; CultureInfo ci = new CultureInfo("en-US"); foreach (DataRow dr in dt.Rows) { // ...create a new row, and append a set of this row's data to it. ++rowIndex; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { cellValue = dr.ItemArray[colInx].ToString(); cellValue = ReplaceHexadecimalSymbols(cellValue); cellReference = excelColumnNames[colInx] + rowIndex.ToString(); // Create cell with data if (IsIntegerColumn[colInx] || IsFloatColumn[colInx]) { // For numeric cells without any decimal places. // If this numeric value is NULL, then don't write anything to the Excel file. cellFloatValue = 0; if (double.TryParse(cellValue, out cellFloatValue)) { cellValue = cellFloatValue.ToString(ci); AppendNumericCell(cellReference, cellValue, writer); } } else if (IsDateColumn[colInx]) { // This is a date value. DateTime dateValue; if (DateTime.TryParse(cellValue, out dateValue)) { AppendDateCell(cellReference, dateValue, writer); } else { // This should only happen if we have a DataColumn of type "DateTime", but this particular value is null/blank. AppendTextCell(cellReference, cellValue, writer); } } else { // For text cells, just write the input data straight out to the Excel file. AppendTextCell(cellReference, cellValue, writer); } } writer.WriteEndElement(); // End of Row } writer.WriteEndElement(); // End of SheetData writer.WriteEndElement(); // End of worksheet writer.Close(); dt.Clear(); }
public static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, DefinedNames definedNamesCol) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII); writer.WriteStartElement(new Worksheet()); UInt32 inx = 1; writer.WriteStartElement(new Columns()); foreach (DataColumn dc in dt.Columns) { writer.WriteElement(new Column { Min = inx, Max = inx, CustomWidth = true, Width = DEFAULT_COLUMN_WIDTH }); inx++; } writer.WriteEndElement(); writer.WriteStartElement(new SheetData()); string cellValue = ""; string cellReference = ""; int numberOfColumns = dt.Columns.Count; bool[] IsIntegerColumn = new bool[numberOfColumns]; bool[] IsFloatColumn = new bool[numberOfColumns]; bool[] IsDateColumn = new bool[numberOfColumns]; string[] excelColumnNames = new string[numberOfColumns]; for (int n = 0; n < numberOfColumns; n++) { excelColumnNames[n] = GetExcelColumnName(n); } uint rowIndex = 1; writer.WriteStartElement(new Row { RowIndex = rowIndex, Height = 20, CustomHeight = true }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dt.Columns[colInx]; writer.AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName); IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int")); IsFloatColumn[colInx] = (col.DataType.FullName == typeof(decimal).FullName) || (col.DataType.FullName == typeof(double).FullName) || (col.DataType.FullName == typeof(Single).FullName); IsDateColumn[colInx] = (col.DataType.FullName == typeof(DateTime).FullName); } writer.WriteEndElement(); // End of header "Row" double cellFloatValue = 0; CultureInfo ci = Thread.CurrentThread.CurrentCulture; //new CultureInfo("en-US"); foreach (DataRow dr in dt.Rows) { ++rowIndex; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { cellValue = dr.ItemArray[colInx].ToString(); cellValue = ReplaceHexadecimalSymbols(cellValue); cellReference = excelColumnNames[colInx] + rowIndex.ToString(); if (IsIntegerColumn[colInx] || IsFloatColumn[colInx]) { cellFloatValue = 0; bool bIncludeDecimalPlaces = IsFloatColumn[colInx]; if (double.TryParse(cellValue, out cellFloatValue)) { cellValue = cellFloatValue.ToString(ci); writer.AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces ? NumericFormatDecimaPlaces.Two : NumericFormatDecimaPlaces.Zero); } } else if (IsDateColumn[colInx]) { DateTime dateValue; if (DateTime.TryParse(cellValue, out dateValue)) { writer.AppendDateCell(cellReference, dateValue); } else { writer.AppendTextCell(cellReference, cellValue); } } else { writer.AppendTextCell(cellReference, cellValue); } } writer.WriteEndElement(); // End of Row } writer.WriteEndElement(); // End of SheetData writer.WriteEndElement(); // End of worksheet writer.Close(); }
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); string cellValue = ""; // Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable. // // We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual // cells of data, we'll know if to write Text values or Numeric cell values. int numberOfColumns = dt.Columns.Count; bool[] IsNumericColumn = new bool[numberOfColumns]; bool[] IsDateColumn = new bool[numberOfColumns]; string[] excelColumnNames = new string[numberOfColumns]; for (int n = 0; n < numberOfColumns; n++) { excelColumnNames[n] = GetExcelColumnName(n); } // // Create the Header row in our Excel Worksheet // uint rowIndex = 1; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dt.Columns[colInx]; AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer); IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single"); IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime"); } writer.WriteEndElement(); // End of header "Row" // // Now, step through each row of data in our DataTable... // double cellNumericValue = 0; foreach (DataRow dr in dt.Rows) { // ...create a new row, and append a set of this row's data to it. ++rowIndex; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { cellValue = dr.ItemArray[colInx].ToString(); cellValue = ReplaceHexadecimalSymbols(cellValue); // Create cell with data if (IsNumericColumn[colInx]) { // For numeric cells, make sure our input data IS a number, then write it out to the Excel file. // If this numeric value is NULL, then don't write anything to the Excel file. cellNumericValue = 0; if (double.TryParse(cellValue, out cellNumericValue)) { cellValue = cellNumericValue.ToString(); AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); } } else if (IsDateColumn[colInx]) { // This is a date value. DateTime dtValue; string strValue = ""; if (DateTime.TryParse(cellValue, out dtValue)) { strValue = dtValue.ToShortDateString(); } AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer); } else { // For text cells, just write the input data straight out to the Excel file. AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); } } writer.WriteEndElement(); // End of Row } writer.WriteEndElement(); // End of SheetData writer.WriteEndElement(); // End of worksheet writer.Close(); }
public void WriteRandomValuesSAX(string filename, int numRows, int numCols) { using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true)) { WorkbookPart workbookPart = myDoc.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart); WorksheetPart replacementPart = workbookPart.AddNewPart <WorksheetPart>(); string replacementPartId = workbookPart.GetIdOfPart(replacementPart); OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart); Row r = new Row(); Cell c = new Cell(); CellFormula f = new CellFormula(); f.CalculateCell = true; f.Text = "RAND()"; c.Append(f); CellValue v = new CellValue(); c.Append(v); while (reader.Read()) { if (reader.ElementType == typeof(SheetData)) { if (reader.IsEndElement) { continue; } 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(); } else { if (reader.IsStartElement) { writer.WriteStartElement(reader); } else if (reader.IsEndElement) { writer.WriteEndElement(); } } } reader.Close(); writer.Close(); Sheet sheet = workbookPart.Workbook.Descendants <Sheet>() .Where(s => s.Id.Value.Equals(origninalSheetId)).First(); sheet.Id.Value = replacementPartId; workbookPart.DeletePart(worksheetPart); } }
public int writeHeader(List <ChannelModel> channels, OperatorModel operatorParam, FacilityModel facilityParam) { int retvalueTemp = 0; if (_doc != null && _workBook != null && _workSheet != null) { string originalPartId = _workBook.GetIdOfPart(_workSheet); WorksheetPart replacementPart = _workBook.AddNewPart <WorksheetPart>(); string replacementPartId = _workBook.GetIdOfPart(replacementPart); _reader = OpenXmlReader.Create(_workSheet); _writer = OpenXmlWriter.Create(replacementPart); while (_reader.Read()) { if (_reader.ElementType == typeof(Selection)) { continue; } if (_reader.ElementType == typeof(SheetData)) { if (_reader.IsEndElement) { continue; } _writer.WriteStartElement(new SheetData()); // beginning of sheetdata // append section begins var labelRow = new Row(); // label row labelRow.RowIndex = 1; _writer.WriteStartElement(labelRow); // begining of row var operatorLabelCell = new Cell(); var facilityLabelCell = new Cell(); var creationDateLabelCell = new Cell(); operatorLabelCell.CellValue = new CellValue(" Operatör "); facilityLabelCell.CellValue = new CellValue(" Tesis "); creationDateLabelCell.CellValue = new CellValue(" Oluşturulma Tarihi "); _writer.WriteElement(operatorLabelCell); _writer.WriteElement(facilityLabelCell); _writer.WriteElement(creationDateLabelCell); _writer.WriteEndElement(); // end of label row var infoRow = new Row(); // info row infoRow.RowIndex = 2; _writer.WriteStartElement(infoRow); // begining of row var operatorInfoCell = new Cell(); var facilityInfoCell = new Cell(); var creationDateInfoCell = new Cell(); operatorInfoCell.CellValue = new CellValue(operatorParam.DisplayName); facilityInfoCell.CellValue = new CellValue(facilityParam.ListDisplay); creationDateInfoCell.CellValue = new CellValue(DateTime.Now.ToShortTimeString()); _writer.WriteElement(operatorInfoCell); _writer.WriteElement(facilityInfoCell); _writer.WriteElement(creationDateInfoCell); _writer.WriteEndElement(); // end of info row var channelListRow = new Row(); // channelListRow row channelListRow.RowIndex = 3; _writer.WriteStartElement(channelListRow); // begining of row for (int k = 0; k < channels.Count; k++) { var dataInfoCell = new Cell(); dataInfoCell.CellValue = new CellValue(channels[k].name); _writer.WriteElement(dataInfoCell); } _writer.WriteEndElement(); // end of channel info row _writer.WriteEndElement(); // end of sheetdata } else { if (_reader.IsStartElement) { _writer.WriteStartElement(_reader); if (_reader.ElementType == typeof(CellValue)) { _writer.WriteString(_reader.GetText()); } } else if (_reader.IsEndElement) { _writer.WriteEndElement(); } else { } } } _writer.Close(); _reader.Close(); Sheet sheet = _workBook.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(originalPartId)).First(); sheet.Id.Value = replacementPartId; _workBook.DeletePart(_workSheet); } else { retvalueTemp = -1; } return(retvalueTemp); }
internal virtual void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location) { DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>(); writer.WriteStartElement(new Drawing() { Id = part.GetIdOfPart(drawingsPart) }); writer.WriteEndElement(); ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>(); chartPart.ChartSpace = new ChartSpace(); chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") }); Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart()); // Set chart title chartContainer.AppendChild(ChartPropertySetter.SetTitle(ChartPropertySetter.ChartProperties.Title)); chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted() { Val = false }); // Create a new clustered column chart. PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea()); uint chartSeriesCounter = 0; OpenXmlCompositeElement chart = ChartPropertySetter.CreateChart(plotArea); foreach (var chartDataSeriesGrouped in ChartData.GroupBy(x => x.Series)) { // Set chart and series depending on type. OpenXmlCompositeElement chartSeries = ChartPropertySetter.CreateChartSeries(chartDataSeriesGrouped.Key, chartSeriesCounter, chart); // Every method from chartPropertySetter can be overriden to customize chart export. ChartPropertySetter.SetChartShapeProperties(chartSeries); ChartPropertySetter.SetChartAxis(chartSeries, chartDataSeriesGrouped.ToList()); chartSeriesCounter++; } chart.Append(new AxisId() { Val = new UInt32Value(48650112u) }); chart.Append(new AxisId() { Val = new UInt32Value(48672768u) }); // Add the Category Axis (X axis). ChartPropertySetter.SetLineCategoryAxis(plotArea); // Add the Value Axis (Y axis). ChartPropertySetter.SetValueAxis(plotArea); ChartPropertySetter.SetLegend(chartContainer); ChartPropertySetter.SetChartLocation(drawingsPart, chartPart, location); }
public void WriteWorksheetPart(OpenXmlWriter writer) { // TODO: final cleanup // - merge redundant columns // - remove unused shared strings // Remove rows without cells foreach (var rowItem in _cachedCells.ToList()) { if (rowItem.Value.Count == 0) { _cachedCells.Remove(rowItem.Key); } } // Simulate rows for cached rows foreach (var rowIdx in _cachedRows.Keys) { if (!_cachedCells.ContainsKey(rowIdx)) { _cachedCells[rowIdx] = new SortedList <uint, CellProxy>(); } } // Get first and last addresses uint minRow = uint.MaxValue; uint minCol = uint.MaxValue; uint maxRow = 0; uint maxCol = 0; foreach (var rowItem in _cachedCells) { uint rowIdx = rowItem.Key; var cells = rowItem.Value; if (minRow == uint.MaxValue) { minRow = rowIdx; } maxRow = rowIdx; if (cells.Count > 0) { minCol = Math.Min(minCol, cells.Keys.First()); maxCol = Math.Max(maxCol, cells.Keys.Last()); } } string firstAddress = null, lastAddress = null; if (minRow < uint.MaxValue && minCol < uint.MaxValue) { firstAddress = RowColumn.ToAddress(minRow, minCol); if (minRow != maxRow || minCol != maxCol) { lastAddress = RowColumn.ToAddress(maxRow, maxCol); } } else { firstAddress = "A1"; } writer.WriteStartDocument(); writer.WriteStartElement(new Worksheet()); foreach (string childTagName in SchemaInfo.WorksheetChildSequence) { if (childTagName == "sheetData") { WriteSheetData(writer); } else if (childTagName == "dimension") { string dimensionRef = firstAddress + (lastAddress != null ? ":" + lastAddress : ""); writer.WriteElement(new SheetDimension() { Reference = dimensionRef }); } else if (childTagName == "sheetViews") { SheetViews svs = GetFirstElement <SheetViews>(); if (svs != null) { foreach (SheetView sv in svs.Elements <SheetView>()) { foreach (Selection sel in sv.Elements <Selection>()) { if (minRow < uint.MaxValue) { sel.ActiveCell = firstAddress; sel.SequenceOfReferences = new ListValue <StringValue>(new StringValue[] { new StringValue(firstAddress) }); } else { sel.Remove(); } } } writer.WriteElement(svs); } } else { foreach (var e in GetElementsByTagName(childTagName)) { writer.WriteElement(e); } } } writer.WriteEndElement(); // worksheet }
private void WriteSheetData(OpenXmlWriter writer) { writer.WriteStartElement(new SheetData()); foreach (KeyValuePair <uint, SortedList <uint, CellProxy> > pair in this._cachedCells) { uint key = pair.Key; SortedList <uint, CellProxy> cells = pair.Value; List <OpenXmlAttribute> attributes = this.EnumRowAttributes(key, cells).ToList <OpenXmlAttribute>(); if ((attributes.Count > 0) || (cells.Count > 0)) { writer.WriteStartElement(new Row(), attributes); foreach (KeyValuePair <uint, CellProxy> pair2 in cells) { uint col = pair2.Key; CellProxy cellProxy = pair2.Value; writer.WriteStartElement(new Cell(), this.EnumCellProxyAttributes(key, col, cellProxy)); if (cellProxy.Formula != null) { CellFormula elementObject = new CellFormula(cellProxy.Formula.Text); if (cellProxy.Formula.R1 != null) { elementObject.R1 = cellProxy.Formula.R1; } if (cellProxy.Formula.R2 != null) { elementObject.R2 = cellProxy.Formula.R2; } if (cellProxy.Formula.Reference != null) { elementObject.Reference = cellProxy.Formula.Reference; } if (cellProxy.Formula.AlwaysCalculateArray.HasValue) { bool?alwaysCalculateArray = cellProxy.Formula.AlwaysCalculateArray; elementObject.AlwaysCalculateArray = alwaysCalculateArray.HasValue ? ((BooleanValue)alwaysCalculateArray.GetValueOrDefault()) : null; } if (cellProxy.Formula.Bx.HasValue) { bool?bx = cellProxy.Formula.Bx; elementObject.Bx = bx.HasValue ? ((BooleanValue)bx.GetValueOrDefault()) : null; } if (cellProxy.Formula.CalculateCell.HasValue) { bool?calculateCell = cellProxy.Formula.CalculateCell; elementObject.CalculateCell = calculateCell.HasValue ? ((BooleanValue)calculateCell.GetValueOrDefault()) : null; } if (cellProxy.Formula.DataTable2D.HasValue) { bool?nullable8 = cellProxy.Formula.DataTable2D; elementObject.DataTable2D = nullable8.HasValue ? ((BooleanValue)nullable8.GetValueOrDefault()) : null; } if (cellProxy.Formula.DataTableRow.HasValue) { bool?dataTableRow = cellProxy.Formula.DataTableRow; elementObject.DataTableRow = dataTableRow.HasValue ? ((BooleanValue)dataTableRow.GetValueOrDefault()) : null; } if (cellProxy.Formula.FormulaType.HasValue) { CellFormulaValues?formulaType = cellProxy.Formula.FormulaType; elementObject.FormulaType = formulaType.HasValue ? ((EnumValue <CellFormulaValues>)formulaType.GetValueOrDefault()) : null; } if (cellProxy.Formula.Input1Deleted.HasValue) { bool?nullable14 = cellProxy.Formula.Input1Deleted; elementObject.Input1Deleted = nullable14.HasValue ? ((BooleanValue)nullable14.GetValueOrDefault()) : null; } if (cellProxy.Formula.Input2Deleted.HasValue) { bool?nullable16 = cellProxy.Formula.Input2Deleted; elementObject.Input2Deleted = nullable16.HasValue ? ((BooleanValue)nullable16.GetValueOrDefault()) : null; } if (cellProxy.Formula.SharedIndex.HasValue) { uint?sharedIndex = cellProxy.Formula.SharedIndex; elementObject.SharedIndex = sharedIndex.HasValue ? ((UInt32Value)sharedIndex.GetValueOrDefault()) : null; } writer.WriteElement(elementObject); } if (cellProxy.Value != null) { writer.WriteElement(new CellValue(cellProxy.SerializedValue)); } writer.WriteEndElement(); } writer.WriteEndElement(); } } writer.WriteEndElement(); }
public void WriteWorksheetPart(OpenXmlWriter writer) { foreach (KeyValuePair <uint, SortedList <uint, CellProxy> > pair in this._cachedCells.ToList <KeyValuePair <uint, SortedList <uint, CellProxy> > >()) { if (pair.Value.Count == 0) { this._cachedCells.Remove(pair.Key); } } foreach (uint num in this._cachedRows.Keys) { if (!this._cachedCells.ContainsKey(num)) { this._cachedCells[num] = new SortedList <uint, CellProxy>(); } } uint maxValue = uint.MaxValue; uint num3 = uint.MaxValue; uint row = 0; uint num5 = 0; foreach (KeyValuePair <uint, SortedList <uint, CellProxy> > pair2 in this._cachedCells) { uint key = pair2.Key; SortedList <uint, CellProxy> list = pair2.Value; if (maxValue == uint.MaxValue) { maxValue = key; } row = key; if (list.Count > 0) { num3 = Math.Min(num3, list.Keys.First <uint>()); num5 = Math.Max(num5, list.Keys.Last <uint>()); } } string str = null; string str2 = null; if ((maxValue < uint.MaxValue) && (num3 < uint.MaxValue)) { str = RowColumn.ToAddress(maxValue, num3); if ((maxValue != row) || (num3 != num5)) { str2 = RowColumn.ToAddress(row, num5); } } else { str = "A1"; } writer.WriteStartDocument(); writer.WriteStartElement(new Worksheet()); foreach (string str3 in SchemaInfo.WorksheetChildSequence) { switch (str3) { case "sheetData": this.WriteSheetData(writer); break; case "dimension": { string str4 = str + ((str2 != null) ? (":" + str2) : ""); SheetDimension elementObject = new SheetDimension { Reference = str4 }; writer.WriteElement(elementObject); break; } case "sheetViews": { SheetViews firstElement = this.GetFirstElement <SheetViews>(); if (firstElement != null) { foreach (SheetView view in firstElement.Elements <SheetView>()) { foreach (Selection selection in view.Elements <Selection>()) { if (maxValue < uint.MaxValue) { selection.ActiveCell = str; selection.SequenceOfReferences = new ListValue <StringValue>(new StringValue[] { new StringValue(str) }); } else { selection.Remove(); } } } writer.WriteElement(firstElement); } break; } default: foreach (OpenXmlElement element in this.GetElementsByTagName(str3)) { writer.WriteElement(element); } break; } } writer.WriteEndElement(); }
public static Stream ToXLSX(TranslationModule project) { MemoryStream xlsStream = new MemoryStream(); var worksheet = new MemoryWorksheet(); Export.ToIWorksheet(project, worksheet); var ss = new Stylesheet(); ss.CellStyleFormats = new CellStyleFormats(); ss.CellStyleFormats.Count = 1; ss.CellStyles = new CellStyles(); ss.CellStyles.Count = 1; ss.DifferentialFormats = new DifferentialFormats(); ss.DifferentialFormats.Count = 0; ss.TableStyles = new TableStyles(); ss.TableStyles.Count = 0; using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(xlsStream, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = myDoc.AddWorkbookPart(); //workbookPart.Workbook = new Workbook(); WorkbookStylesPart stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>(); stylesPart.Stylesheet = ss; stylesPart.Stylesheet.Save(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); //SheetData sheetData = new SheetData(); //worksheetPart.Worksheet = new Worksheet(sheetData); /* * Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets()); * Sheet sheet = new Sheet() * { * Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart), * SheetId = 1, * Name = "mySheet" * }; * sheets.Append(sheet); */ OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); for (int row = 0; row < worksheet.Rows; row++) { var oxa = new List <OpenXmlAttribute>(); // this is the row index oxa.Add(new OpenXmlAttribute("r", null, row.ToString())); writer.WriteStartElement(new Row(), oxa); for (int col = 0; col < worksheet.Columns; col++) { oxa = new List <OpenXmlAttribute>(); // this is the data type ("t"), with CellValues.String ("str") oxa.Add(new OpenXmlAttribute("t", null, "str")); string val = worksheet[row, col] != null ? worksheet[row, col].ToString() : ""; //var cell = new Cell(new CellValue(val)); writer.WriteStartElement(new Cell(), oxa); //Cell c = new Cell(); CellValue v = new CellValue(val); writer.WriteElement(v); //c.AppendChild(v); //writer.WriteElement(); writer.WriteEndElement(); } writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); writer = OpenXmlWriter.Create(myDoc.WorkbookPart); writer.WriteStartElement(new Workbook()); writer.WriteStartElement(new Sheets()); // you can use object initialisers like this only when the properties // are actual properties. SDK classes sometimes have property-like properties // but are actually classes. For example, the Cell class has the CellValue // "property" but is actually a child class internally. // If the properties correspond to actual XML attributes, then you're fine. writer.WriteElement(new Sheet() { Name = "Sheet1", SheetId = 1, Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart) }); // this is for Sheets writer.WriteEndElement(); // this is for Workbook writer.WriteEndElement(); writer.Close(); myDoc.Close(); } return(xlsStream); }
private void Initialize() { _workBookWriter = OpenXmlWriter.Create(_xl.WorkbookPart); _workBookWriter.WriteStartElement(new Workbook()); _workBookWriter.WriteStartElement(new Sheets()); }
public virtual void Export(string fileName, LayoutList list) { this.list = list; using (SpreadsheetDocument xl = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) { // Add a WorkbookPart to the document. WorkbookPart workbookpart = xl.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); //add styles WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>(); wbsp.Stylesheet = CreateStylesheet(); wbsp.Stylesheet.Save(); // Add a WorksheetPart to the WorkbookPart. var worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); // Add a SharedStringTablePart to the WorkbookPart. var stringPart = workbookpart.AddNewPart <SharedStringTablePart>(); var stringTable = new StringKeyList(); // Add Sheets to the Workbook. var sheets = xl.WorkbookPart.Workbook.AppendChild(new Sheets()); // Append a new worksheet and associate it with the workbook. var sheet = new Sheet() { Id = xl.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "DataSheet" }; sheets.Append(sheet); workbookpart.Workbook.Save(); mcells = new List <MergeCell>(); writer = OpenXmlWriter.Create(worksheetPart); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetProperties()); writer.WriteElement(new OutlineProperties() { SummaryBelow = false, SummaryRight = false }); writer.WriteEndElement(); mc = 0; writer.WriteStartElement(new Columns()); WriteMapColumns(list.ListInfo.Columns, 0, 0); writer.WriteEndElement(); writer.WriteStartElement(new SheetData()); int ind = 1; var row = new Row() { RowIndex = (uint)ind, Height = 25 }; row.AppendChild(GetCell(list.Description, 0, ind, (uint)13, stringTable)); WriteRows(writer, new List <Row>(new Row[] { row })); mcells.Add(new MergeCell() { Reference = new CellRange(0, 1, mc - 1, 1).ToString() }); WriteMapItem(list.ListInfo.Columns, -1, null, 0, 0, ref ind, stringTable); if (list.Selection.Count > 1) { var items = list.Selection.GetItems <object>(); for (var i = 0; i < items.Count; i++) { var item = items[i]; WriteMapItem(list.ListInfo.Columns, i, item, 0, 0, ref ind, stringTable); } } else if (list.NodeInfo != null) { var items = list.NodeInfo.Nodes.GetTopLevel().ToList(); for (var i = 0; i < items.Count; i++) { var item = items[i] as Node; WriteMapItem(list.ListInfo.Columns, i, item, 0, 0, ref ind, stringTable); } } else if (list.ListInfo.GroupVisible) { foreach (LayoutGroup g in list.Groups) { this.group = g; if (list.ListInfo.GroupHeader) { ind++; var header = new Row() { RowIndex = (uint)ind, CustomHeight = true, Height = 20 }; header.AppendChild(GetCell(g.TextValue, 0, ind, 8, stringTable)); mcells.Add(new MergeCell() { Reference = new CellRange(0, ind, mc - 1, ind).ToString() }); WriteRow(writer, header); } for (int i = g.IndexStart; i <= g.IndexEnd; i++) { WriteMapItem(list.ListInfo.Columns, i, list.ListSource[i], 0, 0, ref ind, stringTable); } if (list.ListInfo.CollectingRow) { WriteMapItem(list.ListInfo.Columns, -2, null, 0, 0, ref ind, stringTable); } //ind++; } } else { for (int i = 0; i < list.ListSource.Count; i++) { WriteMapItem(list.ListInfo.Columns, i, list.ListSource[i], 0, 0, ref ind, stringTable); } if (list.ListInfo.CollectingRow) { WriteMapItem(list.ListInfo.Columns, -2, null, 0, 0, ref ind, stringTable); } } writer.WriteEndElement(); if (mcells.Count > 0) { writer.WriteStartElement(new MergeCells()); foreach (var cell in mcells) { writer.WriteElement(cell); } writer.WriteEndElement(); } writer.WriteEndElement(); writer.Close(); } }
public ActionResult Search(FormCollection model) { string selectedReport = model["cboReports"]; string datePeriod = model["DatePeriod"]; if (selectedReport.HasValue()) { Guid idReport = new Guid(selectedReport); Reports report = Reports.Get(idReport); IEnumerable <dynamic> data = Reports.Run(report, datePeriod); HttpContext.Response.SetCookie(new HttpCookie("fileDownload", "true") { Path = "/" }); var context = HttpContext.Response; context.Buffer = context.BufferOutput = false; context.Cache.SetCacheability(HttpCacheability.Private); context.Cache.SetExpires(DateTime.Now); //context.ContentType = (new ContentType("text/csv") { CharSet = "utf-8" }).ToString(); // CSV //context.ContentType = (new ContentType("application/vnd.ms-excel") { CharSet = "utf-8" }).ToString(); context.ContentType = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { CharSet = "utf-8" }.ToString(); context.AppendHeader("Content-Disposition", new ContentDisposition { DispositionType = DispositionTypeNames.Attachment, FileName = string.Format(CultureInfo.InvariantCulture, report.FileNamePrefix + "_{0:yyyyMMdd_HHmmss}.xlsx", DateTime.Now) }.ToString() ); context.AppendHeader("X-Content-Type-Options", "nosniff"); using (MemoryStream mDocument = new MemoryStream()) { // Using SAX using (SpreadsheetDocument document = SpreadsheetDocument.Create(mDocument, SpreadsheetDocumentType.Workbook)) { List <OpenXmlAttribute> attributes; document.AddWorkbookPart(); // Stylesheet WorkbookStylesPart stylesheet = document.WorkbookPart.AddNewPart <WorkbookStylesPart>(); stylesheet.Stylesheet = new Stylesheet(new Fonts( new Font( // 0 = Default new Color() { Rgb = new HexBinaryValue() { Value = "000000" } } ), new Font( // 1 = Bold new Bold() ), new Font( // 2 = Red new Color() { Rgb = new HexBinaryValue() { Value = "FF0000" } } ) ), new Fills( new Fill() { } ), new Borders(new Border() { }), new CellFormats( new CellFormat() { FontId = 0 }, // 0 new CellFormat() { FontId = 1, ApplyFont = true }, // 1 new CellFormat() { FontId = 2, ApplyFont = true } // 2 ) ); stylesheet.Stylesheet.Save(); WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); IDictionary <string, object> firstRow = data.FirstOrDefault(); if (firstRow != null) { int row = 1; attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, row.ToString()) }; writer.WriteStartElement(new Row(), attributes); int col1 = 1; foreach (var cols in firstRow.Keys.ToList()) { 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(); row++; foreach (IDictionary <string, object> row2 in data) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, row.ToString()) }; writer.WriteStartElement(new Row(), attributes); int col = 1; foreach (var key in row2.Keys) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(col) + row) }; if (row2[key] is decimal) { if ((decimal)row2[key] < 0) { attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2) } } else if (row2[key] is double) { if ((double)row2[key] < 0) { attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2) } } writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue(row2[key] != null ? row2[key].ToString() : "")); writer.WriteEndElement(); col++; } writer.WriteEndElement(); row++; } } else { // Empty row (no data found) attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, "1") }; writer.WriteStartElement(new Row(), attributes); attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(1) + 1), new OpenXmlAttribute("s", "", "1") // Bold (Style 1) }; writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue("")); writer.WriteEndElement(); writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); writer = OpenXmlWriter.Create(document.WorkbookPart); writer.WriteStartElement(new Workbook()); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet() { Name = "Sheet 1", SheetId = 1, Id = document.WorkbookPart.GetIdOfPart(workSheetPart) }); writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); document.Save(); document.Close(); mDocument.WriteTo(context.OutputStream); } } return(null); } return(null); }
/* * report.GetReportData() */ public ActionResult Export(Reports report) { try { var reportData = report.GetReportData(true); HttpContext.Response.SetCookie(new HttpCookie("fileDownload", "true") { Path = "/" }); var context = HttpContext.Response; context.Buffer = context.BufferOutput = false; context.Cache.SetCacheability(HttpCacheability.Private); context.Cache.SetExpires(DateTime.Now); context.ContentType = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { CharSet = "utf-8" }.ToString(); context.AppendHeader("Content-Disposition", new ContentDisposition { DispositionType = DispositionTypeNames.Attachment, FileName = string.Format(CultureInfo.InvariantCulture, RemoveInvalidFilePathCharacters(reportData.title) + "_{0:yyyyMMdd_HHmmss}.xlsx", DateTime.Now) }.ToString() ); context.AppendHeader("X-Content-Type-Options", "nosniff"); using (MemoryStream mDocument = new MemoryStream()) { // Using SAX using (SpreadsheetDocument document = SpreadsheetDocument.Create(mDocument, SpreadsheetDocumentType.Workbook)) { List <OpenXmlAttribute> attributes; document.AddWorkbookPart(); // Stylesheet WorkbookStylesPart stylesheet = document.WorkbookPart.AddNewPart <WorkbookStylesPart>(); stylesheet.Stylesheet = new Stylesheet(new Fonts( new Font(new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }), // 0 new Font(new Bold()), // 1 new Font(new Color() { Rgb = new HexBinaryValue() { Value = "FF0000" } }) // 2 ), new Fills(new Fill() { }), new Borders(new Border() { }), new CellFormats( new CellFormat() { FontId = 0 }, // 0 new CellFormat() { FontId = 1, ApplyFont = true }, // 1 new CellFormat() { FontId = 2, ApplyFont = true } // 2 ) ); stylesheet.Stylesheet.Save(); WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); IDictionary <string, object> firstRow = new Dictionary <string, object>() { { Words.Reports_Name, "" }, { Words.Reports_Value, "" } }; if (firstRow != null) { int row = 1; AddLine(writer, row, new string[] { reportData.title }); row++; AddLine(writer, row, new string[] { "" }); row++; attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, row.ToString()) }; writer.WriteStartElement(new Row(), attributes); int col1 = 1; foreach (var cols in firstRow.Keys.ToList()) { 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(); row++; for (int i = 0; i < reportData.labels.Length; i++) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, row.ToString()) }; writer.WriteStartElement(new Row(), attributes); int col = 1; var row2 = new Dictionary <string, object>() { { "Name", reportData.labels[i] }, { "Value", reportData.datasets[0].data[i] } }; foreach (var key in row2.Keys) { attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(col) + row) }; if (row2[key] is decimal) { if ((decimal)row2[key] < 0) { attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2) } } else if (row2[key] is double) { if ((double)row2[key] < 0) { attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2) } } writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue(row2[key] != null ? row2[key].ToString() : "")); writer.WriteEndElement(); col++; } writer.WriteEndElement(); row++; } } else { // Empty row (no data found) attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, "1") }; writer.WriteStartElement(new Row(), attributes); attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", "", GetColumnName(1) + 1), new OpenXmlAttribute("s", "", "1") // Bold (Style 1) }; writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue("")); writer.WriteEndElement(); writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); writer = OpenXmlWriter.Create(document.WorkbookPart); writer.WriteStartElement(new Workbook()); writer.WriteStartElement(new Sheets()); writer.WriteElement(new Sheet() { Name = "Sheet 1", SheetId = 1, Id = document.WorkbookPart.GetIdOfPart(workSheetPart) }); writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); document.Save(); document.Close(); mDocument.WriteTo(context.OutputStream); } } } catch (Exception ex) { ex.Log(); } return(null); }
private void WriteReportColumns() { try { _openXmlWriter.WriteStartElement(new Columns()); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 32.29, Max = 1, Min = 1 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 2, Min = 2 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 3, Min = 3 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 4, Min = 4 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 5, Min = 5 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 6, Min = 6 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 7, Min = 7 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 8, Min = 8 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 9, Min = 9 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 32.29, Max = 10, Min = 10 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 32.29, Max = 11, Min = 11 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 12, Min = 12 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 13, Min = 13 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 14, Min = 14 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 15, Min = 15 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 16, Min = 16 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 24.43, Max = 17, Min = 17 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 32.29, Max = 18, Min = 18 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 32.29, Max = 19, Min = 19 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 32.29, Max = 20, Min = 20 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 32.29, Max = 21, Min = 21 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 32.29, Max = 22, Min = 22 }); _openXmlWriter.WriteElement(new Column() { CustomWidth = true, Width = 32.29, Max = 23, Min = 23 }); _openXmlWriter.WriteEndElement(); } catch (Exception exception) { LogWriter.LogError("Unable to generate 'STIG Discrepancies' columns."); throw exception; } }
public void SaveFile(string path) { using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook)) { WorkbookPart wbp = spreadsheet.AddWorkbookPart(); wbp.Workbook = new Workbook(); Sheets sheets = wbp.Workbook.AppendChild <Sheets>(new Sheets()); Parallel.For(0, dict_sheet_name_map.Count, sheet_num => { WorksheetPart wsp; float tmp_fltvalue; string tmp_strvalue = ""; Cell tmp_cell; lock ("addition") { wsp = wbp.AddNewPart <WorksheetPart>(); Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(wsp), SheetId = (UInt32)(sheet_num + 1), Name = sheet_names[sheet_num] }; sheets.Append(sheet); } Parallel.For(0, lst_DepositCells[sheet_num].Count, i => { lst_DepositCells[sheet_num][i].Sort((x, y) => x.Length == y.Length ? string.Compare(x, y) : x.Length > y.Length ? 1 : -1); }); OpenXmlWriter writer = OpenXmlWriter.Create(wsp); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); for (int row = 0; row < lst_DepositCells[sheet_num].Count; row++) { writer.WriteStartElement(new Row()); for (int col = 0; col < lst_DepositCells[sheet_num][row].Count; col++) { string tmp_address = lst_DepositCells[sheet_num][row][col]; bool is_string = false; if (dict_FloatResults[sheet_num].TryGetValue(tmp_address, out tmp_fltvalue)) { is_string = false; } else if (dict_StringResults[sheet_num].TryGetValue(tmp_address, out tmp_strvalue)) { is_string = true; } else if (dict_FloatConstants[sheet_num].TryGetValue(tmp_address, out tmp_fltvalue)) { is_string = false; } else if (dict_StringConstants[sheet_num].TryGetValue(tmp_address, out tmp_strvalue)) { is_string = true; } if (is_string) { if (insert_formula) { tmp_cell = new Cell() { CellReference = tmp_address, DataType = CellValues.String, CellFormula = new CellFormula(dict_Formulas[lst_DepositCells[sheet_num][row][col]]) } } } ; else { tmp_cell = new Cell() { CellReference = tmp_address, DataType = CellValues.String } };
private static void WriteDataTableToExcelWorksheet(ReportCondition dtCondition, DataTable dtDetail, WorksheetPart worksheetPart, WorkbookStylesPart stylesPart) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.Unicode); //ASCII writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); string cellValue = string.Empty; uint rowIndexHeader = 1; // Create the Conditon Report writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "BOC Detailed Utilization", 1, stylesPart.Stylesheet, System.Drawing.Color.White, false, 20, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Date:", 2, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat( "B" , string.Format("{0} - {1}", dtCondition.FromDate.ToString("dd.MM.yyyy"), dtCondition.ToDate.AddDays(-1).ToString("dd.MM.yyyy")) , 2, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Location:", 3, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.LocationPath.ToString(), 3, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Group:", 4, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.GroupName.ToString(), 4, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Resource:", 5, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.ResourceName.ToString(), 5, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Method:", 6, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.Method.ToString(), 6, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Include weekends:", 7, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", SetWeekendTitle(dtCondition), 7, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Upper threshold (%):", 8, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.UpperThreshold.ToString(), 8, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); writer.WriteStartElement(new Row { RowIndex = rowIndexHeader++ }); AppendTextCellFormat("A", "Lower threshold (%):", 9, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); AppendTextCellFormat("B", dtCondition.LowerThreshold.ToString(), 9, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); writer.WriteEndElement(); // End of Conditon Report // Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable. int numberOfColumns = dtDetail.Columns.Count; bool[] IsNumericColumn = new bool[numberOfColumns]; bool[] IsDateColumn = new bool[numberOfColumns]; string[] excelColumnNames = new string[numberOfColumns]; for (int n = 0; n < numberOfColumns; n++) { excelColumnNames[n] = GetExcelColumnName(n); } // Create the Header row in our Excel Worksheet writer.WriteStartElement(new Row { RowIndex = 11 }); AppendTextCellFormat("A", "Resource", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer); AppendTextCellFormat("B", "Hours Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer); AppendTextCellFormat("C", "% Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 10, ref writer); AppendTextCellFormat("D", "Flag", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer); AppendTextCellFormat("E", "Group", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer); AppendTextCellFormat("F", "Hours Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer); AppendTextCellFormat("G", "% Used", 11, stylesPart.Stylesheet, System.Drawing.Color.White, true, 11, true, 9, ref writer); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dtDetail.Columns[colInx]; //AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer); IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single"); IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime"); } writer.WriteEndElement(); // End of header row // Now, step through each row of data in our DataTable... uint rowIndex = 11; double cellNumericValue = 0; foreach (DataRow dr in dtDetail.Rows) { // ...create a new row, and append a set of this row's data to it. ++rowIndex; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { cellValue = dr.ItemArray[colInx].ToString(); // Create cell with data if (IsNumericColumn[colInx]) { cellNumericValue = 0; if (double.TryParse(cellValue, out cellNumericValue)) { if (cellNumericValue >= 0) { cellValue = ((float)cellNumericValue).ToString("0.00"); AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); } else { cellValue = string.Empty; //AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, false, 9, ref writer); } } } else if (IsDateColumn[colInx]) { DateTime dtValue = DateTime.Parse(cellValue); string strValue = dtValue.ToShortDateString(); AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer); } else if (colInx == 0) { AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, false, 9, ref writer); } else { //AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); AppendTextCellFormat(excelColumnNames[colInx], cellValue, (int)rowIndex, stylesPart.Stylesheet, System.Drawing.Color.White, false, 11, true, 9, ref writer); } } writer.WriteEndElement(); // End of Row } writer.WriteEndElement(); // End of SheetData writer.WriteEndElement(); // End of worksheet writer.Close(); }
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); string cellValue = ""; int numberOfColumns = dt.Columns.Count; bool[] IsNumericColumn = new bool[numberOfColumns]; bool[] IsDateColumn = new bool[numberOfColumns]; string[] excelColumnNames = new string[numberOfColumns]; for (int n = 0; n < numberOfColumns; n++) { excelColumnNames[n] = GetExcelColumnName(n); } uint rowIndex = 1; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dt.Columns[colInx]; AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer); IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single"); IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime"); } writer.WriteEndElement(); double cellNumericValue = 0; foreach (DataRow dr in dt.Rows) { ++rowIndex; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { cellValue = dr.ItemArray[colInx].ToString(); cellValue = ReplaceHexadecimalSymbols(cellValue); if (IsNumericColumn[colInx]) { cellNumericValue = 0; if (double.TryParse(cellValue, out cellNumericValue)) { cellValue = cellNumericValue.ToString(); AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); } } else if (IsDateColumn[colInx]) { DateTime dtValue; string strValue = ""; if (DateTime.TryParse(cellValue, out dtValue)) { strValue = dtValue.ToShortDateString(); } AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer); } else { AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer); } } writer.WriteEndElement(); } writer.WriteEndElement(); writer.WriteEndElement(); writer.Close(); }
public void StartWriteHead() { _writer.WriteStartElement(new Worksheet()); _writer.WriteStartElement(new SheetData()); }
/// <summary> /// 表格内容 /// </summary> /// <param name="worksheetPart"></param> private void GenerateWorksheetPartContent(WorksheetPart worksheetPart) { using (OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart)) { // worksheet 开始 writer.WriteStartElement(new Worksheet()); // 是否有自定义列 bool hasCustomColume = _columnInfos.Any(x => x.Width != null); if (hasCustomColume) { // cols 开始 writer.WriteStartElement(new Columns()); } int columnCount = _columnInfos.Count; // 表头 Row headRow = new Row() { RowIndex = 1, Spans = new ListValue <StringValue>() { InnerText = $"1:{columnCount}" } }; Cell[] headCells = new Cell[columnCount]; for (int i = 0; i < columnCount; i++) { ColumnInfo citem = _columnInfos[i]; uint cindex = (uint)i + 1; if (citem.Width != null) { var column = new Column() { Min = cindex, Max = cindex, Width = citem.Width.Value, BestFit = true, CustomWidth = true }; // col writer.WriteElement(column); } Cell cell = ConstructHeadCell(citem.Show); cell.CellReference = CellReferenceUtil.GetCellReference(0, (uint)i); headCells[i] = cell; } headRow.Append(headCells); if (hasCustomColume) { // cols 结束 writer.WriteEndElement(); } // sheetData 开始 writer.WriteStartElement(new SheetData()); // 写入表头行 writer.WriteElement(headRow); // 写入数据 uint rowIndex = 2; foreach (T data in _sourceDatas) { Row row = new Row() { RowIndex = rowIndex, Spans = new ListValue <StringValue>() { InnerText = $"1:{columnCount}" } }; // row 开始 writer.WriteStartElement(row); for (int k = 0; k < columnCount; k++) { ColumnInfo citem = _columnInfos[k]; PropertyInfo pi = _propertyDic[citem.PropertyName]; object v = pi.GetValue(data, null); Cell cell = ConstructCell(v, citem.FormatString, pi.PropertyType); cell.CellReference = CellReferenceUtil.GetCellReference(rowIndex - 1, (uint)k); // 写入 c writer.WriteElement(cell); } // row 结束 writer.WriteEndElement(); rowIndex++; } // sheetData 结束 writer.WriteEndElement(); // worksheet 结束 writer.WriteEndElement(); writer.Close(); } }
private void WriteSheetData(OpenXmlWriter writer) { writer.WriteStartElement(new SheetData()); foreach (var rowItem in _cachedCells) { uint rowIdx = rowItem.Key; var cells = rowItem.Value; var rowAttrs = EnumRowAttributes(rowIdx, cells).ToList(); if (rowAttrs.Count > 0 || cells.Count > 0) { writer.WriteStartElement(new Row(), rowAttrs); foreach (var cellItem in cells) { uint colIdx = cellItem.Key; CellProxy cellProxy = cellItem.Value; writer.WriteStartElement(new Cell(), EnumCellProxyAttributes(rowIdx, colIdx, cellProxy)); if (cellProxy.Formula != null) { CellFormula cf = new CellFormula(cellProxy.Formula.Text); if (cellProxy.Formula.R1 != null) { cf.R1 = cellProxy.Formula.R1; } if (cellProxy.Formula.R2 != null) { cf.R2 = cellProxy.Formula.R2; } if (cellProxy.Formula.Reference != null) { cf.Reference = cellProxy.Formula.Reference; } if (cellProxy.Formula.AlwaysCalculateArray != null) { cf.AlwaysCalculateArray = cellProxy.Formula.AlwaysCalculateArray; } if (cellProxy.Formula.Bx != null) { cf.Bx = cellProxy.Formula.Bx; } if (cellProxy.Formula.CalculateCell != null) { cf.CalculateCell = cellProxy.Formula.CalculateCell; } if (cellProxy.Formula.DataTable2D != null) { cf.DataTable2D = cellProxy.Formula.DataTable2D; } if (cellProxy.Formula.DataTableRow != null) { cf.DataTableRow = cellProxy.Formula.DataTableRow; } if (cellProxy.Formula.FormulaType != null) { cf.FormulaType = cellProxy.Formula.FormulaType; } if (cellProxy.Formula.Input1Deleted != null) { cf.Input1Deleted = cellProxy.Formula.Input1Deleted; } if (cellProxy.Formula.Input2Deleted != null) { cf.Input2Deleted = cellProxy.Formula.Input2Deleted; } if (cellProxy.Formula.SharedIndex != null) { cf.SharedIndex = cellProxy.Formula.SharedIndex; } writer.WriteElement(cf); } if (cellProxy.Value != null) { writer.WriteElement(new CellValue(cellProxy.SerializedValue)); } writer.WriteEndElement(); // c } writer.WriteEndElement(); // row } } writer.WriteEndElement(); // sheetData }
internal override void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location) { DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>(); writer.WriteStartElement(new Drawing() { Id = part.GetIdOfPart(drawingsPart) }); writer.WriteEndElement(); ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>(); chartPart.ChartSpace = new ChartSpace(); chartPart.ChartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") }); Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart()); chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted() { Val = false }); // Create a new clustered column chart. PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea()); Layout layout1 = plotArea.AppendChild <Layout>(new Layout()); BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart()); barChart.Append(new BarDirection() { Val = BarDirectionValues.Bar }); barChart.Append(new BarGrouping() { Val = BarGroupingValues.Stacked }); barChart.Append(new GapWidth() { Val = (UInt16Value)75U }); barChart.Append(new Overlap() { Val = 100 }); GanttTypeChart ganttChart = new GanttTypeChart(UserSettings); var groupedData = GanttData .GroupBy(x => x.Name); List <GanttDataPairedSeries> ganttDataWithSeries = new List <GanttDataPairedSeries>(); for (int i = 0; i < groupedData.Max(x => x.Count()); i++) { // For each series create a hidden one for spacing. BarChartSeries barChartSeriesHidden = barChart.AppendChild <BarChartSeries>(new BarChartSeries( new Index() { Val = new UInt32Value((uint)(i * 2)) }, new Order() { Val = new UInt32Value((uint)(i * 2)) }, new SeriesText(new NumericValue() { Text = "Not Active" }))); BarChartSeries barChartSeriesValue = barChart.AppendChild <BarChartSeries>(new BarChartSeries( new Index() { Val = new UInt32Value((uint)(i * 2) + 1) }, new Order() { Val = new UInt32Value((uint)(i * 2) + 1) }, new SeriesText(new NumericValue() { Text = "Time Spent" }))); ganttChart.SetChartShapeProperties(barChartSeriesHidden, visible: false); ganttChart.SetChartShapeProperties(barChartSeriesValue, colorPoints: (uint)GanttData.Count); var ganttData = new List <GanttData>(); foreach (var data in groupedData.Where(x => x.Count() >= i + 1)) { ganttData.Add(data.ElementAt(i)); } ganttDataWithSeries.Add(new GanttDataPairedSeries() { BarChartSeriesHidden = barChartSeriesHidden, BarChartSeriesValue = barChartSeriesValue, Values = ganttData }); } ganttChart.SetChartAxis(ganttDataWithSeries, groupedData.ToList()); barChart.Append(new AxisId() { Val = new UInt32Value(48650112u) }); barChart.Append(new AxisId() { Val = new UInt32Value(48672768u) }); // Add the Category Axis (X axis). ganttChart.SetGanttCategoryAxis(plotArea); // Add the Value Axis (Y axis). ganttChart.SetGanttValueAxis(plotArea, GanttData.Min(x => x.Start), GanttData.Max(x => x.End)); chartContainer.Append(new PlotVisibleOnly() { Val = new BooleanValue(true) }); ganttChart.SetChartLocation(drawingsPart, chartPart, location); }
public int write(List <List <IWritable> > dataList) { int retvalueTemp = 0; if (_doc != null && _workBook != null && _workSheet != null) { string originalPartId = _workBook.GetIdOfPart(_workSheet); WorksheetPart replacementPart = _workBook.AddNewPart <WorksheetPart>(); string replacementPartId = _workBook.GetIdOfPart(replacementPart); _reader = OpenXmlReader.Create(_workSheet); _writer = OpenXmlWriter.Create(replacementPart); while (_reader.Read()) { if (_reader.ElementType == typeof(Selection)) { continue; } if (_reader.ElementType == typeof(SheetData)) { if (_reader.IsStartElement) { _writer.WriteStartElement(_reader); continue; } // append section begins //_writer.WriteStartElement(new SheetData()); // beginning of sheetdata for (int rowIndex = 0; rowIndex < dataList.Count; rowIndex++) { Row row = new Row(); _writer.WriteStartElement(row); // begining of row for (int colIndex = 0; colIndex < dataList.Count; colIndex++) { Cell cell = new Cell(); cell.CellValue = new CellValue(dataList[rowIndex][colIndex].ToString()); _writer.WriteElement(cell); } _writer.WriteEndElement(); // end of row } _writer.WriteEndElement(); // end of sheetdata } else { if (_reader.IsStartElement) { _writer.WriteStartElement(_reader); if (_reader.ElementType == typeof(CellValue)) { _writer.WriteString(_reader.GetText()); } } else if (_reader.IsEndElement) { _writer.WriteEndElement(); } else { } } } _writer.Close(); _reader.Close(); Sheet sheet = _workBook.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(originalPartId)).First(); sheet.Id.Value = replacementPartId; _workBook.DeletePart(_workSheet); } else { retvalueTemp = -1; } return(retvalueTemp); }
public static void GetDataResultsAdjust(IList <RecordAdjust> lstRecord, string filePath, string templatePath, string status, string fromdate, string todate) { if ((!File.Exists(templatePath))) { //If we do not have a template file, we need to create one CreateSpreadsheetWorkbook(templatePath); } //Copy the template file to the output file location File.Copy(templatePath, filePath, true); //Open the copied file using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filePath, true)) { //Navigate to the workbook part WorkbookPart workbookPart = myDoc.WorkbookPart; //open the first worksheet WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); //Get the id of this sheet. We need this because we are going to add a new //worksheet to this workbook, then we are going to delete this worksheet //This ID will tell us which one to delete string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart); //Add the new worksheet WorksheetPart replacementPart = workbookPart.AddNewPart <WorksheetPart>(); //This is the ID of the new worksheet string replacementPartId = workbookPart.GetIdOfPart(replacementPart); //We are going to read from the original worksheet to get the //templated items that we added to the worksheet in the traditional way OpenXmlReader reader = OpenXmlReader.Create(worksheetPart); //We are goint to copy the items from the original worksheet by using //an XML writer, this overcomes the memory limitations of having //an extremely large dataset. OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart); //The template does not have any data so we will be creating new rows and cells //Then writing them using XML. Row r = new Row(); Cell c = new Cell(); while ((reader.Read())) { //This iterates through the sheet data and copies it if ((object.ReferenceEquals(reader.ElementType, typeof(SheetData)))) { //Exit the loop if we hit a sheetdata end element if ((reader.IsEndElement)) { break; // TODO: might not be correct. Was : Exit While } //We create a new sheetdata element (basically this is the reoot container for a sheet) writer.WriteStartElement(new SheetData()); if (status == "0") { int rowIndex2 = 2; r.RowIndex = (UInt32)rowIndex2; writer.WriteStartElement(r); c = CreateCell(5, "BÁO CÁO HÓA ĐƠN THAY THẾ", rowIndex2); writer.WriteElement(c); writer.WriteEndElement(); } else if (status == "1") { int rowIndex2 = 2; r.RowIndex = (UInt32)rowIndex2; writer.WriteStartElement(r); c = CreateCell(5, "BÁO CÁO HÓA ĐƠN ĐIỀU CHỈNH", rowIndex2); writer.WriteElement(c); writer.WriteEndElement(); } int rowIndextieudea = 3; r.RowIndex = (UInt32)rowIndextieudea; writer.WriteStartElement(r); c = CreateCell(0, "Từ ngày:" + fromdate, rowIndextieudea); writer.WriteElement(c); writer.WriteEndElement(); int rowIndextieudeb = 4; r.RowIndex = (UInt32)rowIndextieudeb; writer.WriteStartElement(r); c = CreateCell(0, "Đến ngày:" + todate, rowIndextieudeb); writer.WriteElement(c); writer.WriteEndElement(); int rowIndextieudec = 5; r.RowIndex = (UInt32)rowIndextieudec; writer.WriteStartElement(r); c = CreateCell(0, "Tổng số hóa đơn:" + lstRecord.Count(), rowIndextieudec); writer.WriteElement(c); writer.WriteEndElement(); int rowIndex = 6; r.RowIndex = (UInt32)rowIndex; //Start the first row. writer.WriteStartElement(r); //Iterate through the columns to write a row conaining the column names c = CreateCell(0, "STT", rowIndex); writer.WriteElement(c); c = CreateCell(1, "Ký hiệu mẫu", rowIndex); writer.WriteElement(c); c = CreateCell(2, "Ký hiệu hóa đơn", rowIndex); writer.WriteElement(c); c = CreateCell(3, "Số hóa đơn", rowIndex); writer.WriteElement(c); c = CreateCell(4, "Khách hàng", rowIndex); writer.WriteElement(c); c = CreateCell(5, "Ký hiệu mẫu", rowIndex); writer.WriteElement(c); c = CreateCell(6, "Ký hiệu hóa đơn", rowIndex); writer.WriteElement(c); c = CreateCell(7, "Số hóa đơn", rowIndex); writer.WriteElement(c); c = CreateCell(8, "Khách hàng", rowIndex); writer.WriteElement(c); c = CreateCell(9, "Người thực hiện", rowIndex); writer.WriteElement(c); c = CreateCell(10, "Ngày thực hiện", rowIndex); writer.WriteElement(c); c = CreateCell(11, "Trạng thái", rowIndex); writer.WriteElement(c); //End first row writer.WriteEndElement(); foreach (var item in lstRecord) { if (item == null) { continue; } rowIndex = rowIndex + 1; r.RowIndex = (UInt32)rowIndex; writer.WriteStartElement(r); //iterate through the columns to write their data c = CreateCell(0, item.stt.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(1, item.patternOlder.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(2, item.serialOlder.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(3, string.Format("{0:0000000}", Convert.ToInt32(item.noOlder)), rowIndex); writer.WriteElement(c); //c = CreateCell(4, item.cusnameOlder.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(5, item.patternNew.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(6, item.serialNew.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(7, string.Format("{0:0000000}", Convert.ToInt32(item.noNew)), rowIndex); writer.WriteElement(c); c = CreateCell(8, item.cusnameNew.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(9, item.username.ToString(), rowIndex); writer.WriteElement(c); c = CreateCell(10, String.Format("{0:dd/MM/yyyy}", DateTime.Parse(item.proccessdate)), rowIndex); writer.WriteElement(c); if (item.status == 1) { c = CreateCell(11, "Lập hóa đơn thay thế", rowIndex); writer.WriteElement(c); } else if (item.status == 2) { c = CreateCell(11, "Lập hóa đơn điều chỉnh", rowIndex); writer.WriteElement(c); } //write the end of the row writer.WriteEndElement(); } writer.WriteEndElement(); } else if ((reader.IsStartElement)) { //Start elements are directly copied writer.WriteStartElement(reader); } else if ((reader.IsEndElement)) { //End elements are directly copied writer.WriteEndElement(); } } //Close the reader and writer reader.Close(); writer.Close(); //Get the newly created sheet (same id as the old sheet, but it is the first one) Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId)).First(); //Assign it the new sheet id sheet.Id.Value = replacementPartId; //remove the old sheet workbookPart.DeletePart(worksheetPart); //Done myDoc.Close(); //Delete template file if (File.Exists(templatePath)) { File.Delete(templatePath); } } }
public static MemoryStream ExportDSToExcel(DataSet ds) { MemoryStream StreamSpreadsheet = new MemoryStream(); try { using (var spreadsheetDocument = SpreadsheetDocument.Create(StreamSpreadsheet, SpreadsheetDocumentType.Workbook)) { // Add a WorkbookPart to the document WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); // Instantiate a Workbook in the WorkbookPart\ workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); // Add Sheets to the Workbook. Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets()); // Declaring Writer to the WorksheetPart on using statement using OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart); // Declaring row and cell objects to reuse in the construction of the Sheet Row row = null; Cell cell = null; // For each table, generate a sheet and fill it with the correspondent data foreach (DataTable table in ds.Tables) { // Append a new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = table.TableName }; sheets.Append(sheet); writer.WriteStartElement(new Worksheet()); // Writes init tag for worksheet writer.WriteStartElement(new SheetData()); // Writes init tag for sheetData #region Header Row row = new Row(); writer.WriteStartElement(row); // Init tag Row(Header) List <string> columns = new List <string>(); // Used for searching values in the DataTable foreach (DataColumn column in table.Columns) { columns.Add(column.ColumnName); cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(column.ColumnName); writer.WriteElement(cell); } writer.WriteEndElement(); // End tag Row(Header) #endregion #region Common rows foreach (DataRow dsrow in table.Rows) { row = new Row(); writer.WriteStartElement(row); // Init tag Row foreach (string col in columns) { cell = new Cell(); // Checks and sets data type for the cell according to the DataTable data type if (dsrow[col] is int) { cell.DataType = CellValues.Number; } else if (dsrow[col] is DateTime) { cell.DataType = CellValues.Date; } else if (dsrow[col] is bool) { cell.DataType = CellValues.Boolean; } else { cell.DataType = CellValues.String; } cell.CellValue = new CellValue(dsrow[col].ToString()); writer.WriteElement(cell); } writer.WriteEndElement(); // End tag Row } #endregion writer.WriteEndElement(); // End tag SheetData writer.WriteEndElement(); // End tag Worksheet workbookpart.Workbook.Save(); writer.Dispose(); spreadsheetDocument.Save(); spreadsheetDocument.Close(); } } } catch (Exception) { throw; } return(StreamSpreadsheet); }
/// <summary> /// CellValues = Boolean -> expects cellValue "True" or "False" /// CellValues = InlineString -> stores string within sheet /// CellValues = SharedString -> stores index within sheet. If this is called, please call CreateShareStringPart after creating all sheet data to create the shared string part /// CellValues = Date -> expects ((DateTime)value).ToOADate().ToString(CultureInfo.InvariantCulture) as cellValue /// and new OpenXmlAttribute[] { new OpenXmlAttribute("s", null, "1") }.ToList() as attributes so that the correct formatting can be applied /// /// </summary> /// <param name="writer"></param> /// <param name="cellValue"></param> /// <param name="dataType"></param> /// <param name="attributes"></param> public void WriteCellValueSax(OpenXmlWriter writer, string cellValue, CellValues dataType, List <OpenXmlAttribute> attributes = null) { switch (dataType) { case CellValues.InlineString: { if (attributes == null) { attributes = new List <OpenXmlAttribute>(); } attributes.Add(new OpenXmlAttribute("t", null, "inlineStr")); writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new InlineString(new Text(cellValue))); writer.WriteEndElement(); break; } case CellValues.SharedString: { if (attributes == null) { attributes = new List <OpenXmlAttribute>(); } attributes.Add(new OpenXmlAttribute("t", null, "s")); //shared string type writer.WriteStartElement(new Cell(), attributes); if (!_shareStringDictionary.ContainsKey(cellValue)) { _shareStringDictionary.Add(cellValue, _shareStringMaxIndex); _shareStringMaxIndex++; } //writing the index as the cell value writer.WriteElement(new CellValue(_shareStringDictionary[cellValue].ToString())); writer.WriteEndElement(); //cell break; } case CellValues.Date: { if (attributes == null) { writer.WriteStartElement(new Cell() { DataType = CellValues.Number }); } else { writer.WriteStartElement(new Cell() { DataType = CellValues.Number }, attributes); } writer.WriteElement(new CellValue(cellValue)); writer.WriteEndElement(); break; } case CellValues.Boolean: { if (attributes == null) { attributes = new List <OpenXmlAttribute>(); } attributes.Add(new OpenXmlAttribute("t", null, "b")); //boolean type writer.WriteStartElement(new Cell(), attributes); writer.WriteElement(new CellValue(cellValue == "True" ? "1" : "0")); writer.WriteEndElement(); break; } default: { if (attributes == null) { writer.WriteStartElement(new Cell() { DataType = dataType }); } else { writer.WriteStartElement(new Cell() { DataType = dataType }, attributes); } writer.WriteElement(new CellValue(cellValue)); writer.WriteEndElement(); break; } } }
private void WriteCommentPart(WorksheetCommentsPart wcp, VmlDrawingPart vdp) { List <SLCellPoint> listCommentKeys = slws.Comments.Keys.ToList <SLCellPoint>(); listCommentKeys.Sort(new SLCellReferencePointComparer()); bool bAuthorFound = false; int iAuthorIndex = 0; SLComment comm; int i = 0; SLRowProperties rp; SLColumnProperties cp; SLCellPoint pt; // just in case if (slws.Authors.Count == 0) { if (this.DocumentProperties.Creator.Length > 0) { slws.Authors.Add(this.DocumentProperties.Creator); } else { slws.Authors.Add(SLConstants.ApplicationName); } } int iDataRange = 1; // hah! optional... we'll see... int iOptionalShapeTypeId = 202; string sShapeTypeId = string.Format("_x0000_t{0}", iOptionalShapeTypeId); int iShapeIdBase = iDataRange * 1024; int iRowID = 0; int iColumnID = 0; double fRowRemainder = 0; double fColumnRemainder = 0; long lRowEMU = 0; long lColumnEMU = 0; long lRowRemainder = 0; long lColumnRemainder = 0; int iEMU = 0; double fMargin = 0; double fFrac = 0; int iFrac = 0; string sFrac = string.Empty; ImagePart imgp; string sFileName = string.Empty; // image data in base 64, relationship ID Dictionary <string, string> dictImageData = new Dictionary <string, string>(); // not supporting existing VML drawings. But if supporting, process the VmlDrawingPart for // ImageParts. // Apparently, Excel chokes if a "non-standard" relationship ID is given // to VML drawings. It seems to only accept the form "rId{num}", and {num} seems // to have to start from 1. I don't know if Excel will also choke if you jump // from 1 to 3, but I *do* know you can't even start from rId3. // Excel VML seems to be particularly strict on this... // The error originated by having a "non-standard" relationship ID for a // VML image. Say "R2dk723lgsjg2" or whatever. Then fire up Excel and open // that spreadsheet. Then save. Then open it again. You'll get an error. // Apparently, Excel will put "rId1" on the tag o:relid. The error is that // the original o:relid with "R2dk723lgsjg2" as the value is still there. // Meaning the o:relid attribute is duplicated, hence the error. // Why don't I just use the number of vdp.Parts or even vdp.ImageParts to // get the next valid relationship ID? I don't know. Paranoia? // The existing relationship IDs *might* be in sequential order, but you never // know what Excel accepts... If you can get me the Microsoft Excel developer // who can explain this, I'll gladly change the algorithm... // So why the dictionary? Apparently, Excel also chokes if there are duplicates of // the VML image. So even 2 unique relationship IDs that *happens* to have identical // image data will tie Excel into knots. I am so upset with Excel right now... // I know it will keep file size down if only unique image data is stored, but still... StringBuilder sbVml = new StringBuilder(); sbVml.Append("<xml xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">"); sbVml.Append("<o:shapelayout v:ext=\"edit\">"); sbVml.AppendFormat("<o:idmap v:ext=\"edit\" data=\"{0}\"/>", iDataRange); sbVml.Append("</o:shapelayout>"); sbVml.AppendFormat("<v:shapetype id=\"{0}\" coordsize=\"21600,21600\" o:spt=\"{1}\" path=\"m,l,21600r21600,l21600,xe\">", sShapeTypeId, iOptionalShapeTypeId); sbVml.Append("<v:stroke joinstyle=\"miter\"/><v:path gradientshapeok=\"t\" o:connecttype=\"rect\"/>"); sbVml.Append("</v:shapetype>"); using (OpenXmlWriter oxwComment = OpenXmlWriter.Create(wcp)) { oxwComment.WriteStartElement(new Comments()); oxwComment.WriteStartElement(new Authors()); for (i = 0; i < slws.Authors.Count; ++i) { oxwComment.WriteElement(new Author(slws.Authors[i])); } oxwComment.WriteEndElement(); oxwComment.WriteStartElement(new CommentList()); for (i = 0; i < listCommentKeys.Count; ++i) { pt = listCommentKeys[i]; comm = slws.Comments[pt]; bAuthorFound = false; for (iAuthorIndex = 0; iAuthorIndex < slws.Authors.Count; ++iAuthorIndex) { if (comm.Author.Equals(slws.Authors[iAuthorIndex])) { bAuthorFound = true; break; } } if (!bAuthorFound) { iAuthorIndex = 0; } oxwComment.WriteStartElement(new Comment() { Reference = SLTool.ToCellReference(pt.RowIndex, pt.ColumnIndex), AuthorId = (uint)iAuthorIndex }); oxwComment.WriteElement(comm.rst.ToCommentText()); oxwComment.WriteEndElement(); sbVml.AppendFormat("<v:shape id=\"_x0000_s{0}\" type=\"#{1}\"", iShapeIdBase + i + 1, sShapeTypeId); sbVml.Append(" style='position:absolute;"); if (!comm.HasSetPosition) { comm.Top = pt.RowIndex - 1 + SLConstants.DefaultCommentTopOffset; comm.Left = pt.ColumnIndex + SLConstants.DefaultCommentLeftOffset; if (comm.Top < 0) { comm.Top = 0; } if (comm.Left < 0) { comm.Left = 0; } } if (comm.UsePositionMargin) { sbVml.AppendFormat("margin-left:{0}pt;", comm.LeftMargin.ToString("0.##", CultureInfo.InvariantCulture)); sbVml.AppendFormat("margin-top:{0}pt;", comm.TopMargin.ToString("0.##", CultureInfo.InvariantCulture)); } else { iRowID = (int)Math.Floor(comm.Top); fRowRemainder = comm.Top - iRowID; iColumnID = (int)Math.Floor(comm.Left); fColumnRemainder = comm.Left - iColumnID; lRowEMU = 0; lColumnEMU = 0; for (iEMU = 1; iEMU <= iRowID; ++iEMU) { if (slws.RowProperties.ContainsKey(iEMU)) { rp = slws.RowProperties[iEMU]; lRowEMU += rp.HeightInEMU; } else { lRowEMU += slws.SheetFormatProperties.DefaultRowHeightInEMU; } } if (slws.RowProperties.ContainsKey(iRowID + 1)) { rp = slws.RowProperties[iRowID + 1]; lRowRemainder = Convert.ToInt64(fRowRemainder * rp.HeightInEMU); lRowEMU += lRowRemainder; } else { lRowRemainder = Convert.ToInt64(fRowRemainder * slws.SheetFormatProperties.DefaultRowHeightInEMU); lRowEMU += lRowRemainder; } for (iEMU = 1; iEMU <= iColumnID; ++iEMU) { if (slws.ColumnBreaks.ContainsKey(iEMU)) { cp = slws.ColumnProperties[iEMU]; lColumnEMU += cp.WidthInEMU; } else { lColumnEMU += slws.SheetFormatProperties.DefaultColumnWidthInEMU; } } if (slws.ColumnProperties.ContainsKey(iColumnID + 1)) { cp = slws.ColumnProperties[iColumnID + 1]; lColumnRemainder = Convert.ToInt64(fColumnRemainder * cp.WidthInEMU); lColumnEMU += lColumnRemainder; } else { lColumnRemainder = Convert.ToInt64(fColumnRemainder * slws.SheetFormatProperties.DefaultColumnWidthInEMU); lColumnEMU += lColumnRemainder; } fMargin = (double)lColumnEMU / (double)SLConstants.PointToEMU; sbVml.AppendFormat("margin-left:{0}pt;", fMargin.ToString("0.##", CultureInfo.InvariantCulture)); fMargin = (double)lRowEMU / (double)SLConstants.PointToEMU; sbVml.AppendFormat("margin-top:{0}pt;", fMargin.ToString("0.##", CultureInfo.InvariantCulture)); } if (comm.AutoSize) { sbVml.Append("width:auto;height:auto;"); } else { sbVml.AppendFormat("width:{0}pt;", comm.Width.ToString("0.##", CultureInfo.InvariantCulture)); sbVml.AppendFormat("height:{0}pt;", comm.Height.ToString("0.##", CultureInfo.InvariantCulture)); } sbVml.AppendFormat("z-index:{0};", i + 1); sbVml.AppendFormat("visibility:{0}'", comm.Visible ? "visible" : "hidden"); if (!comm.Fill.HasFill) { // use #ffffff ? sbVml.Append(" fillcolor=\"window [65]\""); } else if (comm.Fill.Type == SLA.SLFillType.NoFill) { sbVml.Append(" filled=\"f\""); sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); } else if (comm.Fill.Type == SLA.SLFillType.SolidFill) { sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); } else if (comm.Fill.Type == SLA.SLFillType.GradientFill) { if (comm.Fill.GradientColor.GradientStops.Count > 0) { sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"", comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.R.ToString("x2"), comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.G.ToString("x2"), comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.B.ToString("x2")); } } else if (comm.Fill.Type == SLA.SLFillType.BlipFill) { // don't have to do anything } else if (comm.Fill.Type == SLA.SLFillType.PatternFill) { sbVml.AppendFormat(" fillcolor=\"#{0}{1}{2}\"", comm.Fill.PatternForegroundColor.DisplayColor.R.ToString("x2"), comm.Fill.PatternForegroundColor.DisplayColor.G.ToString("x2"), comm.Fill.PatternForegroundColor.DisplayColor.B.ToString("x2")); } if (comm.LineColor != null) { sbVml.AppendFormat(" strokecolor=\"#{0}{1}{2}\"", comm.LineColor.Value.R.ToString("x2"), comm.LineColor.Value.G.ToString("x2"), comm.LineColor.Value.B.ToString("x2")); } if (comm.fLineWeight != null) { sbVml.AppendFormat(" strokeweight=\"{0}pt\"", comm.fLineWeight.Value.ToString("0.##", CultureInfo.InvariantCulture)); } sbVml.Append(" o:insetmode=\"auto\">"); sbVml.Append("<v:fill"); if (comm.Fill.Type == SLA.SLFillType.SolidFill || comm.Fill.Type == SLA.SLFillType.GradientFill) { if (comm.Fill.Type == SLA.SLFillType.SolidFill) { fFrac = 100.0 - (double)comm.Fill.SolidColor.Transparency; } else { fFrac = 100.0 - comm.bFromTransparency; } iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0); if (iFrac <= 0) { sFrac = "0"; } else if (iFrac >= 65536) { sFrac = "1"; } else { sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture)); } // default is 1 if (!sFrac.Equals("1")) { sbVml.AppendFormat(" opacity=\"{0}\"", sFrac); } } if (comm.Fill.Type == SLA.SLFillType.SolidFill) { sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); } else if (comm.Fill.Type == SLA.SLFillType.GradientFill) { if (comm.Fill.GradientColor.GradientStops.Count > 0) { sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.R.ToString("x2"), comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.G.ToString("x2"), comm.Fill.GradientColor.GradientStops[0].Color.DisplayColor.B.ToString("x2")); } else { // shouldn't happen, but you know, in case... sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); } fFrac = 100.0 - comm.bToTransparency; iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0); if (iFrac <= 0) { sFrac = "0"; } else if (iFrac >= 65536) { sFrac = "1"; } else { sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture)); } // default is 1 if (!sFrac.Equals("1")) { sbVml.AppendFormat(" o:opacity=\"{0}\"", sFrac); } sbVml.Append(" rotate=\"t\""); if (comm.Fill.GradientColor.GradientStops.Count > 0) { sbVml.Append(" colors=\""); for (int iGradient = 0; iGradient < comm.Fill.GradientColor.GradientStops.Count; ++iGradient) { // you take the position/gradient value straight fFrac = (double)comm.Fill.GradientColor.GradientStops[iGradient].Position; iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0); if (iFrac <= 0) { sFrac = "0"; } else if (iFrac >= 65536) { sFrac = "1"; } else { sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture)); } if (iGradient > 0) { sbVml.Append(";"); } sbVml.AppendFormat("{0} #{1}{2}{3}", sFrac, comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.R.ToString("x2"), comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.G.ToString("x2"), comm.Fill.GradientColor.GradientStops[iGradient].Color.DisplayColor.B.ToString("x2")); } sbVml.Append("\""); } if (comm.Fill.GradientColor.IsLinear) { // use temporarily // VML increases angles in counter-clockwise direction, // otherwise we'd just use the angle straight from the property //...fFrac = 360.0 - (double)comm.Fill.GradientColor.Angle; fFrac = (double)comm.Fill.GradientColor.Angle; sbVml.AppendFormat(" angle=\"{0}\"", fFrac.ToString("0.##", CultureInfo.InvariantCulture)); sbVml.Append(" focus=\"100%\" type=\"gradient\""); } else { switch (comm.Fill.GradientColor.PathType) { case A.PathShadeValues.Shape: sbVml.Append(" focusposition=\"50%,50%\" focus=\"100%\" type=\"gradientradial\""); break; case A.PathShadeValues.Rectangle: case A.PathShadeValues.Circle: // because there's no way to do a circular gradient with VML... switch (comm.Fill.GradientColor.Direction) { case SLA.SLGradientDirectionValues.Center: sbVml.Append(" focusposition=\"50%,50%\""); break; case SLA.SLGradientDirectionValues.CenterToBottomLeftCorner: // so the "centre" is at the top-right sbVml.Append(" focusposition=\"100%,0%\""); break; case SLA.SLGradientDirectionValues.CenterToBottomRightCorner: // so the "centre" is at the top-left sbVml.Append(" focusposition=\"0%,0%\""); break; case SLA.SLGradientDirectionValues.CenterToTopLeftCorner: // so the "centre" is at the bottom-right sbVml.Append(" focusposition=\"100%,100%\""); break; case SLA.SLGradientDirectionValues.CenterToTopRightCorner: // so the "centre" is at the bottom-left sbVml.Append(" focusposition=\"0%,100%\""); break; } sbVml.Append(" focus=\"100%\" type=\"gradientradial\""); break; } } } else if (comm.Fill.Type == SLA.SLFillType.BlipFill) { string sRelId = "rId1"; using (FileStream fs = new FileStream(comm.Fill.BlipFileName, FileMode.Open)) { byte[] ba = new byte[fs.Length]; fs.Read(ba, 0, ba.Length); string sImageData = Convert.ToBase64String(ba); if (dictImageData.ContainsKey(sImageData)) { sRelId = dictImageData[sImageData]; comm.Fill.BlipRelationshipID = sRelId; } else { // if we haven't found a viable relationship ID by 10 million iterations, // then we have serious issues... for (int iIDNum = 1; iIDNum <= SLConstants.VmlTenMillionIterations; ++iIDNum) { sRelId = string.Format("rId{0}", iIDNum.ToString(CultureInfo.InvariantCulture)); // we could use a hashset to store the relationship IDs so we // don't use the ContainsValue() because ContainsValue() is supposedly // slow... I'm not gonna care because if this algorithm slows enough // that ContainsValue() is inefficient, that means there are enough VML // drawings to choke a modestly sized art museum. if (!dictImageData.ContainsValue(sRelId)) { break; } } imgp = vdp.AddImagePart(SLA.SLDrawingTool.GetImagePartType(comm.Fill.BlipFileName), sRelId); fs.Position = 0; imgp.FeedData(fs); comm.Fill.BlipRelationshipID = vdp.GetIdOfPart(imgp); dictImageData[sImageData] = sRelId; } } sbVml.AppendFormat(" o:relid=\"{0}\"", comm.Fill.BlipRelationshipID); // all this to get from "myawesomepicture.jpg" to "myawesomepicture" sFileName = comm.Fill.BlipFileName; // use temporarily iFrac = sFileName.LastIndexOfAny("\\/".ToCharArray()); sFileName = sFileName.Substring(iFrac + 1); iFrac = sFileName.LastIndexOf("."); sFileName = sFileName.Substring(0, iFrac); sbVml.AppendFormat(" o:title=\"{0}\"", sFileName); sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.SolidColor.DisplayColor.R.ToString("x2"), comm.Fill.SolidColor.DisplayColor.G.ToString("x2"), comm.Fill.SolidColor.DisplayColor.B.ToString("x2")); sbVml.Append(" recolor=\"t\" rotate=\"t\""); fFrac = 100.0 - (double)comm.Fill.BlipTransparency; iFrac = Convert.ToInt32(fFrac * 65536.0 / 100.0); if (iFrac <= 0) { sFrac = "0"; } else if (iFrac >= 65536) { sFrac = "1"; } else { sFrac = string.Format("{0}f", iFrac.ToString(CultureInfo.InvariantCulture)); } // default is 1 if (!sFrac.Equals("1")) { sbVml.AppendFormat(" o:opacity=\"{0}\"", sFrac); } if (comm.Fill.BlipTile) { sbVml.Append(" type=\"tile\""); sbVml.AppendFormat(" size=\"{0}%,{1}%\"", comm.Fill.BlipScaleX.ToString("0.##", CultureInfo.InvariantCulture), comm.Fill.BlipScaleY.ToString("0.##", CultureInfo.InvariantCulture)); } else { sbVml.Append(" type=\"frame\""); // use temporarily //fFrac = (50.0 - (double)comm.Fill.BlipLeftOffset) + (50.0 - (double)comm.Fill.BlipRightOffset); fFrac = 100.0 - (double)comm.Fill.BlipLeftOffset - (double)comm.Fill.BlipRightOffset; sbVml.AppendFormat(" size=\"{0}%,", fFrac.ToString("0.##", CultureInfo.InvariantCulture)); fFrac = 100.0 - (double)comm.Fill.BlipTopOffset - (double)comm.Fill.BlipBottomOffset; sbVml.AppendFormat("{0}%\"", fFrac.ToString("0.##", CultureInfo.InvariantCulture)); } } else if (comm.Fill.Type == SLA.SLFillType.PatternFill) { string sRelId = "rId1"; using (MemoryStream ms = new MemoryStream()) { using (System.Drawing.Bitmap bm = SLA.SLDrawingTool.GetVmlPatternFill(comm.Fill.PatternPreset)) { bm.Save(ms, System.Drawing.Imaging.ImageFormat.Png); } byte[] ba = new byte[ms.Length]; ms.Read(ba, 0, ba.Length); string sImageData = Convert.ToBase64String(ba); if (dictImageData.ContainsKey(sImageData)) { sRelId = dictImageData[sImageData]; comm.Fill.BlipRelationshipID = sRelId; } else { // go check the "normal" image part additions for comments... for (int iIDNum = 1; iIDNum <= SLConstants.VmlTenMillionIterations; ++iIDNum) { sRelId = string.Format("rId{0}", iIDNum.ToString(CultureInfo.InvariantCulture)); if (!dictImageData.ContainsValue(sRelId)) { break; } } imgp = vdp.AddImagePart(ImagePartType.Png, sRelId); ms.Position = 0; imgp.FeedData(ms); comm.Fill.BlipRelationshipID = vdp.GetIdOfPart(imgp); dictImageData[sImageData] = sRelId; } } sbVml.AppendFormat(" o:relid=\"{0}\"", comm.Fill.BlipRelationshipID); sbVml.AppendFormat(" o:title=\"{0}\"", SLA.SLDrawingTool.ConvertToVmlTitle(comm.Fill.PatternPreset)); sbVml.AppendFormat(" color2=\"#{0}{1}{2}\"", comm.Fill.PatternBackgroundColor.DisplayColor.R.ToString("x2"), comm.Fill.PatternBackgroundColor.DisplayColor.G.ToString("x2"), comm.Fill.PatternBackgroundColor.DisplayColor.B.ToString("x2")); sbVml.Append(" recolor=\"t\" type=\"pattern\""); } sbVml.Append("/>"); if (comm.LineStyle != StrokeLineStyleValues.Single || comm.vLineDashStyle != null) { sbVml.Append("<v:stroke"); switch (comm.LineStyle) { case StrokeLineStyleValues.Single: // don't have to do anything break; case StrokeLineStyleValues.ThickBetweenThin: sbVml.Append(" linestyle=\"thickBetweenThin\"/>"); break; case StrokeLineStyleValues.ThickThin: sbVml.Append(" linestyle=\"thickThin\"/>"); break; case StrokeLineStyleValues.ThinThick: sbVml.Append(" linestyle=\"thinThick\"/>"); break; case StrokeLineStyleValues.ThinThin: sbVml.Append(" linestyle=\"thinThin\"/>"); break; } if (comm.vLineDashStyle != null) { switch (comm.vLineDashStyle.Value) { case SLDashStyleValues.Solid: sbVml.Append(" dashstyle=\"solid\"/>"); break; case SLDashStyleValues.ShortDash: sbVml.Append(" dashstyle=\"shortdash\"/>"); break; case SLDashStyleValues.ShortDot: sbVml.Append(" dashstyle=\"shortdot\"/>"); break; case SLDashStyleValues.ShortDashDot: sbVml.Append(" dashstyle=\"shortdashdot\"/>"); break; case SLDashStyleValues.ShortDashDotDot: sbVml.Append(" dashstyle=\"shortdashdotdot\"/>"); break; case SLDashStyleValues.Dot: sbVml.Append(" dashstyle=\"dot\"/>"); break; case SLDashStyleValues.Dash: sbVml.Append(" dashstyle=\"dash\"/>"); break; case SLDashStyleValues.LongDash: sbVml.Append(" dashstyle=\"longdash\"/>"); break; case SLDashStyleValues.DashDot: sbVml.Append(" dashstyle=\"dashdot\"/>"); break; case SLDashStyleValues.LongDashDot: sbVml.Append(" dashstyle=\"longdashdot\"/>"); break; case SLDashStyleValues.LongDashDotDot: sbVml.Append(" dashstyle=\"longdashdotdot\"/>"); break; } } if (comm.vEndCap != null) { switch (comm.vEndCap.Value) { case StrokeEndCapValues.Flat: sbVml.Append(" endcap=\"flat\"/>"); break; case StrokeEndCapValues.Round: sbVml.Append(" endcap=\"round\"/>"); break; case StrokeEndCapValues.Square: sbVml.Append(" endcap=\"square\"/>"); break; } } sbVml.Append("/>"); } if (comm.HasShadow) { sbVml.AppendFormat("<v:shadow on=\"t\" color=\"#{0}{1}{2}\" obscured=\"t\"/>", comm.ShadowColor.R.ToString("x2"), comm.ShadowColor.G.ToString("x2"), comm.ShadowColor.B.ToString("x2")); } sbVml.Append("<v:path o:connecttype=\"none\"/>"); sbVml.Append("<v:textbox style='mso-direction-alt:auto;"); switch (comm.Orientation) { case SLCommentOrientationValues.Horizontal: // don't have to do anything break; case SLCommentOrientationValues.TopDown: sbVml.Append("layout-flow:vertical;mso-layout-flow-alt:top-to-bottom;"); break; case SLCommentOrientationValues.Rotated270Degrees: sbVml.Append("layout-flow:vertical;mso-layout-flow-alt:bottom-to-top;"); break; case SLCommentOrientationValues.Rotated90Degrees: sbVml.Append("layout-flow:vertical;"); break; } if (comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft) { sbVml.Append("direction:RTL;"); } // no else because don't have to do anything if (comm.AutoSize) { sbVml.Append("mso-fit-shape-to-text:t;"); } sbVml.Append("'><div"); if (comm.HorizontalTextAlignment != SLHorizontalTextAlignmentValues.Distributed || comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft) { sbVml.Append(" style='"); switch (comm.HorizontalTextAlignment) { case SLHorizontalTextAlignmentValues.Left: sbVml.Append("text-align:left;"); break; case SLHorizontalTextAlignmentValues.Justify: sbVml.Append("text-align:justify;"); break; case SLHorizontalTextAlignmentValues.Center: sbVml.Append("text-align:center;"); break; case SLHorizontalTextAlignmentValues.Right: sbVml.Append("text-align:right;"); break; case SLHorizontalTextAlignmentValues.Distributed: // don't have to do anything break; } if (comm.TextDirection == SLAlignmentReadingOrderValues.RightToLeft) { sbVml.Append("direction:rtl;"); } sbVml.Append("'"); } sbVml.Append("></div>"); sbVml.Append("</v:textbox>"); sbVml.Append("<x:ClientData ObjectType=\"Note\">"); sbVml.Append("<x:MoveWithCells/>"); sbVml.Append("<x:SizeWithCells/>"); // anchors are bloody hindering awkward inconvenient to calculate... //sbVml.Append("<x:Anchor>"); //sbVml.Append("2, 15, 2, 14, 4, 23, 6, 19"); //sbVml.Append("</x:Anchor>"); sbVml.Append("<x:AutoFill>False</x:AutoFill>"); switch (comm.HorizontalTextAlignment) { case SLHorizontalTextAlignmentValues.Left: // don't have to do anything break; case SLHorizontalTextAlignmentValues.Justify: sbVml.Append("<x:TextHAlign>Justify</x:TextHAlign>"); break; case SLHorizontalTextAlignmentValues.Center: sbVml.Append("<x:TextHAlign>Center</x:TextHAlign>"); break; case SLHorizontalTextAlignmentValues.Right: sbVml.Append("<x:TextHAlign>Right</x:TextHAlign>"); break; case SLHorizontalTextAlignmentValues.Distributed: sbVml.Append("<x:TextHAlign>Distributed</x:TextHAlign>"); break; } switch (comm.VerticalTextAlignment) { case SLVerticalTextAlignmentValues.Top: // don't have to do anything break; case SLVerticalTextAlignmentValues.Justify: sbVml.Append("<x:TextVAlign>Justify</x:TextVAlign>"); break; case SLVerticalTextAlignmentValues.Center: sbVml.Append("<x:TextVAlign>Center</x:TextVAlign>"); break; case SLVerticalTextAlignmentValues.Bottom: sbVml.Append("<x:TextVAlign>Bottom</x:TextVAlign>"); break; case SLVerticalTextAlignmentValues.Distributed: sbVml.Append("<x:TextVAlign>Distributed</x:TextVAlign>"); break; } sbVml.AppendFormat("<x:Row>{0}</x:Row>", pt.RowIndex - 1); sbVml.AppendFormat("<x:Column>{0}</x:Column>", pt.ColumnIndex - 1); if (comm.Visible) { sbVml.Append("<x:Visible/>"); } sbVml.Append("</x:ClientData>"); sbVml.Append("</v:shape>"); } oxwComment.WriteEndElement(); // this is for Comments oxwComment.WriteEndElement(); } sbVml.Append("</xml>"); using (MemoryStream mem = new MemoryStream(Encoding.ASCII.GetBytes(sbVml.ToString()))) { vdp.FeedData(mem); } }
private void writeHyperlinks(OpenXmlWriter writer, WorksheetPart woorksheetPart, SpreadsheetHyperlinkManager hyperlinkManager) { var hyperlinks = hyperlinkManager.GetHyperlinks(); if (!hyperlinks.Any()) { return; } var hyperlinkTargetRelationshipIds = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase); var clickableHyperLinks = new HashSet <KeyValuePair <SpreadsheetLocation, SpreadsheetHyperLink> >(); // we must add external URLs as relationships and reference them in the hyperlinks // the more unique URLs there are the more performance problems there are // because of this, a limit of INT_MaxUniqueHyperlinks was put in place // without it the export can take 10x longer and it only gets worse the more unique links there are // why is that? // when adding a new relationship there is a uniqueness check. The check is performed sequentialy so the actual impact i O(n^2) // also this part does not seem streamable so all of the openxml element would need to be stored in the memory foreach (var hyperlink in hyperlinks.Where(x => !string.IsNullOrEmpty(x.Value.Target))) { var target = hyperlink.Value.Target; if (hyperlinkTargetRelationshipIds.Count > MaxUniqueHyperlinks) { break; } clickableHyperLinks.Add(hyperlink); if (!hyperlinkTargetRelationshipIds.ContainsKey(target)) { var uri = Utilities.SafelyCreateUri(target); if (uri == null) { hyperlinkTargetRelationshipIds[target] = ""; continue; } var relId = woorksheetPart.AddHyperlinkRelationship(uri, true).Id; hyperlinkTargetRelationshipIds[target] = relId; } } if (clickableHyperLinks.Count == 0) { return; } writer.WriteStartElement(new Hyperlinks()); foreach (var link in clickableHyperLinks) { var attributes = new List <OpenXmlAttribute>(); attributes.Add(new OpenXmlAttribute("ref", null, string.Format("{0}{1}", SpreadsheetHelper.ExcelColumnFromNumber(link.Key.ColumnIndex), link.Key.RowIndex))); string id; if (hyperlinkTargetRelationshipIds.TryGetValue(link.Value.Target, out id) && !string.IsNullOrEmpty(id)) { var idAtt = new OpenXmlAttribute("r", "id", "http://schemas.openxmlformats.org/officeDocument/2006/relationships", hyperlinkTargetRelationshipIds[link.Value.Target]); attributes.Add(idAtt); } else { attributes.Add(new OpenXmlAttribute("location", null, link.Value.Target)); attributes.Add(new OpenXmlAttribute("display", null, link.Value.DisplayValue)); } writer.WriteStartElement(new Hyperlink(), attributes); writer.WriteEndElement(); } writer.WriteEndElement(); }
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, DefinedNames definedNamesCol) { OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII); writer.WriteStartElement(new Worksheet()); // To demonstrate how to set column-widths in Excel, here's how to set the width of all columns to our default of "25": UInt32 inx = 1; writer.WriteStartElement(new Columns()); foreach (DataColumn dc in dt.Columns) { writer.WriteElement(new Column { Min = inx, Max = inx, CustomWidth = true, Width = DEFAULT_COLUMN_WIDTH }); inx++; } writer.WriteEndElement(); writer.WriteStartElement(new SheetData()); string cellValue = ""; string cellReference = ""; // Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable. // // We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual // cells of data, we'll know if to write Text values or Numeric cell values. int numberOfColumns = dt.Columns.Count; bool[] IsIntegerColumn = new bool[numberOfColumns]; bool[] IsFloatColumn = new bool[numberOfColumns]; bool[] IsDateColumn = new bool[numberOfColumns]; string[] excelColumnNames = new string[numberOfColumns]; for (int n = 0; n < numberOfColumns; n++) { excelColumnNames[n] = GetExcelColumnName(n); } // // Create the Header row in our Excel Worksheet // We'll set the row-height to 20px, and (using the "AppendHeaderTextCell" function) apply some formatting to the cells. // uint rowIndex = 1; writer.WriteStartElement(new Row { RowIndex = rowIndex, Height = 20, CustomHeight = true }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { DataColumn col = dt.Columns[colInx]; AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName, writer); IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int")); IsFloatColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single"); IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime"); // Uncomment the following lines, for an example of how to create some Named Ranges in your Excel file #if FALSE // For each column of data in this worksheet, let's create a Named Range, showing where there are values in this column // eg "NamedRange_UserID" = "Drivers!$A2:$A6" // "NamedRange_Surname" = "Drivers!$B2:$B6" string columnHeader = col.ColumnName.Replace(" ", "_"); string NamedRange = string.Format("{0}!${1}2:${2}{3}", worksheetName, excelColumnNames[colInx], excelColumnNames[colInx], dt.Rows.Count + 1); DefinedName definedName = new DefinedName() { Name = "NamedRange_" + columnHeader, Text = NamedRange }; definedNamesCol.Append(definedName); #endif } writer.WriteEndElement(); // End of header "Row" // // Now, step through each row of data in our DataTable... // double cellFloatValue = 0; CultureInfo ci = new CultureInfo("en-US"); foreach (DataRow dr in dt.Rows) { // ...create a new row, and append a set of this row's data to it. ++rowIndex; writer.WriteStartElement(new Row { RowIndex = rowIndex }); for (int colInx = 0; colInx < numberOfColumns; colInx++) { cellValue = dr.ItemArray[colInx].ToString(); cellValue = ReplaceHexadecimalSymbols(cellValue); cellReference = excelColumnNames[colInx] + rowIndex.ToString(); // Create cell with data if (IsIntegerColumn[colInx] || IsFloatColumn[colInx]) { // For numeric cells without any decimal places. // If this numeric value is NULL, then don't write anything to the Excel file. cellFloatValue = 0; bool bIncludeDecimalPlaces = IsFloatColumn[colInx]; if (double.TryParse(cellValue, out cellFloatValue)) { cellValue = cellFloatValue.ToString(ci); AppendNumericCell(cellReference, cellValue, bIncludeDecimalPlaces, writer); } } else if (IsDateColumn[colInx]) { // For date values, we save the value to Excel as a number, but need to set the cell's style to format // it as either a date or a date-time. DateTime dateValue; if (DateTime.TryParse(cellValue, out dateValue)) { AppendDateCell(cellReference, dateValue, writer); } else { // This should only happen if we have a DataColumn of type "DateTime", but this particular value is null/blank. AppendTextCell(cellReference, cellValue, writer); } } else { // For text cells, just write the input data straight out to the Excel file. AppendTextCell(cellReference, cellValue, writer); } } writer.WriteEndElement(); // End of Row } writer.WriteEndElement(); // End of SheetData writer.WriteEndElement(); // End of worksheet writer.Close(); }
/// <summary> /// OpenWriter Style, for Multiple DataTable into Multiple Worksheets in a single Workbook. A real f*****g pain. /// </summary> /// <param name="filePath">Where your file will be.</param> /// <param name="listDataTables">List of dataTables. Can contain just 1, doesn't matter.</param> /// <param name="yesHeader">You want headers?</param> /// <param name="yesZero">You want zero instead of null?</param> public void LargeExportOneWorkbook( string filePath, IEnumerable <DataTable> listDataTables, bool yesHeader = false, bool yesZero = false) { try { using (SpreadsheetDocument document = SpreadsheetDocument.Create( filePath, SpreadsheetDocumentType.Workbook)) { document.AddWorkbookPart(); OpenXmlWriter writerXb = OpenXmlWriter.Create(document.WorkbookPart); writerXb.WriteStartElement(new Workbook()); writerXb.WriteStartElement(new Sheets()); var count = 0; foreach (DataTable dt in listDataTables) { var dicColName = new Dictionary <int, string>(); for (var colIndex = 0; colIndex < dt.Columns.Count; colIndex++) { int dividend = colIndex + 1; string columnName = string.Empty; while (dividend > 0) { int modifier = (dividend - 1) % 26; columnName = $"{Convert.ToChar(65 + modifier).ToString(CultureInfo.InvariantCulture)}{columnName}"; dividend = (dividend - modifier) / 26; } dicColName.Add(colIndex + 1, columnName); } // var dicType = new Dictionary<Type, CellValues>(4) // { // { typeof(DateTime), CellValues.Date }, // { typeof(string), CellValues.InlineString }, // { typeof(double), CellValues.Number }, // { typeof(int), CellValues.Number }, // { typeof(bool), CellValues.Boolean } // }; var dicType = new Dictionary <Type, string>(4) { { typeof(DateTime), "d" }, { typeof(string), "s" }, { typeof(double), "n" }, { typeof(int), "n" }, { typeof(bool), "b" } }; // this list of attributes will be used when writing a start element List <OpenXmlAttribute> attributes; var workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>(); OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart); writer.WriteStartElement(new Worksheet()); writer.WriteStartElement(new SheetData()); if (yesHeader) { // create a new list of attributes attributes = new List <OpenXmlAttribute> { // add the row index attribute to the list new OpenXmlAttribute("r", null, 1.ToString()) }; // write the row start element with the row index attribute writer.WriteStartElement(new Row(), attributes); for (var columnNum = 1; columnNum <= dt.Columns.Count; ++columnNum) { // reset the list of attributes attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("t", null, "str"), new OpenXmlAttribute("r", string.Empty, $"{dicColName[columnNum]}1") }; // add data type attribute - in this case inline string (you might want to look at the shared strings table) // add the cell reference attribute // write the cell start element with the type and reference attributes writer.WriteStartElement(new Cell(), attributes); // write the cell value writer.WriteElement(new CellValue(dt.Columns[columnNum - 1].ColumnName)); // writer.WriteElement(new CellValue(string.Format("This is Row {0}, Cell {1}", rowNum, columnNum))); // write the end cell element writer.WriteEndElement(); } // write the end row element writer.WriteEndElement(); } for (var rowNum = 1; rowNum <= dt.Rows.Count; rowNum++) { // create a new list of attributes attributes = new List <OpenXmlAttribute> { new OpenXmlAttribute("r", null, (yesHeader ? rowNum + 1 : rowNum).ToString()) }; // add the row index attribute to the list // write the row start element with the row index attribute writer.WriteStartElement(new Row(), attributes); DataRow dr = dt.Rows[rowNum - 1]; for (var columnNum = 1; columnNum <= dt.Columns.Count; columnNum++) { Type type = dt.Columns[columnNum - 1].DataType; // reset the list of attributes attributes = new List <OpenXmlAttribute> { // Add data type attribute - in this case inline string (you might want to look at the shared strings table) new OpenXmlAttribute("t", null, type == typeof(string) ? "str" : dicType[type]), // Add the cell reference attribute new OpenXmlAttribute("r", string.Empty, $"{dicColName[columnNum]}{(yesHeader ? rowNum + 1 : rowNum).ToString(CultureInfo.InvariantCulture)}") }; // write the cell start element with the type and reference attributes writer.WriteStartElement(new Cell(), attributes); // write the cell value if (yesZero | (dr[columnNum - 1].ToString() != "0")) { writer.WriteElement(new CellValue(dr[columnNum - 1].ToString())); } // write the end cell element writer.WriteEndElement(); } // write the end row element writer.WriteEndElement(); } // write the end SheetData element writer.WriteEndElement(); // write the end Worksheet element writer.WriteEndElement(); writer.Close(); writerXb.WriteElement( new Sheet { Name = dt.TableName, SheetId = Convert.ToUInt32(count + 1), Id = document.WorkbookPart.GetIdOfPart(workSheetPart) }); count++; } // End Sheets writerXb.WriteEndElement(); // End Workbook writerXb.WriteEndElement(); writerXb.Close(); document.WorkbookPart.Workbook.Save(); document.Close(); } } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } }