Exporting Dataset to Excel

[dgorka]

So I've got some data that I want to export to an excel file. Yesterday I found a site (http://www.kjmsolutions.com/datasetarray.htm) that had me pass it into an array and then populate the excel cells with the array. It worked perfectly fine yesterday, but I came in this morning, tried it, and got the following error:

"Exception from HRESULT: 0x800A03EC"

Now, I've found the line that it doesn't like, and I'll make it bold where I post my code. One of my co-workers told me to try and bypass the array and just send the dataset itself to excel, which I've been trying to get working. Until I figure that out I thought that I'd try and post my code here and see if anyone here can figure out why it refuses to work. The only thing we can come up with is that there might be something wrong with the data(since it worked yesterday, and works with data from other tables). Thank you in advance. Code is as follows(Most of the comments are from his site, just to help me remember what does what):

'rows of dataset variable
Dim rows As Integer = ds.Tables(TableForExcel).Rows.Count
'columns of dataset variable
Dim columns As Integer = ds.Tables(TableForExcel).Columns.Count
'Used to count rows in loop
Dim r As Integer
'used to count columns in loop
Dim c As Integer

'array to hold data - the size is set by the dataset 'rows' number and 'columns' data
Dim DataArray(rows, columns) As Object

'first we deal with the column in the loop - assigning the element in the dataarray of
'c. Dont worry about 'r' at this point. We are only 'filling the 'c' of the array
For c = 0 To columns - 1
DataArray(r, c) = ds.Tables(TableForExcel).Columns.Item(c).ColumnName

'next we deal with the row (r of the array) entries for the 'c' column - for that column we just created
'in the array - that is the part which threw me because I was expecting it to write the whole row.
'Notice we are still using the same value for c. It has not changed from the first loop.
For r = 0 To rows - 1
DataArray(r, c) = ds.Tables(TableForExcel).Rows(r).Item(c)

'will loop on rows here until all of that columns(c) values are collected
Next

'conclude the activity for that column - moving on until all columns are collected
Next

'now we write the data to the sheet - use A2 as we need to leave room for the header row
oSheet.Range("A2").Resize(rows, columns).Value = DataArray

'we write the header row - we do this cell by cell to allow for special formatting I could
'have combined it but it was easier this way

'count the columns involved
Dim columns2 As Integer

'the movement of the loop - necessary due to rows in excel not being zero based.
Dim columns3 As Integer = 1

'count the columns in the dataset(columns2
For columns2 = 0 To ds.Tables(TableForExcel).Columns.Count - 1

'assigning the values of the dataset column(columns3) in row 1 of spreadsheet
'(notice not zero based). We do not have to do anything with the row value as
'we know this is the only row we are interested in.
oSheet.Cells(1, columns3).Value = ds.Tables(TableForExcel).Columns(columns2).ColumnName

'next excel column we are going to write to - incrementing it upward
columns3 = columns3 + 1

'moving on to next dataset column
Next

Again, thank you.

 

[dgorka]

So we figured out the problem. One of the fields that I was passing into the database was a stacktrace(this report going to excel was of errors), and in the stacktrace were many a vbCrLf. Well, all we had to do was do a replace on where we pass them into the db, and then get rid of the old records(luckily it was all test data). So now it works great.

For anyone who may have looked at this and tried to figure it out, thank you. Your effort was appreciated.

 

[dgorka]

Okay,

So I'm getting the same error again, Exception from HRESULT: 0x800A03EC, at the same line as in bold above. I checked the database and there are no VBCRLF's in it, just the || that I replaced them with. Anyone have any ideas? It's very appreciated.

Thanks

 

[cjard]

The following code is an implementation of an extended dataset that can write itself to Excel file. Excel does not need to be installed. Additionally, I cant see how this will fail, so you can try it as a robust replacement for your code:

