public void WriteToExcel(int row, template temp)
{
try
{
xlWorkSheet.Cells[row, 1] = temp.index_card;
xlWorkSheet.Cells[row, 2] = temp.index1;
xlWorkSheet.Cells[row, 3] = temp.index2;
xlWorkSheet.Cells[row, 4] = temp.index3;
xlWorkSheet.Cells[row, 5] = temp.index4;
xlWorkSheet.Cells[row, 6] = temp.index5;
xlWorkSheet.Cells[row, 7] = temp.index6;
xlWorkSheet.Cells[row, 8] = temp.index7;
xlWorkSheet.Cells[row, 9] = temp.index8;
xlWorkSheet.Cells[row, 10] = temp.index9;
xlWorkSheet.Cells[row, 11] = temp.index10;
xlWorkSheet.Cells[row, 12] = temp.index11;
xlWorkSheet.Cells[row, 13] = temp.index12;
xlWorkSheet.Cells[row, 14] = temp.index13;
xlWorkSheet.Cells[row, 15] = temp.index14;
xlWorkSheet.Cells[row, 16] = temp.index15;
xlWorkSheet.Cells[row, 17] = temp.index16;
xlWorkSheet.Cells[row, 18] = temp.index17;
xlWorkSheet.Cells[row, 19] = temp.index18;
xlWorkSheet.Cells[row, 20] = temp.index19;
xlWorkSheet.Cells[row, 21] = temp.index20;
xlWorkSheet.Cells[row, 22] = temp.Original_Document_Name;
xlWorkSheet.Cells[row, 23] = temp.Document_Type;
xlWorkSheet.Cells[row, 24] = temp.version;
xlWorkSheet.Cells[row, 25] = temp.obj_id;
xlWorkSheet.Cells[row, 26] = temp.paraent_id;
xlWorkSheet.Cells[row, 27] = temp.owner;
xlWorkSheet.Cells[row, 28] = temp.last_modify_date;
xlWorkSheet.Cells[row, 29] = temp.create_date;
xlWorkSheet.Cells[row, 30] = temp.dms_path;
xlWorkSheet.Cells[row, 31] = temp.physical_file_path;
//xlWorkBook.Save();
}
catch (Exception ex)
{
throw ex;
}
}
public void Create_Excel()
{
string connetionString = null;
SqlConnection connection;
//SqlCommand command;
//string SQL = "select * from obj as a left join (select a.obj_id , a.parent_id , obj_name , obj_create_time, obj_modify_time , obj_owner ,temp_id , doc_ext, doc_type, doc_version , doc_extpath from obj as a left join document as b on a.obj_id = b.obj_id where a.obj_type ='D'" ;
//SQL = SQL + ") as b on a.parent_id = b.temp_id where a.parent_id in (select temp_id from obj as a left join document as b on a.obj_id = b.obj_id where a.obj_type ='D')";
string SQL = "select a.obj_id as index_parent_id , a.obj_name as index_name , b.* from obj as a left join (select a.obj_id as docID, a.parent_id , obj_name , obj_create_time, obj_modify_time , obj_owner ,temp_id , doc_ext, doc_type, doc_version , doc_extpath from obj as a left join document as b on a.obj_id = b.obj_id where a.obj_type ='D') as b on a.parent_id = b.temp_id where a.parent_id in (select temp_id from obj as a left join document as b on a.obj_id = b.obj_id where a.obj_type ='D') order by docID";
string password = "******";
connetionString = "Data Source=RDTEST1\\RECTIDOC;Initial Catalog=RECTIDOC;User ID=sa;Password="******"";
temp.index1 = ds.Tables[0].Rows[i].ItemArray[1].ToString();
temp.Original_Document_Name = ds.Tables[0].Rows[i].ItemArray[4].ToString();
temp.Document_Type = ds.Tables[0].Rows[i].ItemArray[10].ToString();
temp.version = Int32.Parse(ds.Tables[0].Rows[i].ItemArray[11].ToString());
temp.obj_id = Int32.Parse(ds.Tables[0].Rows[i].ItemArray[2].ToString());
temp.paraent_id = Int32.Parse(ds.Tables[0].Rows[i].ItemArray[3].ToString());
temp.owner = ds.Tables[0].Rows[i].ItemArray[7].ToString();
temp.last_modify_date = ds.Tables[0].Rows[i].ItemArray[6].ToString();
temp.create_date = ds.Tables[0].Rows[i].ItemArray[5].ToString();
temp.dms_path = "";
temp.physical_file_path = "";
}
for (int p = 1; p < 19; p++)
{
if (CurrentDocID == ds.Tables[0].Rows[currentRow + p].ItemArray[2].ToString())
{
count++;
switch (count)
{
case 1:
temp.index2 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 2:
temp.index3 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 3:
temp.index4 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 4:
temp.index5 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 5:
temp.index6 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 6:
temp.index7 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 7:
temp.index8 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 8:
temp.index9 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 9:
temp.index10 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 10:
temp.index11 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 11:
temp.index12 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 12:
temp.index13 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 13:
temp.index14 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 14:
temp.index15 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 15:
temp.index16 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 16:
temp.index17 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 17:
temp.index18 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 18:
temp.index19 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
case 19:
temp.index20 = ds.Tables[0].Rows[currentRow + p].ItemArray[1].ToString();
break;
}
i++;
}
else
{
p = 20;
}
}
WriteToExcel(ExcelCount++, temp);
}
xlWorkBook.Save();
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
//string SQL3 = "drop table #temp1 ";
//using (var cmd = _SQLConnection.conn.CreateCommand())
//{
// _SQLConnection.conn.Open();
// cmd.CommandText = SQL3;
// var result = cmd.ExecuteNonQuery();
//}
}