public void AddHorizontalPageBreak(Int32 row) { if (!RowBreaks.Contains(row)) { RowBreaks.Add(row); } }
/// <summary> /// 改ページ の取得 /// </summary> /// <param name="indecis"></param> /// <returns></returns> public static RowBreaks GetRowBreaks(IEnumerable <uint> indecis) { var rowBreaks = new RowBreaks(); rowBreaks.Append(indecis.Select(x => new Break { Id = x, Max = 16383, ManualPageBreak = true, }).ToArray()); var count = (uint)indecis.Count(); rowBreaks.Count = count; rowBreaks.ManualBreakCount = count; return(rowBreaks); }
public void InsertHorizontalPageBreak(int rowIndex) { rowIndex++; RowBreaks rb = WorksheetPart.Worksheet.GetFirstChild <RowBreaks>(); if (rb == null) { rb = new RowBreaks(); rb.ManualBreakCount = (UInt32Value)0; rb.Count = (UInt32Value)0; WorksheetPart.Worksheet.Append(rb); } Break rowBreak = new Break() { Id = (UInt32Value)(uint)rowIndex, Max = (UInt32Value)16383U, ManualPageBreak = true }; rb.Append(rowBreak); rb.ManualBreakCount++; rb.Count++; }
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()); } }
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()); } }
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 } }