static Stylesheet CreateStylesheet()
{
Stylesheet ss = new Stylesheet();
#region fts (fonts)
Fonts fts = new Fonts();
var ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontName ftn = new FontName();
ftn.Val = StringValue.FromString("Arial");
FontSize ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(11);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
ftn = new FontName();
ftn.Val = StringValue.FromString("Verdana");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(18);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
ft.Append(new Bold());
ftn = new FontName();
ftn.Val = StringValue.FromString("Arial");
ftsz = new FontSize();
ftsz.Val = DoubleValue.FromDouble(11);
ft.FontName = ftn;
ft.FontSize = ftsz;
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
#endregion
#region fills
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("00efffd9");
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
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("00f8e8d6");
patternFill.BackgroundColor = new BackgroundColor();
patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
#endregion
#region borders
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);
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);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
#endregion
#region csfs (CellStyleFormats)
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);
#endregion
uint iExcelIndex = 164;
var nfs = new DocumentFormat.OpenXml.Spreadsheet.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);
var nfDateTime = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
nfs.Append(nfDateTime);
var nfDate = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
nfDate.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfDate.FormatCode = StringValue.FromString("dd/mm/yyyy");
nfs.Append(nfDate);
var nf4decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
var nf2decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
nfs.Append(nf2decimal);
// @ is also Excel style index 49
var nfForcedText = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfForcedText.FormatCode = StringValue.FromString("@");
nfs.Append(nfForcedText);
// #,##0.00 is also Excel style index 4
var nf0decimal = new DocumentFormat.OpenXml.Spreadsheet.NumberingFormat();
nf0decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf0decimal.FormatCode = StringValue.FromString("#,##0");
nfs.Append(nf0decimal);
// index 1
cf = new CellFormat();
cf.NumberFormatId = nfDate.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 2
cf = new CellFormat();
cf.NumberFormatId = nf4decimal.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 3
cf = new CellFormat();
cf.NumberFormatId = nf0decimal.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 4
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 0; //NO Fill
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 5
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 1; //Verdana 18
cf.FillId = 0; //NO Fill
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 6
// column text
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 2; //Arial 11, Bold
cf.FillId = 0; //NO Fill
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 7
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 3; //Light Orange
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 = 2; //Arial 11, Bold
cf.FillId = 2; //Light Green
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
// index 9
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 0; //NO Fill
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cf.Append(new Alignment()
{
WrapText = true
});
cfs.Append(cf);
// index 10
cf = new CellFormat();
cf.NumberFormatId = nfForcedText.NumberFormatId;
cf.FontId = 0; //Arial 11
cf.FillId = 3; //Light Orange
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cf.Append(new Alignment()
{
WrapText = true
});
cfs.Append(cf);
nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.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);
ss.Append(css);
DifferentialFormats dfs = new DifferentialFormats();
dfs.Count = 0;
ss.Append(dfs);
TableStyles tss = new TableStyles();
tss.Count = 0;
tss.DefaultTableStyle = StringValue.FromString("TableStyleMedium9");
tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
ss.Append(tss);
return(ss);
}
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 byte[] GetSpreadsheet(int implementationGuideId, int maxValueSetMembers)
{
try
{
TDBImplementationGuide ig = tdb.ImplementationGuides.SingleOrDefault(y => y.Id == implementationGuideId);
if (ig == null)
{
throw new Exception("Could not find ImplementationGuide specified.");
}
var igTypePlugin = ig.ImplementationGuideType.GetPlugin();
List <ImplementationGuideValueSet> valueSets = ig.GetValueSets(tdb);
using (MemoryStream ms = new MemoryStream())
{
SpreadsheetDocument spreadsheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, true);
WorkbookPart workbookpart = spreadsheet.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
workbookpart.Workbook.AppendChild <Sheets>(new Sheets());
Worksheet sheet1 = CreateWorksheet(workbookpart.Workbook, "Affected Value Sets");
SheetData sheet1Data = sheet1.GetFirstChild <SheetData>();
Worksheet sheet2 = CreateWorksheet(workbookpart.Workbook, "Value Set Members");
SheetData sheet2Data = sheet2.GetFirstChild <SheetData>();
int sheet1Count = 2;
int sheet2Count = 2;
// Sheet 1 column widths
Columns sheet1Cols = new Columns(
new Column()
{
Min = 1,
Max = 1,
Width = 56,
BestFit = BooleanValue.FromBoolean(true),
CustomWidth = BooleanValue.FromBoolean(true)
},
new Column()
{
Min = 2,
Max = 2,
Width = 38,
BestFit = BooleanValue.FromBoolean(true),
CustomWidth = BooleanValue.FromBoolean(true)
});
sheet1.InsertBefore <Columns>(sheet1Cols, sheet1Data);
// Sheet 2 column widths
Columns sheet2Cols = new Columns(
new Column()
{
Min = 1,
Max = 1,
Width = 32,
BestFit = BooleanValue.FromBoolean(true),
CustomWidth = BooleanValue.FromBoolean(true)
},
new Column()
{
Min = 2,
Max = 2,
Width = 51,
BestFit = BooleanValue.FromBoolean(true),
CustomWidth = BooleanValue.FromBoolean(true)
},
new Column()
{
Min = 3,
Max = 3,
Width = 26,
BestFit = BooleanValue.FromBoolean(true),
CustomWidth = BooleanValue.FromBoolean(true)
},
new Column()
{
Min = 4,
Max = 4,
Width = 39,
BestFit = BooleanValue.FromBoolean(true),
CustomWidth = BooleanValue.FromBoolean(true)
},
new Column()
{
Min = 5,
Max = 5,
Width = 23,
BestFit = BooleanValue.FromBoolean(true),
CustomWidth = BooleanValue.FromBoolean(true)
});
sheet2.InsertBefore <Columns>(sheet2Cols, sheet2Data);
// Add headers
sheet1Data.AppendChild(
new Row(
new Cell(
new CellValue("Value Set Name"))
{
CellReference = "A1",
DataType = CellValues.String
},
new Cell(
new CellValue("Value Set OID"))
{
CellReference = "B1",
DataType = CellValues.String
})
{
RowIndex = 1
});
sheet2Data.AppendChild(
new Row(
new Cell(
new CellValue("Value Set OID"))
{
CellReference = "A1",
DataType = CellValues.String
},
new Cell(
new CellValue("Value Set Name"))
{
CellReference = "B1",
DataType = CellValues.String
},
new Cell(
new CellValue("Code"))
{
CellReference = "C1",
DataType = CellValues.String
},
new Cell(
new CellValue("Display Name"))
{
CellReference = "D1",
DataType = CellValues.String
},
new Cell(
new CellValue("Code System Name"))
{
CellReference = "E1",
DataType = CellValues.String
})
{
RowIndex = 1
});
for (int x = 0; x < valueSets.Count; x++)
{
var cValueSet = valueSets[x];
string summaryXml = string.Format(
"<row r=\"{0}\" xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" +
" <c r=\"A{0}\" t=\"str\"><v>{1}</v></c>" +
" <c r=\"B{0}\" t=\"str\"><v>{2}</v></c>" +
"</row>",
sheet1Count++,
XmlEncodeText(cValueSet.ValueSet.Name),
XmlEncodeText(cValueSet.ValueSet.GetIdentifier(igTypePlugin)));
Row newSummaryRow = new Row(summaryXml);
sheet1Data.AppendChild(newSummaryRow);
List <ValueSetMember> members = cValueSet.ValueSet.GetActiveMembers(cValueSet.BindingDate);
for (int i = 0; i < members.Count && (maxValueSetMembers == 0 || i < maxValueSetMembers); i++)
{
var cMember = members[i];
string memberXml = string.Format(
"<row r=\"{0}\" xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" +
" <c r=\"A{0}\" t=\"str\"><v>{1}</v></c>" +
" <c r=\"B{0}\" t=\"str\"><v>{2}</v></c>" +
" <c r=\"C{0}\" t=\"str\"><v>{3}</v></c>" +
" <c r=\"D{0}\" t=\"str\"><v>{4}</v></c>" +
" <c r=\"E{0}\" t=\"str\"><v>{5}</v></c>" +
"</row>",
sheet2Count++,
XmlEncodeText(cValueSet.ValueSet.GetIdentifier(igTypePlugin)),
XmlEncodeText(cValueSet.ValueSet.Name),
XmlEncodeText(cMember.Code),
XmlEncodeText(cMember.DisplayName),
XmlEncodeText(cMember.CodeSystem.Name));
Row newMemberRow = new Row(memberXml);
sheet2Data.AppendChild(newMemberRow);
}
}
workbookpart.Workbook.Save();
spreadsheet.Close();
ms.Position = 0;
byte[] buffer = new byte[ms.Length];
ms.Read(buffer, 0, (int)ms.Length);
return(buffer);
}
}
catch (Exception ex)
{
Log.For(this).Critical("Error occurred while generating spreadsheet for IG vocabulary", ex);
throw;
}
}
public CustomStylesheet()
{
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.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 DocumentFormat.OpenXml.Spreadsheet.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 Stylesheet CreateStylesheet()
{
Stylesheet ss = new Stylesheet();
Fonts fts = new Fonts();
Font ft = new Font()
{
FontName = new FontName()
{
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(8)
}
};
fts.Append(ft);
ft = new Font()
{
FontName = new FontName()
{
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(18)
}
};
fts.Append(ft);
ft = new Font()
{
FontName = new FontName()
{
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(9)
},
Bold = new Bold()
};//new Bold() { Val = new BooleanValue(true) }
fts.Append(ft);
ft = new Font()
{
FontName = new FontName()
{
Val = StringValue.FromString("Arial")
},
FontSize = new FontSize()
{
Val = DoubleValue.FromDouble(14)
}
};
fts.Append(ft);
fts.Count = UInt32Value.FromUInt32((uint)fts.ChildElements.Count);
Fills fills = new Fills();
Fill fill = new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.None
}
};
fills.Append(fill);
fill = new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.Gray125
}
};
fills.Append(fill);
fill = new Fill()
{
PatternFill = new PatternFill()
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor()
{
Rgb = HexBinaryValue.FromString("00d3d3d3")
},
BackgroundColor = new BackgroundColor()
{
Rgb = HexBinaryValue.FromString("00d3d3d3")
}
}
};
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
Borders borders = new Borders();
Border border = new Border()
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
border = new Border()
{
LeftBorder = new LeftBorder()
{
Style = BorderStyleValues.Thin
},
RightBorder = new RightBorder()
{
Style = BorderStyleValues.Thin
},
TopBorder = new TopBorder()
{
Style = BorderStyleValues.Thin
},
BottomBorder = new BottomBorder()
{
Style = BorderStyleValues.Thin
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
border = new Border()
{
LeftBorder = new LeftBorder()
{
Style = BorderStyleValues.Thick
},
RightBorder = new RightBorder()
{
Style = BorderStyleValues.Thick
},
TopBorder = new TopBorder()
{
Style = BorderStyleValues.Thick
},
BottomBorder = new BottomBorder()
{
Style = BorderStyleValues.Thick
},
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
border = new Border()
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder()
{
Style = BorderStyleValues.Double
},
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
CellStyleFormats csfs = new CellStyleFormats();
CellFormat cf = new CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, 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()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
};
cfs.Append(cf);
NumberingFormat nfDateTime = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
};
nfs.Append(nfDateTime);
NumberingFormat nf4decimal = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00")
};
nfs.Append(nf4decimal);
// #,##0.00 is also Excel style index 4
NumberingFormat nf2decimal = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00")
};
nfs.Append(nf2decimal);
// @ is also Excel style index 49
NumberingFormat nfForcedText = new NumberingFormat()
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("@")
};
nfs.Append(nfForcedText);
// index 1
cf = new CellFormat()
{
NumberFormatId = nfDateTime.NumberFormatId, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 2
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 3
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 0, FillId = 0, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 4
cf = new CellFormat()
{
NumberFormatId = nfForcedText.NumberFormatId, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 5 Header text
cf = new CellFormat()
{
NumberFormatId = nfForcedText.NumberFormatId, FontId = 1, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 6 column text
cf = new CellFormat()
{
NumberFormatId = nfForcedText.NumberFormatId, FontId = 0, FillId = 0, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cf.Alignment = new Alignment()
{
Vertical = new EnumValue <VerticalAlignmentValues>(VerticalAlignmentValues.Center)
};
cfs.Append(cf);
// index 7 coloured 2 decimal text
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 0, FillId = 2, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 8 coloured column text
cf = new CellFormat()
{
NumberFormatId = nfForcedText.NumberFormatId, FontId = 0, FillId = 2, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 9
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 2, FillId = 0, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 10
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 2, FillId = 2, BorderId = 1, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cf.Alignment = new Alignment()
{
Vertical = VerticalAlignmentValues.Center, Horizontal = HorizontalAlignmentValues.Center
};
cfs.Append(cf);
// index 11
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 2, FillId = 0, BorderId = 3, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 12
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 2, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
// index 13
cf = new CellFormat()
{
NumberFormatId = nf2decimal.NumberFormatId, FontId = 3, FillId = 0, BorderId = 0, FormatId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cfs.Append(cf);
nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
ss.Append(nfs);
ss.Append(fts);
ss.Append(fills);
ss.Append(borders);
ss.Append(csfs);
ss.Append(cfs);
var css = new CellStyles();
DocumentFormat.OpenXml.Spreadsheet.CellStyle cs = new DocumentFormat.OpenXml.Spreadsheet.CellStyle()
{
Name = StringValue.FromString("Normal"), FormatId = 0, BuiltinId = 0
};
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
ss.Append(css);
var dfs = new DifferentialFormats {
Count = 0
};
ss.Append(dfs);
TableStyles tss = new TableStyles()
{
Count = 0, DefaultTableStyle = StringValue.FromString("TableStyleMedium9"), DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
};
ss.Append(tss);
return(ss);
}
//var numberCell = new Cell {
// DataType = CellValues.Number,
// CellReference = header + index,
// CellValue = new CellValue(text),
// StyleIndex = 3
//};
public void Execute()
{
List <Person> personen = GetData();
String pathToTemp = @"..\..\testFolder";
if (!Directory.Exists(pathToTemp))
{
Directory.CreateDirectory(pathToTemp);
}
String tempFile = pathToTemp + "\\" + DateTime.Now.Ticks / TimeSpan.TicksPerMillisecond + ".xlsx";
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(tempFile, SpreadsheetDocumentType.Workbook)) {
// create the workbook
var dateFormat = new NumberingFormat()
{
NumberFormatId = (UInt32Value)0,
FormatCode = StringValue.FromString("dd.MM.yyyy")
};
WorkbookPart part = spreadSheet.AddWorkbookPart();
part.Workbook = new Workbook();
part.AddNewPart <WorksheetPart>();
part.WorksheetParts.First().Worksheet = new Worksheet();
WorkbookStylesPart sp = spreadSheet.WorkbookPart.AddNewPart <WorkbookStylesPart>();
sp.Stylesheet = new Stylesheet();
sp.Stylesheet.NumberingFormats = new NumberingFormats();
sp.Stylesheet.NumberingFormats.Append(dateFormat);
CellFormat cellFormat = new CellFormat();
cellFormat.NumberFormatId = dateFormat.NumberFormatId;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
sp.Stylesheet.CellFormats = new CellFormats();
sp.Stylesheet.CellFormats.AppendChild <CellFormat>(cellFormat);
sp.Stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint)sp.Stylesheet.CellFormats.ChildElements.Count);
sp.Stylesheet.Save();
Sheets sheets = spreadSheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// create sheet data
foreach (Person p in personen)
{
WorksheetPart tabPart = part.AddNewPart <WorksheetPart>();
Worksheet workSheet1 = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheet sheet1 = new Sheet()
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(tabPart),
SheetId = 1,
Name = p.Name
};
sheets.Append(sheet1);
Row r = new Row();
r.AppendChild(new Cell()
{
CellValue = new CellValue(p.Name), DataType = CellValues.String
});
r.AppendChild(new Cell()
{
CellValue = new CellValue(p.BirthDay.ToOADate().ToString()), StyleIndex = 0
});
r.AppendChild(new Cell()
{
CellValue = new CellValue(p.HeightInCm.ToString(CultureInfo.InvariantCulture)), DataType = CellValues.Number
});
r.AppendChild(new Cell()
{
CellValue = new CellValue(p.Weight.ToString(CultureInfo.InvariantCulture)), DataType = CellValues.Number
});
sheetData1.AppendChild(r);
workSheet1.AppendChild(sheetData1);
tabPart.Worksheet = workSheet1;
}
part.Workbook.Save();
}
Process.Start(@tempFile);
Environment.Exit(0);
}
private static void AddMainLogo(OpenXmlElement elementToAppend, string relationshipId)
{
var element = new Drawing
{
Inline = new Inline
{
DistanceFromTop = UInt32Value.FromUInt32(0),
DistanceFromBottom = UInt32Value.FromUInt32(0),
DistanceFromLeft = UInt32Value.FromUInt32(0),
DistanceFromRight = UInt32Value.FromUInt32(0),
AnchorId = HexBinaryValue.FromString("29B39BAA"),
EditId = HexBinaryValue.FromString("5481AAF3"),
Extent = new Extent
{
Cx = Int64Value.FromInt64(1257300),
Cy = Int64Value.FromInt64(1057275)
},
EffectExtent = new EffectExtent
{
LeftEdge = Int64Value.FromInt64(0),
TopEdge = Int64Value.FromInt64(0),
RightEdge = Int64Value.FromInt64(0),
BottomEdge = Int64Value.FromInt64(0)
},
DocProperties = new DocProperties
{
Id = UInt32Value.FromUInt32(1),
Name = "Picture 1"
},
NonVisualGraphicFrameDrawingProperties = new NonVisualGraphicFrameDrawingProperties
{
GraphicFrameLocks = new GraphicFrameLocks
{
NoChangeAspect = BooleanValue.FromBoolean(true)
}
},
Graphic = new Graphic
{
GraphicData = new GraphicData(new DocumentFormat.OpenXml.Drawing.Pictures.Picture
{
NonVisualPictureProperties = new DocumentFormat.OpenXml.Drawing.Pictures.NonVisualPictureProperties
{
NonVisualDrawingProperties = new DocumentFormat.OpenXml.Drawing.Pictures.NonVisualDrawingProperties
{
Id = UInt32Value.FromUInt32(0),
Name = "Picture 1"
},
NonVisualPictureDrawingProperties = new DocumentFormat.OpenXml.Drawing.Pictures.NonVisualPictureDrawingProperties
{
PictureLocks = new PictureLocks
{
NoChangeAspect = BooleanValue.FromBoolean(true),
NoChangeArrowheads = BooleanValue.FromBoolean(true)
}
}
},
BlipFill = new DocumentFormat.OpenXml.Drawing.Pictures.BlipFill(new BlipExtensionList(new BlipExtension {
Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}"
}),
new Stretch(new FillRectangle()))
{
Blip = new Blip
{
Embed = relationshipId,
},
SourceRectangle = new SourceRectangle(),
},
ShapeProperties = new DocumentFormat.OpenXml.Drawing.Pictures.ShapeProperties(new PresetGeometry {
Preset = new EnumValue <ShapeTypeValues> {
Value = ShapeTypeValues.Rectangle
}, AdjustValueList = new AdjustValueList()
},
new NoFill(),
new DocumentFormat.OpenXml.Drawing.Outline(new NoFill()))
{
BlackWhiteMode = new EnumValue <BlackWhiteModeValues> {
Value = BlackWhiteModeValues.Auto
},
Transform2D = new Transform2D
{
Offset = new Offset
{
X = Int64Value.FromInt64(0),
Y = Int64Value.FromInt64(0)
},
Extents = new Extents
{
Cx = Int64Value.FromInt64(1257300),
Cy = Int64Value.FromInt64(1057275)
}
}
}
})
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/picture"
}
}
}
};
// Append the reference to body, the element should be in a Run.
elementToAppend.AppendChild(new Paragraph(new Run(element)
{
RunProperties = new DocumentFormat.OpenXml.Wordprocessing.RunProperties
{
NoProof = new NoProof()
}
}));
}
private static CellFormats CreateCellFormats(NumberingFormats nfs)
{
CellFormats cfs = new CellFormats();
CellFormat cf = new CellFormat();
cf = new CellFormat();
cf.NumberFormatId = 0;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cfs.Append(cf);
//index 1
cf = new CellFormat();
cf.NumberFormatId = 164 + 1;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
//index 2
cf = new CellFormat();
cf.NumberFormatId = 164 + 2;
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 = 164 + 3;
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 = 164 + 4;
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 = 164 + 5;
cf.FontId = 1;
cf.FillId = 0;
cf.BorderId = 0;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
//index 6
cf = new CellFormat();
cf.NumberFormatId = 164 + 6;
cf.FontId = 0;
cf.FillId = 0;
cf.BorderId = 1;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
//index 7
cf = new CellFormat();
cf.NumberFormatId = 164 + 7;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 1;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
//index 8
cf = new CellFormat();
cf.NumberFormatId = 164 + 8;
cf.FontId = 0;
cf.FillId = 2;
cf.BorderId = 1;
cf.FormatId = 0;
cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cfs.Append(cf);
cfs.Count = UInt32Value.FromUInt32((uint)cfs.ChildElements.Count);
return(cfs);
}
public static byte[] ExportCurrencies()
{
using (MemoryStream mem = new MemoryStream())
{
using (var workbook = SpreadsheetDocument.Create(mem, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new Workbook();
workbook.WorkbookPart.AddNewPart <WorkbookStylesPart>().Stylesheet = createStylesheetCurrencies();
workbook.WorkbookPart.Workbook.Sheets = new Sheets();
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
SpreadsheetPrinterSettingsPart spreadsheetPrinterSettingsPart1 = sheetPart.AddNewPart <SpreadsheetPrinterSettingsPart>();
sheetPart.Worksheet = new Worksheet();
SheetProperties sp = new SheetProperties(new PageSetupProperties());
sheetPart.Worksheet.Append(sp);
sheetPart.Worksheet.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(true);
Columns cols = new Columns();
UInt32Value ind = 1U;
cols.AppendChild(new Column()
{
Width = 15, CustomWidth = true, Min = ind, Max = ind
});
ind++;
cols.AppendChild(new Column()
{
Width = 15, CustomWidth = true, Min = ind, Max = ind
});
ind++;
cols.AppendChild(new Column()
{
Width = 30, CustomWidth = true, Min = ind, Max = ind
});
ind++;
cols.AppendChild(new Column()
{
Width = 20, CustomWidth = true, Min = ind, Max = ind
});
ind++;
cols.AppendChild(new Column()
{
Width = 20, CustomWidth = true, Min = ind, Max = ind
});
ind++;
cols.AppendChild(new Column()
{
Width = 20, CustomWidth = true, Min = ind, Max = ind
});
ind++;
cols.AppendChild(new Column()
{
Width = 20, CustomWidth = true, Min = ind, Max = ind
});
ind++;
cols.AppendChild(new Column()
{
Width = 20, CustomWidth = true, Min = ind, Max = ind
});
ind++;
cols.AppendChild(new Column()
{
Width = 20, CustomWidth = true, Min = ind, Max = ind
});
ind++;
cols.AppendChild(new Column()
{
Width = 20, CustomWidth = true, Min = ind, Max = ind
});
ind++;
cols.AppendChild(new Column()
{
Width = 20, CustomWidth = true, Min = ind, Max = ind
});
sheetPart.Worksheet.Append(cols);
var sheetData = new SheetData();
sheetPart.Worksheet.Append(sheetData);
MergeCells mergeCells1 = new MergeCells();
mergeCells1.AppendChild(new MergeCell()
{
Reference = "A1:A2"
});
mergeCells1.AppendChild(new MergeCell()
{
Reference = "B1:B2"
});
mergeCells1.AppendChild(new MergeCell()
{
Reference = "D1:E1"
});
mergeCells1.AppendChild(new MergeCell()
{
Reference = "F1:G1"
});
mergeCells1.AppendChild(new MergeCell()
{
Reference = "H1:I1"
});
mergeCells1.AppendChild(new MergeCell()
{
Reference = "J1:K1"
});
sheetPart.Worksheet.Append(mergeCells1);
Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
Sheet sheet = new Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = "Měnové kurzy"
};
sheets.Append(sheet);
Row row1 = new Row()
{
Height = 24, CustomHeight = true
};
row1.AppendChild(new Cell()
{
StyleIndex = 1, CellValue = new CellValue("DATUM"), DataType = CellValues.String
});
row1.AppendChild(new Cell()
{
StyleIndex = 1, CellValue = new CellValue("POČET"), DataType = CellValues.String
});
row1.AppendChild(new Cell()
{
StyleIndex = 1, CellValue = new CellValue(DataClass.Currency.GetBankName(DataClass.BankEnumerator.CNB)), DataType = CellValues.String
});
row1.AppendChild(new Cell()
{
StyleIndex = 7, CellValue = new CellValue(DataClass.Currency.GetBankName(DataClass.BankEnumerator.CSOB)), DataType = CellValues.String
});
row1.AppendChild(new Cell()
{
StyleIndex = 3, DataType = CellValues.String
});
row1.AppendChild(new Cell()
{
StyleIndex = 7, CellValue = new CellValue(DataClass.Currency.GetBankName(DataClass.BankEnumerator.RB)), DataType = CellValues.String
});
row1.AppendChild(new Cell()
{
StyleIndex = 3, DataType = CellValues.String
});
row1.AppendChild(new Cell()
{
StyleIndex = 7, CellValue = new CellValue(DataClass.Currency.GetBankName(DataClass.BankEnumerator.KB)), DataType = CellValues.String
});
row1.AppendChild(new Cell()
{
StyleIndex = 3, DataType = CellValues.String
});
row1.AppendChild(new Cell()
{
StyleIndex = 7, CellValue = new CellValue(DataClass.Currency.GetBankName(DataClass.BankEnumerator.SPORITELNA)), DataType = CellValues.String
});
row1.AppendChild(new Cell()
{
StyleIndex = 3, DataType = CellValues.String
});
sheetData.AppendChild(row1);
Row row2 = new Row()
{
Height = 24, CustomHeight = true
};
row2.AppendChild(new Cell()
{
StyleIndex = 1, DataType = CellValues.String
});
row2.AppendChild(new Cell()
{
StyleIndex = 1, DataType = CellValues.String
});
row2.AppendChild(new Cell()
{
StyleIndex = 1, CellValue = new CellValue("prodej"), DataType = CellValues.String
});
row2.AppendChild(new Cell()
{
StyleIndex = 7, CellValue = new CellValue("prodej"), DataType = CellValues.String
});
row2.AppendChild(new Cell()
{
StyleIndex = 3, CellValue = new CellValue("nákup"), DataType = CellValues.String
});
row2.AppendChild(new Cell()
{
StyleIndex = 7, CellValue = new CellValue("prodej"), DataType = CellValues.String
});
row2.AppendChild(new Cell()
{
StyleIndex = 3, CellValue = new CellValue("nákup"), DataType = CellValues.String
});
row2.AppendChild(new Cell()
{
StyleIndex = 7, CellValue = new CellValue("prodej"), DataType = CellValues.String
});
row2.AppendChild(new Cell()
{
StyleIndex = 3, CellValue = new CellValue("nákup"), DataType = CellValues.String
});
row2.AppendChild(new Cell()
{
StyleIndex = 7, CellValue = new CellValue("prodej"), DataType = CellValues.String
});
row2.AppendChild(new Cell()
{
StyleIndex = 3, CellValue = new CellValue("nákup"), DataType = CellValues.String
});
sheetData.AppendChild(row2);
int rowIndex = 3;
foreach (var cur in CurrencySingleton.Instance.Currencies)
{
Row currencyHeaderRow = new Row()
{
Height = 24, CustomHeight = true
};
currencyHeaderRow.AppendChild(new Cell()
{
StyleIndex = 2, CellValue = new CellValue(cur.Name), DataType = CellValues.String
});
currencyHeaderRow.AppendChild(new Cell()
{
StyleIndex = 2
});
currencyHeaderRow.AppendChild(new Cell()
{
StyleIndex = 2
});
currencyHeaderRow.AppendChild(new Cell()
{
StyleIndex = 2
});
currencyHeaderRow.AppendChild(new Cell()
{
StyleIndex = 2
});
currencyHeaderRow.AppendChild(new Cell()
{
StyleIndex = 2
});
currencyHeaderRow.AppendChild(new Cell()
{
StyleIndex = 2
});
currencyHeaderRow.AppendChild(new Cell()
{
StyleIndex = 2
});
currencyHeaderRow.AppendChild(new Cell()
{
StyleIndex = 2
});
currencyHeaderRow.AppendChild(new Cell()
{
StyleIndex = 2
});
currencyHeaderRow.AppendChild(new Cell()
{
StyleIndex = 2
});
sheetData.AppendChild(currencyHeaderRow);
mergeCells1.AppendChild(new MergeCell()
{
Reference = "A" + rowIndex + ":K" + rowIndex + ""
});
rowIndex++;
var dates = cur.BankRates[0].Value.Select(x => x.Key).ToList();
foreach (var date in dates)
{
Row newRow = new Row()
{
Height = 24, CustomHeight = true
};
newRow.AppendChild(new Cell()
{
StyleIndex = 1, CellValue = new CellValue(date.ToShortDateString()), DataType = CellValues.Date
});
newRow.AppendChild(new Cell()
{
StyleIndex = 1, CellValue = new CellValue(cur.BankRates[0].Value.First(x => x.Key == date).Value.Amount.ToString()), DataType = CellValues.Number
});
if (cur.BankRates.Any(x => x.Key == DataClass.BankEnumerator.CNB))
{
newRow.AppendChild(new Cell()
{
StyleIndex = 5, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.CNB).Value.First(x => x.Key == date).Value.SellRate.ToString().Replace(',', '.')), DataType = CellValues.Number
});
}
else
{
newRow.AppendChild(new Cell()
{
StyleIndex = 5
});
}
if (cur.BankRates.Any(x => x.Key == DataClass.BankEnumerator.CSOB) && cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.CSOB).Value.Any(x => x.Key == date))
{
newRow.AppendChild(new Cell()
{
StyleIndex = 6, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.CSOB).Value.First(x => x.Key == date).Value.SellRate.ToString().Replace(',', '.')), DataType = CellValues.Number
});
newRow.AppendChild(new Cell()
{
StyleIndex = 5, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.CSOB).Value.First(x => x.Key == date).Value.BuyRate.ToString().Replace(',', '.')), DataType = CellValues.Number
});
}
else
{
newRow.AppendChild(new Cell()
{
StyleIndex = 6
});
newRow.AppendChild(new Cell()
{
StyleIndex = 5
});
}
if (cur.BankRates.Any(x => x.Key == DataClass.BankEnumerator.RB) && cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.RB).Value.Any(x => x.Key == date))
{
newRow.AppendChild(new Cell()
{
StyleIndex = 6, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.RB).Value.First(x => x.Key == date).Value.SellRate.ToString().Replace(',', '.')), DataType = CellValues.Number
});
newRow.AppendChild(new Cell()
{
StyleIndex = 5, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.RB).Value.First(x => x.Key == date).Value.BuyRate.ToString().Replace(',', '.')), DataType = CellValues.Number
});
}
else
{
newRow.AppendChild(new Cell()
{
StyleIndex = 6
});
newRow.AppendChild(new Cell()
{
StyleIndex = 5
});
}
if (cur.BankRates.Any(x => x.Key == DataClass.BankEnumerator.KB) && cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.KB).Value.Any(x => x.Key == date))
{
newRow.AppendChild(new Cell()
{
StyleIndex = 6, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.KB).Value.First(x => x.Key == date).Value.SellRate.ToString().Replace(',', '.')), DataType = CellValues.Number
});
newRow.AppendChild(new Cell()
{
StyleIndex = 5, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.KB).Value.First(x => x.Key == date).Value.BuyRate.ToString().Replace(',', '.')), DataType = CellValues.Number
});
}
else
{
newRow.AppendChild(new Cell()
{
StyleIndex = 6
});
newRow.AppendChild(new Cell()
{
StyleIndex = 5
});
}
if (cur.BankRates.Any(x => x.Key == DataClass.BankEnumerator.SPORITELNA) && cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.SPORITELNA).Value.Any(x => x.Key == date))
{
newRow.AppendChild(new Cell()
{
StyleIndex = 6, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.SPORITELNA).Value.First(x => x.Key == date).Value.SellRate.ToString().Replace(',', '.')), DataType = CellValues.Number
});
newRow.AppendChild(new Cell()
{
StyleIndex = 5, CellValue = new CellValue(cur.BankRates.First(x => x.Key == DataClass.BankEnumerator.SPORITELNA).Value.First(x => x.Key == date).Value.BuyRate.ToString().Replace(',', '.')), DataType = CellValues.Number
});
}
else
{
newRow.AppendChild(new Cell()
{
StyleIndex = 6
});
newRow.AppendChild(new Cell()
{
StyleIndex = 5
});
}
sheetData.AppendChild(newRow);
rowIndex++;
}
}
workbook.WorkbookPart.Workbook.Save();
workbook.Close();
}
return(mem.ToArray());
}
}
/// <summary>
/// Write excel file of a list of object as T
/// Assume that maximum of 24 columns
/// </summary>
/// <typeparam name="T">Object type to pass in</typeparam>
/// <param name="fileName">Full path of the file name of excel spreadsheet</param>
/// <param name="objects">list of the object type</param>
/// <param name="sheetName">Sheet names of Excel File</param>
/// <param name="headerNames">Header names of the object</param>
public void Create <T>(
string fileName,
List <T> objects,
string sheetName,
List <string> headerNames)
{
//Open the copied template workbook.
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
// Create Styles and Insert into Workbook
WorkbookStylesPart stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
string relId = workbookPart.GetIdOfPart(worksheetPart);
Workbook workbook = new Workbook();
FileVersion fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
SheetData sheetData = CreateSheetData <T>(objects, headerNames, stylesPart);
Worksheet worksheet = new Worksheet();
//PageMargins pageM = worksheet.GetFirstChild<PageMargins>();
//SheetProtection sheetProtection = new SheetProtection();
//sheetProtection.Sheet = true;
//sheetProtection.Objects = true;
//sheetProtection.Scenarios = true;
////add column C:Z to allow edit range, which means column A,B and after Z are locked
//ProtectedRanges pRanges = new ProtectedRanges();
//ProtectedRange pRange = new ProtectedRange();
//ListValue<StringValue> lValue = new ListValue<StringValue>();
//lValue.InnerText = "D1:Z1048576";
//pRange.SequenceOfReferences = lValue;
//pRange.Name = "AllowEditRange1";
//pRanges.Append(pRange);
//worksheet.InsertBefore(sheetProtection, pageM);
//worksheet.InsertBefore(pRanges, pageM);
int numCols = headerNames.Count;
int width = headerNames.Max(h => h.Length) + 5;
Columns columns = new Columns();
for (int col = 0; col < numCols; col++)
{
Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);
if (col == 0)
{
c.Hidden = BooleanValue.FromBoolean(true);
}
columns.Append(c);
}
worksheet.Append(columns);
Sheets sheets = new Sheets();
Sheet sheet = new Sheet {
Name = sheetName, SheetId = 1, Id = relId
};
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
private static async Task <MemoryStream> DataToExcelStreamAsync(WriteRowsDelegate writeRows, IList <String> headers, string sheetName, List <int> columnWidths = null)
{
var xmlStream = ReportingHelper.GetResourceStream("Shesha.Web.DataTable.Excel.template.xlsx", typeof(ExcelUtility).Assembly);
using (var document = SpreadsheetDocument.Open(xmlStream, true))
{
var workbookPart = document.WorkbookPart;
var worksheetPart = workbookPart.WorksheetParts.First();
var originalSheetId = workbookPart.GetIdOfPart(worksheetPart);
var replacementPart = workbookPart.AddNewPart <WorksheetPart>();
var replacementPartId = workbookPart.GetIdOfPart(replacementPart);
// Configure the spreadsheet
SetSheetName(sheetName, document);
SetStyleSheet(document);
// Fit to page
var sp = new SheetProperties(new PageSetupProperties());
var ws = worksheetPart.Worksheet;
ws.SheetProperties = sp;
// Set the FitToPage property to true
ws.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(true);
var pgOr = new PageSetup
{
// Page size A4 landscape
PaperSize = 9,
Orientation = OrientationValues.Landscape,
// Scale to fit to page width
FitToWidth = 1,
FitToHeight = 0
};
ws.AppendChild(pgOr);
var maxWidth = 0;
if (columnWidths != null)
{
var idx = 1;
var columns =
columnWidths
.Select(
w => new Column
{
CustomWidth = true,
Min = Convert.ToUInt32(idx),
Max = Convert.ToUInt32(idx++),
Width = w,
BestFit = false
})
.ToList();
var cols = new DocumentFormat.OpenXml.Spreadsheet.Columns(columns);
worksheetPart.Worksheet.InsertBefore(cols, worksheetPart.Worksheet.GetFirstChild <SheetData>());
}
else
{
maxWidth = headers.Select(h => h.Length).Max();
AddCellWidthStyles(Convert.ToUInt32(1), Convert.ToUInt32(headers.Count), maxWidth, document, worksheetPart);
}
worksheetPart.Worksheet.Save();
document.WorkbookPart.Workbook.Save();
using (var xmlReader = OpenXmlReader.Create(worksheetPart))
{
using (var xmlWriter = OpenXmlWriter.Create(replacementPart))
{
while (xmlReader.Read())
{
if (xmlReader.ElementType == typeof(SheetData))
{
if (xmlReader.IsEndElement)
{
continue;
}
xmlWriter.WriteStartElement(new SheetData());
var headerCell = new Cell(new CellValue());
headerCell.DataType = new EnumValue <CellValues>(CellValues.String);
// write headers
xmlWriter.WriteStartElement(new Row());
SetHeaderStyle(document, headerCell);
foreach (var header in headers)
{
headerCell.CellValue.Text = header;
xmlWriter.WriteElement(headerCell);
}
xmlWriter.WriteEndElement();
await writeRows.Invoke(xmlWriter);
xmlWriter.WriteEndElement();
}
else
{
if (xmlReader.IsStartElement)
{
xmlWriter.WriteStartElement(xmlReader);
}
else if (xmlReader.IsEndElement)
{
xmlWriter.WriteEndElement();
}
}
}
}
}
var sheet = workbookPart.Workbook.Descendants <Sheet>().First(s => s.Id.Value.Equals(originalSheetId));
sheet.Id.Value = replacementPartId;
workbookPart.DeletePart(worksheetPart);
}
return(xmlStream);
}
/// <summary>
/// Creates style sheet
/// </summary>
/// <returns>The style sheet</returns>
internal static Stylesheet CreateStylesheet()
{
Stylesheet styleSheet = new Stylesheet();
CellFormats cellFormats = new CellFormats();
CellFormat cellFormat = new CellFormat
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0
};
cellFormats.Append(cellFormat);
uint iExcelIndex = 164;
NumberingFormats numberingFormats = new NumberingFormats();
NumberingFormat nfDateTime = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
};
numberingFormats.Append(nfDateTime);
NumberingFormat nf4decimal = new NumberingFormat();
nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
numberingFormats.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");
numberingFormats.Append(nf2decimal);
// @ is also Excel style index 49
NumberingFormat nfForcedText = new NumberingFormat();
nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfForcedText.FormatCode = StringValue.FromString("@");
numberingFormats.Append(nfForcedText);
NumberingFormat nfInteger = new NumberingFormat();
nfInteger.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
nfInteger.FormatCode = StringValue.FromString("#");
numberingFormats.Append(nfInteger);
// index 1
// Format dd/mm/yyyy
cellFormat = new CellFormat();
cellFormat.NumberFormatId = 14;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
// index 2
// Format #,##0.00
cellFormat = new CellFormat();
cellFormat.NumberFormatId = 4;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
// index 3
cellFormat = new CellFormat();
cellFormat.NumberFormatId = nfDateTime.NumberFormatId;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
// index 4
cellFormat = new CellFormat();
cellFormat.NumberFormatId = nf4decimal.NumberFormatId;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
// index 5
cellFormat = new CellFormat();
cellFormat.NumberFormatId = nf2decimal.NumberFormatId;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
// index 6
cellFormat = new CellFormat();
cellFormat.NumberFormatId = nfForcedText.NumberFormatId;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
// index 7
// Header text
cellFormat = new CellFormat();
cellFormat.NumberFormatId = nfForcedText.NumberFormatId;
cellFormat.FontId = 1;
cellFormat.FillId = 0;
cellFormat.BorderId = 0;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
// index 8
// column text
cellFormat = new CellFormat();
cellFormat.NumberFormatId = nfForcedText.NumberFormatId;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 1;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
// index 9
// coloured 2 decimal text
cellFormat = new CellFormat();
cellFormat.NumberFormatId = nf2decimal.NumberFormatId;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 1;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
// index 10
// coloured column text
cellFormat = new CellFormat();
cellFormat.NumberFormatId = nfForcedText.NumberFormatId;
cellFormat.FontId = 1;
cellFormat.FillId = 2;
cellFormat.BorderId = 1;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
// index 11
// coloured 2 decimal text
cellFormat = new CellFormat();
cellFormat.NumberFormatId = nfInteger.NumberFormatId;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 1;
cellFormat.FormatId = 0;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
// index 12
// coloured footer text
cellFormat = new CellFormat
{
NumberFormatId = nfForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 1,
FormatId = 0,
Alignment = new Alignment {
Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center
},
ApplyAlignment = true,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// index 13
// column text
cellFormat = new CellFormat();
cellFormat.NumberFormatId = nfForcedText.NumberFormatId;
cellFormat.FontId = 0;
cellFormat.FillId = 0;
cellFormat.BorderId = 1;
cellFormat.FormatId = 0;
cellFormat.Alignment = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center
};
cellFormat.ApplyAlignment = true;
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
cellFormats.Append(cellFormat);
numberingFormats.Count = UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
styleSheet.Append(numberingFormats);
CreateFonts(styleSheet);
CreateFills(styleSheet);
CreateBorders(styleSheet);
CreateCellStyleFormats(styleSheet);
styleSheet.Append(cellFormats);
CreateCellStyles(styleSheet);
styleSheet.Append(new DifferentialFormats {
Count = 0
});
styleSheet.Append(new TableStyles
{
Count = 0,
DefaultTableStyle = "TableStyleMedium9",
DefaultPivotStyle = "PivotStyleLight16"
});
return(styleSheet);
}
private void Button_Click_1(object sender, RoutedEventArgs e)
{
string mainGoAction = Properties.Settings.Default.mainGoAction;
if (mainGoAction == "email")
{
selectedOption.IsEnabled = true;
notSelectedOption.IsEnabled = true;
if (dataGrid.SelectedItems.Count == 0)
{
selectedOption.IsEnabled = false;
}
if (dataGrid.SelectedItems.Count == dataGrid.Items.Count)
{
notSelectedOption.IsEnabled = false;
}
System.Windows.Controls.Button senderButton = sender as System.Windows.Controls.Button;
System.Windows.Controls.ContextMenu contextMenu = senderButton.ContextMenu;
contextMenu.IsEnabled = true;
contextMenu.PlacementTarget = senderButton;
contextMenu.Placement = System.Windows.Controls.Primitives.PlacementMode.Left;
contextMenu.IsOpen = true;
}
else if (mainGoAction == "excel")
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel Workbook (*.xlsx)|*.xlsx";
if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
try
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(saveFileDialog.FileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorkbookStylesPart workbookStylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
workbookStylePart.Stylesheet = new Stylesheet(
new DocumentFormat.OpenXml.Spreadsheet.Fonts(
new DocumentFormat.OpenXml.Spreadsheet.Font(),
new DocumentFormat.OpenXml.Spreadsheet.Font()
{
Color = new DocumentFormat.OpenXml.Spreadsheet.Color()
{
Theme = 10
}, Underline = new DocumentFormat.OpenXml.Spreadsheet.Underline()
},
new DocumentFormat.OpenXml.Spreadsheet.Font()
{
Bold = new DocumentFormat.OpenXml.Spreadsheet.Bold()
}),
new Fills(
new Fill()),
new Borders(
new DocumentFormat.OpenXml.Spreadsheet.Border()),
new CellFormats(
new CellFormat()
{
NumberFormatId = 0, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0
},
new CellFormat()
{
NumberFormatId = 14, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
},
new CellFormat()
{
NumberFormatId = 10, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true)
},
new CellFormat()
{
NumberFormatId = 0, FormatId = 0, FontId = 1, BorderId = 0, FillId = 0
},
new CellFormat()
{
NumberFormatId = 0, FormatId = 0, FontId = 2, BorderId = 0, FillId = 0
}));
workbookStylePart.Stylesheet.Save();
Hyperlinks hyperlinks = new Hyperlinks();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
worksheetPart.Worksheet.Append(hyperlinks);
worksheetPart.Worksheet.Save();
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Table"
};
sheets.Append(sheet);
SharedStringTablePart sharedStringTablePart = spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
sharedStringTablePart.SharedStringTable = new SharedStringTable();
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
sheetData.Append(new Row(
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Requestor E-mail")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Feedback Rate")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Number Of Feedbacks")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Number Of No Feedbacks")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Reference")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Publish Date")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Title")),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 4
}));
int rowCounter = 1;
foreach (UserData userData in dataGridSource)
{
foreach (SimRequestData simRequestData in userData.simRequests)
{
rowCounter++;
sheetData.Append(new Row(
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, userData.email.ToString())),
DataType = new EnumValue <CellValues>(CellValues.SharedString)
},
new Cell()
{
CellValue = new CellValue((Convert.ToDouble(userData.rate) / 100d).ToString()),
StyleIndex = 2
},
new Cell()
{
CellValue = new CellValue(userData.feedbacks.ToString())
},
new Cell()
{
CellValue = new CellValue(userData.noFeedbacks.ToString())
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, simRequestData.reference.ToString())),
DataType = new EnumValue <CellValues>(CellValues.SharedString),
StyleIndex = 3
},
new Cell()
{
CellValue = new CellValue(Convert.ToDateTime(simRequestData.publishDate).ToOADate().ToString()),
StyleIndex = 1
},
new Cell()
{
CellValue = new CellValue(insertSharedString(sharedStringTablePart, simRequestData.title.ToString())),
DataType = new EnumValue <CellValues>(CellValues.SharedString)
}
));
string hyperlinkID = "id" + rowCounter.ToString();
hyperlinks.Append(new DocumentFormat.OpenXml.Spreadsheet.Hyperlink()
{
Reference = "E" + rowCounter.ToString(), Id = hyperlinkID
});
worksheetPart.AddHyperlinkRelationship(simRequestData.requestLink, true, hyperlinkID);
}
}
workbookPart.Workbook.Save();
}
}
catch (Exception exception)
{
System.Windows.MessageBox.Show(exception.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error, MessageBoxResult.OK);
}
}
}
}