Imports System.Data 
Imports System.IO 
Imports System.Xml 
Imports System.Xml.Xsl 
Namespace ExcelUtil 
 Public Class ExcelableDataset 
 Inherits DataSet 
   Public Function ToExcelXMLString() As String 
     Dim xt As XslCompiledTransform = New XslCompiledTransform 
     xt.Load(XmlReader.Create(System.IO.File.OpenRead("Excel.xsl"))) 
     Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder 
     xt.Transform(XmlReader.Create(New StringReader(Me.GetXml)), XmlWriter.Create(sb, xt.OutputSettings)) 
     Return sb.ToString 
   End Function 
   Public Sub WriteExcelXMLFile(ByVal path As String) 
     Dim xt As XslCompiledTransform = New XslCompiledTransform 
     xt.Load(XmlReader.Create(System.IO.File.OpenRead("Excel.xsl"))) 
     Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder 
     xt.Transform(XmlReader.Create(New StringReader(Me.GetXml)), XmlWriter.Create(System.IO.File.OpenWrite(path), xt.OutputSettings)) 
   End Sub 
 End Class 
End Namespace

This code has been generated by a c#-to-vb converter and visually sense-checked

 

[cjard]

You will additionally need the XSL

<xsl:stylesheet version="1.0"
    xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
 xmlns:msxsl="urn:schemas-microsoft-com:xslt"
 xmlns:user="urn:my-scripts"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" > 
 
<xsl:template match="/">
  <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">
    <xsl:apply-templates/>
  </Workbook>
</xsl:template>


<xsl:template match="/*">
  <Worksheet>
  <xsl:attribute name="ss:Name">
  <xsl:value-of select="local-name(/*/*)"/>
  </xsl:attribute>
    <Table x:FullColumns="1" x:FullRows="1">
      <Row>
        <xsl:for-each select="*[position() = 1]/*">
          <Cell><Data ss:Type="String">
          <xsl:value-of select="local-name()"/>
          </Data></Cell>
        </xsl:for-each>
      </Row>
      <xsl:apply-templates/>
    </Table>
  </Worksheet>
</xsl:template>


<xsl:template match="/*/*">
  <Row>
    <xsl:apply-templates/>
  </Row>
</xsl:template>


<xsl:template match="/*/*/*">
  <Cell><Data ss:Type="String">
    <xsl:value-of select="."/>
  </Data></Cell>
</xsl:template>


</xsl:stylesheet>

Credits for the creation of this XSL go to Peter Bromberg:
http://www.eggheadcafe.com/tutorial...9d-c9ca1b04fb7e/lightweight-aspnet-datas.aspx

Microsoft also have a more in-depth stylised example on their web page:
http://support.microsoft.com/kb/319180

 

[Edy]

thanx for your replies but where do i use this xsl.
thanx in advance

 

[cjard]

You dont, you just add it to your project and ensure it is output along with any other resource files your app might need, like pictures, icons, wavs..

 

[kate_cha]

Hi CJard, do you have any similar sort of code for .net1.1?

I have the following code, which used to work, but which now runs without errors, but also without showing any results - it just shows a blank screen, with the title on it.

Response.Clear()
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"

xmlTransform.TransformSource = "Excel.xslt"
xmlTransform.DocumentContent = [custom object returning valid xml string]
xmlTransform.DataBind()



Excel.xslt is below,and is in the same directory as the code that is calling it.

<xsl:stylesheet version="1.0"
xmlns="urn:schemas-microsoft-comffice:spreadsheet"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:user="urn:my-scripts"
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet" >

<xsl:template match="/">
<Workbook xmlns="urn:schemas-microsoft-comffice:spreadsheet"
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns:ss="urn:schemas-microsoft-comffice:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<xsl:apply-templates/>
</Workbook>
</xsl:template>

<xsl:template match="/*">
<Worksheet>
<xsl:attribute name="ss:Name">
<xsl:value-of select="local-name(/*/*)"/>
</xsl:attribute>
<Table x:FullColumns="1" x:FullRows="1">
<Row>
<xsl:for-each select="*[position() = 1]/*">
<Cell><Data ss:Type="String">
<xsl:value-of select="local-name()"/>
</Data></Cell>
</xsl:for-each>
</Row>
<xsl:apply-templates/>
</Table>
</Worksheet>
</xsl:template>

