|
Here is the NUnit test for it as well.
Option Compare Binary
Option Explicit On
Option Strict On
Imports System
Imports NUnit.Framework
'************************************************************************************
'
' Copyright © 2002 James W. Newkirk, Michael C. Two, Alexei A. Vorontsov
' Copyright © 2000-2002 Philip A. Craig
'
' This software is provided 'as-is', without any express or implied warranty. In no
' event will the authors be held liable for any damages arising from the use of this
' software.
'
' Permission is granted to anyone to use this software for any purpose, including
' commercial applications, and to alter it and redistribute it freely, subject to the
' following restrictions:
'
' 1. The origin of this software must not be misrepresented you must not claim that
' you wrote the original software. If you use this software in a product, an
' acknowledgment (see the following) in the product documentation is required.
'
' Portions Copyright © 2002 James W. Newkirk, Michael C. Two, Alexei A. Vorontsov
' or Copyright © 2000-2002 Philip A. Craig
'
' 2. Altered source versions must be plainly marked as such, and must not be
' misrepresented as being the original software.
'
' 3. This notice may not be removed or altered from any source distribution.
'
'***********************************************************************************/
Namespace SSI.Test.UtilTest
<TestFixture()> Public Class ExportDataTest
Public Sub New()
MyBase.New()
End Sub
'<TestFixtureSetUp()> Public Sub Init()
'End Sub
<Test()> Public Sub s100_ExportDataSetToCSV()
' simple dataset export
Dim testDS As New DataSet
testDS.Tables.Add("Test1")
testDS.Tables(0).Columns.Add("Col1", GetType(System.String))
testDS.Tables(0).Columns.Add("Col2", GetType(System.Int32))
testDS.Tables(0).Columns.Add("Col3", GetType(System.DateTime))
Dim testRow As DataRow = testDS.Tables(0).NewRow
testRow("Col1") = "Test String"
testRow("Col2") = 123456789
testRow("col3") = #1/1/2000#
testDS.Tables(0).Rows.Add(testRow)
Dim fileName As String = "C:\Test.csv"
If System.IO.File.Exists(fileName) Then
System.IO.File.Delete(fileName)
End If
Dim myExport As New SSI.Util.ExportData.Export
myExport.ExportDetails(testDS.Tables(0), Util.ExportData.Export.ExportFormat.CSV, fileName)
Dim reader As System.IO.StreamReader = System.IO.File.OpenText(fileName)
Dim line As String = reader.ReadLine
Assert.AreEqual("""Col1"",""Col2"",""Col3""", line, "CSV Header")
line = reader.ReadLine
Assert.AreEqual("""Test String"",123456789,01-Jan-2000", line, "CSV Detail")
reader.Close()
System.IO.File.Delete(fileName)
End Sub
<Test()> Public Sub s110_ExportDataSetToTab()
' simple dataset export
Dim testDS As New DataSet
testDS.Tables.Add("Test1")
testDS.Tables(0).Columns.Add("Col1", GetType(System.String))
testDS.Tables(0).Columns.Add("Col2", GetType(System.Int32))
testDS.Tables(0).Columns.Add("Col3", GetType(System.DateTime))
Dim testRow As DataRow = testDS.Tables(0).NewRow
testRow("Col1") = "Test String"
testRow("Col2") = 123456789
testRow("col3") = #1/1/2000#
testDS.Tables(0).Rows.Add(testRow)
Dim fileName As String = "C:\Test.xls"
If System.IO.File.Exists(fileName) Then
System.IO.File.Delete(fileName)
End If
Dim myExport As New SSI.Util.ExportData.Export
myExport.ExportDetails(testDS.Tables(0), Util.ExportData.Export.ExportFormat.Excel, fileName)
Dim reader As System.IO.StreamReader = System.IO.File.OpenText(fileName)
Dim line As String = reader.ReadLine
Assert.AreEqual("""Col1""" & vbTab & """Col2""" & vbTab & """Col3""", line, "Tab Header")
line = reader.ReadLine
Assert.AreEqual("""Test String""" & vbTab & "123456789" & vbTab & "01-Jan-2000", line, "Tab")
reader.Close()
System.IO.File.Delete(fileName)
End Sub
<Test()> Public Sub s120_ExportDataSetToXML()
' simple dataset export
Dim testDS As New DataSet
testDS.Tables.Add("Test1")
testDS.Tables(0).Columns.Add("Col1", GetType(System.String))
testDS.Tables(0).Columns.Add("Col2", GetType(System.Int32))
testDS.Tables(0).Columns.Add("Col3", GetType(System.DateTime))
Dim testRow As DataRow = testDS.Tables(0).NewRow
testRow("Col1") = "Test String"
testRow("Col2") = 123456789
testRow("col3") = #1/1/2000#
testDS.Tables(0).Rows.Add(testRow)
Dim fileName As String = "C:\Test.xml"
If System.IO.File.Exists(fileName) Then
System.IO.File.Delete(fileName)
End If
Dim myExport As New SSI.Util.ExportData.Export
myExport.ExportDetails(testDS.Tables(0), Util.ExportData.Export.ExportFormat.XML, fileName)
Dim reader As System.IO.StreamReader = System.IO.File.OpenText(fileName)
Dim line As String = reader.ReadLine
Assert.AreEqual("<NewDataSet>", line, "Tab Header")
line = reader.ReadLine
Assert.AreEqual(" <Test1>", line, "Tab")
line = reader.ReadLine
Assert.AreEqual(" <Col1>Test String</Col1>", line, "Tab")
line = reader.ReadLine
Assert.AreEqual(" <Col2>123456789</Col2>", line, "Tab")
line = reader.ReadLine
Assert.AreEqual(" <Col3>2000-01-01T00:00:00-05:00</Col3>", line, "Tab")
line = reader.ReadLine
Assert.AreEqual(" </Test1>", line, "Tab")
line = reader.ReadLine
Assert.AreEqual("</NewDataSet>", line, "Tab Header")
reader.Close()
System.IO.File.Delete(fileName)
End Sub
<Test()> Public Sub s200_ExportDataSetToCSVColumnsList()
' simple dataset export
Dim testDS As New DataSet
testDS.Tables.Add("Test1")
testDS.Tables(0).Columns.Add("Col1", GetType(System.String))
testDS.Tables(0).Columns.Add("Col2", GetType(System.Int32))
testDS.Tables(0).Columns.Add("Col3", GetType(System.DateTime))
Dim testRow As DataRow = testDS.Tables(0).NewRow
testRow("Col1") = "Test String"
testRow("Col2") = 123456789
testRow("col3") = #1/1/2000#
testDS.Tables(0).Rows.Add(testRow)
Dim colList As Integer() = {1, 2}
Dim myExport As New SSI.Util.ExportData.Export
Dim fileName As String = "C:\Test.csv"
If System.IO.File.Exists(fileName) Then
System.IO.File.Delete(fileName)
End If
myExport.ExportDetails(testDS.Tables(0), Util.ExportData.Export.ExportFormat.CSV, fileName, colList)
Dim reader As System.IO.StreamReader = System.IO.File.OpenText(fileName)
Dim line As String = reader.ReadLine
Assert.AreEqual("""Col2"",""Col3""", line, "CSV Header")
line = reader.ReadLine
Assert.AreEqual("123456789,01-Jan-2000", line, "CSV Detail")
reader.Close()
System.IO.File.Delete(fileName)
End Sub
<Test()> Public Sub s210_ExportDataSetToCSVColumnsList()
' simple dataset export
Dim testDS As New DataSet
testDS.Tables.Add("Test1")
testDS.Tables(0).Columns.Add("Col1", GetType(System.String))
testDS.Tables(0).Columns.Add("Col2", GetType(System.Int32))
testDS.Tables(0).Columns.Add("Col3", GetType(System.DateTime))
Dim testRow As DataRow = testDS.Tables(0).NewRow
testRow("Col1") = "Test String"
testRow("Col2") = 123456789
testRow("col3") = #1/1/2000#
testDS.Tables(0).Rows.Add(testRow)
Dim colList As String() = {"col1", "col3"}
Dim fileName As String = "C:\Test.csv"
If System.IO.File.Exists(fileName) Then
System.IO.File.Delete(fileName)
End If
Dim myExport As New SSI.Util.ExportData.Export
myExport.ExportDetails(testDS.Tables(0), Util.ExportData.Export.ExportFormat.CSV, fileName, colList)
Dim reader As System.IO.StreamReader = System.IO.File.OpenText(fileName)
Dim line As String = reader.ReadLine
Assert.AreEqual("""Col1"",""Col3""", line, "CSV Header")
line = reader.ReadLine
Assert.AreEqual("""Test String"",01-Jan-2000", line, "CSV Detail")
reader.Close()
System.IO.File.Delete(fileName)
End Sub
<Test()> Public Sub s220_ExportDataSetToCSVColumnsListHeaders()
' simple dataset export
Dim testDS As New DataSet
testDS.Tables.Add("Test1")
testDS.Tables(0).Columns.Add("Col1", GetType(System.String))
testDS.Tables(0).Columns.Add("Col2", GetType(System.Int32))
testDS.Tables(0).Columns.Add("Col3", GetType(System.DateTime))
Dim testRow As DataRow = testDS.Tables(0).NewRow
testRow("Col1") = "Test String"
testRow("Col2") = 123456789
testRow("col3") = #1/1/2000#
testDS.Tables(0).Rows.Add(testRow)
Dim colList As String() = {"col1", "col3"}
Dim headers As String() = {"Header1", "Header 2"}
Dim fileName As String = "C:\Test.csv"
If System.IO.File.Exists(fileName) Then
System.IO.File.Delete(fileName)
End If
Dim myExport As New SSI.Util.ExportData.Export
myExport.ExportDetails(testDS.Tables(0), Util.ExportData.Export.ExportFormat.CSV, fileName, colList, headers)
Dim reader As System.IO.StreamReader = System.IO.File.OpenText(fileName)
Dim line As String = reader.ReadLine
Assert.AreEqual("""Header1"",""Header 2""", line, "CSV Header")
line = reader.ReadLine
Assert.AreEqual("""Test String"",01-Jan-2000", line, "CSV Detail")
reader.Close()
System.IO.File.Delete(fileName)
End Sub
End Class
End Namespace
|
|
|
|
|
Hallo,
can You share the whole VB project for us, c# programmers, cause I'm having some difficulties...
Mike
|
|
|
|
|
OK, I figured it out...
Mike
|
|
|
|
|
This doesnt appear to work in the web environment, am I missing something?
|
|
|
|
|
how do I modified the code so it will export proper format for date and time?
|
|
|
|
|
see my redo just posted (titled Redo Using StringBuilder)
|
|
|
|
|
Hey, I also had the problem with the language that it didn't work, didn't export right, like alot in here did.
Well, I found the solution and I want to share with all of you, and even the creator.
To make it work, in the ASP.NET page in which you use the class in, the CodePage should be your language number (without ResponseEncoding), that's why I would advice you to use this class in a different ASP.NET file.
e.g: <%@ Page Language="C#" CodePage="1255" %>
(This works for Hebrew).
It's that easy!
Good luck all!
NaNg.
|
|
|
|
|
It didn´t work for me. What do you mean "I would advice you to use this class in a different ASP.NET file" ? the class is in a separate file, export.cs.
Liglio
|
|
|
|
|
I mean that if you have a button that on his click event you would activate it - NO.
On the button click, open a new page which is dedicated to that, and only that.
That page will be closed automaticly when the download is done.
If you are having problems, maybe it's because of that... the codepage and the responseEncoding are getting mixed.
If you continue to have more troubles, msg again and I'll try to walk you through it.
NaNg.
|
|
|
|
|
can you specify the steps to change ?
Thanks in advance.
|
|
|
|
|
Instead of getting the data in the datatable, the generated CSV contains the actual html page.Do I have something wrong in my code ?
param[0] = new SqlParameter("@StartTime",SqlDbType.DateTime);
param[0].Value = Convert.ToDateTime(Request["Start"]);
param[1] = new SqlParameter("@EndTime",SqlDbType.DateTime);
param[1].Value = Convert.ToDateTime(Request["End"]);
dt = SqlHelper.ExecuteDataset(Global.DbConn,CommandType.StoredProcedure,"sp_GetData",param).Tables[0];
RKLib.ExportData.Export x = new RKLib.ExportData.Export("Web");
x.ExportDetails(dt, RKLib.ExportData.Export.ExportFormat.Excel, "calendarexport.xls");
|
|
|
|
|
I get this too! Any one had it before? Have a fix?
|
|
|
|
|
I need help
chinese big5 Encoding error
|
|
|
|
|
When I click on open on the popup, I get an error message...somthing like unable to find file in folder...c:/.../IE5.0...etc
But I am using Internet Explorer 6.0
How do I solve this problem?
By the way, great piece of code...helps me a lot...except for this open thing...
Kevin Sumputh
|
|
|
|
|
Excellent, just what we needed. It works perfectly and converts 5000 rows in seconds (we use it with a windows app.).
|
|
|
|
|
When i run this tool on Windows English version and Office Japanese version, no problem. But running on Windows Japanese version and Office Japanese version, it is incorrect about row and column.
|
|
|
|
|
I can write a windows csv generator with 70% less code that operates a 1000 times quicker.
At the least, try this:
http://www.dotnetspider.com/kb/Article1336.aspx[^]
Do not use this code. The developer obviously doesn't understand what XSL is for if he is doing this. Try running this against 90k records and tell me how it is doing 10 minutes into the run.
Stephen R
|
|
|
|
|
Doesn't Excel have a limit of 16384 rows in a sheet?
|
|
|
|
|
How can i format header
wefwfe
|
|
|
|
|
Hi.
When tring to build this in .NET I get the following warning: 'System.Xml.Xsl.XslTransform' is obsolete: 'This class has been deprecated. Please use System.Xml.Xsl.XslCompiledTransform instead. http://go.microsoft.com/fwlink/?linkid=14202'
Tring to use the provided class resolve in a compile error - I can't find any overload with the objects matching the one you created.
Do you know a way to resolve this?
|
|
|
|
|
Drop the 4th parameter on the xslTran.Transform(xmlDoc, null, sw) line and you'll be fine - worked for me.
|
|
|
|
|
I converted this to vb, but this is the replacement I am using:
Dim xslTran As XslCompiledTransform = New XslCompiledTransform
xslTran.Load(New XmlTextReader(stream), Nothing, Nothing)
Dim sw As System.IO.StringWriter = New System.IO.StringWriter
xslTran.Transform(xmlDoc, Nothing, sw)
|
|
|
|
|
My code is as follows:
<br />
string MyFileDate = DateTime.Now.ToString("yyyy-MM-dd_HH-mm-ss");<br />
string FileName = @"AquariumNet_StandardReportingQuery_" + MyFileDate + ".csv";<br />
try<br />
{<br />
DataTable dtExport = ((DataSet)Session["dsFinal"]).Tables["CustomerAnswers"].Copy();<br />
<br />
Export objExport = new Export("Web");<br />
objExport.ExportDetails(dtExport, Export.ExportFormat.CSV, FileName);<br />
}<br />
catch (Exception Ex)<br />
{<br />
lblError.Text = "ERROR :: " + Ex.Data + " :: " + Ex.Message;<br />
}<br />
I get a "System.Collections.ListDictionaryInternal - Object Reference not set to an instance of an object" error!!!
I've added an empty GridView and then bound to Session["dsFinal"] to check that it does indeed contain data!
Please help!
Aaran
|
|
|
|
|
Does anybody know how to create a MDB file?
|
|
|
|
|
Sorry for my bad English!
I'm export dataset to xls, but when i want to import this file i get an error "External table is not in the expected format"
help me please!
|
|
|
|
|