// 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); }