public static PageMargin PageMarginFromPredefined(PredefinedPageMargins marginType)
{
var margins = PageMargins(marginType);
return(new PageMargin
{
Top = Int32Value.FromInt32(Convert.ToInt32(margins.top.ToDxa())),
Left = UInt32Value.FromUInt32(Convert.ToUInt32(margins.left.ToDxa())),
Right = UInt32Value.FromUInt32(Convert.ToUInt32(margins.right.ToDxa())),
Bottom = Int32Value.FromInt32(Convert.ToInt32(margins.bottom.ToDxa())),
Header = UInt32Value.FromUInt32(Convert.ToUInt32(margins.header.ToDxa())),
Footer = UInt32Value.FromUInt32(Convert.ToUInt32(margins.footer.ToDxa()))
});
}
private void SetFont(double size, string fontName, int fontFamilyId, bool bold, bool italic, UnderlineValues underlineValue, string fontColor = null)
{
Font font = new Font();
font.FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(size)
};
font.FontName = new FontName()
{
Val = StringValue.FromString(fontName)
};
font.FontFamilyNumbering = new FontFamilyNumbering()
{
Val = Int32Value.FromInt32(fontFamilyId)
};
font.Bold = new Bold()
{
Val = BooleanValue.FromBoolean(bold)
};
font.Italic = new Italic()
{
Val = BooleanValue.FromBoolean(italic)
};
font.Underline = new Underline()
{
Val = new EnumValue <UnderlineValues>(underlineValue)
};
if (!string.IsNullOrEmpty(fontColor))
{
font.Color = new Color()
{
Rgb = fontColor
};
}
//font.FontScheme = new FontScheme() { Val = new EnumValue<FontSchemeValues>(fontScheme) };
//font.Color = new Color() { Theme = UInt32Value.FromUInt32(colorSchemeId) };
ActualFonts.Append(font);
SetFontsCount();
}
public static SectionProperties PageMargin(this SectionProperties properties,
int Left = 1134,
int Top = 568,
int Right = 850,
int Bottom = 1134,
int Header = 708,
int Footer = 708,
int Gutter = 0)
{
var margin = properties.Content().GetOrAppend <PageMargin>();
margin.Left = UInt32Value.FromUInt32((uint)Left);
margin.Top = Int32Value.FromInt32(Top);
margin.Right = UInt32Value.FromUInt32((uint)Right);
margin.Bottom = Int32Value.FromInt32(Bottom);
margin.Header = UInt32Value.FromUInt32((uint)Header);
margin.Footer = UInt32Value.FromUInt32((uint)Footer);
margin.Gutter = UInt32Value.FromUInt32((uint)Gutter);
return(properties);
}
public static Cell GerarCelula(int linha, int coluna, object conteudo)
{
var celula = new Cell
{
CellReference = $"{DeParaColuna(coluna)}{linha}",
};
if (conteudo != null)
{
if (conteudo is short || conteudo is int)
{
celula.CellValue = new CellValue(Int32Value.FromInt32(Convert.ToInt32(conteudo)));
celula.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else if (conteudo is DateTime)
{
celula.CellValue = new CellValue(Convert.ToDateTime(conteudo).ToOADate().ToString(cultura));
celula.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else if (conteudo is double || conteudo is decimal)
{
celula.CellValue = new CellValue(DoubleValue.FromDouble(Convert.ToDouble(conteudo, cultura)));
celula.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else
{
celula.CellValue = new CellValue(conteudo.ToString());
celula.DataType = new EnumValue <CellValues>(CellValues.String);
}
}
else
{
celula.CellValue = new CellValue();
}
return(celula);
}
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));
}
private void SetFileProperties(CustomFilePropertiesPart customProp)
{
customProp.Properties = new Properties();
int kvIndex = 2;
foreach (KeyValuePair <string, string> kvPair in DocProperties)
{
CustomDocumentProperty newProp = new CustomDocumentProperty()
{
Name = kvPair.Key, FormatId = "{D5CDD505-2E9C-101B-9397-08002B2CF9AE}", PropertyId = Int32Value.FromInt32(kvIndex), VTLPWSTR = new DocumentFormat.OpenXml.VariantTypes.VTLPWSTR(kvPair.Value)
};
customProp.Properties.Append(newProp);
kvIndex++;
}
}
private static void FillFirstTableRow(OpenXmlElement table, string relationshipId)
{
var tableRow = new TableRow
{
TextId = HexBinaryValue.FromString("77777777"),
ParagraphId = HexBinaryValue.FromString("080C4265"),
RsidTableRowAddition = HexBinaryValue.FromString("009B2C1D")
};
var rowProperties = new TableRowProperties();
rowProperties.AppendChild(new GridAfter
{
Val = Int32Value.FromInt32(2)
});
rowProperties.AppendChild(new WidthAfterTableRow
{
Width = StringValue.FromString("6375"),
Type = new EnumValue <TableWidthUnitValues> {
Value = TableWidthUnitValues.Dxa
}
});
tableRow.TableRowProperties = rowProperties;
var tableCell = new TableCell
{
TableCellProperties = new TableCellProperties
{
TableCellWidth = new TableCellWidth
{
Width = StringValue.FromString("800"),
Type = new EnumValue <TableWidthUnitValues> {
Value = TableWidthUnitValues.Dxa
}
},
TableCellBorders = new TableCellBorders
{
TopBorder = new TopBorder
{
Val = new EnumValue <BorderValues> {
Value = BorderValues.Single
},
Size = UInt32Value.FromUInt32(0),
Space = UInt32Value.FromUInt32(0),
Color = StringValue.FromString("FFFFFF")
},
LeftBorder = new LeftBorder
{
Val = new EnumValue <BorderValues> {
Value = BorderValues.Single
},
Size = UInt32Value.FromUInt32(0),
Space = UInt32Value.FromUInt32(0),
Color = StringValue.FromString("FFFFFF")
},
BottomBorder = new BottomBorder
{
Val = new EnumValue <BorderValues> {
Value = BorderValues.Single
},
Size = UInt32Value.FromUInt32(0),
Space = UInt32Value.FromUInt32(0),
Color = StringValue.FromString("FFFFFF")
},
RightBorder = new RightBorder
{
Val = new EnumValue <BorderValues> {
Value = BorderValues.Single
},
Size = UInt32Value.FromUInt32(0),
Space = UInt32Value.FromUInt32(0),
Color = StringValue.FromString("FFFFFF")
}
}
}
};
AddMainLogo(tableCell, relationshipId);
tableRow.AppendChild(tableCell);
table.AppendChild(tableRow);
}
private static void FillSecondTableRow(OpenXmlElement table)
{
var tableRow = new TableRow
{
TextId = HexBinaryValue.FromString("77777777"),
ParagraphId = HexBinaryValue.FromString("3125C09D"),
RsidTableRowAddition = HexBinaryValue.FromString("009B2C1D")
};
var rowProperties = new TableRowProperties();
rowProperties.AppendChild(new GridAfter
{
Val = Int32Value.FromInt32(2)
});
rowProperties.AppendChild(new WidthAfterTableRow
{
Width = StringValue.FromString("6375"),
Type = new EnumValue <TableWidthUnitValues> {
Value = TableWidthUnitValues.Dxa
}
});
tableRow.TableRowProperties = rowProperties;
var tableCell = new TableCell
{
TableCellProperties = new TableCellProperties
{
TableCellWidth = new TableCellWidth
{
Width = StringValue.FromString("800"),
Type = new EnumValue <TableWidthUnitValues> {
Value = TableWidthUnitValues.Dxa
}
},
TableCellBorders = new TableCellBorders
{
TopBorder = new TopBorder
{
Val = new EnumValue <BorderValues> {
Value = BorderValues.Single
},
Size = UInt32Value.FromUInt32(0),
Space = UInt32Value.FromUInt32(0),
Color = StringValue.FromString("FFFFFF")
},
LeftBorder = new LeftBorder
{
Val = new EnumValue <BorderValues> {
Value = BorderValues.Single
},
Size = UInt32Value.FromUInt32(0),
Space = UInt32Value.FromUInt32(0),
Color = StringValue.FromString("FFFFFF")
},
BottomBorder = new BottomBorder
{
Val = new EnumValue <BorderValues> {
Value = BorderValues.Single
},
Size = UInt32Value.FromUInt32(0),
Space = UInt32Value.FromUInt32(0),
Color = StringValue.FromString("FFFFFF")
},
RightBorder = new RightBorder
{
Val = new EnumValue <BorderValues> {
Value = BorderValues.Single
},
Size = UInt32Value.FromUInt32(0),
Space = UInt32Value.FromUInt32(0),
Color = StringValue.FromString("FFFFFF")
}
}
}
};
tableCell.AppendChild(new Paragraph
{
ParagraphId = HexBinaryValue.FromString("595BC873"),
TextId = HexBinaryValue.FromString("77777777"),
RsidParagraphAddition = HexBinaryValue.FromString("009B2C1D"),
RsidRunAdditionDefault = HexBinaryValue.FromString("009B2C1D")
});
tableRow.AppendChild(tableCell);
table.AppendChild(tableRow);
}
/// <summary>
/// Deletes the supplied sheet instance from the workbook part
/// </summary>
/// <param name="workbookPart">The workbook part to delete the sheet from</param>
/// <param name="sheet">The sheet to delete</param>
/// <exception cref="ArgumentNullException">
/// workbookPart
/// or
/// sheet
/// </exception>
public static void DeleteSheet(this WorkbookPart workbookPart, DocumentFormat.OpenXml.Spreadsheet.Sheet sheet)
{
if (workbookPart == null)
{
throw new ArgumentNullException("workbookPart");
}
if (sheet == null)
{
throw new ArgumentNullException("sheet");
}
// get the id of the sheet for deletion
Int32Value sheetId = Int32Value.FromInt32((int)sheet.SheetId.Value);
// Remove the sheet reference from the workbook.
WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
SheetViews views = worksheetPart.Worksheet.GetFirstChild <SheetViews>();
if (views != null)
{
views.Remove();
worksheetPart.Worksheet.Save();
}
sheet.Remove();
// Delete the worksheet part.
workbookPart.DeletePart(worksheetPart);
// Get the CalculationChainPart
// Note: An instance of this part type contains an ordered set of references to all cells in all worksheets in the
// workbook whose value is calculated from any formula
CalculationChainPart calChainPart = workbookPart.CalculationChainPart;
if (calChainPart != null)
{
List <CalculationCell> forRemoval = new List <CalculationCell>();
var calChainEntries = calChainPart.CalculationChain.Descendants <CalculationCell>().ToList();
foreach (CalculationCell item in calChainEntries)
{
if (item.SheetId == null)
{
item.Remove();
}
else if (item.SheetId.HasValue && item.SheetId.Value.Equals(sheetId))
{
item.Remove();
}
}
if (calChainPart.CalculationChain.Count() == 0)
{
workbookPart.DeletePart(calChainPart);
}
}
workbookPart.Workbook.Save();
}
