|
Rama, great class! Nice work.
However, I ran into a problem with the web piece when trying to open the excel file without saving when the client is running Windows XP SP2.
I modified the Export_with_XSLT_Web method with the following changes:
private void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
{
try
{
// Appending Headers
// Modified MJC 7/20/2006
response.ClearHeaders();
response.Clear();
response.Buffer= true;
if(FormatType == ExportFormat.CSV)
{
response.ContentType = "text/csv";
response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
}
else
{
response.ContentType = "application/vnd.ms-excel";
response.AppendHeader("content-disposition", "attachment; filename=" + FileName);
}
// XSLT to use for transforming this dataset.
MemoryStream stream = new MemoryStream( );
XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
CreateStylesheet(writer, sHeaders, sFileds, FormatType);
writer.Flush( );
stream.Seek( 0, SeekOrigin.Begin);
XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
XslTransform xslTran = new XslTransform();
xslTran.Load(new XmlTextReader(stream), null, null);
System.IO.StringWriter sw = new System.IO.StringWriter();
xslTran.Transform(xmlDoc, null, sw, null);
// Modified MJC 7/20/2006
System.Text.Encoding encoding = new System.Text.ASCIIEncoding();
byte[] _byte = encoding.GetBytes(sw.ToString());
//Writeout the Content
//response.Write(sw.ToString());
// Modified MJC 7/20/2006
response.BinaryWrite(_byte);
sw.Close();
writer.Close();
stream.Close();
//response.End();
// Modified MJC 7/20/2006
response.Flush();
response.Close();
}
|
|
|
|
|
if there is a blank or a special char in one of my headers, i've got an invalid xpath expression which throw an exception
what can i do ?
thx
|
|
|
|
|
i'm having the same problems. has anyone got a solution for this?
|
|
|
|
|
I found that I could just iterate through all columns in my datatable and rename any problematic headings using the replace() string method. Here is an example:
private void btnExcel_Click(object sender, System.EventArgs e)<br />
{<br />
sfdExcel.FileName = "Consignments_" + DateTime.Now.ToString().Replace("/","").Replace(" ","").Replace(":","");<br />
if( this.sfdExcel.ShowDialog() == DialogResult.OK )<br />
{<br />
DataTable dt = ds.Tables["Consignment"].Copy();<br />
for (int i = 0;i<=dt.Columns.Count-1;i++)<br />
{<br />
dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.Replace(" ","").Replace("?","").Replace(":","");<br />
}<br />
<br />
RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Win");<br />
objExport.ExportDetails(dt,Export.ExportFormat.Excel, sfdExcel.FileName);<br />
<br />
}<br />
}
I'm sure there are other ways to achieve the solution, but this worked for me!
|
|
|
|
|
yes replacing the column name with some characters preferrably "_" will help
My requirement was column names had 2 words eg:First Name which was throwing invalid token exception.
Hence we replaced the column names spaces with underscorein 3 places
In method Export_with_XSLT_Web we added
System.IO.StringWriter sw = new System.IO.StringWriter();
xslTran.Transform(xmlDoc, null, sw, null);
///this is done just to replace _ with spaces
string final = sw.ToString().Replace("_"," ");
//Writeout the Content
response.Write(final);
In CreatestyleSheet method we addedd
writer.WriteAttributeString("select", "'" + sHeaders[i].Replace(" ","_") + "'");
writer.WriteAttributeString("select", sFileds[i].Replace(" ","_"));
this worked for use hopefully work for others
|
|
|
|
|
Isn't there a way to preserve names with spaces? I mean, why can't there be spaces in the columns' name?
|
|
|
|
|
wild_oscar, see the previous post from jungleboy, it tells you how to do it.
|
|
|
|
|
I read the post but am having difficulties applying it.
I have applied in
// xsl:value-of for headers
for (int i = 0; i < sHeaders.Length; i++)
this line writer.WriteAttributeString("select", "'" + sHeaders[i].Replace(" ","_") + "'");, replacing the select attribute string that was there,
and the other line in the // xsl:value-of for data fields
for (int i = 0; i < sFileds.Length; i++)
I've also put the other lines of code where you say.
The thing is that now the columns where changes happened have empty values on all rows, what step have I missed?
Thanks
|
|
|
|
|
In CreatestyleSheet method we addedd
writer.WriteAttributeString("select", "'" + sHeaders[i].Replace(" ","_") + "'");
writer.WriteAttributeString("select", sFileds[i].Replace(" ","_"));
Where was that added? I cannot seem to make it work
|
|
|
|
|
jamesvickers wrote: for (int i = 0;i<=dt.Columns.Count-1;i++)
{
dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.Replace(" ","").Replace("?","").Replace(":","");
}
I used your example as the basis for my own solution when working with an Access database that had space in it's column names except the only difference is that I used a foreach loop to iterate through the colums(not sure if that makes any difference).
foreach(DataColumn column in dsOrders.Tables[0].Columns)<br />
{<br />
column.ColumnName = column.ColumnName.Replace(" ", "_");<br />
}
|
|
|
|
|
Just another item...make sure header column names don't begin with a number. I had problem with header "24_Stuff". Had to add underscore to front... "_24_Stuff" and that worked.
HTH
Dave
|
|
|
|
|
Do This
String sColumnName;
for (int i = 0; i < dtExport.Columns.Count; i++)
{
sColumnName = dtExport.Columns[i].ColumnName;
sHeaders[i] = sColumnName;
sFileds[i] = XmlConvert.EncodeName(sColumnName);
}
That is convert the field name using XmlConvert.EncodeName();
|
|
|
|
|
hi all,
i have alittle a bit with vietnamese language .
I have been set
response.ContentEncoding = System.Text.Encoding.Default;
and then set
XmlTextWriter writer = new XmlTextWriter(stream, Encoding.Unicode);
but it still dont work correct.
pls help me.
Thanks.
Thang
|
|
|
|
|
I also have this problem. How to solve this?
Thanks alot.
|
|
|
|
|
Hi,
While using RKLib.dll I'm continously getting the error message as 'Thread was being aborted'. Following is the code I was using:
string strsql="Select .....";
SqlDataAdapter AdpIndex=new SqlDataAdapter(strsql,dbConn);
DataSet IndexDS = new DataSet();
AdpIndex.Fill(IndexDS,"IndexList");
DataTable dtIndex = IndexDS.Tables["IndexList"].Copy();
rcount= dtIndex.Rows.Count;
int[] icolumns = {0,1,2,3,4,5};
RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Web");
objExport.ExportDetails(dtIndex, icolumns, Export.ExportFormat.Excel, "RegionwiseIndexes.xls");
Can anybody please help me? Thanks.
Raja Raman S
Reuters India Pvt.Ltd.
Bangalore
-- modified at 10:17 Thursday 29th June, 2006
|
|
|
|
|
|
Hi, Removing the try catch solves the "Thread was being aborted" problem and creates the download file.
But how do you then catch genuine Exception messages like "There are no details to export"
Another interesting fact with the try catch and "Thread was being aborted" problem is that it goes into the exception; creates the download file; then terminates execution immediately.
Any help on catching genuine errors and ignoring the threading issue greatly appreciated.
|
|
|
|
|
hai,
This is the error i got while executing my program :
System.MissingMethodException: Method not found: Void System.Xml.Xsl.XslTransform.Load(System.Xml.XmlReader, System.Xml.XmlResolver, System.Security.Policy.Evidence).
Can anyone help me in rectify the problem.
The source code is given below:
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace WindowsApplication1
{
///
/// Summary description for Form1.
///
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.DataGrid dataGrid1;
///
/// Required designer variable.
///
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//new XslTransform).Load();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
///
/// Clean up any resources being used.
///
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InitializeComponent()
{
this.dataGrid1 = new System.Windows.Forms.DataGrid();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.SuspendLayout();
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(24, 40);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(256, 208);
this.dataGrid1.TabIndex = 0;
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(292, 273);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.dataGrid1});
this.Name = "Form1";
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.ResumeLayout(false);
}
#endregion
///
/// The main entry point for the application.
///
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void Form1_Load(object sender, System.EventArgs e)
{
DataSet dataset1=new DataSet();
SqlConnection con=new SqlConnection("Data Source=ie10-pramodn;Database='demo01';User Id=sa;PWD=''");
con.Open();
SqlCommand cmd=new SqlCommand();
cmd.CommandText="select * from student;";
cmd.Connection=con;
SqlDataAdapter adapt= new SqlDataAdapter();
adapt.SelectCommand=cmd;
adapt.Fill(dataset1,"student");
DataView view = dataset1.Tables["student"].DefaultView;
dataGrid1.DataSource = view;
fun(dataset1.Tables[0],"ramu");
con.Close();
}
public void fun(DataTable dt,string name)
{
try
{
int[] iColumns = {0,1,2,3,4,5,6,7,8,9};
RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Win");
objExport.ExportDetails(dt,RKLib.ExportData.Export.ExportFormat.Excel, "d:/Excel/"+name+".csv");
}
catch(Exception eee)
{
MessageBox.Show(eee.ToString());
Console.Write(eee);
}
}
}
}
|
|
|
|
|
This program help me to find a solution for my problem.
While converting from vb to vb.net 2003 recordset is saving directly to a flat file.No direct method in VB.net to copy a Datatable into a target file.
Thanks a lot and expecting more solutions.............
Sujo(Wipro)
|
|
|
|
|
First, Thanks for any assistance I receive.
I have a Dataset that I am using to populate a .CSV file and provide the ability for the end user to 'download' the resulting file. I am populating the dataset and populating a DataTable with it. I am then creating the Folder and File with the extension '.CSV'. I then populate the file row by row using a string. The file is in the correct location, format (.CSV) and properly populated at this point.
I then call a function that I have attempted to allow the user to download the file. I have attempted this in several different fashions using the Response object. All attempts are downloaded without the .CSV extension. I have pasted some of the code to preform this function below. I do trap an error that states "Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack". I have 'Googled' this error and modified my VS Options>Debugging>Just-in-Time to all the Debug in mixed mode (Managed and Native). The error occurs when I step through the code at the Response.End
I have attempted this with the HtmlTextWriter, StringWriter, StreamWriter and StringBuilder all with the same result.
Can someone assist?
string strFileNameCSV = "C:\\ExportReports\\Export_CSV_" + ReportForm1.strUserID + ".csv";
//FileInfo fiBPM_Export = new FileInfo(strFileNameCSV);
//Clear Headers
Response.Clear();
Response.ClearHeaders();
//set the conttent type of the file to be downloaded
Response.ContentType = "text/comma-separated-values";
// initialize the http content-disposition header to indicate a file attachment with the filename
Response.AppendHeader("Content-Disposition", "Attachment; Filename=\"" + strFileNameCSV + "\"");
////// transfer the file byte-by-byte to the response object
//FileInfo fileToDownload = new FileInfo(strFileNameCSV);
//Response.Flush();
//Response.WriteFile(fileToDownload.FullName);
Response.WriteFile(strFileNameCSV);
//Response.TransmitFile(strFileNameCSV);
//Response.Redirect(strFileNameCSV);
Response.End();
-- modified at 15:15 Wednesday 3rd May, 2006
|
|
|
|
|
Following Code works fine for me:
public void ExportDetails( DataTable DetailsTable, ExportFormat FormatType, string FileName )
{
try
{
if(DetailsTable.Rows.Count == 0)
{ throw new Exception("There are no details to export."); }
// Create Dataset
DataSet dsExport = new DataSet("Export");
//
DataTable dtExport = DetailsTable.Copy();
dtExport.TableName = "Values";
dsExport.Tables.Add(dtExport);
// Getting Field Names
string[] sHeaders = new string[dtExport.Columns.Count];
string[] sFileds = new string[dtExport.Columns.Count];
for ( int i=0; i < dtExport.Columns.Count; i++ )
{
sHeaders[i] = dtExport.Columns[i].ColumnName;
sFileds[i] = dtExport.Columns[i].ColumnName;
}
if(this.AppType == "Web")
{
// this.Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
this.Export_CSV( dsExport, sHeaders, sFileds, FileName );
}
else if(this.AppType == "Win")
{
// Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
}
}
catch( Exception oException )
{ throw oException; }
}
private void Export_CSV( DataSet dsExport, string[] sHeaders, string[] sFileds, string sFileName )
{
StringWriter oSWriter = new System.IO.StringWriter();
StringBuilder oSBuilder = new StringBuilder();
string sFieldSep = ";";
// Writung the Header ----------------
for( int i=0; i< sHeaders.Length; i++ )
{
oSBuilder.Append( sHeaders[i] + sFieldSep );
}
oSWriter.WriteLine( oSBuilder.ToString().Substring(0,oSBuilder.ToString().Length - 1) );
oSBuilder.Length = 0;
// Writing the Rows ------------------
DataTable oDTable = dsExport.Tables[0];
//
foreach(DataRow oDataRow in oDTable.Rows)
{
foreach(DataColumn oDataColumn in oDTable.Columns)
{
oSBuilder.Append( oDataRow[oDataColumn.ColumnName] + sFieldSep );
}
oSWriter.WriteLine( oSBuilder.ToString().Substring(0,oSBuilder.ToString().Length - 1) );
oSBuilder.Length = 0;
}
// Appending Headers -----------------
this.Response.Clear();
this.Response.Buffer= true;
this.Response.ContentType = "text/csv";
this.Response.AppendHeader("content-disposition", "attachment; filename=" + sFileName);
// "Write" to User -------------------
this.Response.Write( oSWriter.ToString() );
// -----------------------------------
oSWriter.Close();
// Terminate the Thread...
this.Response.End();
}
HTH - if not contact me via EMail
Jan Waiz
I-Com VBX Ltd.
Phone: 0049-40-56060630
EMail: j.waiz@icom-vbx.com
|
|
|
|
|
Why did you do it via XML/XLS?
A much more easier Way is to use it by writing per DataRow in a StringBuilder and Write that Line into the StringWriter. Less Lines and easier to understand
Regards
Jan Waiz
|
|
|
|
|
can you tell me how can i update data in datagrid and at the same time data is updated in EXCEL sheet related with datagrid this must be at button_click event.
THANKS A LOT
NEERAJ KUMAR
|
|
|
|
|
hello sir,
i want to read the data from excel file and save in an array and see the repeated numbers are there are not.please help in in this topic and end me the code in visual basic.net.
thanks in advance.
Regards,
Malli
Malli
|
|
|
|
|
hello sir,
i want to read the data from excel file and save in an array and see the repeated numbers are there are not.please help in in this topic and end me the code.
thanks in advance.
Regards,
Malli
Malli
|
|
|
|
|