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();
}
private static void ChangeWorksheetPart1(WorksheetPart worksheetPart1)
{
Worksheet worksheet1 = worksheetPart1.Worksheet;
HeaderFooter headerFooter1 = new HeaderFooter();
OddHeader oddHeader1 = new OddHeader();
oddHeader1.Text = "&L&G";
headerFooter1.Append(oddHeader1);
worksheet1.Append(headerFooter1);
LegacyDrawingHeaderFooter legacyDrawingHeaderFooter1 = new LegacyDrawingHeaderFooter()
{
Id = "rId2"
};
worksheet1.Append(legacyDrawingHeaderFooter1);
}
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());
}
}
