public override void BeforeExecute(Sungero.Reporting.Server.BeforeExecuteEventArgs e) { RegistrationSettingReport.ReportDate = Calendar.UserToday; // Заполнить значения по умолчанию, если отчет вызван в невизуальном режиме. if (RegistrationSettingReport.FilterDepartmentsForBusinessUnits == null) { RegistrationSettingReport.FilterDepartmentsForBusinessUnits = true; } var filterDepartment = RegistrationSettingReport.FilterDepartmentsForBusinessUnits == true; #region Описание параметров if (RegistrationSettingReport.BusinessUnit != null) { RegistrationSettingReport.ParamsDescriprion += string.Format("{0}: {1} \n", Reports.Resources.RegistrationSettingReport.BusinessUnit, RegistrationSettingReport.BusinessUnit.Name); } if (!string.IsNullOrWhiteSpace(RegistrationSettingReport.Direction)) { RegistrationSettingReport.ParamsDescriprion += string.Format("{0}: {1} \n", Reports.Resources.RegistrationSettingReport.DocumentFlow, RegistrationSettingReport.DirectionLabel); } RegistrationSettingReport.ParamsDescriprion += string.Format("{0}: {1} \n", Reports.Resources.RegistrationSettingReport.FilterDepartmentsForBusinessUnits, RegistrationSettingReport.FilterDepartmentsForBusinessUnits == true ? Reports.Resources.RegistrationSettingReport.Yes : Reports.Resources.RegistrationSettingReport.No); #endregion var sourceDataTableName = Constants.RegistrationSettingReport.SourceTableName; RegistrationSettingReport.SourceDataTableName = sourceDataTableName; var reportSessionId = System.Guid.NewGuid().ToString(); RegistrationSettingReport.ReportSessionId = reportSessionId; var units = RegistrationSettingReport.BusinessUnit != null ? new List <Company.IBusinessUnit>() { RegistrationSettingReport.BusinessUnit } : Company.BusinessUnits.GetAll().Where(u => u.Status == CoreEntities.DatabookEntry.Status.Active).ToList(); var flows = new List <Enumeration>() { DocumentFlow.Incoming, DocumentFlow.Outgoing, DocumentFlow.Inner, DocumentFlow.Contracts }; if (!string.IsNullOrWhiteSpace(RegistrationSettingReport.Direction)) { flows = flows.Where(i => i.Value == RegistrationSettingReport.Direction).ToList(); } var kinds = DocumentKinds.GetAll().Where(k => k.Status == CoreEntities.DatabookEntry.Status.Active && !Equals(k.NumberingType, DocumentKind.NumberingType.NotNumerable)).ToList(); var settings = RegistrationSettings.GetAll().Where(s => s.Status == CoreEntities.DatabookEntry.Status.Active && s.DocumentRegister.Status == CoreEntities.DatabookEntry.Status.Active).ToList(); var departments = Company.Departments.GetAll().Where(d => d.Status == CoreEntities.DatabookEntry.Status.Active).OrderBy(d => d.Name).ToList(); var separator = ";" + System.Environment.NewLine; var allDepartmentString = Reports.Resources.RegistrationSettingReport.AllDepartmentString; var otherDepartmentString = Reports.Resources.RegistrationSettingReport.OtherDepartmentString; // Строчки также используются в условной разметке для подсветки красным\курсивом. var settingNotFound = Reports.Resources.RegistrationSettingReport.SettingNotFound; var defaultSetting = Reports.Resources.RegistrationSettingReport.DefaultSetting; var registerNotFound = Reports.Resources.RegistrationSettingReport.RegisterNotFound; var tableData = new List <Structures.RegistrationSettingReport.TableLine>(); foreach (var unit in units) { foreach (var flow in flows) { foreach (var kind in kinds.Where(k => Equals(k.DocumentFlow, flow))) { var error = Equals(kind.NumberingType, DocumentKind.NumberingType.Numerable) ? settingNotFound : defaultSetting; var subSettings = settings.Where(s => Equals(s.DocumentFlow, flow) && (!s.BusinessUnits.Any() || s.BusinessUnits.Any(u => Equals(u.BusinessUnit, unit))) && s.DocumentKinds.Any(k => Equals(k.DocumentKind, kind))) .ToList(); foreach (var setting in subSettings) { var subDepartments = setting.Departments.Where(d => !filterDepartment || Equals(d.Department.BusinessUnit, unit)).ToList(); if (setting.Departments.Any() && !subDepartments.Any()) { continue; } var department = !setting.Departments.Any() ? allDepartmentString : string.Join(separator, subDepartments.Select(d => d.Department.Name)); var example = Functions.DocumentRegister.GetValueExample(setting.DocumentRegister); var line = Structures.RegistrationSettingReport.TableLine.Create(); line.BusinessUnit = unit.Name; line.DocumentFlow = GetDocumentFlowName(flow); line.DocumentFlowIndex = flows.IndexOf(flow); line.DocumentKind = kind.Name; line.RegistrationSetting = setting.Name; line.RegistrationSettingUri = Hyperlinks.Get(setting); line.Priority = setting.Priority ?? 0; line.Departments = department; line.SettingType = GetSettingType(kind, setting); line.DocumentRegister = setting.DocumentRegister.Name; line.DocumentRegisterUri = Hyperlinks.Get(setting.DocumentRegister); line.NumberExample = example; line.ReportSessionId = reportSessionId; tableData.Add(line); } // Если настроек для вида не найдено. if (!subSettings.Any() && kind.NumberingType == DocumentKind.NumberingType.Numerable) { var line = Structures.RegistrationSettingReport.TableLine.Create(); line.BusinessUnit = unit.Name; line.DocumentFlow = GetDocumentFlowName(flow); line.DocumentFlowIndex = flows.IndexOf(flow); line.DocumentKind = kind.Name; line.RegistrationSetting = error; line.Priority = -1; line.Departments = allDepartmentString; line.SettingType = GetSettingType(kind, null); line.ReportSessionId = reportSessionId; tableData.Add(line); continue; } // Если есть хоть одна настройка для всех подразделений - идём дальше. if (subSettings.Any(s => !s.Departments.Any())) { continue; } // Если нет подразделений без настроек - идем дальше var allDeparmentInUnit = departments.Where(d => Equals(d.BusinessUnit, unit)).ToList(); var settingDepartments = subSettings.SelectMany(s => s.Departments).Select(s => s.Department).ToList(); if (!allDeparmentInUnit.Except(settingDepartments).Any()) { continue; } var allDepartments = filterDepartment ? departments.Where(d => Equals(d.BusinessUnit, unit)).ToList() : departments; var registers = new List <IDocumentRegister>(); var firstDepartment = allDepartments.Except(settingDepartments).FirstOrDefault(); if (firstDepartment != null) { registers.AddRange(Functions.DocumentRegister.GetDocumentRegistersByParams(kind, unit, firstDepartment, SettingType.Registration, false)); } registers = registers.Distinct().ToList(); var regDepartment = allDepartments.Any(d => settingDepartments.Contains(d)) ? otherDepartmentString : allDepartmentString; if (!registers.Any()) { var line = Structures.RegistrationSettingReport.TableLine.Create(); line.BusinessUnit = unit.Name; line.DocumentFlow = GetDocumentFlowName(flow); line.DocumentFlowIndex = flows.IndexOf(flow); line.DocumentKind = kind.Name; line.RegistrationSetting = registerNotFound; line.Priority = -1; line.Departments = regDepartment; line.SettingType = GetSettingType(kind, null); line.ReportSessionId = reportSessionId; tableData.Add(line); } foreach (var register in registers) { var example = Functions.DocumentRegister.GetValueExample(register); var line = Structures.RegistrationSettingReport.TableLine.Create(); line.BusinessUnit = unit.Name; line.DocumentFlow = GetDocumentFlowName(flow); line.DocumentFlowIndex = flows.IndexOf(flow); line.DocumentKind = kind.Name; line.RegistrationSetting = error; line.Priority = 0; line.Departments = regDepartment; line.DocumentRegister = register.Name; line.DocumentRegisterUri = Hyperlinks.Get(register); line.SettingType = GetSettingType(kind, null); line.NumberExample = example; line.ReportSessionId = reportSessionId; tableData.Add(line); } } } } // Полная сортировка данных. tableData = tableData .OrderBy(t => t.BusinessUnit) .ThenBy(t => t.DocumentFlowIndex) .ThenBy(t => t.DocumentKind) .ThenByDescending(t => t.Priority) .ThenBy(t => t.Departments) .ToList(); for (var i = 0; i < tableData.Count; i++) { tableData[i].Id = i; } Functions.Module.WriteStructuresToTable(RegistrationSettingReport.SourceDataTableName, tableData); }
private void WriteSelectedWorksheet() { // split into writing for existing worksheet and for new worksheet this.CleanUpReallyEmptyCells(); int i = 0; bool bFound = false; OpenXmlElement oxe; SLColumnProperties cp; SLRowProperties rp; byte byMaxOutline; List<int> listintkeys; // remove empty rows/columns plus getting the maximum outline levels at the same time. byMaxOutline = 0; listintkeys = slws.RowProperties.Keys.ToList<int>(); foreach (int key in listintkeys) { rp = slws.RowProperties[key]; if (rp.IsEmpty) slws.RowProperties.Remove(key); else if (rp.OutlineLevel > byMaxOutline) byMaxOutline = rp.OutlineLevel; } if (byMaxOutline > 0) slws.SheetFormatProperties.OutlineLevelRow = byMaxOutline; byMaxOutline = 0; listintkeys = slws.ColumnProperties.Keys.ToList<int>(); foreach (int key in listintkeys) { cp = slws.ColumnProperties[key]; if (cp.IsEmpty) slws.ColumnProperties.Remove(key); else if (cp.OutlineLevel > byMaxOutline) byMaxOutline = cp.OutlineLevel; } if (byMaxOutline > 0) slws.SheetFormatProperties.OutlineLevelColumn = byMaxOutline; List<SLCellPoint> listCellRefKeys = slws.Cells.Keys.ToList<SLCellPoint>(); listCellRefKeys.Sort(new SLCellReferencePointComparer()); HashSet<int> hsRows = new HashSet<int>(listCellRefKeys.GroupBy(g => g.RowIndex).Select(s => s.Key).ToList<int>()); hsRows.UnionWith(slws.RowProperties.Keys.ToList<int>()); // this now contains every row index that's either in the list of row properties // or in the list of cells. List<int> listRowIndex = hsRows.ToList<int>(); listRowIndex.Sort(); List<int> listColumnIndex = slws.ColumnProperties.Keys.ToList<int>(); listColumnIndex.Sort(); int iDimensionStartRowIndex = SLConstants.RowLimit + 1; int iDimensionStartColumnIndex = SLConstants.ColumnLimit + 1; int iDimensionEndRowIndex = -1; int iDimensionEndColumnIndex = -1; if (listCellRefKeys.Count > 0 || listRowIndex.Count > 0 || listColumnIndex.Count > 0 || slws.MergeCells.Count > 0) { foreach (SLCellPoint refpt in slws.Cells.Keys) { // just check for columns because row checking is already done with RowProperties // this cuts down on checking, and speed things up. if (refpt.ColumnIndex < iDimensionStartColumnIndex) iDimensionStartColumnIndex = refpt.ColumnIndex; if (refpt.ColumnIndex > iDimensionEndColumnIndex) iDimensionEndColumnIndex = refpt.ColumnIndex; } if (listRowIndex.Count > 0) { if (listRowIndex[0] < iDimensionStartRowIndex) iDimensionStartRowIndex = listRowIndex[0]; if (listRowIndex[listRowIndex.Count - 1] > iDimensionEndRowIndex) iDimensionEndRowIndex = listRowIndex[listRowIndex.Count - 1]; } if (listColumnIndex.Count > 0) { if (listColumnIndex[0] < iDimensionStartColumnIndex) iDimensionStartColumnIndex = listColumnIndex[0]; if (listColumnIndex[listColumnIndex.Count - 1] > iDimensionEndColumnIndex) iDimensionEndColumnIndex = listColumnIndex[listColumnIndex.Count - 1]; } foreach (SLMergeCell mc in slws.MergeCells) { if (mc.StartRowIndex < iDimensionStartRowIndex) iDimensionStartRowIndex = mc.StartRowIndex; if (mc.StartColumnIndex < iDimensionStartColumnIndex) iDimensionStartColumnIndex = mc.StartColumnIndex; if (mc.EndRowIndex > iDimensionEndRowIndex) iDimensionEndRowIndex = mc.EndRowIndex; if (mc.EndColumnIndex > iDimensionEndColumnIndex) iDimensionEndColumnIndex = mc.EndColumnIndex; } // need to do for hyperlinks? //foreach (SLHyperlink hl in slws.Hyperlinks) //{ // if (hl.Reference.StartRowIndex < iDimensionStartRowIndex) iDimensionStartRowIndex = hl.Reference.StartRowIndex; // if (hl.Reference.StartColumnIndex < iDimensionStartColumnIndex) iDimensionStartColumnIndex = hl.Reference.StartColumnIndex; // if (hl.Reference.EndRowIndex > iDimensionEndRowIndex) iDimensionEndRowIndex = hl.Reference.EndRowIndex; // if (hl.Reference.EndColumnIndex > iDimensionEndColumnIndex) iDimensionEndColumnIndex = hl.Reference.EndColumnIndex; //} } string sDimensionCellRange = string.Empty; if (iDimensionStartRowIndex > SLConstants.RowLimit) iDimensionStartRowIndex = 1; if (iDimensionStartColumnIndex > SLConstants.ColumnLimit) iDimensionStartColumnIndex = 1; if (iDimensionEndRowIndex < 1) iDimensionEndRowIndex = 1; if (iDimensionEndColumnIndex < 1) iDimensionEndColumnIndex = 1; if (iDimensionStartRowIndex == iDimensionEndRowIndex && iDimensionStartColumnIndex == iDimensionEndColumnIndex) { sDimensionCellRange = SLTool.ToCellReference(iDimensionStartRowIndex, iDimensionStartColumnIndex); } else { sDimensionCellRange = string.Format("{0}:{1}", SLTool.ToCellReference(iDimensionStartRowIndex, iDimensionStartColumnIndex), SLTool.ToCellReference(iDimensionEndRowIndex, iDimensionEndColumnIndex)); } Row r; SLCell c; int iRowIndex = 0; int iCellDataKey = 0; int iRowKey = 0; SLCellPoint pt; if (!IsNewWorksheet) { // Need to check? //if (string.IsNullOrEmpty(gsSelectedWorksheetRelationshipID)) return; WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(gsSelectedWorksheetRelationshipID); if (slws.ForceCustomRowColumnDimensionsSplitting) { slws.ToggleCustomRowColumnDimension(true); } if (slws.PageSettings.HasSheetProperties) { wsp.Worksheet.SheetProperties = slws.PageSettings.SheetProperties.ToSheetProperties(); } else { wsp.Worksheet.SheetProperties = null; } wsp.Worksheet.SheetDimension = new SheetDimension() { Reference = sDimensionCellRange }; if (slws.SheetViews.Count > 0) { wsp.Worksheet.SheetViews = new SheetViews(); foreach (SLSheetView sv in slws.SheetViews) { wsp.Worksheet.SheetViews.Append(sv.ToSheetView()); } } else { wsp.Worksheet.SheetViews = null; } wsp.Worksheet.SheetFormatProperties = slws.SheetFormatProperties.ToSheetFormatProperties(); #region Filling Columns if (wsp.Worksheet.Elements<Columns>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<Columns>(); } if (slws.ColumnProperties.Count > 0) { Columns cols = new Columns(); Column col; int iPreviousColumnIndex = listColumnIndex[0]; int iCurrentColumnIndex = iPreviousColumnIndex; string sCollectiveColumnData = string.Empty; string sCurrentColumnData = string.Empty; int colmin, colmax; colmin = colmax = iCurrentColumnIndex; cp = slws.ColumnProperties[iCurrentColumnIndex]; sCollectiveColumnData = cp.ToHash(); col = new Column(); col.Min = (uint)colmin; col.Max = (uint)colmax; if (cp.HasWidth) { col.Width = cp.Width; col.CustomWidth = true; } else { col.Width = slws.SheetFormatProperties.DefaultColumnWidth; } if (cp.StyleIndex > 0) col.Style = cp.StyleIndex; if (cp.Hidden) col.Hidden = cp.Hidden; if (cp.BestFit) col.BestFit = cp.BestFit; if (cp.Phonetic) col.Phonetic = cp.Phonetic; if (cp.OutlineLevel > 0) col.OutlineLevel = cp.OutlineLevel; if (cp.Collapsed) col.Collapsed = cp.Collapsed; for (i = 1; i < listColumnIndex.Count; ++i) { iPreviousColumnIndex = iCurrentColumnIndex; iCurrentColumnIndex = listColumnIndex[i]; cp = slws.ColumnProperties[iCurrentColumnIndex]; sCurrentColumnData = cp.ToHash(); if ((iCurrentColumnIndex != (iPreviousColumnIndex + 1)) || (sCollectiveColumnData != sCurrentColumnData)) { col.Max = (uint)colmax; cols.Append(col); colmin = iCurrentColumnIndex; colmax = iCurrentColumnIndex; sCollectiveColumnData = sCurrentColumnData; col = new Column(); col.Min = (uint)colmin; col.Max = (uint)colmax; if (cp.HasWidth) { col.Width = cp.Width; col.CustomWidth = true; } else { col.Width = slws.SheetFormatProperties.DefaultColumnWidth; } if (cp.StyleIndex > 0) col.Style = cp.StyleIndex; if (cp.Hidden) col.Hidden = cp.Hidden; if (cp.BestFit) col.BestFit = cp.BestFit; if (cp.Phonetic) col.Phonetic = cp.Phonetic; if (cp.OutlineLevel > 0) col.OutlineLevel = cp.OutlineLevel; if (cp.Collapsed) col.Collapsed = cp.Collapsed; } else { colmax = iCurrentColumnIndex; } } // there's always a "leftover" column col.Max = (uint)colmax; cols.Append(col); bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { if (child is SheetProperties || child is SheetDimension || child is SheetViews || child is SheetFormatProperties) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(cols, oxe); } else { wsp.Worksheet.PrependChild(cols); } } #endregion SheetData sd = new SheetData(); iCellDataKey = 0; for (iRowKey = 0; iRowKey < listRowIndex.Count; ++iRowKey) { iRowIndex = listRowIndex[iRowKey]; if (slws.RowProperties.ContainsKey(iRowIndex)) { r = slws.RowProperties[iRowIndex].ToRow(); r.RowIndex = (uint)iRowIndex; } else { r = new Row(); r.RowIndex = (uint)iRowIndex; } while (iCellDataKey < listCellRefKeys.Count) { pt = listCellRefKeys[iCellDataKey]; if (pt.RowIndex == iRowIndex) { c = slws.Cells[pt]; r.Append(c.ToCell(SLTool.ToCellReference(pt.RowIndex, pt.ColumnIndex))); ++iCellDataKey; } else { break; } } sd.Append(r); } wsp.Worksheet.RemoveAllChildren<SheetData>(); bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { if (child is SheetProperties || child is SheetDimension || child is SheetViews || child is SheetFormatProperties || child is Columns) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(sd, oxe); } else { wsp.Worksheet.PrependChild(sd); } #region Sheet protection if (wsp.Worksheet.Elements<SheetProtection>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<SheetProtection>(); } if (slws.HasSheetProtection) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(slws.SheetProtection.ToSheetProtection(), oxe); } else { wsp.Worksheet.PrependChild(slws.SheetProtection.ToSheetProtection()); } } #endregion #region AutoFilter if (wsp.Worksheet.Elements<AutoFilter>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<AutoFilter>(); } if (slws.HasAutoFilter) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(slws.AutoFilter.ToAutoFilter(), oxe); } else { wsp.Worksheet.PrependChild(slws.AutoFilter.ToAutoFilter()); } } #endregion #region Filling merge cells if (wsp.Worksheet.Elements<MergeCells>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<MergeCells>(); } if (slws.MergeCells.Count > 0) { MergeCells mcs = new MergeCells() { Count = (uint)slws.MergeCells.Count }; for (i = 0; i < slws.MergeCells.Count; ++i) { mcs.Append(slws.MergeCells[i].ToMergeCell()); } bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(mcs, oxe); } else { wsp.Worksheet.PrependChild(mcs); } } #endregion #region Conditional Formatting if (wsp.Worksheet.Elements<ConditionalFormatting>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<ConditionalFormatting>(); } if (slws.ConditionalFormattings.Count > 0) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties) { oxe = child; bFound = true; } } if (bFound) { for (i = slws.ConditionalFormattings.Count - 1; i >= 0; --i) { wsp.Worksheet.InsertAfter(slws.ConditionalFormattings[i].ToConditionalFormatting(), oxe); } } else { for (i = slws.ConditionalFormattings.Count - 1; i >= 0; --i) { wsp.Worksheet.PrependChild(slws.ConditionalFormattings[i].ToConditionalFormatting()); } } } #endregion #region DataValidations if (wsp.Worksheet.Elements<DataValidations>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<DataValidations>(); } if (slws.DataValidations.Count > 0) { DataValidations dvs = new DataValidations(); if (slws.DataValidationDisablePrompts) dvs.DisablePrompts = slws.DataValidationDisablePrompts; if (slws.DataValidationXWindow != null) dvs.XWindow = slws.DataValidationXWindow.Value; if (slws.DataValidationYWindow != null) dvs.YWindow = slws.DataValidationYWindow.Value; dvs.Count = (uint)slws.DataValidations.Count; foreach (SLDataValidation dv in slws.DataValidations) { dvs.Append(dv.ToDataValidation()); } bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(dvs, oxe); } else { wsp.Worksheet.PrependChild(dvs); } } #endregion #region Hyperlinks if (wsp.Worksheet.Elements<Hyperlinks>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<Hyperlinks>(); } if (slws.Hyperlinks.Count > 0) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations) { oxe = child; bFound = true; } } Hyperlinks hls = new Hyperlinks(); HyperlinkRelationship hlrel; foreach (SLHyperlink hl in slws.Hyperlinks) { if (hl.IsExternal && hl.IsNew) { hlrel = wsp.AddHyperlinkRelationship(new Uri(hl.HyperlinkUri, hl.HyperlinkUriKind), true); hl.Id = hlrel.Id; } hls.Append(hl.ToHyperlink()); } if (bFound) { wsp.Worksheet.InsertAfter(hls, oxe); } else { wsp.Worksheet.PrependChild(hls); } } #endregion #region PrintOptions if (wsp.Worksheet.Elements<PrintOptions>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<PrintOptions>(); } if (slws.PageSettings.HasPrintOptions) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(slws.PageSettings.ExportPrintOptions(), oxe); } else { wsp.Worksheet.PrependChild(slws.PageSettings.ExportPrintOptions()); } } #endregion #region PageMargins if (wsp.Worksheet.Elements<PageMargins>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<PageMargins>(); } if (slws.PageSettings.HasPageMargins) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(slws.PageSettings.ExportPageMargins(), oxe); } else { wsp.Worksheet.PrependChild(slws.PageSettings.ExportPageMargins()); } } #endregion #region PageSetup if (wsp.Worksheet.Elements<PageSetup>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<PageSetup>(); } if (slws.PageSettings.HasPageSetup) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(slws.PageSettings.ExportPageSetup(), oxe); } else { wsp.Worksheet.PrependChild(slws.PageSettings.ExportPageSetup()); } } #endregion #region HeaderFooter if (wsp.Worksheet.Elements<HeaderFooter>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<HeaderFooter>(); } if (slws.PageSettings.HasHeaderFooter) { bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(slws.PageSettings.ExportHeaderFooter(), oxe); } else { wsp.Worksheet.PrependChild(slws.PageSettings.ExportHeaderFooter()); } } #endregion #region RowBreaks if (wsp.Worksheet.Elements<RowBreaks>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<RowBreaks>(); } if (slws.RowBreaks.Count > 0) { List<int> bkkeys = slws.RowBreaks.Keys.ToList<int>(); bkkeys.Sort(); RowBreaks rowbk = new RowBreaks(); int bkmancount = 0; foreach (int bkindex in bkkeys) { if (slws.RowBreaks[bkindex].ManualPageBreak) ++bkmancount; rowbk.Append(slws.RowBreaks[bkindex].ToBreak()); } rowbk.Count = (uint)slws.RowBreaks.Count; rowbk.ManualBreakCount = (uint)bkmancount; bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup || child is HeaderFooter) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(rowbk, oxe); } else { wsp.Worksheet.PrependChild(rowbk); } } #endregion #region ColumnBreaks if (wsp.Worksheet.Elements<ColumnBreaks>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<ColumnBreaks>(); } if (slws.ColumnBreaks.Count > 0) { List<int> bkkeys = slws.ColumnBreaks.Keys.ToList<int>(); bkkeys.Sort(); ColumnBreaks colbk = new ColumnBreaks(); int bkmancount = 0; foreach (int bkindex in bkkeys) { if (slws.ColumnBreaks[bkindex].ManualPageBreak) ++bkmancount; colbk.Append(slws.ColumnBreaks[bkindex].ToBreak()); } colbk.Count = (uint)slws.ColumnBreaks.Count; colbk.ManualBreakCount = (uint)bkmancount; bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup || child is HeaderFooter || child is RowBreaks) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(colbk, oxe); } else { wsp.Worksheet.PrependChild(colbk); } } #endregion #region Drawing // these are "new" charts and pictures added if (slws.Charts.Count > 0 || slws.Pictures.Count > 0) { // if the length > 0, then we assume there's already an existing DrawingsPart if (slws.DrawingId.Length > 0) { WriteImageParts(wsp.DrawingsPart); } else { DrawingsPart dp = wsp.AddNewPart<DrawingsPart>(); dp.WorksheetDrawing = new Xdr.WorksheetDrawing(); dp.WorksheetDrawing.AddNamespaceDeclaration("xdr", SLConstants.NamespaceXdr); dp.WorksheetDrawing.AddNamespaceDeclaration("a", SLConstants.NamespaceA); DocumentFormat.OpenXml.Spreadsheet.Drawing drawing = new DocumentFormat.OpenXml.Spreadsheet.Drawing(); drawing.Id = wsp.GetIdOfPart(dp); WriteImageParts(dp); // NOTE: SmartTags is deprecated in Open XML SDK 2.5, so have to remove // from check below? bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup || child is HeaderFooter || child is RowBreaks || child is ColumnBreaks || child is CustomProperties || child is CellWatches || child is IgnoredErrors /*|| child is SmartTags*/) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(drawing, oxe); } else { wsp.Worksheet.PrependChild(drawing); } } } #endregion #region LegacyDrawing if (slws.Comments.Count > 0) { // we're going to do this only if there are no comments and VML already if (wsp.WorksheetCommentsPart == null && wsp.Worksheet.Elements<LegacyDrawing>().Count() == 0) { WorksheetCommentsPart wcp = wsp.AddNewPart<WorksheetCommentsPart>(); VmlDrawingPart vdp = wsp.AddNewPart<VmlDrawingPart>(); WriteCommentPart(wcp, vdp); LegacyDrawing ldrawing = new LegacyDrawing(); ldrawing.Id = wsp.GetIdOfPart(vdp); // NOTE: SmartTags is deprecated in Open XML SDK 2.5, so have to remove // from check below? bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup || child is HeaderFooter || child is RowBreaks || child is ColumnBreaks || child is CustomProperties || child is CellWatches || child is IgnoredErrors /*|| child is SmartTags*/ || child is DocumentFormat.OpenXml.Spreadsheet.Drawing) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(ldrawing, oxe); } else { wsp.Worksheet.PrependChild(ldrawing); } } } #endregion #region Picture if (wsp.Worksheet.Elements<Picture>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<Picture>(); } if (slws.BackgroundPictureId.Length > 0 || slws.BackgroundPictureDataIsInFile != null) { Picture pic = new Picture(); if (slws.BackgroundPictureId.Length > 0) { pic.Id = slws.BackgroundPictureId; } else if (slws.BackgroundPictureDataIsInFile != null) { ImagePart imgp = wsp.AddImagePart(slws.BackgroundPictureImagePartType); if (slws.BackgroundPictureDataIsInFile.Value) { using (FileStream fs = new FileStream(slws.BackgroundPictureFileName, FileMode.Open)) { imgp.FeedData(fs); } } else { using (MemoryStream ms = new MemoryStream(slws.BackgroundPictureByteData)) { imgp.FeedData(ms); } } pic.Id = wsp.GetIdOfPart(imgp); } // NOTE: SmartTags is deprecated in Open XML SDK 2.5, so have to remove // from check below? bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup || child is HeaderFooter || child is RowBreaks || child is ColumnBreaks || child is CustomProperties || child is CellWatches || child is IgnoredErrors /*|| child is SmartTags*/ || child is DocumentFormat.OpenXml.Spreadsheet.Drawing || child is LegacyDrawing || child is LegacyDrawingHeaderFooter) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(pic, oxe); } else { wsp.Worksheet.PrependChild(pic); } } #endregion #region Tables if (wsp.Worksheet.Elements<TableParts>().Count() > 0) { wsp.Worksheet.RemoveAllChildren<TableParts>(); } if (slws.Tables.Count > 0) { TableParts tps = new TableParts() { Count = (uint)slws.Tables.Count }; TableDefinitionPart tdp; string sRelID = string.Empty; foreach (SLTable t in slws.Tables) { if (t.IsNewTable) { if (t.RelationshipID.Length > 0) { // is a modified existing table tdp = (TableDefinitionPart)wsp.GetPartById(t.RelationshipID); tdp.Table = t.ToTable(); sRelID = t.RelationshipID; } else { // is a completely new table tdp = wsp.AddNewPart<TableDefinitionPart>(); tdp.Table = t.ToTable(); sRelID = wsp.GetIdOfPart(tdp); } } else { // if it's an existing table with no modifications, // don't need to do anything to the XML content. tdp = (TableDefinitionPart)wsp.GetPartById(t.RelationshipID); sRelID = t.RelationshipID; } tps.Append(new TablePart() { Id = sRelID }); } // NOTE: SmartTags is deprecated in Open XML SDK 2.5, so have to remove // from check below? bFound = false; oxe = wsp.Worksheet.FirstChild; foreach (var child in wsp.Worksheet.ChildElements) { // start with SheetData because it's a required child element if (child is SheetData || child is SheetCalculationProperties || child is SheetProtection || child is ProtectedRanges || child is Scenarios || child is AutoFilter || child is SortState || child is DataConsolidate || child is CustomSheetViews || child is MergeCells || child is PhoneticProperties || child is ConditionalFormatting || child is DataValidations || child is Hyperlinks || child is PrintOptions || child is PageMargins || child is PageSetup || child is HeaderFooter || child is RowBreaks || child is ColumnBreaks || child is CustomProperties || child is CellWatches || child is IgnoredErrors /*|| child is SmartTags*/ || child is DocumentFormat.OpenXml.Spreadsheet.Drawing || child is LegacyDrawing || child is LegacyDrawingHeaderFooter || child is Picture || child is OleObjects || child is Controls || child is WebPublishItems) { oxe = child; bFound = true; } } if (bFound) { wsp.Worksheet.InsertAfter(tps, oxe); } else { wsp.Worksheet.PrependChild(tps); } } #endregion #region 2010 Conditional formatting, Sparklines and possibly other extensions WorksheetExtensionList wsextlist; WorksheetExtension wsext; List<WorksheetExtension> listExtensions = new List<WorksheetExtension>(); slws.RefreshSparklineGroups(); if (wsp.Worksheet.Elements<WorksheetExtensionList>().Count() > 0) { wsextlist = wsp.Worksheet.Elements<WorksheetExtensionList>().First(); foreach (var wsextchild in wsextlist.ChildElements) { if (wsextchild is WorksheetExtension) { wsext = (WorksheetExtension)wsextchild; wsext.RemoveAllChildren<X14.ConditionalFormattings>(); wsext.RemoveAllChildren<X14.SparklineGroups>(); // there might be other extension types, like slicers (erhmahgerd...). if (wsext.ChildElements.Count > 0) { listExtensions.Add((WorksheetExtension)wsext.CloneNode(true)); } } } wsp.Worksheet.RemoveAllChildren<WorksheetExtensionList>(); } if (slws.ConditionalFormattings2010.Count > 0 || slws.SparklineGroups.Count > 0 || listExtensions.Count > 0) { wsextlist = new WorksheetExtensionList(); foreach (WorksheetExtension ext in listExtensions) { // be extra safe by cloning again to avoid pass-by-reference. Deeply. wsextlist.Append((WorksheetExtension)ext.CloneNode(true)); } if (slws.ConditionalFormattings2010.Count > 0) { // this is important! Apparently extensions are tied to a URI that Microsoft uses. wsext = new WorksheetExtension() { Uri = SLConstants.ConditionalFormattingExtensionUri }; wsext.AddNamespaceDeclaration("x14", SLConstants.NamespaceX14); X14.ConditionalFormattings cfs = new X14.ConditionalFormattings(); foreach (SLConditionalFormatting2010 cfr2010 in slws.ConditionalFormattings2010) { cfs.Append(cfr2010.ToConditionalFormatting()); } wsext.Append(cfs); wsextlist.Append(wsext); } if (slws.SparklineGroups.Count > 0) { // this is important! Apparently extensions are tied to a URI that Microsoft uses. wsext = new WorksheetExtension() { Uri = SLConstants.SparklineExtensionUri }; wsext.AddNamespaceDeclaration("x14", SLConstants.NamespaceX14); X14.SparklineGroups spkgrps = new X14.SparklineGroups(); spkgrps.AddNamespaceDeclaration("xm", SLConstants.NamespaceXm); foreach (SLSparklineGroup spkgrp in slws.SparklineGroups) { spkgrps.Append(spkgrp.ToSparklineGroup()); } wsext.Append(spkgrps); wsextlist.Append(wsext); } // WorksheetExtensionList is the very last element possible. // So we can just append it because everything else is in front. wsp.Worksheet.Append(wsextlist); } #endregion wsp.Worksheet.Save(); // end of writing for existing worksheet } else { // start of writing for new worksheet WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>(); gsSelectedWorksheetRelationshipID = wbp.GetIdOfPart(wsp); foreach (SLSheet s in slwb.Sheets) { if (s.Name.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase)) { s.Id = gsSelectedWorksheetRelationshipID; break; } } if (slws.ForceCustomRowColumnDimensionsSplitting) { slws.ToggleCustomRowColumnDimension(true); } List<OpenXmlAttribute> oxa; OpenXmlWriter oxw = OpenXmlWriter.Create(wsp); oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("xmlns:r", null, SLConstants.NamespaceRelationships)); if (slws.ConditionalFormattings2010.Count > 0 || slws.SparklineGroups.Count > 0) { oxa.Add(new OpenXmlAttribute("xmlns:x14", null, SLConstants.NamespaceX14)); oxa.Add(new OpenXmlAttribute("xmlns:xm", null, SLConstants.NamespaceXm)); oxa.Add(new OpenXmlAttribute("xmlns:mc", null, SLConstants.NamespaceMc)); oxa.Add(new OpenXmlAttribute("xmlns:x14ac", null, SLConstants.NamespaceX14ac)); oxa.Add(new OpenXmlAttribute("mc", "Ignorable", SLConstants.NamespaceMc, "x14ac")); oxw.WriteStartElement(new Worksheet(), oxa); } else { oxw.WriteStartElement(new Worksheet(), oxa); } if (slws.PageSettings.HasSheetProperties) { oxw.WriteElement(slws.PageSettings.SheetProperties.ToSheetProperties()); } oxw.WriteElement(new SheetDimension() { Reference = sDimensionCellRange }); if (slws.SheetViews.Count > 0) { oxw.WriteStartElement(new SheetViews()); foreach (SLSheetView sv in slws.SheetViews) { oxw.WriteElement(sv.ToSheetView()); } oxw.WriteEndElement(); } oxa = new List<OpenXmlAttribute>(); if (slws.SheetFormatProperties.BaseColumnWidth != null && slws.SheetFormatProperties.BaseColumnWidth.Value != 8) { oxa.Add(new OpenXmlAttribute("baseColWidth", null, slws.SheetFormatProperties.BaseColumnWidth.Value.ToString(CultureInfo.InvariantCulture))); } if (slws.SheetFormatProperties.HasDefaultColumnWidth) { oxa.Add(new OpenXmlAttribute("defaultColWidth", null, slws.SheetFormatProperties.DefaultColumnWidth.ToString(CultureInfo.InvariantCulture))); } oxa.Add(new OpenXmlAttribute("defaultRowHeight", null, slws.SheetFormatProperties.DefaultRowHeight.ToString("0.####", CultureInfo.InvariantCulture))); if (slws.SheetFormatProperties.CustomHeight != null && slws.SheetFormatProperties.CustomHeight.Value) { oxa.Add(new OpenXmlAttribute("customHeight", null, "1")); } if (slws.SheetFormatProperties.ZeroHeight != null && slws.SheetFormatProperties.ZeroHeight.Value) { oxa.Add(new OpenXmlAttribute("zeroHeight", null, "1")); } if (slws.SheetFormatProperties.ThickTop != null && slws.SheetFormatProperties.ThickTop.Value) { oxa.Add(new OpenXmlAttribute("thickTop", null, "1")); } if (slws.SheetFormatProperties.ThickBottom != null && slws.SheetFormatProperties.ThickBottom.Value) { oxa.Add(new OpenXmlAttribute("thickBottom", null, "1")); } if (slws.SheetFormatProperties.OutlineLevelRow != null && slws.SheetFormatProperties.OutlineLevelRow.Value > 0) { oxa.Add(new OpenXmlAttribute("outlineLevelRow", null, slws.SheetFormatProperties.OutlineLevelRow.Value.ToString(CultureInfo.InvariantCulture))); } if (slws.SheetFormatProperties.OutlineLevelColumn != null && slws.SheetFormatProperties.OutlineLevelColumn.Value > 0) { oxa.Add(new OpenXmlAttribute("outlineLevelCol", null, slws.SheetFormatProperties.OutlineLevelColumn.Value.ToString(CultureInfo.InvariantCulture))); } oxw.WriteStartElement(new SheetFormatProperties(), oxa); oxw.WriteEndElement(); #region Filling Columns if (slws.ColumnProperties.Count > 0) { oxw.WriteStartElement(new Columns()); int iPreviousColumnIndex = listColumnIndex[0]; int iCurrentColumnIndex = iPreviousColumnIndex; string sCollectiveColumnData = string.Empty; string sCurrentColumnData = string.Empty; int colmin, colmax; colmin = colmax = iCurrentColumnIndex; cp = slws.ColumnProperties[iCurrentColumnIndex]; sCollectiveColumnData = cp.ToHash(); oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("min", null, colmin.ToString(CultureInfo.InvariantCulture))); // max is left to the end because we're calculating it //oxa.Add(new OpenXmlAttribute("max", null, colmax.ToString(CultureInfo.InvariantCulture))); if (cp.HasWidth) { oxa.Add(new OpenXmlAttribute("width", null, cp.Width.ToString(CultureInfo.InvariantCulture))); oxa.Add(new OpenXmlAttribute("customWidth", null, "1")); } else { oxa.Add(new OpenXmlAttribute("width", null, slws.SheetFormatProperties.DefaultColumnWidth.ToString(CultureInfo.InvariantCulture))); } if (cp.StyleIndex > 0) oxa.Add(new OpenXmlAttribute("style", null, cp.StyleIndex.ToString(CultureInfo.InvariantCulture))); if (cp.Hidden != false) oxa.Add(new OpenXmlAttribute("hidden", null, "1")); if (cp.BestFit != false) oxa.Add(new OpenXmlAttribute("bestFit", null, "1")); if (cp.Phonetic != false) oxa.Add(new OpenXmlAttribute("phonetic", null, "1")); if (cp.OutlineLevel > 0) oxa.Add(new OpenXmlAttribute("outlineLevel", null, cp.OutlineLevel.ToString(CultureInfo.InvariantCulture))); if (cp.Collapsed != false) oxa.Add(new OpenXmlAttribute("collapsed", null, "1")); for (i = 1; i < listColumnIndex.Count; ++i) { iPreviousColumnIndex = iCurrentColumnIndex; iCurrentColumnIndex = listColumnIndex[i]; cp = slws.ColumnProperties[iCurrentColumnIndex]; sCurrentColumnData = cp.ToHash(); if ((iCurrentColumnIndex != (iPreviousColumnIndex + 1)) || (sCollectiveColumnData != sCurrentColumnData)) { oxa.Add(new OpenXmlAttribute("max", null, colmax.ToString(CultureInfo.InvariantCulture))); oxw.WriteStartElement(new Column(), oxa); oxw.WriteEndElement(); colmin = iCurrentColumnIndex; colmax = iCurrentColumnIndex; sCollectiveColumnData = sCurrentColumnData; oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("min", null, colmin.ToString(CultureInfo.InvariantCulture))); if (cp.HasWidth) { oxa.Add(new OpenXmlAttribute("width", null, cp.Width.ToString(CultureInfo.InvariantCulture))); oxa.Add(new OpenXmlAttribute("customWidth", null, "1")); } else { oxa.Add(new OpenXmlAttribute("width", null, slws.SheetFormatProperties.DefaultColumnWidth.ToString(CultureInfo.InvariantCulture))); } if (cp.StyleIndex > 0) oxa.Add(new OpenXmlAttribute("style", null, cp.StyleIndex.ToString(CultureInfo.InvariantCulture))); if (cp.Hidden != false) oxa.Add(new OpenXmlAttribute("hidden", null, "1")); if (cp.BestFit != false) oxa.Add(new OpenXmlAttribute("bestFit", null, "1")); if (cp.Phonetic != false) oxa.Add(new OpenXmlAttribute("phonetic", null, "1")); if (cp.OutlineLevel > 0) oxa.Add(new OpenXmlAttribute("outlineLevel", null, cp.OutlineLevel.ToString(CultureInfo.InvariantCulture))); if (cp.Collapsed != false) oxa.Add(new OpenXmlAttribute("collapsed", null, "1")); } else { colmax = iCurrentColumnIndex; } } // there's always a "leftover" column oxa.Add(new OpenXmlAttribute("max", null, colmax.ToString(CultureInfo.InvariantCulture))); oxw.WriteStartElement(new Column(), oxa); oxw.WriteEndElement(); oxw.WriteEndElement(); } #endregion oxw.WriteStartElement(new SheetData()); iCellDataKey = 0; for (iRowKey = 0; iRowKey < listRowIndex.Count; ++iRowKey) { iRowIndex = listRowIndex[iRowKey]; oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("r", null, iRowIndex.ToString(CultureInfo.InvariantCulture))); if (slws.RowProperties.ContainsKey(iRowIndex)) { rp = slws.RowProperties[iRowIndex]; if (rp.StyleIndex > 0) { oxa.Add(new OpenXmlAttribute("s", null, rp.StyleIndex.ToString(CultureInfo.InvariantCulture))); oxa.Add(new OpenXmlAttribute("customFormat", null, "1")); } if (rp.HasHeight) { oxa.Add(new OpenXmlAttribute("ht", null, rp.Height.ToString(CultureInfo.InvariantCulture))); } if (rp.Hidden != false) { oxa.Add(new OpenXmlAttribute("hidden", null, "1")); } if (rp.CustomHeight) { oxa.Add(new OpenXmlAttribute("customHeight", null, "1")); } if (rp.OutlineLevel > 0) { oxa.Add(new OpenXmlAttribute("outlineLevel", null, rp.OutlineLevel.ToString(CultureInfo.InvariantCulture))); } if (rp.Collapsed != false) { oxa.Add(new OpenXmlAttribute("collapsed", null, "1")); } if (rp.ThickTop != false) { oxa.Add(new OpenXmlAttribute("thickTop", null, "1")); } if (rp.ThickBottom != false) { oxa.Add(new OpenXmlAttribute("thickBot", null, "1")); } if (rp.ShowPhonetic != false) { oxa.Add(new OpenXmlAttribute("ph", null, "1")); } } oxw.WriteStartElement(new Row(), oxa); while (iCellDataKey < listCellRefKeys.Count) { pt = listCellRefKeys[iCellDataKey]; if (pt.RowIndex == iRowIndex) { c = slws.Cells[pt]; oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("r", null, SLTool.ToCellReference(pt.RowIndex, pt.ColumnIndex))); if (c.StyleIndex > 0) { oxa.Add(new OpenXmlAttribute("s", null, c.StyleIndex.ToString(CultureInfo.InvariantCulture))); } // number type is default switch (c.DataType) { case CellValues.Boolean: oxa.Add(new OpenXmlAttribute("t", null, "b")); break; case CellValues.Date: oxa.Add(new OpenXmlAttribute("t", null, "d")); break; case CellValues.Error: oxa.Add(new OpenXmlAttribute("t", null, "e")); break; case CellValues.InlineString: oxa.Add(new OpenXmlAttribute("t", null, "inlineStr")); break; case CellValues.SharedString: oxa.Add(new OpenXmlAttribute("t", null, "s")); break; case CellValues.String: oxa.Add(new OpenXmlAttribute("t", null, "str")); break; } if (c.CellMetaIndex > 0) { oxa.Add(new OpenXmlAttribute("cm", null, c.CellMetaIndex.ToString(CultureInfo.InvariantCulture))); } if (c.ValueMetaIndex > 0) { oxa.Add(new OpenXmlAttribute("vm", null, c.ValueMetaIndex.ToString(CultureInfo.InvariantCulture))); } if (c.ShowPhonetic != false) { oxa.Add(new OpenXmlAttribute("ph", null, "1")); } oxw.WriteStartElement(new Cell(), oxa); if (c.CellFormula != null) { oxw.WriteElement(c.CellFormula.ToCellFormula()); } if (c.CellText != null) { if (c.CellText.Length > 0) { if (c.ToPreserveSpace) { oxw.WriteElement(new CellValue(c.CellText) { Space = SpaceProcessingModeValues.Preserve }); } else { oxw.WriteElement(new CellValue(c.CellText)); } } } else { if (c.DataType == CellValues.Number) { oxw.WriteElement(new CellValue(c.NumericValue.ToString(CultureInfo.InvariantCulture))); } else if (c.DataType == CellValues.SharedString) { oxw.WriteElement(new CellValue(c.NumericValue.ToString("f0", CultureInfo.InvariantCulture))); } else if (c.DataType == CellValues.Boolean) { if (c.NumericValue > 0.5) oxw.WriteElement(new CellValue("1")); else oxw.WriteElement(new CellValue("0")); } } oxw.WriteEndElement(); ++iCellDataKey; } else { break; } } oxw.WriteEndElement(); } oxw.WriteEndElement(); #region Sheet protection if (slws.HasSheetProtection) { oxw.WriteElement(slws.SheetProtection.ToSheetProtection()); } #endregion #region AutoFilter if (slws.HasAutoFilter) { oxw.WriteElement(slws.AutoFilter.ToAutoFilter()); } #endregion #region Filling merge cells if (slws.MergeCells.Count > 0) { oxw.WriteStartElement(new MergeCells() { Count = (uint)slws.MergeCells.Count }); for (i = 0; i < slws.MergeCells.Count; ++i) { oxw.WriteElement(slws.MergeCells[i].ToMergeCell()); } oxw.WriteEndElement(); } #endregion #region Conditional Formatting if (slws.ConditionalFormattings.Count > 0) { for (i = 0; i < slws.ConditionalFormattings.Count; ++i) { oxw.WriteElement(slws.ConditionalFormattings[i].ToConditionalFormatting()); } } #endregion #region DataValidations if (slws.DataValidations.Count > 0) { DataValidations dvs = new DataValidations(); if (slws.DataValidationDisablePrompts) dvs.DisablePrompts = slws.DataValidationDisablePrompts; if (slws.DataValidationXWindow != null) dvs.XWindow = slws.DataValidationXWindow.Value; if (slws.DataValidationYWindow != null) dvs.YWindow = slws.DataValidationYWindow.Value; dvs.Count = (uint)slws.DataValidations.Count; foreach (SLDataValidation dv in slws.DataValidations) { dvs.Append(dv.ToDataValidation()); } oxw.WriteElement(dvs); } #endregion #region Hyperlinks if (slws.Hyperlinks.Count > 0) { Hyperlinks hls = new Hyperlinks(); HyperlinkRelationship hlrel; foreach (SLHyperlink hl in slws.Hyperlinks) { if (hl.IsExternal && hl.IsNew) { hlrel = wsp.AddHyperlinkRelationship(new Uri(hl.HyperlinkUri, hl.HyperlinkUriKind), true); hl.Id = hlrel.Id; } hls.Append(hl.ToHyperlink()); } oxw.WriteElement(hls); } #endregion #region PrintOptions if (slws.PageSettings.HasPrintOptions) { oxw.WriteElement(slws.PageSettings.ExportPrintOptions()); } #endregion #region PageMargins if (slws.PageSettings.HasPageMargins) { oxw.WriteElement(slws.PageSettings.ExportPageMargins()); } #endregion #region PageSetup if (slws.PageSettings.HasPageSetup) { oxw.WriteElement(slws.PageSettings.ExportPageSetup()); } #endregion #region HeaderFooter if (slws.PageSettings.HasHeaderFooter) { oxw.WriteElement(slws.PageSettings.ExportHeaderFooter()); } #endregion #region RowBreaks if (slws.RowBreaks.Count > 0) { List<int> bkkeys = slws.RowBreaks.Keys.ToList<int>(); bkkeys.Sort(); // if it's a new worksheet, then all breaks are considered manual oxw.WriteStartElement(new RowBreaks() { Count = (uint)slws.RowBreaks.Count, ManualBreakCount = (uint)slws.RowBreaks.Count }); foreach (int bkindex in bkkeys) { oxw.WriteElement(slws.RowBreaks[bkindex].ToBreak()); } oxw.WriteEndElement(); } #endregion #region ColumnBreaks if (slws.ColumnBreaks.Count > 0) { List<int> bkkeys = slws.ColumnBreaks.Keys.ToList<int>(); bkkeys.Sort(); // if it's a new worksheet, then all breaks are considered manual oxw.WriteStartElement(new ColumnBreaks() { Count = (uint)slws.ColumnBreaks.Count, ManualBreakCount = (uint)slws.ColumnBreaks.Count }); foreach (int bkindex in bkkeys) { oxw.WriteElement(slws.ColumnBreaks[bkindex].ToBreak()); } oxw.WriteEndElement(); } #endregion #region Drawing // these are "new" charts and pictures added if (slws.Charts.Count > 0 || slws.Pictures.Count > 0) { DrawingsPart dp = wsp.AddNewPart<DrawingsPart>(); dp.WorksheetDrawing = new Xdr.WorksheetDrawing(); dp.WorksheetDrawing.AddNamespaceDeclaration("xdr", SLConstants.NamespaceXdr); dp.WorksheetDrawing.AddNamespaceDeclaration("a", SLConstants.NamespaceA); oxw.WriteElement(new DocumentFormat.OpenXml.Spreadsheet.Drawing() { Id = wsp.GetIdOfPart(dp) }); WriteImageParts(dp); } #endregion #region LegacyDrawing // these are "new" comments added if (slws.Comments.Count > 0) { WorksheetCommentsPart wcp = wsp.AddNewPart<WorksheetCommentsPart>(); VmlDrawingPart vdp = wsp.AddNewPart<VmlDrawingPart>(); WriteCommentPart(wcp, vdp); oxw.WriteElement(new LegacyDrawing() { Id = wsp.GetIdOfPart(vdp) }); } #endregion #region Picture if (slws.BackgroundPictureDataIsInFile != null) { ImagePart imgp = wsp.AddImagePart(slws.BackgroundPictureImagePartType); if (slws.BackgroundPictureDataIsInFile.Value) { using (FileStream fs = new FileStream(slws.BackgroundPictureFileName, FileMode.Open)) { imgp.FeedData(fs); } } else { using (MemoryStream ms = new MemoryStream(slws.BackgroundPictureByteData)) { imgp.FeedData(ms); } } oxw.WriteElement(new Picture() { Id = wsp.GetIdOfPart(imgp) }); } #endregion if (slws.Tables.Count > 0) { // If it's a new worksheet, ALL tables are new tables... oxw.WriteStartElement(new TableParts() { Count = (uint)slws.Tables.Count }); TableDefinitionPart tdp; foreach (SLTable t in slws.Tables) { tdp = wsp.AddNewPart<TableDefinitionPart>(); tdp.Table = t.ToTable(); oxw.WriteElement(new TablePart() { Id = wsp.GetIdOfPart(tdp) }); } oxw.WriteEndElement(); } #region 2010 Conditional formatting, Sparklines and possibly other extensions slws.RefreshSparklineGroups(); if (slws.ConditionalFormattings2010.Count > 0 || slws.SparklineGroups.Count > 0) { oxw.WriteStartElement(new WorksheetExtensionList()); if (slws.ConditionalFormattings2010.Count > 0) { oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("xmlns:x14", null, SLConstants.NamespaceX14)); // this is important! Apparently extensions are tied to a URI that Microsoft uses. oxa.Add(new OpenXmlAttribute("uri", null, SLConstants.ConditionalFormattingExtensionUri)); oxw.WriteStartElement(new WorksheetExtension(), oxa); oxw.WriteStartElement(new X14.ConditionalFormattings()); foreach (SLConditionalFormatting2010 cf2010 in slws.ConditionalFormattings2010) { oxw.WriteElement(cf2010.ToConditionalFormatting()); } oxw.WriteEndElement(); oxw.WriteEndElement(); } if (slws.SparklineGroups.Count > 0) { oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("xmlns:x14", null, SLConstants.NamespaceX14)); // this is important! Apparently extensions are tied to a URI that Microsoft uses. oxa.Add(new OpenXmlAttribute("uri", null, SLConstants.SparklineExtensionUri)); oxw.WriteStartElement(new WorksheetExtension(), oxa); oxa = new List<OpenXmlAttribute>(); oxa.Add(new OpenXmlAttribute("xmlns:xm", null, SLConstants.NamespaceXm)); oxw.WriteStartElement(new X14.SparklineGroups(), oxa); foreach (SLSparklineGroup spkgrp in slws.SparklineGroups) { oxw.WriteElement(spkgrp.ToSparklineGroup()); } oxw.WriteEndElement(); oxw.WriteEndElement(); } oxw.WriteEndElement(); } #endregion oxw.WriteEndElement(); oxw.Dispose(); // end of writing for new worksheet } }
private void GenerateWorksheetAContentSubsector(WorksheetPart worksheetPart, int SheetOrdinal) { MergeCells mergeCells = new MergeCells(); Row row = new Row(); Cell cell = new Cell(); Hyperlinks hyperlinks = new Hyperlinks(); string Id = XlsxBase.sheetNameAndIDList[SheetOrdinal].SheetID; XlsxBase.CurrentColumn = 0; XlsxBase.CurrentRow = 0; XlsxBase.CurrentColumnProp = 0; Worksheet worksheet = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } }; worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); SheetViews sheetViews = new SheetViews(); SheetView sheetView = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U }; Selection selection = new Selection() { ActiveCell = "A1", SequenceOfReferences = new ListValue <StringValue>() { InnerText = "A1" } }; sheetView.Append(selection); sheetViews.Append(sheetView); SheetFormatProperties sheetFormatProperties = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D }; Columns columns = new Columns(); List <double?> columnWidthList = new List <double?>() { 30D }; foreach (double?width in columnWidthList) { Column colum = XlsxBase.AddColumnProp(columns, width); } SheetData sheetData = new SheetData(); BaseEnumService baseEnumService = new BaseEnumService(_TaskRunnerBaseService._BWObj.appTaskModel.Language); TVItemService tvItemService = new TVItemService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User); TVItemStatService tvItemStatService = new TVItemStatService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User); TVItemModel tvItemModelSubsector = tvItemService.GetTVItemModelWithTVItemIDDB(_TaskRunnerBaseService._BWObj.appTaskModel.TVItemID); List <TVItemModel> tvItemModelMunicipalityList = tvItemService.GetChildrenTVItemModelListWithTVItemIDAndTVTypeDB(tvItemModelSubsector.TVItemID, TVTypeEnum.Municipality); foreach (TVItemModel tvItemModelMunicipality in tvItemModelMunicipalityList) { //tvItemStatService.SetTVItemStatForTVItemIDAndParentsTVItemID(tvItemModelMunicipality.TVItemID); XlsxBase.CurrentRowHeight = 24D; XlsxBase.CurrentFontSize = 18; XlsxBase.CurrentBorderStyleValue = BorderStyleValues.Thick; XlsxBase.CurrentBottomBorder = true; XlsxBase.CurrentBorderColor = System.Drawing.Color.Green; XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center; row = XlsxBase.AddRow(); string URL = _TaskRunnerBaseService.GetUrlFromTVItem(tvItemModelMunicipality); XlsxBase.CurrentFontColor = System.Drawing.Color.Blue; cell = XlsxBase.AddCellHyperlink(hyperlinks, row, tvItemModelMunicipality.TVText, URL); XlsxBase.CurrentFontColor = null; //cell = XlsxBase.AddCellString(row, tvItemModel.TVText); cell = XlsxBase.AddCellString(row, null); sheetData.Append(row); MergeCell mergeCell = new MergeCell() { Reference = "A" + XlsxBase.CurrentRow.ToString() + ":B" + XlsxBase.CurrentRow.ToString() }; mergeCells.Append(mergeCell); XlsxBase.CurrentRowHeight = 16D; XlsxBase.CurrentFontSize = 12; XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center; List <TVItemStatModel> tvItemStatModelList = tvItemStatService.GetTVItemStatModelListWithTVItemIDDB(tvItemModelMunicipality.TVItemID); int count = 0; foreach (TVItemStatModel tvItemStatModel in tvItemStatModelList) { count += 1; row = XlsxBase.AddRow(); if (count % 5 == 0) { XlsxBase.CurrentBorderStyleValue = BorderStyleValues.Thin; XlsxBase.CurrentBottomBorder = true; } else { XlsxBase.CurrentBorderStyleValue = null; XlsxBase.CurrentBottomBorder = false; } XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Right; cell = XlsxBase.AddCellString(row, baseEnumService.GetEnumText_TVTypeEnum(tvItemStatModel.TVType)); XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center; cell = XlsxBase.AddCellNumber(row, tvItemStatModel.ChildCount.ToString()); sheetData.Append(row); } XlsxBase.CurrentBorderStyleValue = null; XlsxBase.CurrentBottomBorder = false; for (int i = 0; i < 2; i++) { row = XlsxBase.AddRow(); cell = XlsxBase.AddCellString(row, null); sheetData.Append(row); } } PageMargins pageMargins = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D }; PageSetup pageSetup = new PageSetup() { Orientation = OrientationValues.Portrait, Id = "rId" + SheetOrdinal.ToString() }; worksheet.Append(sheetViews); worksheet.Append(sheetFormatProperties); if (columns.ChildElements.Count > 0) { worksheet.Append(columns); } worksheet.Append(sheetData); mergeCells.Count = (UInt32Value)((UInt32)mergeCells.ChildElements.Count); if (mergeCells.ChildElements.Count > 0) { worksheet.Append(mergeCells); } if (XlsxBase.UsedHyperlinkList.Count > 0) { worksheet.Append(hyperlinks); } worksheet.Append(pageMargins); worksheet.Append(pageSetup); worksheetPart.Worksheet = worksheet; }
private void GenerateWorksheetAContentSubsectorPollutionSourceFieldSheet(WorksheetPart worksheetPart, Workbook workbook, string SheetName, int SheetOrdinal, bool ActivePollutionSource) { BaseEnumService baseEnumService = new BaseEnumService(_TaskRunnerBaseService._BWObj.appTaskModel.Language); TVItemService tvItemService = new TVItemService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User); PolSourceSiteService polSourceSiteService = new PolSourceSiteService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User); //PolSourceObservationService polSourceObservationService = new PolSourceObservationService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User); //MWQMSubsectorService mwqmSubsectorService = new MWQMSubsectorService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User); MergeCells mergeCells = new MergeCells(); Row row = new Row(); Cell cell = new Cell(); Hyperlinks hyperlinks = new Hyperlinks(); string Id = XlsxBase.sheetNameAndIDList[SheetOrdinal].SheetID; XlsxBase.CurrentColumn = 0; XlsxBase.CurrentRow = 0; XlsxBase.CurrentColumnProp = 0; Worksheet worksheet = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } }; worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); SheetViews sheetViews = new SheetViews(); SheetView sheetView = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U }; Selection selection = new Selection() { ActiveCell = "A1", SequenceOfReferences = new ListValue <StringValue>() { InnerText = "A1" } }; sheetView.Append(selection); sheetViews.Append(sheetView); SheetFormatProperties sheetFormatProperties = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D }; Columns columns = new Columns(); List <double?> columnWidthList = new List <double?>() { 7D, 10D, 90D, 12D, 12D, 10D, 14D, 20D }; foreach (double?width in columnWidthList) { Column colum = XlsxBase.AddColumnProp(columns, width); } SheetData sheetData = new SheetData(); TVItemModel tvItemModelSubsector = tvItemService.GetTVItemModelWithTVItemIDDB(_TaskRunnerBaseService._BWObj.appTaskModel.TVItemID); //MWQMSubsectorModel mwqmSubsectorModel = mwqmSubsectorService.GetMWQMSubsectorModelWithMWQMSubsectorTVItemIDDB(_TaskRunnerBaseService._BWObj.appTaskModel.TVItemID); XlsxBase.CurrentRowHeight = 28D; XlsxBase.CurrentFontSize = 24; XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center; XlsxBase.CurrentBorderStyleValue = BorderStyleValues.Thin; XlsxBase.CurrentBottomBorder = true; row = XlsxBase.AddRow(); string URL = _TaskRunnerBaseService.GetUrlFromTVItem(tvItemModelSubsector); XlsxBase.CurrentFontColor = System.Drawing.Color.Blue; cell = XlsxBase.AddCellHyperlink(hyperlinks, row, tvItemModelSubsector.TVText, URL); XlsxBase.CurrentFontColor = null; //cell = XlsxBase.AddCellString(row, tvItemModel.TVText); cell = XlsxBase.AddCellString(row, null); sheetData.Append(row); MergeCell mergeCell = new MergeCell() { Reference = "A" + XlsxBase.CurrentRow.ToString() + ":H" + XlsxBase.CurrentRow.ToString() }; mergeCells.Append(mergeCell); List <TVItemModel> tvItemModelPolSourceList = tvItemService.GetChildrenTVItemModelListWithTVItemIDAndTVTypeDB(_TaskRunnerBaseService._BWObj.appTaskModel.TVItemID, TVTypeEnum.PolSourceSite); XlsxBase.CurrentRowHeight = 24D; XlsxBase.CurrentFontSize = 18; XlsxBase.CurrentBorderStyleValue = BorderStyleValues.Thin; XlsxBase.CurrentBottomBorder = true; XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center; row = XlsxBase.AddRow(); XlsxBase.CurrentFontColor = null; cell = XlsxBase.AddCellString(row, "Site"); cell = XlsxBase.AddCellString(row, "Type"); cell = XlsxBase.AddCellString(row, "Observation"); cell = XlsxBase.AddCellString(row, "Lat"); cell = XlsxBase.AddCellString(row, "Lng"); cell = XlsxBase.AddCellString(row, "Active"); cell = XlsxBase.AddCellString(row, "Update"); cell = XlsxBase.AddCellString(row, "Civic Address"); sheetData.Append(row); XlsxBase.CurrentRowHeight = 160D; XlsxBase.CurrentFontSize = 12; XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Left; List <PolSourceSiteModel> polSourceSiteModelList = polSourceSiteService.GetPolSourceSiteModelListWithSubsectorTVItemIDDB(tvItemModelSubsector.TVItemID); int countPolSourceSite = 0; foreach (PolSourceSiteModel polSourceSiteModel in polSourceSiteModelList.OrderBy(c => c.Site).ToList()) { bool IsActive = (from c in tvItemModelPolSourceList where c.TVItemID == polSourceSiteModel.PolSourceSiteTVItemID select c.IsActive).FirstOrDefault(); if (ActivePollutionSource != IsActive) { continue; } countPolSourceSite += 1; PolSourceObservationModel polSourceObservationModel = polSourceSiteService._PolSourceObservationService.GetPolSourceObservationModelLatestWithPolSourceSiteTVItemIDDB(polSourceSiteModel.PolSourceSiteTVItemID); List <MapInfoPointModel> mapInfoPointModelList = polSourceSiteService._MapInfoService._MapInfoPointService.GetMapInfoPointModelListWithTVItemIDAndTVTypeAndMapInfoDrawTypeDB(polSourceSiteModel.PolSourceSiteTVItemID, TVTypeEnum.PolSourceSite, MapInfoDrawTypeEnum.Point); row = XlsxBase.AddRow(); if (countPolSourceSite % 5 == 0) { XlsxBase.CurrentBorderStyleValue = BorderStyleValues.Thin; XlsxBase.CurrentBottomBorder = true; } else { XlsxBase.CurrentBorderStyleValue = null; XlsxBase.CurrentBottomBorder = false; } XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center; cell = XlsxBase.AddCellString(row, polSourceSiteModel.Site.ToString()); if (polSourceObservationModel != null) { List <PolSourceObservationIssueModel> polSourceObservationIssueModelList = polSourceSiteService._PolSourceObservationService._PolSourceObservationIssueService.GetPolSourceObservationIssueModelListWithPolSourceObservationIDDB(polSourceObservationModel.PolSourceObservationID); string SelectedObservation = "Selected: \r\n"; int PolSourceObsInfoInt = 0; foreach (PolSourceObservationIssueModel polSourceObservationIssueModel in polSourceObservationIssueModelList) { List <int> observationInfoList = polSourceObservationIssueModel.ObservationInfo.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList().Select(c => int.Parse(c)).ToList(); PolSourceObsInfoInt = observationInfoList.Where(c => (c > 10500 && c < 10599) || (c > 15200 && c < 15299)).FirstOrDefault(); foreach (PolSourceObsInfoEnum polSourceObsInfo in polSourceObservationIssueModel.PolSourceObsInfoList) { SelectedObservation += baseEnumService.GetEnumText_PolSourceObsInfoReportEnum(polSourceObsInfo); } SelectedObservation += "\r\n\r\n"; } if (PolSourceObsInfoInt > 0) { cell = XlsxBase.AddCellString(row, baseEnumService.GetEnumText_PolSourceObsInfoEnum((PolSourceObsInfoEnum)PolSourceObsInfoInt)); } else { cell = XlsxBase.AddCellString(row, "Empty"); } } XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Left; XlsxBase.WrapText = true; if (polSourceObservationModel != null) { List <PolSourceObservationIssueModel> polSourceObservationIssueModelList = polSourceSiteService._PolSourceObservationService._PolSourceObservationIssueService.GetPolSourceObservationIssueModelListWithPolSourceObservationIDDB(polSourceObservationModel.PolSourceObservationID); string SelectedObservation = "Selected: \r\n"; int PolSourceObsInfoInt = 0; foreach (PolSourceObservationIssueModel polSourceObservationIssueModel in polSourceObservationIssueModelList) { List <int> observationInfoList = polSourceObservationIssueModel.ObservationInfo.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList().Select(c => int.Parse(c)).ToList(); foreach (PolSourceObsInfoEnum polSourceObsInfo in polSourceObservationIssueModel.PolSourceObsInfoList) { SelectedObservation += baseEnumService.GetEnumText_PolSourceObsInfoReportEnum(polSourceObsInfo); } SelectedObservation += "\r\n\r\n"; } if (PolSourceObsInfoInt > 0) { cell = XlsxBase.AddCellString(row, "Written: \r\n" + (string.IsNullOrWhiteSpace(polSourceObservationModel.Observation_ToBeDeleted) ? "" : polSourceObservationModel.Observation_ToBeDeleted.ToString()) + "\r\n\r\n" + SelectedObservation); } else { cell = XlsxBase.AddCellString(row, "Written: \r\n\r\n" + SelectedObservation); } } XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center; Alignment alignment1 = new Alignment() { WrapText = true }; if (mapInfoPointModelList.Count > 0) { cell = XlsxBase.AddCellString(row, mapInfoPointModelList[0].Lat.ToString("F5")); cell = XlsxBase.AddCellString(row, mapInfoPointModelList[0].Lng.ToString("F5")); } else { cell = XlsxBase.AddCellString(row, ""); cell = XlsxBase.AddCellString(row, ""); } TVItemModel tvItemModelPolSource = tvItemService.GetTVItemModelWithTVItemIDDB(polSourceSiteModel.PolSourceSiteTVItemID); cell = XlsxBase.AddCellString(row, tvItemModelPolSource.IsActive.ToString()); cell = XlsxBase.AddCellString(row, polSourceObservationModel.ObservationDate_Local.ToString("yyyy MMM dd")); cell = XlsxBase.AddCellString(row, "empty"); sheetData.Append(row); } if (countPolSourceSite > 0) { DefinedNames definedNames1 = new DefinedNames(); DefinedName definedName1 = new DefinedName() { Name = "_xlnm._FilterDatabase", LocalSheetId = (UInt32Value)0U, Hidden = true }; definedName1.Text = "'" + SheetName + "'" + "!$A$2:$H$" + (countPolSourceSite + 2).ToString(); definedNames1.Append(definedName1); workbook.Append(definedNames1); AutoFilter autoFilter = new AutoFilter() { Reference = "a2:B" + (countPolSourceSite + 2).ToString() }; worksheet.Append(autoFilter); } PageMargins pageMargins = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D }; PageSetup pageSetup = new PageSetup() { Orientation = OrientationValues.Portrait, Id = "rId" + SheetOrdinal.ToString() }; worksheet.Append(sheetViews); worksheet.Append(sheetFormatProperties); if (columns.ChildElements.Count > 0) { worksheet.Append(columns); } worksheet.Append(sheetData); mergeCells.Count = (UInt32Value)((UInt32)mergeCells.ChildElements.Count); if (mergeCells.ChildElements.Count > 0) { worksheet.Append(mergeCells); } if (XlsxBase.UsedHyperlinkList.Count > 0) { worksheet.Append(hyperlinks); } worksheet.Append(pageMargins); worksheet.Append(pageSetup); worksheetPart.Worksheet = worksheet; }
/// <summary> /// Создать задачу на обработку конфликтов синхронизации контрагентов. /// </summary> /// <param name="box">Абонентский ящик.</param> /// <param name="party">Организация из сервиса обмена.</param> /// <param name="parties">Список конфликтных контрагентов.</param> /// <returns>Задача на обработку конфликтов синхронизации контрагентов.</returns> public static ICounterpartyConflictProcessingTask Create(IBusinessUnitBox box, NpoComputer.DCX.Common.Organization party, List <Parties.ICounterparty> parties) { var task = CounterpartyConflictProcessingTasks.Create(); var dateWithUTC = Sungero.Docflow.PublicFunctions.Module.GetDateWithUTCLabel(Calendar.Now); var subject = CounterpartyConflictProcessingTasks.Resources.ConflictTaskSubjectFormat(box.BusinessUnit.Name, box.ExchangeService.Name, dateWithUTC); task.Subject = Exchange.PublicFunctions.Module.CutText(subject, task.Info.Properties.Subject.Length); task.ThreadSubject = CounterpartyConflictProcessingTasks.Resources.ConflictTaskThreadSubject; task.Assignee = box.Responsible; task.MaxDeadline = Calendar.Today.AddWorkingDays(task.Assignee, 2); if (parties.All(p => Parties.CompanyBases.Is(p) && Equals(Parties.CompanyBases.As(p).TRRC, party.Kpp)) || parties.All(p => !Parties.CompanyBases.Is(p) && string.IsNullOrWhiteSpace(party.Kpp))) { task.ActiveText = CounterpartyConflictProcessingTasks.Resources.ConflictTaskMany; foreach (var attach in parties) { task.ActiveText += string.Format("{0}{1}{2}", Environment.NewLine, Constants.BusinessUnitBox.Delimiter, Hyperlinks.Get(attach)); } } else { task.ActiveText = CounterpartyConflictProcessingTasks.Resources.ConflictTaskSingleFormat(party.Name, party.Inn, party.Kpp); if (!string.IsNullOrWhiteSpace(party.Ogrn)) { task.ActiveText += CounterpartyConflictProcessingTasks.Resources.ConflictTaskSingleOgrnFormat(Environment.NewLine, party.Ogrn); } } foreach (var attach in parties) { task.Attachments.Add(attach); } task.Save(); return(task); }
public override StateView GetDocumentSummary() { var documentSummary = StateView.Create(); var documentBlock = documentSummary.AddBlock(); // Краткое имя документа. var documentName = _obj.DocumentKind.Name; if (!string.IsNullOrWhiteSpace(_obj.RegistrationNumber)) { documentName += Docflow.OfficialDocuments.Resources.Number + _obj.RegistrationNumber; } if (_obj.RegistrationDate != null) { documentName += Docflow.OfficialDocuments.Resources.DateFrom + _obj.RegistrationDate.Value.ToString("d"); } documentBlock.AddLabel(documentName); // Типовой/Не типовой. var isStandardLabel = _obj.IsStandard.Value ? ContractBases.Resources.isStandartContract : ContractBases.Resources.isNotStandartContract; documentBlock.AddLabel(string.Format("({0})", isStandardLabel)); documentBlock.AddLineBreak(); documentBlock.AddLineBreak(); // Контрагент. documentBlock.AddLabel(string.Format("{0}:", ContractBases.Resources.Counterparty)); if (_obj.Counterparty != null) { documentBlock.AddLabel(Hyperlinks.Get(_obj.Counterparty)); if (_obj.Counterparty.Nonresident == true) { documentBlock.AddLabel(string.Format("({0})", _obj.Counterparty.Info.Properties.Nonresident.LocalizedName).ToLower()); } } else { documentBlock.AddLabel("-"); } documentBlock.AddLineBreak(); // Содержание. var subject = !string.IsNullOrEmpty(_obj.Subject) ? _obj.Subject : "-"; documentBlock.AddLabel(string.Format("{0}: {1}", ContractBases.Resources.Subject, subject)); documentBlock.AddLineBreak(); // Сумма договора. var amount = this.GetTotalAmountDocumentSummary(_obj.TotalAmount, _obj.Currency); var amountText = string.Format("{0}: {1}", _obj.Info.Properties.TotalAmount.LocalizedName, amount); documentBlock.AddLabel(amountText); documentBlock.AddLineBreak(); // Срок действия договора. var validity = "-"; var validFrom = _obj.ValidFrom.HasValue ? string.Format("{0} {1} ", ContractBases.Resources.From, _obj.ValidFrom.Value.Date.ToShortDateString()) : string.Empty; var validTill = _obj.ValidTill.HasValue ? string.Format("{0} {1}", ContractBases.Resources.Till, _obj.ValidTill.Value.Date.ToShortDateString()) : string.Empty; var isAutomaticRenewal = _obj.IsAutomaticRenewal.Value && !string.IsNullOrEmpty(validTill) ? string.Format(", {0}", ContractBases.Resources.Renewal) : string.Empty; if (!string.IsNullOrEmpty(validFrom) || !string.IsNullOrEmpty(validTill)) { validity = string.Format("{0}{1}{2}", validFrom, validTill, isAutomaticRenewal); } var validityText = string.Format("{0}:", ContractBases.Resources.Validity); documentBlock.AddLabel(validityText); documentBlock.AddLabel(validity); documentBlock.AddLineBreak(); documentBlock.AddEmptyLine(); // Примечание. var note = string.IsNullOrEmpty(_obj.Note) ? "-" : _obj.Note; var noteText = string.Format("{0}:", ContractBases.Resources.Note); documentBlock.AddLabel(noteText); documentBlock.AddLabel(note); return(documentSummary); }
// Generates content of worksheetPart2. private void GenerateWorksheetPart2Content(WorksheetPart worksheetPart2) { Worksheet worksheet2 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } }; worksheet2.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); worksheet2.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"); worksheet2.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); worksheet2.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); worksheet2.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); SheetDimension sheetDimension2 = new SheetDimension() { Reference = "A1:V19" }; SheetViews sheetViews2 = new SheetViews(); SheetView sheetView2 = new SheetView() { TabSelected = true, WorkbookViewId = (UInt32Value)0U }; Selection selection2 = new Selection() { ActiveCell = "X5", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "X5" } }; sheetView2.Append(selection2); sheetViews2.Append(sheetView2); SheetFormatProperties sheetFormatProperties2 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D }; SheetData sheetData2 = new SheetData(); Row row2 = new Row() { RowIndex = (UInt32Value)1U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D }; Cell cell2 = new Cell() { CellReference = "A1" }; CellValue cellValue2 = new CellValue(); cellValue2.Text = "1"; cell2.Append(cellValue2); Cell cell3 = new Cell() { CellReference = "C1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString }; CellValue cellValue3 = new CellValue(); cellValue3.Text = "0"; cell3.Append(cellValue3); row2.Append(cell2); row2.Append(cell3); Row row3 = new Row() { RowIndex = (UInt32Value)2U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D }; Cell cell4 = new Cell() { CellReference = "A2" }; CellValue cellValue4 = new CellValue(); cellValue4.Text = "2"; cell4.Append(cellValue4); row3.Append(cell4); Row row4 = new Row() { RowIndex = (UInt32Value)3U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D }; Cell cell5 = new Cell() { CellReference = "A3" }; CellValue cellValue5 = new CellValue(); cellValue5.Text = "3"; cell5.Append(cellValue5); row4.Append(cell5); Row row5 = new Row() { RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, Height = 135D, DyDescent = 0.25D }; Cell cell6 = new Cell() { CellReference = "A4" }; CellValue cellValue6 = new CellValue(); cellValue6.Text = "4"; cell6.Append(cellValue6); Cell cell7 = new Cell() { CellReference = "C4", StyleIndex = (UInt32Value)2U, DataType = CellValues.SharedString }; CellValue cellValue7 = new CellValue(); cellValue7.Text = "1"; cell7.Append(cellValue7); row5.Append(cell6); row5.Append(cell7); Row row6 = new Row() { RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D }; Cell cell8 = new Cell() { CellReference = "A5" }; CellValue cellValue8 = new CellValue(); cellValue8.Text = "5"; cell8.Append(cellValue8); Cell cell9 = new Cell() { CellReference = "Q5", StyleIndex = (UInt32Value)3U, DataType = CellValues.SharedString }; CellValue cellValue9 = new CellValue(); cellValue9.Text = "6"; cell9.Append(cellValue9); Cell cell10 = new Cell() { CellReference = "T5" }; CellValue cellValue10 = new CellValue(); cellValue10.Text = "4"; cell10.Append(cellValue10); row6.Append(cell8); row6.Append(cell9); row6.Append(cell10); Row row7 = new Row() { RowIndex = (UInt32Value)6U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D }; Cell cell11 = new Cell() { CellReference = "A6" }; CellFormula cellFormula1 = new CellFormula(); cellFormula1.Text = "SUM(A1:A5)"; CellValue cellValue11 = new CellValue(); cellValue11.Text = "15"; cell11.Append(cellFormula1); cell11.Append(cellValue11); Cell cell12 = new Cell() { CellReference = "T6" }; CellValue cellValue12 = new CellValue(); cellValue12.Text = "7"; cell12.Append(cellValue12); row7.Append(cell11); row7.Append(cell12); Row row8 = new Row() { RowIndex = (UInt32Value)7U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D }; Cell cell13 = new Cell() { CellReference = "T7" }; CellValue cellValue13 = new CellValue(); cellValue13.Text = "6"; cell13.Append(cellValue13); row8.Append(cell13); Row row9 = new Row() { RowIndex = (UInt32Value)8U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D }; Cell cell14 = new Cell() { CellReference = "T8" }; CellValue cellValue14 = new CellValue(); cellValue14.Text = "5"; cell14.Append(cellValue14); row9.Append(cell14); Row row10 = new Row() { RowIndex = (UInt32Value)9U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D }; Cell cell15 = new Cell() { CellReference = "T9" }; CellValue cellValue15 = new CellValue(); cellValue15.Text = "4"; cell15.Append(cellValue15); row10.Append(cell15); Row row11 = new Row() { RowIndex = (UInt32Value)10U, Spans = new ListValue<StringValue>() { InnerText = "1:22" }, DyDescent = 0.25D }; Cell cell16 = new Cell() { CellReference = "T10" }; CellValue cellValue16 = new CellValue(); cellValue16.Text = "7"; cell16.Append(cellValue16); row11.Append(cell16); Row row12 = new Row() { RowIndex = (UInt32Value)17U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D }; Cell cell17 = new Cell() { CellReference = "A17", DataType = CellValues.SharedString }; CellValue cellValue17 = new CellValue(); cellValue17.Text = "3"; cell17.Append(cellValue17); Cell cell18 = new Cell() { CellReference = "B17", DataType = CellValues.SharedString }; CellValue cellValue18 = new CellValue(); cellValue18.Text = "4"; cell18.Append(cellValue18); Cell cell19 = new Cell() { CellReference = "C17", DataType = CellValues.SharedString }; CellValue cellValue19 = new CellValue(); cellValue19.Text = "5"; cell19.Append(cellValue19); row12.Append(cell17); row12.Append(cell18); row12.Append(cell19); Row row13 = new Row() { RowIndex = (UInt32Value)18U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D }; Cell cell20 = new Cell() { CellReference = "A18" }; CellValue cellValue20 = new CellValue(); cellValue20.Text = "1"; cell20.Append(cellValue20); Cell cell21 = new Cell() { CellReference = "B18" }; CellValue cellValue21 = new CellValue(); cellValue21.Text = "2"; cell21.Append(cellValue21); Cell cell22 = new Cell() { CellReference = "C18" }; CellValue cellValue22 = new CellValue(); cellValue22.Text = "3"; cell22.Append(cellValue22); row13.Append(cell20); row13.Append(cell21); row13.Append(cell22); Row row14 = new Row() { RowIndex = (UInt32Value)19U, Spans = new ListValue<StringValue>() { InnerText = "1:3" }, DyDescent = 0.25D }; Cell cell23 = new Cell() { CellReference = "A19" }; CellValue cellValue23 = new CellValue(); cellValue23.Text = "4"; cell23.Append(cellValue23); Cell cell24 = new Cell() { CellReference = "B19" }; CellValue cellValue24 = new CellValue(); cellValue24.Text = "5"; cell24.Append(cellValue24); Cell cell25 = new Cell() { CellReference = "C19" }; CellValue cellValue25 = new CellValue(); cellValue25.Text = "6"; cell25.Append(cellValue25); row14.Append(cell23); row14.Append(cell24); row14.Append(cell25); sheetData2.Append(row2); sheetData2.Append(row3); sheetData2.Append(row4); sheetData2.Append(row5); sheetData2.Append(row6); sheetData2.Append(row7); sheetData2.Append(row8); sheetData2.Append(row9); sheetData2.Append(row10); sheetData2.Append(row11); sheetData2.Append(row12); sheetData2.Append(row13); sheetData2.Append(row14); Hyperlinks hyperlinks1 = new Hyperlinks(); Hyperlink hyperlink2 = new Hyperlink() { Reference = "Q5", Id = "rId1" }; hyperlinks1.Append(hyperlink2); PageMargins pageMargins2 = new PageMargins() { Left = 0.25D, Right = 0.25D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D }; PageSetup pageSetup1 = new PageSetup() { Orientation = OrientationValues.Landscape, HorizontalDpi = (UInt32Value)4294967293U, VerticalDpi = (UInt32Value)0U, Id = "rId2" }; S.Drawing drawing3 = new S.Drawing() { Id = "rId3" }; LegacyDrawing legacyDrawing1 = new LegacyDrawing() { Id = "rId4" }; TableParts tableParts1 = new TableParts() { Count = (UInt32Value)1U }; TablePart tablePart1 = new TablePart() { Id = "rId5" }; tableParts1.Append(tablePart1); worksheet2.Append(sheetDimension2); worksheet2.Append(sheetViews2); worksheet2.Append(sheetFormatProperties2); worksheet2.Append(sheetData2); worksheet2.Append(hyperlinks1); worksheet2.Append(pageMargins2); worksheet2.Append(pageSetup1); worksheet2.Append(drawing3); worksheet2.Append(legacyDrawing1); worksheet2.Append(tableParts1); worksheetPart2.Worksheet = worksheet2; }
public override void BeforeExecute(Sungero.Reporting.Server.BeforeExecuteEventArgs e) { var sourceDocument = AcquaintanceReport.Document; var sourceTask = AcquaintanceReport.Task; var calledFromDocument = sourceDocument != null; var selectedVersionNumber = this.AcquaintanceReport.DocumentVersion; // Если у документа нет тела, но есть задачи ознакомления, номер версии берем 0, иначе выбранный. var versionNumber = 0; if (selectedVersionNumber != null) { versionNumber = Convert.ToInt32(selectedVersionNumber); } var tasks = new List <IAcquaintanceTask>(); if (calledFromDocument) { // Получить задачи на ознакомление по документу. tasks = Docflow.PublicFunctions.OfficialDocument.Remote.GetAcquaintanceTasks(sourceDocument); // Фильтр по номеру версии. tasks = tasks .Where(t => t.AcquaintanceVersions.First(v => v.IsMainDocument == true).Number == versionNumber) .ToList(); } else { tasks.Add(sourceTask); versionNumber = Functions.AcquaintanceTask.GetDocumentVersion(sourceTask); sourceDocument = sourceTask.DocumentGroup.OfficialDocuments.First(); } // Провалидировать подписи версии. Sungero.Domain.Shared.IEntity version = null; if (versionNumber > 0 && sourceDocument.Versions.Any(v => v.Number == versionNumber)) { version = sourceDocument.Versions.First(v => v.Number == versionNumber).ElectronicDocument; } var validationMessages = Functions.Module.GetDocumentSignatureValidationErrors(version, true); if (validationMessages.Any()) { validationMessages.Insert(0, RecordManagement.Resources.SignatureValidationErrorMessage); AcquaintanceReport.SignValidationErrors = string.Join(System.Environment.NewLine, validationMessages); } // Шапка. var nonBreakingSpace = Convert.ToChar(160); AcquaintanceReport.DocumentHyperlink = Hyperlinks.Get(sourceDocument); AcquaintanceReport.DocumentName = Docflow.PublicFunctions.Module.FormatDocumentNameForReport(sourceDocument, versionNumber, true); // Приложения. var documentAddenda = Functions.Module.GetAcquintanceTaskAddendas(tasks); if (documentAddenda.Any()) { AcquaintanceReport.AddendaDescription = Reports.Resources.AcquaintanceReport.Addendas; foreach (var addendum in documentAddenda) { var addendumInfo = string.Format("\n - {0} ({1}:{2}{3}).", addendum.DisplayValue.Trim(), Docflow.Resources.Id, nonBreakingSpace, addendum.Id); AcquaintanceReport.AddendaDescription += addendumInfo; } } // Данные. var reportSessionId = System.Guid.NewGuid().ToString(); AcquaintanceReport.ReportSessionId = reportSessionId; var dataTable = new List <Structures.AcquaintanceReport.TableLine>(); var department = this.AcquaintanceReport.Department; foreach (var task in tasks) { var createdDate = Docflow.PublicFunctions.Module.ToShortDateShortTime(task.Created.Value.ToUserTime()); var taskId = task.Id; var taskHyperlink = Hyperlinks.Get(task); var isElectronicAcquaintance = task.IsElectronicAcquaintance == true; var taskDisplayName = isElectronicAcquaintance ? Reports.Resources.AcquaintanceReport.ElectronicAcquaintanceTaskDisplayNameFormat(createdDate) : Reports.Resources.AcquaintanceReport.SelfSignAcquaintanceTaskDisplayNameFormat(createdDate); // Фильтрация сотрудников по подразделениям. var acquainters = AcquaintanceFormReportServerHandlers.GetEmployeesFromParticipants(task); if (AcquaintanceReport.Department != null) { acquainters = AcquaintanceReport.IncludeSubDepartments == true ? acquainters.Where(x => x.IncludedIn(AcquaintanceReport.Department)) : acquainters.Where(x => Equals(x.Department, AcquaintanceReport.Department)); } foreach (var employee in acquainters) { var newLine = Structures.AcquaintanceReport.TableLine.Create(); newLine.RowNumber = 0; newLine.ReportSessionId = reportSessionId; // Задача. newLine.TaskDisplayName = taskDisplayName; newLine.TaskId = taskId; newLine.TaskHyperlink = taskHyperlink; // Сотрудник. newLine.ShortName = employee.Person.ShortName; newLine.LastName = employee.Person.LastName; if (employee.JobTitle != null) { newLine.JobTitle = employee.JobTitle.DisplayValue; } newLine.Department = employee.Department.DisplayValue; if (task.Status != Status.InProcess && task.Status != Status.Suspended && task.Status != Status.Completed) { if (employee.Status != Company.Employee.Status.Closed) { dataTable.Add(newLine); } continue; } // Задание. var assignment = AcquaintanceAssignments.GetAll() .Where(a => Equals(a.Task, task) && Equals(a.Performer, employee) && a.Created >= task.Started) .FirstOrDefault(); if (assignment == null) { if (employee.Status != Company.Employee.Status.Closed) { dataTable.Add(newLine); } continue; } newLine.AssignmentId = assignment.Id.ToString(); newLine.AssignmentHyperlink = Hyperlinks.Get(assignment); var isCompleted = assignment.Status == Sungero.Workflow.Task.Status.Completed; if (isCompleted) { // Дата ознакомления. var completed = Calendar.ToUserTime(assignment.Completed.Value); newLine.AcquaintanceDate = Docflow.PublicFunctions.Module.ToShortDateShortTime(completed); // Примечание. if (!Equals(assignment.CompletedBy, assignment.Performer)) { var completedByShortName = Employees.Is(assignment.CompletedBy) ? Employees.As(assignment.CompletedBy).Person.ShortName : assignment.CompletedBy.Name; newLine.Note += string.Format("{0}\n", completedByShortName); newLine.Note += string.Format("\"{0}\"", assignment.ActiveText); } else if (!Equals(assignment.ActiveText, Reports.Resources.AcquaintanceReport.AcquaintedDefaultResult.ToString())) { newLine.Note += string.Format("\"{0}\"", assignment.ActiveText); } } // Статус. newLine.State = Functions.Module.GetAcquaintanceAssignmentState(assignment, isElectronicAcquaintance, isCompleted); dataTable.Add(newLine); } } // Фильтр по статусу выполнения. if (AcquaintanceReport.EmployeesAcquaintanceStatus.Equals(Reports.Resources.AcquaintanceReport.ForAcquaintedPerformers)) { dataTable = dataTable.Where(d => d.State == Reports.Resources.AcquaintanceReport.AcquaintedState).ToList(); } else if (AcquaintanceReport.EmployeesAcquaintanceStatus.Equals(Reports.Resources.AcquaintanceReport.ForNotAcquaintedPerformers)) { dataTable = dataTable.Where(d => d.State != Reports.Resources.AcquaintanceReport.AcquaintedState).ToList(); } Docflow.PublicFunctions.Module.WriteStructuresToTable(Constants.AcquaintanceReport.SourceTableName, dataTable); // Подвал. var currentUser = Users.Current; var printedByName = Employees.Is(currentUser) ? Employees.As(currentUser).Person.ShortName : currentUser.Name; AcquaintanceReport.Printed = Reports.Resources.AcquaintanceReport.PrintedByFormat(printedByName, Calendar.UserNow); }
public override void BeforeExecute(Sungero.Reporting.Server.BeforeExecuteEventArgs e) { if (SkippedNumbersReport.DocumentRegisterId.HasValue) { SkippedNumbersReport.DocumentRegister = DocumentRegisters.Get(SkippedNumbersReport.DocumentRegisterId.Value); } SkippedNumbersReport.CurrentDate = Calendar.Now; var documentRegister = SkippedNumbersReport.DocumentRegister; var documents = Enumerable.Empty <IOfficialDocument>().AsQueryable(); AccessRights.AllowRead(() => { documents = Docflow.OfficialDocuments.GetAll() .Where(d => d.DocumentRegister == SkippedNumbersReport.DocumentRegister) .Where(d => d.RegistrationState == RegistrationState.Registered || d.RegistrationState == RegistrationState.Reserved); }); #region Период формирования отчета и разрезы var baseDate = Calendar.UserNow; var periodOffset = SkippedNumbersReport.PeriodOffset.HasValue ? SkippedNumbersReport.PeriodOffset.Value : 0; // Признак того, что отчет запущен из диалога регистрации. var launchedFromDialog = SkippedNumbersReport.RegistrationDate.HasValue; if (launchedFromDialog) { baseDate = SkippedNumbersReport.RegistrationDate.Value; // По умолчанию для отчета из диалога регистрации берем данные за последний месяц. SkippedNumbersReport.Period = Constants.SkippedNumbersReport.Month; SkippedNumbersReport.PeriodOffset = 0; } if (SkippedNumbersReport.Period.Equals(Constants.SkippedNumbersReport.Year)) { SkippedNumbersReport.PeriodBegin = Calendar.BeginningOfYear(baseDate.AddYears(periodOffset)); SkippedNumbersReport.PeriodEnd = periodOffset == 0 ? Calendar.EndOfYear(baseDate) : Calendar.EndOfYear(baseDate.AddYears(periodOffset)); } if (SkippedNumbersReport.Period.Equals(Constants.SkippedNumbersReport.Quarter)) { SkippedNumbersReport.PeriodBegin = Docflow.PublicFunctions.AccountingDocumentBase.BeginningOfQuarter(baseDate.AddMonths(3 * periodOffset)); SkippedNumbersReport.PeriodEnd = periodOffset == 0 ? Docflow.PublicFunctions.AccountingDocumentBase.EndOfQuarter(baseDate) : Docflow.PublicFunctions.AccountingDocumentBase.EndOfQuarter(baseDate.AddMonths(3 * periodOffset)); } if (SkippedNumbersReport.Period.Equals(Constants.SkippedNumbersReport.Month)) { SkippedNumbersReport.PeriodBegin = Calendar.BeginningOfMonth(baseDate.AddMonths(periodOffset)); SkippedNumbersReport.PeriodEnd = periodOffset == 0 ? Calendar.EndOfMonth(baseDate) : Calendar.EndOfMonth(baseDate.AddMonths(periodOffset)); } if (SkippedNumbersReport.Period.Equals(Constants.SkippedNumbersReport.Week)) { SkippedNumbersReport.PeriodBegin = Calendar.BeginningOfWeek(baseDate.AddDays(7 * periodOffset)); SkippedNumbersReport.PeriodEnd = periodOffset == 0 ? Calendar.EndOfWeek(baseDate) : Calendar.EndOfWeek(baseDate.AddDays(7 * periodOffset)); } // Получить границы периода журнала регистрации. var registrationDate = launchedFromDialog ? SkippedNumbersReport.RegistrationDate.Value : SkippedNumbersReport.PeriodEnd.Value; DateTime?documentRegisterPeriodBegin = Functions.DocumentRegister.GetBeginPeriod(documentRegister, registrationDate); DateTime?documentRegisterPeriodEnd = Functions.DocumentRegister.GetEndPeriod(documentRegister, registrationDate) ?? SkippedNumbersReport.PeriodEnd.Value; // Начало расчетного периода. var periodBegin = SkippedNumbersReport.PeriodBegin; // Если отчет вызван из диалога регистрации взять "месяц назад" от даты регистрации. if (launchedFromDialog) { periodBegin = registrationDate.AddMonths(-1); } // Если начало указанного периода раньше начала периода журнала, то считать от последнего. if (documentRegisterPeriodBegin.HasValue && documentRegisterPeriodBegin > periodBegin) { periodBegin = documentRegisterPeriodBegin; } else if (!documentRegisterPeriodBegin.HasValue) { documentRegisterPeriodBegin = Calendar.SqlMinValue; } SkippedNumbersReport.PeriodBegin = periodBegin; // Конец расчетного периода. var periodEnd = launchedFromDialog ? SkippedNumbersReport.RegistrationDate.Value.EndOfDay() : SkippedNumbersReport.PeriodEnd; SkippedNumbersReport.PeriodEnd = periodEnd; var hasLeadingDocument = SkippedNumbersReport.LeadingDocument != null; var hasDepartment = SkippedNumbersReport.Department != null; var hasBusinessUnit = SkippedNumbersReport.BusinessUnit != null; // Отфильтровать документы по разрезам. if (hasLeadingDocument) { documents = documents.Where(d => Equals(d.LeadingDocument, SkippedNumbersReport.LeadingDocument)); } if (hasDepartment) { documents = documents.Where(d => Equals(d.Department, SkippedNumbersReport.Department)); } if (hasBusinessUnit) { documents = documents.Where(d => Equals(d.BusinessUnit, SkippedNumbersReport.BusinessUnit)); } #endregion #region Генерация формата номера var numberFormat = string.Empty; foreach (var item in documentRegister.NumberFormatItems.OrderBy(x => x.Number)) { var elementName = string.Empty; if (item.Element == DocumentRegisterNumberFormatItems.Element.Number) { elementName = DocumentRegisters.Resources.NumberFormatNumber; } else if (item.Element == DocumentRegisterNumberFormatItems.Element.Year2Place || item.Element == DocumentRegisterNumberFormatItems.Element.Year4Place) { elementName = DocumentRegisters.Resources.NumberFormatYear; } else if (item.Element == DocumentRegisterNumberFormatItems.Element.Quarter) { elementName = DocumentRegisters.Resources.NumberFormatQuarter; } else if (item.Element == DocumentRegisterNumberFormatItems.Element.Month) { elementName = DocumentRegisters.Resources.NumberFormatMonth; } else if (item.Element == DocumentRegisterNumberFormatItems.Element.LeadingNumber) { elementName = DocumentRegisters.Resources.NumberFormatLeadingNumber; } else if (item.Element == DocumentRegisterNumberFormatItems.Element.Log) { elementName = DocumentRegisters.Resources.NumberFormatLog; } else if (item.Element == DocumentRegisterNumberFormatItems.Element.RegistrPlace) { elementName = DocumentRegisters.Resources.NumberFormatRegistrPlace; } else if (item.Element == DocumentRegisterNumberFormatItems.Element.CaseFile) { elementName = DocumentRegisters.Resources.NumberFormatCaseFile; } else if (item.Element == DocumentRegisterNumberFormatItems.Element.DepartmentCode) { elementName = DocumentRegisters.Resources.NumberFormatDepartmentCode; } else if (item.Element == DocumentRegisterNumberFormatItems.Element.BUCode) { elementName = DocumentRegisters.Resources.NumberFormatBUCode; } else if (item.Element == DocumentRegisterNumberFormatItems.Element.DocKindCode) { elementName = DocumentRegisters.Resources.NumberFormatDocKindCode; } else if (item.Element == DocumentRegisterNumberFormatItems.Element.CPartyCode) { elementName = DocumentRegisters.Resources.NumberFormatCounterpartyCode; } numberFormat += elementName + item.Separator; } SkippedNumbersReport.NumberFormat = numberFormat; #endregion #region Границы индексов в выбранном периоде // Получить минимальный индекс по документам в периоде (при ручной регистрации мб нарушение следования индексов). var firstDocumentIndex = Functions.DocumentRegister.GetIndex(documents, periodBegin, periodEnd, false); // Получить индекс документа из предыдущего периода. var previousIndex = 0; if (periodBegin != documentRegisterPeriodBegin) { previousIndex = Functions.DocumentRegister.FilterDocumentsByPeriod(documents, documentRegisterPeriodBegin, periodBegin.Value.AddDays(-1).EndOfDay()) .Where(d => !firstDocumentIndex.HasValue || d.Index < firstDocumentIndex).Select(d => d.Index).OrderByDescending(a => a).FirstOrDefault() ?? 0; } if (firstDocumentIndex == null) { firstDocumentIndex = previousIndex + 1; } var firstIndex = firstDocumentIndex < previousIndex ? firstDocumentIndex : previousIndex + 1; // Получить первый индекс документа следующего периода. var nextIndex = periodEnd != documentRegisterPeriodEnd? Functions.DocumentRegister.GetIndex(documents, periodEnd.Value.AddDays(1).BeginningOfDay(), documentRegisterPeriodEnd, false) : null; // Если в следующем периоде ещё нет документов, то взять текущий индекс журнала. var leadingDocumentId = hasLeadingDocument ? SkippedNumbersReport.LeadingDocument.Id : 0; var departmentId = hasDepartment ? SkippedNumbersReport.Department.Id : 0; var businessUnitId = hasBusinessUnit ? SkippedNumbersReport.BusinessUnit.Id : 0; if (nextIndex == null) { nextIndex = Functions.DocumentRegister.GetCurrentNumber(documentRegister, registrationDate, leadingDocumentId, departmentId, businessUnitId) + 1; } // Получить индекс по зарегистрированным документам (при ручной регистрации мб нарушение следования индексов). var lastDocumentIndex = Functions.DocumentRegister.GetIndex(documents, periodBegin, periodEnd, true) ?? nextIndex - 1; var lastIndex = lastDocumentIndex >= nextIndex ? lastDocumentIndex : nextIndex - 1; // Для случая когда нет документов в периоде. if (lastIndex < firstIndex) { lastIndex = firstIndex - 1; } #endregion // Отфильтровать документы по найденным границам индексов и по периоду журнала регистрации. // Допускать документы с номером не соответствующим формату (Index = 0). documents = documents .Where(d => !documentRegisterPeriodBegin.HasValue || d.RegistrationDate >= documentRegisterPeriodBegin) .Where(d => !documentRegisterPeriodEnd.HasValue || d.RegistrationDate <= documentRegisterPeriodEnd) .Where(l => l.Index >= firstIndex && l.Index <= lastIndex || (l.Index == 0 && l.RegistrationDate <= SkippedNumbersReport.PeriodEnd && l.RegistrationDate >= SkippedNumbersReport.PeriodBegin)); // Заполнить маску для гиперссылки. if (documents.Count() > 0) { var link = Hyperlinks.Get(documents.First()); var index = link.IndexOf("?type="); SkippedNumbersReport.hyperlinkMask = link.Substring(0, index) + "?type=DocGUID&id=DocId"; } else { SkippedNumbersReport.hyperlinkMask = string.Empty; } #region Вычислить пропущенные индексы // Создать временную таблицу для списка номеров "подряд". var skipsTableName = Constants.SkippedNumbersReport.SkipsTableName; SkippedNumbersReport.SkipsTableName = skipsTableName; var skipedNumberList = new List <string>(); var skipedNumbers = new List <Structures.SkippedNumbersReport.SkippedNumber>(); var reportSessionId = Guid.NewGuid().ToString(); SkippedNumbersReport.ReportSessionId = reportSessionId; // Заполнить таблицу номеров. var month = documentRegisterPeriodBegin.Value.Month < 10 ? string.Format("0{0}", documentRegisterPeriodBegin.Value.Month) : documentRegisterPeriodBegin.Value.Month.ToString(); var day = documentRegisterPeriodBegin.Value.Day < 10 ? string.Format("0{0}", documentRegisterPeriodBegin.Value.Day) : documentRegisterPeriodBegin.Value.Day.ToString(); var startDate = string.Format("{0}{1}{2}", documentRegisterPeriodBegin.Value.Year, month, day); month = documentRegisterPeriodEnd.Value.Month < 10 ? string.Format("0{0}", documentRegisterPeriodEnd.Value.Month) : documentRegisterPeriodEnd.Value.Month.ToString(); day = documentRegisterPeriodEnd.Value.Day < 10 ? string.Format("0{0}", documentRegisterPeriodEnd.Value.Day) : documentRegisterPeriodEnd.Value.Day.ToString(); var endDate = string.Format("{0}{1}{2}", documentRegisterPeriodEnd.Value.Year, month, day); var queryText = string.Format(Queries.SkippedNumbersReport.GetSkippedIndexes, SkippedNumbersReport.DocumentRegister.Id.ToString(), (firstIndex - 1).ToString(), (lastIndex + 1).ToString(), hasBusinessUnit.ToString(), businessUnitId.ToString(), hasDepartment.ToString(), departmentId.ToString(), hasLeadingDocument.ToString(), leadingDocumentId.ToString(), documentRegisterPeriodBegin.HasValue.ToString(), startDate, endDate); // Получить интервалы пропущеных индексов журнала в периоде. // Key - начало интервала, Value - окончиние интервала. var skippedIndexIntervals = new Dictionary <int, int>(); using (var command = SQL.GetCurrentConnection().CreateCommand()) { command.CommandText = queryText; var result = command.ExecuteReader(); while (result.Read()) { skippedIndexIntervals.Add((int)result[1], (int)result[0]); } result.Close(); } // Заполнить отчет данными для пропущенных индексов. foreach (var interval in skippedIndexIntervals) { var intervalStart = interval.Key; var intervalEnd = interval.Value; // Три и более подряд идущих пропущеных индексов должны быть собраны в одну строку. var intervalLength = intervalEnd - intervalStart + 1; if (intervalLength >= 3) { skipedNumbers.Add(Structures.SkippedNumbersReport.SkippedNumber.Create(Docflow.Reports.Resources.SkippedNumbersReport.NumbersAreSkipped, string.Format("{0}-{1}", intervalStart.ToString(), intervalEnd.ToString()), intervalStart, reportSessionId)); skipedNumberList.Add(string.Format("{0}-{1}", intervalStart.ToString(), intervalEnd.ToString())); continue; } for (var i = intervalStart; i <= intervalEnd; i++) { skipedNumbers.Add(Structures.SkippedNumbersReport.SkippedNumber.Create(Docflow.Reports.Resources.SkippedNumbersReport.NumberIsSkipped, i.ToString(), i, reportSessionId)); skipedNumberList.Add(i.ToString()); } } #endregion Functions.Module.WriteStructuresToTable(skipsTableName, skipedNumbers); // Получить 8-10 первых пропущенных номеров строкой. Для остальных указать общее количество. var skipedNumberCount = skipedNumberList.Count; var maxDisplayedNumberCount = 10; var minHiddenNumberCount = 3; var displayedValuesCount = skipedNumberCount; if (skipedNumberCount >= (maxDisplayedNumberCount + minHiddenNumberCount)) { displayedValuesCount = maxDisplayedNumberCount; } else if (skipedNumberCount > maxDisplayedNumberCount) { displayedValuesCount = skipedNumberCount - minHiddenNumberCount; } SkippedNumbersReport.SkipedNumberList = string.Join(", ", skipedNumberList.ToArray(), 0, displayedValuesCount); var hiddenSkipedNumberCount = skipedNumberCount - displayedValuesCount; if (hiddenSkipedNumberCount > 0) { var numberLabel = Functions.Module.GetNumberDeclination(hiddenSkipedNumberCount, Resources.SkippedNumbersReportNumber, Resources.SkippedNumbersReportNumberGenetive, Resources.SkippedNumbersReportNumberPlural); SkippedNumbersReport.SkipedNumberList += string.Format(Sungero.Docflow.Reports.Resources.SkippedNumbersReport.And, hiddenSkipedNumberCount, numberLabel); } // Создать таблицу для доступных пользователю документов. var availableDocuments = new List <Structures.SkippedNumbersReport.AvailableDocument>(); var previousDocDate = Calendar.SqlMinValue; foreach (var document in documents.ToList().OrderBy(x => x.Index)) { var numberOnFormat = document.Index != null && document.Index != 0; var canRead = document.AccessRights.CanRead(); var inCorrectOrder = (previousDocDate <= document.RegistrationDate || !numberOnFormat) && (document.RegistrationDate >= SkippedNumbersReport.PeriodBegin && document.RegistrationDate <= SkippedNumbersReport.PeriodEnd); availableDocuments.Add(Structures.SkippedNumbersReport.AvailableDocument.Create(document.Id, numberOnFormat, canRead, inCorrectOrder, reportSessionId)); if (numberOnFormat && inCorrectOrder) { previousDocDate = document.RegistrationDate.Value; } } SkippedNumbersReport.AvailableDocumentsTableName = Constants.SkippedNumbersReport.AvailableDocumentsTableName; Functions.Module.WriteStructuresToTable(SkippedNumbersReport.AvailableDocumentsTableName, availableDocuments); }
public override string ToString() => $"(Footnotes: {Content.Elements().Count()}, Hyperlinks: {Hyperlinks.Count()})";
private void Button_Click_1(object sender, RoutedEventArgs e) { string mainGoAction = Properties.Settings.Default.mainGoAction; if (mainGoAction == "email") { selectedOption.IsEnabled = true; notSelectedOption.IsEnabled = true; if (dataGrid.SelectedItems.Count == 0) { selectedOption.IsEnabled = false; } if (dataGrid.SelectedItems.Count == dataGrid.Items.Count) { notSelectedOption.IsEnabled = false; } System.Windows.Controls.Button senderButton = sender as System.Windows.Controls.Button; System.Windows.Controls.ContextMenu contextMenu = senderButton.ContextMenu; contextMenu.IsEnabled = true; contextMenu.PlacementTarget = senderButton; contextMenu.Placement = System.Windows.Controls.Primitives.PlacementMode.Left; contextMenu.IsOpen = true; } else if (mainGoAction == "excel") { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Excel Workbook (*.xlsx)|*.xlsx"; if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { try { using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(saveFileDialog.FileName, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorkbookStylesPart workbookStylePart = workbookPart.AddNewPart <WorkbookStylesPart>(); workbookStylePart.Stylesheet = new Stylesheet( new DocumentFormat.OpenXml.Spreadsheet.Fonts( new DocumentFormat.OpenXml.Spreadsheet.Font(), new DocumentFormat.OpenXml.Spreadsheet.Font() { Color = new DocumentFormat.OpenXml.Spreadsheet.Color() { Theme = 10 }, Underline = new DocumentFormat.OpenXml.Spreadsheet.Underline() }, new DocumentFormat.OpenXml.Spreadsheet.Font() { Bold = new DocumentFormat.OpenXml.Spreadsheet.Bold() }), new Fills( new Fill()), new Borders( new DocumentFormat.OpenXml.Spreadsheet.Border()), new CellFormats( new CellFormat() { NumberFormatId = 0, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0 }, new CellFormat() { NumberFormatId = 14, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true) }, new CellFormat() { NumberFormatId = 10, FormatId = 0, FontId = 0, BorderId = 0, FillId = 0, ApplyNumberFormat = BooleanValue.FromBoolean(true) }, new CellFormat() { NumberFormatId = 0, FormatId = 0, FontId = 1, BorderId = 0, FillId = 0 }, new CellFormat() { NumberFormatId = 0, FormatId = 0, FontId = 2, BorderId = 0, FillId = 0 })); workbookStylePart.Stylesheet.Save(); Hyperlinks hyperlinks = new Hyperlinks(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); worksheetPart.Worksheet.Append(hyperlinks); worksheetPart.Worksheet.Save(); Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Table" }; sheets.Append(sheet); SharedStringTablePart sharedStringTablePart = spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>(); sharedStringTablePart.SharedStringTable = new SharedStringTable(); SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>(); sheetData.Append(new Row( new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Requestor E-mail")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Feedback Rate")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Number Of Feedbacks")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Number Of No Feedbacks")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Reference")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Publish Date")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, "Title")), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 4 })); int rowCounter = 1; foreach (UserData userData in dataGridSource) { foreach (SimRequestData simRequestData in userData.simRequests) { rowCounter++; sheetData.Append(new Row( new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, userData.email.ToString())), DataType = new EnumValue <CellValues>(CellValues.SharedString) }, new Cell() { CellValue = new CellValue((Convert.ToDouble(userData.rate) / 100d).ToString()), StyleIndex = 2 }, new Cell() { CellValue = new CellValue(userData.feedbacks.ToString()) }, new Cell() { CellValue = new CellValue(userData.noFeedbacks.ToString()) }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, simRequestData.reference.ToString())), DataType = new EnumValue <CellValues>(CellValues.SharedString), StyleIndex = 3 }, new Cell() { CellValue = new CellValue(Convert.ToDateTime(simRequestData.publishDate).ToOADate().ToString()), StyleIndex = 1 }, new Cell() { CellValue = new CellValue(insertSharedString(sharedStringTablePart, simRequestData.title.ToString())), DataType = new EnumValue <CellValues>(CellValues.SharedString) } )); string hyperlinkID = "id" + rowCounter.ToString(); hyperlinks.Append(new DocumentFormat.OpenXml.Spreadsheet.Hyperlink() { Reference = "E" + rowCounter.ToString(), Id = hyperlinkID }); worksheetPart.AddHyperlinkRelationship(simRequestData.requestLink, true, hyperlinkID); } } workbookPart.Workbook.Save(); } } catch (Exception exception) { System.Windows.MessageBox.Show(exception.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error, MessageBoxResult.OK); } } } }
/// <summary> /// Добавить гиперссылку на номенклатуру дел к строке. /// </summary> /// <param name="source">Исходная строка.</param> /// <returns>Строка, дополненная ссылкой на номенклатуру дел.</returns> public virtual string AppendCaseFilesHyperlinkTo(string source) { return(string.Format("{0}{1}{2}", source, Environment.NewLine, Hyperlinks.Get(CaseFiles.Info))); }
// Generates content of Sheet. private void GenerateWorksheetPartContent(WorksheetPart worksheetPart, DataTable data, bool isPrimaryTab = false) { Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } }; worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); SheetViews sheetViews = new SheetViews(); SheetView sheetView = new SheetView() { TabSelected = isPrimaryTab, WorkbookViewId = (UInt32Value)0U }; sheetViews.Append(sheetView); SheetFormatProperties sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 15D, DyDescent = 0.25D }; //If you want to default select a cell //Selection selection1 = new Selection() { ActiveCell = "A3", SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A3" } }; //sheetView1.Append(selection1); //Column Creation Column tempCol; Columns columns = new Columns(); for (int ii = 0; ii < data.Columns.Count; ii++) { tempCol = new Column() { Min = (UInt32Value)Convert.ToUInt32(ii + 1), Max = (UInt32Value)Convert.ToUInt32(ii + 1), Width = 10.28515625D, CustomWidth = true, BestFit = true }; columns.Append(tempCol); } SheetData sheetData = new SheetData(); //Creating cells and adding them to rows Row row; Cell cell; CellValue cellVal; int rowIndex = 1; string cellName = ""; List<string> cellsWithHyperLinks = new List<string>(); //Insert Header Row row = new Row() { RowIndex = (UInt32Value)Convert.ToUInt32(rowIndex), Spans = new ListValue<StringValue>() { InnerText = "1:" + data.Columns.Count } };//, DyDescent = 0.25D }; for (int jj = 0; jj < data.Columns.Count; jj++) { cellName = GetExcelCellName(jj + 1, rowIndex); cell = new Cell() { CellReference = cellName, StyleIndex = (UInt32Value)1U, DataType = CellValues.String }; cellVal = new CellValue() { Text = data.Columns[jj].ColumnName }; cell.Append(cellVal); row.Append(cell); } sheetData.Append(row); rowIndex++; //Insert Data foreach (DataRow datarow in data.Rows) { row = new Row() { RowIndex = (UInt32Value)Convert.ToUInt32(rowIndex), Spans = new ListValue<StringValue>() { InnerText = "1:" + data.Columns.Count } };//, DyDescent = 0.25D }; for (int jj = 0; jj < data.Columns.Count; jj++) { cellName = GetExcelCellName(jj + 1, rowIndex); cell = new Cell() { CellReference = cellName, StyleIndex = (UInt32Value)0U }; if (data.Columns[jj].DataType == typeof(int) || data.Columns[jj].DataType == typeof(double)) { cell.DataType = CellValues.Number; } else if (data.Columns[jj].DataType == typeof(DateTime)) { cell.DataType = CellValues.Date; } else { cell.DataType = CellValues.String; } cellVal = new CellValue() { Text = datarow[jj].ToString() }; if (cellVal.Text.ToLower().Contains("http")) { cell.StyleIndex = (UInt32Value)2U; cellsWithHyperLinks.Add(cellName); } cell.Append(cellVal); row.Append(cell); } //Add the row to the sheetdata object sheetData.Append(row); rowIndex++; } //Adding hyperlinks Hyperlink hyperlink; Hyperlinks hyperlinks = null; if (cellsWithHyperLinks.Count > 0) { hyperlinks = new Hyperlinks(); for (int ii = 0; ii < cellsWithHyperLinks.Count; ii++) { hyperlink = new Hyperlink() { Reference = cellsWithHyperLinks[ii], Id = "rId" + (ii + 1) }; hyperlinks.Append(hyperlink); } } //PageMargins pageMargins1 = new PageMargins() { Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D }; //PageSetup pageSetup1 = new PageSetup() { Orientation = OrientationValues.Portrait, VerticalDpi = (UInt32Value)0U, Id = "rId2" }; worksheet1.Append(sheetViews); worksheet1.Append(sheetFormatProperties1); worksheet1.Append(columns); worksheet1.Append(sheetData); if (hyperlinks != null) { worksheet1.Append(hyperlinks); } //worksheet1.Append(pageMargins1); //worksheet1.Append(pageSetup1); worksheetPart.Worksheet = worksheet1; }