Package org.apache.poi.ss.util

Examples of org.apache.poi.ss.util.CellRangeAddress

527
528
529
530
531
532
533
534
535
536
537
    HSSFCellStyle emptyRightCellStyle = myWorkBook.createCellStyle();
    emptyRightCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
   
    HSSFCellStyle emptyCellStyle = myWorkBook.createCellStyle();
   
    cra = new CellRangeAddress(mySheet.getLastRowNum()+1, mySheet.getLastRowNum()+1, 0, 7);
    mySheet.addMergedRegion( cra );
     
    myFont = myWorkBook.createFont();
    myFont.setFontHeight((short) 240);
    myFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
View Full Code Here
665
666
667
668
669
670
671
672
673
674
675
    myCell.setCellValue("");
    myCell.setCellStyle(emptyRightCellStyle);
  }
 
  public void writeMonth(int k, int j){
    cra = new CellRangeAddress(mySheet.getLastRowNum()+1, mySheet.getLastRowNum()+1, 0, 7);
    mySheet.addMergedRegion( cra );
   
    myFont = myWorkBook.createFont();
    myFont.setFontHeight((short) 240);
    myFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
View Full Code Here
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
              "Soz. Vers. AN",
              "Gehalt Netto",
              "Soz. Vers. AG",
              "Gesamtkosten"};

    CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 7);
    mySheet.addMergedRegion( cra );
   
    myRow = mySheet.createRow(0);
   
    myCell = myRow.createCell(0);
    myCell.setCellValue("�bersicht aller Abrechnungen im Monat "+df.format(startDat));
   
    myFont = myWorkBook.createFont();
    myFont.setFontHeight((short) 350);
    myFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
   
    myCellStyle = myWorkBook.createCellStyle();
    myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
    myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THICK);
    myCellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
    myCellStyle.setFillForegroundColor(HSSFColor.GREY_80_PERCENT.index);
    myCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    myCellStyle.setFont(myFont);
    myCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   
    myCell.setCellStyle(myCellStyle);
   
    for(int i = 1; i < 7; i++){
      myCell = myRow.createCell(i);
      myCellStyle = myWorkBook.createCellStyle();
      myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
      myCellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
      myCell.setCellStyle(myCellStyle);
    }
    myCell = myRow.createCell(7);
    myCellStyle = myWorkBook.createCellStyle();
    myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
    myCellStyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
    myCellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
    myCell.setCellStyle(myCellStyle);
   
   
    myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
   
    myFont = myWorkBook.createFont();
    myFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
   
    myCellStyle = myWorkBook.createCellStyle();
    myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    myCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    myCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    myCellStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
    myCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    myCellStyle.setFont(myFont);
   
    for(int i = 0; i < header.length; i++){
        myCell = myRow.createCell(i);
        myCell.setCellValue(header[i]);
        myCell.setCellStyle(myCellStyle);
    }
   
    HSSFCellStyle dateCellStyle = myWorkBook.createCellStyle();
    dateCellStyle.setDataFormat(HSSFDataFormat
        .getBuiltinFormat("m/d/yy"));
    dateCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    dateCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   
    HSSFCellStyle euroCellStyle = myWorkBook.createCellStyle();
    euroCellStyle.setDataFormat(myWorkBook.createDataFormat().getFormat("#,##0.00 �"));
    euroCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    euroCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   
    HSSFCellStyle euroRightCellStyle = myWorkBook.createCellStyle();
    euroRightCellStyle.setDataFormat(myWorkBook.createDataFormat().getFormat("#,##0.00 �"));
    euroRightCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    euroRightCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
   
    while(rs.next()){
      myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
      if(rs.getInt("PersID") != persID){
        if(!firstEntry){
          createSumRows();
        }
        myCell = myRow.createCell(0);
        myCell.setCellValue(rs.getString("PersID")+": "+rs.getString("Nachname")+", "+rs.getString("Vorname"));
        myCellStyle = myWorkBook.createCellStyle();
        myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        myCell.setCellStyle(myCellStyle);
       
        myCell = myRow.createCell(1);
        myCell.setCellValue(rs.getDate("BuchungsDat"));
        myCell.setCellStyle(dateCellStyle);
       
        myCell = myRow.createCell(2);
        myCell.setCellValue(rs.getDouble("gehaltausgezahlt.GehBrutto")+
                  rs.getDouble("gehaltausgezahlt.sonstBezug")-
                  rs.getDouble("gehaltausgezahlt.sonstAbzug"));
        myCell.setCellStyle(euroCellStyle);
        gesGesamtgehalt += myCell.getNumericCellValue();
        mitarbGesamtgehalt += myCell.getNumericCellValue();
       
        myCell = myRow.createCell(3);     
        myCell.setCellValue(rs.getDouble("gehaltausgezahlt.LSt")+
                  rs.getDouble("gehaltausgezahlt.KiSt")+
                  rs.getDouble("gehaltausgezahlt.Soli"));
        myCell.setCellStyle(euroCellStyle);
        gesSteuerabzug += myCell.getNumericCellValue();
        mitarbSteuerabzug += myCell.getNumericCellValue();
       
        myCell = myRow.createCell(4);     
        myCell.setCellValue(rs.getDouble("gehaltausgezahlt.RVAN")+
                  rs.getDouble("gehaltausgezahlt.KVAN")+
                  rs.getDouble("gehaltausgezahlt.ALVAN")+
                  rs.getDouble("gehaltausgezahlt.PVAN"));
        myCell.setCellStyle(euroCellStyle);
        gesSozVersAN += myCell.getNumericCellValue();
        mitarbSozVersAN += myCell.getNumericCellValue();

        myCell = myRow.createCell(5);
        myCell.setCellValue(rs.getDouble("gehaltausgezahlt.GehNetto"));
        myCell.setCellStyle(euroCellStyle);
        gesGehaltNetto += myCell.getNumericCellValue();
        mitarbGehaltNetto += myCell.getNumericCellValue();
       
        myCell = myRow.createCell(6);     
        myCell.setCellValue(rs.getDouble("gehaltausgezahlt.RVAG")+
                    rs.getDouble("gehaltausgezahlt.KVAG")+
                    rs.getDouble("gehaltausgezahlt.ALVAG")+
                    rs.getDouble("gehaltausgezahlt.PVAG"));
        myCell.setCellStyle(euroCellStyle);
        gesSozVersAG += myCell.getNumericCellValue();
        mitarbSozVersAG += myCell.getNumericCellValue();
       
        myCell = myRow.createCell(7);
        myCell.setCellValue(rs.getDouble("gehaltausgezahlt.GehBrutto")+
            rs.getDouble("gehaltausgezahlt.sonstBezug")-
            rs.getDouble("gehaltausgezahlt.sonstAbzug")+
            rs.getDouble("gehaltausgezahlt.RVAG")+
            rs.getDouble("gehaltausgezahlt.KVAG")+
            rs.getDouble("gehaltausgezahlt.ALVAG")+
            rs.getDouble("gehaltausgezahlt.PVAG"));
        myCell.setCellStyle(euroRightCellStyle);
        gesGesamtkosten += myCell.getNumericCellValue();
        mitarbGesamtkosten += myCell.getNumericCellValue();
       
        firstEntry = false;
        persID = rs.getInt("PersID");
      }else{
        myCell = myRow.createCell(0);
        myCellStyle = myWorkBook.createCellStyle();
        myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
        myCell.setCellStyle(myCellStyle);
       
        myCell = myRow.createCell(1);
        myCell.setCellValue(rs.getDate("BuchungsDat"));
        myCell.setCellStyle(dateCellStyle);

        myCell = myRow.createCell(2);
        myCell.setCellValue(rs.getDouble("gehaltausgezahlt.GehBrutto")+
                  rs.getDouble("gehaltausgezahlt.sonstBezug")-
                  rs.getDouble("gehaltausgezahlt.sonstAbzug"));
        myCell.setCellStyle(euroCellStyle);
        gesGesamtgehalt += myCell.getNumericCellValue();
        mitarbGesamtgehalt += myCell.getNumericCellValue();
       
        myCell = myRow.createCell(3);     
        myCell.setCellValue(rs.getDouble("gehaltausgezahlt.LSt")+
                  rs.getDouble("gehaltausgezahlt.KiSt")+
                  rs.getDouble("gehaltausgezahlt.Soli"));
        myCell.setCellStyle(euroCellStyle);
        gesSteuerabzug += myCell.getNumericCellValue();
        mitarbSteuerabzug += myCell.getNumericCellValue();
       
        myCell = myRow.createCell(4);     
        myCell.setCellValue(rs.getDouble("gehaltausgezahlt.RVAN")+
                  rs.getDouble("gehaltausgezahlt.KVAN")+
                  rs.getDouble("gehaltausgezahlt.ALVAN")+
                  rs.getDouble("gehaltausgezahlt.PVAN"));
        myCell.setCellStyle(euroCellStyle);
        gesSozVersAN += myCell.getNumericCellValue();
        mitarbSozVersAN += myCell.getNumericCellValue();

        myCell = myRow.createCell(5);
        myCell.setCellValue(rs.getDouble("gehaltausgezahlt.GehNetto"));
        myCell.setCellStyle(euroCellStyle);
        gesGehaltNetto += myCell.getNumericCellValue();
        mitarbGehaltNetto += myCell.getNumericCellValue();
       
        myCell = myRow.createCell(6);     
        myCell.setCellValue(rs.getDouble("gehaltausgezahlt.RVAG")+
                    rs.getDouble("gehaltausgezahlt.KVAG")+
                    rs.getDouble("gehaltausgezahlt.ALVAG")+
                    rs.getDouble("gehaltausgezahlt.PVAG"));
        myCell.setCellStyle(euroCellStyle);
        gesSozVersAG += myCell.getNumericCellValue();
        mitarbSozVersAG += myCell.getNumericCellValue();
       
        myCell = myRow.createCell(7);
        myCell.setCellValue(rs.getDouble("gehaltausgezahlt.GehBrutto")+
            rs.getDouble("gehaltausgezahlt.sonstBezug")-
            rs.getDouble("gehaltausgezahlt.sonstAbzug")+
            rs.getDouble("gehaltausgezahlt.RVAG")+
            rs.getDouble("gehaltausgezahlt.KVAG")+
            rs.getDouble("gehaltausgezahlt.ALVAG")+
            rs.getDouble("gehaltausgezahlt.PVAG"));
        myCell.setCellStyle(euroRightCellStyle);
        gesGesamtkosten += myCell.getNumericCellValue();
        mitarbGesamtkosten += myCell.getNumericCellValue();
      }
    }
    createSumRows();
   
    cra = new CellRangeAddress(mySheet.getLastRowNum(), mySheet.getLastRowNum(), 0, 7);
    mySheet.addMergedRegion( cra );
     
    myFont = myWorkBook.createFont();
    myFont.setFontHeight((short) 240);
    myFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
