// 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 UpdateWorksheetContent(WorksheetPart workSheetPart, List<ExcelSheet> templateRows, SharedStringTable sharedStrings, List<ProjectFile> projectFiles, Customer customer, User user)
{
IEnumerable<SheetData> sheets = workSheetPart.Worksheet.ChildElements.OfType<SheetData>();
foreach (var sheetData in sheets)
{
sheetData.RemoveAllChildren<Row>();
List<String> strings = sharedStrings.ChildElements.OfType<SharedStringItem>().Where(shItem => shItem.ChildElements.OfType<Text>().Any()).Select(shItem =>
{
var firstOrDefault = shItem.ChildElements.OfType<Text>().FirstOrDefault();
return firstOrDefault != null ? firstOrDefault.Text : String.Empty;
}).ToList();
//from this point is the content
int linesToAdd = 0;
var templateSheet = templateRows.FirstOrDefault(row => row.SheetData.Equals(sheetData));
if (templateSheet != null)
{
foreach (var projectFile in projectFiles)
{
GenerateWorksheetDataContent(sheetData, templateSheet.Rows, linesToAdd, new List<TokensProvider> {projectFile, customer, user}, strings);
linesToAdd += (templateSheet.Rows.Count + 5);
}
}
}
}
private Sheet GetCurrentSheet(WorksheetPart worksheetPart)
{
var sheetId = workbookPart.GetIdOfPart(worksheetPart);
var correspondingSheet = sheets.FirstOrDefault(
s => s.Id.HasValue && s.Id.Value == sheetId);
return correspondingSheet;
}
public OpenXLRow(WorkbookPart wbPart, WorksheetPart wsPart, List<string> columns, Row xRow)
{
this.WbPart = wbPart;
this.WsPart = wsPart;
this.Columns = columns;
this._cells = GetCells(xRow);
}
/// <summary>
/// Creates an empty worksheet part
/// </summary>
/// <returns></returns>
public OpenXmlSDK.WorksheetPart Create()
{
XDocument xDocument = CreateEmptyWorksheet();
OpenXmlSDK.WorksheetPart part = Add(xDocument);
return(part);
}
public static void SetCellDate(WorksheetPart worksheetPart, DateTime value, string columnName, DocumentFormat.OpenXml.Spreadsheet.Row row)
{
Cell cell = GetCell(worksheetPart.Worksheet, columnName, row);
cell.CellValue = new CellValue(value.ToOADate().ToString("0"));
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
}
public static void SetInlineCell(WorksheetPart worksheetPart, string text, string columnName, uint rowIndex)
{
Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex);
cell.CellValue = new CellValue(string.IsNullOrWhiteSpace(text) ? "" : text);
cell.DataType = new EnumValue<CellValues>(CellValues.InlineString);
}
protected internal Sheet(ref DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart, ref DocumentFormat.OpenXml.Spreadsheet.Sheet sheet)
{
this.worksheetPart = worksheetPart;
this.worksheet = this.worksheetPart.Worksheet;
this.sheetData = this.worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>();
this.sheet = sheet;
}
public static void SetCell(WorksheetPart worksheetPart, string text, string columnName, DocumentFormat.OpenXml.Spreadsheet.Row row, out Cell cell)
{
cell = GetCell(worksheetPart.Worksheet, columnName, row);
cell.CellValue = new CellValue(string.IsNullOrWhiteSpace(text) ? "" : text);
cell.DataType = new EnumValue<CellValues>(CellValues.String);
}
/// <summary>
/// Creates the content of the shet (columns, rows, cells)
/// </summary>
/// <param name="spreadsheetDocument">The spreadsheet containing the sheets</param>
/// <param name="worksheetPart">The worksheetpart for this item</param>
private void InsertTextIntoCells(SpreadsheetDocument spreadsheetDocument, WorksheetPart worksheetPart)
{
// Get the SharedStringTablePart. If it does not exist, create a new one.
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Any())
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
#region Excel headers
string header1 = "ResourceTypeID";
int index1 = _sharedResources.InsertSharedStringItem(header1, shareStringPart);
Cell headerCell1 = _sharedResources.InsertCellInWorksheet("A", 1, worksheetPart);
headerCell1.CellValue = new CellValue(index1.ToString(CultureInfo.InvariantCulture));
headerCell1.DataType = new EnumValue<CellValues>(CellValues.SharedString);
string header2 = "ResourceType";
int index2 = _sharedResources.InsertSharedStringItem(header2, shareStringPart);
Cell headerCell2 = _sharedResources.InsertCellInWorksheet("B", 1, worksheetPart);
headerCell2.CellValue = new CellValue(index2.ToString(CultureInfo.InvariantCulture));
headerCell2.DataType = new EnumValue<CellValues>(CellValues.SharedString);
#endregion
#region insert the items from the temporary list into the ktUIOrder excel sheet
int columnCount = 1;
uint rowCount = 2;
foreach (ktResourceType resourceType in _resourceType.ktResourceTypeList)
{
if (columnCount >= 2)
{
columnCount = 1;
}
string text1 = resourceType.ResourceTypeID;
Cell cell1 = _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true), rowCount, worksheetPart);
cell1.CellValue = new CellValue(text1);
cell1.DataType = CellValues.Number;
columnCount++;
string text2 = resourceType.ResourceType;
Cell cell2 = _sharedResources.InsertCellInWorksheet(_sharedResources.Number2String(columnCount, true), rowCount, worksheetPart);
cell2.CellValue = new CellValue(text2);
cell2.DataType = CellValues.String;
columnCount++;
rowCount++;
}
#endregion
worksheetPart.Worksheet.Save();
}
protected override DataTable ReadFile()
{
string outputPath = CopyFileToAppData();
DataTable dataTable = new DataTable();
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(outputPath, false))
{
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = GetWorksheetPartByName(spreadsheetDocument, _sheetName);
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheetPart.Worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.SheetData>().First();
IEnumerable <Row> rows = sheetData.Descendants <Row>();
foreach (Cell cell in rows.ElementAt(0))
{
dataTable.Columns.Add(GetCellValue(spreadsheetDocument, cell));
}
for (int i = 1; i < rows.Count() - 1; i++)
{
DataRow tempRow = dataTable.NewRow();
for (int j = 0; j < rows.ElementAt(i).Descendants <Cell>().Count(); j++)
{
tempRow[j] = GetCellValue(spreadsheetDocument, rows.ElementAt(i).Descendants <Cell>().ElementAt(j));
}
dataTable.Rows.Add(tempRow);
}
}
return(dataTable);
}
/// <summary>
/// Gets shapes within a worksheet that have a specified name (id)
/// </summary>
/// <param name="worksheetPart">The <see cref="OpenXmlPackaging.WorksheetPart"/></param>
/// <param name="name">The id/name required</param>
/// <returns></returns>
private static IEnumerable <DrawingSpreadsheet.Shape> GetShapesWithName(OpenXmlPackaging.WorksheetPart worksheetPart, string name)
{
IEnumerable <DrawingSpreadsheet.Shape> shapes = worksheetPart.DrawingsPart.WorksheetDrawing.Descendants <DrawingSpreadsheet.Shape>()
.Where(s => s.NonVisualShapeProperties.NonVisualDrawingProperties.Name == name);
return(shapes);
}
public Spreadsheet.Sheet AddSheet(string sheetName)
{
uint maxSheetId = 1;
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = null;
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = null;
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = null;
wsPart = this.workbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.WorksheetPart>();
wsPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
sheets = this.workbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
maxSheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max();
maxSheetId++;
}
sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet();
sheet.Id = this.workbookPart.GetIdOfPart(wsPart);
sheet.SheetId = maxSheetId;
sheet.Name = sheetName;
//if (this.workbookPart.Workbook.Sheets == null)
//{
// sheets = this.workbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
//}
sheets.Append(sheet);
Spreadsheet.Sheet newSheet = new Spreadsheet.Sheet(ref wsPart, ref sheet);
this.sheetCollection.Add(newSheet);
return(newSheet);
}
public static void SetCellDateTime(WorksheetPart worksheetPart, DateTime value, string columnName, uint rowIndex)
{
Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex);
cell.CellValue = new CellValue(value.ToOADate().ToString("0.000#####"));
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
}
public void AddNewWorksheet()
{
WorkbookPart workbookPart = spreadSheet.WorkbookPart;
WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
newWorksheetPart.Worksheet.Save();
CurrentWorksheetPart = newWorksheetPart;
//workbookPart.SharedStringTablePart.SharedStringTable.Count = workbookPart.SharedStringTablePart.SharedStringTable.Count + 1;
//workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount = workbookPart.SharedStringTablePart.SharedStringTable.UniqueCount + 1;
string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);
Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
uint sheetId = 1;
if (sheets.Elements<Sheet>().Count() > 0)
{
sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
string sheetName = "Sheet" + sheetId;
// Append the new worksheet and associate it with the workbook.
Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
}
public string GetValueCell(ref WorkbookPart wbPart, ref WorksheetPart wsPart, string addressName)
{
string value = null;
Cell theCell = wsPart.Worksheet.Descendants<Cell>().
Where(c => c.CellReference == addressName).FirstOrDefault();
value = GetCleanValueCell(theCell, ref wbPart);
return value;
}
public void CreateHeader(WorksheetPart worksheetPart)
{
if (worksheetPart == null)
{
throw new ArgumentNullException("worksheetPart");
}
ExcelUtility.SetStringCell(worksheetPart.Worksheet, "B", 2, ServiceOffering.Id.ToString());
ExcelUtility.SetStringCell(worksheetPart.Worksheet, "C", 2, ServiceOffering.Name);
}
public void CreateErrorRows(WorksheetPart worksheetPart)
{
for (int rowIndex = 0; rowIndex < ErrorRows.Count; rowIndex++)
{
for (int i = 0; i < ErrorRows[rowIndex].RowErrors.Count; i++)
{
ExcelUtility.SetStringCell(worksheetPart.Worksheet, CellDictionary[i], (uint)rowIndex + 4, ErrorRows[rowIndex].RowErrors[i]);
}
}
}
/// <summary>
/// Get the values for the table header row (table initial row)
/// </summary>
/// <param name="worksheet">Worksheet where the table is being defined</param>
/// <param name="row">Table initial row</param>
/// <param name="fromColumn">Table initial column</param>
/// <param name="toColumn">Table final column</param>
/// <returns></returns>
private string[] GetTableHeaders(OpenXmlSDK.WorksheetPart worksheet, int row, short fromColumn, short toColumn)
{
List <string> tableHeaders = new List <string>();
for (short c = fromColumn; c <= toColumn; c++)
{
tableHeaders.Add(WorksheetAccessor.GetValue(worksheet, c, row));
}
return(tableHeaders.ToArray <string>());
}
/// <summary>
/// Creates a spreadsheet document from a value table
/// </summary>
/// <param name="filePath">Path to store the document</param>
/// <param name="headerList">Contents of first row (header)</param>
/// <param name="valueTable">Contents of data</param>
/// <param name="initialRow">Row to start copying data from</param>
/// <returns></returns>
public static SpreadsheetDocument Create(string filePath, List <string> headerList, string[][] valueTable, int initialRow)
{
headerRow = initialRow;
SpreadsheetDocument document = Create(filePath);
//Creates a worksheet with given data
OpenXmlSDK.WorksheetPart worksheet = document.Worksheets.Create(headerList, valueTable, headerRow);
XDocument worksheetDocument = document.GetXDocument(document.Document.WorkbookPart);
return(document);
}
/// <summary>
/// Set the width for a range of columns
/// </summary>
/// <param name="worksheet">Worksheet containing the columns to be affected</param>
/// <param name="fromColumn">Initial column to affect</param>
/// <param name="toColumn">Final column to affect</param>
/// <param name="width">Column width</param>
public static void SetColumnWidth(OpenXmlSDK.WorksheetPart worksheet, short fromColumn, short toColumn, int width)
{
//Get the worksheet markup
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
//Look for worksheet cols element
XElement colsXElement = worksheetXDocument.Root.Element(ns + "cols");
if (colsXElement == null)
{
//cols elements does not exist
//create a new one
colsXElement = new XElement(ns + "cols");
//create a new col element (for setting the width)
//the col element could span more than one column -span is controlled by min (initial column) and max (final column) attributes
colsXElement.Add(new XElement(ns + "col",
new XAttribute("min", fromColumn.ToString()),
new XAttribute("max", toColumn.ToString()),
new XAttribute("width", width.ToString()),
new XAttribute("customWidth", "1")));
//cols element must be added before worksheet sheetData element
worksheetXDocument.Root.Element(ns + "sheetData").AddBeforeSelf(colsXElement);
}
else
{
//look for a col element for the column range indicated for fromColumn and toColumn
XElement colXElement = colsXElement.Elements(ns + "col")
.Where(c => (System.Convert.ToInt32(c.Attribute("min").Value) == fromColumn) && (System.Convert.ToInt32(c.Attribute("max").Value) == toColumn)).FirstOrDefault();
if (colXElement != null)
{
//col element does exist
//change its width value
colXElement.SetAttributeValue("width", width);
}
else
{
//col element does not exist
//create a new one
colsXElement.Add(new XElement(ns + "col",
new XAttribute("min", fromColumn.ToString()),
new XAttribute("max", toColumn.ToString()),
new XAttribute("width", width.ToString()),
new XAttribute("customWidth", "1")));
}
}
//Update the worksheet part markup at worksheet part stream
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
private void AddReportColumns(WorkbookPart workbookPart, WorksheetPart worksheetPart, List<string> columnNames)
{
for (int i = 0; i < columnNames.Count; i++)
{
string fieldName = columnNames[i];
WorkbookHelper.AddCellText(
workbookPart,
worksheetPart,
fieldName,
(uint)(i + 1), 1U);
}
}
private WorksheetPart wsSheet = null; // WorkSheetPart
#endregion Fields
#region Constructors
public LogGenerator(string fn)
{
//// get spreadsheet path from constructor
//path = folder;
//// File name is based on date and time
//DateTime now = DateTime.Now;
//// Construct the spreadsheet filename
//string fn = string.Format("{0}\\report_{1}-{2}-{3}_{4}{5}{6}.xlsx",
// path, now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second);
if (File.Exists(fn))
{
doc = SpreadsheetDocument.Open(fn, true);
}
else
{
//
// Create the Spreadsheet document
//
doc = SpreadsheetDocument.Create(fn, SpreadsheetDocumentType.Workbook);
//
// Add WoorkBookPart to the document
//
wbPart = doc.AddWorkbookPart();
wbPart.Workbook = new Workbook();
wbPart.Workbook.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
//
// Add WorkSheetPart to the WorkBookPart
//
wsSheet = wbPart.AddNewPart<WorksheetPart>();
wsSheet.Worksheet = new Worksheet(new SheetData());
wsSheet.Worksheet.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
//
// Add sheets to the WorkBook
//
sheets = doc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
//
// Append new sheet and associate it with the workbook
//
sheet = new Sheet() { Id = doc.WorkbookPart.GetIdOfPart(wsSheet), SheetId = 1, Name = wsName };
sheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
sheets.Append(sheet);
wbPart.Workbook.Save();
//CreatePackage(fn);
//if (File.Exists(fn))
//{
// TestXls();
//}
}
}
private void AddReportLines(WorkbookPart workbookPart, WorksheetPart worksheetPart, Report report, uint startRow)
{
for (int iRow = 0; iRow < report.ReportLines.Count; iRow++)
{
for (int iCol = 0; iCol < report.ColumnNames.Count; iCol++)
{
WorkbookHelper.AddCellText(
workbookPart,
worksheetPart,
report[iRow][iCol].ToString(),
(uint)iCol + 1, (uint)iRow + startRow + 1);
}
}
}
public static void SetCellValue(WorksheetPart worksheetPart, decimal? value, string columnName, DocumentFormat.OpenXml.Spreadsheet.Row row, out Cell cell)
{
cell = GetCell(worksheetPart.Worksheet, columnName, row);
if (value.HasValue)
{
cell.CellValue = new CellValue(value.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
}
else
{
cell.CellValue = new CellValue();
cell.DataType = new EnumValue<CellValues>(CellValues.InlineString);
}
}
public static void InsertValuesInWorksheet(WorksheetPart worksheetPart, uint rowIdx, List<string> values)
{
var worksheet = worksheetPart.Worksheet;
var sheetData = worksheet.GetFirstChild<SheetData>();
Row row = new Row();
values.ForEach(v =>
{
Cell cell = new Cell() { DataType = CellValues.InlineString };
InlineString inlineString = new InlineString();
inlineString.Append(new Text() { Text = v });
cell.Append(inlineString);
row.Append(cell);
});
sheetData.Append(row);
}
private void InitializeWorksheets()
{
IEnumerable <DocumentFormat.OpenXml.Spreadsheet.Sheet> oXmlSheets = this.workbookPart.Workbook.Sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>();
IEnumerable <DocumentFormat.OpenXml.Packaging.WorksheetPart> wsParts = this.workbookPart.WorksheetParts;
if (oXmlSheets.Count() > 0)
{
for (int i = 0; i < oXmlSheets.Count(); i++)
{
DocumentFormat.OpenXml.Spreadsheet.Sheet oXmlSheet = oXmlSheets.ElementAt(i);
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = this.workbookPart.GetPartById(oXmlSheet.Id) as DocumentFormat.OpenXml.Packaging.WorksheetPart;
Spreadsheet.Sheet sheet = new Spreadsheet.Sheet(ref wsPart, ref oXmlSheet);
this.sheetCollection.Add(sheet);
}
}
}
/// <summary>
/// Creates a spreadsheet document with a chart from a value table
/// </summary>
/// <param name="filePath">Path to store the document</param>
/// <param name="headerList">Contents of first row (header)</param>
/// <param name="valueTable">Contents of data</param>
/// <param name="chartType">Chart type</param>
/// <param name="categoryColumn">Column to use as category for charting</param>
/// <param name="columnsToChart">Columns to use as data series</param>
/// <param name="initialRow">Row index to start copying data</param>
/// <returns>SpreadsheetDocument</returns>
//public static void Create(SpreadsheetDocument document, List<string> headerList, string[][] valueTable, ChartType chartType, string categoryColumn, List<string> columnsToChart, int initialRow)
//{
// headerRow = initialRow;
// //Creates worksheet with data
// WorksheetPart worksheet = WorksheetAccessor.Create(document, headerList, valueTable, headerRow);
// //Creates chartsheet with given series and category
// string sheetName = GetSheetName(worksheet, document);
// ChartsheetPart chartsheet =
// ChartsheetAccessor.Create(document,
// chartType,
// GetValueReferences(sheetName, categoryColumn, headerList, columnsToChart, valueTable),
// GetHeaderReferences(sheetName, categoryColumn, headerList, columnsToChart, valueTable),
// GetCategoryReference(sheetName, categoryColumn, headerList, valueTable)
// );
//}
/// <summary>
/// Gets the internal name of a worksheet from a document
/// </summary>
private static string GetSheetName(WorksheetPart worksheet, SpreadsheetDocument document)
{
//Gets the id of worksheet part
string partId = document.WorkbookPart.GetIdOfPart(worksheet);
XDocument workbookDocument = document.WorkbookPart.GetXDocument();
//Gets the name from sheet tag related to worksheet
string sheetName =
workbookDocument.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Where(
t =>
t.Attribute(relationshipsns + "id").Value == partId
).First()
.Attribute("name").Value;
return sheetName;
}
/// <summary>
/// Adds a given worksheet to the document
/// </summary>
/// <param name="worksheet">Worksheet document to add</param>
/// <returns>Worksheet part just added</returns>
public OpenXmlSDK.WorksheetPart Add(XDocument worksheet)
{
// Associates base content to a new worksheet part
OpenXmlSDK.WorkbookPart workbook = ((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart;
OpenXmlSDK.WorksheetPart worksheetPart = workbook.AddNewPart <OpenXmlSDK.WorksheetPart>();
XDocument worksheetDocument = parentDocument.GetXDocument(worksheetPart);
if (worksheetDocument.Root == null)
{
worksheetDocument.Add(
new XElement("root")
);
}
worksheetDocument.Root.ReplaceWith(worksheet.Root);
// Associates the worksheet part to the workbook part
XDocument document = parentDocument.GetXDocument(((OpenXmlSDK.SpreadsheetDocument)parentDocument.Document).WorkbookPart);
int sheetId =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Count() + 1;
int worksheetCount =
document.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Where(
t =>
t.Attribute("name").Value.StartsWith("sheet", StringComparison.OrdinalIgnoreCase)
)
.Count() + 1;
// Adds content to workbook document to reference worksheet document
document.Root
.Element(ns + "sheets")
.Add(
new XElement(ns + "sheet",
new XAttribute("name", string.Format("sheet{0}", worksheetCount)),
new XAttribute("sheetId", sheetId),
new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(worksheetPart))
)
);
return(worksheetPart);
}
/// <summary>
/// Gets the internal name of a worksheet from a document
/// </summary>
private static string GetSheetName(OpenXmlSDK.WorksheetPart worksheet, SpreadsheetDocument document)
{
//Gets the id of worksheet part
string partId = document.Document.WorkbookPart.GetIdOfPart(worksheet);
XDocument workbookDocument = document.GetXDocument(document.Document.WorkbookPart);
//Gets the name from sheet tag related to worksheet
string sheetName =
workbookDocument.Root
.Element(ns + "sheets")
.Elements(ns + "sheet")
.Where(
t =>
t.Attribute(relationshipsns + "id").Value == partId
).First()
.Attribute("name").Value;
return(sheetName);
}
/// <summary>
/// Set the value for a specific cell
/// </summary>
/// <param name="worksheet">Worksheet part containing the cell to be affected</param>
/// <param name="fromColumn">Initial column for setting the value</param>
/// <param name="fromRow">Initial row for setting the value</param>
/// <param name="toColumn">Final column for setting the value</param>
/// <param name="toRow">Final row for setting the value</param>
/// <param name="value">Cell value</param>
public static void SetCellValue(OpenXmlSDK.WorksheetPart worksheet, int fromRow, int toRow, short fromColumn, short toColumn, string value)
{
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
for (int row = fromRow; row <= toRow; row++)
{
for (int col = fromColumn; col <= toColumn; col++)
{
AddValue(worksheetXDocument, row, col, value);
}
}
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
public static void AddCellText(WorkbookPart workbookPart,
WorksheetPart worksheetPart,
string text,
uint colIndex,
uint rowIndex)
{
string columnName = GetColumnIdentifier((int)colIndex);
// Insert the text into the SharedStringTablePart.
int index = InsertSharedStringItem(workbookPart, text);
// Insert cell A1 into the new worksheet.
Cell cell = InsertCellInWorksheet(worksheetPart, columnName, rowIndex);
// Set the value of cell A1.
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
}
private void CreateWorkSheet(WorksheetPart worksheetPart, DataGridView dgv)
{
Worksheet worksheet = new Worksheet();
SheetData sheetData = new SheetData();
UInt32Value currRowIndex = 1U;
Row excelRow = new Row();
foreach(DataGridViewColumn col in dgv.Columns)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
CellValue cellValue = new CellValue();
cellValue.Text = col.HeaderText;
cell.Append(cellValue);
excelRow.Append(cell);
}
sheetData.Append(excelRow);
currRowIndex++;
foreach (DataGridViewRow row in dgv.Rows)
{
excelRow = new Row();
excelRow.RowIndex = currRowIndex++;
foreach (DataGridViewCell col in row.Cells)
{
Cell cell = new Cell();
CellValue cellValue = new CellValue();
cell.DataType = CellValues.String;
cellValue.Text = col.Value?.ToString();
cell.Append(cellValue);
excelRow.Append(cell);
}
sheetData.Append(excelRow);
}
SheetFormatProperties formattingProps = new SheetFormatProperties()
{
DefaultRowHeight = 20D,
DefaultColumnWidth = 20D
};
worksheet.Append(formattingProps);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
}
/// <summary>
/// Apply a cell style to a specific cell
/// </summary>
/// <param name="worksheet">worksheet containing the cell to be affected</param>
/// <param name="fromColumn">Starting Cell Column</param>
/// <param name="toColumn">Ending Cell Column</param>
/// <param name="fromRow">Starting Cell Row</param>
/// <param name="toRow">Ending Cell Row</param>
/// <param name="cellStyle">Cell Style</param>
public static void SetCellStyle(OpenXmlSDK.WorksheetPart worksheet, short fromColumn, short toColumn, int fromRow, int toRow, string cellStyle)
{
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
for (int row = fromRow; row <= toRow; row++)
{
for (short col = fromColumn; col <= toColumn; col++)
{
XElement cellXelement = GetCell(worksheetXDocument, col, row);
cellXelement.SetAttributeValue("s", styles.GetCellStyleIndex(cellStyle));
}
}
XmlWriter worksheetWriter = XmlTextWriter.Create(worksheet.GetStream(System.IO.FileMode.Create));
worksheetXDocument.WriteTo(worksheetWriter);
worksheetWriter.Flush();
worksheetWriter.Close();
}
private static OpenXmlPackaging.WorksheetPart GetWorksheetPartByName(
DocumentFormat.OpenXml.Packaging.SpreadsheetDocument document, string sheetName)
{
IEnumerable <OpenXmlSpread.Sheet> sheets =
document.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>()
.Elements <OpenXmlSpread.Sheet>().Where(s => s.Name == sheetName);
if (sheets.Count() == 0)
{
throw new Exception(String.Format("A planilha {0} não existe", sheetName));
}
string relationshipId = sheets.First().Id.Value;
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart =
(DocumentFormat.OpenXml.Packaging.WorksheetPart)
document.WorkbookPart.GetPartById(relationshipId);
return(worksheetPart);
}
// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet.
// If the cell already exists, returns it.
public Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string cellReference = columnName + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
Row row;
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);
}
// 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();
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
//worksheet.Save(); <-- Slow bastard!!
return newCell;
}
}
public WorksheetIndexer(WorksheetPart toIndex, Sheet sheet)
{
if (toIndex == null)
{
throw new ArgumentNullException("toIndex");
}
// Set the sheet.
this.sheet = sheet;
// Index the sheet data.
var sheetData = toIndex.Worksheet.Descendants<SheetData>().FirstOrDefault();
this.SheetData = new ArrayBasedSheetDataIndexer(sheetData);
// Add the validators.
this.dataValidations = toIndex.Worksheet.Descendants<DataValidation>().ToList();
// Set the worksheet.
this.Worksheet = toIndex.Worksheet;
}
/// <summary>
/// Creates a spreadsheet document with a chart from a value table
/// </summary>
/// <param name="filePath">Path to store the document</param>
/// <param name="headerList">Contents of first row (header)</param>
/// <param name="valueTable">Contents of data</param>
/// <param name="chartType">Chart type</param>
/// <param name="categoryColumn">Column to use as category for charting</param>
/// <param name="columnsToChart">Columns to use as data series</param>
/// <param name="initialRow">Row index to start copying data</param>
/// <returns>SpreadsheetDocument</returns>
public static SpreadsheetDocument Create(string filePath, List <string> headerList, string[][] valueTable, ChartType chartType, string categoryColumn, List <string> columnsToChart, int initialRow)
{
headerRow = initialRow;
SpreadsheetDocument document = Create(filePath);
//Creates worksheet with data
OpenXmlSDK.WorksheetPart worksheet = document.Worksheets.Create(headerList, valueTable, headerRow);
XDocument worksheetDocument = document.GetXDocument(document.Document.WorkbookPart);
//Creates chartsheet with given series and category
string sheetName = GetSheetName(worksheet, document);
OpenXmlSDK.ChartsheetPart chartsheet =
document.Chartsheets.Create(
chartType,
GetValueReferences(sheetName, categoryColumn, headerList, columnsToChart, valueTable),
GetHeaderReferences(sheetName, categoryColumn, headerList, columnsToChart, valueTable),
GetCategoryReference(sheetName, categoryColumn, headerList, valueTable)
);
return(document);
}
public static ShapeModel GetShapeModel(OpenXmlPackaging.WorksheetPart wsPart, string id)
{
Guard.IsNotNull(wsPart, "wsPart");
Guard.IsNotNullOrEmpty(id, "id");
ShapeModel model = null;
// Get all drawing shapes in the worksheet with a specified name
IEnumerable <DrawingSpreadsheet.Shape> shapes = GetShapesWithName(wsPart, id);
int countOfShapes = shapes.Count();
if (countOfShapes > 0)
{
DrawingSpreadsheet.TwoCellAnchor anchor = shapes.First().Ancestors <DrawingSpreadsheet.TwoCellAnchor>().FirstOrDefault();
model = new ShapeModel(wsPart.Worksheet, anchor);
model.HasMoreThanOneInstance = countOfShapes > 1;
}
return(model);
}
/// <summary>
/// Creates a worksheet document and inserts data into it
/// </summary>
/// <param name="headerList">List of values that will act as the header</param>
/// <param name="valueTable">Values for worksheet content</param>
/// <param name="headerRow">Header row</param>
/// <returns></returns>
internal OpenXmlSDK.WorksheetPart Create(List <string> headerList, string[][] valueTable, int headerRow)
{
XDocument xDocument = CreateEmptyWorksheet();
for (int i = 0; i < headerList.Count; i++)
{
AddValue(xDocument, headerRow, i + 1, headerList[i]);
}
int rows = valueTable.GetLength(0);
int cols = valueTable[0].GetLength(0);
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < cols; j++)
{
AddValue(xDocument, i + headerRow + 1, j + 1, valueTable[i][j]);
}
}
OpenXmlSDK.WorksheetPart part = Add(xDocument);
return(part);
}
/// <summary>
/// Get value for a cell in a worksheet
/// </summary>
/// <param name="worksheet"></param>
/// <param name="column"></param>
/// <param name="row"></param>
/// <remarks>Author:Johann Granados Company: Staff DotNet Creation Date: 8/30/2008</remarks>
/// <returns></returns>
public static string GetValue(OpenXmlSDK.WorksheetPart worksheet, short column, int row)
{
XDocument worksheetXDocument = XDocument.Load(new XmlTextReader(worksheet.GetStream()));
XElement cellValueXElement = GetCell(worksheetXDocument, column, row);
if (cellValueXElement != null)
{
if (cellValueXElement.Element(ns + "v") != null)
{
return(sharedStrings.GetSharedString(System.Convert.ToInt32(cellValueXElement.Value)));
}
else
{
return(cellValueXElement.Element(ns + "is").Element(ns + "t").Value);
}
}
else
{
return(string.Empty);
}
}
internal static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
var worksheet = worksheetPart.Worksheet;
var sheetData = worksheet.GetFirstChild<SheetData>();
var cellReference = columnName + rowIndex;
Row row;
if (sheetData.Elements<Row>().Count(r => r.RowIndex == rowIndex) != 0)
row = sheetData.Elements<Row>().First(r => r.RowIndex == rowIndex);
else
{
row = new Row() { RowIndex = rowIndex };
sheetData.Append(row);
}
if (row.Elements<Cell>().Any(c => c.CellReference.Value == cellReference))
return row.Elements<Cell>().First(c => c.CellReference.Value == cellReference);
var refCell = row.Elements<Cell>().FirstOrDefault(cell => String.Compare(cell.CellReference.Value, cellReference, StringComparison.OrdinalIgnoreCase) > 0);
var newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
private void ThreadMethod(WorksheetPart param)
{
try
{
var ws = new OpenXmlWorksheet(param, SharedStrings);
OpenXmlWorksheets.Add(ws);
}
catch (XmlException)
{
return;
}
catch (InvalidDataException)
{
return;
}
catch (InvalidOperationException)
{
return;
}
}
public bool RemoveSheet(string sheetName)
{
bool pkgSheetRemoved = false;
Spreadsheet.Sheet sheet = null;
IEnumerable <DocumentFormat.OpenXml.Spreadsheet.Sheet> qSheets = null;
DocumentFormat.OpenXml.Spreadsheet.Sheet oXmlSheet = null;
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = null;
sheet = this.sheetCollection.Where <Sheet>(sht => sht.Name.HasValue && string.Equals(sht.Name.Value, sheetName)).First();
qSheets = this.workbookPart.Workbook.Sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Where(sht => sht.Name.HasValue && string.Equals(sht.Name.Value, sheetName));
if (qSheets.Count() >= 1)
{
oXmlSheet = qSheets.ElementAt(0);
}
wsPart = this.workbookPart.GetPartById(oXmlSheet.Id) as WorksheetPart;
oXmlSheet.Remove();
this.workbookPart.DeletePart(wsPart);
pkgSheetRemoved = true;
return(this.sheetCollection.Remove(sheet) & pkgSheetRemoved);
}
/// <summary>
/// Create an instance of OpenXmlPart according to the given relationship type.
/// </summary>
/// <param name="openXmlPackage">The container OpenXmlPackage.</param>
/// <param name="relationshipType">The relationship type of the target part.</param>
/// <param name="openXmlPart">The created instance of OpenXmlPart.</param>
/// <remarks>This partial method will be generated by code generaotr.</remarks>
static partial void CreatePartCore(OpenXmlPackage openXmlPackage, string relationshipType, ref OpenXmlPart openXmlPart)
{
if (openXmlPackage == null)
{
throw new ArgumentNullException("openXmlPackage");
}
if (relationshipType == null)
{
throw new ArgumentNullException("relationshipType");
}
if (openXmlPackage is WordprocessingDocument)
{
switch (relationshipType)
{
case MainDocumentPart.RelationshipTypeConstant:
openXmlPart = new MainDocumentPart();
return;
case CustomXmlPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlPart();
return;
case CustomXmlPropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlPropertiesPart();
return;
case GlossaryDocumentPart.RelationshipTypeConstant:
openXmlPart = new GlossaryDocumentPart();
return;
case WordprocessingCommentsPart.RelationshipTypeConstant:
openXmlPart = new WordprocessingCommentsPart();
return;
case AlternativeFormatImportPart.RelationshipTypeConstant:
openXmlPart = new AlternativeFormatImportPart();
return;
case ChartPart.RelationshipTypeConstant:
openXmlPart = new ChartPart();
return;
case ChartDrawingPart.RelationshipTypeConstant:
openXmlPart = new ChartDrawingPart();
return;
case ImagePart.RelationshipTypeConstant:
openXmlPart = new ImagePart();
return;
case EmbeddedPackagePart.RelationshipTypeConstant:
openXmlPart = new EmbeddedPackagePart();
return;
case ThemeOverridePart.RelationshipTypeConstant:
openXmlPart = new ThemeOverridePart();
return;
case ChartStylePart.RelationshipTypeConstant:
openXmlPart = new ChartStylePart();
return;
case ChartColorStylePart.RelationshipTypeConstant:
openXmlPart = new ChartColorStylePart();
return;
case DiagramColorsPart.RelationshipTypeConstant:
openXmlPart = new DiagramColorsPart();
return;
case DiagramDataPart.RelationshipTypeConstant:
openXmlPart = new DiagramDataPart();
return;
case SlidePart.RelationshipTypeConstant:
openXmlPart = new SlidePart();
return;
case DiagramPersistLayoutPart.RelationshipTypeConstant:
openXmlPart = new DiagramPersistLayoutPart();
return;
case DiagramLayoutDefinitionPart.RelationshipTypeConstant:
openXmlPart = new DiagramLayoutDefinitionPart();
return;
case DiagramStylePart.RelationshipTypeConstant:
openXmlPart = new DiagramStylePart();
return;
case EmbeddedObjectPart.RelationshipTypeConstant:
openXmlPart = new EmbeddedObjectPart();
return;
case VmlDrawingPart.RelationshipTypeConstant:
openXmlPart = new VmlDrawingPart();
return;
case LegacyDiagramTextPart.RelationshipTypeConstant:
openXmlPart = new LegacyDiagramTextPart();
return;
case EmbeddedControlPersistenceBinaryDataPart.RelationshipTypeConstant:
openXmlPart = new EmbeddedControlPersistenceBinaryDataPart();
return;
case NotesSlidePart.RelationshipTypeConstant:
openXmlPart = new NotesSlidePart();
return;
case NotesMasterPart.RelationshipTypeConstant:
openXmlPart = new NotesMasterPart();
return;
case ThemePart.RelationshipTypeConstant:
openXmlPart = new ThemePart();
return;
case UserDefinedTagsPart.RelationshipTypeConstant:
openXmlPart = new UserDefinedTagsPart();
return;
case SlideLayoutPart.RelationshipTypeConstant:
openXmlPart = new SlideLayoutPart();
return;
case SlideMasterPart.RelationshipTypeConstant:
openXmlPart = new SlideMasterPart();
return;
case EmbeddedControlPersistencePart.RelationshipTypeConstant:
openXmlPart = new EmbeddedControlPersistencePart();
return;
case SlideSyncDataPart.RelationshipTypeConstant:
openXmlPart = new SlideSyncDataPart();
return;
case WorksheetPart.RelationshipTypeConstant:
openXmlPart = new WorksheetPart();
return;
case DrawingsPart.RelationshipTypeConstant:
openXmlPart = new DrawingsPart();
return;
case WebExtensionPart.RelationshipTypeConstant:
openXmlPart = new WebExtensionPart();
return;
case PivotTablePart.RelationshipTypeConstant:
openXmlPart = new PivotTablePart();
return;
case PivotTableCacheDefinitionPart.RelationshipTypeConstant:
openXmlPart = new PivotTableCacheDefinitionPart();
return;
case PivotTableCacheRecordsPart.RelationshipTypeConstant:
openXmlPart = new PivotTableCacheRecordsPart();
return;
case SingleCellTablePart.RelationshipTypeConstant:
openXmlPart = new SingleCellTablePart();
return;
case TableDefinitionPart.RelationshipTypeConstant:
openXmlPart = new TableDefinitionPart();
return;
case QueryTablePart.RelationshipTypeConstant:
openXmlPart = new QueryTablePart();
return;
case ControlPropertiesPart.RelationshipTypeConstant:
openXmlPart = new ControlPropertiesPart();
return;
case CustomPropertyPart.RelationshipTypeConstant:
openXmlPart = new CustomPropertyPart();
return;
case WorksheetSortMapPart.RelationshipTypeConstant:
openXmlPart = new WorksheetSortMapPart();
return;
case SlicersPart.RelationshipTypeConstant:
openXmlPart = new SlicersPart();
return;
case TimeLinePart.RelationshipTypeConstant:
openXmlPart = new TimeLinePart();
return;
case DocumentSettingsPart.RelationshipTypeConstant:
openXmlPart = new DocumentSettingsPart();
return;
case MailMergeRecipientDataPart.RelationshipTypeConstant:
openXmlPart = new MailMergeRecipientDataPart();
return;
case EndnotesPart.RelationshipTypeConstant:
openXmlPart = new EndnotesPart();
return;
case FontTablePart.RelationshipTypeConstant:
openXmlPart = new FontTablePart();
return;
case FontPart.RelationshipTypeConstant:
openXmlPart = new FontPart();
return;
case FootnotesPart.RelationshipTypeConstant:
openXmlPart = new FootnotesPart();
return;
case NumberingDefinitionsPart.RelationshipTypeConstant:
openXmlPart = new NumberingDefinitionsPart();
return;
case StyleDefinitionsPart.RelationshipTypeConstant:
openXmlPart = new StyleDefinitionsPart();
return;
case StylesWithEffectsPart.RelationshipTypeConstant:
openXmlPart = new StylesWithEffectsPart();
return;
case WebSettingsPart.RelationshipTypeConstant:
openXmlPart = new WebSettingsPart();
return;
case FooterPart.RelationshipTypeConstant:
openXmlPart = new FooterPart();
return;
case HeaderPart.RelationshipTypeConstant:
openXmlPart = new HeaderPart();
return;
case WordprocessingPrinterSettingsPart.RelationshipTypeConstant:
openXmlPart = new WordprocessingPrinterSettingsPart();
return;
case CustomizationPart.RelationshipTypeConstant:
openXmlPart = new CustomizationPart();
return;
case WordAttachedToolbarsPart.RelationshipTypeConstant:
openXmlPart = new WordAttachedToolbarsPart();
return;
case VbaProjectPart.RelationshipTypeConstant:
openXmlPart = new VbaProjectPart();
return;
case VbaDataPart.RelationshipTypeConstant:
openXmlPart = new VbaDataPart();
return;
case WordprocessingCommentsExPart.RelationshipTypeConstant:
openXmlPart = new WordprocessingCommentsExPart();
return;
case WordprocessingPeoplePart.RelationshipTypeConstant:
openXmlPart = new WordprocessingPeoplePart();
return;
case ThumbnailPart.RelationshipTypeConstant:
openXmlPart = new ThumbnailPart();
return;
case CoreFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new CoreFilePropertiesPart();
return;
case ExtendedFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new ExtendedFilePropertiesPart();
return;
case CustomFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomFilePropertiesPart();
return;
case DigitalSignatureOriginPart.RelationshipTypeConstant:
openXmlPart = new DigitalSignatureOriginPart();
return;
case XmlSignaturePart.RelationshipTypeConstant:
openXmlPart = new XmlSignaturePart();
return;
case QuickAccessToolbarCustomizationsPart.RelationshipTypeConstant:
openXmlPart = new QuickAccessToolbarCustomizationsPart();
return;
case RibbonExtensibilityPart.RelationshipTypeConstant:
openXmlPart = new RibbonExtensibilityPart();
return;
case RibbonAndBackstageCustomizationsPart.RelationshipTypeConstant:
openXmlPart = new RibbonAndBackstageCustomizationsPart();
return;
case WebExTaskpanesPart.RelationshipTypeConstant:
openXmlPart = new WebExTaskpanesPart();
return;
}
}
else if (openXmlPackage is SpreadsheetDocument)
{
switch (relationshipType)
{
case WorkbookPart.RelationshipTypeConstant:
openXmlPart = new WorkbookPart();
return;
case CustomXmlPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlPart();
return;
case CustomXmlPropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlPropertiesPart();
return;
case CalculationChainPart.RelationshipTypeConstant:
openXmlPart = new CalculationChainPart();
return;
case CellMetadataPart.RelationshipTypeConstant:
openXmlPart = new CellMetadataPart();
return;
case ConnectionsPart.RelationshipTypeConstant:
openXmlPart = new ConnectionsPart();
return;
case CustomXmlMappingsPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlMappingsPart();
return;
case SharedStringTablePart.RelationshipTypeConstant:
openXmlPart = new SharedStringTablePart();
return;
case WorkbookRevisionHeaderPart.RelationshipTypeConstant:
openXmlPart = new WorkbookRevisionHeaderPart();
return;
case WorkbookRevisionLogPart.RelationshipTypeConstant:
openXmlPart = new WorkbookRevisionLogPart();
return;
case WorkbookUserDataPart.RelationshipTypeConstant:
openXmlPart = new WorkbookUserDataPart();
return;
case WorkbookStylesPart.RelationshipTypeConstant:
openXmlPart = new WorkbookStylesPart();
return;
case ThemePart.RelationshipTypeConstant:
openXmlPart = new ThemePart();
return;
case ImagePart.RelationshipTypeConstant:
openXmlPart = new ImagePart();
return;
case ThumbnailPart.RelationshipTypeConstant:
openXmlPart = new ThumbnailPart();
return;
case VolatileDependenciesPart.RelationshipTypeConstant:
openXmlPart = new VolatileDependenciesPart();
return;
case ChartsheetPart.RelationshipTypeConstant:
openXmlPart = new ChartsheetPart();
return;
case SpreadsheetPrinterSettingsPart.RelationshipTypeConstant:
openXmlPart = new SpreadsheetPrinterSettingsPart();
return;
case DrawingsPart.RelationshipTypeConstant:
openXmlPart = new DrawingsPart();
return;
case ChartPart.RelationshipTypeConstant:
openXmlPart = new ChartPart();
return;
case ChartDrawingPart.RelationshipTypeConstant:
openXmlPart = new ChartDrawingPart();
return;
case EmbeddedPackagePart.RelationshipTypeConstant:
openXmlPart = new EmbeddedPackagePart();
return;
case ThemeOverridePart.RelationshipTypeConstant:
openXmlPart = new ThemeOverridePart();
return;
case ChartStylePart.RelationshipTypeConstant:
openXmlPart = new ChartStylePart();
return;
case ChartColorStylePart.RelationshipTypeConstant:
openXmlPart = new ChartColorStylePart();
return;
case DiagramColorsPart.RelationshipTypeConstant:
openXmlPart = new DiagramColorsPart();
return;
case DiagramDataPart.RelationshipTypeConstant:
openXmlPart = new DiagramDataPart();
return;
case SlidePart.RelationshipTypeConstant:
openXmlPart = new SlidePart();
return;
case DiagramPersistLayoutPart.RelationshipTypeConstant:
openXmlPart = new DiagramPersistLayoutPart();
return;
case DiagramLayoutDefinitionPart.RelationshipTypeConstant:
openXmlPart = new DiagramLayoutDefinitionPart();
return;
case DiagramStylePart.RelationshipTypeConstant:
openXmlPart = new DiagramStylePart();
return;
case EmbeddedObjectPart.RelationshipTypeConstant:
openXmlPart = new EmbeddedObjectPart();
return;
case VmlDrawingPart.RelationshipTypeConstant:
openXmlPart = new VmlDrawingPart();
return;
case LegacyDiagramTextPart.RelationshipTypeConstant:
openXmlPart = new LegacyDiagramTextPart();
return;
case EmbeddedControlPersistenceBinaryDataPart.RelationshipTypeConstant:
openXmlPart = new EmbeddedControlPersistenceBinaryDataPart();
return;
case NotesSlidePart.RelationshipTypeConstant:
openXmlPart = new NotesSlidePart();
return;
case NotesMasterPart.RelationshipTypeConstant:
openXmlPart = new NotesMasterPart();
return;
case UserDefinedTagsPart.RelationshipTypeConstant:
openXmlPart = new UserDefinedTagsPart();
return;
case SlideLayoutPart.RelationshipTypeConstant:
openXmlPart = new SlideLayoutPart();
return;
case SlideMasterPart.RelationshipTypeConstant:
openXmlPart = new SlideMasterPart();
return;
case EmbeddedControlPersistencePart.RelationshipTypeConstant:
openXmlPart = new EmbeddedControlPersistencePart();
return;
case SlideSyncDataPart.RelationshipTypeConstant:
openXmlPart = new SlideSyncDataPart();
return;
case WorksheetPart.RelationshipTypeConstant:
openXmlPart = new WorksheetPart();
return;
case WorksheetCommentsPart.RelationshipTypeConstant:
openXmlPart = new WorksheetCommentsPart();
return;
case PivotTablePart.RelationshipTypeConstant:
openXmlPart = new PivotTablePart();
return;
case PivotTableCacheDefinitionPart.RelationshipTypeConstant:
openXmlPart = new PivotTableCacheDefinitionPart();
return;
case PivotTableCacheRecordsPart.RelationshipTypeConstant:
openXmlPart = new PivotTableCacheRecordsPart();
return;
case SingleCellTablePart.RelationshipTypeConstant:
openXmlPart = new SingleCellTablePart();
return;
case TableDefinitionPart.RelationshipTypeConstant:
openXmlPart = new TableDefinitionPart();
return;
case QueryTablePart.RelationshipTypeConstant:
openXmlPart = new QueryTablePart();
return;
case ControlPropertiesPart.RelationshipTypeConstant:
openXmlPart = new ControlPropertiesPart();
return;
case CustomPropertyPart.RelationshipTypeConstant:
openXmlPart = new CustomPropertyPart();
return;
case WorksheetSortMapPart.RelationshipTypeConstant:
openXmlPart = new WorksheetSortMapPart();
return;
case SlicersPart.RelationshipTypeConstant:
openXmlPart = new SlicersPart();
return;
case TimeLinePart.RelationshipTypeConstant:
openXmlPart = new TimeLinePart();
return;
case WebExtensionPart.RelationshipTypeConstant:
openXmlPart = new WebExtensionPart();
return;
case DialogsheetPart.RelationshipTypeConstant:
openXmlPart = new DialogsheetPart();
return;
case ExternalWorkbookPart.RelationshipTypeConstant:
openXmlPart = new ExternalWorkbookPart();
return;
case ExcelAttachedToolbarsPart.RelationshipTypeConstant:
openXmlPart = new ExcelAttachedToolbarsPart();
return;
case VbaProjectPart.RelationshipTypeConstant:
openXmlPart = new VbaProjectPart();
return;
case VbaDataPart.RelationshipTypeConstant:
openXmlPart = new VbaDataPart();
return;
case MacroSheetPart.RelationshipTypeConstant:
openXmlPart = new MacroSheetPart();
return;
case InternationalMacroSheetPart.RelationshipTypeConstant:
openXmlPart = new InternationalMacroSheetPart();
return;
case CustomDataPropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomDataPropertiesPart();
return;
case CustomDataPart.RelationshipTypeConstant:
openXmlPart = new CustomDataPart();
return;
case SlicerCachePart.RelationshipTypeConstant:
openXmlPart = new SlicerCachePart();
return;
case TimeLineCachePart.RelationshipTypeConstant:
openXmlPart = new TimeLineCachePart();
return;
case CoreFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new CoreFilePropertiesPart();
return;
case ExtendedFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new ExtendedFilePropertiesPart();
return;
case CustomFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomFilePropertiesPart();
return;
case DigitalSignatureOriginPart.RelationshipTypeConstant:
openXmlPart = new DigitalSignatureOriginPart();
return;
case XmlSignaturePart.RelationshipTypeConstant:
openXmlPart = new XmlSignaturePart();
return;
case QuickAccessToolbarCustomizationsPart.RelationshipTypeConstant:
openXmlPart = new QuickAccessToolbarCustomizationsPart();
return;
case RibbonExtensibilityPart.RelationshipTypeConstant:
openXmlPart = new RibbonExtensibilityPart();
return;
case RibbonAndBackstageCustomizationsPart.RelationshipTypeConstant:
openXmlPart = new RibbonAndBackstageCustomizationsPart();
return;
case WebExTaskpanesPart.RelationshipTypeConstant:
openXmlPart = new WebExTaskpanesPart();
return;
}
}
else if (openXmlPackage is PresentationDocument)
{
switch (relationshipType)
{
case PresentationPart.RelationshipTypeConstant:
openXmlPart = new PresentationPart();
return;
case CustomXmlPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlPart();
return;
case CustomXmlPropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomXmlPropertiesPart();
return;
case FontPart.RelationshipTypeConstant:
openXmlPart = new FontPart();
return;
case PresentationPropertiesPart.RelationshipTypeConstant:
openXmlPart = new PresentationPropertiesPart();
return;
case TableStylesPart.RelationshipTypeConstant:
openXmlPart = new TableStylesPart();
return;
case ThemePart.RelationshipTypeConstant:
openXmlPart = new ThemePart();
return;
case ImagePart.RelationshipTypeConstant:
openXmlPart = new ImagePart();
return;
case ViewPropertiesPart.RelationshipTypeConstant:
openXmlPart = new ViewPropertiesPart();
return;
case SlidePart.RelationshipTypeConstant:
openXmlPart = new SlidePart();
return;
case ChartPart.RelationshipTypeConstant:
openXmlPart = new ChartPart();
return;
case ChartDrawingPart.RelationshipTypeConstant:
openXmlPart = new ChartDrawingPart();
return;
case EmbeddedPackagePart.RelationshipTypeConstant:
openXmlPart = new EmbeddedPackagePart();
return;
case ThemeOverridePart.RelationshipTypeConstant:
openXmlPart = new ThemeOverridePart();
return;
case ChartStylePart.RelationshipTypeConstant:
openXmlPart = new ChartStylePart();
return;
case ChartColorStylePart.RelationshipTypeConstant:
openXmlPart = new ChartColorStylePart();
return;
case DiagramColorsPart.RelationshipTypeConstant:
openXmlPart = new DiagramColorsPart();
return;
case DiagramDataPart.RelationshipTypeConstant:
openXmlPart = new DiagramDataPart();
return;
case WorksheetPart.RelationshipTypeConstant:
openXmlPart = new WorksheetPart();
return;
case DrawingsPart.RelationshipTypeConstant:
openXmlPart = new DrawingsPart();
return;
case DiagramPersistLayoutPart.RelationshipTypeConstant:
openXmlPart = new DiagramPersistLayoutPart();
return;
case DiagramLayoutDefinitionPart.RelationshipTypeConstant:
openXmlPart = new DiagramLayoutDefinitionPart();
return;
case DiagramStylePart.RelationshipTypeConstant:
openXmlPart = new DiagramStylePart();
return;
case WebExtensionPart.RelationshipTypeConstant:
openXmlPart = new WebExtensionPart();
return;
case VmlDrawingPart.RelationshipTypeConstant:
openXmlPart = new VmlDrawingPart();
return;
case LegacyDiagramTextPart.RelationshipTypeConstant:
openXmlPart = new LegacyDiagramTextPart();
return;
case PivotTablePart.RelationshipTypeConstant:
openXmlPart = new PivotTablePart();
return;
case PivotTableCacheDefinitionPart.RelationshipTypeConstant:
openXmlPart = new PivotTableCacheDefinitionPart();
return;
case PivotTableCacheRecordsPart.RelationshipTypeConstant:
openXmlPart = new PivotTableCacheRecordsPart();
return;
case SingleCellTablePart.RelationshipTypeConstant:
openXmlPart = new SingleCellTablePart();
return;
case TableDefinitionPart.RelationshipTypeConstant:
openXmlPart = new TableDefinitionPart();
return;
case QueryTablePart.RelationshipTypeConstant:
openXmlPart = new QueryTablePart();
return;
case EmbeddedControlPersistencePart.RelationshipTypeConstant:
openXmlPart = new EmbeddedControlPersistencePart();
return;
case EmbeddedControlPersistenceBinaryDataPart.RelationshipTypeConstant:
openXmlPart = new EmbeddedControlPersistenceBinaryDataPart();
return;
case ControlPropertiesPart.RelationshipTypeConstant:
openXmlPart = new ControlPropertiesPart();
return;
case EmbeddedObjectPart.RelationshipTypeConstant:
openXmlPart = new EmbeddedObjectPart();
return;
case CustomPropertyPart.RelationshipTypeConstant:
openXmlPart = new CustomPropertyPart();
return;
case WorksheetSortMapPart.RelationshipTypeConstant:
openXmlPart = new WorksheetSortMapPart();
return;
case SlicersPart.RelationshipTypeConstant:
openXmlPart = new SlicersPart();
return;
case TimeLinePart.RelationshipTypeConstant:
openXmlPart = new TimeLinePart();
return;
case SlideCommentsPart.RelationshipTypeConstant:
openXmlPart = new SlideCommentsPart();
return;
case NotesSlidePart.RelationshipTypeConstant:
openXmlPart = new NotesSlidePart();
return;
case NotesMasterPart.RelationshipTypeConstant:
openXmlPart = new NotesMasterPart();
return;
case UserDefinedTagsPart.RelationshipTypeConstant:
openXmlPart = new UserDefinedTagsPart();
return;
case SlideLayoutPart.RelationshipTypeConstant:
openXmlPart = new SlideLayoutPart();
return;
case SlideMasterPart.RelationshipTypeConstant:
openXmlPart = new SlideMasterPart();
return;
case SlideSyncDataPart.RelationshipTypeConstant:
openXmlPart = new SlideSyncDataPart();
return;
case CommentAuthorsPart.RelationshipTypeConstant:
openXmlPart = new CommentAuthorsPart();
return;
case HandoutMasterPart.RelationshipTypeConstant:
openXmlPart = new HandoutMasterPart();
return;
case LegacyDiagramTextInfoPart.RelationshipTypeConstant:
openXmlPart = new LegacyDiagramTextInfoPart();
return;
case VbaProjectPart.RelationshipTypeConstant:
openXmlPart = new VbaProjectPart();
return;
case VbaDataPart.RelationshipTypeConstant:
openXmlPart = new VbaDataPart();
return;
case CoreFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new CoreFilePropertiesPart();
return;
case ExtendedFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new ExtendedFilePropertiesPart();
return;
case CustomFilePropertiesPart.RelationshipTypeConstant:
openXmlPart = new CustomFilePropertiesPart();
return;
case ThumbnailPart.RelationshipTypeConstant:
openXmlPart = new ThumbnailPart();
return;
case DigitalSignatureOriginPart.RelationshipTypeConstant:
openXmlPart = new DigitalSignatureOriginPart();
return;
case XmlSignaturePart.RelationshipTypeConstant:
openXmlPart = new XmlSignaturePart();
return;
case QuickAccessToolbarCustomizationsPart.RelationshipTypeConstant:
openXmlPart = new QuickAccessToolbarCustomizationsPart();
return;
case RibbonExtensibilityPart.RelationshipTypeConstant:
openXmlPart = new RibbonExtensibilityPart();
return;
case RibbonAndBackstageCustomizationsPart.RelationshipTypeConstant:
openXmlPart = new RibbonAndBackstageCustomizationsPart();
return;
case WebExTaskpanesPart.RelationshipTypeConstant:
openXmlPart = new WebExTaskpanesPart();
return;
}
}
else
{
System.Diagnostics.Debug.Assert(false);
}
return;
}
/// <summary>
/// 向工作表插入一個單元格
/// </summary>
/// <param name="columnName">列名稱</param>
/// <param name="rowIndex">行索引</param>
/// <param name="worksheetPart"></param>
/// <returns></returns>
private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string cellReference = columnName + rowIndex;//列的引用字符串，類似:"A3"或"B5"
//如果指定的行存在，則直接返回該行，否則插入新行
Row row;
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);
}
//如果該行沒有指定ColumnName的列，則插入新列，否則直接返回該列
if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
//列必須按(字母)順序插入，因此要先根據"列引用字符串"查找插入的位置
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="worksheetPart"></param>
/// <param name="startRow"></param>
/// <param name="endRow"></param>
/// <returns></returns>
private List<VariableIdentifier> getVariableIdentifiers(WorksheetPart worksheetPart, int startRow, int endRow)
{
//NEW OPENXMLREADER
if (this.VariableIdentifiers == null || this.VariableIdentifiers.Count == 0)
{
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
int rowNum = 0;
// read variable rows to get name and id from area variable
while (reader.Read())
{
if (reader.ElementType == typeof(Row))
{
do
{
if (reader.HasAttributes)
rowNum = Convert.ToInt32(reader.Attributes.First(a => a.LocalName == "r").Value);
if (rowNum >= startRow && rowNum <= endRow)
{
Row row = (Row)reader.LoadCurrentElement();
if (row.Hidden == null) VariableIdentifierRows.Add(rowToList(row));
else if (row.Hidden != true) VariableIdentifierRows.Add(rowToList(row));
}
} while (reader.ReadNextSibling() && rowNum < endRow); // Skip to the next row
break;
}
}
// convert variable rows to VariableIdentifiers
if (VariableIdentifierRows != null)
{
foreach (List<string> l in VariableIdentifierRows)
{
//create headerVariables
if (VariableIdentifiers.Count == 0)
{
foreach (string s in l)
{
VariableIdentifier hv = new VariableIdentifier();
hv.name = s;
VariableIdentifiers.Add(hv);
}
}
else
{
foreach (string s in l)
{
int id = Convert.ToInt32(s);
int index = l.IndexOf(s);
VariableIdentifiers.ElementAt(index).id = id;
}
}
}
}
}
if (this.VariableIdentifiers != null) return this.VariableIdentifiers;
else return null;
}
/// <summary>
/// Add Rows to a WorksheetPart
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="worksheetPart"></param>
/// <param name="startRow"></param>
/// <param name="endRow"></param>
/// <param name="dataTuples"></param>
protected void AddRows(WorksheetPart worksheetPart, int startRow, int endRow, List<AbstractTuple> dataTuples)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
int rowIndex = endRow;
//add row
foreach (DataTuple dataTuple in dataTuples)
{
// convert datatuple to row and add it to sheetdata
Row row = DatatupleToRow(dataTuple, rowIndex);
bool empty = true;
foreach (Cell c in row.Elements<Cell>().ToList())
{
if (!String.IsNullOrEmpty(c.InnerText))
{
empty = false;
break;
}
}
if (!empty)
{
sheetData.Append(row);
if (!dataTuple.Equals(dataTuples.Last()))
rowIndex++;
}
}
numOfDataRows = rowIndex;
}
/// <summary>
/// Add Rows to a WorksheetPart
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="worksheetPart"></param>
/// <param name="startRow"></param>
/// <param name="endRow"></param>
/// <param name="dataTuplesIds"></param>
protected void AddRows(WorksheetPart worksheetPart, int startRow, int endRow, List<long> dataTuplesIds, DatasetManager datasetManager)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
int rowIndex = endRow;
DataTupleIterator tupleIterator = new DataTupleIterator(dataTuplesIds, datasetManager);
foreach (var tuple in tupleIterator)
{
// convert datatuple to row and add it to sheetdata
Row row = DatatupleToRow(tuple, rowIndex);
bool empty = true;
foreach (Cell c in row.Elements<Cell>().ToList())
{
if (!String.IsNullOrEmpty(c.InnerText))
{
empty = false;
break;
}
}
if (!empty)
{
sheetData.Append(row);
if (!tuple.Id.Equals(dataTuplesIds.Last()))
rowIndex++;
}
}
numOfDataRows = rowIndex;
}
private static void GerarExcelOpXml(System.Data.DataTable dt, string pathExcelFile, string sheetName,
string ColunaDefault, int rowDefault, int larguraColuna)
{
var styleIndexDefault = 0;
var styleIndexDefaultLine = 2;
DocumentFormat.OpenXml.UInt32Value styleIndex;
using (OpenXmlPackaging.SpreadsheetDocument myDoc =
OpenXmlPackaging.SpreadsheetDocument.Open(pathExcelFile, true))
{
OpenXmlPackaging.WorksheetPart worksheetPart = GetWorksheetPartByName(myDoc, sheetName);
OpenXmlSpread.Cell cellDefault =
GetCell(worksheetPart.Worksheet, ColunaDefault, Convert.ToUInt32(rowDefault));
styleIndex = cellDefault.StyleIndex;
styleIndexDefault = Convert.ToInt32(styleIndex.Value.ToString());
OpenXmlSpread.Cell cellDefaultline =
GetCell(worksheetPart.Worksheet, ColunaDefault, Convert.ToUInt32(rowDefault + 1));
styleIndex = cellDefaultline.StyleIndex;
styleIndexDefaultLine = Convert.ToInt32(styleIndex.Value.ToString());
}
using (OpenXmlPackaging.SpreadsheetDocument myDoc =
OpenXmlPackaging.SpreadsheetDocument.Open(pathExcelFile, true))
{
OpenXmlPackaging.WorksheetPart worksheetPart = GetWorksheetPartByName(myDoc, sheetName);
OpenXmlSpread.Stylesheet stylesheet = myDoc.WorkbookPart.WorkbookStylesPart.Stylesheet;
OpenXmlSpread.CellFormat deafultFormat = new OpenXmlSpread.CellFormat()
{
Alignment = new OpenXmlSpread.Alignment()
{
Horizontal = OpenXmlSpread.HorizontalAlignmentValues.Left,
Vertical = OpenXmlSpread.VerticalAlignmentValues.Center
},
ApplyAlignment = true
};
stylesheet.CellFormats.AppendChild(deafultFormat);
DocumentFormat.OpenXml.OpenXmlWriter
writer = DocumentFormat.OpenXml.OpenXmlWriter.Create(worksheetPart);
var indexColumn = ColumnIndex(ColunaDefault);
var startColunaIndex = indexColumn + 1;
writer.WriteStartElement(new OpenXmlSpread.Worksheet());
writer.WriteStartElement(new OpenXmlSpread.Columns());
AjustaLarguraColunas(writer, startColunaIndex, larguraColuna, dt.Columns.Count);
writer.WriteStartElement(new OpenXmlSpread.SheetData());
writer.WriteStartElement(new OpenXmlSpread.Row {
RowIndex = (UInt32)rowDefault
});
CriarColunas(writer, dt, indexColumn, rowDefault, styleIndexDefault);
CriarLinha(writer, dt, indexColumn, rowDefault, styleIndexDefaultLine);
writer.Dispose();
}
}
/// <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 void Add(OpenXmlSDK.WorksheetPart worksheet, string tableStyle, bool useHeaders, short fromColumn, short toColumn, int fromRow, int toRow)
{
//Getting the id for this table
int tableId = GetNextTableId();
//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
OpenXmlSDK.TableDefinitionPart table = worksheet.AddNewPart <OpenXmlSDK.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(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 = parentDocument.GetXDocument(worksheet);
//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);
}
public void LoadData(string sheetName, uint sheetId, DataTable dt)
{
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = null;
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = null;
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = null;
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = null;
uint rowIndex = 1;
uint colIndex = 1;
string collName = string.Empty;
Row row = null;
Cell cell = null;
sheetData = new SheetData();
// Header Row
row = new Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
// Header Row cellls
foreach (DataColumn dc in dt.Columns)
{
collName = ExcelHelper.ConvertColumnNumberToName((int)colIndex);
cell = new Cell()
{
CellReference = collName + rowIndex
};
row.AppendChild(cell);
Text txt = new Text();
txt.Text = dc.ColumnName;
InlineString inStr = new InlineString();
inStr.AppendChild(txt);
cell.DataType = CellValues.InlineString;
cell.AppendChild(inStr);
colIndex++;
}
rowIndex++;
foreach (DataRow dr in dt.Rows)
{
row = new Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
colIndex = 1;
foreach (DataColumn dc in dt.Columns)
{
collName = ExcelHelper.ConvertColumnNumberToName((int)colIndex);
cell = new Cell()
{
CellReference = collName + rowIndex
};
row.AppendChild(cell);
Text txt = new Text();
txt.Text = dr[dc.ColumnName].ToString();
InlineString inStr = new InlineString();
inStr.AppendChild(txt);
cell.DataType = CellValues.InlineString;
cell.AppendChild(inStr);
colIndex++;
}
rowIndex++;
}
wsPart = wbPart.AddNewPart <WorksheetPart>();
wsPart.Worksheet = new Worksheet(sheetData);
sheet = new Sheet();
sheet.Id = wbPart.GetIdOfPart(wsPart);
sheet.SheetId = sheetId;
sheet.Name = sheetName;
sheets = wbPart.Workbook.AppendChild(new Sheets());
sheets.Append(sheet);
}
