Formating spreadsheet on the fly

[20002008]

Hi,
I am trying to format a excel spreadsheet on the fly. The data is written to it using a streamwriter. I tried formatting the streamwriter but i can't do much with it. I need to add the header bold, with background color and grid lines to the cells.
Can anyone please help me with it?

My code:

#region "Excel Methods"

private void ExportGridViewToCSV(DataTable dt, string fileName)
{
string path = HttpContext.Current.Server.MapPath(fileName);
StreamWriter sw = new StreamWriter(path, false);

// Write the headers.
for (int i = 0; i < dt.Columns.Count; i++)
{
sw.Write("\"" + dt.Columns + "\"");

if (i < dt.Columns.Count - 1)

sw.Write(",");

}

sw.WriteLine();

// Write the content.
foreach (DataRow row in dt.Rows)
{
for (int j = 0; j < dt.Columns.Count; j++)
{

if (row[j].ToString().Trim() == "&nbsp;")
{
sw.Write("\"" + "" + "\"");
}
else
{
sw.Write("\"" + row[j].ToString().Trim() + "\"");
}

if (j < dt.Columns.Count - 1)
sw.Write(",");
}
sw.WriteLine();
}

sw.Close();

//var excelFile = new ExcelFile();
//var worksheet = excelFile.Worksheets.Add("Worksheet1");
//ws.Cells[0, 0].Value = DateTime.Now; // Type is System.DateTime

//// Some Font Styles
//ws.Cells[0, 0].Style.Font.Color = Color.Blue;
//ws.Cells[0, 0].Style.Font.Italic = true;
//ws.Cells[0, 0].Style.Font.Name = "Comic Sans MS";
//ws.Cells[0, 0].Style.Font.Size = 150;

//// Setting border on cell range
//var mergedRange = ws.Cells.GetSubrangeAbsolute(0, 0, 10, 5);
//var cellStyle = new CellStyle();
//cellStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Thin);
//mergedRange.Style = cellStyle;




HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
HttpContext.Current.Response.ContentType = "text/csv";

HttpContext.Current.Response.WriteFile(path);
HttpContext.Current.Response.End();

}

#endregion
}
}

 

[Rhys666]

You're not exporting an Excel workbook but a simple CSV file according to the method, response content type etc.

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[20002008]

Thanks for your response.
Yes, I am exporting it to csv file but it automatically opens up in excel.

 

[ca8msm]

On your machine maybe, but that will not be the same for someone who doesn't have excel or chooses to have their system open csv files in other programs...

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks