|
How do I use this in ASP.Net webpage.
When i use
exportToExcel(dataSet1,"testr.txt");
Then i get a file write deny. (error writing to "c:\windows\system32\")
Does any1 know how to work around this?
Mat
|
|
|
|
|
Solution:
exportToExcel(dataSet11,Server.MapPath("~/GridData"+s+".xml"));
|
|
|
|
|
Assume you've fixed this by now. If not use exportToExcel(dataSet1, Server.MapPath("") + "\testr.txt";
This will save the file in your web application's directory. However, a better choice would be to make the file open as a download when a button is pressed. To do this:
1) Change the method so it returns a string rather than saving a file. The first 3 lines will then be:
<br />
public static string exportToExcel(DataSet source)<br />
{<br />
System.Text.StringBuilder sb = new System.Text.StringBuilder();<br />
Delete the line which reads excelDoc = new System.IO.StreamWriter(fileName);
Replace all instances of excelDoc.Write(...) with sb.Append(...)
Replace excelDoc.Close(); with return sb.ToString();
2) In the web page, when the output button is clicked, send the page output to Excel rather than browser. E.g.:
<br />
protected void btnExport_Click(object sender, EventArgs e)<br />
{<br />
Response.Clear();<br />
Response.Buffer = true;<br />
Response.Charset = "";<br />
Response.ContentType = "application/excel";<br />
Response.AddHeader("content-disposition", "attachment;filename=ExcelFile.xls");<br />
Response.Write(exportToExcel(ds));<br />
this.EnableViewState = false;<br />
Response.End();<br />
}<br />
where ds is the name of the dataset.
|
|
|
|
|
Thanks, I was looking for direct download. It helps me a lot.
|
|
|
|
|
Wow... Thanks! You made my day.
|
|
|
|
|
I want to use this method in a app I am doing.
I will change somethings main like: source to ds and filename to file.
This is mainly because I know when I look at ds I know what it is.
I guess it is just preference.
You will be noted in the comments of the method.
The is not for profit.
God Bless,
Jason
Programmer: A biological machine designed to convert caffeine into code. Developer: A person who develops working systems by writing and using software.
[ ^]
|
|
|
|
|
thank you for the recognition! i wouldn't have asked for any compensation anyways just glad that it helped.
there is an error in the code so be sure that you fix it in your implementation.
<br />
XMLstring = XMLstring.Replace("&","&");<br />
XMLstring = XMLstring.Replace(">",">");<br />
XMLstring = XMLstring.Replace("<","<");<br />
needs to be changed to
<br />
XMLstring = XMLstring.Replace("&","&");<br />
XMLstring = XMLstring.Replace(">",">");<br />
XMLstring = XMLstring.Replace("<","<");<br />
|
|
|
|
|
Thanks for the heads up on that.
I changed and added this:
excelDoc.Write(startExcelXML);<br />
<br />
foreach (DataTable dt in ds.Tables)<br />
{ <br />
excelDoc.Write("<Worksheet ss:Name=\"" + dt.TableName + "\">");<br />
excelDoc.Write("<Table>");<br />
excelDoc.Write("<Row>");<br />
Either I missed it or there was not a directive to cycle through all of the sheets.
That is why is used foreach (DataTable dt...)
Thanks again.
-- modified at 15:16 Thursday 29th March, 2007
God Bless,
Jason
Programmer: A biological machine designed to convert caffeine into code. Developer: A person who develops working systems by writing and using software.
[ ^]
|
|
|
|
|
Great piece of code. Had to do some tweaking to get it to work in my workspace but nevertheless a lifesaver.
|
|
|
|
|
Hi thanks for posting such gr8 code....
from this I am able to generate report in Excel...
but now my client some style in the report... he want some colored cell and colored headings inside the excel sheet so how can fill color to cell and data.....
Thanks in advance,
Veer
cheers
|
|
|
|
|
the best way to approach this is to open excel and set the style to one of the cells in the document.
next, save the document as xml and open it using a text editor.
you should be able to see some style attribute applied to that cell. just mimic that style attribute in your own code.
cheers
|
|
|
|
|
Hi,
Does anybody know what versions of Excel support opening this xml styled xls?
Thanks,
Mark
_______________
0x2B || !0x2B
|
|
|
|
|
|
best way to see what you can do in excel is:
- open excel and create a cell exactly how you would want it
- save the excel document as an xml document
- open the file in notepad or some texteditor
- look for the style information for the cell you want
- you may be able to use DOM to dynamically load/append different styles to the same set of data, but i haven't done anything like that with this procedure
|
|
|
|
|
hi
the function written by u is excellent.
i just want how can add my own style to it or how can i give my own CSS to
this? i want to apply my CSS to this XML.
|
|
|
|
|
Excellent article. One quick thing I'm having a problem with though. The code runs fine and then I get a prompt to Open, Save, and Cancel. If I choose open and Excel is not currently running on my desktop I get the following error:
....{filename}[1].xls could not be found. Check the spelling of the file name, and verify that the file location is correct.
If Excel is running on my desk, the excel file displays correctly.
Any help would be greatly appreciated.
|
|
|
|
|
First of all, nice code, you've posted.
I've already used it in an C#-Application which worked perfect.
Now i'm transferring the code for an MFC-App which isn't a real problem, but i'm stuck with one problem:
How do i have to Format a Date/Time-String to let Excel realize the milliseconds in it?
I've tried "YY-MM-DD hh:mm:ss.ms" (e.g. "2006-11-21 17:45:13.32") but that doesn't work. Everything before the Milliseconds is realized correct.
Would really appreciate some help!
Thank, regards,
mike
|
|
|
|
|
the easiest way to see discover different formats is to construct an excel document by hand exactly like you want it.
- so create an excel document and add a cell that has the exact style you want (for example a specific date format).
- once you have manually entered the data, save the file as an xml spreadsheet and open it up in some text editor.
- you'll be able to deduce the styles on the cell.
- i was looking through the different date formats and i didn't see one that included milliseconds. you might want to split your data up into two separate columns (one column for the date, and the other for the time).
good luck
|
|
|
|
|
Thanks for your answer, but the way you did it in your sample-code ("YY-MM-DD hh:mm:ss.ms", e.g. "2006-11-21 17:45:13.32") is absolute correct.
The reason why it didn't worked the first time I've tried it, is, that i used the wrong format in Excel itself for the date-/time-cells (the export-format, which is mentioned above was ok).
The correct format to show milliseconds in an Excel-Cell is the following (only tried with an German version of Excel): "TT.MM.YY hh:mm:ss,00" (I'd guess that the English version would need this one "DD.MM.YY hh:mm:ss.00" but i don't know for sure).
Thankds, regards,
mike
|
|
|
|
|
You need to add code to clear Parameters to reset it for each row.
private static void SetParametersInCommand(DataRow row, OleDbCommand command)<br />
{<br />
UnicodeEncoding en = new UnicodeEncoding();<br />
string argumentName = string.Empty;<br />
int index = 0;<br />
command.Parameters.Clear();<br />
foreach (DataColumn col in row.Table.Columns)<br />
{<br />
argumentName = "@Args" + index;<br />
command.Parameters.Add(new OleDbParameter(argumentName, OleDbType.VarBinary)).Value =<br />
en.GetBytes(row[col].ToString());<br />
index++;<br />
}<br />
}
|
|
|
|
|
Alright guys, a small error in the code. Note: it was correct until it became not just a user submission. Here is the error, the error occured because of html conversion.
Its in the System.String case statement. Please be sure to change it. Also, performance overall is improved if you combine all the excelDoc.Write() commands under each case statment...helps out alot.
<br> case "System.String":<br> string XMLstring = x[y].ToString();<br> XMLstring = XMLstring.Trim();<br> XMLstring = XMLstring.Replace("&","&");<br> XMLstring = XMLstring.Replace(">",">");<br> XMLstring = XMLstring.Replace("<","<");<br> excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" + <br> "<Data ss:Type=\"String\">");<br> excelDoc.Write(XMLstring);<br> excelDoc.Write("</Data></Cell>");<br> break;<br>
|
|
|
|
|
Hi all and greetings
I have a problem to export in excel with this great code.
I obtain a blank worksheet
I have a complex query where results come from different tables and view on database.
Database is MySql version 5.0
At the end of my query, i've try to do the follow:
[code]
MySqlDataAdapter adapter = new MySqlDataAdapter(myQuery, myConn);
DataSet ds = new DataSet();
adapter.Fille(ds, "Here I Dont Know what write, cause i dont have a single table");
exportToExcel(ds, @"c:\myFile.xls");
[/code]
My complex query, works perfectly to view data on a datagridview in this way:
[code]
MySqlDataAdapter adapter = new MySqlDataAdapter(myQuery, myConn);
DataTable dt = new DataTable();
adapter.Fille(dt);
datagridview.datasource = dt;
[/code]
I've already tryed to add the table to dataset:
[code]
MySqlDataAdapter adapter = new MySqlDataAdapter(myQuery, myConn);
DataTable dt = new DataTable();
DataSet ds = new DataSet();
adapter.Fille(dt);
ds.Tables.Add(dt);
exportToExcel(ds, @"c\:myFile.xls");
[/code]
This give me a blank file too.
Thx in advance.
Raoul.
|
|
|
|
|
i am unfamiliar with the MySqlDataAdapter class, but you might want to try creating a Temporary table at the begin of the query, insert into the Temporary table and then Select * from Temporary Table. but bottom line is that if you can bind that dataset returned to a datagrid then you should be able to export it. Start by trying to bind it to a datagrid. if you can do that then just step through the my export code and see where its messing up. you might also want to check the file in notepad and see if its writing anything into the file at all. also, remeber that excel is limited to 255 columns so if your query returns more than 255 columns it may create problems.
-let me know how things go
|
|
|
|
|
Many thx for reply
But... it still going bad
here is the code:
<br />
string select = "SELECT dip.Surname,dip.Name";<br />
<br />
for (int count = 1; count <= days; ++count)<br />
{<br />
select += ",MAX(CASE WHEN ISNULL(IF(monthlyview.day='" + count + "', DATE_FORMAT(monthlyview.total, '%H.%i'), '')) THEN monthlyview.nothing WHEN ISNULL(monthlyview.nothing) THEN IF(monthlyview.day = '" + count + "' ,DATE_FORMAT(monthlyview.total, '%H.%i'), '') END) AS '" + count + "'";<br />
}<br />
<br />
<br />
select += ",DATE_FORMAT(globalmonth.total, '%H.%i') AS 'GrandTotal' FROM dip, hbeta, monthlyview, globalmonth " +<br />
"WHERE monthlyview.ID_Dip = dip.ID_Dipendente " +<br />
"AND monthlyview.ID_Dip = globalmese.ID_Dip " +<br />
"AND monthlyview.ID_Month = '" + ((DateTimePicker)dateTimePickerHost.Control).Value.Month + "' " +<br />
"GROUP BY globalmese.ID_Dip";<br />
MySqlDataAdapter mda = new MySqlDataAdapter(select, cn);<br />
DataSet ds = new DataSet();<br />
DataTable dt = new DataTable();<br />
mda.Fill(dt);<br />
ds.Tables.Add(dt);<br />
dataGridView1.DataSource = ds;<br />
dataGridView1.DataMember = dt.ToString();<br />
exportToExcel(ds, "c:\\test.xls");<br />
This code works perfectly in program; he give me expected results on datagridview.
About number of columns, number cant be more of 32.
Im still having a blank excel sheet.
Thank you so much
Another little edit:
If i open the file with notepad,as your suggets, i could read data in it
-- modified at 7:19 Thursday 21st September, 2006
|
|
|
|
|
Hi! Cool code! The code has helped me a lot, but I still Have one problem left. I'am exporting my excelfile to a system wher the columns in the Excelfile should be <col1>, <col2>, the Converter does not like the > sign, it causes an error when the file is opened, does anyony know how to handle this?
Thanks /Ola
|
|
|
|