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;
}
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);
}
}
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;
}