View Full Code Here
659
660
661
662
663
664
665
666
667
668
669
                CFRuleRecord.ComparisonOperator.BETWEEN, "'first sheet'!D1", "'other sheet'!D1");

        HSSFConditionalFormattingRule [] cfRules = { rule1 };

        CellRangeAddress[] regions = {
            new CellRangeAddress(2, 4, 0, 0), // A3:A5
        };
        sheetCF.addConditionalFormatting(regions, cfRules);

        wb.setSheetOrder("other sheet", 0);
View Full Code Here
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
     */
    protected void shiftMerged(int startRow, int endRow, int n, boolean isRow) {
        List<CellRangeAddress> shiftedRegions = new ArrayList<CellRangeAddress>();
        //move merged regions completely if they fall within the new region boundaries when they are shifted
        for (int i = 0; i < getNumMergedRegions(); i++) {
             CellRangeAddress merged = getMergedRegion(i);

             boolean inStart= (merged.getFirstRow() >= startRow || merged.getLastRow() >= startRow);
             boolean inEnd  = (merged.getFirstRow() <= endRow   || merged.getLastRow() <= endRow);

             //don't check if it's not within the shifted area
             if (!inStart || !inEnd) {
                continue;
             }

             //only shift if the region outside the shifted rows is not merged too
             if (!SheetUtil.containsCell(merged, startRow-1, 0) &&
                 !SheetUtil.containsCell(merged, endRow+1, 0)){
                 merged.setFirstRow(merged.getFirstRow()+n);
                 merged.setLastRow(merged.getLastRow()+n);
                 //have to remove/add it back
                 shiftedRegions.add(merged);
                 removeMergedRegion(i);
                 i = i -1; // we have to back up now since we removed one
             }
        }

        //read so it doesn't get shifted again
        Iterator<CellRangeAddress> iterator = shiftedRegions.iterator();
        while (iterator.hasNext()) {
            CellRangeAddress region = iterator.next();

            this.addMergedRegion(region);
        }
    }
