Removing blank worksheets Excel worksheet

[ChrisHunter]

Is there a way to remove blank worksheets from an excel report that is generated in SSRS?

I've done some research and i could find solutions such as removing blank rows and even entire sheets but only via marcor's in excel, which won't work in my case as the report generates a new excel file each time the report is run so there is no template excel file to do it in.

Is there a way, either in SSRS before the report is finialised, or in the C# side after the report is finialised but before it's displayed, to remove the blank worksheets from the report?

Thanks in advance.

[Ketsuekiame]

Seems odd that Reporting Services would add blank worksheets, wonder the reasoning behind it...

Do you have access to the MS Office library for .NET? That will be quite powerful for doing what you need.

[ChrisHunter]

I know I think it's something to do with the fact that the report is a generic report and in some cases a number of the tables arn't used (don't display any data) but might still be taking up space, others, I've got no clue about.

Yes I've got the Office library, is it possible to open up the excel work book before it's opened, remove the blank worksheets or add a macro (I've seen some examples of such a macro) that will do just that when the report is opened?

In all honesty I'm pretty un-educated when it comes to SSRS and the reports in question were made by someone else and I have the task of managing them.

So do you think removing the blank sheets in C# after the report has been generated?

[Ketsuekiame]

Yeah, you should be able to get the office library to remove the worksheets you don't need.

Once you load your spreadsheet, you should be able to select the worksheet (via an array) and call Delete() on it.

When you're done, save it again and you should be good to go :)

[ChrisHunter]

Yeah that was my orignal idea but the number of and position of the sheets within the document differ from report to report so I wouldn't know the exact index of the sheets.

Thanks for your help, I'll keep trying.

[Ketsuekiame]

You can retrieve the UsedRange of the worksheet. That way you can determine if the worksheet has any data on it. If it's empty, you can delete the sheet.

[EterS]

Get index of blank worksheet and then remove.

I use a C# Excel library to remove

            //Load Workbook
            Workbook book = new Workbook();
            book.LoadFromFile("sample.xls");
            //Remove Worksheet
            Worksheet sheet = book.Worksheets[1];
            sheet.Remove();

[ChrisHunter]

I wouldn't know the exact index of the sheets.

So as already discussed that won't work Eters. It's a report template that's generated for a large number of customers so the amount of sheets per report will differ from report to report.

The same report is done in PDF and it doesn't have blank (done by using expessions to set the visibility of the table objects but the same expressions don't work for the Excel report which I find weird because all the information, table orders etc are the same, it's literary just the application used to display it that is different.

I haven't tried the UsedRange yet but I'll give it a go.

[ChrisHunter]

I can thankfully say after trying 1,000,001 differn't things I finally found the solution!

So my problem was that I had 1 template that would be used to generate a report for numerous clients. However, depending on the type of client, particular tables in the template weren't populated (just left blank) and causing blank sheets to appear in the report.

The solution was as simple as, in SSRS designer, putting the following expression in the Visible property of the table that was blank:

=IIF(CountRows() = 0, True, False)

This has frustrated the hell out of me for ages. Occam's razor springs to mind!

Thanks to everyone for your help.

[ChrisHunter]

EDIT:

=IIF(CountRows("TableName") = 0, True, False)

The only problem is now the pages alignment of the tables is off when I need to display the sheets again (when the row count is > 0).

[TnTinMN]

Chris,

When you write "the pages alignment of the tables is off" do you mean the physical positioning on a given page or do you mean that they are not appearing on the Excel tab that you expect?

If it is a physical positioning problem (the table is not at the top of the page) then it may be due to the hidden table having it's PageBreakAtEnd property being set. It may work to change all tables to PageBreakAtStart.

I am assuming that the report you are running is similar to those used in the ReportViewer as the Reportviewrr is based on SSRS. This may not be the case though.

[ChrisHunter]

I don't know how I can explain it any better to be honest, I simply get blank sheets because a table doesn't contain any data but still takes up space (so it can't be seen but it's hidden property is still true). If I set the hidden property to true when the row count is 0 it messes up the alignment when that table is the displayed.

[TnTinMN]

Chris,

I do not know if this is applicable to your situation, but this technique sort of works for RDLC reports.

• Set the height of the table to zero. It will not let it be exactly zero, but will set it to some small value.
• Add a rectangle (no border) to the report and place the table in it. Then set the height of the rectangle to zero; again will not be exactly zero, but close.
• Toggle the table visibility as you have been doing in an expression.

You the following example, I have set it to page break before the rectangle.

second page with table invisible

[ChrisHunter]

Humm... The task I was working on that I was having this problem with has been put on hold for the moment but I'll give your suggestion go an see what happens.

Tar very much lar.

[ChrisHunter]

Sorry for my stupidity guys but I'd set the width of the report (the the dotted canvas behind the tables) so that it was bigger than the width of a landscape Excel page so it was continuing onto the next page.

Problem offically solved, thanks for all your help.