List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook HSSFWorkbook
public HSSFWorkbook()
From source file:com.bonsoft.test.Report.java
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed ArrayList<ReportLine> lines = new ArrayList<>(); try {/*w w w . ja v a 2 s .c o m*/ Orgs org = (Orgs) jComboBox1.getSelectedItem(); Stores store = (Stores) jComboBox2.getSelectedItem(); Calendar period = Calendar.getInstance(); try { period.setTime((new SimpleDateFormat("dd.MM.yyyy")).parse(jFormattedTextField1.getText())); } catch (ParseException ex) { Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex); } Connection connection = null; Statement statement = null; ResultSet result = null; try { connection = DriverManager.getConnection("jdbc:postgresql://88.201.248.46:5432/personal", "vitaly", "m127rqu4"); } catch (SQLException ex) { Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex); } try { statement = connection.createStatement(); } catch (SQLException ex) { Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex); } String sql = "select operations.descr, date_part('hour', mhr_period) as hr, sum(mhr_qty) as cnt from mhr, operations " + "where mhr.operation_id = operations.id and mhR_qty > 0 and mhr.org_id = " + org.getId() + " and store_id = " + store.getId() + " and " + "date_part('day', mhr_period) = " + period.get(Calendar.DAY_OF_MONTH) + " and date_part('month', mhr_period) = " + (period.get(Calendar.MONTH) + 1) + " and date_part('year', mhr_period) = " + period.get(Calendar.YEAR) + " and mhr.operation_id in (select id from operations) group by operations.descr, hr having count(mhr_qty) > 0 order by " + "operations.descr, hr"; try { result = statement.executeQuery(sql); } catch (SQLException ex) { Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex); } System.out.println("?: " + org.getName() + ", id = " + org.getId()); System.out.println(": " + store.getDescr() + ", id = " + store.getId()); System.out.println(": " + period.get(Calendar.DAY_OF_MONTH)); System.out.println("??: " + (period.get(Calendar.MONTH) + 1)); System.out.println(": " + period.get(Calendar.YEAR)); String oldDescr = ""; ReportLine line = null; while (result.next()) { String descr = result.getString("descr"); double hr = result.getDouble("hr"); double cnt = result.getDouble("cnt"); cnt = Math.ceil(cnt); if (oldDescr.equals(descr)) { line.add(hr, cnt); } else { oldDescr = descr; line = new ReportLine(); line.setName(descr); line.add(hr, cnt); lines.add(line); } } result.close(); statement.close(); connection.close(); } catch (SQLException ex) { Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex); } Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet(""); Row title = sheet.createRow(0); Cell cell = title.createCell(0); cell.setCellValue("?"); Row row = null; int x = 0, y = 0; for (ReportLine line : lines) { row = sheet.createRow(++y); cell = row.createCell(0); cell.setCellValue(line.getName()); for (int i = 0; i < line.getLen(); i++) { x = line.getHours().get(i) - ReportLine.getMinH() + 1; cell = title.createCell(x); cell.setCellValue(line.getHours().get(i) + ":00"); cell = row.createCell(x); cell.setCellValue(line.getCounts().get(i)); } } sheet.autoSizeColumn(0); try (FileOutputStream fileExcel = new FileOutputStream("Report.xls")) { workbook.write(fileExcel); } catch (IOException ex) { Logger.getLogger(Report.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.br.uepb.bsc7.www.UI.ManipulaXLS.java
public /*static*/ void criaXLS1(String arqSaida, String nomePlan) throws IOException, NullPointerException { int numLinhas = 5; int numCelulas = 2; //Cria pasta de trabalho wb = new HSSFWorkbook(); //Cria planilha HSSFSheet s = wb.createSheet(nomePlan); //wb.setSheetName(0, nomePlan); //Alterar para a quantidade de linhas retornada por cada relatrio for (int i = 0; i < numLinhas; i++) { //Cria a linha HSSFRow linha = s.createRow(i);/* w w w . j a v a2 s . c o m*/ //Alterar para a quantidade de clulas retornada por cada relatrio for (int j = 0; j < numCelulas; j++) { HSSFCell celula = linha.createCell(j); /*O valor passado deve ser de acordo com aquele recebido por cada relatrio, provavelmente String;*/ //celula.setCellValue((String) tabela.getValueAt(i, j)); celula.setCellValue(i + j); } } FileOutputStream fileOutputStream = new FileOutputStream(arqSaida + ".xls"); try { wb.write(fileOutputStream); //Fecha o fileOutputStream //Melhorar este catch } catch (IOException ex) { System.out.println("Teste"); } finally { fileOutputStream.close(); wb.close(); //Fecha a pasta de trabalho } }
From source file:com.br.uepb.bsc7.www.UI.ManipulaXLS.java
public /*static*/ void criaXLS(JTable tabela, String arqSaida, String nomePlan) throws IOException, NullPointerException { int numLinhas = tabela.getRowCount(); int numCelulas = tabela.getColumnCount(); //Cria pasta de trabalho wb = new HSSFWorkbook(); //Cria planilha HSSFSheet s = wb.createSheet(nomePlan); //wb.setSheetName(0, nomePlan); //Alterar para a quantidade de linhas retornada por cada relatrio for (int i = 0; i < numLinhas; i++) { //Cria a linha HSSFRow linha = s.createRow(i);/* w w w . j a v a 2 s . c o m*/ //Alterar para a quantidade de clulas retornada por cada relatrio for (int j = 0; j < numCelulas; j++) { HSSFCell celula = linha.createCell(j); /*O valor passado deve ser de acordo com aquele recebido por cada relatrio, provavelmente String;*/ celula.setCellValue((String) tabela.getValueAt(i, j)); } } FileOutputStream fileOutputStream = new FileOutputStream(arqSaida + ".xls"); try { wb.write(fileOutputStream); //Fecha o fileOutputStream //Melhorar este catch } catch (IOException ex) { System.out.println("Teste"); } finally { fileOutputStream.close(); wb.close(); //Fecha a pasta de trabalho } }
From source file:com.byd.test.actions.OrderAction.java
@RequestMapping("xopera") public ModelAndView xopera(HttpServletRequest request, HttpServletResponse response) { try {/*w ww.j ava2 s . c om*/ OutputStream out = response.getOutputStream(); response.reset(); response.setHeader("content-disposition", "attachment;filename=" + "fileName.xls"); response.setContentType("application/x-octetstream;charset=gb2312"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = null; String sheetName = "first sheet"; sheet = workbook.createSheet(sheetName); System.out.println("*-*-----------------------"); HSSFCell cell = sheet.createRow(1).createCell((short) 2); cell.setCellValue(323); workbook.write(out); out.flush(); out.close(); } catch (IOException ex) { Logger.getLogger(OrderAction.class.getName()).log(Level.SEVERE, null, ex); } String s = (String) request.getParameter("plantCode");//String a = request.getQueryString(""); System.out.println("plantCode: " + s); return new ModelAndView("myname"); }
From source file:com.carfinance.module.common.controller.DocumentDownloadController.java
/** * ????Excel/*from w w w . j a v a2 s . c om*/ * @param model * @param request * @param response */ @RequestMapping(value = "/cashierstatement", method = RequestMethod.GET) public void cashierStatement(Model model, HttpServletRequest request, HttpServletResponse response) { String contrace_id_str = request.getParameter("contrace_id"); String contrace_no = ""; String customer_name = ""; String customer_no = ""; String daily_available_km = ""; String contrace_type = ""; VehicleContraceInfo vehicleContraceInfo = this.vehicleServiceManageService .getVehicleContraceInfoById(Long.valueOf(contrace_id_str)); if (vehicleContraceInfo != null) { contrace_no = vehicleContraceInfo.getContrace_no(); customer_name = vehicleContraceInfo.getCustomer_name(); daily_available_km = vehicleContraceInfo.getDaily_available_km() + ""; } else { PropertyContraceInfo propertyContraceInfo = this.vehicleServiceManageService .getPropertyContraceInfoById(Long.valueOf(contrace_id_str)); if (propertyContraceInfo != null) { contrace_no = propertyContraceInfo.getContrace_no(); customer_name = propertyContraceInfo.getCustomer_name(); contrace_type = "?"; } } List<VehicleContraceVehsInfo> vehicleContraceVehsInfoList = this.vehicleServiceManageService .getVehicleContraceVehsListByContraceId(Long.valueOf(contrace_id_str)); //1.ContentType response.setContentType("multipart/form-data"); //2.????(??a.pdf) response.setHeader("Content-Disposition", "attachment;fileName=" + contrace_no + ".xls"); // webbookExcel HSSFWorkbook wb = new HSSFWorkbook(); for (VehicleContraceVehsInfo v : vehicleContraceVehsInfoList) { // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet(v.getLicense_plate()); // sheet0,??poiExcel?short HSSFRow row = sheet.createRow((int) 0); // ? HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ? HSSFCell cell = row.createCell((short) 0); cell.setCellValue("??"); cell.setCellStyle(style); cell = row.createCell((short) 1); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell((short) 2); cell.setCellValue(""); cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue("?"); cell.setCellStyle(style); cell = row.createCell((short) 4); cell.setCellValue(""); cell.setCellStyle(style); row = sheet.createRow(1); row.createCell((short) 0).setCellValue(customer_name); row.createCell((short) 1).setCellValue(customer_no); row.createCell((short) 2).setCellValue(contrace_type); row.createCell((short) 3).setCellValue(""); row.createCell((short) 4).setCellValue(""); row = sheet.createRow(2); HSSFCell cell2 = row.createCell((short) 0); cell2.setCellValue("?"); cell2.setCellStyle(style); cell2 = row.createCell((short) 1); cell2.setCellValue("??"); cell2.setCellStyle(style); cell2 = row.createCell((short) 2); cell2.setCellValue(""); cell2.setCellStyle(style); cell2 = row.createCell((short) 3); cell2.setCellValue(""); cell2.setCellStyle(style); cell2 = row.createCell((short) 4); cell2.setCellValue("??"); cell2.setCellStyle(style); row = sheet.createRow(3); row.createCell((short) 0).setCellValue(v.getKm()); row.createCell((short) 1).setCellValue(v.getOil_percent()); long over_km = (v.getReturn_km() - v.getKm()) > 0 ? (v.getReturn_km() - v.getKm()) : 0; row.createCell((short) 2).setCellValue(over_km); row.createCell((short) 3).setCellValue(""); row.createCell((short) 4).setCellValue(""); row = sheet.createRow(4); HSSFCell cell4 = row.createCell((short) 0); cell4.setCellValue(""); cell4.setCellStyle(style); cell4 = row.createCell((short) 1); cell4.setCellValue("?"); cell4.setCellStyle(style); cell4 = row.createCell((short) 2); cell4.setCellValue(""); cell4.setCellStyle(style); cell4 = row.createCell((short) 3); cell4.setCellValue(""); cell4.setCellStyle(style); cell4 = row.createCell((short) 4); cell4.setCellValue(""); cell4.setCellStyle(style); row = sheet.createRow(5); row.createCell((short) 0).setCellValue(v.getReturn_km()); row.createCell((short) 1).setCellValue(v.getRevert_oil_percent()); long over_oil = (v.getOil_percent() - v.getRevert_oil_percent()) > 0 ? (v.getOil_percent() - v.getRevert_oil_percent()) : 0; row.createCell((short) 2).setCellValue(over_oil); row.createCell((short) 3).setCellValue(""); row.createCell((short) 4).setCellValue(v.getActually_price()); } // ? try { String path = appProps.get("contrace.over.download.path") + contrace_no + ".xls"; FileOutputStream fout = new FileOutputStream(path); wb.write(fout); fout.close(); ServletOutputStream out; //File(?download.pdf) File file = new File(path); try { FileInputStream inputStream = new FileInputStream(file); //3.response?ServletOutputStream(out) out = response.getOutputStream(); int b = 0; byte[] buffer = new byte[512]; while (b != -1) { b = inputStream.read(buffer); //4.?(out) out.write(buffer, 0, b); } inputStream.close(); out.close(); out.flush(); } catch (IOException e) { e.printStackTrace(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:com.catexpress.util.FormatosPOI.java
public void formatoSolicitud(Solicitud solicitud, Set<Proveedor> proveedores) throws FileNotFoundException, IOException { Workbook wb = new HSSFWorkbook(); Sheet sheet;// ww w . j av a 2s . c o m int cont = 0; for (Proveedor proveedor : proveedores) { sheet = wb.createSheet(proveedor.getNombre()); Row rTitulo = sheet.createRow(0); CellRangeAddress craTitulo = new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 6 //last column (0-based) ); sheet.addMergedRegion(craTitulo); Cell titulo = rTitulo.createCell(0); titulo.setCellValue("SOLICITUD DE MERCANC?A"); titulo.setCellStyle(estiloHeader(wb, TITULO)); rTitulo.setHeightInPoints(20); Row rUsuario = sheet.createRow(1); CellRangeAddress craUsuario = new CellRangeAddress(1, 1, 0, 6); sheet.addMergedRegion(craUsuario); Cell usuario = rUsuario.createCell(0); usuario.setCellValue((solicitud.getUsuario().getNombre() + " " + solicitud.getUsuario().getApPaterno() + " " + solicitud.getUsuario().getApMaterno()).toUpperCase()); usuario.setCellStyle(estiloHeader(wb, USUARIO)); rUsuario.setHeightInPoints(25); Row rSucursal = sheet.createRow(2); CellRangeAddress craSucursal = new CellRangeAddress(2, 2, 0, 6); sheet.addMergedRegion(craSucursal); Cell sucursal = rSucursal.createCell(0); sucursal.setCellValue("Sucursal: " + solicitud.getSucursal().getNombre()); sucursal.setCellStyle(estiloHeader(wb, SUCURSAL)); RegionUtil.setBorderTop(sucursal.getCellStyle().getBorderTop(), craSucursal, sheet, wb); RegionUtil.setBorderLeft(sucursal.getCellStyle().getBorderLeft(), craSucursal, sheet, wb); RegionUtil.setBorderRight(sucursal.getCellStyle().getBorderRight(), craSucursal, sheet, wb); RegionUtil.setBorderBottom(sucursal.getCellStyle().getBorderBottom(), craSucursal, sheet, wb); rSucursal.setHeightInPoints(20); Row rBlank = sheet.createRow(3); Cell blank; for (int i = 0; i <= 6; i++) { blank = rBlank.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Row rFecha = sheet.createRow(4); Cell labelFecha = rFecha.createCell(0); labelFecha.setCellValue("FECHA:"); labelFecha.setCellStyle(estiloHeader(wb, LABEL)); CellRangeAddress craFecha = new CellRangeAddress(4, 4, 1, 3); sheet.addMergedRegion(craFecha); Cell fecha = rFecha.createCell(1); fecha.setCellValue(solicitud.getFechaSolicitud()); fecha.setCellStyle(estiloHeader(wb, FECHA)); for (int i = 4; i <= 6; i++) { blank = rFecha.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Row rVigencia = sheet.createRow(5); Cell labelVigencia = rVigencia.createCell(0); labelVigencia.setCellValue("VIGENCIA:"); labelVigencia.setCellStyle(estiloHeader(wb, LABEL)); CellRangeAddress craVigencia = new CellRangeAddress(5, 5, 1, 3); sheet.addMergedRegion(craVigencia); Cell vigencia = rVigencia.createCell(1); Calendar clndr = Calendar.getInstance(); clndr.setTime(solicitud.getFechaSolicitud()); clndr.add(Calendar.DAY_OF_MONTH, 3); vigencia.setCellValue(clndr.getTime()); vigencia.setCellStyle(estiloHeader(wb, FECHA)); blank = rVigencia.createCell(4); blank.setCellStyle(estiloVacio(wb)); Cell labelNoPedido = rVigencia.createCell(5); labelNoPedido.setCellValue("PEDIDO No:"); labelNoPedido.setCellStyle(estiloCuadro(wb, AMARILLO)); Cell noPedido = rVigencia.createCell(6); noPedido.setCellValue(solicitud.getId()); noPedido.setCellStyle(estiloCuadro(wb, AMARILLO)); Row rHoja = sheet.createRow(6); for (int i = 0; i <= 4; i++) { blank = rHoja.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Cell labelHoja = rHoja.createCell(5); labelHoja.setCellValue("HOJA:"); labelHoja.setCellStyle(estiloCuadro(wb, LABEL)); Cell hoja = rHoja.createCell(6); hoja.setCellValue(++cont + "/" + proveedores.size()); hoja.setCellStyle(estiloCuadro(wb, LABEL)); Row rProveedor = sheet.createRow(7); CellRangeAddress craProveedor = new CellRangeAddress(7, 8, 0, 2); sheet.addMergedRegion(craProveedor); Cell prov = rProveedor.createCell(0); prov.setCellValue(proveedor.getNombre()); prov.setCellStyle(estiloProveedor(wb)); for (int i = 3; i <= 6; i++) { blank = rProveedor.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Row rProveedor2 = sheet.createRow(8); for (int i = 3; i <= 6; i++) { blank = rProveedor2.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Row rTotales = sheet.createRow(9); for (int i = 0; i <= 1; i++) { blank = rTotales.createCell(i); blank.setCellStyle(estiloVacio(wb)); } Cell labelTotales = rTotales.createCell(2); labelTotales.setCellValue("TOTALES: "); labelTotales.setCellStyle(estiloTotales(wb)); blank = rTotales.createCell(3); blank.setCellStyle(estiloTotales(wb)); Cell totalSolicitado = rTotales.createCell(4); totalSolicitado.setCellStyle(estiloTotales(wb)); totalSolicitado.setCellType(CellType.FORMULA); totalSolicitado.setCellFormula("SUM(E12:E" + (11 + solicitud.getDetalles().size()) + ")"); Cell totalSurtido = rTotales.createCell(5); totalSurtido.setCellStyle(estiloTotales(wb)); totalSurtido.setCellType(CellType.FORMULA); totalSurtido.setCellFormula("SUM(F12:F" + (11 + solicitud.getDetalles().size()) + ")"); Cell totalNegado = rTotales.createCell(6); totalNegado.setCellStyle(estiloTotales(wb)); totalNegado.setCellType(CellType.FORMULA); totalNegado.setCellFormula("SUM(G12:G" + (11 + solicitud.getDetalles().size()) + ")"); Row rColumnas = sheet.createRow(10); Cell labelCodigo = rColumnas.createCell(0); labelCodigo.setCellValue("CODIGO"); labelCodigo.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelOpciones = rColumnas.createCell(1); labelOpciones.setCellValue("OPCIONES"); labelOpciones.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelModelo = rColumnas.createCell(2); labelModelo.setCellValue("MODELO / MATERIAL / COLOR"); labelModelo.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelTalla = rColumnas.createCell(3); labelTalla.setCellValue("TALLA"); labelTalla.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelSolicitado = rColumnas.createCell(4); labelSolicitado.setCellValue("SOLICITADO"); labelSolicitado.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelSurtido = rColumnas.createCell(5); labelSurtido.setCellValue("SURTIDO"); labelSurtido.setCellStyle(estiloColumnas(wb, COLUMNA)); Cell labelNegado = rColumnas.createCell(6); labelNegado.setCellValue("NEGADO"); labelNegado.setCellStyle(estiloColumnas(wb, COLUMNA)); Row rValues = sheet.createRow(11); Cell codigo; Cell opciones; Cell modelo; Cell talla; Cell solicitado; Cell surtido; Cell negado; for (Dsolicitud detalle : solicitud.getDetalles()) { if (detalle.getProducto().getProvedor().equals(proveedor)) { codigo = rValues.createCell(0); codigo.setCellValue(detalle.getProducto().getCBarras()); codigo.setCellStyle(estiloColumnas(wb, 0)); opciones = rValues.createCell(1); opciones.setCellValue(" - "); opciones.setCellStyle(estiloColumnas(wb, 0)); modelo = rValues.createCell(2); modelo.setCellValue(detalle.getProducto().getModelo().getNombre() + " / " + detalle.getProducto().getColor().getNombre()); modelo.setCellStyle(estiloColumnas(wb, 0)); talla = rValues.createCell(3); talla.setCellValue(detalle.getProducto().getTalla().getNombre()); talla.setCellStyle(estiloColumnas(wb, 0)); solicitado = rValues.createCell(4); solicitado.setCellValue(detalle.getCantidad()); solicitado.setCellStyle(estiloColumnas(wb, 0)); surtido = rValues.createCell(5); surtido.setCellStyle(estiloColumnas(wb, SURTIDO)); negado = rValues.createCell(6); negado.setCellStyle(estiloColumnas(wb, 0)); } } for (int i = 0; i <= 6; i++) { sheet.autoSizeColumn(i, true); } } // Write the output to a file FileOutputStream fileOut = new FileOutputStream("Solicitud" + solicitud.getId() + ".xls"); wb.write(fileOut); fileOut.close(); }
From source file:com.cimmyt.reports.impl.ServiceReportCustomQueryImpl.java
License:Apache License
public ServiceReportCustomQueryImpl() { book = new HSSFWorkbook(); sheet = book.createSheet();/* w w w. j av a2 s . c o m*/ plainStyle = book.createFont(); headerStyle = book.createFont(); plainStyle.setFontHeightInPoints((short) 11); plainStyle.setBoldweight((short) 11); plainStyle.setFontName(HSSFFont.FONT_ARIAL); BeanUtils.copyProperties(plainStyle, headerStyle); headerStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle = book.createCellStyle(); headerCellStyle.setWrapText(true); headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY); headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); headerCellStyle.setFont(headerStyle); plainCellStyle = book.createCellStyle(); plainCellStyle.setWrapText(true); plainCellStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY); plainCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); plainCellStyle.setFont(plainStyle); }
From source file:com.cimmyt.reports.impl.ServiceReportLaboratoryImpl.java
License:Apache License
private HSSFWorkbook createBookPlateExcel(StudyLabReportBean beanReport) { HSSFWorkbook book = new HSSFWorkbook(); // Create a new Sheet in book HSSFSheet sheet = book.createSheet(); // Creates a new row for headers HSSFRow row = sheet.createRow(0);//from w w w . j a v a 2 s. c o m // Creates a new cell for title HSSFCell cell = row.createCell(0); // Create cell contents. HSSFRichTextString text = new HSSFRichTextString(""); cell.setCellValue(text); // Creates a new HSSFRow rowHeaders = sheet.createRow(2); styleCellNormallyHeader = styleCellNormally(book, true); styleCellNormally = styleCellNormally(book, false); styleCellControl = getStyleCeldSolidForeground(book, cellControl); styleCellControlDART = getStyleCeldSolidForeground(book, cellControlDART); styleCellControlRandom = getStyleCeldSolidForeground(book, cellControlRandom); styleCellControlKBIo = getStyleCeldSolidForeground(book, cellKBiocontrolRandom); styleCellBlank = getStyleCeldSolidForeground(book, cellBlankForegroundColor); if (beanReport.getMapPlateSamples().size() > 0) { Iterator iteratorMapFirst = beanReport.getMapPlateSamples().entrySet().iterator(); int rowCounter = 1; while (iteratorMapFirst.hasNext()) { Map.Entry entry = (Map.Entry) iteratorMapFirst.next(); Map<String, SampleDetail> mapInner = (Map<String, SampleDetail>) entry.getValue(); Integer key = (Integer) entry.getKey(); sheet.createRow(rowCounter); rowCounter++; rowCounter = createHeaderPlate(sheet, rowCounter, beanReport.getNumberColumn(), styleCellNormallyHeader, beanReport.getPatternPlate() + key.toString()); int rowCounterLabel = 0; for (int sizeRow = 0; sizeRow < beanReport.getNameRow().length; sizeRow++) { HSSFRow rowData = sheet.createRow(rowCounter); for (int sizeColumn = 0; sizeColumn <= beanReport.getNumberColumn(); sizeColumn++) { if (sizeColumn == 0) { writeCell(rowData, sizeColumn, beanReport.getNameRow()[rowCounterLabel], styleCellNormallyHeader); rowCounterLabel++; } else { SampleDetail detail = mapInner.get(beanReport.getPatternPlate() + key.toString() + beanReport.getNameRow()[sizeRow] + (sizeColumn)); if (detail != null) { HSSFCellStyle style = null; String sampleName = ""; if (detail.getControltype() != null && !detail.getControltype().equals("")) style = validateStatusSample(detail.getControltype()); else sampleName = getFieldsReport(beanReport, detail); String strDetail = getTemplateFiled(detail); if (strDetail != null && !strDetail.isEmpty()) { if (!sampleName.isEmpty()) { sampleName = sampleName + "\n" + strDetail; } else { sampleName = sampleName + strDetail; } } writeCell(rowData, sizeColumn, sampleName, style); } } } rowCounter++; } } return book; } return null; }
From source file:com.cimmyt.reports.impl.ServiceReportLaboratoryImpl.java
License:Apache License
private HSSFWorkbook getBookResultData(ResultDataExportDataBean resultDataExport, PropertyHelper pro, SortedMap<Integer, ResultsPreferencesDetail> sortedMap) { HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow filaDatGral;/* www .j av a2 s . c o m*/ HSSFCell cellDatGral; HSSFRichTextString textDatGral; filaDatGral = sheet.createRow(0); cellDatGral = filaDatGral.createCell(0); textDatGral = new HSSFRichTextString(pro.getKey(LBL_GENERIC_PLATE)); cellDatGral.setCellValue(textDatGral); cellDatGral = filaDatGral.createCell(1); textDatGral = new HSSFRichTextString(resultDataExport.getListPlate()); cellDatGral.setCellValue(textDatGral); filaDatGral = sheet.createRow(1); cellDatGral = filaDatGral.createCell(0); textDatGral = new HSSFRichTextString(pro.getKey(LBL_GENERIC_EXPORT)); cellDatGral.setCellValue(textDatGral); cellDatGral = filaDatGral.createCell(1); textDatGral = new HSSFRichTextString(resultDataExport.getNameExport()); cellDatGral.setCellValue(textDatGral); filaDatGral = sheet.createRow(2); cellDatGral = filaDatGral.createCell(0); textDatGral = new HSSFRichTextString("Date"); cellDatGral.setCellValue(textDatGral); cellDatGral = filaDatGral.createCell(1); textDatGral = new HSSFRichTextString(resultDataExport.getDateExport()); cellDatGral.setCellValue(textDatGral); HSSFRow filaEncabezos = sheet.createRow(6); int colCounter = 0; for (ResultsPreferencesDetail resultsPreferencesDetail : sortedMap.values()) { HSSFCell headerCell = filaEncabezos.createCell(colCounter); HSSFRichTextString headerText = new HSSFRichTextString(resultsPreferencesDetail.getHeader()); headerCell.setCellValue(headerText); colCounter++; } int rowCounter = 7; colCounter = 0; for (RowResultDataBean bean : resultDataExport.getListResults()) { HSSFRow rowData = sheet.createRow(rowCounter); colCounter = 0; for (String str : bean.getListCell()) { if (str != null) { HSSFCell dataCell = rowData.createCell(colCounter); HSSFRichTextString cellValue = new HSSFRichTextString(str); dataCell.setCellValue(cellValue); } colCounter++; } rowCounter++; } return book; }
From source file:com.clican.pluto.dataprocess.engine.processes.ExcelProcessor.java
License:LGPL
@SuppressWarnings("unchecked") public void writeExcel(ProcessorContext context, ExcelExecBean execBean) throws Exception { Workbook book = null;//from w w w .jav a 2s.c o m InputStream is = null; try { is = execBean.getInputStream(); } catch (FileNotFoundException e) { } if (is != null) { book = WorkbookFactory.create(is); is.close(); } else { book = new HSSFWorkbook(); } CreationHelper createHelper = book.getCreationHelper(); CellStyle dateStyle = book.createCellStyle(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd")); CellStyle numStyle = book.createCellStyle(); numStyle.setDataFormat(createHelper.createDataFormat().getFormat("0.00000000")); CellStyle intNumStyle = book.createCellStyle(); intNumStyle.setDataFormat(createHelper.createDataFormat().getFormat("0")); List<Object> result = context.getAttribute(execBean.getParamName()); String[] columns = execBean.getColumns(); if (execBean.getColumns() != null) { columns = execBean.getColumns(); } else { columns = ((List<String>) context.getAttribute(execBean.getColumnsVarName())).toArray(new String[] {}); } String sheetName; if (StringUtils.isNotEmpty(execBean.getSheetName())) { sheetName = execBean.getSheetName(); } else { sheetName = context.getAttribute(execBean.getSheetVarName()).toString(); } // int number = book.getNumberOfSheets(); Sheet sheet = book.createSheet(sheetName); int rowNum = 0; Row firstRow = sheet.createRow(rowNum++); for (int i = 0; i < columns.length; i++) { Cell cell = firstRow.createCell(i); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(columns[i]); } for (int i = 0; i < result.size(); i++) { Object row = result.get(i); Row dataRow = sheet.createRow(rowNum++); for (int j = 0; j < columns.length; j++) { Object obj = PropertyUtils.getNestedProperty(row, columns[j]); Cell cell = dataRow.createCell(j); if (obj == null) { cell.setCellType(Cell.CELL_TYPE_BLANK); } else { if (obj instanceof String) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(obj.toString()); } else if (obj instanceof Date) { cell.setCellValue((Date) obj); cell.setCellStyle(dateStyle); } else if (obj instanceof Integer || obj instanceof Long) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(intNumStyle); cell.setCellValue(new Double(obj.toString())); } else if (obj instanceof Number) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(numStyle); cell.setCellValue(new Double(obj.toString())); } else { throw new DataProcessException("??Excel?"); } } } } OutputStream os = null; try { os = execBean.getOutputStream(); book.write(os); } finally { if (os != null) { os.close(); } } }