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