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}