protected string GenerateLeaveReport(LeaveReportFilterDTO model)
{
#region Style Definition
SLStyle titleStyle = new SLStyle();
titleStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
titleStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
titleStyle.Font.FontSize = 16;
titleStyle.Font.FontName = "微軟正黑體";
titleStyle.Font.Bold = true;
SLStyle borderStyle = new SLStyle();
borderStyle.SetBottomBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Gray);
borderStyle.SetRightBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Gray);
SLStyle dateRangeStyle = new SLStyle();
dateRangeStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
dateRangeStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
dateRangeStyle.Font.FontSize = 14;
dateRangeStyle.Font.FontName = "微軟正黑體";
SLStyle columnHeaderStyle = new SLStyle();
columnHeaderStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
columnHeaderStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
columnHeaderStyle.Font.FontSize = 12;
columnHeaderStyle.Font.FontName = "微軟正黑體";
SLStyle totalColumnStyle = new SLStyle();
totalColumnStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
totalColumnStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right);
totalColumnStyle.Font.FontSize = 12;
totalColumnStyle.Font.FontName = "微軟正黑體";
totalColumnStyle.SetRightBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, SLThemeColorIndexValues.Dark1Color);
SLStyle holidayTitleStyle = new SLStyle();
holidayTitleStyle.SetFontColor(System.Drawing.Color.Red);
holidayTitleStyle.Font.FontSize = 12;
holidayTitleStyle.Font.FontName = "微軟正黑體";
SLStyle holidayBackgroundStyle = new SLStyle();
holidayBackgroundStyle.Fill.SetPattern(DocumentFormat.OpenXml.Spreadsheet.PatternValues.Solid, System.Drawing.Color.FromArgb(255, 247, 247), System.Drawing.Color.FromArgb(255, 247, 247));
SLStyle rowHeaderStyle = new SLStyle();
rowHeaderStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
rowHeaderStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
rowHeaderStyle.Font.FontSize = 12;
rowHeaderStyle.Font.FontName = "微軟正黑體";
SLStyle totalRowStyle = new SLStyle();
totalRowStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
totalRowStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right);
totalRowStyle.Font.FontSize = 12;
totalRowStyle.Font.FontName = "微軟正黑體";
totalRowStyle.SetBottomBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, SLThemeColorIndexValues.Dark1Color);
SLStyle pmLeaveStyle = new SLStyle();
pmLeaveStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
pmLeaveStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
pmLeaveStyle.Fill.SetPattern(DocumentFormat.OpenXml.Spreadsheet.PatternValues.Solid, System.Drawing.Color.FromArgb(94, 219, 149), System.Drawing.Color.FromArgb(94, 219, 149));
pmLeaveStyle.Font.FontSize = 10;
pmLeaveStyle.Font.FontName = "Arial";
pmLeaveStyle.Font.FontColor = System.Drawing.Color.White;
pmLeaveStyle.Font.Bold = true;
SLStyle amLeaveStyle = new SLStyle();
amLeaveStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
amLeaveStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
amLeaveStyle.Fill.SetPattern(DocumentFormat.OpenXml.Spreadsheet.PatternValues.Solid, System.Drawing.Color.FromArgb(40, 191, 231), System.Drawing.Color.FromArgb(40, 191, 231));
amLeaveStyle.Font.FontSize = 10;
amLeaveStyle.Font.FontName = "Arial";
amLeaveStyle.Font.FontColor = System.Drawing.Color.White;
amLeaveStyle.Font.Bold = true;
SLStyle dayLeaveStyle = new SLStyle();
dayLeaveStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
dayLeaveStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
dayLeaveStyle.Fill.SetPattern(DocumentFormat.OpenXml.Spreadsheet.PatternValues.Solid, System.Drawing.Color.FromArgb(204, 101, 219), System.Drawing.Color.FromArgb(204, 101, 219));
dayLeaveStyle.Font.FontSize = 10;
dayLeaveStyle.Font.FontName = "Arial";
dayLeaveStyle.Font.FontColor = System.Drawing.Color.White;
dayLeaveStyle.Font.Bold = true;
#endregion Style Definition
string returnFilePath = null;
int _dataHeaderColumn = 1;
int _dataHeaderRow = 4;
int _totalHeaderColumn = 2;
int _totalHeaderRow = 5;
List<int> holidayColumnIndex = new List<int>();
try
{
DateTime fromDate = model.fromDate.HasValue ? model.fromDate.Value : DateTime.Today;
DateTime toDate = model.toDate.HasValue ? model.toDate.Value : fromDate.AddDays(6);
if (toDate < fromDate) return null;
Dictionary<string, int> dateColumnIndex = new Dictionary<string, int>();
Dictionary<int, int> technicianRowIndex = new Dictionary<int, int>();
SLDocument document = new SLDocument();
#region Establish Column Index
IHolidayService holidayService = AutoSessionServiceFactory.GetHolidayService(ApplicationSetting.Current.DefaultConnectionString);
List<DateTime> holidays = holidayService.GetHolidays(fromDate, toDate);
DateTime currentDate = fromDate;
int currentColumn = _totalHeaderColumn + 1;
while (currentDate <= toDate)
{
dateColumnIndex.Add(currentDate.ToString("yyyy-MM-dd"), currentColumn);
document.SetCellValue(_dataHeaderRow, currentColumn, currentDate.ToString("dd MMM, ddd"));
document.SetCellStyle(_dataHeaderRow, currentColumn, columnHeaderStyle);
if (holidays.Contains(currentDate) || currentDate.DayOfWeek == DayOfWeek.Sunday)
{
document.SetCellStyle(_dataHeaderRow, currentColumn, holidayTitleStyle);
holidayColumnIndex.Add(currentColumn);
}
currentColumn++;
currentDate = currentDate.AddDays(1);
}
#endregion Establish Column Index
#region Establish Row Index
int currentRow = _totalHeaderRow + 1;
ITechnicianService technicianService = AutoSessionServiceFactory.GetTechnicianService(ApplicationSetting.Current.DefaultConnectionString);
List<LeaveReportTechnicianDTO> technicians = technicianService.GetLeaveReportTechnicians(fromDate, toDate).ToList();
if (technicians != null)
{
foreach (LeaveReportTechnicianDTO technician in technicians)
{
technicianRowIndex.Add(technician.nID, currentRow);
document.SetCellValue(currentRow, _dataHeaderColumn, technician.sName);
document.SetCellStyle(currentRow, _dataHeaderColumn, rowHeaderStyle);
currentRow++;
}
}
#endregion Establish Row Index
if (dateColumnIndex.Count() == 0 || technicianRowIndex.Count() == 0) return null;
#region Title Section
document.MergeWorksheetCells(1, 1, 1, dateColumnIndex.Count() + _totalHeaderColumn);
document.SetCellValue(1, 1, "請假報告表");
document.SetCellStyle(1, 1, titleStyle);
document.SetCellValue(2, 2, "由");
document.SetCellValue(2, 3, fromDate.ToString("yyyy/MM/dd"));
document.SetCellValue(2, 4, "至");
document.SetCellValue(2, 5, toDate.ToString("yyyy/MM/dd"));
document.SetCellStyle(2, 2, 2, 5, dateRangeStyle);
document.SetCellValue(_totalHeaderRow, _totalHeaderColumn, "Total");
#endregion Title Section
#region Fill Leaves
ILeaveService leaveService = AutoSessionServiceFactory.GetLeaveService(ApplicationSetting.Current.DefaultConnectionString);
List<LeaveDTO> leaves = leaveService.GetLeavesByDateRange(fromDate, toDate).ToList();
if (leaves != null && leaves.Count() > 0)
{
foreach (LeaveDTO leave in leaves)
{
int rowIndex = technicianRowIndex[leave.nTechnicianID];
int columnIndex = dateColumnIndex[leave.dDate.ToString("yyyy-MM-dd")];
if (leave.bIsAM.HasValue && leave.bIsAM.Value)
{
document.SetCellValue(rowIndex, columnIndex, "AM");
document.SetCellStyle(rowIndex, columnIndex, amLeaveStyle);
}
else if (leave.bIsPM.HasValue && leave.bIsPM.Value)
{
document.SetCellValue(rowIndex, columnIndex, "PM");
document.SetCellStyle(rowIndex, columnIndex, pmLeaveStyle);
}
else if (leave.bIsFullDay.HasValue && leave.bIsFullDay.Value)
{
document.SetCellValue(rowIndex, columnIndex, "1 Day");
document.SetCellStyle(rowIndex, columnIndex, dayLeaveStyle);
}
}
}
#endregion Fill Leaves
#region Set Up Total Formula
string formulaStringFormat = "=COUNTIF({0}, \"1 Day\") + COUNTIF({0}, \"AM\")*0.5 + COUNTIF({0}, \"PM\")*0.5";
int formulaRow = _totalHeaderRow;
for (int i = _totalHeaderColumn + 1; i <= _totalHeaderColumn + dateColumnIndex.Count; i++)
{
string cellRange = SLConvert.ToCellRange(_totalHeaderRow + 1, i, _totalHeaderRow + technicianRowIndex.Count, i);
document.SetCellValue(formulaRow, i, string.Format(formulaStringFormat, cellRange));
document.SetCellStyle(formulaRow, i, totalRowStyle);
}
int formulaColumn = _totalHeaderColumn;
for (int i = _totalHeaderRow + 1; i <= _totalHeaderRow + technicianRowIndex.Count; i++)
{
string cellRange = SLConvert.ToCellRange(i, _totalHeaderColumn + 1, i, _totalHeaderColumn + dateColumnIndex.Count);
document.SetCellValue(i, formulaColumn, string.Format(formulaStringFormat, cellRange));
document.SetCellStyle(i, formulaColumn, totalColumnStyle);
}
#endregion Set Up Total Formula
#region Draw Borders
document.SetCellStyle(_totalHeaderRow + 1, _totalHeaderColumn + 1, _totalHeaderRow + technicianRowIndex.Count(), _totalHeaderColumn + dateColumnIndex.Count(), borderStyle);
#endregion Draw Bottom Border
#region Fill Holiday Column Background
foreach (int columnIndex in holidayColumnIndex)
{
document.SetCellStyle(_totalHeaderRow + 1, columnIndex, _totalHeaderRow + technicianRowIndex.Count, columnIndex, holidayBackgroundStyle);
}
#endregion Fill Holiday Column Background
#region Auto Fit Column
for (int i = 1; i <= _totalHeaderColumn + dateColumnIndex.Count(); i++)
{
document.AutoFitColumn(i);
}
#endregion Auto Fit Column
#region Freeze Pane
document.FreezePanes(_totalHeaderRow, _totalHeaderColumn);
#endregion Freeze Pane
#region Print Setting
SLPageSettings pageSetting = new SLPageSettings();
pageSetting.PaperSize = SLPaperSizeValues.A4Paper;
pageSetting.Orientation = DocumentFormat.OpenXml.Spreadsheet.OrientationValues.Landscape;
pageSetting.ScalePage(1, 100);
pageSetting.TopMargin = pageSetting.BottomMargin = 0.3;
pageSetting.LeftMargin = pageSetting.RightMargin = 0.3;
document.SetPageSettings(pageSetting);
document.SetDefinedName("Print_Titles", "=Sheet1!$1:$" + _dataHeaderRow, "", "Sheet1");
#endregion Print Setting
returnFilePath = string.Format(@"{0}\LeaveReport_{1}.xlsx", _leaveReportTempFolderPath, DateTime.Now.ToString("yyyyMMddHHmmss"));
document.SaveAs(returnFilePath);
}
catch
{
returnFilePath = null;
}
return returnFilePath;
}
internal SLPageSettings Clone()
{
SLPageSettings ps = new SLPageSettings(this.listThemeColors, this.listIndexedColors);
ps.SheetProperties = this.SheetProperties.Clone();
ps.bShowGridLines = this.bShowGridLines;
ps.bShowRowColumnHeaders = this.bShowRowColumnHeaders;
ps.bShowRuler = this.bShowRuler;
ps.vView = this.vView;
ps.iZoomScale = this.iZoomScale;
ps.iZoomScaleNormal = this.iZoomScaleNormal;
ps.iZoomScalePageLayoutView = this.iZoomScalePageLayoutView;
ps.PrintHorizontalCentered = this.PrintHorizontalCentered;
ps.PrintVerticalCentered = this.PrintVerticalCentered;
ps.PrintHeadings = this.PrintHeadings;
ps.PrintGridLines = this.PrintGridLines;
ps.PrintGridLinesSet = this.PrintGridLinesSet;
ps.HasPageMargins = this.HasPageMargins;
ps.fLeftMargin = this.fLeftMargin;
ps.fRightMargin = this.fRightMargin;
ps.fTopMargin = this.fTopMargin;
ps.fBottomMargin = this.fBottomMargin;
ps.fHeaderMargin = this.fHeaderMargin;
ps.fFooterMargin = this.fFooterMargin;
ps.PaperSize = this.PaperSize;
ps.iFirstPageNumber = this.iFirstPageNumber;
ps.iScale = this.iScale;
ps.iFitToWidth = this.iFitToWidth;
ps.iFitToHeight = this.iFitToHeight;
ps.PageOrder = this.PageOrder;
ps.Orientation = this.Orientation;
ps.UsePrinterDefaults = this.UsePrinterDefaults;
ps.BlackAndWhite = this.BlackAndWhite;
ps.Draft = this.Draft;
ps.CellComments = this.CellComments;
ps.Errors = this.Errors;
ps.HorizontalDpi = this.HorizontalDpi;
ps.VerticalDpi = this.VerticalDpi;
ps.iCopies = this.iCopies;
ps.OddHeaderText = this.OddHeaderText;
ps.OddFooterText = this.OddFooterText;
ps.EvenHeaderText = this.EvenHeaderText;
ps.EvenFooterText = this.EvenFooterText;
ps.FirstHeaderText = this.FirstHeaderText;
ps.FirstFooterText = this.FirstFooterText;
ps.DifferentOddEvenPages = this.DifferentOddEvenPages;
ps.DifferentFirstPage = this.DifferentFirstPage;
ps.ScaleWithDocument = this.ScaleWithDocument;
ps.AlignWithMargins = this.AlignWithMargins;
return ps;
}
/// <summary>
/// Get the page settings of sheet.
/// </summary>
/// <param name="SheetName">The name of the sheet.</param>
/// <returns>An SLPageSettings object with the page settings of the specified sheet.</returns>
public SLPageSettings GetPageSettings(string SheetName)
{
if (SheetName.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase))
{
return slws.PageSettings.Clone();
}
SLPageSettings ps = new SLPageSettings(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
bool bSheetFound = false;
SLSheet sheet = new SLSheet(string.Empty, 0, string.Empty, SLSheetType.Unknown);
foreach (SLSheet s in slwb.Sheets)
{
if (s.Name.Equals(SheetName, StringComparison.OrdinalIgnoreCase))
{
bSheetFound = true;
sheet = s.Clone();
break;
}
}
if (bSheetFound)
{
if (sheet.SheetType == SLSheetType.Worksheet)
{
WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sheet.Id);
using (OpenXmlReader oxr = OpenXmlReader.Create(wsp))
{
bool bFound = false;
SLSheetView slsv;
while (oxr.Read())
{
if (oxr.ElementType == typeof(SheetProperties))
{
ps.SheetProperties.FromSheetProperties((SheetProperties)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(SheetView))
{
// if we find a sheet view with the default workbook view id,
// we'll just take that.
if (!bFound)
{
slsv = new SLSheetView();
slsv.FromSheetView((SheetView)oxr.LoadCurrentElement());
if (slsv.ShowFormulas) ps.bShowFormulas = slsv.ShowFormulas;
if (!slsv.ShowGridLines) ps.bShowGridLines = slsv.ShowGridLines;
if (!slsv.ShowRowColHeaders) ps.bShowRowColumnHeaders = slsv.ShowRowColHeaders;
if (!slsv.ShowRuler) ps.bShowRuler = slsv.ShowRuler;
if (slsv.View != SheetViewValues.Normal) ps.vView = slsv.View;
if (slsv.ZoomScale != 100) ps.ZoomScale = slsv.ZoomScale;
if (slsv.ZoomScaleNormal != 0) ps.ZoomScaleNormal = slsv.ZoomScaleNormal;
if (slsv.ZoomScalePageLayoutView != 0) ps.ZoomScalePageLayoutView = slsv.ZoomScalePageLayoutView;
if (slsv.WorkbookViewId == 0) bFound = true;
}
}
else if (oxr.ElementType == typeof(PrintOptions))
{
ps.ImportPrintOptions((PrintOptions)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(PageMargins))
{
ps.ImportPageMargins((PageMargins)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(PageSetup))
{
ps.ImportPageSetup((PageSetup)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(HeaderFooter))
{
ps.ImportHeaderFooter((HeaderFooter)oxr.LoadCurrentElement());
}
}
}
}
else if (sheet.SheetType == SLSheetType.Chartsheet)
{
ChartsheetPart csp = (ChartsheetPart)wbp.GetPartById(sheet.Id);
if (csp.Chartsheet.ChartSheetProperties != null)
{
ps.SheetProperties.FromChartSheetProperties(csp.Chartsheet.ChartSheetProperties);
}
if (csp.Chartsheet.PageMargins != null)
{
ps.ImportPageMargins(csp.Chartsheet.PageMargins);
}
if (csp.Chartsheet.ChartSheetPageSetup != null)
{
ps.ImportChartSheetPageSetup(csp.Chartsheet.ChartSheetPageSetup);
}
if (csp.Chartsheet.HeaderFooter != null)
{
ps.ImportHeaderFooter(csp.Chartsheet.HeaderFooter);
}
}
else if (sheet.SheetType == SLSheetType.DialogSheet)
{
DialogsheetPart dsp = (DialogsheetPart)wbp.GetPartById(sheet.Id);
if (dsp.DialogSheet.SheetProperties != null)
{
ps.SheetProperties.FromSheetProperties(dsp.DialogSheet.SheetProperties);
}
if (dsp.DialogSheet.PrintOptions != null)
{
ps.ImportPrintOptions(dsp.DialogSheet.PrintOptions);
}
if (dsp.DialogSheet.PageMargins != null)
{
ps.ImportPageMargins(dsp.DialogSheet.PageMargins);
}
if (dsp.DialogSheet.PageSetup != null)
{
ps.ImportPageSetup(dsp.DialogSheet.PageSetup);
}
if (dsp.DialogSheet.HeaderFooter != null)
{
ps.ImportHeaderFooter(dsp.DialogSheet.HeaderFooter);
}
}
else if (sheet.SheetType == SLSheetType.Macrosheet)
{
// not doing anything for macrosheets. What *are* macrosheets?
}
}
return ps;
}
internal void SetPageSettingsSheetView(SLPageSettings ps)
{
if (ps.bShowFormulas != null)
{
// TODO: images and charts?
// Actually I don't feel like updating those...
if (ps.bShowFormulas.Value != slws.IsDoubleColumnWidth)
{
List<int> keys = slws.ColumnProperties.Keys.ToList<int>();
SLColumnProperties cp;
slws.IsDoubleColumnWidth = ps.bShowFormulas.Value;
if (ps.bShowFormulas.Value)
{
// have to test beforehand because setting the default column width
// assigns the HasDefaultColumnWidth property
if (!slws.SheetFormatProperties.HasDefaultColumnWidth)
{
slws.SheetFormatProperties.DefaultColumnWidth = 2 * slws.SheetFormatProperties.DefaultColumnWidth;
slws.SheetFormatProperties.HasDefaultColumnWidth = false;
}
else
{
slws.SheetFormatProperties.DefaultColumnWidth = 2 * slws.SheetFormatProperties.DefaultColumnWidth;
}
foreach (int colkey in keys)
{
cp = slws.ColumnProperties[colkey];
if (cp.HasWidth)
{
cp.Width = 2 * cp.Width;
slws.ColumnProperties[colkey] = cp.Clone();
}
}
}
else
{
// have to test beforehand because setting the default column width
// assigns the HasDefaultColumnWidth property
if (!slws.SheetFormatProperties.HasDefaultColumnWidth)
{
slws.SheetFormatProperties.DefaultColumnWidth = 0.5 * slws.SheetFormatProperties.DefaultColumnWidth;
slws.SheetFormatProperties.HasDefaultColumnWidth = false;
}
else
{
slws.SheetFormatProperties.DefaultColumnWidth = 0.5 * slws.SheetFormatProperties.DefaultColumnWidth;
}
foreach (int colkey in keys)
{
cp = slws.ColumnProperties[colkey];
if (cp.HasWidth)
{
cp.Width = 0.5 * cp.Width;
slws.ColumnProperties[colkey] = cp.Clone();
}
}
}
}
}
if (ps.HasSheetView)
{
if (slws.SheetViews.Count > 0)
{
bool bFound = false;
foreach (SLSheetView sv in slws.SheetViews)
{
if (sv.WorkbookViewId == 0)
{
bFound = true;
if (ps.bShowFormulas != null) sv.ShowFormulas = ps.bShowFormulas.Value;
if (ps.bShowGridLines != null) sv.ShowGridLines = ps.bShowGridLines.Value;
if (ps.bShowRowColumnHeaders != null) sv.ShowRowColHeaders = ps.bShowRowColumnHeaders.Value;
if (ps.bShowRuler != null) sv.ShowRuler = ps.bShowRuler.Value;
if (ps.vView != null) sv.View = ps.vView.Value;
if (ps.iZoomScale != null) sv.ZoomScale = ps.iZoomScale.Value;
if (ps.iZoomScaleNormal != null) sv.ZoomScaleNormal = ps.iZoomScaleNormal.Value;
if (ps.iZoomScalePageLayoutView != null) sv.ZoomScalePageLayoutView = ps.iZoomScalePageLayoutView.Value;
}
}
if (!bFound)
{
slws.SheetViews.Add(ps.ExportSLSheetView());
}
}
else
{
slws.SheetViews.Add(ps.ExportSLSheetView());
}
}
}
/// <summary>
/// Set page settings to a sheet.
/// </summary>
/// <param name="PageSettings">An SLPageSettings object with the properties already set.</param>
/// <param name="SheetName">The name of the sheet.</param>
public void SetPageSettings(SLPageSettings PageSettings, string SheetName)
{
if (SheetName.Equals(gsSelectedWorksheetName, StringComparison.OrdinalIgnoreCase))
{
slws.PageSettings = PageSettings.Clone();
this.SetPageSettingsSheetView(PageSettings);
return;
}
// we're not going to double column widths for non-currently-selected worksheets
// when show formulas is true...
// Too much work for the rare occurence that it happens...
bool bSheetFound = false;
SLSheet sheet = new SLSheet(string.Empty, 0, string.Empty, SLSheetType.Unknown);
foreach (SLSheet s in slwb.Sheets)
{
if (s.Name.Equals(SheetName, StringComparison.OrdinalIgnoreCase))
{
bSheetFound = true;
sheet = s.Clone();
break;
}
}
if (bSheetFound)
{
bool bFound = false;
OpenXmlElement oxe;
if (sheet.SheetType == SLSheetType.Worksheet)
{
#region Worksheet
WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sheet.Id);
if (PageSettings.HasSheetProperties)
{
wsp.Worksheet.SheetProperties = PageSettings.SheetProperties.ToSheetProperties();
}
else
{
wsp.Worksheet.SheetProperties = null;
}
#region SheetViews
if (PageSettings.HasSheetView)
{
if (wsp.Worksheet.SheetViews != null)
{
bool bSheetViewFound = false;
foreach (SheetView sv in wsp.Worksheet.SheetViews)
{
if (sv.WorkbookViewId == 0)
{
if (PageSettings.bShowFormulas != null) sv.ShowFormulas = PageSettings.bShowFormulas.Value;
if (PageSettings.bShowGridLines != null) sv.ShowGridLines = PageSettings.bShowGridLines.Value;
if (PageSettings.bShowRowColumnHeaders != null) sv.ShowRowColHeaders = PageSettings.bShowRowColumnHeaders.Value;
if (PageSettings.bShowRuler != null) sv.ShowRuler = PageSettings.bShowRuler.Value;
if (PageSettings.vView != null) sv.View = PageSettings.vView.Value;
if (PageSettings.iZoomScale != null) sv.ZoomScale = PageSettings.iZoomScale.Value;
if (PageSettings.iZoomScaleNormal != null) sv.ZoomScaleNormal = PageSettings.iZoomScaleNormal.Value;
if (PageSettings.iZoomScalePageLayoutView != null) sv.ZoomScalePageLayoutView = PageSettings.iZoomScalePageLayoutView.Value;
}
}
if (!bSheetViewFound)
{
wsp.Worksheet.SheetViews.Append(PageSettings.ExportSLSheetView().ToSheetView());
}
}
else
{
wsp.Worksheet.SheetViews = new SheetViews();
wsp.Worksheet.SheetViews.Append(PageSettings.ExportSLSheetView().ToSheetView());
}
}
#endregion
#region PrintOptions
if (PageSettings.HasPrintOptions)
{
if (wsp.Worksheet.Elements<PrintOptions>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<PrintOptions>();
}
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(PageSettings.ExportPrintOptions(), oxe);
}
else
{
wsp.Worksheet.PrependChild(PageSettings.ExportPrintOptions());
}
}
#endregion
#region PageMargins
if (PageSettings.HasPageMargins)
{
if (wsp.Worksheet.Elements<PageMargins>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<PageMargins>();
}
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(PageSettings.ExportPageMargins(), oxe);
}
else
{
wsp.Worksheet.PrependChild(PageSettings.ExportPageMargins());
}
}
#endregion
#region PageSetup
if (PageSettings.HasPageSetup)
{
if (wsp.Worksheet.Elements<PageSetup>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<PageSetup>();
}
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(PageSettings.ExportPageSetup(), oxe);
}
else
{
wsp.Worksheet.PrependChild(PageSettings.ExportPageSetup());
}
}
#endregion
#region HeaderFooter
if (PageSettings.HasHeaderFooter)
{
if (wsp.Worksheet.Elements<HeaderFooter>().Count() > 0)
{
wsp.Worksheet.RemoveAllChildren<HeaderFooter>();
}
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(PageSettings.ExportHeaderFooter(), oxe);
}
else
{
wsp.Worksheet.PrependChild(PageSettings.ExportHeaderFooter());
}
}
#endregion
wsp.Worksheet.Save();
#endregion
}
else if (sheet.SheetType == SLSheetType.Chartsheet)
{
#region Chartsheet
ChartsheetPart csp = (ChartsheetPart)wbp.GetPartById(sheet.Id);
if (PageSettings.HasChartSheetProperties)
{
csp.Chartsheet.ChartSheetProperties = PageSettings.SheetProperties.ToChartSheetProperties();
}
else
{
csp.Chartsheet.ChartSheetProperties = null;
}
if (PageSettings.HasPageMargins)
{
csp.Chartsheet.PageMargins = PageSettings.ExportPageMargins();
}
else
{
csp.Chartsheet.PageMargins = null;
}
if (PageSettings.HasChartSheetPageSetup)
{
csp.Chartsheet.ChartSheetPageSetup = PageSettings.ExportChartSheetPageSetup();
}
else
{
csp.Chartsheet.ChartSheetPageSetup = null;
}
if (PageSettings.HasHeaderFooter)
{
csp.Chartsheet.HeaderFooter = PageSettings.ExportHeaderFooter();
}
else
{
csp.Chartsheet.HeaderFooter = null;
}
csp.Chartsheet.Save();
#endregion
}
else if (sheet.SheetType == SLSheetType.DialogSheet)
{
#region DialogSheet
DialogsheetPart dsp = (DialogsheetPart)wbp.GetPartById(sheet.Id);
if (PageSettings.HasSheetProperties)
{
dsp.DialogSheet.SheetProperties = PageSettings.SheetProperties.ToSheetProperties();
}
else
{
dsp.DialogSheet.SheetProperties = null;
}
if (PageSettings.HasPrintOptions)
{
dsp.DialogSheet.PrintOptions = PageSettings.ExportPrintOptions();
}
else
{
dsp.DialogSheet.PrintOptions = null;
}
if (PageSettings.HasPageMargins)
{
dsp.DialogSheet.PageMargins = PageSettings.ExportPageMargins();
}
else
{
dsp.DialogSheet.PageMargins = null;
}
if (PageSettings.HasPageSetup)
{
dsp.DialogSheet.PageSetup = PageSettings.ExportPageSetup();
}
else
{
dsp.DialogSheet.PageSetup = null;
}
if (PageSettings.HasHeaderFooter)
{
dsp.DialogSheet.HeaderFooter = PageSettings.ExportHeaderFooter();
}
else
{
dsp.DialogSheet.HeaderFooter = null;
}
dsp.DialogSheet.Save();
#endregion
}
else if (sheet.SheetType == SLSheetType.Macrosheet)
{
#region Macrosheet
// don't care about macrosheets for now. What *are* macrosheets?
#endregion
}
}
}
/// <summary>
/// Set page settings to the currently selected worksheet.
/// </summary>
/// <param name="PageSettings">An SLPageSettings object with the properties already set.</param>
public void SetPageSettings(SLPageSettings PageSettings)
{
slws.PageSettings = PageSettings.Clone();
this.SetPageSettingsSheetView(PageSettings);
}
internal void SetPageSettingsSheetView(SLPageSettings ps)
{
if (ps.HasSheetView)
{
if (slws.SheetViews.Count > 0)
{
bool bFound = false;
foreach (SLSheetView sv in slws.SheetViews)
{
if (sv.WorkbookViewId == 0)
{
bFound = true;
if (ps.bShowGridLines != null) sv.ShowGridLines = ps.bShowGridLines.Value;
if (ps.bShowRowColumnHeaders != null) sv.ShowRowColHeaders = ps.bShowRowColumnHeaders.Value;
if (ps.bShowRuler != null) sv.ShowRuler = ps.bShowRuler.Value;
if (ps.vView != null) sv.View = ps.vView.Value;
if (ps.iZoomScale != null) sv.ZoomScale = ps.iZoomScale.Value;
if (ps.iZoomScaleNormal != null) sv.ZoomScaleNormal = ps.iZoomScaleNormal.Value;
if (ps.iZoomScalePageLayoutView != null) sv.ZoomScalePageLayoutView = ps.iZoomScalePageLayoutView.Value;
}
}
if (!bFound)
{
slws.SheetViews.Add(ps.ExportSLSheetView());
}
}
else
{
slws.SheetViews.Add(ps.ExportSLSheetView());
}
}
}
/// <summary>
/// Get the page settings of sheet.
/// </summary>
/// <param name="SheetName">The name of the sheet.</param>
/// <returns></returns>
public SLPageSettings GetPageSettings(string SheetName)
{
if (SheetName.Equals(gsSelectedWorksheetName, StringComparison.InvariantCultureIgnoreCase))
{
return slws.PageSettings.Clone();
}
SLPageSettings ps = new SLPageSettings(SimpleTheme.listThemeColors, SimpleTheme.listIndexedColors);
bool bSheetFound = false;
SLSheet sheet = new SLSheet(string.Empty, 0, string.Empty, SLSheetType.Unknown);
foreach (SLSheet s in slwb.Sheets)
{
if (s.Name.Equals(SheetName, StringComparison.InvariantCultureIgnoreCase))
{
bSheetFound = true;
sheet = s.Clone();
break;
}
}
if (bSheetFound)
{
if (sheet.SheetType == SLSheetType.Worksheet)
{
WorksheetPart wsp = (WorksheetPart)wbp.GetPartById(sheet.Id);
using (OpenXmlReader oxr = OpenXmlReader.Create(wsp))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(SheetProperties))
{
ps.SheetProperties.FromSheetProperties((SheetProperties)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(PrintOptions))
{
ps.ImportPrintOptions((PrintOptions)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(PageMargins))
{
ps.ImportPageMargins((PageMargins)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(PageSetup))
{
ps.ImportPageSetup((PageSetup)oxr.LoadCurrentElement());
}
else if (oxr.ElementType == typeof(HeaderFooter))
{
ps.ImportHeaderFooter((HeaderFooter)oxr.LoadCurrentElement());
}
}
}
}
else if (sheet.SheetType == SLSheetType.Chartsheet)
{
ChartsheetPart csp = (ChartsheetPart)wbp.GetPartById(sheet.Id);
if (csp.Chartsheet.ChartSheetProperties != null)
{
ps.SheetProperties.FromChartSheetProperties(csp.Chartsheet.ChartSheetProperties);
}
if (csp.Chartsheet.PageMargins != null)
{
ps.ImportPageMargins(csp.Chartsheet.PageMargins);
}
if (csp.Chartsheet.ChartSheetPageSetup != null)
{
ps.ImportChartSheetPageSetup(csp.Chartsheet.ChartSheetPageSetup);
}
if (csp.Chartsheet.HeaderFooter != null)
{
ps.ImportHeaderFooter(csp.Chartsheet.HeaderFooter);
}
}
else if (sheet.SheetType == SLSheetType.DialogSheet)
{
DialogsheetPart dsp = (DialogsheetPart)wbp.GetPartById(sheet.Id);
if (dsp.DialogSheet.SheetProperties != null)
{
ps.SheetProperties.FromSheetProperties(dsp.DialogSheet.SheetProperties);
}
if (dsp.DialogSheet.PrintOptions != null)
{
ps.ImportPrintOptions(dsp.DialogSheet.PrintOptions);
}
if (dsp.DialogSheet.PageMargins != null)
{
ps.ImportPageMargins(dsp.DialogSheet.PageMargins);
}
if (dsp.DialogSheet.PageSetup != null)
{
ps.ImportPageSetup(dsp.DialogSheet.PageSetup);
}
if (dsp.DialogSheet.HeaderFooter != null)
{
ps.ImportHeaderFooter(dsp.DialogSheet.HeaderFooter);
}
}
else if (sheet.SheetType == SLSheetType.Macrosheet)
{
// not doing anything for macrosheets. What *are* macrosheets?
}
}
return ps;
}
protected string GenerateServiceChecklist(int? orderID)
{
#region Style Definition
SLStyle companyAddressStyle = new SLStyle();
companyAddressStyle.Font.FontName = "Arial";
companyAddressStyle.Font.FontSize = 10;
companyAddressStyle.SetWrapText(true);
companyAddressStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
companyAddressStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
SLStyle titleStyle = new SLStyle();
titleStyle.Font.FontName = "Arial";
titleStyle.Font.FontSize = 16;
titleStyle.Font.Bold = true;
titleStyle.SetWrapText(true);
titleStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
titleStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
SLStyle jobInformationLabelStyle = new SLStyle();
jobInformationLabelStyle.Font.FontName = "Arial";
jobInformationLabelStyle.Font.FontSize = 12;
jobInformationLabelStyle.SetWrapText(true);
jobInformationLabelStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
jobInformationLabelStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right);
SLStyle jobInformationContentStyle = new SLStyle();
jobInformationContentStyle.Font.FontName = "Arial";
jobInformationContentStyle.Font.FontSize = 12;
jobInformationContentStyle.SetWrapText(true);
jobInformationContentStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
jobInformationContentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
SLStyle itemIndexStyle = new SLStyle();
itemIndexStyle.Font.FontName = "Arial";
itemIndexStyle.Font.FontSize = 11;
itemIndexStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Top);
itemIndexStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right);
itemIndexStyle.Border.BottomBorder.BorderStyle = itemIndexStyle.Border.TopBorder.BorderStyle = itemIndexStyle.Border.LeftBorder.BorderStyle = itemIndexStyle.Border.RightBorder.BorderStyle = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin;
itemIndexStyle.Border.BottomBorder.Color = itemIndexStyle.Border.TopBorder.Color = itemIndexStyle.Border.LeftBorder.Color = itemIndexStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
SLStyle itemRowHeaderStyle = new SLStyle();
itemRowHeaderStyle.Font.FontName = "Arial";
itemRowHeaderStyle.Font.FontSize = 11;
itemRowHeaderStyle.SetWrapText(true);
itemRowHeaderStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Top);
itemRowHeaderStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
itemRowHeaderStyle.Border.BottomBorder.BorderStyle = itemRowHeaderStyle.Border.TopBorder.BorderStyle = itemRowHeaderStyle.Border.LeftBorder.BorderStyle = itemRowHeaderStyle.Border.RightBorder.BorderStyle = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin;
itemRowHeaderStyle.Border.BottomBorder.Color = itemRowHeaderStyle.Border.TopBorder.Color = itemRowHeaderStyle.Border.LeftBorder.Color = itemRowHeaderStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
SLStyle itemContentStyle = new SLStyle();
itemContentStyle.Font.FontName = "Arial";
itemContentStyle.Font.FontSize = 11;
itemContentStyle.SetWrapText(true);
itemContentStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
itemContentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
itemContentStyle.Border.BottomBorder.BorderStyle = itemContentStyle.Border.TopBorder.BorderStyle = itemContentStyle.Border.LeftBorder.BorderStyle = itemContentStyle.Border.RightBorder.BorderStyle = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin;
itemContentStyle.Border.BottomBorder.Color = itemContentStyle.Border.TopBorder.Color = itemContentStyle.Border.LeftBorder.Color = itemContentStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
SLStyle topBorderStyle = new SLStyle();
topBorderStyle.Border.SetTopBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle bottomBorderStyle = new SLStyle();
bottomBorderStyle.Border.SetBottomBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle leftBorderStyle = new SLStyle();
leftBorderStyle.Border.SetLeftBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle rightBorderStyle = new SLStyle();
rightBorderStyle.Border.SetRightBorder(DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin, System.Drawing.Color.Black);
SLStyle footerLabelStyle = new SLStyle();
footerLabelStyle.Font.FontName = "Arial";
footerLabelStyle.Font.FontSize = 11;
footerLabelStyle.Alignment.WrapText = true;
footerLabelStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Top);
SLStyle remarkStyle = new SLStyle();
remarkStyle.Font.FontName = "Arial";
remarkStyle.Font.FontSize = 11;
remarkStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
remarkStyle.Border.BottomBorder.BorderStyle = remarkStyle.Border.TopBorder.BorderStyle = remarkStyle.Border.LeftBorder.BorderStyle = remarkStyle.Border.RightBorder.BorderStyle = DocumentFormat.OpenXml.Spreadsheet.BorderStyleValues.Thin;
remarkStyle.Border.BottomBorder.Color = remarkStyle.Border.TopBorder.Color = remarkStyle.Border.LeftBorder.Color = remarkStyle.Border.RightBorder.Color = System.Drawing.Color.Black;
SLStyle legendLabelStyle = new SLStyle();
legendLabelStyle.Font.FontName = "Arial";
legendLabelStyle.Font.FontSize = 11;
legendLabelStyle.SetVerticalAlignment(DocumentFormat.OpenXml.Spreadsheet.VerticalAlignmentValues.Center);
SLStyle leftAlignmentStyle = new SLStyle();
leftAlignmentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
SLStyle rightAlignmentStyle = new SLStyle();
rightAlignmentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right);
SLStyle centerAlignmentStyle = new SLStyle();
centerAlignmentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
SLStyle wrapTextStyle = new SLStyle();
wrapTextStyle.Alignment.WrapText = true;
#endregion Style Definition
int signatureMaxWidth = 160, signatureMaxHeight = 140;
string returnFilePath = null;
string logoPath = System.Web.Hosting.HostingEnvironment.MapPath("~/CSS/Layout/Images/logo.png");
double _borderWidth = 0.01;
int _valueStartRow = 13;
int _valueStartColumn = 4;
int _valueEndColumn = 7;
int valueEndRow = 13;
int currentSheetIndex = 1;
Dictionary<Guid, int> subItemRowIndex = new Dictionary<Guid, int>();
IJobFormService jobFormService = AutoSessionServiceFactory.GetJobFormService(ApplicationSetting.Current.DefaultConnectionString);
IFormItemService formItemService = AutoSessionServiceFactory.GetFormItemService(ApplicationSetting.Current.DefaultConnectionString);
SLDocument document = new SLDocument();
document.RenameWorksheet("Sheet" + currentSheetIndex, "Checklist" + currentSheetIndex);
document.SelectWorksheet("Checklist" + currentSheetIndex);
try
{
#region Get Data
ServiceChecklistDTO checklist = jobFormService.GetServiceChecklist(orderID.Value);
IEnumerable<ServiceChecklistFormItemDTO> formItems = formItemService.GetServiceChecklistFormItems();
#endregion Get Data
#region Validation
if (checklist == null || checklist.valueGroups == null || checklist.valueGroups.Count() == 0) return "No-Record";
#endregion Validation
#region Title Section
document.MergeWorksheetCells(2, 1, 4, 4);
lock (logoSyncLock)
{
SLPicture logo = new SLPicture(logoPath);
logo.SetPosition(1 + _borderWidth, _borderWidth);
document.InsertPicture(logo);
}
for (int i = 2; i <= 4; i++)
{
document.MergeWorksheetCells(i, 5, i, 7);
}
document.SetCellValue(2, 5, "Units A-D, 15/F, Goodman Kwai Chung Logistics Centre, 585-609 Castle Peak Rood, Kwai Chung, N.T., Hong Kong");
document.SetCellValue(3, 5, "香港新界葵涌青山道585-609號嘉民葵涌物流中心15樓A-D室");
document.SetCellValue(4, 5, "電話 Tel: (852) 2494 9455 傳真 Fax: (852) 2481 2051");
document.SetCellStyle(2, 5, 4, 8, companyAddressStyle);
document.MergeWorksheetCells(6, 1, 6, 7);
document.SetCellValue(6, 1, "Routine Check / Service Checklist" + Environment.NewLine + "例行檢查 / 記錄表");
document.SetCellStyle(6, 1, titleStyle);
#endregion Title Section
#region Job Information
string customerCode = string.IsNullOrEmpty(checklist.sCustomerCode) ? string.Empty : checklist.sCustomerCode;
string customerName = checklist.nLanguage == 1 ? string.IsNullOrEmpty(checklist.sCustomerChiName) ? string.Empty : checklist.sCustomerChiName : string.IsNullOrEmpty(checklist.sCustomerName) ? string.Empty : checklist.sCustomerName;
string completionDate = checklist.dCompletionDate.HasValue ? checklist.dCompletionDate.Value.ToString("dd-MM-yyyy") : string.Empty;
string equipmentType = string.IsNullOrEmpty(checklist.sEquipmentType) ? string.Empty : checklist.sEquipmentType;
string equipmentTypeRemark = string.IsNullOrEmpty(checklist.sEquipmentTypeRemark) ? string.Empty : checklist.sEquipmentTypeRemark;
string location = string.IsNullOrEmpty(checklist.sWorkingAddress) ? string.Empty : checklist.sWorkingAddress;
document.MergeWorksheetCells(7, 1, 8, 2);
document.SetCellValue(7, 1, "Name of Customer" + Environment.NewLine + "客戶名稱 :");
document.SetCellStyle(7, 1, jobInformationLabelStyle);
document.MergeWorksheetCells(7, 3, 8, 3);
document.SetCellValue(7, 3, customerName);
document.SetCellStyle(7, 3, jobInformationContentStyle);
document.MergeWorksheetCells(7, 4, 7, 5);
document.SetCellValue(7, 4, "Job / ACM No. :");
document.SetCellStyle(7, 4, jobInformationLabelStyle);
document.MergeWorksheetCells(7, 6, 7, 7);
document.SetCellValue(7, 6, customerCode);
document.SetCellStyle(7, 6, jobInformationContentStyle);
document.MergeWorksheetCells(8, 4, 8, 5);
document.SetCellValue(8, 4, "Work Date 工作日期 :");
document.SetCellStyle(8, 4, jobInformationLabelStyle);
document.MergeWorksheetCells(8, 6, 8, 7);
document.SetCellValue(8, 6, completionDate);
document.SetCellStyle(8, 6, jobInformationContentStyle);
document.MergeWorksheetCells(9, 1, 9, 2);
document.SetCellValue(9, 1, "Location" + Environment.NewLine + "工作地址 :");
document.SetCellStyle(9, 1, jobInformationLabelStyle);
document.MergeWorksheetCells(9, 3, 9, 7);
document.SetCellValue(9, 3, location);
document.SetCellStyle(9, 3, jobInformationContentStyle);
document.MergeWorksheetCells(10, 1, 10, 2);
document.SetCellValue(10, 1, "Equipment Type" + Environment.NewLine + "設備類別 :");
document.SetCellStyle(10, 1, jobInformationLabelStyle);
document.SetCellValue(10, 3, equipmentType);
document.SetCellStyle(10, 3, jobInformationContentStyle);
document.MergeWorksheetCells(10, 4, 10, 5);
document.SetCellValue(10, 4, "Equipment Type Remark" + Environment.NewLine + "設備類別備註 :");
document.SetCellStyle(10, 4, jobInformationLabelStyle);
document.MergeWorksheetCells(10, 6, 10, 7);
document.SetCellValue(10, 6, equipmentTypeRemark);
document.SetCellStyle(10, 6, jobInformationContentStyle);
#endregion Job Information
#region Checklist Header
document.SetCellValue(12, 2, "Check List");
document.SetCellValue(12, 3, "檢查內容");
document.MergeWorksheetCells(12, _valueStartColumn, 12, _valueEndColumn);
int currentRow = _valueStartRow;
int currentFormItemStartRow = _valueStartRow;
foreach (ServiceChecklistFormItemDTO formItem in formItems)
{
string formItemPrefix = string.IsNullOrEmpty(formItem.sPrefix) ? string.Empty : formItem.sPrefix;
string formItemName = string.IsNullOrEmpty(formItem.sName) ? string.Empty : formItem.sName;
string formItemEngName = string.IsNullOrEmpty(formItem.sEngName) ? string.Empty : formItem.sEngName;
document.SetCellValue(currentRow, 1, formItemPrefix);
document.SetCellValue(currentRow, 2, formItemEngName);
document.SetCellValue(currentRow, 3, formItemName);
if (formItem.subItems != null && formItem.subItems.Count() > 0)
{
if (formItem.subItems.Count() == 1)
{
subItemRowIndex.Add(formItem.subItems.ElementAt(0).gID, currentRow);
}
else
{
foreach (ServiceChecklistFormSubItemDTO subItem in formItem.subItems)
{
currentRow++;
string subItemPrefix = string.IsNullOrEmpty(subItem.sPrefix) ? string.Empty : subItem.sPrefix;
string subItemName = string.IsNullOrEmpty(subItem.sName) ? string.Empty : subItem.sName;
string subItemEngName = string.IsNullOrEmpty(subItem.sEngName) ? string.Empty : subItem.sEngName;
string subItemSuffix = string.IsNullOrEmpty(subItem.sSuffix) ? string.Empty : subItem.sSuffix;
document.SetCellValue(currentRow, 2, string.Format("{0}{1}{2}", string.IsNullOrEmpty(subItemPrefix) ? string.Empty : subItemPrefix + " ", subItemEngName, string.IsNullOrEmpty(subItemSuffix) ? string.Empty : " (" + subItemSuffix + ")"));
document.SetCellValue(currentRow, 3, subItem.sName);
subItemRowIndex.Add(subItem.gID, currentRow);
}
}
}
if (currentRow != currentFormItemStartRow)
{
document.MergeWorksheetCells(currentFormItemStartRow, 1, currentRow, 1);
}
currentFormItemStartRow = currentRow + 1;
currentRow++;
}
valueEndRow = currentRow - 1;
document.SetCellStyle(_valueStartRow - 1, 1, valueEndRow, 1, itemIndexStyle);
document.SetCellStyle(_valueStartRow - 1, 2, valueEndRow, 3, itemRowHeaderStyle);
document.SetCellStyle(_valueStartRow - 1, _valueStartColumn, valueEndRow, _valueEndColumn, itemContentStyle);
#endregion Checklist Header
#region Footer Section
#region Checklist Remark
int remarkRow = currentRow;
string checklistRemark = string.IsNullOrEmpty(checklist.sRemark) ? string.Empty : checklist.sRemark;
document.MergeWorksheetCells(currentRow, 1, currentRow, 2);
document.MergeWorksheetCells(currentRow, 3, currentRow, 7);
document.SetCellValue(currentRow, 1, "Remarks 備註");
document.SetCellValue(currentRow, 3, checklistRemark);
#region Set Style
SLStyle currentStyle = remarkStyle;
currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
document.SetCellStyle(currentRow, 1, currentRow, 2, currentStyle);
currentStyle = remarkStyle;
currentStyle.SetWrapText(true);
currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
document.SetCellStyle(currentRow, 3, currentRow, 7, currentStyle);
#endregion Set Style
#endregion Checklist Remark
currentRow++;
#region Legend
int legendRow = currentRow;
document.MergeWorksheetCells(currentRow, 1, currentRow, 2);
document.MergeWorksheetCells(currentRow, 3, currentRow, 7);
document.SetCellValue(currentRow, 1, "Legend 說明");
document.SetCellValue(currentRow, 3, "OK : OK 良好 R : Require Repair 需維修 L : Replacement 更換 N : Not Applicable 不適用");
#region Set Style
currentStyle = legendLabelStyle;
currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
document.SetCellStyle(currentRow, 1, currentRow, 2, currentStyle);
currentStyle = legendLabelStyle;
currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Center);
document.SetCellStyle(currentRow, 3, currentRow, 7, currentStyle);
#endregion Set Style
#endregion Legend
currentRow++;
int footerBoxStartRow = currentRow;
string technicians = string.IsNullOrEmpty(checklist.sTechnicianNameString) ? string.Empty : checklist.sTechnicianNameString;
string startTime = checklist.tActualStartTime.HasValue ? checklist.tActualStartTime.Value.ToString(@"hh\:mm") : string.Empty;
string endTime = checklist.tActualEndTime.HasValue ? checklist.tActualEndTime.Value.ToString(@"hh\:mm") : string.Empty;
string customerOpinion = string.IsNullOrEmpty(checklist.sCustomerOpinion) ? string.Empty : checklist.sCustomerOpinion;
string signDate = checklist.dSignDate.HasValue ? checklist.dSignDate.Value.ToString("dd-MM-yyyy") : string.Empty;
document.MergeWorksheetCells(currentRow, 1, currentRow, 2);
document.SetCellValue(currentRow, 1, "Service Done by 檢查員:");
document.SetCellValue(currentRow, 3, technicians);
document.MergeWorksheetCells(currentRow, _valueStartColumn, currentRow, _valueEndColumn);
document.SetCellValue(currentRow, _valueStartColumn, "Opinion of Customer(s), if any 客戶意見 :");
currentRow++;
document.MergeWorksheetCells(currentRow, 1, currentRow, 2);
document.SetCellValue(currentRow, 1, "Work Start Time 工作開始時間:");
document.SetCellValue(currentRow, 3, startTime);
document.MergeWorksheetCells(currentRow, _valueStartColumn, currentRow + 1, _valueEndColumn);
document.SetCellValue(currentRow, 4, customerOpinion);
currentRow++;
document.MergeWorksheetCells(currentRow, 1, currentRow, 2);
document.SetCellValue(currentRow, 1, "Work Completion Time 工作完成時間:");
document.SetCellValue(currentRow, 3, endTime);
currentRow++;
int signatureStartRow = currentRow;
document.MergeWorksheetCells(currentRow, 1, currentRow + 5, 3);
document.SetCellValue(currentRow, 1, "Office use 公司填寫");
document.MergeWorksheetCells(currentRow, _valueStartColumn, currentRow, _valueStartColumn + 1);
document.SetCellValue(currentRow, _valueStartColumn, "Authorized Signature & Chop for Work Completion to Satisfaction");
currentRow++;
document.MergeWorksheetCells(currentRow, _valueStartColumn, currentRow, _valueStartColumn + 1);
document.SetCellValue(currentRow, _valueStartColumn, "以上工作認可完成請客戶蓋章及簽署");
currentRow = currentRow + 4;
document.SetCellValue(currentRow, _valueStartColumn + 1, "Date 簽署日期:");
document.MergeWorksheetCells(currentRow, _valueEndColumn - 1, currentRow, _valueEndColumn);
document.SetCellValue(currentRow, _valueEndColumn - 1, signDate);
document.MergeWorksheetCells(signatureStartRow, _valueEndColumn - 1, signatureStartRow + 4, _valueEndColumn);
#region Signature
try
{
if (!string.IsNullOrEmpty(checklist.sSignaturePath))
{
string signatureFullPath = ApplicationSetting.Current.RootFolderPath + checklist.sSignaturePath;
SLPicture signature = new SLPicture(signatureFullPath);
//Get Signature Dimension
System.Drawing.Bitmap bm = new System.Drawing.Bitmap(signatureFullPath);
double pictureHeight = bm.Height;
double pictureWidth = bm.Width;
bm.Dispose();
int scale = 0;
if (pictureHeight / pictureWidth > ((double)signatureMaxHeight) / ((double)signatureMaxWidth))
{
scale = (int)(((double)signatureMaxHeight / pictureHeight) * 100.0);
}
else
{
scale = (int)(((double)signatureMaxWidth / pictureWidth) * 100.0);
}
signature.SetPosition(signatureStartRow - 1 + _borderWidth, _valueEndColumn - 2 + _borderWidth);
signature.ResizeInPercentage(scale, scale);
document.InsertPicture(signature);
}
}
catch
{
}
#endregion Signature
document.SetCellStyle(footerBoxStartRow, 1, currentRow, _valueEndColumn, footerLabelStyle);
currentStyle = footerLabelStyle;
currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Left);
document.SetCellStyle(footerBoxStartRow, 1, currentRow, 2, currentStyle);
document.SetCellStyle(footerBoxStartRow, 3, signatureStartRow - 1, _valueEndColumn, currentStyle);
currentStyle = footerLabelStyle;
currentStyle.SetHorizontalAlignment(DocumentFormat.OpenXml.Spreadsheet.HorizontalAlignmentValues.Right);
document.SetCellStyle(signatureStartRow, _valueStartColumn, currentRow, _valueStartColumn + 1, currentStyle);
#region Draw Box
document.SetCellStyle(footerBoxStartRow, _valueStartColumn, footerBoxStartRow, _valueEndColumn, topBorderStyle);
document.SetCellStyle(footerBoxStartRow, _valueStartColumn, currentRow, _valueStartColumn, leftBorderStyle);
document.SetCellStyle(footerBoxStartRow, _valueEndColumn, currentRow, _valueEndColumn, rightBorderStyle);
document.SetCellStyle(currentRow, _valueStartColumn, currentRow, _valueEndColumn, bottomBorderStyle);
#endregion Draw Box
#endregion Footer Section
#region Print Setting
SLPageSettings pageSetting = new SLPageSettings();
pageSetting.PaperSize = SLPaperSizeValues.A4Paper;
pageSetting.Orientation = DocumentFormat.OpenXml.Spreadsheet.OrientationValues.Portrait;
pageSetting.ScalePage(1, 1);
pageSetting.TopMargin = pageSetting.BottomMargin = 0.3;
pageSetting.LeftMargin = pageSetting.RightMargin = 0.7;
document.SetPageSettings(pageSetting);
#endregion Print Setting
#region Column Width Setting
document.SetColumnWidth(1, 5.0);
document.SetColumnWidth(2, 40.0);
document.SetColumnWidth(3, 30.0);
for (int i = _valueStartColumn; i <= _valueEndColumn; i++)
{
document.SetColumnWidth(i, 20.0);
}
#endregion Column Width Setting
#region Row Height Setting
document.SetRowHeight(2, 30.0);
document.SetRowHeight(6, 45.0);
document.SetRowHeight(9, 10, 30.0);
document.SetRowHeight(remarkRow, 30.0);
document.SetRowHeight(legendRow, 30.0);
document.SetRowHeight(legendRow + 1, 75.0);
document.SetRowHeight(footerBoxStartRow + 1, currentRow, 20.0);
document.SetRowHeight(signatureStartRow, 30.0);
#endregion Row Height Setting
#region Fill Values
if (checklist.valueGroups != null && checklist.valueGroups.Count() > 0)
{
for (int i = 0; i < checklist.valueGroups.Count(); i++)
{
if (i > 3 && i % 4 == 0)
{
string activeSheetName = document.GetCurrentWorksheetName();
document.AddWorksheet("Dummy");
//Open New Worksheet
currentSheetIndex++;
string currentSheetName = "Checklist" + currentSheetIndex;
bool isCopySuccess = document.CopyWorksheet(activeSheetName, currentSheetName);
document.SelectWorksheet(currentSheetName);
document.DeleteWorksheet("Dummy");
//document.CopyCellFromWorksheet(activeSheetName, 1, 1, currentRow, _valueEndColumn, 1, 1, SLPasteTypeValues.Paste);
//Clear Value Part
for (int c = _valueStartColumn; c <= _valueEndColumn; c++)
{
for (int r = _valueStartRow; r <= valueEndRow; r++)
{
document.SetCellValue(r, c, string.Empty);
}
}
}
ServiceChecklistValueGroupDTO valueGroup = checklist.valueGroups.ElementAt(i);
int columnIndex = (i % 4) + _valueStartColumn;
foreach (ServiceChecklistValueDTO value in valueGroup.values)
{
int rowIndex = subItemRowIndex[value.gFormSubItemID.Value];
document.SetCellValue(rowIndex, columnIndex, value.sValue);
}
}
}
#endregion Fill Values
returnFilePath = string.Format(@"{0}\ServiceChecklist_{1}.xlsx", _serviceChecklistTempFolderPath, DateTime.Now.ToString("yyyyMMddHHmmss"));
document.SaveAs(returnFilePath);
}
catch (Exception e)
{
LogManager.Write(e, "RecWomCrm", "System");
returnFilePath = null;
}
finally
{
document.Dispose();
}
return returnFilePath;
}