List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createFont
@Override
public HSSFFont createFont()
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportAgentReport(List repAgent) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Agent"); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); HSSFRow row1 = sheet.createRow(0);/*w w w.j a va 2 s . c o m*/ HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Agent Report"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row2 = sheet.createRow(2); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("ID"); cell20.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("SYSTEM DATE"); cell21.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("SYSTEM STAFF"); cell22.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("CODE"); cell23.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("NAME"); cell24.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("ADDRESS"); cell25.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("TEL"); cell26.setCellStyle(styleC3Center); sheet.autoSizeColumn(6); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("FAX"); cell27.setCellStyle(styleC3Center); sheet.autoSizeColumn(7); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("DESCRIPTION"); cell28.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("NAME T"); cell29.setCellStyle(styleC3Center); sheet.autoSizeColumn(9); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("DESCRIPTION T"); cell30.setCellStyle(styleC3Center); sheet.autoSizeColumn(10); HSSFCell cell31 = row2.createCell(11); cell31.setCellValue("ADDRESS T"); cell31.setCellStyle(styleC3Center); sheet.autoSizeColumn(11); HSSFCell cell32 = row2.createCell(12); cell32.setCellValue("EMAIL"); cell32.setCellStyle(styleC3Center); sheet.autoSizeColumn(12); HSSFCell cell33 = row2.createCell(13); cell33.setCellValue("WEB"); cell33.setCellStyle(styleC3Center); sheet.autoSizeColumn(13); HSSFCell cell34 = row2.createCell(14); cell34.setCellValue("REMARKS"); cell34.setCellStyle(styleC3Center); sheet.autoSizeColumn(14); HSSFCell cell35 = row2.createCell(15); cell35.setCellValue("WARNING"); cell35.setCellStyle(styleC3Center); sheet.autoSizeColumn(15); HSSFCell cell36 = row2.createCell(16); cell36.setCellValue("REF ID"); cell36.setCellStyle(styleC3Center); sheet.autoSizeColumn(16); HSSFCell cell37 = row2.createCell(17); cell37.setCellValue("BRANCH"); cell37.setCellStyle(styleC3Center); sheet.autoSizeColumn(17); HSSFCell cell38 = row2.createCell(18); cell38.setCellValue("BRANCH NO"); cell38.setCellStyle(styleC3Center); sheet.autoSizeColumn(18); HSSFCell cell39 = row2.createCell(19); cell39.setCellValue("TAX NO"); cell39.setCellStyle(styleC3Center); sheet.autoSizeColumn(19); int count = 3; HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); for (int i = 0; i < repAgent.size(); i++) { ReportAgent data = (ReportAgent) repAgent.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getId()); cell0.setCellStyle(styleC23); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(String.valueOf(data.getSystemdate())); cell1.setCellStyle(styleC23); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getSystemstaff()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getCode()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getName()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getAddress().trim()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getTel()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getFax()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getDescription().trim()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getNameT()); cell8.setCellStyle(styleC24); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getDescriptionT().trim()); cell9.setCellStyle(styleC24); HSSFCell cell10 = row.createCell(11); cell10.setCellValue(data.getAddressT().trim()); cell10.setCellStyle(styleC24); HSSFCell cell11 = row.createCell(12); cell11.setCellValue(data.getEmail()); cell11.setCellStyle(styleC24); HSSFCell cell12 = row.createCell(13); cell12.setCellValue(data.getWeb()); cell12.setCellStyle(styleC24); HSSFCell cell14 = row.createCell(14); cell14.setCellValue(data.getRemarks()); cell14.setCellStyle(styleC24); HSSFCell cell15 = row.createCell(15); cell15.setCellValue(data.getWarning()); cell15.setCellStyle(styleC24); HSSFCell cell16 = row.createCell(16); cell16.setCellValue(data.getRefid()); cell16.setCellStyle(styleC24); HSSFCell cell17 = row.createCell(17); cell17.setCellValue(data.getBranch()); cell17.setCellStyle(styleC24); HSSFCell cell18 = row.createCell(18); cell18.setCellValue(data.getBranchno()); cell18.setCellStyle(styleC24); HSSFCell cell19 = row.createCell(19); cell19.setCellValue(data.getTaxno()); cell19.setCellStyle(styleC23); } for (int j = 0; j < 20; j++) { sheet.autoSizeColumn(j); } for (int k = 4; k < 21; k++) { if (k != 6 && k != 7 && k != 12 && k != 13 && k < 16) { sheet.setColumnWidth(k, 256 * 35);//27 } else { sheet.setColumnWidth(k, 256 * 20);//27 } } exportFileExcel("AgentReport", wb); }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportStaffReport(List repStaff) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Staff"); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); HSSFRow row1 = sheet.createRow(0);//from ww w .ja va2 s . co m HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Staff Report"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row2 = sheet.createRow(2); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("ID"); cell20.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("SYSTEM DATE"); cell21.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("SYSTEM STAFF"); cell22.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("CODE"); cell23.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("NAME"); cell24.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("PASSWD"); cell25.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("POSITION"); cell26.setCellStyle(styleC3Center); sheet.autoSizeColumn(6); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("DEPARTMENT ID"); cell27.setCellStyle(styleC3Center); sheet.autoSizeColumn(7); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("TEL"); cell28.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("CAR"); cell29.setCellStyle(styleC3Center); sheet.autoSizeColumn(9); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("STATUS"); cell30.setCellStyle(styleC3Center); sheet.autoSizeColumn(10); HSSFCell cell31 = row2.createCell(11); cell31.setCellValue("SIGNATURE"); cell31.setCellStyle(styleC3Center); sheet.autoSizeColumn(11); int count = 3; HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); for (int i = 0; i < repStaff.size(); i++) { ReportStaff data = (ReportStaff) repStaff.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getId()); cell0.setCellStyle(styleC23); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(String.valueOf(data.getSystemdate())); cell1.setCellStyle(styleC23); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getSystemstaff()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getCode()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getName()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getPasswd()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getPosition()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getDepartmentid()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getTel()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getCar()); cell8.setCellStyle(styleC24); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getStatus()); cell9.setCellStyle(styleC24); HSSFCell cell10 = row.createCell(11); cell10.setCellValue(data.getSignature()); cell10.setCellStyle(styleC24); } for (int j = 0; j < 12; j++) { sheet.autoSizeColumn(j); } // sheet.setColumnWidth(8, 256*40);//27 // sheet.setColumnWidth(10, 256*40);//27 exportFileExcel("StaffReport", wb); }
From source file:com.tecnosur.util.Excel.java
public void ExportarMatriculados(CControlmatricula datos, String aula) { try { // Defino el Libro de Excel HSSFWorkbook wb = new HSSFWorkbook(); // Creo la Hoja en Excel Sheet sheet = wb.createSheet("matriculados"); // quito las lineas del libro para darle un mejor acabado sheet.setDisplayGridlines(false); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6)); // creo una nueva fila Row trow = sheet.createRow((short) 1); createTituloCell(wb, trow, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "ALUMNOS MATRICULADOS"); Row trow2 = sheet.createRow((short) 3); createTituloCell(wb, trow2, 0, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_CENTER, "AULA: " + aula); // Creo la cabecera de mi listado en Excel Row row = sheet.createRow((short) 5); // Creo las celdas de mi fila, se puede poner un diseo a la celda_codigo createCell(wb, row, 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "CDIGO", true, true); createCell(wb, row, 1, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "ALUMNO", true, true); createCell(wb, row, 2, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "TIPO PAGO", true, true); createCell(wb, row, 3, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "CRONO. PAGO", true, true); createCell(wb, row, 4, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "N CUOT.", true, true); createCell(wb, row, 5, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "MON. MATR.", true, true); createCell(wb, row, 6, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_CENTER, "MON. CUOT.", true, true); // Definimos el tamao de las celdas, podemos definir un tamaa especifico o hacer que //la celda_codigo se acomode segn su tamao Sheet ssheet = wb.getSheetAt(0); ssheet.setColumnWidth(0, 60 * 40); ssheet.setColumnWidth(1, 255 * 40); ssheet.setColumnWidth(2, 90 * 40); ssheet.autoSizeColumn(3);//from w w w . j a v a2 s. co m ssheet.autoSizeColumn(4); ssheet.autoSizeColumn(5); ssheet.autoSizeColumn(6); HSSFFont cellFont = wb.createFont(); cellFont.setFontHeightInPoints((short) 8); cellFont.setFontName(HSSFFont.FONT_ARIAL); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setFont(cellFont); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setLeftBorderColor((short) 8); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setRightBorderColor((short) 8); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBottomBorderColor((short) 8); for (int i = 0; i < datos.size(); i++) { row = sheet.createRow((short) i + 6); Cell celda_codigo = row.createCell(0); celda_codigo.setCellStyle(cellStyle); celda_codigo.setCellValue(datos.get(i).getIdalumno()); Cell celda_alumno = row.createCell(1); celda_alumno.setCellStyle(cellStyle); celda_alumno.setCellValue(datos.get(i).getNom_completo()); Cell celda_tipopago = row.createCell(2); celda_tipopago.setCellStyle(cellStyle); celda_tipopago.setCellValue(datos.get(i).getTipopagante()); Cell celda_crono = row.createCell(3); celda_crono.setCellStyle(cellStyle); celda_crono.setCellValue(datos.get(i).getDesc_cronopagtable()); Cell celda_ncuota = row.createCell(4); celda_ncuota.setCellStyle(cellStyle); celda_ncuota.setCellValue(datos.get(i).getNum_cuota()); Cell celda_matricula = row.createCell(5); celda_matricula.setCellStyle(cellStyle); celda_matricula.setCellValue(datos.get(i).getMonmatricula()); Cell celda_moncuota = row.createCell(6); celda_moncuota.setCellStyle(cellStyle); celda_moncuota.setCellValue(datos.get(i).getMoncuota()); } String strRuta = "TYSAC_Matriculados.xls"; FileOutputStream fileOut = new FileOutputStream(strRuta); wb.write(fileOut); fileOut.close(); Runtime.getRuntime().exec("cmd /c start " + strRuta); } catch (IOException e) { System.out.println("Error de escritura"); e.printStackTrace(); } }
From source file:com.tecnosur.util.Excel.java
private static void createTituloCell(HSSFWorkbook wb, Row row, int column, short halign, short valign, String strContenido) {/*from ww w .j a v a 2s. com*/ CreationHelper ch = wb.getCreationHelper(); Cell cell = row.createCell(column); cell.setCellValue(ch.createRichTextString(strContenido)); HSSFFont cellFont = wb.createFont(); cellFont.setFontHeightInPoints((short) 11); cellFont.setFontName(HSSFFont.FONT_ARIAL); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(halign); cellStyle.setVerticalAlignment(valign); cellStyle.setFont(cellFont); cell.setCellStyle(cellStyle); }
From source file:com.tecnosur.util.Excel.java
private static void createCell(HSSFWorkbook wb, Row row, int column, short halign, short valign, String strContenido, boolean booBorde, boolean booCabecera) { CreationHelper ch = wb.getCreationHelper(); Cell cell = row.createCell(column);/* w w w.j ava 2 s .c om*/ HSSFFont cellFont = wb.createFont(); cellFont.setFontHeightInPoints((short) 8); cellFont.setFontName(HSSFFont.FONT_ARIAL); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cell.setCellValue(ch.createRichTextString(strContenido)); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(halign); cellStyle.setVerticalAlignment(valign); cellStyle.setFont(cellFont); if (booBorde) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBottomBorderColor((short) 8); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setLeftBorderColor((short) 8); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setRightBorderColor((short) 8); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setTopBorderColor((short) 8); } if (booCabecera) { cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBottomBorderColor((short) 8); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setLeftBorderColor((short) 8); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setRightBorderColor((short) 8); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setTopBorderColor((short) 8); cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); } cell.setCellStyle(cellStyle); }
From source file:com.testmax.util.ExcelSheet.java
License:CDDL license
/** * This method is used to set the styles for all the headers * of the excel sheet.//from w w w. java 2 s .co m * @param sampleWorkBook - Name of the workbook. * @return cellStyle - Styles for the Header data of Excel sheet. */ private HSSFCellStyle setHeaderStyle(HSSFWorkbook workBook) { HSSFFont font = workBook.createFont(); font.setFontName(HSSFFont.FONT_ARIAL); font.setColor(IndexedColors.PLUM.getIndex()); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle cellStyle = workBook.createCellStyle(); cellStyle.setFont(font); return cellStyle; }
From source file:com.tm.hiber.service.util.DatabaseUtil.java
/** * * @param objFile/*from w w w . j av a2 s .c o m*/ * @param objJTable * @return number of records exported */ public int exportData(File objFile, JTable objJTable) throws FileNotFoundException, IOException { int response = 0; if (objFile == null) { return response; } DefaultTableModel tm = (DefaultTableModel) objJTable.getModel(); Object[] rows = tm.getDataVector().toArray(); JTableHeader columnNames = objJTable.getTableHeader(); TableColumnModel columnModel = columnNames.getColumnModel(); int columnCount = columnModel.getColumnCount(); Vector<String> vecColumnNames = new Vector<String>(); for (int c = 0; c < columnCount; c++) { vecColumnNames.add(columnModel.getColumn(c).getHeaderValue().toString()); } HSSFWorkbook exportReadyWorkbook = new HSSFWorkbook(); HSSFSheet dataSheet = exportReadyWorkbook.createSheet(getExportSheetName()); if (vecColumnNames.size() > 0) { int columnCounter = 0; Row objHSSFColumnName = dataSheet.createRow(0); for (String strColumnName : vecColumnNames) { /* Set Header CSS */ Cell objHSSFCell = objHSSFColumnName.createCell(columnCounter); objHSSFCell.setCellValue(strColumnName); CellStyle csll = exportReadyWorkbook.createCellStyle(); Font objFont = exportReadyWorkbook.createFont(); objFont.setFontName("Calibri"); objFont.setColor(IndexedColors.BLACK.index); objFont.setBold(true); csll.setFont(objFont); csll.setFillBackgroundColor(HSSFColor.YELLOW.index); csll.setFillForegroundColor(HSSFColor.YELLOW.index); csll.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csll.setAlignment(HSSFCellStyle.ALIGN_CENTER); //csll.setWrapText(true); objHSSFCell.setCellStyle(csll); columnCounter++; } } if (rows != null && rows.length > 0) { /* Set Data into Sheet */ for (int i = 0; i < rows.length; i++) { Vector objCellsData = (Vector) rows[i]; Row objHSSFRow = dataSheet.createRow(i + 1); if (objCellsData != null && objCellsData.size() > 0) { for (int j = 0; j < objCellsData.size(); j++) { /* Set Cell Data CSS */ Cell objHSSFCell = objHSSFRow.createCell(j); CellStyle csll = exportReadyWorkbook.createCellStyle(); Font objFont = exportReadyWorkbook.createFont(); objFont.setColor(IndexedColors.BLACK.index); objFont.setBold(false); objFont.setFontName("Calibri"); csll.setFont(objFont); csll.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); csll.setFillBackgroundColor(IndexedColors.WHITE.index); csll.setFillForegroundColor(IndexedColors.WHITE.index); csll.setAlignment(HSSFCellStyle.ALIGN_CENTER); //csll.setWrapText(true); csll.setBorderBottom(CellStyle.BORDER_THIN); csll.setBorderTop(CellStyle.BORDER_THIN); csll.setBottomBorderColor(HSSFColor.GREY_25_PERCENT.index); csll.setTopBorderColor(HSSFColor.GREY_50_PERCENT.index); objHSSFCell.setCellStyle(csll); Object cellData = objCellsData.get(j); objHSSFCell.setCellValue((String) cellData); } } } for (int i = 0; i < columnCount; i++) { if (i == 2) { dataSheet.setColumnWidth(i, 30 * 256); } else { dataSheet.autoSizeColumn(i); } } /* Write File */ FileOutputStream objFileOutputStream = new FileOutputStream(objFile); exportReadyWorkbook.write(objFileOutputStream); objFileOutputStream.flush(); objFileOutputStream.close(); response = rows.length; } return response; }
From source file:com.ts.excelservlet.UDR_Driver_Excel.java
/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) *//*from w ww .ja v a2 s. com*/ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // TODO Auto-generated method stub DBTransaction dbtranobj = new DBTransaction(); String vehicle_number = request.getParameter("vehicle_number"); HttpSession session = request.getSession(true); String[] select = (String[]) session.getAttribute("id"); String driver_name = request.getParameter("driver_name"); //System.out.println("IMEI : " +imeinumber); //String vehicle_number=""; int index = 2; System.out.println("************** doGet ************"); OutputStream out = null; try { response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=Driver_UDR.xls"); Connection con = dbtranobj.connect(); // Class.forName("org.postgresql.Driver").newInstance(); //conn = DriverManager.getConnection("jdbc:postgresql://182.72.167.34:5432/master_database","postgres", "postgres"); ResultSet rs = null; Statement st = null; st = con.createStatement(); rs = st.executeQuery( "SELECT * FROM driver_info WHERE driver_name='" + driver_name + "' order by driver_name"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Driver Sheet"); sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 1)); sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 2)); /* sheet.addMergedRegion(new Region(0,(short)0,0,(short)3)); sheet.addMergedRegion(new Region(0,(short)0,0,(short)4)); sheet.addMergedRegion(new Region(0,(short)0,0,(short)5));*/ HSSFRow rowhead = sheet.createRow((short) 0); rowhead.setHeight((short) 500); /*rowhead.createCell((short) 0).setCellValue("Fuel Information For " +vehicle_number); * */ HSSFCell cell2B = rowhead.createCell(0); cell2B.setCellValue(new HSSFRichTextString("Report For Driver : " + driver_name)); // Style Font in Cell 2B HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle = wb.createCellStyle(); HSSFFont hSSFFont = wb.createFont(); hSSFFont.setFontName(HSSFFont.FONT_ARIAL); hSSFFont.setFontHeightInPoints((short) 14); hSSFFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); hSSFFont.setColor(HSSFColor.BLUE.index); cellStyle.setFont(hSSFFont); cell2B.setCellStyle(cellStyle); HSSFRow rowhead1 = sheet.createRow((short) 1); rowhead1.setHeight((short) 600); Cell cell = rowhead1.createCell((short) 0); HSSFCellStyle cellStyle1 = wb.createCellStyle(); cellStyle1 = wb.createCellStyle(); HSSFFont hSSFFont1 = wb.createFont(); hSSFFont1.setFontName(HSSFFont.FONT_ARIAL); hSSFFont1.setFontHeightInPoints((short) 12); hSSFFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); hSSFFont1.setColor(HSSFColor.BLACK.index); cellStyle1.setFont(hSSFFont1); cell.setCellStyle(cellStyle1); // cell.setCellValue("SNO"); sheet.setColumnWidth(0, 7000); // rowhead.createCell((short) 0).setCellValue("Date"); if (select != null && select.length != 0) { for (int i = 0; i < select.length; i++) { if (select[i].equalsIgnoreCase("doe")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("LICENCE EXPIRY"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("doj")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("JOINED DATE"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("license_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("LICENCE NUMBER "); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("vehicle_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("VEHICLE NUMBER"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("shift_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("SHIFT NUMBER"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("route_number")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("ROUTE NUMBER"); sheet.setColumnWidth(i, 7000); } else if (select[i].equalsIgnoreCase("address")) { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue("ADDRESS"); sheet.setColumnWidth(i, 7000); } else { Cell cell1 = rowhead1.createCell((short) i); cell1.setCellStyle(cellStyle1); cell1.setCellValue(select[i]); sheet.setColumnWidth(i, 7000); } } } rs = st.executeQuery( "SELECT * FROM driver_info WHERE driver_name='" + driver_name + "' order by driver_name"); while (rs.next()) { HSSFRow row = sheet.createRow((short) index); row.setHeight((short) 500); if (select != null && select.length != 0) { for (int i = 0; i < select.length; i++) { row.createCell((short) i).setCellValue(rs.getString(select[i])); } } index++; } out = response.getOutputStream(); wb.write(out); } catch (Exception e) { throw new ServletException("Exception in Excel Sample Servlet", e); } finally { if (out != null) out.close(); } }
From source file:com.util.poi.ExcelView.java
License:Open Source License
/** * ?Excel/*from w w w . j a v a 2 s. c o m*/ * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); //comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); /* Map<String, Object> map=toHashMap(item); cell.setCellValue(map.get(properties[i]).toString());*/ cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { /*Map<String, Object> map=toHashMap(item); cell.setCellValue(map.get(properties[i]).toString());*/ cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
From source file:com.vportal.portlet.vdoc.service.util.ReportUtil.java
License:Open Source License
public static void createCellBold(HSSFRow row, short column, String value, HSSFWorkbook wb) { HSSFCellStyle style = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setColor((short) 0xc); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font);/* w w w.ja v a2s .co m*/ style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND); HSSFCell cell = row.getCell(column); if (cell == null) cell = row.createCell(column); //cell.setEncoding(wb.ENCODING_UTF_16); cell.setCellValue(value); cell.setCellStyle(style); }