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;
}
public static DataTable LoadExcelIntoDataTable(string filePath)
{
var doc = new SLDocument(filePath);
var worksheetStats = doc.GetWorksheetStatistics();
var dataTable = PrepareDataTableForExcel(doc);
for (var row = 2; row < worksheetStats.EndRowIndex; row++)
{
var dataRow = dataTable.NewRow();
for (var col = 0; col < worksheetStats.EndColumnIndex; col++)
{
dataRow[col] = doc.GetCellValueAsString(row, col);
}
dataTable.Rows.Add(dataRow);
}
return dataTable;
}
private DataGroup GetDataGroup(SLDocument doc, int colNum)
{
return new DataGroup
{
Name = GetColumnName(doc, colNum),
Entries = GetCellsInColumn(doc, colNum)
};
}
private RawData ParseData(SLDocument doc)
{
RawData data = new RawData();
for (int i = 1; i <= numCols; i++)
{
data.Add(GetDataGroup(doc, i));
}
return data;
}
private IEnumerable<double> GetCellsInColumn(SLDocument doc, int colNum)
{
List<double> cells = new List<double>();
for (int i = 2; i <= numRows; i++)
{
string value = doc.GetCellValueAsString(i, colNum);
if(!string.IsNullOrWhiteSpace(value))
cells.Add(Convert.ToDouble(value));
}
return cells;
}
public static Dictionary<int, string> GetIndexedColumnNames(SLDocument doc)
{
var indexedColumnNames = new Dictionary<int, string> { };
var worksheetStats = doc.GetWorksheetStatistics();
for (var i = 1; i <= worksheetStats.NumberOfColumns; i++)
{
indexedColumnNames.Add(i, doc.GetCellValueAsString(1, i));
}
return indexedColumnNames;
}
public void Open(string filePath)
{
using (FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (StreamReader streamReader = new StreamReader(fileStream))
{
slDocument = new SLDocument(fileStream);
}
}
parameter = filePath;
SetSheet(slDocument.GetSheetNames()[0]);
}
public static ExcelMetaData GetExcelMetaData(Stream ms)
{
var doc = new SLDocument(ms);
var worksheetStats = doc.GetWorksheetStatistics();
var excelStats = new ExcelMetaData
{
RowCount = worksheetStats.NumberOfRows,
IndexedColumnNames = GetIndexedColumnNames(doc)
};
return excelStats;
}
public void CreaExcel(Dictionary<TimeSpan, Res> algoritmoRes, Dictionary<TimeSpan, Res> baselineRes, Dictionary<TimeSpan, Res> bowRes, string outputFile)
{
using (SLDocument exl = new SLDocument(BASELINE + "template.xlsx"))
{
foreach (var sheet in new string[] { "Algoritmo", "Emoticons", "Bow"})
{
Dictionary<TimeSpan, Res> valori = null;
switch (sheet)
{
case "Algoritmo":
valori = algoritmoRes;
break;
case "Emoticons":
valori = baselineRes;
break;
case "Bow":
valori = bowRes;
break;
default:
break;
}
exl.SelectWorksheet(sheet);
int i = 2;
foreach (var g in valori)
{
exl.SetCellValue("A" + i, g.Key.ToString());
exl.SetCellValue("B" + i, g.Value.Pos);
exl.SetCellValue("C" + i, g.Value.Neg);
exl.SetCellValue("D" + i, g.Value.Neu);
exl.SetCellValue("E" + i, g.Value.Totale);
exl.SetCellValue("F" + i, g.Value.Pos - g.Value.Neg);
i++;
}
}
exl.SaveAs(outputFile);
}
}
/// <summary>
///
/// </summary>
/// <param name="source"></param>
/// <param name="fromWhich"></param>
/// <param name="rowDelimiter"></param>
/// <param name="columnDelimiter"></param>
/// <param name="selectColumns"></param>
/// <returns></returns>
public static string GetRowsAsString(this Dictionary <Int32, Dictionary <Int32, SLCell> > source, SpreadsheetLight.SLDocument fromWhich, string rowDelimiter, string columnDelimiter, List <int> selectColumns)
{
if (source == null)
{
}
var result = string.Join(rowDelimiter, source
.Skip(1)
.Select(v => v.GetRowAsString(fromWhich, columnDelimiter, selectColumns)));
return(result);
}
private string GetColumnName(SLDocument doc, int colNum)
{
return doc.GetCellValueAsString(1, colNum);
}
private void CacheStats(SLDocument doc)
{
SLWorksheetStatistics stats = doc.GetWorksheetStatistics();
numCols = stats.NumberOfColumns;
numRows = stats.NumberOfRows;
}
private SLDocument Open(string filepath)
{
SLDocument doc = new SLDocument(filepath);
return doc;
}
public void GenerateCompositeAirlinesReport(string path)
{
Console.WriteLine("Generating merged report from SQLite and MySql...");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
SLDocument excelFile = new SLDocument();
using (AirportsDbContextSQLite sqliteDbContext = new AirportsDbContextSQLite())
{
using (AirportsDbContextMySql mysqlDbContext = new AirportsDbContextMySql())
{
var compositeReports = from report in mysqlDbContext.Airlinereports.AsEnumerable()
join airline in sqliteDbContext.Airlines.AsEnumerable()
on report.AirlineName equals airline.Name
select
new {
report.AirlineName,
report.TotalFlightsCount,
report.AverageFlightsCount,
report.TotalFlightsDuration,
report.StartDate,
report.EndDate,
airline.Website,
airline.FoundationYear
};
excelFile.SetCellValue("A1", "Airline Name");
excelFile.SetCellValue("B1", "Total Flights Count");
excelFile.SetCellValue("C1", "Average Flights Duration");
excelFile.SetCellValue("D1", "Total Flights Duration");
excelFile.SetCellValue("E1", "From Date");
excelFile.SetCellValue("F1", "To Date");
excelFile.SetCellValue("G1", "Company Website");
excelFile.SetCellValue("H1", "Foundation Year");
int rowCounter = 2;
foreach (var report in compositeReports)
{
excelFile.SetCellValue("A" + rowCounter, report.AirlineName);
excelFile.SetCellValue("B" + rowCounter, report.TotalFlightsCount.ToString());
excelFile.SetCellValue("C" + rowCounter, report.AverageFlightsCount.ToString());
excelFile.SetCellValue("D" + rowCounter, report.TotalFlightsDuration.ToString());
excelFile.SetCellValue("E" + rowCounter, report.StartDate.ToString());
excelFile.SetCellValue("F" + rowCounter, report.EndDate.ToString());
excelFile.SetCellValue("G" + rowCounter, report.Website.ToString());
excelFile.SetCellValue("H" + rowCounter, report.FoundationYear.ToString());
rowCounter++;
}
}
}
DateTime currentDate = DateTime.Now;
string fileNameSuffix = string.Format("-{0}.{1}.{2}-{3}.{4}.{5}.pdf",
currentDate.Day, currentDate.Month, currentDate.Year, currentDate.Hour, currentDate.Minute, currentDate.Second);
excelFile.SaveAs(string.Format("{0}AirlineRepors_{1}.xlsx", path, fileNameSuffix));
}
/// <summary>
/// /
/// </summary>
/// <param name="source"></param>
/// <param name="fromWhich"></param>
/// <param name="columnDelimiter"></param>
/// <returns></returns>
private static string GetRowAsString(this KeyValuePair <Int32, Dictionary <Int32, SLCell> > source, SpreadsheetLight.SLDocument fromWhich, string columnDelimiter)
{
return(source.GetRowAsString(fromWhich, columnDelimiter, null));
}
/// <summary>
///
/// </summary>
/// <param name="source"></param>
/// <param name="fromWhich"></param>
/// <param name="columnDelimiter"></param>
/// <param name="selectColumns"></param>
/// <returns></returns>
private static string GetRowAsString(this KeyValuePair <Int32, Dictionary <Int32, SLCell> > source, SpreadsheetLight.SLDocument fromWhich, string columnDelimiter, List <int> selectColumns)
{
if (source.Value == null)
{
return(string.Empty);
}
var result = string.Join(", ", source.Value
.Where(v => selectColumns == null || selectColumns.Count == 0 || selectColumns.Contains(v.Key))
.Select(v => fromWhich.GetCellValueAsString(source.Key, v.Key)));
return(result);
}
private void emergencySituation(string dadi)
{
AppendText(richTextBox1, Color.DarkSlateBlue, "Toplam " + data_say.ToString() + " adet data işlemden geçti.");
ts_adet.Text = getAdet().ToString() + " Adet Kayıt Bulunmaktadır.";
if (checkBox1.Checked && listView1.Items.Count > 0)
{
bool tarih = false, id = false, badi = false, bistel = false, bceptel = false, badres = false;
SLDocument exc = new SLDocument();
SLStyle st = exc.CreateStyle();
st.SetFontBold(true);
if (checkBox7.Checked)
{
tarih = true;
exc.SetCellValue("A1", "İlan Tarihi");
exc.SetCellStyle("A1", st);
}
if (checkBox2.Checked)
{
id = true;
exc.SetCellValue("B1", "İlan ID");
exc.SetCellStyle("B1", st);
}
if (checkBox3.Checked)
{
badi = true;
exc.SetCellValue("C1", "Adı");
exc.SetCellStyle("C1", st);
}
if (checkBox4.Checked)
{
bistel = true;
exc.SetCellValue("D1", "İş Telefonu");
exc.SetCellStyle("D1", st);
}
if (checkBox5.Checked)
{
bceptel = true;
exc.SetCellValue("E1", "Cep Telefonu");
exc.SetCellStyle("E1", st);
}
if (checkBox6.Checked)
{
badres = true;
exc.SetCellValue("F1", "Adresi");
exc.SetCellStyle("F1", st);
}
int ex_sira = 2;
foreach (ListViewItem itemRow in listView1.Items)
{
string i_no = itemRow.SubItems[0].Text;
string adi = itemRow.SubItems[1].Text;
string istel = itemRow.SubItems[2].Text;
string ceptel = itemRow.SubItems[3].Text;
string adres = itemRow.SubItems[4].Text;
if (tarih)
exc.SetCellValue("A" + ex_sira, DateTime.Now.ToShortDateString());
if (id)
exc.SetCellValue("B" + ex_sira, i_no);
if (badi)
exc.SetCellValue("C" + ex_sira, adi);
if (bistel)
exc.SetCellValue("D" + ex_sira, istel);
if (bceptel)
exc.SetCellValue("E" + ex_sira, ceptel);
if (badres)
exc.SetCellValue("F" + ex_sira, adres);
ex_sira++;
}
if (exc.GetCellValueAsString("A1") == "")
exc.HideColumn("A");
if (exc.GetCellValueAsString("B1") == "")
exc.HideColumn("B");
if (exc.GetCellValueAsString("C1") == "")
exc.HideColumn("C");
if (exc.GetCellValueAsString("D1") == "")
exc.HideColumn("D");
if (exc.GetCellValueAsString("E1") == "")
exc.HideColumn("E");
string exc_isim = dadi + ".xlsx";
exc.SaveAs(exc_isim);
}
}
static void Main(string[] args)
{
if (args.Length < 2)
{
Console.WriteLine("Usage: ResourceTool.exe -i/e sample.xlsx");
return;
}
switch (args[0])
{
case "-i":
{
var doc = new SLDocument(args[1]);
doc.SelectWorksheet("Translations");
var stat = doc.GetWorksheetStatistics();
var languages = new List<string>();
for (var i = 3; i <= stat.EndColumnIndex; i++)
{
var lang = doc.GetCellValueAsString(2, i);
var resxName = lang == "en-US" ? "Resources.resx" : string.Format("Resources.{0}.resx", lang);
languages.Add(resxName);
}
for (var j = 0; j < languages.Count; j++)
{
var language = languages[j];
var resxFile = Path.Combine(RootPath, "Properties", language);
var resx = new XmlDocument();
resx.Load(resxFile);
for (var i = 3; i <= stat.EndRowIndex; i++)
{
var key = doc.GetCellValueAsString(i, 1);
Console.WriteLine("{0} {1} {2}", i, key, language);
var value = doc.GetCellValueAsString(i, j + 3);
var node = resx.SelectSingleNode(string.Format(".//data[@name='{0}']", key));
if (node == null)
{
node = resx.CreateElement("data");
var name = resx.CreateAttribute("name");
name.Value = key;
node.Attributes.Append(name);
var xmlspace = resx.CreateAttribute("xml:space");
xmlspace.Value = "preserve";
node.Attributes.Append(xmlspace);
resx.DocumentElement.AppendChild(node);
var valueNode = resx.CreateElement("value");
valueNode.InnerText = value;
node.AppendChild(valueNode);
}
else
{
var valueNode = node.SelectSingleNode("value");
if (valueNode == null)
{
valueNode = resx.CreateElement("value");
node.AppendChild(valueNode);
}
valueNode.InnerText = value;
}
}
resx.Save(resxFile);
}
}
break;
case "-e":
{
if (File.Exists(args[1])) File.Delete(args[1]);
var r = new Regex(@"Resources\.([a-z]{2}-[A-Z]{2})?\.?resx");
var doc = new SLDocument();
doc.AddWorksheet("Translations");
doc.DeleteWorksheet("Sheet1");
doc.SelectWorksheet("Translations");
doc.SetCellValue(1, 1, "Key");
doc.SetCellValue(1, 2, "Comment");
var headerStyle = doc.CreateStyle();
headerStyle.Font.Bold = true;
headerStyle.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightBlue,
System.Drawing.Color.LightBlue);
doc.SetCellStyle(1, 1, headerStyle);
doc.SetCellStyle(1, 2, headerStyle);
var resxFiles = Directory.GetFiles(Path.Combine(RootPath, "Properties"), "*.resx");
var columnIndex = 3;
var keys = new List<string>();
var files = resxFiles.OrderBy(n =>
{
var l = r.Match(Path.GetFileName(n)).Groups[1].Value;
return string.IsNullOrEmpty(l) ? null : n;
});
foreach (var resx in files)
{
var lang = r.Match(Path.GetFileName(resx)).Groups[1].Value;
var english = false;
if (string.IsNullOrEmpty(lang))
{
lang = "en-US";
english = true;
}
var ci = CultureInfo.GetCultureInfo(lang);
doc.SetCellValue(1, columnIndex, ci.EnglishName);
doc.SetCellValue(2, columnIndex, ci.Name);
doc.SetCellStyle(1, columnIndex, headerStyle);
doc.SetColumnWidth(columnIndex, 100);
var rr = new ResXResourceReader(resx);
if (english)
{
rr.UseResXDataNodes = true;
var rowIndex = 3;
foreach (DictionaryEntry entry in rr)
{
var key = (string) entry.Key;
var node = (ResXDataNode) entry.Value;
keys.Add(key);
doc.SetCellValue(rowIndex, 1, key);
doc.SetCellValue(rowIndex, 2, node.Comment);
doc.SetCellValue(rowIndex, columnIndex,
node.GetValue((ITypeResolutionService) null).ToString());
rowIndex++;
}
}
else
{
rr.UseResXDataNodes = true;
foreach (DictionaryEntry entry in rr)
{
var key = (string) entry.Key;
var node = (ResXDataNode) entry.Value;
var index = keys.IndexOf(key);
int rowIndex;
if (index == -1)
{
rowIndex = keys.Count;
keys.Add(key);
doc.SetCellValue(rowIndex, 1, key);
}
else
{
rowIndex = index + 3;
}
doc.SetCellValue(rowIndex, columnIndex,
node.GetValue((ITypeResolutionService) null).ToString());
}
}
columnIndex++;
}
doc.AutoFitColumn(1, 2);
doc.SaveAs(args[1]);
}
break;
default:
Console.WriteLine("Unknown switch {0}", args[0]);
return;
}
}
private void asdasda1()
{
//ilanlar tablosu ilan_id sütunu önceki işlem görmüş ilanlar eklenecek.
if (listBox1.Items.Count == 0)
{
MessageBox.Show("Ana Menüden Aranacak Kelime Ekleyin..");
return;
}
sTime = DateTime.Now;
AppendText(richTextBox1, Color.DarkViolet, "İşlemler Başladı.. " + sTime.ToString());
a.Clear();
button1.Enabled = false;
button2.Enabled = false;
textBox1.Enabled = false;
for (int i = 0; i < listBox1.Items.Count; i++)
{
bool varmı = false;
int var_adet = 0;
listView1.Items.Clear();
listView3.Items.Clear();
bool adt = false;
if (listBox1.Items[i].ToString().Contains("http://www.sahibinden.com/"))
{
stlbl.Text = "Başladı.. Aranan Sayfa: " + listBox1.Items[i].ToString();
listBox1.SetSelected(i, true);
for (int ss = 0; ss <= 980; ss += 20)
{
string html = getHTML(listBox1.Items[i].ToString() + "?pagingOffset=" + ss);
if (html.Equals("yok"))
{
MessageBox.Show("Bağlantıda Bir Sorun Oluştu. Alınan Tüm Bilgiler, Database'ye ve ayarları yaptıysanız Excel'e yazıldı. Tekrar Çalıştırın. Hatalı Bölüm: 1 - Giriş Seviyesi");
string exc_isim = "";
if (listBox1.Items[i].ToString().Contains("http://www.sahibinden.com/"))
exc_isim = DateTime.Now.ToShortDateString() + "_" + listBox1.Items[i].ToString().Substring(listBox1.Items[i].ToString().LastIndexOf('/'), listBox1.Items[i].ToString().Length - 1) + ".xlsx";
else
exc_isim = DateTime.Now.ToString().Replace(' ', '_').Replace(':', '-').Replace('/', '-') + "_" + listBox1.Items[i].ToString() + ".xlsx";
emergencySituation(exc_isim);
reLoad();
return;
}
if (!html.Contains("<td class=\"searchResultsFirstColumn\">"))
break;
if (!adt)
{
string[] adet = html.Split(new string[] { "<div class=\"infoSearchResults\"> <div>" }, StringSplitOptions.None);
string[] adet1 = adet[1].Split(new string[] { "</div>" }, StringSplitOptions.None);
AppendText(richTextBox1, Color.Red, StripHTML(adet1[0]) + " " + DateTime.Now.ToString());
adt = true;
}
int syf = (ss / 20);
syf++;
AppendText(richTextBox1, Color.Red, "\"" + listBox1.Items[i].ToString() + "\" adlı sayfanın " + syf + ". sayfası işlemek için sıraya alınıyor.." + DateTime.Now.ToString());
string[] link = html.Split(new string[] { "<a class=\"classifiedTitle\" href=\"" }, StringSplitOptions.None);
for (int j = 1; j < link.Length; j++)
{
string[] lst = link[j].Split('"');
string[] iid = lst[0].Split('-');
string[] iid1 = iid[iid.Length - 1].Split('/');
if (KontrolID(iid1[0]))
{
a.Add(iid1[0]);
}
}
}
}
else
{
stlbl.Text = "Başladı.. Aranan Kelime: " + listBox1.Items[i].ToString();
listBox1.SetSelected(i, true);
if (listBox1.Items[i].ToString().Contains(' '))
listBox1.Items[i].ToString().Replace(' ', '+');
for (int ss = 0; ss <= 980; ss += 20)
{
string html = getHTML("http://www.sahibinden.com/arama?query_text=" + listBox1.Items[i].ToString() + "&pagingOffset=" + ss);
if (html.Equals("yok"))
{
MessageBox.Show("Bağlantıda Bir Sorun Oluştu. Alınan Tüm Bilgiler, Database'ye ve ayarları yaptıysanız Excel'e yazıldı. Tekrar Çalıştırın. Hatalı Bölüm: 1 - Giriş Seviyesi");
string exc_isim = "";
if (listBox1.Items[i].ToString().Contains("http://www.sahibinden.com/"))
exc_isim = DateTime.Now.ToShortDateString() + "_" + listBox1.Items[i].ToString().Substring(listBox1.Items[i].ToString().LastIndexOf('/'), listBox1.Items[i].ToString().Length - 1) + ".xlsx";
else
exc_isim = DateTime.Now.ToString().Replace(' ', '_').Replace(':', '-').Replace('/', '-') + "_" + listBox1.Items[i].ToString() + ".xlsx";
emergencySituation(exc_isim);
reLoad();
return;
}
if (!html.Contains("<td class=\"searchResultsFirstColumn\">"))
break;
if (!adt)
{
string[] adet = html.Split(new string[] { "<div><h1>" }, StringSplitOptions.None);
string[] adet1 = adet[1].Split(new string[] { "</div>" }, StringSplitOptions.None);
AppendText(richTextBox1, Color.Red, StripHTML(adet1[0]) + " " + DateTime.Now.ToString());
adt = true;
}
int syf = (ss / 20);
syf++;
AppendText(richTextBox1, Color.Red, "\"" + listBox1.Items[i].ToString() + "\" adlı kelimenin " + syf + ". sayfası işlemek için sıraya alınıyor.." + DateTime.Now.ToString());
string[] link = html.Split(new string[] { "<a class=\"classifiedTitle\" href=\"" }, StringSplitOptions.None);
for (int j = 1; j < link.Length; j++)
{
string[] lst = link[j].Split('"');
string[] iid = lst[0].Split('-');
string[] iid1 = iid[iid.Length - 1].Split('/');
if (KontrolID(iid1[0]))
{
a.Add(iid1[0]);
}
}
}
}
MessageBox.Show(a[5].ToString());
foreach (string aa in a)
{
string ad = "Yok", ist = "Yok", cept = "Yok", adres = "Yok";
string info = getHTML("http://www.sahibinden.com/search.php?b%5Bsearch_text%5D=" + aa);
if (info.Equals("yok"))
{
MessageBox.Show("Bağlantıda Bir Sorun Oluştu. Alınan Tüm Bilgiler, Database'ye ve ayarları yaptıysanız Excel'e yazıldı. Tekrar Çalıştırın. Hatalı Bölüm: 2 - ID Aramaları");
string exc_isim = "";
if (listBox1.Items[i].ToString().Contains("http://www.sahibinden.com/"))
exc_isim = DateTime.Now.ToShortDateString() + "_" + listBox1.Items[i].ToString().Substring(listBox1.Items[i].ToString().LastIndexOf('/'), listBox1.Items[i].ToString().Length - 1) + ".xlsx";
else
exc_isim = DateTime.Now.ToString().Replace(' ', '_').Replace(':', '-').Replace('/', '-') + "_" + listBox1.Items[i].ToString() + ".xlsx";
emergencySituation(exc_isim);
reLoad();
return;
}
giris_say++;
listBox2.Items.Add(giris_say.ToString());
if (info.Contains("<td class=\"searchResultsSmallThumbnail\">") || info.Contains("18 yaş altı kişilerin girmesi yasaktır."))
{
AppendText(richTextBox1, Color.DarkBlue, "hatalı ilan");
continue;
}
if (info.Contains("Güvenlik Kontrolü"))
{
/*AppendText(richTextBox1, Color.Red, "Güvenlik Protokolü. Modeminizi Yeniden Başlatın. Captcha Kontrolü Başladı.");
string exc_isim = "";
if (listBox1.Items[i].ToString().Contains("http://www.sahibinden.com/"))
exc_isim = DateTime.Now.ToShortDateString() + "_" + listBox1.Items[i].ToString().Substring(listBox1.Items[i].ToString().LastIndexOf('/'), listBox1.Items[i].ToString().Length - 1) + ".xlsx";
else
exc_isim = DateTime.Now.ToString().Replace(' ', '_').Replace(':', '-').Replace('/', '-') + "_" + listBox1.Items[i].ToString() + ".xlsx";
emergencySituation(exc_isim);
return;*/
Process.Start("IExplore.exe", "http://www.sahibinden.com/ilan/emlak-konut-satilik-sahibinden-katta-tek-deniz-manzarali-3-plus1-ici-sifir-130-m2-161857851/detay/");
MessageBox.Show("Güvenlik Kontrolü Çıktı. Explorer'dan Giriş Yaptıktan Sonra Tamam'a Basın.");
}
if (info.Contains("<ul class=\"userContactInfo\">"))
{
string[] d1 = info.Split(new string[] { "</ul>" }, StringSplitOptions.None);
string[] dd = info.Split(new string[] { "<span>" }, StringSplitOptions.None);
for (int t = 1; t < dd.Length; t++)
{
string[] dd1 = dd[t].Split('<');
if (dd1[0].Contains("0 (5"))
cept = dd1[0];
else
ist = dd1[0];
}
}
if (info.Contains("classifiedUserContent"))
{
string[] dd = info.Split(new string[] { "<h5>" }, StringSplitOptions.None);
string[] dd1 = dd[1].Split('<');
ad = dd1[0];
}
string[] adr = info.Split(new string[] { "<div class=\"classifiedInfo\">" }, StringSplitOptions.None);
string[] adr1 = adr[1].Split(new string[] { "<ul class=\"classifiedInfoList\">" }, StringSplitOptions.None);
string[] adr2 = adr1[0].Split(new string[] { "\">" }, StringSplitOptions.None);
string adres_tut = "";
for (int s = 1; s < adr2.Length; s++)
{
string[] cc = adr2[s].Split('<');
cc[0] = cc[0].Trim();
if (s != adr2.Length - 1)
adres_tut += cc[0] + " / ";
else
adres_tut += cc[0];
}
adres = adres_tut;
bool yeni_kisi = false;
MessageBox.Show(ist + " - " + cept + " - " + adres_tut + " - " + ad);
if (!ist.Equals("Yok") && !cept.Equals("Yok"))
{
OleDbCommand sorgu = new OleDbCommand("SELECT * from sh where ceptel ='" + cept + "' or istel='" + ist + "'", conn);
conn.Open();
if (sorgu.ExecuteScalar() == null)
yeni_kisi = true;
conn.Close();
}
if (ist.Equals("Yok") && !cept.Equals("Yok"))
{
OleDbCommand sorgu = new OleDbCommand("SELECT * from sh where ceptel ='" + cept + "'", conn);
conn.Open();
if (sorgu.ExecuteScalar() == null)
yeni_kisi = true;
conn.Close();
}
if (!ist.Equals("Yok") && cept.Equals("Yok"))
{
OleDbCommand sorgu = new OleDbCommand("SELECT * from sh where istel ='" + ist + "'", conn);
conn.Open();
if (sorgu.ExecuteScalar() == null)
yeni_kisi = true;
conn.Close();
}
if (yeni_kisi)
{
varmı = true;
var_adet++;
listView1.Items.Add(new ListViewItem(new string[] { aa, ad, ist, cept, adres }));
listView3.Items.Add(new ListViewItem(new string[] { ad, cept }));
AppendText(richTextBox1, Color.DarkBlue, StripHTML(aa) + " ID'li ilandaki " + ad.ToUpper() + " isimli kişi veritabanına eklendi.");
ilanIDEkle(aa);
Ekle(aa, ad, ist, cept, adres, listBox1.Items[i].ToString());
data_say++;
toolStripStatusLabel5.Text = "Toplam " + data_say.ToString() + " adet yeni data işlendi.";
}
}
if (!varmı)
AppendText(richTextBox1, Color.DarkGreen, "\"" + listBox1.Items[i].ToString() + "\" adlı kelimede yeni ilan yok.");
else
AppendText(richTextBox1, Color.DarkGreen, "\"" + listBox1.Items[i].ToString() + "\" adlı kelimede yeni " + var_adet + " ilan bulundu.");
//tamamen bittiğinde bu satırdayız.
//excele kaydet vsvs
bool tarih = false, id = false, badi = false, bistel = false, bceptel = false, badres = false;
if (varmı)//yeni ilan varsa işle
if (checkBox1.Checked)
{
SLDocument exc = new SLDocument();
SLStyle st = exc.CreateStyle();
st.SetFontBold(true);
if (checkBox7.Checked)
{
tarih = true;
exc.SetCellValue("A1", "İlan Tarihi");
exc.SetCellStyle("A1", st);
}
if (checkBox2.Checked)
{
id = true;
exc.SetCellValue("B1", "İlan ID");
exc.SetCellStyle("B1", st);
}
if (checkBox3.Checked)
{
badi = true;
exc.SetCellValue("C1", "Adı");
exc.SetCellStyle("C1", st);
}
if (checkBox4.Checked)
{
bistel = true;
exc.SetCellValue("D1", "İş Telefonu");
exc.SetCellStyle("D1", st);
}
if (checkBox5.Checked)
{
bceptel = true;
exc.SetCellValue("E1", "Cep Telefonu");
exc.SetCellStyle("E1", st);
}
if (checkBox6.Checked)
{
badres = true;
exc.SetCellValue("F1", "Adresi");
exc.SetCellStyle("F1", st);
}
int ex_sira = 2;
foreach (ListViewItem itemRow in listView1.Items)
{
string i_no = itemRow.SubItems[0].Text;
string adi = itemRow.SubItems[1].Text;
string istel = itemRow.SubItems[2].Text;
string ceptel = itemRow.SubItems[3].Text;
string adres = itemRow.SubItems[4].Text;
if (tarih)
exc.SetCellValue("A" + ex_sira, DateTime.Now.ToShortDateString());
if (id)
exc.SetCellValue("B" + ex_sira, i_no);
if (badi)
exc.SetCellValue("C" + ex_sira, adi);
if (bistel)
exc.SetCellValue("D" + ex_sira, istel);
if (bceptel)
exc.SetCellValue("E" + ex_sira, ceptel);
if (badres)
exc.SetCellValue("F" + ex_sira, adres);
ex_sira++;
}
if (exc.GetCellValueAsString("A1") == "")
exc.HideColumn("A");
if (exc.GetCellValueAsString("B1") == "")
exc.HideColumn("B");
if (exc.GetCellValueAsString("C1") == "")
exc.HideColumn("C");
if (exc.GetCellValueAsString("D1") == "")
exc.HideColumn("D");
if (exc.GetCellValueAsString("E1") == "")
exc.HideColumn("E");
string exc_isim = "";
if (listBox1.Items[i].ToString().Contains("http://www.sahibinden.com/"))
exc_isim = DateTime.Now.ToShortDateString() + "_" + listBox1.Items[i].ToString().Substring(listBox1.Items[i].ToString().LastIndexOf('/'), listBox1.Items[i].ToString().Length - 1) + ".xlsx";
else
exc_isim = DateTime.Now.ToString().Replace(' ', '_').Replace(':', '-').Replace('/', '-') + "_" + listBox1.Items[i].ToString() + ".xlsx";
exc.SaveAs(exc_isim);
AppendText(richTextBox1, Color.DarkOrange, "\"" + listBox1.Items[i].ToString() + "\" adlı kelimenin sonuçları " + exc_isim + " adlı excel dosyasına yazıldı.");
}
}
button1.Enabled = true;
button2.Enabled = true;
textBox1.Enabled = true;
stlbl.Text = "İşlemler Tamamlandı. Beklemede..";
AppendText(richTextBox1, Color.DarkSlateBlue, "Toplam " + data_say.ToString() + " adet data işlemden geçti.");
AppendText(richTextBox1, Color.DarkViolet, calcTimeDiff(sTime, DateTime.Now).ToString());
ts_adet.Text = getAdet().ToString() + " Adet Kayıt Bulunmaktadır.";
listBox2.Items.Add(giris_say.ToString());
}
private void button7_Click(object sender, EventArgs e)
{
if (listView2.Items.Count > 0)
{
SLDocument exc = new SLDocument();
SLStyle st = exc.CreateStyle();
st.SetFontBold(true);
exc.SetCellValue("A1", "İlan ID");
exc.SetCellStyle("A1", st);
exc.SetCellValue("B1", "Adı");
exc.SetCellStyle("B1", st);
exc.SetCellValue("C1", "İş Telefonu");
exc.SetCellStyle("C1", st);
exc.SetCellValue("D1", "Cep Telefonu");
exc.SetCellStyle("D1", st);
exc.SetCellValue("E1", "Adresi");
exc.SetCellStyle("E1", st);
exc.SetCellValue("F1", "Tarihi");
exc.SetCellStyle("F1", st);
int ex_sira = 2;
foreach (ListViewItem itemRow in listView2.Items)
{
string i_no = itemRow.SubItems[0].Text;
string adi = itemRow.SubItems[1].Text;
string istel = itemRow.SubItems[2].Text;
string ceptel = itemRow.SubItems[3].Text;
string adres = itemRow.SubItems[4].Text;
string tarih = itemRow.SubItems[5].Text;
exc.SetCellValue("A" + ex_sira, i_no);
exc.SetCellValue("B" + ex_sira, adi);
exc.SetCellValue("C" + ex_sira, istel);
exc.SetCellValue("D" + ex_sira, ceptel);
exc.SetCellValue("E" + ex_sira, adres);
exc.SetCellValue("F" + ex_sira, tarih);
ex_sira++;
}
FolderBrowserDialog fbd = new FolderBrowserDialog();
DialogResult result = fbd.ShowDialog();
string a = fbd.SelectedPath + "\\excel_çıktısı.xlsx";
exc.SaveAs(a);
MessageBox.Show("Dosyanız " + a + " olarak kayıt edildi.");
}
else
{
MessageBox.Show("Çıktı Alabilecek Bilgiler Görüntülenmemiş!..");
}
}
public static string[] splitHeader(this Dictionary <Int32, Dictionary <Int32, SLCell> > source, SpreadsheetLight.SLDocument fromWhich, string rowDelimiter, string columnDelimiter, List <int> selectColumns)
{
var headers = string.Join(rowDelimiter, source
.Take(1)
.Select(v => v.GetRowAsString(fromWhich, columnDelimiter, selectColumns))).Split(',');
return(headers);
}
protected string GenerateServiceReport(int? orderID)
{
int signatureMaxWidth = 200, signatureMaxHeight = 180;
double excelBorderWidth = 0.01;
string returnFilePath = null;
IOrderService orderService = AutoSessionServiceFactory.GetOrderService(ApplicationSetting.Current.DefaultConnectionString);
string templateFilePath = System.Web.Hosting.HostingEnvironment.MapPath("~/ReportTemplate/REC_CRM_Service_Report_Template.xlsx");
try
{
SLDocument template = new SLDocument(templateFilePath, "Sheet1");
ServiceReportOrderDTO order = orderService.GetServiceReportOrderByID(orderID.Value);
string completionDate = order.dCompletionDate.HasValue ? order.dCompletionDate.Value.ToString("yyyy/MM/dd") : string.Empty;
string customerName = string.IsNullOrEmpty(order.sCustomerName) ? string.Empty : order.sCustomerName;
string customerCode = string.IsNullOrEmpty(order.sCustomerCode) ? string.Empty : order.sCustomerCode;
string workingAddress = string.IsNullOrEmpty(order.sAddress) ? string.Empty : order.sAddress;
string contactPerson = string.IsNullOrEmpty(order.sContactPersonName) ? string.Empty : order.sContactPersonName;
string model = string.IsNullOrEmpty(order.sModel) ? string.Empty : order.sModel;
string fault = string.IsNullOrEmpty(order.sFault) ? string.Empty : order.sFault;
string remark = string.IsNullOrEmpty(order.sRemark) ? string.Empty : order.sRemark;
string repair = string.IsNullOrEmpty(order.sRepair) ? string.Empty : order.sRepair;
string report = string.IsNullOrEmpty(order.sReport) ? string.Empty : order.sReport;
string reviewerName = string.IsNullOrEmpty(order.sReviewerName) ? string.Empty : order.sReviewerName;
string technicians = string.IsNullOrEmpty(order.sTechnicianNameString) ? string.Empty : order.sTechnicianNameString;
string startTime = order.tStartTime.HasValue ? order.tStartTime.Value.ToString(@"hh\:mm") : string.Empty;
string endTime = order.tEndTime.HasValue ? order.tEndTime.Value.ToString(@"hh\:mm") : string.Empty;
string signDate = order.dReportSignDate.HasValue ? order.dReportSignDate.Value.ToString("yyyy/MM/dd") : string.Empty;
string customerOpinion = string.IsNullOrEmpty(order.sCustomerOpinion) ? string.Empty : order.sCustomerOpinion;
string receipt = string.IsNullOrEmpty(order.sReceipt) ? string.Empty : order.sReceipt;
#region Fill Data
List<SLDefinedName> definedNames = template.GetDefinedNames();
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "RepairDate"), completionDate);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerName"), customerName);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerCode"), customerCode);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "WorkingAddress"), workingAddress);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "ContactPerson"), contactPerson);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Model"), model);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Fault"), fault);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Remark"), remark);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Repair"), repair);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Report"), report);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Technicians"), technicians);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "StartTime"), startTime);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "EndTime"), endTime);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "ReviewerName"), reviewerName);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "SignDate"), signDate);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerOpinion"), customerOpinion);
template.SetCellValue(this.ConvertSingleCellDefinedNameCellReference(definedNames, "Receipt"), receipt);
#region Insert Signature
string signaturePath = ApplicationSetting.Current.RootFolderPath + order.sReportSignPath;
string signatureCellReference = this.ConvertSingleCellDefinedNameCellReference(definedNames, "CustomerSignature");
int signatureColumnIndex = SLConvert.ToColumnIndex(signatureCellReference);
int signatureRowIndex = int.Parse(signatureCellReference.Substring(1));
try
{
//Get Signature Dimension
System.Drawing.Bitmap bm = new System.Drawing.Bitmap(signaturePath);
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);
}
SLPicture pic = new SLPicture(signaturePath);
pic.SetPosition(signatureRowIndex - 1 + excelBorderWidth, signatureColumnIndex - 1 + excelBorderWidth);
pic.ResizeInPercentage(scale, scale);
template.InsertPicture(pic);
}
catch (Exception ex)
{
//File path not correct or file not exist
}
#endregion Insert Signature
#endregion Fill Data
#region Auto Fit Row
for (int i = 9; i <= 13; i++)
{
template.AutoFitRow(i);
}
#endregion Auto Fit Row
returnFilePath = string.Format(@"{0}\ServiceReport_{1}_{2}.xlsx", _serviceReportTempFolderPath, orderID, DateTime.Now.ToString("yyyyMMddHHmmss"));
template.SaveAs(returnFilePath);
}
catch (Exception e)
{
LogManager.Write(e, "RecWomCrm", "System");
returnFilePath = null;
}
return returnFilePath;
}
public ActionResult viewPRExcel()
{
ModelState.Clear();
var viewModel = new PRViewModel
{
//allCoEs = db.CoEs.ToList(),
allCoEs = db.CoEs.ToList(),
allMaps = db.Indicator_CoE_Maps.ToList(),
allFootnoteMaps = db.Indicator_Footnote_Maps.ToList()
};
// Create the workbook
var wb = new XLWorkbook();
var prBlue = XLColor.FromArgb(0, 51, 102);
var prGreen = XLColor.FromArgb(0,118,53);
var prYellow = XLColor.FromArgb(255,192,0);
var prRed = XLColor.FromArgb(255,0,0);
var prHeader1Fill = prBlue;
var prHeader1Font = XLColor.White;
var prHeader2Fill = XLColor.White;
var prHeader2Font = XLColor.Black;
var prBorder = XLColor.FromArgb(0, 0, 0);
var prAreaFill = XLColor.FromArgb(192, 192, 192);
var prAreaFont = XLColor.Black;
var prBorderWidth = XLBorderStyleValues.Thin;
var prFontSize = 10;
var prTitleFont = 20;
var prFootnoteSize = 8;
var prHeighSeperator = 7.5;
var prNumberWidth = 4;
var prIndicatorWidth = 50;
var prValueWidth = 10;
var prDefWidth = 100;
var prFootnoteCharsNewLine = 125;
var wsPRName = "PR";
var wsDefName = "Definitions";
var wsPR = wb.Worksheets.Add(wsPRName);
var wsDef = wb.Worksheets.Add(wsDefName);
List<IXLWorksheet> wsList = new List<IXLWorksheet>();
wsList.Add(wsPR);
wsList.Add(wsDef);
foreach (var ws in wsList)
{
var currentRow = 1;
int startRow;
int indicatorNumber = 1;
foreach (var coe in viewModel.allCoEs)
{
string[,] columnHeaders = new string[0,0];
if (ws.Name == wsPRName)
{
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{"FY_10_11",""},
{"FY_11_12",""},
{"FY_12_13",""},
{"FY 13 14 Performance","FY_13_14_Q1"},
{"FY 13 14 Performance","FY_13_14_Q2"},
{"FY 13 14 Performance","FY_13_14_Q3"},
{"FY 13 14 Performance","FY_13_14_Q4"},
{"FY 13 14 Performance","FY_13_14_YTD"},
{"Target",""},
{"Performance_Threshold",""},
{"Comparator",""}
};
}
else if (ws.Name == wsDefName)
{
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{"Definition_Calculation",""},
{"Target_Rationale",""},
{"Comparator_Source",""}
};
}
var currentCol = 1;
var prHeader2ColStart = 99;
var prHeader2ColEnd = 1;
int maxCol = columnHeaders.GetUpperBound(0) + 1;
var prTitle = ws.Cell(currentRow, 1);
prTitle.Value = coe.CoE;
prTitle.Style.Font.FontSize = prTitleFont;
prTitle.Style.Font.Bold = true;
prTitle.Style.Font.FontColor = prHeader1Font;
prTitle.Style.Fill.BackgroundColor = prHeader1Fill;
ws.Range(ws.Cell(currentRow, 1), ws.Cell(currentRow, maxCol)).Merge();
ws.Range(ws.Cell(currentRow + 1, 1), ws.Cell(currentRow + 1, maxCol)).Merge();
ws.Row(currentRow + 1).Height = prHeighSeperator;
currentRow += 2;
startRow = currentRow;
for (int i = 0; i <= columnHeaders.GetUpperBound(0); i++)
{
if (columnHeaders[i, 1] == "")
{
var columnField = columnHeaders[i, 0];
string cellValue;
Type t = typeof(Indicators);
cellValue = t.GetProperty(columnField) != null ?
ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicators), columnField).DisplayName :
ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicator_CoE_Maps), columnField).DisplayName;
ws.Cell(currentRow, currentCol).Value = cellValue;
ws.Range(ws.Cell(currentRow, currentCol), ws.Cell(currentRow + 1, currentCol)).Merge();
currentCol++;
}
else
{
var columnField = columnHeaders[i, 1];
var columnFieldTop = columnHeaders[i, 0];
ws.Cell(currentRow + 1, currentCol).Value = ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicators), columnField).DisplayName;
ws.Cell(currentRow, currentCol).Value = columnFieldTop;
if (currentCol < prHeader2ColStart) { prHeader2ColStart = currentCol; }
if (currentCol > prHeader2ColEnd) { prHeader2ColEnd = currentCol; }
currentCol++;
}
}
currentCol--;
ws.Range(ws.Cell(currentRow, prHeader2ColStart).Address, ws.Cell(currentRow, prHeader2ColEnd).Address).Merge();
var prHeader1 = ws.Range(ws.Cell(currentRow, 1).Address, ws.Cell(currentRow + 1, currentCol).Address);
var prHeader2 = ws.Range(ws.Cell(currentRow + 1, prHeader2ColStart).Address, ws.Cell(currentRow + 1, prHeader2ColEnd).Address);
prHeader1.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
prHeader1.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
prHeader1.Style.Fill.BackgroundColor = prHeader1Fill;
prHeader1.Style.Font.FontColor = prHeader1Font;
prHeader2.Style.Fill.BackgroundColor = prHeader2Fill;
prHeader2.Style.Font.FontColor = prHeader2Font;
currentRow += 2;
List<Footnotes> footnotes = new List<Footnotes>();
foreach (var areaMap in coe.Area_CoE_Map.OrderBy(x=> x.Area.Sort))
{
var prArea = ws.Range(ws.Cell(currentRow, 1), ws.Cell(currentRow, maxCol));
prArea.Merge();
prArea.Style.Fill.BackgroundColor = prAreaFill;
prArea.Style.Font.FontColor = prAreaFont;
prArea.Value = areaMap.Area.Area;
currentRow++;
foreach (var map in viewModel.allMaps.Where(e => e.Indicator.Area.Equals(areaMap.Area)).Where(d => d.CoE.CoE.Contains(coe.CoE)).OrderBy(f => f.Number))
{
currentCol = 1;
ws.Cell(currentRow, currentCol).Value = indicatorNumber;
indicatorNumber++;
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
currentCol++;
int j = 0;
ws.Cell(currentRow, currentCol).Value = map.Indicator.Indicator;
foreach (var footnote in map.Indicator.Indicator_Footnote_Map.Where(e => e.Indicator_ID == map.Indicator_ID).OrderBy(e => e.Indicator_ID))
{
if (!footnotes.Contains(footnote.Footnote)) { footnotes.Add(footnote.Footnote); }
if (j != 0)
{
ws.Cell(currentRow, currentCol).RichText.AddText(",").VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
}
ws.Cell(currentRow, currentCol).RichText.AddText(footnote.Footnote_ID).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
j++;
}
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
if (ws.Name == wsPRName)
{
string[,] columnIndicators = new string[,]{
{map.Indicator.FY_10_11, map.Indicator.FY_10_11_Sup,""},
{map.Indicator.FY_11_12, map.Indicator.FY_11_12_Sup,""},
{map.Indicator.FY_12_13, map.Indicator.FY_12_13_Sup,""},
{map.Indicator.FY_13_14_Q1, map.Indicator.FY_13_14_Q1_Sup,map.Indicator.Q1_Color,},
{map.Indicator.FY_13_14_Q2, map.Indicator.FY_13_14_Q2_Sup,map.Indicator.Q2_Color,},
{map.Indicator.FY_13_14_Q3, map.Indicator.FY_13_14_Q3_Sup,map.Indicator.Q3_Color,},
{map.Indicator.FY_13_14_Q4, map.Indicator.FY_13_14_Q4_Sup,map.Indicator.Q4_Color,},
{map.Indicator.FY_13_14_YTD, map.Indicator.FY_13_14_YTD_Sup,map.Indicator.YTD_Color,},
{map.Indicator.Target, map.Indicator.Target_Sup,""},
{map.Indicator.Performance_Threshold, map.Indicator.Performance_Threshold_Sup,""},
{map.Indicator.Comparator, map.Indicator.Comparator_Sup,""},
};
var startCol = currentCol;
int k = 1;
for (var i = 0; i <= columnIndicators.GetUpperBound(0); i++)
{
for (j = 0; j <= columnIndicators.GetUpperBound(1); j++)
{
if (columnIndicators[i, j] != null)
{
columnIndicators[i, j] = columnIndicators[i, j].Replace("<b>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</b>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<u>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</u>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<i>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</i>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<sup>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</sup>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<sub>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</sub>", "");
}
}
if (i != columnIndicators.GetUpperBound(0) && columnIndicators[i, 0] == "=")
{
k = 1;
while (columnIndicators[i + k, 0] == "=") { k++; }
ws.Range(ws.Cell(currentRow, startCol + i - 1), ws.Cell(currentRow, startCol + i + k - 1)).Merge();
i += k - 1;
k = 1;
}
else if (columnIndicators[i, 0] != "=")
{
var cell = ws.Cell(currentRow, currentCol + i);
string cellValue = "";
if (columnIndicators[i, 0] != null)
{
cellValue = columnIndicators[i, 0].ToString();
}
if (cellValue.Contains("$"))
{
}
cell.Value = "'" + cellValue;
if (columnIndicators[i, 1] != null)
{
cell.RichText.AddText(columnIndicators[i, 1]).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
}
switch (columnIndicators[i, 2])
{
case "cssWhite":
cell.RichText.SetFontColor(XLColor.Black);
cell.Style.Fill.BackgroundColor = XLColor.White;
break;
case "cssGreen":
cell.RichText.SetFontColor(XLColor.White);
cell.Style.Fill.BackgroundColor = prGreen;
break;
case "cssYellow":
cell.RichText.SetFontColor(XLColor.Black);
cell.Style.Fill.BackgroundColor = prYellow;
break;
case "cssRed":
cell.RichText.SetFontColor(XLColor.White);
cell.Style.Fill.BackgroundColor = prRed;
break;
}
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
}
currentRow++;
}
else if (ws.Name == wsDefName)
{
ws.Cell(currentRow, currentCol).Value = map.Indicator.Definition_Calculation;
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
ws.Cell(currentRow, currentCol).Value = map.Indicator.Target_Rationale;
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
ws.Cell(currentRow, currentCol).Value = map.Indicator.Comparator_Source;
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
currentRow++;
}
}
}
var footnoteRow = ws.Range(ws.Cell(currentRow, 1), ws.Cell(currentRow, maxCol));
footnoteRow.Merge();
footnoteRow.Style.Font.FontSize = prFootnoteSize;
Footnotes defaultFootnote = db.Footnotes.FirstOrDefault(x => x.Footnote_Symbol == "*");
if (!footnotes.Contains(defaultFootnote))
{
footnotes.Add(defaultFootnote);
}
int cellLength = 0;
foreach (var footnote in footnotes.OrderBy(x => x.Footnote_Symbol))
{
ws.Cell(currentRow, 1).RichText.AddText(footnote.Footnote_Symbol).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
ws.Cell(currentRow, 1).RichText.AddText(" " + footnote.Footnote + ";");
cellLength += footnote.Footnote_Symbol.ToString().Length + footnote.Footnote.ToString().Length + 2;
if (cellLength > prFootnoteCharsNewLine)
{
ws.Cell(currentRow, 1).RichText.AddNewLine();
cellLength = 0;
ws.Row(currentRow).Height += 15;
}
}
var pr = ws.Range(ws.Cell(startRow, 1), ws.Cell(currentRow, maxCol));
pr.Style.Border.InsideBorder = prBorderWidth;
pr.Style.Border.InsideBorderColor = prBorder;
pr.Style.Border.OutsideBorder = prBorderWidth;
pr.Style.Border.OutsideBorderColor = prBorder;
pr.Style.Font.FontSize = prFontSize;
pr.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
pr.Style.Alignment.WrapText = true;
ws.Column(1).Width = prNumberWidth;
ws.Column(2).Width = prIndicatorWidth;
for (var i = 3; i <= 15; i++)
{
ws.Column(i).Width = ws.Name == wsPRName ? prValueWidth : prDefWidth;
}
currentRow += 2;
footnotes.Clear();
indicatorNumber = 1;
}
}
MemoryStream preImage = new MemoryStream();
wb.SaveAs(preImage);
MemoryStream postImage = new MemoryStream();
SLDocument postImageWb = new SLDocument(preImage, wsPRName);
string imagePath = this.HttpContext.ApplicationInstance.Server.MapPath("~/Images/logo.png");
SLPicture picLogo = new SLPicture(imagePath);
picLogo.SetPosition(0, 0);
postImageWb.InsertPicture(picLogo);
// Prepare the response
HttpResponse httpResponse = this.HttpContext.ApplicationInstance.Context.Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
httpResponse.AddHeader("content-disposition", "attachment;filename=\"test.xlsx\"");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
postImageWb.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
return View(viewModel);
}
public void GenerateCompositeTelecommunicationReport()
{
Console.WriteLine("Generating merged report from SQLite and MySql...");
if (!Directory.Exists(OutputPath))
{
Directory.CreateDirectory(OutputPath);
}
SLDocument excelFile = new SLDocument();
using (var sqliteDbContext = new TelecommunicationProviderDbContext())
{
var mysqlDbContext = new TelecommunicationProviderMySqlData();
var compositeReports = from MySqlData in mysqlDbContext.ModelsMySqlRepository.All().AsEnumerable()
join sqliteData in sqliteDbContext.DifferentUserProviders.AsEnumerable()
on 1 equals 1 // it's magic :D
select
new
{
// MySqlData.Id,
MySqlData.NumberOfContracts,
MySqlData.Date,
// sqliteData.Id,
sqliteData.NumberOfProviders,
sqliteData.UserSsn
};
excelFile.SetCellValue("A1", "User SSN");
excelFile.SetCellValue("B1", "Number of Contracts");
excelFile.SetCellValue("C1", "Number of Providers");
excelFile.SetCellValue("D1", "Date");
//// excelFile.SetCellValue("E1", "From Date");
//// excelFile.SetCellValue("F1", "To Date");
//// excelFile.SetCellValue("G1", "Company Website");
//// excelFile.SetCellValue("H1", "Foundation Year");
int rowCounter = 2;
foreach (var report in compositeReports)
{
excelFile.SetCellValue("A" + rowCounter, report.UserSsn);
excelFile.SetCellValue("B" + rowCounter, report.NumberOfContracts.ToString());
excelFile.SetCellValue("C" + rowCounter, report.NumberOfProviders.ToString());
excelFile.SetCellValue("D" + rowCounter, report.Date.ToString());
// excelFile.SetCellValue("E" + rowCounter, report.StartDate.ToString());
// excelFile.SetCellValue("F" + rowCounter, report.EndDate.ToString());
// excelFile.SetCellValue("G" + rowCounter, report.Website.ToString());
// excelFile.SetCellValue("H" + rowCounter, report.FoundationYear.ToString());
rowCounter++;
}
}
DateTime currentDate = DateTime.Now;
string fileNameSuffix = string.Format(
"-{0}.{1}.{2}-{3}.{4}.{5}",
currentDate.Day,
currentDate.Month,
currentDate.Year,
currentDate.Hour,
currentDate.Minute,
currentDate.Second);
excelFile.SaveAs(string.Format(OutputFileNameFormat, OutputPath, fileNameSuffix));
}
private int CurrentReporteRow(SLDocument document, int column, int currentRow, string[] item)
{
foreach (var i in item)
{
// O todos los atributos de la entidad :)
document.SetCellValue(currentRow, column++, i);
}
return column;
}
public ActionResult viewPRExcel(Int16 fiscalYear, Int16? coeID)
{
ModelState.Clear();
var viewModel = new PRViewModel
{
//allCoEs = db.CoEs.ToList(),
allCoEs = db.CoEs.ToList(),
allMaps = db.Indicator_CoE_Maps.ToList(),
allFootnoteMaps = db.Indicator_Footnote_Maps.ToList()
};
// Create the workbook
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var ef = new ExcelPackage();
var wb = ef.Workbook;
var prBlue = ExcelColor.FromArgb(0, 51, 102);
var prGreen = ExcelColor.FromArgb(0, 118, 53);
var prYellow = ExcelColor.FromArgb(255, 192, 0);
var prRed = ExcelColor.FromArgb(255, 0, 0);
var prHeader1Fill = prBlue;
var prHeader1Font = ExcelColor.White;
var prHeader2Fill = ExcelColor.White;
var prHeader2Font = ExcelColor.Black;
var prBorder = ExcelColor.FromArgb(0, 0, 0);
var prAreaFill = ExcelColor.FromArgb(192, 192, 192);
var prAreaFont = ExcelColor.Black;
var prBorderWidth = XLBorderStyleValues.Thin;
var prFontSize = 10;
var prTitleFont = 20;
var prFootnoteSize = 8;
var prHeightSeperator = 7.5;
var prAreaObjectiveFontsize = 8;
var indentLength = 24;
var firstIndentLength = 20;
var innerIndentLength = 5;
var newLineHeight = 12.6;
var defNote = "Portal data from the Canadian Institute for Health Information (CIHI) has been used to generate data within this report with acknowledgement to CIHI, the Ministry of Health and Long-Term Care (MOHLTC) and Stats Canada (as applicable). Views are not those of the acknowledged sources. Facility identifiable data other than Mount Sinai Hospital (MSH) is not to be published without the consent of that organization (except where reported at an aggregate level). As this is not a database supported by MSH, please demonstrate caution with use and interpretation of the information. MSH is not responsible for any changes derived from the source data/canned reports. Data may be subject to change.";
var prNumberWidth = 4;
var prIndicatorWidth = 55;
var prValueWidth = 11;
var prDefWidth = 100;
var prRatiWidth = 50;
var prCompWidth = 50;
//var fitRatio = 3.77;
var fitRatio = 1.7;
List<int> fitAdjustableRows = new List<int>();
var prFootnoteCharsNewLine = 125;
var prObjectivesCharsNewLine = 226;
//DELETE THIS
//coeID = null;
var allCoes = new List<CoEs>();
if (coeID != 0 && coeID != null)
{
allCoes = viewModel.allCoEs.Where(x => x.CoE_ID == coeID).ToList();
}
else
{
allCoes = viewModel.allCoEs.ToList();
}
foreach (var coe in allCoes)
{
var wsPRName = coe.CoE_Abbr;
var wsDefName = "Def_" + coe.CoE_Abbr;
var wsPR = wb.Worksheets.Add(wsPRName);
var wsDef = wb.Worksheets.Add(wsDefName);
List<OfficeOpenXml.ExcelWorksheet> wsList = new List<OfficeOpenXml.ExcelWorksheet>();
wsList.Add(wsPR);
wsList.Add(wsDef);
foreach (var ws in wsList)
{
var currentRow = 4;
ws.Row(2).Height = 21;
int startRow;
int indicatorNumber = 1;
ws.PrinterSettings.TopMargin = 0;
ws.PrinterSettings.HeaderMargin = 0;
ws.PrinterSettings.BottomMargin = 0.5M;
ws.PrinterSettings.LeftMargin = 0;
ws.PrinterSettings.RightMargin = 0;
ws.PrinterSettings.Orientation = eOrientation.Landscape;
ws.PrinterSettings.PaperSize = ePaperSize.Legal;
ws.PrinterSettings.FitToHeight = 1;
ws.PrinterSettings.FitToWidth = 1;
//ws.PageSetup.Margins.Top = 0;
//ws.PageSetup.Margins.Header = 0;
//ws.PageSetup.Margins.Left = 0.5;
//ws.PageSetup.Margins.Right = 0.5;
//ws.PageSetup.Margins.Bottom = 0.5;
//ws.PageSetup.PageOrientation = XLPageOrientation.Landscape;
//ws.PageSetup.PaperSize = XLPaperSize.LegalPaper;
//ws.PageSetup.FitToPages(1, 1);
string[,] columnHeaders = new string[0, 0];
if (ws.Name == wsPRName)
{
var prHeadder2Title = FiscalYear.FYStrFull("FY_", fiscalYear) + "Performance";
prHeadder2Title = prHeadder2Title.Replace("_", " ");
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{FiscalYear.FYStrFull("FY_3", fiscalYear), ""},
{FiscalYear.FYStrFull("FY_2", fiscalYear),""},
{FiscalYear.FYStrFull("FY_1", fiscalYear),""},
{prHeadder2Title,"Q1"},
{prHeadder2Title,"Q2"},
{prHeadder2Title,"Q3"},
{prHeadder2Title,"Q4"},
{prHeadder2Title,"YTD"},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Target",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Performance_Threshold",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator",""}
};
}
else if (ws.Name == wsDefName)
{
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Definition_Calculation",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Target_Rationale",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator_Source",""}
};
}
var currentCol = 1;
var prHeader2ColStart = 99;
var prHeader2ColEnd = 1;
int maxCol = columnHeaders.GetUpperBound(0) + 1;
var prTitle = ws.Cells[currentRow, 1];
prTitle.Value = coe.CoE;
prTitle.Style.Font.Size = prTitleFont;
prTitle.Style.Font.Bold = true;
prTitle.Style.Font.Color.SetColor(prHeader1Font);
prTitle.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
prTitle.Style.Fill.BackgroundColor.SetColor(prHeader1Fill);
ws.Cells[currentRow, 1, currentRow, maxCol].Merge = true;
ws.Cells[currentRow + 1, 1, currentRow + 1, maxCol].Merge = true;
ws.Row(currentRow + 1).Height = prHeightSeperator;
currentRow += 2;
startRow = currentRow;
for (int i = 0; i <= columnHeaders.GetUpperBound(0); i++)
{
if (columnHeaders[i, 1] == "")
{
var columnField = columnHeaders[i, 0];
string cellValue;
Type t = typeof(Indicators);
cellValue = t.GetProperty(columnField) != null ?
ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicators), columnField).DisplayName :
ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicator_CoE_Maps), columnField).DisplayName;
ws.Cells[currentRow, currentCol].Value = cellValue;
ws.Cells[currentRow, currentCol, currentRow + 1, currentCol].Merge = true;
currentCol++;
}
else
{
var columnField = columnHeaders[i, 1];
var columnFieldTop = columnHeaders[i, 0];
ws.Cells[currentRow + 1, currentCol].Value = columnField;
ws.Cells[currentRow, currentCol].Value = columnFieldTop;
if (currentCol < prHeader2ColStart) { prHeader2ColStart = currentCol; }
if (currentCol > prHeader2ColEnd) { prHeader2ColEnd = currentCol; }
currentCol++;
}
}
currentCol--;
var prHeader1 = ws.Cells[currentRow, 1, currentRow + 1, currentCol];
if (prHeader2ColStart != 99)
{
ws.Cells[currentRow, prHeader2ColStart, currentRow, prHeader2ColEnd].Merge = true;
var prHeader2 = ws.Cells[currentRow + 1, prHeader2ColStart, currentRow + 1, prHeader2ColEnd];
prHeader2.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
prHeader2.Style.Fill.BackgroundColor.SetColor(prHeader2Fill);
prHeader2.Style.Font.Color.SetColor(prHeader2Font);
}
prHeader1.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
prHeader1.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
prHeader1.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
prHeader1.Style.Fill.BackgroundColor.SetColor(prHeader1Fill);
prHeader1.Style.Font.Color.SetColor(prHeader1Font);
currentRow += 2;
List<Footnotes> footnotes = new List<Footnotes>();
foreach (var areaMap in coe.Area_CoE_Map.Where(x=>x.Fiscal_Year == fiscalYear).OrderBy(x => x.Area.Sort))
{
var cellLengthObjective = 0;
var prArea = ws.Cells[currentRow, 1, currentRow, maxCol];
//fitAdjustableRows.Add(currentRow);
prArea.Merge = true;
prArea.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
prArea.Style.Fill.BackgroundColor.SetColor(prAreaFill);
prArea.Style.Font.Color.SetColor(prAreaFont);
prArea.FirstOrDefault().RichText.Add(areaMap.Area.Area).Bold = true;
cellLengthObjective += areaMap.Area.Area.Length;
if (ws == wsPR)
{
var indent = new string('_', indentLength);
var innerIndent = new string('_', innerIndentLength);
var firstIndent = indent.Substring(0, firstIndentLength - areaMap.Area.Area.Length);
var stringSeperators = new string[] { "•" };
/*
if (areaMap.Objective != null)
{
var objectives = areaMap.Objective.Split(stringSeperators, StringSplitOptions.None);
for (var i = 1; i < objectives.Length; i++)
{
if (i == 1)
{
prArea.FirstOrDefault().RichText.Add(firstIndent).Size = prAreaObjectiveFontsize;
cellLengthObjective += firstIndent.Length;
}
//var innerIndentAdj = new string('_', maxObjectiveLength < objectives[i].Length ? 0 : maxObjectiveLength - objectives[i].Length);
var innerIndentAdj = "";
cellLengthObjective += objectives[i].Length + innerIndent.Length + innerIndentAdj.Length;
if (cellLengthObjective > prObjectivesCharsNewLine)
{
prArea.FirstOrDefault().RichText.Add("\r\n");
ws.Row(currentRow).Height += (int)newLineHeight;
prArea.FirstOrDefault().RichText.Add(indent).Color = ClosedXML.Excel.XLColor.FromColor(prAreaFill);
prArea.FirstOrDefault().RichText.Add(indent).SetFontColor( ClosedXML.Excel.XLColor.FromColor(prAreaFont)).SetFontSize(prAreaObjectiveFontsize);
cellLengthObjective = indent.Length;
}
prArea.FirstCell().RichText.AddText(innerIndent + innerIndentAdj).FontColor = ClosedXML.Excel.XLColor.FromColor(prAreaFill);
prArea.FirstCell().RichText.AddText(" •" + objectives[i]).FontSize = prAreaObjectiveFontsize;
cellLengthObjective += objectives[i].Length;
}
}*/
}
currentRow++;
foreach (var map in viewModel.allMaps.Where(x => x.Fiscal_Year == fiscalYear).Where(e => e.Indicator.Area.Equals(areaMap.Area)).Where(d => d.CoE.CoE.Contains(coe.CoE)).OrderBy(f => f.Number))
{
fitAdjustableRows.Add(currentRow);
currentCol = 1;
ws.Cells[currentRow, currentCol].Value = indicatorNumber;
indicatorNumber++;
ws.Cells[currentRow, currentCol].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
currentCol++;
int j = 0;
ws.Cells[currentRow, currentCol].Value = map.Indicator.Indicator;
foreach (var footnote in map.Indicator.Indicator_Footnote_Map.Where(x => x.Fiscal_Year == fiscalYear).Where(e => e.Indicator_ID == map.Indicator_ID).OrderBy(e => e.Indicator_ID))
{
if (!footnotes.Contains(footnote.Footnote)) { footnotes.Add(footnote.Footnote); }
if (j != 0)
{
ws.Cells[currentRow, currentCol].RichText.Add(",").VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;
}
ws.Cells[currentRow, currentCol].RichText.Add(footnote.Footnote.Footnote_Symbol).VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;
j++;
}
ws.Cells[currentRow, currentCol].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
currentCol++;
if (ws.Name == wsPRName)
{
for (var i = 3; i <= 15; i++)
{
ws.Column(i).Width = ws.Name == wsPRName ? prValueWidth : prDefWidth;
}
var obj = map.Indicator;
var type = obj.GetType();
string[,] columnIndicators = new string[,]{
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_3",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_3",fiscalYear) + "_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_2",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_2",fiscalYear) + "_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_1",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_1",fiscalYear) + "_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Target").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Target_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Performance_Threshold").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Performance_Threshold_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Comparator").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Comparator_Sup").GetValue(obj,null),
""
},
};
var startCol = currentCol;
int k = 1;
for (var i = 0; i <= columnIndicators.GetUpperBound(0); i++)
{
for (j = 0; j <= columnIndicators.GetUpperBound(1); j++)
{
if (columnIndicators[i, j] != null)
{
columnIndicators[i, j] = columnIndicators[i, j].Replace("<b>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</b>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<u>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</u>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<i>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</i>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<sup>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</sup>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<sub>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</sub>", "");
}
}
if (i != columnIndicators.GetUpperBound(0) && columnIndicators[i, 0] == "=")
{
k = 1;
while (columnIndicators[i + k, 0] == "=") { k++; }
ws.Cells[currentRow, startCol + i - 1, currentRow, startCol + i + k - 1].Merge = true;
i += k - 1;
k = 1;
}
else if (columnIndicators[i, 0] != "=")
{
var cell = ws.Cells[currentRow, currentCol + i];
string cellValue = "";
if (columnIndicators[i, 0] != null)
{
cellValue = columnIndicators[i, 0].ToString();
}
if (cellValue.Contains("$"))
{
}
cell.Value = "'" + cellValue;
if (columnIndicators[i, 1] != null)
{
cell.RichText.Add(columnIndicators[i, 1]).VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;
}
switch (columnIndicators[i, 2])
{
case "cssWhite":
//cell.RichText.SetFontColor(XLColor.Black);
cell.Style.Font.Color.SetColor(ExcelColor.Black);
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(ExcelColor.White);
break;
case "cssGreen":
cell.Style.Font.Color.SetColor(ExcelColor.White);
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(prGreen);
break;
case "cssYellow":
cell.Style.Font.Color.SetColor(ExcelColor.Black);
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(prYellow);
break;
case "cssRed":
cell.Style.Font.Color.SetColor(ExcelColor.White);
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(prRed);
break;
}
cell.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
}
currentRow++;
}
else if (ws.Name == wsDefName)
{
ws.Column(3).Width = prDefWidth;
ws.Column(4).Width = prRatiWidth;
ws.Column(5).Width = prCompWidth;
var obj = map.Indicator;
var type = obj.GetType();
string defn = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Definition_Calculation").GetValue(obj, null);
string rationale = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Target_Rationale").GetValue(obj, null);
string comp = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator_Source").GetValue(obj, null);
double maxLines = 1;
double lines;
var listColumnStrings = new List<string>();
listColumnStrings.Add(defn);
listColumnStrings.Add(rationale);
listColumnStrings.Add(comp);
foreach (var str in listColumnStrings)
{
if (str != null)
{
lines = str.Length / ws.Column(currentCol).Width;
maxLines = maxLines < lines ? lines : maxLines;
ws.Cells[currentRow, currentCol].Value = str;
}
ws.Cells[currentRow, currentCol].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
currentCol++;
}
ws.Row(currentRow).Height = newLineHeight*Math.Ceiling(maxLines);
currentRow++;
}
}
}
var footnoteRow = ws.Cells[currentRow, 1, currentRow, maxCol];
footnoteRow.Merge = true;
footnoteRow.Style.Font.Size = prFootnoteSize;
/*Footnotes defaultFootnote = db.Footnotes.FirstOrDefault(x => x.Footnote_Symbol == "*");
if (!footnotes.Contains(defaultFootnote))
{
footnotes.Add(defaultFootnote);
}*/
int cellLengthFootnote = 0;
if (ws.Name == wsPRName)
{
foreach (var footnote in footnotes)
{
ws.Cells[currentRow, 1].RichText.Add(footnote.Footnote_Symbol).VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;
ws.Cells[currentRow, 1].RichText.Add(" " + footnote.Footnote + ";");
ws.Cells[currentRow, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
cellLengthFootnote += footnote.Footnote_Symbol.ToString().Length + footnote.Footnote.ToString().Length + 2;
if (cellLengthFootnote > prFootnoteCharsNewLine)
{
ws.Cells[currentRow, 1].RichText.Add("\r\n");;
cellLengthFootnote = 0;
ws.Row(currentRow).Height += newLineHeight;
}
}
}
else
{
ws.Cells[currentRow, 1].Value = defNote;
ws.Row(currentRow).Height = 28;
}
var pr = ws.Cells[startRow, 1, currentRow - 1, maxCol];
//pr.Style.Border.InsideBorder = prBorderWidth;
//pr.Style.Border.InsideBorderColor = prBorder;
//pr.Style.Border.OutsideBorder = prBorderWidth;
//pr.Style.Border.OutsideBorderColor = prBorder;
pr.Style.Font.Size = prFontSize;
pr = ws.Cells[startRow, 1, currentRow, maxCol];
pr.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
pr.Style.WrapText = true;
ws.Column(1).Width = prNumberWidth;
ws.Column(2).Width = prIndicatorWidth;
footnotes.Clear();
indicatorNumber = 1;
/*var totalHeight = ExcelFunctions.getTotalHeight(ws,4);
var totalWidth = ExcelFunctions.getTotalWidth(ws,1);
var fitHeight = (int)(totalWidth / fitRatio);
var fitWidth = (int)(totalHeight * fitRatio);
if (ws.Name == "Def_WIH Obs") { System.Diagnostics.Debugger.Break(); }
if (fitHeight > totalHeight)
{
var fitAddHeightTotal = (fitHeight - totalHeight);
var fitAddHeightPerRow = fitAddHeightTotal / fitAdjustableRows.Count;
foreach (var row in fitAdjustableRows)
{
ws.Row(row).Height += fitAddHeightPerRow;
}
}
else
{
while ((fitWidth - totalWidth) / fitWidth > 0.001 )
{
var fitAddWidthTotal = (fitWidth - totalWidth) / 10;
var fitAddWidthPerRow = fitAddWidthTotal / (ws.LastColumnUsed().ColumnNumber() - 1);
foreach (var col in ws.Columns(2, ws.LastColumnUsed().ColumnNumber()))
{
col.Width += fitAddWidthPerRow / 5.69;
}
ExcelFunctions.AutoFitWorksheet(ws, 2, 3, newLineHeight);
totalHeight = ExcelFunctions.getTotalHeight(ws, 4);
totalWidth = ExcelFunctions.getTotalWidth(ws, 1);
fitHeight = (int)(totalWidth / fitRatio);
fitWidth = (int)(totalHeight * fitRatio);
}
}*/
}
}
FileStream fs = new FileStream(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test1.xlsx"), FileMode.Create);
ef.SaveAs(fs);
fs.Close();
//var test = GemBox.Spreadsheet.ExcelFile.Load(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test.xlsx"), LoadOptions.XlsxDefault);
var test = new Spire.Xls.Workbook();
test.LoadFromFile(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test1.xlsx"));
// Set PDF template
Spire.Pdf.PdfDocument pdfDocument = new Spire.Pdf.PdfDocument();
pdfDocument.PageSettings.Orientation = Spire.Pdf.PdfPageOrientation.Landscape;
pdfDocument.PageSettings.Width = 970;
pdfDocument.PageSettings.Height = 850;
//Convert Excel to PDF using the template above
Spire.Xls.Converter.PdfConverter pdfConverter = new Spire.Xls.Converter.PdfConverter(test);
Spire.Xls.Converter.PdfConverterSettings settings = new Spire.Xls.Converter.PdfConverterSettings();
settings.TemplateDocument = pdfDocument;
pdfDocument = pdfConverter.Convert(settings);
// Save and preview PDF
pdfDocument.SaveToFile(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test1.pdf"));
//wb.SaveAs(preImage);
MemoryStream postImage = new MemoryStream();
SLDocument postImageWb = new SLDocument(preImage);
string picPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/logo.png");
SLPicture picLogo = new SLPicture(picPath);
string picPathOPEO = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/logoOPEO.png");
SLPicture picLogoOPEO = new SLPicture(picPathOPEO);
string picMonthlyPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/Monthly.png");
SLPicture picMonthly = new SLPicture(picMonthlyPath);
string picQuaterlyPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/quaterly.png");
SLPicture picQuaterly = new SLPicture(picQuaterlyPath);
string picNAPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/na.png");
SLPicture picNA = new SLPicture(picNAPath);
string picTargetPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/target.png");
SLPicture picTarget = new SLPicture(picTargetPath);
/*
foreach (var ws in wb.Worksheets)
{
postImageWb.SelectWorksheet(ws.Name);
for (int i = 1; i < 20; ++i)
{
var a = postImageWb.GetRowHeight(i);
}
picLogo.SetPosition(0, 0);
picLogo.ResizeInPercentage(25, 25);
postImageWb.InsertPicture(picLogo);
picLogoOPEO.SetRelativePositionInPixels(0, ws.LastColumnUsed().ColumnNumber() + 1, -140, 0);
picLogoOPEO.ResizeInPercentage(45, 45);
postImageWb.InsertPicture(picLogoOPEO);
if (ws.Name.Substring(0, 3) != "Def")
{
picTarget.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -240, 1);
picNA.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -400, 1);
picMonthly.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -500, 1);
picQuaterly.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -490, 1);
picMonthly.ResizeInPercentage(70, 70);
picQuaterly.ResizeInPercentage(70, 70);
picNA.ResizeInPercentage(70, 70);
picTarget.ResizeInPercentage(70, 70);
postImageWb.InsertPicture(picMonthly);
postImageWb.InsertPicture(picQuaterly);
postImageWb.InsertPicture(picNA);
postImageWb.InsertPicture(picTarget);
}
}*/
// Prepare the response
HttpResponse httpResponse = this.HttpContext.ApplicationInstance.Context.Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
httpResponse.AddHeader("content-disposition", "attachment;filename=\"test.xlsx\"");
//httpResponse.ContentType = "application/pdf";
//httpResponse.AddHeader("content-disposition", "attachment;filename=\"test.pdf\"");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
postImageWb.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
return View(viewModel);
}
public ActionResult viewPRExcel(Int16 fiscalYear, Int16? coeID)
{
ModelState.Clear();
var viewModel = new PRViewModel
{
//allCoEs = db.CoEs.ToList(),
allCoEs = db.CoEs.ToList(),
allMaps = db.Indicator_CoE_Maps.ToList(),
allFootnoteMaps = db.Indicator_Footnote_Maps.ToList()
};
// Create the workbook
var wb = new XLWorkbook();
var prBlue = ExcelGlobalVariables.prBlue;// XLColor.FromArgb(0, 51, 102);
var prGreen = ExcelGlobalVariables.prGreen;//XLColor.FromArgb(0, 118, 53);
var prYellow = ExcelGlobalVariables.prYellow; //XLColor.FromArgb(255, 192, 0);
var prRed = ExcelGlobalVariables.prRed;// XLColor.FromArgb(255, 0, 0);
var prHeader1Fill = ExcelGlobalVariables.prHeader1Fill;//prBlue;
var prHeader1Font = ExcelGlobalVariables.prHeader1Font;//XLColor.White;
var prHeader2Fill = ExcelGlobalVariables.prHeader2Fill;//XLColor.White;
var prHeader2Font = ExcelGlobalVariables.prHeader2Font;//XLColor.Black;
var prBorder = ExcelGlobalVariables.prBorder;//XLColor.FromArgb(0, 0, 0);
var prAreaFill = ExcelGlobalVariables.prAreaFill;//XLColor.FromArgb(192, 192, 192);
var prAreaFont = ExcelGlobalVariables.prAreaFont;//XLColor.Black;
var prBorderWidth = XLBorderStyleValues.Thin;
var prFontSize = 10;
var prTitleFont = 20;
var prFootnoteSize = 8;
var prHeightSeperator = 7.5;
var prAreaObjectiveFontsize = 8;
var indentLength = 2;
var newLineHeight = 12.6;
var defNote = "Portal data from the Canadian Institute for Health Information (CIHI) has been used to generate data within this report with acknowledgement to CIHI, the Ministry of Health and Long-Term Care (MOHLTC) and Stats Canada (as applicable). Views are not those of the acknowledged sources. Facility identifiable data other than Mount Sinai Hospital (MSH) is not to be published without the consent of that organization (except where reported at an aggregate level). As this is not a database supported by MSH, please demonstrate caution with use and interpretation of the information. MSH is not responsible for any changes derived from the source data/canned reports. Data may be subject to change.";
var prNumberWidth = 4;
var prIndicatorWidth = 55;
var prValueWidth = 11;
var prDefWidth = 100;
var prRatiWidth = 50;
var prCompWidth = 50;
//var fitRatio = 3.77;
var fitRatio = 1.7;
List<int> fitAdjustableRows = new List<int>();
var prFootnoteCharsNewLine = 125;
var prObjectivesCharsNewLine = 226;
var allCoes = new List<CoEs>();
if (coeID != 0 && coeID != null)
{
allCoes = viewModel.allCoEs.Where(x => x.CoE_ID == coeID).ToList();
}
else
{
allCoes = viewModel.allCoEs.ToList();
}
foreach (var coe in allCoes)
{
var wsPRName = coe.CoE_Abbr != null && coe.CoE_Abbr != "" ? coe.CoE_Abbr : "Indicators";
var wsDefName = coe.CoE_Abbr != null && coe.CoE_Abbr != "" ? "Def_" + coe.CoE_Abbr : "Def_Indicators";
var wsPR = wb.Worksheets.Add(wsPRName);
var wsDef = wb.Worksheets.Add(wsDefName);
List<IXLWorksheet> wsList = new List<IXLWorksheet>();
wsList.Add(wsPR);
wsList.Add(wsDef);
foreach (var ws in wsList)
{
var currentRow = 4;
ws.Row(2).Height = 21;
int startRow;
int indicatorNumber = 1;
ws.PageSetup.Margins.Top = 0;
ws.PageSetup.Margins.Header = 0;
ws.PageSetup.Margins.Left = 0.5;
ws.PageSetup.Margins.Right = 0.5;
ws.PageSetup.Margins.Bottom = 0.5;
ws.PageSetup.PageOrientation = XLPageOrientation.Landscape;
ws.PageSetup.PaperSize = XLPaperSize.LegalPaper;
ws.PageSetup.FitToPages(1, 1);
string[,] columnHeaders = new string[0, 0];
if (ws.Name == wsPRName)
{
var prHeadder2Title = FiscalYear.FYStrFull("FY_", fiscalYear) + "Performance";
prHeadder2Title = prHeadder2Title.Replace("_", " ");
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{FiscalYear.FYStrFull("FY_3", fiscalYear), ""},
{FiscalYear.FYStrFull("FY_2", fiscalYear),""},
{FiscalYear.FYStrFull("FY_1", fiscalYear),""},
{prHeadder2Title,"Q1"},
{prHeadder2Title,"Q2"},
{prHeadder2Title,"Q3"},
{prHeadder2Title,"Q4"},
{prHeadder2Title,"YTD"},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Target",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Performance_Threshold",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator",""}
};
}
else if (ws.Name == wsDefName)
{
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Definition_Calculation",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Target_Rationale",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator_Source",""}
};
}
var currentCol = 1;
var prHeader2ColStart = 99;
var prHeader2ColEnd = 1;
int maxCol = columnHeaders.GetUpperBound(0) + 1;
var prTitle = ws.Cell(currentRow, 1);
prTitle.Value = coe.CoE;
prTitle.Style.Font.FontSize = prTitleFont;
prTitle.Style.Font.Bold = true;
prTitle.Style.Font.FontColor = prHeader1Font;
prTitle.Style.Fill.BackgroundColor = prHeader1Fill;
ws.Range(ws.Cell(currentRow, 1), ws.Cell(currentRow, maxCol)).Merge();
ws.Range(ws.Cell(currentRow + 1, 1), ws.Cell(currentRow + 1, maxCol)).Merge();
ws.Row(currentRow + 1).Height = prHeightSeperator;
currentRow += 2;
startRow = currentRow;
for (int i = 0; i <= columnHeaders.GetUpperBound(0); i++)
{
if (columnHeaders[i, 1] == "")
{
var columnField = columnHeaders[i, 0];
string cellValue;
Type t = typeof(Indicators);
cellValue = t.GetProperty(columnField) != null ?
ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicators), columnField).DisplayName :
ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicator_CoE_Maps), columnField).DisplayName;
ws.Cell(currentRow, currentCol).Value = cellValue;
ws.Range(ws.Cell(currentRow, currentCol), ws.Cell(currentRow + 1, currentCol)).Merge();
currentCol++;
}
else
{
var columnField = columnHeaders[i, 1];
var columnFieldTop = columnHeaders[i, 0];
ws.Cell(currentRow + 1, currentCol).Value = columnField;
ws.Cell(currentRow, currentCol).Value = columnFieldTop;
if (currentCol < prHeader2ColStart) { prHeader2ColStart = currentCol; }
if (currentCol > prHeader2ColEnd) { prHeader2ColEnd = currentCol; }
currentCol++;
}
}
currentCol--;
ws.Range(ws.Cell(currentRow, prHeader2ColStart).Address, ws.Cell(currentRow, prHeader2ColEnd).Address).Merge();
var prHeader1 = ws.Range(ws.Cell(currentRow, 1).Address, ws.Cell(currentRow + 1, currentCol).Address);
var prHeader2 = ws.Range(ws.Cell(currentRow + 1, prHeader2ColStart).Address, ws.Cell(currentRow + 1, prHeader2ColEnd).Address);
prHeader1.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
prHeader1.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
prHeader1.Style.Fill.BackgroundColor = prHeader1Fill;
prHeader1.Style.Font.FontColor = prHeader1Font;
prHeader2.Style.Fill.BackgroundColor = prHeader2Fill;
prHeader2.Style.Font.FontColor = prHeader2Font;
currentRow += 2;
List<Footnotes> footnotes = new List<Footnotes>();
foreach (var areaMap in coe.Area_CoE_Map.Where(x => x.Fiscal_Year == fiscalYear).OrderBy(x => x.Area.Sort))
{
var cellLengthObjective = 0;
var prArea = ws.Range(ws.Cell(currentRow, 1), ws.Cell(currentRow, maxCol));
//fitAdjustableRows.Add(currentRow);
prArea.Merge();
prArea.Style.Fill.BackgroundColor = prAreaFill;
prArea.Style.Font.FontColor = prAreaFont;
prArea.FirstCell().RichText.AddText(areaMap.Area.Area).Bold = true;
cellLengthObjective += areaMap.Area.Area.Length;
if (ws == wsPR)
{
var indent = new string('_', indentLength);
var stringSeperators = new string[] { "•" };
if (areaMap.Objective != null)
{
var objectives = Regex.Matches(areaMap.Objective, @"\[.*?\]").Cast<Match>().Select(m => m.Value.Substring(1, m.Value.Length - 2)).ToList();
//for (var i = 1; i < objectives.Length; i++)
var i = 1;
foreach (var objective in objectives)
{
prArea.FirstCell().RichText.AddNewLine();
ws.Row(currentRow).Height += newLineHeight;
prArea.FirstCell().RichText.AddText(indent).SetFontColor(prAreaFill).SetFontSize(prAreaObjectiveFontsize);
prArea.FirstCell().RichText.AddText(" " + i +". " + objective).FontSize = prAreaObjectiveFontsize;
i++;
}
}
}
currentRow++;
var allMaps = viewModel.allMaps.Where(x => x.Fiscal_Year == fiscalYear).Where(e => e.Indicator.Area.Equals(areaMap.Area)).Where(d => d.CoE.CoE != null && d.CoE.CoE.Contains(coe.CoE)).OrderBy(f => f.Number).ToList();
var allNValues = new List<Indicator_CoE_Maps>();
if (ws.Name == wsPRName)
{
allNValues = viewModel.allMaps.Where(x => x.Fiscal_Year == fiscalYear && x.Indicator.Indicator_N_Value == true).ToList();
}
var allMapsWithNValues = new List<Indicator_CoE_Maps>();
foreach (var nValue in allNValues)
{
var indicatorIndex = allMaps.FirstOrDefault(x => x.Indicator_ID == nValue.Indicator.Indicator_N_Value_ID);
if (indicatorIndex != null)
{
var position = allMaps.IndexOf(indicatorIndex);
allMapsWithNValues.Add(indicatorIndex);
allMaps.Insert(position + 1, nValue);
}
}
foreach (var map in allMaps)
{
fitAdjustableRows.Add(currentRow);
currentCol = 1;
int rowSpan = 1;
if (allMapsWithNValues.Contains(map) || !allNValues.Contains(map))
{
if (allMapsWithNValues.Contains(map))
{
rowSpan = 2;
ws.Range(ws.Cell(currentRow, currentCol), ws.Cell(currentRow + 1, currentCol)).Merge();
ws.Range(ws.Cell(currentRow, currentCol + 1), ws.Cell(currentRow + 1, currentCol + 1)).Merge();
}
ws.Cell(currentRow, currentCol).Style.Border.OutsideBorder = prBorderWidth;
ws.Cell(currentRow, currentCol).Style.Border.OutsideBorderColor = prBorder;
ws.Cell(currentRow, currentCol).Value = indicatorNumber;
indicatorNumber++;
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
currentCol++;
ws.Cell(currentRow, currentCol).Style.Border.OutsideBorder = prBorderWidth;
ws.Cell(currentRow, currentCol).Style.Border.OutsideBorderColor = prBorder;
int j = 0;
ws.Cell(currentRow, currentCol).Value = map.Indicator.Indicator;
foreach (var footnote in map.Indicator.Indicator_Footnote_Map.Where(x => x.Fiscal_Year == fiscalYear).Where(e => e.Indicator_ID == map.Indicator_ID).OrderBy(e => e.Indicator_ID))
{
if (!footnotes.Contains(footnote.Footnote)) { footnotes.Add(footnote.Footnote); }
if (j != 0)
{
ws.Cell(currentRow, currentCol).RichText.AddText(",").VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
}
ws.Cell(currentRow, currentCol).RichText.AddText(footnote.Footnote.Footnote_Symbol).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
j++;
}
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
}
else
{
ws.Cell(currentRow, currentCol).Style.Border.OutsideBorder = prBorderWidth;
ws.Cell(currentRow, currentCol).Style.Border.OutsideBorderColor = prBorder;
currentCol += 2;
rowSpan = 0;
}
if (ws.Name == wsPRName)
{
for (var i = 3; i <= 15; i++)
{
ws.Column(i).Width = ws.Name == wsPRName ? prValueWidth : prDefWidth;
}
var obj = map.Indicator;
var type = obj.GetType();
string[,] columnIndicators = new string[,]{
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_3",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_3",fiscalYear) + "_Sup").GetValue(obj,null),
"",
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_2",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_2",fiscalYear) + "_Sup").GetValue(obj,null),
"",
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_1",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_1",fiscalYear) + "_Sup").GetValue(obj,null),
"",
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1_Color").GetValue(obj,null),
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2_Color").GetValue(obj,null),
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3_Color").GetValue(obj,null),
"1",
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4_Color").GetValue(obj,null),
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD_Color").GetValue(obj,null),
"1"
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Target").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Target_Sup").GetValue(obj,null),
"",
rowSpan.ToString()
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Performance_Threshold").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Performance_Threshold_Sup").GetValue(obj,null),
"",
rowSpan.ToString()
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Comparator").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Comparator_Sup").GetValue(obj,null),
"",
rowSpan.ToString()
},
};
var startCol = currentCol;
int k = 1;
for (var i = 0; i <= columnIndicators.GetUpperBound(0); i++)
{
for (var j = 0; j <= columnIndicators.GetUpperBound(1); j++)
{
if (columnIndicators[i, j] != null)
{
columnIndicators[i, j] = columnIndicators[i, j].Replace("<b>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</b>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<u>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</u>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<i>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</i>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<sup>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</sup>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<sub>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</sub>", "");
}
}
if (i != columnIndicators.GetUpperBound(0) && columnIndicators[i, 0] == "=")
{
k = 1;
while (columnIndicators[i + k, 0] == "=") { k++; }
ws.Range(ws.Cell(currentRow, startCol + i - 1), ws.Cell(currentRow, startCol + i + k - 1)).Merge();
i += k - 1;
k = 1;
}
else if (columnIndicators[i, 0] != "=")
{
ws.Cell(currentRow, currentCol + i).Style.Border.OutsideBorder = prBorderWidth;
ws.Cell(currentRow, currentCol + i).Style.Border.OutsideBorderColor = prBorder;
if (columnIndicators[i, 3] != "0")
{
if (columnIndicators[i, 3] == "2") {
ws.Range(ws.Cell(currentRow, currentCol + i), ws.Cell(currentRow + 1, currentCol + i)).Merge();
}
if (allNValues.Contains(map))
{
ws.Cell(currentRow, currentCol + i).Style.Border.TopBorder = XLBorderStyleValues.None;
}
else if (allMapsWithNValues.Contains(map))
{
ws.Cell(currentRow, currentCol + i).Style.Border.BottomBorder = XLBorderStyleValues.None;
}
var cell = ws.Cell(currentRow, currentCol + i);
string cellValue = "";
if (columnIndicators[i, 0] != null)
{
cellValue = columnIndicators[i, 0].ToString();
}
if (cellValue.Contains("$"))
{
}
cell.Value = "'" + cellValue;
if (columnIndicators[i, 1] != null)
{
cell.RichText.AddText(columnIndicators[i, 1]).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
}
switch (columnIndicators[i, 2])
{
case "cssWhite":
cell.RichText.SetFontColor(XLColor.Black);
cell.Style.Fill.BackgroundColor = XLColor.White;
break;
case "cssGreen":
cell.RichText.SetFontColor(XLColor.White);
cell.Style.Fill.BackgroundColor = prGreen;
break;
case "cssYellow":
cell.RichText.SetFontColor(XLColor.Black);
cell.Style.Fill.BackgroundColor = prYellow;
break;
case "cssRed":
cell.RichText.SetFontColor(XLColor.White);
cell.Style.Fill.BackgroundColor = prRed;
break;
}
cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
}
}
}
currentRow++;
}
else if (ws.Name == wsDefName)
{
ws.Column(3).Width = prDefWidth;
ws.Column(4).Width = prRatiWidth;
ws.Column(5).Width = prCompWidth;
var obj = map.Indicator;
var type = obj.GetType();
string defn = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Definition_Calculation").GetValue(obj, null);
string rationale = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Target_Rationale").GetValue(obj, null);
string comp = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator_Source").GetValue(obj, null);
double maxLines = 1;
double lines;
if (defn != null)
{
lines = defn.Length / ws.Column(currentCol).Width;
maxLines = maxLines < lines ? lines : maxLines;
ws.Cell(currentRow, currentCol).Value = defn;
}
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
if (rationale != null)
{
lines = rationale.Length / ws.Column(currentCol).Width;
maxLines = maxLines < lines ? lines : maxLines;
ws.Cell(currentRow, currentCol).Value = rationale;
}
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
if (comp != null)
{
lines = comp.Length / ws.Column(currentCol).Width;
maxLines = maxLines < lines ? lines : maxLines;
ws.Cell(currentRow, currentCol).Value = comp;
}
ws.Cell(currentRow, currentCol).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
currentCol++;
ws.Row(currentRow).Height = newLineHeight * Math.Ceiling(maxLines);
currentRow++;
}
}
}
var footnoteRow = ws.Range(ws.Cell(currentRow, 1), ws.Cell(currentRow, maxCol));
footnoteRow.Merge();
footnoteRow.Style.Font.FontSize = prFootnoteSize;
/*Footnotes defaultFootnote = db.Footnotes.FirstOrDefault(x => x.Footnote_Symbol == "*");
if (!footnotes.Contains(defaultFootnote))
{
footnotes.Add(defaultFootnote);
}*/
int cellLengthFootnote = 0;
if (ws.Name == wsPRName)
{
foreach (var footnote in footnotes.OrderBy(x=>x.Footnote_Order))
{
ws.Cell(currentRow, 1).RichText.AddText(footnote.Footnote_Symbol).VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
ws.Cell(currentRow, 1).RichText.AddText(" " + footnote.Footnote + ";");
ws.Cell(currentRow, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Top;
cellLengthFootnote += footnote.Footnote_Symbol.ToString().Length + footnote.Footnote.ToString().Length + 2;
if (cellLengthFootnote > prFootnoteCharsNewLine)
{
ws.Cell(currentRow, 1).RichText.AddNewLine();
cellLengthFootnote = 0;
ws.Row(currentRow).Height += newLineHeight;
}
}
}
else
{
ws.Cell(currentRow, 1).Value = defNote;
ws.Row(currentRow).Height = 28;
}
var pr = ws.Range(ws.Cell(startRow, 1), ws.Cell(currentRow - 1, maxCol));
if (pr.Worksheet.Name == wsDefName)
{
pr.Style.Border.InsideBorder = prBorderWidth;
pr.Style.Border.InsideBorderColor = prBorder;
}
pr.Style.Border.OutsideBorder = prBorderWidth;
pr.Style.Border.OutsideBorderColor = prBorder;
pr.Style.Font.FontSize = prFontSize;
pr = ws.Range(ws.Cell(startRow, 1), ws.Cell(currentRow, maxCol));
pr.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
pr.Style.Alignment.WrapText = true;
ws.Column(1).Width = prNumberWidth;
ws.Column(2).Width = prIndicatorWidth;
footnotes.Clear();
indicatorNumber = 1;
var totalHeight = ExcelFunctions.getTotalHeight(ws, 4);
var totalWidth = ExcelFunctions.getTotalWidth(ws, 1);
var fitHeight = (int)(totalWidth / fitRatio);
var fitWidth = (int)(totalHeight * fitRatio);
if (ws.Name == "Def_WIH Obs") { System.Diagnostics.Debugger.Break(); }
if (fitHeight > totalHeight)
{
var fitAddHeightTotal = (fitHeight - totalHeight);
var fitAddHeightPerRow = fitAddHeightTotal / fitAdjustableRows.Count;
foreach (var row in fitAdjustableRows)
{
ws.Row(row).Height += fitAddHeightPerRow;
}
}
else
{
while ((fitWidth - totalWidth) / fitWidth > 0.001)
{
var fitAddWidthTotal = (fitWidth - totalWidth) / 10;
var fitAddWidthPerRow = fitAddWidthTotal / (ws.LastColumnUsed().ColumnNumber() - 1);
foreach (var col in ws.Columns(2, ws.LastColumnUsed().ColumnNumber()))
{
col.Width += fitAddWidthPerRow / 5.69;
}
ExcelFunctions.AutoFitWorksheet(ws, 2, 3, newLineHeight);
totalHeight = ExcelFunctions.getTotalHeight(ws, 4);
totalWidth = ExcelFunctions.getTotalWidth(ws, 1);
fitHeight = (int)(totalWidth / fitRatio);
fitWidth = (int)(totalHeight * fitRatio);
}
}
}
}
MemoryStream preImage = new MemoryStream();
wb.SaveAs(preImage);
//Aspose.Cells.Workbook test = new Aspose.Cells.Workbook(preImage);
//test.Save(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/logo.pdf"), Aspose.Cells.SaveFormat.Pdf);
MemoryStream postImage = new MemoryStream();
SLDocument postImageWb = new SLDocument(preImage);
string picPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/logo.png");
SLPicture picLogo = new SLPicture(picPath);
string picPathOPEO = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/logoOPEO.png");
SLPicture picLogoOPEO = new SLPicture(picPathOPEO);
string picMonthlyPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/Monthly.png");
SLPicture picMonthly = new SLPicture(picMonthlyPath);
string picQuaterlyPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/quaterly.png");
SLPicture picQuaterly = new SLPicture(picQuaterlyPath);
string picNAPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/na.png");
SLPicture picNA = new SLPicture(picNAPath);
string picTargetPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/target.png");
SLPicture picTarget = new SLPicture(picTargetPath);
foreach (var ws in wb.Worksheets)
{
postImageWb.SelectWorksheet(ws.Name);
for (int i = 1; i < 20; ++i)
{
var a = postImageWb.GetRowHeight(i);
}
picLogo.SetPosition(0, 0);
picLogo.ResizeInPercentage(25, 25);
postImageWb.InsertPicture(picLogo);
picLogoOPEO.SetRelativePositionInPixels(0, ws.LastColumnUsed().ColumnNumber() + 1, -140, 0);
picLogoOPEO.ResizeInPercentage(45, 45);
postImageWb.InsertPicture(picLogoOPEO);
if (ws.Name.Substring(0, 3) != "Def")
{
picTarget.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -240, 1);
picNA.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -400, 1);
picMonthly.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -500, 1);
picQuaterly.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -490, 1);
picMonthly.ResizeInPercentage(70, 70);
picQuaterly.ResizeInPercentage(70, 70);
picNA.ResizeInPercentage(70, 70);
picTarget.ResizeInPercentage(70, 70);
postImageWb.InsertPicture(picMonthly);
postImageWb.InsertPicture(picQuaterly);
postImageWb.InsertPicture(picNA);
postImageWb.InsertPicture(picTarget);
}
}
// Prepare the response
HttpResponse httpResponse = this.HttpContext.ApplicationInstance.Context.Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
httpResponse.AddHeader("content-disposition", "attachment;filename=\"test.xlsx\"");
//httpResponse.ContentType = "application/pdf";
//httpResponse.AddHeader("content-disposition", "attachment;filename=\"test.pdf\"");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
postImageWb.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
return View(viewModel);
}
private static DataTable PrepareDataTableForExcel(SLDocument doc)
{
var indexedColumnNames = GetIndexedColumnNames(doc);
var table = new DataTable();
foreach (var columnName in indexedColumnNames)
{
table.Columns.Add(columnName.Value);
}
return table;
}
public static byte[] ExportarDetalleProcesoExcel(Func<SLDocument, bool> body)
{
using (var document = new SLDocument())
{
var name = document.GetCurrentWorksheetName();
document.AddWorksheet("DetalleProceso");
document.DeleteWorksheet(name);
document.SelectWorksheet("DetalleProceso");
body(document);
var stream = new MemoryStream();
document.SaveAs(stream);
stream.Position = 0;
var buffer = new byte[stream.Length];
stream.Read(buffer, 0, buffer.Length);
return buffer;
}
}
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;
}
