Options

Exporting matrix form to excel

r3ignr3ign Member Posts: 10
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.

Comments

  • Options
    jspoppjspopp Member Posts: 54
    Hi,

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

    thanks
  • Options
    SogSog Member Posts: 1,023
    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?
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • Options
    r3ignr3ign Member Posts: 10
    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?
  • Options
    SogSog Member Posts: 1,023
    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,...)
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • Options
    r3ignr3ign Member Posts: 10
    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.
  • Options
    SogSog Member Posts: 1,023
    and no, there is no foreach statement, just to annoy copy pasters.
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • Options
    r3ignr3ign Member Posts: 10
    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;
    
  • Options
    SogSog Member Posts: 1,023
    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.
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • Options
    r3ignr3ign Member Posts: 10
    thanks i got what you meant. :D
  • Options
    r3ignr3ign Member Posts: 10
    edited 2012-04-23
    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);
  • Options
    SogSog Member Posts: 1,023
    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.
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
Sign In or Register to comment.