private void GetBranchLIst()
{
try
{
CommonInfoBal objComm = new CommonInfoBal();
DataTable data = new DataTable();
data = objComm.GetBranchList();
DataRow dr = data.NewRow();
dr["Branch_ID"] = 0;
dr["Branch_Name"] = "All";
data.Rows.InsertAt(dr, 0);
cmb_Branch.DataSource = data;
cmb_Branch.DisplayMember = "Branch_Name";
cmb_Branch.ValueMember = "Branch_ID";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void GetCommonInfo()
{
try
{
CommonInfoBal objCommonInfoBal = new CommonInfoBal();
DataRow drCommonInfo = null;
drCommonInfo = objCommonInfoBal.GetCommpanyInfo();
if (drCommonInfo != null)
{
_CommpanyName = drCommonInfo.Table.Rows[0][0].ToString();
_branchName = drCommonInfo.Table.Rows[0][1].ToString();
_branchAddress = drCommonInfo.Table.Rows[0][2].ToString();
_branchContactNumber = drCommonInfo.Table.Rows[0][3].ToString();
}
}
catch (Exception)
{
throw;
}
}
private void Export_xls(string FileName)
{
IPOReportBAL bal = new IPOReportBAL();
int dataSartingRowIndex = 7;
string[] ColumnsHeader = new string[] { "Trec Code", "DPID", "Customer ID", "Applicants Name", "Bo ID No", "Applicant Category", "Currency", "Amount", "Draft No", "BankName", "Branch Name", "Date", "Security Code", "Remarks" };
string[] ColumnsAlphabet = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
var missValue = System.Reflection.Missing.Value;
var excelApp = new Excel.ApplicationClass();
//ExcelDoc_Prepared(out excelApp);
var workbook = excelApp.Workbooks.Add(missValue);
var currentSheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
string password = string.Empty;
//password = objBal.GetAllEFT_File_Info_ByFileNo(txtFileNo.Text.Trim()).File_Password;
//Initial Formating
currentSheet.Columns.NumberFormat = "@";
currentSheet.Name = "KSCL EFT";
Excel.Range rngColumns = currentSheet.get_Range("A" + dataSartingRowIndex, "" + ColumnsAlphabet[ColumnsHeader.Length] + "" + dataSartingRowIndex);
rngColumns.Font.Bold = true;
rngColumns.Interior.Color = ColorTranslator.ToOle(Color.LightGray);
/////////////////////////////////
////////********** Set Company Name & Address ***************////////
CommonInfoBal objComm = new CommonInfoBal();
DataTable data = new DataTable();
data = objComm.GetCompanyNameHeadOffice();
//////// *************** Company Name ***************** ///////////
currentSheet.get_Range("A1", "H1").Merge(true);
currentSheet.get_Range("A1", "H1").Value2 = data.Rows[0]["BrokerName"].ToString();
currentSheet.get_Range("A1", "H1").HorizontalAlignment = 3;
currentSheet.get_Range("A1", "H1").Font.Name = "Arial";
currentSheet.get_Range("A1", "H1").Font.Bold = true;
currentSheet.get_Range("A1", "H1").Font.Size = 11;
currentSheet.get_Range("A1", "H1").RowHeight = 15;
currentSheet.get_Range("A1", "H1").VerticalAlignment = 3;
currentSheet.get_Range("A1", "H1").HorizontalAlignment = 3;
//////// *************** Company Name ***************** ///////////
currentSheet.get_Range("A2", "H2").Merge(true);
currentSheet.get_Range("A2", "H2").Value2 = data.Rows[0]["Address"].ToString();
currentSheet.get_Range("A2", "H2").HorizontalAlignment = 3;
currentSheet.get_Range("A2", "H2").Font.Name = "Arial";
currentSheet.get_Range("A2", "H2").Font.Size = 10;
currentSheet.get_Range("A2", "H2").RowHeight = 12.75;
currentSheet.get_Range("A2", "H2").VerticalAlignment = 3;
currentSheet.get_Range("A2", "H2").HorizontalAlignment = 3;
//////// *************** Phone & Fax ***************** ///////////
currentSheet.get_Range("A3", "H3").Merge(true);
currentSheet.get_Range("A3", "H3").Value2 = "Phone: " + data.Rows[0]["Telephone"].ToString() + ",Fax: " + data.Rows[0]["Fax"].ToString();
currentSheet.get_Range("A3", "H3").HorizontalAlignment = 3;
currentSheet.get_Range("A3", "H3").Font.Name = "Arial";
currentSheet.get_Range("A3", "H3").Font.Size = 10;
currentSheet.get_Range("A3", "H3").RowHeight = 12.75;
currentSheet.get_Range("A3", "H3").VerticalAlignment = 3;
currentSheet.get_Range("A3", "H3").HorizontalAlignment = 3;
//////// *************** Contact Numner ***************** ///////////
currentSheet.get_Range("A4", "H4").Merge(true);
currentSheet.get_Range("A4", "H4").Value2 = "Contact No: 01720220321, 01710878300";
currentSheet.get_Range("A4", "H4").HorizontalAlignment = 3;
currentSheet.get_Range("A4", "H4").Font.Name = "Arial";
currentSheet.get_Range("A4", "H4").Font.Size = 10;
currentSheet.get_Range("A4", "H4").RowHeight = 12.75;
currentSheet.get_Range("A4", "H4").VerticalAlignment = 3;
currentSheet.get_Range("A4", "H4").HorizontalAlignment = 3;
//////// *************** Cheque Requisition Date ***************** ///////////
currentSheet.get_Range("A5", "H5").Merge(true);
currentSheet.get_Range("A5", "H5").Value2 = "NRB Print Date: " + DateTime.Today.Date.ToString("dd-MM-yyyy");
currentSheet.get_Range("A5", "H5").HorizontalAlignment = 3;
currentSheet.get_Range("A5", "H5").Font.Name = "Arial";
currentSheet.get_Range("A5", "H5").Font.Bold = true;
currentSheet.get_Range("A5", "H5").Font.Size = 9;
currentSheet.get_Range("A5", "H5").Font.Underline = true;
currentSheet.get_Range("A5", "H5").RowHeight = 12.75;
currentSheet.get_Range("A5", "H5").VerticalAlignment = 3;
currentSheet.get_Range("A5", "H5").HorizontalAlignment = 3;
//////////////////////////////
//Insert Columns Name
int count_Columns = 1;
foreach (string str in ColumnsHeader)
{
currentSheet.Cells[dataSartingRowIndex, count_Columns] = str;
count_Columns++;
}
DataTable dt = new DataTable();
dt = bal.GetNRBSuccessfulUnsuccessful(cmbStatusName.Text, Cmbcompanyname.Text, CmbCustomerCode.Text);
List <Export_ExcelDocBo> resultList = new List <Export_ExcelDocBo>();
foreach (DataRow row in dt.Rows)
{
Export_ExcelDocBo obj = new Export_ExcelDocBo();
obj.TrecCode = Convert.ToString((int)row["Trec Code"]);
obj.DPID = (string)row["DPID"] ?? string.Empty;
obj.Cust_Code = (string)row["Customer ID"] ?? string.Empty;
obj.ReceiverName = (string)row["Applicants Name"] ?? string.Empty;
obj.BOID = (string)row["Bo ID No"] ?? string.Empty;
obj.ApplicantCategory = (string)row["Applicant Category"] ?? string.Empty;
obj.Currency = (string)row["Currency"] ?? string.Empty;
obj.Amount = Convert.ToDouble(row["Amount"]) != 0.00 ? Convert.ToDouble(row["Amount"]) : 0.00;
obj.DraftNO = (string)row["Draft No"] ?? string.Empty;
obj.BankName = (string)row["BankName"] ?? string.Empty;
obj.BranchName = (string)row["Branch Name"] ?? string.Empty;
obj.Received_Date = Convert.ToDateTime(row["Date"]);
obj.SecurityCode = (string)row["Security Code"] ?? string.Empty;
obj.Reason = (string)row["Remarks"] ?? string.Empty;
resultList.Add(obj);
}
var dataList = resultList;
int count_Row = dataSartingRowIndex + 1;
foreach (var obj in dataList)
{
currentSheet.Cells[count_Row, 1] = Convert.ToString(obj.TrecCode).Trim();
currentSheet.Cells[count_Row, 2] = Convert.ToString(obj.DPID).Trim();
currentSheet.Cells[count_Row, 3] = Convert.ToString(obj.Cust_Code).Trim();
currentSheet.Cells[count_Row, 4] = Convert.ToString(obj.ReceiverName).Trim();
currentSheet.Cells[count_Row, 5] = Convert.ToString(obj.BOID).Trim();
currentSheet.Cells[count_Row, 6] = Convert.ToString(obj.ApplicantCategory).Trim();
currentSheet.Cells[count_Row, 7] = Convert.ToString(obj.Currency).Trim();
currentSheet.Cells[count_Row, 8] = Convert.ToString(obj.Amount).Trim();
currentSheet.Cells[count_Row, 9] = Convert.ToString(obj.DraftNO).Trim();
currentSheet.Cells[count_Row, 10] = Convert.ToString(obj.BankName).Trim();
currentSheet.Cells[count_Row, 11] = Convert.ToString(obj.BranchName).Trim();
currentSheet.Cells[count_Row, 12] = Convert.ToString(obj.Received_Date.ToShortDateString());
currentSheet.Cells[count_Row, 13] = Convert.ToString(obj.SecurityCode).Trim();
currentSheet.Cells[count_Row, 14] = Convert.ToString(obj.Reason).Trim();
count_Row++;
}
//Column Resizing and Post Formatting
Excel.Range rngALL = currentSheet.get_Range("A" + dataSartingRowIndex, "" + ColumnsAlphabet[ColumnsHeader.Length] + "" + (dataSartingRowIndex + (dataList.Count + 1)));
rngALL.Columns.AutoFit();
rngALL.VerticalAlignment = 3;
rngALL.HorizontalAlignment = 3;
//currentSheet.Protect(password, true, true, true, false, false, true, true, false, false, false, false, false, true, true, false);
workbook.Author = "K-Secuirites Ltd.";
workbook.SaveAs(FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, password, missValue,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
missValue, missValue, missValue, missValue, missValue);
workbook.Close(true, missValue, missValue);
excelApp.Quit();
releaseObject(excelApp);
releaseObject(workbook);
releaseObject(currentSheet);
}
