// Adds child parts and generates content of the specified part.
public static void CreateTable(WorkbookPart workbookPart, WorksheetPart worksheetPart, string[] columns, int topLeftColumn, int topLeftRow, int width, int height)
{
List<WorksheetPart> worksheets = workbookPart.GetPartsOfType<WorksheetPart>().ToList();
uint maxTableId = worksheets.Select(ws => ws.TableDefinitionParts.ToList()).SelectMany(tableDefinitions => tableDefinitions).Aggregate<TableDefinitionPart, uint>(0, (current, tableDef) => Math.Max(tableDef.Table.Id, current));
uint tableId = maxTableId + 1;
var tables = new TableParts { Count = 1U };
worksheetPart.Worksheet.Append((IEnumerable<OpenXmlElement>)tables);
var newTableDefnPart = worksheetPart.AddNewPart<TableDefinitionPart>();
string relationshipId = worksheetPart.GetIdOfPart(newTableDefnPart);
string cellReference = string.Format("{0}{1}:{2}{3}", GetColumnIdentifier(topLeftColumn), topLeftRow, GetColumnIdentifier(topLeftColumn + width - 1), topLeftRow + height);
var table1 = new Table { Id = tableId, Name = "Table" + relationshipId, DisplayName = "Table" + relationshipId, Reference = cellReference, TotalsRowShown = false };
var autoFilter1 = new AutoFilter { Reference = cellReference };
var tableColumns1 = new TableColumns { Count = (uint)columns.Length };
for (int iColumn = 0; iColumn < columns.Length; iColumn++)
{
var tableColumn = new TableColumn { Id = (UInt32Value)(uint)iColumn + 1, Name = columns[iColumn] };
tableColumns1.Append((IEnumerable<OpenXmlElement>)tableColumn);
}
var tableStyleInfo1 = new TableStyleInfo { Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table1.Append((IEnumerable<OpenXmlElement>)autoFilter1);
table1.Append((IEnumerable<OpenXmlElement>)tableColumns1);
table1.Append((IEnumerable<OpenXmlElement>)tableStyleInfo1);
newTableDefnPart.Table = table1;
var table = new TablePart { Id = relationshipId };
tables.Append((IEnumerable<OpenXmlElement>)table);
//TableStyles tableStyles1 = new TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
//worksheetPart.Worksheet.Append(tableStyles1);
}
private void generateTaskTableContent(TableDefinitionPart tableDefinitionPart)
{
Table table = new Table()
{
Id = (UInt32Value)4U, Name = "WorkItemsTable", DisplayName = "WorkItemsTable", Reference = "A1:J" + (reportData.tasks.value.Count + 1).ToString(), TotalsRowShown = false
};
table.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
//table1.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
//table1.AddNamespaceDeclaration("xr3", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3");
//table1.SetAttribute(new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", "{5EFA48B8-3293-4748-AA3B-548E4EF8F199}"));
AutoFilter autoFilter1 = new AutoFilter()
{
Reference = "A1:J" + (reportData.tasks.value.Count + 1).ToString()
};
//autoFilter1.SetAttribute(new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", "{EDA87F4E-B12B-41C9-8B20-4850D8E9E195}"));
TableColumns tableColumns = new TableColumns()
{
Count = (uint)headers.Count
};
for (int i = 1; i <= headers.Count; i++)
{
TableColumn tableColumn = new TableColumn()
{
Id = (uint)i, Name = headers[i - 1]
};
//tableColumn.SetAttribute(new OpenXmlAttribute("xr3", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3", "{D2D20073-D29B-4500-9950-65429DC6D749}"));
Console.WriteLine("Header value: {0} ", headers[i - 1]);
Console.WriteLine("Header cell ref: {0} ", rowArry[i - 1].ToString() + "1");
tableColumns.Append(tableColumn);
}
TableStyleInfo tableStyleInfo = new TableStyleInfo()
{
Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false
};
table.Append(autoFilter1);
table.Append(tableColumns);
table.Append(tableStyleInfo);
tableDefinitionPart.Table = table;
}
private void DefineTable(WorksheetPart worksheetPart, LineItem[] lineItems)
{
TableDefinitionPart tableDefinitionPart = worksheetPart.AddNewPart <TableDefinitionPart>("rId" + (worksheetPart.TableDefinitionParts.Count() + 1));
int tableNo = worksheetPart.TableDefinitionParts.Count();
string reference = $"A1:{((char)(64 + _fields.Length))}{lineItems.Length}";
Table table = new Table()
{
Id = (UInt32)tableNo, Name = "Table" + tableNo, DisplayName = "Table" + tableNo, Reference = reference, TotalsRowShown = false
};
AutoFilter autoFilter = new AutoFilter()
{
Reference = reference
};
TableColumns tableColumns = new TableColumns()
{
Count = (UInt32)_fields.Length
};
for (int i = 0; i < _fields.Length; i++)
{
tableColumns.Append(new TableColumn()
{
Id = (UInt32)(i + 1), Name = _fields[i]
});
}
table.Append(autoFilter);
table.Append(tableColumns);
tableDefinitionPart.Table = table;
TableParts tableParts = new TableParts()
{
Count = (UInt32)1
};
TablePart tablePart = new TablePart()
{
Id = "rId" + tableNo
};
tableParts.Append(tablePart);
worksheetPart.Worksheet.Append(tableParts);
}
private static void GenerateTablePartContent(TableDefinitionPart part, ReportResult reportResult, List <DataRow> rows, int columnCount)
{
int rowCount = 2;
if (rows.Count() > 0)
{
rowCount = rows.Count() + 1;
}
// int columnCount = reportResult.Metadata.ReportColumns.Values.Count()-1;
string reference = "A1:" + GetExcelColumnName(columnCount) + rowCount.ToString();
Table table1 = new Table()
{
Id = (UInt32Value)1U, Name = "Table1", DisplayName = "Table1", Reference = reference, TotalsRowShown = false
};
AutoFilter autoFilter1 = new AutoFilter()
{
Reference = reference
};
TableColumns tableColumns1 = new TableColumns()
{
Count = Convert.ToUInt32(columnCount + 1)
};
int i = 1;
foreach (ReportColumn column in reportResult.Metadata.ReportColumns.Values)
{
if (!column.IsHidden && column.Type != "Image")
{
tableColumns1.Append(new TableColumn()
{
Id = Convert.ToUInt32(i++), Name = column.Title.Trim()
});
}
}
TableStyleInfo tableStyleInfo1 = new TableStyleInfo()
{
Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false
};
table1.Append(autoFilter1);
table1.Append(tableColumns1);
table1.Append(tableStyleInfo1);
table1.HeaderRowCount = 1;
part.Table = table1;
}
// Generates content of tableDefinitionPart1.
private static void GenerateTableDefinitionPart1Content(TableDefinitionPart tableDefinitionPart1, DataTable dtSource)
{
if (dtSource.Columns.Count > 26)
{
throw new NotSupportedException("More than 26 columns are currently not supported in ExcelTools."); // add later if needed
}
char colA = 'A';
char colEnd = (char)((int)colA + (dtSource.Columns.Count - 1));
int row1 = 1;
int rowEnd = dtSource.Rows.Count + 1; // include header row
string tblReference = string.Format("{0}{1}:{2}{3}", colA, row1, colEnd, rowEnd);
Table table1 = new Table()
{
Id = (UInt32Value)1U, Name = "Table1", DisplayName = "Table1", Reference = tblReference, TotalsRowShown = false, BorderFormatId = (UInt32Value)1U
};
TableColumns tableColumns1 = new TableColumns()
{
Count = (UInt32Value)(uint)dtSource.Columns.Count
};
int colId = 1;
foreach (DataColumn dc in dtSource.Columns)
{
TableColumn tableColumn1 = new TableColumn()
{
Id = (UInt32Value)(uint)colId, Name = dc.ColumnName
};
tableColumns1.Append(tableColumn1);
colId++;
}
TableStyleInfo tableStyleInfo1 = new TableStyleInfo()
{
ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false
};
table1.Append(tableColumns1);
table1.Append(tableStyleInfo1);
tableDefinitionPart1.Table = table1;
}
// Generates content of tableDefinitionPart1.
public static void GenerateTableDefinitionPart1Content(TableDefinitionPart tableDefinitionPart1, List <string> titres, int nbLi, int nbCol, int indice)
{
string refe = "A1:" + Ultimate.headerColumns[nbCol - 1] + nbLi;
Table table1 = new Table()
{
Id = (uint)indice, Name = "Table1", DisplayName = "Table" + indice, Reference = refe, TotalsRowShown = false
};
AutoFilter autoFilter1 = new AutoFilter()
{
Reference = refe
};
TableColumns tableColumns1 = new TableColumns()
{
Count = (uint)titres.Count
};
int i = 1;
foreach (string t in titres)
{
TableColumn tableColumn1 = new TableColumn()
{
Id = (uint)i, Name = t
};
tableColumns1.Append(tableColumn1);
i++;
}
TableStyleInfo tableStyleInfo1 = new TableStyleInfo()
{
Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false
};
table1.Append(autoFilter1);
table1.Append(tableColumns1);
table1.Append(tableStyleInfo1);
tableDefinitionPart1.Table = table1;
}
// Generates content of tableDefinitionPart2.
private void GenerateTableDefinitionPart2Content(TableDefinitionPart tableDefinitionPart2)
{
Table table2 = new Table(){ Id = (UInt32Value)1U, Name = "Table1", DisplayName = "Table1", Reference = "A1:C4", TotalsRowShown = false };
AutoFilter autoFilter2 = new AutoFilter(){ Reference = "A1:C4" };
TableColumns tableColumns2 = new TableColumns(){ Count = (UInt32Value)3U };
TableColumn tableColumn4 = new TableColumn(){ Id = (UInt32Value)1U, Name = "Column1" };
TableColumn tableColumn5 = new TableColumn(){ Id = (UInt32Value)2U, Name = "Column2" };
TableColumn tableColumn6 = new TableColumn(){ Id = (UInt32Value)3U, Name = "Column3" };
tableColumns2.Append(tableColumn4);
tableColumns2.Append(tableColumn5);
tableColumns2.Append(tableColumn6);
TableStyleInfo tableStyleInfo2 = new TableStyleInfo(){ Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table2.Append(autoFilter2);
table2.Append(tableColumns2);
table2.Append(tableStyleInfo2);
tableDefinitionPart2.Table = table2;
}
// Generates content of tableDefinitionPart1.
private void GenerateTableDefinitionPart1Content(TableDefinitionPart tableDefinitionPart1)
{
Table table1 = new Table(){ Id = (UInt32Value)2U, Name = "Table2", DisplayName = "Table2", Reference = "E1:G4", TotalsRowShown = false };
AutoFilter autoFilter1 = new AutoFilter(){ Reference = "E1:G4" };
TableColumns tableColumns1 = new TableColumns(){ Count = (UInt32Value)3U };
TableColumn tableColumn1 = new TableColumn(){ Id = (UInt32Value)1U, Name = "Column1" };
TableColumn tableColumn2 = new TableColumn(){ Id = (UInt32Value)2U, Name = "Column2" };
TableColumn tableColumn3 = new TableColumn(){ Id = (UInt32Value)3U, Name = "Column3" };
tableColumns1.Append(tableColumn1);
tableColumns1.Append(tableColumn2);
tableColumns1.Append(tableColumn3);
TableStyleInfo tableStyleInfo1 = new TableStyleInfo(){ Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table1.Append(autoFilter1);
table1.Append(tableColumns1);
table1.Append(tableStyleInfo1);
tableDefinitionPart1.Table = table1;
}
// Generates content of tableDefinitionPart6.
private void GenerateTableDefinitionPart6Content(TableDefinitionPart tableDefinitionPart6)
{
Table table6 = new Table(){ Id = (UInt32Value)1U, Name = "DataTable1", DisplayName = "DataTable1", Reference = "A1:F5", TotalsRowShown = false };
AutoFilter autoFilter6 = new AutoFilter(){ Reference = "A1:F5" };
SortState sortState2 = new SortState(){ Reference = "A2:F2001" };
SortCondition sortCondition2 = new SortCondition(){ Reference = "A2:A2001" };
sortState2.Append(sortCondition2);
TableColumns tableColumns6 = new TableColumns(){ Count = (UInt32Value)6U };
TableColumn tableColumn13 = new TableColumn(){ Id = (UInt32Value)1U, Name = "Date", DataFormatId = (UInt32Value)13U };
TableColumn tableColumn14 = new TableColumn(){ Id = (UInt32Value)2U, Name = "Product" };
TableColumn tableColumn15 = new TableColumn(){ Id = (UInt32Value)3U, Name = "Quantity" };
TableColumn tableColumn16 = new TableColumn(){ Id = (UInt32Value)4U, Name = "Price" };
TableColumn tableColumn17 = new TableColumn(){ Id = (UInt32Value)5U, Name = "DeliveryDate", DataFormatId = (UInt32Value)12U };
TableColumn tableColumn18 = new TableColumn(){ Id = (UInt32Value)6U, Name = "Employee" };
tableColumns6.Append(tableColumn13);
tableColumns6.Append(tableColumn14);
tableColumns6.Append(tableColumn15);
tableColumns6.Append(tableColumn16);
tableColumns6.Append(tableColumn17);
tableColumns6.Append(tableColumn18);
TableStyleInfo tableStyleInfo6 = new TableStyleInfo(){ Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table6.Append(autoFilter6);
table6.Append(sortState2);
table6.Append(tableColumns6);
table6.Append(tableStyleInfo6);
tableDefinitionPart6.Table = table6;
}
/// <summary>
/// Generates content of tableDefinitionPart1.
/// </summary>
/// <param name="tableDefinitionPart">Table definition.</param>
private static void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart)
{
Table table1 = new Table() { Id = (UInt32Value)5U, Name = Constants.MetadataRangeName, DisplayName = Constants.MetadataRangeName, Reference = "A1:B44", TotalsRowShown = false };
AutoFilter autoFilter1 = new AutoFilter() { Reference = "A1:B44" };
TableColumns tableColumns1 = new TableColumns() { Count = (UInt32Value)2U };
TableColumn tableColumn1 = new TableColumn() { Id = (UInt32Value)1U, Name = "Name" };
TableColumn tableColumn2 = new TableColumn() { Id = (UInt32Value)2U, Name = "Value" };
tableColumns1.Append(tableColumn1);
tableColumns1.Append(tableColumn2);
TableStyleInfo tableStyleInfo1 = new TableStyleInfo() { Name = Constants.MetadataTableStyleName, ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table1.Append(autoFilter1);
table1.Append(tableColumns1);
table1.Append(tableStyleInfo1);
tableDefinitionPart.Table = table1;
}
// Generates content of tableDefinitionPart4.
private void GenerateTableDefinitionPart4Content(TableDefinitionPart tableDefinitionPart4)
{
Table table4 = new Table(){ Id = (UInt32Value)3U, Name = "SalesTable1", DisplayName = "SalesTable1", Reference = "K1:K11", TotalsRowShown = false, HeaderRowFormatId = (UInt32Value)9U };
AutoFilter autoFilter4 = new AutoFilter(){ Reference = "K1:K11" };
TableColumns tableColumns4 = new TableColumns(){ Count = (UInt32Value)1U };
TableColumn tableColumn10 = new TableColumn(){ Id = (UInt32Value)1U, Name = "Employee" };
tableColumns4.Append(tableColumn10);
TableStyleInfo tableStyleInfo4 = new TableStyleInfo(){ Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table4.Append(autoFilter4);
table4.Append(tableColumns4);
table4.Append(tableStyleInfo4);
tableDefinitionPart4.Table = table4;
}
// Generates content of tableDefinitionPart3.
private void GenerateTableDefinitionPart3Content(TableDefinitionPart tableDefinitionPart3)
{
Table table3 = new Table(){ Id = (UInt32Value)4U, Name = "DataTable2", DisplayName = "DataTable2", Reference = "A1:F6", TotalsRowShown = false };
AutoFilter autoFilter3 = new AutoFilter(){ Reference = "A1:F6" };
SortState sortState1 = new SortState(){ Reference = "A2:F2001" };
SortCondition sortCondition1 = new SortCondition(){ Reference = "A2" };
sortState1.Append(sortCondition1);
TableColumns tableColumns3 = new TableColumns(){ Count = (UInt32Value)6U };
TableColumn tableColumn4 = new TableColumn(){ Id = (UInt32Value)1U, Name = "Date", DataFormatId = (UInt32Value)8U };
TableColumn tableColumn5 = new TableColumn(){ Id = (UInt32Value)2U, Name = "Product", DataFormatId = (UInt32Value)7U };
TableColumn tableColumn6 = new TableColumn(){ Id = (UInt32Value)3U, Name = "Quantity", DataFormatId = (UInt32Value)6U };
TableColumn tableColumn7 = new TableColumn(){ Id = (UInt32Value)4U, Name = "Price", DataFormatId = (UInt32Value)5U };
TableColumn tableColumn8 = new TableColumn(){ Id = (UInt32Value)5U, Name = "DeliveryDate", DataFormatId = (UInt32Value)4U };
TableColumn tableColumn9 = new TableColumn(){ Id = (UInt32Value)6U, Name = "Employee", DataFormatId = (UInt32Value)3U };
tableColumns3.Append(tableColumn4);
tableColumns3.Append(tableColumn5);
tableColumns3.Append(tableColumn6);
tableColumns3.Append(tableColumn7);
tableColumns3.Append(tableColumn8);
tableColumns3.Append(tableColumn9);
TableStyleInfo tableStyleInfo3 = new TableStyleInfo(){ Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table3.Append(autoFilter3);
table3.Append(sortState1);
table3.Append(tableColumns3);
table3.Append(tableStyleInfo3);
tableDefinitionPart3.Table = table3;
}
// Generates content of tableDefinitionPart2.
private void GenerateTableDefinitionPart2Content(TableDefinitionPart tableDefinitionPart2)
{
Table table2 = new Table(){ Id = (UInt32Value)5U, Name = "ProductTable2", DisplayName = "ProductTable2", Reference = "H1:I11", TotalsRowShown = false, HeaderRowFormatId = (UInt32Value)2U };
AutoFilter autoFilter2 = new AutoFilter(){ Reference = "H1:I11" };
TableColumns tableColumns2 = new TableColumns(){ Count = (UInt32Value)2U };
TableColumn tableColumn2 = new TableColumn(){ Id = (UInt32Value)1U, Name = "ProductName" };
TableColumn tableColumn3 = new TableColumn(){ Id = (UInt32Value)2U, Name = "Price", DataFormatId = (UInt32Value)1U };
tableColumns2.Append(tableColumn2);
tableColumns2.Append(tableColumn3);
TableStyleInfo tableStyleInfo2 = new TableStyleInfo(){ Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table2.Append(autoFilter2);
table2.Append(tableColumns2);
table2.Append(tableStyleInfo2);
tableDefinitionPart2.Table = table2;
}
// Generates content of tableDefinitionPart1.
private void GenerateTableDefinitionPart1Content(TableDefinitionPart tableDefinitionPart1)
{
Table table1 = new Table(){ Id = (UInt32Value)1U, Name = "Table_Query", DisplayName = "Table_Query", Reference = "B2:I13", TableType = TableValues.QueryTable, TotalsRowShown = false };
AutoFilter autoFilter1 = new AutoFilter(){ Reference = "B2:I13" };
TableColumns tableColumns1 = new TableColumns(){ Count = (UInt32Value)8U };
TableColumn tableColumn1 = new TableColumn(){ Id = (UInt32Value)1U, UniqueName = "1", Name = "account_id", QueryTableFieldId = (UInt32Value)1U };
TableColumn tableColumn2 = new TableColumn(){ Id = (UInt32Value)2U, UniqueName = "2", Name = "account_parent", QueryTableFieldId = (UInt32Value)2U };
TableColumn tableColumn3 = new TableColumn(){ Id = (UInt32Value)3U, UniqueName = "3", Name = "account_description", QueryTableFieldId = (UInt32Value)3U };
TableColumn tableColumn4 = new TableColumn(){ Id = (UInt32Value)4U, UniqueName = "4", Name = "account_type", QueryTableFieldId = (UInt32Value)4U };
TableColumn tableColumn5 = new TableColumn(){ Id = (UInt32Value)5U, UniqueName = "5", Name = "account_rollup", QueryTableFieldId = (UInt32Value)5U };
TableColumn tableColumn6 = new TableColumn(){ Id = (UInt32Value)6U, UniqueName = "6", Name = "Custom Members", QueryTableFieldId = (UInt32Value)6U };
TableColumn tableColumn7 = new TableColumn(){ Id = (UInt32Value)7U, UniqueName = "7", Name = "CurrentUser", QueryTableFieldId = (UInt32Value)7U };
TableColumn tableColumn8 = new TableColumn(){ Id = (UInt32Value)8U, UniqueName = "8", Name = "RefreshTime", QueryTableFieldId = (UInt32Value)8U, DataFormatId = (UInt32Value)0U };
tableColumns1.Append(tableColumn1);
tableColumns1.Append(tableColumn2);
tableColumns1.Append(tableColumn3);
tableColumns1.Append(tableColumn4);
tableColumns1.Append(tableColumn5);
tableColumns1.Append(tableColumn6);
tableColumns1.Append(tableColumn7);
tableColumns1.Append(tableColumn8);
TableStyleInfo tableStyleInfo1 = new TableStyleInfo(){ Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table1.Append(autoFilter1);
table1.Append(tableColumns1);
table1.Append(tableStyleInfo1);
tableDefinitionPart1.Table = table1;
}
/// <summary>
/// Generates content of tableDefinitionPart1.
/// </summary>
/// <param name="tableDefinitionPart">Table definition part.</param>
/// <param name="hasFileLevelMetadata">Has file level metadata.</param>
private static void GenerateParameterTablePartContent(TableDefinitionPart tableDefinitionPart, bool hasFileLevelMetadata)
{
string reference = hasFileLevelMetadata ? "D1:I44" : "A1:F44";
Table table1 = new Table() { Id = (UInt32Value)6U, Name = Constants.ParaMetadataRangeName, DisplayName = Constants.ParaMetadataRangeName, Reference = reference, TotalsRowShown = false };
AutoFilter autoFilter1 = new AutoFilter() { Reference = reference };
TableColumns tableColumns1 = new TableColumns() { Count = (UInt32Value)6U };
tableColumns1.Append(new TableColumn() { Id = (UInt32Value)1U, Name = Statics.TableName });
tableColumns1.Append(new TableColumn() { Id = (UInt32Value)2U, Name = Statics.TableDescription });
tableColumns1.Append(new TableColumn() { Id = (UInt32Value)3U, Name = Statics.FieldName });
tableColumns1.Append(new TableColumn() { Id = (UInt32Value)4U, Name = Statics.FieldDescription });
tableColumns1.Append(new TableColumn() { Id = (UInt32Value)5U, Name = Statics.DataType });
tableColumns1.Append(new TableColumn() { Id = (UInt32Value)6U, Name = Statics.Units });
TableStyleInfo tableStyleInfo1 = new TableStyleInfo() { Name = Constants.MetadataTableStyleName, ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table1.Append(autoFilter1);
table1.Append(tableColumns1);
table1.Append(tableStyleInfo1);
tableDefinitionPart.Table = table1;
}
/// <summary>
/// Generate Excel Document.
/// </summary>
/// <param name="queryResult">QueryResult</param>
/// <returns>MemoryStream</returns>
public static MemoryStream CreateExcelDocument(QueryResult queryResult)
{
var ms = new MemoryStream( );
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook, true))
{
// Create the Workbook
WorkbookPart workbookPart = spreadSheet.AddWorkbookPart( );
spreadSheet.WorkbookPart.Workbook = new Workbook( );
// A Workbook must only have exactly one <Sheets> section
spreadSheet.WorkbookPart.Workbook.AppendChild(new Sheets( ));
var newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>( );
newWorksheetPart.Worksheet = new Worksheet( );
// Create a new Excel worksheet
SheetData sheetData = newWorksheetPart.Worksheet.AppendChild(new SheetData( ));
var tdp = newWorksheetPart.AddNewPart <TableDefinitionPart>( );
string rId = newWorksheetPart.GetIdOfPart(tdp);
var T = new Table
{
Id = 1U,
Name = "MyTable",
DisplayName = "MyTable",
Reference = "A1:B10",
TotalsRowShown = false
};
var columns = new TableColumns
{
Count = 2U
};
var column1 = new TableColumn
{
Id = 1U,
Name = "Column1"
};
var column2 = new TableColumn
{
Id = 2U,
Name = "Column2"
};
var styleInfo = new TableStyleInfo
{
Name = "TableStyleMedium2",
ShowFirstColumn = false,
ShowLastColumn = false,
ShowRowStripes = true,
ShowColumnStripes = false
};
var autoFilter = new AutoFilter
{
Reference = "A1:B10"
};
columns.Append(column1);
columns.Append(column2);
T.Append(autoFilter);
T.Append(columns);
T.Append(styleInfo);
tdp.Table = T;
T.Save( );
var tableParts = new TableParts
{
Count = 1U
};
var tablePart = new TablePart
{
Id = rId
};
tableParts.Append(tablePart);
newWorksheetPart.Worksheet.Append(tableParts);
newWorksheetPart.Worksheet.Save( );
// Create Styles and Insert into Workbook
var stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>( );
Stylesheet styles = new ExportDataStylesheet( );
styles.Save(stylesPart);
// Insert Datatable data into the worksheet.
InsertTableData(queryResult, sheetData, stylesPart);
// Save the worksheet.
newWorksheetPart.Worksheet.Save( );
// Link this worksheet to our workbook
spreadSheet.WorkbookPart.Workbook.GetFirstChild <Sheets>( ).AppendChild(new Sheet
{
Id = spreadSheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = 1,
Name = "Table"
});
// Save the workbook.
spreadSheet.WorkbookPart.Workbook.Save( );
}
return(ms);
}
public Table GetTableDefinition(int id, int columnIndex, int rowIndex)
{
var startColumn = SpreadsheetHelper.ExcelColumnFromNumber(columnIndex);
var endColumn = SpreadsheetHelper.ExcelColumnFromNumber(columnIndex + Columns.Count - 1);
var reference = string.Format("{0}{1}:{2}{3}", startColumn, rowIndex, endColumn, rowIndex + RowCount + (ShowHeaderRow ? 0 : -1));
var table = new Table()
{
Id = (UInt32Value)(UInt32)id, Name = Name, DisplayName = "table" + id + "_", TotalsRowShown = false, Reference = reference
};
AutoFilter autoFilter = null;
if (!ShowHeaderRow)
{
table.HeaderRowCount = 0;
}
else
{
autoFilter = new AutoFilter()
{
Reference = reference
};
}
var tablesColumns = new TableColumns()
{
Count = (UInt32Value)(UInt32)Columns.Count
};
var i = 1;
foreach (var column in Columns)
{
tablesColumns.Append(column.ToOpenXmlColumn(i));
if (autoFilter != null && column.FilterValues.Any())
{
var filterColumn = new FilterColumn()
{
ColumnId = (UInt32)i - 1
};
var filters = new Filters();
foreach (var filterValue in column.FilterValues)
{
filters.AppendChild(new Filter()
{
Val = filterValue
});
}
filterColumn.AppendChild(filters);
autoFilter.AppendChild(filterColumn);
}
i++;
}
TableStyleInfo tableStyle = new TableStyleInfo()
{
Name = StyleName, ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false,
};
if (autoFilter != null)
{
table.Append(autoFilter);
}
table.Append(tablesColumns);
table.Append(tableStyle);
return(table);
}
// Generates content of tableDefinitionPart5.
private void GenerateTableDefinitionPart5Content(TableDefinitionPart tableDefinitionPart5)
{
Table table5 = new Table(){ Id = (UInt32Value)2U, Name = "ProductTable1", DisplayName = "ProductTable1", Reference = "H1:I11", TotalsRowShown = false, HeaderRowFormatId = (UInt32Value)11U };
AutoFilter autoFilter5 = new AutoFilter(){ Reference = "H1:I11" };
TableColumns tableColumns5 = new TableColumns(){ Count = (UInt32Value)2U };
TableColumn tableColumn11 = new TableColumn(){ Id = (UInt32Value)1U, Name = "ProductName" };
TableColumn tableColumn12 = new TableColumn(){ Id = (UInt32Value)2U, Name = "Price", DataFormatId = (UInt32Value)10U };
tableColumns5.Append(tableColumn11);
tableColumns5.Append(tableColumn12);
TableStyleInfo tableStyleInfo5 = new TableStyleInfo(){ Name = "TableStyleMedium2", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false };
table5.Append(autoFilter5);
table5.Append(tableColumns5);
table5.Append(tableStyleInfo5);
tableDefinitionPart5.Table = table5;
}