Export to Excel

Question

User-1620310474 posted

Hello,

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?

Thanks,

Answer 1

User26357911 posted

 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.

 

Cheers. 

 

 

Answer 2

User-1620310474 posted

Not sure if Excel is loaded on the web server.  What if it is not?

Answer 3

User-1848774011 posted

Greetings,

 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.

Answer 4

User26357911 posted

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.
 

Answer 5

User-1620310474 posted

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?  

string path = Server.MapPath("ExcelFile.xls");
ApplicationClass app = new ApplicationClass();
Workbook book = null;
Worksheet sheet = null;
Range range = null;

book = app.Workbooks.Open(path, Missing.Value, false, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

sheet = (Worksheet)book.Worksheets["Sheet1"];
range = sheet.get_Range("A4", Missing.Value);
range.set_Value(Missing.Value, 10000);

sheet = (Worksheet)book.Worksheets["Sheet2"];
range = sheet.get_Range("A4", Missing.Value);
range.set_Value(Missing.Value, 20000);

app.Visible = true;
app.ScreenUpdating = true;

Answer 6

User26357911 posted

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. 

 

 

Answer 7

User26357911 posted

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... http://forums.asp.net/t/1277997.aspx

 

Answer 8

User-1620310474 posted

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?

Answer 9

User26357911 posted

 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.

Answer 10

User-1620310474 posted

So instead of doing this:

 string[,] arrMC = new string[iRows, iCols];
for (int r = 0; r < iRows; r++)
{
for (int c = 0; c < iCols; c++)
{
arrMC[r, c] = gvMgmntConditions.Rows[r].Cells[c].Text;
}
}

range.set_Value(Missing.Value, arrMC);

 

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.  

Answer 11

User26357911 posted

Basically.

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.

Answer 12

User-1620310474 posted

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
...
Next

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;
            }
        }
        range.set_Value(Missing.Value, arrNT);
    }

The image is in the first column and the hyperlink is in the third column.

Answer 13

User-1620310474 posted

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.
                }
                else
                {
                    arrNT[r, c] = gvNonTimelyFilers.Rows[r].Cells[c].Text;
                }
            }

Answer 14

User26357911 posted

Where you are  adding a hyper link, try the following:

sheet.HyperLinks.Add(arrNT[r,c], "http://www.asp.net", null, null, "ASP.Net");

Answer 15

User-1620310474 posted

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.

Answer 16

User-1620310474 posted

I believe this is what is needed:

arrNT[r, c] = "=HYPERLINK(" + '"' + hyp.ResolveUrl(hyp.NavigateUrl) + '"' + ", " + '"' + hyp.Text + '"' + ")";

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!

Answer 17

User26357911 posted

 Instead of setting the Value property, try setting the Formula property of the Range.

Answer 18

User-1620310474 posted
It doesn't seem to work.  I'll have to create a macro to convert after the user opens the workbook.

Answer 19

User-1620310474 posted
I appreciate the help and hopefully this is my last question.   I have been working locally up to this point and got it all functioning on my laptop.   I published to another server and now Excel won't fire when I click the Export to Excel link.   The page refreshes and nothing happens.    I don't think I need anything installed on the server but since this is the first time dealing with the Interop assembly I don't want to assume.   Do you have a clue why this wouldn't work on another server?   Possibly security settings?

Answer 20

User26357911 posted

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.

 

 

Answer 21

User-1620310474 posted

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?

Answer 22

User26357911 posted

 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 :)

Answer 23

User-1620310474 posted

wow, lots of stuff in the trace.   anything particular i should be looking for?

Answer 24

User26357911 posted

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.

That should help you narrow down what is and is not working, or at least let you know what is working.

Answer 25

User-610339241 posted

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.

 

protected void Button1_Click(object sender, EventArgs e)

{

string path = "Multiskrivare_2008-10-16 17_31_54.xls";

RR.Public.Base.ExcelManager.SetDataToExcel("E:\\Websites\\WebSite9\\Multiskrivare_2008-10-16 17_31_54.xls");

//Response.Redirect("/" + path);

System.IO.FileInfo file = new System.IO.FileInfo("E:\\Websites\\WebSite9\\" + path);

Response.Clear(); // clear the current output content from the buffer

Response.ClearHeaders();

Response.AppendHeader("Content-Disposition", "attachment; filename=" + file.Name);

Response.AppendHeader("Content-Length", file.Length.ToString());

Response.AppendHeader("Content-type", "application/octet-stream");

//Response.ContentType = "application/octet-stream";

Response.WriteFile(file.FullName);

Response.End();

}

 

Class

namespace RR.Public.Base

{

/// <summary>

///

/// </summary>

///

public class ExcelManager

{

private static readonly string ExcelConnectionFormatString =

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1'";

private static readonly string ExcelConnectionFormatStringCreate =

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;'";

public static void SetDataToExcel(string excelPath)

{

//Stream stream = typeof(ExcelManager).Assembly.GetManifestResourceStream("RR.Public.Base.Resources.ExportedFile.xls");

Stream stream = File.OpenRead("E:\\Websites\\WebSite9\\Bin\\abc.xls");FileStream fileStream = new FileStream(excelPath, FileMode.Create, FileAccess.ReadWrite);

ReadWriteStream(stream, fileStream);

using (OleDbConnection _Connection = new OleDbConnection(string.Format(ExcelConnectionFormatStringCreate, excelPath)))

{

_Connection.Open();

System.Data.OleDb.OleDbCommand command;command = new OleDbCommand("DROP TABLE [Manohar$];", _Connection);

command.ExecuteNonQuery();

string test = string.Empty;

test = "CREATE TABLE [Manohar$]([FirstName] Varchar(200), [LastName] Varchar(200), [MiddleName] Varchar(200))";command = new OleDbCommand(test, _Connection);

command.ExecuteNonQuery();

 

test = string.Empty;

test = "INSERT INTO [Manohar$] ([FirstName],[LastName],[MiddleName]) VALUES ('Manohar','Sajjan','S')";

command = new OleDbCommand(test.ToString(), _Connection);

command.ExecuteNonQuery();

 

//test.Length = 0;

//test.Append("DELETE FROM [Manohar$] WHERE [Tillverkare] = null AND [Modell] = null AND [Bild] = null");

//command = new OleDbCommand(test.ToString(), _Connection);

//command.ExecuteNonQuery();

 

}

}

private static void ReadWriteStream(Stream readStream, Stream writeStream)

{

int Length = 256;

Byte[] buffer = new Byte[Length];

int bytesRead = readStream.Read(buffer, 0, Length);

// write the required bytes

while (bytesRead > 0)

{

writeStream.Write(buffer, 0, bytesRead);

bytesRead = readStream.Read(buffer, 0, Length);

}

readStream.Close();

writeStream.Close();

}

}

}

Answer 26

User1297983026 posted

Hi Friends,

I am trying to export the DataTable to Excel file FOr which i am using following code onclick of the image button

 

Protected Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnExportExcel.Click

Try

Dim dt As DataTable = getDataTable() ' this function returnds the datatable 

Dim str As String = DataSetToExcel.DataTable2ExcelString(dt)

Response.AppendHeader("Content-Type", "application/vnd.ms-excel")

Response.AppendHeader("Content-disposition", "attachment; filename=DataReport.xls")

Response.Write(str)

 

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

 

 

 

 

Catch ex As Exception

Throw ex

End Try

End Sub