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); } 

image excel result


Comments

Popular posts from this blog

serialization - Convert Any type in scala to Array[Byte] and back -

matplotlib support failed in PyCharm on OSX -

python - Matplotlib: TypeError: 'AxesSubplot' object is not callable -