Java tutorial
/* * ==================================================================== * Licensed to the Apache Software Foundation (ASF) under one or more * contributor license agreements. See the NOTICE file distributed with * this work for additional information regarding copyright ownership. * The ASF licenses this file to You 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 com.wantdo.stat.excel.poi_src; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileOutputStream; import java.io.IOException; import java.util.Calendar; import java.util.HashMap; import java.util.Map; public class SSPerformanceTest { public static void main(String[] args) { if (args.length != 4) usage("need four command arguments"); String type = args[0]; long timeStarted = System.currentTimeMillis(); Workbook workBook = createWorkbook(type); boolean isHType = workBook instanceof HSSFWorkbook; int rows = parseInt(args[1], "Failed to parse rows value as integer"); int cols = parseInt(args[2], "Failed to parse cols value as integer"); boolean saveFile = parseInt(args[3], "Failed to parse saveFile value as integer") != 0; Map<String, CellStyle> styles = createStyles(workBook); Sheet sheet = workBook.createSheet("Main Sheet"); Cell headerCell = sheet.createRow(0).createCell(0); headerCell.setCellValue("Header text is spanned across multiple cells"); headerCell.setCellStyle(styles.get("header")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1")); int sheetNo = 0; int rowIndexInSheet = 1; double value = 0; Calendar calendar = Calendar.getInstance(); for (int rowIndex = 0; rowIndex < rows; rowIndex++) { if (isHType && sheetNo != rowIndex / 0x10000) { sheet = workBook.createSheet("Spillover from sheet " + (++sheetNo)); headerCell.setCellValue("Header text is spanned across multiple cells"); headerCell.setCellStyle(styles.get("header")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1")); rowIndexInSheet = 1; } Row row = sheet.createRow(rowIndexInSheet); for (int colIndex = 0; colIndex < cols; colIndex++) { Cell cell = row.createCell(colIndex); String address = new CellReference(cell).formatAsString(); switch (colIndex) { case 0: // column A: default number format cell.setCellValue(value++); break; case 1: // column B: #,##0 cell.setCellValue(value++); cell.setCellStyle(styles.get("#,##0.00")); break; case 2: // column C: $#,##0.00 cell.setCellValue(value++); cell.setCellStyle(styles.get("$#,##0.00")); break; case 3: // column D: red bold text on yellow background cell.setCellValue(address); cell.setCellStyle(styles.get("red-bold")); break; case 4: // column E: boolean // TODO booleans are shown as 1/0 instead of TRUE/FALSE cell.setCellValue(rowIndex % 2 == 0); break; case 5: // column F: date / time cell.setCellValue(calendar); cell.setCellStyle(styles.get("m/d/yyyy")); calendar.roll(Calendar.DAY_OF_YEAR, -1); break; case 6: // column F: formula // TODO formulas are not yet supported in SXSSF //cell.setCellFormula("SUM(A" + (rowIndex+1) + ":E" + (rowIndex+1)+ ")"); //break; default: cell.setCellValue(value++); break; } } rowIndexInSheet++; } if (saveFile) { String fileName = type + "_" + rows + "_" + cols + "." + getFileSuffix(args[0]); try { FileOutputStream out = new FileOutputStream(fileName); workBook.write(out); out.close(); } catch (IOException ioe) { System.err .println("Error: failed to write to file \"" + fileName + "\", reason=" + ioe.getMessage()); } } long timeFinished = System.currentTimeMillis(); System.out.println("Elapsed " + (timeFinished - timeStarted) / 1000 + " seconds"); } static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 14); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(headerFont); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put("header", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 12); monthFont.setColor(IndexedColors.RED.getIndex()); monthFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); styles.put("red-bold", style); String[] nfmt = { "#,##0.00", "$#,##0.00", "m/d/yyyy" }; for (String fmt : nfmt) { style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat(fmt)); styles.put(fmt, style); } return styles; } static void usage(String message) { System.err.println(message); System.err.println("usage: java SSPerformanceTest HSSF|XSSF|SXSSF rows cols saveFile (0|1)? "); System.exit(1); } static Workbook createWorkbook(String type) { if ("HSSF".equals(type)) return new HSSFWorkbook(); else if ("XSSF".equals(type)) return new XSSFWorkbook(); else if ("SXSSF".equals(type)) return new SXSSFWorkbook(); else usage("Unknown type \"" + type + "\""); return null; } static String getFileSuffix(String type) { if ("HSSF".equals(type)) return "xls"; else if ("XSSF".equals(type)) return "xlsx"; else if ("SXSSF".equals(type)) return "xlsx"; return null; } static int parseInt(String value, String msg) { try { return Integer.parseInt(value); } catch (NumberFormatException e) { usage(msg); } return 0; } }