Analyzing the "Bad" Output
Place your cursor in the Employee ID column. Notice that it is one cell wide. Now move your cursor one column to the right, to the City column. Notice this is four fields wide. Look at the rest of the fields the same way. Region is four cells wide, Country is four cells wide, and Postal Code is three cells wide.
Now, highlight all of the data (not the headings--just the data). Navigate Data > Sort, and choose Column A as your sort column, and click OK. You should receive an error message that says "This operation requires the merged cells to be identically sized." This message is the fast track to angry sales managers paying you a visit. They do not want to slice and dice a spreadsheet so that the columns fit properly. They just want it to work when the data are exported.
Turning "Bad" into "Good"
There are several report formatting options and document properties you can set programmatically for Excel output. One of the programming options is "ExcelUseConstantColumnWidth". When set to true, the Crystal engine will set all Excel fields to the same width, and merge as many fields as necessary to hold the entire field. Setting this to false will let the Crystal engine put each value in a single cell of a more appropriate width.
Add the following lines of code:
Dim objExcelOptions As ExcelFormatOptions = New ExcelFormatOptions
objExcelOptions.ExcelUseConstantColumnWidth = False
rptExcel.ExportOptions.FormatOptions = objExcelOptions
so that your Page_Init now reads
Dim rptExcel As New ReportDocument
Dim strExportFile As String = Server.MapPath(".") & "/bad.xls"
rptExcel.Load(Server.MapPath("bad.rpt"))
rptExcel.ExportOptions.ExportDestinationType = ExportDestinationType.DiskFile
rptExcel.ExportOptions.ExportFormatType = ExportFormatType.Excel
Dim objExcelOptions As ExcelFormatOptions = New ExcelFormatOptions
objExcelOptions.ExcelUseConstantColumnWidth = False
rptExcel.ExportOptions.FormatOptions = objExcelOptions
Dim objOptions As DiskFileDestinationOptions = New DiskFileDestinationOptions
objOptions.DiskFileName = strExportFile
rptExcel.ExportOptions.DestinationOptions = objOptions
rptExcel.Export()
objOptions = Nothing
rptExcel = Nothing
Response.Redirect("bad.xls")
Now, rebuild and run your project. You'll see now that there are very narrow columns between each of the data columns. Tabbing from column to column shows that Employee ID uses a single cell, City requires two cells (a narrow one, probably C, and a wide one, probably D), Region and Country each use a wide cell, and Postal Code uses two cells.
What's going on here? The answer is that the "variable column width" option is not perfect. Better than before, but not perfect. When a column is comprised of merged cells, this is usually because the Crystal engine could not figure out how to size the column properly. There may have been a spacing issue, or the width of the data column was such that the engine couldn't figure out how to best size the column. Fortunately, with a little design tweaking, we can cure the rest of our problems.
Design factors that influence Excel output include:
- field width
- field height
- spacing between fields
- alignment of fields in the same column
Looking at the data in Postal Code, we can see the column is wider than necessary--it really only needs one cell. Perhaps by making it the same width as Employee ID field in our report, it will be the same width in Excel. To do this, select the header and field for Postal code, then the header and field for Employee ID. Make sure to select Employee ID last. Right-click on any of the selected fields, and choose Size > Same Width. Save your report, rebuild and run your application. Make sure to close your browser--don't just reload the page. Sure enough, Postal Code is only one cell wide. However, the column titles are truncated. This will take some trial and error on your part by stretching the columns to get right, but now you know the process. Make sure to keep the header the same width as the field in the report file.
What about City? Trying the same resizing trick reveals that it's already the same size as Region and Country, yet the latter two only require one cell. As I mentioned above, another cause of merged cells is odd spacing between columns. Zoom in to about 150%, and you'll see that all of the data columns are approximately the same distance apart--about 1 grid spacing. Adjusting the spacing and rebuilding/running shows that we didn't fix a thing. The answer is actually a little higher on the page--the Print Date field. Set the Print Date field to the same width as Employee ID, save/rebuild/open, and everything should be nicely arranged, all in columns that are one cell wide. If you need more room for a report title, stretch the title field so that its width is equal to that of several columns. When the report is rendered, the stretched field will be merged cells, but the data columns will remain separate.
Besides sizing all the fields in the same column to the same width, these fields should be aligned neatly. In our simple example, we probably don't have any alignment problems. But, if you need to align a number of fields, select all the fields you want to align. Right-click on the field you want to match the others to, and choose Align > Lefts from the menu. You can align lefts, rights, tops, bottoms. Try a few options and see what happens (you're not going to break anything, and this is a learning exercise).
If you see very thin rows or columns between other rows or columns, adjust the spacing between the elements. Try and maintain a consistent spacing horizontally as well as vertically--this helps the Crystal Engine determine where to place the cell boundaries. Ideally, there should only be one grid space between two elements.
In our little demonstration, cleaning up the few formatting problems (such as truncated column headings) will require a little patience and possibly a few cycles of resize/save/rebuild/run. You can resize the column headings, making sure the data field is the same width. When designing production reports, the process actually goes fairly quickly if you know what to look for.
