Export to Excel

leppie

public FileContentResult ExportToExcel(object datasource)
{
var grid = new GridView();
grid.DataSource = datasource;
grid.DataBind();

  var sw = new StringWriter();
  var htw = new HtmlTextWriter(sw);

  grid.RenderControl(htw);

  byte\[\] excelFileBytesContent = this.Response.ContentEncoding.GetBytes(sw.ToString());
  var excelFileContentResult = new FileContentResult(excelFileBytesContent, "application/vnd.ms-excel");

  return excelFileContentResult;

}

It kinda works, but WTF??? :doh:

IronScheme
((λ (x) `(,x ',x)) '(λ (x) `(,x ',x)))

GibbleCH

Excel can load Html tables no problem. There are some CSS issues that arise from it. But it really does work quite well. And while it doesn't appear that in this case you're going to have any styles being applied, if you did apply formatting to the GridView, they would be reflected in the excel document.

leppie

GibbleCH wrote:

Excel can load Html tables no problem.

I agree, but for a business requirement? :doh:

IronScheme
((λ (x) `(,x ',x)) '(λ (x) `(,x ',x)))

GibbleCH

I'm actually using a similar technique in a recent app we have written. However, the HTML passed to excel comes from the browser. This allows us to take the data, render HTML, the user then has the ability to hide/show columns, sort, filter, etc, then export the result to excel. If I don't pass the HTML to excel (well, a per-processor that cleans it up), then I have to look at it, determine what they are currently showing, query the db all over, then try to create an excel document that mimics the displayed data WITH all the current formatting, which is a duplication of effort since all that work has already been done in rendering the HTML. It also allows us to just modify the rendering of the HTML, and the export to excel is almost always working automatically without also having to modify the code that generates the excel document.

leppie

It is all good an well till you get to text that looks like numeric data to excel. Eg: a telephone number with a leading 0. You end up with a number without the leading zero. Or a long number (say ID or SS nr), displays in exponential form.

IronScheme
((λ (x) `(,x ',x)) '(λ (x) `(,x ',x)))

Sundance Kid

Yea.. had the problem with the 0 in the excel part too. But we used it too, because the calc method had a lot of logic to output HTML, so we reused it for the excel outtput. Worked like a bomb (but the 0 was problematic)

BobJanova

That's actually relatively neat. I can't think of a more concise way of getting data into an Excel readable format without using interop or a library like Aspose which costs money (and sticking it on the clipboard and reading it back doesn't count, that has a big nasty side-effect).

leppie

Not really. Use a free one like A Very Easy to Use Excel XML Import-Export Library[^] And have happiness with text that look like numbers :)

IronScheme
((λ (x) `(,x ',x)) '(λ (x) `(,x ',x)))

BobJanova

Well, using a whole library just for that is kind of overkill, still. That's a really useful looking library, though.

GibbleCH

I generate my cells with classnames, then run the data through a filter, and setup proper mso-number-format styles on the cells prior to sending it to excel. It works well

Sundance Kid

Thanks for the tip. Yea.. that project was done in record time about a year ago. But will keep it in mind!