public CustomStylesheet() { Fonts fts = new Fonts(); Font ft = new Font(); FontName ftn = new FontName(); ftn.Val = StringValue.FromString("Calibri"); FontSize ftsz = new FontSize(); ftsz.Val = DoubleValue.FromDouble(11); ft.FontName = ftn; ft.FontSize = ftsz; fts.Append(ft); ft = new Font(); ftn = new FontName(); ftn.Val = StringValue.FromString("Palatino Linotype"); ftsz = new FontSize(); ftsz.Val = DoubleValue.FromDouble(18); ft.FontName = ftn; ft.FontSize = ftsz; fts.Append(ft); fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count); Fills fills = new Fills(); Fill fill; PatternFill patternFill; fill = new Fill(); patternFill = new PatternFill(); patternFill.PatternType = PatternValues.None; fill.PatternFill = patternFill; fills.Append(fill); fill = new Fill(); patternFill = new PatternFill(); patternFill.PatternType = PatternValues.Gray125; fill.PatternFill = patternFill; fills.Append(fill); fill = new Fill(); patternFill = new PatternFill(); patternFill.PatternType = PatternValues.Solid; patternFill.ForegroundColor = new ForegroundColor(); patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString("00ff9728"); patternFill.BackgroundColor = new BackgroundColor(); patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb; fill.PatternFill = patternFill; fills.Append(fill); fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count); Borders borders = new Borders(); Border border = new Border(); border.LeftBorder = new LeftBorder(); border.RightBorder = new RightBorder(); border.TopBorder = new TopBorder(); border.BottomBorder = new BottomBorder(); border.DiagonalBorder = new DiagonalBorder(); borders.Append(border); //Boarder Index 1 border = new Border(); border.LeftBorder = new LeftBorder(); border.LeftBorder.Style = BorderStyleValues.Thin; border.RightBorder = new RightBorder(); border.RightBorder.Style = BorderStyleValues.Thin; border.TopBorder = new TopBorder(); border.TopBorder.Style = BorderStyleValues.Thin; border.BottomBorder = new BottomBorder(); border.BottomBorder.Style = BorderStyleValues.Thin; border.DiagonalBorder = new DiagonalBorder(); borders.Append(border); //Boarder Index 2 border = new Border(); border.LeftBorder = new LeftBorder(); border.RightBorder = new RightBorder(); border.TopBorder = new TopBorder(); border.TopBorder.Style = BorderStyleValues.Thin; border.BottomBorder = new BottomBorder(); border.BottomBorder.Style = BorderStyleValues.Thin; border.DiagonalBorder = new DiagonalBorder(); borders.Append(border); borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count); CellStyleFormats csfs = new CellStyleFormats(); CellFormat cf = new CellFormat(); cf.NumberFormatId = 0; cf.FontId = 0; cf.FillId = 0; cf.BorderId = 0; csfs.Append(cf); csfs.Count = UInt32Value.FromUInt32((uint)csfs.ChildElements.Count); uint iExcelIndex = 164; NumberingFormats nfs = new NumberingFormats(); CellFormats cfs = new CellFormats(); cf = new CellFormat(); cf.NumberFormatId = 0; cf.FontId = 0; cf.FillId = 0; cf.BorderId = 0; cf.FormatId = 0; cfs.Append(cf); NumberingFormat nfDateTime = new NumberingFormat(); nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++); nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss"); nfs.Append(nfDateTime); NumberingFormat nf4decimal = new NumberingFormat(); nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++); nf4decimal.FormatCode = StringValue.FromString("#,##0.0000"); nfs.Append(nf4decimal); // #,##0.00 is also Excel style index 4 NumberingFormat nf2decimal = new NumberingFormat(); nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++); nf2decimal.FormatCode = StringValue.FromString("#,##0.00"); nfs.Append(nf2decimal); // @ is also Excel style index 49 NumberingFormat nfForcedText = new NumberingFormat(); nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++); nfForcedText.FormatCode = StringValue.FromString("@"); nfs.Append(nfForcedText); // index 1 // Format dd/mm/yyyy cf = new CellFormat(); cf.NumberFormatId = 14; cf.FontId = 0; cf.FillId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 2 // Format #,##0.00 cf = new CellFormat(); cf.NumberFormatId = 4; cf.FontId = 0; cf.FillId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 3 cf = new CellFormat(); cf.NumberFormatId = nfDateTime.NumberFormatId; cf.FontId = 0; cf.FillId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 4 cf = new CellFormat(); cf.NumberFormatId = nf4decimal.NumberFormatId; cf.FontId = 0; cf.FillId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 5 cf = new CellFormat(); cf.NumberFormatId = nf2decimal.NumberFormatId; cf.FontId = 0; cf.FillId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 6 cf = new CellFormat(); cf.NumberFormatId = nfForcedText.NumberFormatId; cf.FontId = 0; cf.FillId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 7 // Header text cf = new CellFormat(); cf.NumberFormatId = nfForcedText.NumberFormatId; cf.FontId = 1; cf.FillId = 0; cf.BorderId = 0; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 8 // column text cf = new CellFormat(); cf.NumberFormatId = nfForcedText.NumberFormatId; cf.FontId = 0; cf.FillId = 0; cf.BorderId = 1; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 9 // coloured 2 decimal text cf = new CellFormat(); cf.NumberFormatId = nf2decimal.NumberFormatId; cf.FontId = 0; cf.FillId = 2; cf.BorderId = 2; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); // index 10 // coloured column text cf = new CellFormat(); cf.NumberFormatId = nfForcedText.NumberFormatId; cf.FontId = 0; cf.FillId = 2; cf.BorderId = 2; cf.FormatId = 0; cf.ApplyNumberFormat = BooleanValue.FromBoolean(true); cfs.Append(cf); nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count); cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count); this.Append(nfs); this.Append(fts); this.Append(fills); this.Append(borders); this.Append(csfs); this.Append(cfs); CellStyles css = new CellStyles(); CellStyle cs = new CellStyle(); cs.Name = StringValue.FromString("Normal"); cs.FormatId = 0; cs.BuiltinId = 0; css.Append(cs); css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count); this.Append(css); DifferentialFormats dfs = new DifferentialFormats(); dfs.Count = 0; this.Append(dfs); TableStyles tss = new TableStyles(); tss.Count = 0; tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9"); tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16"); this.Append(tss); }
public void OpenXmlSimpleTypeConverterTest() { // 1. Base64BinaryValue Base64BinaryValue base64 = new Base64BinaryValue(); base64 = "AA3322"; Assert.True("AA3322" == base64); 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(); Int16 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(); Int32 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(); Int64 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(); Single single = Single.MaxValue; singleValue = single; Assert.True(single == singleValue); single = Single.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(); UInt16 uint16 = UInt16.MaxValue; uint16Value = uint16; Assert.True(uint16 == uint16Value); uint16 = UInt16.MinValue; uint16Value = UInt16Value.FromUInt16(uint16); Assert.Equal(uint16, uint16Value.Value); Assert.Equal(uint16, UInt16Value.ToUInt16(uint16Value)); // 19. UInt32Value UInt32Value uint32Value = new UInt32Value(); UInt32 uint32 = UInt32.MaxValue; uint32Value = uint32; Assert.True(uint32 == uint32Value); uint32 = UInt32.MinValue; uint32Value = UInt32Value.FromUInt32(uint32); Assert.Equal(uint32, uint32Value.Value); Assert.Equal(uint32, UInt32Value.ToUInt32(uint32Value)); // 20. UInt64Value UInt64Value uint64Value = new UInt64Value(); UInt64 uint64 = UInt64.MaxValue; uint64Value = uint64; Assert.True(uint64 == uint64Value); uint64 = UInt64.MinValue; uint64Value = UInt64Value.FromUInt64(uint64); Assert.Equal(uint64, uint64Value.Value); Assert.Equal(uint64, UInt64Value.ToUInt64(uint64Value)); }
public NewExcel() { //创建默认字体样式1 Arial 24 var fonts = new Fonts(); var font = new DocumentFormat.OpenXml.Spreadsheet.Font(); var fontName = new FontName { Val = StringValue.FromString("Arial") }; var fontSize = new FontSize { Val = DoubleValue.FromDouble(11) }; font.FontName = fontName; font.FontSize = fontSize; fonts.Append(font); fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count); //创建填充样式1 var fills = new Fills(); var fill = new Fill(); var patternFill = new PatternFill { PatternType = PatternValues.None }; fill.PatternFill = patternFill; fills.Append(fill); fill = new Fill(); patternFill = new PatternFill { PatternType = PatternValues.Gray0625 }; fill.PatternFill = patternFill; fills.Append(fill); fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count); //创建线条样式1 var borders = new Borders(); var border = new Border { LeftBorder = new LeftBorder(), RightBorder = new RightBorder(), TopBorder = new TopBorder(), BottomBorder = new BottomBorder(), DiagonalBorder = new DiagonalBorder() }; borders.Append(border); borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count); //创建单元格样式 var cellStyleFormats = new CellStyleFormats(); var cellFormat = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 }; cellStyleFormats.Append(cellFormat); cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count); var cellFormats = new CellFormats(); cellFormat = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 }; cellFormats.Append(cellFormat); cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count); this.Append(fonts); this.Append(fills); this.Append(borders); this.Append(cellStyleFormats); this.Append(cellFormats); var css = new CellStyles(); var cs = new CellStyle { Name = StringValue.FromString("Normal"), FormatId = 0, BuiltinId = 0 }; css.Append(cs); css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count); this.Append(css); var dfs = new DifferentialFormats { Count = 0 }; this.Append(dfs); var tss = new TableStyles { Count = 0, DefaultTableStyle = StringValue.FromString("TableStyleMedium9"), DefaultPivotStyle = StringValue.FromString("PivotStyleLight16") }; this.Append(tss); }