Click here to Skip to main content
15,885,309 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello everyone,

I am using following code to export data to excel.

C#
table.RenderControl(htw);

//  render the htmlwriter into the response
Response.Write("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
Response.Write("<head>");
Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=windows-1252\">");
Response.Write("<!--[if gte mso 9]>");
Response.Write("<xml>");
Response.Write("<x:ExcelWorkbook>");
Response.Write("<x:ExcelWorksheets>");
Response.Write("<x:ExcelWorksheet>");
//this line names the worksheet
Response.Write("<x:Name>Application Details</x:Name>");
Response.Write("<x:WorksheetOptions>");
//these 2 lines are what works the magic
Response.Write("<x:Panes>");
Response.Write("</x:Panes>");
Response.Write("</x:WorksheetOptions>");
Response.Write("</x:ExcelWorksheet>");
// ---------------------------------------
Response.Write("<x:ExcelWorksheet>");
//this line names the worksheet
Response.Write("<x:Name>Server Details </x:Name>");
Response.Write("<x:WorksheetOptions>");
//these 2 lines are what works the magic
Response.Write("<x:Panes>");
Response.Write("</x:Panes>");
Response.Write("</x:WorksheetOptions>");
Response.Write("</x:ExcelWorksheet>");
//--------------------------------------------
Response.Write("</x:ExcelWorksheets>");
Response.Write("</x:ExcelWorkbook>");
Response.Write("</xml>");
Response.Write("<![endif]-->");
Response.Write("</head>");
Response.Write("<body>");
HttpContext.Current.Response.Write(sw.ToString());
Response.Write("</body>");
Response.Write("</html>");


Response.End();

HttpContext.Current.Response.End();


This adds two sheets into excel and add data into first sheet i.e "Application Details"
I am not able to add data into 2nd sheet i.e "Server Details".

Please help if you can.

Thanks
Posted
v3

1 solution

You Can use Belowing code.....

C#
private void button1_Click_1(object sender, EventArgs e)
        {
 
            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app  = new Microsoft.Office.Interop.Excel.Application();
 
 
            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook =  app.Workbooks.Add(Type.Missing);
           
 
            // creating new Excelsheet in workbook
             Microsoft.Office.Interop.Excel._Worksheet worksheet = null;                   
           
           // see the excel sheet behind the program
            app.Visible = true;
          
           // get the reference of first sheet. By default its name is Sheet1.
           // store its reference to worksheet
            worksheet = workbook.Sheets["Sheet1"];
            worksheet = workbook.ActiveSheet;
 
            // changing the name of active sheet
            worksheet.Name = "Exported from gridview";
 
           
            // storing header part in Excel
            for(int i=1;i<datagridview1.columns.count+1;i++)>
            {
    worksheet.Cells[1, i] = dataGridView1.Columns[i-1].HeaderText;
            }
 
 
 
            // storing Each row and column value to excel sheet
            for (int i=0; i < dataGridView1.Rows.Count-1 ; i++)
            {
                for(int j=0;j<datagridview1.columns.count;j++)>
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }
 
 
            // save the application
            workbook.SaveAs("c:\\output.xls",Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive , Type.Missing, Type.Missing, Type.Missing, Type.Missing);
           
            // Exit from the application
          app.Quit();
        }
 
   
Note this part of code gets data from DataGridView and fills cells.

            // storing Each row and column value to excel sheet
            for (int i=0; i < dataGridView1.Rows.Count-1 ; i++)
            {
                for(int j=0;j<datagridview1.columns.count;j++)>
                {
                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                }
            }




I have taken dataGridView1.Rows.Count-1, because in datagridview it contains empty row at the last. (See in the figure of datagridview.)
 
Share this answer
 
v2
Comments
deepakdynamite 1-Apr-13 9:26am    
Thanks for your response. I used same thing but it this case "Microsoft.Office.Interop.Excel", we must have Office installed on server which is again a problem for me.
fjdiewornncalwe 4-Apr-13 15:14pm    
Plagiarized from here

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900