public IEnumerable <TableColumn> TableColumns()
{
XNamespace x = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
return(TableDefinitionPart
.GetXDocument()
.Root
.Element(x + "tableColumns")
.Elements(x + "tableColumn")
.Select((c, i) =>
new TableColumn(this)
{
Id = (int)c.Attribute("id"),
ColumnNumber = this.LeftColumn + i,
Name = (string)c.Attribute("name"),
DataDxfId = (int?)c.Attribute("dataDxfId"),
QueryTableFieldId = (int?)c.Attribute("queryTableFieldId"),
UniqueName = (string)c.Attribute("uniqueName"),
ColumnIndex = i,
}
));
}
// 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;
}
public static void AddWorksheet(SpreadsheetDocument sDoc, WorksheetDfn worksheetData, int tableCounter)
{
Regex validSheetName = new Regex(@"^[^'*\[\]/\\:?][^*\[\]/\\:?]{0,30}$");
if (!validSheetName.IsMatch(worksheetData.Name))
{
throw new InvalidSheetNameException(worksheetData.Name);
}
// throw WorksheetAlreadyExistsException if a sheet with the same name (case-insensitive) already exists in the workbook
string UCName = worksheetData.Name.ToUpper();
XDocument wXDoc = sDoc.WorkbookPart.GetXDocument();
if (wXDoc
.Root
.Elements(S.sheets)
.Elements(S.sheet)
.Attributes(SSNoNamespace.name)
.Select(a => ((string)a).ToUpper())
.Contains(UCName))
{
throw new WorksheetAlreadyExistsException(worksheetData.Name);
}
// create the worksheet with the supplied name
XDocument appXDoc = sDoc
.ExtendedFilePropertiesPart
.GetXDocument();
XElement vector = appXDoc
.Root
.Elements(EP.TitlesOfParts)
.Elements(VT.vector)
.FirstOrDefault();
if (vector != null)
{
int?size = (int?)vector.Attribute(SSNoNamespace.size);
if (size == null)
{
size = 1;
}
else
{
size = size + 1;
}
vector.SetAttributeValue(SSNoNamespace.size, size);
vector.Add(
new XElement(VT.lpstr, worksheetData.Name));
XElement i4 = appXDoc
.Root
.Elements(EP.HeadingPairs)
.Elements(VT.vector)
.Elements(VT.variant)
.Elements(VT.i4)
.FirstOrDefault();
if (i4 != null)
{
i4.Value = ((int)i4 + 1).ToString();
}
sDoc.ExtendedFilePropertiesPart.PutXDocument();
}
WorkbookPart workbook = sDoc.WorkbookPart;
string rId = "R" + Guid.NewGuid().ToString().Replace("-", "");
WorksheetPart worksheetPart = workbook.AddNewPart <WorksheetPart>(rId);
XDocument wbXDoc = workbook.GetXDocument();
XElement sheets = wbXDoc.Descendants(S.sheets).FirstOrDefault();
sheets.Add(
new XElement(S.sheet,
new XAttribute(SSNoNamespace.name, worksheetData.Name.ToString()),
new XAttribute(SSNoNamespace.sheetId, sheets.Elements(S.sheet).Count() + 1),
new XAttribute(R.id, rId)));
workbook.PutXDocument();
string ws = S.s.ToString();
string relns = R.r.ToString();
using (Stream partStream = worksheetPart.GetStream(FileMode.Create, FileAccess.Write))
{
using (XmlWriter partXmlWriter = XmlWriter.Create(partStream))
{
partXmlWriter.WriteStartDocument();
partXmlWriter.WriteStartElement("worksheet", ws);
partXmlWriter.WriteStartElement("sheetData", ws);
int numColumnHeadingRows = 0;
int numColumns = 0;
int numColumnsInRows = 0;
int numRows;
if (worksheetData.ColumnHeadings != null)
{
RowDfn row = new RowDfn
{
Cells = worksheetData.ColumnHeadings
};
SerializeRows(sDoc, partXmlWriter, new[] { row }, 1, out numColumns, out numColumnHeadingRows);
}
SerializeRows(sDoc, partXmlWriter, worksheetData.Rows, numColumnHeadingRows + 1, out numColumnsInRows,
out numRows);
int totalRows = numColumnHeadingRows + numRows;
int totalColumns = Math.Max(numColumns, numColumnsInRows);
if (worksheetData.ColumnHeadings != null && worksheetData.TableName != null)
{
partXmlWriter.WriteEndElement();
string rId2 = "R" + Guid.NewGuid().ToString().Replace("-", "");
partXmlWriter.WriteStartElement("tableParts", ws);
partXmlWriter.WriteStartAttribute("count");
partXmlWriter.WriteValue(1);
partXmlWriter.WriteEndAttribute();
partXmlWriter.WriteStartElement("tablePart", ws);
partXmlWriter.WriteStartAttribute("id", relns);
partXmlWriter.WriteValue(rId2);
TableDefinitionPart tdp = worksheetPart.AddNewPart <TableDefinitionPart>(rId2);
XDocument tXDoc = tdp.GetXDocument();
XElement table = new XElement(S.table,
new XAttribute(SSNoNamespace.id, tableCounter),
new XAttribute(SSNoNamespace.name, worksheetData.TableName),
new XAttribute(SSNoNamespace.displayName, worksheetData.TableName),
new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString()),
new XAttribute(SSNoNamespace.totalsRowShown, 0),
new XElement(S.autoFilter,
new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString())),
new XElement(S.tableColumns,
new XAttribute(SSNoNamespace.count, totalColumns),
worksheetData.ColumnHeadings.Select((ch, i) =>
new XElement(S.tableColumn,
new XAttribute(SSNoNamespace.id, i + 1),
new XAttribute(SSNoNamespace.name, ch.Value)))),
new XElement(S.tableStyleInfo,
new XAttribute(SSNoNamespace.name, "TableStyleMedium2"),
new XAttribute(SSNoNamespace.showFirstColumn, 0),
new XAttribute(SSNoNamespace.showLastColumn, 0),
new XAttribute(SSNoNamespace.showRowStripes, 1),
new XAttribute(SSNoNamespace.showColumnStripes, 0)));
tXDoc.Add(table);
tdp.PutXDocument();
}
}
}
sDoc.WorkbookPart.WorkbookStylesPart.PutXDocument();
sDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
}
//------------------------------------------------------------------------------------
//------------------------------------------------------------------------------------
//Tableau
public static void AddTableDefinitionPart(WorksheetPart part, List <string> titres, int nbLi, int nbCol)
{
TableDefinitionPart tableDefinitionPart1 = part.AddNewPart <TableDefinitionPart>("vId1");
GenerateTableDefinitionPart1Content(tableDefinitionPart1, titres, nbLi, nbCol);
}
private void ParseTableDefinition(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, Stream tableDefinitionPart, TableDefinitionPart newTableDefinitionPart)
{
using (var reader = OpenXmlReader.Create(tableDefinitionPart))
using (var writer = XmlWriter.Create(newTableDefinitionPart.GetStream(),
new XmlWriterSettings {
Encoding = Encoding.UTF8, CloseOutput = true
}))
{
writer.WriteStartDocument(true);
while (reader.Read())
{
if (reader.ElementType == typeof(Excel.Table))
{
var table = (Excel.Table)reader.LoadCurrentElement();
var code = param.ParseCode(table.Name);
if (code != null)
{
var reference = CellRange.Parse(table.Reference.Value);
var defName = new DefinedName
{
Name = table.Name,
Range = reference,
Code = code,
CacheValue = param.GetValue(code)
};
if (defName.CacheValue is QResult result && result.Values.Count > 0)
{
var index = reference.Start.Row + result.Values.Count;
if (index > reference.End.Row)
{
defName.NewRange = new CellRange(reference.Start, new CellReference(reference.End.Col, index));
table.Reference = defName.NewRange.ToString();
//table.TotalsRowCount = (uint)newrange.Rows;
}
defName.Table = table;
cacheNames[defName.Range.Start.ToString()] = defName;
}
}
WriteElement(writer, table);
}
else if (reader.IsStartElement)
{
WriteStartElement(writer, reader);
}
else if (reader.IsEndElement)
{
writer.WriteEndElement();
}
}
writer.WriteEndDocument();
writer.Flush();
}
}
private void ParseTableDefinition(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, TableDefinitionPart tableDefinitionPart, TableDefinitionPart newTableDefinitionPart)
{
using (var stream = tableDefinitionPart.GetStream())
{
ParseTableDefinition(param, cacheNames, stream, newTableDefinitionPart);
}
}
private void ParseTableDefinition(ExecuteArgs param, Dictionary <string, DefinedName> cacheNames, TableDefinitionPart tableDefinitionPart)
{
using (var temp = new MemoryStream())
{
using (var stream = tableDefinitionPart.GetStream())
stream.CopyTo(temp);
temp.Position = 0;
ParseTableDefinition(param, cacheNames, temp, tableDefinitionPart);
}
}
// Generates content of worksheetPart1.
private static void GenerateWorksheetPart1Content(WorksheetPart worksheetPart1, DataTable dtSource, bool encloseInDataTable)
{
Worksheet worksheet1 = new Worksheet();
worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
SheetData sheetData1 = new SheetData();
// todo: refactor to use bulk xml insert if performance is slow due to large data sets
Row row1 = new Row()
{
RowIndex = 1U
};
Cell cell1;
InlineString inlineString1;
Text text1;
CellValue cv;
// add header row
char charCol = 'A';
foreach (DataColumn dc in dtSource.Columns)
{
cell1 = new Cell()
{
CellReference = charCol.ToString() + "1", DataType = CellValues.InlineString
};
inlineString1 = new InlineString();
text1 = new Text();
text1.Text = dc.ColumnName;
inlineString1.Append(text1);
cell1.Append(inlineString1);
row1.Append(cell1);
charCol = (char)((int)charCol + 1);
}
sheetData1.Append(row1);
// add rows
int rowIx = 2;
foreach (DataRow dr in dtSource.Rows)
{
row1 = new Row()
{
RowIndex = (uint)rowIx
};
charCol = 'A';
foreach (DataColumn dc in dtSource.Columns)
{
if (dc.DataType == typeof(int) || dc.DataType == typeof(decimal))
{
cell1 = new Cell()
{
CellReference = charCol.ToString() + rowIx.ToString()
};
}
else
{
cell1 = new Cell()
{
CellReference = charCol.ToString() + rowIx.ToString(), DataType = CellValues.String
};
}
cv = new CellValue();
cv.Text = dr[dc.ColumnName].ToString();
cell1.Append(cv);
row1.Append(cell1);
charCol = (char)((int)charCol + 1);
}
sheetData1.Append(row1);
rowIx++;
}
worksheet1.Append(sheetData1);
string tableReferenceId = "rId2";
if (encloseInDataTable)
{
TableParts tableParts1 = new TableParts()
{
Count = (UInt32Value)1U
};
TablePart tablePart1 = new TablePart()
{
Id = tableReferenceId
};
tableParts1.Append(tablePart1);
worksheet1.Append(tableParts1);
}
worksheetPart1.Worksheet = worksheet1;
if (encloseInDataTable)
{
TableDefinitionPart tableDefinitionPart1 = worksheetPart1.AddNewPart <TableDefinitionPart>(tableReferenceId);
GenerateTableDefinitionPart1Content(tableDefinitionPart1, dtSource);
}
} // generate content
/// <summary>
/// Method for adding a new table definition part
/// </summary>
/// <param name="worksheet">Worksheet to add the table to</param>
/// <param name="tableStyle">Style to be assigned to the table</param>
/// <param name="useHeaders">Set a header row</param>
/// <param name="fromColumn">Initial column for table</param>
/// <param name="toColumn">Final column for table</param>
/// <param name="fromRow">Intial row for table</param>
/// <param name="toRow">Final row for table</param>
public static OpenXmlPowerToolsDocument Add(SmlDocument doc, string worksheetName, string tableStyle, bool useHeaders, short fromColumn, short toColumn, int fromRow, int toRow)
{
using (OpenXmlMemoryStreamDocument streamDoc = new OpenXmlMemoryStreamDocument(doc))
{
using (SpreadsheetDocument document = streamDoc.GetSpreadsheetDocument())
{
//Getting the id for this table
int tableId = GetNextTableId(document);
//Set the table cell range
string tableRange = string.Format("{0}{1}:{2}{3}", WorksheetAccessor.GetColumnId(fromColumn),
fromRow,
WorksheetAccessor.GetColumnId(toColumn),
toRow);
//Creating a new id for the relationship between the table definition part and the worksheet
string tableRelationShipId = "rId" + Guid.NewGuid();
//Create a new table definition part
WorksheetPart worksheet = WorksheetAccessor.Get(document, worksheetName);
TableDefinitionPart table = worksheet.AddNewPart <TableDefinitionPart>(tableRelationShipId);
//string tableColumns = string.Empty;
XElement tableColumnsXElement = new XElement(ns + "tableColumns", new XAttribute("count", (toColumn - fromColumn) + 1));
//Get the name for table column elements from the first table row
string[] tableHeaders = GetTableHeaders(document, worksheet, fromRow, fromColumn, toColumn);
for (int i = 0; i <= (toColumn - fromColumn); i++)
{
//Create the markup for the SpreadsheetML table column elements
tableColumnsXElement.Add(
new XElement(ns + "tableColumn", new XAttribute("id", i + 1), new XAttribute("name", tableHeaders[i])));
}
XElement tableXElement =
new XElement(ns + "table",
new XAttribute("xmlns", ns), //default namespace
new XAttribute("id", tableId),
new XAttribute("name", "Table" + tableId.ToString()),
new XAttribute("displayName", "Table" + tableId.ToString()),
new XAttribute("ref", tableRange),
new XAttribute("totalsRowShown", "0"));
if (useHeaders)
{
tableXElement.Add(
new XElement(ns + "autoFilter", new XAttribute("ref", tableRange)));
}
tableXElement.Add(tableColumnsXElement);
tableXElement.Add(
new XElement(ns + "tableStyleInfo",
new XAttribute("name", tableStyle),
new XAttribute("showFirstColumn", "0"),
new XAttribute("showLastColumn", "0"),
new XAttribute("showRowStripes", "0"),
new XAttribute("showColumnStripes", "0")));
//Write the markup to the Table Definition Part Stream
XmlWriter tablePartStreamWriter = XmlWriter.Create(table.GetStream());
tableXElement.WriteTo(tablePartStreamWriter);
tablePartStreamWriter.Flush();
tablePartStreamWriter.Close();
//Create or modify the table parts definition at worksheet (for setting the relationship id with the new table)
XDocument worksheetMarkup = worksheet.GetXDocument();
//Look for the tableParts element at worksheet markup
XElement tablePartsElement = worksheetMarkup.Root.Element(ns + "tableParts");
if (tablePartsElement != null)
{
//tableParts elements does exist at worksheet markup
//increment the tableParts count attribute value
short tableCount = System.Convert.ToInt16(tablePartsElement.Attribute("count").Value);
tablePartsElement.SetAttributeValue("count", tableCount++.ToString());
}
else
{
//tableParts does not exist at worksheet markup
//create a new tableParts element
tablePartsElement = new XElement(ns + "tableParts",
new XAttribute(ns + "count", "1"));
worksheetMarkup.Root.Add(tablePartsElement);
}
//create the tablePart element
XElement tablePartEntryElement = new XElement(ns + "tablePart",
new XAttribute(relationshipns + "id", tableRelationShipId));
//add the new tablePart element to the worksheet tableParts element
tablePartsElement.Add(tablePartEntryElement);
worksheet.PutXDocument();
}
return(streamDoc.GetModifiedDocument());
}
}
/// <summary>
/// Generate Excel Document.
/// </summary>
/// <param name="reportResult">QueryResult</param>
/// <returns>MemoryStream</returns>
public static MemoryStream CreateExcelDocument(ReportResult reportResult, List <DataRow> rows)
{
sharedStringIndex = 0;
sharedStrings = new ConcurrentDictionary <int, string>();
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());
WorksheetPart newWorksheetPart = spreadSheet.WorkbookPart.AddNewPart <WorksheetPart>("rId1");
newWorksheetPart.Worksheet = new Worksheet();
//Add columns of constant width
int columnIndx = 1;
Columns cols = new Columns();
foreach (ReportColumn column in reportResult.Metadata.ReportColumns.Values)
{
if (!column.IsHidden && column.Type != "Image")
{
cols.Append(CreateColumnData(columnIndx));
columnIndx++;
}
}
newWorksheetPart.Worksheet.Append(cols);
newWorksheetPart.Worksheet.Save();
// Create a new Excel worksheet
SheetData sheetData = newWorksheetPart.Worksheet.AppendChild(new SheetData());
// Create Styles and Insert into Workbook
WorkbookStylesPart stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId3");
Stylesheet styles = new ExportDataStylesheet();
styles.Save(stylesPart);
// Insert Datatable data into the worksheet.
InsertTableData(reportResult, sheetData, rows);
//Create table part.
TableDefinitionPart tableDefinitionPart = newWorksheetPart.AddNewPart <TableDefinitionPart>("rId1");
GenerateTablePartContent(tableDefinitionPart, reportResult, rows, columnIndx - 2);
TableParts tableParts1 = new TableParts()
{
Count = (UInt32Value)1U
};
TablePart tablePart1 = new TablePart()
{
Id = "rId1"
};
tableParts1.Append(tablePart1);
newWorksheetPart.Worksheet.Append(tableParts1);
newWorksheetPart.Worksheet.Save();
//add shared stringd
SharedStringTablePart sharedStringTablePart = spreadSheet.WorkbookPart.AddNewPart <SharedStringTablePart>("rId2");
SharedStringTable sharedStringTable = new SharedStringTable();
foreach (string sharedString in sharedStrings.Values)
{
CreateSharedStringItem(sharedStringTable, sharedString);
}
sharedStringTablePart.SharedStringTable = sharedStringTable;
// 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 static void ExcelTableToDataTable(this DataTable dt, string path, string sheetName, string tableName)
{
dt.TableName = tableName;
using (SpreadsheetDocument document = SpreadsheetDocument.Open(path, true))
{
//References to the workbook and Shared String Table.
var id = document.WorkbookPart.Workbook.Descendants <Sheet>().First(s => s.Name == sheetName).Id;
//Get sheet my the sheet id
WorksheetPart sheet = (WorksheetPart)document.WorkbookPart.GetPartById(id);
//Get Excel table definition
TableDefinitionPart tableDefinitionPart = (from t in sheet.TableDefinitionParts where t.Table.DisplayName == tableName select t).First();
//Get the cell reference for the Excel table
string cellReference = tableDefinitionPart.Table.Reference.ToString();
//Get start and end Row and Column
Regex regexCellName = new Regex("[A-Za-z]+");
Regex regexRowIndex = new Regex(@"\d+");
int startRow = Convert.ToInt32(regexRowIndex.Match(OpenXmlExt.ReferenceIndex(cellReference, 0)).ToString());
int startColumn = Convert.ToInt32(OpenXmlExt.TranslateColumnNameToIndex(regexCellName.Match(OpenXmlExt.ReferenceIndex(cellReference, 0)).ToString()));
int endRow = Convert.ToInt32(regexRowIndex.Match(OpenXmlExt.ReferenceIndex(cellReference, 1)).ToString());
int endColumn = Convert.ToInt32(OpenXmlExt.TranslateColumnNameToIndex(regexCellName.Match(OpenXmlExt.ReferenceIndex(cellReference, 1)).ToString()));
//Get column names
var columnNames = from n in tableDefinitionPart.Table.TableColumns
select(from a in XDocument.Load(new StringReader(n.OuterXml)).Descendants()
select a.Attribute(XName.Get("name")).Value).First();
//Convert Excel table to ADO.NET DataTable
DataColumn dataColumn;
foreach (var name in columnNames)
{
dataColumn = new DataColumn(name.Replace(" ", string.Empty), typeof(System.String));
dataColumn.Caption = name;
dt.Columns.Add(dataColumn);
}
SharedStringTable sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
IEnumerable <Row> dataRows =
from row in sheet.Worksheet.Descendants <Row>()
select row;
DataRow dataRow;
foreach (Row row in dataRows)
{
if (row.RowIndex > startRow && row.RowIndex <= endRow)
{
var cells = from cell in row.Descendants <Cell>() select cell;
int rowIndex;
int columnIndex;
string cellValue;
int absoluteColumnIndex;
dataRow = dt.NewRow();
foreach (var cell in cells)
{
rowIndex = Convert.ToInt32(regexRowIndex.Match(cell.CellReference.Value).ToString());
columnIndex = OpenXmlExt.TranslateColumnNameToIndex(regexCellName.Match(cell.CellReference.Value).ToString());
absoluteColumnIndex = columnIndex - startColumn;
if (columnIndex >= startColumn && columnIndex <= endColumn)
{
if (cell.CellValue != null)
{
cellValue = cell.CellValue.InnerText;
if (cell.DataType == "s")
{
cellValue = sharedStrings.ElementAt(Convert.ToInt32(cellValue)).InnerText;
}
dataRow[absoluteColumnIndex] = cellValue;
}
}
}
dt.Rows.Add(dataRow);
}
}
}
}