/// <summary>
/// Generates Table Definition.
/// </summary>
/// <param name="tableDefinitionPart">Table Definition To add Table to</param>
private void GenerateTableDefinition(TableDefinitionPart tableDefinitionPart)
{
var columnEnd = ConvertToAlphabetBasedNumber(_tableColumns.Count); //This function is zero based index
var rowEnd = _rows.Count + 1; //Need to include the header
var table = new DocumentFormat.OpenXml.Spreadsheet.Table {
Id = 1U,
Name = "Table1",
DisplayName = "Table1",
Reference = $"A1:{columnEnd}{rowEnd}",
TotalsRowShown = false,
HeaderRowFormatId = 1U,
DataFormatId = 0U,
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "xr xr3"
}
};
table.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
table.AddNamespaceDeclaration("xr3", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3");
table.SetAttribute(new OpenXmlAttribute("xr", "uid", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision", "{ED5DF9B8-D8E7-4542-9E4F-8E05D7A30983}"));
AutoFilter autoFilter = new AutoFilter {
Reference = $"A1:{columnEnd}{rowEnd}"
};
TableColumns tableColumns = new TableColumns {
Count = (uint)_tableColumns.Count
};
for (var i = 0; i < tableColumns.Count; i++)
{
tableColumns.Append(new TableColumn
{
Id = (uint)(i + 1), Name = _tableColumns[i], DataFormatId = 2U
});
}
TableStyleInfo tableStyleInfo1 = new TableStyleInfo {
Name = "TableStyleMedium15", ShowFirstColumn = false, ShowLastColumn = false, ShowRowStripes = true, ShowColumnStripes = false
};
table.Append(autoFilter);
table.Append(tableColumns);
table.Append(tableStyleInfo1);
tableDefinitionPart.Table = table;
}
private static void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart, XLTable xlTable,
SaveContext context)
{
context.TableId++;
var reference = xlTable.RangeAddress.FirstAddress + ":" + xlTable.RangeAddress.LastAddress;
var tableName = GetTableName(xlTable.Name, context);
var table = new Table
{
Id = context.TableId,
Name = tableName,
DisplayName = tableName,
Reference = reference
};
if (!xlTable.ShowHeaderRow)
table.HeaderRowCount = 0;
if (xlTable.ShowTotalsRow)
table.TotalsRowCount = 1;
else
table.TotalsRowShown = false;
var tableColumns1 = new TableColumns {Count = (UInt32)xlTable.ColumnCount()};
UInt32 columnId = 0;
foreach (var fieldName in xlTable.FieldNames.Keys)
{
columnId++;
var xlField = xlTable.Field(fieldName);
var tableColumn1 = new TableColumn
{
Id = columnId,
Name = fieldName.Replace("_x000a_", "_x005f_x000a_").Replace(Environment.NewLine, "_x000a_")
};
if (xlTable.ShowTotalsRow)
{
if (xlField.TotalsRowFunction != XLTotalsRowFunction.None)
{
tableColumn1.TotalsRowFunction = xlField.TotalsRowFunction.ToOpenXml();
if (xlField.TotalsRowFunction == XLTotalsRowFunction.Custom)
tableColumn1.TotalsRowFormula = new TotalsRowFormula(xlField.TotalsRowFormulaA1);
}
if (!XLHelper.IsNullOrWhiteSpace(xlField.TotalsRowLabel))
tableColumn1.TotalsRowLabel = xlField.TotalsRowLabel;
}
tableColumns1.AppendChild(tableColumn1);
}
var tableStyleInfo1 = new TableStyleInfo
{
ShowFirstColumn = xlTable.EmphasizeFirstColumn,
ShowLastColumn = xlTable.EmphasizeLastColumn,
ShowRowStripes = xlTable.ShowRowStripes,
ShowColumnStripes = xlTable.ShowColumnStripes
};
if (xlTable.Theme != XLTableTheme.None)
tableStyleInfo1.Name = Enum.GetName(typeof(XLTableTheme), xlTable.Theme);
if (xlTable.ShowAutoFilter)
{
var autoFilter1 = new AutoFilter();
if (xlTable.ShowTotalsRow)
{
xlTable.AutoFilter.Range = xlTable.Worksheet.Range(
xlTable.RangeAddress.FirstAddress.RowNumber, xlTable.RangeAddress.FirstAddress.ColumnNumber,
xlTable.RangeAddress.LastAddress.RowNumber - 1, xlTable.RangeAddress.LastAddress.ColumnNumber);
}
else
xlTable.AutoFilter.Range = xlTable.Worksheet.Range(xlTable.RangeAddress);
PopulateAutoFilter(xlTable.AutoFilter, autoFilter1);
table.AppendChild(autoFilter1);
}
table.AppendChild(tableColumns1);
table.AppendChild(tableStyleInfo1);
tableDefinitionPart.Table = table;
}
private static void AddTablePart(WorksheetPart sheetPart, TableColumn[] columns, int rowCount, WorkbookPart workBookPart)
{
if (sheetPart == null)
{
throw new ArgumentNullException(nameof(sheetPart));
}
if (columns == null)
{
throw new ArgumentNullException(nameof(columns));
}
if (rowCount < 0)
{
throw new ArgumentOutOfRangeException(nameof(rowCount));
}
if (workBookPart == null)
{
throw new ArgumentNullException(nameof(workBookPart));
}
var rangeReference = GetXlsTableRangeReference(columns.Length, rowCount);
var ignoredErrors = new IgnoredErrors(
new IgnoredError
{
NumberStoredAsText = true,
SequenceOfReferences = new ListValue <StringValue>
{
InnerText = rangeReference
}
}
);
// Ignored errors must be added before table parts.
sheetPart.Worksheet.Append(ignoredErrors);
var tableDefinitionPart = sheetPart.AddNewPart <TableDefinitionPart>();
var autoFilter = new AutoFilter {
Reference = rangeReference
};
var tableColumns = new TableColumns {
Count = (uint)columns.Length
};
var styleInfo = new TableStyleInfo
{
Name = "TableStyleMedium2",
ShowFirstColumn = false,
ShowLastColumn = false,
ShowRowStripes = true,
ShowColumnStripes = false
};
var tableId = workBookPart.WorksheetParts
.Select(x => x.TableDefinitionParts.Where(y => y.Table != null)
.Select(y => (uint)y.Table.Id).DefaultIfEmpty(0U).Max()).DefaultIfEmpty(0U).Max() + 1;
var table =
new DocumentFormat.OpenXml.Spreadsheet.Table(autoFilter, tableColumns, styleInfo)
{
Id = tableId,
Name = "Table" + tableId,
DisplayName = "Table" + tableId,
Reference = rangeReference,
TotalsRowShown = false
};
for (var i = 0; i < columns.Length; i++)
{
table.TableColumns.Append(
new DocumentFormat.OpenXml.Spreadsheet.TableColumn
{
Id = (uint)(i + 1),
Name = columns[i].ColumnName
});
}
tableDefinitionPart.Table = table;
var tableParts = new TableParts(
new TablePart
{
Id = sheetPart.GetIdOfPart(tableDefinitionPart)
}
)
{
Count = 1U
};
sheetPart.Worksheet.Append(tableParts);
}