private void btnCheck_Click(object sender, EventArgs e)
{
try
{
objExDb = new ExcelDB();
objUtilityDB = new UtilityDB();
DataTable dtExcel = new DataTable();
dtExcel = objExDb.GetBrPayRollCheckListReportsData(docMonth.Value.ToString("MMMyyyy").ToUpper(), CommonData.CompanyCode, CommonData.BranchCode, "ALL", cbPayRollType.SelectedItem.ToString(), "PAYCHECKLIST").Tables[0];
objExDb = null;
if (dtExcel.Rows.Count > 0)
{
Excel.Application oXL = new Excel.Application();
Excel.Workbook theWorkbook = oXL.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)oXL.ActiveSheet;
oXL.Visible = true;
string sLastColumn = objUtilityDB.GetColumnName(51);
Excel.Range rg = worksheet.get_Range("A3", sLastColumn + "3");
Excel.Range rgData = worksheet.get_Range("A3", sLastColumn + (Convert.ToInt32(dtExcel.Rows.Count) + 3).ToString());
rgData.Font.Size = 11;
rgData.WrapText = true;
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.Borders.Weight = 2;
rg.Font.Bold = true;
rg.Font.Name = "Times New Roman";
rg.Font.Size = 10;
rg.WrapText = true;
rg.Font.ColorIndex = 2; // White Color : 2 and Red = 3,30; Green = 10,43;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Interior.ColorIndex = 31;
rg.Borders.Weight = 2;
rg.Borders.LineStyle = Excel.Constants.xlSolid;
rg.Cells.RowHeight = 38;
rg = worksheet.get_Range("A3", Type.Missing);
rg.Cells.ColumnWidth = 4;
rg = worksheet.get_Range("B3", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("C3", Type.Missing);
rg.Cells.ColumnWidth = 6;
rg = worksheet.get_Range("D3", Type.Missing);
rg.Cells.ColumnWidth = 30;
rg = worksheet.get_Range("E3", Type.Missing);
rg.Cells.ColumnWidth = 30;
rg = worksheet.get_Range("F3", Type.Missing);
rg.Cells.ColumnWidth = 30;
rg = worksheet.get_Range("G3", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("H3", Type.Missing);
rg.Cells.ColumnWidth = 7;
rg = worksheet.get_Range("I3", Type.Missing);
rg.Cells.ColumnWidth = 7;
//rg = worksheet.get_Range("I3", Type.Missing);
//rg.Cells.ColumnWidth = 10;
//rg.Interior.ColorIndex = 44;
rg = worksheet.get_Range(objUtilityDB.GetColumnName(47) + "3", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range(objUtilityDB.GetColumnName(48) + "3", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range(objUtilityDB.GetColumnName(51) + "3", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range(objUtilityDB.GetColumnName(49) + "3", Type.Missing);
rg.Cells.ColumnWidth = 20;
rg = worksheet.get_Range(objUtilityDB.GetColumnName(50) + "3", Type.Missing);
rg.Cells.ColumnWidth = 20;
Excel.Range rgHead = null;
rgHead = worksheet.get_Range("A1", "I2");
rgHead.Merge(Type.Missing);
rgHead.Font.Size = 14;
rgHead.Font.ColorIndex = 1;
rgHead.Font.Bold = true;
rgHead.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.VerticalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.Value2 = "BRANCH PAY ROLL REGISTER FOR THE MONTH OF " + dtExcel.Rows[0]["HPCM_WAGEMONTH"].ToString() + "";
int iColumn = 1;
worksheet.Cells[3, iColumn++] = "SlNo";
worksheet.Cells[3, iColumn++] = "Company";
worksheet.Cells[3, iColumn++] = "Ecode";
worksheet.Cells[3, iColumn++] = "Name";
worksheet.Cells[3, iColumn++] = "Desig";
worksheet.Cells[3, iColumn++] = "Department";
worksheet.Cells[3, iColumn++] = "Doj";
worksheet.Cells[3, iColumn++] = "Present Daye";
worksheet.Cells[3, iColumn++] = "Lops";
for (int i = 0; i < 2; i++)
{
rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iColumn) + "2", objUtilityDB.GetColumnName(iColumn + 13) + "2");
//rgHead.Cells.ColumnWidth = 5;
rgHead.Merge(Type.Missing);
if (i == 0)
{
rgHead.Value2 = "ACTUALS";
}
else
{
rgHead.Value2 = "EARNINGS";
}
rgHead.Interior.ColorIndex = 44 + i;
rgHead.Borders.Weight = 2;
rgHead.Borders.LineStyle = Excel.Constants.xlSolid;
rgHead.Cells.RowHeight = 20;
rgHead.Font.Size = 14;
rgHead.Font.ColorIndex = 1;
rgHead.Font.Bold = true;
rgHead.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.VerticalAlignment = Excel.Constants.xlCenter;
rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iColumn) + "3", objUtilityDB.GetColumnName(iColumn + 13) + "3");
rgHead.Interior.ColorIndex = 44 + i;
rgHead.Font.ColorIndex = 1;
rgHead.Cells.ColumnWidth = 5;
worksheet.Cells[3, iColumn++] = "PF Basic";
worksheet.Cells[3, iColumn++] = "Basic";
worksheet.Cells[3, iColumn++] = "HRA";
worksheet.Cells[3, iColumn++] = "Conv Allw";
worksheet.Cells[3, iColumn++] = "CCA Allw";
worksheet.Cells[3, iColumn++] = "LTA Allw";
worksheet.Cells[3, iColumn++] = "Special Allw";
worksheet.Cells[3, iColumn++] = "Books & Period.";
worksheet.Cells[3, iColumn++] = "Med Reimb";
worksheet.Cells[3, iColumn++] = "Children";
worksheet.Cells[3, iColumn++] = "Vehicle Allw";
worksheet.Cells[3, iColumn++] = "Petrol Allw";
worksheet.Cells[3, iColumn++] = "Uniform Allw";
worksheet.Cells[3, iColumn++] = "Earning Total";
}
rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iColumn) + "2", objUtilityDB.GetColumnName(iColumn + 7) + "2");
//rgHead.Cells.ColumnWidth = 5;
rgHead.Merge(Type.Missing);
rgHead.Value2 = "DEDUCTIONS";
rgHead.Interior.ColorIndex = 46;
rgHead.Borders.Weight = 2;
rgHead.Borders.LineStyle = Excel.Constants.xlSolid;
rgHead.Cells.RowHeight = 20;
rgHead.Font.Size = 14;
rgHead.Font.ColorIndex = 1;
rgHead.Font.Bold = true;
rgHead.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.VerticalAlignment = Excel.Constants.xlCenter;
rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iColumn) + "3", objUtilityDB.GetColumnName(iColumn + 7) + "3");
rgHead.Interior.ColorIndex = 46;
rgHead.Font.ColorIndex = 1;
rgHead.Cells.ColumnWidth = 5;
worksheet.Cells[3, iColumn++] = "PF";
worksheet.Cells[3, iColumn++] = "Proff Tax";
worksheet.Cells[3, iColumn++] = "ESI";
worksheet.Cells[3, iColumn++] = "Sal Adv";
worksheet.Cells[3, iColumn++] = "Pers Loan";
worksheet.Cells[3, iColumn++] = "TDS";
worksheet.Cells[3, iColumn++] = "Others";
worksheet.Cells[3, iColumn++] = "Total Deductions";
worksheet.Cells[3, iColumn++] = "Net Pay";
worksheet.Cells[3, iColumn++] = "Pay Mode";
worksheet.Cells[3, iColumn++] = "Bank Name";
worksheet.Cells[3, iColumn++] = "Account No";
worksheet.Cells[3, iColumn++] = "PF No";
worksheet.Cells[3, iColumn++] = "ESI No";
iColumn = 1;
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
worksheet.Cells[i + 4, iColumn++] = i + 1;
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_COMPANY_CODE"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_EORA_CODE"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_NAME"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_DESIG"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_DEPT_NAME"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_DOJ"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PRE"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_LOP"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PF_BASIC"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_BASIC"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_HRA"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_CONV_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_CCA"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_LTA_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_SPL_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_BNP_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_MED_REIMB"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_CH_ED_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_VEH_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PET_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_UNF_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = "=U" + (i + 4).ToString() + "+V" + (i + 4).ToString() +
"+K" + (i + 4).ToString() + "+L" + (i + 4).ToString() +
"+M" + (i + 4).ToString() + "+N" + (i + 4).ToString() +
"+O" + (i + 4).ToString() + "+P" + (i + 4).ToString() +
"+Q" + (i + 4).ToString() + "+R" + (i + 4).ToString() +
"+S" + (i + 4).ToString() + "+T" + (i + 4).ToString() + "";
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_Pf_ERNG_BASIC"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ERNG_BASIC"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ERNG_HRA"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ERNG_CONV_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ERNG_CCA"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ERNG_LTA_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ERNG_SPL_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ERNG_BNP_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ERNG_MED_REIMB"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ERNG_CH_ED_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ERNG_VEH_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ERNG_PET_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ERNG_UNF_ALW"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ERNG_TOTAL"].ToString();
//worksheet.Cells[i + 4, iColumn++] = "=AG" + (i + 4).ToString() + "+AH" + (i + 4).ToString() +
// "+AI" + (i + 4).ToString() + "+AJ" + (i + 4).ToString() +
// "+Y" + (i + 4).ToString() + "+Z" + (i + 4).ToString() +
// "+AA" + (i + 4).ToString() + "+AB" + (i + 4).ToString() +
// "+AC" + (i + 4).ToString() + "+AD" + (i + 4).ToString() +
// "+AE" + (i + 4).ToString() + "+AF" + (i + 4).ToString() + "";
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_DEDU_PF"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_DEDU_PROFTAX"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_DEDU_ESI"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_DEDU_SAL_ADV"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_DEDU_PERS_LOAN"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_DEDU_TDS"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_DEDU_OTHERS"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_DEDU_TOTAL"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_NET_PAY"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PAY_MODE"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_BANK_NAME"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_BANK_ACCOUNT_NO"].ToString() + "'";
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_PF_NUMBER"].ToString() + "";
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["HPCM_PS_ESI_NUMBER"].ToString() + "";
iColumn = 1;
}
}
else
{
MessageBox.Show("No Data Found!", "SSERP", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void btnDownload_Click(object sender, EventArgs e)
{
objSQLDB = new SQLDB();
string sqlQry = "";
DataSet dsData = new DataSet();
if (cmbStatus.SelectedIndex == 0)
{
sqlQry = "EXEC GetBroucherListForStationaryDispatch 'APPROVED','" + dtpFromDate.Value.ToString("dd/MMM/yyyy") + "','" + dtpToDate.Value.ToString("dd/MMM/yyyy") + "','EXCEL'";
}
else if (cmbStatus.SelectedIndex == 1)
{
sqlQry = "EXEC GetBroucherListForStationaryDispatch 'DISPATCHED','" + dtpFromDate.Value.ToString("dd/MMM/yyyy") + "','" + dtpToDate.Value.ToString("dd/MMM/yyyy") + "','EXCEL'";
}
else if (cmbStatus.SelectedIndex == 2)
{
sqlQry = "EXEC GetBroucherListForStationaryDispatch 'DELIVERED','" + dtpFromDate.Value.ToString("dd/MMM/yyyy") + "','" + dtpToDate.Value.ToString("dd/MMM/yyyy") + "','EXCEL'";
}
try
{
dsData = objSQLDB.ExecuteDataSet(sqlQry);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
try
{
objUtilityDB = new UtilityDB();
DataTable dtExcel = new DataTable();
Excel.Application oXL = new Excel.Application();
dtExcel = dsData.Tables[0];
Excel.Workbook theWorkbook = oXL.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)oXL.ActiveSheet;
oXL.Visible = true;
string sLastColumn = objUtilityDB.GetColumnName(21);
Excel.Range rg = worksheet.get_Range("A3", sLastColumn + "3");
Excel.Range rgData = worksheet.get_Range("A3", sLastColumn + (Convert.ToInt32(dtExcel.Rows.Count) + 3).ToString());
rgData.Font.Size = 11;
rgData.WrapText = true;
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.Borders.Weight = 2;
rg.Font.Bold = true;
//rg.Font.Name = "Times New Roman";
rg.Font.Size = 10;
rg.WrapText = true;
rg.Font.ColorIndex = 2; // White Color : 2 and Red = 3,30; Green = 10,43;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Interior.ColorIndex = 31;
rg.Borders.Weight = 2;
rg.Borders.LineStyle = Excel.Constants.xlSolid;
rg.Cells.RowHeight = 38;
rg = worksheet.get_Range("A3", Type.Missing);
rg.Cells.ColumnWidth = 4;
rg = worksheet.get_Range("B3", "C3");
rg.Cells.ColumnWidth = 50;
rg = worksheet.get_Range("D3", Type.Missing);
rg.Cells.ColumnWidth = 40;
rg = worksheet.get_Range("E3", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("I3", "M3");
rg.Cells.ColumnWidth = 13;
rg = worksheet.get_Range("N3", Type.Missing);
rg.Cells.ColumnWidth = 5;
rg = worksheet.get_Range("O3", "P3");
rg.Cells.ColumnWidth = 40;
Excel.Range rgHead = null;
rgHead = worksheet.get_Range("A1", "I2");
rgHead.Merge(Type.Missing);
rgHead.Font.Size = 14;
rgHead.Font.ColorIndex = 1;
rgHead.Font.Bold = true;
rgHead.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.VerticalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.Value2 = "STATIONARY INDENT STATUS REGISTER FROM " + dtpFromDate.Value.ToString("dd/MMM/yyyy").ToUpper() +
" TO " + dtpToDate.Value.ToString("dd/MMM/yyyy").ToUpper() + "";
int iColumn = 1;
worksheet.Cells[3, iColumn++] = "SlNo";
worksheet.Cells[3, iColumn++] = "Company";
worksheet.Cells[3, iColumn++] = "Branch";
worksheet.Cells[3, iColumn++] = "Self";
worksheet.Cells[3, iColumn++] = "FinYear";
worksheet.Cells[3, iColumn++] = "Indent NO";
worksheet.Cells[3, iColumn++] = "Dispatch No";
worksheet.Cells[3, iColumn++] = "GRNNo at Branch";
worksheet.Cells[3, iColumn++] = "Indent Date";
worksheet.Cells[3, iColumn++] = "Indent Status";
worksheet.Cells[3, iColumn++] = "Approved Date";
worksheet.Cells[3, iColumn++] = "Dispatch Date";
worksheet.Cells[3, iColumn++] = "Delivered Date";
worksheet.Cells[3, iColumn++] = "Item SlNo";
worksheet.Cells[3, iColumn++] = "Item Name";
worksheet.Cells[3, iColumn++] = "Item Category";
worksheet.Cells[3, iColumn++] = "Available Qty";
worksheet.Cells[3, iColumn++] = "Request Qty";
worksheet.Cells[3, iColumn++] = "Approved Qty";
worksheet.Cells[3, iColumn++] = "Dispatch Qty";
worksheet.Cells[3, iColumn++] = "Delivered Qty";
int iRow = 4; iColumn = 1;
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
worksheet.Cells[iRow, iColumn++] = i + 1;
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_comp_name"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_branch_name"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_member_name"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_fin_year"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_indent_no"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_disp_no"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_grn_no"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_indent_date"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_indent_status"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_approved_date"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_disp_date"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_delivered_date"];
worksheet.Cells[iRow, iColumn++] = "";
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_item_name"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_category"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_available_qty"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_req_qty"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_ho_qty"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_disp_qty"];
worksheet.Cells[iRow, iColumn++] = dtExcel.Rows[i]["st_delv_qty"];
iColumn = 1;
iRow++;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void btnDownload_Click(object sender, EventArgs e)
{
DataTable dtExcel = new DataTable();
objExcelDB = new ExcelDB();
objUtilityDB = new UtilityDB();
#region Branch Master"
if (CheckData())
{
GetSelectedValues();
try
{
dtExcel = objExcelDB.GetBranchMaster(sComp, sBranch, sBranch, cbStatus.SelectedItem.ToString()).Tables[0];
objExcelDB = null;
if (dtExcel.Rows.Count > 0)
{
Excel.Application oXL = new Excel.Application();
Excel.Workbook theWorkbook = oXL.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)oXL.ActiveSheet;
oXL.Visible = true;
int iTotColumns = 0;
iTotColumns = 29;
string sLastColumn = objUtilityDB.GetColumnName(iTotColumns);
Excel.Range rgHead = null;
Excel.Range rg = worksheet.get_Range("A4", sLastColumn + "4");
Excel.Range rgData = worksheet.get_Range("A5", sLastColumn + (dtExcel.Rows.Count + 4).ToString());
rgData.Font.Size = 11;
rgData.WrapText = true;
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.Borders.Weight = 2;
rgData = worksheet.get_Range("A1", "K2");
rgData.Merge(Type.Missing);
rgData.Font.Bold = true; rgData.Font.Size = 16;
rgData.Value2 = "BRANCH SP/PU/TU DETAILS";
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Font.Bold = true;
rg.Font.Name = "Times New Roman";
rg.Font.Size = 10;
rg.WrapText = true;
rg.Font.ColorIndex = 2; // White Color : 2 and Red = 3,30; Green = 10,43;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.Interior.ColorIndex = 31;
rg.Borders.Weight = 2;
rg.Borders.LineStyle = Excel.Constants.xlSolid;
rg.Cells.RowHeight = 38;
rg = worksheet.get_Range("A4", Type.Missing);
rg.Cells.ColumnWidth = 4;
rg = worksheet.get_Range("B4", Type.Missing);
rg.Cells.ColumnWidth = 12;
rg = worksheet.get_Range("C4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("D4", Type.Missing);
rg.Cells.ColumnWidth = 50;
rg = worksheet.get_Range("E4", Type.Missing);
rg.Cells.ColumnWidth = 12;
rg = worksheet.get_Range("F4", Type.Missing);
rg.Cells.ColumnWidth = 12;
rg = worksheet.get_Range("G4", Type.Missing);
rg.Cells.ColumnWidth = 12;
rg = worksheet.get_Range("H4", Type.Missing);
rg.Cells.ColumnWidth = 12;
rg = worksheet.get_Range("I4", Type.Missing);
rg.Cells.ColumnWidth = 5;
rg = worksheet.get_Range("J4", Type.Missing);
rg.Cells.ColumnWidth = 8;
rg = worksheet.get_Range("K4", Type.Missing);
rg.Cells.ColumnWidth = 20;
rg = worksheet.get_Range("L4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("M4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("N4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("O4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("P4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("Q4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("R4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("S4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("T4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("U4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("V4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("W4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("X4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("Y4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("Z4", Type.Missing);
rg.Cells.ColumnWidth = 9;
rg = worksheet.get_Range("AA4", Type.Missing);
rg.Cells.ColumnWidth = 20;
rg = worksheet.get_Range("AB4", Type.Missing);
rg.Cells.ColumnWidth = 30;
rg = worksheet.get_Range("AC4", Type.Missing);
rg.Cells.ColumnWidth = 8;
int iColumn = 1, iStartRow = 4;
worksheet.Cells[iStartRow, iColumn++] = "SlNo";
worksheet.Cells[iStartRow, iColumn++] = "Branch Code";
worksheet.Cells[iStartRow, iColumn++] = "Branch Name";
worksheet.Cells[iStartRow, iColumn++] = "Address";
worksheet.Cells[iStartRow, iColumn++] = "Sate";
worksheet.Cells[iStartRow, iColumn++] = "District";
worksheet.Cells[iStartRow, iColumn++] = "Mondal";
worksheet.Cells[iStartRow, iColumn++] = "Location";
worksheet.Cells[iStartRow, iColumn++] = "ESI";
worksheet.Cells[iStartRow, iColumn++] = " Head Ecode";
worksheet.Cells[iStartRow, iColumn++] = "Head Name";
worksheet.Cells[iStartRow, iColumn++] = "Desig";
worksheet.Cells[iStartRow, iColumn++] = "Br Contact NO";
worksheet.Cells[iStartRow, iColumn++] = "Branch E_mail";
worksheet.Cells[iStartRow, iColumn++] = "Hr E_mail";
worksheet.Cells[iStartRow, iColumn++] = "RHr E_mail";
worksheet.Cells[iStartRow, iColumn++] = "Inch E_mail";
worksheet.Cells[iStartRow, iColumn++] = "Trainer E_mail";
worksheet.Cells[iStartRow, iColumn++] = "TIN";
worksheet.Cells[iStartRow, iColumn++] = "CST";
worksheet.Cells[iStartRow, iColumn++] = "EXERC";
worksheet.Cells[iStartRow, iColumn++] = "Rent Type";
worksheet.Cells[iStartRow, iColumn++] = "From Date";
worksheet.Cells[iStartRow, iColumn++] = "To Date";
worksheet.Cells[iStartRow, iColumn++] = "Deposit";
worksheet.Cells[iStartRow, iColumn++] = "Rent EMI";
worksheet.Cells[iStartRow, iColumn++] = "Woner Name";
worksheet.Cells[iStartRow, iColumn++] = "Remarks";
worksheet.Cells[iStartRow, iColumn++] = "Status";
iStartRow++; iColumn = 1;
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
worksheet.Cells[iStartRow, iColumn++] = i + 1;
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_BRANCH_CODE"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_BRANCH_NAME"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_BRANCH_ADDRESS"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_STATE"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_DISTRICT"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_MANDAL"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_LOCATION"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_ESI_APPLICABLE"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_HEAD_ECODE"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_HEAD_NAME"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_HEAD_DESIG"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_BRANCH_CONTNO"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_MAIL_ID"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_HR_MAIL_ID"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_REGHR_MAILID"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_INCH_MAILID"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_TRAINER_MAILID"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_TIN_NO"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_CST_NO"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_EXREG_NO"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_RENT_TYPE"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_RENT_FROM"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_RENT_TO"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_SECURE_DEPOSIT"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_RENT_EMI"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_OWNER_NAME"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_REMARKS"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["RS_STATUS"];
iStartRow++; iColumn = 1;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
#endregion
}
private void btnDownload_Click(object sender, EventArgs e)
{
DataTable dtExcel = new DataTable();
objExcelDB = new ExcelDB();
objUtilityDB = new UtilityDB();
int iTotColumns = 0;
if (CheckData() == true)
{
GetSelectedValues();
if (txtFrmPersPts.Text.Length == 0)
{
txtFrmPersPts.Text = "0";
}
if (txtToPersPts.Text.Length == 0)
{
txtToPersPts.Text = "0";
}
#region "Low Performers List"
try
{
dtExcel = objExcelDB.Get_LowPerformersList(CommonData.CompanyCode, CommonData.BranchCode, Convert.ToInt32(cbEcode.SelectedValue), dtpFromDoc.Value.ToString("MMMyyyy").ToUpper(),
dtpToDoc.Value.ToString("MMMyyyy").ToUpper(), sMonthsFlag, frmMnths, ToMnths, sGrpsFlag, frmGrps, ToGrps, sPntsFlag, frmPersPoints,
ToPersPoints, sPntsPerGrpFlag, frmPntsPerGrp, ToPntsPerGrp, sPntsPerHeadFlag, frmPntsPerHead, ToPntsPerHead, cbSortBy.Text, "EXCEL",
sPntsGrpHeadFlag, Convert.ToDateTime(dtpLOSAsOnDate.Value).ToString("dd/MMM/yyyy")).Tables[0];
objExcelDB = null;
if (dtExcel.Rows.Count > 0)
{
Excel.Application oXL = new Excel.Application();
Excel.Workbook theWorkbook = oXL.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)oXL.ActiveSheet;
oXL.Visible = true;
iTotColumns = 13 + (7 * Convert.ToInt32(dtExcel.Rows[0]["al_NoOf_Months"])) + 12;
string sLastColumn = objUtilityDB.GetColumnName(iTotColumns);
Excel.Range rg = worksheet.get_Range("A3", sLastColumn + "3");
Excel.Range rgData = worksheet.get_Range("A3", sLastColumn + (Convert.ToInt32(dtExcel.Rows[0]["al_No_Of_Emp"]) + 3).ToString());
rgData.Font.Size = 11;
rgData.WrapText = true;
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.Borders.Weight = 2;
string strSelection = "";
strSelection = "Months Worked From " + txFrmMnths.Text + " To " + txtToMnths.Text + ", Groups From " + txtFrmGrps.Text + " To " + txtToGrps.Text + " ";
if (chkPersPnts.Checked == true)
{
strSelection += " , Personal Points From " + frmPersPoints + " To " + ToPersPoints + "";
}
if (chkPntsPerGrp.Checked == true)
{
strSelection += " , Points P/G From " + frmPntsPerGrp + " To " + ToPntsPerGrp + "";
}
if (chkPntsPerHead.Checked == true && chkPntsPerHead.Checked == true)
{
strSelection += " " + cbSelection.Text.ToString();
}
else if (chkPntsPerHead.Checked == true || chkPntsPerHead.Checked == true)
{
strSelection += " ,";
}
if (chkPntsPerHead.Checked == true)
{
strSelection += "Points P/H From " + frmPntsPerHead + " To " + ToPntsPerHead + "";
}
strSelection += " , Length Of Service As On Date " + Convert.ToDateTime(dtpLOSAsOnDate.Value).ToString("dd/MMM/yyyy") + "";
rg.Font.Bold = true;
rg.Font.Name = "Times New Roman";
rg.Font.Size = 10;
rg.WrapText = true;
rg.Font.ColorIndex = 2; // White Color : 2 and Red = 3,30; Green = 10,43;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Interior.ColorIndex = 31;
rg.Borders.Weight = 2;
rg.Borders.LineStyle = Excel.Constants.xlSolid;
rg.Cells.RowHeight = 38;
rg = worksheet.get_Range("A3", Type.Missing);
rg.Cells.ColumnWidth = 5;
rg = worksheet.get_Range("B3", Type.Missing);
rg.Cells.ColumnWidth = 6;
rg = worksheet.get_Range("C3", Type.Missing);
rg.Cells.ColumnWidth = 25;
rg = worksheet.get_Range("D3", Type.Missing);
rg.Cells.ColumnWidth = 7;
rg = worksheet.get_Range("E3", Type.Missing);
rg.Cells.ColumnWidth = 12;
rg = worksheet.get_Range("F3", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("G3", Type.Missing);
rg.Cells.ColumnWidth = 12;
rg = worksheet.get_Range("H3", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("I3", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("J3", Type.Missing);
rg.Cells.ColumnWidth = 20;
rg = worksheet.get_Range("K3", Type.Missing);
rg.Cells.ColumnWidth = 30;
rg = worksheet.get_Range("L3", Type.Missing);
rg.Cells.ColumnWidth = 30;
rg = worksheet.get_Range("M3", Type.Missing);
rg.Cells.ColumnWidth = 30;
worksheet.Cells[3, 1] = "Sl.No";
worksheet.Cells[3, 2] = "Ecode";
worksheet.Cells[3, 3] = "Emp Name";
worksheet.Cells[3, 4] = "Desig";
worksheet.Cells[3, 5] = "DOJ";
worksheet.Cells[3, 6] = "Tot Length Of Service";
worksheet.Cells[3, 7] = "DOP";
worksheet.Cells[3, 8] = "LOS In Pres. Desig";
worksheet.Cells[3, 9] = "State";
worksheet.Cells[3, 10] = "Branch";
worksheet.Cells[3, 11] = "Level-1";
worksheet.Cells[3, 12] = "Level-2";
worksheet.Cells[3, 13] = "Level-3";
int iStartColumn = 0;
Excel.Range rgHead;
for (int iMonths = 1; iMonths <= Convert.ToInt32(dtExcel.Rows[0]["al_NoOf_Months"]); iMonths++)
{
rgHead = worksheet.get_Range("A1", "M1");
rgHead.Merge(Type.Missing);
rgHead.Font.Size = 14;
rgHead.Font.ColorIndex = 1;
rgHead.Font.Bold = true;
rgHead.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.VerticalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.Value2 = "LIST OF LOW PERFORMERS \n " + cbEcode.Text + " ";
rgHead.Cells.RowHeight = 35;
rgHead = worksheet.get_Range("A2", "M2");
rgHead.Merge(Type.Missing);
rgHead.Font.Size = 12;
rgHead.Font.ColorIndex = 30;
rgHead.Font.Bold = true;
rgHead.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.VerticalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.Value2 = "" + strSelection + "";
rgHead.Cells.RowHeight = 15;
iStartColumn = (7 * iMonths) + 7;
rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iStartColumn) + "2", objUtilityDB.GetColumnName(iStartColumn + 6) + "2");
//rgHead.Cells.ColumnWidth = 5;
rgHead.Merge(Type.Missing);
rgHead.Interior.ColorIndex = 32 + iMonths;
rgHead.Borders.Weight = 2;
rgHead.Borders.LineStyle = Excel.Constants.xlSolid;
rgHead.Cells.RowHeight = 25;
rgHead.Font.Size = 14;
rgHead.Font.ColorIndex = 1;
rgHead.Font.Bold = true;
rgHead.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.VerticalAlignment = Excel.Constants.xlCenter;
rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iStartColumn) + "3", objUtilityDB.GetColumnName(iStartColumn + 6) + "3");
rgHead.Interior.ColorIndex = 32 + iMonths;
rgHead.Font.ColorIndex = 1;
rgHead.Cells.ColumnWidth = 5;
worksheet.Cells[3, iStartColumn] = "Pers Pmd";
worksheet.Cells[3, iStartColumn + 1] = "Pers Points";
worksheet.Cells[3, iStartColumn + 2] = "Pers Cust";
worksheet.Cells[3, iStartColumn + 3] = "Groups";
worksheet.Cells[3, iStartColumn + 4] = "Group Pmd";
worksheet.Cells[3, iStartColumn + 5] = "Group Points";
worksheet.Cells[3, iStartColumn + 6] = "Group Cust";
}
iStartColumn = 14 + (7 * Convert.ToInt32(dtExcel.Rows[0]["al_NoOf_Months"]));
rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iStartColumn) + "2", objUtilityDB.GetColumnName(iStartColumn + 11) + "2");
//rgHead.Cells.ColumnWidth = 5;
rgHead.Merge(Type.Missing);
rgHead.Interior.ColorIndex = 34 + Convert.ToInt32(dtExcel.Rows[0]["al_NoOf_Months"]) + 1;
rgHead.Borders.Weight = 2;
rgHead.Borders.LineStyle = Excel.Constants.xlSolid;
rgHead.Font.Size = 14;
rgHead.Font.ColorIndex = 1;
rgHead.Font.Bold = true;
rgHead.Cells.Value2 = "TOTAL";
rgHead.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.VerticalAlignment = Excel.Constants.xlCenter;
rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iStartColumn) + "3", objUtilityDB.GetColumnName(iStartColumn + 11) + "3");
rgHead.Interior.ColorIndex = 34 + Convert.ToInt32(dtExcel.Rows[0]["al_NoOf_Months"]) + 1;
rgHead.Font.ColorIndex = 1;
rgHead.Cells.ColumnWidth = 5;
worksheet.Cells[3, iStartColumn] = "Pers Work Months";
worksheet.Cells[3, iStartColumn + 1] = "Pers PMD";
worksheet.Cells[3, iStartColumn + 2] = "Pers Points";
worksheet.Cells[3, iStartColumn + 3] = "Pers Cust";
worksheet.Cells[3, iStartColumn + 4] = "Group Work Months";
worksheet.Cells[3, iStartColumn + 5] = "Groups";
worksheet.Cells[3, iStartColumn + 6] = "Group Tot Points";
worksheet.Cells[3, iStartColumn + 7] = "Group Tot Pmd";
worksheet.Cells[3, iStartColumn + 8] = "Group Avg Pmd P/G";
worksheet.Cells[3, iStartColumn + 9] = "Group Points P/G";
worksheet.Cells[3, iStartColumn + 10] = "Group Points P/H";
worksheet.Cells[3, iStartColumn + 11] = "Group Cust P/H";
int iRowCounter = 4; int iColumnCounter = 1;
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
if (i > 0)
{
if (dtExcel.Rows[i]["al_eora_code"].ToString() == dtExcel.Rows[i - 1]["al_eora_code"].ToString())
{
int iMonthNo = Convert.ToInt32(dtExcel.Rows[i]["al_Month_SlNo"]);
iColumnCounter = (7 * iMonthNo) + 7;
rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iColumnCounter) + "2", objUtilityDB.GetColumnName(iColumnCounter + 6) + "2");
rgHead.Cells.Value2 = dtExcel.Rows[i]["al_doc_month"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_pers_pmd"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_pers_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Pers_Cust"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_groups"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_pmd"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_Cust"];
}
else
{
iRowCounter++;
worksheet.Cells[iRowCounter, iColumnCounter++] = iRowCounter - 3;
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_eora_code"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_eora_name"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Pres_Desig"];
if (dtExcel.Rows[i]["al_DOJ"].ToString() != "")
{
worksheet.Cells[iRowCounter, iColumnCounter++] = Convert.ToDateTime(dtExcel.Rows[i]["al_DOJ"]).ToString("dd/MMM/yyyy");
}
else
{
worksheet.Cells[iRowCounter, iColumnCounter++] = "";
}
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Serv_Length"];
if (dtExcel.Rows[i]["al_Last_Prm_Date"].ToString() != "")
{
worksheet.Cells[iRowCounter, iColumnCounter++] = Convert.ToDateTime(dtExcel.Rows[i]["al_Last_Prm_Date"]).ToString("dd/MMM/yyyy");
}
else
{
worksheet.Cells[iRowCounter, iColumnCounter++] = "";
}
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_LOS_In_PresDesig"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_state_code"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_branch_Name"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Lvl1_Name"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Lvl2_Name"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Lvl3_Name"];
int iMonthNo = Convert.ToInt32(dtExcel.Rows[i]["al_Month_SlNo"]);
iColumnCounter = (7 * iMonthNo) + 7;
rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iColumnCounter) + "2", objUtilityDB.GetColumnName(iColumnCounter + 6) + "2");
rgHead.Cells.Value2 = dtExcel.Rows[i]["al_doc_month"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_pers_pmd"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_pers_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Pers_Cust"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_groups"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_pmd"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_Cust"];
iColumnCounter = iTotColumns - 11;
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_pers_work_months"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_pers_Tot_pmd"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_pers_Tot_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Pers_Tot_Cust"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_work_months"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Tot_Groups"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_Tot_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_Tot_pmd"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Avg_PMD"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Avg_Pnts"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Pnts_Per_Head"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Avg_Cust"];
}
}
else
{
worksheet.Cells[iRowCounter, iColumnCounter++] = iRowCounter - 3;
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_eora_code"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_eora_name"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Pres_Desig"];
if (dtExcel.Rows[i]["al_DOJ"].ToString() != "")
{
worksheet.Cells[iRowCounter, iColumnCounter++] = Convert.ToDateTime(dtExcel.Rows[i]["al_DOJ"]).ToString("dd/MMM/yyyy");
}
else
{
worksheet.Cells[iRowCounter, iColumnCounter++] = "";
}
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Serv_Length"];
if (dtExcel.Rows[i]["al_Last_Prm_Date"].ToString() != "")
{
worksheet.Cells[iRowCounter, iColumnCounter++] = Convert.ToDateTime(dtExcel.Rows[i]["al_Last_Prm_Date"]).ToString("dd/MMM/yyyy");
}
else
{
worksheet.Cells[iRowCounter, iColumnCounter++] = "";
}
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_LOS_In_PresDesig"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_state_code"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_branch_Name"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Lvl1_Name"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Lvl2_Name"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Lvl3_Name"];
int iMonthNo = Convert.ToInt32(dtExcel.Rows[i]["al_Month_SlNo"]);
//int iStartColumn = 0;
iColumnCounter = (7 * iMonthNo) + 7;
rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iColumnCounter) + "2", objUtilityDB.GetColumnName(iColumnCounter + 6) + "2");
rgHead.Cells.Value2 = dtExcel.Rows[i]["al_doc_month"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_pers_pmd"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_pers_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Pers_Cust"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_groups"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_pmd"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_Cust"];
iColumnCounter = iTotColumns - 11;
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_pers_work_months"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_pers_Tot_pmd"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_pers_Tot_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Pers_Tot_Cust"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_work_months"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Tot_Groups"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_Tot_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_group_Tot_pmd"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Avg_PMD"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Avg_Pnts"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Pnts_Per_Head"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["al_Avg_Cust"];
}
iColumnCounter = 1;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
#endregion
}
}
private void btnDownload_Click(object sender, EventArgs e)
{
GetSelectedControlsIDs();
objExcelDB = new ExcelDB();
objUtilityDB = new UtilityDB();
DataTable dtExcel = objExcelDB.GetEmpPerfAgainstAsset(company, branches, finyear, documentMonth, sAssetType, sAssetModel, sEcode, "").Tables[0];
objExcelDB = null;
int iTotColumns = 0;
if (dtExcel.Rows.Count > 0)
{
Excel.Application oXL = new Excel.Application();
Excel.Workbook theWorkbook = oXL.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)oXL.ActiveSheet;
oXL.Visible = true;
iTotColumns = 8 + (7 * Convert.ToInt32(dtExcel.Rows[0]["sr_total_months"]));
string sLastColumn = objUtilityDB.GetColumnName(iTotColumns);
Excel.Range rg = worksheet.get_Range("A3", sLastColumn + "3");
Excel.Range rgData = worksheet.get_Range("A3", sLastColumn + (Convert.ToInt32(dtExcel.Rows[0]["sr_Noof_Emps"]) + 3).ToString());
rgData.Font.Size = 11;
rgData.WrapText = true;
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.Borders.Weight = 2;
rg.Font.Bold = true;
rg.Font.Name = "Times New Roman";
rg.Font.Size = 10;
rg.WrapText = true;
rg.Font.ColorIndex = 2; // White Color : 2 and Red = 3,30; Green = 10,43;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Interior.ColorIndex = 31;
rg.Borders.Weight = 2;
rg.Borders.LineStyle = Excel.Constants.xlSolid;
rg.Cells.RowHeight = 38;
rg = worksheet.get_Range("A3", Type.Missing);
rg.Cells.ColumnWidth = 4;
rg = worksheet.get_Range("B3", Type.Missing);
rg.Cells.ColumnWidth = 6;
rg = worksheet.get_Range("C3", Type.Missing);
rg.Cells.ColumnWidth = 30;
rg = worksheet.get_Range("D3", Type.Missing);
rg.Cells.ColumnWidth = 30;
rg = worksheet.get_Range("E3", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("F3", Type.Missing);
rg.Cells.ColumnWidth = 40;
rg = worksheet.get_Range("G3", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("H3", Type.Missing);
rg.Cells.ColumnWidth = 7;
worksheet.Cells[3, 1] = "SlNo";
worksheet.Cells[3, 2] = "Ecode";
worksheet.Cells[3, 3] = "Name";
worksheet.Cells[3, 4] = "Desig";
worksheet.Cells[3, 5] = "Doj";
worksheet.Cells[3, 6] = "Branch";
worksheet.Cells[3, 7] = "PhoneNo";
worksheet.Cells[3, 8] = "Issued Month";
for (int iMonths = 1; iMonths <= Convert.ToInt32(dtExcel.Rows[0]["sr_total_months"]); iMonths++)
{
Excel.Range rgHead = worksheet.get_Range("A1", "H2");
rgHead.Merge(Type.Missing);
rgHead.Font.Size = 14;
rgHead.Font.ColorIndex = 1;
rgHead.Font.Bold = true;
rgHead.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.VerticalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.Value2 = dtExcel.Rows[0]["sr_asset_type"].ToString().ToUpper() + " USER DETAILS WITH PERFORMANCE";
int iStartColumn = 0;
iStartColumn = (7 * iMonths) + 2;
rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iStartColumn) + "2", objUtilityDB.GetColumnName(iStartColumn + 6) + "2");
//rgHead.Cells.ColumnWidth = 5;
rgHead.Merge(Type.Missing);
rgHead.Interior.ColorIndex = 32 + iMonths;
rgHead.Borders.Weight = 2;
rgHead.Borders.LineStyle = Excel.Constants.xlSolid;
rgHead.Cells.RowHeight = 20;
rgHead.Font.Size = 14;
rgHead.Font.ColorIndex = 1;
rgHead.Font.Bold = true;
rgHead.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.VerticalAlignment = Excel.Constants.xlCenter;
rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iStartColumn) + "3", objUtilityDB.GetColumnName(iStartColumn + 6) + "3");
rgHead.Interior.ColorIndex = 32 + iMonths;
rgHead.Font.ColorIndex = 1;
rgHead.Cells.ColumnWidth = 5;
//worksheet.get_Range(objUtilityDB.GetColumnName(iStartColumn) + "2", objUtilityDB.GetColumnName(iStartColumn + 6) + "2").Merge(Type.Missing);
//rg.Application.ActiveWindow.FreezePanes = true;
//rgHead.Height = 10;
//rgHead.Cells.Value2 = CommonData.BranchName + "";
worksheet.Cells[3, iStartColumn] = "Pers Points";
worksheet.Cells[3, iStartColumn + 1] = "Pers Cust";
worksheet.Cells[3, iStartColumn + 2] = "Groups";
worksheet.Cells[3, iStartColumn + 3] = "Avg Pmd";
worksheet.Cells[3, iStartColumn + 4] = "Group Points";
worksheet.Cells[3, iStartColumn + 5] = "Points P/G";
worksheet.Cells[3, iStartColumn + 6] = "Group Cust";
}
int iRowCounter = 4; int iColumnCounter = 1;
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
if (i > 0)
{
if (dtExcel.Rows[i]["sr_eora_code"].ToString() == dtExcel.Rows[i - 1]["sr_eora_code"].ToString())
{
int iMonthNo = Convert.ToInt32(dtExcel.Rows[i]["sr_Montn_SlNo"]);
//int iStartColumn = 0;
iColumnCounter = (7 * iMonthNo) + 2;
Excel.Range rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iColumnCounter) + "2", objUtilityDB.GetColumnName(iColumnCounter + 6) + "2");
rgHead.Cells.Value2 = dtExcel.Rows[i]["sr_doc_month"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_pers_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_pers_cust"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_groups"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_group_avgpmd"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_group_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_points_pergroup"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_group_cust"];
}
else
{
iRowCounter++;
worksheet.Cells[iRowCounter, iColumnCounter++] = iRowCounter - 3;
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_eora_code"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_eora_name"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_desig"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_doj"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_branch_name"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_cont_no"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_issue_month"];
int iMonthNo = Convert.ToInt32(dtExcel.Rows[i]["sr_Montn_SlNo"]);
//int iStartColumn = 0;
iColumnCounter = (7 * iMonthNo) + 2;
Excel.Range rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iColumnCounter) + "2", objUtilityDB.GetColumnName(iColumnCounter + 6) + "2");
rgHead.Cells.Value2 = dtExcel.Rows[i]["sr_doc_month"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_pers_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_pers_cust"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_groups"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_group_avgpmd"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_group_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_points_pergroup"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_group_cust"];
}
}
else
{
worksheet.Cells[iRowCounter, iColumnCounter++] = iRowCounter - 3;
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_eora_code"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_eora_name"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_desig"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_doj"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_branch_name"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_cont_no"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_issue_month"];
int iMonthNo = Convert.ToInt32(dtExcel.Rows[i]["sr_Montn_SlNo"]);
//int iStartColumn = 0;
iColumnCounter = (7 * iMonthNo) + 2;
Excel.Range rgHead = worksheet.get_Range(objUtilityDB.GetColumnName(iColumnCounter) + "2", objUtilityDB.GetColumnName(iColumnCounter + 6) + "2");
rgHead.Cells.Value2 = dtExcel.Rows[i]["sr_doc_month"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_pers_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_pers_cust"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_groups"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_group_avgpmd"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_group_points"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_points_pergroup"];
worksheet.Cells[iRowCounter, iColumnCounter++] = dtExcel.Rows[i]["sr_group_cust"];
}
iColumnCounter = 1;
}
}
}
private void btnDownload_Click(object sender, EventArgs e)
{
DataTable dtExcel = new DataTable();
objExcelDB = new ExcelDB();
objUtilityDB = new UtilityDB();
if (CheckData() == true)
{
GetSelectedValues();
if (strStatus == "")
{
strStatus = "ALL";
}
#region "iFrmType 0 :: Query Register"
if (iFrmType == 0)
{
if (cbReportType.SelectedIndex == 0)
{
try
{
dtExcel = objExcelDB.GetAuditQueryReg(Company, Branches, "", DocumentMonth, strEcodes, "ALL", strDept, "ALL", "ALL", "ALL", strStatus, "AUDIT_QUERY_REGISTER").Tables[0];
objExcelDB = null;
if (dtExcel.Rows.Count > 0)
{
Excel.Application oXL = new Excel.Application();
Excel.Workbook theWorkbook = oXL.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)oXL.ActiveSheet;
oXL.Visible = true;
int iTotColumns = 0;
iTotColumns = 17;
string sLastColumn = objUtilityDB.GetColumnName(iTotColumns);
Excel.Range rgHead = null;
Excel.Range rg = worksheet.get_Range("A4", sLastColumn + "4");
Excel.Range rgData = worksheet.get_Range("A5", sLastColumn + (dtExcel.Rows.Count + 4).ToString());
rgData.Font.Size = 11;
rgData.WrapText = true;
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.Borders.Weight = 2;
rgData = worksheet.get_Range("A1", "Q2");
rgData.Merge(Type.Missing);
rgData.Font.Bold = true; rgData.Font.Size = 16;
rgData.Value2 = "AUDIT MAJOR POINTS REGISTER";
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Font.Bold = true;
rg.Font.Name = "Times New Roman";
rg.Font.Size = 10;
rg.WrapText = true;
rg.Font.ColorIndex = 2; // White Color : 2 and Red = 3,30; Green = 10,43;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.Interior.ColorIndex = 31;
rg.Borders.Weight = 2;
rg.Borders.LineStyle = Excel.Constants.xlSolid;
rg.Cells.RowHeight = 38;
rg = worksheet.get_Range("A4", Type.Missing);
rg.Cells.ColumnWidth = 4;
rg = worksheet.get_Range("B4", Type.Missing);
rg.Cells.ColumnWidth = 8;
rg = worksheet.get_Range("C4", Type.Missing);
rg.Cells.ColumnWidth = 8;
rg = worksheet.get_Range("D4", Type.Missing);
rg.Cells.ColumnWidth = 8;
rg = worksheet.get_Range("E4", Type.Missing);
rg.Cells.ColumnWidth = 25;
rg = worksheet.get_Range("F4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("G4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("H4", Type.Missing);
rg.Cells.ColumnWidth = 20;
rg = worksheet.get_Range("I4", Type.Missing);
rg.Cells.ColumnWidth = 40;
rg = worksheet.get_Range("K4", Type.Missing);
rg.Cells.ColumnWidth = 40;
rg = worksheet.get_Range("L4", Type.Missing);
rg.Cells.ColumnWidth = 40;
rg = worksheet.get_Range("M4", Type.Missing);
rg.Cells.ColumnWidth = 40;
rg = worksheet.get_Range("N4", Type.Missing);
rg.Cells.ColumnWidth = 40;
rg = worksheet.get_Range("O4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("P4", Type.Missing);
rg.Cells.ColumnWidth = 20;
rg = worksheet.get_Range("Q4", Type.Missing);
rg.Cells.ColumnWidth = 20;
int iColumn = 1, iStartRow = 4;
worksheet.Cells[iStartRow, iColumn++] = "SlNo";
worksheet.Cells[iStartRow, iColumn++] = "QueryID";
worksheet.Cells[iStartRow, iColumn++] = "Doc Month";
worksheet.Cells[iStartRow, iColumn++] = "Visit Month";
worksheet.Cells[iStartRow, iColumn++] = "Branch";
worksheet.Cells[iStartRow, iColumn++] = "Logical Branch";
worksheet.Cells[iStartRow, iColumn++] = "Zone";
worksheet.Cells[iStartRow, iColumn++] = "Region";
worksheet.Cells[iStartRow, iColumn++] = "Audit Point";
worksheet.Cells[iStartRow, iColumn++] = "Dept";
worksheet.Cells[iStartRow, iColumn++] = "Explanation By Accounts Head";
worksheet.Cells[iStartRow, iColumn++] = "Explanation By Sales Head";
worksheet.Cells[iStartRow, iColumn++] = "Explanation By Service Head";
worksheet.Cells[iStartRow, iColumn++] = "Explanation By Unit Head";
worksheet.Cells[iStartRow, iColumn++] = "Status";
worksheet.Cells[iStartRow, iColumn++] = "Unsolved Reason";
worksheet.Cells[iStartRow, iColumn++] = "AuditBy";
iStartRow++; iColumn = 1;
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
worksheet.Cells[iStartRow, iColumn++] = i + 1;
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_query_id"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_doc_month"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_visit_month"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_branch_name"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_LogBranch"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_zone"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_region"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_Audit_point"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_dept"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_exp_HAcc"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_exp_Hsales"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_exp_Hservice"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_exp_Others"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_status"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_reason"];
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["ad_audit_ecode"] + "-" + dtExcel.Rows[i]["ad_audit_name"];
iStartRow++; iColumn = 1;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
#endregion
#region "iFrmType==0 :: Audit Solvation Summary"
if (iFrmType == 0 && cbReportType.SelectedIndex == 1)
{
objExcelDB = new ExcelDB();
dtExcel = objExcelDB.Get_AuditSolvationSummary(Company, Branches, "", DocumentMonth, "SUMMARY").Tables[0];
objExcelDB = null;
if (dtExcel.Rows.Count > 0)
{
try
{
Excel.Application oXL = new Excel.Application();
Excel.Workbook theWorkbook = oXL.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)oXL.ActiveSheet;
oXL.Visible = true;
int iTotColumns = 0;
iTotColumns = 9;
string sLastColumn = objUtilityDB.GetColumnName(iTotColumns);
Excel.Range rgHead = null;
Excel.Range rg = worksheet.get_Range("A4", sLastColumn + "4");
Excel.Range rgData = worksheet.get_Range("A5", sLastColumn + (dtExcel.Rows.Count + 4).ToString());
rgData.Font.Size = 11;
rgData.WrapText = true;
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.Borders.Weight = 2;
rgData = worksheet.get_Range("A1", "I2");
rgData.Merge(Type.Missing);
rgData.Font.Bold = true; rgData.Font.Size = 16;
rgData.Value2 = "AUDIT SOLVATION SUMMARY";
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Font.Bold = true;
rg.Font.Name = "Times New Roman";
rg.Font.Size = 10;
rg.WrapText = true;
rg.Font.ColorIndex = 2; // White Color : 2 and Red = 3,30; Green = 10,43;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.Interior.ColorIndex = 31;
rg.Borders.Weight = 2;
rg.Borders.LineStyle = Excel.Constants.xlSolid;
rg.Cells.RowHeight = 38;
rg = worksheet.get_Range("A4", Type.Missing);
rg.Cells.ColumnWidth = 4;
rg = worksheet.get_Range("B4", Type.Missing);
rg.Cells.ColumnWidth = 40;
rg = worksheet.get_Range("C4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("D4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("E4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("F4", Type.Missing);
rg.Cells.ColumnWidth = 8;
rg = worksheet.get_Range("G4", Type.Missing);
rg.Cells.ColumnWidth = 8;
rg = worksheet.get_Range("H4", Type.Missing);
rg.Cells.ColumnWidth = 8;
rg = worksheet.get_Range("I4", Type.Missing);
rg.Cells.ColumnWidth = 8;
int iColumn = 1, iStartRow = 4;
worksheet.Cells[iStartRow, iColumn++] = "SlNo";
worksheet.Cells[iStartRow, iColumn++] = "Branch Name";
worksheet.Cells[iStartRow, iColumn++] = "Visit Month";
worksheet.Cells[iStartRow, iColumn++] = "Doc Month";
worksheet.Cells[iStartRow, iColumn++] = "Total Points";
worksheet.Cells[iStartRow, iColumn++] = "Solved Points";
worksheet.Cells[iStartRow, iColumn++] = "Solved-FollowUp";
worksheet.Cells[iStartRow, iColumn++] = "Unsolved Points";
worksheet.Cells[iStartRow, iColumn++] = "% Of Solvation";
iStartRow++; iColumn = 1;
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
worksheet.Cells[iStartRow, iColumn++] = i + 1;
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["aq_branch_name"].ToString();
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["aq_vis_month"].ToString();
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["aq_doc_month"].ToString();
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["aq_tot_points"].ToString();
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["aq_solved_points"].ToString();
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["aq_solved_pointsFUP"].ToString();
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["aq_unsolved_points"].ToString();
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["aq_Percentage_of_Sol"].ToString();
iStartRow++; iColumn = 1;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
}
#endregion
}
}
private void btnDownload_Click(object sender, EventArgs e)
{
if (CheckData())
{
GetSelectedCompAndBranches();
try
{
if (cbDesig.Text == "SR")
{
//GetSelectedCompAndBranches();
objExDb = new ExcelDB();
objUtilityDB = new UtilityDB();
DataTable dtExcel = new DataTable();
dtExcel = objExDb.GetAllIndiaTopSrDownloadExcel(Company, Branches, txtNoofRecords.Text.ToString(), dtpFromDoc.Value.ToString("MMMyyyy"), dtpToDoc.Value.ToString("MMMyyyy"), "").Tables[0];
objExDb = null;
if (dtExcel.Rows.Count > 0)
{
Excel.Application oXL = new Excel.Application();
Excel.Workbook theWorkbook = oXL.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)oXL.ActiveSheet;
oXL.Visible = true;
int iTotColumns = 0;
iTotColumns = 7;
string sLastColumn = objUtilityDB.GetColumnName(iTotColumns);
Excel.Range rg = worksheet.get_Range("A3", sLastColumn + "3");
Excel.Range rgData = worksheet.get_Range("A3", sLastColumn + (Convert.ToInt32(dtExcel.Rows.Count) + 3).ToString());
rgData.Font.Size = 11;
rgData.WrapText = true;
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.Borders.Weight = 2;
rg.Font.Bold = true;
rg.Font.Name = "Times New Roman";
rg.Font.Size = 10;
rg.WrapText = true;
rg.Font.ColorIndex = 2; // White Color : 2 and Red = 3,30; Green = 10,43;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Interior.ColorIndex = 31;
rg.Borders.Weight = 2;
rg.Borders.LineStyle = Excel.Constants.xlSolid;
rg.Cells.RowHeight = 38;
Excel.Range rgHead = null;
rgHead = worksheet.get_Range("A1", "G2");
rgHead.Merge(Type.Missing);
rgHead.Font.Size = 14;
rgHead.Font.ColorIndex = 1;
rgHead.Font.Bold = true;
rgHead.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
rgHead.Cells.VerticalAlignment = Excel.Constants.xlCenter;
string NoOfRecords = txtNoofRecords.Text.ToString();
rgHead.Cells.Value2 = "ALL INDIA TOP " + NoOfRecords + " SR's";
rg = worksheet.get_Range("A3", Type.Missing);
rg.Cells.ColumnWidth = 7;
rg = worksheet.get_Range("B3", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("C3", Type.Missing);
rg.Cells.ColumnWidth = 8;
rg = worksheet.get_Range("D3", Type.Missing);
rg.Cells.ColumnWidth = 30;
rg = worksheet.get_Range("E3", Type.Missing);
rg.Cells.ColumnWidth = 35;
rg = worksheet.get_Range("F3", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("G3", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("H3", Type.Missing);
rg.Cells.ColumnWidth = 10;
int iColumn = 1;
worksheet.Cells[3, iColumn++] = "Rank";
worksheet.Cells[3, iColumn++] = "Emp Pic";
worksheet.Cells[3, iColumn++] = "Ecode";
worksheet.Cells[3, iColumn++] = "Emp Name ";
worksheet.Cells[3, iColumn++] = "Brance Name";
worksheet.Cells[3, iColumn++] = "Region";
worksheet.Cells[3, iColumn++] = "Total Points";
iColumn = 1;
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
string strImgFile = "";
worksheet.Cells[i + 4, iColumn++] = (i + 1).ToString();
if (dtExcel.Rows[i]["sr_Photo"].ToString() != "")
{
strImgFile = byteArrayToImage((byte[])dtExcel.Rows[i]["sr_Photo"]);
Excel.Range CellRange = (Excel.Range)worksheet.Cells[i + 4, iColumn++];
float Left = (float)((double)CellRange.Left);
float Top = (float)((double)CellRange.Top);
float ImageSize = 65;
worksheet.Shapes.AddPicture(strImgFile, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left + 2, Top + 2, ImageSize, ImageSize);
CellRange.RowHeight = ImageSize + 2;
}
else
{
worksheet.Cells[i + 4, iColumn++] = "";
}
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["sr_eora_code"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["sr_eora_name"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["sr_branch_Name"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["sr_region_name"].ToString();
worksheet.Cells[i + 4, iColumn++] = dtExcel.Rows[i]["sr_total_points"].ToString();
iColumn = 1;
}
}
else
{
MessageBox.Show("No Data Found!", "SSERP", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
if (cbDesig.Text == "GC" || cbDesig.Text == "GL")
{
//GetSelectedCompAndBranches();
objExDb = new ExcelDB();
objUtilityDB = new UtilityDB();
DataTable dtExcel = new DataTable();
dtExcel = objExDb.GetAllIndiaTopGLSDownloadExcel(Company, Branches, txtNoofRecords.Text.ToString(), dtpFromDoc.Value.ToString("MMMyyyy"), dtpToDoc.Value.ToString("MMMyyyy"), "").Tables[0];
objExDb = null;
if (dtExcel.Rows.Count > 0)
{
Excel.Application oXL = new Excel.Application();
Excel.Workbook theWorkbook = oXL.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)oXL.ActiveSheet;
oXL.Visible = true;
int iTotColumns = 0;
iTotColumns = 9;
string sLastColumn = objUtilityDB.GetColumnName(iTotColumns);
Excel.Range rgHead = null;
Excel.Range rg = worksheet.get_Range("A4", sLastColumn + "4");
Excel.Range rgData = worksheet.get_Range("A5", sLastColumn + (dtExcel.Rows.Count + 4).ToString());
rgData.Font.Size = 11;
rgData.WrapText = true;
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.Borders.Weight = 2;
rgData = worksheet.get_Range("A1", "G3");
rgData.Merge(Type.Missing);
rgData.Font.Bold = true; rgData.Font.Size = 16;
rgData.Value2 = "ALL INDIA TOP " + txtNoofRecords.Text.ToString() + " GC/GL'S ";
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Font.Bold = true;
rg.Font.Name = "Times New Roman";
rg.Font.Size = 10;
rg.WrapText = true;
rg.Font.ColorIndex = 2; // White Color : 2 and Red = 3,30; Green = 10,43;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.Interior.ColorIndex = 31;
rg.Borders.Weight = 2;
rg.Borders.LineStyle = Excel.Constants.xlSolid;
rg.Cells.RowHeight = 38;
rg = worksheet.get_Range("A4", Type.Missing);
rg.Cells.ColumnWidth = 4;
rg = worksheet.get_Range("B4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("C4", Type.Missing);
rg.Cells.ColumnWidth = 8;
rg = worksheet.get_Range("D4", Type.Missing);
rg.Cells.ColumnWidth = 30;
rg = worksheet.get_Range("E4", Type.Missing);
rg.Cells.ColumnWidth = 35;
rg = worksheet.get_Range("F4", Type.Missing);
rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("G3", Type.Missing);
rg.Cells.ColumnWidth = 8;
rg = worksheet.get_Range("H3", "I3");
rg.Merge(Type.Missing);
rg.Value2 = "TOTAL";
rg.Font.Bold = true; rg.Borders.Weight = 2; rg.Font.Size = 11;
rg.Interior.ColorIndex = 31; rg.Font.ColorIndex = 2;
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg = worksheet.get_Range("H4", Type.Missing);
rg.Cells.ColumnWidth = 6;
rg = worksheet.get_Range("I4", Type.Missing);
rg.Cells.ColumnWidth = 6;
rg.WrapText = true;
int iColumn = 1, iStartRow = 4;
worksheet.Cells[iStartRow, iColumn++] = "SlNo";
worksheet.Cells[iStartRow, iColumn++] = "Emp Pic";
worksheet.Cells[iStartRow, iColumn++] = "Ecode";
worksheet.Cells[iStartRow, iColumn++] = "Emp Name";
worksheet.Cells[iStartRow, iColumn++] = "Branch Name";
worksheet.Cells[iStartRow, iColumn++] = "Region";
worksheet.Cells[iStartRow, iColumn++] = "Category";
worksheet.Cells[iStartRow, iColumn++] = "Groups";
worksheet.Cells[iStartRow, iColumn++] = "Points P/G";
iStartRow++; iColumn = 1;
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
string strImgFile = "";
worksheet.Cells[iStartRow, iColumn++] = (i + 1).ToString();
if (dtExcel.Rows[i]["gl_Photo"].ToString() != "")
{
strImgFile = byteArrayToImage((byte[])dtExcel.Rows[i]["gl_Photo"]);
Excel.Range CellRange = (Excel.Range)worksheet.Cells[iStartRow, iColumn++];
float Left = (float)((double)CellRange.Left);
float Top = (float)((double)CellRange.Top);
float ImageSize = 65;
worksheet.Shapes.AddPicture(strImgFile, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, Left + 2, Top + 2, ImageSize, ImageSize);
CellRange.RowHeight = ImageSize + 2;
}
else
{
worksheet.Cells[iStartRow, iColumn++] = "";
}
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["gl_eora_code"].ToString();
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["gl_eora_name"].ToString();
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["gl_branch_Name"].ToString();
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["gl_region_name"].ToString();
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["gl_category"].ToString();
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["gl_groups"].ToString();
worksheet.Cells[iStartRow, iColumn++] = dtExcel.Rows[i]["gl_points_avg"].ToString();
iStartRow++; iColumn = 1;
}
}
else
{
MessageBox.Show("No Data Found!", "SSERP", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
if (iFrmType == 1)
{
#region STATE WISE STOCK POINT RECONCILATION
try
{
objExDb = new ExcelDB();
DataTable dtExcel = objExDb.StateWiseStockReconsilation(Company, Branches, dtpFromDoc.Value.ToString("MMMyyyy").ToUpper(), "").Tables[0];
objExDb = null;
if (dtExcel.Rows.Count > 0)
{
Excel.Application oXL = new Excel.Application();
Excel.Workbook theWorkbook = oXL.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)oXL.ActiveSheet;
oXL.Visible = true;
Excel.Range rg = worksheet.get_Range("A4", "Y4");
Excel.Range rgData = worksheet.get_Range("A4", "Y" + (dtExcel.Rows.Count + 4).ToString());
rgData.Font.Size = 11;
rgData.WrapText = true;
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.Borders.Weight = 2;
rg.Font.Bold = true;
rg.Font.Name = "Times New Roman";
rg.Font.Size = 10;
rg.WrapText = true;
rg.Font.ColorIndex = 2; // White Color : 2 and Red = 3,30; Green = 10,43;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg.Interior.ColorIndex = 31;
rg.Borders.Weight = 2;
rg.Borders.LineStyle = Excel.Constants.xlSolid;
rg.Cells.RowHeight = 38;
rg = worksheet.get_Range("A1", "U1");
rg.Merge(Type.Missing);
rg.Value2 = "STOCK RECONSILATION";
rg.Font.Bold = true; rg.Font.Size = 16;
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rgData = worksheet.get_Range("W1", "Y1");
rgData.Merge(Type.Missing);
rgData.Font.Bold = true; rgData.Font.Size = 14;
rgData.Value2 = "DOC MONTH : " + dtpFromDoc.Value.ToString("MMMyyyy");
rgData.VerticalAlignment = Excel.Constants.xlCenter;
rgData.HorizontalAlignment = Excel.Constants.xlCenter;
rg = worksheet.get_Range("A4", Type.Missing);
rg.Cells.ColumnWidth = 5;
rg.Cells.Value2 = "Sl.No";
rg = worksheet.get_Range("B4", Type.Missing);
rg.Cells.ColumnWidth = 30;
rg.Value2 = " SP NAME";
rg = worksheet.get_Range("C4", Type.Missing);
rg.Cells.ColumnWidth = 30;
rg.Value2 = " BRANCH NAME";
rg = worksheet.get_Range("D2", "F3");
rg.Merge(Type.Missing);
rg.Value2 = "OPENING STOCK";
rg.Font.ColorIndex = 2;
rg.Interior.ColorIndex = 21;
rg.Font.Bold = true; rg.Font.Size = 12;
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg = worksheet.get_Range("D4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "GOOD";
rg = worksheet.get_Range("E4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "DAMAGE";
rg = worksheet.get_Range("F4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "TOTAL";
rg = worksheet.get_Range("G2", "I3");
rg.Merge(Type.Missing);
rg.Value2 = "RECEIPTS(GRN)";
rg.Font.ColorIndex = 3;
rg.Interior.ColorIndex = 8;
rg.Font.Bold = true; rg.Font.Size = 12;
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg = worksheet.get_Range("G4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "GOOD";
rg = worksheet.get_Range("H4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "DAMAGE";
rg = worksheet.get_Range("I4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "TOTAL";
rg = worksheet.get_Range("J2", "P2");
rg.Merge(Type.Missing);
rg.Value2 = "ISSUES";
rg.Font.ColorIndex = 2;
rg.Interior.ColorIndex = 5;
rg.Font.Bold = true; rg.Font.Size = 12;
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg = worksheet.get_Range("J3", "J4");
rg.Merge(Type.Missing);
rg.Interior.ColorIndex = 31;
rg.Font.ColorIndex = 2;
rg.WrapText = true;
rg.Value2 = " DC GOOD";
rg = worksheet.get_Range("K3", "M3");
rg.Merge(Type.Missing);
rg.Value2 = "DCST";
rg.Font.ColorIndex = 2;
rg.Interior.ColorIndex = 9;
rg.Font.Bold = true; rg.Font.Size = 12;
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg = worksheet.get_Range("K4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "GOOD";
rg = worksheet.get_Range("L4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "DAMAGE";
rg = worksheet.get_Range("M4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "TOTAL";
rg = worksheet.get_Range("N3", "P3");
rg.Merge(Type.Missing);
rg.Value2 = "TOTAL";
rg.Font.ColorIndex = 2;
rg.Interior.ColorIndex = 21;
rg.Font.Bold = true; rg.Font.Size = 12;
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg = worksheet.get_Range("N4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "GOOD";
rg = worksheet.get_Range("O4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "DAMAGE";
rg = worksheet.get_Range("P4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "TOTAL";
//rg = worksheet.get_Range("Q2", "Q3");
//rg.Merge(Type.Missing);
//rg.Value2 = "";
//rg.Font.ColorIndex = 2;
//rg = worksheet.get_Range("Q4", Type.Missing);
rg = worksheet.get_Range("Q2", "Q4");
rg.Merge(Type.Missing);
rg.Interior.ColorIndex = 31;
rg.Font.ColorIndex = 2;
rg.WrapText = true;
rg.Font.Bold = true; rg.Font.Size = 12;
rg.Value2 = "DSPU_ISSUE DAMAGE";
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
//rg = worksheet.get_Range("R2", "R3");
//rg.Merge(Type.Missing);
//rg.Value2 = "";
//rg.Font.ColorIndex = 2;
//rg = worksheet.get_Range("R4", Type.Missing);
//rg.Cells.ColumnWidth = 15;
rg = worksheet.get_Range("R2", "R4");
rg.Merge(Type.Missing);
rg.Interior.ColorIndex = 21;
rg.Font.ColorIndex = 2;
rg.WrapText = true;
rg.Font.Bold = true; rg.Font.Size = 12;
rg.Value2 = "GRPU RECEIPT GOOD";
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg = worksheet.get_Range("S2", "T3");
rg.Merge(Type.Missing);
rg.Value2 = "INTERNAL CONVERSION";
rg.Font.ColorIndex = 3;
rg.Interior.ColorIndex = 8;
rg.WrapText = true;
rg.Font.Bold = true; rg.Font.Size = 12;
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg = worksheet.get_Range("S4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "G2D";
rg = worksheet.get_Range("T4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "D2G";
//rg = worksheet.get_Range("U2", "U3");
//rg.Merge(Type.Missing);
//rg.Value2 = "";
//rg = worksheet.get_Range("U4", Type.Missing);
//rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("U2", "U4");
rg.Merge(Type.Missing);
rg.Interior.ColorIndex = 21;
rg.Font.ColorIndex = 2;
rg.WrapText = true;
rg.Font.Bold = true; rg.Font.Size = 12;
rg.Value2 = "SHORTAGE/WRITE OFF";
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
//rg = worksheet.get_Range("V2", "V3");
//rg.Merge(Type.Missing);
//rg.Value2 = "";
//rg = worksheet.get_Range("V4", Type.Missing);
//rg.Cells.ColumnWidth = 10;
rg = worksheet.get_Range("V2", "V4");
rg.Merge(Type.Missing);
rg.Interior.ColorIndex = 31;
rg.Font.ColorIndex = 2;
rg.Font.Bold = true; rg.Font.Size = 12;
rg.Value2 = "EXCESS";
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg = worksheet.get_Range("W2", "Y3");
rg.Merge(Type.Missing);
rg.Value2 = "CLOSING STOCK";
rg.Font.ColorIndex = 2;
rg.Interior.ColorIndex = 21;
rg.Font.Bold = true; rg.Font.Size = 12;
rg.VerticalAlignment = Excel.Constants.xlCenter;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg = worksheet.get_Range("W4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "GOOD";
rg = worksheet.get_Range("X4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "DAMAGE";
rg = worksheet.get_Range("Y4", Type.Missing);
rg.Cells.ColumnWidth = 10;
rg.Value2 = "TOTAL";
int RowCounter = 1;
foreach (DataRow dr in dtExcel.Rows)
{
int i = 1;
worksheet.Cells[RowCounter + 4, i++] = RowCounter;
worksheet.Cells[RowCounter + 4, i++] = dr["sm_state"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_BRANCH_NAME"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_OPST_GOOD"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_OPST_DMG"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_OPST_TOT"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_GRN_GOOD"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_GRN_DMG"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_GRN_TOT"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_DC_GOOD"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_DCST_GOOD"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_DCST_DMG"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_DCST_TOT"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_ISSUE_GOOD"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_ISSUE_DMG"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_ISSUE_TOT"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_REFILL_DC"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_REFILL_GRN"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_INTCNV_G2D"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["SMS_INTCNV_D2G"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_SHORTAGE_TOT"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_EXCESS_TOT"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_CLOSING_GOOD"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_CLOSING_DMG"].ToString();
worksheet.Cells[RowCounter + 4, i++] = dr["MSMS_CLOSING_TOT"].ToString();
RowCounter++;
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
#endregion
}
}
