Exporting matrix form to excel

r3ign

Can anyone give me any clue how to do this? I was able to export a fix fields to excel with excel buffer but i dont know how to do this on a more dynamic one which may include dates.

jspopp

Hi,

Can you go into more detail into what you are trying to achieve?

thanks

Sog

I would export a matrixform something like this:
Mainrec = record on the left side of the matrixform
matrixrec = record on the right side of the matrixform
Toprec = record for the columns in the matrixrec

First export the column names:
excelbuffer.add(mainrec.column1);
excelbuffer.add(mainrec.column2);
...
foreach toprec repeat
excelbuffer.add(toprec.value);
until toprec.next

Excelbuffer.newrow;
Then add the values;
foreach mainrec repeat
excelbuffer.add(mainrec.value)
excelbuffer.add(mainrec.value2)
foreach toprec repeat
matrixrec.filter//filtered on mainrec & toprec
matrixrec.findfirst
excelbuffer.add(matrixrec.value);
until toprec.next = 0
excelbuffer.newrow;
until mainrec.next;

easy enough, no?

r3ign

Here is what i meant:
http://i39.tinypic.com/olrna.png
Without date range i think its almost impossible to export this to excel. Seeing the date could be infinite.
Any suggestions?

Sog

Yes, offcourse you'll need a limiter for the dates. Unless you want to have an overview of 01/01/0000 until 31/12/9999
Dates are in the virtual date table. So use that table to filter and show the result.
I would export when a date filter has been applied, and use the option chosen by the user (per day, week, month,...)

r3ign

Didn't know there was a foreach statement in nav haha as i've been only using it for a few weeks.
Thank you for your help. I will try this now.

Sog

and no, there is no foreach statement, just to annoy copy pasters.

r3ign

my code will be something like this, i'm still having trouble. i dont know how to get the column/row of matrix.
Is there a way to transfer the matrix record to a table? or is there any easier way to do this
argh
these are the columns i need to export: http://i39.tinypic.com/olrna.png

Window.OPEN(
  'test' +
  '@1@@@@@@@@@@@@@@@@@@@@@\');
Window.UPDATE(1,0);

RecNo :=0;

TempExcelBuffer.DELETEALL;
CLEAR(TempExcelBuffer);

///my logic is to send the matrix as a record get the fields first 
//col - variable and row variable are record
//1st to insert the column

  IF row.FIND('-') THEN BEGIN
    RowNo := RowNo + 1;
    ColumnNo := 1;
    REPEAT
      ColumnNo := ColumnNo + 1;
      EnterCell(
        RowNo,
        ColumnNo,
        FALSE,
        FALSE,
        FALSE);
    UNTIL row.NEXT = 0;
  END;

//begin adding the rows
// in a .net language it will be something like this(forgive me for this since i have been rushing)
for row=1 to row.count

	for col = 1 to col.count	
		EnterCell(row, col, record, FALSE, FALSE, FALSE);
	next col
next row

Window.CLOSE;
//Create The Worksheet
TempExcelBuffer.CreateBook;
TempExcelBuffer.CreateSheet(Text000,Text000,COMPANYNAME,USERID);
TempExcelBuffer.GiveUserControl;



EnterCell(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean;Italic : Boolean;UnderLine : Boolean)
TempExcelBuffer.INIT;
TempExcelBuffer.VALIDATE("Row No.",RowNo);
TempExcelBuffer.VALIDATE("Column No.",ColumnNo);
TempExcelBuffer."Cell Value as Text" := CellValue;
TempExcelBuffer.Formula := '';
TempExcelBuffer.Bold := Bold;
TempExcelBuffer.Italic := Italic;
TempExcelBuffer.Underline := UnderLine;
TempExcelBuffer.INSERT;

Sog

Your code must mimic the matrix form, not depend on it.
You'll have to use the same source tables and put the same filters on it.
For the columns, the table is a virtual table called Date.

r3ign

thanks i got what you meant.

r3ign

One last question.. since the dates are on day basis.. how can i change the period type of the date table into month or year'
i can't seem to get this to run in my code

edit: nevermind got it.. its date.setrange(period type, periodtype::choice);

Sog

To change the period, filters can be applied on the date table.
The period type is commonly used, they can be day, week, month, quarter and year.
Filter and iterate over them. use the startperiod and endperiod fields in the date table to put the filter on the matrix-record.