Saving data from SQL query in excel in C# console app -
i have c# program.cs code created save results sql query xls file, problem have 2 sql query , there 2 result sets need written excel file, able write 1st result through below code not able write second result, can please on this? please see code below, using same code block both queries seem results 1st query.
static void main(string[] args) { //sqlconnection cnn; //string connectionstring = null; string connectionstring = "integrated security = sspi;initial catalog=database; data source=<instance name>;"; string sql1 = null; string sql2 = null; string data = null; string data1 = null; int = 0; int j = 0; string filename = @"e:\file\testfile.xls"; if (!file.exists(filename)) { file.create(filename).dispose(); using (textwriter tw = new streamwriter(filename)) { tw.writeline("the first line!"); tw.close(); } } ////*** preparing excel application excel.application xlapp; excel.workbook xlworkbook; excel.worksheet xlworksheet; object misvalue = system.reflection.missing.value; ///*** opening excel application xlapp = new microsoft.office.interop.excel.application(); xlworkbook = xlapp.workbooks.open(filename); xlworksheet = (excel.worksheet)(xlworkbook.activesheet excel.worksheet); xlapp.displayalerts = false; sqlconnection conn = new sqlconnection(connectionstring); //cnn = new sqlconnection(connectionstring); conn.open(); ////** write sql query here sql1 = "select top 5 documentid, docfilename <table> order createdate desc"; sql2 = "select top 5 docid, title <table> order createdate desc"; ///*** preparing retrieve value database sql.datatable dtable = new sql.datatable(); sqldataadapter dscmd = new sqldataadapter(sql1, conn); sql.dataset ds = new sql.dataset(); dscmd.fill(dtable); ////*** generating column names here string[] colnames = new string[dtable.columns.count]; int col = 0; foreach (sql.datacolumn dc in dtable.columns) colnames[col++] = dc.columnname; char lastcolumn = (char)(65 + dtable.columns.count - 1); xlworksheet.get_range("a1", lastcolumn + "1").value2 = colnames; xlworksheet.get_range("a1", lastcolumn + "1").font.bold = true; xlworksheet.get_range("a1", lastcolumn + "1").verticalalignment = excel.xlvalign.xlvaligncenter; /////*** inserting column , values excel file (i = 0; <= dtable.rows.count - 1; i++) { (j = 0; j <= dtable.columns.count - 1; j++) { data = dtable.rows[i].itemarray[j].tostring(); xlworksheet.cells[i + 2, j + 1] = data; xlworkbook.save(); } } //enter new block in excel -- second query, need here below sql.datatable dtable1 = new sql.datatable(); sqldataadapter dscmd1 = new sqldataadapter(sql2, conn); sql.dataset ds1 = new sql.dataset(); dscmd1.fill(dtable1); ////*** generating column names here string[] colnames1 = new string[dtable1.columns.count]; int col1 = 0; foreach (sql.datacolumn dc in dtable1.columns) colnames1[col1++] = dc.columnname; char lastcolumn1 = (char)(68 + dtable1.columns.count - 1); xlworksheet.get_range("d1", lastcolumn1 + "1").value2 = colnames1; xlworksheet.get_range("d1", lastcolumn1 + "1").font.bold = true; xlworksheet.get_range("d1", lastcolumn1 + "1").verticalalignment = excel.xlvalign.xlvaligncenter; /////*** inserting column , values excel file (i = 0; <= dtable1.rows.count - 1; i++) { (j = 0; j <= dtable1.columns.count - 1; j++) { data1 = dtable1.rows[i].itemarray[j].tostring(); xlworksheet.cells[i + 2, j + 1] = data1; xlworkbook.save(); } } //end of block xlworkbook.close(true, misvalue, misvalue); xlapp.quit(); system.runtime.interopservices.marshal.releasecomobject(xlworksheet); system.runtime.interopservices.marshal.releasecomobject(xlworkbook); system.runtime.interopservices.marshal.releasecomobject(xlapp); }
Comments
Post a Comment