Java tutorial
/******************************************************************************* * Copyright 2014 Virginia Polytechnic Institute and State University * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. ******************************************************************************/ package edu.vt.vbi.patric.common; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFPalette; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.json.simple.JSONArray; import org.json.simple.JSONObject; import edu.vt.vbi.patric.dao.ResultType; import org.slf4j.Logger; import org.slf4j.LoggerFactory; //NOTES: //1. In allowing getter/setter methods to add new headers/fields, it opens up the // possibility of mismatching header and fields arrays (ie, one longer than the other) //2. This class knows whether it's using HSSF or XSSF by which of wb or xwb are null //OPTIONS INFORMATION //-Boarder Options: // see createBorderStyle for options //-Alternating row color // 0 = not alternating // 1 = alternating //-Empty cells // 0 = don't highlight // 1 = highlight //TABLE OF CONTENTS: //1. Global variables //2. Constructors //3. Primary Methods //4. Helper Methods //5. Getters/setters //6. Methods for testing public class ExcelHelper { private List<String> headers; private List<String> fields; private List<?> source; private Map<String, CellStyle> styles; private Map<String, XSSFCellStyle> xstyles; // Excel Workbooks private Workbook wb; private XSSFWorkbook xwb; // Option selection Variables private int borderOpt; private int alternatingOpt; private int emptyOpt; private static final Logger LOGGER = LoggerFactory.getLogger(ExcelHelper.class); /** * This constructor is used when you're reading in a file instead of generating one * * @param type = type of workbook. "hssf" or "xssf", defaults to XSSF */ public ExcelHelper(String type) { if (type.equalsIgnoreCase("hssf")) { wb = new HSSFWorkbook(); styles = new HashMap<>(); styles = createStyles(); } else { xwb = new XSSFWorkbook(); xstyles = new HashMap<>(); xstyles = createXStyles(); } borderOpt = 0; alternatingOpt = 0; emptyOpt = 0; headers = new ArrayList<>(); fields = new ArrayList<>(); source = new JSONArray(); } /** * Full constructor * * @param type = type of workbook. "hssf" or "xssf", defaults to HSSF * @param h = list of column headers * @param f = list of field names for data retrieval * @param s = list of JSON objects for data retrieval */ public ExcelHelper(String type, List<String> h, List<String> f, List<?> s) { if (type.equalsIgnoreCase("hssf")) { wb = new HSSFWorkbook(); styles = new HashMap<>(); styles = createStyles(); } else { xwb = new XSSFWorkbook(); xstyles = new HashMap<>(); xstyles = createXStyles(); } borderOpt = 0; alternatingOpt = 0; emptyOpt = 0; headers = h; fields = f; source = s; } // 3. Primary Methods // -------------------------------------------------------------------- /** * This method builds the spreadsheet based on flagged options. */ public void buildSpreadsheet() { // row counter int rowCount = 0; // Alternating background helper boolean bg = false; // Empty Cell flag boolean emptyCell = false; // This logical structure builds the spreadsheet based on whether it is // XSSF or HSSF if (xwb == null) { Sheet sheet1 = wb.createSheet("Results"); // Create the header row Row headerRow = sheet1.createRow(rowCount); rowCount++; // create the header cells based off the values of headers array Cell headerCell; for (int i = 0; i < headers.size(); i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(headers.get(i)); headerCell.setCellStyle(styles.get("header")); } if (source instanceof JSONArray) { // print body // Iterator<?> itr = source.iterator(); // while (itr.hasNext()) { for (Object aRow : source) { Row row = sheet1.createRow(rowCount); rowCount++; // JSONObject jObj = (JSONObject) itr.next(); JSONObject jObj = (JSONObject) aRow; for (int i = 0; i < fields.size(); i++) { String _f = fields.get(i); Cell cell = row.createCell(i); if (jObj.get(_f) != null) { cell.setCellValue(jObj.get(_f).toString()); } else { cell.setCellValue(""); emptyCell = true; } if (alternatingOpt == 1 && !bg) { cell.setCellStyle(styles.get("bg2")); } else { cell.setCellStyle(styles.get("bg1")); } if (emptyOpt == 1 && emptyCell) { cell.setCellStyle(styles.get("empty")); emptyCell = false; } } bg = !bg; } } else if (source instanceof List<?>) { // print body // Iterator<?> itr = source.iterator(); // while (itr.hasNext()) { for (Object aRow : source) { Row row = sheet1.createRow(rowCount); rowCount++; // ResultType rObj = (ResultType) itr.next(); ResultType rObj = (ResultType) aRow; for (int i = 0; i < fields.size(); i++) { String _f = fields.get(i); Cell cell = row.createCell(i); if (rObj.get(_f) != null) { cell.setCellValue(rObj.get(_f)); } else { cell.setCellValue(""); emptyCell = true; } if (alternatingOpt == 1 && !bg) { cell.setCellStyle(styles.get("bg2")); } else { cell.setCellStyle(styles.get("bg1")); } if (emptyOpt == 1 && emptyCell) { cell.setCellStyle(styles.get("empty")); emptyCell = false; } } bg = !bg; } } setColWidths(); } else { XSSFSheet sheet1 = xwb.createSheet("Results"); // Create the header row XSSFRow headerRow = sheet1.createRow(rowCount); rowCount++; // create the header cells based off the values of titles array XSSFCell headerCell; for (int i = 0; i < headers.size(); i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(headers.get(i)); headerCell.setCellStyle(xstyles.get("header")); } if (source instanceof JSONArray) { // print body Iterator<?> itr = source.iterator(); bg = true; while (itr.hasNext()) { XSSFRow row = sheet1.createRow(rowCount); rowCount++; JSONObject jObj = (JSONObject) itr.next(); for (int i = 0; i < fields.size(); i++) { String _f = fields.get(i); XSSFCell cell = row.createCell(i); if (jObj.get(_f) != null) { cell.setCellValue(jObj.get(_f).toString()); } else { cell.setCellValue(""); emptyCell = true; } if (alternatingOpt == 1 && !bg) { cell.setCellStyle(xstyles.get("bg2")); } else { cell.setCellStyle(xstyles.get("bg1")); } if (emptyOpt == 1 && emptyCell) { cell.setCellStyle(xstyles.get("empty")); emptyCell = false; } } bg = !bg; } } else if (source instanceof ArrayList<?>) { // print body Iterator<?> itr = source.iterator(); bg = true; while (itr.hasNext()) { XSSFRow row = sheet1.createRow(rowCount); rowCount++; ResultType rObj = (ResultType) itr.next(); for (int i = 0; i < fields.size(); i++) { String _f = fields.get(i); XSSFCell cell = row.createCell(i); if (rObj.get(_f) != null) { cell.setCellValue(rObj.get(_f)); } else { cell.setCellValue(""); emptyCell = true; } if (alternatingOpt == 1 && !bg) { cell.setCellStyle(xstyles.get("bg2")); } else { cell.setCellStyle(xstyles.get("bg1")); } if (emptyOpt == 1 && emptyCell) { cell.setCellStyle(xstyles.get("empty")); emptyCell = false; } } bg = !bg; } } setColWidths(); } } /** * This method will write a simple text file using the header and field arrays, with an array as the source * * @return = string text file */ public String writeToTextFile() { StringBuilder sb = new StringBuilder(); for (String header : headers) { sb.append(header).append("\t"); } sb.append("\n"); if (source instanceof JSONArray) { // Iterator<?> itr = source.iterator(); // while (itr.hasNext()) { // JSONObject jObj = (JSONObject) itr.next(); for (Object obj : source) { JSONObject jObj = (JSONObject) obj; for (String _f : fields) { if (jObj.get(_f) != null) { sb.append(jObj.get(_f)); sb.append("\t"); } else { sb.append("\t"); } } sb.append("\n"); } } else if (source instanceof List<?>) { // Iterator<?> itr = source.iterator(); // while (itr.hasNext()) { // ResultType rObj = (ResultType) itr.next(); for (Object obj : source) { ResultType rObj = (ResultType) obj; for (String _f : fields) { if (rObj.get(_f) != null) { sb.append(rObj.get(_f)); sb.append("\t"); } else { sb.append("\t"); } } sb.append("\n"); } } return sb.toString(); } /** * This method takes the completed spreadsheet and prints it to the browser * * @param out = output stream to use */ public void writeSpreadsheettoBrowser(OutputStream out) { try { if (wb == null) { xwb.write(out); } else { wb.write(out); } } catch (Exception ex) { LOGGER.error(ex.getMessage(), ex); } } /** * Writes the workbook to an Excel file * * @param fN = the desired filename (NOTE: DO NOT include the file extension) */ public void writeSpreadsheet(String fN) { String fileName = ""; if (fN == null || fN.equals("")) { fileName = "temp.xls"; } else { fileName = fN + ".xls"; } try { if (wb == null) { fileName += "x"; FileOutputStream out = new FileOutputStream(fileName); xwb.write(out); out.close(); } else { FileOutputStream out = new FileOutputStream(fileName); wb.write(out); out.close(); } } catch (Exception ex) { LOGGER.error(ex.getMessage(), ex); } } /** * Reads in an Excel file via a filename * * @param fileName = name of the file to read in * @return true/false for success/failure */ public boolean readFile(String fileName) { InputStream inp; try { inp = new FileInputStream(fileName); } catch (FileNotFoundException e) { inp = null; LOGGER.error(e.getMessage(), e); } if (fileName.substring(fileName.length() - 1, fileName.length()).equals("x")) { try { xwb = (XSSFWorkbook) WorkbookFactory.create(inp); return true; } catch (FileNotFoundException e) { LOGGER.error( "File Not Found Exception thrown. Uncomment in readFile(String fileName) to see stack trace.", e); return false; } catch (InvalidFormatException e) { LOGGER.error( "Invalid Format Exception thrown. Uncomment in readFile(String fileName) to see stack trace.", e); return false; } catch (IOException e) { LOGGER.error("I/O Exception thrown. Uncomment in readFile(String fileName) to see stack trace.", e); return false; } catch (Exception e) { return false; } } else { try { wb = WorkbookFactory.create(inp); return true; } catch (FileNotFoundException e) { LOGGER.error( "File Not Found Exception thrown. Uncomment in readFile(String fileName) to see stack trace.", e); return false; } catch (InvalidFormatException e) { LOGGER.error( "Invalid Format Exception thrown. Uncomment in readFile(String fileName) to see stack trace.", e); return false; } catch (IOException e) { LOGGER.error("I/O Exception thrown. Uncomment in readFile(String fileName) to see stack trace.", e); return false; } catch (Exception e) { return false; } } } /** * Returns the text in the cell at (row, col) * * @param row * @param col * @return text of this cell, or "" otherwise */ public String getCellText(int row, int col) { if (xwb == null) { Sheet sheet = wb.getSheetAt(0); Cell cell = sheet.getRow(row).getCell(col); String s = cell.getStringCellValue(); if (s != null) { return s; } else { return ""; } } else { Sheet sheet = wb.getSheetAt(0); Cell cell = sheet.getRow(row).getCell(col); String s = cell.getStringCellValue(); if (s != null) { return s; } else { return ""; } } } /** * Changes the text of the cell at (row, col) to newText * * @param row * @param col * @param newText */ public void setCellText(int row, int col, String newText) { if (xwb == null) { Sheet sheet = wb.getSheetAt(0); Cell cell = sheet.getRow(row).getCell(col); cell.setCellValue(newText); } else { Sheet sheet = wb.getSheetAt(0); Cell cell = sheet.getRow(row).getCell(col); cell.setCellValue(newText); } } // 4. Helper Methods // --------------------------------------------------------------------- /** * This method creates a map of Cellstyle objects for page building. Note: this method used for HSSF pages * * @return hashmap of styles */ private Map<String, CellStyle> createStyles() { // create custom colors HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); // This replaces various shades of grey with custom colors palette.setColorAtIndex(HSSFColor.GREY_25_PERCENT.index, (byte) 0, // RGB red (0-255) (byte) 52, // RGB green (byte) 94 // RGB blue ); palette.setColorAtIndex(HSSFColor.GREY_40_PERCENT.index, (byte) 230, (byte) 240, (byte) 248); palette.setColorAtIndex(HSSFColor.GREY_50_PERCENT.index, (byte) 255, (byte) 193, (byte) 193); // Create header style CellStyle style = createBorderStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); style.setFont(headerFont); styles.put("header", style); // Create alternating-color body styles Font bodyFont = wb.createFont(); bodyFont.setFontHeightInPoints((short) 8); style = createBorderStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(bodyFont); style.setWrapText(true); styles.put("bg1", style); style = createBorderStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); style.setFont(bodyFont); styles.put("bg2", style); // create style for empty cell style = createBorderStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(bodyFont); style.setWrapText(true); styles.put("empty", style); return styles; } /** * This method creates a map of Cellstyle objects for page building. Note: this method used for XSSF pages * * @return hashmap of styles */ private Map<String, XSSFCellStyle> createXStyles() { XSSFCellStyle style = (XSSFCellStyle) createBorderStyle(); // create style for cells in header row Font headerFont = xwb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(0, 52, 94))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setWrapText(true); style.setFont(headerFont); xstyles.put("header", style); // create styles for alternating background color cells Font bodyFont = xwb.createFont(); bodyFont.setFontHeightInPoints((short) 8); style = (XSSFCellStyle) createBorderStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(230, 240, 248))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(bodyFont); style.setWrapText(true); xstyles.put("bg2", style); style = (XSSFCellStyle) createBorderStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setWrapText(true); style.setFont(bodyFont); xstyles.put("bg1", style); style = (XSSFCellStyle) createBorderStyle(); style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 193, 193))); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(bodyFont); style.setWrapText(true); xstyles.put("empty", style); return xstyles; } /** * Returns a CellStyle with a thin black boarder around all edges Boarder Options: 0 = no boarder 1 = all thin black boarder 2 = top+bot thin * black boarder * * @return CellStyle */ private CellStyle createBorderStyle() { CellStyle style; if (wb == null) { style = xwb.createCellStyle(); } else { style = wb.createCellStyle(); } int opt = borderOpt; switch (opt) { case 0: // no border break; case 1: // all thin black border style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); break; case 2: // thin, only top+bot style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); break; default: // nothing break; } return style; } /** * This method automatically sets the column widths How: Measures the character length of the text in header cell of a column. Max column length * is either the title length or the title length *4 */ public void setColWidths() { if (wb == null) { int margin = 4; XSSFSheet sheet = xwb.getSheetAt(0); XSSFRow row = sheet.getRow(0); for (int i = 0; i < row.getLastCellNum(); i++) { sheet.setColumnWidth(i, (decideXColumnWidth(sheet, i) + margin) * 256); } } else { int margin = 4; Sheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(0); for (int i = 0; i < row.getLastCellNum(); i++) { sheet.setColumnWidth(i, (decideColumnWidth(sheet, i) + margin) * 256); } } } /** * Returns the width the Column should be (XSSF version) * @param sheet - sheet of workbook * @param col - the column to work with * @return length (in characters) of that column */ private int decideXColumnWidth(XSSFSheet sheet, int col) { int titleLength = sheet.getRow(0).getCell(col).getStringCellValue().length(); int longestString = titleLength; for (int i = 0; i < sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); XSSFCell cell = row.getCell(col); int temp = cell.getStringCellValue().length(); if (temp > titleLength * 2) { longestString = temp; } } if (longestString > titleLength * 4) { longestString = titleLength * 4; } return longestString; } /** * Returns the width the Column should be (HSSF version) * @param sheet - sheet of workbook * @param col - the column to work with * @return length (in characters) of that column */ private int decideColumnWidth(Sheet sheet, int col) { int titleLength = sheet.getRow(0).getCell(col).getStringCellValue().length(); int longestString = titleLength; for (int i = 0; i < sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); Cell cell = row.getCell(col); int temp = cell.getStringCellValue().length(); if (temp > titleLength * 2) { longestString = temp; } } if (longestString > titleLength * 4) { longestString = titleLength * 4; } return longestString; } /** * Sets header array * * @param h = new list of headers */ public void setHeaders(List<String> h) { headers = h; } /** * Returns the header array * * @return header */ public List<String> getHeaders() { return headers; } /** * Sets fields array * * @param f = new list of fields */ public void setFields(List<String> f) { fields = f; } /** * Returns the fields array * * @return fields */ public List<String> getFields() { return fields; } /** * Sets predetermined boarder option. Styles must be remade if border option is changed see createBorderStyle for options * * @param opt = option to change to */ public void setBorderOption(int opt) { borderOpt = opt; if (wb == null) { xstyles = createXStyles(); } else { styles = createStyles(); } } /** * Sets alternating border color option * * @param opt = option to change to */ public void setAltOption(int opt) { alternatingOpt = opt; } /** * Sets empty cell highlighting option * * @param opt = option to change to */ public void setEmptyOption(int opt) { emptyOpt = opt; } }