Exporting matrix form to excel

r3ign · **Posted:** Tue Apr 10, 2012 10:33 am

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 · **Posted:** Tue Apr 10, 2012 8:21 pm

Hi,

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

thanks

Sog · **Posted:** Wed Apr 11, 2012 10:00 am

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 · **Posted:** Thu Apr 12, 2012 9:19 am

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 · **Posted:** Thu Apr 12, 2012 9:24 am

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 · **Posted:** Thu Apr 12, 2012 9:50 am

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 · **Posted:** Thu Apr 12, 2012 10:21 am

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

r3ign · **Posted:** Fri Apr 13, 2012 5:42 am

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

Code: Select all

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 · **Posted:** Fri Apr 13, 2012 11:44 am

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 · **Posted:** Fri Apr 20, 2012 3:07 am

thanks i got what you meant.

r3ign · **Posted:** Mon Apr 23, 2012 8:39 am

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 · **Posted:** Mon Apr 23, 2012 8:51 am

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.

mibuso.com

Exporting matrix form to excel

Who is online