001/* 002 * (C) Copyright 2013 Nuxeo SA (http://nuxeo.com/) and others. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 * 016 * Contributors: 017 * Martin Pernollet 018 */ 019 020package org.nuxeo.ecm.platform.groups.audit.service.acl.excel; 021 022import java.io.File; 023import java.io.FileInputStream; 024import java.io.FileOutputStream; 025import java.io.IOException; 026import java.io.InputStream; 027import java.util.Collection; 028import java.util.HashMap; 029import java.util.Map; 030 031import org.apache.commons.io.IOUtils; 032import org.apache.commons.logging.Log; 033import org.apache.commons.logging.LogFactory; 034import org.apache.poi.hssf.usermodel.HSSFAnchor; 035import org.apache.poi.hssf.usermodel.HSSFPalette; 036import org.apache.poi.hssf.usermodel.HSSFPatriarch; 037import org.apache.poi.hssf.usermodel.HSSFWorkbook; 038import org.apache.poi.hssf.util.HSSFColor; 039import org.apache.poi.openxml4j.exceptions.InvalidFormatException; 040import org.apache.poi.ss.usermodel.Cell; 041import org.apache.poi.ss.usermodel.CellStyle; 042import org.apache.poi.ss.usermodel.ClientAnchor; 043import org.apache.poi.ss.usermodel.Comment; 044import org.apache.poi.ss.usermodel.CreationHelper; 045import org.apache.poi.ss.usermodel.Drawing; 046import org.apache.poi.ss.usermodel.FillPatternType; 047import org.apache.poi.ss.usermodel.Font; 048import org.apache.poi.ss.usermodel.Picture; 049import org.apache.poi.ss.usermodel.RichTextString; 050import org.apache.poi.ss.usermodel.Row; 051import org.apache.poi.ss.usermodel.Sheet; 052import org.apache.poi.ss.usermodel.Workbook; 053import org.apache.poi.ss.usermodel.WorkbookFactory; 054import org.apache.poi.ss.util.CellRangeAddress; 055import org.apache.poi.xssf.usermodel.XSSFDrawing; 056import org.apache.poi.xssf.usermodel.XSSFWorkbook; 057 058/** 059 * A utility wrapper around Apache POI Excel spreadsheet builder. Comments only supported on XLS type (no XLSX) To add 060 * new excel features, see 061 * 062 * @see http://poi.apache.org/spreadsheet/quick-guide.html (or a copy in /doc) 063 * @author Martin Pernollet <mpernollet@nuxeo.com> 064 */ 065public class ExcelBuilder implements IExcelBuilder { 066 static Log log = LogFactory.getLog(ExcelBuilder.class); 067 068 /** 069 * The max number of columns supported by an excel sheet (256="IV" column header) 070 */ 071 public static final int MAX_COLUMN = 256; 072 073 /** The max number of rows supported by an excel sheet */ 074 public static int MAX_ROW = 65536; // NOSONAR (not final for tests) 075 076 public static final int LAST_COLUMN = MAX_COLUMN - 1; 077 078 public static final int LAST_ROW = MAX_ROW - 1; 079 080 /** 081 * States if the builder should throw an IllegalArgumentException or simply warn with a log once a cell index exceed 082 * max number of columns or row. 083 */ 084 public static final boolean CRASH_ON_CELL_OVERFLOW = false; 085 086 public enum Type { 087 XLS, XLSX 088 } 089 090 // factories and content 091 protected Type type; 092 093 protected Workbook workbook; 094 095 protected CreationHelper create; 096 097 protected Drawing drawing; 098 099 protected Font boldFont; 100 101 protected int currentSheetId; 102 103 // indexs 104 protected Map<Integer, Sheet> sheets; 105 106 public ExcelBuilder() { 107 this(Type.XLS); 108 } 109 110 public ExcelBuilder(Type type) { 111 this(type, "default"); 112 } 113 114 public ExcelBuilder(Type type, String firstSheet) { 115 this.type = type; 116 if (Type.XLS.equals(type)) 117 this.workbook = new HSSFWorkbook(); 118 else if (Type.XLSX.equals(type)) 119 this.workbook = new XSSFWorkbook(); 120 this.sheets = new HashMap<>(); 121 this.create = workbook.getCreationHelper(); 122 this.currentSheetId = newSheet(0, "Repository"); 123 this.drawing = getCurrentSheet().createDrawingPatriarch(); 124 125 this.boldFont = workbook.createFont(); 126 this.boldFont.setBold(true); 127 } 128 129 /** {@inheritDoc} */ 130 @Override 131 public Workbook getWorkbook() { 132 return workbook; 133 } 134 135 public HSSFWorkbook getHSSFWorkbook() { 136 return (HSSFWorkbook) workbook; 137 } 138 139 /* CELL MANAGEMENT */ 140 141 /** {@inheritDoc} */ 142 @Override 143 public Cell setCell(int row, int column, String content, CellStyle style) { 144 if (!validateCellIndex(row, column, content)) { 145 // do not try to create an invalid cell 146 return null; 147 } 148 149 Cell cell = getOrCreateCell(row, column); 150 cell.setCellValue(create.createRichTextString(content)); 151 152 if (style != null) { 153 cell.setCellStyle(style); 154 } 155 return cell; 156 } 157 158 /** 159 * Validate a cell index. If cell index is out of maximum number of rows/columns: 160 * <ul> 161 * <li>throws an {@link IllegalArgumentException} if {@link CRASH_ON_CELL_OVERFLOW} is set to true. 162 * <li>otherwise emit a log warning and return false to invalidate this cell. 163 * </ul> 164 */ 165 protected boolean validateCellIndex(int row, int column, String content) { 166 if (row >= MAX_ROW) { 167 String message = "max number of row (" + MAX_ROW + ") exceeded @ " + row + " by '" + content + "'"; 168 if (CRASH_ON_CELL_OVERFLOW) 169 throw new IllegalArgumentException(message); 170 else 171 log.warn(message); 172 return false; 173 } 174 if (column >= MAX_COLUMN) { 175 String message = "max number of column (" + MAX_COLUMN + ") exceeded @ " + column + " by '" + content + "'"; 176 if (CRASH_ON_CELL_OVERFLOW) 177 throw new IllegalArgumentException(message); 178 else 179 log.warn(message); 180 return false; 181 } 182 return true; 183 } 184 185 /** {@inheritDoc} */ 186 @Override 187 public Cell setCell(int row, int column, String content) { 188 return setCell(row, column, content, null); 189 } 190 191 /* SHEET MANAGEMENT */ 192 193 /** {@inheritDoc} */ 194 @Override 195 public Sheet getCurrentSheet() { 196 return sheets.get(getCurrentSheetId()); 197 } 198 199 /** {@inheritDoc} */ 200 @Override 201 public int getCurrentSheetId() { 202 return currentSheetId; 203 } 204 205 /** {@inheritDoc} */ 206 @Override 207 public void setCurrentSheetId(int s) { 208 currentSheetId = s; 209 } 210 211 /** {@inheritDoc} */ 212 @Override 213 public int newSheet(int index, String name) { 214 Sheet s = workbook.createSheet(name); 215 sheets.put(index, s); 216 return index; 217 } 218 219 /** {@inheritDoc} */ 220 @Override 221 public Collection<Sheet> getAllSheets() { 222 return sheets.values(); 223 } 224 225 protected boolean sheetInitialized(int index) { 226 return sheets.containsKey(index); 227 } 228 229 /* FORMATTING SHORTCUTS */ 230 231 /** {@inheritDoc} */ 232 @Override 233 public void setRowHeight(int row, int height) { 234 getOrCreateRow(row).setHeight((short) height); 235 } 236 237 // Set the width (in units of 1/256th of a character width) 238 /** {@inheritDoc} */ 239 @Override 240 public void setColumnWidth(int column, int width) { 241 getCurrentSheet().setColumnWidth(column, width); 242 } 243 244 /** {@inheritDoc} */ 245 @Override 246 public void setColumnWidthAuto(int column) { 247 getCurrentSheet().autoSizeColumn(column); 248 } 249 250 /** {@inheritDoc} */ 251 @Override 252 public void setFreezePane(int colSplit, int rowSplit) { 253 getCurrentSheet().createFreezePane(colSplit, rowSplit); 254 } 255 256 /** {@inheritDoc} */ 257 @Override 258 public void setFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow) { 259 getCurrentSheet().createFreezePane(colSplit, rowSplit, leftmostColumn, topRow); 260 } 261 262 /** {@inheritDoc} */ 263 @Override 264 public void setSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane) { 265 getCurrentSheet().createSplitPane(xSplitPos, ySplitPos, leftmostColumn, topRow, activePane); 266 } 267 268 /** {@inheritDoc} */ 269 @Override 270 @SuppressWarnings("deprecation") 271 public void mergeRange(int firstRow, int firstColumn, int lastRow, int lastColumn) { 272 getCurrentSheet().addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn)); 273 } 274 275 /* BUILDER METHODS */ 276 277 /** Return a new cell style instance for the choosen workbook {@link Type}. */ 278 @Override 279 public CellStyle newCellStyle() { 280 return workbook.createCellStyle(); 281 } 282 283 protected Cell getOrCreateCell(int i, int j) { 284 Sheet sheet = getCurrentSheet(); 285 Row row = sheet.getRow(i); 286 if (row == null) { 287 row = sheet.createRow(i); 288 } 289 Cell cell = row.getCell(j); 290 if (cell == null) { 291 cell = row.createCell(j); 292 } 293 return cell; 294 } 295 296 protected Row getOrCreateRow(int i) { 297 Sheet sheet = getCurrentSheet(); 298 Row row = sheet.getRow(i); 299 if (row == null) { 300 row = sheet.createRow(i); 301 } 302 return row; 303 } 304 305 /* COMMENTS */ 306 307 /** {@inheritDoc} */ 308 @Override 309 public Comment addComment(Cell cell, String text, int row, int col, int colWidth, int rowHeight) { 310 Comment comment = buildComment(text, row, col, colWidth, rowHeight); 311 if (comment != null) 312 cell.setCellComment(comment); 313 return comment; 314 } 315 316 /** 317 * Return a Comment. Comments are supported only on XLS file (HSSF framework). 318 * 319 * @param row 320 * @param col 321 * @param colWidth 322 * @param rowHeight 323 * @return 324 */ 325 public Comment buildComment(String text, int row, int col, int colWidth, int rowHeight) { 326 ClientAnchor anchor = create.createClientAnchor(); 327 anchor.setCol1(col); 328 anchor.setCol2(col + colWidth); 329 anchor.setRow1(row); 330 anchor.setRow2(row + rowHeight); 331 332 // Create the comment and set the text+author 333 Comment comment = null; 334 if (drawing instanceof HSSFPatriarch) { 335 HSSFPatriarch p = (HSSFPatriarch) drawing; 336 comment = p.createComment((HSSFAnchor) anchor); 337 } else if (drawing instanceof XSSFDrawing) { 338 log.error("comments not supported on XSSFDrawing, i.e. XLSX files"); 339 // XSSFDrawing p = (XSSFDrawing)drawing; 340 // comment = p.createComment((XSSFAnchor)anchor); 341 } 342 if (comment != null) { 343 RichTextString str = create.createRichTextString(text); 344 comment.setString(str); 345 comment.setAuthor(""); 346 // Assign the comment to the cell 347 return comment; 348 } else 349 return null; 350 } 351 352 /* COLORS */ 353 354 /** {@inheritDoc} */ 355 @Override 356 public CellStyle newColoredCellStyle(ByteColor color) { 357 CellStyle style = newCellStyle(); 358 style.setFillForegroundColor(getColor(color).getIndex()); 359 style.setFillPattern(FillPatternType.SOLID_FOREGROUND); 360 return style; 361 } 362 363 @Override 364 public HSSFColor getColor(ByteColor color) { 365 return getColor(color.r, color.g, color.b); 366 } 367 368 public HSSFColor getColor(byte r, byte g, byte b) { 369 HSSFWorkbook hwb = getHSSFWorkbook(); 370 HSSFPalette palette = hwb.getCustomPalette(); 371 HSSFColor color = palette.findSimilarColor(r, g, b); 372 return color; 373 } 374 375 /* PICTURES */ 376 377 @Override 378 public int loadPicture(String image) throws IOException { 379 InputStream is = new FileInputStream(image); 380 byte[] bytes = IOUtils.toByteArray(is); 381 int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG); 382 is.close(); 383 return pictureIdx; 384 } 385 386 @Override 387 public void setPicture(int pictureIdx, int col1, int row1, boolean resize) { 388 ClientAnchor anchor = create.createClientAnchor(); 389 // set top-left corner of the picture, 390 // subsequent call of Picture#resize() will operate relative to it 391 anchor.setCol1(col1); 392 anchor.setRow1(row1); 393 Picture pict = drawing.createPicture(anchor, pictureIdx); 394 395 // auto-size picture relative to its top-left corner 396 if (resize) 397 pict.resize(); 398 } 399 400 /* FONTS */ 401 402 @Override 403 public Font getBoldFont() { 404 return boldFont; 405 } 406 407 @Override 408 public Font newFont(int size) { 409 Font newFont = workbook.createFont(); 410 // newFont.setBoldweight(Font.BOLDWEIGHT_BOLD); 411 newFont.setFontHeightInPoints((short) size); 412 return newFont; 413 } 414 415 @Override 416 public Font newFont() { 417 return workbook.createFont(); 418 } 419 420 /* IO */ 421 422 /** {@inheritDoc} */ 423 @Override 424 public void save(String file) throws IOException { 425 save(new File(file)); 426 } 427 428 /** {@inheritDoc} */ 429 @Override 430 public void save(File file) throws IOException { 431 FileOutputStream fileOut = new FileOutputStream(file); 432 workbook.write(fileOut); 433 fileOut.close(); 434 } 435 436 /** {@inheritDoc} */ 437 @Override 438 public Workbook load(String file) throws InvalidFormatException, IOException { 439 return load(file); 440 } 441 442 /** {@inheritDoc} */ 443 @Override 444 public Workbook load(File file) throws InvalidFormatException, IOException { 445 InputStream inp = new FileInputStream(file); 446 return WorkbookFactory.create(inp); 447 } 448}