CODEWITHSUNDEEP
crystal-reportsexport-to-excel
Rams
Rams

Reputation: 91

How to eliminate blank rows while exporting to Excel?

When I export a crystal report to an Excel file, it adds a blank row after every detail row. I have tried many solutions like aligning the fields, keeping the height similar, alignment as baseline. But none of them work.

Can you please let me know a solution so that such blank rows don't get added?

Upvotes: 3

Views: 26516

Answers (10)

summer fondness
summer fondness

Reputation: 101

I found "Use worksheet functions for summaries" under the Excel Format Options can cause extra rows.

Upvotes: 0

Sabatino Ognibene
Sabatino Ognibene

Reputation: 171

Using crystal reports 2008 here.

My report has the report header, report footer, and page footer suppressed. Only the page header (for column headers) and details are not suppressed.

Doing the following removed the extra rows when exporting...

On the design tab of crystal reports, I right clicked on each field in the page header section and did the following:

  1. Select “Size and Position”
  2. Set Y to 0 and height to 0.153.
  3. Click OK
  4. Repeat for every field.

I then right clicked on each field in the details section and performed the above steps again.

After that, I grabbed the little bar that separates the page header and details section and moved it as far up as possible, removing all extra space. Then I grabbed the bar between the details and report footer section and moved it as far up as possible, removing all extra space. Saved file.

Now when I export there are no extra rows.

Upvotes: 0

hafiz haseeb
hafiz haseeb

Reputation: 1

There are only a few things to do, to eliminate the empty rows

  1. Align all the columns and rows to the top (make sure to click on fit section option)

  2. All the columns and detail columns should be equal in size and align

  3. In section, expert make sure in all sections the checkbox (keep together unchecked)

  4. In the Detail section, all the columns should be set to Top 0 (Go to the properties [by pressing F4 or direct] set TOP = 0)

Upvotes: 0

Doghouse Riley
Doghouse Riley

Reputation: 1

I had a problem that couldn't be solved by the tips above because my report needed group footer rows only, not detail.

After a good deal of trial and error, I found that the culprits were string fields in my group footer row which had null values in the detail.

I have no idea why those fields were somehow forcing line breaks, but I do know that when I replaced the string field

"serial_no"

in my GF row with this formula

"if serial_no = "" then " " else serial_no" (where the length of the inserted blank field is the length of a populated serial_no)

the inserted lines vanished, never to return.

Upvotes: 0

Bruno Henn
Bruno Henn

Reputation: 141

If the report is complex with a lot of different rows and columns it will be almost impossible to export. It will create bunch of new lines and columns so then it is impossible to manipulate the excel file.

Otherwise, if it's a more simple report, with some small changes like: - realign columns - align all fields in the same row to top - remove blank spaces - suppress blank rows

It is possible to get a better exporting

Upvotes: 0

QSS
QSS

Reputation: 41

you can export to Microsoft Excel (Data Only), it will remove all the issues of rows/columns gaps provided you have a CR V 13+

Upvotes: 0

Abhishek Soni
Abhishek Soni

Reputation: 11

The best way is Right click on Details cell then select "SIZE & POSITION" then Keep all cell have same Hight and KEEP Y length as 0.0 Issue resolved enter image description here

Upvotes: 0

davejal
davejal

Reputation: 6133

This solved this issue for me.

  1. align to top
  2. arrange lines

The first align to top:

  1. Select all fields on the row, right click, align to top
  2. move the selection to the top of details section (no space between the details section and the section above
  3. remove remaining space at the bottom of the details section

The second:

  1. right click details
  2. click on arrange lines
  3. right click details again
  4. click on fit section

This solved the issue for me.

Sometimes only the first part is necesarry.

Upvotes: 1

HoosierDawg51
HoosierDawg51

Reputation: 1

I had a similar problem with exporting a "|" delimited file when trying to suppress a (group)record when my quantity was zero. Using the section expert the data was suppressed but my pipes were still showing up.
The solution was to use Report>Selection Formulas>Group and use my formula Sum ({Command.QUANTITY}, {@PerformingProvider-Physician}) <> 0.00 ^ is the group This eliminates the group with 0 quantity from the export

Upvotes: 0

Rams
Rams

Reputation: 91

Finally, I got a solution with trial and error method. On crystal report designer, I went to Report->Section Expert. Under Details section, unchecked the 'Free-form Placement' checkbox and clicked on 'OK'. This solution worked and the blank rows no longer appeared while exporting to excel.

Upvotes: 3

Related Questions