View Full Code Here
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
        if (!(rec instanceof FormulaRecordAggregate)) {
            String ref = new CellReference(cell).formatAsString();
            throw new IllegalArgumentException("Cell " + ref + " is not part of an array formula.");
        }
        FormulaRecordAggregate fra = (FormulaRecordAggregate) rec;
        CellRangeAddress range = fra.removeArrayFormula(cell.getRowIndex(), cell.getColumnIndex());

        CellRange<HSSFCell> result = getCellRange(range);
        // clear all cells in the range
        for (Cell c : result) {
            c.setCellType(Cell.CELL_TYPE_BLANK);
View Full Code Here
430
431
432
433
434
435
436
437
438
439
440
        return _fileOpts;
    }


    public HyperlinkRecord(RecordInputStream in) {
        _range = new CellRangeAddress(in);

        _guid = new GUID(in);

        /**
         * streamVersion (4 bytes): An unsigned integer that specifies the version number
View Full Code Here
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
            rule1, rule2
        };

        short col = 1;
        CellRangeAddress [] regions = {
            new CellRangeAddress(0, 65535, col, col)
        };

        sheetCF.addConditionalFormatting(regions, cfRules);
        sheetCF.addConditionalFormatting(regions, cfRules);

        // Verification
        assertEquals(2, sheetCF.getNumConditionalFormattings());
        sheetCF.removeConditionalFormatting(1);
        assertEquals(1, sheetCF.getNumConditionalFormattings());
        ConditionalFormatting cf = sheetCF.getConditionalFormattingAt(0);
        assertNotNull(cf);

        regions = cf.getFormattingRanges();
        assertNotNull(regions);
        assertEquals(1, regions.length);
        CellRangeAddress r = regions[0];
        assertEquals(1, r.getFirstColumn());
        assertEquals(1, r.getLastColumn());
        assertEquals(0, r.getFirstRow());
        assertEquals(65535, r.getLastRow());

        assertEquals(2, cf.getNumberOfRules());

        rule1 = cf.getRule(0);
        assertEquals("7",rule1.getFormula1());
View Full Code Here
702
703
704
705
706
707
708
709
710
711
712
   
    /**
     * Initialize a new url link
     */
    public void newUrlLink() {
        _range = new CellRangeAddress(0, 0, 0, 0);
        _guid = STD_MONIKER;
        _linkOpts = HLINK_URL | HLINK_ABS | HLINK_LABEL;
        setLabel("");
        _moniker = URL_MONIKER;
        setAddress("");
View Full Code Here
715
716
717
718
719
720
721
722
723
724
725
    /**
     * Initialize a new file link
     */
    public void newFileLink() {
        _range = new CellRangeAddress(0, 0, 0, 0);
        _guid = STD_MONIKER;
        _linkOpts = HLINK_URL | HLINK_LABEL;
        _fileOpts = 0;
        setLabel("");
        _moniker = FILE_MONIKER;
View Full Code Here
TOP

Related Classes of org.apache.poi.ss.util.CellRangeAddress

  • accounts.web.ReportPoiController
  • accounts.web.ReportPoiControllerTest
  • AlMaGe.export.ExcelReportDreiJahre
  • AlMaGe.export.ExcelReportFuenfJahre
  • AlMaGe.export.ExcelReportJahr
  • AlMaGe.export.ExcelReportQuartal
  • AlMaGe.export.ExcelReportZehnJahre
  • br.net.woodstock.rockframework.office.spreadsheet.poi.HSSFSpreadsheetDocumentWriter
  • br.net.woodstock.rockframework.office.spreadsheet.poi.XSSFSpreadsheetDocumentWriter
  • com.dtrules.compiler.excel.util.Rules2Excel
    • Copyright © 2018 www.massapicom. All rights reserved.
    All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.