0

The project I'm working on is generating excel files as below,

string contentType = "application/vnd.ms-excel; charset=utf-8";
string extension = "xls";
string fileName = "report-" + DateTime.Now.Ticks.ToString();
string contentDisposition = string.Format("attachment; filename={0}.{1}", fileName, extension);

WebOperationContext.Current.OutgoingResponse.ContentType = contentType;
WebOperationContext.Current.OutgoingResponse.Headers.Set("Content-Disposition", contentDisposition);

//Some business logic and fetching data from db. Fetched data is converted 
//to HTML format which I share an example of it separately. This converted HTML string is
//assigned to variable named "result" in the end of these logic process

byte[] preambles = serializer.PreferredEncoding.GetPreamble();
byte[] serializedResult = serializer.PreferredEncoding.GetBytes(result);
byte[] resultBuffer = new byte[preambles.Length + serializedResult.Length];

//serializer is a custom class which basically uses System.Text.UTF8Encoding

preambles.CopyTo(resultBuffer, 0);

serializedResult.CopyTo(resultBuffer, preambles.Length);

return new MemoryStream(resultBuffer);

Quite outdated but works fine generating Excel files with single sheet. Now, one of the Excel files generated in this way requested to have two sheets. Is there anyone out there still generating their .xls files in this way and know how to make them contains more than one sheet?

As I promised, below I'm also sharing that converted HTML string which I use to feed that byte array.

<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="tr" lang="tr">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
    <table>
        <thead>
            <tr><th>Name Surname</th><th>0-5 (Min)</th><th>6-60 (Min)</th><th>> 60 (Min)</th><th>0-5 (%)</th><th>6-60 (%)</th><th>> 60 (%)</th><th>Sum</th></tr>
        </thead>
        <tbody>
            <tr><td>Joe</td><td>1</td><td>0</td><td>0</td><td>100</td><td>0</td><td>0</td><td>1</td></tr><tr><td>John</td><td>0</td><td>0</td><td>1</td><td>0</td><td>0</td><td>100</td><td>1</td></tr>
        <tbody>
    </table>
</body>

Thanks in advance!

RaZzLe
  • 1,984
  • 3
  • 13
  • 24
  • 1
    So, it looks like you're actually creating an HTML file that Excel automatically can read as if it were a spreadsheet (XLS) but that's not the standard way of doing it. Which is also why this method will not be able to produce a multi-sheet XLS or XLSX document. I would recommend using the [EPPlus](https://github.com/EPPlusSoftware/EPPlus) package instead. It will work far better and won't have many of the limitations of your HTML method – Bron Davies Aug 15 '22 at 14:49
  • @BronDavies Are you familiar with EPPlus? I have developed a WCF service which downloads an excel file via memorystream with the help of EPPlus, but the file is full of weird encrypted characters like `PK�UU�H��3` If you have time, mind to check this full description: https://stackoverflow.com/questions/73399628/exported-excel-file-contains-unreadable-characters-through-net-wcf – RaZzLe Aug 18 '22 at 09:48
  • I posted some suggestions on the other question – Bron Davies Aug 18 '22 at 14:15

0 Answers0