예제 #1
0
        private static void AddGradient(SpreadsheetDocument spreadSheet, string dummyColorCode, string firstColorCode, string secondColorCode)
        {
            var wbPart      = spreadSheet.GetPartsOfType <WorkbookPart>().FirstOrDefault();
            var wbStylePart = wbPart?.GetPartsOfType <WorkbookStylesPart>().FirstOrDefault();
            var stylesheet  = wbStylePart?.Stylesheet;

            var oldFill =
                stylesheet?.Fills.FirstOrDefault(f => f.OuterXml.Contains(dummyColorCode));             // find the fill that uses your unique color

            if (oldFill == null)
            {
                return;
            }
            var gradientFill = new GradientFill {
                Degree = 0
            };

            gradientFill.Append(new GradientStop {
                Position = 0D, Color = new Color {
                    Rgb = firstColorCode
                }
            });
            gradientFill.Append(new GradientStop {
                Position = 1D, Color = new Color {
                    Rgb = secondColorCode
                }
            });
            oldFill.ReplaceChild(gradientFill, oldFill.FirstChild);        // inside the fill replace the patternFill with your gradientFill
        }
예제 #2
0
 public IEnumerable <ISheet> ReadSheets()
 {
     return(workbook
            .GetPartsOfType <WorkbookPart>()
            .SelectMany(
                (workbookPart) => workbookPart.Workbook
                .Descendants <Sheet>()
                .Select(
                    (worksheet) =>
     {
         var wsPart = (WorksheetPart)(workbookPart.GetPartById(worksheet.Id));
         var worksheetData = wsPart.Worksheet;
         return new OpenXmlSheet(workbook, workbookPart, worksheet, worksheetData);
     }))
            .Select(openXmlSheet => (ISheet)openXmlSheet));
 }
예제 #3
0
        /// <summary>
        ///
        /// </summary>
        /// <remarks></remarks>
        /// <seealso cref=""/>
        /// <param name="datasetId"></param>
        /// <param name="datasetVersionOrderNr"></param>
        /// <param name="dataStructureId"></param>
        /// <param name="title"></param>
        /// <param name="extention"></param>
        /// <returns></returns>
        public string CreateFile(long datasetId, long datasetVersionOrderNr, long dataStructureId, string title, string extention)
        {
            string dataPath = GetFullStorePath(datasetId, datasetVersionOrderNr, title, extention);

            //Template will not be filtered by columns
            if (this.VisibleColumns == null)
            {
                #region generate file with full datastructure

                string dataStructureFilePath = GetDataStructureTemplatePath(dataStructureId, extention);
                //dataPath = GetStorePath(datasetId, datasetVersionOrderNr, title, extention);

                try
                {
                    SpreadsheetDocument dataStructureFile = SpreadsheetDocument.Open(dataStructureFilePath, true);
                    SpreadsheetDocument dataFile          = SpreadsheetDocument.Create(dataPath,
                                                                                       dataStructureFile.DocumentType);

                    foreach (OpenXmlPart part in dataStructureFile.GetPartsOfType <OpenXmlPart>())
                    {
                        OpenXmlPart newPart = dataFile.AddPart <OpenXmlPart>(part);
                    }

                    dataFile.WorkbookPart.Workbook.Save();
                    dataStructureFile.Dispose();
                    dataFile.Dispose();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message.ToString());
                }

                #endregion
            }

            // create a file with a subset of variables
            if (this.VisibleColumns != null)
            {
                /// call templateprovider from rpm
                ExcelTemplateProvider provider = new ExcelTemplateProvider();

                string path     = GetStorePath(datasetId, datasetVersionOrderNr);
                string newTitle = GetNewTitle(datasetId, datasetVersionOrderNr, title, extention);


                provider.CreateTemplate(getVariableIds(this.VisibleColumns), dataStructureId, path, newTitle);
            }

            return(dataPath);
        }
