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