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:br.sp.telesul.service.ExportServiceImpl.java
public void writeExcelCertificacoes(String templateHead, String[] columns, HSSFWorkbook workbook) { try {// w w w .j a va2 s . co m List<Funcionario> funcionarios = funcionarioService.search(); HSSFSheet sheet = workbook.createSheet(templateHead); Row rowHeading = sheet.createRow(0); for (int i = 0; i < columns.length; i++) { rowHeading.createCell(i).setCellValue(columns[i]); } //Estilizar o Cabealho - Stylesheet the heading for (int i = 0; i < columns.length; i++) { CellStyle stylerowHeading = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBold(true); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 11); font.setColor(HSSFColor.WHITE.index); stylerowHeading.setFont(font); stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER); stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index); stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND); rowHeading.getCell(i).setCellStyle(stylerowHeading); } //Preencher linhas int r = 1; for (Funcionario f : funcionarios) { if (!f.getCertificacoes().isEmpty()) { for (Certificacao ct : f.getCertificacoes()) { Row row = sheet.createRow(r); CellStyle styleDate = workbook.createCellStyle(); HSSFDataFormat dfExame = workbook.createDataFormat(); styleDate.setDataFormat(dfExame.getFormat("dd/mm/yyyy")); try { Cell Nome = row.createCell(0); Nome.setCellValue(f.getNome()); } catch (NullPointerException e) { } try { Cell cod = row.createCell(1); cod.setCellValue(ct.getCodigo()); } catch (NullPointerException e) { } try { Cell nome = row.createCell(3); nome.setCellValue(ct.getNome()); } catch (NullPointerException e) { } try { Cell empresa = row.createCell(2); empresa.setCellValue(ct.getEmpresa()); } catch (NullPointerException e) { } try { Cell dtExame = row.createCell(4); dtExame.setCellValue(ct.getDtExame()); dtExame.setCellStyle(styleDate); } catch (NullPointerException e) { } try { Cell dtValidade = row.createCell(5); dtValidade.setCellValue(ct.getDtValidade()); dtValidade.setCellStyle(styleDate); } catch (NullPointerException e) { } try { Cell copia = row.createCell(6); copia.setCellValue(ct.getCopia()); } catch (NullPointerException e) { } r++; } } } for (int i = 0; i < columns.length; i++) { sheet.autoSizeColumn(i); } // String file = "C:/Users/ebranco.TELESULCORP/new.xls"; // FileOutputStream out = new FileOutputStream(file); // workbook.write(out); // out.close(); // workbook.close(); // System.out.println("Excell write succesfully"); } catch (Exception e) { System.out.println("Error" + e); } }
From source file:br.sp.telesul.service.ExportServiceImpl.java
public void writeExcelIdiomas(String templateHead, String[] columns, HSSFWorkbook workbook) { try {/*from w w w. j av a 2s . c o m*/ List<Funcionario> funcionarios = funcionarioService.search(); HSSFSheet sheet = workbook.createSheet(templateHead); Row rowHeading = sheet.createRow(0); for (int i = 0; i < columns.length; i++) { rowHeading.createCell(i).setCellValue(columns[i]); } //Estilizar o Cabealho - Stylesheet the heading for (int i = 0; i < columns.length; i++) { CellStyle stylerowHeading = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBold(true); font.setFontName(HSSFFont.FONT_ARIAL); font.setFontHeightInPoints((short) 11); font.setColor(HSSFColor.WHITE.index); stylerowHeading.setFont(font); stylerowHeading.setVerticalAlignment(CellStyle.ALIGN_CENTER); stylerowHeading.setFillForegroundColor(HSSFColor.ROYAL_BLUE.index); stylerowHeading.setFillPattern(CellStyle.SOLID_FOREGROUND); rowHeading.getCell(i).setCellStyle(stylerowHeading); } //Preencher linhas int r = 1; for (Funcionario f : funcionarios) { if (!f.getIdiomas().isEmpty()) { for (Idioma idm : f.getIdiomas()) { try { if (idm.getNivel() != null || idm.getNome() != null) { Row row = sheet.createRow(r); Cell Nome = row.createCell(0); Nome.setCellValue(f.getNome()); Cell language = row.createCell(1); language.setCellValue(idm.getNome().toString()); Cell nivel = row.createCell(2); nivel.setCellValue(idm.getNivel().toString()); r++; } } catch (NullPointerException ne) { System.out.println("Error " + ne); break; } } } } for (int i = 0; i < columns.length; i++) { sheet.autoSizeColumn(i); } } catch (Exception e) { System.out.println("Error" + e); } }
From source file:br.sp.telesul.service.ExportServiceImpl.java
public void autoSizeColum(HSSFSheet sheet, String[] columns) { for (int i = 0; i < columns.length; i++) { sheet.autoSizeColumn(i); }//ww w. j a va2s . c o m }
From source file:cn.mario256.blog.ExcelView.java
License:Open Source License
/** * ?Excel// w w w . j ava 2s.c o m * * @param model * ? * @param workbook * HSSFWorkbook * @param request * HttpServletRequest * @param response * HttpServletResponse */ 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:co.com.controlestatidisticoshewhart.main.VentanaPrincipal.java
private void obtenerRutaGuardarArchivo() { try {//from w w w.j ava 2s . c o m JFileChooser file = new JFileChooser(); file.showSaveDialog(this); File guarda = file.getSelectedFile(); if (guarda != null) { String nombreArchivo = guarda.getAbsolutePath() + Constante.XLS; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet hojaUnoDatosGenerados = workbook.createSheet(Constante.TITULO_HOJA_DATOS); HSSFSheet hojaDosDatosResultados = workbook.createSheet(Constante.TITULO_HOJA_RESULTADOS); List<Dato> datosGenerados = DatosExcelSingleton.getInstance().getListaDatosExcel(); Map resultadosCalculo = DatosExcelSingleton.getInstance().getResultadosDeCalculo(); HSSFRow rowhead = hojaUnoDatosGenerados.createRow((short) 0); /** Encabezado hoja uno. */ rowhead.createCell(0).setCellValue(Constante.INDICE); rowhead.createCell(1).setCellValue(Constante.DATO_ALEATORIO); rowhead.createCell(2).setCellValue(Constante.SUPERA_LIMITE_INFERIOR); rowhead.createCell(3).setCellValue(Constante.SUPERA_LIMITE_SUPERIOR); HSSFRow rowheadHojaDos = hojaDosDatosResultados.createRow((short) 0); /** Encabezado hoja dos. */ rowheadHojaDos.createCell(0).setCellValue(Constante.COLUMNA_LIMITE_INFERIOR); rowheadHojaDos.createCell(1).setCellValue(Constante.COLUMNA_LIMITE_SUPERIOR); rowheadHojaDos.createCell(2).setCellValue(Constante.COLUMNA_MEDIA); rowheadHojaDos.createCell(3).setCellValue(Constante.COLUMNA_DATOS_FUERA_LIMITE); rowheadHojaDos.createCell(4).setCellValue(Constante.COLUMNA_ARL); /** * Escribir en hoja uno. */ HSSFRow row = null; int fila = 1; for (int i = 0; i < datosGenerados.size(); i++) { row = hojaUnoDatosGenerados.createRow(fila++); row.createCell(0).setCellValue(datosGenerados.get(i).getSecuencial()); row.createCell(1).setCellValue(datosGenerados.get(i).getNumero()); row.createCell(2).setCellValue(datosGenerados.get(i).isSobrePasaLimiteInferior()); row.createCell(3).setCellValue(datosGenerados.get(i).isSobrePasaLimiteSuperior()); } /** * Escribir en hoja dos. */ HSSFRow filaResultados = null; filaResultados = hojaDosDatosResultados.createRow(1); filaResultados.createCell(0) .setCellValue(resultadosCalculo.get(Constante.LIMITE_CONTROL_INFERIOR).toString()); filaResultados.createCell(1) .setCellValue(resultadosCalculo.get(Constante.LIMITE_CONTROL_SUPERIOR).toString()); filaResultados.createCell(2) .setCellValue(resultadosCalculo.get(Constante.MEDIA_DATOS_EXTREMOS).toString()); filaResultados.createCell(3) .setCellValue(resultadosCalculo.get(Constante.CANTIDAD_DATOS_EXTREMOS).toString()); filaResultados.createCell(4) .setCellValue(resultadosCalculo.get(Constante.AVERAGE_RUN_LENGTH).toString()); /** * Ajustar columnas hoja uno. */ hojaUnoDatosGenerados.autoSizeColumn(0); hojaUnoDatosGenerados.autoSizeColumn(1); hojaUnoDatosGenerados.autoSizeColumn(2); hojaUnoDatosGenerados.autoSizeColumn(3); /** * Ajustar columnas hoja dos. */ hojaDosDatosResultados.autoSizeColumn(0); hojaDosDatosResultados.autoSizeColumn(1); hojaDosDatosResultados.autoSizeColumn(2); hojaDosDatosResultados.autoSizeColumn(3); hojaDosDatosResultados.autoSizeColumn(4); try (FileOutputStream fileOut = new FileOutputStream(nombreArchivo)) { workbook.write(fileOut); } catch (Exception e) { throw new Exception("Error al escribir el archivo. Intente de nuevo."); } mensaje(Constante.ARCHIVO_EXCEL_CON_RESULTADOS_GENERADO); JOptionPane.showMessageDialog(null, "El archivo se ha guardado Exitosamente.", "Informacin", JOptionPane.INFORMATION_MESSAGE); } else { mensaje("Debe seleccionar una ruta valida e ingresar el nombre del archivo."); JOptionPane.showMessageDialog(null, "Debe seleccionar una ruta valida e ingresar el nombre del archivo.", "Advertencia", JOptionPane.WARNING_MESSAGE); } } catch (Exception ex) { mensaje("Error al generar el archivo.Intentelo de nuevo."); JOptionPane.showMessageDialog(null, "Su archivo no se ha guardado", "Advertencia", JOptionPane.WARNING_MESSAGE); } }
From source file:com.aan.girsang.client.ui.master.barang.BarangPanel.java
private void exportExcel(List<Barang> dataList) throws IOException { if (dataList != null && !dataList.isEmpty()) { HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet(); HSSFSheet worksheet = workBook.createSheet("Sheet 0"); // Nama Field Row judul = sheet.createRow((short) 0); Cell cell = judul.createCell((short) 0); cell.setCellValue("This is a test of merging"); HSSFRow headingRow = sheet.createRow((short) 2); headingRow.createCell((short) 0).setCellValue("ID"); headingRow.createCell((short) 1).setCellValue("BARCODE 1"); headingRow.createCell((short) 2).setCellValue("BARCODE 2"); headingRow.createCell((short) 3).setCellValue("NAMA BARANG"); headingRow.createCell((short) 4).setCellValue("GOLONGAN"); headingRow.createCell((short) 5).setCellValue("SAT. JUAL"); headingRow.createCell((short) 6).setCellValue("ST. TOKO"); headingRow.createCell((short) 7).setCellValue("ST. GUDANG"); headingRow.createCell((short) 8).setCellValue("SAT. BELI"); headingRow.createCell((short) 9).setCellValue("ISI PEM."); headingRow.createCell((short) 10).setCellValue("HRG PEM."); headingRow.createCell((short) 11).setCellValue("HRG NORMAL"); headingRow.createCell((short) 12).setCellValue("HRG MEMBER"); headingRow.createCell((short) 13).setCellValue("JUAL"); int panjang = headingRow.getLastCellNum() - 1; short rowNo = 3; sheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) panjang //last column (0-based) ));/*from w ww .ja va 2 s.co m*/ CellStyle styleData = workBook.createCellStyle(); styleData.setBorderBottom(CellStyle.BORDER_THIN); styleData.setBorderRight(CellStyle.BORDER_THIN); styleData.setBorderLeft(CellStyle.BORDER_THIN); for (Barang b : dataList) { HSSFRow row = sheet.createRow(rowNo); String jual; if (b.getJual() == true) { jual = "Jual"; } else { jual = "Tidak"; } row.createCell((short) 0).setCellValue(b.getPlu()); row.createCell((short) 1).setCellValue(b.getBarcode1()); row.createCell((short) 2).setCellValue(b.getBarcode2()); row.createCell((short) 3).setCellValue(b.getNamaBarang()); row.createCell((short) 4).setCellValue(b.getGolonganBarang().getGolonganBarang()); row.createCell((short) 5).setCellValue(b.getSatuan()); row.createCell((short) 6).setCellValue(b.getStokToko()); row.createCell((short) 7).setCellValue(b.getStokGudang()); row.createCell((short) 8).setCellValue(b.getSatuanPembelian()); row.createCell((short) 9).setCellValue(b.getIsiPembelian()); row.createCell((short) 10).setCellValue(TextComponentUtils.formatNumber(b.getHargaBeli())); row.createCell((short) 11).setCellValue(TextComponentUtils.formatNumber(b.getHargaNormal())); row.createCell((short) 12).setCellValue(TextComponentUtils.formatNumber(b.getHargaMember())); row.createCell((short) 13).setCellValue(jual); for (int i = 0; i <= 13; i++) { row.getCell((short) i).setCellStyle(styleData); } rowNo++; } for (int i = 0; i <= 13; i++) { sheet.autoSizeColumn(i); } Font font = workBook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); //style judul CellStyle styleTitle = workBook.createCellStyle(); styleTitle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION); styleTitle.setFont(font); judul.getCell(0).setCellStyle(styleTitle); //judul field CellStyle styleHeading = workBook.createCellStyle(); styleHeading.setFont(font); styleHeading.setAlignment(CellStyle.ALIGN_CENTER_SELECTION); styleHeading.setBorderBottom(CellStyle.BORDER_THIN); styleHeading.setBorderTop(CellStyle.BORDER_THIN); styleHeading.setBorderRight(CellStyle.BORDER_THIN); styleHeading.setBorderLeft(CellStyle.BORDER_THIN); for (int i = 0; i < headingRow.getLastCellNum(); i++) {//For each cell in the row headingRow.getCell(i).setCellStyle(styleHeading);//Set the style } String file = "D:/Student_detais.xls"; try { try (FileOutputStream fos = new FileOutputStream(file)) { workBook.write(fos); } JOptionPane.showMessageDialog(null, "Sukses"); } catch (FileNotFoundException e) { System.out.println("Invalid directory or file not found"); } catch (IOException e) { System.out.println("Error occurred while writting excel file to directory"); } } }
From source file:com.accounting.accountMBean.DifferentAccReports.java
public void postProcessXLS(Object document) { String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate1"); ndc = new NepaliDateConverter(); HSSFWorkbook wb = (HSSFWorkbook) document; HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFCellStyle headerCellStyle1 = wb.createCellStyle(); HSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); HSSFSheet sheet = wb.getSheetAt(0); int noOfColumns = sheet.getRow(0).getLastCellNum(); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i); }//from w w w. ja v a 2s . co m sheet.shiftRows(0, sheet.getLastRowNum(), 4); HSSFRow firstRow = sheet.createRow(1); firstRow.createCell(0).setCellValue(PageName); firstRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow secondRow = sheet.createRow(0); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow thirdRow = sheet.createRow(3); String generatedDate = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + generatedDate + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); HSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("To Date: " + startDateString); fourthRow.getCell(0).setCellStyle(headerCellStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 4)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 4)); // HSSFRow lastRow; // double totalDr = 0; // for (Row row : sheet) { // if (row.getRowNum() > 4) { // String cost = row.getCell(3).getStringCellValue(); // if (cost != null && !cost.isEmpty()) { // row.getCell(3).setCellType(HSSFCell.CELL_TYPE_BLANK); // row.getCell(3).setCellType(HSSFCell.CELL_TYPE_NUMERIC); // row.getCell(3).setCellValue(Double.parseDouble(cost.replace(",", ""))); // totalDr += Double.parseDouble(cost.replace(",", "")); // } // } // } }
From source file:com.accounting.accountMBean.DifferentAccReports.java
public void postProcessXLSAgeingReport(Object document) { ndc = new NepaliDateConverter(); String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateReading = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate5"); HSSFWorkbook wb = (HSSFWorkbook) document; HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFCellStyle headerCellStyle1 = wb.createCellStyle(); HSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); HSSFSheet sheet = wb.getSheetAt(0); int noOfColumns = sheet.getRow(0).getLastCellNum(); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i); }/*w ww . j av a 2s . c om*/ sheet.shiftRows(0, sheet.getLastRowNum(), 4); HSSFRow firstRow = sheet.createRow(1); firstRow.createCell(0).setCellValue(PageName); firstRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow secondRow = sheet.createRow(0); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow thirdRow = sheet.createRow(3); String generatedDate = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + generatedDate + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); HSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("From: " + startDateString + " To: " + endDateReading); fourthRow.getCell(0).setCellStyle(headerCellStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 6)); for (Row row : sheet) { if (row.getRowNum() > 4) { String cost = row.getCell(4).getStringCellValue(); if (cost != null && !cost.isEmpty()) { row.getCell(4).setCellType(HSSFCell.CELL_TYPE_BLANK); row.getCell(4).setCellType(HSSFCell.CELL_TYPE_NUMERIC); row.getCell(4).setCellValue(Double.parseDouble(cost.replace(",", ""))); } } } }
From source file:com.accounting.inventory.InventorySalesMBean.java
public void postProcessXLSSalesBetDate(Object document) { String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate5"); HSSFWorkbook wb = (HSSFWorkbook) document; HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFCellStyle headerCellStyle1 = wb.createCellStyle(); HSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); HSSFSheet sheet = wb.getSheetAt(0); int noOfColumns = sheet.getRow(0).getLastCellNum(); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i); }//from w w w . j av a 2 s . c o m sheet.shiftRows(0, sheet.getLastRowNum(), 4); HSSFRow firstRow = sheet.createRow(1); firstRow.createCell(0).setCellValue("SALES INVOICE REPORT"); firstRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow secondRow = sheet.createRow(0); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow thirdRow = sheet.createRow(3); String date = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); HSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString); fourthRow.getCell(0).setCellStyle(headerCellStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 6)); for (Row row : sheet) { if (row.getRowNum() > 4) { for (int i = 4; i < 7; i++) { String cost1 = row.getCell(i).getStringCellValue(); if (!cost1.isEmpty()) { row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK); row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC); row.getCell(i).setCellValue(Double.valueOf(cost1)); } } } } }
From source file:com.accounting.inventory.InventorySalesMBean.java
public void postProcessXLS(Object document) { String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate5"); HSSFWorkbook wb = (HSSFWorkbook) document; HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFCellStyle headerCellStyle1 = wb.createCellStyle(); HSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); HSSFSheet sheet = wb.getSheetAt(0); int noOfColumns = sheet.getRow(0).getLastCellNum(); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i); }/* w ww .java 2s. com*/ sheet.shiftRows(0, sheet.getLastRowNum(), 4); HSSFRow firstRow = sheet.createRow(1); firstRow.createCell(0).setCellValue("SALES REPORT"); firstRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow secondRow = sheet.createRow(0); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow thirdRow = sheet.createRow(3); String date = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); if (!displayAll) { if (stage == 1) { HSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString); fourthRow.getCell(0).setCellStyle(headerCellStyle); } } sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 6)); HSSFRow lastRow; double totalSold = 0; for (Row row : sheet) { if (row.getRowNum() > 4) { String cost = row.getCell(3).getStringCellValue(); if (cost != null && !cost.isEmpty()) { row.getCell(3).setCellType(HSSFCell.CELL_TYPE_BLANK); row.getCell(3).setCellType(HSSFCell.CELL_TYPE_NUMERIC); row.getCell(3).setCellValue(Double.valueOf(cost)); totalSold += Double.parseDouble(cost.replace(",", "")); } } } for (Row row : sheet) { if (row.getRowNum() > 4) { for (int i = 4; i < 5; i++) { String cost1 = row.getCell(i).getStringCellValue(); row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK); row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC); row.getCell(i).setCellValue(Double.valueOf(cost1)); // totalSold += Double.valueOf(row.getCell(3).getStringCellValue()); } } } lastRow = sheet.createRow(sheet.getLastRowNum() + 1); HSSFCell totalSumTextCell = lastRow.createCell(2); totalSumTextCell.setCellValue("Total Sales Amount: "); HSSFCell totalUnitsCell = lastRow.createCell(3); totalUnitsCell.setCellValue(totalSold); }