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}