Click here to Skip to main content
15,919,613 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
<pre> private string ExcelHeader()
        {
            // Excel header
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.AppendFormat("<?xml version=\"1.0\"?>\n");
            sb.Append("<?mso-application progid=\"Excel.Sheet\"?>\n");
            sb.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
            sb.Append("xmlns:o=\"urn:schemas-microsoft-com:office:office\" ");
            sb.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
            sb.Append("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
            sb.Append("xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n");
            sb.Append("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
            sb.Append("</DocumentProperties>");
            sb.Append("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\n");
            sb.Append("<ProtectStructure>Ture</ProtectStructure>\n");
            sb.Append("<ProtectWindows>False</ProtectWindows>\n");
            sb.Append("</ExcelWorkbook>\n");
            return sb.ToString();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source=sanjay;Initial Catalog=Login;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("select * from login", con);
            System.Data.DataTable dt = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            sda.Fill(dt);
            StringBuilder strExcelXml = new StringBuilder();
            if (dt.Rows.Count > 0)
            {
                //First Write the Excel Header
                strExcelXml.Append(ExcelHeader());
                string sanjay = "sanjay";
                // Create First Worksheet
                strExcelXml.Append("<Worksheet ss:Name=\"" + sanjay.ToString() + "\">");
                strExcelXml.Append("<Table border=\"2\">");
                strExcelXml.Append("<Tr>");
                strExcelXml.Append("<Td>SANJAY</Td>");
                strExcelXml.Append("</Tr>");
                strExcelXml.Append("</Table>");
                strExcelXml.Append("<WorksheetOptions> </WorksheetOptions>");
                strExcelXml.Append("</Worksheet>");
                // Then Table Tag
                for (int i = 1; i < dt.Rows.Count; i++)
                {
                    // Create First Worksheet tag
                    strExcelXml.Append("<Worksheet ss:Name=\"" + i.ToString() + "\">");
                    // Then Table Tag
                    strExcelXml.Append("<Table>");
                        // Row Tag
                        strExcelXml.Append("<Tr>");
                        for (int j = 0; j < dt.Rows.Count; j++)
                        {// Cell Tags
                            strExcelXml.Append("<Td>");
                            strExcelXml.Append("Sheet" + i.ToString() + "<Row>" + dt.Rows[i][0].ToString() + "<Col>" + dt.Rows[i][1].ToString());
                            strExcelXml.Append("</Col></Row>");
                            strExcelXml.Append("</Td>");
                        }
                        strExcelXml.Append("</Tr>");
                    strExcelXml.Append("</Table>");
                    strExcelXml.Append("</Worksheet>");
                }
                // Close the Workbook tag (in Excel header 
                // you can see the Workbook tag)
                strExcelXml.Append("</Workbook>\n");
            }
            using (System.IO.StreamWriter file = new System.IO.StreamWriter("D:\\DEPOSIT_ACC_Final .XLS"))
            {
                file.WriteLine(strExcelXml.ToString());
            }
            System.Diagnostics.Process.Start("D:\\DEPOSIT_ACC_Final .XLS");
        }
    }


What I have tried:

I have tried the data export in `Excel` file in `Multiple Excel sheets` but `excel sheet` create success but data not printing in table format any body help me
Posted
Updated 9-Mar-20 0:17am
Comments
Maciej Los 9-Mar-20 5:34am    
What you're trying to do is to create html file rather than Excel file...
Member 14765915 9-Mar-20 5:59am    
I am tried the string Builder using then stream writer using only one file create in my project
Richard MacCutchan 9-Mar-20 10:07am    
Your code does not produce a valid Excel file. Where did you find the details for creating this XML format?

1 solution

I'd suggest to use EPPlus library[^].
You can download package from NuGet: NuGet Gallery | EPPlus 5.0.3[^]
Take a good care about license, because:
Quote:
From version 5 EPPlus changes the licence model using a dual license, Polyform Non Commercial / Commercial license.
 
Share this answer
 

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