Exporting from sql server 2008 r2 to excel with header

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Arjay Nebres
    New Member
    • Dec 2013
    • 2

    #1

    Exporting from sql server 2008 r2 to excel with header

    I'am trying to export data from my database to excel . The problem is my code is running but once i check my excel file there's no header included but just only the data in every particular header.
    here's my code .. please suggest or help me to solve my problem . thank you.


    Code:
    //EXPORT
               SqlConnection cnn;
                string connectionstring = null;
                string sql = null;
                string data = null;
                int i = 0;
                int j = 0;
                string title=store.Text;
     
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
     
     
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    
                connectionstring = "Data Source=user-pc\\sqlexpress;Initial Catalog=StoreBarcode;Integrated Security=True";
                cnn = new SqlConnection(connectionstring);
    
                try
                {
                    cnn.Open();
                    sql = "SELECT * FROM Barcode." + title + "";
                    SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
                    DataSet ds = new DataSet();
                    dscmd.Fill(ds);
    
                    for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                    {
                        for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                        {
                            data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                            xlWorkSheet.Cells[i + 1, j + 1] = data;
                        }
                    }
                }
                catch
                {
                    MessageBox.Show("Choose from Store to Export!");
                }
    
                try
                {
                    xlWorkBook.SaveAs(""+title+".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                    xlWorkBook.Close(true, misValue, misValue);
    
                    xlApp.Quit();
    
    
                    releaseObject(xlWorkSheet);
                    releaseObject(xlWorkBook);
                    releaseObject(xlApp);
    
                    MessageBox.Show("Excel file created , you can find the file @ C:\\Users\\User\\Documents");
                }
                catch {
                    MessageBox.Show("Exporting has been Cancelled!");
                }
Working...