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 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); } }
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)); }
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; }
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ş!.."); } }
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); } }
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)); }
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; }
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; }