[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I display the gridlines in a spreadsheet exported from a VB.Net web gridview?

Posted on 2007-07-20
22
Medium Priority
?
2,352 Views
Last Modified: 2013-11-26
Hi!

    I'm successfully exporting a griview on an ASP.Net web form to an Excel file.  My problem is that the resulting new Excel file doesn't have the 'gridlines' option checked, so there are not gridlines showing on the newly-created file!  I don't want to use the gidlines from the gridview, mainly because they're just not Excel's real gridlines.

   In code - as I'm creating the stringwriter and rendering the HTML to export the gridview - how can I tell Excel to check the checkbox for 'Gridlines' in its Options, or more accurately, how can I set that option for the newly-created Excel file from within code?  Perhaps I need to export this as a CSV instead or something.  I'd hate to have to save the file on the server and then open it back up for the user.  Right now it just asks them if they want to open or save the file.  Either way, no gridlines.

   I've seen this question out there a fair amount of times.  I've even asked before last February on the ASP.Net forums, but never got an answer.  So, if we can come up with an answer that would be great.

   Oh, I'm using VB.Net, VS 2005, and Office 2003, if that helps.  All I'm looking for are the few lines of code that I can place into the 'on click' event of the Export button that contains the code that actually exports the gridview to Excel, that will allow the file to be opened up with gridlines displaying.

   Please let me know!

Thanks!
Karen
0
Comment
Question by:klgrube
  • 8
  • 6
  • 4
  • +2
21 Comments
 
LVL 7

Expert Comment

by:ASPSQLServerCOM
ID: 19535266
0
 

Author Comment

by:klgrube
ID: 19535463
I'm not using Automation.  I can't.  I'm using ResponseWrite and StringWriter to do the Export. So this article doesn't help.

Thanks, though!
Karen
0
 
LVL 7

Expert Comment

by:ASPSQLServerCOM
ID: 19535509
use simple table tag and open it in excel

<TABLE id="Table1" cellSpacing="1" cellPadding="1" width="300" border="1">
                  <TR>
                        <TD>1111111</TD>
                        <TD>22222222</TD>
                        <TD>333333333</TD>
                  </TR>
                  <TR>
                        <TD>1111111</TD>
                        <TD>22222222</TD>
                        <TD>333333333</TD>
                  </TR>
            </TABLE>
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:klgrube
ID: 19535610
Hi!

    Not sure what I'd gain by doing that.  The data is already in a gridview.  All I have to do is render the gridview control as an html writer and then do a Response.Write.  Why would I need to consstruct another table and what would that do for me?  I think I'm just missing something in this technique that will turn on the gridlines in the new Excel file.  That's all I'm looking for.

Karen
0
 
LVL 7

Expert Comment

by:ASPSQLServerCOM
ID: 19535726
Hi,

could it be possible for you to paste some HTML which you are rendering.

especially you have to play around with the grid style property to display the lines.

Thanks,
Dipesh
0
 

Author Comment

by:klgrube
ID: 19535769
I'm sorry, but the problem I have has nothing to do with how the griview is set up in terms of its own gridlines.  I know I can use the gridview gridliens if I want, but that only puts 'borders' around the cells used by that little part of the excel file occupied by the exported data.  It doesn't turn on Excel's normal gridlines.  THAT's what I need to know how to do.  In this new file, it's as though someone went in, clicked on 'Tools' then 'options' and then unchecked the 'gridlines' checkbox.  In fact, that checkbox is NOT checked in these new files, and I have no clue why it isn't or how to set it as checked or "enabled" or 'on' or 'true' or whatever the correct term is for telling Excel to display its own normal gridlines.

Any suggestions?

Karen
0
 
LVL 7

Expert Comment

by:ASPSQLServerCOM
ID: 19535828
Hi,

If I am not doing mistake, you are rendering the output as HTML with html writer and then doing  Response.Write and telling browser to open it with
Response.ContentType = "application/vnd.xls"

Thanks,
Dipesh
0
 

Author Comment

by:klgrube
ID: 19536420
Correct.  Yet whenever I do this, the gridlines in the new Excel file are turned off.  How can I fix that?  I don't want to use the gridview's gridlines.  Any suggestions as to how I can turn the gridlines on or activate them in this newly-created file?

Karen
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 19540193
did u try adding a style to ur gridview so that it has borders around the cells ?

Rejo
0
 

Author Comment

by:klgrube
ID: 19540308
I'm really sorry if I'm not being clear.  I just want the normal Excel gridlines to display when the file opens!  I don't want cell borders or anything even remotely resembling that.

For some reason, when you export a gridview to EXCEL this way, the normal EXCEL gridlines are turned off!!!  Don't ask me why they would do that.

When you open the file that gets created and you go to 'Tools;' Options', and look at the first tab, there is a checkbox (among other settings) that lets you turn the gridline display on or off within that workbook.  Somehow, this is getting set as 'unchecked' when you export a gridview to Excel.   Or perhaps more accurately, it isn't getting checked properly, or defaulting properly to Excel's normal screen where you see the gridlines.

What I'm asking is what I can do to get that set back to where the gridlines are displayed when the file opens.  That's all.  I mean, I need to know how to set that from within the asp.net (VB) program code that exports the gridview, not manually.  Or at least I need an explanation of why this is happening.  I've found several posts where people were complaining about this, but none of them had an answer.  I'm hoping we can find one.

Thanks!
Karen
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 19548607
option set for the excel application cannot be remotely changed unless you use office automation to crete instance of the excel application object.. also any changes to these options will remain (for that PC and user) even after the objects are released and closed.
0
 

Author Comment

by:klgrube
ID: 19548661
And that I sort of understand.  but why on Earth am I having to even think about this?  Shouldn't this be taking on the default settings of either the user or the server?  This is ridiculous!   Why isn't it and isnt' there something I can do about this - aside from yelling at Microsoft.

Karen
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 19548827
Karen, I can understand your frustration .. logically this should take the default setting of the logged in user on the client machine and not the server .. note, I haven't  tested this myself ..

Rejo
0
 

Author Comment

by:klgrube
ID: 19560446
Have you been able to test this yet?  I do believe this is a Micorsoft bug, but it's been around for quite a while and II'd like to think there's a fix for it.  Any suggestions?

Karen
0
 
LVL 1

Accepted Solution

by:
elguaro earned 2000 total points
ID: 19562173
If you open the generated excel file with notepad you can see what the html you are rendering looks like.

It will be something very simple like

<div>
<table>
  <!-- your columns and rows are shown here-->
</table>
</div>

It will have more attributes, styles and stuff but that is the basic structure generated.

In order to have Excel show the gridlines you need to add more information to this html.

To try things out add this to your generated excel file

<html xmlns:o=""urn:schemas-microsoft-com:office:office""
   xmlns:x=""urn:schemas-microsoft-com:office:excel""
   xmlns=""http://www.w3.org/TR/REC-html40"">
<head>
<!--[if gte mso 9]><xml>
 <x:ExcelWorkbook>
  <x:ExcelWorksheets>
   <x:ExcelWorksheet>
    <x:Name>Sheet1</x:Name>
    <x:WorksheetOptions>
     <x:Selected/>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
    </x:WorksheetOptions>
   </x:ExcelWorksheet>
  </x:ExcelWorksheets>
 </x:ExcelWorkbook>
</xml><![endif]-->
</head>");

<!-- the table goes here -->

</html>

Now open the file in excel and you will see that it has the gridlines.

It is one of the ExcelWorksheet properties that we are setting in the xml what does the trick. I'm not sure wich one it is, haven't bothered to test it. In this xml you can also control things like how many sheets come up and their names etc.

So what you want is to render this together with the grid. That is simple enough to do:

System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

 Response.Clear();
 Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
 Response.Charset = "";
 Response.Cache.SetCacheability(HttpCacheability.NoCache);
 Response.ContentType = "application/vnd.xls";

 htmlWrite.Write(<insert the html shown above here>);
 htmlWrite.WriteLine("");

 GridView1.RenderControl(htmlWrite);
 htmlWrite.Write(@"</html");

  Response.Write(stringWrite.ToString());
  Response.End();

That should produce what you are after.

Cheers.

0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 19565479
elguaro, good suggestion of using xml tags to manipulate the options (I did not see the options been changed in the html that you have given above .. which property changes the gridlines?) .. klgrube, anyway, if this works, I think xml support in excel started from the version 2003 .. so the prerequisite for this screen would be excel 2003 ..

klgrube,
>>Have you been able to test this yet?
I was expecting you to test it ..  :-) ..
0
 

