We help IT Professionals succeed at work.
Get Started

Export to Excel clientside

HugoHiasl
HugoHiasl asked
on
890 Views
Last Modified: 2008-01-09
Hi Everybody,

I need to export the content of a ASPxGrid to Excel on the clientside. I can imagine actually 3 possible solutions to do it. I implemented 2 of them and got stuck in trouble.

First would be using ActiveX Objects to create excel workbook and export the data. But there I stuck in setting up the right datatype for the columns. Especially I have Date format in Javascript and if I set this to the Cell.Value property it writes a UTC string to the excel sheet instead of using "Short Date" or something similar.

Second one (my favourite) is creating an XML like:
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 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">
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
  </Style>
  <Style ss:ID="s21">
   <Font ss:Bold="1"/>
  </Style>
  <Style ss:ID="s23">
   <NumberFormat ss:Format="Short Date"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Recruit Candidates">
  <Table ss:ExpandedColumnCount="14" ss:ExpandedRowCount="34" x:FullColumns="1"
   x:FullRows="1">
   <Column ss:Width="41.25"/>
   <Column ss:Width="81.75"/>
   <Column ss:Width="32.25"/>
   <Column ss:Width="114.75"/>
   <Column ss:Width="84"/>
   <Column ss:Width="99.75"/>
   <Column ss:Width="63"/>
   <Column ss:Width="72.75"/>
   <Column ss:Width="120.75"/>
   <Column ss:Width="104.25"/>
   <Column ss:Width="51.75" ss:Span="1"/>
   <Column ss:Index="13" ss:Width="32.25"/>
   <Column ss:Width="51.75"/>
   <Row ss:StyleID="s21">
    <Cell><Data ss:Type="String">Status</Data></Cell>
    <Cell><Data ss:Type="String">Submitted</Data></Cell>
    <Cell><Data ss:Type="String">Docs</Data></Cell>
    <Cell><Data ss:Type="String">Last Name</Data></Cell>
    <Cell><Data ss:Type="String">First Name</Data></Cell>
    <Cell><Data ss:Type="String">School</Data></Cell>
    <Cell><Data ss:Type="String">Degree</Data></Cell>
    <Cell><Data ss:Type="String">School End</Data></Cell>
    <Cell><Data ss:Type="String">Preference</Data></Cell>
    <Cell><Data ss:Type="String">Pool Answer</Data></Cell>
    <Cell><Data ss:Type="String">Forward</Data></Cell>
    <Cell><Data ss:Type="String">Similar</Data></Cell>
    <Cell><Data ss:Type="String">Pool</Data></Cell>
    <Cell><Data ss:Type="String">Upload</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="Number">6</Data></Cell>
    <Cell ss:StyleID="s23"><Data ss:Type="DateTime">2003-04-28T18:40:34.000</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
    <Cell><Data ss:Type="String">Mustermann</Data></Cell>
    <Cell><Data ss:Type="String">Mike</Data></Cell>
    <Cell><Data ss:Type="String">University of XY-Town</Data></Cell>
    <Cell><Data ss:Type="String">Master</Data></Cell>
    <Cell ss:StyleID="s23"><Data ss:Type="DateTime">1995-11-01T00:00:00.000</Data></Cell>
    <Cell><Data ss:Type="String">1.MUC</Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
    <Cell><Data ss:Type="String"></Data></Cell>
    <Cell><Data ss:Type="String">no</Data></Cell>
    <Cell><Data ss:Type="String">no</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>


This works fine. Here I got the problem, that I need to send send the clientside generated string back to the server to let the server make a submit to the client with a content-type "application/vnd.msexcel" set.

An approach like
var test = window.open();
test.document.open('application.vnd-msexcel');
test.document.write(xmlstring);
test.document.close();

did not work. The document seems always to be set to contenttype 'text/html';

If this would be possible, I would love this browser-independent solution even if it needs to work only on IE for me.


The third approach (not testet yet because I found to less documentation):
The Exel2003 offers a methos XmlImportXml(data,xmlMap) to import data as XML. But I did not find some information about the expected format (I think the same as my xmlString in solution 2) and nothing about the XmlMap and how to set this piece up manually by the grid contents.

Can anyone please assist me in getting this functionality to work? I'd prefer version 2 without the need to send the whole data back to the server to resubmit it the same client.

Thanks a lot in Advance.

Oliver
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE