The following C# code is used to write the data from database to excel in formatted way.

I have used HTML Tags for the formatting.

We need to add the necessary HTML tags. The tags used here are Table, TR, Font and TH.

It can be customized according to the needs.



public void WriteDatabaseToExcel(string sFileName)

{

StreamWriter sw;
SqlDataReader dr;

SqlConnection conn;


try

{


//Sql Connection Estblishment

string ConnString = "Initial Catalog=dataPool;Data Source=localhost;Integrated Security=SSPI;Persist Security Info=False";

conn = new Sqlconnection(connString);

SqlCommand cmd = new SqlCommand("Select [First Name], [Last Name],Dob From Table1", conn);


//Open the Sql connection
Conn.Open();


//Read the data to the DataReader

SqlDataReader dr = new SqlDataReader();

dr = cmd.ExecuteReader();


//prepare Formatted HTML String to write to the Excel


StringBuilder sb = new StringBuilder();


//Making HTML

sb.Append("< equiv="\" content="\" charset="UTF-8\">);


//Draw the table

sb.Append("< class="\" border="1" id="\">");
sb.Append("<>< colspan="4">< size="5"> < /Font >< /TD ><>");
sb.Append("<>");


//Write the Header Column Details

for (int i = 0; i <>" + dr.GetName(i) + "< /TH >");

}

sb.Append("< /TR >");


//Write the content for each cell

while (dr.Read())

{

sb.Append(" <>");

for (int i = 0; i <>" + dr.GetValue(i).ToString() + "< /TD >");

}

sb.Append("< /TR >"); //New line

}

dr.Close();

sb.Append("< /TABLE >");



//WRITING the formatted string to the Excel File

sw = new StreamWriter(sFile);

sw.Write(sb.ToString());
sw.Close();



}
}

catch (Exception ex)
{
MessageBox.Show(ex.Message);


}

finally
{


//Clean up of resources

if !(dr = Nul)
dr.Close();

if (!(conn = Null) && (conn.State == ConnectionState.Open))
conn.close();

}

//Opening the Excel File


Process.Start(sFileName);


}
}


USAGE


WriteDatabaseToExcel ("C:\TestExcel.xls");


After writing the content, it will open the excel file.