/// <summary>
/// Поворачивает документ в ландшафтную ориентацию
/// </summary>
public void RotateLandscape()
{
var worksheetIds = _workbookPart.Workbook.Descendants <Sheet>().Select(s => s.Id.Value);
foreach (string worksheetId in worksheetIds)
{
PageSetup pageSetup = _workSheet.Descendants <PageSetup>().FirstOrDefault();
if (pageSetup != null)
{
pageSetup = new PageSetup
{
Orientation = OrientationValues.Landscape,
PaperSize = 9U
};
_workSheet.AppendChild(pageSetup);
}
else
{
pageSetup = new PageSetup()
{
Orientation = OrientationValues.Landscape
};
_workSheet.AppendChild(pageSetup);
}
_workSheet.Save();
}
_workbookPart.Workbook.Save();
}
private static Sheet CreateSheet <T>(int sheetIndex, SheetDefinition <T> def, WorkbookPart workbookPart)
{
// create worksheet part
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var worksheetId = workbookPart.GetIdOfPart(worksheetPart);
// variables
var numCols = def.Fields.Count;
var numRows = def.Objects.Count;
var az = new List <Char>(Enumerable.Range('A', 'Z' - 'A' + 1).Select(i => (Char)i).ToArray());
var headerCols = az.GetRange(0, numCols);
var hasTitleRow = def.Title != null;
var hasSubtitleRow = def.SubTitle != null;
var titleRowCount = hasTitleRow ? 1 + (hasSubtitleRow ? 1 : 0) : hasSubtitleRow ? 1 : 0;
// get the worksheet data
int firstTableRow;
var sheetData = CreateSheetData(def.Objects, def.Fields, headerCols, def.IncludeTotalsRow, def.Title,
def.SubTitle,
out firstTableRow);
// populate column metadata
var columns = new Columns();
for (var col = 0; col < numCols; col++)
{
var width = ColumnWidth(sheetData, col, titleRowCount);
columns.AppendChild(CreateColumnMetadata((UInt32)col + 1, (UInt32)numCols + 1, width));
}
// populate worksheet
var worksheet = new Worksheet();
worksheet.AppendChild(columns);
worksheet.AppendChild(sheetData);
// add an auto filter
worksheet.AppendChild(new AutoFilter
{
Reference =
String.Format("{0}{1}:{2}{3}", headerCols.First(), firstTableRow - 1, headerCols.Last(),
numRows + titleRowCount + 1)
});
// add worksheet to worksheet part
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
return(new Sheet {
Name = def.Name, SheetId = (UInt32)sheetIndex, Id = worksheetId
});
}
public static void PageSetupUpdate(WorksheetPart worksheetPart, OrientationValues landscapeOrPortrait,
DoubleValue marginLeft, DoubleValue marginRight, DoubleValue marginTop, DoubleValue marginBottom, DoubleValue marginHeader, DoubleValue marginFooter,
Boolean isFitToPage, UInt32Value FitToHeight, UInt32Value FitToWidth, UInt32Value pageSize, string headerLeft, string headerCenter, string headerRight, string footerLeft, string footerRight)
{
Worksheet ws = worksheetPart.Worksheet;
//page setup them moi pagesetup properties
SheetProperties sp = new SheetProperties(new PageSetupProperties());
ws.SheetProperties = sp;
PrintOptions printOp = new PrintOptions();
printOp.HorizontalCentered = true;
ws.AppendChild(printOp);
PageMargins pageMargins = new PageMargins();
pageMargins.Left = marginLeft;
pageMargins.Right = marginRight;
pageMargins.Top = marginTop;
pageMargins.Bottom = marginBottom;
pageMargins.Header = marginHeader;
pageMargins.Footer = marginFooter;
ws.AppendChild(pageMargins);
// Set the FitToPage property to true
ws.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(isFitToPage);
DocumentFormat.OpenXml.Spreadsheet.PageSetup pgOr = new DocumentFormat.OpenXml.Spreadsheet.PageSetup();
pgOr.Orientation = landscapeOrPortrait;
pgOr.PaperSize = pageSize;
pgOr.FitToHeight = FitToHeight;
pgOr.FitToWidth = FitToWidth;
ws.AppendChild(pgOr);
HeaderFooter headerFooter1 = new HeaderFooter();
OddHeader oddHeader1 = new OddHeader();
oddHeader1.Text = "&L&\"Times New Roman,Regular\"" + headerLeft + "&C&\"Times New Roman,Regular\"" + headerCenter + "&R&\"Times New Roman,Regular\"" + headerRight;
OddFooter oddFooter1 = new OddFooter();
oddFooter1.Text = "&L&\"Times New Roman,Regular\"" + footerLeft + "&C&P&R&\"Times New Roman,Regular\"" + footerRight;
headerFooter1.Append(oddHeader1);
headerFooter1.Append(oddFooter1);
ws.AppendChild(headerFooter1);
//save worksheet properties
//worksheetPart.Worksheet.Save();
}
/// <summary>
/// Adds a new sheet.
/// </summary>
/// <param name="name">The sheet name.</param>
public XlsxSheet AddSheet(string name)
{
Guard.EnsureNotEmpty(name, nameof(name));
var workbookPart = _spreadsheetDocument.WorkbookPart;
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var workSheet = new Worksheet();
var sheetData = new SheetData();
workSheet.AppendChild(sheetData);
worksheetPart.Worksheet = workSheet;
var sheetId = 1u;
var allSheets = workbookPart.Workbook.Sheets.OfType <Sheet>();
if (allSheets.Any())
{
sheetId = allSheets.Max(sh => sh.SheetId.Value) + 1;
}
var sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = name
};
workbookPart.Workbook.Sheets.Append(sheet);
return(new XlsxSheet(worksheetPart, sheetData, sheet));
}
public void KoExportTest()
{
// Runden für das KO-System erzeugen
var tournamentBracketLogRoundOne = new TournamentBracketLogRound(1);
tournamentBracketLogRoundOne.AddMatch(PLAYER_FIVE, PLAYER_THREE, true);
tournamentBracketLogRoundOne.AddMatch(PLAYER_SIX, PLAYER_ONE, true);
var tournamentBracketLogRoundTwo = new TournamentBracketLogRound(2);
tournamentBracketLogRoundTwo.AddMatch(PLAYER_FIVE, PLAYER_SIX, false);
// Runden hinzufügen
TournamentBracketLog.Rounds.Add(tournamentBracketLogRoundOne);
TournamentBracketLog.Rounds.Add(tournamentBracketLogRoundTwo);
// Dinge erzeugen, die wichtig für den Export sind
var fileName = "KnockOutStageGroupExportTest.xlsx";
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetdata = new SheetData();
worksheet.AppendChild(sheetdata);
worksheetPart.Worksheet = worksheet;
Sheet sheet2 = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 2,
Name = "KO"
};
sheets.Append(sheet2);
// Den eigentlichen Export durchführen
var excelExportFactory = new ExcelExportFactory();
var bracketLog = excelExportFactory.CreateExcelExport(ExcelExportFactory.ExcelExportType.Bracket);
bracketLog.Export(document, sheetdata, worksheetPart);
// Zellen aus dem Dokument laden, welche angeschaut werden müssen
var firstUsedCell = CellFinder.GetCell(worksheetPart.Worksheet, 1, 2);
var firstRoundStartCell = CellFinder.GetCell(worksheetPart.Worksheet, 1, 3);
var secondRoundStartCell = CellFinder.GetCell(worksheetPart.Worksheet, 2, 4);
var thirdRoundStartCell = CellFinder.GetCell(worksheetPart.Worksheet, 3, 6);
// Tests durchführen
Assert.IsTrue(firstUsedCell != null && firstUsedCell.DataType == CellValues.String);
Assert.AreEqual(firstUsedCell.CellValue.Text, "Round 1");
Assert.IsTrue(firstRoundStartCell != null && firstRoundStartCell.DataType == CellValues.String);
Assert.IsTrue(secondRoundStartCell != null && secondRoundStartCell.DataType == CellValues.String);
Assert.IsTrue(thirdRoundStartCell != null && thirdRoundStartCell.DataType == CellValues.String);
}
}
public byte[] Save()
{
using (var ms = new MemoryStream())
using (var doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = doc.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
Sheets sheets = doc.WorkbookPart.Workbook.AppendChild(new Sheets());
var stylesPart = doc.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = new ExcelWorkbookStylesheet().CreateStylesheet(this.sheets);
foreach (var item in this.sheets)
{
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var workSheet = new Worksheet();
var sheetData = new SheetData();
foreach (var rowGroup in item.Cells.GroupBy(g => g.Value.Row))
{
var row = new Row()
{
RowIndex = Convert.ToUInt32(rowGroup.Key)
};
foreach (var cellItem in rowGroup)
{
row.Append(this.CreateCell(cellItem.Value));
}
sheetData.Append(row);
}
workSheet.AppendChild(sheetData);
worksheetPart.Worksheet = workSheet;
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
Sheet sheet1 = new Sheet()
{
Id = doc.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = item.SheetName
};
sheets.Append(sheet1);
}
doc.Close();
return(ms.ToArray());
}
}
private void SheetDataSet(SheetData sheetData1, string reference, Worksheet worksheet1)
{
var sheetDimension1 = new SheetDimension()
{
Reference = reference
};
var sheetViews1 = new SheetViews();
var sheetView1 = new SheetView()
{
TabSelected = true, WorkbookViewId = 0
};
var sheetFormatProperties1 = new SheetFormatProperties {
DefaultRowHeight = 15D, DyDescent = 0.25D
};
var filter = new AutoFilter()
{
Reference = reference
};
var pageMargins1 = new PageMargins()
{
Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D
};
sheetViews1.AppendChild(sheetView1);
worksheet1.AppendChild(sheetDimension1);
worksheet1.AppendChild(sheetViews1);
worksheet1.AppendChild(sheetFormatProperties1);
worksheet1.AppendChild(sheetData1);
worksheet1.AppendChild(filter);
worksheet1.AppendChild(pageMargins1);
}
public static void AddRow(this Worksheet ws, params object[] data)
{
var sd = ws.GetFirstChild <SheetData>();
if (sd == null)
{
sd = ws.AppendChild(new SheetData());
}
var row = sd.AppendChild(new Row());
foreach (var item in data)
{
if (item == null)
{
row.AppendChild(new Cell());
}
else if (item is HyperlinkCell)
{
var hyperlinkCell = item as HyperlinkCell;
var cell = CreateTextCell(hyperlinkCell.DisplayString);
if (hyperlinkCell.StyleIndex.HasValue)
{
cell.StyleIndex = (UInt32Value)hyperlinkCell.StyleIndex.Value;
}
row.AppendChild(cell);
var hlRelationship = ws.WorksheetPart.AddHyperlinkRelationship(hyperlinkCell.Url, true);
var hyperlink = new Hyperlink
{
Reference = GetCellRefence(sd, row),
Id = hlRelationship.Id
};
var hyperlinks = ws.Descendants <Hyperlinks>().FirstOrDefault() ?? ws.AppendChild(new Hyperlinks());
hyperlinks.Append(hyperlink);
}
else if (item is string)
{
row.AppendChild(CreateTextCell(item as string));
}
else
{
row.AppendChild(CreateNumberCell(item.ToString()));
}
}
}
/// <summary>
/// Sets the page setup orientation of a worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <param name="orientation"></param>
public static void SetOrientation(this Worksheet worksheet, OrientationValues orientation)
{
PageSetup pageSetup = worksheet.Descendants <PageSetup>().FirstOrDefault();
if (pageSetup == null)
{
worksheet.AppendChild(
new PageSetup
{
Orientation = orientation
});
}
}
public static void PageSetupUpdate(WorksheetPart worksheetPart, OrientationValues landscapeOrPortrait,
DoubleValue marginLeft, DoubleValue marginRight, DoubleValue marginTop, DoubleValue marginBottom, DoubleValue marginHeader, DoubleValue marginFooter,
Boolean isFitToPage, UInt32Value pageSize)
{
Worksheet ws = worksheetPart.Worksheet;
//page setup them moi pagesetup properties
SheetProperties sp = new SheetProperties(new PageSetupProperties());
ws.SheetProperties = sp;
PrintOptions printOp = new PrintOptions();
printOp.HorizontalCentered = true;
ws.AppendChild(printOp);
PageMargins pageMargins = new PageMargins();
pageMargins.Left = marginLeft;
pageMargins.Right = marginRight;
pageMargins.Top = marginTop;
pageMargins.Bottom = marginBottom;
pageMargins.Header = marginHeader;
pageMargins.Footer = marginFooter;
ws.AppendChild(pageMargins);
// Set the FitToPage property to true
ws.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(isFitToPage);
DocumentFormat.OpenXml.Spreadsheet.PageSetup pgOr = new DocumentFormat.OpenXml.Spreadsheet.PageSetup();
pgOr.Orientation = landscapeOrPortrait;
pgOr.PaperSize = pageSize;
pgOr.FitToHeight = 0;
pgOr.FitToWidth = 1;
ws.AppendChild(pgOr);
//save worksheet properties
//worksheetPart.Worksheet.Save();
}
private void WriteFooter(string footer)
{
HeaderFooter headerFooter = Worksheet.Descendants <HeaderFooter>().FirstOrDefault();
if (headerFooter == null)
{
headerFooter = new HeaderFooter();
Worksheet.AppendChild <HeaderFooter>(headerFooter);
}
headerFooter.EvenFooter = new EvenFooter();
headerFooter.EvenFooter.Text = footer;
headerFooter.OddFooter = new OddFooter();
headerFooter.OddFooter.Text = footer;
}
private void SetColumnProperties(Worksheet worksheet)
{
Columns columns = new Columns(
new Column
{
Min = 12,
Max = 12,
Width = 12,
CustomWidth = true
}
);
worksheet.AppendChild(columns);
}
private void ExportToExcel(object sender, EventArgs e)
{
JObject json = JObject.Parse(textBox1.Text);
JObject playlist = (JObject)json["playlist"];
// 创建文档
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument document = SpreadsheetDocument.Create("out.xlsx", SpreadsheetDocumentType.Workbook);
// 准备文件结构中的各XML
// Add a WorkbookPart to the document.
WorkbookPart workbookPart = document.AddWorkbookPart();
// Add a SharedStringTablePart to the WorkbookPart.
SharedStringTablePart sharedStringTablePart = workbookPart.AddNewPart <SharedStringTablePart>();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
// 生成WorkbookPart的XML结构
Workbook workbook = workbookPart.Workbook = new Workbook();
// Add Sheets to the Workbook.
Sheets sheets = workbook.AppendChild(new Sheets());
// Append a new worksheet and associate it with the workbook.
sheets.Append(new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = (string)playlist["name"]
});
// 生成SharedStringTablePart的XML结构
SharedStringTable sharedStringTable = sharedStringTablePart.SharedStringTable = new SharedStringTable();
// 生成WorksheetPart的XML结构
Worksheet worksheet = worksheetPart.Worksheet = new Worksheet();
// Get the sheetData cell table.
SheetData sheetData = worksheet.AppendChild(new SheetData());
// 插入数据
AddDatas(sheetData, sharedStringTable, (JArray)playlist["tracks"]);
// 关闭文档
document.Close();
MessageBox.Show("导出成功！");
}
/// <summary>
/// Adds a row to the worksheet
/// </summary>
/// <param name="workSheet">Worksheet to add to</param>
/// <param name="data">An array of objects to add</param>
/// <param name="styleIndex">Style index from the workbooks styles to use</param>
public static void AddRow(this Worksheet workSheet, object[] data, uint styleIndex = 0)
{
var sd = workSheet.GetFirstChild <SheetData>();
if (sd == null)
{
sd = workSheet.AppendChild(new SheetData());
}
var row = sd.AppendChild(new Row());
foreach (var item in data)
{
Cell GetCell()
{
switch (item)
{
case null:
return(new Cell());
case DateTimeOffset dt:
return(BuildDateCell(dt));
case int i:
return(BuildNumberCell(i.ToString()));
case PropertyInfo pi:
return(BuildTextCell(pi.Name));
case string str:
return(BuildTextCell(str));
default:
Console.WriteLine("Warning: item is not a string. The item will be written with .ToString()");
return(BuildTextCell(item.ToString()));
}
}
var cell = GetCell();
cell.StyleIndex = styleIndex;
row.AppendChild(cell);
}
}
protected Cell CreateCell(CellReference cellRef, EnumValue <CellValues> dataType)
{
var sheetData = _sheet.Descendants <SheetData>().FirstOrDefault();
if (sheetData == null)
{
sheetData = new SheetData();
_sheet.AppendChild(sheetData);
}
var row = sheetData.Descendants <Row>().FirstOrDefault(a => a.RowIndex == cellRef.Row);
if (row == null)
{
row = new Row();
row.RowIndex = new UInt32Value((uint)cellRef.Row);
var prevRow = GetPrevRow(cellRef.Row, sheetData);
if (prevRow == null)
{
sheetData.InsertAt(row, 0);
}
else
{
sheetData.InsertAfter(row, prevRow);
}
}
var cell = new Cell
{
CellReference = cellRef.ToString(),
DataType = dataType
};
var prevCell = GetPrevCell(cellRef, row);
if (prevCell == null)
{
row.InsertAt(cell, 0);
}
else
{
row.InsertAfter(cell, prevCell);
}
return(cell);
}
/// <summary>
/// Import worksheet part from one document to another
/// </summary>
/// <param name="sourceDoc">Source excel document</param>
/// <param name="srcSheet">Source worksheet</param>
/// <param name="targetDoc">Target excel document</param>
/// <param name="tgtSheet">Position of imported worksheet</param>
/// <returns></returns>
protected static void ImportWorksheet(SpreadsheetDocument sourceDoc, Worksheet srcSheet, SpreadsheetDocument targetDoc, Worksheet tgtSheet)
{
// Import sheet format properties
tgtSheet.SheetFormatProperties = srcSheet.SheetFormatProperties.Clone() as SheetFormatProperties;
// Import dimension
tgtSheet.SheetDimension = srcSheet.SheetDimension.Clone() as SheetDimension;
// Import columns
var srcCols = srcSheet.GetFirstChild <Columns>();
if (srcCols != null)
{
tgtSheet.AppendChild(srcCols.Clone() as Columns);
}
// Import sheet data
var srcSheetData = srcSheet.GetFirstChild <SheetData>();
if (srcSheetData != null)
{
var tgtSheetData = tgtSheet.GetFirstChild <SheetData>();
if (tgtSheetData == null)
{
tgtSheetData = tgtSheet.AppendChild(new SheetData()) as SheetData;
}
// Import rows
foreach (var row in srcSheetData.Elements <Row>())
{
var newRow = row.CloneNode(false) as Row;
tgtSheetData.AppendChild(newRow);
// Import row style
if (newRow.StyleIndex != null)
{
var id = newRow.StyleIndex.Value;
var newId = ImportStyle(sourceDoc, id, targetDoc);
newRow.StyleIndex = new UInt32Value(newId);
}
// Import cells
foreach (var cell in row.Elements <Cell>())
{
var newCell = cell.Clone() as Cell;
if (newCell == null)
{
throw new InvalidCastException("Cloned cell is not Cell!");
}
// Handle shared string
if (cell.CellValue != null && cell.DataType != null &&
cell.DataType.Value == CellValues.SharedString)
{
uint id = uint.Parse(cell.CellValue.InnerText);
uint newId = ImportSharedString(sourceDoc, id, targetDoc);
newCell.CellValue = new CellValue(newId.ToString(CultureInfo.InvariantCulture));
}
// Handle style
if (cell.StyleIndex != null)
{
uint id = uint.Parse(cell.StyleIndex.InnerText);
uint newId = ImportStyle(sourceDoc, id, targetDoc);
newCell.StyleIndex = new UInt32Value(newId);
}
// Handle cell metadata
if (cell.CellMetaIndex != null)
{
}
// Handle value metadata
if (cell.ValueMetaIndex != null)
{
}
newRow.AppendChild(newCell);
}
}
}
// Import merge cells
var srcMergeCells = srcSheet.GetFirstChild <MergeCells>();
if (srcMergeCells != null)
{
tgtSheet.AppendChild(srcMergeCells.Clone() as MergeCells);
}
}
public static Table AddTable(this Worksheet worksheet, int rowStart, int rowCount, int columnStart, params string[] headers)
{
if (rowCount == 1)
{
rowCount++;
}
string range = ComputeRange(rowStart, rowCount, columnStart, headers.Length);
var sheetViews = worksheet.GetFirstChild <SheetViews>();
if (sheetViews == null)
{
sheetViews = worksheet.InsertAt(new SheetViews(), 0);
}
var sheetView = sheetViews.AppendChild(new SheetView());
sheetView.WorkbookViewId = 0;
var selection = sheetView.AppendChild(new Selection());
selection.SequenceOfReferences = new ListValue <StringValue>()
{
InnerText = range
};
selection.ActiveCell = range.Substring(0, range.IndexOf(":", StringComparison.Ordinal));
var tableDefPart = worksheet.WorksheetPart.AddNewPart <TableDefinitionPart>();
// use unique ids for tables.
uint tableID = IncrementalUniqueId;
var tp = new TablePart
{
Id = worksheet.WorksheetPart.GetIdOfPart(tableDefPart)
};
var tableParts = worksheet.GetFirstChild <TableParts>();
if (tableParts == null)
{
tableParts = worksheet.AppendChild(new TableParts());
}
tableParts.AppendChild(tp);
tableDefPart.Table = new Table()
{
Id = tableID,
Name = tableID.ToString(CultureInfo.CurrentCulture),
DisplayName = "Table" + tableID.ToString(CultureInfo.CurrentCulture)
};
tableDefPart.Table.Reference = range;
uint columnCount = (uint)headers.Length;
var tc = tableDefPart.Table.AppendChild(new TableColumns()
{
Count = columnCount
});
for (uint i = 0; i < columnCount; i++)
{
tc.AppendChild(new TableColumn()
{
Id = i + 1, Name = headers[i]
});
}
tableDefPart.Table.AutoFilter = new AutoFilter
{
Reference = range
};
var styleInfo = tableDefPart.Table.AppendChild(new TableStyleInfo());
styleInfo.Name = "TableStyleMedium2";
styleInfo.ShowFirstColumn = false;
styleInfo.ShowRowStripes = true;
styleInfo.ShowLastColumn = false;
styleInfo.ShowColumnStripes = false;
return(tableDefPart.Table);
}
public static void ExportToExcel(bool doubleKo = false)
{
// Neues Dokument erstellen
var fileName = "KnockOutStage.xlsx";
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Groups
WorksheetPart worksheetPart1 = workbookPart.AddNewPart <WorksheetPart>();
SheetData sheetData1 = new SheetData();
if (TournamentGroupLog.Groups.Count > 0)
{
Worksheet worksheet1 = new Worksheet();
worksheet1.AppendChild(sheetData1);
worksheetPart1.Worksheet = worksheet1;
Sheet sheet1 = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart1),
SheetId = 1,
Name = "Groups"
};
sheets.Append(sheet1);
}
// KO
WorksheetPart worksheetPart2 = workbookPart.AddNewPart <WorksheetPart>();
Worksheet worksheet2 = new Worksheet();
SheetData sheetData2 = new SheetData();
worksheet2.AppendChild(sheetData2);
worksheetPart2.Worksheet = worksheet2;
Sheet sheet2 = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart2),
SheetId = 2,
Name = "KO"
};
sheets.Append(sheet2);
var excelExportFactory = new ExcelExportFactory();
if (TournamentGroupLog.Groups.Count > 0)
{
var groupLog = excelExportFactory.CreateExcelExport(ExcelExportFactory.ExcelExportType.Groups);
groupLog.Export(document, sheetData1, worksheetPart1);
}
if (!doubleKo)
{
var bracketLog = excelExportFactory.CreateExcelExport(ExcelExportFactory.ExcelExportType.Bracket);
bracketLog.Export(document, sheetData2, worksheetPart2);
}
else
{
var doubleKoLog = excelExportFactory.CreateExcelExport(ExcelExportFactory.ExcelExportType.DoubleKo);
doubleKoLog.Export(document, sheetData2, worksheetPart2);
}
document.Close();
}
}
/// <summary>
/// Import worksheet part from one document to another
/// </summary>
/// <param name="sourceDoc">Source excel document</param>
/// <param name="srcSheet">Source worksheet</param>
/// <param name="targetDoc">Target excel document</param>
/// <param name="tgtSheet">Position of imported worksheet</param>
/// <returns></returns>
protected static void ImportWorksheet(SpreadsheetDocument sourceDoc, Worksheet srcSheet, SpreadsheetDocument targetDoc, Worksheet tgtSheet)
{
// Import sheet format properties
tgtSheet.SheetFormatProperties = srcSheet.SheetFormatProperties.Clone() as SheetFormatProperties;
// Import dimension
tgtSheet.SheetDimension = srcSheet.SheetDimension.Clone() as SheetDimension;
// Imported style buffer
var dicStyles = new Dictionary<uint, uint>();
// Import columns
var srcCols = srcSheet.GetFirstChild<Columns>();
if (srcCols != null)
{
var tgtCols = tgtSheet.GetFirstChild<Columns>() ?? tgtSheet.AppendChild(new Columns());
foreach (var srcCol in srcCols.Descendants<Column>())
{
var tgtCol = (Column)srcCol.Clone();
tgtCols.AppendChild(tgtCol);
// Handle column style
if (srcCol.Style != null)
{
uint iNewIdx = 0;
if (dicStyles.ContainsKey(srcCol.Style.Value))
iNewIdx = dicStyles[srcCol.Style.Value];
else
{
iNewIdx = ImportStyle(sourceDoc, srcCol.Style.Value, targetDoc);
dicStyles[srcCol.Style.Value] = iNewIdx;
}
tgtCol.Style = new UInt32Value(iNewIdx);
}
}
}
// Import sheet data
var srcSheetData = srcSheet.GetFirstChild<SheetData>();
if (srcSheetData != null)
{
var tgtSheetData = tgtSheet.GetFirstChild<SheetData>();
if (tgtSheetData == null)
{
tgtSheetData = tgtSheet.AppendChild(new SheetData()) as SheetData;
}
// Import rows
foreach (var row in srcSheetData.Elements<Row>())
{
var newRow = row.CloneNode(false) as Row;
tgtSheetData.AppendChild(newRow);
// Import row style
if (newRow.StyleIndex != null)
{
var id = newRow.StyleIndex.Value;
var newId = dicStyles.ContainsKey(id) ? dicStyles[id] : ImportStyle(sourceDoc, id, targetDoc);
dicStyles[id] = newId;
newRow.StyleIndex = new UInt32Value(newId);
}
// Import cells
foreach (var cell in row.Elements<Cell>())
{
var newCell = cell.Clone() as Cell;
if (newCell == null) throw new InvalidCastException("Cloned cell is not Cell!");
// Handle shared string
if (cell.CellValue != null && cell.DataType != null &&
cell.DataType.Value == CellValues.SharedString)
{
uint id = uint.Parse(cell.CellValue.InnerText);
uint newId = ImportSharedString(sourceDoc, id, targetDoc);
newCell.CellValue = new CellValue(newId.ToString(CultureInfo.InvariantCulture));
}
// Handle style
if (cell.StyleIndex != null)
{
uint id = uint.Parse(cell.StyleIndex.InnerText);
uint newId = dicStyles.ContainsKey(id) ? dicStyles[id] : ImportStyle(sourceDoc, id, targetDoc);
dicStyles[id] = newId;
newCell.StyleIndex = new UInt32Value(newId);
}
// Handle cell metadata
if (cell.CellMetaIndex != null)
{}
// Handle value metadata
if (cell.ValueMetaIndex != null)
{}
newRow.AppendChild(newCell);
}
}
}
// Import merge cells
var srcMergeCells = srcSheet.GetFirstChild<MergeCells>();
if (srcMergeCells != null)
{
tgtSheet.AppendChild(srcMergeCells.Clone() as MergeCells);
}
}
//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);
}
public void Save()
{
SpreadsheetDocument xl = SpreadsheetDocument.Create(FileOutputPath, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart <WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
uint rowIndex = 1;
Row rowHeaders = new Row {
RowIndex = rowIndex++
};
string[] headers = { "Codice Ordine", "Totale (€)", "Data & Ora" };
foreach (string header in headers)
{
Cell cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(header)
};
rowHeaders.AppendChild(cell);
}
sd.AppendChild(rowHeaders);
foreach (Order order in SavingOrders)
{
Row row = new Row {
RowIndex = rowIndex
};
Cell cell = new Cell
{
DataType = CellValues.Number,
CellValue = new CellValue(order.Id.ToString())
};
Cell cell2 = new Cell
{
DataType = CellValues.Number,
CellValue = new CellValue(order.TotalPrice.ToString("##.##")),
StyleIndex = 4
};
Cell cell3 = new Cell
{
DataType = CellValues.Date,
CellValue = new CellValue(order.DateTime.ToString("dd-MM-yyyy HH:mm:ss"))
};
row.Append(cell, cell2, cell3);
sd.AppendChild(row);
rowIndex++;
}
Row rowFine = new Row {
RowIndex = rowIndex
};
//Cell cellTextSum = new Cell
//{
// DataType = CellValues.String,
// CellValue = new CellValue("Totale (€):"),
//};
Cell cellSum = new Cell
{
DataType = CellValues.Number,
CellValue = new CellValue(SavingOrders.Sum(x => x.TotalPrice).ToString("##.##")),
CellReference = "B" + rowIndex
};
rowFine.AppendChild(cellSum);
sd.AppendChild(rowFine);
ws.AppendChild(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet
{
Name = Path.GetFileNameWithoutExtension(FileOutputPath),
SheetId = 1,
Id = wbp.GetIdOfPart(wsp)
};
sheets.AppendChild(sheet);
wb.Append(fv, sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}
public void CreateExcelDoc()
{
try
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Worksheet worksheet = worksheetPart.Worksheet = new Worksheet();
//worksheetPart.Worksheet.Save();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Report"
};
sheets.Append(sheet);
Columns columns = new Columns();
for (uint i = 0; i < 19; i++)
{
columns.AppendChild(new Column
{
Min = i + 1,
Max = i + 1,
Width = 15,
BestFit = true,
//CustomWidth = true
});
}
worksheet.AppendChild(columns);
workbookPart.Workbook.Save();
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = ExcelUtils.GenerateStylesheet();
stylePart.Stylesheet.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
Row row = new Row();
MergeCells mergeCells;
if (worksheet.Elements <MergeCells>().Count() > 0)
{
mergeCells = worksheet.Elements <MergeCells>().First();
}
else
{
mergeCells = new MergeCells();
if (worksheet.Elements <CustomChartsheetView>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements <CustomChartsheetView>().First());
}
else
{
worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.GetFirstChild <SheetData>());
}
}
row.Append(
ExcelUtils.ConstructCell("Счет", CellValues.String, 2),
ExcelUtils.ConstructCell("Наименование", CellValues.String, 2),
ExcelUtils.ConstructCell("Инвентарный номер", CellValues.String, 2),
ExcelUtils.ConstructCell("КФО", CellValues.String, 2),
ExcelUtils.ConstructCell("Сальдо на начало периода", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Обороты за период", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Сальдо на конец периода", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Расположение", CellValues.String, 2),
ExcelUtils.ConstructCell("Комментарий", CellValues.String, 2),
ExcelUtils.ConstructCell("Дата обновления", CellValues.String, 2)
);
sheetData.Append(row);
row = new Row();
row.Append(
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Дебет", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Кредит", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Дебет", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Кредит", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Дебет", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Кредит", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
}
);
sheetData.Append(row);
row = new Row();
row.Append(
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
ExcelUtils.ConstructCell("Сумма", CellValues.String, 2),
ExcelUtils.ConstructCell("Количество", CellValues.String, 2),
ExcelUtils.ConstructCell("Сумма", CellValues.String, 2),
ExcelUtils.ConstructCell("Количество", CellValues.String, 2),
ExcelUtils.ConstructCell("Сумма", CellValues.String, 2),
ExcelUtils.ConstructCell("Количество", CellValues.String, 2),
ExcelUtils.ConstructCell("Сумма", CellValues.String, 2),
ExcelUtils.ConstructCell("Количество", CellValues.String, 2),
ExcelUtils.ConstructCell("Сумма", CellValues.String, 2),
ExcelUtils.ConstructCell("Количество", CellValues.String, 2),
ExcelUtils.ConstructCell("Сумма", CellValues.String, 2),
ExcelUtils.ConstructCell("Количество", CellValues.String, 2),
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
},
new Cell()
{
StyleIndex = 2
});
sheetData.Append(row);
List <MergeCell> mergeCellsList = new List <MergeCell>()
{
new MergeCell {
Reference = "A1:A3"
},
new MergeCell {
Reference = "B1:B3"
},
new MergeCell {
Reference = "C1:C3"
},
new MergeCell {
Reference = "D1:D3"
},
new MergeCell {
Reference = "E1:H1"
},
new MergeCell {
Reference = "I1:L1"
},
new MergeCell {
Reference = "M1:P1"
},
new MergeCell {
Reference = "Q1:Q3"
},
new MergeCell {
Reference = "R1:R3"
},
new MergeCell {
Reference = "S1:S3"
},
new MergeCell {
Reference = "E2:F2"
},
new MergeCell {
Reference = "G2:H2"
},
new MergeCell {
Reference = "I2:J2"
},
new MergeCell {
Reference = "K2:L2"
},
new MergeCell {
Reference = "M2:N2"
},
new MergeCell {
Reference = "O2:P2"
}
};
mergeCells.Append(mergeCellsList);
worksheetPart.Worksheet.Save();
}
}
catch (Exception e)
{
Debug.Print(e.Message);
}
finally
{
GC.Collect();
}
}
//void InitBasicStylePart(WorkbookPart workbookPart) {
// WorkbookStylesPart stylesPart;
// if (!workbookPart.GetPartsOfType<WorkbookStylesPart>().Any()) {
// stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
// stylesPart.Stylesheet = new Stylesheet();
// var stylesheet = stylesPart.Stylesheet;
// stylesheet.Fonts = new DocumentFormat.OpenXml.Spreadsheet.Fonts(
// new Font(
// new FontSize() { Val = 11 },
// new Color() { Theme = 1 }
// )
// );
// stylesheet.CellStyleFormats = new CellStyleFormats();
// stylesheet.CellStyleFormats.Append(new CellFormat());
// stylesheet.CellFormats = new CellFormats();
// }
// var cellFormat = stylesheet.CellFormats.Elements<CellFormat>().FirstOrDefault(cf => cf.FormatId == cellStyle.FormatId)
// ?? stylesheet.CellFormats.AppendChild(new CellFormat() {
// FormatId = cellStyle.FormatId,
// });
// if (stylesheet.CellStyles == null) {
// stylesheet.CellStyles = new CellStyles();
// }
// var cellStyles = stylesheet.CellStyles;
// var cellStyle = cellStyles.Elements<CellStyle>().FirstOrDefault(cs => cs.Name == "Hyperlink")
// ?? cellStyles.AppendChild(new CellStyle() {
// Name = "Hyperlink",
// BuiltinId = 8,
// FormatId = 0 //index 0 from cellstyleformats
// });
//}
void Save(SpreadsheetDocument spreadsheetDocument)
{
//Create workbook parts
var workbookPart = spreadsheetDocument.AddWorkbookPart();
//Sets workbook
var workbook = new Workbook();
workbookPart.Workbook = workbook;
//Shared string
var sharedStringPart = workbookPart.AddNewPart <SharedStringTablePart>();
sharedStringPart.SharedStringTable = new SharedStringTable();
//Set theme
using (var stream = GetType().GetTypeInfo().Assembly.GetManifestResourceStream("Genexcel.Resources.Office.theme1.xml")) {
using (var reader = new StreamReader(stream)) {
var xml = reader.ReadToEnd();
workbookPart.AddNewPart <ThemePart>();
workbookPart.ThemePart.Theme = new Theme(xml);
}
}
//Set styles
using (var stream = GetType().GetTypeInfo().Assembly.GetManifestResourceStream("Genexcel.Resources.Office.styles.xml")) {
using (var reader = new StreamReader(stream)) {
var xml = reader.ReadToEnd();
workbookPart.AddNewPart <WorkbookStylesPart>();
workbookPart.WorkbookStylesPart.Stylesheet = new Stylesheet(xml);
}
}
//Adiciona lista sheets
var sheets = workbook.AppendChild(new Sheets());
//Adiciona as planilhas ao workbook
uint sheetId = 1;
int sharedStringsIndex = 0;
foreach (var s in _sheets)
{
//Criar worksheet part no workbookpart
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var worksheet = new Worksheet();
//Columns
if (s.HasCustomColumn)
{
var columns = new Columns();
worksheet.Append(columns);
Column currentColElement = null;
Models.Column currentColModel = new Models.Column(); //fake current
for (uint i = 0; i < s.Columns.Length; i++)
{
var col = s.Columns[i];
if (col == currentColModel)
{
currentColElement.Max = i + 1;
}
else
{
currentColElement = new Column()
{
//Style = 1,
Min = i + 1,
Max = i + 1,
//CustomWidth = false,
Width = Models.Column.DEFAULT_WIDTH
};
if (col != null)
{
currentColElement.CustomWidth = true;
currentColElement.Width = col.Width;
}
columns.Append(currentColElement);
}
currentColModel = col;
}
//Column currentColumnElement = null;
//Models.Column currentColumnModel = null;
//foreach (var col in s.Columns) {
// Column colElement;
// if(col == currentColumnModel) { colElement = currentColumnElement }
// if(col == null) {
// }
// columns.Append(new Column() {
// Min = (uint)col.Min,
// Max = (uint)col.Max,
// Width = col.Width,
// Style = 1,
// CustomWidth = true
// });
//}
}
var sheetData = new SheetData();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
var name = s.Name ?? "Plan";
name = name.Length > _sheetNameLengthLimit?
name.Substring(0, _sheetNameLengthLimit) :
name;
// Append a new worksheet and associate it with the workbook.
var sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = sheetId++,
Name = name
};
sheets.Append(sheet);
Dictionary <uint, Row> dctRows = new Dictionary <uint, Row>();
Dictionary <string, Cell> dctCells = new Dictionary <string, Cell>();
foreach (var c in s.GetCells())
{
// Insert cell A1 into the new worksheet.
Cell cell; // = InsertCellInWorksheet(ColTranslate(c.Col), (uint)c.Row, worksheetPart);
//Worksheet worksheet = worksheetPart.Worksheet;
//SheetData sheetData = worksheet.GetFirstChild<SheetData>();
var columnName = ColTranslate(c.Col);
var rowIndex = (uint)c.Row;
string cellReference = columnName + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
Row row;
if (dctRows.ContainsKey(rowIndex))
{
row = dctRows[rowIndex];
}
//else if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0) {
// row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
//}
else
{
row = new Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
dctRows[rowIndex] = row;
}
// If there is not a cell with the specified column name, insert one.
//if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0) {
//return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
//}
if (dctCells.ContainsKey(cellReference))
{
cell = dctCells[cellReference];
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
//Cell refCell = null;
//foreach (var cell in row.Elements<Cell>()) {
// if (string.Compare(cell.CellReference.Value, cellReference, true) > 0) {
// refCell = cell;
// break;
// }
//}
var newCell = new Cell()
{
CellReference = cellReference
};
//row.InsertBefore(newCell, refCell);
row.Append(newCell);
dctCells[cellReference] = newCell;
//worksheet.Save();
cell = newCell;
}
var value = c.Value;
if (value is string)
{
int index;
var str = value.ToString();
//str = "TESTE VELOCIDADE";
if (_dctSharedStrings.ContainsKey(str))
{
index = _dctSharedStrings[str];
}
else
{
index = sharedStringsIndex++;
sharedStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(str)));
//shareStringPart.SharedStringTable.Save();
_dctSharedStrings[str] = index;
}
//int index = InsertSharedStringItem(value.ToString(), sharedStringPart);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
else if (value is int ||
value is decimal ||
value is long ||
value is short ||
value is double ||
value is float ||
value is byte)
{
var toString = value.GetType().GetTypeInfo()
.GetDeclaredMethods("ToString")
.First(m => m.GetParameters().Any(p => p.ParameterType == typeof(IFormatProvider))); //.GetMethod("ToString", new Type[] { typeof(CultureInfo) }).GetMethodInfo();
var formattedValue = toString.Invoke(value, new object[] { new CultureInfo("en-US") }).ToString();
cell.CellValue = new CellValue(formattedValue);
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
if (!string.IsNullOrWhiteSpace(c.Hyperlink))
{
var rId = $"r{Guid.NewGuid().ToString()}";
//if (workbookPart.GetPartsOfType<Relat>().Any()) {
// shareStringPart = worksheet.getp.GetPartsOfType<SharedStringTablePart>().First();
//} else {
// shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
//}
var rel = worksheetPart.AddHyperlinkRelationship(new Uri(c.Hyperlink), true, rId);
var hyperlinks = worksheet.Elements <Hyperlinks>().FirstOrDefault();
if (hyperlinks == null)
{
hyperlinks = worksheet.AppendChild(new Hyperlinks());
}
hyperlinks.Append(new DocumentFormat.OpenXml.Spreadsheet.Hyperlink()
{
Reference = cell.CellReference,
Id = rId
});
cell.StyleIndex = 2; //Hyperlink, should be an enum
}
}
//Charts
foreach (var ch in s.Charts)
{
//https://msdn.microsoft.com/en-us/library/office/cc820055.aspx#How the Sample Code Works
// Add a new drawing to the worksheet.
var drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
worksheetPart.Worksheet.Append(new Drawing()
{
Id = worksheetPart.GetIdOfPart(drawingsPart)
});
worksheetPart.Worksheet.Save();
var chartPart = drawingsPart.AddNewPart <ChartPart>();
var chartSpace = new ChartSpace();
chartPart.ChartSpace = chartSpace;
chartSpace.Append(new Date1904()
{
Val = false
});
chartSpace.Append(new EditingLanguage()
{
Val = "en-US"
});
chartSpace.Append(new RoundedCorners()
{
Val = false
});
var chart = chartSpace.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Chart());
//chartSpace.Append(new ChartShapeProperties(
// new SolidFill(
// new SchemeColor() { Val = SchemeColorValues.Background1 }
// ),
// new DocumentFormat.OpenXml.Drawing.Outline(
// new SolidFill(
// new SchemeColor(
// new LuminanceModulation() { Val = 15000 },
// new LuminanceOffset() { Val = 85000 }
// ) { Val = SchemeColorValues.Text1 }
// )
// ) {
// Width = 9525,
// CapType = LineCapValues.Flat,
// CompoundLineType = CompoundLineValues.Single,
// Alignment = PenAlignmentValues.Center
// }
// ));
//Dont know
chart.AppendChild(new Title(
new Overlay()
{
Val = false
},
new ChartShapeProperties(
new NoFill(),
new DocumentFormat.OpenXml.Drawing.Outline(new NoFill()),
new EffectList()
),
new DocumentFormat.OpenXml.Drawing.Charts.TextProperties(
new BodyProperties()
{
Rotation = 0,
UseParagraphSpacing = true,
VerticalOverflow = TextVerticalOverflowValues.Ellipsis,
Vertical = TextVerticalValues.Horizontal,
Wrap = TextWrappingValues.Square,
Anchor = TextAnchoringTypeValues.Center,
AnchorCenter = true,
},
new Paragraph(
new ParagraphProperties(
new DefaultRunProperties(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 65000
},
new LuminanceOffset()
{
Val = 35000
}
)
{
Val = SchemeColorValues.Text1
}
),
new LatinFont()
{
Typeface = "+mn-lt"
},
new EastAsianFont()
{
Typeface = "+mn-ea"
},
new ComplexScriptFont()
{
Typeface = "+mn-cs"
}
)
{
FontSize = 1400,
Bold = false,
Italic = false,
Underline = TextUnderlineValues.None,
Strike = TextStrikeValues.NoStrike,
Kerning = 1200,
Baseline = 0
}
)
)
)
));
//Allow showing title on top
chart.AppendChild(new AutoTitleDeleted()
{
Val = false
});
//Create plot area
var plotArea = chart.AppendChild(new PlotArea());
var layout = plotArea.AppendChild(new Layout());
if (ch is Models.AreaChart || ch is Models.BarChart)
{
#region init chart
var chObject = ch as Models.Chart;
OpenXmlCompositeElement chartElement;
if (ch is Models.AreaChart)
{
chartElement = plotArea.AppendChild(
//Dont know what extensions are for
new AreaChart(new Grouping()
{
Val = GroupingValues.Standard
})
);
}
else
{
chartElement = plotArea.AppendChild(
//Dont know what extensions are for
new BarChart(
new BarDirection()
{
Val = BarDirectionValues.Column
},
new BarGrouping()
{
Val = BarGroupingValues.Clustered
})
);
}
chartElement.AppendChild(new VaryColors()
{
Val = false
});
#endregion
#region data
foreach (var dts in chObject.Data.Datasets)
{
var index = (uint)chObject.Data.Datasets.IndexOf(dts);
OpenXmlCompositeElement chartSeries;
if (ch is Models.AreaChart)
{
chartSeries = chartElement.AppendChild(new AreaChartSeries());
}
else
{
chartSeries = chartElement.AppendChild(new BarChartSeries());
}
chartSeries.Append(
new Index()
{
Val = index
},
new Order()
{
Val = index
},
new SeriesText()
{
NumericValue = new NumericValue(dts.Title)
},
new ChartShapeProperties(
new SolidFill(new SchemeColor()
{
Val = SchemeColorValues.Accent1
}),
new DocumentFormat.OpenXml.Drawing.Outline(new NoFill()),
new EffectList()
)
);
if (ch is Models.BarChart)
{
chartSeries.Append(new InvertIfNegative()
{
Val = false
});
}
//Eixo x (labels)
var categoryAxisData = chartSeries.AppendChild(new CategoryAxisData());
var strLit = categoryAxisData.AppendChild(new StringLiteral());
strLit.Append(new PointCount()
{
Val = (uint)chObject.Data.Labels.Count
});
foreach (var lbl in chObject.Data.Labels)
{
strLit.AppendChild(new StringPoint()
{
Index = (uint)chObject.Data.Labels.IndexOf(lbl)
})
.Append(new NumericValue(lbl));
}
var values = chartSeries.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values());
var numLit = values.AppendChild(new NumberLiteral());
numLit.Append(new FormatCode("General"));
numLit.Append(new PointCount()
{
Val = (uint)chObject.Data.Labels.Count
});
foreach (var lbl in chObject.Data.Labels)
{
var lblIndex = chObject.Data.Labels.IndexOf(lbl);
var val = dts.Data.Count > lblIndex ? dts.Data[lblIndex] : 0;
numLit.AppendChild(new NumericPoint()
{
Index = (uint)chObject.Data.Labels.IndexOf(lbl)
})
.Append(new NumericValue(val.ToString()));
}
// numLit.AppendChild(new NumericPoint() { Index = new UInt32Value(0u) })
// .Append
//(new NumericValue("28"));
}
#endregion
#region options?
//Not required for a valid xlsx
chartElement
.AppendChild(
new DataLabels(
new ShowLegendKey()
{
Val = false
},
new ShowValue()
{
Val = false
},
new ShowCategoryName()
{
Val = false
},
new ShowSeriesName()
{
Val = false
},
new ShowPercent()
{
Val = false
},
new ShowBubbleSize()
{
Val = false
}
)
);
if (ch is Models.BarChart)
{
chartElement.Append(new GapWidth()
{
Val = 219
});
chartElement.Append(new Overlap()
{
Val = -27
});
}
#endregion
#region Axis
chartElement.Append(new AxisId()
{
Val = 48650112u
});
chartElement.Append(new AxisId()
{
Val = 48672768u
});
// Add the Category Axis.
var catAx = plotArea
.AppendChild(
new CategoryAxis(
new AxisId()
{
Val = 48650112u
},
new Scaling(
new Orientation()
{
Val = DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax
}
),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = AxisPositionValues.Bottom
},
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = "General",
SourceLinked = true
},
new MajorTickMark()
{
Val = ch is Models.AreaChart ? TickMarkValues.Outside : TickMarkValues.None
},
new MinorTickMark()
{
Val = TickMarkValues.None
},
new TickLabelPosition()
{
Val = TickLabelPositionValues.NextTo
},
new ChartShapeProperties(
new NoFill(),
new DocumentFormat.OpenXml.Drawing.Outline(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 15000
},
new LuminanceOffset()
{
Val = 85000
}
)
{
Val = SchemeColorValues.Text1
}
)
)
{
Width = 9525,
CapType = LineCapValues.Flat,
CompoundLineType = CompoundLineValues.Single,
Alignment = PenAlignmentValues.Center
}
),
new DocumentFormat.OpenXml.Drawing.Charts.TextProperties(
new BodyProperties()
{
Rotation = -60000000,
UseParagraphSpacing = true,
VerticalOverflow = TextVerticalOverflowValues.Ellipsis,
Vertical = TextVerticalValues.Horizontal,
Wrap = TextWrappingValues.Square,
Anchor = TextAnchoringTypeValues.Center,
AnchorCenter = true,
},
new Paragraph(
new ParagraphProperties(
new DefaultRunProperties(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 65000
},
new LuminanceOffset()
{
Val = 35000
}
)
{
Val = SchemeColorValues.Text1
}
),
new LatinFont()
{
Typeface = "+mn-lt"
},
new EastAsianFont()
{
Typeface = "+mn-ea"
},
new ComplexScriptFont()
{
Typeface = "+mn-cs"
}
)
{
FontSize = 900,
Bold = false,
Italic = false,
Underline = TextUnderlineValues.None,
Strike = TextStrikeValues.NoStrike,
Kerning = 1200,
Baseline = 0
}
),
new EndParagraphRunProperties()
{
Language = "en-US"
}
)
),
new CrossingAxis()
{
Val = 48672768U
},
new Crosses()
{
Val = CrossesValues.AutoZero
},
new AutoLabeled()
{
Val = true
},
new LabelAlignment()
{
Val = LabelAlignmentValues.Center
},
new LabelOffset()
{
Val = 100
},
new NoMultiLevelLabels()
{
Val = false
}
)
);
// Add the Value Axis.
var valAx = plotArea
.AppendChild(
new ValueAxis(
new AxisId()
{
Val = 48672768u
},
new Scaling(new Orientation()
{
Val = DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax
}),
new Delete()
{
Val = false
},
new AxisPosition()
{
Val = AxisPositionValues.Left
},
new MajorGridlines(
new ChartShapeProperties(
new DocumentFormat.OpenXml.Drawing.Outline(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 15000
},
new LuminanceOffset()
{
Val = 85000
}
)
{
Val = SchemeColorValues.Text1
}
),
new Round()
)
{
Width = 9525,
CapType = LineCapValues.Flat,
CompoundLineType = CompoundLineValues.Single,
Alignment = PenAlignmentValues.Center
},
new EffectList()
)
),
new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
{
FormatCode = "General",
SourceLinked = true
},
new MajorTickMark()
{
Val = TickMarkValues.None
},
new MinorTickMark()
{
Val = TickMarkValues.None
},
new TickLabelPosition()
{
Val = TickLabelPositionValues.NextTo
},
new ChartShapeProperties(
new NoFill(),
new DocumentFormat.OpenXml.Drawing.Outline(new NoFill()),
new EffectList()
),
new DocumentFormat.OpenXml.Drawing.Charts.TextProperties(
new BodyProperties()
{
Rotation = -60000000,
UseParagraphSpacing = true,
VerticalOverflow = TextVerticalOverflowValues.Ellipsis,
Vertical = TextVerticalValues.Horizontal,
Wrap = TextWrappingValues.Square,
Anchor = TextAnchoringTypeValues.Center,
AnchorCenter = true,
},
new Paragraph(
new ParagraphProperties(
new DefaultRunProperties(
new SolidFill(
new SchemeColor(
new LuminanceModulation()
{
Val = 65000
},
new LuminanceOffset()
{
Val = 35000
}
)
{
Val = SchemeColorValues.Text1
}
),
new LatinFont()
{
Typeface = "+mn-lt"
},
new EastAsianFont()
{
Typeface = "+mn-ea"
},
new ComplexScriptFont()
{
Typeface = "+mn-cs"
}
)
{
FontSize = 900,
Bold = false,
Italic = false,
Underline = TextUnderlineValues.None,
Strike = TextStrikeValues.NoStrike,
Kerning = 1200,
Baseline = 0
}
),
new EndParagraphRunProperties()
{
Language = "en-US"
}
)
),
new CrossingAxis()
{
Val = 48650112U
},
new Crosses()
{
Val = CrossesValues.AutoZero
},
new CrossBetween()
{
Val = ch is Models.AreaChart ? CrossBetweenValues.MidpointCategory : CrossBetweenValues.Between
})
);
// Add the chart Legend.
//Legend legend = chart.AppendChild(new Legend(new LegendPosition() { Val = new EnumValue<LegendPositionValues>(LegendPositionValues.Right) },
// new Layout()));
chart.Append(new PlotVisibleOnly()
{
Val = true
});
chart.Append(new DisplayBlanksAs()
{
Val = ch is Models.AreaChart ? DisplayBlanksAsValues.Zero : DisplayBlanksAsValues.Gap
});
chart.Append(new ShowDataLabelsOverMaximum()
{
Val = false
});
#endregion
// Save the chart part.
chartPart.ChartSpace.Save();
}
#region position?
// Position the chart on the worksheet using a TwoCellAnchor object.
drawingsPart.WorksheetDrawing = new WorksheetDrawing();
TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor());
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId("0"),
new ColumnOffset("0"),
new RowId("0"),
new RowOffset("0")));
twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("8"),
new ColumnOffset("0"),
new RowId("15"),
new RowOffset("0")));
// Append a GraphicFrame to the TwoCellAnchor object.
var graphicFrame = twoCellAnchor.AppendChild(new DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame());
graphicFrame.Macro = "";
graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties()
{
Id = new UInt32Value(2u),
Name = "Chart 1"
}, new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));
graphicFrame.Append(new Transform(new Offset()
{
X = 0L, Y = 0L
},
new Extents()
{
Cx = 0L, Cy = 0L
}));
graphicFrame.Append(new Graphic(new GraphicData(new ChartReference()
{
Id = drawingsPart.GetIdOfPart(chartPart)
})
{
Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
}));
twoCellAnchor.Append(new ClientData());
#endregion
// Save the WorksheetDrawing object.
drawingsPart.WorksheetDrawing.Save();
}
}
var validator = new OpenXmlValidator();
var errors = validator.Validate(spreadsheetDocument);
if (errors.Any())
{
var sbError = new StringBuilder();
sbError.Append("ERROR: ");
foreach (var e in errors)
{
sbError.Append($"***{e.Node.ToString()}:{e.Description}***");
}
throw new Exception(sbError.ToString());
}
workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
public async Task <System.Net.Http.HttpResponseMessage> GenerateTemplate()
{
Contract.Ensures(Contract.Result <Task <HttpResponseMessage> >() != null);
// get template entities
var templateEntities = await this.GetNamedEntities(cTemplateEntityName);
if (templateEntities == null)
{
return(this.Request.CreateResponse(HttpStatusCode.NotFound));
}
#region Generate Excel File OpenXMLSDK-MOT
string fileSaveLocation = Path.GetTempFileName();
var properties = templateEntities.ElementType.GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(p => p.Name != "ID");
using (var workbook = SpreadsheetDocument.Create(fileSaveLocation, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
WorkbookStylesPart stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = CreateStylesheet();
stylesPart.Stylesheet.Save();
workbook.WorkbookPart.Workbook = new Workbook
{
Sheets = new Sheets()
};
#region Sheet 1
WorksheetPart worksheetPart1 = workbookPart.AddNewPart <WorksheetPart>();
Worksheet workSheet1 = new Worksheet();
SheetData sheetData1 = new SheetData();
// the data for sheet 1
//Row rowInSheet1 = new Row();
//Cell emptyCell = CreateTextCell(cellHeader, index, "");
//rowInSheet1.Append(emptyCell);
//sheetData1.Append(rowInSheet1);
workSheet1.AppendChild(sheetData1);
worksheetPart1.Worksheet = workSheet1;
Sheet sheet1 = new Sheet()
{
Id = workbook.WorkbookPart.GetIdOfPart(worksheetPart1),
SheetId = 1,
Name = m_ExcelTemplateSheetOneName
};
workbook.WorkbookPart.Workbook.Sheets.Append(sheet1);
#endregion
#region Sheet 2
WorksheetPart worksheetPart2 = workbookPart.AddNewPart <WorksheetPart>();
Worksheet workSheet2 = new Worksheet();
SheetFormatProperties sheetFormatProperties = new SheetFormatProperties()
{
DefaultColumnWidth = 25.00D, DefaultRowHeight = 0D
};
workSheet2.SheetFormatProperties = sheetFormatProperties;
SheetData sheetData2 = new SheetData();
// the data for sheet 2
Row headerRow = new Row();
List <String> columns = new List <string>();
foreach (PropertyInfo column in properties)
{
columns.Add(column.Name);
Cell cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.Name),
StyleIndex = Convert.ToUInt32(1)
};
headerRow.AppendChild(cell);
}
sheetData2.AppendChild(headerRow);
foreach (var entity in templateEntities)
{
Row newRow = new Row();
foreach (string col in columns)
{
Cell cell = new Cell
{
DataType = CellValues.String
};
object value = templateEntities.ElementType.GetProperty(col).GetValue(entity);
cell.CellValue = new CellValue(value == null ? string.Empty : value.ToString());
newRow.AppendChild(cell);
}
sheetData2.AppendChild(newRow);
}
workSheet2.AppendChild(sheetData2);
worksheetPart2.Worksheet = workSheet2;
Sheet sheet2 = new Sheet()
{
Id = workbook.WorkbookPart.GetIdOfPart(worksheetPart2),
SheetId = 2,
Name = m_ExcelTemplateSheetTwoName
};
workbook.WorkbookPart.Workbook.Sheets.Append(sheet2);
#region Protection
SheetProtection sheetProtection = new SheetProtection
{
Sheet = true,
Objects = true,
Scenarios = true,
Password = GetSheetPassword(m_ExcelTemplateSheetPassword)
};
worksheetPart2.Worksheet.InsertAfter(sheetProtection, sheetData2);
#endregion
#endregion
}
var bytes = File.ReadAllBytes(fileSaveLocation);
File.Delete(fileSaveLocation);
var stream = new MemoryStream(bytes);
if (stream == null)
{
return(this.Request.CreateResponse(HttpStatusCode.NotFound));
}
#endregion
var mediaNameStr = "Template.xlsx";
var mediaTypeStr = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
var mediaType = new MediaTypeHeaderValue(mediaTypeStr);
// get the range and stream media type
var range = this.Request.Headers.Range;
HttpResponseMessage response;
if (range == null)
{
// if the range header is present but null, then the header value must be invalid
if (this.Request.Headers.Contains("Range"))
{
return(this.Request.CreateErrorResponse(HttpStatusCode.RequestedRangeNotSatisfiable, "GenerateTemplate"));
}
// if no range was requested, return the entire stream
response = this.Request.CreateResponse(HttpStatusCode.OK);
response.Headers.AcceptRanges.Add("bytes");
response.Content = new StreamContent(stream);
response.Content.Headers.ContentType = mediaType;
response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
mediaNameStr = System.Web.HttpUtility.UrlEncode(mediaNameStr);
response.Content.Headers.ContentDisposition.FileName = mediaNameStr;
return(response);
}
var partialStream = EnsureStreamCanSeek(stream);
response = this.Request.CreateResponse(HttpStatusCode.PartialContent);
response.Headers.AcceptRanges.Add("bytes");
try
{
// return the requested range(s)
response.Content = new ByteRangeStreamContent(partialStream, range, mediaType);
}
catch (InvalidByteRangeException exception)
{
DynamicLogger.Instance.WriteLoggerLogError("GenerateTemplate", exception);
response.Dispose();
return(Request.CreateErrorResponse(exception));
}
// change status code if the entire stream was requested
if (response.Content.Headers.ContentLength.Value == partialStream.Length)
{
response.StatusCode = HttpStatusCode.OK;
}
return(response);
}
/// <summary>Saves one worksheet in the spreadsheet. You can add one or more.</summary>
/// <typeparam name="T">The class type that contains data that will be added to the worksheet</typeparam>
/// <param name="dataList">The data that will be added to the worksheet</param>
/// <param name="sheetName">The name of the worksheet</param>
/// <param name="createHeaderRow">Indicates if the worksheet has a header row.</param>
/// <returns>A worksheet</returns>
public void AddWorksheet <T>(List <T> dataList, string sheetName, bool createHeaderRow = true) where T : class
{
if (dataList == null)
{
throw new ArgumentException("You must specify a list of data!");
}
if (string.IsNullOrEmpty(sheetName))
{
sheetName = string.Format("sheet{0}", _spreadSheetId);
}
if (_spreadSheet == null)
{
_ms = new MemoryStream();
_spreadSheet = SpreadsheetDocument.Create(_ms, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
_workbookpart = _spreadSheet.AddWorkbookPart();
_workbookpart.Workbook = new Workbook();
SharedStringTablePart shareStringPart = _workbookpart.AddNewPart <SharedStringTablePart>();
shareStringPart.SharedStringTable = new SharedStringTable();
// Add Sheets to the Workbook.
_sheets = _spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets());
// Add minimal Stylesheet
// http://stackoverflow.com/questions/2792304/how-to-insert-a-date-to-an-open-xml-worksheet
var stylesPart = _workbookpart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet
{
Fonts = new Fonts(new Font()),
Fills = new Fills(new Fill()),
Borders = new Borders(new Border()),
CellStyleFormats = new CellStyleFormats(new CellFormat()),
CellFormats = new CellFormats(
// empty one for index 0, seems to be required
new CellFormat()),
NumberingFormats = new NumberingFormats()
};
}
List <ClassToExcelColumn> columns = CreateColumns(sheetName, typeof(T));
CreateAdditionalCellFormats(columns);
// For each Excel sheet (that has separate data)
// - a separate WorkSheetPart object is needed
// - a separate WorkSheet object is needed
// - a separate SheetData object is needed
// - a separate Sheet object is needed
// Ref: http://stackoverflow.com/a/22230453/97803
// Also see: https://msdn.microsoft.com/en-us/library/office/cc881781.aspx
// Add a WorkSheetPart
WorksheetPart worksheetPart = _workbookpart.AddNewPart <WorksheetPart>();
// Append Sheet
Sheet sheet = new Sheet
{
Id = _spreadSheet.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = _spreadSheetId++,
Name = sheetName
};
_sheets.Append(sheet);
// Add a Worksheet
Worksheet worksheet = new Worksheet();
worksheetPart.Worksheet = worksheet;
// Create and add SheetData
SheetData sheetData = CreateSheetData(dataList, createHeaderRow, columns);
worksheet.AppendChild(sheetData);
worksheet.Save();
}
private void Consolidate(object sender, RoutedEventArgs e)
{
if (lstFileList.Items.Count == 0)
{
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.DefaultExt = "xlsx";
if (sfd.ShowDialog() == true)
{
string filename = sfd.FileName;
Dictionary <string, Dictionary <string, Dictionary <string, string> > > MetricValuesByYearByMetricByFile = new Dictionary <string, Dictionary <string, Dictionary <string, string> > >();
int index = 1;
foreach (object oFileName in lstFileList.Items)
{
string FileTitle = "Run " + index++;
MetricValuesByYearByMetricByFile.Add(FileTitle, ProcessFile(oFileName.ToString()));
}
Dictionary <string, Dictionary <string, Dictionary <string, string> > > MetricValuesByFileByYearByMetric = GetMetricValuesByFileByYearByMetric(MetricValuesByYearByMetricByFile);
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filename, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
uint MetricID = 1;
foreach (string metric in MetricValuesByFileByYearByMetric.Keys)
{
WorksheetPart worksheetPart1 = workbookPart.AddNewPart <WorksheetPart>();
Worksheet worksheet1 = new Worksheet();
SheetData sheetData1 = new SheetData();
// the data for sheet 1
Row headerRow = new Row();
headerRow.Append(ConstructCell("Year", CellValues.String));
List <string> runFiles = new List <string>();
foreach (string file in MetricValuesByYearByMetricByFile.Keys)
{
headerRow.Append(ConstructCell(file, CellValues.String));
runFiles.Add(file);
}
headerRow.Append(ConstructCell("Average", CellValues.String));
sheetData1.Append(headerRow);
foreach (string year in MetricValuesByFileByYearByMetric[metric].Keys)
{
Row yearRow = new Row();
yearRow.Append(ConstructCell(year, CellValues.Number));
sheetData1.Append(yearRow);
double sum = 0;
int num = 0;
foreach (string filecursor in runFiles)
{
if (MetricValuesByFileByYearByMetric[metric][year].ContainsKey(filecursor) && !String.IsNullOrWhiteSpace(MetricValuesByFileByYearByMetric[metric][year][filecursor]))
{
yearRow.Append(ConstructCell(MetricValuesByFileByYearByMetric[metric][year][filecursor], CellValues.Number));
num++;
sum += MetricValuesByFileByYearByMetric[metric][year][filecursor].ConvertToDouble();
}
else
{
yearRow.Append(ConstructCell("", CellValues.Number));
}
}
if (num > 0)
{
yearRow.Append(ConstructCell((sum / num).ToString(), CellValues.Number));
}
}
worksheet1.AppendChild(sheetData1);
worksheetPart1.Worksheet = worksheet1;
Sheet sheet1 = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart1),
SheetId = MetricID,
Name = metric
};
MetricID++;
sheets.Append(sheet1);
worksheetPart1.Worksheet.Save();
}
// End: Code block for Excel sheet 1
/*foreach (string metricName in MetricValuesByFileByYearByMetric.Keys)
* {
* string relId = workbookPart.Workbook.Descendants<Sheet>().First(s => metricName.Equals(s.Name)).Id;
* WorksheetPart wsp = (WorksheetPart)workbookPart.GetPartById(relId);
*
* SheetData sheetData = wsp.Worksheet.AppendChild(new SheetData());
*
* Row headRow = new Row();
*
* headRow.Append(
* ConstructCell("Year", CellValues.String));
*
* foreach (string file in MetricValuesByYearByMetricByFile.Keys)
* {
* headRow.Append(ConstructCell(file, CellValues.String));
* }
*
*
* sheetData.AppendChild(headRow);
* worksheetPart.Worksheet.Save();
* }*/
//workbookPart.Workbook.Save();
}
System.Diagnostics.Process.Start(filename);
}
}
public static void CreateSpreadsheetWorkbook(string filepath, List <Student> students)
{
SpreadsheetDocument ssDoc = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookPart = ssDoc.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
Sheets sheets = ssDoc.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
//Begin: Sheet 1 Code
WorksheetPart worksheetPart1 = workbookPart.AddNewPart <WorksheetPart>();
Worksheet workSheet1 = new Worksheet();
SheetData sheetData1 = new SheetData();
// data for sheet 1
Row rowInSheet1 = new Row();
rowInSheet1.RowIndex = 2;
Cell cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue("Hello, My name is Ankita")
};
rowInSheet1.Append(cell);
sheetData1.Append(rowInSheet1);
workSheet1.AppendChild(sheetData1);
worksheetPart1.Worksheet = workSheet1;
Sheet sheet1 = new Sheet()
{
Id = ssDoc.WorkbookPart.GetIdOfPart(worksheetPart1),
SheetId = 1,
Name = "Sheet1"
};
sheets.Append(sheet1);
// End: Sheet 1 Code
// Begin: Code block for Excel sheet 2
WorksheetPart worksheetPart2 = workbookPart.AddNewPart <WorksheetPart>();
Worksheet workSheet2 = new Worksheet();
SheetData sheetData2 = new SheetData();
// the data for sheet 2
Row headerRow = new Row();
string[] headers = { "UniqueId", "StudentId", "FirstName", "LastName", "DateOfBirth", "isMe", "Age" };
foreach (var header in headers)
{
Cell c = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(header),
};
headerRow.Append(c);
}
sheetData2.Append(headerRow);
UInt32 startIndex = 2;
foreach (Student s in students)
{
Row r = new Row();
r.RowIndex = startIndex;
Cell uidCell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue("" + Guid.NewGuid())
};
r.Append(uidCell);
Cell studentIdCell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(s.StudentId)
};
r.Append(studentIdCell);
Cell firstNameCell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(s.FirstName)
};
r.Append(firstNameCell);
Cell lastNameCell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(s.LastName)
};
r.Append(lastNameCell);
Cell dobCell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(s.DateOfBirth)
};
r.Append(dobCell);
Cell isMeCell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue("" + s.MyRecord)
};
r.Append(isMeCell);
Cell AgeCell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue("" + s.Age)
};
r.Append(AgeCell);
sheetData2.Append(r);
startIndex++;
}
workSheet2.AppendChild(sheetData2);
worksheetPart2.Worksheet = workSheet2;
Sheet sheet2 = new Sheet()
{
Id = ssDoc.WorkbookPart.GetIdOfPart(worksheetPart2),
SheetId = 2,
Name = "Sheet2"
};
sheets.Append(sheet2);
// End: Code block for Excel sheet 2
workbookPart.Workbook.Save();
ssDoc.Close();
}
/// <summary>
/// Takes a List<List<XlsxValue>> as a matrix and adds an Excel Worksheet for that data
/// Style information will have been included in each XlsValue object
/// You may also specify an orientation at this point
/// </summary>
/// <param name="rowLists"></param>
/// <param name="sheetLabel"></param>
/// <param name="oval"></param>
public void InsertDataWorksheet(List <List <XlsxValue> > rowLists, string sheetLabel, OrientationValues oval = OrientationValues.Default, bool Autosize = true)
{
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = (uint)workbookPart.WorksheetParts.Count(), Name = sheetLabel
};
Worksheet workSheet = new Worksheet();
SheetData sheetData = new SheetData();
UInt32Value rowcounter = 1;
foreach (var list in rowLists)
{
// Add a row to the cell table.
Row row;
row = new Row()
{
RowIndex = rowcounter
};
sheetData.Append(row);
Cell refCell = null;
foreach (XlsxValue str in list)
{
Cell addCell = new Cell();
row.InsertAfter(addCell, refCell);
addCell.CellValue = new CellValue(str.Value);
//If the value was flagged as a potential number, then check if it parses as a number.If so, it will be a number in Xlsx
if (str.DataType == CellValues.Number)
{
if (!Double.TryParse(str.Value, out double result))
{
addCell.DataType = new EnumValue <CellValues>(CellValues.String);
}
}
else //otherwise we just allow it to be whatever type it was created as (default is CellValues.String)
{
addCell.DataType = str.DataType;
}
addCell.StyleIndex = (uint)str.StyleId;
refCell = addCell;
}
rowcounter++;
}
//Set column sizes, either automatically or with reference to each XlsxValue property
if (Autosize)
{
workSheet.Append(AutoSize(sheetData));
}
else
{
workSheet.Append(SetColumnSize(rowLists));
}
workSheet.AppendChild(sheetData);
worksheetPart.Worksheet = workSheet;
sheets.Append(sheet);
if (oval != OrientationValues.Default)
{
SetOrientation(worksheetPart, oval);
}
}
public void GroupExportTest()
{
// Alle Gruppenspiele hinzufügen
TournamentGroupLog.AddMatch(TournamentGroupLogMatch.Create(PLAYER_THREE, PLAYER_FOUR, 1, 1));
TournamentGroupLog.AddMatch(TournamentGroupLogMatch.Create(PLAYER_FIVE, PLAYER_FOUR, 1, 1));
TournamentGroupLog.AddMatch(TournamentGroupLogMatch.Create(PLAYER_THREE, PLAYER_SEVEN, 1, 1));
TournamentGroupLog.AddMatch(TournamentGroupLogMatch.Create(PLAYER_SIX, PLAYER_ONE, 1, 1));
TournamentGroupLog.AddMatch(TournamentGroupLogMatch.Create(PLAYER_FIVE, PLAYER_SEVEN, 1, 1));
TournamentGroupLog.AddMatch(TournamentGroupLogMatch.Create(PLAYER_FOUR, PLAYER_SEVEN, 1, 1));
TournamentGroupLog.AddMatch(TournamentGroupLogMatch.Create(PLAYER_ONE, PLAYER_TWO, 1, 1));
TournamentGroupLog.AddMatch(TournamentGroupLogMatch.Create(PLAYER_SIX, PLAYER_EIGHT, 1, 1));
TournamentGroupLog.AddMatch(TournamentGroupLogMatch.Create(PLAYER_SIX, PLAYER_TWO, 1, 1));
TournamentGroupLog.AddMatch(TournamentGroupLogMatch.Create(PLAYER_ONE, PLAYER_EIGHT, 1, 1));
TournamentGroupLog.AddMatch(TournamentGroupLogMatch.Create(PLAYER_FIVE, PLAYER_THREE, 1, 1));
TournamentGroupLog.AddMatch(TournamentGroupLogMatch.Create(PLAYER_EIGHT, PLAYER_TWO, 1, 1));
// Gruppen erzeugen
var groupA = new List <Player>();
groupA.Add(new Player(new Name(PLAYER_FIVE), new Identification(5)));
groupA.Add(new Player(new Name(PLAYER_THREE), new Identification(3)));
groupA.Add(new Player(new Name(PLAYER_FOUR), new Identification(4)));
groupA.Add(new Player(new Name(PLAYER_SEVEN), new Identification(7)));
var groupB = new List <Player>();
groupB.Add(new Player(new Name(PLAYER_SIX), new Identification(6)));
groupB.Add(new Player(new Name(PLAYER_ONE), new Identification(1)));
groupB.Add(new Player(new Name(PLAYER_EIGHT), new Identification(8)));
groupB.Add(new Player(new Name(PLAYER_TWO), new Identification(2)));
// Gruppen hinzufügen
TournamentGroupLog.Groups.Add(1, groupA);
TournamentGroupLog.Groups.Add(2, groupB);
// Dinge erzeugen, die wichtig für den Export sind
var fileName = "KnockOutStageGroupExportTest.xlsx";
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Groups
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
SheetData sheetdata = new SheetData();
if (TournamentGroupLog.Groups.Count > 0)
{
Worksheet worksheet = new Worksheet();
worksheet.AppendChild(sheetdata);
worksheetPart.Worksheet = worksheet;
Sheet sheet1 = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Groups"
};
sheets.Append(sheet1);
}
// Den eigentlichen Export durchführen
var excelExportFactory = new ExcelExportFactory();
var groupLog = excelExportFactory.CreateExcelExport(ExcelExportFactory.ExcelExportType.Groups);
groupLog.Export(document, sheetdata, worksheetPart);
// Daten, die aus der Datei benötigt werden, sodass die Tests durchgeführt werden können
var playerCell = CellFinder.GetCell(worksheetPart.Worksheet, 10, 2);
var firstValueCell = CellFinder.GetCell(worksheetPart.Worksheet, 2, 2);
var secondValueCell = CellFinder.GetCell(worksheetPart.Worksheet, 2, 3);
var firstGroupNameCell = CellFinder.GetCell(worksheetPart.Worksheet, 1, 14);
var groupWinnerCell = CellFinder.GetCell(worksheetPart.Worksheet, 1, 15);
var groupDifferenceCell = CellFinder.GetCell(worksheetPart.Worksheet, 2, 21);
var groupPointsCell = CellFinder.GetCell(worksheetPart.Worksheet, 3, 24);
int firstValue, secondValue;
firstValueCell.CellValue.TryGetInt(out firstValue);
secondValueCell.CellValue.TryGetInt(out secondValue);
// Tests, um zu überprüfen, ob der Export funktioniert hat.
Assert.IsTrue(playerCell != null && playerCell.DataType == CellValues.String);
Assert.IsTrue(firstValueCell != null && firstValueCell.DataType == CellValues.Number);
Assert.AreEqual(firstValue, 1);
Assert.AreEqual(secondValue, 1);
Assert.AreEqual(firstGroupNameCell.CellValue.Text, "Group 1");
Assert.IsTrue(groupWinnerCell != null && groupWinnerCell.DataType == CellValues.String);
Assert.IsTrue(groupDifferenceCell != null && groupDifferenceCell.DataType == CellValues.Number);
Assert.IsTrue(groupPointsCell != null && groupPointsCell.DataType == CellValues.Number);
}
}
public void WriteCessCategoryExcelFile(DataTable cessSummary, DataTable cessDetail, string path, string firstLine)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Begin: Code block for Excel sheet 1 // Cess Summary
WorksheetPart worksheetPart1 = workbookpart.AddNewPart <WorksheetPart>();
Worksheet workSheet1 = new Worksheet();
SheetData sheetData1 = new SheetData();
WorkbookStylesPart stylesPart1 = workbookpart.AddNewPart <WorkbookStylesPart>();
stylesPart1.Stylesheet = GenerateStyleSheet();
stylesPart1.Stylesheet.Save();
// the data for sheet 1 // Cess Summary
Row firstRow1 = new Row();
Cell dataCell1 = new Cell();
dataCell1.CellReference = "A1";
dataCell1.DataType = CellValues.String;
CellValue cellValue1 = new CellValue();
cellValue1.Text = firstLine;
dataCell1.Append(cellValue1);
firstRow1.AppendChild(dataCell1);
sheetData1.AppendChild(firstRow1);
workSheet1.AppendChild(sheetData1);
worksheetPart1.Worksheet = workSheet1;
MergeCells mergeCells1 = new MergeCells();
mergeCells1.Append(new MergeCell()
{
Reference = new StringValue("A1:H1")
});
worksheetPart1.Worksheet.InsertAfter(mergeCells1, worksheetPart1.Worksheet.Elements <SheetData>().First());
Row headerRow1 = new Row();
List <String> columns1 = new List <string>();
foreach (System.Data.DataColumn column in cessSummary.Columns)
{
columns1.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow1.AppendChild(cell);
}
sheetData1.AppendChild(headerRow1);
foreach (DataRow dsrow1 in cessSummary.Rows)
{
Row newRow1 = new Row();
foreach (String col in columns1)
{
Cell cell = new Cell();
if (col == "NetSales" || col == "Vatable" || col == "NonVatable" || col == "TotalTax" || col == "GrandTotal" || col == "CateringLevy")
{
cell.DataType = CellValues.Number;
cell.StyleIndex = 3;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue(dsrow1[col].ToString());
newRow1.AppendChild(cell);
}
sheetData1.AppendChild(newRow1);
}
Sheet sheets1 = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart1),
SheetId = 1,
Name = "Summary"
};
sheets.Append(sheets1);
// End: Code block for Excel sheet 1 // Cess Summary
// Begin: Code block for Excel sheet 2 // Cess Details
WorksheetPart worksheetPart2 = workbookpart.AddNewPart <WorksheetPart>();
Worksheet workSheet2 = new Worksheet();
SheetData sheetData2 = new SheetData();
// the data for sheet 1 // Cess Details
Row firstRow2 = new Row();
Cell dataCell2 = new Cell();
dataCell2.CellReference = "A1";
dataCell2.DataType = CellValues.String;
CellValue cellValue2 = new CellValue();
cellValue2.Text = firstLine;
dataCell2.Append(cellValue2);
firstRow2.AppendChild(dataCell2);
sheetData2.AppendChild(firstRow2);
workSheet2.AppendChild(sheetData2);
worksheetPart2.Worksheet = workSheet2;
MergeCells mergeCells2 = new MergeCells();
mergeCells2.Append(new MergeCell()
{
Reference = new StringValue("A1:G1")
});
worksheetPart2.Worksheet.InsertAfter(mergeCells2, worksheetPart2.Worksheet.Elements <SheetData>().First());
Row headerRow2 = new Row();
List <String> columns2 = new List <string>();
foreach (System.Data.DataColumn column in cessDetail.Columns)
{
columns2.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow2.AppendChild(cell);
}
sheetData2.AppendChild(headerRow2);
foreach (DataRow dsrow2 in cessDetail.Rows)
{
Row newRow2 = new Row();
foreach (String col in columns2)
{
Cell cell = new Cell();
if (col == "NetSales" || col == "Vatable" || col == "NonVatable" || col == "TotalTax" || col == "GrandTotal" || col == "CateringLevy")
{
cell.DataType = CellValues.Number;
cell.StyleIndex = 3;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue(dsrow2[col].ToString());
newRow2.AppendChild(cell);
}
sheetData2.AppendChild(newRow2);
}
Sheet sheets2 = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart2),
SheetId = 2,
Name = "Detail"
};
sheets.Append(sheets2);
// End: Code block for Excel sheet 1 // Cess Details
workbookpart.Workbook.Save();
}
}
