예제 #1
0
        /// <summary>
        ///
        /// </summary>
        /// <remarks></remarks>
        /// <seealso cref=""/>
        /// <param name="workbookPart"></param>
        /// <param name="definedName"></param>
        /// <returns></returns>
        private static WorksheetPart getWorkSheetPart(WorkbookPart workbookPart, DefinedNameVal definedName)
        {
            //get worksheet based on defined name
            string relId = workbookPart.Workbook.Descendants <Sheet>()
                           .Where(s => definedName.SheetName.Equals(s.Name))
                           .First()
                           .Id;

            return((WorksheetPart)workbookPart.GetPartById(relId));
        }
예제 #2
0
        /// <summary>
        /// prepare existing excel file to append data
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="dataStructureId"></param>
        protected override void Init(string filePath, long dataStructureId)
        {
            // loading datastructure
            dataStructure = GetDataStructure(dataStructureId);

            // open excel file
            spreadsheetDocument = SpreadsheetDocument.Open(filePath, true);

            // get workbookpart
            workbookPart = spreadsheetDocument.WorkbookPart;

            // get all the defined area
            List <DefinedNameVal> namesTable = buildDefinedNamesTable(workbookPart);

            // select data area
            this.areaOfData = namesTable.Where(p => p.Key.Equals("Data")).FirstOrDefault();

            // set starting row number
            rowIndex = areaOfData.EndRow;

            // Select variable area
            this.areaOfVariables = namesTable.Where(p => p.Key.Equals("VariableIdentifiers")).FirstOrDefault();

            // Get integers for reading data
            startColumn = getColumnNumber(this.areaOfData.StartColumn);
            endColumn   = getColumnNumber(this.areaOfData.EndColumn);

            numOfColumns = (endColumn - startColumn) + 1;
            offset       = getColumnNumber(getColumnName(this.areaOfData.StartColumn)) - 1;

            // generate Style for cell types
            generateStyle(spreadsheetDocument);

            // get styleSheet
            stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;

            // Get shared strings
            sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ToArray();

            // select worksheetpart by selected defined name area like data in sheet
            // sheet where data area is inside
            worksheetPart = getWorkSheetPart(workbookPart, this.areaOfData);

            // Get VariableIndentifiers
            VariableIdentifiers = getVariableIdentifiers(worksheetPart, this.areaOfVariables.StartRow, this.areaOfVariables.EndRow);

            // get sheetData object for adding data to
            sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
        }
예제 #3
0
파일: ExcelWriter.cs 프로젝트: BEXIS2/Core
        /// <summary>
        /// Add Datatuples to a Excel Template file
        /// </summary>
        /// <remarks></remarks>
        /// <seealso cref=""/>
        /// <param name="dataTuples"> Datatuples to add</param>
        /// <param name="filePath">Path of the excel template file</param>
        /// <param name="dataStructureId">Id of datastructure</param>
        /// <returns>List of Errors or null</returns>
        public List<Error> AddDataTuplesToTemplate(DatasetManager datasetManager, List<long> dataTuplesIds, string filePath, long dataStructureId )
        {
            if (File.Exists(filePath))
            {

                //Stream file = Open(filePath);

                //_dataTuples = dataTuples;
                // loading datastructure
                dataStructure = GetDataStructure(dataStructureId);

                // open excel file
                spreadsheetDocument = SpreadsheetDocument.Open(filePath, true);

                // get workbookpart
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;

                // get all the defined area
                List<DefinedNameVal> namesTable = buildDefinedNamesTable(workbookPart);

                // select data area
                this.areaOfData = namesTable.Where(p => p.Key.Equals("Data")).FirstOrDefault();

                // Select variable area
                this.areaOfVariables = namesTable.Where(p => p.Key.Equals("VariableIdentifiers")).FirstOrDefault();

                // Get intergers for reading data
                startColumn = getColumnNumber(this.areaOfData.StartColumn);
                endColumn = getColumnNumber(this.areaOfData.EndColumn);

                numOfColumns = (endColumn - startColumn) + 1;
                offset = getColumnNumber(getColumnName(this.areaOfData.StartColumn)) - 1;

                // gerneat Style for cell types
                generateStyle(spreadsheetDocument);

                // get styleSheet
                stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;

                // Get shared strings
                sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ToArray();

                // select worksheetpart by selected defined name area like data in sheet
                // sheet where data area is inside
                WorksheetPart worksheetPart = getWorkSheetPart(workbookPart, this.areaOfData);

                // Get VarioableIndentifiers
                this.VariableIdentifiers = getVariableIdentifiers(worksheetPart, this.areaOfVariables.StartRow, this.areaOfVariables.EndRow);

                AddRows(worksheetPart, this.areaOfData.StartRow, this.areaOfData.EndRow, dataTuplesIds, datasetManager);

                // set data area

                foreach (DefinedName name in workbookPart.Workbook.GetFirstChild<DefinedNames>())
                {
                    if (name.Name == "Data")
                    {
                        string[] tempArr = name.InnerText.Split('$');
                        string temp = "";
                        //$A$10:$C$15

                        tempArr[tempArr.Count() - 1] = numOfDataRows.ToString();

                        foreach (string t in tempArr)
                        {
                            if (t == tempArr.First())
                            {
                                temp = temp + t;
                            }
                            else
                            {
                                temp = temp + "$" + t;
                            }
                        }

                        name.Text = temp;
                    }
                }

                spreadsheetDocument.WorkbookPart.Workbook.Save();
                spreadsheetDocument.Close();

            }

            return ErrorMessages;
        }
예제 #4
0
파일: ExcelWriter.cs 프로젝트: BEXIS2/Core
 /// <summary>
 ///
 /// </summary>
 /// <remarks></remarks>
 /// <seealso cref=""/>
 /// <param name="workbookPart"></param>
 /// <param name="definedName"></param>
 /// <returns></returns>
 private static WorksheetPart getWorkSheetPart(WorkbookPart workbookPart, DefinedNameVal definedName)
 {
     //get worksheet based on defined name
     string relId = workbookPart.Workbook.Descendants<Sheet>()
     .Where(s => definedName.SheetName.Equals(s.Name))
     .First()
     .Id;
     return (WorksheetPart)workbookPart.GetPartById(relId);
 }