Last post Dec 16, 2008 03:12 PM by Asifkhan Pathan
Jun 04, 2008 08:24 AM|jrp210|LINK
I have a requirement to export data from a web page to Excel. I would like to use a pre-formatted Excel template located within my web site to use when populating the data. This template will have multiple tabs which will have different types of information
in them. Can someone point me to a tutorial or help me get started with this?
Jun 04, 2008 11:09 AM|grahamlower|LINK
If you have Excel loaded on the web server, you can create an instance of the engine and manipulate documents from there. I have done something similar with Word in the past where I needed extract metadata/information that was not available when converted
to RTF, for example. Specific technical details will take some digging up, so if you really need it let me know and I will go hunting (Googl... err, MSN Live Search <grin> worked great in finding info). You will want to keep open Excel programming reference
(it's installed with Office if you do the full install, just look for the CHM that is named appropriately).
A site like
http://www.thescarms.com/dotnet/ExcelObject.aspx, if it doesn't help you outright, should get you enough of the nomenclature to at least get started in you... um... search engine adventures.
Jun 04, 2008 12:58 PM|jrp210|LINK
Not sure if Excel is loaded on the web server. What if it is not?
Jun 04, 2008 01:00 PM|jankins|LINK
I am working on a similar project and found the following tutorial useful.
http://www.aspnetpro.com/NewsletterArticle/2003/09/asp200309so_l/asp200309so_l.asp It sounds like the first section of the tutorial is what you are looking for.
Jun 04, 2008 05:02 PM|grahamlower|LINK
Not sure if Excel is loaded on the web server. What if it is not?
If Excel is not loaded on the server, it becomes a dramatically more complicated issue. There are really only a couple of options. First, you can go the XML route, but that will depend on you consumer's Office setup. Older versions of Excel do no play
nice with XML, but if that isn't an issue for you start by looking at this site: http://office.microsoft.com/en-us/excel/HA102063961033.aspx. The other alternative is to "hack" the Excel file format, which may or may not prove fruitful.
If you are going the "hack" route, you will need a good file comparison program (Beyond Compare from Scooter Software is my person preference). You will need to save the base, empty, file. Then you will need to save several copies with data. Using the
comparison program, identity where the data is being written and with what pattern (this is the hard part). Once that is done, you simple need to take you base file, tweaking the in same pattern (with your data, of course) to produce the output file. I
have added "markers" to the base file to make these easier (e.g. adding a @param at a point where I want to insert data, the doing a replace of @param with the data I wanted).
Having dealt with this problem with other file formats, Office and otherwise, I
really have to suggest you push to get Office on the server, if possible.
Jun 20, 2008 11:49 AM|jrp210|LINK
Excel is not loaded on the server and my requirement is to use the Interop assembly. I got this far and tested the ability to add a single value to a cell in Sheet1 and Sheet2. How can I transfer the data in my gridviews to these ranges?
book = app.Workbooks.Open(path,
Jun 20, 2008 01:13 PM|grahamlower|LINK
At this point you just iterate through each row in your gridview and then each cell in the row, grab it's Value, then find the correpsonding range in the spreadsheet and put it in. A couple of nexted For loops will fix you right up.
Jun 22, 2008 02:31 AM|grahamlower|LINK
I was thinking about this some more, and if you wanted to not use a prefomatted Excel template, you may want to go the ReportViewer route. You can create an RDLC that reportview can render into an Excell document (or PDF). If interested, you may want to
check out the follow thread which has some code geared towards a PDF, but could be adapted for an Excel doc...
Jun 23, 2008 11:33 AM|jrp210|LINK
Thanks, I'll have to check out the link.
I have the export working more or less but noticed that the items that are hyperlinks or images in the gridviews are not being exported. How would I get these items to be displayed in my Excel template?
Jun 23, 2008 12:31 PM|grahamlower|LINK
Instead of looking at the Text property of the cell, iterate through the Controls collection of the cell. Anything beyond 'plain vanilla' is done by adding a control and setting it's properties, and does not get represent in Text.
Jun 23, 2008 01:14 PM|jrp210|LINK
So instead of doing this:
I would alter this line arrMC[r, c] = gvMgmntConditions.Rows[r].Cells[c].Text;
to determine what type of control it is and perform additional processing? The only two differences are the hyperlink and template. The template field is being used to display an image instead of the value.
Jun 23, 2008 03:12 PM|grahamlower|LINK
If your structure is consistent, you could use the column number to determine the handling, knowing the type of control and where it lays in the Controls collection (probably index 0). This would be easier to implement, but less tolerant of change.
Jun 24, 2008 08:54 AM|jrp210|LINK
Thanks for all your replys. Can I ask if you have a code snippet you could supply to get me over the hump? I have seen examples using:
For Each row As GridViewRow In gvIndex.Rows
But I already have code in place for about 15 different gridviews that would need to be changed. I am assuming my current method for one of the gridviews would still work with a bit of modification but I am stuck as to the correct syntax for checking for
a HyperlinkField and Image (a flag image is displayed in the grid).
private void GetData(Workbook book)
Worksheet sheet = null;
Range range = null;
sheet = (Worksheet)book.Worksheets["Data"];
range = sheet.get_Range("A4", Missing.Value);
int iRows = gv.Rows.Count;
int iCols = gv.Columns.Count;
range = range.get_Resize(iRows, iCols);
string[,] arrNT = new string[iRows, iCols];
for (int r = 0; r < iRows; r++)
for (int c = 0; c < iCols; c++)
arrNT[r, c] = gv.Rows[r].Cells[c].Text;
The image is in the first column and the hyperlink is in the third column.
Jun 24, 2008 09:27 AM|jrp210|LINK
I think I got it except for the hyperlink. This code gets me what I need but the hyperlink text does not appear as a hyperlink in the Excel cell. Is there a way to make it a hyperlink so someone can click it?
for (int c = 0; c < iCols; c++)
if (c == 2)
HyperLink hyp = (HyperLink)gv.Rows[r].FindControl("hypNT");
arrNT[r, c] = "<a href=" + hyp.NavigateUrl + ">" + hyp.Text + "</a>";
else if (c==0)
Image img = (Image)gv.Rows[r].FindControl("imgNTFlag");
arrNT[r, c] = FormatImageUrlToText(img.ImageUrl); // changing the Image URL to a letter depending on the URL text.
arrNT[r, c] = gvNonTimelyFilers.Rows[r].Cells[c].Text;
Jun 24, 2008 10:59 AM|grahamlower|LINK
Where you are adding a hyper link, try the following:
sheet.HyperLinks.Add(arrNT[r,c], "http://www.asp.net", null, null, "ASP.Net");
Jun 24, 2008 11:23 AM|jrp210|LINK
Unfortunately that didn't work. I am populating the array and then setting the range. I may have to display the text and the link in two different columns if it comes down to it.
Jun 24, 2008 11:38 AM|jrp210|LINK
I believe this is what is needed:
arrNT[r, c] =
Unfortunately when it gets exported to Excel, the cell looks like this:
=HYPERLINK("http://www.sec.gov/Archives/edgar/data/826083/000095013407019947/0000950134-07-019947-index.htm", "NT 10-Q")
But, if I click on the Excel formula bar when in the Hyperlink cell and move to another cell, it converts it correctly!
Jun 24, 2008 01:07 PM|grahamlower|LINK
Instead of setting the Value property, try setting the Formula property of the Range.
Jun 24, 2008 03:43 PM|jrp210|LINK
Jun 26, 2008 09:28 AM|jrp210|LINK
Jun 26, 2008 11:05 AM|grahamlower|LINK
Glad to help. It keeps me sharp.
The interop is a wrapper for the Excel DLLs, etc., for .Net. If you don't have Excel installed on the server, that would be a problem. Alternatively (and I would hesitate to go this route, personally, because of the complexity), you could try and determine
what DLLs are being used by the interop and register those manually. If you want to attempt it, you will have to go to Sysinternals (taken over by Microsoft in the past year or so) and look at the tools they have.
Jun 26, 2008 12:04 PM|jrp210|LINK
Actually, I just verified that Office 2007 is installed on the server. Is there any tool that will help me diagnose why the export to Excel isn't working?
Jun 26, 2008 12:27 PM|grahamlower|LINK
On your page properties, turn Trace ="True". In your code a line like Trace.Write("some useful infromation here") or Trace.Write("title", "more information").
This can provide you information along the way, which may help narrow down the issue, almost as good as running with a debugger :)
Jun 26, 2008 04:45 PM|jrp210|LINK
wow, lots of stuff in the trace. anything particular i should be looking for?
Jun 26, 2008 05:19 PM|grahamlower|LINK
Most of the stuff in the trace you will want in this case is stuff you will need to add using the Trace.Write method.
Start by adding Trace.Write to you loops, and then one at the end that outputs the count. Also add Trace.Write Your objects... it will all .ToString for you, which should say the object's type, not nothing.
Make sure that it finds the files [Trace.Write("File 1 Exists", System.IO.File.Exists(filename1) )] , and so on and so forth.
Try reading something from the excel file (the first cell maybe) and outputting that in the Trace to verify that you are open the file okay.
Oct 17, 2008 09:48 AM|manohar_s|LINK
Hi i have a requirement i need to export to excel the collection data, it is working fine but when it is exported i would get one empty row after the header.
Please help me it is very urgent. please see the below code which i am using.
writeStream.Write(buffer, 0, bytesRead);
bytesRead = readStream.Read(buffer, 0, Length);
Dec 16, 2008 03:12 PM|Asifkhan Pathan|LINK
I am trying to export the DataTable to Excel file FOr which i am using following code onclick of the image button
and i am calling the function to convert the datatable into the string...DataSetToExcel.DataTable2ExcelString(dt).
The function works perfectly
but when i try to open the created .xls file i get excel waring pop up 'Unable to read file' and again another error popup with message
"Excel cannot file '~$GDExcel.xlam'because the file format or file extension is not valid"
then again one more pop up asking for do you want to open file....
If click OK then it opens the .xls file which contails proper data as i wanted.
When i tried to dig for the warning and errror pop up messages , i opend the .xls file in NOtepad and in notepad i saw my complete .aspx page HTML .
I am not getting why all the page html is coming into the excel file.
I tried to clear the response object just before the AppendHeader staments but its of no use.
Please tell me what is wrong in the code and how to avoid the warning and the pop up messages