예제 #4
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="ns"></param>
        /// <param name="table"></param>
        /// <param name="title"></param>
        /// <param name="extention"></param>
        /// <returns></returns>
        public string CreateFile(string ns, long dataStructureId, string title, string extension)
        {
            string dataPath = GetFullStorePath(ns, title, extension);

            //Template will not be filtered by columns
            if (this.VisibleColumns == null)
            {
                #region generate file with full datastructure

                string dataStructureFilePath = GetDataStructureTemplatePath(dataStructureId, extension);

                try
                {
                    SpreadsheetDocument dataStructureFile = SpreadsheetDocument.Open(dataStructureFilePath, true);
                    SpreadsheetDocument dataFile          = SpreadsheetDocument.Create(dataPath,
                                                                                       dataStructureFile.DocumentType);

                    foreach (OpenXmlPart part in dataStructureFile.GetPartsOfType <OpenXmlPart>())
                    {
                        OpenXmlPart newPart = dataFile.AddPart <OpenXmlPart>(part);
                    }

                    dataFile.WorkbookPart.Workbook.Save();
                    dataStructureFile.Dispose();
                    dataFile.Dispose();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message.ToString());
                }

                #endregion
            }

            return(dataPath);
        }
        // Adds child parts and generates content of the specified part.
        private void CreateParts(SpreadsheetDocument document)
        {
            var context = new SaveContext();

            var workbookPart = document.WorkbookPart ?? document.AddWorkbookPart();

            var worksheets = WorksheetsInternal;
            var partsToRemove = workbookPart.Parts.Where(s => worksheets.Deleted.Contains(s.RelationshipId)).ToList();
            partsToRemove.ForEach(s => workbookPart.DeletePart(s.OpenXmlPart));
            context.RelIdGenerator.AddValues(workbookPart.Parts.Select(p => p.RelationshipId).ToList(), RelType.Workbook);

            var extendedFilePropertiesPart = document.ExtendedFilePropertiesPart ??
                                             document.AddNewPart<ExtendedFilePropertiesPart>(
                                                 context.RelIdGenerator.GetNext(RelType.Workbook));

            GenerateExtendedFilePropertiesPartContent(extendedFilePropertiesPart);

            GenerateWorkbookPartContent(workbookPart, context);

            var sharedStringTablePart = workbookPart.SharedStringTablePart ??
                                        workbookPart.AddNewPart<SharedStringTablePart>(
                                            context.RelIdGenerator.GetNext(RelType.Workbook));

            GenerateSharedStringTablePartContent(sharedStringTablePart, context);

            var workbookStylesPart = workbookPart.WorkbookStylesPart ??
                                     workbookPart.AddNewPart<WorkbookStylesPart>(
                                         context.RelIdGenerator.GetNext(RelType.Workbook));

            GenerateWorkbookStylesPartContent(workbookStylesPart, context);

            foreach (var worksheet in WorksheetsInternal.Cast<XLWorksheet>().OrderBy(w => w.Position))
            {
                //context.RelIdGenerator.Reset(RelType.);
                WorksheetPart worksheetPart;
                var wsRelId = worksheet.RelId;
                if (workbookPart.Parts.Any(p => p.RelationshipId == wsRelId))
                {
                    worksheetPart = (WorksheetPart)workbookPart.GetPartById(wsRelId);
                    var wsPartsToRemove = worksheetPart.TableDefinitionParts.ToList();
                    wsPartsToRemove.ForEach(tdp => worksheetPart.DeletePart(tdp));
                }
                else
                    worksheetPart = workbookPart.AddNewPart<WorksheetPart>(wsRelId);


                context.RelIdGenerator.AddValues(worksheetPart.HyperlinkRelationships.Select(hr => hr.Id).ToList(),
                    RelType.Workbook);
                context.RelIdGenerator.AddValues(worksheetPart.Parts.Select(p => p.RelationshipId).ToList(),
                    RelType.Workbook);
                if (worksheetPart.DrawingsPart != null)
                    context.RelIdGenerator.AddValues(
                        worksheetPart.DrawingsPart.Parts.Select(p => p.RelationshipId).ToList(), RelType.Workbook);

                // delete comment related parts (todo: review)
                DeleteComments(worksheetPart, worksheet, context);

                if (worksheet.Internals.CellsCollection.GetCells(c => c.HasComment).Any())
                {
                    var worksheetCommentsPart =
                        worksheetPart.AddNewPart<WorksheetCommentsPart>(context.RelIdGenerator.GetNext(RelType.Workbook));

                    GenerateWorksheetCommentsPartContent(worksheetCommentsPart, worksheet);

                    //VmlDrawingPart vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>(worksheet.LegacyDrawingId);
                    var vmlDrawingPart = worksheetPart.VmlDrawingParts.FirstOrDefault();
                    if (vmlDrawingPart == null)
                    {
                        if (XLHelper.IsNullOrWhiteSpace(worksheet.LegacyDrawingId))
                        {
                            worksheet.LegacyDrawingId = context.RelIdGenerator.GetNext(RelType.Workbook);
                            worksheet.LegacyDrawingIsNew = true;
                        }

                        vmlDrawingPart = worksheetPart.AddNewPart<VmlDrawingPart>(worksheet.LegacyDrawingId);
                    }
                    GenerateVmlDrawingPartContent(vmlDrawingPart, worksheet, context);
                }

                GenerateWorksheetPartContent(worksheetPart, worksheet, context);

                if (worksheet.PivotTables.Any())
                {
                    GeneratePivotTables(workbookPart, worksheetPart, worksheet, context);
                }


                //DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>("rId1");
                //GenerateDrawingsPartContent(drawingsPart, worksheet);

                //foreach (var chart in worksheet.Charts)
                //{
                //    ChartPart chartPart = drawingsPart.AddNewPart<ChartPart>("rId1");
                //    GenerateChartPartContent(chartPart, (XLChart)chart);
                //}
            }

            GenerateCalculationChainPartContent(workbookPart, context);

            if (workbookPart.ThemePart == null)
            {
                var themePart = workbookPart.AddNewPart<ThemePart>(context.RelIdGenerator.GetNext(RelType.Workbook));
                GenerateThemePartContent(themePart);
            }

            if (CustomProperties.Any())
            {
                document.GetPartsOfType<CustomFilePropertiesPart>().ToList().ForEach(p => document.DeletePart(p));
                var customFilePropertiesPart =
                    document.AddNewPart<CustomFilePropertiesPart>(context.RelIdGenerator.GetNext(RelType.Workbook));

                GenerateCustomFilePropertiesPartContent(customFilePropertiesPart);
            }
            SetPackageProperties(document);
        }
