|
Hi, You're probably getting this error because you have white space/special characters in your column headings. Here's a way to make it work AND maintain your whitespaces/special characters in your output! The programmer should take a hard look at this and update his code because this is a bug. Nice and simple code, though!
Find the ExportDetails function, type 1 (public void ExportDetails(DataTable DetailsTable, ExportFormat FormatType, string FileName)). Locate the for loop. Paste over it with the following code:
for (int i=0; i < dtExport.Columns.Count; i++)
{
sHeaders[i] = dtExport.Columns[i].ColumnName;
sFileds[i] = System.Web.HttpUtility.UrlEncode(dtExport.Columns[i].ColumnName);
}
The above code handles spaces and the limited numbers of characters I tested. You could use many different functions besides the UrlEncode method (most of which would be much better!). A replace(" ","-") or something similar takes care of spaces just as well. Come to think of it, maybe you could just use the index: i.ToString()? This is NOT a production fix, but will work fine if you control the headers! Also, I only call one version of ExportDetails, so if you use a different one, you'll have to modify that one differently.
I have NOT tested this much, just enough to make sure the error went away and output was generated. Break it, improve it, and post it!
|
|
|
|
|
1. set DataColumn.Caption to "xxx xxx" (with space) and left ColumnName as usual.
2, using following code:
for (int i=0; i < dtExport.Columns.Count; i++)
{
sHeaders[i] = dtExport.Columns[i].Caption ;
sFileds[i] = System.Web.HttpUtility.UrlEncode(dtExport.Columns[i].ColumnName);
}
these code works fine on my project
God bless me!
|
|
|
|
|
I am using a windows app and I kept getting NaN data in 4 of my 12 data columns.
I found this to work MUCH better.
for (int i=0; i < dtExport.Columns.Count; i++)
{
sHeaders[i] = dtExport.Columns[i].ColumnName;
sFields[i] = XmlConvert.EncodeName(dtExport.Columns[i].ColumnName);
}
|
|
|
|
|
Thanks sykiemikey,
That's the right solution.
|
|
|
|
|
Hi.
I have the error in this method. The error is "Subproceso Anulado" in the line Response.End.
This its a part of the code. Can anybody help me?
Thanks
System.IO.StringWriter sw = new System.IO.StringWriter(); xslTran.Transform(xmlDoc, null, sw, null);
//Writeout the Content
response.Write(sw.ToString());
sw.Close();
writer.Close();
stream.Close();
response.End();
}
catch(ThreadAbortException Ex)
{
string ErrMsg = Ex.Message;
}
catch(Exception Ex)
Federico Lazarte
Cordoba - Argentina
|
|
|
|
|
This part of the code has me confused?
// Specify the column list to export
int[] iColumns = {1,2,3,5,6};
I am new to C# what I need help with is this.
columnsNum = myDataTable.Count; //Say I have 10 Coulumns
How do i write a string to give iColumns = {1,2,3,5,6,7,8,9,10}; //I want a dynamic string I guess is what I am after..
int[] iColumns = columnsNum???? //This is what I don't know how to do.
I do appreciate all the answers I find on this site. All of you have made learning C# so easy and Thanks in advance for any help.
|
|
|
|
|
I redid the code using stringbuilder to get around some of the formatting problems, especially with dates. I also reduced the overloads and added a few more features. Use as you like:
Imports System.Data
Imports System.Web
Imports System.Web.SessionState
Imports System.IO
Imports System.Text
Imports System.Xml
Imports System.Xml.Xsl
Imports System.Threading
Namespace ExportData
Public Class Export
' ---------------------------------------------------------
'/ <summary>
'/ Exports datatable to CSV or Excel format.
'/
'/
'/ VB.Net Example to be used in WindowsForms
'/ -----------------------------------------
'/ Imports MyLib.ExportData
'/
'/ Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
'/
'/ Try
'/
'/ 'Declarations
'/ Dim dsUsers As DataSet = (CType(Session("dsUsers"), DataSet)).Copy()
'/ Dim oExport As New MyLib.ExportData.Export()
'/ Dim FileName As String = "C:\UserList.xls"
'/ Dim ColList() As Integer = New Integer() {2, 3, 4, 5, 6}
'/ oExport.ExportDetails(dsUsers.Tables(0), ColList, Export.ExportFormat.CSV, FileName)
'/
'/ Catch Ex As Exception
'/ lblError.Text = Ex.Message
'/ End Try
'/
'/ End Sub
'/
'/ </summary>
'
Public Enum ExportFormat
CSV = 1
Excel = 2
XML = 3
End Enum
'
Public Sub ExportDetails(ByVal DetailsTable As DataTable, ByVal FormatType As ExportFormat, ByVal FileName As String)
Dim colList() As Integer = Nothing
ExportDetails(DetailsTable, FormatType, FileName, colList, Nothing)
End Sub 'ExportDetails
Public Sub ExportDetails(ByVal DetailsTable As DataTable, ByVal FormatType As ExportFormat, ByVal FileName As String, ByVal ColumnList() As Integer)
ExportDetails(DetailsTable, FormatType, FileName, ColumnList, Nothing)
End Sub 'ExportDetails
Public Sub ExportDetails(ByVal DetailsTable As DataTable, ByVal FormatType As ExportFormat, ByVal FileName As String, ByVal ColumnList() As String)
ExportDetails(DetailsTable, FormatType, FileName, ColumnList, Nothing)
End Sub 'ExportDetails
Public Sub ExportDetails(ByVal DetailsTable As DataTable, ByVal FormatType As ExportFormat, ByVal FileName As String, ByVal ColumnList() As String, ByVal headers() As String)
'column list provided as character array - translate to integer
Dim colList(ColumnList.Length) As Integer
Dim colTo As DataColumn
Dim colName As String
Dim i As Integer = 0
For Each colTo In DetailsTable.Columns
For Each colName In ColumnList
If colTo.ColumnName.ToLower = colName.ToLower Then
colList(i) = colTo.Ordinal
Exit For
End If
Next
Next
ExportDetails(DetailsTable, FormatType, FileName, colList, headers)
End Sub 'ExportDetails
' Function : ExportDetails
' Arguments : DetailsTable, ColumnList, Headers, FormatType, FileName
' Purpose : To get the specified column headers in the datatable and
' exports in CSV / Excel format with specified columns and
' with specified headers
Public Sub ExportDetails(ByVal DetailsTable As DataTable, ByVal FormatType As ExportFormat, ByVal FileName As String, ByVal ColumnList() As Integer, ByVal Headers() As String)
Try
Dim dt As DataTable = DetailsTable
' Create Dataset if needed
If ColumnList IsNot Nothing Then
Dim dtExport As DataTable = DetailsTable.Copy()
dtExport.TableName = "Values"
dt = dtExport
Dim colNum As Integer
Dim foundCol As Boolean
For i As Integer = dt.Columns.Count - 1 To 0 Step -1
foundCol = False
For Each colNum In ColumnList
If colNum = i Then
foundCol = True
Exit For
End If
Next
If Not foundCol Then
dt.Columns.RemoveAt(i)
End If
Next
End If
Dim delimiter As Char
Select Case FormatType
Case ExportFormat.CSV
delimiter = ","c
Case ExportFormat.Excel
delimiter = vbTab
Case Else
delimiter = ","c
End Select
'open file for export
Dim writer As System.IO.StreamWriter = System.IO.File.CreateText(FileName)
Select Case FormatType
Case ExportFormat.CSV, ExportFormat.Excel
'write out header
If Headers IsNot Nothing AndAlso Headers.Length > 0 Then
writer.WriteLine(DelimitList(Headers, delimiter))
Else
writer.WriteLine(DelimitHeader(dt, delimiter))
End If
Dim dr As DataRow
For Each dr In dt.Rows
writer.WriteLine(DelimitDetail(dt, dr, delimiter))
Next
Case ExportFormat.XML
'write out as xml - pretty simple
dt.WriteXml(writer)
End Select
writer.Close()
Catch Ex As Exception
Throw Ex
End Try
End Sub 'ExportDetails
Private Function DelimitHeader(ByVal dt As DataTable, ByVal delimiter As Char) As String
Dim sbDelimited As New StringBuilder()
Dim firstCol As Boolean = True
Dim colTo As DataColumn
For Each colTo In dt.Columns
If firstCol Then
firstCol = False
Else
sbDelimited.Append(delimiter)
End If
sbDelimited.Append("""" & colTo.ColumnName & """")
Next
Return sbDelimited.ToString()
End Function
Private Function DelimitList(ByVal list() As String, ByVal delimiter As Char) As String
Dim sbDelimited As New StringBuilder()
Dim firstCol As Boolean = True
Dim listElement As String
For Each listElement In list
If firstCol Then
firstCol = False
Else
sbDelimited.Append(delimiter)
End If
sbDelimited.Append("""" & listElement.Replace("""", """""") & """")
Next
Return sbDelimited.ToString()
End Function
Private Function DelimitDetail(ByVal dt As DataTable, ByVal dr As DataRow, ByVal delimiter As Char) As String
Dim sbDelimited As New StringBuilder()
Dim colTo As DataColumn
Dim firstCol As Boolean = True
Dim dateValue As DateTime
Const dateCompare As DateTime = #1/1/1753#
For Each colTo In dt.Columns
If firstCol Then
firstCol = False
Else
sbDelimited.Append(delimiter)
End If
Select Case colTo.DataType.Name
' Handle special cases
Case "String"
sbDelimited.Append("""" & DirectCast(dr(colTo.Ordinal), String).Replace("""", """""") & """")
Case "DateTime"
dateValue = DirectCast(dr(colTo.Ordinal), DateTime)
If dateValue.TimeOfDay = dateCompare.TimeOfDay Then
'no time given - just use date
sbDelimited.Append(DirectCast(dr(colTo.Ordinal), DateTime).ToString("dd-MMM-yyyy"))
Else
sbDelimited.Append(DirectCast(dr(colTo.Ordinal), DateTime).ToString("dd-MMM-yyyy HH:mm:ss"))
End If
Case Else
sbDelimited.Append(dr(colTo.Ordinal).ToString)
End Select
Next
Return sbDelimited.ToString()
End Function
End Class 'Export
End Namespace
|
|
|
|
|
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?
|
|
|
|
|