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