List of usage examples for org.apache.poi.hssf.usermodel HSSFRow setHeight
@Override public void setHeight(short height)
From source file:com.ts.excelservlet.UDR_Driver_Excel.java
/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) *///from w w w.ja va 2 s. c o m protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub DBTransaction dbtranobj = new DBTransaction(); String vehicle_number = request.getParameter("vehicle_number"); HttpSession session = request.getSession(true); String[] select = (String[]) session.getAttribute("id"); String driver_name = request.getParameter("driver_name"); //System.out.println("IMEI : " +imeinumber); //String vehicle_number=""; int index = 2; System.out.println("************** doGet ************"); OutputStream out = null; try { response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Driver_UDR.xls"); Connection con = dbtranobj.connect(); // Class.forName("org.postgresql.Driver").newInstance(); //conn = DriverManager.getConnection("jdbc:postgresql://182.72.167.34:5432/master_database","postgres", "postgres"); ResultSet rs = null; Statement st = null; st = con.createStatement(); rs = st.executeQuery( "SELECT * FROM driver_info WHERE driver_name='" + driver_name + "' order by driver_name"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Driver Sheet"); sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 1)); sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 2)); /* sheet.addMergedRegion(new Region(0,(short)0,0,(short)3)); sheet.addMergedRegion(new Region(0,(short)0,0,(short)4)); sheet.addMergedRegion(new Region(0,(short)0,0,(short)5));*/ HSSFRow rowhead = sheet.createRow((short) 0); rowhead.setHeight((short) 500); /*rowhead.createCell((short) 0).setCellValue("Fuel Information For " +vehicle_number); * */ HSSFCell cell2B = rowhead.createCell(0); cell2B.setCellValue(new HSSFRichTextString("Report For Driver : " + driver_name)); // Style Font in Cell 2B HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle = wb.createCellStyle(); HSSFFont hSSFFont = wb.createFont(); hSSFFont.setFontName(HSSFFont.FONT_ARIAL); hSSFFont.setFontHeightInPoints((short) 14); hSSFFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); hSSFFont.setColor(HSSFColor.BLUE.index); cellStyle.setFont(hSSFFont); cell2B.setCellStyle(cellStyle); HSSFRow rowhead1 = sheet.createRow((short) 1); rowhead1.setHeight((short) 600); Cell cell = rowhead1.createCell((short) 0); HSSFCellStyle cellStyle1 = wb.createCellStyle(); cellStyle1 = wb.createCellStyle(); HSSFFont hSSFFont1 = wb.createFont(); hSSFFont1.setFontName(HSSFFont.FONT_ARIAL); hSSFFont1.setFontHeightInPoints((short) 12); hSSFFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); hSSFFont1.setColor(HSSFColor.BLACK.index); cellStyle1.setFont(hSSFFont1); cell.setCellStyle(cellStyle1); // cell.setCellValue("SNO"); sheet.setColumnWidth(0, 7000); // rowhead.createCell((short) 0).setCellValue("Date"); if (select != null && select.length != 0) { for (int i = 0; i < select.length; i++) { if (select[i].equalsIgnoreCase("doe")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("LICENCE EXPIRY"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("doj")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("JOINED DATE"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("license_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("LICENCE NUMBER "); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("vehicle_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("VEHICLE NUMBER"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("shift_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("SHIFT NUMBER"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("route_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("ROUTE NUMBER"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("address")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("ADDRESS"); sheet.setColumnWidth(i, 7000); } else { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue(select[i]); sheet.setColumnWidth(i, 7000); } } } rs = st.executeQuery( "SELECT * FROM driver_info WHERE driver_name='" + driver_name + "' order by driver_name"); while (rs.next()) { HSSFRow row = sheet.createRow((short) index); row.setHeight((short) 500); if (select != null && select.length != 0) { for (int i = 0; i < select.length; i++) { row.createCell((short) i).setCellValue(rs.getString(select[i])); } } index++; } out = response.getOutputStream(); wb.write(out); } catch (Exception e) { throw new ServletException("Exception in Excel Sample Servlet", e); } finally { if (out != null) out.close(); } }
From source file:com.util.poi.ExcelView.java
License:Open Source License
/** * ?Excel//from w w w . j av a2s . c o m * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); //comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); /* Map<String, Object> map=toHashMap(item); cell.setCellValue(map.get(properties[i]).toString());*/ cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { /*Map<String, Object> map=toHashMap(item); cell.setCellValue(map.get(properties[i]).toString());*/ cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
From source file:demo.poi.BigExample.java
License:Apache License
public static void main(String[] args) throws IOException { int rownum;//from w ww. j av a 2 s. co m // create a new file FileOutputStream out = new FileOutputStream("target/bigworkbook.xls"); // create a new workbook HSSFWorkbook wb = new HSSFWorkbook(); // create a new sheet HSSFSheet s = wb.createSheet(); // declare a row object reference HSSFRow r = null; // declare a cell object reference HSSFCell c = null; // create 3 cell styles HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); // create 2 fonts objects HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); //set font 1 to 12 point type f.setFontHeightInPoints((short) 12); //make it red f.setColor(HSSFColor.RED.index); // make it bold //arial is the default font f.setBoldweight(Font.BOLDWEIGHT_BOLD); //set font 2 to 10 point type f2.setFontHeightInPoints((short) 10); //make it the color at palette index 0xf (white) f2.setColor(HSSFColor.WHITE.index); //make it bold f2.setBoldweight(Font.BOLDWEIGHT_BOLD); //set cell stlye cs.setFont(f); //set the cell format see HSSFDataFromat for a full list cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); //set a thin border cs2.setBorderBottom(CellStyle.BORDER_THIN); //fill w fg fill color cs2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set foreground fill to red cs2.setFillForegroundColor(HSSFColor.RED.index); // set the font cs2.setFont(f2); // set the sheet name to HSSF Test wb.setSheetName(0, "HSSF Test"); // create a sheet with 300 rows (0-299) for (rownum = 0; rownum < 300; rownum++) { // create a row r = s.createRow(rownum); // on every other row if ((rownum % 2) == 0) { // make the row height bigger (in twips - 1/20 of a point) r.setHeight((short) 0x249); } //r.setRowNum(( short ) rownum); // create 50 cells (0-49) (the += 2 becomes apparent later for (int cellnum = 0; cellnum < 50; cellnum += 2) { // create a numeric cell c = r.createCell(cellnum); // do some goofy math to demonstrate decimals c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); // on every other row if ((rownum % 2) == 0) { // set this cell to the first cell style we defined c.setCellStyle(cs); } // create a string cell (see why += 2 in the c = r.createCell(cellnum + 1); // set the cell's string value to "TEST" c.setCellValue("TEST"); // make this column a bit wider s.setColumnWidth(cellnum + 1, (int) ((50 * 8) / ((double) 1 / 20))); // on every other row if ((rownum % 2) == 0) { // set this to the white on red cell style // we defined above c.setCellStyle(cs2); } } } //draw a thick black border on the row at the bottom using BLANKS // advance 2 rows rownum++; rownum++; r = s.createRow(rownum); // define the third style to be the default // except with a thick black border at the bottom cs3.setBorderBottom(CellStyle.BORDER_THICK); //create 50 cells for (int cellnum = 0; cellnum < 50; cellnum++) { //create a blank type cell (no value) c = r.createCell(cellnum); // set it to the thick black border style c.setCellStyle(cs3); } //end draw thick black border // demonstrate adding/naming and deleting a sheet // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); //end deleted sheet // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); out.close(); }
From source file:excel.PoiWriteExcelFile.java
public static int generarReporte() { //Calendar cal=Calendar.getInstance(); Calendar cal = WorkMonitorUI.instante; try {/*from ww w . j a v a2 s.c o m*/ FileOutputStream fileOut = new FileOutputStream("HH_" + instante.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()) .toUpperCase() + "_" + persona.getNombre().toUpperCase().charAt(0) + "." + persona.getApellido().toUpperCase() + "_" + instante.get(Calendar.YEAR) + ".xls"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet worksheet = workbook.createSheet( cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()).toUpperCase() + "-" + cal.get(Calendar.YEAR)); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.YELLOW.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Calibri"); font.setItalic(false); font.setBold(true); font.setColor(HSSFColor.BLACK.index); cellStyle.setFont(font); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle diasStyle = workbook.createCellStyle(); diasStyle.setFillForegroundColor(HSSFColor.SEA_GREEN.index); diasStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setFontName("Calibri"); font.setItalic(false); font.setBold(true); font.setColor(HSSFColor.WHITE.index); diasStyle.setFont(font); diasStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); diasStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); diasStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); diasStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle schedStyle = workbook.createCellStyle(); schedStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); schedStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font3 = workbook.createFont(); font3.setFontHeightInPoints((short) 11); font3.setFontName("Calibri"); font3.setItalic(false); font3.setColor(HSSFColor.BLACK.index); schedStyle.setFont(font3); schedStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); schedStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); schedStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); schedStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle workdayStyle = workbook.createCellStyle(); //workdayStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); workdayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); workdayStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); workdayStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); workdayStyle.setWrapText(true); HSSFFont font2 = workbook.createFont(); font2.setFontHeightInPoints((short) 8); font2.setFontName("Serif"); font2.setItalic(false); //font2.setColor(HSSFColor.YELLOW.index); workdayStyle.setFont(font2); workdayStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle weekendStyle = workbook.createCellStyle(); weekendStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); weekendStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); weekendStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); weekendStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); weekendStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); weekendStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle horarioStyle = workbook.createCellStyle(); horarioStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); horarioStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); horarioStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); horarioStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); horarioStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); horarioStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font4 = workbook.createFont(); font4.setFontHeightInPoints((short) 10); font4.setFontName("Serif"); font4.setItalic(false); font4.setBold(true); //font2.setColor(HSSFColor.YELLOW.index); horarioStyle.setFont(font4); // index from 0,0... cell A1 is cell(0,0) HSSFRow row1 = worksheet.createRow((short) 0); row1.setHeight((short) 500); //System.out.println("cal.get(Calendar.YEAR)="+cal.get(Calendar.YEAR)); HSSFCell cellA1 = row1.createCell((short) 0); cellA1.setCellValue( cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.getDefault()).toUpperCase() + "-" + cal.get(Calendar.YEAR)); cellA1.setCellStyle(cellStyle); HSSFRow row2 = worksheet.createRow((short) 1); HSSFCell cellA4 = row2.createCell((short) 0); cellA4.setCellValue("Horario"); cellA4.setCellStyle(horarioStyle); //row2.setHeight((short)500); HSSFRow row3 = worksheet.createRow((short) 2); HSSFCell cellA3 = row3.createCell((short) 0); cellA3.setCellValue("Inicio - Trmino"); cellA3.setCellStyle(diasStyle); Calendar hora = Calendar.getInstance(); hora.set(Calendar.HOUR_OF_DAY, 9); hora.set(Calendar.MINUTE, 0); hora.set(Calendar.SECOND, 0); SimpleDateFormat sdf = new SimpleDateFormat("HH:mm"); HSSFCell cellXn; for (int i = 0; i < 29; ++i) { HSSFRow row = worksheet.createRow((short) i + 3); row.setHeight((short) 500); cellXn = row.createCell((short) 0); String horaIni = sdf.format(hora.getTime()); hora.add(Calendar.MINUTE, 30); String horaFin = sdf.format(hora.getTime()); cellXn.setCellValue(horaIni + " - " + horaFin); cellXn.setCellStyle(schedStyle); } System.out.println("cal.get(Calendar.MONTH)1=" + cal.get(Calendar.MONTH)); cal.add(Calendar.DAY_OF_MONTH, -cal.get(Calendar.DAY_OF_MONTH) + 1); int diasMes = cal.getActualMaximum(Calendar.DAY_OF_MONTH); System.out.println("cal.get(Calendar.MONTH)2=" + cal.get(Calendar.MONTH)); sdf = new SimpleDateFormat("EEEE d"); System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)1=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); for (int i = 0; i < diasMes; ++i) { cellXn = row2.createCell((short) i + 1); String dia = sdf.format(cal.getTime()); dia = Character.toUpperCase(dia.charAt(0)) + dia.substring(1); cellXn.setCellValue(dia); cellXn.setCellStyle(horarioStyle); //System.out.println("cal.get(Calendar.DAY_OF_MONTH)="+cal.get(Calendar.DAY_OF_MONTH)); cal.add(Calendar.DAY_OF_MONTH, 1); } for (int i = 0; i < diasMes; ++i) { cellXn = row3.createCell((short) i + 1); cellXn.setCellValue("Descripcin"); cellXn.setCellStyle(diasStyle); } System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)2=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); // Retroceder mes para que quede como estaba cal.add(Calendar.MONTH, -1); //cal.add(Calendar.DAY_OF_MONTH, -1); System.out.println( "cal.getActualMaximum(Calendar.DAY_OF_MONTH)3=" + cal.getActualMaximum(Calendar.DAY_OF_MONTH)); HhDao hhDao = new HhDao(); Object[][] hh = new Object[29][cal.getActualMaximum(Calendar.DAY_OF_MONTH)]; hh = hhDao.getByMes(WorkMonitorUI.persona.getId(), cal.getTime()); cal.set(Calendar.DAY_OF_MONTH, 1); Sheet sheet = workbook.getSheetAt(0); sdf = new SimpleDateFormat("EEEE"); HSSFPatriarch _drawing = (HSSFPatriarch) sheet.createDrawingPatriarch(); CreationHelper factory = workbook.getCreationHelper(); for (int i = 0; i < 29; ++i) { Row r = sheet.getRow(i + 3); for (int j = 0; j < diasMes; ++j) { if (hh[i][j].toString() != "") { cellXn = (HSSFCell) r.createCell((short) j + 1); Hh _hh = (Hh) hh[i][j]; cellXn.setCellValue( _hh.getTarea().getNombre().trim() + ": " + _hh.getActividad().getNombre().trim()); HSSFAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5); org.apache.poi.ss.usermodel.Comment comment = _drawing.createComment(anchor); String comentario = _hh.getTarea().getComentario().toLowerCase(); if (_hh.getComentario() != null) comentario = comentario + _hh.getComentario().toLowerCase(); RichTextString str = factory.createRichTextString(comentario); comment.setString(str); cellXn.setCellComment(comment); } else { cellXn = (HSSFCell) r.createCell((short) j + 1); cellXn.setCellValue(""); } //System.out.println("sdf.format(cal.getTime())="+sdf.format(cal.getTime())); if (Arrays.asList("sbado", "domingo").contains(sdf.format(cal.getTime()))) cellXn.setCellStyle(weekendStyle); else cellXn.setCellStyle(workdayStyle); sheet.setColumnWidth(j, 5000); cal.add(Calendar.DAY_OF_MONTH, 1); //sheet.autoSizeColumn(j); } // Retroceder mes para que quede como estaba cal.add(Calendar.MONTH, -1); System.out.println("cal.get(Calendar.MONTH)3=" + cal.get(Calendar.MONTH)); cal.set(Calendar.DAY_OF_MONTH, 1); } sheet.setColumnWidth(diasMes, 5000); WorkMonitorUI.instante = Calendar.getInstance(); sheet.setColumnWidth(0, 5000); sheet.createFreezePane(1, 3); // Freeze just one row //sheet.createFreezePane( 0, 1, 0, 1 ); workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); return -1; } catch (IOException e) { e.printStackTrace(); return -2; } return 1; }
From source file:gda.hrpd.data.HSSF.java
License:Apache License
/** * Constructor HSSF - given a filename this outputs a sample sheet with just a set of rows/cells. * * @param filename/*w w w .ja v a 2 s . c o m*/ * @param write * @exception IOException */ public HSSF(String filename, @SuppressWarnings("unused") boolean write) throws IOException { short rownum = 0; FileOutputStream out = new FileOutputStream(filename); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFRow r; HSSFCell c = null; HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = (short) 0; rownum < 300; rownum++) { r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } // r.setRowNum(( short ) rownum); for (short cellnum = (short) 0; cellnum < 50; cellnum += 2) { c = r.createCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell((short) (cellnum + 1), HSSFCell.CELL_TYPE_STRING); c.setCellValue(new HSSFRichTextString("TEST")); s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20))); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } // 50 characters divided by 1/20th of a point } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (short cellnum = (short) 0; cellnum < 50; cellnum++) { c = r.createCell(cellnum, HSSFCell.CELL_TYPE_BLANK); // c.setCellValue(0); c.setCellStyle(cs3); } s.addMergedRegion(new Region((short) 0, (short) 0, (short) 3, (short) 3)); s.addMergedRegion(new Region((short) 100, (short) 100, (short) 110, (short) 110)); // end draw thick black border // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet wb.write(out); out.close(); }
From source file:gov.guilin.ExcelView.java
License:Open Source License
/** * ?Excel/*from ww w . java 2 s . com*/ * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** * OSSW_Cover ? //from www. j a va2 s . com * OSSW_Cover written by byunghoon */ public static void ossw_Cover(HSSFWorkbook wb, String CoverDate) { LogMaker.makelog("Ex.java Making OSSW_Cover Sheet"); String Empty = null; try { HSSFSheet sheet = wb.createSheet("SW ? "); HSSFRow row1 = sheet.createRow(1); for (int i = 0; i < 20; i++) { row1.setHeight((short) 500); row1.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row1.getCell(i).setCellStyle(Style.osswCoverCellStyel); } HSSFRow row2 = sheet.createRow(8); row2.setHeight((short) 500); row2.createCell(9, HSSFCell.CELL_TYPE_STRING).setCellValue("SW?? ? "); row2.getCell(9).setCellStyle(Style.osswCoverCellStyelfont); HSSFRow row3 = sheet.createRow(9); row3.setHeight((short) 500); row3.createCell(9, HSSFCell.CELL_TYPE_STRING).setCellValue(Values.company); row3.getCell(9).setCellStyle(Style.osswCoverCellStyelfont); HSSFRow row4 = sheet.createRow(16); for (int i = 0; i < 20; i++) { row4.setHeight((short) 500); row4.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row4.getCell(i).setCellStyle(Style.osswCoverCellStyel); } HSSFRow row5 = sheet.createRow(24); row5.setHeight((short) 500); row5.createCell(9, HSSFCell.CELL_TYPE_STRING).setCellValue(TimeStamp.getTimeCover()); row5.getCell(9).setCellStyle(Style.osswCoverCellStyelfont); HSSFRow row6 = sheet.createRow(34); row6.setHeight((short) 500); row6.createCell(9, HSSFCell.CELL_TYPE_STRING).setCellValue("??"); row6.getCell(9).setCellStyle(Style.osswCoverCellStyelfont); HSSFRow row7 = sheet.createRow(35); row7.setHeight((short) 500); row7.createCell(9, HSSFCell.CELL_TYPE_STRING).setCellValue("SW ?"); row7.getCell(9).setCellStyle(Style.osswCoverCellStyelfont); } catch (Exception e) { e.printStackTrace(); } }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** * OSSW_Summary ?//from ww w . jav a 2s. c o m * OSSW_Summary written by byunghoon */ public static void ossw_Summary(HSSFWorkbook wb) { LogMaker.makelog("Ex.java Making OSSW_Summary Sheet"); String Empty = null; try { HSSFSheet sheet = wb.createSheet("? ? "); HSSFRow row1 = sheet.createRow(1); row1.setHeight((short) 800); row1.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row1.getCell(0).setCellStyle(Style.osswSummaryCellStyel_1); for (int i = 1; i < 11; i++) { row1.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row1.getCell(i).setCellStyle(Style.osswSummaryCellStyel_3); if (i == 5) { row1.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue("? ? "); row1.getCell(i).setCellStyle(Style.osswSummaryCellStyel_3); } if (i == 10) { row1.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row1.getCell(i).setCellStyle(Style.osswSummaryCellStyel_2); } } HSSFRow row2 = sheet.createRow(2); row2.setHeight((short) 500); row2.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue("? ??"); row2.getCell(0).setCellStyle(Style.osswSummaryCellStyel_5); for (int i = 1; i < 11; i++) { row2.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row2.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); if (i == 3) { row2.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Summary.licenseName.getName()); row2.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); } if (i == 10) { row2.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row2.getCell(i).setCellStyle(Style.osswSummaryCellStyel_6); } } HSSFRow row3 = sheet.createRow(3); row3.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue("?? ? "); row3.getCell(0).setCellStyle(Style.osswSummaryCellStyel_5); row3.setHeight((short) 500); for (int i = 1; i < 11; i++) { row3.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row3.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); if (i == 3) { row3.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Summary.files_analyzed + " Files"); row3.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); } if (i == 10) { row3.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row3.getCell(i).setCellStyle(Style.osswSummaryCellStyel_6); } } HSSFRow row4 = sheet.createRow(4); row4.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue("?? ? "); row4.getCell(0).setCellStyle(Style.osswSummaryCellStyel_5); row4.setHeight((short) 500); for (int i = 1; i < 11; i++) { row4.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row4.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); if (i == 3) { row4.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue( Summary.kibytes_analyzed + " KiB" + " (" + Summary.bytes_analyzed + " bytes)"); row4.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); } if (i == 10) { row4.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row4.getCell(i).setCellStyle(Style.osswSummaryCellStyel_6); } } HSSFRow row5 = sheet.createRow(5); row5.setHeight((short) 500); row5.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue("?? ? "); row5.getCell(0).setCellStyle(Style.osswSummaryCellStyel_5); for (int i = 1; i < 11; i++) { row5.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row5.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); if (i == 3) { row5.createCell(i, HSSFCell.CELL_TYPE_STRING) .setCellValue(Summary.id_required_files + " Files"); row5.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); } if (i == 10) { row5.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row5.getCell(i).setCellStyle(Style.osswSummaryCellStyel_6); } } HSSFRow row6 = sheet.createRow(6); row6.setHeight((short) 500); row6.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue("SW ? "); row6.getCell(0).setCellStyle(Style.osswSummaryCellStyel_5); for (int i = 1; i < 11; i++) { row6.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row6.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); if (i == 3) { row6.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Summary.oss_files + " Files" + " ( " + String.format("%.2f", Summary.oss_files_percent) + "% )"); row6.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); } if (i == 10) { row6.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row6.getCell(i).setCellStyle(Style.osswSummaryCellStyel_6); } } HSSFRow row7 = sheet.createRow(7); row7.setHeight((short) 500); row7.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue(" ? ? "); row7.getCell(0).setCellStyle(Style.osswSummaryCellStyel_5); for (int i = 1; i < 11; i++) { row7.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row7.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); if (i == 3) { row7.createCell(i, HSSFCell.CELL_TYPE_STRING) .setCellValue(Summary.attention_conflict_files + " Files" + " ( " + String.format("%.2f", Summary.attention_conflict_files_percent) + "% )"); row7.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); } if (i == 10) { row7.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row7.getCell(i).setCellStyle(Style.osswSummaryCellStyel_6); } } HSSFRow row8 = sheet.createRow(8); row8.setHeight((short) 500); row8.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue(" ? "); row8.getCell(0).setCellStyle(Style.osswSummaryCellStyel_5); for (int i = 1; i < 11; i++) { row8.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row8.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); if (i == 3) { row8.createCell(i, HSSFCell.CELL_TYPE_STRING) .setCellValue(Summary.violation_conflict_files + " Files" + " ( " + String.format("%.2f", Summary.violation_conflict_files_percent) + "% )"); row8.getCell(i).setCellStyle(Style.osswSummaryCellStyel_4); } if (i == 10) { row8.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(Empty); row8.getCell(i).setCellStyle(Style.osswSummaryCellStyel_6); } } } catch (Exception e) { e.printStackTrace(); } }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** * OSSW_BOM written by byunghoon/* w w w .j av a 2 s . c o m*/ * */ public static void ossw_BOM(HSSFWorkbook wb) { LogMaker.makelog("Ex.java Making OSSW_BOM Sheet"); String Empty = null; try { HSSFSheet sheet = wb.createSheet("? ?(Bill of Materials"); HSSFRow row1 = sheet.createRow(1); row1.setHeight((short) 800); row1.createCell(0, HSSFCell.CELL_TYPE_STRING).setCellValue(" "); row1.getCell(0).setCellStyle(Style.osswBOMCellStyel_1); sheet.setColumnWidth(0, 7000); row1.createCell(1, HSSFCell.CELL_TYPE_STRING).setCellValue("?"); row1.getCell(1).setCellStyle(Style.osswBOMCellStyel_1); sheet.setColumnWidth(1, 7000); row1.createCell(2, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row1.getCell(2).setCellStyle(Style.osswBOMCellStyel_1); sheet.setColumnWidth(2, 7000); row1.createCell(3, HSSFCell.CELL_TYPE_STRING).setCellValue("?"); row1.getCell(3).setCellStyle(Style.osswBOMCellStyel_1); sheet.setColumnWidth(3, 15000); row1.createCell(4, HSSFCell.CELL_TYPE_STRING).setCellValue("??"); row1.getCell(4).setCellStyle(Style.osswBOMCellStyel_1); sheet.setColumnWidth(4, 7000); row1.createCell(5, HSSFCell.CELL_TYPE_STRING).setCellValue(""); row1.getCell(5).setCellStyle(Style.osswBOMCellStyel_1); sheet.setColumnWidth(5, 7000); row1.createCell(6, HSSFCell.CELL_TYPE_STRING).setCellValue("? "); row1.getCell(6).setCellStyle(Style.osswBOMCellStyel_1); sheet.setColumnWidth(6, 7000); } catch (Exception e) { e.printStackTrace(); } }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** *///from w w w . jav a2 s .co m public static void setOneLineHeader(HSSFSheet sheet, int iRowNum, ArrayList<String> al) { LogMaker.makelog("Creating Header in Excel"); try { HSSFRow row = sheet.createRow(iRowNum); row.setHeight((short) 600); int i = 0; for (String str : al) { row.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(str); if (i == 1) { row.getCell(i).setCellStyle(Style.finalHeaderCellStyle); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 1, 1); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } else if (i == 2) { row.getCell(i).setCellStyle(Style.finalHeaderCellStyle); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, i, i); DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint( new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } else if (i == 3) { row.getCell(i).setCellStyle(Style.finalHeaderCellStyle); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, i, i); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B", "C", "D", "E" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } else { try { row.getCell(i).setCellStyle(Style.finalHeaderCellStyle); } catch (NullPointerException e) { e.printStackTrace(); } } sheet.autoSizeColumn((short) i, true); i++; } } catch (Exception e) { e.printStackTrace(); } }