List of usage examples for org.apache.poi.ss.usermodel PrintSetup setFitHeight
void setFitHeight(short height);
From source file:Demos.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); boolean xlsx = true; for (int i = 0; i < args.length; i++) { if (args[i].charAt(0) == '-') { xlsx = args[i].equals("-xlsx"); } else {/*from ww w . j a va2 s. c o m*/ calendar.set(Calendar.YEAR, Integer.parseInt(args[i])); } } int year = calendar.get(Calendar.YEAR); 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 = "C:\\Users\\BaldiniHP\\Desktop\\calendar.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:Documentos.ClaseAlmacenGeneral.java
public void crearExcel() { try {//from w w w . j a v a 2 s . co m // Defino el Libro de Excel HSSFWorkbook wb = new HSSFWorkbook(); // Creo la Hoja en Excel Sheet sheet1 = wb.createSheet("Productos"); Sheet sheet2 = wb.createSheet("hoja2"); Sheet sheet3 = wb.createSheet("hoja3"); // quito las lineas del libro para darle un mejor acabado // sheet.setDisplayGridlines(false); sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); // creo una nueva fila Row trow = sheet1.createRow((short) 0); createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Productos de Almacn-Repostera AnaIS " + ControllerFechas.getFechaActual()); // Creo la cabecera de mi listado en Excel Row row = sheet1.createRow((short) 2); createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true, true); createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true); createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true); createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true); createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia minma", true, true); Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", ""); try ( // Creamos un Statement para poder hacer peticiones a la bd Statement stat = con.createStatement()) { ResultSet resultado = stat.executeQuery( "select idProducto, nombre, cantidad,UnidadExistencia,minStock where tipoProducto=2 from producto"); while (resultado.next()) { //creamos la fila Row fila = sheet1.createRow(3 + i); String idProducto = String.valueOf(resultado.getString("idProducto")); String nombre = String.valueOf(resultado.getString("nombre")); String cantidad = String.valueOf(resultado.getInt("cantidad")); String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia")); String minStock = String.valueOf(resultado.getInt("minStock")); // Creo las celdas de mi fila, se puede poner un diseo a la celda System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - " + UnidadExistencia + " - " + minStock); creandoCelda(wb, fila, 0, idProducto); creandoCelda(wb, fila, 1, nombre); creandoCelda(wb, fila, 2, cantidad); creandoCelda(wb, fila, 3, UnidadExistencia); creandoCelda(wb, fila, 4, minStock); i++; } } con.close(); // Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que // la celda se acomode segn su tamao Sheet ssheet = wb.getSheetAt(0); ssheet.autoSizeColumn(0); ssheet.autoSizeColumn(1); ssheet.autoSizeColumn(2); ssheet.autoSizeColumn(3); ssheet.autoSizeColumn(4); ssheet.autoSizeColumn(5); ssheet.autoSizeColumn(6); ssheet.autoSizeColumn(7); //Ajustando la hoja de una pagina Sheet sheet = wb.createSheet("format sheet"); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); //Area de impresion wb.setPrintArea(0, 0, 1, 0, 9); String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports" + System.getProperty("file.separator") + "Almacen" + ControllerFechas.getFechaActual() + ".xls"; //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls"; try (FileOutputStream fileOut = new FileOutputStream(strRuta)) { wb.write(fileOut); } JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta); } catch (Exception e) { e.printStackTrace(); //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo"); } }
From source file:Documentos.ClaseAlmacenProducto.java
public void crearExcel() { try {//from w ww .ja v a 2 s .c om // Defino el Libro de Excel HSSFWorkbook wb = new HSSFWorkbook(); // Creo la Hoja en Excel Sheet sheet1 = wb.createSheet("Productos"); Sheet sheet2 = wb.createSheet("hoja2"); Sheet sheet3 = wb.createSheet("hoja3"); // quito las lineas del libro para darle un mejor acabado // sheet.setDisplayGridlines(false); sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); // creo una nueva fila Row trow = sheet1.createRow((short) 0); createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Productos existentes-Repostera AnaIS " + ControllerFechas.getFechaActual()); // Creo la cabecera de mi listado en Excel Row row = sheet1.createRow((short) 2); createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true, true); createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true); createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true); createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Precio de Venta", true, true); createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Ganancia", true, true); createCell(wb, row, 5, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true); Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", ""); try ( // Creamos un Statement para poder hacer peticiones a la bd Statement stat = con.createStatement()) { ResultSet resultado = stat.executeQuery( "select idproducto,nombre,format(cantidad,0) as cantidad,preciocompra,precioVenta,concat('$ ',format(((precioventa-preciocompra)*cantidad),2)) as ganancia, UnidadExistencia from producto where tipoProducto = 3;"); while (resultado.next()) { //creamos la fila Row fila = sheet1.createRow(3 + i); String idProducto = String.valueOf(resultado.getString("idProducto")); String nombre = String.valueOf(resultado.getString("nombre")); String cantidad = String.valueOf(resultado.getInt("cantidad")); String precioVenta = String.valueOf(resultado.getString("precioVenta")); String ganancia = String.valueOf(resultado.getString("ganancia")); String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia")); //String Image = String.valueOf(resultado.getBlob("Image")); // Creo las celdas de mi fila, se puede poner un diseo a la celda System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - " + precioVenta + " - " + ganancia + " - " + UnidadExistencia); creandoCelda(wb, fila, 0, idProducto); creandoCelda(wb, fila, 1, nombre); creandoCelda(wb, fila, 2, cantidad); creandoCelda(wb, fila, 3, precioVenta); creandoCelda(wb, fila, 4, ganancia); creandoCelda(wb, fila, 5, UnidadExistencia); //creandoCelda(wb, fila, 5, Image); i++; } } con.close(); // Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que // la celda se acomode segn su tamao Sheet ssheet = wb.getSheetAt(0); ssheet.autoSizeColumn(0); ssheet.autoSizeColumn(1); ssheet.autoSizeColumn(2); ssheet.autoSizeColumn(3); ssheet.autoSizeColumn(4); ssheet.autoSizeColumn(5); ssheet.autoSizeColumn(6); ssheet.autoSizeColumn(7); //Ajustando la hoja de una pagina Sheet sheet = wb.createSheet("format sheet"); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); //Area de impresion wb.setPrintArea(0, 0, 1, 0, 9); String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports" + System.getProperty("file.separator") + "Productos" + ControllerFechas.getFechaActual() + ".xls"; //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls"; try (FileOutputStream fileOut = new FileOutputStream(strRuta)) { wb.write(fileOut); } JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta); } catch (Exception e) { e.printStackTrace(); //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo"); } }
From source file:Documentos.ClaseAlmacenXLS.java
public void crearExcel() { try {/*from w ww. j a v a2 s . c om*/ // Defino el Libro de Excel HSSFWorkbook wb = new HSSFWorkbook(); // Creo la Hoja en Excel Sheet sheet1 = wb.createSheet("Productos"); Sheet sheet2 = wb.createSheet("hoja2"); Sheet sheet3 = wb.createSheet("hoja3"); // quito las lineas del libro para darle un mejor acabado // sheet.setDisplayGridlines(false); sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); // creo una nueva fila Row trow = sheet1.createRow((short) 0); createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Productos de Almacn-Repostera AnaIS " + ControllerFechas.getFechaActual()); // Creo la cabecera de mi listado en Excel Row row = sheet1.createRow((short) 2); createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cdigo de producto", true, true); createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Nombre", true, true); createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Cantidad", true, true); createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia", true, true); createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "Existencia minma", true, true); Class.forName("com.mysql.jdbc.Driver"); con = DriverManager.getConnection("jdbc:mysql://localhost/poscakeapp", "root", ""); try ( // Creamos un Statement para poder hacer peticiones a la bd Statement stat = con.createStatement()) { ResultSet resultado = stat.executeQuery( "select idProducto, nombre, cantidad,UnidadExistencia,minStock from producto where tipoProducto=2 "); while (resultado.next()) { //creamos la fila Row fila = sheet1.createRow(3 + i); String idProducto = String.valueOf(resultado.getString("idProducto")); String nombre = String.valueOf(resultado.getString("nombre")); String cantidad = String.valueOf(resultado.getInt("cantidad")); String UnidadExistencia = String.valueOf(resultado.getInt("UnidadExistencia")); String minStock = String.valueOf(resultado.getInt("minStock")); // Creo las celdas de mi fila, se puede poner un diseo a la celda System.out.println(i + " /// " + idProducto + " - " + nombre + " - " + cantidad + " - " + UnidadExistencia + " - " + minStock); creandoCelda(wb, fila, 0, idProducto); creandoCelda(wb, fila, 1, nombre); creandoCelda(wb, fila, 2, cantidad); creandoCelda(wb, fila, 3, UnidadExistencia); creandoCelda(wb, fila, 4, minStock); i++; } } con.close(); // Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que // la celda se acomode segn su tamao Sheet ssheet = wb.getSheetAt(0); ssheet.autoSizeColumn(0); ssheet.autoSizeColumn(1); ssheet.autoSizeColumn(2); ssheet.autoSizeColumn(3); ssheet.autoSizeColumn(4); ssheet.autoSizeColumn(5); ssheet.autoSizeColumn(6); ssheet.autoSizeColumn(7); //Ajustando la hoja de una pagina Sheet sheet = wb.createSheet("format sheet"); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); //Area de impresion wb.setPrintArea(0, 0, 1, 0, 9); String strRuta = System.getProperty("user.dir") + System.getProperty("file.separator") + "reports" + System.getProperty("file.separator") + "Almacen" + ControllerFechas.getFechaActual() + ".xls"; //"C:\\Users\\Tet\\Documents\\GitHub\\gestionProyecto\\4.- Cdigo\\AnaIsRepo" + ControllerFechas.getFechaActual() + ".xls"; try (FileOutputStream fileOut = new FileOutputStream(strRuta)) { wb.write(fileOut); } JOptionPane.showMessageDialog(null, "Se ha creado!\nSu archivo es:\n" + strRuta); } catch (Exception e) { e.printStackTrace(); //JOptionPane.showMessageDialog(null, "El archivo no se ha creado debido a que otro usuario esta haciendo uso de el.\nSe recomienda cerrar el archivo"); } }
From source file:featurescomparison.workingwithworkbook.fittoonepage.java.ApacheFitSheetToOnePage.java
License:Apache License
public static void main(String[] args) throws Exception { String dataPath = "src/featurescomparison/workingwithworkbook/fittoonepage/data/"; Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true);// w w w .j a va 2 s . co m ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); // Create various cells and rows for spreadsheet. FileOutputStream fileOut = new FileOutputStream(dataPath + "ApacheFitSheetToOnePage.xlsx"); wb.write(fileOut); fileOut.close(); System.out.println("Done."); }
From source file:featurescomparison.workingwithworksheets.fittoonepage.java.ApacheFitSheetToOnePage.java
License:Apache License
public static void main(String[] args) throws Exception { String dataPath = "src/featurescomparison/workingwithworksheets/fittoonepage/data/"; Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true);/*from www . j av a 2 s . c o m*/ ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); // Create various cells and rows for spreadsheet. FileOutputStream fileOut = new FileOutputStream(dataPath + "ApacheFitSheetToOnePage.xlsx"); wb.write(fileOut); fileOut.close(); System.out.println("Done."); }
From source file:itpreneurs.itp.report.archive.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//w ww. j a v a 2 s .com // if(args.length > 0 && args[0].equals("-xls")) wb = new // HSSFWorkbook(); // else wb = new XSSFWorkbook(); wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); // 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(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } // columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); calendar.setTime(fmt.parse("9-Jul")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } // freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } // group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); // set column widths, the width is measured in units of 1/256th of a // character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:module.siadap.domain.SiadapRootModule.java
License:Open Source License
private void populateSheet(HSSFSheet sheetToWriteTo, boolean considerQuotas, UnitSiadapWrapper unitToSearchIn, HSSFWorkbook wb, boolean shouldIncludeEndOfRole, boolean includeHarmonizationResponsibles, boolean shouldIncludeUniverse) { CreationHelper creationHelper = wb.getCreationHelper(); // make the sheet fit the page PrintSetup ps = sheetToWriteTo.getPrintSetup(); sheetToWriteTo.setAutobreaks(true);/*from w ww.ja v a2 s .c o m*/ ps.setFitHeight((short) 1); ps.setFitWidth((short) 1); /* ** styles ** */ // CostCenter style HSSFFont costCenterFont = wb.createFont(); costCenterFont.setColor(HSSFColor.DARK_BLUE.index); costCenterFont.setFontHeightInPoints((short) 12); costCenterFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle costCenterStyle = wb.createCellStyle(); costCenterStyle.setFont(costCenterFont); // make the Unit header style CellStyle unitHeaderStyle = wb.createCellStyle(); unitHeaderStyle.setBorderBottom(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderTop(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderLeft(CellStyle.BORDER_THIN); unitHeaderStyle.setBorderRight(CellStyle.BORDER_THIN); unitHeaderStyle.setAlignment(CellStyle.ALIGN_CENTER); unitHeaderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); HSSFFont headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setItalic(true); unitHeaderStyle.setFont(headerFont); // make the default name style CellStyle defaultTextNameStyle = wb.createCellStyle(); defaultTextNameStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextNameStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextNameStyle.setBorderBottom(CellStyle.BORDER_NONE); defaultTextNameStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextNameStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); HSSFFont defaultFont = wb.createFont(); defaultFont.setFontHeightInPoints((short) 11); defaultTextNameStyle.setFont(defaultFont); // make the last line name style CellStyle defaultTextNameLastStyle = wb.createCellStyle(); defaultTextNameLastStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderBottom(CellStyle.BORDER_THIN); defaultTextNameLastStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextNameLastStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextNameLastStyle.setFont(defaultFont); // make the default IST-ID last line style CellStyle defaultTextIstIdLastStyle = wb.createCellStyle(); defaultTextIstIdLastStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setBorderBottom(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextIstIdLastStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextIstIdLastStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextIstIdLastStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultTextIstIdLastStyle.setFont(defaultFont); // make the default IST-ID style CellStyle defaultTextIstIdStyle = wb.createCellStyle(); defaultTextIstIdStyle.setBorderLeft(CellStyle.BORDER_THIN); defaultTextIstIdStyle.setBorderBottom(CellStyle.BORDER_NONE); defaultTextIstIdStyle.setBorderTop(CellStyle.BORDER_NONE); defaultTextIstIdStyle.setBorderRight(CellStyle.BORDER_THIN); defaultTextIstIdStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); defaultTextIstIdStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultTextIstIdStyle.setFont(defaultFont); // header style // CellStyle headerStyle = wb.createCellStyle(); // HSSFFont headerFont = wb.createFont(); // headerFont.setFontName(HSSFFont.FONT_ARIAL); // headerFont.setFontHeightInPoints((short) 10); // headerStyle.setFont(headerFont); // // first line style CellStyle firstLineStyle = wb.createCellStyle(); HSSFFont firstLineFont = wb.createFont(); firstLineFont.setColor(HSSFColor.DARK_BLUE.index); firstLineFont.setFontHeightInPoints((short) 14); firstLineFont.setBoldweight(Font.BOLDWEIGHT_BOLD); firstLineStyle.setFont(firstLineFont); firstLineStyle.setAlignment(CellStyle.ALIGN_CENTER); firstLineStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // second line style CellStyle secondLineStyle = wb.createCellStyle(); HSSFFont secondLineFont = wb.createFont(); secondLineFont.setBoldweight(Font.BOLDWEIGHT_BOLD); secondLineFont.setFontHeightInPoints((short) 14); secondLineStyle.setFont(secondLineFont); secondLineStyle.setAlignment(CellStyle.ALIGN_CENTER); secondLineStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // the style for Unit Harmonization responsibles - title CellStyle unitHarmonizationTitleStyle = wb.createCellStyle(); // the BLUE title font - is equal to 'firstLineFont' unitHarmonizationTitleStyle.setFont(firstLineFont); // now we just have to shade it unitHarmonizationTitleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); unitHarmonizationTitleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); unitHarmonizationTitleStyle.setAlignment(CellStyle.ALIGN_CENTER); // the style for Unit Harmonization responsibles - normal // let's create the BLUE Arial 14 font for the responsibles of // harmonization HSSFFont harmonizationResponsibleFont = wb.createFont(); harmonizationResponsibleFont.setColor(HSSFColor.DARK_BLUE.index); harmonizationResponsibleFont.setFontHeightInPoints((short) 14); CellStyle unitHarmonizationResponsibleStyle = wb.createCellStyle(); unitHarmonizationResponsibleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); unitHarmonizationResponsibleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); unitHarmonizationResponsibleStyle.setFont(harmonizationResponsibleFont); unitHarmonizationResponsibleStyle.setAlignment(CellStyle.ALIGN_CENTER); /* ** END of styles ** */ /* ** Immutable IST header ** */ HSSFHeader header = sheetToWriteTo.getHeader(); header.setCenter(HSSFHeader.font("Arial", "Normal") + HSSFHeader.fontSize((short) 10)); header.setCenter("Instituto Superior Tcnico"); int rowIndex = START_ROW_INDEX; int cellIndex = START_CELL_INDEX; int firstLineIndex = rowIndex++; int secondLineIndex = rowIndex++; /* ** Write the first lines with the dates ** */ HSSFRow row = sheetToWriteTo.createRow(firstLineIndex); HSSFCell cell = row.createCell(cellIndex); cell.setCellValue("SIADAP - LISTA DE AVALIADORES " + unitToSearchIn.getYear()); cell.setCellStyle(firstLineStyle); sheetToWriteTo .addMergedRegion(new CellRangeAddress(firstLineIndex, firstLineIndex, cellIndex, cellIndex + 3)); // second line if (!considerQuotas) { cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(secondLineIndex); cell = row.createCell(cellIndex); cell.setCellValue("PESSOAL CONTRATADO PELA ADIST"); cell.setCellStyle(secondLineStyle); } /* ** write the IST logo ** */ int pictureIdx = wb.addPicture(istLogoBytes, Workbook.PICTURE_TYPE_PNG); HSSFPatriarch drawingPatriarch = sheetToWriteTo.createDrawingPatriarch(); ClientAnchor clientAnchor = creationHelper.createClientAnchor(); clientAnchor.setCol1(cellIndex); clientAnchor.setRow1(rowIndex); HSSFPicture picture = drawingPatriarch.createPicture(clientAnchor, pictureIdx); // let's give the next item some space rowIndex += 6; /* ** Dynamic IST footer ** */ HSSFFooter footer = sheetToWriteTo.getFooter(); footer.setLeft("Lista gerada em: " + HSSFFooter.date() + " " + HSSFFooter.time()); footer.setCenter(HSSFFooter.page()); footer.setRight("SIADAP - Lista de avaliadores " + unitToSearchIn.getYear()); for (UnitSiadapWrapper eachUnit : unitToSearchIn .getAllChildUnits(unitToSearchIn.getConfiguration().getUnitRelations())) { Collection<Person> harmonizationResponsibles = eachUnit.getHarmonizationResponsibles(); if (includeHarmonizationResponsibles && !harmonizationResponsibles.isEmpty()) { // let's add the section stating the responsible for // Harmonization cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(++rowIndex); // let's merge the row sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); cell = row.createCell(cellIndex); cell.setCellStyle(unitHarmonizationTitleStyle); cell.setCellValue("UNIDADE DE HARMONIZAO: " + eachUnit.getName()); // a 'blank' styled line row = sheetToWriteTo.createRow(++rowIndex); // merge it sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); row.createCell(cellIndex).setCellStyle(unitHarmonizationResponsibleStyle); // each responsible has one of the following lines for (Person harmonizationResponsible : harmonizationResponsibles) { cellIndex = START_CELL_INDEX; row = sheetToWriteTo.createRow(++rowIndex); // merge it sheetToWriteTo .addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); cell = row.createCell(cellIndex); cell.setCellStyle(unitHarmonizationResponsibleStyle); cell.setCellValue("RESPONS?VEL PELA HARMONIZAO: " + harmonizationResponsible.getName()); } // and let's add an extra 'blank' styled line row = sheetToWriteTo.createRow(++rowIndex); sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cellIndex, cellIndex + 3)); row.createCell(cellIndex).setCellStyle(unitHarmonizationResponsibleStyle); // and a regular one! (skip one in the index) ++rowIndex; } if (eachUnit.getQuotaAwareTotalPeopleWorkingInUnit(false, considerQuotas) > 0) { row = sheetToWriteTo.createRow(++rowIndex); cellIndex = START_CELL_INDEX; // write the unit name and cost center String unitNameWithCC = eachUnit.getUnit().getPartyName().getContent(); if (eachUnit.getUnit().getPartyTypesSet().contains(PartyType.readBy("CostCenter"))) { unitNameWithCC += " - " + eachUnit.getUnit().getAcronym(); } cell = row.createCell(cellIndex++); sheetToWriteTo.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, --cellIndex, ++cellIndex)); cell.setCellValue(unitNameWithCC); cell.setCellStyle(costCenterStyle); /* **** write the Unit header ***** */ // restart the cell's index cellIndex = START_CELL_INDEX; // IST id avaliado int firstLineAfterUnitNameIndex = ++rowIndex; int secondLineAfterUnitNameIndex = ++rowIndex; row = sheetToWriteTo.createRow(firstLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("IST id."); row = sheetToWriteTo.createRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge the IST id sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); // Nome avaliado row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("Nome"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); if (shouldIncludeUniverse) { // SIADAP do avaliado row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("SIADAP"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); } // Ist id do avaliador row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("IST id."); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); // avaliador row = sheetToWriteTo.getRow(firstLineAfterUnitNameIndex); cell = row.createCell(++cellIndex); cell.setCellStyle(unitHeaderStyle); cell.setCellValue("Avaliador"); row = sheetToWriteTo.getRow(secondLineAfterUnitNameIndex); cell = row.createCell(cellIndex); cell.setCellStyle(unitHeaderStyle); // merge sheetToWriteTo.addMergedRegion(new CellRangeAddress(firstLineAfterUnitNameIndex, secondLineAfterUnitNameIndex, cellIndex, cellIndex)); List<PersonSiadapWrapper> listToUse = (considerQuotas) ? eachUnit.getUnitEmployeesWithQuotas(false) : eachUnit.getUnitEmployeesWithoutQuotas(true); // now let's take care of exporting the persons for (PersonSiadapWrapper personWrapper : listToUse) { row = sheetToWriteTo.createRow(++rowIndex); // restart the cell's index cellIndex = START_CELL_INDEX; String istIdEvaluated = personWrapper.getPerson().getUser().getUsername(); cell = row.createCell(cellIndex++); cell.setCellValue(istIdEvaluated); cell.setCellStyle(defaultTextIstIdStyle); String nameEvaluatedPerson = personWrapper.getPerson().getName(); cell = row.createCell(cellIndex++); cell.setCellValue(nameEvaluatedPerson); cell.setCellStyle(defaultTextNameStyle); if (shouldIncludeUniverse) { Siadap siadap = personWrapper.getSiadap(); String siadapUniverseToBeWritten = (siadap == null || siadap.getDefaultSiadapUniverse() == null) ? "No definido" : siadap.getDefaultSiadapUniverse().getLocalizedName(); cell = row.createCell(cellIndex++); cell.setCellValue(siadapUniverseToBeWritten); cell.setCellStyle(defaultTextNameStyle); } PersonSiadapWrapper evaluatorWrapper = personWrapper.getEvaluator(); String istIdEvaluator = evaluatorWrapper == null ? "-" : evaluatorWrapper.getPerson().getUser().getUsername(); cell = row.createCell(cellIndex++); cell.setCellValue(istIdEvaluator); cell.setCellStyle(defaultTextIstIdStyle); String nameEvaluatorWrapper = evaluatorWrapper == null ? "-" : evaluatorWrapper.getName(); cell = row.createCell(cellIndex++); cell.setCellValue(nameEvaluatorWrapper); cell.setCellStyle(defaultTextNameStyle); } // let's make a bottom border on the last four cells for (int i = START_CELL_INDEX; i < START_CELL_INDEX + 4; i++) { cell = row.getCell(i); // let's diferentaitate between the IST-id and the name if (i == START_CELL_INDEX || i == START_CELL_INDEX + 2) // first // cell, // IST-ID // then. // or // third // cell // the // other // IST-ID { cell.setCellStyle(defaultTextIstIdLastStyle); } else { cell.setCellStyle(defaultTextNameLastStyle); } } row = sheetToWriteTo.createRow(++rowIndex); row = sheetToWriteTo.createRow(++rowIndex); } } sheetToWriteTo.autoSizeColumn(START_CELL_INDEX); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 1); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 2); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 3); sheetToWriteTo.autoSizeColumn(START_CELL_INDEX + 4); // now let's resize the logo picture.resize(); }
From source file:nl.b3p.viewer.features.ExcelDownloader.java
License:Open Source License
@Override public void init() throws IOException { wb = new XSSFWorkbook(); styles = createStyles(wb);/*from w w w.ja va 2 s . com*/ sheet = wb.createSheet(fs.getName().toString()); //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(15f); int colNum = 0; Drawing drawing = sheet.createDrawingPatriarch(); CreationHelper factory = wb.getCreationHelper(); // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = factory.createClientAnchor(); for (ConfiguredAttribute configuredAttribute : attributes) { if (configuredAttribute.isVisible()) { Cell cell = headerRow.createCell(colNum); String alias = attributeAliases.get(configuredAttribute.getAttributeName()); cell.setCellValue(alias); if (!alias.equals(configuredAttribute.getAttributeName())) { Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(configuredAttribute.getAttributeName()); comment.setString(str); cell.setCellComment(comment); } cell.setCellStyle(styles.get("header")); sheet.autoSizeColumn(colNum); colNum++; } } //freeze the first row sheet.createFreezePane(0, 1); currentRow = 1; }
From source file:org.bbreak.excella.reports.util.ReportsUtil.java
License:Open Source License
/** * fromIdx??toIdx?????/*from ww w .j a v a 2 s . co m*/ * @param workbook fromIdx?toIdx??workbook * @param fromIdx ? * @param sheet */ public static void copyPrintSetup(Workbook workbook, int fromIdx, Sheet toSheet) { Sheet fromSheet = workbook.getSheetAt(fromIdx); // ? PrintSetup fromPrintSetup = fromSheet.getPrintSetup(); PrintSetup printSetup = toSheet.getPrintSetup(); printSetup.setCopies(fromPrintSetup.getCopies()); printSetup.setDraft(fromPrintSetup.getDraft()); printSetup.setFitHeight(fromPrintSetup.getFitHeight()); printSetup.setFitWidth(fromPrintSetup.getFitWidth()); printSetup.setFooterMargin(fromPrintSetup.getFooterMargin()); printSetup.setHeaderMargin(fromPrintSetup.getHeaderMargin()); printSetup.setHResolution(fromPrintSetup.getHResolution()); printSetup.setLandscape(fromPrintSetup.getLandscape()); printSetup.setLeftToRight(fromPrintSetup.getLeftToRight()); printSetup.setNoColor(fromPrintSetup.getNoColor()); printSetup.setNoOrientation(fromPrintSetup.getNoOrientation()); printSetup.setPageStart(fromPrintSetup.getPageStart()); printSetup.setPaperSize(fromPrintSetup.getPaperSize()); printSetup.setScale(fromPrintSetup.getScale()); printSetup.setUsePage(fromPrintSetup.getUsePage()); printSetup.setValidSettings(fromPrintSetup.getValidSettings()); printSetup.setVResolution(fromPrintSetup.getVResolution()); // ? String printArea = workbook.getPrintArea(fromIdx); if (printArea != null) { if (printArea.contains("!")) { printArea = printArea.substring(printArea.indexOf("!") + 1); } int toIdx = workbook.getSheetIndex(toSheet); workbook.setPrintArea(toIdx, printArea); } // ? toSheet.setRepeatingColumns(fromSheet.getRepeatingColumns()); toSheet.setRepeatingRows(fromSheet.getRepeatingRows()); }