List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createSheet
@Override
public HSSFSheet createSheet(String sheetname)
From source file:com.ibm.ioes.utilities.MailForProvisioning.java
public HSSFWorkbook generateExcel_Multiple(ArrayList<ACSMailTemplateDto> mailDataList, String mailProvisioningFlag) { HSSFWorkbook wb = null; try {/*w w w. ja v a 2 s. c o m*/ ACSMailTemplateDto mailObjDto = null; wb = new HSSFWorkbook(); HSSFSheet mailProvisionSheet = wb.createSheet("Mail Provision"); HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(boldFont); HSSFRow excelRow = null; HSSFCell excelCell = null; excelRow = mailProvisionSheet.createRow(0); excelRow = mailProvisionSheet.createRow(0); if ("PROVISIONING_MAIL_OVCC".equals(mailProvisioningFlag)) { excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(1); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account name")); excelCell = excelRow.createCell(2); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ISS Order no")); excelCell = excelRow.createCell(3); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Product")); excelCell = excelRow.createCell(4); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent ID")); excelCell = excelRow.createCell(5); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID")); excelCell = excelRow.createCell(6); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("OVCC ID")); excelCell = excelRow.createCell(7); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Bundled OVCC plan")); excelCell = excelRow.createCell(8); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Chairperson Name")); excelCell = excelRow.createCell(9); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Person Name")); excelCell = excelRow.createCell(10); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Address")); excelCell = excelRow.createCell(11); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Status")); } else { excelCell = excelRow.createCell(0); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Date of Creation")); excelCell = excelRow.createCell(1); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Account name")); excelCell = excelRow.createCell(2); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ISS Order no")); excelCell = excelRow.createCell(3); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Product")); excelCell = excelRow.createCell(4); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Parent ID")); excelCell = excelRow.createCell(5); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Child ID")); excelCell = excelRow.createCell(6); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("VCS ID")); excelCell = excelRow.createCell(7); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Bundled VCS plan")); excelCell = excelRow.createCell(8); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Chairperson Name")); excelCell = excelRow.createCell(9); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Person Name")); excelCell = excelRow.createCell(10); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Billing Address")); excelCell = excelRow.createCell(11); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("ID/HW address")); excelCell = excelRow.createCell(12); excelCell.setCellStyle(headerCellStyle); excelCell.setCellValue(new HSSFRichTextString("Status")); } int iCount = 1; for (int i = 0; i < mailDataList.size(); i++) { mailObjDto = mailDataList.get(i); excelRow = mailProvisionSheet.createRow(iCount++); if ("PROVISIONING_MAIL_OVCC".equals(mailProvisioningFlag)) { excelCell = excelRow.createCell(0); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(1); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccountName()))); excelCell = excelRow.createCell(2); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(3); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getProductName()))); excelCell = excelRow.createCell(4); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(5); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(6); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getMultipleId()))); excelCell = excelRow.createCell(7); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPackageName()))); excelCell = excelRow.createCell(8); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(9); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(10); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBillingAddress()))); excelCell = excelRow.createCell(11); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getStatus()))); } else { excelCell = excelRow.createCell(0); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getDateOfCreation()))); excelCell = excelRow.createCell(1); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getAccountName()))); excelCell = excelRow.createCell(2); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getOrderNo()))); excelCell = excelRow.createCell(3); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getProductName()))); excelCell = excelRow.createCell(4); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getParentId()))); excelCell = excelRow.createCell(5); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChildId()))); excelCell = excelRow.createCell(6); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getMultipleId()))); excelCell = excelRow.createCell(7); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getPackageName()))); excelCell = excelRow.createCell(8); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getChairPersonName()))); excelCell = excelRow.createCell(9); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBcpName()))); excelCell = excelRow.createCell(10); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getBillingAddress()))); excelCell = excelRow.createCell(11); excelCell.setCellValue( new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getHwAddress()))); excelCell = excelRow.createCell(12); excelCell .setCellValue(new HSSFRichTextString("" + Utility.fnCheckNull(mailObjDto.getStatus()))); } } } catch (Exception ex) { Utility.LOG(true, true, "Error while fetching accounts for provisiong : " + new Date()); } return wb; }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
public void generarExcelVentasCantadas() throws IOException { cargaResumen();// w ww . j a v a 2s. com if (ventasCantadas == null || ventasCantadas.isEmpty()) { JsfUtil.addErrorMessage("No hay datos para generar"); } else { Map<String, List<ResumenVentasCantadas>> map2 = new HashMap<>(); for (ResumenVentasCantadas rc : ventasCantadas) { List<ResumenVentasCantadas> valueList = map2.get(rc.getTerritorio()); if (valueList == null) { valueList = new ArrayList<>(); valueList.add(rc); map2.put(rc.getTerritorio(), valueList); } else { valueList.add(rc); } } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Ventas Detalle"); configAnchoCols(sheet); //Fonts Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTotal3 = workbook.createFont(); fontTotal3.setFontHeightInPoints((short) 8); fontTotal3.setColor(HSSFColor.RED.index); fontTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTerritorioTotal3 = workbook.createFont(); fontTerritorioTotal3.setFontHeightInPoints((short) 8); fontTerritorioTotal3.setColor(HSSFColor.ORANGE.index); fontTerritorioTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Estilos DataFormat format = workbook.createDataFormat(); CellStyle styleTotal3 = workbook.createCellStyle(); styleTotal3.setFont(fontTotal3); styleTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setDataFormat(format.getFormat("0.0%")); styleTerritorioTotal3 = workbook.createCellStyle(); styleTerritorioTotal3.setFont(fontTerritorioTotal3); styleTerritorioTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTerritorioTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); creaCabecera(workbook, sheet); String[] aCols20 = null; String[] aCols10 = null; String[] aColsGrue = null; String indicesTotales = ""; for (Map.Entry<String, List<ResumenVentasCantadas>> entry : map2.entrySet()) { ++indexRow; HSSFRow row = sheet.createRow((++indexRow)); HSSFCell cellTerr = row.createCell(indexCol); cellTerr.setCellValue(entry.getKey().toUpperCase()); cellTerr.setCellStyle(styleTerritorio); int indexInicioGrupo = indexRow + 2; List<ResumenVentasCantadas> detalles = entry.getValue(); Comparator<ResumenVentasCantadas> comp = new Comparator<ResumenVentasCantadas>() { @Override public int compare(ResumenVentasCantadas o1, ResumenVentasCantadas o2) { return o1.getOrden() > o2.getOrden() ? 1 : -1; } }; Collections.sort(detalles, comp); for (ResumenVentasCantadas rv : detalles) { cols20 = ""; cols10 = ""; colsGrue = ""; int indexFilaActual = ++indexRow; HSSFRow row1 = sheet.createRow((indexFilaActual)); HSSFCell cellZona = row1.createCell(indexCol + 0); cellZona.setCellValue(rv.getZona()); cellZona.setCellStyle(styleTitulo9); HSSFCell cellVendedor = row1.createCell(indexCol + 1); cellVendedor.setCellValue(rv.getVendedor()); cellVendedor.setCellStyle(styleTitulo9); HSSFCell cellBoletas = row1.createCell(indexCol + 2); cellBoletas.setCellValue(rv.getCantboletas()); cellBoletas.setCellStyle(styleCantidad); generarDetalles(row1, indexFilaActual, rv); } int indexFinGrupo = indexRow + 1; int indexTotal1 = ++indexRow; int indexTotal2 = ++indexRow; int indexTotal3 = ++indexRow; HSSFRow rowTotal1 = sheet.createRow(indexTotal1); HSSFRow rowTotal2 = sheet.createRow((indexTotal2)); HSSFRow rowTotal3 = sheet.createRow((indexTotal3)); aCols20 = cols20.split(","); aCols10 = cols10.split(","); aColsGrue = colsGrue.split(","); //TOTAL 1 generarTotal1(rowTotal1, indexInicioGrupo, indexFinGrupo, aColsGrue, aCols20, aCols10, cellTerr); //TOTAL 2 generarTotal2(rowTotal2, indexFinGrupo, aColsGrue, cellTerr); //TOTAL 3 generarTotal3(rowTotal3, indexFinGrupo, styleTotal3, cellTerr, sheet); //++indexRow; indicesTotales += (indexFinGrupo + 1) + ","; } String[] aIndexTotales = indicesTotales.split(","); //TOTAL pais int indexTotalPais = ++indexRow; HSSFRow rowTotalPais = sheet.createRow((indexTotalPais)); generarTotalPais(rowTotalPais, styleTotal3, sheet, aIndexTotales, aColsGrue, aCols20, aCols10); HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance() .getExternalContext().getResponse(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Ventas Cantadas.xls"); workbook.write(response.getOutputStream()); response.getOutputStream().flush(); response.getOutputStream().close(); FacesContext.getCurrentInstance().responseComplete(); indexRow = 1; } }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
public void generarExcelVentasCantadasDetalle() throws IOException { cargaResumenPorVendedor();/*from w w w. j a v a 2s. com*/ if (ventasCantadas == null || ventasCantadas.isEmpty()) { JsfUtil.addErrorMessage("No hay datos para generar"); } else { Map<String, List<ResumenVentasCantadas>> map2 = new HashMap<>(); for (ResumenVentasCantadas rc : ventasCantadas) { List<ResumenVentasCantadas> valueList = map2.get(rc.getTerritorio()); if (valueList == null) { valueList = new ArrayList<>(); valueList.add(rc); map2.put(rc.getTerritorio(), valueList); } else { valueList.add(rc); } } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Ventas Detalle"); configAnchoCols(sheet); //Fonts Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTotal3 = workbook.createFont(); fontTotal3.setFontHeightInPoints((short) 8); fontTotal3.setColor(HSSFColor.RED.index); fontTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Fonts Font fontTerritorioTotal3 = workbook.createFont(); fontTerritorioTotal3.setFontHeightInPoints((short) 8); fontTerritorioTotal3.setColor(HSSFColor.ORANGE.index); fontTerritorioTotal3.setBoldweight(Font.BOLDWEIGHT_BOLD); //Estilos DataFormat format = workbook.createDataFormat(); CellStyle styleTotal3 = workbook.createCellStyle(); styleTotal3.setFont(fontTotal3); styleTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleTotal3.setDataFormat(format.getFormat("0.0%")); styleTerritorioTotal3 = workbook.createCellStyle(); styleTerritorioTotal3.setFont(fontTerritorioTotal3); styleTerritorioTotal3.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleTerritorioTotal3.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); // styleTerritorioTotal3.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); creaCabecera(workbook, sheet); String[] aCols20 = null; String[] aCols10 = null; String[] aColsGrue = null; String indicesTotales = ""; for (Map.Entry<String, List<ResumenVentasCantadas>> entry : map2.entrySet()) { ++indexRow; HSSFRow row = sheet.createRow((++indexRow)); HSSFCell cellTerr = row.createCell(indexCol); cellTerr.setCellValue(entry.getKey().toUpperCase()); cellTerr.setCellStyle(styleTerritorio); int indexInicioGrupo = indexRow + 2; List<ResumenVentasCantadas> detalles = entry.getValue(); Comparator<ResumenVentasCantadas> comp = new Comparator<ResumenVentasCantadas>() { @Override public int compare(ResumenVentasCantadas o1, ResumenVentasCantadas o2) { return o1.getOrden() > o2.getOrden() ? 1 : -1; } }; Collections.sort(detalles, comp); for (ResumenVentasCantadas rv : detalles) { cols20 = ""; cols10 = ""; colsGrue = ""; int indexFilaActual = ++indexRow; HSSFRow row1 = sheet.createRow((indexFilaActual)); HSSFCell cellZona = row1.createCell(indexCol + 0); cellZona.setCellValue(rv.getZona()); cellZona.setCellStyle(styleTitulo9); HSSFCell cellVendedor = row1.createCell(indexCol + 1); cellVendedor.setCellValue(rv.getVendedor()); cellVendedor.setCellStyle(styleTitulo9); HSSFCell cellBoletas = row1.createCell(indexCol + 2); cellBoletas.setCellValue(rv.getCantboletas()); cellBoletas.setCellStyle(styleCantidad); generarDetalles(row1, indexFilaActual, rv); } int indexFinGrupo = indexRow + 1; int indexTotal1 = ++indexRow; int indexTotal2 = ++indexRow; int indexTotal3 = ++indexRow; HSSFRow rowTotal1 = sheet.createRow(indexTotal1); HSSFRow rowTotal2 = sheet.createRow((indexTotal2)); HSSFRow rowTotal3 = sheet.createRow((indexTotal3)); aCols20 = cols20.split(","); aCols10 = cols10.split(","); aColsGrue = colsGrue.split(","); //TOTAL 1 generarTotal1(rowTotal1, indexInicioGrupo, indexFinGrupo, aColsGrue, aCols20, aCols10, cellTerr); //TOTAL 2 generarTotal2(rowTotal2, indexFinGrupo, aColsGrue, cellTerr); //TOTAL 3 generarTotal3(rowTotal3, indexFinGrupo, styleTotal3, cellTerr, sheet); //++indexRow; indicesTotales += (indexFinGrupo + 1) + ","; } String[] aIndexTotales = indicesTotales.split(","); //TOTAL pais int indexTotalPais = ++indexRow; HSSFRow rowTotalPais = sheet.createRow((indexTotalPais)); generarTotalPais(rowTotalPais, styleTotal3, sheet, aIndexTotales, aColsGrue, aCols20, aCols10); HttpServletResponse response = (HttpServletResponse) FacesContext.getCurrentInstance() .getExternalContext().getResponse(); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Ventas Cantadas.xls"); workbook.write(response.getOutputStream()); response.getOutputStream().flush(); response.getOutputStream().close(); FacesContext.getCurrentInstance().responseComplete(); indexRow = 1; } }
From source file:com.idega.block.datareport.business.SimpleReportBusinessBean.java
License:Open Source License
public void writeSimpleExcelFile(JRDataSource reportData, String nameOfReport, String filePathAndName, ReportDescription description) throws IOException { if (nameOfReport == null || "".equals(nameOfReport)) { nameOfReport = NAME_OF_REPORT;// www . j a va2 s .com } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(TextSoap.encodeToValidExcelSheetName(nameOfReport)); int rowIndex = 0; //-- Report Name --// // Create a row and put some cells in it. Rows are 0 based. HSSFRow row = sheet.createRow((short) rowIndex++); // Create a cell and put a value in it. HSSFCell cell = row.createCell((short) 0); // Create a new font and alter it. HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 24); font.setFontName(REPORT_FONT); font.setItalic(true); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // Fonts are set into a style so create a new one to use. HSSFCellStyle style = wb.createCellStyle(); style.setFont(font); // Create a cell and put a value in it. cell.setCellValue(nameOfReport); cell.setCellStyle(style); //-- Report Parameters --// rowIndex++; HSSFRow row1 = null; String parameterString = ""; List labels = description.getListOfHeaderParameterLabelKeys(); List parameters = description.getListOfHeaderParameterKeys(); Iterator labelIter = labels.iterator(); Iterator parameterIter = parameters.iterator(); boolean newLineForeEachParameter = description.doCreateNewLineForEachParameter(); while (labelIter.hasNext() && parameterIter.hasNext()) { String label = description.getParameterOrLabelName((String) labelIter.next()); String parameter = description.getParameterOrLabelName((String) parameterIter.next()); if (newLineForeEachParameter) { row1 = sheet.createRow((short) rowIndex++); row1.createCell((short) 0).setCellValue(label + " " + parameter); } else { parameterString += label + " " + parameter + " "; } } if (!newLineForeEachParameter) { row1 = sheet.createRow((short) rowIndex++); row1.createCell((short) 0).setCellValue(parameterString); } rowIndex++; //-- Report ColumnHeader --// List fields = description.getListOfFields(); HSSFRow headerRow = sheet.createRow((short) rowIndex++); HSSFCellStyle headerCellStyle = wb.createCellStyle(); headerCellStyle.setWrapText(true); headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFFont headerCellFont = wb.createFont(); //headerCellFont.setFontHeightInPoints((short)12); headerCellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerCellStyle.setFont(headerCellFont); int colIndex = 0; int columnWithUnit = 256; // the unit is 1/256 of a character int numberOfCharactersPerLineInLongTextFields = 60; int numberOfCharactersPerLineInRatherLongTextFields = 35; int numberOfCharactersPerLineInUndifinedTextFields = 20; for (Iterator iter = fields.iterator(); iter.hasNext(); colIndex++) { ReportableField field = (ReportableField) iter.next(); HSSFCell headerCell = headerRow.createCell((short) colIndex); headerCell.setCellValue(description.getColumnName(field)); headerCell.setCellStyle(headerCellStyle); //column width int fieldsMaxChar = field.getMaxNumberOfCharacters(); int colWith = numberOfCharactersPerLineInRatherLongTextFields * columnWithUnit; //default, can be rather long text if (fieldsMaxChar > 0 && fieldsMaxChar < numberOfCharactersPerLineInRatherLongTextFields) { colWith = (fieldsMaxChar + 1) * columnWithUnit; // short fields } else if (fieldsMaxChar > 500) { // when the field is set to be able to contain very long text colWith = numberOfCharactersPerLineInLongTextFields * columnWithUnit; //can be very long text } else if (fieldsMaxChar < 0) { colWith = numberOfCharactersPerLineInUndifinedTextFields * columnWithUnit; } sheet.setColumnWidth((short) colIndex, (short) colWith); } //-- Report ColumnDetail --// try { HSSFCellStyle dataCellStyle = wb.createCellStyle(); dataCellStyle.setWrapText(true); dataCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); sheet.createFreezePane(0, rowIndex); while (reportData.next()) { HSSFRow dataRow = sheet.createRow((short) rowIndex++); colIndex = 0; for (Iterator iter = fields.iterator(); iter.hasNext(); colIndex++) { ReportableField field = (ReportableField) iter.next(); HSSFCell dataCell = dataRow.createCell((short) colIndex); Object fieldValue = reportData.getFieldValue(field); if (fieldValue != null) { dataCell.setCellValue(String.valueOf(fieldValue)); } dataCell.setCellStyle(dataCellStyle); } } } catch (JRException e) { //-- Exception fetching data --// HSSFRow exceptionRow = sheet.createRow((short) rowIndex++); HSSFCell exceptionCell = exceptionRow.createCell((short) 0); // Create a new font and alter it. HSSFFont exceptionFont = wb.createFont(); exceptionFont.setFontName(REPORT_FONT); exceptionFont.setItalic(true); // Fonts are set into a style so create a new one to use. HSSFCellStyle exceptionStyle = wb.createCellStyle(); exceptionStyle.setFont(exceptionFont); // Create a cell and put a value in it. exceptionCell.setCellValue("Error occurred while getting data. Check log for more details."); exceptionCell.setCellStyle(exceptionStyle); e.printStackTrace(); } // Write the output to a file FileOutputStream fileOut = new FileOutputStream(filePathAndName); wb.write(fileOut); fileOut.close(); }
From source file:com.inkubator.common.util.NewMain.java
/** * @param args the command line arguments *//*from w ww .j a va2 s . c om*/ public static void main(String[] args) throws IOException { File file1 = new File( "C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\JSON_Ek\\Surabaya\\Page1.txt"); File file2 = new File( "C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\JSON_Ek\\Surabaya\\Page2.txt"); // File file3 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\json\\json\\menado\\page3.txt"); File file3 = new File( "C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\JSON_Ek\\Surabaya\\Page3.txt"); File file4 = new File( "C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\JSON_Ek\\Surabaya\\Page4.txt"); File file5 = new File( "C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\JSON_Ek\\Surabaya\\Page5.txt"); File file6 = new File( "C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\JSON_Ek\\Surabaya\\Page6.txt"); // File file7 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 7.txt"); // File file8 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 8.txt"); // File file9 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 9.txt"); // File file10 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 10.txt"); // File file11 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 11.txt"); // File file12 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 12.txt"); // File file13 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 13.txt"); // File file14 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 14.txt"); // File file15 = new File("C:\\Users\\deni.fahri\\AppData\\Roaming\\Skype\\My Skype Received Files\\Bandung\\Bandung\\Page 15.txt"); // File file16 = new File("C:\\Users\\deni.fahri\\Downloads\\page16.txt"); // File file2 = new File("C:\\Users\\deni.fahri\\Documents\\hasil.txt"); String agoda = FilesUtil.getAsStringFromFile(file1); String agoda1 = FilesUtil.getAsStringFromFile(file2); String agoda2 = FilesUtil.getAsStringFromFile(file3); String agoda3 = FilesUtil.getAsStringFromFile(file4); String agoda4 = FilesUtil.getAsStringFromFile(file5); String agoda5 = FilesUtil.getAsStringFromFile(file6); // String agoda6 = FilesUtil.getAsStringFromFile(file7); // String agoda7 = FilesUtil.getAsStringFromFile(file8); // String agoda8 = FilesUtil.getAsStringFromFile(file9); // String agoda9 = FilesUtil.getAsStringFromFile(file10); // String agoda10 = FilesUtil.getAsStringFromFile(file11); // String agoda11 = FilesUtil.getAsStringFromFile(file12); // String agoda12 = FilesUtil.getAsStringFromFile(file13); // String agoda13 = FilesUtil.getAsStringFromFile(file14); // String agoda14 = FilesUtil.getAsStringFromFile(file15); // String agoda15 = FilesUtil.getAsStringFromFile(file16); //// System.out.println(" Test Nya adalah :" + agoda); //// String a=StringUtils.substringAfter("\"HotelTranslatedName\":", agoda); //// System.out.println(" hasil; "+a); //// // TODO code application logic here //// System.out.println("Nilai " + JsonConverter.getValueByKeyStatic(agoda, "HotelTranslatedName")); TypeToken<List<HotelModel>> token = new TypeToken<List<HotelModel>>() { }; Gson gson = new GsonBuilder().create(); // List<HotelModel> data = new ArrayList<>(); // HotelModel hotelModel = new HotelModel(); // hotelModel.setAddress("sdfsdffsfsdfsdfdsfdsf"); // hotelModel.setAccommodationName("Aku"); // HotelModel hotelModel1 = new HotelModel(); // hotelModel1.setAddress("sdfsdffsfsdfsdfdsfdsf"); // hotelModel1.setAccommodationName("Avvvku"); // HotelModel hotelModel2 = new HotelModel(); // hotelModel2.setAddress("sdfsdffsfsdfsdfdsfdsf"); // hotelModel2.setAccommodationName("Akvvvu"); // data.add(hotelModel); // data.add(hotelModel1); // data.add(hotelModel2); // String json = gson.toJson(data); List<HotelModel> total = new ArrayList<>(); List<HotelModel> data1 = new ArrayList<>(); List<HotelModel> data2 = new ArrayList<>(); List<HotelModel> data3 = new ArrayList<>(); List<HotelModel> data4 = new ArrayList<>(); List<HotelModel> data5 = new ArrayList<>(); List<HotelModel> data6 = new ArrayList<>(); List<HotelModel> data7 = new ArrayList<>(); List<HotelModel> data8 = new ArrayList<>(); List<HotelModel> data9 = new ArrayList<>(); List<HotelModel> data10 = new ArrayList<>(); List<HotelModel> data11 = new ArrayList<>(); List<HotelModel> data12 = new ArrayList<>(); List<HotelModel> data13 = new ArrayList<>(); List<HotelModel> data14 = new ArrayList<>(); List<HotelModel> data15 = new ArrayList<>(); List<HotelModel> data16 = new ArrayList<>(); data1 = gson.fromJson(agoda, token.getType()); data2 = gson.fromJson(agoda1, token.getType()); data3 = gson.fromJson(agoda2, token.getType()); data4 = gson.fromJson(agoda3, token.getType()); data5 = gson.fromJson(agoda4, token.getType()); data6 = gson.fromJson(agoda5, token.getType()); // data7 = gson.fromJson(agoda6, token.getType()); // data8 = gson.fromJson(agoda7, token.getType()); // data9 = gson.fromJson(agoda8, token.getType()); // data10 = gson.fromJson(agoda9, token.getType()); // data11 = gson.fromJson(agoda10, token.getType()); // data12 = gson.fromJson(agoda11, token.getType()); // data13 = gson.fromJson(agoda12, token.getType()); // data14 = gson.fromJson(agoda13, token.getType()); // data15 = gson.fromJson(agoda14, token.getType()); // data16 = gson.fromJson(agoda15, token.getType()); total.addAll(data1); total.addAll(data2); total.addAll(data3); total.addAll(data4); total.addAll(data5); total.addAll(data6); // total.addAll(data7); // total.addAll(data8); // total.addAll(data9); // total.addAll(data10); // total.addAll(data11); // total.addAll(data12); // total.addAll(data13); // total.addAll(data14); // total.addAll(data15); // total.addAll(data16); System.out.println(" Ukurannn nya " + total.size()); // System.out.println(" Ukurannya " + data2.size()); for (HotelModel mode : total) { System.out.println(mode); } // HotelModel hotelModel = gson.fromJson(agoda, HotelModel.class); // String Data = hotelModel.getHotelTranslatedName() + ";" + hotelModel.getStarRating() + ";" + hotelModel.getAddress() + ";" + hotelModel.getIsFreeWifi(); // FilesUtil.writeToFileFromString(file2, Data); // System.out.println(hotelModel); // HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Agoda Data Hotel Surabaya"); //// TreeMap<String, Object[]> datatoExel = new TreeMap<>(); int i = 1; // datatoExel.put("1", new Object[]{"Hotel Agoda Jakarta"}); datatoExel.put("1", new Object[] { "Nama Hotel", "Arena", "Alamat", "Rating", "Apakah Gratis Wifi", "Harga Mulai Dari", "Longitude", "Latitude" }); for (HotelModel mode : total) { datatoExel.put(String.valueOf(i + 1), new Object[] { mode.getHotelTranslatedName(), mode.getAreaName(), mode.getAddress(), mode.getStarRating(), mode.getIsFreeWifi(), mode.getTextPrice() + " " + mode.getCurrencyCode(), mode.getCoordinate().getLongitude(), mode.getCoordinate().getLatitude() }); i++; } // //// int i=1; //// for (HotelModel mode : data2) { //// datatoExel.put(String.valueOf(i), new Object[]{1d, "John", 1500000d}); ////// } //// //// datatoExel.put("4", new Object[]{3d, "Dean", 700000d}); //// Set<String> keyset = datatoExel.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = datatoExel.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) { cell.setCellValue((Date) obj); } else if (obj instanceof Boolean) { cell.setCellValue((Boolean) obj); } else if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } } } try { FileOutputStream out = new FileOutputStream(new File("C:\\Users\\deni.fahri\\Documents\\Surabaya.xls")); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:com.isoftstone.web.dao.Car_dao.java
/** * ?excel,?excel/*from w w w . j av a2s. c om*/ * @param carlist * @param path * @return ?excel */ public boolean addToExcel(List<Car_inf> carlist, String path) { System.out.println("excel\n"); // webbookExcel HSSFWorkbook wb = new HSSFWorkbook(); // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet(""); // sheet0,poiExcelshort HSSFRow row = sheet.createRow((int) 0); // HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFCell cell = row.createCell((short) 0); cell.setCellValue("id"); 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); cell = row.createCell((short) 5); cell.setCellValue(""); cell.setCellStyle(style); for (int i = 0; i < carlist.size(); i++) { row = sheet.createRow((int) i + 1); Car_inf car = (Car_inf) carlist.get(i); // String logon = car.getLogon().toString(); String dated = car.getDated().toString(); row.createCell((short) 0).setCellValue(car.getId()); row.createCell((short) 1).setCellValue(car.getBrand()); row.createCell((short) 2).setCellValue(car.getSeat()); row.createCell((short) 3).setCellValue(logon); row.createCell((short) 4).setCellValue(dated); row.createCell((short) 5).setCellValue(car.getD_license()); row.createCell((short) 6).setCellValue(car.getC_license()); } // try { System.out.println("true\n"); String name = path + "?.xls"; FileOutputStream fout = new FileOutputStream(name); wb.write(fout); fout.close(); System.out.println("\n"); return true; } catch (Exception e) { e.printStackTrace(); } return false; }
From source file:com.itn.excelDao.ExcelView.java
@Override protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest requesr, HttpServletResponse response) throws Exception { // get data model which is passed by the Spring container List<Users> allUsers = (List<Users>) model.get("allUsers"); //Create new excel sheet HSSFSheet sheet = workbook.createSheet("Java Books"); sheet.setDefaultColumnWidth(30);//from w ww. j a v a2s. com //create style for header cells CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setFontName("Arial"); style.setFillForegroundColor(HSSFColor.BLUE.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); style.setFont(font); // create header row HSSFRow header = sheet.createRow(0); header.createCell(0).setCellValue("id"); header.getCell(0).setCellStyle(style); header.createCell(1).setCellValue("First Name"); header.getCell(1).setCellStyle(style); header.createCell(2).setCellValue("Last Name"); header.getCell(2).setCellStyle(style); header.createCell(3).setCellValue("Email Address"); header.getCell(3).setCellStyle(style); // create data rows int rowCount = 1; for (Users aUsers : allUsers) { HSSFRow aRow = sheet.createRow(rowCount++); aRow.createCell(0).setCellValue(aUsers.getId()); aRow.createCell(1).setCellValue(aUsers.getFirstName()); aRow.createCell(2).setCellValue(aUsers.getLastName()); aRow.createCell(3).setCellValue(aUsers.getEmail()); } }
From source file:com.jfinal.ext.render.excel.PoiKit.java
License:Apache License
public HSSFWorkbook export() { Preconditions.checkNotNull(headers, "headers can not be null"); Preconditions.checkNotNull(columns, "columns can not be null"); Preconditions.checkArgument(cellWidth >= 0, "cellWidth < 0"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(sheetName); HSSFRow row = null;/* w ww .j a va2 s. c o m*/ HSSFCell cell = null; if (headers.length > 0) { row = sheet.createRow(0); if (headerRow <= 0) { headerRow = HEADER_ROW; } headerRow = Math.min(headerRow, MAX_ROWS); for (int h = 0, lenH = headers.length; h < lenH; h++) { @SuppressWarnings("deprecation") Region region = new Region(0, (short) h, (short) headerRow - 1, (short) h);// ?rowFromcolumnFrom sheet.addMergedRegion(region);// rowTocolumnTo // sheet.getNumMergedRegions(); if (cellWidth > 0) { sheet.setColumnWidth(h, cellWidth); } cell = row.createCell(h); cell.setCellValue(headers[h]); } } if (data.size() == 0) { return wb; } for (int i = 0, len = data.size(); i < len; i++) { row = sheet.createRow(i + headerRow); Object obj = data.get(i); if (obj == null) { continue; } if (obj instanceof Map) { processAsMap(columns, row, obj); } else if (obj instanceof Model) { processAsModel(columns, row, obj); } else if (obj instanceof Record) { processAsRecord(columns, row, obj); } } return wb; }
From source file:com.jitendrasinghnz.excelreadutility.ExcelReadStringArrayXSL.java
License:Open Source License
public void setOutputSingletResult(String[][] inputData, String[] outputResult, String filePath) { String[][] outputResultTwoDimensionArray; outputResultTwoDimensionArray = new String[inputData.length][inputData[0].length + 1]; Path folderPath = null;/* ww w . j a va2s. c o m*/ try { folderPath = Paths.get(filePath); } catch (InvalidPathException i) { System.out.println("Please Check whether " + filePath + " exist "); } for (int i = 0; i < outputResultTwoDimensionArray.length; i++) { for (int j = 0; j < outputResultTwoDimensionArray[i].length; j++) { if (j == (outputResultTwoDimensionArray[i].length - 1)) { for (int k = j; k < outputResultTwoDimensionArray[i].length; k++) { outputResultTwoDimensionArray[i][k] = outputResult[i]; } } else outputResultTwoDimensionArray[i][j] = inputData[i][j]; } } CharSequence filepathWindows = "\\"; CharSequence filepathGNULinux = "/"; try { if (filePath.contains(filepathWindows) || filePath.contains(filepathGNULinux)) { String filename = "test_output_singlet_" + String.valueOf(System.currentTimeMillis()) + ".xls"; String finalFileName = filePath + filename; FileOutputStream outputFile = new FileOutputStream(finalFileName); HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(); HSSFSheet hSSFSheet = hSSFWorkbook.createSheet("output"); for (int i = 0; i < outputResultTwoDimensionArray.length; i++) { HSSFRow row = hSSFSheet.createRow(i); for (int j = 0; j < outputResultTwoDimensionArray[i].length; j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(outputResultTwoDimensionArray[i][j]); } } hSSFWorkbook.write(outputFile); outputFile.flush(); outputFile.close(); System.out.println("An output file named \"" + filename + "\" was created at \"" + filePath + "\""); System.out.println("Good Bye !!!"); } } catch (FileNotFoundException fnfe) { System.out.println("Sorry " + filePath + " does not exist"); } catch (IOException ioe) { System.out.println("Error to open/close file from path " + filePath); } }
From source file:com.jitendrasinghnz.excelreadutility.ExcelReadStringArrayXSL.java
License:Open Source License
public void setOutputResult(String[][] inputData, String[][] outputResult, String filePath) { String[][] outputResultTwoDimensionArray; outputResultTwoDimensionArray = new String[inputData.length][inputData[0].length + outputResult[0].length]; Path folderPath = null;//from w w w . j a v a 2 s. c o m try { folderPath = Paths.get(filePath); } catch (InvalidPathException i) { System.out.println("Please Check whether " + filePath + " exist "); } for (int i = 0; i < outputResultTwoDimensionArray.length; i++) { for (int j = 0; j < outputResultTwoDimensionArray[i].length; j++) { if (j >= (outputResultTwoDimensionArray[i].length - outputResult[i].length)) { outputResultTwoDimensionArray[i][j] = outputResult[i][j - (outputResultTwoDimensionArray[i].length - outputResult[i].length)]; } else outputResultTwoDimensionArray[i][j] = inputData[i][j]; } } CharSequence filepathWindows = "\\"; CharSequence filepathGNULinux = "/"; try { if (filePath.contains(filepathWindows) || filePath.contains(filepathGNULinux)) { String filename = "test_output_" + String.valueOf(System.currentTimeMillis()) + ".xls"; String finalFileName = filePath + filename; FileOutputStream outputFile = new FileOutputStream(finalFileName); HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(); HSSFSheet hSSFSheet = hSSFWorkbook.createSheet("output"); for (int i = 0; i < outputResultTwoDimensionArray.length; i++) { HSSFRow row = hSSFSheet.createRow(i); for (int j = 0; j < outputResultTwoDimensionArray[i].length; j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(outputResultTwoDimensionArray[i][j]); } } hSSFWorkbook.write(outputFile); outputFile.flush(); outputFile.close(); System.out.println("An output file named \"" + filename + "\" was created at \"" + filePath + "\""); System.out.println("Good Bye !!!"); } } catch (FileNotFoundException fnfe) { System.out.println("Sorry " + filePath + " does not exist"); } catch (IOException ioe) { System.out.println("Error to open/close file from path " + filePath); } }