public void NavigateToQaAutomation() { HomePage homePage = new HomePage(this.driver); var registrationPage = new RegistrationPage(this.driver); PageFactory.InitElements(this.driver, homePage); homePage.Navigate(); this.driver.FindElement(By.Id("menu-item-374")).Click(); //RegistratonButton.Click(); registrationPage.AssertRegistrationPageIsOpen("Registration"); var registrationUser = ExcelDB.GetTestData("Login"); IWebElement firstName = driver.FindElement(By.Name("FirstName")); firstName.Clear(); firstName.SendKeys(registrationUser.FirstName); IWebElement lastName = driver.FindElement(By.Name("LastName")); firstName.Clear(); firstName.SendKeys(registrationUser.LastName); IWebElement password = driver.FindElement(By.Name("password")); password.Clear(); password.SendKeys(registrationUser.Password); IWebElement login = driver.FindElement(By.XPath("/html/body/div/div[2]/div[2]/div[2]/div[1]/form/input[2]")); login.Click(); IWebElement logo = driver.FindElement(By.XPath("//*[@id=\"page-header\"]/div[1]/div/div/div[1]/a/img[1]")); Assert.IsTrue(logo.Displayed); }
public override List <Condition> GetHSNNotifiedConditionData(string hsnNumber) { List <Condition> condition = new List <Condition>(); ExcelDB exceldb = new ExcelDB(); condition = exceldb.GetNotifiedHSNConditionsData(hsnNumber); return(condition); }
public override List <Condition> GetSACNotifiedConditionData(string sacNumber) { List <Condition> condition = new List <Condition>(); ExcelDB exceldb = new ExcelDB(); condition = exceldb.GetNotifiedSACConditionsData(sacNumber); return(condition); }
public override List <Notified> GetSACNotificationData(string sacNumber) { List <Notified> notify = new List <Notified>(); ExcelDB exceldb = new ExcelDB(); notify = exceldb.GetSACNotificationData(sacNumber); return(notify); }
public override DataSet ViewInvoice(string SellerGSTN, string FromDt, String Todate) { DataSet ds = new DataSet(); ExcelDB exceldb = new ExcelDB(); ds = exceldb.ViewInvoice(SellerGSTN, FromDt, Todate); return(ds); }
//public override int SaveInvoiceData(Seller seller) //{ // int result = 0; // ExcelDB exceldb = new ExcelDB(); // result = exceldb.SaveInvoiceData(seller); // return result; //} public override int UploadInvoice(List <Seller> SellerDaTa, String InvoiceNo) { int result = 0; Seller seller = new Seller(); seller.SellerDaTa = new List <Seller>(); seller.SellerDaTa = SellerDaTa; ExcelDB exceldb = new ExcelDB(); result = exceldb.UploadInvoice(seller.SellerDaTa, InvoiceNo); return(result); }
static void Main(string[] args) { if (args.Length != 5 && args.Length != 6) { Console.WriteLine("Usage: CreateDailyCalculations server user pass outputFile dryrun [sites]"); Console.WriteLine(" server = hostname "); Console.WriteLine(" user = username"); Console.WriteLine(" pass = passwordfile"); Console.WriteLine(" outputfile = filename for stats/results"); Console.WriteLine(" dryrun = true|false -- when true only simulates changes"); Console.WriteLine(" sites = amfi,anci,... -- list of sites to filter"); return; } var host = args[0]; string user = args[1]; string pass = File.ReadAllLines(args[2])[0]; var outputFileName = args[3]; bool dryRun = (args[4] == "true"); var sites = new string[] { }; if (args.Length == 6) { sites = args[5].ToLower().Split(','); } var svr = PostgreSQL.GetPostgresServer("timeseries", host, user, pass); //UpdateVMS_daily_por(svr); TimeSeriesDatabase db = new TimeSeriesDatabase(svr); Console.WriteLine(db.Server.ConnectionString); var fn = @"c:\temp\a.xlsx"; File.Copy(Path.Combine(FileUtility.GetExecutableDirectory(), "daily_calcs_and_series.xlsx"), fn, true); var pcodeLookup = ExcelDB.Read(fn, "daily_instant_pcode"); DailyCalcGenerator tool = new DailyCalcGenerator(db, outputFileName); tool.AddDailyCalculations(pcodeLookup, dryRun, sites); // what about years like 6190..... //HydrometInfoUtility.ArchiverEnabled(.. ? }
static void Main(string[] args) { string serverIP = "lrgs1"; //.Logger.Logger.EnableLogger(); var fn = @"V:\PN6200\Hydromet\HelpWanted\migrate to linux.xls"; var tbl = ExcelDB.Read(fn, "owrd_1"); var cs = PostgreSQL.CreateADConnectionString(serverIP, "timeseries"); PostgreSQL svr = new PostgreSQL(cs); //SQLiteServer svr = new SQLiteServer(@"c:\temp\lrgs1.pdb"); TimeSeriesDatabase db = new TimeSeriesDatabase(svr); AddToPisces(tbl, db); AddSitesToDecodes(serverIP, tbl); }
private DataTable GetTable(string excelName, string sheetName) { // Performance p = new Performance(); DataTable rval = new DataTable(); if (excelDataSet == null) { excelDataSet = new DataSet(); } if (!excelDataSet.Tables.Contains(sheetName)) { rval = ExcelDB.Read(excelName, sheetName); excelDataSet.Tables.Add(rval); } // p.Report("read " + sheetName); return(excelDataSet.Tables[sheetName]); }
/// <summary> /// Create a SeriesCatalog by looking at each sheet and column names inside /// </summary> private void BuildSiteCatalog() { m_treeTable = new DataTable("SeriesCatalog"); m_treeTable.Columns.Add("Label", typeof(string)); m_treeTable.Columns.Add("Level", typeof(Int32)); m_treeTable.Columns.Add("DatabaseName"); m_treeTable.Columns.Add("SheetName"); m_treeTable.Columns.Add("DateColumn"); m_treeTable.Columns.Add("ValueColumn"); m_treeTable.Rows.Add(Name, 0, "", "", "", ""); string[] sheetNames = ExcelDB.SheetNames(m_filename); for (int i = 0; i < sheetNames.Length; i++) { string sheetName = sheetNames[i]; if (sheetName.Trim() != "") { string sql = " Select * from [" + sheetName + "$] WHERE 2 = 1"; DataTable t1 = ExcelDB.Read(m_filename, sheetName, sql); if (t1.Columns.Count > 0 && t1.Columns[0].ColumnName.ToLower().IndexOf("date") == 0) { string dateColumn = t1.Columns[0].ColumnName; for (int j = 1; j < t1.Columns.Count; j++) { string valueColumn = t1.Columns[j].ColumnName; m_treeTable.Rows.Add(valueColumn, 1, Name, sheetName, dateColumn, valueColumn); } } } } }
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()); } }
/// <summary> /// Executes the DoDemoWorkCommand /// </summary> public void Execute(object parameter) { // Create OpenFileDialog Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog(); // Set filter for file extension and default file extension dlg.DefaultExt = ".xlsx"; dlg.Filter = "Text documents (.xlsx)|*.xlsx"; // Display OpenFileDialog by calling ShowDialog method Nullable <bool> result = dlg.ShowDialog(); // Get the selected file name and display in a TextBox if (result == true) { // Open document string filename = dlg.FileName; /* A typical task would involve processing a list of files, or similar work. * To simulate this type of task, we will create a simple list of integers, * which we will call the 'workList'. Each background task will traverse this * list in order to perform its assigned work. */ // Initialize var progressDialogViewModel = m_ViewModel.ProgressDialogViewModel; progressDialogViewModel.ClearViewModel(); /* All background tasks can be cancelled by clicking a Cancel button on * the progress dialog. Clicking the button invokes a Cancel command in * the ProgressDialogViewModel. The .NET Task Parallel Library uses a * CancellationTokenSource (CTS) to cancel background tasks. We create * a CTS here and pass it to the ProgressDialogViewModel, so that the * Cancel command has access when it needs it. */ // Set the view model's token source progressDialogViewModel.TokenSource = new CancellationTokenSource(); /* The ProgressDialogViewModel.ProgressMax property holds the maximum * progress value for this particular operation. The progress percentage * is calculated as Progress/ProgressMax. This operation has two background * tasks, each of which traverses the work list. The first task takes three * times as long to traverse the list as the second task, so we set the * ProgressMax property to four times the length of the work list. * * The first task will advance the Progress property three clicks for each * item on the list, and the second task will advance the property one click * for each item. As a result, when the first task is complete, the Progress * dialog will show 75% complete, and it will show 100% complete when the * second task completes. */ // Announce that work is starting m_ViewModel.RaiseWorkStartedEvent(); // Launch first background task var taskOne = Task.Factory.StartNew(() => ExcelDB.MigrateSubstance(filename, progressDialogViewModel)); taskOne.ContinueWith(t => m_ViewModel.RaiseWorkEndedEvent(), TaskScheduler.FromCurrentSynchronizationContext()); } }
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(); 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) { 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) { if (strRep == "STOCKPOINT_RECONSILATION") { #region BR WISE STOCK POINT RECONCILATION try { string[] strBranchCode; strBranchCode = ((SSAdmin.NewCheckboxListItem)(clbGLList.SelectedItem)).Tag.ToString().Split('@'); objExDb = new ExcelDB(); DataTable dtExcel = objExDb.BRWiseStockReconsilation(strBranchCode[1], strBranchCode[0], Convert.ToDateTime(dtpDate.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; 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 : " + dtpDate.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 = " PRODUCT 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.WrapText = true; rg.Interior.ColorIndex = 31; rg.Font.ColorIndex = 2; 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.Font.Bold = true; rg.Font.Size = 12; rg.Value2 = "DSPU_ISSUE DAMAGE"; rg.WrapText = true; 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.WrapText = true; 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("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["rs_branch_name"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_product_name"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_open_stock_good"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_open_stock_damage"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_total_open_stock"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_recieved_good"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_recieved_damage"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_recieved_total"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_stock_dispatched_DC"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_stock_dispatched_DCST_Good"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_stock_dispatched_DCST_Dmg"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_stock_dispatched_DCST"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_stock_dispatched_good"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_stock_dispatched_dmg"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_stock_dispatched"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_refill_issue"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_refill_recieved"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_interconv_to_bad"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_interconv_to_good"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_remarks_shortage"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_remarks_excess"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_closing_stock_good"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_closing_stock_damage"].ToString(); worksheet.Cells[RowCounter + 4, i++] = dr["rs_closing_stock_total"].ToString(); RowCounter++; } } } 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 excelDwnldCollectReg() { try { DataTable dtEx = new DataTable(); ExcelDB objExDb = new ExcelDB(); dtEx = objExDb.GetDlCollReg(CommonData.CompanyCode, CommonData.BranchCode, dtpInvoiceFromDate.Value.ToString("dd/MMM/yyyy").ToUpper(), dtpInvoiceToDate.Value.ToString("dd/MMM/yyyy"), "ALL", "").Tables[0]; if (dtEx.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; worksheet.Name = CommonData.DocMonth.ToUpper(); oXL.Visible = true; Excel.Range rgHead = worksheet.get_Range("A1", "VP1"); rgHead.Font.Size = 14; rgHead.Cells.MergeCells = true; rgHead.VerticalAlignment = Excel.Constants.xlCenter; rgHead.Font.Bold = true; rgHead.Font.ColorIndex = 30; rgHead.Borders.Weight = 2; Excel.Range rg = worksheet.get_Range("A2", "S2"); Excel.Range rgData = worksheet.get_Range("A3", "S2" + (dtEx.Rows.Count + 2).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; rgHead = worksheet.get_Range("A1", "S1"); rgHead.Cells.ColumnWidth = 5; rg = worksheet.get_Range("A2", Type.Missing); rg.Cells.ColumnWidth = 5; rg.Cells.Value2 = "Sl.No"; rg = worksheet.get_Range("B2", Type.Missing); rg.Cells.ColumnWidth = 30; rg.Cells.Value2 = "Company"; rg = worksheet.get_Range("C2", Type.Missing); rg.Cells.ColumnWidth = 30; rg.Cells.Value2 = "Branch"; rg = worksheet.get_Range("D2", Type.Missing); rg.Cells.ColumnWidth = 10; rg.Cells.Value2 = "Financial Year"; rg = worksheet.get_Range("E2", Type.Missing); rg.Cells.ColumnWidth = 7; rg.Cells.Value2 = "TRN Date"; rg = worksheet.get_Range("F2", Type.Missing); rg.Cells.ColumnWidth = 7; rg.Cells.Value2 = "Transaction No"; rg = worksheet.get_Range("G2", Type.Missing); rg.Cells.ColumnWidth = 7; rg.Cells.Value2 = "Ref No"; rg = worksheet.get_Range("H2", Type.Missing); rg.Cells.ColumnWidth = 7; rg.Cells.Value2 = "Dealer Code"; rg = worksheet.get_Range("I2", Type.Missing); rg.Cells.ColumnWidth = 13; rg.Cells.Value2 = "Firm Name"; rg = worksheet.get_Range("J2", Type.Missing); rg.Cells.ColumnWidth = 7; rg.Cells.Value2 = "Dealer Village"; rg = worksheet.get_Range("K2", Type.Missing); rg.Cells.ColumnWidth = 10; rg.Cells.Value2 = "Dealer Mandal"; rg = worksheet.get_Range("L2", Type.Missing); rg.Cells.ColumnWidth = 10; rg.Cells.Value2 = "Dealer Distrcit"; rg = worksheet.get_Range("M2", Type.Missing); rg.Cells.ColumnWidth = 10; rg.Cells.Value2 = "Dealer State"; rg = worksheet.get_Range("N2", Type.Missing); rg.Cells.ColumnWidth = 10; rg.Cells.Value2 = "Payment Mode"; rg = worksheet.get_Range("O2", Type.Missing); rg.Cells.ColumnWidth = 10; rg.Cells.Value2 = "Cheq/DD No"; rg = worksheet.get_Range("P2", Type.Missing); rg.Cells.ColumnWidth = 20; rg.Cells.Value2 = "Collected By"; rg = worksheet.get_Range("Q2", Type.Missing); rg.Cells.ColumnWidth = 20; rg.Cells.Value2 = "Received Amount"; rg = worksheet.get_Range("R2", Type.Missing); rg.Cells.ColumnWidth = 20; rg.Cells.Value2 = "Remarks"; rg = worksheet.get_Range("S2", Type.Missing); rg.Cells.ColumnWidth = 20; rg.Cells.Value2 = "Entered By"; int RowCounter = 2; foreach (DataRow dr in dtEx.Rows) { int i = 1; worksheet.Cells[RowCounter + 1, i++] = RowCounter - 1; worksheet.Cells[RowCounter + 1, i++] = dr["Company_Name"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["Branch_Name"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["FinYear"].ToString(); worksheet.Cells[RowCounter + 1, i++] = Convert.ToDateTime(dr["ReceivedDate"]).ToString("dd/MMM/yyyy"); worksheet.Cells[RowCounter + 1, i++] = dr["VoucherId"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["ReferenceNo"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["DealerCode"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["FirmName"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["DealerVillage"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["DealerMandal"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["DealerDistrict"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["DealerSate"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["PaymentMode"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["CheqOrDDNo"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["CollectedBy"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["ReceivedAmount"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["Remarks"].ToString(); worksheet.Cells[RowCounter + 1, i++] = dr["EnteredBy"].ToString(); RowCounter++; } //worksheet.Cells[RowCounter + 2, 22] = "=SUM(V3:V" + (dtEx.Rows.Count + 2) + ")"; //} //catch (Exception ex) //{ // MessageBox.Show(ex.ToString()); //} } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
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 } }