<xsl:template match="/*/*">
<Row>
<xsl:apply-templates/>
</Row>
</xsl:template>

<xsl:template match="/*/*/*">
<Cell><Data ss:Type="String">
<xsl:value-of select="."/>
</Data></Cell>
</xsl:template>

</xsl:stylesheet>


thanks!!

 

[cjard]

For a 1.1 version, see the original article by Peter:

http://www.eggheadcafe.com/articles/20050404.asp

The code there has warnings of being deprecated in 2.0 so I assume it is 1.1

 

[ballwah]

Hi cjard,

I'm trying to export a dataset to excel as well, but I found problem when using the below codes:

The following code is an implementation of an extended dataset that can write itself to Excel file. Excel does not need to be installed. Additionally, I cant see how this will fail, so you can try it as a robust replacement for your code:


Code:
Imports System.Data Imports System.IO Imports System.Xml Imports System.Xml.Xsl Namespace ExcelUtil Public Class ExcelableDataset Inherits DataSet Public Function ToExcelXMLString() As String Dim xt As XslCompiledTransform = New XslCompiledTransform xt.Load(XmlReader.Create(System.IO.File.OpenRead("Excel.xsl"))) Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder xt.Transform(XmlReader.Create(New StringReader(Me.GetXml)), XmlWriter.Create(sb, xt.OutputSettings)) Return sb.ToString End Function Public Sub WriteExcelXMLFile(ByVal path As String) Dim xt As XslCompiledTransform = New XslCompiledTransform xt.Load(XmlReader.Create(System.IO.File.OpenRead("Excel.xsl"))) Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder xt.Transform(XmlReader.Create(New StringReader(Me.GetXml)), XmlWriter.Create(System.IO.File.OpenWrite(path), xt.OutputSettings)) End Sub End Class End Namespace
This code has been generated by a c#-to-vb converter and visually sense-checked

The program highlights all the words begin with "Xsl"...by the way, where should I put this code? In the front of my code? or do I need to open a new form for that? or these code is only available for VB instead of VB.net?

Many thanks for your help!!!

Cheers,
Ballwah

 

[cjard]

Erm, you mean it puts a wiggly line under XslCOmpiledTransform? It means it doesnt know what that is. Either it is not referenced, or it is not imported. Use the "Error Correction Options" dialog to assist you with either adding a reference to System.Xml (i think) or importing System.Xml at the top of the class

 

[ballwah]

Hi cjard,

Yes, words are highlighted by wiggle lines. I've imported System.Data, System.IO, System.Xml and System.Xml.Xsl but those wiggle lines still exist. What should I do to fix this?

 

[cjard]

Why dont you point to the wiggle lines and see what the error actually is?

 

[ballwah]

This is what I got:

XslCompiledTransform = Type XslCompiledTransform is not defined
XmlReader.Create = Create is not the member of System.xml.XmlReader
XmlWriter.Create = Create is not the member of System.xml.XmlWriter

 

[cjard]

http://msdn2.microsoft.com/en-us/library/system.xml.xsl.xslcompiledtransform(vs.80).aspx
NET Framework Class Library
XslCompiledTransform Class

Note: This class is new in the .NET Framework version 2.0.
Transforms XML data using an XSLT style sheet. Namespace: System.Xml.Xsl
Assembly: System.Xml (in system.xml.dll)




Are you sure you have referenced and imported the necessary? The errror helper can do this for you (show the Error Correction Options dialog)


Creating Xml Readers:
http://msdn2.microsoft.com/en-us/library/9khb6435(VS.80).aspx

Creating Xml Writers:
http://msdn2.microsoft.com/en-us/library/kkz7cs0d(VS.80).aspx


I dont know what else to say; you have the same setup as me (VS 2005), and my code works - clean and rebuild your solution