Export To Excel

fahd

Dear Experts,


I have tried searching the topic on how to export to excel but couldn't find any. I have a report (209 : Vendor Purchase List).

and i want to export to excel.

Is there any way that i could export to excel? How can i export it.

Right now i am saving that report as an html and then opening it in excel. but it's not coming proper. I would really appreciate if someone could help me with this.

julkifli33

use excel buffer (table 370)

Sog

Exporting to Excel is build-in for forms.
Exporting to Excel for reports is not build-in. And can be handled trough the workaround you've just described.
Some reports have a build-in Export to Excel option, while others haven't. If you want any of those reports exported to excel directly, then this will require development. This is usually done by the aid of the Excel Buffer.

satbir

Use Report "Export Budget to Excel" as an example.

SPost29

Hi Fahd,
I found "export budget to excel" to be way overloaded with extra code and hard to understand.

Here are the basics:
ExcelBuffer is set as a global temp table
Exporttoexcel is a boolean on the request form to choose to export to Excel or not
Users almost always want it to default to export
Report - OnPreReport()

IF (ExportToExcel) THEN BEGIN  // this gets the buffer ready to go
  RowNo := 0;
  ExcelBuf.LOCKTABLE;
END;

Report - OnPostReport() this handles the creation of the workbook and worsheet and gives control to the user

IF (ExportToExcel) THEN BEGIN
  ExcelBuf.CreateBook;
  ExcelBuf.CreateSheet('Name','Name',COMPANYNAME,USERID);
  ExcelBuf.GiveUserControl;
END;

ONPRESECTION OF HEADER

IF (ExportToExcel) AND (CurrReport.PAGENO = 1) THEN BEGIN  // these are the headings
  RowNo += 1;

  EnterCell(RowNo,1,Text002,TRUE,FALSE,'');
  EnterCell(RowNo,2,Text001,TRUE,FALSE,'');
  EnterCell(RowNo,3,Text012,TRUE,FALSE,'');
  EnterCell(RowNo,4,Text004,TRUE,FALSE,'');
  RowNo += 1;      // puts space after header before body starts
END;

ONPRESECTION OF body

IF (ExportToExcel) THEN BEGIN  // this is your data
  RowNo += 1;
  EnterCell(RowNo,1,"Document No.",FALSE,FALSE,'');
  EnterCell(RowNo,2,FORMAT("No."),FALSE,FALSE,'00000')
  EnterCell(RowNo,3,"Vendor Item No.",FALSE,FALSE,'');
  EnterCell(RowNo,4,FORMAT(Quantity),FALSE,FALSE,'0');
END;

Create this function:
EnterCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean;UnderLine : Boolean;NumberFormat : Text[30])

ExcelBuf.INIT;
ExcelBuf.VALIDATE("Row No.",RowNo);
ExcelBuf.VALIDATE("Column No.",ColumnNo);
ExcelBuf."Cell Value as Text" := CellValue;
ExcelBuf.Formula := '';
ExcelBuf.Bold := Bold;
ExcelBuf.Underline := UnderLine;
ExcelBuf.NumberFormat := NumberFormat;
ExcelBuf.INSERT;

These are the basics, a little tedious each time, but make yourself and export to Excel template report and you can copy and paste a lot of the code.
You of course can add more as you learn more or need to do more.
Good Luck
Steve

boni_rosas

Hi SPost29, do you know if is possible create 2 Sheets in the same file?
ExcelBuf.CreateSheet('Name','Name',COMPANYNAME,USERID);

Sog

Yes, it is possible to create multiple sheets.
However, the functionality already available in the Excel Buffer is rather limited.
You can have a sheet and a infosheet. The infosheet is mostly used for showing the reportdata, filter, date or execution, ...
But you can always modify excel buffer to add your own functionality to support multiple sheets.

Savatage

fahd wrote:

I have tried searching the topic on how to export to excel but couldn't find any.

:shock:
http://lmsmfy.com/?q=EXPORT+EXCEL

Mike_HWG

Wow, I find SPost29's approach to be a little heavy handed.
Also, yes, searching is good, but since I've created a little helper text file for myself and I'm waiting on a huge task to complete here at work, I don't mind a quick cut and paste:

Look at Report 10145 Item Sales By Customer, which utilizes a multi-sheet output

