List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet autoSizeColumn
@Override public void autoSizeColumn(int column)
This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing.
From source file:pe.gob.mef.gescon.web.ui.ContenidoMB.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); //Para los datos HSSFCellStyle centerStyle = wb.createCellStyle(); centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle centerGrayStyle = wb.createCellStyle(); centerGrayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); centerGrayStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); centerGrayStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); HSSFCellStyle grayBG = wb.createCellStyle(); grayBG.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); grayBG.setFillPattern(CellStyle.SOLID_FOREGROUND); int i = 1;/*from w w w . j av a 2 s. co m*/ for (Conocimiento c : this.getListaContenido()) { HSSFRow row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { HSSFCell cell = row.getCell(j); if (i % 2 == 0) { if (j > 0) { cell.setCellStyle(centerGrayStyle); } else { cell.setCellStyle(grayBG); cell.setCellValue(c.getVtitulo()); } } else { if (j > 0) { cell.setCellStyle(centerStyle); } else { cell.setCellValue(c.getVtitulo()); } } } i++; } // Para la cabecera HSSFRow header = sheet.getRow(0); HSSFCellStyle headerStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerStyle.setFont(font); for (int j = 0; j < header.getPhysicalNumberOfCells(); j++) { HSSFCell cell = header.getCell(j); cell.setCellStyle(headerStyle); sheet.autoSizeColumn(j); } }
From source file:pe.gob.mef.gescon.web.ui.OportunidadMB.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); //Para los datos HSSFCellStyle centerStyle = wb.createCellStyle(); centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle centerGrayStyle = wb.createCellStyle(); centerGrayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); centerGrayStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); centerGrayStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); HSSFCellStyle grayBG = wb.createCellStyle(); grayBG.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); grayBG.setFillPattern(CellStyle.SOLID_FOREGROUND); int i = 1;/*from w w w .j av a 2s.c om*/ for (Conocimiento c : this.getListaOportunidad()) { HSSFRow row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { HSSFCell cell = row.getCell(j); if (i % 2 == 0) { if (j > 0) { cell.setCellStyle(centerGrayStyle); } else { cell.setCellStyle(grayBG); cell.setCellValue(c.getVtitulo()); } } else { if (j > 0) { cell.setCellStyle(centerStyle); } else { cell.setCellValue(c.getVtitulo()); } } } i++; } // Para la cabecera HSSFRow header = sheet.getRow(0); HSSFCellStyle headerStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerStyle.setFont(font); for (int j = 0; j < header.getPhysicalNumberOfCells(); j++) { HSSFCell cell = header.getCell(j); cell.setCellStyle(headerStyle); sheet.autoSizeColumn(j); } }
From source file:pe.gob.mef.gescon.web.ui.PreguntaMB.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); //Para los datos HSSFCellStyle centerStyle = wb.createCellStyle(); centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle centerGrayStyle = wb.createCellStyle(); centerGrayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); centerGrayStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); centerGrayStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); HSSFCellStyle grayBG = wb.createCellStyle(); grayBG.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); grayBG.setFillPattern(CellStyle.SOLID_FOREGROUND); int i = 1;//from w w w . ja v a2s .co m for (Pregunta p : this.getListaPregunta()) { HSSFRow row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { HSSFCell cell = row.getCell(j); if (i % 2 == 0) { if (j > 0) { cell.setCellStyle(centerGrayStyle); } else { cell.setCellStyle(grayBG); cell.setCellValue(p.getVasunto()); } } else { if (j > 0) { cell.setCellStyle(centerStyle); } else { cell.setCellValue(p.getVasunto()); } } } i++; } // Para la cabecera HSSFRow header = sheet.getRow(0); HSSFCellStyle headerStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerStyle.setFont(font); for (int j = 0; j < header.getPhysicalNumberOfCells(); j++) { HSSFCell cell = header.getCell(j); cell.setCellStyle(headerStyle); sheet.autoSizeColumn(j); } }
From source file:pe.gob.mef.gescon.web.ui.WikiMB.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); // Para la cabecera HSSFRow header = sheet.getRow(0);/*from w w w .j a v a2 s . c o m*/ HSSFCellStyle headerStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerStyle.setFont(font); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(headerStyle); sheet.autoSizeColumn(i); } HSSFCellStyle centerStyle = wb.createCellStyle(); centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle centerGrayStyle = wb.createCellStyle(); centerGrayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); centerGrayStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); centerGrayStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); HSSFCellStyle grayBG = wb.createCellStyle(); grayBG.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); grayBG.setFillPattern(CellStyle.SOLID_FOREGROUND); int i = 1; for (Conocimiento c : this.getListaWiki()) { HSSFRow row = sheet.getRow(i); for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) { HSSFCell cell = row.getCell(j); if (i % 2 == 0) { if (j > 0) { cell.setCellStyle(centerGrayStyle); } else { cell.setCellStyle(grayBG); } } else { if (j > 0) { cell.setCellStyle(centerStyle); } } } i++; } }
From source file:quickreports.masterlist.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from w ww. j a va2s . c o m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { response.setContentType("text/html;charset=UTF-8"); //PrintWriter out = response.getWriter(); /* TODO output your page here. You may use following sample code. */ //______________________________________________________________________________________ // CREATE THE WORKSHEETS //______________________________________________________________________________________ HSSFWorkbook wb = new HSSFWorkbook(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Cambria"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFCellStyle stylesum = wb.createCellStyle(); stylesum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylesum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylesum.setBorderTop(HSSFCellStyle.BORDER_THIN); stylesum.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylesum.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylesum.setBorderRight(HSSFCellStyle.BORDER_THIN); stylesum.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.BLACK.index); fontx.setFontName("Cambria"); stylex.setFont(fontx); stylex.setWrapText(true); stylesum.setFont(fontx); stylesum.setWrapText(true); HSSFSheet shet = wb.createSheet("Masterlist"); String year = ""; if (request.getParameter("year") != null) { year = request.getParameter("year"); } dbConn conn = new dbConn(); //========Query 1================= HSSFRow rw0 = shet.createRow(1); HSSFCell cell = rw0.createCell(0); cell.setCellValue("APHIAPLUS Nuru Ya Bonde Supported Sites Summary " + year); cell.setCellStyle(style); shet.addMergedRegion(new CellRangeAddress(1, 1, 0, 3)); int count1 = 3; String qry1 = "call rpt_masterlist_summary('2015-10-01','2016-09-30','" + year + "')"; conn.rs = conn.st.executeQuery(qry1); ResultSetMetaData metaData = conn.rs.getMetaData(); int columnCount = metaData.getColumnCount(); ArrayList mycolumns1 = new ArrayList(); while (conn.rs.next()) { if (count1 == 3) { //header rows HSSFRow rw = shet.createRow(count1); rw.setHeightInPoints(26); for (int i = 1; i <= columnCount; i++) { mycolumns1.add(metaData.getColumnLabel(i)); HSSFCell cell0 = rw.createCell(i - 1); cell0.setCellValue(metaData.getColumnLabel(i)); cell0.setCellStyle(stylex); //create row header } //end of for loop count1++; } //end of if //data rows HSSFRow rw = shet.createRow(count1); for (int a = 0; a < columnCount; a++) { // System.out.print(mycolumns1.get(a) + ":" + conn.rs.getString("" + mycolumns1.get(a))); HSSFCell cell0 = rw.createCell(a); if (a > 0) { cell0.setCellValue(conn.rs.getInt(mycolumns1.get(a).toString())); } else { cell0.setCellValue(conn.rs.getString("" + mycolumns1.get(a))); } cell0.setCellStyle(style2); } // System.out.println(""); count1++; } HSSFRow rw01 = shet.createRow(count1 + 1); HSSFCell cell1 = rw01.createCell(0); cell1.setCellValue("APHIAPLUS Nuru Ya Bonde supported sites details " + year); cell1.setCellStyle(style); shet.addMergedRegion(new CellRangeAddress(count1 + 1, count1 + 1, 0, 3)); //========Query two====Facility Details============== String qry = "call rpt_masterlist('2015-10-01','2016-09-30','" + year + "')"; conn.rs = conn.st.executeQuery(qry); metaData = conn.rs.getMetaData(); columnCount = metaData.getColumnCount(); int count = count1 + 3; ArrayList mycolumns = new ArrayList(); while (conn.rs.next()) { if (count == (count1 + 3)) { //header rows HSSFRow rw = shet.createRow(count); rw.setHeightInPoints(26); for (int i = 1; i <= columnCount; i++) { mycolumns.add(metaData.getColumnLabel(i)); HSSFCell cell0 = rw.createCell(i - 1); cell0.setCellValue(metaData.getColumnLabel(i)); cell0.setCellStyle(stylex); //create row header } //end of for loop count++; } //end of if //data rows HSSFRow rw = shet.createRow(count); for (int a = 0; a < columnCount; a++) { //System.out.print(mycolumns.get(a) + ":" + conn.rs.getString("" + mycolumns.get(a))); HSSFCell cell0 = rw.createCell(a); if ((a >= 3 && a <= 14) || (a >= 18 && a <= 22) || (a >= 27 && a <= 29)) { cell0.setCellValue(conn.rs.getInt(mycolumns.get(a).toString())); } else { cell0.setCellValue(conn.rs.getString("" + mycolumns.get(a))); } cell0.setCellStyle(style2); } // System.out.println(""); count++; } //Autofreeze || Autofilter || Remove Gridlines || shet.setAutoFilter(new CellRangeAddress(count1 + 3, count - 1, 0, columnCount - 1)); //System.out.println("1,"+rowpos+",0,"+colposcopy); for (int i = 0; i <= columnCount; i++) { shet.autoSizeColumn(i); } shet.setDisplayGridlines(false); shet.createFreezePane(4, 14); if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } IdGenerator IG = new IdGenerator(); String createdOn = IG.CreatedOn(); System.out.println("" + "MasterList_Gen_" + createdOn.trim() + ".xls"); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=" + "MasterList_Gen_" + createdOn.trim() + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:send.sms.az.util.SqlExcel.java
License:Open Source License
/** * This method generates an excel sheet containing data from the given Map. * The name of the excel sheet will be the String passed as a parameter. *//*from w ww .j a v a 2 s. c o m*/ public void generateExcel(Map<String, LinkedHashMap<String, String>> resultMap, String name) { FileOutputStream fileOut = null; try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle headerStyle = wb.createCellStyle(); HSSFSheet sheet3 = wb.createSheet(name); HSSFFont headerFont = wb.createFont(); // headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index); headerStyle.setFillBackgroundColor(HSSFColor.WHITE.index); headerStyle.setFont(headerFont); try { fileOut = new FileOutputStream("C:\\" + name + ".xls"); } catch (FileNotFoundException e) { e.printStackTrace(); } HSSFRow sessionname = sheet3.createRow(2); HSSFCell title = sessionname.createCell(3); title.setCellStyle(headerStyle); title.setCellValue(name); HSSFRow row = sheet3.createRow(5); Map<String, LinkedHashMap<String, String>> rMap = resultMap; Map<String, String> columnDetails = rMap.get("1"); Set<String> s = columnDetails.keySet(); int cellNo = 0; for (String s1 : s) { HSSFCell cell0 = row.createCell(cellNo); cell0.setCellStyle(headerStyle); cell0.setCellValue(s1); cellNo++; } for (int i = 1; i <= rMap.size(); i++) { columnDetails = rMap.get(new Integer(i).toString()); System.out.println(i); HSSFRow nextrow = sheet3.createRow(5 + i); Set<String> set = columnDetails.keySet(); int cellNum = 0; for (String s2 : set) { nextrow.createCell(cellNum).setCellValue(columnDetails.get(s2)); cellNum++; } } sheet3.autoSizeColumn(0); sheet3.autoSizeColumn(1); sheet3.autoSizeColumn(2); sheet3.autoSizeColumn(3); wb.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException fe) { fe.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { try { fileOut.flush(); fileOut.close(); } catch (IOException e) { e.printStackTrace(); } } }
From source file:teamdash.wbs.excel.WBSExcelWriter.java
License:Open Source License
private void autoSizeColumns(HSSFSheet sheet, TableColumnModel columns) { for (int i = 0; i <= columns.getColumnCount(); i++) sheet.autoSizeColumn(s(i)); }
From source file:ua.com.ecotep.unianalysis.export.XLSDataExport.java
@Override public void exportData(String selectedFile, AnProperties props, ObservableList<ObservableList<Object>> exportData, List<String> columnTitles) throws Exception { if (selectedFile == null) { return;// w w w . j a va 2 s.c o m } System.setProperty("java.awt.headless", "true"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet1"); HSSFPrintSetup ps = sheet.getPrintSetup(); ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); ps.setLandscape(true); HSSFCellStyle cellStyleT = wb.createCellStyle(); HSSFFont font1 = wb.createFont(); font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font1.setFontHeightInPoints((short) 8); cellStyleT.setFont(font1); int rnumber = 0; HSSFRow row = sheet.createRow(0); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10)); HSSFCell cell = row.createCell(0); cell.setCellValue( new HSSFRichTextString(" ??? ??? - " + DateConverters.getDateToStr(LocalDate.now()))); cell.setCellStyle(cellStyleT); rnumber++; if (props != null) { String val = " : " + DateConverters.getDateToStr(props.getDateFrom()) + " " + DateConverters.getDateToStr(props.getDateTo()); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; //---------- if (props.isSalMode()) { String type = props.getSalSalerType() == AnProperties.SALER_TYPES.PROFILE ? " " : " "; val = "? " + type + "| " + props.getSaler().getNameSaler(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getSalSalerType() == AnProperties.SALER_TYPES.SALER) { type = props.isSalDirectSales() ? "? " : " "; val = " : " + type; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.isSalFixedDepartment()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (props.isGoodMode()) { if (props.getGoodClassLev0() != null) { val = "? 1: " + props.getGoodClassLev0().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev1() != null) { val = "? 2: " + props.getGoodClassLev1().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev2() != null) { val = "? 3: " + props.getGoodClassLev2().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev3() != null) { val = "? 4: " + props.getGoodClassLev3().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (props.getGoodCustomSearch() != null && !props.getGoodCustomSearch().isEmpty()) { val = " : " + props.getGoodCustomSearch(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() == 2) { val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(0)); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(1)); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (!props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() > 0) { val = "+: "; for (LocalDate ld : props.getGoodsIndateLst()) { val += DateConverters.getDateToStr(ld) + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (!props.getClLst().isEmpty()) { if (props.isClIncluded()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } else { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } val = "+: "; for (ClientBean cb : props.getClLst()) { val += cb.getClientCl() + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (!props.getVLst().isEmpty()) { if (props.isVIncluded()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } else { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } val = ""; for (String v : props.getVLst()) { val += v + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } //---------- HSSFCellStyle cellStyleH = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyleH.setFont(font); cellStyleH.setWrapText(true); cellStyleH.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleH.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleH.setBorderLeft((short) 1); cellStyleH.setBorderRight((short) 1); cellStyleH.setBorderTop((short) 1); cellStyleH.setBorderBottom((short) 1); HSSFCellStyle cellStyleHh = wb.createCellStyle(); font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyleHh.setFont(font); cellStyleHh.setWrapText(true); cellStyleHh.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleHh.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleHh.setBorderLeft((short) 1); cellStyleHh.setBorderRight((short) 1); cellStyleHh.setBorderTop((short) 1); cellStyleHh.setBorderBottom((short) 1); //filling table HSSFCellStyle cellStyleN = wb.createCellStyle(); cellStyleN.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellStyleN.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleN.setBorderLeft((short) 1); cellStyleN.setBorderRight((short) 1); cellStyleN.setBorderTop((short) 1); cellStyleN.setBorderBottom((short) 1); HSSFCellStyle cellStyleI = wb.createCellStyle(); cellStyleI.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleI.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleI.setBorderLeft((short) 1); cellStyleI.setBorderRight((short) 1); cellStyleI.setBorderTop((short) 1); cellStyleI.setBorderBottom((short) 1); HSSFCellStyle cellStyleD = wb.createCellStyle(); cellStyleD.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleD.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT); HSSFDataFormat df = wb.createDataFormat(); cellStyleD.setDataFormat(df.getFormat("#,##0.0")); cellStyleD.setBorderLeft((short) 1); cellStyleD.setBorderRight((short) 1); cellStyleD.setBorderTop((short) 1); cellStyleD.setBorderBottom((short) 1); HSSFCellStyle cellStyleP = wb.createCellStyle(); cellStyleP.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleP.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleP.setDataFormat(df.getFormat("0.0\\%")); cellStyleP.setBorderLeft((short) 1); cellStyleP.setBorderRight((short) 1); cellStyleP.setBorderTop((short) 1); cellStyleP.setBorderBottom((short) 1); // filling column headers row = sheet.createRow(rnumber); String rowTitle = null; row = sheet.createRow(rnumber); row.setHeightInPoints(40); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(cellStyleH); for (int t = 0; t < columnTitles.size(); t++) { cell = row.createCell(t + 1); cell.setCellValue(new HSSFRichTextString(columnTitles.get(t))); cell.setCellStyle(cellStyleH); } // filling table with data rnumber++; for (ObservableList<Object> line : exportData) { row = sheet.createRow(rnumber); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString((String) line.get(0))); cell.setCellStyle(cellStyleN); for (int i = 1; i < line.size(); i++) { Double val = (Double) line.get(i); cell = row.createCell(i); cell.setCellStyle(cellStyleD); cell.setCellValue(val); } rnumber++; } for (int t = 0; t < columnTitles.size(); t++) { sheet.autoSizeColumn((short) t); } saveWorkBook(wb, selectedFile); execute(selectedFile); }
From source file:util.ExcelConverter.java
public static File createXls(String[] header, String[][] data, String path) { try {/*from w w w.j a v a2 s. c o m*/ HSSFWorkbook xwb = new HSSFWorkbook(); HSSFSheet sheet = xwb.createSheet(); CellStyle cellStyle = xwb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_LEFT); cellStyle.setAlignment(CellStyle.VERTICAL_TOP); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setWrapText(false); Font bold = xwb.createFont(); bold.setBoldweight(Font.BOLDWEIGHT_BOLD); bold.setFontHeightInPoints((short) 10); CellStyle cellStyleHeader = xwb.createCellStyle(); cellStyleHeader.setAlignment(CellStyle.ALIGN_LEFT); cellStyleHeader.setAlignment(CellStyle.VERTICAL_TOP); cellStyleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyleHeader.setFont(bold); cellStyleHeader.setWrapText(false); HSSFRow row; Cell cell; //header row = sheet.createRow(0); for (int i = 0; i < header.length; i++) { cell = row.createCell(i); cell.setCellStyle(cellStyleHeader); cell.setCellValue(header[i]); } int colCount = header.length; int no = 1; for (String[] obj : data) { row = sheet.createRow(no); for (int i = 0; i < colCount; i++) { cell = row.createCell(i); cell.setCellStyle(cellStyle); cell.setCellValue(obj[i]); } no++; } for (int i = 0; i < header.length; i++) { sheet.autoSizeColumn(i); } File newFile = new File(path); try (FileOutputStream fileOut = new FileOutputStream(newFile)) { xwb.write(fileOut); } return newFile; } catch (IOException e) { return null; } }
From source file:Utility.TestReports.java
public void setCellStyle(org.apache.poi.hssf.usermodel.HSSFSheet sheet, org.apache.poi.ss.usermodel.Cell cell, org.apache.poi.hssf.usermodel.HSSFWorkbook wb) { CellStyle s = null;/* www .j av a 2s. c om*/ s = sheet.getWorkbook().createCellStyle(); cell.setCellStyle(s); for (int i = 0; i < 10; i++) { sheet.autoSizeColumn(i); } }