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