// 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);
        }
Esempio n. 2
0
        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;
 }
Esempio n. 4
0
 public OpenXLRow(WorkbookPart wbPart, WorksheetPart wsPart, List<string> columns, Row xRow)
 {
     this.WbPart = wbPart;
     this.WsPart = wsPart;
     this.Columns = columns;
     this._cells = GetCells(xRow);
 }
Esempio n. 5
0
        /// <summary>
        /// Creates an empty worksheet part
        /// </summary>
        /// <returns></returns>
        public OpenXmlSDK.WorksheetPart Create()
        {
            XDocument xDocument = CreateEmptyWorksheet();

            OpenXmlSDK.WorksheetPart part = Add(xDocument);
            return(part);
        }
Esempio n. 6
0
        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);
        }
Esempio n. 7
0
        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;
 }
Esempio n. 9
0
        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);
        }
Esempio n. 10
0
        /// <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();
        }
Esempio n. 11
0
        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);
        }
Esempio n. 12
0
        /// <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);
        }
Esempio n. 14
0
        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);
        }
Esempio n. 15
0
        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>());
        }
Esempio n. 20
0
        /// <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);
        }
Esempio n. 21
0
        /// <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);
            }
        }
Esempio n. 23
0
        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);
         }
     }
 }
Esempio n. 25
0
        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);
            }
        }
Esempio n. 26
0
        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;
        }
Esempio n. 29
0
        /// <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);
        }
Esempio n. 30
0
        /// <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);
        }
Esempio n. 31
0
        /// <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);
        }
Esempio n. 33
0
        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;
        }
Esempio n. 34
0
        /// <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();
        }
Esempio n. 35
0
        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);
        }
Esempio n. 36
0
        // 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;
            }
        }
Esempio n. 37
0
        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;
        }
Esempio n. 38
0
        /// <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);
        }
Esempio n. 39
0
        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);
        }
Esempio n. 40
0
        /// <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);
        }
Esempio n. 41
0
        /// <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);
            }
        }
Esempio n. 42
0
 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);
        }
Esempio n. 45
0
        /// <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;
        }
Esempio n. 46
0
        /// <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;
            }
        }
Esempio n. 47
0
        /// <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;
        }
Esempio n. 48
0
        /// <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;
        }
Esempio n. 49
0
        /// <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;
        }
Esempio n. 50
0
        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);
        }
Esempio n. 52
0
    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);
    }