예제 #6
0
        /// <summary>
        /// Creates the excel report.
        /// </summary>
        /// <param name="templatePath">The report template file path.</param>
        /// <param name="destinationPath">The destination path of the generated report.</param>
        /// <param name="sessionIds">The session ids.</param>
        public static void GenerateReport(string templatePath, string destinationPath, IList <string> sessionIds)
        {
            // Build the list of selected session ID's into a comma delimited string.
            for (int i = 0; i < sessionIds.Count; i++)
            {
                sessionIds[i] = "'" + sessionIds[i] + "'";
            }
            string sessionIdList = string.Join(",", sessionIds);

            // Create a temporary staging Excel file.
            string appDir         = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
            string stagedFilename = Path.Combine(appDir, Path.GetFileName(destinationPath));

            using (SpreadsheetDocument outputWorkbook = SpreadsheetDocument.Create(stagedFilename, SpreadsheetDocumentType.Workbook, true))
            {
                // Make sure the new workbook is clear.
                outputWorkbook.DeleteParts(outputWorkbook.GetPartsOfType <OpenXmlPart>());

                // Copy all the parts from the report template to the output workbook.
                using (SpreadsheetDocument templateWorkbook = SpreadsheetDocument.Open(templatePath, false))
                {
                    foreach (OpenXmlPart part in templateWorkbook.GetPartsOfType <OpenXmlPart>())
                    {
                        outputWorkbook.AddPart(part);
                    }
                }

                // Process each of the connections in the output workbook.
                if (outputWorkbook?.WorkbookPart?.ConnectionsPart?.Connections != null)
                {
                    foreach (Connection connection in outputWorkbook.WorkbookPart.ConnectionsPart.Connections.Elements <Connection>())
                    {
                        // If this connection points to a database link then perform some additional processing.
                        if (connection?.DatabaseProperties != null && connection.DatabaseProperties.Connection.Value.StartsWith("Provider="))
                        {
                            // Get the database connection string.
                            OleDbConnectionStringBuilder oleDbConnString = new OleDbConnectionStringBuilder(connection.DatabaseProperties.Connection.Value);

                            // Change the user name and password.
                            oleDbConnString.Add("User ID", "report_viewer");
                            oleDbConnString.Add("Password", "report_viewer");

                            // Change the data source and the initial catalog (database).
                            oleDbConnString.Add("Data Source", GlobalSettings.Items[Setting.ReportingDatabaseServer]);
                            oleDbConnString.Add("Initial Catalog", GlobalSettings.Items[Setting.ReportingDatabase]);
                            string appName = GlobalSettings.IsDistributedSystem ? "STF" : "STB";
                            oleDbConnString.Add("Application Name", appName);

                            // Put the modified connection string back into the output workbook.
                            connection.DatabaseProperties.Connection.Value = oleDbConnString.ToString();

                            // Modify the SQL command to include the list of session IDs.
                            string sqlCommand = connection.DatabaseProperties.Command.Value;
                            connection.DatabaseProperties.Command.Value = sqlCommand.Replace("'{SessionId}'", sessionIdList);
                        }

                        if (connection?.DatabaseProperties != null &&
                            connection.DatabaseProperties.Connection.Value.StartsWith("DRIVER="))
                        {
                            DbConnectionStringBuilder dbConnectionString = new DbConnectionStringBuilder(true);
                            dbConnectionString.ConnectionString = connection.DatabaseProperties.Connection.Value;
                            dbConnectionString["SERVER"]        = GlobalSettings.Items[Setting.ReportingDatabaseServer];
                            dbConnectionString["UID"]           = "report_viewer";
                            dbConnectionString["PWD"]           = "report_viewer";

                            connection.DatabaseProperties.Connection.Value = dbConnectionString.ConnectionString;
                            string sqlCommand = connection.DatabaseProperties.Command.Value;
                            connection.DatabaseProperties.Command.Value = sqlCommand.Replace("'{SessionId}'", sessionIdList);
                        }
                    }
                }

                // Modify a few of the workbook package properties.
                outputWorkbook.PackageProperties.Created        = DateTime.Now;
                outputWorkbook.PackageProperties.Modified       = DateTime.Now;
                outputWorkbook.PackageProperties.Creator        = string.Empty;
                outputWorkbook.PackageProperties.LastModifiedBy = string.Empty;
                if (outputWorkbook?.WorkbookPart?.Workbook?.AbsolutePath != null)
                {
                    outputWorkbook.WorkbookPart.Workbook.AbsolutePath = null;
                }

                // Save the output workbook.
                outputWorkbook.WorkbookPart.Workbook.Save();
            }

            // Save the staged Excel file.
            File.Copy(stagedFilename, destinationPath, true);

            // Try to delete the temporary staged Excel file.
            try
            {
                File.Delete(stagedFilename);
            }
            catch (Exception)
            {
            }
        }
