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}