To begin adding Excel printing to a report, you need to add a temporary record variable that points to the Excel Buffer table.
Excel output can be either one sheet or two sheets. In the two sheet output usage, NAV creates an Info sheet and a Data sheet. To begin multiple sheets, use SetUseInfoSheed().
Creation follows a general code format, which builds the Excel workbook before actual output.
It is customary to insert a CurrReport.SHOWOUTPUT(NOT PrintToExcel) statement on each OnPreSection of your report so that a user only gets the Excel Output, or only gets the report.
Common function calls are:

// This function adds information to the Information sheet
AddInfoColumn(Value,isFormula, CommentText, isBold, isItalic, isUnderline,NumFormat);
							
// This function adds information to the Data sheet
AddColumn(Value,isFormula, CommentText, isBold, isItalic, isUnderline,NumFormat);
				
// This function tells Excel to skip to a new row
NewRow();
		
// The Excel buffer keeps track of the current row and column.  This call resets those variables so you can start at the top again
ExcelBuf.ClearNewRow;

AddInfoColumn and AddColumn use a variable called NumFormat which defines the cell formatting. Here are common examples, a full listing can be found at http://www.ozgrid.com/Excel/CustomFormats.htm

Example		Description
-------		-----------
'#,##0.00'		Translate numbers with thousandth-place comma and two significant decimal figure
'@'			Translate numbers as text
'0.0%'		Translate numbers to percentages with one significant decimal figure

Example code:

/////////////////////////////////////////////////////////
//VARIABLES
//
//Name       Dataytpe   Subtype             Length       Temporary
//ExcelBuf   Record       "Excel Buffer"      n/a            YES
/////////////////////////////////////////////////////////

Report - OnPreReport()

IF PrintToExcel THEN
  MakeExcelInfo;  // This user-created function is optional.  You would use it if you wanted your first sheet to act as a sort
                        // of 'cover page' with information on the report name, filters, etc.
		
ExcelBuf.ClearNewRow;	 // Start at the first cell/row again
		
MakeExcelDataHeader; 	// This user-created function will start building columns and rows to mimic the header section of the report

SomeRecord, Body - OnPreSection() section

IF PrintToExcel THEN
  MakeExcelDataBody;	// This user-created function will build the data of the document, row by row

Report - OnPostReport() section

IF PrintToExcel THEN
  CreateExcelbook;  // This user-created function will output the buffer to Excel

Functions:

MakeExcelInfo()
  ExcelBuf.SetUseInfoSheed;
		
  ExcelBuf.AddInfoColumn(); // Add columns and rows. Keep repeating for all columns in the current row
  ExcelBuf.NewRow;  

  // Keep creating new columns and rows until complete.
  // Don't make a new row at the end of the function!  
  // It is expected the next function will start off by creating its own new row

MakeExcelDataHeader()
  ExcelBuf.NewRow;
		
  // Add  columns and rows
  // Note the ExcelBuf.AddColumn() function's NumFormat should be mindful of the data
  // that will be presented in the next row where we print the actual values
		
  ExcelBuf.AddColumn();	// Keep repeating for all columns in the current row
  ExcelBuf.NewRow;	

  // Keep creating new columns and rows until complete.
  // Don't make a new row at the end of the function!
  // It is expected the next function will start off by creating its own new row

MakeExcelDataBody()
  // Identical to MakeExcelDataHeader, except you will use the actual values now
  // Remember that it is important to specify the cell format at this time!

  ExcelBuf.AddColumn();  // Keep repeating for all columns in the current row	
  ExcelBuf.NewRow;  

  // Keep creating new columns and rows until complete.
  // Don't make a new row at the end of the function!  
  // It is expected the next function will start off by creating its own
  // new row

CreateExcelbook()
  ExcelBuf.CreateBook;
		
  // Note that you are restricted to two sheets, but you can rename them.  Standard
  // format is 'Data' for the first sheet and the name of the report for the second sheet
  ExcelBuf.CreateSheet('first sheet name','second sheet name',COMPANYNAME,USERID);
  ExcelBuf.GiveUserControl;
  ERROR('');

ismailthesacrificer1@gmail.com

I followed this blog but in my case i need to see the report header information and report body information in a single Sheet can any one suggest regarding this
Thanks in Advance..