예제 #7
0
        /// <summary>
        ///
        /// </summary>
        /// <remarks></remarks>
        /// <seealso cref=""/>
        /// <param name="variables"></param>
        /// <param name="path"></param>
        /// <param name="filename"></param>
        /// <returns></returns>
        public SpreadsheetDocument CreateTemplate(List <Variable> variables, string path, string filename)
        {
            if (!Directory.Exists(Path.Combine(AppConfiguration.DataPath, path)))
            {
                Directory.CreateDirectory(Path.Combine(AppConfiguration.DataPath, path));
            }

            SpreadsheetDocument template          = SpreadsheetDocument.Open(Path.Combine(AppConfiguration.GetModuleWorkspacePath("RPM"), "Template", _fileName), true);
            SpreadsheetDocument dataStructureFile = SpreadsheetDocument.Create(Path.Combine(AppConfiguration.DataPath, path, filename), template.DocumentType);

            //dataStructureFile = SpreadsheetDocument.Open(Path.Combine(AppConfiguration.GetModuleWorkspacePath("RPM"), "Template", filename), true);


            foreach (OpenXmlPart part in template.GetPartsOfType <OpenXmlPart>())
            {
                OpenXmlPart newPart = dataStructureFile.AddPart <OpenXmlPart>(part);
            }

            template.Close();

            // get worksheet
            List <StyleIndexStruct> styleIndex  = new List <StyleIndexStruct>();
            CellFormats             cellFormats = dataStructureFile.WorkbookPart.WorkbookStylesPart.Stylesheet.Elements <CellFormats>().First();
            //number 0,00
            CellFormat cellFormat = new CellFormat()
            {
                NumberFormatId = (UInt32Value)2U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
            };

            cellFormat.Protection        = new Protection();
            cellFormat.Protection.Locked = false;
            cellFormats.Append(cellFormat);
            styleIndex.Add(new StyleIndexStruct()
            {
                Name = "Decimal", Index = (uint)cellFormats.Count++, DisplayPattern = null
            });
            //number 0
            cellFormat = new CellFormat()
            {
                NumberFormatId = (UInt32Value)1U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
            };
            cellFormat.Protection        = new Protection();
            cellFormat.Protection.Locked = false;
            cellFormats.Append(cellFormat);
            styleIndex.Add(new StyleIndexStruct()
            {
                Name = "Number", Index = (uint)cellFormats.Count++, DisplayPattern = null
            });
            //text
            cellFormat = new CellFormat()
            {
                NumberFormatId = (UInt32Value)49U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
            };
            cellFormat.Protection        = new Protection();
            cellFormat.Protection.Locked = false;
            cellFormats.Append(cellFormat);
            styleIndex.Add(new StyleIndexStruct()
            {
                Name = "Text", Index = (uint)cellFormats.Count++, DisplayPattern = null
            });
            //DateTime
            cellFormat = new CellFormat()
            {
                NumberFormatId = (UInt32Value)22U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
            };
            cellFormat.Protection        = new Protection();
            cellFormat.Protection.Locked = false;
            cellFormats.Append(cellFormat);
            styleIndex.Add(new StyleIndexStruct()
            {
                Name = "DateTime", Index = (uint)cellFormats.Count++, DisplayPattern = DataTypeDisplayPattern.Pattern.Where(p => p.Systemtype.Equals(DataTypeCode.DateTime) && p.Name.Equals("DateTime")).FirstOrDefault()
            });
            //Date
            cellFormat = new CellFormat()
            {
                NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
            };
            cellFormat.Protection        = new Protection();
            cellFormat.Protection.Locked = false;
            cellFormats.Append(cellFormat);
            styleIndex.Add(new StyleIndexStruct()
            {
                Name = "Date", Index = (uint)cellFormats.Count++, DisplayPattern = DataTypeDisplayPattern.Pattern.Where(p => p.Systemtype.Equals(DataTypeCode.DateTime) && p.Name.Equals("Date")).FirstOrDefault()
            });
            //Time
            cellFormat = new CellFormat()
            {
                NumberFormatId = (UInt32Value)21U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)1U, ApplyNumberFormat = true
            };
            cellFormat.Protection        = new Protection();
            cellFormat.Protection.Locked = false;
            cellFormats.Append(cellFormat);
            styleIndex.Add(new StyleIndexStruct()
            {
                Name = "Time", Index = (uint)cellFormats.Count++, DisplayPattern = DataTypeDisplayPattern.Pattern.Where(p => p.Systemtype.Equals(DataTypeCode.DateTime) && p.Name.Equals("Time")).FirstOrDefault()
            });

            Worksheet worksheet = dataStructureFile.WorkbookPart.WorksheetParts.First().Worksheet;



            List <Row> rows = GetRows(worksheet, 1, 11);

            foreach (Variable var in variables)
            {
                DataContainerManager CM            = new DataContainerManager();
                DataAttribute        dataAttribute = CM.DataAttributeRepo.Get(var.DataAttribute.Id);

                int    indexVar    = variables.ToList().IndexOf(var) + 1;
                string columnIndex = GetClomunIndex(indexVar);

                string cellRef = columnIndex + 1;
                Cell   cell    = new Cell()
                {
                    CellReference = cellRef,
                    StyleIndex    = (UInt32Value)4U,
                    DataType      = CellValues.String,
                    CellValue     = new CellValue(var.Label)
                };
                rows.ElementAt(0).AppendChild(cell);

                cellRef = columnIndex + 2;
                cell    = new Cell()
                {
                    CellReference = cellRef,
                    DataType      = CellValues.String,
                    StyleIndex    = getExcelStyleIndex(dataAttribute.DataType, styleIndex),
                    CellValue     = new CellValue("")
                };
                rows.ElementAt(1).AppendChild(cell);

                cellRef = columnIndex + 3;
                cell    = new Cell()
                {
                    CellReference = cellRef,
                    StyleIndex    = (UInt32Value)4U,
                    DataType      = CellValues.String,
                    CellValue     = new CellValue(var.Id.ToString())
                };
                rows.ElementAt(2).AppendChild(cell);



                cellRef = columnIndex + 4;
                cell    = new Cell()
                {
                    CellReference = cellRef,
                    StyleIndex    = (UInt32Value)4U,
                    DataType      = CellValues.String,
                    CellValue     = new CellValue(dataAttribute.ShortName)
                };
                rows.ElementAt(3).AppendChild(cell);

                // description from variable
                // if not then from attribute
                string description = "";
                description = String.IsNullOrEmpty(var.Description) ? dataAttribute.Description : var.Description;

                cellRef = columnIndex + 5;
                cell    = new Cell()
                {
                    CellReference = cellRef,
                    StyleIndex    = (UInt32Value)4U,
                    DataType      = CellValues.String,
                    CellValue     = new CellValue(description)
                };
                rows.ElementAt(4).AppendChild(cell);

                string classification = "";

                if (dataAttribute.Classification != null)
                {
                    classification = dataAttribute.Classification.Name;
                }

                cellRef = columnIndex + 6;
                cell    = new Cell()
                {
                    CellReference = cellRef,
                    StyleIndex    = (UInt32Value)4U,
                    DataType      = CellValues.String,
                    CellValue     = new CellValue(classification)
                };
                rows.ElementAt(5).AppendChild(cell);

                string unit = "";

                if (var.Unit != null)
                {
                    unit = var.Unit.Name;
                }

                cellRef = columnIndex + 7;
                cell    = new Cell()
                {
                    CellReference = cellRef,
                    StyleIndex    = (UInt32Value)4U,
                    DataType      = CellValues.String,
                    CellValue     = new CellValue(unit)
                };
                rows.ElementAt(6).AppendChild(cell);

                string dataType = "";

                if (dataAttribute.DataType != null)
                {
                    dataType = dataAttribute.DataType.Name;
                }

                cellRef = columnIndex + 8;
                cell    = new Cell()
                {
                    CellReference = cellRef,
                    StyleIndex    = (UInt32Value)4U,
                    DataType      = CellValues.String,
                    CellValue     = new CellValue(dataType)
                };
                rows.ElementAt(7).AppendChild(cell);

                cellRef = columnIndex + 9;
                cell    = new Cell()
                {
                    CellReference = cellRef,
                    StyleIndex    = (UInt32Value)4U,
                    DataType      = CellValues.String,
                    CellValue     = new CellValue(var.IsValueOptional.ToString())
                };
                rows.ElementAt(8).AppendChild(cell);

                cellRef = columnIndex + 10;
                cell    = new Cell()
                {
                    CellReference = cellRef,
                    StyleIndex    = (UInt32Value)4U,
                    DataType      = CellValues.String,
                    CellValue     = new CellValue(dataAttribute.IsMultiValue.ToString())
                };
                rows.ElementAt(9).AppendChild(cell);
            }

            foreach (DefinedName name in dataStructureFile.WorkbookPart.Workbook.GetFirstChild <DefinedNames>())
            {
                if (name.Name == "Data" || name.Name == "VariableIdentifiers")
                {
                    string[] tempArr = name.InnerText.Split('$');
                    string   temp    = "";
                    tempArr[tempArr.Count() - 2] = GetClomunIndex(variables.Count());
                    foreach (string t in tempArr)
                    {
                        if (t == tempArr.First())
                        {
                            temp = temp + t;
                        }
                        else
                        {
                            temp = temp + "$" + t;
                        }
                    }
                    name.Text = temp;
                }
            }

            //WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
            //WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

            dataStructureFile.WorkbookPart.Workbook.Save();

            dataStructureFile.Close();

            return(dataStructureFile);
        }
