At 03:39 PM 11/10/2008, Bill Niehaus wrote:
I am trying to print a report to an XLS file. I can create the XLS
file and get most of the columns, but some of the columns do not show
up. Several of the page headings fail to show up as well. I tried to
make the columns the same size for 6 variable memo fields. Only three
of them show up. Do I need a minimum spacing between the columns? I
have tried using arial font 8, courier 8, and two other monospace
fonts 8 size for the report. The font selection seemed to make little
difference. Would another font be more conducive to a report that is
destined for xls?
All of the information shows up fine on a screen preview or paper
printout from the report.
Bill,
When PRINTing a report as XLS, the key is to line up the columns as
to how they will fit in the Excel spreadsheet. Try Arial 10 and see
what you get.
Also, you may have to "overlap" the columns in this case to eliminate
the addition of the extra column.
I am assuming that you are printing a complex report with sub-totals,
totals by break header/break footer and grand totals at the end of
the whole (Report Footer).
If you simply printing details items with headers, try the simply
GATEWAY EXPORT command.
-- Example
IF (CVAL('DATABASE')) <> 'RRBYW16' OR (CVAL('DATABASE')) IS NULL THEN
CONNECT RRBYW16 IDENTIFIED BY NONE
ENDIF
CLS
PAUSE 3 USING ' Exporting Search Results as XLS Workbook ' +
CAPTION ' Save Search Results' ICON APP +
OPTION MESSAGE_FONT_NAME VERDANA +
|MESSAGE_FONT_SIZE 10 +
|MESSAGE_FONT_COLOR RED +
|THEMENAME Sports Blue
-- Delete previously created Customer.xls or Customer.xlsx, if any
SET ERROR MESSAGE 2077 OFF
DELETE Customer_Search_Result.xls
DELETE Customer_Search_Result.xlsx
SET ERROR MESSAGE 2077 ON
-- Find the default file extension and version of MS Excel installed
-- .xlsx for MS Office Excel 2007
PLUGIN RRegistry.RBL 'vCheckExtension|CHECK_KEY|HKCR|.xlsx|'
IF vCheckExtension = 'TRUE' THEN
-- Create XLSX Worksheet for MS Excel 2007
GATEWAY EXPORT XLSW Customer_Search_Result.xlsx +
SELECT +
InvoiceDate=14, +
RegistID=10, +
OrderNumber=14, +
CustID=10, +
(SLastName+','&SFirstName&SMiddle)=30 AS ShipTo, +
SCompany=40, +
(SCity+','&SState)=22 AS SCityAndState, +
SCountryName=16, +
SEMail=30, +
(BLastName+','&BFirstName)=30 AS BillTo, +
BCompany=40, +
(BCity+','&BState)=22 AS BCityAndState, +
BCountryName=16, +
BEMail=30, +
NumLic=8, +
OrderTotal=S=10, +
ProductKey=26 +
FROM OrderSummary WHERE &vWhereClause ORDER BY InvoiceDate DESC +
OPTION SHEET_NAME Customers Search Result +
|COL_NAMES ON +
|ACTION OPENVIEW
ELSE
-- Create XLS Worksheet for older MS Excel
GATEWAY EXPORT XLSW Customer.xls +
SELECT +
InvoiceDate=14, +
RegistID=10, +
OrderNumber=14, +
CustID=10, +
(SLastName+','&SFirstName&SMiddle)=30 AS ShipTo, +
SCompany=40, +
(SCity+','&SState)=22 AS SCityAndState, +
SCountryName=16, +
SEMail=30, +
(BLastName+','&BFirstName)=30 AS BillTo, +
BCompany=40, +
(BCity+','&BState)=22 AS BCityAndState, +
BCountryName=16, +
BEMail=30, +
NumLic=8, +
OrderTotal=S=10, +
ProductKey=26 +
FROM OrderSummary WHERE &vWhereClause ORDER BY InvoiceDate DESC +
OPTION SHEET_NAME Customers Search Result +
|COL_NAMES ON +
|ACTION OPENVIEW
ENDIF
RETURN
Hope that helps!
Very Best R:egards,
Razzak.