Author Comment

by:klgrube
ID: 19565605
Thank you!  I'll be testing this later today.   I'm not sure exactly how the code works in the sense of how it sets that option, but I'll certainly try it.

Thanks!
Karen
0
 
LVL 1

Expert Comment

by:elguaro
ID: 19571855
What I did was save a spreadsheet as html from within Excel. The resultintg html looks like what I showed before.  I'm not entirely sure myself which specific option is setting the gridlines on, but it would have to be one of the WorkSheetOptions.

<x:WorksheetOptions>
     <x:Selected/>
     <x:ProtectContents>False</x:ProtectContents>
     <x:ProtectObjects>False</x:ProtectObjects>
     <x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>

It'd be intersting to play around removing one by one and see which one does it.

0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 19574760
elguaro, Maybe you are seeing the gridlines becuase your excel application is set to show gridlines .. try using the excel options menu and unchecking this option and again opening the saved file .. does that show the gridlines? It might not show up, but your approach is more logical .. so there should be an option which could be added to the tag and that might work .. its upto klgrube to give this a try ..
0
 
LVL 26

Expert Comment

by:Rejojohny
ID: 19577328
gridlines are not set for the application as i said .. it is for a worksheet as mentioned by elguaro .. my mistake ..
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20346029
Forced accept.

Computer101
EE Admin
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question