public static void PageSetupUpdate(WorksheetPart worksheetPart, OrientationValues landscapeOrPortrait, DoubleValue marginLeft, DoubleValue marginRight, DoubleValue marginTop, DoubleValue marginBottom, DoubleValue marginHeader, DoubleValue marginFooter, Boolean isFitToPage, UInt32Value FitToHeight, UInt32Value FitToWidth, UInt32Value pageSize, string headerLeft, string headerCenter, string headerRight, string footerLeft, string footerRight) { Worksheet ws = worksheetPart.Worksheet; //page setup them moi pagesetup properties SheetProperties sp = new SheetProperties(new PageSetupProperties()); ws.SheetProperties = sp; PrintOptions printOp = new PrintOptions(); printOp.HorizontalCentered = true; ws.AppendChild(printOp); PageMargins pageMargins = new PageMargins(); pageMargins.Left = marginLeft; pageMargins.Right = marginRight; pageMargins.Top = marginTop; pageMargins.Bottom = marginBottom; pageMargins.Header = marginHeader; pageMargins.Footer = marginFooter; ws.AppendChild(pageMargins); // Set the FitToPage property to true ws.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(isFitToPage); DocumentFormat.OpenXml.Spreadsheet.PageSetup pgOr = new DocumentFormat.OpenXml.Spreadsheet.PageSetup(); pgOr.Orientation = landscapeOrPortrait; pgOr.PaperSize = pageSize; pgOr.FitToHeight = FitToHeight; pgOr.FitToWidth = FitToWidth; ws.AppendChild(pgOr); HeaderFooter headerFooter1 = new HeaderFooter(); OddHeader oddHeader1 = new OddHeader(); oddHeader1.Text = "&L&\"Times New Roman,Regular\"" + headerLeft + "&C&\"Times New Roman,Regular\"" + headerCenter + "&R&\"Times New Roman,Regular\"" + headerRight; OddFooter oddFooter1 = new OddFooter(); oddFooter1.Text = "&L&\"Times New Roman,Regular\"" + footerLeft + "&C&P&R&\"Times New Roman,Regular\"" + footerRight; headerFooter1.Append(oddHeader1); headerFooter1.Append(oddFooter1); ws.AppendChild(headerFooter1); //save worksheet properties //worksheetPart.Worksheet.Save(); }
public byte[] Build(User currentUser, DateTime min, DateTime max, string filterUserScope, int[] filterUserIds, string filterProjectScope, int[] filterProjectIds, string filterLockStatus) { var organization = db.Organizations.Find(currentUser.OrganizationId); var reportTitle = $"PUNCHES BY DAY {min.ToString("M/d/yyyy")} thru {max.ToString("M/d/yyyy")} GENERATED {DateTime.Now.ToString("ddd, MMM d, yyyy h:mm:ss tt").ToUpperInvariant()}"; using (var stream = new MemoryStream()) using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook)) { // ------------------------------------------------------------ // Add document properties. // ------------------------------------------------------------ document.PackageProperties.Creator = "BRIZBEE"; document.PackageProperties.Created = DateTime.UtcNow; // ------------------------------------------------------------ // Add a WorkbookPart to the document. // ------------------------------------------------------------ var workbookPart1 = document.AddWorkbookPart(); workbookPart1.Workbook = new Workbook(); // ------------------------------------------------------------ // Apply stylesheet. // ------------------------------------------------------------ var workStylePart1 = workbookPart1.AddNewPart <WorkbookStylesPart>(); workStylePart1.Stylesheet = Stylesheets.Common();; // ------------------------------------------------------------ // Add a WorksheetPart to the WorkbookPart. // ------------------------------------------------------------ var worksheetPart1 = workbookPart1.AddNewPart <WorksheetPart>(); var worksheet1 = new Worksheet(); var rowBreaks1 = new RowBreaks() { Count = 0, ManualBreakCount = 0 }; var mergeCells1 = new MergeCells() { Count = 0 }; var sheetData1 = new SheetData(); // ------------------------------------------------------------ // Collect the users based on the filters. // ------------------------------------------------------------ List <User> users; if (filterUserScope.ToUpperInvariant() == "SPECIFIC") { users = db.Users .Where(u => u.OrganizationId == currentUser.OrganizationId) .Where(u => u.IsDeleted == false) .Where(u => filterUserIds.Contains(u.Id)) .OrderBy(u => u.Name) .ToList(); } else { users = db.Users .Where(u => u.OrganizationId == currentUser.OrganizationId) .Where(u => u.IsDeleted == false) .OrderBy(u => u.Name) .ToList(); } // ------------------------------------------------------------ // Collect the punches based on the filters. // ------------------------------------------------------------ IQueryable <Punch> punchesQueryable = db.Punches .Include(p => p.Task.Job.Customer) .Include(p => p.User) .Include(p => p.ServiceRate) .Include(p => p.PayrollRate) .Where(p => p.User.OrganizationId == currentUser.OrganizationId) .Where(p => p.User.IsDeleted == false) .Where(p => p.OutAt.HasValue == true) .Where(p => DbFunctions.TruncateTime(p.InAt) >= min.Date) .Where(p => DbFunctions.TruncateTime(p.InAt) <= max.Date); // Optionally filter projects. if (filterProjectScope.ToUpperInvariant() == "SPECIFIC") { punchesQueryable = punchesQueryable .Where(p => filterProjectIds.Contains(p.Task.JobId)); } // Optionally filter locked or unlocked. if (filterLockStatus.ToUpperInvariant() == "ONLY") { punchesQueryable = punchesQueryable.Where(p => p.CommitId != null); } else if (filterLockStatus.ToUpperInvariant() == "UNCOMMITTED") { punchesQueryable = punchesQueryable.Where(p => p.CommitId == null); } var punches = punchesQueryable.ToList(); var dates = punches .GroupBy(p => p.InAt.Date) .Select(g => g.Key) .OrderBy(g => g.Date) .ToList(); // ------------------------------------------------------------ // Loop each date. // ------------------------------------------------------------ var rowIndex = (uint)1; foreach (var date in dates) { var punchesForDate = punches .Where(p => p.InAt.Date == date.Date) .OrderBy(p => p.InAt); // ------------------------------------------------------------ // Header for date cell. // ------------------------------------------------------------ var rowDate = new Row() { RowIndex = rowIndex, Height = 22D, CustomHeight = true, Spans = new ListValue <StringValue>() { InnerText = "1:1" }, StyleIndex = 4U, CustomFormat = true }; var cellDate = new Cell() { CellReference = $"A{rowIndex}", StyleIndex = 4U, DataType = CellValues.String, CellValue = new CellValue(date.ToString("D")) }; rowDate.Append(cellDate); sheetData1.Append(rowDate); // Merge the date across the row. var mergeCell1 = new MergeCell() { Reference = $"A{rowIndex}:M{rowIndex}" }; mergeCells1.Append(mergeCell1); mergeCells1.Count++; rowIndex++; // ------------------------------------------------------------ // Headers for punch cells. // ------------------------------------------------------------ var rowHeaders = new Row() { RowIndex = rowIndex, Height = 16D, CustomHeight = true, StyleIndex = 1U, CustomFormat = true }; // InAt var cellInAtHeader = new Cell() { CellReference = $"A{rowIndex}", DataType = CellValues.String, StyleIndex = 1U }; var cellValueForInAtHeader = new CellValue("In"); cellInAtHeader.Append(cellValueForInAtHeader); rowHeaders.Append(cellInAtHeader); // OutAt var cellOutAtHeader = new Cell() { CellReference = $"B{rowIndex}", DataType = CellValues.String, StyleIndex = 1U }; var cellValueForOutAtHeader = new CellValue("Out"); cellOutAtHeader.Append(cellValueForOutAtHeader); rowHeaders.Append(cellOutAtHeader); // User Name var cellUserNameHeader = new Cell() { CellReference = $"C{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("User") }; rowHeaders.Append(cellUserNameHeader); // Task Number var cellTaskNumberHeader = new Cell() { CellReference = $"D{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("#") }; rowHeaders.Append(cellTaskNumberHeader); // Task Name var cellTaskNameHeader = new Cell() { CellReference = $"E{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Task") }; rowHeaders.Append(cellTaskNameHeader); // Project Number var cellProjectNumberHeader = new Cell() { CellReference = $"F{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("#") }; rowHeaders.Append(cellProjectNumberHeader); // Project Name var cellProjectNameHeader = new Cell() { CellReference = $"G{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Project") }; rowHeaders.Append(cellProjectNameHeader); // Customer Number var cellCustomerNumberHeader = new Cell() { CellReference = $"H{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("#") }; rowHeaders.Append(cellCustomerNumberHeader); // Customer Name var cellCustomerNameHeader = new Cell() { CellReference = $"I{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Customer") }; rowHeaders.Append(cellCustomerNameHeader); // Customer Rate var cellCustomerRateHeader = new Cell() { CellReference = $"J{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Customer Rate") }; rowHeaders.Append(cellCustomerRateHeader); // Payroll Rate var cellPayrollRateHeader = new Cell() { CellReference = $"K{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Payroll Rate") }; rowHeaders.Append(cellPayrollRateHeader); // Locked var cellLockedHeader = new Cell() { CellReference = $"L{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Locked?") }; rowHeaders.Append(cellLockedHeader); // Total var cellTotalHeader = new Cell() { CellReference = $"M{rowIndex}", DataType = CellValues.String, StyleIndex = 2U, CellValue = new CellValue("Total") }; rowHeaders.Append(cellTotalHeader); sheetData1.Append(rowHeaders); rowIndex++; // ------------------------------------------------------------ // Punch cells. // ------------------------------------------------------------ foreach (var punch in punchesForDate) { var rowPunch = new Row() { RowIndex = rowIndex, Height = 16D, CustomHeight = true }; // InAt var cellInAt = new Cell() { CellReference = $"A{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.InAt.ToShortTimeString()) }; rowPunch.Append(cellInAt); // OutAt var cellOutAt = new Cell() { CellReference = $"B{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.OutAt.Value.ToShortTimeString()) }; rowPunch.Append(cellOutAt); // User Name var cellUserName = new Cell() { CellReference = $"C{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.User.Name) }; rowPunch.Append(cellUserName); // Task Number var cellTaskNumber = new Cell() { CellReference = $"D{rowIndex}", DataType = CellValues.Number, StyleIndex = 6U, CellValue = new CellValue(punch.Task.Number) }; rowPunch.Append(cellTaskNumber); // Task Name var cellTaskName = new Cell() { CellReference = $"E{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.Task.Name) }; rowPunch.Append(cellTaskName); // Project Number var cellProjectNumber = new Cell() { CellReference = $"F{rowIndex}", DataType = CellValues.Number, StyleIndex = 6U, CellValue = new CellValue(punch.Task.Job.Number) }; rowPunch.Append(cellProjectNumber); // Project Name var cellProjectName = new Cell() { CellReference = $"G{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.Task.Job.Name) }; rowPunch.Append(cellProjectName); // Customer Number var cellCustomerNumber = new Cell() { CellReference = $"H{rowIndex}", DataType = CellValues.Number, StyleIndex = 6U, CellValue = new CellValue(punch.Task.Job.Customer.Number) }; rowPunch.Append(cellCustomerNumber); // Customer Name var cellCustomerName = new Cell() { CellReference = $"I{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.Task.Job.Customer.Name) }; rowPunch.Append(cellCustomerName); // Customer Rate var cellCustomerRate = new Cell() { CellReference = $"J{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.ServiceRateId.HasValue ? punch.ServiceRate.Name : "") }; rowPunch.Append(cellCustomerRate); // Payroll Rate var cellPayrollRate = new Cell() { CellReference = $"K{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(punch.PayrollRateId.HasValue ? punch.PayrollRate.Name : "") }; rowPunch.Append(cellPayrollRate); // Locked var cellLocked = new Cell() { CellReference = $"L{rowIndex}", DataType = CellValues.String, StyleIndex = 3U, CellValue = new CellValue(punch.CommitId.HasValue ? "X" : "") }; rowPunch.Append(cellLocked); // Calculate the total var total = Math.Round((punch.OutAt.Value - punch.InAt).TotalMinutes / 60, 2).ToString("0.00"); // Total var cellTotal = new Cell() { CellReference = $"M{rowIndex}", DataType = CellValues.Number, StyleIndex = 5U, CellValue = new CellValue(total) }; rowPunch.Append(cellTotal); sheetData1.Append(rowPunch); rowIndex++; } // ------------------------------------------------------------ // Cells for date total. // ------------------------------------------------------------ var rowDateTotal = new Row() { RowIndex = rowIndex, Height = 16D, CustomHeight = true, StyleIndex = 1U, CustomFormat = true }; // Header Cell var cellDateFormatted = new Cell() { CellReference = $"A{rowIndex}", DataType = CellValues.String, StyleIndex = 2U, CellValue = new CellValue("Daily Total") }; rowDateTotal.Append(cellDateFormatted); // Calculate the total double dailyTotalMinutes = 0; foreach (var punch in punchesForDate) { dailyTotalMinutes += (punch.OutAt.Value - punch.InAt).TotalMinutes; } var dailyTotal = Math.Round(dailyTotalMinutes / 60, 2).ToString("0.00"); // Total Cell var cellDateTotal = new Cell() { CellReference = $"M{rowIndex}", DataType = CellValues.Number, StyleIndex = 2U, CellValue = new CellValue(dailyTotal) }; rowDateTotal.Append(cellDateTotal); sheetData1.Append(rowDateTotal); // Merge the date across the row. var mergeCell3 = new MergeCell() { Reference = $"A{rowIndex}:L{rowIndex}" }; mergeCells1.Append(mergeCell3); mergeCells1.Count++; rowIndex++; // ------------------------------------------------------------ // Add a page break. // ------------------------------------------------------------ var rowBreak1 = new Break() { Id = rowIndex, Max = 16383U, ManualPageBreak = true }; rowBreaks1.Append(rowBreak1); rowBreaks1.ManualBreakCount++; rowBreaks1.Count++; rowIndex++; } // ------------------------------------------------------------ // Custom column width. // ------------------------------------------------------------ var columns1 = new Columns(); var column1 = new Column() { Min = 1U, Max = 1U, Width = 9D, CustomWidth = true }; var column2 = new Column() { Min = 2U, Max = 2U, Width = 9D, CustomWidth = true }; var column3 = new Column() { Min = 3U, Max = 3U, Width = 28D, CustomWidth = true }; var column4 = new Column() { Min = 4U, Max = 4U, Width = 8D, CustomWidth = true }; var column5 = new Column() { Min = 5U, Max = 5U, Width = 28D, CustomWidth = true }; var column6 = new Column() { Min = 6U, Max = 6U, Width = 8D, CustomWidth = true }; var column7 = new Column() { Min = 7U, Max = 7U, Width = 28D, CustomWidth = true }; var column8 = new Column() { Min = 8U, Max = 8U, Width = 8D, CustomWidth = true }; var column9 = new Column() { Min = 9U, Max = 9U, Width = 28D, CustomWidth = true }; var column10 = new Column() { Min = 10U, Max = 10U, Width = 15D, CustomWidth = true }; var column11 = new Column() { Min = 11U, Max = 11U, Width = 15D, CustomWidth = true }; var column12 = new Column() { Min = 12U, Max = 12U, Width = 8D, CustomWidth = true }; var column13 = new Column() { Min = 13U, Max = 13U, Width = 8D, CustomWidth = true }; columns1.Append(column1); columns1.Append(column2); columns1.Append(column3); columns1.Append(column4); columns1.Append(column5); columns1.Append(column6); columns1.Append(column7); columns1.Append(column8); columns1.Append(column9); columns1.Append(column10); columns1.Append(column11); columns1.Append(column12); columns1.Append(column13); // ------------------------------------------------------------ // Sheet Views. // ------------------------------------------------------------ var sheetViews1 = new SheetViews(); var sheetView1 = new SheetView() { ShowGridLines = true, TabSelected = true, ZoomScaleNormal = 100U, WorkbookViewId = 0U }; var selection1 = new Selection() { ActiveCell = "A1", SequenceOfReferences = new ListValue <StringValue>() { InnerText = "A1" } }; sheetView1.Append(selection1); sheetViews1.Append(sheetView1); // ------------------------------------------------------------ // Sheet Format. // ------------------------------------------------------------ var sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 16D, DyDescent = 0.35D }; // ------------------------------------------------------------ // Page Setup. // ------------------------------------------------------------ var pageMargins1 = new PageMargins() { Left = 0.5D, Right = 0.5D, Top = 0.5D, Bottom = 0.5D, Header = 0.3D, Footer = 0.3D }; var pageSetup1 = new PageSetup() { Orientation = OrientationValues.Landscape }; // ------------------------------------------------------------ // Header and Footer. // ------------------------------------------------------------ var headerFooter1 = new HeaderFooter(); var oddHeader1 = new OddHeader(); oddHeader1.Text = reportTitle; var oddFooter1 = new OddFooter(); oddFooter1.Text = organization.Name; headerFooter1.Append(oddHeader1); headerFooter1.Append(oddFooter1); // ------------------------------------------------------------ // Build the worksheet. // ------------------------------------------------------------ worksheet1.Append(sheetViews1); worksheet1.Append(columns1); worksheet1.Append(sheetData1); // Cannot add zero merge cells. if (mergeCells1.Count != 0) { worksheet1.Append(mergeCells1); } worksheet1.Append(pageMargins1); worksheet1.Append(pageSetup1); worksheet1.Append(headerFooter1); worksheet1.Append(rowBreaks1); worksheetPart1.Worksheet = worksheet1; // ------------------------------------------------------------ // Add Sheets to the Workbook. // ------------------------------------------------------------ var sheets = workbookPart1.Workbook.AppendChild(new Sheets()); // ------------------------------------------------------------ // Append a new worksheet and associate it with the workbook. // ------------------------------------------------------------ var sheet = new Sheet() { Id = workbookPart1.GetIdOfPart(worksheetPart1), SheetId = 1, Name = "Report" }; sheets.Append(sheet); // Save and close the document. workbookPart1.Workbook.Save(); document.Close(); return(stream.ToArray()); } }
private static void GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet, SaveContext context) { #region Worksheet if (worksheetPart.Worksheet == null) worksheetPart.Worksheet = new Worksheet(); GenerateTables(xlWorksheet, worksheetPart, context); if ( !worksheetPart.Worksheet.NamespaceDeclarations.Contains(new KeyValuePair<String, String>("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) { worksheetPart.Worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); } #endregion var cm = new XLWSContentManager(worksheetPart.Worksheet); #region SheetProperties if (worksheetPart.Worksheet.SheetProperties == null) worksheetPart.Worksheet.SheetProperties = new SheetProperties(); worksheetPart.Worksheet.SheetProperties.TabColor = xlWorksheet.TabColor.HasValue ? GetTabColor(xlWorksheet.TabColor) : null; cm.SetElement(XLWSContentManager.XLWSContents.SheetProperties, worksheetPart.Worksheet.SheetProperties); if (worksheetPart.Worksheet.SheetProperties.OutlineProperties == null) worksheetPart.Worksheet.SheetProperties.OutlineProperties = new OutlineProperties(); worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryBelow = (xlWorksheet.Outline.SummaryVLocation == XLOutlineSummaryVLocation.Bottom); worksheetPart.Worksheet.SheetProperties.OutlineProperties.SummaryRight = (xlWorksheet.Outline.SummaryHLocation == XLOutlineSummaryHLocation.Right); if (worksheetPart.Worksheet.SheetProperties.PageSetupProperties == null && (xlWorksheet.PageSetup.PagesTall > 0 || xlWorksheet.PageSetup.PagesWide > 0)) worksheetPart.Worksheet.SheetProperties.PageSetupProperties = new PageSetupProperties {FitToPage = true}; #endregion var maxColumn = 0; var sheetDimensionReference = "A1"; if (xlWorksheet.Internals.CellsCollection.Count > 0) { maxColumn = xlWorksheet.Internals.CellsCollection.MaxColumnUsed; var maxRow = xlWorksheet.Internals.CellsCollection.MaxRowUsed; sheetDimensionReference = "A1:" + XLHelper.GetColumnLetterFromNumber(maxColumn) + maxRow.ToStringLookup(); } if (xlWorksheet.Internals.ColumnsCollection.Count > 0) { var maxColCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max(); if (maxColCollection > maxColumn) maxColumn = maxColCollection; } #region SheetViews if (worksheetPart.Worksheet.SheetDimension == null) worksheetPart.Worksheet.SheetDimension = new SheetDimension {Reference = sheetDimensionReference}; cm.SetElement(XLWSContentManager.XLWSContents.SheetDimension, worksheetPart.Worksheet.SheetDimension); if (worksheetPart.Worksheet.SheetViews == null) worksheetPart.Worksheet.SheetViews = new SheetViews(); cm.SetElement(XLWSContentManager.XLWSContents.SheetViews, worksheetPart.Worksheet.SheetViews); var sheetView = (SheetView)worksheetPart.Worksheet.SheetViews.FirstOrDefault(); if (sheetView == null) { sheetView = new SheetView {WorkbookViewId = 0U}; worksheetPart.Worksheet.SheetViews.AppendChild(sheetView); } if (xlWorksheet.TabSelected) sheetView.TabSelected = true; else sheetView.TabSelected = null; if (xlWorksheet.ShowFormulas) sheetView.ShowFormulas = true; else sheetView.ShowFormulas = null; if (xlWorksheet.ShowGridLines) sheetView.ShowGridLines = null; else sheetView.ShowGridLines = false; if (xlWorksheet.ShowOutlineSymbols) sheetView.ShowOutlineSymbols = null; else sheetView.ShowOutlineSymbols = false; if (xlWorksheet.ShowRowColHeaders) sheetView.ShowRowColHeaders = null; else sheetView.ShowRowColHeaders = false; if (xlWorksheet.ShowRuler) sheetView.ShowRuler = null; else sheetView.ShowRuler = false; if (xlWorksheet.ShowWhiteSpace) sheetView.ShowWhiteSpace = null; else sheetView.ShowWhiteSpace = false; if (xlWorksheet.ShowZeros) sheetView.ShowZeros = null; else sheetView.ShowZeros = false; if (xlWorksheet.RightToLeft) sheetView.RightToLeft = true; else sheetView.RightToLeft = null; if (xlWorksheet.SheetView.View == XLSheetViewOptions.Normal) sheetView.View = null; else sheetView.View = xlWorksheet.SheetView.View.ToOpenXml(); var pane = sheetView.Elements<Pane>().FirstOrDefault(); if (pane == null) { pane = new Pane(); sheetView.AppendChild(pane); } pane.State = PaneStateValues.FrozenSplit; Double hSplit = xlWorksheet.SheetView.SplitColumn; Double ySplit = xlWorksheet.SheetView.SplitRow; pane.HorizontalSplit = hSplit; pane.VerticalSplit = ySplit; pane.TopLeftCell = XLHelper.GetColumnLetterFromNumber(xlWorksheet.SheetView.SplitColumn + 1) + (xlWorksheet.SheetView.SplitRow + 1); if (hSplit == 0 && ySplit == 0) sheetView.RemoveAllChildren<Pane>(); if (xlWorksheet.SelectedRanges.Any() || xlWorksheet.ActiveCell != null) { sheetView.RemoveAllChildren<Selection>(); var firstSelection = xlWorksheet.SelectedRanges.FirstOrDefault(); var selection = new Selection(); if (xlWorksheet.ActiveCell != null) selection.ActiveCell = xlWorksheet.ActiveCell.Address.ToStringRelative(false); else if (firstSelection != null) selection.ActiveCell = firstSelection.RangeAddress.FirstAddress.ToStringRelative(false); var seqRef = new List<String> {selection.ActiveCell.Value}; seqRef.AddRange(xlWorksheet.SelectedRanges .Select(range => range.RangeAddress.ToStringRelative(false))); selection.SequenceOfReferences = new ListValue<StringValue> {InnerText = String.Join(" ", seqRef.Distinct().ToArray())}; sheetView.Append(selection); } #endregion var maxOutlineColumn = 0; if (xlWorksheet.ColumnCount() > 0) maxOutlineColumn = xlWorksheet.GetMaxColumnOutline(); var maxOutlineRow = 0; if (xlWorksheet.RowCount() > 0) maxOutlineRow = xlWorksheet.GetMaxRowOutline(); #region SheetFormatProperties if (worksheetPart.Worksheet.SheetFormatProperties == null) worksheetPart.Worksheet.SheetFormatProperties = new SheetFormatProperties(); cm.SetElement(XLWSContentManager.XLWSContents.SheetFormatProperties, worksheetPart.Worksheet.SheetFormatProperties); worksheetPart.Worksheet.SheetFormatProperties.DefaultRowHeight = xlWorksheet.RowHeight; if (xlWorksheet.RowHeightChanged) worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = true; else worksheetPart.Worksheet.SheetFormatProperties.CustomHeight = null; var worksheetColumnWidth = GetColumnWidth(xlWorksheet.ColumnWidth); if (xlWorksheet.ColumnWidthChanged) worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = worksheetColumnWidth; else worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = null; if (maxOutlineColumn > 0) worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = (byte)maxOutlineColumn; else worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelColumn = null; if (maxOutlineRow > 0) worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = (byte)maxOutlineRow; else worksheetPart.Worksheet.SheetFormatProperties.OutlineLevelRow = null; #endregion #region Columns if (xlWorksheet.Internals.CellsCollection.Count == 0 && xlWorksheet.Internals.ColumnsCollection.Count == 0 && xlWorksheet.Style.Equals(DefaultStyle)) worksheetPart.Worksheet.RemoveAllChildren<Columns>(); else { if (!worksheetPart.Worksheet.Elements<Columns>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Columns); worksheetPart.Worksheet.InsertAfter(new Columns(), previousElement); } var columns = worksheetPart.Worksheet.Elements<Columns>().First(); cm.SetElement(XLWSContentManager.XLWSContents.Columns, columns); var sheetColumnsByMin = columns.Elements<Column>().ToDictionary(c => c.Min.Value, c => c); //Dictionary<UInt32, Column> sheetColumnsByMax = columns.Elements<Column>().ToDictionary(c => c.Max.Value, c => c); Int32 minInColumnsCollection; Int32 maxInColumnsCollection; if (xlWorksheet.Internals.ColumnsCollection.Count > 0) { minInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Min(); maxInColumnsCollection = xlWorksheet.Internals.ColumnsCollection.Keys.Max(); } else { minInColumnsCollection = 1; maxInColumnsCollection = 0; } var worksheetStyleId = context.SharedStyles[xlWorksheet.GetStyleId()].StyleId; if (minInColumnsCollection > 1) { UInt32Value min = 1; UInt32Value max = (UInt32)(minInColumnsCollection - 1); for (var co = min; co <= max; co++) { var column = new Column { Min = co, Max = co, Style = worksheetStyleId, Width = worksheetColumnWidth, CustomWidth = true }; UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax); } } for (var co = minInColumnsCollection; co <= maxInColumnsCollection; co++) { UInt32 styleId; Double columnWidth; var isHidden = false; var collapsed = false; var outlineLevel = 0; if (xlWorksheet.Internals.ColumnsCollection.ContainsKey(co)) { styleId = context.SharedStyles[xlWorksheet.Internals.ColumnsCollection[co].GetStyleId()].StyleId; columnWidth = GetColumnWidth(xlWorksheet.Internals.ColumnsCollection[co].Width); isHidden = xlWorksheet.Internals.ColumnsCollection[co].IsHidden; collapsed = xlWorksheet.Internals.ColumnsCollection[co].Collapsed; outlineLevel = xlWorksheet.Internals.ColumnsCollection[co].OutlineLevel; } else { styleId = context.SharedStyles[xlWorksheet.GetStyleId()].StyleId; columnWidth = worksheetColumnWidth; } var column = new Column { Min = (UInt32)co, Max = (UInt32)co, Style = styleId, Width = columnWidth, CustomWidth = true }; if (isHidden) column.Hidden = true; if (collapsed) column.Collapsed = true; if (outlineLevel > 0) column.OutlineLevel = (byte)outlineLevel; UpdateColumn(column, columns, sheetColumnsByMin); //, sheetColumnsByMax); } var collection = maxInColumnsCollection; foreach ( var col in columns.Elements<Column>().Where(c => c.Min > (UInt32)(collection)).OrderBy( c => c.Min.Value)) { col.Style = worksheetStyleId; col.Width = worksheetColumnWidth; col.CustomWidth = true; if ((Int32)col.Max.Value > maxInColumnsCollection) maxInColumnsCollection = (Int32)col.Max.Value; } if (maxInColumnsCollection < XLHelper.MaxColumnNumber && !xlWorksheet.Style.Equals(DefaultStyle)) { var column = new Column { Min = (UInt32)(maxInColumnsCollection + 1), Max = (UInt32)(XLHelper.MaxColumnNumber), Style = worksheetStyleId, Width = worksheetColumnWidth, CustomWidth = true }; columns.AppendChild(column); } CollapseColumns(columns, sheetColumnsByMin); if (!columns.Any()) { worksheetPart.Worksheet.RemoveAllChildren<Columns>(); cm.SetElement(XLWSContentManager.XLWSContents.Columns, null); } } #endregion #region SheetData if (!worksheetPart.Worksheet.Elements<SheetData>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetData); worksheetPart.Worksheet.InsertAfter(new SheetData(), previousElement); } var sheetData = worksheetPart.Worksheet.Elements<SheetData>().First(); cm.SetElement(XLWSContentManager.XLWSContents.SheetData, sheetData); var cellsByRow = new Dictionary<Int32, List<IXLCell>>(); foreach (var c in xlWorksheet.Internals.CellsCollection.GetCells()) { var rowNum = c.Address.RowNumber; if (!cellsByRow.ContainsKey(rowNum)) cellsByRow.Add(rowNum, new List<IXLCell>()); cellsByRow[rowNum].Add(c); } var lastRow = 0; var sheetDataRows = sheetData.Elements<Row>().ToDictionary(r => r.RowIndex == null ? ++lastRow : (Int32)r.RowIndex.Value, r => r); foreach ( var r in xlWorksheet.Internals.RowsCollection.Deleted.Where(r => sheetDataRows.ContainsKey(r.Key))) { sheetData.RemoveChild(sheetDataRows[r.Key]); sheetDataRows.Remove(r.Key); xlWorksheet.Internals.CellsCollection.Deleted.RemoveWhere(d => d.Row == r.Key); } var distinctRows = cellsByRow.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys); var noRows = (sheetData.Elements<Row>().FirstOrDefault() == null); foreach (var distinctRow in distinctRows.OrderBy(r => r)) { Row row; // = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex.Value == (UInt32)distinctRow); if (sheetDataRows.ContainsKey(distinctRow)) row = sheetDataRows[distinctRow]; else { row = new Row {RowIndex = (UInt32)distinctRow}; if (noRows) { sheetData.AppendChild(row); noRows = false; } else { if (sheetDataRows.Any(r => r.Key > row.RowIndex.Value)) { var minRow = sheetDataRows.Where(r => r.Key > (Int32)row.RowIndex.Value).Min(r => r.Key); var rowBeforeInsert = sheetDataRows[minRow]; sheetData.InsertBefore(row, rowBeforeInsert); } else sheetData.AppendChild(row); } } if (maxColumn > 0) row.Spans = new ListValue<StringValue> {InnerText = "1:" + maxColumn.ToStringLookup()}; row.Height = null; row.CustomHeight = null; row.Hidden = null; row.StyleIndex = null; row.CustomFormat = null; row.Collapsed = null; if (xlWorksheet.Internals.RowsCollection.ContainsKey(distinctRow)) { var thisRow = xlWorksheet.Internals.RowsCollection[distinctRow]; if (thisRow.HeightChanged) { row.Height = thisRow.Height; row.CustomHeight = true; row.CustomFormat = true; } if (thisRow.GetStyleId() != xlWorksheet.GetStyleId()) { row.StyleIndex = context.SharedStyles[thisRow.GetStyleId()].StyleId; row.CustomFormat = true; } if (thisRow.IsHidden) row.Hidden = true; if (thisRow.Collapsed) row.Collapsed = true; if (thisRow.OutlineLevel > 0) row.OutlineLevel = (byte)thisRow.OutlineLevel; } var lastCell = 0; var cellsByReference = row.Elements<Cell>().ToDictionary(c => c.CellReference == null ? XLHelper.GetColumnLetterFromNumber( ++lastCell) + distinctRow : c.CellReference.Value, c => c); foreach (var c in xlWorksheet.Internals.CellsCollection.Deleted.ToList()) { var key = XLHelper.GetColumnLetterFromNumber(c.Column) + c.Row.ToStringLookup(); if (!cellsByReference.ContainsKey(key)) continue; row.RemoveChild(cellsByReference[key]); xlWorksheet.Internals.CellsCollection.Deleted.Remove(c); } if (!cellsByRow.ContainsKey(distinctRow)) continue; var isNewRow = !row.Elements<Cell>().Any(); var mRows = row.Elements<Cell>().ToDictionary(c => XLHelper.GetColumnNumberFromAddress(c.CellReference.Value), c => c); foreach (var opCell in cellsByRow[distinctRow] .OrderBy(c => c.Address.ColumnNumber) .Select(c => (XLCell)c)) { var styleId = context.SharedStyles[opCell.GetStyleId()].StyleId; var dataType = opCell.DataType; var cellReference = (opCell.Address).GetTrimmedAddress(); Cell cell; if (cellsByReference.ContainsKey(cellReference)) cell = cellsByReference[cellReference]; else { cell = new Cell {CellReference = new StringValue(cellReference)}; if (isNewRow) row.AppendChild(cell); else { var newColumn = XLHelper.GetColumnNumberFromAddress(cellReference); Cell cellBeforeInsert = null; int[] lastCo = {Int32.MaxValue}; foreach (var c in mRows.Where(kp => kp.Key > newColumn).Where(c => lastCo[0] > c.Key)) { cellBeforeInsert = c.Value; lastCo[0] = c.Key; } if (cellBeforeInsert == null) row.AppendChild(cell); else row.InsertBefore(cell, cellBeforeInsert); } } cell.StyleIndex = styleId; var formula = opCell.FormulaA1; if (opCell.HasFormula) { if (formula.StartsWith("{")) { formula = formula.Substring(1, formula.Length - 2); var f = new CellFormula {FormulaType = CellFormulaValues.Array}; if (opCell.FormulaReference.FirstAddress.Equals(opCell.Address)) { f.Text = formula; f.Reference = opCell.FormulaReference.ToStringRelative(); } cell.CellFormula = f; } else cell.CellFormula = new CellFormula(formula); cell.CellValue = null; } else { cell.CellFormula = null; cell.DataType = opCell.DataType == XLCellValues.DateTime ? null : GetCellValue(opCell); var cellValue = new CellValue(); if (dataType == XLCellValues.Text) { if (opCell.InnerText.Length == 0) cell.CellValue = null; else { if (opCell.ShareString) { cellValue.Text = opCell.SharedStringId.ToString(); cell.CellValue = cellValue; } else { var text = opCell.GetString(); var t = new Text(text); if (text.PreserveSpaces()) t.Space = SpaceProcessingModeValues.Preserve; cell.InlineString = new InlineString {Text = t}; } } } else if (dataType == XLCellValues.TimeSpan) { var timeSpan = opCell.GetTimeSpan(); cellValue.Text = XLCell.BaseDate.Add(timeSpan).ToOADate().ToString(CultureInfo.InvariantCulture); cell.CellValue = cellValue; } else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) { if (!XLHelper.IsNullOrWhiteSpace(opCell.InnerText)) cellValue.Text = Double.Parse(opCell.InnerText).ToString(CultureInfo.InvariantCulture); cell.CellValue = cellValue; } else { cellValue.Text = opCell.InnerText; cell.CellValue = cellValue; } } } xlWorksheet.Internals.CellsCollection.Deleted.RemoveWhere(d => d.Row == distinctRow); } foreach ( var r in xlWorksheet.Internals.CellsCollection.Deleted.Select(c => c.Row).Distinct().Where( sheetDataRows.ContainsKey)) { sheetData.RemoveChild(sheetDataRows[r]); sheetDataRows.Remove(r); } #endregion #region SheetProtection if (xlWorksheet.Protection.Protected) { if (!worksheetPart.Worksheet.Elements<SheetProtection>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetProtection); worksheetPart.Worksheet.InsertAfter(new SheetProtection(), previousElement); } var sheetProtection = worksheetPart.Worksheet.Elements<SheetProtection>().First(); cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, sheetProtection); var protection = xlWorksheet.Protection; sheetProtection.Sheet = protection.Protected; if (!XLHelper.IsNullOrWhiteSpace(protection.PasswordHash)) sheetProtection.Password = protection.PasswordHash; sheetProtection.FormatCells = GetBooleanValue(!protection.FormatCells, true); sheetProtection.FormatColumns = GetBooleanValue(!protection.FormatColumns, true); sheetProtection.FormatRows = GetBooleanValue(!protection.FormatRows, true); sheetProtection.InsertColumns = GetBooleanValue(!protection.InsertColumns, true); sheetProtection.InsertHyperlinks = GetBooleanValue(!protection.InsertHyperlinks, true); sheetProtection.InsertRows = GetBooleanValue(!protection.InsertRows, true); sheetProtection.DeleteColumns = GetBooleanValue(!protection.DeleteColumns, true); sheetProtection.DeleteRows = GetBooleanValue(!protection.DeleteRows, true); sheetProtection.AutoFilter = GetBooleanValue(!protection.AutoFilter, true); sheetProtection.PivotTables = GetBooleanValue(!protection.PivotTables, true); sheetProtection.Sort = GetBooleanValue(!protection.Sort, true); sheetProtection.SelectLockedCells = GetBooleanValue(!protection.SelectLockedCells, false); sheetProtection.SelectUnlockedCells = GetBooleanValue(!protection.SelectUnlockedCells, false); } else { worksheetPart.Worksheet.RemoveAllChildren<SheetProtection>(); cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, null); } #endregion #region AutoFilter worksheetPart.Worksheet.RemoveAllChildren<AutoFilter>(); if (xlWorksheet.AutoFilter.Enabled) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.AutoFilter); worksheetPart.Worksheet.InsertAfter(new AutoFilter(), previousElement); var autoFilter = worksheetPart.Worksheet.Elements<AutoFilter>().First(); cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, autoFilter); PopulateAutoFilter(xlWorksheet.AutoFilter, autoFilter); } else { cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, null); } #endregion #region MergeCells if ((xlWorksheet).Internals.MergedRanges.Any()) { if (!worksheetPart.Worksheet.Elements<MergeCells>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.MergeCells); worksheetPart.Worksheet.InsertAfter(new MergeCells(), previousElement); } var mergeCells = worksheetPart.Worksheet.Elements<MergeCells>().First(); cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, mergeCells); mergeCells.RemoveAllChildren<MergeCell>(); foreach (var mergeCell in (xlWorksheet).Internals.MergedRanges.Select( m => m.RangeAddress.FirstAddress.ToString() + ":" + m.RangeAddress.LastAddress.ToString()).Select( merged => new MergeCell {Reference = merged})) mergeCells.AppendChild(mergeCell); mergeCells.Count = (UInt32)mergeCells.Count(); } else { worksheetPart.Worksheet.RemoveAllChildren<MergeCells>(); cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, null); } #endregion #region Conditional Formatting if (!xlWorksheet.ConditionalFormats.Any()) { worksheetPart.Worksheet.RemoveAllChildren<ConditionalFormatting>(); cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, null); } else { worksheetPart.Worksheet.RemoveAllChildren<ConditionalFormatting>(); var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ConditionalFormatting); //if (!worksheetPart.Worksheet.Elements<ConditionalFormatting>().Any()) //{ // var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ConditionalFormatting); // worksheetPart.Worksheet.InsertAfter(new ConditionalFormatting(), previousElement); //} //var conditionalFormats = worksheetPart.Worksheet.Elements<ConditionalFormatting>().First(); //cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormats); ////conditionalFormats.RemoveAllChildren<ConditionalFormat>(); var priority = 0; foreach (var cf in xlWorksheet.ConditionalFormats) { priority++; var conditionalFormatting = new ConditionalFormatting { SequenceOfReferences = new ListValue<StringValue> {InnerText = cf.Range.RangeAddress.ToStringRelative(false)} }; conditionalFormatting.Append(XLCFConverters.Convert(cf, priority, context)); worksheetPart.Worksheet.InsertAfter(conditionalFormatting, previousElement); previousElement = conditionalFormatting; cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormatting); } } #endregion #region DataValidations if (!xlWorksheet.DataValidations.Any(d => d.IsDirty())) { worksheetPart.Worksheet.RemoveAllChildren<DataValidations>(); cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, null); } else { if (!worksheetPart.Worksheet.Elements<DataValidations>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.DataValidations); worksheetPart.Worksheet.InsertAfter(new DataValidations(), previousElement); } var dataValidations = worksheetPart.Worksheet.Elements<DataValidations>().First(); cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, dataValidations); dataValidations.RemoveAllChildren<DataValidation>(); foreach (var dv in xlWorksheet.DataValidations) { var sequence = dv.Ranges.Aggregate(String.Empty, (current, r) => current + (r.RangeAddress + " ")); if (sequence.Length > 0) sequence = sequence.Substring(0, sequence.Length - 1); var dataValidation = new DataValidation { AllowBlank = dv.IgnoreBlanks, Formula1 = new Formula1(dv.MinValue), Formula2 = new Formula2(dv.MaxValue), Type = dv.AllowedValues.ToOpenXml(), ShowErrorMessage = dv.ShowErrorMessage, Prompt = dv.InputMessage, PromptTitle = dv.InputTitle, ErrorTitle = dv.ErrorTitle, Error = dv.ErrorMessage, ShowDropDown = !dv.InCellDropdown, ShowInputMessage = dv.ShowInputMessage, ErrorStyle = dv.ErrorStyle.ToOpenXml(), Operator = dv.Operator.ToOpenXml(), SequenceOfReferences = new ListValue<StringValue> {InnerText = sequence} }; dataValidations.AppendChild(dataValidation); } dataValidations.Count = (UInt32)xlWorksheet.DataValidations.Count(); } #endregion #region Hyperlinks var relToRemove = worksheetPart.HyperlinkRelationships.ToList(); relToRemove.ForEach(worksheetPart.DeleteReferenceRelationship); if (!xlWorksheet.Hyperlinks.Any()) { worksheetPart.Worksheet.RemoveAllChildren<Hyperlinks>(); cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, null); } else { if (!worksheetPart.Worksheet.Elements<Hyperlinks>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Hyperlinks); worksheetPart.Worksheet.InsertAfter(new Hyperlinks(), previousElement); } var hyperlinks = worksheetPart.Worksheet.Elements<Hyperlinks>().First(); cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, hyperlinks); hyperlinks.RemoveAllChildren<Hyperlink>(); foreach (var hl in xlWorksheet.Hyperlinks) { Hyperlink hyperlink; if (hl.IsExternal) { var rId = context.RelIdGenerator.GetNext(RelType.Workbook); hyperlink = new Hyperlink {Reference = hl.Cell.Address.ToString(), Id = rId}; worksheetPart.AddHyperlinkRelationship(hl.ExternalAddress, true, rId); } else { hyperlink = new Hyperlink { Reference = hl.Cell.Address.ToString(), Location = hl.InternalAddress, Display = hl.Cell.GetFormattedString() }; } if (!XLHelper.IsNullOrWhiteSpace(hl.Tooltip)) hyperlink.Tooltip = hl.Tooltip; hyperlinks.AppendChild(hyperlink); } } #endregion #region PrintOptions if (!worksheetPart.Worksheet.Elements<PrintOptions>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PrintOptions); worksheetPart.Worksheet.InsertAfter(new PrintOptions(), previousElement); } var printOptions = worksheetPart.Worksheet.Elements<PrintOptions>().First(); cm.SetElement(XLWSContentManager.XLWSContents.PrintOptions, printOptions); printOptions.HorizontalCentered = xlWorksheet.PageSetup.CenterHorizontally; printOptions.VerticalCentered = xlWorksheet.PageSetup.CenterVertically; printOptions.Headings = xlWorksheet.PageSetup.ShowRowAndColumnHeadings; printOptions.GridLines = xlWorksheet.PageSetup.ShowGridlines; #endregion #region PageMargins if (!worksheetPart.Worksheet.Elements<PageMargins>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageMargins); worksheetPart.Worksheet.InsertAfter(new PageMargins(), previousElement); } var pageMargins = worksheetPart.Worksheet.Elements<PageMargins>().First(); cm.SetElement(XLWSContentManager.XLWSContents.PageMargins, pageMargins); pageMargins.Left = xlWorksheet.PageSetup.Margins.Left; pageMargins.Right = xlWorksheet.PageSetup.Margins.Right; pageMargins.Top = xlWorksheet.PageSetup.Margins.Top; pageMargins.Bottom = xlWorksheet.PageSetup.Margins.Bottom; pageMargins.Header = xlWorksheet.PageSetup.Margins.Header; pageMargins.Footer = xlWorksheet.PageSetup.Margins.Footer; #endregion #region PageSetup if (!worksheetPart.Worksheet.Elements<PageSetup>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageSetup); worksheetPart.Worksheet.InsertAfter(new PageSetup(), previousElement); } var pageSetup = worksheetPart.Worksheet.Elements<PageSetup>().First(); cm.SetElement(XLWSContentManager.XLWSContents.PageSetup, pageSetup); pageSetup.Orientation = xlWorksheet.PageSetup.PageOrientation.ToOpenXml(); pageSetup.PaperSize = (UInt32)xlWorksheet.PageSetup.PaperSize; pageSetup.BlackAndWhite = xlWorksheet.PageSetup.BlackAndWhite; pageSetup.Draft = xlWorksheet.PageSetup.DraftQuality; pageSetup.PageOrder = xlWorksheet.PageSetup.PageOrder.ToOpenXml(); pageSetup.CellComments = xlWorksheet.PageSetup.ShowComments.ToOpenXml(); pageSetup.Errors = xlWorksheet.PageSetup.PrintErrorValue.ToOpenXml(); if (xlWorksheet.PageSetup.FirstPageNumber > 0) { pageSetup.FirstPageNumber = (UInt32)xlWorksheet.PageSetup.FirstPageNumber; pageSetup.UseFirstPageNumber = true; } else { pageSetup.FirstPageNumber = null; pageSetup.UseFirstPageNumber = null; } if (xlWorksheet.PageSetup.HorizontalDpi > 0) pageSetup.HorizontalDpi = (UInt32)xlWorksheet.PageSetup.HorizontalDpi; else pageSetup.HorizontalDpi = null; if (xlWorksheet.PageSetup.VerticalDpi > 0) pageSetup.VerticalDpi = (UInt32)xlWorksheet.PageSetup.VerticalDpi; else pageSetup.VerticalDpi = null; if (xlWorksheet.PageSetup.Scale > 0) { pageSetup.Scale = (UInt32)xlWorksheet.PageSetup.Scale; pageSetup.FitToWidth = null; pageSetup.FitToHeight = null; } else { pageSetup.Scale = null; if (xlWorksheet.PageSetup.PagesWide > 0) pageSetup.FitToWidth = (UInt32)xlWorksheet.PageSetup.PagesWide; else pageSetup.FitToWidth = 0; if (xlWorksheet.PageSetup.PagesTall > 0) pageSetup.FitToHeight = (UInt32)xlWorksheet.PageSetup.PagesTall; else pageSetup.FitToHeight = 0; } #endregion #region HeaderFooter var headerFooter = worksheetPart.Worksheet.Elements<HeaderFooter>().FirstOrDefault(); if (headerFooter == null) headerFooter = new HeaderFooter(); else worksheetPart.Worksheet.RemoveAllChildren<HeaderFooter>(); { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.HeaderFooter); worksheetPart.Worksheet.InsertAfter(headerFooter, previousElement); cm.SetElement(XLWSContentManager.XLWSContents.HeaderFooter, headerFooter); } if (((XLHeaderFooter)xlWorksheet.PageSetup.Header).Changed || ((XLHeaderFooter)xlWorksheet.PageSetup.Footer).Changed) { //var headerFooter = worksheetPart.Worksheet.Elements<HeaderFooter>().First(); headerFooter.RemoveAllChildren(); headerFooter.ScaleWithDoc = xlWorksheet.PageSetup.ScaleHFWithDocument; headerFooter.AlignWithMargins = xlWorksheet.PageSetup.AlignHFWithMargins; headerFooter.DifferentFirst = xlWorksheet.PageSetup.DifferentFirstPageOnHF; headerFooter.DifferentOddEven = xlWorksheet.PageSetup.DifferentOddEvenPagesOnHF; var oddHeader = new OddHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.OddPages)); headerFooter.AppendChild(oddHeader); var oddFooter = new OddFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.OddPages)); headerFooter.AppendChild(oddFooter); var evenHeader = new EvenHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.EvenPages)); headerFooter.AppendChild(evenHeader); var evenFooter = new EvenFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.EvenPages)); headerFooter.AppendChild(evenFooter); var firstHeader = new FirstHeader(xlWorksheet.PageSetup.Header.GetText(XLHFOccurrence.FirstPage)); headerFooter.AppendChild(firstHeader); var firstFooter = new FirstFooter(xlWorksheet.PageSetup.Footer.GetText(XLHFOccurrence.FirstPage)); headerFooter.AppendChild(firstFooter); } #endregion #region RowBreaks if (!worksheetPart.Worksheet.Elements<RowBreaks>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.RowBreaks); worksheetPart.Worksheet.InsertAfter(new RowBreaks(), previousElement); } var rowBreaks = worksheetPart.Worksheet.Elements<RowBreaks>().First(); var rowBreakCount = xlWorksheet.PageSetup.RowBreaks.Count; if (rowBreakCount > 0) { rowBreaks.Count = (UInt32)rowBreakCount; rowBreaks.ManualBreakCount = (UInt32)rowBreakCount; var lastRowNum = (UInt32)xlWorksheet.RangeAddress.LastAddress.RowNumber; foreach (var break1 in xlWorksheet.PageSetup.RowBreaks.Select(rb => new Break { Id = (UInt32)rb, Max = lastRowNum, ManualPageBreak = true })) rowBreaks.AppendChild(break1); cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, rowBreaks); } else { worksheetPart.Worksheet.RemoveAllChildren<RowBreaks>(); cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, null); } #endregion #region ColumnBreaks if (!worksheetPart.Worksheet.Elements<ColumnBreaks>().Any()) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ColumnBreaks); worksheetPart.Worksheet.InsertAfter(new ColumnBreaks(), previousElement); } var columnBreaks = worksheetPart.Worksheet.Elements<ColumnBreaks>().First(); var columnBreakCount = xlWorksheet.PageSetup.ColumnBreaks.Count; if (columnBreakCount > 0) { columnBreaks.Count = (UInt32)columnBreakCount; columnBreaks.ManualBreakCount = (UInt32)columnBreakCount; var maxColumnNumber = (UInt32)xlWorksheet.RangeAddress.LastAddress.ColumnNumber; foreach (var break1 in xlWorksheet.PageSetup.ColumnBreaks.Select(cb => new Break { Id = (UInt32)cb, Max = maxColumnNumber, ManualPageBreak = true })) columnBreaks.AppendChild(break1); cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, columnBreaks); } else { worksheetPart.Worksheet.RemoveAllChildren<ColumnBreaks>(); cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, null); } #endregion #region Drawings //worksheetPart.Worksheet.RemoveAllChildren<Drawing>(); //{ // OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Drawing); // worksheetPart.Worksheet.InsertAfter(new Drawing() { Id = String.Format("rId{0}", 1) }, previousElement); //} //Drawing drawing = worksheetPart.Worksheet.Elements<Drawing>().First(); //cm.SetElement(XLWSContentManager.XLWSContents.Drawing, drawing); #endregion #region Tables worksheetPart.Worksheet.RemoveAllChildren<TableParts>(); { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts); worksheetPart.Worksheet.InsertAfter(new TableParts(), previousElement); } var tableParts = worksheetPart.Worksheet.Elements<TableParts>().First(); cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts); tableParts.Count = (UInt32)xlWorksheet.Tables.Count(); foreach ( var tablePart in from XLTable xlTable in xlWorksheet.Tables select new TablePart {Id = xlTable.RelId}) tableParts.AppendChild(tablePart); #endregion #region LegacyDrawing if (xlWorksheet.LegacyDrawingIsNew) { worksheetPart.Worksheet.RemoveAllChildren<LegacyDrawing>(); { if (!XLHelper.IsNullOrWhiteSpace(xlWorksheet.LegacyDrawingId)) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawing); worksheetPart.Worksheet.InsertAfter(new LegacyDrawing {Id = xlWorksheet.LegacyDrawingId}, previousElement); } } } #endregion #region LegacyDrawingHeaderFooter //LegacyDrawingHeaderFooter legacyHeaderFooter = worksheetPart.Worksheet.Elements<LegacyDrawingHeaderFooter>().FirstOrDefault(); //if (legacyHeaderFooter != null) //{ // worksheetPart.Worksheet.RemoveAllChildren<LegacyDrawingHeaderFooter>(); // { // var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawingHeaderFooter); // worksheetPart.Worksheet.InsertAfter(new LegacyDrawingHeaderFooter { Id = xlWorksheet.LegacyDrawingId }, // previousElement); // } //} #endregion }
public byte[] Build(SqlContext context, User currentUser, DateTime min, DateTime max, string filterUserScope, int[] filterUserIds, string filterProjectScope, int[] filterProjectIds) { var organization = context.Organizations.Find(currentUser.OrganizationId); var reportTitle = $"TIME CARDS BY DAY {min.ToString("M/d/yyyy")} thru {max.ToString("M/d/yyyy")} GENERATED {DateTime.Now.ToString("ddd, MMM d, yyyy h:mm:ss tt").ToUpperInvariant()}"; using (var stream = new MemoryStream()) using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook)) { // ------------------------------------------------------------ // Add document properties. // ------------------------------------------------------------ document.PackageProperties.Creator = "BRIZBEE"; document.PackageProperties.Created = DateTime.UtcNow; // ------------------------------------------------------------ // Add a WorkbookPart to the document. // ------------------------------------------------------------ var workbookPart1 = document.AddWorkbookPart(); workbookPart1.Workbook = new Workbook(); // ------------------------------------------------------------ // Apply stylesheet. // ------------------------------------------------------------ var workStylePart1 = workbookPart1.AddNewPart <WorkbookStylesPart>(); workStylePart1.Stylesheet = Stylesheets.Common();; // ------------------------------------------------------------ // Add a WorksheetPart to the WorkbookPart. // ------------------------------------------------------------ var worksheetPart1 = workbookPart1.AddNewPart <WorksheetPart>(); var worksheet1 = new Worksheet(); var rowBreaks1 = new RowBreaks() { Count = 0, ManualBreakCount = 0 }; var mergeCells1 = new MergeCells() { Count = 0 }; var sheetData1 = new SheetData(); // ------------------------------------------------------------ // Collect the users based on the filters. // ------------------------------------------------------------ List <User> users; if (filterUserScope.ToUpperInvariant() == "SPECIFIC") { users = context.Users .Where(u => u.OrganizationId == currentUser.OrganizationId) .Where(u => u.IsDeleted == false) .Where(u => filterUserIds.Contains(u.Id)) .OrderBy(u => u.Name) .ToList(); } else { users = context.Users .Where(u => u.OrganizationId == currentUser.OrganizationId) .Where(u => u.IsDeleted == false) .OrderBy(u => u.Name) .ToList(); } // ------------------------------------------------------------ // Collect the time cards based on the filters. // ------------------------------------------------------------ IQueryable <TimesheetEntry> timeCardsQueryable = context.TimesheetEntries .Include(t => t.Task.Job.Customer) .Include(t => t.User) .Where(t => t.User.OrganizationId == currentUser.OrganizationId) .Where(t => t.User.IsDeleted == false) .Where(t => t.EnteredAt.Date >= min.Date) .Where(t => t.EnteredAt.Date <= max.Date); // Optionally filter projects. if (filterProjectScope.ToUpperInvariant() == "SPECIFIC") { timeCardsQueryable = timeCardsQueryable .Where(t => filterProjectIds.Contains(t.Task.JobId)); } var timeCards = timeCardsQueryable.ToList(); var dates = timeCards .GroupBy(t => t.EnteredAt.Date) .Select(g => g.Key) .OrderBy(g => g.Date) .ToList(); // ------------------------------------------------------------ // Loop each date. // ------------------------------------------------------------ var rowIndex = (uint)1; foreach (var date in dates) { var timeCardsForDate = timeCards .Where(t => t.EnteredAt.Date == date.Date) .OrderBy(t => t.EnteredAt); // ------------------------------------------------------------ // Header for date cell. // ------------------------------------------------------------ var rowDate = new Row() { RowIndex = rowIndex, Height = 22D, CustomHeight = true, Spans = new ListValue <StringValue>() { InnerText = "1:1" }, StyleIndex = 4U, CustomFormat = true }; var cellDate = new Cell() { CellReference = $"A{rowIndex}", StyleIndex = 4U, DataType = CellValues.String, CellValue = new CellValue(date.ToString("D")) }; rowDate.Append(cellDate); sheetData1.Append(rowDate); // Merge the date across the row. var mergeCell0 = new MergeCell() { Reference = $"A{rowIndex}:H{rowIndex}" }; mergeCells1.Append(mergeCell0); mergeCells1.Count++; rowIndex++; // ------------------------------------------------------------ // Headers for time cards cells. // ------------------------------------------------------------ var rowHeaders = new Row() { RowIndex = rowIndex, Height = 16D, CustomHeight = true, StyleIndex = 1U, CustomFormat = true }; // Task Number var cellTaskNumberHeader = new Cell() { CellReference = $"A{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("#") }; rowHeaders.Append(cellTaskNumberHeader); // Task Name var cellTaskNameHeader = new Cell() { CellReference = $"B{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Task") }; rowHeaders.Append(cellTaskNameHeader); // Project Number var cellProjectNumberHeader = new Cell() { CellReference = $"C{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("#") }; rowHeaders.Append(cellProjectNumberHeader); // Project Name var cellProjectNameHeader = new Cell() { CellReference = $"D{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Project") }; rowHeaders.Append(cellProjectNameHeader); // Customer Number var cellCustomerNumberHeader = new Cell() { CellReference = $"E{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("#") }; rowHeaders.Append(cellCustomerNumberHeader); // Customer Name var cellCustomerNameHeader = new Cell() { CellReference = $"F{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Customer") }; rowHeaders.Append(cellCustomerNameHeader); // Notes var cellNotesHeader = new Cell() { CellReference = $"G{rowIndex}", DataType = CellValues.String, StyleIndex = 1U, CellValue = new CellValue("Notes") }; rowHeaders.Append(cellNotesHeader); // Total var cellTotalHeader = new Cell() { CellReference = $"H{rowIndex}", DataType = CellValues.String, StyleIndex = 2U, CellValue = new CellValue("Total") }; rowHeaders.Append(cellTotalHeader); sheetData1.Append(rowHeaders); rowIndex++; // ------------------------------------------------------------ // Time cards cells. // ------------------------------------------------------------ foreach (var timeCard in timeCardsForDate) { var rowTimeCard = new Row() { RowIndex = rowIndex, Height = 16D, CustomHeight = true }; // Task Number var cellTaskNumber = new Cell() { CellReference = $"A{rowIndex}", DataType = CellValues.Number, StyleIndex = 6U, CellValue = new CellValue(timeCard.Task.Number) }; rowTimeCard.Append(cellTaskNumber); // Task Name var cellTaskName = new Cell() { CellReference = $"B{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(timeCard.Task.Name) }; rowTimeCard.Append(cellTaskName); // Project Number var cellProjectNumber = new Cell() { CellReference = $"C{rowIndex}", DataType = CellValues.Number, StyleIndex = 6U, CellValue = new CellValue(timeCard.Task.Job.Number) }; rowTimeCard.Append(cellProjectNumber); // Project Name var cellProjectName = new Cell() { CellReference = $"D{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(timeCard.Task.Job.Name) }; rowTimeCard.Append(cellProjectName); // Customer Number var cellCustomerNumber = new Cell() { CellReference = $"E{rowIndex}", DataType = CellValues.Number, StyleIndex = 6U, CellValue = new CellValue(timeCard.Task.Job.Customer.Number) }; rowTimeCard.Append(cellCustomerNumber); // Customer Name var cellCustomerName = new Cell() { CellReference = $"F{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(timeCard.Task.Job.Customer.Name) }; rowTimeCard.Append(cellCustomerName); // Notes var cellNotes = new Cell() { CellReference = $"G{rowIndex}", DataType = CellValues.String, StyleIndex = 6U, CellValue = new CellValue(timeCard.Notes) }; rowTimeCard.Append(cellNotes); // Calculate the total var total = Math.Round((double)timeCard.Minutes / 60, 2).ToString("0.00"); // Total var cellTotal = new Cell() { CellReference = $"H{rowIndex}", DataType = CellValues.Number, StyleIndex = 5U, CellValue = new CellValue(total) }; rowTimeCard.Append(cellTotal); sheetData1.Append(rowTimeCard); rowIndex++; } // ------------------------------------------------------------ // Cells for date total. // ------------------------------------------------------------ var rowDateTotal = new Row() { RowIndex = rowIndex, Height = 16D, CustomHeight = true, StyleIndex = 1U, CustomFormat = true }; // Header Cell var cellDateFormatted = new Cell() { CellReference = $"A{rowIndex}", DataType = CellValues.String, StyleIndex = 2U, CellValue = new CellValue("Daily Total") }; rowDateTotal.Append(cellDateFormatted); // Calculate the total double dailyTotalMinutes = 0; foreach (var timeCard in timeCardsForDate) { dailyTotalMinutes += timeCard.Minutes; } var dailyTotal = Math.Round(dailyTotalMinutes / 60, 2).ToString("0.00"); // Total Cell var cellDateTotal = new Cell() { CellReference = $"H{rowIndex}", DataType = CellValues.Number, StyleIndex = 2U, CellValue = new CellValue(dailyTotal) }; rowDateTotal.Append(cellDateTotal); sheetData1.Append(rowDateTotal); // Merge the date across the row. var mergeCell3 = new MergeCell() { Reference = $"A{rowIndex}:G{rowIndex}" }; mergeCells1.Append(mergeCell3); mergeCells1.Count++; rowIndex++; // ------------------------------------------------------------ // Add a page break. // ------------------------------------------------------------ var rowBreak1 = new Break() { Id = rowIndex, Max = 16383U, ManualPageBreak = true }; rowBreaks1.Append(rowBreak1); rowBreaks1.ManualBreakCount++; rowBreaks1.Count++; rowIndex++; } // ------------------------------------------------------------ // Custom column width. // ------------------------------------------------------------ var columns1 = new Columns(); var column1 = new Column() { Min = 1U, Max = 1U, Width = 10D, CustomWidth = true }; var column2 = new Column() { Min = 2U, Max = 2U, Width = 28D, CustomWidth = true }; var column3 = new Column() { Min = 3U, Max = 3U, Width = 10D, CustomWidth = true }; var column4 = new Column() { Min = 4U, Max = 4U, Width = 28D, CustomWidth = true }; var column5 = new Column() { Min = 5U, Max = 5U, Width = 10D, CustomWidth = true }; var column6 = new Column() { Min = 6U, Max = 6U, Width = 28D, CustomWidth = true }; var column7 = new Column() { Min = 7U, Max = 7U, Width = 34D, CustomWidth = true }; var column8 = new Column() { Min = 8U, Max = 8U, Width = 10D, CustomWidth = true }; columns1.Append(column1); columns1.Append(column2); columns1.Append(column3); columns1.Append(column4); columns1.Append(column5); columns1.Append(column6); columns1.Append(column7); columns1.Append(column8); // ------------------------------------------------------------ // Sheet Views. // ------------------------------------------------------------ var sheetViews1 = new SheetViews(); var sheetView1 = new SheetView() { ShowGridLines = true, TabSelected = true, ZoomScaleNormal = 100U, WorkbookViewId = 0U }; var selection1 = new Selection() { ActiveCell = "A1", SequenceOfReferences = new ListValue <StringValue>() { InnerText = "A1" } }; sheetView1.Append(selection1); sheetViews1.Append(sheetView1); // ------------------------------------------------------------ // Sheet Format. // ------------------------------------------------------------ var sheetFormatProperties1 = new SheetFormatProperties() { DefaultRowHeight = 16D, DyDescent = 0.35D }; // ------------------------------------------------------------ // Page Setup. // ------------------------------------------------------------ var pageMargins1 = new PageMargins() { Left = 0.5D, Right = 0.5D, Top = 0.5D, Bottom = 0.5D, Header = 0.3D, Footer = 0.3D }; var pageSetup1 = new PageSetup() { Orientation = OrientationValues.Landscape }; // ------------------------------------------------------------ // Header and Footer. // ------------------------------------------------------------ var headerFooter1 = new HeaderFooter(); var oddHeader1 = new OddHeader(); oddHeader1.Text = reportTitle; var oddFooter1 = new OddFooter(); oddFooter1.Text = organization.Name; headerFooter1.Append(oddHeader1); headerFooter1.Append(oddFooter1); // ------------------------------------------------------------ // Build the worksheet. // ------------------------------------------------------------ worksheet1.Append(sheetViews1); worksheet1.Append(columns1); worksheet1.Append(sheetData1); // Cannot add zero merge cells. if (mergeCells1.Count != 0) { worksheet1.Append(mergeCells1); } worksheet1.Append(pageMargins1); worksheet1.Append(pageSetup1); worksheet1.Append(headerFooter1); worksheet1.Append(rowBreaks1); worksheetPart1.Worksheet = worksheet1; // ------------------------------------------------------------ // Add Sheets to the Workbook. // ------------------------------------------------------------ var sheets = workbookPart1.Workbook.AppendChild(new Sheets()); // ------------------------------------------------------------ // Append a new worksheet and associate it with the workbook. // ------------------------------------------------------------ var sheet = new Sheet() { Id = workbookPart1.GetIdOfPart(worksheetPart1), SheetId = 1, Name = "Report" }; sheets.Append(sheet); // Save and close the document. workbookPart1.Workbook.Save(); document.Close(); return(stream.ToArray()); } }