List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook XSSFWorkbook
public XSSFWorkbook()
From source file:com.lufs.java.apache.poi.example.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); boolean xlsx = true; for (String arg : args) { if (arg.charAt(0) == '-') { xlsx = arg.equals("-xlsx"); } else {//w ww. j a va 2 s .c o m calendar.set(Calendar.YEAR, Integer.parseInt(arg)); } } int year = calendar.get(Calendar.YEAR); try (Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook()) { Map<String, CellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month Sheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); Cell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles Row monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); Cell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { Row row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { Cell dayCell_1 = row.createCell(i * 2); Cell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file String file = "calendar.xls"; if (wb instanceof XSSFWorkbook) file += "x"; try (FileOutputStream out = new FileOutputStream(file)) { wb.write(out); } } }
From source file:com.lwr.software.reporter.restservices.ReportExportService.java
License:Open Source License
public Response exportExcel(Report toExport, Set<ReportParameter> reportParams) { Workbook wb = new XSSFWorkbook(); Font boldFont = wb.createFont(); boldFont.setBold(true);/*from w w w . jav a2 s . com*/ CellStyle headerStyle = wb.createCellStyle(); headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBorderTop(BorderStyle.THIN); headerStyle.setBorderRight(BorderStyle.THIN); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setFillBackgroundColor(IndexedColors.BLUE.getIndex()); headerStyle.setFont(boldFont); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); CellStyle titleStyle = wb.createCellStyle(); titleStyle.setBorderBottom(BorderStyle.THIN); titleStyle.setBorderTop(BorderStyle.THIN); titleStyle.setBorderRight(BorderStyle.THIN); titleStyle.setBorderLeft(BorderStyle.THIN); List<RowElement> rows = toExport.getRows(); int sheetIndex = 0; for (RowElement rowElement : rows) { List<Element> elements = rowElement.getElements(); for (Element element : elements) { try { element.setParams(reportParams); element.init(); } catch (Exception e) { logger.error("Unable to init '" + element.getTitle() + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage(), e); return Response.serverError().entity("Unable to init '" + element.getTitle() + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage()).build(); } String sheetName = element.getTitle().substring(0, element.getTitle().length() > 30 ? 30 : element.getTitle().length()) + (sheetIndex++); Sheet sheet = wb.createSheet(sheetName); Row reportTitleRow = sheet.createRow(0); Cell reportTitleHeader = reportTitleRow.createCell(0); reportTitleHeader.setCellStyle(headerStyle); reportTitleHeader.setCellValue("Report Title:"); Cell reportTitleCell = reportTitleRow.createCell(1); reportTitleCell.setCellStyle(titleStyle); reportTitleCell.setCellValue(toExport.getTitle()); Row elementTitleRow = sheet.createRow(1); Cell elementTitleHeader = elementTitleRow.createCell(0); elementTitleHeader.setCellStyle(headerStyle); elementTitleHeader.setCellValue("Element Title:"); Cell elementTitleCell = elementTitleRow.createCell(1); elementTitleCell.setCellStyle(titleStyle); elementTitleCell.setCellValue(element.getTitle()); List<List<Object>> dataToExport = element.getData(); int rowIndex = 3; Row headerRow = sheet.createRow(rowIndex++); List<Object> unifiedHeaderRow = element.getHeader(); for (int i = 0; i < unifiedHeaderRow.size(); i++) { Cell headerCell = headerRow.createCell(i); String headerCellValue = unifiedHeaderRow.get(i).toString(); headerCell.setCellValue(headerCellValue); headerCell.setCellStyle(headerStyle); } for (int i = 0; i < dataToExport.size(); i++) { Row row = sheet.createRow(rowIndex++); List<Object> unifiedRow = dataToExport.get(i); int cellIndex = 0; for (Object cellValue : unifiedRow) { Cell cell = row.createCell(cellIndex); cell.setCellStyle(cellStyle); try { double val = Double.parseDouble(cellValue.toString()); cell.setCellValue(val); } catch (NumberFormatException e) { cell.setCellValue(cellValue.toString()); } cellIndex++; } } } } try { File file = new File(DashboardConstants.APPLN_TEMP_DIR + System.nanoTime()); logger.info("Export CSV temp file path is " + file.getAbsoluteFile()); wb.write(new FileOutputStream(file)); wb.close(); ResponseBuilder responseBuilder = Response.ok((Object) file); responseBuilder.header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); responseBuilder.header("Content-Transfer-Encoding", "binary"); responseBuilder.header("Content-Disposition", "attachment;filename=" + file.getName()); responseBuilder.header("Content-Length", file.length()); Response responseToSend = responseBuilder.build(); file.deleteOnExit(); return responseToSend; } catch (Exception e1) { return Response.serverError() .entity("Unable to export " + toExport.getTitle() + " report " + e1.getMessage()).build(); } }
From source file:com.md.mdcms.xlsx.CSVtoXLSX.java
License:Open Source License
/** * @param args/* w w w .ja va 2 s . co m*/ */ public static void main(String[] args) { // check if correct number of arguments were passed if (args.length != 23) { System.out.println("Invalid number of Parameters passed"); System.out.println("Parameters expected = 23"); System.out.println("Parameters passed = " + args.length); System.out.println("Expected parameters: "); System.out.println("1) csv file path"); System.out.println("2) excel file path"); System.out.println("3) Header1"); System.out.println("4) Header2"); System.out.println("5) Header3"); System.out.println("6) Header4"); System.out.println("7) Header5"); System.out.println("8) Header6"); System.out.println("9) Header7"); System.out.println("10) Header8"); System.out.println("11) Header9"); System.out.println("12) Footer1"); System.out.println("13) Footer2"); System.out.println("14) Footer3"); System.out.println("15) Footer4"); System.out.println("16) Footer5"); System.out.println("17) Footer6"); System.out.println("18) Field Types1"); System.out.println("19) Field Types2"); System.out.println("20) Field Types3"); System.out.println("21) Field Types4"); System.out.println("22) Date order"); System.out.println("23) Date Separator"); System.exit(1); } try { System.setProperty("java.awt.headless", "true"); File csvFile = new File(args[0]); File xlsFile = new File(args[1]); String dateOrder = args[21]; String dateSep = args[22]; // prep CSV String lineIn; BufferedReader br = new BufferedReader(new FileReader(csvFile)); // Workbook Settings Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("Sheet1"); // wb.setLocale(new Locale("en", "EN")); // WritableWorkbook workbook = Workbook.createWorkbook(xlsFile, ws); // WritableSheet sheet = workbook.createSheet("Table1", 0); // SheetSettings settings = new SheetSettings(sheet); // settings.setFitToPages(true); // settings.setPaperSize(PaperSize.A4); // settings.setOrientation(PageOrientation.LANDSCAPE); // set cell formats // arial9Format.setShrinkToFit(true); // arial9Format.setWrap(true); // arial9TotalIntegerFormat.setShrinkToFit(true); // arial9TotalIntegerFormat.setBorder(Border.TOP, BorderLineStyle.DOUBLE); // arial9TotalFloatFormat.setShrinkToFit(true); // arial9TotalFloatFormat.setBorder(Border.TOP, BorderLineStyle.DOUBLE); // colHeaderFormat.setBackground(Colour.GREY_25_PERCENT); // colHeaderFormat.setWrap(true); // colHeaderFormat.setShrinkToFit(true); // colHeaderFormat.setVerticalAlignment(VerticalAlignment.TOP); // floatFormat.setShrinkToFit(true); // floatFormat.setWrap(true); // integerFormat.setShrinkToFit(true); // integerFormat.setWrap(true); // headerFormat.setBackground(Colour.GREY_25_PERCENT); // headerFormat.setShrinkToFit(true); // footerFormat.setBackground(Colour.GREY_25_PERCENT); // footerFormat.setShrinkToFit(true); // cell(column, row) int colnr = 0; int rownr = 0; int firstHeaderRow = 0; // Headers String[] header = { args[2], args[3], args[4], args[5], args[6], args[7], args[8], args[9], args[10] }; int lastHeaderRow = firstHeaderRow; boolean headerFound = false; for (int j = 8; j > -1; j--) { if (!"".equals(header[j].trim()) || (headerFound)) { Label label = new Label(0, j + firstHeaderRow, header[j].replaceAll("\\s+$", ""), headerFormat); sheet.addCell(label); if (!headerFound) { headerFound = true; lastHeaderRow = j + firstHeaderRow; } } } // fill field type list String fieldTypes = args[17].trim() + args[18].trim() + args[19].trim() + args[20].trim(); String[] fieldType = fieldTypes.split(","); // table data int columnHeadingRow = lastHeaderRow; if (headerFound) { columnHeadingRow += 2; } rownr = columnHeadingRow; int firstDataRow = 0; int lastDataRow = 0; String[] char13 = { "m", "w", "A", "B", "C", "D", "E", "G", "H", "K", "M", "N", "O", "P", "Q", "R", "S", "U", "V", "W" }; double factor = 1.0; int width; double w; double charWidth; String value; // loop through CSV lines lineIn = br.readLine(); while (lineIn != null && !"".equals(lineIn)) { String[] fields = lineIn.split("\t"); // loop through columns in line for (int i = 0; (i < fields.length && i < fieldType.length); i++) { if (!fieldType[i].substring(0, 1).equals("E")) { value = fields[i]; value = value.replaceAll("\"", "").trim(); // column heading if (rownr == columnHeadingRow) { factor = 1.3; Label label = new Label(colnr, rownr, value, colHeaderFormat); sheet.addCell(label); } // column data else { factor = 1; if (firstDataRow == 0) { firstDataRow = rownr; } lastDataRow = rownr; // date field if (fieldType[i].equals("D")) { if (value.length() == 6) { if (dateOrder.equals("DMY")) { value = value.substring(4, 6) + dateSep + value.substring(2, 4) + dateSep + value.substring(0, 2); } else { if (dateOrder.equals("MDY")) { value = value.substring(2, 4) + dateSep + value.substring(4, 6) + dateSep + value.substring(0, 2); } else { value = value.substring(0, 2) + dateSep + value.substring(2, 4) + dateSep + value.substring(4, 6); } } } if (value.length() == 8) { if (dateOrder.equals("DMY")) { value = value.substring(6, 8) + dateSep + value.substring(4, 6) + dateSep + value.substring(0, 4); } else { if (dateOrder.equals("MDY")) { value = value.substring(2, 4) + dateSep + value.substring(4, 6) + dateSep + value.substring(0, 4); } else { value = value.substring(0, 4) + dateSep + value.substring(4, 6) + dateSep + value.substring(6, 8); } } } Label label = new Label(colnr, rownr, value, arial9Format); sheet.addCell(label); } // floating point field if (fieldType[i].substring(0, 1).equals("F")) { try { double doubleValue = Double.valueOf(value).doubleValue(); Number number = new Number(colnr, rownr, doubleValue, floatFormat); sheet.addCell(number); } catch (Exception e) { Label label = new Label(colnr, rownr, value, arial9Format); sheet.addCell(label); } } // integer field if (fieldType[i].substring(0, 1).equals("I")) { try { int integerValue = Integer.valueOf(value).intValue(); Number number = new Number(colnr, rownr, integerValue, integerFormat); sheet.addCell(number); } catch (Exception e) { Label label = new Label(colnr, rownr, value, arial9Format); sheet.addCell(label); } } // string field if (fieldType[i].equals("S")) { Label label = new Label(colnr, rownr, value, arial9Format); sheet.addCell(label); } } // calculate cell width and add column number w = 1; for (int j = 0; j < value.length(); j++) { charWidth = 1; for (int k = 0; k < char13.length; k++) { if (char13[k].equals(value.substring(j, j + 1))) { charWidth = 1.3; k = char13.length; } } w = w + (charWidth * factor); } width = Double.valueOf(String.valueOf(w)).intValue(); if (width > 80) { width = 80; } concludeColumnWidth(colnr, width); colnr++; } } lineIn = br.readLine(); colnr = 0; rownr++; } for (Iterator iterator = columnWidth.keySet().iterator(); iterator.hasNext();) { Integer col = (Integer) iterator.next(); sheet.setColumnView(col.intValue(), ((Integer) columnWidth.get(col)).intValue()); } // total row colnr = 0; int columnCount = 5; for (int i = 0; i < fieldType.length; i++) { if (!fieldType[i].substring(0, 1).equals("E")) { if (fieldType[i].length() > 1) { if (fieldType[i].substring(1, 2).equals("T")) { String firstCell; String lastCell; firstCell = CellReferenceHelper.getCellReference(colnr, firstDataRow); lastCell = CellReferenceHelper.getCellReference(colnr, lastDataRow); value = "SUM(" + firstCell + ":" + lastCell + ")"; if (fieldType[i].substring(0, 1).equals("F")) { Formula formula = new Formula(colnr, rownr, value, arial9TotalFloatFormat); sheet.addCell(formula); } else { Formula formula = new Formula(colnr, rownr, value, arial9TotalIntegerFormat); sheet.addCell(formula); } } } colnr++; if (colnr > columnCount) { columnCount = colnr; } } } // merge the header cells if (headerFound) { for (int i = firstHeaderRow; i <= lastHeaderRow; i++) { sheet.mergeCells(0, i, columnCount - 1, i); } } // Footers String[] footer = { args[11], args[12], args[13], args[14], args[15], args[16] }; boolean footerFound = false; rownr++; for (int j = 5; j > -1; j--) { if (!"".equals(footer[j].trim()) || (footerFound)) { Label label = new Label(0, rownr + j, footer[j].replaceAll("\\s+$", ""), footerFormat); sheet.addCell(label); sheet.mergeCells(0, rownr + j, columnCount - 1, rownr + j); footerFound = true; } } // write workbook to file if (xlsFile.exists()) { xlsFile.delete(); } FileOutputStream fileOut = new FileOutputStream(xlsFile); wb.write(fileOut); fileOut.close(); } catch (UnsupportedEncodingException e) { System.out.println(e.toString()); System.exit(1); } catch (IOException e) { System.out.println(e.toString()); System.exit(1); } catch (Exception e) { System.out.println(e.toString()); System.exit(1); } }
From source file:com.mechatronika.trackmchtr.ExportToExcel.java
private static void writeToExcel(String path) throws FileNotFoundException, IOException { new WorkbookFactory(); Workbook wb = new XSSFWorkbook(); //Excell workbook Sheet sheet = wb.createSheet(); //WorkSheet //wb.createSheet("sheetName"); Row row = sheet.createRow(2); //Row created at line 3 TableModel model = table.getModel(); //Table model Row headerRow = sheet.createRow(0); //Create row at line 0 for (int headings = 0; headings < model.getColumnCount(); headings++) { //For each column headerRow.createCell(headings).setCellValue(model.getColumnName(headings));//Write column name }//from w w w . j a va 2s . com for (int rows = 0; rows < model.getRowCount(); rows++) { //For each table row for (int cols = 0; cols < table.getColumnCount(); cols++) { //For each table column row.createCell(cols).setCellValue(model.getValueAt(rows, cols).toString()); //Write value } //Set the row to the next one in the sequence row = sheet.createRow((rows + 3)); } wb.write(new FileOutputStream(path));//Save the file IJ.showMessage("Excel file created!"); }
From source file:com.mimp.controllers.reporte.java
@RequestMapping("/Reportes/OrganismosAcreditados") public void ReporteOrganismo(ModelMap map, HttpSession session, HttpServletResponse response) { Personal usuario = (Personal) session.getAttribute("usuario"); Workbook wb = new XSSFWorkbook(); try {//from w ww . j a va2 s. c o m //Se llama a la plantilla localizada en la ruta // InputStream inp = new FileInputStream("C:\\Plantillas\\OrgAcred.xlsx"); InputStream inp = new FileInputStream("/opt/Plantillas/OrgAcred.xlsx"); wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); //Aqu va el query que consigue los datos de la tabla //ArrayList<Organismo> listaorg = ServicioPersonal.ListaOrganismos(); ArrayList<Organismo> listaorg = ServicioReporte.ReporteOrganismo2(); int i = 1; for (Organismo org : listaorg) { Row row = sheet.createRow(i); Cell cell = row.createCell(0); cell.setCellValue(i); cell = row.createCell(1); cell.setCellValue(org.getEntidad().getNombre()); cell = row.createCell(2); cell.setCellValue(org.getCompetencia()); cell = row.createCell(3); cell.setCellValue(org.getEntidad().getResolAuto()); cell = row.createCell(4); String fechaVenc = ""; try { fechaVenc = format.dateToString(org.getEntidad().getFechaVenc()); } catch (Exception ex) { } cell.setCellValue(fechaVenc); cell = row.createCell(5); for (Iterator iter = org.getRepresentantes().iterator(); iter.hasNext();) { Representante rep = (Representante) iter.next(); cell.setCellValue(rep.getNombre() + " " + rep.getApellidoP()); } cell = row.createCell(6); cell.setCellValue(org.getEntidad().getObs()); i++; } } catch (Exception e) { //e.printStackTrace(); } try { response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-Disposition", "attachment; filename=Registro del nmero Organismos Acreditados.xlsx"); OutputStream fileOut = response.getOutputStream(); wb.write(fileOut); fileOut.flush(); fileOut.close(); } catch (Exception ex) { //ex.printStackTrace(); } String mensaje_log = "El usuario: " + usuario.getNombre() + " " + usuario.getApellidoP() + " con ID: " + usuario.getIdpersonal() + ". Descarg el Reporte 'Organismos Acreditados' "; String Tipo_registro = "Personal"; try { String Numero_registro = String.valueOf(usuario.getIdpersonal()); ServicioPersonal.InsertLog(usuario, Tipo_registro, Numero_registro, mensaje_log); } catch (Exception ex) { } }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
private Workbook(File excelFile, SpreadsheetVersion version) { switch (version) { case EXCEL97: this.workbook = new HSSFWorkbook(); break;/* w w w .j a v a 2s . c o m*/ case EXCEL2007: this.workbook = new XSSFWorkbook(); break; default: throw new IllegalArgumentException("Spreadsheet version not supported!"); } this.excelFile = excelFile; initDefaultDataFormats(); initDefaultStyles(); }