Share via

Export to Excel with multiple sheets

Anonymous
2011-03-21T08:35:02+00:00

Hi,

I have a requirement wherein I want to export data to multiple excel sheets. I am able to export data to single sheet successfully but dont know how to do it for multiple sheets.

I am also displaying the report on my aspx page. I have used standard html to display the report. This html also has css applied on it. Then while exporting to excel I am using stringbuilder and htmltextwriter. The usefulness of using this is that I dont have to worry about applying css again, worrying about the no of rows, merging the rows etc. in the excel report which are the pain areas while using the Excel COM object model.

Following is the code I am using

HttpContext.Current.Response.Clear(); HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Dashboard.xls")); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.Write(strReportContent); where strReportContent contains strReportContent = "<html><head><style type='text/css'>" + stringbuilderSB.ToString() + "</style></head>" + stringWriterSW.ToString() + "</html>"; where stringbuilderSB contains the contents of the CSS file and stringwriterSW contains the actual html content filled in using the usual htmltextwriter.

Now the requirement is that I have multiple strReportContent based on different conditions and I want them to be exported to different worksheets of same workbook. Any ideas ?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-03-21T08:39:01+00:00

    OK,

    A small update from my side.

    I tried this.

    HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Dashboard.xls")); HttpContext.Current.Response.Write(@"<?xml version=""1.0""?>");

    HttpContext.Current.Response.Write(@"<Workbook xmlns:y=""application/vnd.ms-excel"" xmlns:x=""urn:schemas-microsoft-com:office:excel"" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"" xmlns:html=""http://www.w3.org/TR/REC-html40"">"); HttpContext.Current.Response.Write(@"<Worksheet ss:Name=""Sheet1"">"); HttpContext.Current.Response.Write(@"<Table ss:ExpandedColumnCount=""1"" ss:ExpandedRowCount=""1"" x:FullColumns=""1"" x:FullRows=""1"">"); HttpContext.Current.Response.Write(@"<Row>"); HttpContext.Current.Response.Write(@"<Cell><Data ss:Type=""Number"">1</Data></Cell>"); --- This is working, am getting '1' in a cell //HttpContext.Current.Response.Write(@"<Cell><Data ss:Type=""String"">" + strDigitalClientSummaryFinalContent + "</Data></Cell>"); --- This is not working, am getting complete html tags along with my values. HttpContext.Current.Response.Write(@"</Row>"); HttpContext.Current.Response.Write(@"</Table>"); HttpContext.Current.Response.Write(@"</Worksheet>"); HttpContext.Current.Response.Write(@"</Workbook>");

    So now the question how do I display html inside a cell of an excel sheet ? What is the datatype needed ?

    Was this answer helpful?

    0 comments