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