private int writeToExcel(string type, int startRow, ExcelWorksheet ws, string salesId)
{
DataTable dt = new DataTable();
int result = startRow;
List <string> titles = new List <string>();
ws.Cells.Style.Font.Size = 11; // Default font size for whole sheet
ws.Cells.Style.Font.Name = "Calibri"; // Default Font name for whole sheet
var reportYear = Convert.ToInt16(lbReportYear.Content);
var reportMonth = Convert.ToInt16(lbReportMonth.Content);
var startYearMonth1 = (reportYear - 1).ToString() + "/07";
var currentYearMonth1 = reportMonth >= 7 ? (reportYear - 1).ToString() + "/" + reportMonth.ToString() : (reportYear).ToString() + "/" + reportMonth.ToString();
var startYearMonth2 = (reportYear - 2).ToString() + "/07";
var currentYearMonth2 = reportMonth >= 7 ? (reportYear - 2).ToString() + "/" + reportMonth.ToString() : (reportYear - 1).ToString() + "/" + reportMonth.ToString();
var startYearMonth3 = (reportYear - 3).ToString() + "/07";
var currentYearMonth3 = reportMonth >= 7 ? (reportYear - 3).ToString() + "/" + reportMonth.ToString() : (reportYear - 2).ToString() + "/" + reportMonth.ToString();
// Set Title for diffrent table
switch (type)
{
case "s1t1":
case "s1t3":
ws.Name = "年度比較"; // Setting Sheet's name
titles.Add("業務員");
titles.Add(reportYear.ToString() + "年度" + "(" + startYearMonth1 + "~" + currentYearMonth1 + ")");
titles.Add((reportYear - 1).ToString() + "年度" + "(" + startYearMonth2 + "~" + currentYearMonth2 + ")");
titles.Add((reportYear - 2).ToString() + "年度" + "(" + startYearMonth2 + "~" + currentYearMonth3 + ")");
titles.Add((reportYear - 1).ToString() + "v.s" + reportYear.ToString() + "成長率");
titles.Add((reportYear - 2).ToString() + "v.s" + reportYear.ToString() + "成長率");
if (type == "s1t1")
{
ws.Cells[startRow, 1].Value = "同期間比較(已銷+未出)";
dt = ReportsBLL.getSalesReportS1T1();
}
else
{
ws.Cells[startRow, 1].Value = "同期間比較(已銷不含未出)";
dt = ReportsBLL.getSalesReportS1T3();
}
break;
case "s1t2":
titles.Add("業務員");
titles.Add(reportYear.ToString() + "年度已銷+未出(累計至" + currentYearMonth1 + ")");
titles.Add((reportYear - 1).ToString() + "銷貨金額");
titles.Add((reportYear - 2).ToString() + "銷貨金額");
titles.Add(reportYear.ToString() + "達成率");
titles.Add(reportYear.ToString() + "預估");
titles.Add("預估與實際達成率");
ws.Cells[startRow, 1].Value = "累計 vs 銷售";
dt = ReportsBLL.getSalesReportS1T2();
break;
case "s2":
ws.Name = "當月、年度比較"; // Setting Sheet's name
titles.Add("業務員");
titles.Add("客戶編號");
titles.Add("客戶名稱");
titles.Add(reportYear.ToString() + "年度已銷(" + startYearMonth1 + "~" + currentYearMonth1 + ")");
titles.Add((reportYear - 1).ToString() + "年度已銷(" + startYearMonth2 + "~" + currentYearMonth2 + ")");
titles.Add((reportYear - 2).ToString() + "年度已銷(" + startYearMonth3 + "~" + currentYearMonth3 + ")");
titles.Add((reportYear - 1).ToString() + "v.s" + reportYear.ToString() + "同期成長率((D/E)-1)");
titles.Add(reportYear.ToString() + "年度已接未出貨(" + reportYear.ToString() + "/06/30)");
titles.Add(reportYear.ToString() + "年度已銷+未出(" + startYearMonth1 + "~" + currentYearMonth1 + ")(D+H)");
titles.Add((reportYear - 1).ToString() + "年度已銷+未出(" + startYearMonth2 + "~" + currentYearMonth2 + ")");
titles.Add((reportYear - 2).ToString() + "年度已銷+未出(" + startYearMonth3 + "~" + currentYearMonth3 + ")");
titles.Add((reportYear - 1).ToString() + "v.s" + reportYear.ToString() + "成長率((I/J)-1)");
titles.Add((reportYear - 2).ToString() + "v.s" + reportYear.ToString() + "成長率((I/K)-1)");
titles.Add((reportYear - 1).ToString() + "總銷貨額");
titles.Add((reportYear - 2).ToString() + "總銷貨額");
titles.Add(reportYear.ToString() + "年度達成率(I/N)");
titles.Add(reportYear.ToString() + "預估");
titles.Add("預估與實際達成率(I/Q)");
titles.Add(reportYear.ToString() + "/" + reportMonth.ToString() + "下單金額");
if (salesId != "")
{
dt = ReportsBLL.getSalesPersonalReport(salesId);
}
else
{
dt = ReportsBLL.getSalesPersonalReportTotal();
}
break;
case "s3":
ws.Name = "每月預估出貨金額"; // Setting Sheet's name
titles.Add("業務員");
titles.Add("客戶編號");
titles.Add("客戶名稱");
titles.Add((reportYear - 1).ToString() + ".07");
titles.Add((reportYear - 1).ToString() + ".08");
titles.Add((reportYear - 1).ToString() + ".09");
titles.Add((reportYear - 1).ToString() + ".10");
titles.Add((reportYear - 1).ToString() + ".11");
titles.Add((reportYear - 1).ToString() + ".12");
titles.Add((reportYear).ToString() + ".01");
titles.Add((reportYear).ToString() + ".02");
titles.Add((reportYear).ToString() + ".03");
titles.Add((reportYear).ToString() + ".04");
titles.Add((reportYear).ToString() + ".05");
titles.Add((reportYear).ToString() + ".06");
titles.Add("Total");
if (salesId != "")
{
dt = ReportsBLL.getSalesPersonalForecast(salesId);
}
else
{
dt = ReportsBLL.getSalesPersonalForecastTotal();
}
break;
}
if (dt.Rows.Count > 1)
{
if (type != "s2" && type != "s3")
{
// Merging cells and create a center heading for out table
ws.Cells[startRow, 1, startRow, dt.Columns.Count - 1].Merge = true;
ws.Cells[startRow, 1, startRow, dt.Columns.Count - 1].Style.Font.Bold = true;
//ws.Cells[startRow, 1, startRow, dt.Columns.Count-1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Column(1).Width = 18;
ws.Column(2).Width = 18;
ws.Column(3).Width = 18;
ws.Column(4).Width = 18;
ws.Column(5).Width = 12;
ws.Column(6).Width = 18;
ws.Column(7).Width = 12;
}
else if (type == "s2")
{
ws.Column(1).Width = 12;
ws.Column(2).Width = 12;
ws.Column(3).Width = 12;
ws.Column(4).Width = 18;
ws.Column(5).Width = 18;
ws.Column(6).Width = 18;
ws.Column(7).Width = 12;
ws.Column(8).Width = 18;
ws.Column(9).Width = 18;
ws.Column(10).Width = 18;
ws.Column(11).Width = 18;
ws.Column(12).Width = 12;
ws.Column(13).Width = 12;
ws.Column(14).Width = 18;
ws.Column(15).Width = 18;
ws.Column(16).Width = 12;
ws.Column(17).Width = 18;
ws.Column(18).Width = 12;
ws.Column(19).Width = 18;
}
else
{
ws.Column(1).Width = 12;
ws.Column(2).Width = 12;
ws.Column(3).Width = 12;
ws.Column(4).Width = 14;
ws.Column(5).Width = 14;
ws.Column(6).Width = 14;
ws.Column(7).Width = 14;
ws.Column(8).Width = 14;
ws.Column(9).Width = 14;
ws.Column(10).Width = 14;
ws.Column(11).Width = 14;
ws.Column(12).Width = 14;
ws.Column(13).Width = 14;
ws.Column(14).Width = 14;
ws.Column(15).Width = 14;
ws.Column(16).Width = 14;
ws.Column(17).Width = 14;
}
int colIndex = 1;
int rowIndex = startRow + 1;
// Create table header
if (type == "s2" && startRow != 1 || type == "s3" && startRow != 1)
{
}
else
{
foreach (var title in titles)
{
var cell = ws.Cells[rowIndex, colIndex];
cell.Style.WrapText = true;
// Setting Top/left,right/bottom borders.
var border = cell.Style.Border;
border.Bottom.Style =
border.Top.Style =
border.Left.Style =
border.Right.Style = ExcelBorderStyle.Thin;
// Setting Value in cell
cell.Value = title;
colIndex++;
}
}
// Insert table content
//foreach (DataRow dr in dt.Rows) // Adding Data into rows
for (int i = 0; i < dt.Rows.Count; i++)
{
rowIndex++;
for (int j = 1; j < dt.Columns.Count; j++)
{
var cell = ws.Cells[rowIndex, j];
switch (type)
{
case "s1t1":
case "s1t3":
cell.Value = dt.Rows[i][j];
if (j >= 2 && j <= 4)
{
cell.Style.Numberformat.Format = "$#,##0";
}
else if (j >= 5 && j <= 6)
{
cell.Style.Numberformat.Format = "0.00%";
}
break;
case "s1t2":
cell.Value = dt.Rows[i][j];
if (j >= 2 && j <= 4 || j == 6)
{
cell.Style.Numberformat.Format = "$#,##0";
}
else if (j == 5 || j == 7)
{
cell.Style.Numberformat.Format = "0.00%";
}
break;
case "s2":
if (i != 0 && j == 1)
{
}
else
{
cell.Value = dt.Rows[i][j];
}
if (j >= 4 && j <= 6 || j >= 8 && j <= 11 || j >= 14 && j <= 15 || j == 17 || j == 19)
{
cell.Style.Numberformat.Format = "$#,##0";
}
else if (j == 7 || j >= 12 && j <= 13 || j == 16 || j == 18)
{
cell.Style.Numberformat.Format = "0.00%";
}
break;
case "s3":
if (i != 0 && j == 1)
{
}
else
{
cell.Value = dt.Rows[i][j];
}
if (j >= 4)
{
cell.Style.Numberformat.Format = "$#,##0";
}
break;
}
// Setting borders of cell
var border = cell.Style.Border;
border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
colIndex++;
}
}
if (type != "s2" && type != "s3")
{
result += dt.Rows.Count + 5;
}
else
{
result += dt.Rows.Count;
}
}
dt.Clear();
return(result);
}