public static int InsertRow(WorksheetPart worksheetPart) { SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); Row lastRow = sheetData.Elements <Row>().LastOrDefault(); if (lastRow != null) { sheetData.InsertAfter(new Row() { RowIndex = (lastRow.RowIndex + 1) }, lastRow); return((int)UInt32Value.ToUInt32(lastRow.RowIndex)); } else { sheetData.InsertAt(new Row() { RowIndex = 0 }, 0); return(0); } }
public static UInt32Value GetMaxId(ShapeTree tree) { var maxid = (uint)tree.ChildElements.Count; if (tree.ChildElements.Count > 0) { foreach (var child in tree.ChildElements) { if (child is Shape shape) { if (shape?.NonVisualShapeProperties?.NonVisualDrawingProperties?.Id != null) { var id = UInt32Value.ToUInt32(shape?.NonVisualShapeProperties?.NonVisualDrawingProperties?.Id); if (id > maxid) { maxid = id; } } } } } return(maxid); }
public void OpenXmlSimpleTypeConverterTest() { // 1. Base64BinaryValue Base64BinaryValue base64 = new Base64BinaryValue(); base64 = "AA3322"; Assert.True(base64 == "AA3322"); Assert.Equal("AA3322", base64.Value); base64 = Base64BinaryValue.FromString("1234"); Assert.Equal("1234", base64.ToString()); Assert.Equal("1234", Base64BinaryValue.ToString(base64)); // 2. BooleanValue BooleanValue booleanValue = new BooleanValue(); booleanValue = true; Assert.True(booleanValue); Assert.True(booleanValue.Value); booleanValue = BooleanValue.FromBoolean(false); Assert.False(booleanValue); Assert.False(BooleanValue.ToBoolean(booleanValue)); // 3. ByteValue ByteValue byteValue = new ByteValue(); byte bt = 1; byteValue = bt; Assert.True(bt == byteValue); Assert.Equal(bt, byteValue.Value); bt = 2; byteValue = ByteValue.FromByte(bt); Assert.Equal(bt, ByteValue.ToByte(byteValue)); // 4. DateTimeValue DateTimeValue dtValue = new DateTimeValue(); DateTime dt = DateTime.Now; dtValue = dt; Assert.True(dt == dtValue); dt = DateTime.Now.AddDays(1); dtValue = DateTimeValue.FromDateTime(dt); Assert.Equal(dt, dtValue.Value); Assert.Equal(dt, DateTimeValue.ToDateTime(dt)); // 5. DecimalValue DecimalValue decimalValue = new DecimalValue(); decimal dcm = 10; decimalValue = dcm; Assert.True(dcm == decimalValue); decimalValue = DecimalValue.FromDecimal(20); Assert.Equal(20, decimalValue.Value); Assert.Equal(20, DecimalValue.ToDecimal(decimalValue)); // 6. DoubleValue DoubleValue doubleValue = new DoubleValue(); double dbl = 1.1; doubleValue = dbl; Assert.True(dbl == doubleValue); doubleValue = DoubleValue.FromDouble(2.2); Assert.Equal(2.2, doubleValue.Value); Assert.Equal(2.2, DoubleValue.ToDouble(doubleValue)); // 7. HexBinaryValue HexBinaryValue hexBinaryValue = new HexBinaryValue(); string hex = "0X99CCFF"; hexBinaryValue = hex; Assert.True(hex == hexBinaryValue); hex = "111111"; hexBinaryValue = HexBinaryValue.FromString(hex); Assert.Equal(hex, hexBinaryValue.Value); Assert.Equal(hex, HexBinaryValue.ToString(hexBinaryValue)); // 8. Int16 Int16Value int16Value = new Int16Value(); short int16 = 16; int16Value = int16; Assert.True(int16 == int16Value); int16 = 17; int16Value = Int16Value.FromInt16(int16); Assert.Equal(int16, int16Value.Value); Assert.Equal(int16, Int16Value.ToInt16(int16Value)); // 9. Int32 Int32Value int32Value = new Int32Value(); int int32 = 32; int32Value = int32; Assert.True(int32 == int32Value); int32 = 33; int32Value = Int32Value.FromInt32(int32); Assert.Equal(int32, int32Value.Value); Assert.Equal(int32, Int32Value.ToInt32(int32Value)); // 10. Int64 Int64Value int64Value = new Int64Value(); long int64 = 64; int64Value = int64; Assert.True(int64 == int64Value); int64 = 17; int64Value = Int64Value.FromInt64(int64); Assert.Equal(int64, int64Value.Value); Assert.Equal(int64, Int64Value.ToInt64(int64Value)); // 11. IntegerValue IntegerValue integerValue = new IntegerValue(); int integer = 64; integerValue = integer; Assert.True(integer == integerValue); integer = 17; integerValue = IntegerValue.FromInt64(integer); Assert.Equal(integer, integerValue.Value); Assert.Equal(integer, IntegerValue.ToInt64(integerValue)); // 12. OnOffValue OnOffValue onOffValue = new OnOffValue(); onOffValue = true; Assert.True(onOffValue); onOffValue = OnOffValue.FromBoolean(false); Assert.False(onOffValue.Value); Assert.False(OnOffValue.ToBoolean(onOffValue)); // 13. SByteValue SByteValue sbyteValue = new SByteValue(); sbyte sbt = sbyte.MaxValue; sbyteValue = sbt; Assert.True(sbt == sbyteValue); sbt = sbyte.MinValue; sbyteValue = SByteValue.FromSByte(sbt); Assert.Equal(sbt, sbyteValue.Value); Assert.Equal(sbt, SByteValue.ToSByte(sbt)); // 14. SingleValue SingleValue singleValue = new SingleValue(); float single = float.MaxValue; singleValue = single; Assert.True(single == singleValue); single = float.NaN; singleValue = SingleValue.FromSingle(single); Assert.Equal(single, singleValue.Value); Assert.Equal(single, SingleValue.ToSingle(singleValue)); // 15. StringValue StringValue stringValue = new StringValue(); string str = "Ethan"; stringValue = str; Assert.True(str == stringValue); str = "Yin"; stringValue = StringValue.FromString(str); Assert.Equal(str, stringValue.Value); Assert.Equal(str, stringValue.ToString()); Assert.Equal(str, StringValue.ToString(stringValue)); // 16. TrueFalseBlankValue TrueFalseBlankValue tfbValue = new TrueFalseBlankValue(); tfbValue = true; Assert.True(tfbValue); tfbValue = TrueFalseBlankValue.FromBoolean(false); Assert.False(tfbValue.Value); Assert.False(TrueFalseBlankValue.ToBoolean(tfbValue)); // 17. TrueFalseValue TrueFalseValue tfValue = new TrueFalseValue(); tfValue = true; Assert.True(tfValue); tfValue = TrueFalseValue.FromBoolean(false); Assert.False(tfValue.Value); Assert.False(TrueFalseValue.ToBoolean(tfValue)); // 18. UInt16Value UInt16Value uint16Value = new UInt16Value(); ushort uint16 = ushort.MaxValue; uint16Value = uint16; Assert.True(uint16 == uint16Value); uint16 = ushort.MinValue; uint16Value = UInt16Value.FromUInt16(uint16); Assert.Equal(uint16, uint16Value.Value); Assert.Equal(uint16, UInt16Value.ToUInt16(uint16Value)); // 19. UInt32Value UInt32Value uint32Value = new UInt32Value(); uint uint32 = uint.MaxValue; uint32Value = uint32; Assert.True(uint32 == uint32Value); uint32 = uint.MinValue; uint32Value = UInt32Value.FromUInt32(uint32); Assert.Equal(uint32, uint32Value.Value); Assert.Equal(uint32, UInt32Value.ToUInt32(uint32Value)); // 20. UInt64Value UInt64Value uint64Value = new UInt64Value(); ulong uint64 = ulong.MaxValue; uint64Value = uint64; Assert.True(uint64 == uint64Value); uint64 = ulong.MinValue; uint64Value = UInt64Value.FromUInt64(uint64); Assert.Equal(uint64, uint64Value.Value); Assert.Equal(uint64, UInt64Value.ToUInt64(uint64Value)); }
public override byte[] Render(global::Campus.Report.Base.Report report) { byte[] buffer; using (var outputMemoryStream = new MemoryStream()) { using (var document = SpreadsheetDocument.Create(outputMemoryStream, SpreadsheetDocumentType.Workbook, true)) { // Add a WorkbookPart to the document. var workbookpart = document.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. var worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook. var sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets()); // Append a new worksheet and associate it with the workbook. var sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = document.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" }; sheets.Append(sheet); // Add a WorkbookStylesPart to the WorkbookPart var stylesPart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>(); var shData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); stylesPart.Stylesheet = InitializeDefaultStyles(stylesPart.Stylesheet); uint styleId = 2; //обязательно 2 потому что уже додано два стиля по умолчанию с индексами 0 и 1; var styles = report.Select(o => o.Style).ToList(); //нельзя перенести в отдельный метод :( foreach (var s in styles) { if (s is TableStyle) { var style = s as TableStyle; var border = new DocumentFormat.OpenXml.Spreadsheet.Border(); var leftBorder = new DocumentFormat.OpenXml.Spreadsheet.LeftBorder(); var rightBorder = new DocumentFormat.OpenXml.Spreadsheet.RightBorder(); var topBorder = new DocumentFormat.OpenXml.Spreadsheet.TopBorder(); var bottomBorder = new DocumentFormat.OpenXml.Spreadsheet.BottomBorder(); leftBorder.Style = SetLineType(style.BorderLine); rightBorder.Style = SetLineType(style.BorderLine); topBorder.Style = SetLineType(style.BorderLine); bottomBorder.Style = SetLineType(style.BorderLine); leftBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = ToHexBinaryValue(style.BorderColor) }; rightBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = ToHexBinaryValue(style.BorderColor) }; topBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = ToHexBinaryValue(style.BorderColor) }; bottomBorder.Color = new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = ToHexBinaryValue(style.BorderColor) }; border.LeftBorder = leftBorder; border.RightBorder = rightBorder; border.TopBorder = topBorder; border.BottomBorder = bottomBorder; stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(border); var patternFill = new DocumentFormat.OpenXml.Spreadsheet.PatternFill { BackgroundColor = new DocumentFormat.OpenXml.Spreadsheet.BackgroundColor { Rgb = ToHexBinaryValue(style.Background) }, ForegroundColor = new DocumentFormat.OpenXml.Spreadsheet.ForegroundColor { Rgb = ToHexBinaryValue(style.Foreground) }, PatternType = SetPatternType(style.PatternType) }; var fill = new DocumentFormat.OpenXml.Spreadsheet.Fill { PatternFill = patternFill }; stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(fill); DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font() { FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName { Val = new StringValue { Value = s.FontName } }, FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize { Val = s.FontSize }, Color = new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = ToHexBinaryValue(s.FontColor) } }; stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font); } else { stylesPart.Stylesheet.Fills.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Fill>(new DocumentFormat.OpenXml.Spreadsheet.Fill()); stylesPart.Stylesheet.Borders.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Border>(new DocumentFormat.OpenXml.Spreadsheet.Border()); DocumentFormat.OpenXml.Spreadsheet.Font font; if (s != null) { font = new DocumentFormat.OpenXml.Spreadsheet.Font { FontName = new DocumentFormat.OpenXml.Spreadsheet.FontName { Val = new StringValue { Value = s.FontName } }, FontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize { Val = s.FontSize }, Color = new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = ToHexBinaryValue(s.FontColor) } }; foreach (var item in s.TextStyle) { switch (item) { case TextStyleType.Bold: font.Bold = new DocumentFormat.OpenXml.Spreadsheet.Bold { Val = true }; break; case TextStyleType.Italic: font.Italic = new DocumentFormat.OpenXml.Spreadsheet.Italic { Val = true }; break; case TextStyleType.Underline: font.Underline = new DocumentFormat.OpenXml.Spreadsheet.Underline { Val = DocumentFormat.OpenXml.Spreadsheet.UnderlineValues.Single }; break; case TextStyleType.Normal: break; } } } else { font = new DocumentFormat.OpenXml.Spreadsheet.Font(); } stylesPart.Stylesheet.Fonts.AppendChild <DocumentFormat.OpenXml.Spreadsheet.Font>(font); } if (s != null) { var cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat { BorderId = UInt32Value.ToUInt32(styleId), FillId = UInt32Value.ToUInt32(styleId), FontId = UInt32Value.ToUInt32(styleId) }; if (s.Aligment != null) { var align = new Alignment() { TextRotation = new UInt32Value(s.Aligment.Rotation), Horizontal = AlignmentMapper.MapHorizontalAligment(s.Aligment.HorizontalAligment), Vertical = AlignmentMapper.MapVerticalAligment(s.Aligment.VerticalAligment) }; cellFormat.Append(align); } stylesPart.Stylesheet.CellFormats.AppendChild <DocumentFormat.OpenXml.Spreadsheet.CellFormat>(cellFormat); } styleId = styleId + 1; } foreach (var x in report) { if (x is ComplexHeaderCell) { var element = x as ComplexHeaderCell; if (Render(element, styles, shData, worksheetPart, workbookpart)) { continue; } } if (x is TextElement) { var element = x as TextElement; if (Render(element, styles, shData, workbookpart)) { continue; } } if (x is TableElement) { var element = x as TableElement; if (Render(element, styles, shData, workbookpart)) { continue; } } if (x is ImageElement) { var element = x as ImageElement; if (Render(element, styles, shData, workbookpart)) { continue; } } } //var dateLine = shData.AppendChild(new Spreadsheet.Row()); //dateLine.AppendChild(new Spreadsheet.Cell() //{ // CellValue = new Spreadsheet.CellValue(_report.TimeStamp.Date.ToString()), // DataType = Spreadsheet.CellValues.String, // StyleIndex = 0 //}); workbookpart.Workbook.Save(); } buffer = new byte[outputMemoryStream.Position]; outputMemoryStream.Position = 0; outputMemoryStream.Read(buffer, 0, buffer.Length); } return(buffer); }