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("< meta http-equiv=\"Content-Type\" content=\"text/html;charset=UTF-8\" >);
//Draw the table
sb.Append("< TABLE class=\"text\" Border=1 ID=\"Table1\" >");
sb.Append("< TR>< TD ColSpan=4 >< Font Size=5 > < /Font >< /TD >< TR >");
sb.Append("< TR >");
//Write the Header Column Details
for (int i = 0; i < dr.FieldCount; i++)
{
sb.Append("< TH >" + dr.GetName(i) + "< /TH >");
}
sb.Append("< /TR >");
//Write the content for each cell
while (dr.Read())
{
sb.Append(" < TR >");
for (int i = 0; i < dr.FieldCount; i++)
{
sb.Append("< TD >" + 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.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|