List of usage examples for org.apache.poi.hssf.usermodel HSSFRow createCell
@Override public HSSFCell createCell(int column)
From source file:com.cimmyt.reports.impl.ServiceReportLaboratoryImpl.java
License:Apache License
private void writeCell(HSSFRow rowData, int sizeColumn, String strValue, HSSFCellStyle style) { HSSFCell dataCell = rowData.createCell(sizeColumn); HSSFRichTextString cellValue = new HSSFRichTextString(strValue); if (style != null) dataCell.setCellStyle(style);/*from w w w. ja v a 2 s .co m*/ dataCell.setCellType(HSSFCell.CELL_TYPE_STRING); dataCell.setCellValue(cellValue); }
From source file:com.cimmyt.reports.impl.ServiceReportLaboratoryImpl.java
License:Apache License
private void loadDataCell(HSSFRow rowData, String value, int index, HSSFCellStyle styleCellNormallyHeader) { HSSFCell dataCell = rowData.createCell(index); HSSFRichTextString cellValue = new HSSFRichTextString(value); dataCell.setCellStyle(styleCellNormallyHeader); dataCell.setCellType(HSSFCell.CELL_TYPE_STRING); dataCell.setCellValue(cellValue);/*from www . java2 s.c o m*/ }
From source file:com.cimmyt.reports.impl.ServiceReportLaboratoryImpl.java
License:Apache License
private HSSFWorkbook getBookResultData(ResultDataExportDataBean resultDataExport, PropertyHelper pro, SortedMap<Integer, ResultsPreferencesDetail> sortedMap) { HSSFWorkbook book = new HSSFWorkbook(); HSSFSheet sheet = book.createSheet(); HSSFRow filaDatGral; HSSFCell cellDatGral;/*from w ww . jav a 2 s .c om*/ HSSFRichTextString textDatGral; filaDatGral = sheet.createRow(0); cellDatGral = filaDatGral.createCell(0); textDatGral = new HSSFRichTextString(pro.getKey(LBL_GENERIC_PLATE)); cellDatGral.setCellValue(textDatGral); cellDatGral = filaDatGral.createCell(1); textDatGral = new HSSFRichTextString(resultDataExport.getListPlate()); cellDatGral.setCellValue(textDatGral); filaDatGral = sheet.createRow(1); cellDatGral = filaDatGral.createCell(0); textDatGral = new HSSFRichTextString(pro.getKey(LBL_GENERIC_EXPORT)); cellDatGral.setCellValue(textDatGral); cellDatGral = filaDatGral.createCell(1); textDatGral = new HSSFRichTextString(resultDataExport.getNameExport()); cellDatGral.setCellValue(textDatGral); filaDatGral = sheet.createRow(2); cellDatGral = filaDatGral.createCell(0); textDatGral = new HSSFRichTextString("Date"); cellDatGral.setCellValue(textDatGral); cellDatGral = filaDatGral.createCell(1); textDatGral = new HSSFRichTextString(resultDataExport.getDateExport()); cellDatGral.setCellValue(textDatGral); HSSFRow filaEncabezos = sheet.createRow(6); int colCounter = 0; for (ResultsPreferencesDetail resultsPreferencesDetail : sortedMap.values()) { HSSFCell headerCell = filaEncabezos.createCell(colCounter); HSSFRichTextString headerText = new HSSFRichTextString(resultsPreferencesDetail.getHeader()); headerCell.setCellValue(headerText); colCounter++; } int rowCounter = 7; colCounter = 0; for (RowResultDataBean bean : resultDataExport.getListResults()) { HSSFRow rowData = sheet.createRow(rowCounter); colCounter = 0; for (String str : bean.getListCell()) { if (str != null) { HSSFCell dataCell = rowData.createCell(colCounter); HSSFRichTextString cellValue = new HSSFRichTextString(str); dataCell.setCellValue(cellValue); } colCounter++; } rowCounter++; } return book; }
From source file:com.claim.controller.Center16AndJula2015Controller.java
public ProgrameStatus center16_Jula_2015(OppReport report) { ProgrameStatus programeStatus = new ProgrameStatus(); List<ObjRptChula> listData = new ArrayList<ObjRptChula>(); int col_last = 29; int row_start = 8; // index row int row_formula_start = row_start + 1; int col_txtid_width = col_last + 1; try {//ww w.java 2 s . c om connection = new DBManage().open(); Center16AndChula2015DAO chula2015DAO = new Center16AndChula2015DAO(); chula2015DAO.setConnection(connection); if (report.getServiceCode().equals(HCODE_CENTER16)) { // center 16 listData = chula2015DAO.getListChulaDetail(report.getStmp(), HCODE_CENTER16); EXCELL_HEADER1 = "???? OP : " + chula2015DAO.getMonthPayment(report.getStmp()); EXCELL_HEADER2 = "? 16 13661 Model 2 "; EXCELL_HOSPITAL = "?? ? 16 13661 "; //out out = new FileOutputStream(report.getPathFile() + "" + File.separator + "HC16_13661_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); report.setServiceName( " ? 16 "); } else if (report.getServiceCode().equals(HCODE_CHULA)) { // Jula listData = chula2015DAO.getListChulaDetail(report.getStmp(), HCODE_CHULA); EXCELL_HEADER1 = "???? OP : " + chula2015DAO.getMonthPayment(report.getStmp()); EXCELL_HEADER2 = "? 16 13661 Model 2"; EXCELL_HOSPITAL = "?? ? ? 13756"; //out out = new FileOutputStream(report.getPathFile() + "" + File.separator + "Chula_13756_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); report.setServiceName("?"); } //readTemplate file = new FileInputStream( new File("." + File.separator + "xls" + File.separator + "CH16_CHula_detail_2015.xls")); // style Excell HSSFWorkbook wbCenter16Jula = new HSSFWorkbook(file); this.loadStyle(wbCenter16Jula); // Start sheet 1 ******************************************************************************* HSSFSheet sheet = workbookBase.getSheetAt(0); sheet.createFreezePane(4, 8); // col,row sheet.setColumnWidth(col_txtid_width, WIDTH_TXID); HSSFCell cell = null; HSSFRow row = null; // row 0 HEADER0 row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // row 1 HEADER2 row = sheet.createRow(1); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER2); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last)); cell.setCellStyle(csHead); // row 1 HOSPITAL row = sheet.createRow(2); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HOSPITAL); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last)); cell.setCellStyle(csHead); int curRow = row_start; int i = 1; for (int j = 0; j < listData.size(); j++) { ObjRptChula data = listData.get(j); int col1 = i; row = sheet.createRow(curRow); row.setHeight((short) 340); /*PoiHssfUtil stylePoi = new PoiHssfUtil(wb, row, cell); stylePoi.setStyleText(0, String.valueOf(col1), PoiHssfUtil.CENTER);*/ cell = row.createCell(0); cell.setCellValue(col1); cell.setCellStyle(csNum4); cell = row.createCell(1); cell.setCellValue(data.getPid()); cell.setCellStyle(csStringPid); cell = row.createCell(2); cell.setCellValue(data.getHn()); cell.setCellStyle(csStringLeft); cell = row.createCell(3); cell.setCellValue(data.getPname()); cell.setCellStyle(csStringLeft); cell = row.createCell(4); cell.setCellValue(data.getHmain()); cell.setCellStyle(csStringLeft); cell = row.createCell(5); cell.setCellValue(data.getDateopd_th()); cell.setCellStyle(csStringLeft); cell = row.createCell(6); cell.setCellValue(data.getPdxcode()); cell.setCellStyle(csNum4); cell = row.createCell(7); cell.setCellValue(data.getChrg_car()); cell.setCellStyle(csDouble2); cell = row.createCell(8); cell.setCellValue(data.getChrg_rehab_inst()); cell.setCellStyle(csDouble2); cell = row.createCell(9); cell.setCellValue(data.getChrg_ophc()); cell.setCellStyle(csDouble2); cell = row.createCell(10); cell.setCellValue(data.getChrg_car_rehabinst_ophc_total()); cell.setCellStyle(csDouble2); cell = row.createCell(11); cell.setCellValue(data.getChrg_202()); cell.setCellStyle(csDouble2); cell = row.createCell(12); cell.setCellValue(data.getChrg_stditem()); cell.setCellStyle(csDouble2); cell = row.createCell(13); cell.setCellValue(data.getChrg_other()); cell.setCellStyle(csDouble2); cell = row.createCell(14); cell.setCellValue(data.getChrg_total()); cell.setCellStyle(csDouble2); cell = row.createCell(15); cell.setCellValue(data.getSum_chrg()); cell.setCellStyle(csDouble2); cell = row.createCell(16); cell.setCellValue(data.getPaid_car()); cell.setCellStyle(csDouble2); cell = row.createCell(17); cell.setCellValue(data.getPaid_rehab_inst()); cell.setCellStyle(csDouble2); cell = row.createCell(18); cell.setCellValue(data.getPaid_ophc()); cell.setCellStyle(csDouble2); cell = row.createCell(19); cell.setCellValue(data.getPaid_car_rehabinst_ophc_total()); cell.setCellStyle(csDouble2); cell = row.createCell(20); cell.setCellValue(data.getPaid_202()); cell.setCellStyle(csDouble2); cell = row.createCell(21); cell.setCellValue(data.getPaid_stditem()); cell.setCellStyle(csDouble2); cell = row.createCell(22); cell.setCellValue(data.getPaid_other()); cell.setCellStyle(csDouble2); cell = row.createCell(23); cell.setCellValue(data.getPaid_202_stditem_other_total()); cell.setCellStyle(csDouble2); cell = row.createCell(24); cell.setCellValue(data.getPaid_cal_point()); cell.setCellStyle(csDouble2); cell = row.createCell(25); cell.setCellValue(data.getPaid_cal_point_total()); cell.setCellStyle(csDouble2); cell = row.createCell(26); cell.setCellValue(data.getPaid_total()); cell.setCellStyle(csDouble2); cell = row.createCell(27); cell.setCellValue(data.getCompensation_fee_total()); cell.setCellStyle(csDouble2); cell = row.createCell(28); cell.setCellValue(data.getRemark()); cell.setCellStyle(csStringLeft); cell = row.createCell(29); cell.setCellValue(data.getInvoice_no()); cell.setCellStyle(csStringLeft); cell = row.createCell(30); cell.setCellValue(data.getTxid()); cell.setCellStyle(csStringtxid); curRow++; i++; } // row = sheet.createRow(curRow); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 6)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csNum4B); row.createCell(2).setCellStyle(csNum4B); row.createCell(3).setCellStyle(csNum4B); row.createCell(4).setCellStyle(csNum4B); row.createCell(5).setCellStyle(csNum4B); row.createCell(6).setCellStyle(csNum4B); cell = row.createCell(7); cell.setCellFormula(builderFormulaSum(7, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSum(8, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); cell.setCellFormula(builderFormulaSum(9, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSum(10, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(11); cell.setCellFormula(builderFormulaSum(11, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(12); cell.setCellFormula(builderFormulaSum(12, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(13); cell.setCellFormula(builderFormulaSum(13, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(14); cell.setCellFormula(builderFormulaSum(14, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(15); cell.setCellFormula(builderFormulaSum(15, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(16); cell.setCellFormula(builderFormulaSum(16, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(17); cell.setCellFormula(builderFormulaSum(17, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(18); cell.setCellFormula(builderFormulaSum(18, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(19); cell.setCellFormula(builderFormulaSum(19, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(20); cell.setCellFormula(builderFormulaSum(20, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(21); cell.setCellFormula(builderFormulaSum(21, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(22); cell.setCellFormula(builderFormulaSum(22, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(23); cell.setCellFormula(builderFormulaSum(23, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(24); cell.setCellFormula(builderFormulaSum(24, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(25); cell.setCellFormula(builderFormulaSum(25, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(26); cell.setCellFormula(builderFormulaSum(26, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(27); cell.setCellFormula(builderFormulaSum(27, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(28); cell.setCellStyle(csNum4B); workbookBase.setSheetName(0, report.getServiceCode() + " " + report.getServiceName()); workbookBase.write(out); out.close(); file.close(); Console.LOG(Message.exportSuccess(report.getServiceName()), 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); Console.LOG(e.getMessage(), 0); programeStatus.setMessage(ConstantMessage.MSG_PROCESS_FAILS + e.toString()); programeStatus.setTitle(ConstantMessage.MSG_CONTACT_ADMIN); programeStatus.setProcessStatus(false); } finally { if (connection != null) { try { connection.close(); } catch (SQLException ex) { Logger.getLogger(Center16AndJula2015Controller.class.getName()).log(Level.SEVERE, null, ex); } } } return programeStatus; }
From source file:com.claudesoft.service.CarTbDetailed.java
@Override void makeTableHead(HSSFSheet sheet) { HSSFRow row = null; HSSFCell cell = null;/*from w w w . j av a 2s . c om*/ row = sheet.createRow(2); for (int j = 0; j < this.columnCount; j++) { // if (j == 0) { sheet.setColumnWidth(j, 10 * 256); } else { sheet.setColumnWidth(j, 15 * 256); } cell = row.createCell(j); cell.setCellStyle(makeTableHeadStyle()); } cell = sheet.getRow(2).getCell(0); cell.setCellValue(""); cell = sheet.getRow(2).getCell(1); cell.setCellValue(""); cell = sheet.getRow(2).getCell(2); cell.setCellValue(""); cell = sheet.getRow(2).getCell(3); cell.setCellValue(""); cell = sheet.getRow(2).getCell(4); cell.setCellValue(""); cell = sheet.getRow(2).getCell(5); cell.setCellValue(""); cell = sheet.getRow(2).getCell(6); cell.setCellValue(""); cell = sheet.getRow(2).getCell(7); cell.setCellValue(""); cell = sheet.getRow(2).getCell(8); cell.setCellValue(""); cell = sheet.getRow(2).getCell(9); cell.setCellValue(""); cell = sheet.getRow(2).getCell(10); cell.setCellValue(""); cell = sheet.getRow(2).getCell(11); cell.setCellValue(""); }
From source file:com.clonescriptscrapper.excelfile.GenerateCsvFile.java
public static void excel() throws FileNotFoundException, IOException { HSSFWorkbook hwb = new HSSFWorkbook(); HSSFSheet sheet = hwb.createSheet("Monster Details"); HSSFRow rowhead = sheet.createRow((int) 0); rowhead.createCell((int) 0).setCellValue("S.No."); rowhead.createCell((int) 1).setCellValue("CATEGORY_DATA_ID"); rowhead.createCell((int) 2).setCellValue("CATEGORY_ID"); rowhead.createCell((int) 3).setCellValue("TITLE"); rowhead.createCell((int) 4).setCellValue("NAME"); rowhead.createCell((int) 5).setCellValue("CLICKS"); rowhead.createCell((int) 6).setCellValue("ADDED_ON"); rowhead.createCell((int) 7).setCellValue("PAGE_RANK"); rowhead.createCell((int) 8).setCellValue("DESCRIPTION"); rowhead.createCell((int) 9).setCellValue("DEMO_URL"); rowhead.createCell((int) 10).setCellValue("CATEGORY_ID"); rowhead.createCell((int) 11).setCellValue("CATEGORY_NAME"); rowhead.createCell((int) 12).setCellValue("CATEGORY_URL"); rowhead.createCell((int) 13).setCellValue("ISCRAWLED"); try {/*from w w w. ja v a 2s . c o m*/ Class.forName("com.mysql.jdbc.Driver"); java.sql.Connection con = DriverManager .getConnection("jdbc:mysql://localhost:3306/clonescriptdirectorydb", "root", ""); String sql = "SELECT * FROM `categories_data`,categories where categories_data.CATEGORY_ID= categories .CATEGORY_ID;"; java.sql.PreparedStatement ps = con.prepareStatement(sql); ResultSet rs = ps.executeQuery(); int k = 0; while (rs.next()) { HSSFRow row = sheet.createRow((int) k + 2); try { row.createCell((int) 0).setCellValue(k + 1); } catch (Exception sd) { } try { row.createCell((int) 1).setCellValue(rs.getString("CATEGORY_DATA_ID") + ""); } catch (Exception sd) { } try { row.createCell((int) 2).setCellValue(rs.getString("CATEGORY_ID") + ""); } catch (Exception sd) { } try { row.createCell((int) 3).setCellValue(rs.getString("TITLE") + ""); } catch (Exception sd) { } try { row.createCell((int) 4).setCellValue(rs.getString("NAME") + ""); } catch (Exception sd) { } try { row.createCell((int) 5).setCellValue(rs.getString("CLICKS") + ""); } catch (Exception sd) { } try { row.createCell((int) 6).setCellValue(rs.getString("ADDED_ON") + ""); } catch (Exception sd) { } try { row.createCell((int) 7).setCellValue(rs.getString("PAGE_RANK") + ""); } catch (Exception sd) { } try { row.createCell((int) 8).setCellValue(rs.getString("DESCRIPTION") + ""); } catch (Exception sd) { } try { row.createCell((int) 9).setCellValue(rs.getString("DEMO_URL") + ""); } catch (Exception sd) { } try { row.createCell((int) 10).setCellValue(rs.getString("CATEGORY_ID") + ""); } catch (Exception sd) { } try { row.createCell((int) 11).setCellValue(rs.getString("CATEGORY_NAME") + ""); } catch (Exception sd) { } try { row.createCell((int) 12).setCellValue(rs.getString("CATEGORY_URL") + ""); } catch (Exception sd) { } try { row.createCell((int) 13).setCellValue(rs.getString("ISCRAWLED") + ""); } catch (Exception sd) { } k++; } try { String filename = "data.csv"; System.out.println("Directory is created!"); FileOutputStream fileOut = new FileOutputStream(filename); hwb.write(fileOut); fileOut.close(); System.out.println("Your excel file has been generated!"); } catch (IOException iOException) { } } catch (Exception aaa) { } }
From source file:com.cms.utils.ExcelReader.java
public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { Set<CellRangeAddress> mergedRegions = new TreeSet<CellRangeAddress>(); destRow.setHeight(srcRow.getHeight()); for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { HSSFCell oldCell = srcRow.getCell(j); HSSFCell newCell = destRow.getCell(j); if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); }// www . j a va2 s . co m copyCell(oldCell, newCell, styleMap); CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastRow(), mergedRegion.getLastColumn()); if (isNewMergedRegion(newMergedRegion, mergedRegions)) { mergedRegions.add(newMergedRegion); destSheet.addMergedRegion(newMergedRegion); } } } } }
From source file:com.comcast.cats.config.ui.monitoring.reboot.UpTimeAndRebootStatusBean.java
License:Open Source License
public void postProcessXls(Object document) { logger.trace("postProcessXls start document " + document); if (document != null) { HSSFWorkbook workBook = (HSSFWorkbook) document; HSSFSheet sheet = workBook.getSheetAt(0); HSSFRow headerRow = sheet.getRow(0); for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) { sheet.setColumnWidth(i, 30 * 265); // width for 40 characters }/*from w w w .j av a2s.co m*/ sheet.shiftRows(0, sheet.getLastRowNum(), 5); // shift rows 0 to n // by 1 to get space // for header sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F3")); HSSFFont headerFont = workBook.createFont(); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headerCellStyle = workBook.createCellStyle(); headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCell headerCell = headerRow.createCell(0); headerCell.setCellStyle(headerCellStyle); headerCell.setCellValue("CATS Uptime and Reboot Status : " + (new Date())); HSSFCellStyle metaDataCellStyle = workBook.createCellStyle(); metaDataCellStyle.setFont(headerFont); HSSFRow metaDataRow = sheet.getRow(3); if (metaDataRow == null) { metaDataRow = sheet.createRow(3); } HSSFCell metaDataKey = metaDataRow.createCell(0); metaDataKey.setCellStyle(metaDataCellStyle); metaDataKey.setCellValue("CATS Instance"); HSSFCell metaDataValue = metaDataRow.createCell(1); metaDataValue.setCellStyle(metaDataCellStyle); metaDataValue.setCellValue(AuthController.getHostAddress()); HSSFCellStyle datatTableHeaderCellStyle = workBook.createCellStyle(); datatTableHeaderCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); datatTableHeaderCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); datatTableHeaderCellStyle.setFont(headerFont); HSSFRow actualDataTableHeaderRow = sheet.getRow(5); for (int i = 0; i < actualDataTableHeaderRow.getPhysicalNumberOfCells(); i++) { HSSFCell cell = actualDataTableHeaderRow.getCell(i); if (cell != null) { String cellValue = cell.getStringCellValue(); cellValue = cellValue.replace("<br/> ", ""); // replace // any line // breaks cell.setCellValue(cellValue); cell.setCellStyle(datatTableHeaderCellStyle); } } } logger.trace("postProcessXls end"); }
From source file:com.commander4j.util.JExcel.java
License:Open Source License
public void exportToExcel(String filename, ResultSet rs) { try {/*from w w w. j a v a 2s. c om*/ ResultSetMetaData rsmd = rs.getMetaData(); int numberOfColumns = rsmd.getColumnCount(); int columnType = 0; String columnTypeName = ""; int recordNumber = 0; int passwordCol = -1; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFCellStyle cellStyle_varchar = workbook.createCellStyle(); cellStyle_varchar.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_nvarchar = workbook.createCellStyle(); cellStyle_nvarchar.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_varchar2 = workbook.createCellStyle(); cellStyle_varchar2.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_title = workbook.createCellStyle(); cellStyle_title.setAlignment(HorizontalAlignment.CENTER); HSSFCellStyle cellStyle_char = workbook.createCellStyle(); cellStyle_char.setAlignment(HorizontalAlignment.LEFT); HSSFCellStyle cellStyle_date = workbook.createCellStyle(); cellStyle_date.setAlignment(HorizontalAlignment.CENTER); cellStyle_date.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); HSSFCellStyle cellStyle_timestamp = workbook.createCellStyle(); cellStyle_timestamp.setAlignment(HorizontalAlignment.CENTER); cellStyle_timestamp.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); HSSFCellStyle cellStyle_decimal = workbook.createCellStyle(); cellStyle_decimal.setAlignment(HorizontalAlignment.RIGHT); HSSFFont font_title = workbook.createFont(); font_title.setColor((short) 0xc); font_title.setBold(true); ; font_title.setItalic(true); font_title.setUnderline(HSSFFont.U_DOUBLE); cellStyle_title.setFont(font_title); HSSFCell cell; HSSFRow row; // rs.beforeFirst(); while (rs.next()) { recordNumber++; if (recordNumber == 1) { row = sheet.createRow((int) 0); for (int column = 1; column <= numberOfColumns; column++) { cell = row.createCell((int) (column - 1)); String columnName = rsmd.getColumnLabel(column); columnName = columnName.replace("_", " "); columnName = JUtility.capitalize(columnName); cell.setCellStyle(cellStyle_title); cell.setCellValue(columnName); if (columnName.equals("Password")) { passwordCol = column; } } } row = sheet.createRow((int) recordNumber); for (int column = 1; column <= numberOfColumns; column++) { columnType = rsmd.getColumnType(column); columnTypeName = rsmd.getColumnTypeName(column); cell = row.createCell((int) (column - 1)); try { switch (columnType) { case java.sql.Types.NVARCHAR: HSSFRichTextString rtf_nvarchar; if (column == passwordCol) { rtf_nvarchar = new HSSFRichTextString("*****"); } else { rtf_nvarchar = new HSSFRichTextString(rs.getString(column)); } cell.setCellStyle(cellStyle_nvarchar); cell.setCellValue(rtf_nvarchar); break; case java.sql.Types.VARCHAR: HSSFRichTextString rtf_varchar; if (column == passwordCol) { rtf_varchar = new HSSFRichTextString("*****"); } else { rtf_varchar = new HSSFRichTextString(rs.getString(column)); } cell.setCellStyle(cellStyle_varchar); cell.setCellValue(rtf_varchar); break; case java.sql.Types.CHAR: HSSFRichTextString rtf_char = new HSSFRichTextString(rs.getString(column)); cell.setCellStyle(cellStyle_char); cell.setCellValue(rtf_char); break; case java.sql.Types.DATE: try { cell.setCellValue(rs.getTimestamp(column)); cell.setCellStyle(cellStyle_date); } catch (Exception ex) { } break; case java.sql.Types.TIMESTAMP: try { cell.setCellValue(rs.getTimestamp(column)); cell.setCellStyle(cellStyle_timestamp); } catch (Exception ex) { } break; case java.sql.Types.DECIMAL: HSSFRichTextString rtf_decimal = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_decimal); break; case java.sql.Types.NUMERIC: HSSFRichTextString rtf_decimaln = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_decimaln); break; case java.sql.Types.BIGINT: HSSFRichTextString rtf_bigint = new HSSFRichTextString( rs.getBigDecimal(column).toString()); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_bigint); break; case java.sql.Types.INTEGER: HSSFRichTextString rtf_int = new HSSFRichTextString(String.valueOf(rs.getInt(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_int); break; case java.sql.Types.FLOAT: HSSFRichTextString rtf_float = new HSSFRichTextString( String.valueOf(rs.getFloat(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_float); break; case java.sql.Types.DOUBLE: HSSFRichTextString rtf_double = new HSSFRichTextString( String.valueOf(rs.getDouble(column))); cell.setCellStyle(cellStyle_decimal); cell.setCellValue(rtf_double); break; default: cell.setCellValue(new HSSFRichTextString(columnTypeName)); break; } } catch (Exception ex) { String errormessage = ex.getLocalizedMessage(); HSSFRichTextString rtf_exception = new HSSFRichTextString(errormessage); cell.setCellStyle(cellStyle_varchar); cell.setCellValue(rtf_exception); break; } } if (recordNumber == 65535) { break; } } for (int column = 1; column <= numberOfColumns; column++) { sheet.autoSizeColumn((int) (column - 1)); } if (recordNumber > 0) { try { FileOutputStream fileOut = new FileOutputStream(filename.toLowerCase()); workbook.write(fileOut); fileOut.close(); } catch (Exception ex) { setErrorMessage(ex.getMessage()); } } try { workbook.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } catch (SQLException e) { setErrorMessage(e.getMessage()); } }
From source file:com.conecta.sat.utils.BuildXls.java
@Override protected void buildExcelDocument(Map<String, Object> map, org.apache.poi.hssf.usermodel.HSSFWorkbook workbook, HttpServletRequest hsr, HttpServletResponse hsr1) throws Exception { hsr1.setContentType("application/vnd.ms-excel"); DateFormat name = new SimpleDateFormat("ddMMyyyyhhmmss"); hsr1.setHeader("Content-disposition", "attachment; filename=Reporte" + name.format(new Date()) + ".xls"); HSSFSheet sheet = workbook.createSheet("Inventario"); sheet.setDefaultColumnWidth(30);//from w ww . j a v a 2 s. co m CellStyle style = workbook.createCellStyle(); List<PdfDTO> list = (List<PdfDTO>) map.get("list"); HSSFRow header = sheet.createRow(0); for (int i = 0; i < sColumnas.length; i++) { header.createCell(i).setCellValue(sColumnas[i]); header.getCell(i).setCellStyle(style); } int rowCount = 1; HSSFCellStyle my_style_0 = workbook.createCellStyle(); for (PdfDTO pdfDto : list) { HSSFRow aRow = sheet.createRow(rowCount++); aRow.createCell(0).setCellValue(pdfDto.getSerial()); aRow.createCell(1).setCellValue(pdfDto.getEntrega()); aRow.createCell(2).setCellValue(pdfDto.getEntrega()); aRow.createCell(3).setCellValue(pdfDto.getActivacion()); aRow.createCell(4).setCellValue(pdfDto.getCentro()); aRow.createCell(5).setCellValue(pdfDto.getTipo()); aRow.createCell(6).setCellValue(pdfDto.getCliente()); aRow.createCell(7).setCellValue(pdfDto.getFolioPivotal()); // aRow.createCell(8).setCellValue( pdfDto.getIdUnico() ); aRow.createCell(8).setCellValue(pdfDto.getLastUpdate()); } }