0

I'm working on a VS 2008 asp.net webforms application in which it contains an existing RDLC (Client file, and NOT the RDL Server side file).

The problem is upon exporting to excel, all of the worksheets are not named properly, i.e. Sheet1, Sheet2, etc. I have found many examples on exporting data, on SQL Server 2008 R2 feature of RDL, however I am in need of a RDLC solution?

Does anyone know about how to either "fix" this or do some sort of post processing to rename the tabs so the customer doesn't end up seeing Sheet1, Sheet2, Sheet3,etc.?

  1. Application is choosing the path of the RDLC file
  2. Adds DataSource
  3. Chooses xls
  4. Does all the Responses

I see this Stackoverflow link How to get named excel sheets while exporting from SSRS, however:

  1. Can't do the Macro
  2. Can't do the export as SSML
  3. I am NOT using RDL / SQL Server 2008 R2 SSRS, thus the other answers are misleading for people using the RDLC
Community
  • 1
  • 1
Tom Stickel
  • 19,633
  • 6
  • 111
  • 113

3 Answers3

1

Sheet renaming isn't supported with the version of RDLC report. I'm guessing you can't upgrade. So here's a work around: Save the report to a file as normal. Then open it again using Microsoft.Office.Interop.Excel or any other Excel library to rename the sheets. Once you do this save and you're done. 

using Excel = Microsoft.Office.Interop.Excel; 


Excel.ApplicationClass xl=new Excel.ApplicationClass();
    Excel.Workbook xlBook;
    Excel.Worksheet xlSheet;
    string filePath = Server.MapPath(@"\report.xls");
    xlBook = (Workbook)xl.Workbooks.Open(filePath,Type.Missing,
      Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing
     ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
    xlSheet = (Worksheet)xlBook.Worksheets.get_Item(1);
    xlSheet.Name = "New Sheet Name";
    xlBook.Save();
    xl.Application.Workbooks.Close();

List of different libraries that you can use if this one doesn't work for you: Free Libraries 1. Close XML Library - http://closedxml.codeplex.com/documentation 2. Open XML SDK - http://msdn.microsoft.com/en-us/library/bb448854.aspx 3. NOPI - http://npoi.codeplex.com/ 4. CarlosAG - http://www.carlosag.net/Tools/ExcelXmlWriter/

Paid Libraries 5. Spreadsheet Gear 6. Smart XLS 7. Office Writer 8. Spire

LCarter
  • 473
  • 7
  • 13
  • I tried getting around the COM access errors, but then it "Cannot locate the file" ... or other issues like being locked (Even though Excel is closed). – Tom Stickel Feb 02 '13 at 00:52
  • @lcarter Please help clear up some things to get past permissions as I have not gotten it to work. I see that some people use the Interop on and others NPOI. – Tom Stickel Feb 02 '13 at 22:37
  • Thanks this is what I was looking for after all... with the interop. Wish it was easier to setup as a LOT of people have the SHEET1 etc.. issue. – Tom Stickel Feb 04 '13 at 00:06
  • 1
    So I ended up changing to using NPOI free library, it works great http://npoi.codeplex.com/ – Tom Stickel Feb 27 '13 at 16:34
0

I would upgrade to the RDL 2010 schema - it supports Excel Named Sheets (amongst other useful enhancements):

http://msdn.microsoft.com/en-us/library/ee960138.aspx

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
-1

Your not going to be able to do it.

http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/54caf25b-b17b-474c-aa47-fc884f90af03

http://go4answers.webhost4life.com/Example/reportviewer-eport-excel-naming-sheets-157612.aspx

http://forums.asp.net/post/4140154.aspx

This may help for post processing using excels xml dialect:

http://spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx

glh
  • 4,900
  • 3
  • 23
  • 40
  • If you know there are other ways to do it then use them. How do you know I'm not an experienced professional and the best way to show that is with the references above? If I wrote my own answer similar to the above or regurgitated the internet this be be less professional as no proof is given and its just my word. – glh Feb 02 '13 at 02:01
  • I repeat my professional answer, you're not going to be able to do it upgrade to RDL. – glh Feb 02 '13 at 02:05
  • Sorry, not trying to attack you, however I explicitly said " 2. Can't do the export as SSML " and yet you show the URL of http://spacefold.com/lisa/post/Changing-the-Sheet-names-in-SQL-Server-RS-Excel-QnD-XSLT.aspx of which glares in your face with "SSML" so with me stating that, you should assume I don't want that, I found that article, that is not acceptable to the customer of a billion dollar company with this million dollar project. 500 points is for people 1. having solved the problem or 2. Showing code in which solves the problem. Thanks – Tom Stickel Feb 02 '13 at 21:07
  • @TomStickel - Please don't insult people who are trying to help you here. Your comments were downright abusive, and I've removed them. This is not the way to encourage folks to answer your question. – Brad Larson Feb 02 '13 at 21:40
  • @BradLarson I'm looking for authoritative and professional responses. If an answer is clearly unprofessional and just a quick google search then it IS NOT helpful. – Tom Stickel Feb 02 '13 at 22:31