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);
}