예제 #8
0
        /// <summary>
        ///
        /// </summary>
        /// <remarks></remarks>
        /// <seealso cref=""/>
        /// <param name="variables"></param>
        /// <param name="path"></param>
        /// <param name="filename"></param>
        /// <returns></returns>
        public SpreadsheetDocument CreateTemplate(List <long> variableIds, string path, string filename)
        {
            if (!Directory.Exists(Path.Combine(AppConfiguration.DataPath, path)))
            {
                Directory.CreateDirectory(Path.Combine(AppConfiguration.DataPath, path));
            }

            SpreadsheetDocument template          = SpreadsheetDocument.Open(Path.Combine(AppConfiguration.GetModuleWorkspacePath("RPM"), "Template", _fileName), true);
            SpreadsheetDocument dataStructureFile = SpreadsheetDocument.Create(Path.Combine(AppConfiguration.DataPath, path, filename), template.DocumentType);

            //dataStructureFile = SpreadsheetDocument.Open(Path.Combine(AppConfiguration.GetModuleWorkspacePath("RPM"), "Template", filename), true);



            foreach (OpenXmlPart part in template.GetPartsOfType <OpenXmlPart>())
            {
                OpenXmlPart newPart = dataStructureFile.AddPart <OpenXmlPart>(part);
            }

            template.Close();

            //uint iExcelIndex = 164;
            List <StyleIndexStruct> styleIndex = new List <StyleIndexStruct>();

            ExcelHelper.UpdateStylesheet(dataStructureFile.WorkbookPart.WorkbookStylesPart.Stylesheet, out styleIndex);

            Worksheet  worksheet = dataStructureFile.WorkbookPart.WorksheetParts.First().Worksheet;
            List <Row> rows      = GetRows(worksheet, 1, 11);

            List <Variable> variables = this.GetUnitOfWork().GetReadOnlyRepository <Variable>()
                                        .Query(p => variableIds.Contains(p.Id))
                                        .OrderBy(p => p.OrderNo)
                                        .ToList();

            foreach (Variable var in variables)
            {
                DataContainerManager CM = null;
                try
                {
                    CM = new DataContainerManager();
                    DataAttribute dataAttribute = CM.DataAttributeRepo.Get(var.DataAttribute.Id);

                    int    indexVar    = variables.ToList().IndexOf(var) + 1;
                    string columnIndex = GetClomunIndex(indexVar);

                    string cellRef = columnIndex + 1;
                    Cell   cell    = new Cell()
                    {
                        CellReference = cellRef,
                        StyleIndex    = (UInt32Value)4U,
                        DataType      = CellValues.String,
                        CellValue     = new CellValue(var.Label)
                    };
                    rows.ElementAt(0).AppendChild(cell);

                    cellRef = columnIndex + 2;
                    cell    = new Cell()
                    {
                        CellReference = cellRef,
                        DataType      = CellValues.String,
                        StyleIndex    = ExcelHelper.GetExcelStyleIndex(dataAttribute.DataType, styleIndex),
                        CellValue     = new CellValue("")
                    };
                    rows.ElementAt(1).AppendChild(cell);

                    cellRef = columnIndex + 3;
                    cell    = new Cell()
                    {
                        CellReference = cellRef,
                        StyleIndex    = (UInt32Value)4U,
                        DataType      = CellValues.String,
                        CellValue     = new CellValue(var.Id.ToString())
                    };
                    rows.ElementAt(2).AppendChild(cell);



                    cellRef = columnIndex + 4;
                    cell    = new Cell()
                    {
                        CellReference = cellRef,
                        StyleIndex    = (UInt32Value)4U,
                        DataType      = CellValues.String,
                        CellValue     = new CellValue(dataAttribute.ShortName)
                    };
                    rows.ElementAt(3).AppendChild(cell);

                    // description from variable
                    // if not then from attribute
                    string description = "";
                    description = String.IsNullOrEmpty(var.Description) ? dataAttribute.Description : var.Description;

                    cellRef = columnIndex + 5;
                    cell    = new Cell()
                    {
                        CellReference = cellRef,
                        StyleIndex    = (UInt32Value)4U,
                        DataType      = CellValues.String,
                        CellValue     = new CellValue(description)
                    };
                    rows.ElementAt(4).AppendChild(cell);

                    string classification = "";

                    if (dataAttribute.Classification != null)
                    {
                        classification = dataAttribute.Classification.Name;
                    }

                    cellRef = columnIndex + 6;
                    cell    = new Cell()
                    {
                        CellReference = cellRef,
                        StyleIndex    = (UInt32Value)4U,
                        DataType      = CellValues.String,
                        CellValue     = new CellValue(classification)
                    };
                    rows.ElementAt(5).AppendChild(cell);

                    string unit = "";

                    if (var.Unit != null)
                    {
                        unit = var.Unit.Name;
                    }

                    cellRef = columnIndex + 7;
                    cell    = new Cell()
                    {
                        CellReference = cellRef,
                        StyleIndex    = (UInt32Value)4U,
                        DataType      = CellValues.String,
                        CellValue     = new CellValue(unit)
                    };
                    rows.ElementAt(6).AppendChild(cell);

                    string dataType = "";

                    if (dataAttribute.DataType != null)
                    {
                        dataType = dataAttribute.DataType.Name;
                    }

                    cellRef = columnIndex + 8;
                    cell    = new Cell()
                    {
                        CellReference = cellRef,
                        StyleIndex    = (UInt32Value)4U,
                        DataType      = CellValues.String,
                        CellValue     = new CellValue(dataType)
                    };
                    rows.ElementAt(7).AppendChild(cell);

                    cellRef = columnIndex + 9;
                    cell    = new Cell()
                    {
                        CellReference = cellRef,
                        StyleIndex    = (UInt32Value)4U,
                        DataType      = CellValues.String,
                        CellValue     = new CellValue(var.IsValueOptional.ToString())
                    };
                    rows.ElementAt(8).AppendChild(cell);

                    cellRef = columnIndex + 10;
                    cell    = new Cell()
                    {
                        CellReference = cellRef,
                        StyleIndex    = (UInt32Value)4U,
                        DataType      = CellValues.String,
                        CellValue     = new CellValue(dataAttribute.IsMultiValue.ToString())
                    };
                    rows.ElementAt(9).AppendChild(cell);
                }
                finally
                {
                    CM.Dispose();
                }
            }

            foreach (DefinedName name in dataStructureFile.WorkbookPart.Workbook.GetFirstChild <DefinedNames>())
            {
                if (name.Name == "Data" || name.Name == "VariableIdentifiers")
                {
                    string[] tempArr = name.InnerText.Split('$');
                    string   temp    = "";
                    tempArr[tempArr.Count() - 2] = GetClomunIndex(variables.Count());
                    foreach (string t in tempArr)
                    {
                        if (t == tempArr.First())
                        {
                            temp = temp + t;
                        }
                        else
                        {
                            temp = temp + "$" + t;
                        }
                    }
                    name.Text = temp;
                }
            }

            //WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
            //WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

            dataStructureFile.WorkbookPart.Workbook.Save();

            dataStructureFile.Close();

            return(dataStructureFile);
        }
