Any pointers on how to accomplish Excel Output into complex tables?

[Anonymous]

Newbie question here, I have been learning Talend by process of trial-and-error, but part of a PoC is to generate an Excel output from a fancy template we've been using for a long time and I'm unable to figure out even how to go about solving the problem.

Basically I have an Excel template which is sectioned by Salesperson (vertically) with each month of the year on a separate vertical row and columns going across. (see template imag)


Attempt 1
============
SQL Query
      \
    tMaps (perform data validations, calculations and rollups)
        \
     Lookup (offset row number)
          \
     tSplitRows (handled breakdown of 1 SQL result into 12 monthly rows)
            \
          Excel  (output standard Talend Excel control was perfectly organized table (but not gapped to template)
          tExcelSheetOuput   (gapped to template horizontally, but could not get it to skip from section to section vertically)
      

Outcome:  Being a novice, I was very satisfied I got the tSplitRows producing the 12 rows for each salesperson vertically.  However, could not figure out how to communicate to the tFileExcelSheetOutput to jump from one section to the next.

Attempt 2
============
SQL Query
       \
     tMaps (perform data validations, calculations and rollups)
         \
    tUniqRow (on salesperson)
            \
      tFlowToIterateLookup (offset row number, let me be honest I have no idea how this is supposed to work)
              \
           (missing something here to pass data for Row into Iterative subtask, tried HashOutput)
                \
           tSplitRows (handled breakdown of 1 SQL result into 12 monthly rows)
                  \
                 tExcelSheetOutput    

Outcome:  Exception in component tFileExcelSheetOutput_1 java.lang.NullPointerException  and corrupt workbook.

I've tried several different combinations for "Row start Index" value like :  ((Integer)globalMap.get("row6.workbook_offset"))



Can someone point me in the right direction to solve this type of challenge, and I'm happy to work through it?

Many Thanks.

[Anonymous]

Could you show me the stack trace of the mentioned NullPointerException?
One possible reason could be the value in the ((Integer)globalMap.get("row6.workbook_offset")) is simply null and the component expects a not-null value.

How does your template looks like. Is the first image your template?
If you want to write in cells right of existing ones this works well but if the component detects a missing row, it will create a new row and this could disrupt your file.

What do you mean with a corrupt workbook? Could MS Excel not open the file?

[Anonymous]

@jlolling.

Thanks for your responses, here are the answers to each question:
Null Point Exception (when using 1st attempt flow) and  "Row start index (starts with 1)" of ((Integer)globalMap.get("row4.workbook_offset"))

Exception in component tFileExcelSheetOutput_1
java.lang.NullPointerException
	at local_project.workbook__t2_0_1.Workbook__T2.tMSSqlInput_1Process(Workbook__T2.java:12851)
	at local_project.workbook__t2_0_1.Workbook__T2.tFileExcelWorkbookOpen_1Process(Workbook__T2.java:507)
	at local_project.workbook__t2_0_1.Workbook__T2.runJobInTOS(Workbook__T2.java:17872)
	at local_project.workbook__t2_0_1.Workbook__T2.main(Workbook__T2.java:17729)

The values for workbook_offset shown below  (threw out some middle values)

.---------------.
|   tLogRow_1   |
|=-------------=|
|workbook_offset|
|=-------------=|
|4              |
|5              |
|6              |
|7              |
|8              |
|9              |
|10             |
|11             |
|12             |
|13             |
|14             |
|15             |
|25             |
|26             |
...
|328            |
|329            |
|330            |
|331            |
'---------------'

Yes the first screenshot is the template, sections are for each salesperson, columns are products, rows are January-December, Q1-Q4, YTD.  The sections have gaps in between them in the existing template, I am trying to write January row for each product, then February and so forth, then skip to the next section when I encounter the next salesperson and repeat the process.  Each salesperson starts as a single query row, and tSplitRows is splitting one row into month x product matrix.  The gap between sections I believe is my issue.  I was trying to tackle this by providing the workbook offset with each row (increment by 1 then skipping to next section).  Was trying tFlowToIterate to see if working section-by-section would work better but this tool is foreign to me.
Corrupt Workbook : I mean when the Null Exception occurs, I am unable to open the resulting xlsx workbook, renaming to xls also fails to open in Excel, so I interpreted that as it becoming corrupt within the process.

[Anonymous]

@jlolling.
  Am I just trying to solve this problem the wrong way?  Literally any guidance in the right direction and I'll go read whatever articles are necessary to get there.  Thx.

[Anonymous]

Could you perhaps send me an item export of your job. I can offer to take a look at it. jan.lolling@gmail.com
The NullPointerException happens in the code of the job and not inside the component. It would be interesting to see the code around this exception. Just click on Code below the job-editor and go to the line mentioned in the first code number in the stack trace.