|
i have a similar problem here..any help plzz.
|
|
|
|
|
Hi,
I want to create one common Function to Export the Data Grid and DataSet values in to an Excel file....
Is anybody having Solution for the above....
reply as soon as possible.....
**********
THX
**********
|
|
|
|
|
dear sir
i want to check existence of background image in excel sheet using vb.net code .My email id is prabodh_bansal1@yahoo.com
Plz help me solve it
Prabodh Bansal
prabodh
|
|
|
|
|
The Export_with_XSLT_Web takes an error on the response.End();
{Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack.}
base {System.SystemException}: {Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack.}
ExceptionState: Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack.
//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)
{
throw Ex;
}
}
|
|
|
|
|
the same error occurs ...
|
|
|
|
|
|
Is it possible to create the csv file without the headers/column names using this dll for Webforms?
|
|
|
|
|
It works well for me, even using .NET 2.0
thanks
G
|
|
|
|
|
Hi,
When i export to excel(in Win), it has wrong encode. I want it is Unicode instead of UTF - 8.
What can i do?
Thai Anh Duc
Software developer
Catglobe Vietnam
|
|
|
|
|
It's easy. Change the following in Export_With_XSLT:
XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
becomes
XmlTextWriter writer = new XmlTextWriter(stream, Encoding.Default);<br />
Also,
StreamWriter strwriter = new StreamWriter(FileName);
becomes
StreamWriter strwriter = new StreamWriter(path, false, Encoding.Default);
i.e. You need to supply the correct encoding (Default) in TWO places.
|
|
|
|
|
Hi everybody, I have a trouble, because when I use the class for export data to excel the function throw the exeception XPathException: has an invalid token.] and I dont know Why. Please I hope your help thanks in advance
|
|
|
|
|
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.
|
|
|
|
|