예제 #9
0
        public static void AddDataSpreadsheetWorkBook(string filepath, string col, uint rowNum, string text, CellValues type = CellValues.Number)
        {
            OpenSettings openSettings = new OpenSettings();

            openSettings.MarkupCompatibilityProcessSettings =
                new MarkupCompatibilityProcessSettings(
                    MarkupCompatibilityProcessMode.ProcessAllParts,
                    FileFormatVersions.Office2013
                    );

            SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filepath, true, openSettings);

            WorkbookPart workbookPart = spreadsheet.GetPartsOfType <WorkbookPart>().First();
            //workbookPart.Workbook = new Workbook();

            WorksheetPart worksheetPart = workbookPart.GetPartsOfType <WorksheetPart>().First();
            //worksheetPart.Worksheet = new Worksheet(new SheetData());

            //Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild<Sheets>();
            //Sheet sheet = new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };

            //sheets.Append(sheet);

            //  Adding data:
            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
            //  Add a row:
            Row row;

            //row = new Row() { RowIndex = 1 };
            //sheetData.Append(row);
            try
            {
                row = (Row)sheetData.ElementAt((int)rowNum);
            }
            catch (ArgumentOutOfRangeException ex)
            {
                row = new Row()
                {
                    RowIndex = new UInt32Value((uint)rowNum)
                };
                sheetData.Append(row);
            }
            //Row Row2nd = (Row)sheetData.ElementAt(100);

            //  Find the A1 cell in the first Cell
            Cell   refCell      = null;
            string cellLocation = col + rowNum.ToString();

            foreach (Cell cell in row.Elements <Cell>())
            {
                if (string.Compare(cell.CellReference.Value, cellLocation, true) > 0)
                {
                    refCell = cell;
                    break;
                }
            }
            Cell newCell = new Cell()
            {
                CellReference = cellLocation
            };

            row.InsertBefore(newCell, refCell);
            //row.InsertBefore()
            //  Set the cell value:
            newCell.CellValue = new CellValue(text);
            //Can be Shared String here:

            newCell.DataType = new EnumValue <CellValues>(type);

            workbookPart.Workbook.Save();
            spreadsheet.Close();
        }