I need to programmatically create an Excel spreadsheet with 3 worksheets.
For Sheet1, I'm trying to Export a Gridview...with all its formatting...and doing it without using Http.Response, because that technique forces a file 'Save/Save As' requester box to pop up and that seems to halt all further code execution.
If I can succeed in directly writing my Gridview to an Excel Workbook, I should be able to continue my coding routine to then re-open the file and programmatically add Sheets 2 and 3.
This code works...kinda'...almost!
private void ExportGridView(GridView myGrid)
{
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
// Render grid view control.
myGrid.RenderControl(htw);
// Write the rendered content to a file.
string renderedGridView = sw.ToString();
File.WriteAllText("C:\\TimeSheets\\ExportedFile.xlsx", renderedGridView);
}
If I stop coding...and navigate to the TimeSheets Directory, I see the "ExportedFile.xlsx" displayed as "Microsoft Office Excel 2007 Workbook" and my Excel program has no problem opening and displaying my properly formatted Gridview.
But if I continue coding in C# and attempt to open that same file in my code-behind, I'm given a 'File contains corrupted data' error.
If, while using Excel to view the "ExportedFile.xlsx" file, I perform a "Save As", I see the filename is placed in quotes and the file type has defaulted to Save As Webpage htm/html. If I reset those values by removing the quotes from the filename and by setting the file type to ".xlsx"...then, THAT resulting file CAN be read in my code-behind without griping.
Can anyone offer me a solution?
Joe.
