Code the WorksheetOptions on export to Excel

[Matt]

I have a web page that reads a dataset, populates a datagrid, and then
launches Excel to display the results. I was able to format the results
(font, background color ..) and even create a filter for the top header
row, but I want to also freeze the panes. Does anyone know of a way to
program the WorksheetOptions information?

Below is a sample of what I would like the result to be. I got this
result by freezing the panes in Excel and saving the file to HTML
format. I'm not sure if there is a way to send this information to
Excel.

Any ideas?

<x:WorksheetOptions>
<x:CodeName>Sheet1</x:CodeName>
<x:Selected/>
<xoNotDisplayGridlines/>
<x:FreezePanes/>
<x:FrozenNoSplit/>
<x:SplitHorizontal>2</x:SplitHorizontal>
<x:TopRowBottomPane>2</x:TopRowBottomPane>
<x:SplitVertical>2</x:SplitVertical>
<x:LeftColumnRightPane>2</x:LeftColumnRightPane>
<x:ActivePane>0</x:ActivePane>
<xanes>
<xane>
<x:Number>3</x:Number>
</xane>
<xane>
<x:Number>1</x:Number>
</xane>
<xane>
<x:Number>2</x:Number>
</xane>
<xane>
<x:Number>0</x:Number>
</xane>
</xanes>
<xrotectContents>False</xrotectContents>
<xrotectObjects>False</xrotectObjects>
<xrotectScenarios>False</xrotectScenarios>
</x:WorksheetOptions>

 

[Steve C. Orr [MVP, MCSD]]

This article shows multiple ways to accomplish your goal:
http://www.aspnetpro.com/NewsletterArticle/2003/09/asp200309so_l/asp200309so_l.asp

 

[Matt]

Thanks Steve. I saw this article but I didn't think it would solve my
problem because we don't have Excel on our web server. This means that the
entire COM Interop solution suggested would not work. From what I've read in
the article, that's the only one of the solutions that would give me access
to the WorkSheetOptions. I suppose the last option also has the potential to
set the WorkSheetOptions but I don't think having the users change their
security settings in an options. That leaves the middle article solution,
which is what I basically have now. I don't see how I can set freeze panes
option using my current solution.

I should have mentioned that I'm looking for a solution that is server side
and doesn't use COM Interop. My bad.

Any other suggestions?

 

[Steve C. Orr [MVP, MCSD]]

You might consider one of these 3rd party products to make the whole process
easier:
http://www.aspose.com/Products/Aspose.Excel/
http://officewriter.softartisans.com/

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://Steve.Orr.net
Hire top-notch developers at http://www.able-consulting.com

 

[Matt]

Thanks for the suggestion. We don't plan on doing this a lot so buing a 3rd
party product isn't an option right now.

I guess this can't be done. I was hoping that I could somehow send the code
in via the reponse object using a writer but I guess not. I know this can be
done using XML (but that's a different animal) but I have found this to be
too slow.