private void DoRun()
{
string strConn =
@"User=SYSDBA;
Password=SaDM1n@;
Database=main;
DataSource=iscard-db.bank.lan;
Port=3050;
Charset=WIN1251";
string strSQL = @"select PR.PROJECTID, PR.PROJECTNAME
from ACCOUNTS AC
join PROJECTCLASS PR on PR.PROJECTID = AC.PROJECTID + 0
where AC.ACCNUM = @PARAM
union all
select PR.PROJECTID,PR.PROJECTNAME
from EAST_ACC_POOL AP
join AGREEMENTS K on k.contractid = ap.contractid + 0
join PROJECTCLASS PR on PR.PROJECTID = K.PROJECTID + 0
where AP.ACCNUM = @PARAM";
string strSQL1 = @"select f.birthplace
from east_privateclientsform p
left join FINMONFORM F on f.clientprivid=p.clientprivid+0
where p.clientodbid=@PARAM";
string strSQL2 = @"select f.ework
from east_privateclientsform p
left join privateclients F on f.clientprivid=p.clientprivid+0
where p.clientodbid=@PARAM";
string strSQL3 = @"select F.EWORK, F.CLPOSITION, F.BIRTHDATE,P.CLIENTTYPE, list(AG.CONTRACTNUM || ';' || PR.PROJECTNAME, '|') as CNUM_PRJ
from EAST_PRIVATECLIENTSFORM P
join PRIVATECLIENTS F on F.CLIENTPRIVID = P.CLIENTPRIVID + 0
join AGREEMENTS AG on AG.CLIENTPRIVID = P.CLIENTPRIVID + 0
join PROJECTCLASS PR on PR.PROJECTID = AG.PROJECTID + 0
join BALANCES BL on BL.CONTRACTID = AG.CONTRACTID + 0 and
BL.ACCSTATUSID <> 2
where P.CLIENTODBID = @PARAM
group by 1, 2, 3, 4";
string strSQL4 = @"select F.HZIPCODE,
P.P_REG_REGIONNAME,P.P_REG_DISTRICTNAME,P.P_REG_CITYNAME,
P.P_REG_STREET,P.P_REG_BUILDING,P.P_REG_KORP,P.P_REG_FLAT,
P.P_EQUAL_ADDRESS,P.P_POST_INDEX,P.P_ADR_REGIONNAME,
P.P_ADR_DISTRICTNAME,P.P_ADR_CITYNAME,P.P_ADR_BUILDING,
P.P_ADR_STREET,P.P_ADR_KORP,P.P_ADR_FLAT,
F.EWORK,F.CLPOSITION
from EAST_PRIVATECLIENTSFORM P
join PRIVATECLIENTS F on F.CLIENTPRIVID = P.CLIENTPRIVID + 0
where P.CLIENTODBID = @PARAM";
string strSQL5 = @"select ap.iban
from accounts ac
join east_acc_pool ap on ap.contractid=ac.contractid
where ac.accnum = @PARAM
union all
select ap.iban
from east_acc_pool ap
where ap.accnum = @PARAM";
var result = openFileDialog1.ShowDialog();
if (result != DialogResult.OK)
{
MessageBox.Show("Файл не выбран!", "Информация");
return;
}
//string filename = System.IO.Path.GetFileName(openFileDialog1.FileName);
//String xlsPath = System.IO.Path.GetDirectoryName(openFileDialog1.FileName);
progressBar1.Value = 0;
label1.Text = openFileDialog1.FileName;
label2.Text = progressBar1.Value.ToString();
FbDataReader rdr;
FbCommand cmd;
using (FBDatabase fB = new FBDatabase(strConn)) {
fB.OpenConection();
//cmd = fB.ReturnFbCommand(strSQL);
//cmd = fB.ReturnFbCommand(strSQL1);
//cmd = fB.ReturnFbCommand(strSQL2);
//cmd = fB.ReturnFbCommand(strSQL3);
//cmd = fB.ReturnFbCommand(strSQL4);
cmd = fB.ReturnFbCommand(strSQL5);
//using (ExcelDocument excelDoc = new ExcelDocument(openFileDialog1.FileName)) {
// excelDoc.Visible = false;
ExcelPackage excelDoc = new ExcelPackage(openFileDialog1.FileName);
try {
progressBar1.Maximum = excelDoc.RowsWithDataCount + 1;
for (int i = 1; i < excelDoc.RowsWithDataCount + 1; i++)
{
progressBar1.Value = i;
label2.Text = progressBar1.Value.ToString();
//MessageBox.Show(excelDoc.GetCellValue(i, 1));
if (StrNotNumber(excelDoc.GetCellValue(i, 1).ToString().Trim()))
{
cmd.Parameters.Clear();
//cmd.Parameters.Add("@PARAM", FbDbType.Text).Value = excelDoc.GetCellValue(i, 1).ToString().Trim();
cmd.Parameters.Add("@PARAM", FbDbType.Text).Value = excelDoc.GetCellValue(i, 7).ToString().Trim();
cmd.Prepare();
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
//while (rdr.Read()) {
if (!rdr.IsDBNull(0))
{
//MessageBox.Show(rdr["PROJECTID"].ToString() + " " + rdr["PROJECTNAME"].ToString());
/*
* excelDoc.SetCustomFormat("###0", i, 6);
* excelDoc.SetCellValue(Convert.ToInt32(rdr["PROJECTID"].ToString()), i, 6);
* excelDoc.SetCustomFormat("@", i, 7);
* excelDoc.SetCellValue(rdr["PROJECTNAME"].ToString(), i, 7);
*/
//excelDoc.SetCustomFormat("@", i, 7);
//excelDoc.SetCellValue(rdr["birthplace"].ToString(), i, 7);
//excelDoc.SetCustomFormat("@", i, 5);
//excelDoc.SetCellValue(rdr["ework"].ToString(), i, 5);
/*
* excelDoc.SetCustomFormat("@", i, 5);
* excelDoc.SetCellValue(rdr["EWORK"].ToString(), i, 5);
* excelDoc.SetCustomFormat("@", i, 6);
* excelDoc.SetCellValue(rdr["CLPOSITION"].ToString(), i, 6);
* excelDoc.SetCustomFormat("dd.mm.yyyy", i, 7);
* excelDoc.SetCellValue(Convert.ToDateTime(rdr["BIRTHDATE"].ToString()), i, 7);
* excelDoc.SetCustomFormat("#0", i, 8);
* excelDoc.SetCellValue(Convert.ToInt32(rdr["CLIENTTYPE"].ToString()), i, 8);
*
* string[] arr_cnumprj = rdr["CNUM_PRJ"].ToString().Split('|');
* int z = 9;
* foreach (var cnumprj in arr_cnumprj) {
* string[] arr = cnumprj.Split(';');
* //MessageBox.Show(arr[0] + " - " + arr[1]);
* excelDoc.SetCustomFormat("@", i, z);
* excelDoc.SetCellValue(arr[0].ToString(), i, z);
* z++;
* excelDoc.SetCustomFormat("@", i, z);
* excelDoc.SetCellValue(arr[1].ToString(), i, z);
* z++;
* }
*/
/*
* excelDoc.SetCustomFormat("@", i, 30);
* excelDoc.SetCellValue(rdr["HZIPCODE"].ToString(), i, 30);
* excelDoc.SetCustomFormat("@", i, 31);
* excelDoc.SetCellValue(rdr["P_REG_REGIONNAME"].ToString(), i, 31);
* excelDoc.SetCustomFormat("@", i, 32);
* excelDoc.SetCellValue(rdr["P_REG_DISTRICTNAME"].ToString(), i, 32);
* excelDoc.SetCustomFormat("@", i, 33);
* excelDoc.SetCellValue(rdr["P_REG_CITYNAME"].ToString(), i, 33);
* excelDoc.SetCustomFormat("@", i, 34);
* excelDoc.SetCellValue(rdr["P_REG_STREET"].ToString(), i, 34);
*
* excelDoc.SetCustomFormat("@", i, 35);
* excelDoc.SetCellValue(rdr["P_REG_BUILDING"].ToString(), i, 35);
* excelDoc.SetCustomFormat("@", i, 36);
* excelDoc.SetCellValue(rdr["P_REG_KORP"].ToString(), i, 36);
* excelDoc.SetCustomFormat("@", i, 37);
* excelDoc.SetCellValue(rdr["P_REG_FLAT"].ToString(), i, 37);
*
* excelDoc.SetCustomFormat("@", i, 38);
* excelDoc.SetCellValue(rdr["P_EQUAL_ADDRESS"].ToString(), i, 38);
* excelDoc.SetCustomFormat("@", i, 39);
* excelDoc.SetCellValue(rdr["P_POST_INDEX"].ToString(), i, 39);
* excelDoc.SetCustomFormat("@", i, 40);
* excelDoc.SetCellValue(rdr["P_ADR_REGIONNAME"].ToString(), i, 40);
*
* excelDoc.SetCustomFormat("@", i, 41);
* excelDoc.SetCellValue(rdr["P_ADR_DISTRICTNAME"].ToString(), i, 41);
* excelDoc.SetCustomFormat("@", i, 42);
* excelDoc.SetCellValue(rdr["P_ADR_CITYNAME"].ToString(), i, 42);
* excelDoc.SetCustomFormat("@", i, 43);
* excelDoc.SetCellValue(rdr["P_ADR_BUILDING"].ToString(), i, 43);
*
* excelDoc.SetCustomFormat("@", i, 44);
* excelDoc.SetCellValue(rdr["P_ADR_STREET"].ToString(), i, 44);
* excelDoc.SetCustomFormat("@", i, 45);
* excelDoc.SetCellValue(rdr["P_ADR_KORP"].ToString(), i, 45);
* excelDoc.SetCustomFormat("@", i, 46);
* excelDoc.SetCellValue(rdr["P_ADR_FLAT"].ToString(), i, 46);
*
*/
/*
* excelDoc.SetCustomFormat("@", i, 5);
* excelDoc.SetCellValue(rdr["EWORK"].ToString(), i, 5);
* excelDoc.SetCustomFormat("@", i, 6);
* excelDoc.SetCellValue(rdr["CLPOSITION"].ToString(), i, 6);
*/
excelDoc.SetCustomFormat("@", i, 8);
excelDoc.SetCellValue(rdr["iban"].ToString(), i, 8);
}
}
rdr.Close();
}
Application.DoEvents();
}
//excelDoc.workSheet.Columns.AutoFit();
//excelDoc.application.DisplayAlerts = false;
//excelDoc.workBook.SaveAs(openFileDialog1.FileName);
excelDoc.AutoFitAll();
excelDoc.application.SaveAs(new FileInfo(openFileDialog1.FileName));
} finally {
excelDoc.Dispose();
}
progressBar1.Value = 0;
label1.Text = "Файл успешно обработан!";
}
}