Results 1 to 4 of 4

Thread: ROW Duplication

  1. #1
    KalemKutu is offline Member
    Join Date
    Jan 2017
    Posts
    3
    Rep Power
    0

    Default ROW Duplication

    Background:
    1500 columns ( calendar of ca. 3 years = ~1500 days)
    1000 rows

    in Total ~1.5 Mio cells

    ROW = 0
    skip

    ROW = 1
    I am creating for row=1, 1500 columns, i.e. 1500 cells
    Each cell is empty.
    I am just changing the bg-color.
    - Weekend cells are grey background
    - Weekdays cells are white background.

    So far so good! If I save the Excel file, I got what I need.

    ROW = 2...1000 HELP
    Now I wanna apply this for the next 1000 rows aswell.
    But WHAT is the fastest way to do this?

    I tried followinig copy-approach, but not successfully:

    PHP Code:
    final CellCopyPolicy defaultCopyPolicy = new CellCopyPolicy();
    defaultCopyPolicy.createBuilder();
            
    defaultCopyPolicy.setCondenseRows(false);
    defaultCopyPolicy.setCopyCellFormula(false); 
    defaultCopyPolicy.setCopyCellStyle(true);
    defaultCopyPolicy.setCopyCellValue(false); 
    defaultCopyPolicy.setCopyHyperlink(false); 
    defaultCopyPolicy.setCopyMergedRegions(false);
    defaultCopyPolicy.setCopyRowHeight(false);
    defaultCopyPolicy.setMergeHyperlink(false); 
            
    for (int j = 2; j <=1000; j++) {            
             sheet.copyRows(1, 1, j, defaultCopyPolicy);                
    }
    The for-loop takes 45 seconds.
    Afterwards it freezes during saving the file:
    PHP Code:
    FileOutputStream out = new FileOutputStream(new File(targetFile));
    			
    workbook.write(out);
    out.close();
    WHAT is the fastest way to do this?

    (Taskmanager shows 2GB)

  2. #2
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,983
    Rep Power
    8

    Default Re: ROW Duplication

    Two observations:
    1) You are using the builder wrong. It won't influence the outcome, but in the future it may affect any builder you create:
    Java Code:
      CellCopyPolicy policy = new CellCopyPolicy().createBuilder()
                                                        .cellFormula(false)
                                                        .cellStyle(true)
                                                        .cellValue(false)
                                                        .condenseRows(false)
                                                        .copyHyperlink(false)
                                                        .mergedRegions(false)
                                                        .mergeHyperlink(false)
                                                        .rowHeight(false)
                                                        .build();
    2) This smells like a OutOfMemoryError. Crank it up to the most RAM you can spare and try again.

    Can you provide the full source, so I can try it myself?
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: ROW Duplication

    For POI (and Excel, to be honest) you want to keep the number of styles down. There is a limit.

    Since all you are doing is colouring a column of cells, then select that column and apply the colour to all of them at once.
    That way you only have 1500 things to do.
    In fact, since you know weekends are two days next to each other, you can cut that down again...and finally, just select all 1500 columns at the start and apply white to them in one go.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  4. #4
    KalemKutu is offline Member
    Join Date
    Jan 2017
    Posts
    3
    Rep Power
    0

    Default Re: ROW Duplication

    @Tolls

    Thank you for the approach, this really speed up my code!

Similar Threads

  1. Code duplication - must be a better way?
    By Dark Knight in forum New To Java
    Replies: 11
    Last Post: 03-26-2014, 12:05 AM
  2. JComboBox false duplication
    By rookiecoder in forum AWT / Swing
    Replies: 8
    Last Post: 09-26-2012, 11:08 AM
  3. Compare two Arrays, checking for duplication
    By Redefine12 in forum New To Java
    Replies: 4
    Last Post: 02-03-2012, 09:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •