private string read_excel_cell_string(int row, int col, xExcel objExcel)
{
Microsoft.Office.Interop.Excel.Range objCell;
objCell = (Microsoft.Office.Interop.Excel.Range)objExcel.excelWorksheet.Cells[row, col];
try { return((string)objCell.Cells.Value2); }
catch { return(""); }
}
private double read_excel_cell_numeric(int row, int col, xExcel objExcel)
{
Microsoft.Office.Interop.Excel.Range objCell;
objCell = (Microsoft.Office.Interop.Excel.Range)objExcel.excelWorksheet.Cells[row, col];
try { return((double)objCell.Cells.Value2); }
catch { return(0); }
}
private void write_lending_toexcel(string reff, string cat, int startrow, xExcel objExcel)
{
object[] par = new object[] { reff, cat };
DataTable dt = conn.GetDataTable("select * from vw_appfincal_lending where reffnumber=@1 and category = @2 order by seq", par, dbtimeout);
int r; string seq;
double installment;
Microsoft.Office.Interop.Excel.Range objCell;
for (int i = 0; i < dt.Rows.Count; i++)
{
r = startrow + i;
objCell = (Microsoft.Office.Interop.Excel.Range)objExcel.excelWorksheet.Cells[r, 6];
objCell.Value2 = dt.Rows[i]["bank_name"].ToString();
objCell = (Microsoft.Office.Interop.Excel.Range)objExcel.excelWorksheet.Cells[r, 8];
objCell.Value2 = dt.Rows[i]["ket_kondisi"].ToString();
objCell = (Microsoft.Office.Interop.Excel.Range)objExcel.excelWorksheet.Cells[r, 10];
objCell.Value2 = dt.Rows[i]["sifat"].ToString();
objCell = (Microsoft.Office.Interop.Excel.Range)objExcel.excelWorksheet.Cells[r, 12];
objCell.Value2 = dt.Rows[i]["latest_status"].ToString();
objCell = (Microsoft.Office.Interop.Excel.Range)objExcel.excelWorksheet.Cells[r, 14];
if (cat == "CC" || cat == "PRK")
{
objCell.Value2 = dt.Rows[i]["plafond"].ToString();
}
else
{
objCell.Value2 = dt.Rows[i]["baki_debet"].ToString();
}
objCell = (Microsoft.Office.Interop.Excel.Range)objExcel.excelWorksheet.Cells[r, 17];
if (cat != "PRK")
{
objCell.Value2 = dt.Rows[i]["tenor"].ToString();
}
objCell = (Microsoft.Office.Interop.Excel.Range)objExcel.excelWorksheet.Cells[r, 20];
if (cat == "CC")
{
objCell.Value2 = dt.Rows[i]["baki_debet"].ToString();
}
else
{
objCell.Value2 = dt.Rows[i]["annual_interest"].ToString();
}
installment = read_excel_cell_numeric(r, 22, objExcel);
seq = dt.Rows[i]["seq"].ToString();
try
{
string sql = "exec usp_appfincal_lending_updinst @1, @2, @3, @4";
object[] param = new object[] { reff, cat, seq, installment };
conn.ExecNonQuery(sql, param, dbtimeout);
} catch {};
}
}
private void write_read_to_template()
{
string Q_TEMPLATEPROC = "exec USP_EXPORTEXCEL_LISTPROCEDURE @1 ";
string Q_TEMPLATEDETAIL = "exec USP_EXPORTEXCEL_LISTDETAIL @1, @2 ";
string xtpl = "CALC";
string appid = reffnumber.Text.Trim();
string resultPath = Server.MapPath("../Templates/Download");
string resultFilename = appid + ".xls";
string resultFullpath = resultPath + "/" + resultFilename;
string tplFile = Server.MapPath("../Templates/Master/LoanCalculator.xlt");
if (!Directory.Exists(resultPath))
{
Directory.CreateDirectory(resultPath);
}
if (File.Exists(resultFullpath))
{
File.Delete(resultFullpath);
}
xExcel objExcel = new xExcel();
Microsoft.Office.Interop.Excel.Range objCell;
objExcel.OpenFile(tplFile, "");
#region Fill Data
object[] parproc = new object[1] {
xtpl
};
DataTable dt_proc = conn.GetDataTable(Q_TEMPLATEPROC, parproc, dbtimeout);
for (int k = 0; k < dt_proc.Rows.Count; k++)
{
int procseq = (int)dt_proc.Rows[k]["proc_seq"];
string procname = dt_proc.Rows[k]["proc_name"].ToString();
object[] pardet = new object[2] {
xtpl, procseq
};
DataTable dt_field = conn.GetDataTable(Q_TEMPLATEDETAIL, pardet, dbtimeout);
DataTable dt_data = conn.GetDataTable("EXEC " + procname + " '" + appid + "'", null, dbtimeout);
if (dt_data.Rows.Count > 0)
{
for (int i = 0; i < dt_field.Rows.Count; i++)
{
try
{
string SheetNm = dt_field.Rows[i]["sheet"].ToString();
int col = (int)dt_field.Rows[i]["col"];
int row = (int)dt_field.Rows[i]["row"];
string Field = dt_field.Rows[i]["field"].ToString();
object objValue = dt_data.Rows[0][Field].ToString();
string strObject = objValue.ToString();
objExcel.excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)objExcel.excelSheets[SheetNm];
objCell = (Microsoft.Office.Interop.Excel.Range)objExcel.excelWorksheet.Cells[row, col];
objCell.Value2 = objValue;
}
catch (Exception e2)
{
ModuleSupport.LogError(this.Page, e2);
}
}
}
}
#endregion
#region write lending data
write_lending_toexcel(reffnumber.Text.Trim(), "CC", 15, objExcel);
write_lending_toexcel(reffnumber.Text.Trim(), "UL", 48, objExcel);
write_lending_toexcel(reffnumber.Text.Trim(), "SL", 66, objExcel);
write_lending_toexcel(reffnumber.Text.Trim(), "MKI", 89, objExcel);
write_lending_toexcel(reffnumber.Text.Trim(), "INV", 107, objExcel);
write_lending_toexcel(reffnumber.Text.Trim(), "PRK", 120, objExcel);
#endregion
#region read from excel
double iue, tue, tot_monthly_installment, mue1, mue2, income_multiplier1, income_multiplier2, max_dbr1, max_dbr2, dbr_actual, proposal_limit, installment, max_installment;
string estimated_cost, final_recommendation, incomeexpense_result;
NameValueCollection key = new NameValueCollection();
staticFramework.saveNVC(key, reffnumber);
NameValueCollection Fields = new NameValueCollection();
iue = read_excel_cell_numeric(133, 3, objExcel); staticFramework.saveNVC(Fields, "iue", iue);
tue = read_excel_cell_numeric(133, 14, objExcel); staticFramework.saveNVC(Fields, "tue", tue);
tot_monthly_installment = read_excel_cell_numeric(133, 20, objExcel); staticFramework.saveNVC(Fields, "tot_monthly_installment", tot_monthly_installment);
//mue1 = read_excel_cell_numeric(168, 14, objExcel); staticFramework.saveNVC(Fields, "mue1", mue1);
//mue2 = read_excel_cell_numeric(168, 17, objExcel); staticFramework.saveNVC(Fields, "mue2", mue2);
//income_multiplier1 = read_excel_cell_numeric(169, 14, objExcel); staticFramework.saveNVC(Fields, "income_multiplier1", income_multiplier1);
//income_multiplier2 = read_excel_cell_numeric(169, 17, objExcel); staticFramework.saveNVC(Fields, "income_multiplier2", income_multiplier2);
max_dbr1 = read_excel_cell_numeric(180, 8, objExcel); staticFramework.saveNVC(Fields, "max_dbr1", max_dbr1);
//max_dbr2 = read_excel_cell_numeric(170, 17, objExcel); staticFramework.saveNVC(Fields, "max_dbr2", max_dbr2);
dbr_actual = read_excel_cell_numeric(180, 17, objExcel); staticFramework.saveNVC(Fields, "dbr_actual", dbr_actual);
proposal_limit = read_excel_cell_numeric(176, 22, objExcel); staticFramework.saveNVC(Fields, "proposal_limit", proposal_limit);
max_installment = read_excel_cell_numeric(177, 22, objExcel); staticFramework.saveNVC(Fields, "max_installment", max_installment);
installment = read_excel_cell_numeric(177, 17, objExcel); staticFramework.saveNVC(Fields, "installment", installment);
estimated_cost = read_excel_cell_string(179, 8, objExcel); staticFramework.saveNVC(Fields, "estimated_cost", estimated_cost);
try
{
estimated_cost = read_excel_cell_numeric(179, 8, objExcel).ToString(); staticFramework.saveNVC(Fields, "estimated_cost", estimated_cost);
} catch {}
incomeexpense_result = read_excel_cell_string(180, 22, objExcel); staticFramework.saveNVC(Fields, "incomeexpense_result", incomeexpense_result);
final_recommendation = read_excel_cell_string(183, 8, objExcel); staticFramework.saveNVC(Fields, "final_recommendation", final_recommendation);
staticFramework.save(Fields, key, "appfincal_summary", conn);
double highest_limitcc_mob06, highest_limitcc_mob12, lowest_limitcc_mob06, lowest_limitcc_mob12;
string bic_result_temp;
Fields.Clear();
highest_limitcc_mob06 = read_excel_cell_numeric(163, 14, objExcel); staticFramework.saveNVC(Fields, "highest_limitcc_mob06", highest_limitcc_mob06);
highest_limitcc_mob12 = read_excel_cell_numeric(163, 22, objExcel); staticFramework.saveNVC(Fields, "highest_limitcc_mob12", highest_limitcc_mob12);
lowest_limitcc_mob06 = read_excel_cell_numeric(164, 14, objExcel); staticFramework.saveNVC(Fields, "lowest_limitcc_mob06", lowest_limitcc_mob06);
lowest_limitcc_mob12 = read_excel_cell_numeric(164, 22, objExcel); staticFramework.saveNVC(Fields, "lowest_limitcc_mob12", lowest_limitcc_mob12);
bic_result_temp = read_excel_cell_string(166, 14, objExcel); staticFramework.saveNVC(Fields, "bic_result_temp", bic_result_temp);
staticFramework.save(Fields, key, "appfincal_bicsummary", conn);
try
{
objExcel.CloseFile(resultFullpath);
objExcel.stopExcel();
}
catch (Exception e3)
{
ModuleSupport.LogError(this.Page, e3);
try
{
objExcel.CloseFile();
objExcel.stopExcel();
}
catch { }
}
#endregion
}
private string CreateExcel(string xtpl, string regno, string var_user)
{
string mStatus = "Template parameter not set!";
bool bSukses = true;
/// Mengambil application root
string path_in = SvrTplPhysic;
string path_out = SvrPathPhysic;
string file_xlt = xtpl + ".XLT";
string fileNm = xtpl + "-" + regno + ".XLS";
/// Cek apakah file templatenya (input) ada atau tidak
if (!File.Exists(path_in + file_xlt))
{
throw new Exception("File Template tidak ada!");
}
/// Cek direktori untuk menyimpan file hasil export (output)
if (!Directory.Exists(path_out))
{
Directory.CreateDirectory(path_out);
}
/// Cek file untuk menyimpan file hasil export (output)
if (File.Exists(path_out + fileNm))
{
File.Delete(path_out + fileNm);
}
// rony's excel object variables
xExcel objExcel = new xExcel();
objExcel.OpenFile(path_in + file_xlt, "");
#region Fill Data
object[] parproc = new object[1] {
xtpl
};
DataTable dt_proc = conn.GetDataTable(Q_TEMPLATEPROC, parproc, dbtimeout);
for (int k = 0; k < dt_proc.Rows.Count; k++)
{
int procseq = (int)dt_proc.Rows[k]["PROC_SEQ"];
string procname = dt_proc.Rows[k]["PROC_NAME"].ToString();
object[] pardet = new object[2] {
xtpl, procseq
};
DataTable dt_field = conn.GetDataTable(Q_TEMPLATEDETAIL, pardet, dbtimeout);
DataTable dt_data = conn.GetDataTable("EXEC " + procname + " '" + regno + "'", null, dbtimeout);
if (dt_data.Rows.Count > 0)
{
for (int i = 0; i < dt_field.Rows.Count; i++)
{
try
{
string SheetNm = dt_field.Rows[i]["SHEET"].ToString();
int col = (int)dt_field.Rows[i]["COL"];
int row = (int)dt_field.Rows[i]["ROW"];
string Field = dt_field.Rows[i]["FIELD"].ToString();
object objValue = dt_data.Rows[0][Field].ToString();
string strObject = objValue.ToString();
objExcel.excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)objExcel.excelSheets[0];
Microsoft.Office.Interop.Excel.Range objCell = (Microsoft.Office.Interop.Excel.Range)objExcel.excelWorksheet.Cells[row, col];
objCell.Value2 = objValue;
}
catch (Exception e2)
{
mStatus = "Terjadi error saat mengisi data";
ModuleSupport.LogError(this.Page, e2);
}
}
}
}
#endregion
try
{
objExcel.CloseFile(path_out + fileNm);
objExcel.stopExcel();
mStatus = "Pembuatan File Berhasil";
}
catch (Exception e3)
{
ModuleSupport.LogError(this.Page, e3);
mStatus = "Pembuatan File Gagal";
try
{
objExcel.CloseFile();
objExcel.stopExcel();
}
catch { }
}
return(mStatus);
}
