List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet
Sheet createSheet(String sheetname);
From source file:com.suomi.carinsurance.export.excel.ExcelService.java
License:Open Source License
/** * /*from w ww . j ava2s .co m*/ * * @param outputStream ? * @param config ? * @param data ? * @param elementClass */ public <T> void export(OutputStream outputStream, ExcelWorkbook config, List<T> data, Class<T> elementClass) { if (outputStream == null) { throw new RuntimeException("Excel outputStream is null"); } if (config == null) { throw new RuntimeException("Excel config is null"); } if (data == null) { return; } try { // ? Workbook workbook = new HSSFWorkbook(); // ? List<ExcelSheet> sheetConfigList = config.getSheetList(); if (sheetConfigList != null && sheetConfigList.size() > 0) { for (ExcelSheet sheetConfig : sheetConfigList) { Sheet sheet = workbook.createSheet(sheetConfig.getName()); // ? List<ExcelField> fieldConfigList = sheetConfig.getFieldList(); if (fieldConfigList != null && fieldConfigList.size() > 0) { int rowIndex = 0; int colIndex = 0; List<String> javaFieldList = new ArrayList<String>(); // ? Row titleRow = sheet.createRow(rowIndex); for (ExcelField fieldConfig : fieldConfigList) { if (fieldConfig != null && fieldConfig.isExport() //&& StringUtil.isNotBlank(fieldConfig.getExcelName()) && StringUtil.isNotBlank(fieldConfig.getJavaName())) { String excelName = fieldConfig.getExcelName(); if (StringUtil.isBlank(excelName)) { Field field = ReflectUtil.getField(elementClass, fieldConfig.getJavaName()); com.suomi.carinsurance.annotation.ExcelField excelField = field .getAnnotation(com.suomi.carinsurance.annotation.ExcelField.class); excelName = excelField.name(); } if (StringUtil.isBlank(excelName)) { continue; } titleRow.createCell(colIndex).setCellValue(excelName); javaFieldList.add(fieldConfig.getJavaName()); colIndex++; } } // ?? if (data.size() < 1) { break; } for (T item : data) { if (item == null) { continue; } rowIndex++; colIndex = 0; Row dataRow = sheet.createRow(rowIndex); for (String javaField : javaFieldList) { Cell dataCell = dataRow.createCell(colIndex); Object fieldValue = ReflectUtil.getFieldValue(item, javaField); if (fieldValue instanceof Boolean) { dataCell.setCellValue((boolean) fieldValue); } else if (fieldValue instanceof Number) { dataCell.setCellValue(((Number) fieldValue).doubleValue()); } else if (fieldValue instanceof BigDecimal) { dataCell.setCellValue(((BigDecimal) fieldValue).doubleValue()); } else if (fieldValue instanceof Date) { dataCell.setCellValue((Date) fieldValue); } else if (fieldValue instanceof Calendar) { dataCell.setCellValue((Calendar) fieldValue); } else if (fieldValue instanceof String) { dataCell.setCellValue((String) fieldValue); } else { dataCell.setCellValue(""); } colIndex++; } } } } } workbook.write(outputStream); } catch (Exception e) { throw new RuntimeException(e); } finally { IOUtil.close(outputStream); } }
From source file:com.toba.bll.admin.ReportsDownloadServlet.java
/** * Handles the HTTP <code>GET</code> method. * * @param request servlet request/*from w ww. j a v a2 s.c om*/ * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("Users Registered in the Past Month"); Row row = sheet.createRow(0); row.createCell(0).setCellValue("User Name"); row.createCell(1).setCellValue("First Name"); row.createCell(2).setCellValue("Last Name"); row.createCell(3).setCellValue("Registration Date"); Calendar calendar = Calendar.getInstance(); calendar.add(Calendar.MONTH, -1); Date oneMonthAgo = calendar.getTime(); List<User> usersRegisteredInThePastMonth = UserDB.selectUsersRegisteredAfter(oneMonthAgo); HSSFCellStyle dateCellStyle = (HSSFCellStyle) workbook.createCellStyle(); short dateDataFormat = workbook.createDataFormat().getFormat("dd/MM/yyyy"); dateCellStyle.setDataFormat(dateDataFormat); for (int i = 0; i < usersRegisteredInThePastMonth.size(); i++) { User user = usersRegisteredInThePastMonth.get(i); row = sheet.createRow(1 + i); row.createCell(0).setCellValue(user.getUserName()); row.createCell(1).setCellValue(user.getFirstName()); row.createCell(2).setCellValue(user.getLastName()); row.createCell(3).setCellValue(user.getRegistrationDate()); row.getCell(3).setCellStyle(dateCellStyle); } workbook.write(response.getOutputStream()); workbook.close(); response.setHeader("content-disposition", "attachment; filename=users.xls"); response.setHeader("cache-control", "no-cache"); }
From source file:com.toolsverse.etl.connector.excel.ExcelConnector.java
License:Open Source License
@SuppressWarnings("resource") public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception { String fileName = null;//from w ww. j a va2 s . co m OutputStream out = null; if (params.getOutputStream() == null) { fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName( dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xls", true)); params.setRealFileName(fileName); out = new FileOutputStream(fileName); if (params.getTransactionMonitor() != null) params.getTransactionMonitor().addFile(fileName); } else out = params.getOutputStream(); params.setOut(out); Workbook workbook = new HSSFWorkbook(); params.setWorkbook(workbook); Sheet sheet = workbook .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName()); params.setSheet(sheet); Font labelFont = workbook.createFont(); labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle labelCellStyle = workbook.createCellStyle(); labelCellStyle.setFont(labelFont); DataFormat dateTimeFormat = workbook.createDataFormat(); CellStyle dateTimeCellStyle = workbook.createCellStyle(); dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat())); params.setDateTimeCellStyle(dateTimeCellStyle); DataFormat dateFormat = workbook.createDataFormat(); CellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat())); params.setDateCellStyle(dateCellStyle); DataFormat timeFormat = workbook.createDataFormat(); CellStyle timeCellStyle = workbook.createCellStyle(); timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat())); params.setTimeCellStyle(timeCellStyle); // column names Row excelRow = sheet.createRow(0); // metadata int col = 0; for (FieldDef fieldDef : dataSet.getFields().getList()) { if (!fieldDef.isVisible()) continue; Cell labelCell = excelRow.createCell(col++, Cell.CELL_TYPE_STRING); labelCell.setCellStyle(labelCellStyle); labelCell.setCellValue(fieldDef.getName()); } params.setPrePersistOccured(true); }
From source file:com.toolsverse.etl.connector.excel.ExcelXlsxConnector.java
License:Open Source License
@SuppressWarnings("resource") public void prePersist(ExcelConnectorParams params, DataSet dataSet, Driver driver) throws Exception { String fileName = null;/*w w w. ja va 2 s . c o m*/ OutputStream out = null; if (params.getOutputStream() == null) { fileName = SystemConfig.instance().getPathUsingAppFolders(params.getFileName( dataSet.getOwnerName() != null ? dataSet.getOwnerName() : dataSet.getName(), ".xlsx", true)); params.setRealFileName(fileName); out = new FileOutputStream(fileName); if (params.getTransactionMonitor() != null) params.getTransactionMonitor().addFile(fileName); } else out = params.getOutputStream(); params.setOut(out); Workbook workbook = new SXSSFWorkbook(100); params.setWorkbook(workbook); Sheet sheet = workbook .createSheet(Utils.isNothing(params.getSheetName()) ? dataSet.getName() : params.getSheetName()); params.setSheet(sheet); Font labelFont = workbook.createFont(); labelFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle labelCellStyle = workbook.createCellStyle(); labelCellStyle.setFont(labelFont); DataFormat dateTimeFormat = workbook.createDataFormat(); CellStyle dateTimeCellStyle = workbook.createCellStyle(); dateTimeCellStyle.setDataFormat(dateTimeFormat.getFormat(params.getDateTimeFormat())); params.setDateTimeCellStyle(dateTimeCellStyle); DataFormat dateFormat = workbook.createDataFormat(); CellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat(params.getDateFormat())); params.setDateCellStyle(dateCellStyle); DataFormat timeFormat = workbook.createDataFormat(); CellStyle timeCellStyle = workbook.createCellStyle(); timeCellStyle.setDataFormat(timeFormat.getFormat(params.getTimeFormat())); params.setTimeCellStyle(timeCellStyle); // column names Row excelRow = sheet.createRow(0); // metadata int col = 0; for (FieldDef fieldDef : dataSet.getFields().getList()) { if (!fieldDef.isVisible()) continue; Cell labelCell = excelRow.createCell(col++); labelCell.setCellStyle(labelCellStyle); labelCell.setCellValue(fieldDef.getName()); } params.setPrePersistOccured(true); }
From source file:com.tremolosecurity.scale.ui.reports.GenerateSpreadsheet.java
License:Apache License
@Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { resp.setHeader("Cache-Control", "private, no-store, no-cache, must-revalidate"); resp.setHeader("Pragma", "no-cache"); ReportViewer scaleReport = (ReportViewer) req.getSession().getAttribute("scaleReportCached"); Workbook wb = new XSSFWorkbook(); Font font = wb.createFont();//ww w.j a v a2s . com font.setBold(true); Font titleFont = wb.createFont(); titleFont.setBold(true); titleFont.setFontHeightInPoints((short) 16); Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(scaleReport.getReportInfo().getName())); //Create a header Row row = sheet.createRow(0); Cell cell = row.createCell(0); RichTextString title = new XSSFRichTextString(scaleReport.getReportInfo().getName()); title.applyFont(titleFont); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); cell.setCellValue(title); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue(scaleReport.getReportInfo().getDescription()); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3)); row = sheet.createRow(2); cell = row.createCell(0); cell.setCellValue(scaleReport.getRunDateTime()); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3)); row = sheet.createRow(3); int rowNum = 4; if (scaleReport.getResults().getGrouping().isEmpty()) { row = sheet.createRow(rowNum); cell = row.createCell(0); cell.setCellValue("There is no data for this report"); } else { for (ReportGrouping group : scaleReport.getResults().getGrouping()) { for (String colHeader : scaleReport.getResults().getHeaderFields()) { row = sheet.createRow(rowNum); cell = row.createCell(0); RichTextString rcolHeader = new XSSFRichTextString(colHeader); rcolHeader.applyFont(font); cell.setCellValue(rcolHeader); cell = row.createCell(1); cell.setCellValue(group.getHeader().get(colHeader)); rowNum++; } row = sheet.createRow(rowNum); int cellNum = 0; for (String colHeader : scaleReport.getResults().getDataFields()) { cell = row.createCell(cellNum); RichTextString rcolHeader = new XSSFRichTextString(colHeader); rcolHeader.applyFont(font); cell.setCellValue(rcolHeader); cellNum++; } rowNum++; for (Map<String, String> dataRow : group.getData()) { cellNum = 0; row = sheet.createRow(rowNum); for (String colHeader : scaleReport.getResults().getDataFields()) { cell = row.createCell(cellNum); cell.setCellValue(dataRow.get(colHeader)); cellNum++; } rowNum++; } row = sheet.createRow(rowNum); rowNum++; } } resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); wb.write(resp.getOutputStream()); }
From source file:com.tremolosecurity.scalejs.ws.ScaleMain.java
License:Apache License
private void exportToExcel(HttpFilterRequest request, HttpFilterResponse response, Gson gson) throws IOException { int lastslash = request.getRequestURI().lastIndexOf('/'); int secondlastslash = request.getRequestURI().lastIndexOf('/', lastslash - 1); String id = request.getRequestURI().substring(secondlastslash + 1, lastslash); ReportResults res = (ReportResults) request.getSession().getAttribute(id); if (res == null) { response.setStatus(404);/*from w w w .j av a 2 s . c om*/ ScaleError error = new ScaleError(); error.getErrors().add("Report no longer available"); ScaleJSUtils.addCacheHeaders(response); response.getWriter().print(gson.toJson(error).trim()); response.getWriter().flush(); } else { response.setHeader("Cache-Control", "private, no-store, no-cache, must-revalidate"); response.setHeader("Pragma", "no-cache"); Workbook wb = new XSSFWorkbook(); Font font = wb.createFont(); font.setBold(true); Font titleFont = wb.createFont(); titleFont.setBold(true); titleFont.setFontHeightInPoints((short) 16); Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(res.getName())); //Create a header Row row = sheet.createRow(0); Cell cell = row.createCell(0); RichTextString title = new XSSFRichTextString(res.getName()); title.applyFont(titleFont); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); cell.setCellValue(title); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue(res.getDescription()); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3)); row = sheet.createRow(2); cell = row.createCell(0); //cell.setCellValue(new DateTime().toString("MMMM Do, YYYY h:mm:ss a")); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3)); row = sheet.createRow(3); int rowNum = 4; if (res.getGrouping().isEmpty()) { row = sheet.createRow(rowNum); cell = row.createCell(0); cell.setCellValue("There is no data for this report"); } else { for (ReportGrouping group : res.getGrouping()) { for (String colHeader : res.getHeaderFields()) { row = sheet.createRow(rowNum); cell = row.createCell(0); RichTextString rcolHeader = new XSSFRichTextString(colHeader); rcolHeader.applyFont(font); cell.setCellValue(rcolHeader); cell = row.createCell(1); cell.setCellValue(group.getHeader().get(colHeader)); rowNum++; } row = sheet.createRow(rowNum); int cellNum = 0; for (String colHeader : res.getDataFields()) { cell = row.createCell(cellNum); RichTextString rcolHeader = new XSSFRichTextString(colHeader); rcolHeader.applyFont(font); cell.setCellValue(rcolHeader); cellNum++; } rowNum++; for (Map<String, String> dataRow : group.getData()) { cellNum = 0; row = sheet.createRow(rowNum); for (String colHeader : res.getDataFields()) { cell = row.createCell(cellNum); cell.setCellValue(dataRow.get(colHeader)); cellNum++; } rowNum++; } row = sheet.createRow(rowNum); rowNum++; } } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); wb.write(response.getOutputStream()); } }
From source file:com.upbest.utils.AddDimensionedImage.java
License:Apache License
/** * The main entry point to the program. It contains code that demonstrates * one way to use the program.//from ww w . j a v a2 s. c om * * Note, the code is not restricted to use on new workbooks only. If an * image is to be inserted into an existing workbook. just open that * workbook, gat a reference to a sheet and pass that; * * AddDimensionedImage addImage = new AddDimensionedImage(); * * File file = new File("....... Existing Workbook ......."); * FileInputStream fis = new FileInputStream(file); * Workbook workbook = new HSSFWorkbook(fis); * HSSFSheet sheet = workbook.getSheetAt(0); * addImage.addImageToSheet("C3", sheet, "image.jpg", 30, 20, * AddDimensionedImage.EXPAND.ROW); * * @param args the command line arguments */ public static void main(String[] args) { String imageFile = null; String outputFile = null; FileOutputStream fos = null; Workbook workbook = null; Sheet sheet = null; args = new String[] { "c:/logo1.jpg", "c:/poi/image.xlsx" }; try { if (args.length < 2) { System.err.println("Usage: AddDimensionedImage imageFile outputFile"); return; } workbook = new XSSFWorkbook(); // OR XSSFWorkbook sheet = workbook.createSheet("Picture Test"); imageFile = args[0]; outputFile = args[1]; new AddDimensionedImage().addImageToSheet("B5", sheet, sheet.createDrawingPatriarch(), new File(imageFile).toURI().toURL(), 100, 40, AddDimensionedImage.EXPAND_ROW_AND_COLUMN); fos = new FileOutputStream(outputFile); workbook.write(fos); } catch (FileNotFoundException fnfEx) { System.out.println("Caught an: " + fnfEx.getClass().getName()); System.out.println("Message: " + fnfEx.getMessage()); System.out.println("Stacktrace follows..........."); fnfEx.printStackTrace(System.out); } catch (IOException ioEx) { System.out.println("Caught an: " + ioEx.getClass().getName()); System.out.println("Message: " + ioEx.getMessage()); System.out.println("Stacktrace follows..........."); ioEx.printStackTrace(System.out); } finally { if (fos != null) { try { fos.close(); fos = null; } catch (IOException ioEx) { // I G N O R E } } } }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java
License:Open Source License
/** * Adds a new sheet to the given Spreadsheet and Workbook. * * @param spreadsheet//from w w w . j a va 2s .co m * Target Spreadsheet * @param workbook * Target Workbook * @param sheetName * Name of the new sheet * @param rows * Row count for the new sheet * @param columns * Column count for the new sheet */ static void addNewSheet(final Spreadsheet spreadsheet, final Workbook workbook, final String sheetName, int rows, int columns) { final Sheet sheet; if (sheetName == null) { sheet = createNewSheet(workbook); } else { sheet = workbook.createSheet(sheetName); } int sheetIndex = workbook.getSheetIndex(sheet); workbook.setActiveSheet(sheetIndex); spreadsheet.reloadActiveSheetData(); spreadsheet.reloadActiveSheetStyles(); final SpreadsheetState state = spreadsheet.getState(); int[] verticalScrollPositions = Arrays.copyOf(state.verticalScrollPositions, state.sheetNames.length); int[] horizontalScrollPositions = Arrays.copyOf(state.horizontalScrollPositions, state.sheetNames.length); state.verticalScrollPositions = verticalScrollPositions; state.horizontalScrollPositions = horizontalScrollPositions; generateNewSpreadsheet(spreadsheet, sheet, rows, columns); }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java
License:Open Source License
private static Sheet createNewSheet(Workbook workbook) { int idx = workbook.getNumberOfSheets() + 1; String sheetname = "Sheet" + idx; while (workbook.getSheet(sheetname) != null) { idx++;/*from ww w . j av a 2s . c om*/ sheetname = "Sheet" + idx; } return workbook.createSheet(sheetname); }
From source file:com.vincestyling.apkinfoextractor.core.export.ExportToExcel.java
License:Apache License
@Override public void export() throws Exception { Workbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet(Constancts.APP_NAME); sheet.setHorizontallyCenter(true);// ww w . j a v a 2s. c o m sheet.setFitToPage(true); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue( " File generated by ApkInfoExtractor (https://github.com/vince-styling/ApkInfoExtractor), Copyright (C) 2014 Vince Styling"); titleCell.setCellStyle(styles.get("title")); Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); int cellNum = 0; String[] fields = solution.getExtractFields().split(","); for (String field : fields) { if (field.equals(Constancts.ICON)) continue; Cell headerCell = headerRow.createCell(cellNum); headerCell.setCellValue(field); headerCell.setCellStyle(styles.get("header")); sheet.setColumnWidth(cellNum, ApkInfo.getFieldCharacterCount(field) * 256); cellNum++; } int rowNum = 2; for (int i = 0; i < solution.getResultCount(); i++) { ResultDataProvider provider = solution.getResultList().get(i); postProgress(i + 1); cellNum = 0; Row row = sheet.createRow(rowNum++); for (String field : fields) { if (field.equals(Constancts.ICON)) continue; Cell cell = row.createCell(cellNum); cell.setCellStyle(styles.get("cell")); String value = getFieldValue(provider.getApkInfo(), field); cell.setCellValue(value); cellNum++; } row.setHeight((short) (5 * 256)); } File outputFile = new File(solution.getWorkingFolder(), solution.generateOutputFileName() + ".xls"); FileOutputStream out = new FileOutputStream(outputFile); wb.write(out); out.close(); callback.onProcessSuccess(outputFile); }