List of usage examples for org.apache.poi.ss.usermodel Workbook createFont
Font createFont();
From source file:rpt.GUI.ProgramStrategist.CyclePlans.CompareDialogController.java
private void writeHeaders(Workbook wb, Row row, Boolean addOldSOP) { Cell cell = row.createCell(0);//w w w . j a va 2 s. c o m XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBold(true); style.setFillForegroundColor(new XSSFColor(new java.awt.Color(220, 220, 220))); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(headerFont); cell.setCellStyle(style); cell.setCellValue("Plant"); cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue("Platform"); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue("Vehicle"); cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue("Propulsion"); cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue("Denomination"); cell = row.createCell(5); cell.setCellStyle(style); cell.setCellValue("Fuel"); cell = row.createCell(6); cell.setCellStyle(style); cell.setCellValue("Engine Family"); cell = row.createCell(7); cell.setCellStyle(style); cell.setCellValue("Generation"); cell = row.createCell(8); cell.setCellStyle(style); cell.setCellValue("Engine Code"); cell = row.createCell(9); cell.setCellStyle(style); cell.setCellValue("Displacement"); cell = row.createCell(10); cell.setCellStyle(style); cell.setCellValue("Engine power (PS)"); cell = row.createCell(11); cell.setCellStyle(style); cell.setCellValue("Electric motor power (PS)"); cell = row.createCell(12); cell.setCellStyle(style); cell.setCellValue("Torque"); cell = row.createCell(13); cell.setCellStyle(style); cell.setCellValue("Torque overboost"); cell = row.createCell(14); cell.setCellStyle(style); cell.setCellValue("Gearbox Type"); cell = row.createCell(15); cell.setCellStyle(style); cell.setCellValue("Gears"); cell = row.createCell(16); cell.setCellStyle(style); cell.setCellValue("Gearbox"); cell = row.createCell(17); cell.setCellStyle(style); cell.setCellValue("Driveline"); cell = row.createCell(18); cell.setCellStyle(style); cell.setCellValue("Transmission Code"); cell = row.createCell(19); cell.setCellStyle(style); cell.setCellValue("Cert Group"); cell = row.createCell(20); cell.setCellStyle(style); cell.setCellValue("Emission Class"); cell = row.createCell(21); cell.setCellStyle(style); cell.setCellValue("SOP"); cell = row.createCell(22); cell.setCellStyle(style); cell.setCellValue("EOP"); if (addOldSOP) { cell = row.createCell(23); cell.setCellStyle(style); cell.setCellValue("Old SOP"); } if (addOldSOP) { //Same boolean flag cell = row.createCell(24); cell.setCellStyle(style); cell.setCellValue("Old EOP"); } }
From source file:rpt.GUI.ProgramStrategist.CyclePlans.CompareDialogController.java
private int writeRow(Workbook wb, Sheet sheet, Row row, TableVariant variant, Map<String, Map<String, String>> diffList, Boolean colorChanges, Boolean addOldSOP) { //Used for placing comment at the right position CreationHelper factory = wb.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = factory.createClientAnchor(); //Create new style XSSFCellStyle styleRed = (XSSFCellStyle) wb.createCellStyle(); XSSFCellStyle styleBlack = (XSSFCellStyle) wb.createCellStyle(); XSSFFont fontRed = (XSSFFont) wb.createFont(); fontRed.setColor(new XSSFColor(new java.awt.Color(255, 0, 0))); XSSFFont fontBlack = (XSSFFont) wb.createFont(); fontBlack.setColor(new XSSFColor(new java.awt.Color(0, 0, 0))); styleRed.setFont(fontRed);/* www. j av a 2 s. c om*/ styleBlack.setFont(fontBlack); //xEtract differences to highlight Map<String, String> differences; if (diffList != null) { differences = diffList.get(variant.getVariantID()); } else { differences = new HashMap<String, String>(); } //Start with column 0 int cols = 0; //Create string with columns to print String[] columns = { "Plant", "Platform", "Vehicle", "Propulsion", "Denomination", "Fuel", "EngineFamily", "Generation", "EngineCode", "Displacement", "EnginePower", "ElMotorPower", "Torque", "TorqueOverBoost", "GearboxType", "Gears", "Gearbox", "Driveline", "TransmissionCode", "CertGroup", "EmissionClass", "StartOfProd", "EndOfProd" }; Cell cell; for (int i = 0; i < columns.length; i++) { cell = row.createCell(i); if (differences.containsKey(columns[i])) { cell.setCellStyle(styleRed); // position the comment anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum() + 3); // Create the comment and set the text+author Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(differences.get(columns[i])); comment.setString(str); comment.setAuthor("RPT"); // Assign the comment to the cell cell.setCellComment(comment); } else { cell.setCellStyle(styleBlack); } cell.setCellValue(variant.getValue(columns[i])); cols++; } if (addOldSOP) { cell = row.createCell(23); cell.setCellValue(variant.getOldSOP()); cols++; } if (addOldSOP) { cell = row.createCell(24); cell.setCellValue(variant.getOldEOP()); cols++; } return cols; }
From source file:ru.wmbdiff.ExportIntoExcel.java
License:Apache License
public void export(File file, WMBDiffNoRootTreeTableModel model) { logger.info("export begin"); Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("WMBDiff"); int rowNum = 0; //Create Header CellStyle style;/*w w w .ja v a 2s . c o m*/ Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.WHITE.getIndex()); headerFont.setFontHeightInPoints((short) 10); style = workbook.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.WHITE.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.WHITE.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); Row row = sheet.createRow(rowNum++); Cell cell; cell = row.createCell(0); cell.setCellValue("Result"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("Broker"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("Execution Group"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("Name"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("Type"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("Last Modification"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("Deployment Date"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("Bar File"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("Result Description"); cell.setCellStyle(style); sheet.createFreezePane(0, 1); List<DiffExecutionGroup> dEG = model.getDiffExecutionGroupList(); ListIterator<DiffExecutionGroup> litr = dEG.listIterator(); while (litr.hasNext()) { DiffExecutionGroup element = litr.next(); element.getDiffResultList(); ListIterator<DiffDeployedObjectResult> litr2 = element.getDiffResultList().listIterator(); while (litr2.hasNext()) { DiffDeployedObjectResult res = litr2.next(); switch (res.getResult()) { case ONLY_IN_A: createRow(rowNum++, sheet, res.getAObject(), "A", res.getResultDesc()); break; case ONLY_IN_B: createRow(rowNum++, sheet, res.getBObject(), "B", res.getResultDesc()); break; case EQUAL: createRow(rowNum++, sheet, res.getAObject(), "=", res.getResultDesc()); createRow(rowNum++, sheet, res.getBObject(), "=", res.getResultDesc()); sheet.groupRow(rowNum - 2, rowNum - 2); break; case DIFF: createRow(rowNum++, sheet, res.getAObject(), "!=", res.getResultDesc()); createRow(rowNum++, sheet, res.getBObject(), "!=", res.getResultDesc()); sheet.groupRow(rowNum - 2, rowNum - 2); break; } ; } ; } ; //Adjust column width to fit the contents for (int i = 0; i < 9; i++) sheet.autoSizeColumn(i); //set Filter sheet.setAutoFilter(new CellRangeAddress(0, rowNum - 1, 0, 8)); try { FileOutputStream out = new FileOutputStream(file); workbook.write(out); workbook.close(); out.close(); } catch (Exception e) { logger.error("export", e); } logger.info("export end"); }
From source file:se.mithlond.services.content.impl.ejb.report.ExcelReportServiceBean.java
License:Apache License
/** * {@inheritDoc}/* w w w. j a v a 2s . co m*/ */ @Override @SuppressWarnings("all") public CellStyle getCellStyle(final ExcelElement el, final Workbook workbook) { // Check sanity Validate.notNull(workbook, "workbook"); Validate.notNull(el, "el"); // Acquire the el and Font as expected final CellStyle toReturn = workbook.createCellStyle(); final Font theFont = workbook.createFont(); switch (el) { case TITLE: theFont.setFontHeightInPoints((short) 18); theFont.setBold(true); theFont.setColor(IndexedColors.BLUE_GREY.getIndex()); toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); break; case HEADER: theFont.setFontHeightInPoints((short) 11); theFont.setColor(IndexedColors.WHITE.getIndex()); toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); toReturn.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex()); toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND); toReturn.setWrapText(true); break; case CELL: toReturn.setAlignment(HorizontalAlignment.LEFT); toReturn.setWrapText(true); toReturn.setBorderRight(BORDER_THIN); toReturn.setRightBorderColor(GREY_25_PERCENT); toReturn.setBorderLeft(BORDER_THIN); toReturn.setLeftBorderColor(GREY_25_PERCENT); toReturn.setBorderTop(BORDER_THIN); toReturn.setTopBorderColor(GREY_25_PERCENT); toReturn.setBorderBottom(BORDER_THIN); toReturn.setBottomBorderColor(GREY_25_PERCENT); break; case NON_WRAPPING: toReturn.setAlignment(HorizontalAlignment.LEFT); toReturn.setWrapText(false); toReturn.setBorderRight(BORDER_THIN); toReturn.setRightBorderColor(GREY_25_PERCENT); toReturn.setBorderLeft(BORDER_THIN); toReturn.setLeftBorderColor(GREY_25_PERCENT); toReturn.setBorderTop(BORDER_THIN); toReturn.setTopBorderColor(GREY_25_PERCENT); toReturn.setBorderBottom(BORDER_THIN); toReturn.setBottomBorderColor(GREY_25_PERCENT); break; case FORMULA: toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); toReturn.setFillForegroundColor(GREY_25_PERCENT); toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND); toReturn.setDataFormat(workbook.createDataFormat().getFormat("0.00")); break; case ALT_FORMULA: toReturn.setAlignment(HorizontalAlignment.CENTER); toReturn.setVerticalAlignment(VerticalAlignment.CENTER); toReturn.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); toReturn.setFillPattern(FillPatternType.SOLID_FOREGROUND); toReturn.setDataFormat(workbook.createDataFormat().getFormat("0.00")); break; default: throw new IllegalArgumentException( "Style [" + el.name() + "] was not defined. " + "Blame the programmer."); } // All done. toReturn.setFont(theFont); return toReturn; }
From source file:servlet.exportScoreSheet.java
private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style;/*ww w . ja v a 2 s . c om*/ Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 11); monthFont.setColor(IndexedColors.WHITE.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); style.setWrapText(true); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:sql.fredy.sqltools.XLSExport.java
License:Open Source License
/** * Create the XLS-File named fileName/*from ww w . ja va 2s . c o m*/ * * @param fileName is the Name (incl. Path) of the XLS-file to create * * */ public int createXLS(String fileName) { // I need to have a query to process if ((getQuery() == null) && (getPstmt() == null)) { logger.log(Level.WARNING, "Need to have a query to process"); return 0; } // I also need to have a file to write into if (fileName == null) { logger.log(Level.WARNING, "Need to know where to write into"); return 0; } fileName = fixFileName(fileName); checkXlsx(fileName); // I need to have a connection to the RDBMS if (getCon() == null) { logger.log(Level.WARNING, "Need to have a connection to process"); return 0; } //Statement stmt = null; ResultSet resultSet = null; ResultSetMetaData rsmd = null; try { // first we have to create the Statement if (getPstmt() == null) { pstmt = getCon().prepareStatement(getQuery()); } //stmt = getCon().createStatement(); } catch (SQLException sqle1) { setException(sqle1); logger.log(Level.WARNING, "Can not create Statement. Message: " + sqle1.getMessage().toString()); return 0; } logger.log(Level.FINE, "FileName: " + fileName); logger.log(Level.FINE, "Query : " + getQuery()); logger.log(Level.FINE, "Starting export..."); // create an empty sheet Workbook wb; Sheet sheet; Sheet sqlsheet; CreationHelper createHelper = null; //XSSFSheet xsheet; //HSSFSheet sheet; if (isXlsx()) { wb = new SXSSFWorkbook(); createHelper = wb.getCreationHelper(); } else { wb = new HSSFWorkbook(); createHelper = wb.getCreationHelper(); } sheet = wb.createSheet("Data Export"); // create a second sheet just containing the SQL Statement sqlsheet = wb.createSheet("SQL Statement"); Row sqlrow = sqlsheet.createRow(0); Cell sqltext = sqlrow.createCell(0); try { if (getQuery() != null) { sqltext.setCellValue(getQuery()); } else { sqltext.setCellValue(pstmt.toString()); } } catch (Exception lex) { } CellStyle style = wb.createCellStyle(); style.setWrapText(true); sqltext.setCellStyle(style); Row r = null; int row = 0; // row number int col = 0; // column number int columnCount = 0; try { //resultSet = stmt.executeQuery(getQuery()); resultSet = pstmt.executeQuery(); logger.log(Level.FINE, "query executed"); } catch (SQLException sqle2) { setException(sqle2); logger.log(Level.WARNING, "Can not execute query. Message: " + sqle2.getMessage().toString()); return 0; } // create Header in XLS-file ArrayList<String> head = new ArrayList(); try { rsmd = resultSet.getMetaData(); logger.log(Level.FINE, "Got MetaData of the resultset"); columnCount = rsmd.getColumnCount(); logger.log(Level.FINE, Integer.toString(columnCount) + " Columns in this resultset"); r = sheet.createRow(row); // titlerow if ((!isXlsx()) && (columnCount > 255)) { columnCount = 255; } for (int i = 0; i < columnCount; i++) { // we create the cell Cell cell = r.createCell(col); // set the value of the cell cell.setCellValue(rsmd.getColumnName(i + 1)); head.add(rsmd.getColumnName(i + 1)); // then we align center CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // now we make it bold //HSSFFont f = wb.createFont(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(headerFont); //cellStyle.setFont(f); // adapt this font to the cell cell.setCellStyle(cellStyle); col++; } } catch (SQLException sqle3) { setException(sqle3); logger.log(Level.WARNING, "Can not create XLS-Header. Message: " + sqle3.getMessage().toString()); return 0; } // looping the resultSet int wbCounter = 0; try { while (resultSet.next()) { // this is the next row col = 0; // put column counter back to 0 to start at the next row row++; // next row // create a new sheet if more then 60'000 Rows and xls file if ((!isXlsx()) && (row % 65530 == 0)) { wbCounter++; row = 0; sheet = wb.createSheet("Data Export " + Integer.toString(wbCounter)); logger.log(Level.INFO, "created a further page because of a huge amount of data"); // create the head r = sheet.createRow(row); // titlerow for (int i = 0; i < head.size(); i++) { // we create the cell Cell cell = r.createCell(col); // set the value of the cell cell.setCellValue((String) head.get(i)); // then we align center CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // now we make it bold //HSSFFont f = wb.createFont(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(headerFont); //cellStyle.setFont(f); // adapt this font to the cell cell.setCellStyle(cellStyle); col++; } row++; } try { r = sheet.createRow(row); } catch (Exception e) { logger.log(Level.WARNING, "Error while creating row number " + row + " " + e.getMessage()); wbCounter++; row = 0; sheet = wb.createSheet("Data Export " + Integer.toString(wbCounter)); logger.log(Level.WARNING, "created a further page in the hope it helps..."); // create the head r = sheet.createRow(row); // titlerow for (int i = 0; i < head.size(); i++) { // we create the cell Cell cell = r.createCell(col); // set the value of the cell cell.setCellValue((String) head.get(i)); // then we align center CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // now we make it bold //HSSFFont f = wb.createFont(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(headerFont); //cellStyle.setFont(f); // adapt this font to the cell cell.setCellStyle(cellStyle); col++; } row++; } col = 0; // put column counter back to 0 to start at the next row String previousMessage = ""; for (int i = 0; i < columnCount; i++) { try { // depending on the type, create the cell switch (rsmd.getColumnType(i + 1)) { case java.sql.Types.INTEGER: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.FLOAT: r.createCell(col).setCellValue(resultSet.getFloat(i + 1)); break; case java.sql.Types.DOUBLE: r.createCell(col).setCellValue(resultSet.getDouble(i + 1)); break; case java.sql.Types.DECIMAL: r.createCell(col).setCellValue(resultSet.getFloat(i + 1)); break; case java.sql.Types.NUMERIC: r.createCell(col).setCellValue(resultSet.getFloat(i + 1)); break; case java.sql.Types.BIGINT: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.TINYINT: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.SMALLINT: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.DATE: // first we get the date java.sql.Date dat = resultSet.getDate(i + 1); java.util.Date date = new java.util.Date(dat.getTime()); r.createCell(col).setCellValue(date); break; case java.sql.Types.TIMESTAMP: // first we get the date java.sql.Timestamp ts = resultSet.getTimestamp(i + 1); Cell c = r.createCell(col); try { c.setCellValue(ts); // r.createCell(col).setCellValue(ts); // Date Format CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat( createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:mm:ss")); c.setCellStyle(cellStyle); } catch (Exception e) { c.setCellValue(" "); } break; case java.sql.Types.TIME: // first we get the date java.sql.Time time = resultSet.getTime(i + 1); r.createCell(col).setCellValue(time); break; case java.sql.Types.BIT: boolean b1 = resultSet.getBoolean(i + 1); r.createCell(col).setCellValue(b1); break; case java.sql.Types.BOOLEAN: boolean b2 = resultSet.getBoolean(i + 1); r.createCell(col).setCellValue(b2); break; case java.sql.Types.CHAR: r.createCell(col).setCellValue(resultSet.getString(i + 1)); break; case java.sql.Types.NVARCHAR: r.createCell(col).setCellValue(resultSet.getString(i + 1)); break; case java.sql.Types.VARCHAR: try { r.createCell(col).setCellValue(resultSet.getString(i + 1)); } catch (Exception e) { r.createCell(col).setCellValue(" "); logger.log(Level.WARNING, "Exception while writing column {0} row {3} type: {1} Message: {2}", new Object[] { col, rsmd.getColumnType(i + 1), e.getMessage(), row }); } break; default: r.createCell(col).setCellValue(resultSet.getString(i + 1)); break; } } catch (Exception e) { //e.printStackTrace(); if (resultSet.wasNull()) { r.createCell(col).setCellValue(" "); } else { logger.log(Level.WARNING, "Unhandled type at column {0}, row {3} type: {1}. Filling up with blank {2}", new Object[] { col, rsmd.getColumnType(i + 1), e.getMessage(), row }); r.createCell(col).setCellValue(" "); } } col++; } } //pstmt.close(); } catch (SQLException sqle3) { setException(sqle3); logger.log(Level.WARNING, "Exception while writing data into sheet. Message: " + sqle3.getMessage().toString()); } try { // Write the output to a file FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); logger.log(Level.INFO, "File created"); logger.log(Level.INFO, "Wrote: {0} lines into XLS-File", Integer.toString(row)); } catch (Exception e) { logger.log(Level.WARNING, "Exception while writing xls-File: " + e.getMessage().toString()); } return row; }
From source file:test.poi.MyExcelDemo.java
License:Apache License
/** * Create a library of cell styles//from w ww . ja va 2 s.co m */ private static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style; Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(titleFont); styles.put("title", style); Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 11); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setColor(IndexedColors.BLACK.getIndex()); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); // style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("header", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styles.put("cell", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula", style); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setDataFormat(wb.createDataFormat().getFormat("0.00")); styles.put("formula_2", style); return styles; }
From source file:test.XExcel.java
public static void main(String[] args) throws FileNotFoundException, IOException { Properties bundle = new Properties(); bundle.load(new FileInputStream(new File("configuration.properties"))); Integer counter = Integer.parseInt(bundle.getProperty("loop.counter")); String titles[] = { "Firstname", "Lastname", "Country", "Language" }; String data[][] = { { "noman ali", "abbasi", "PK", "EN" }, { "ahsan", "shaikh", "PK", "EN" }, { "abdul jalil", "ahmed", "PK", "EN" }, { "umair", "khan", "PK", "EN" }, { "abdul rahim", "khan", "PK", "EN" } }; int rowCount = 0; try {//from w ww .ja va 2 s.c om File xlsxFile = new File("C:/workbook.xlsx"); Workbook wb = null; Sheet sheet = null; Row row = null; if (xlsxFile.exists()) { FileInputStream fileInputStream = new FileInputStream(xlsxFile); wb = new XSSFWorkbook(fileInputStream); sheet = wb.getSheet("Test"); rowCount = sheet.getPhysicalNumberOfRows(); System.err.println("Writingxisting file ...."); for (int i = 0; i < counter; i++) { row = sheet.createRow(rowCount++); int rndNumber = new Random().nextInt(3); System.out.println(rndNumber); for (int c = 0; c < titles.length; c++) { Cell cell = row.createCell(c); cell.setCellValue(data[rndNumber][c]); } } System.err.println(xlsxFile.delete()); } else { System.err.println("Creatingl file ...."); wb = new XSSFWorkbook(); sheet = wb.createSheet("Test"); row = sheet.createRow(rowCount++); CellStyle cellStyle = wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(font); for (int i = 0; i < titles.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(cellStyle); } for (int i = 0; i < counter; i++) { row = sheet.createRow(rowCount++); int rndNumber = new Random().nextInt(3); for (int c = 0; c < titles.length; c++) { Cell cell = row.createCell(c); cell.setCellValue(data[rndNumber][c]); } } } FileOutputStream fileOut; try { fileOut = new FileOutputStream("C:/workbook.xlsx", true); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } System.out.println("Last " + sheet.getLastRowNum() + ", " + sheet.getPhysicalNumberOfRows()); } catch (java.lang.IllegalArgumentException illegalArgumentException) { System.err.println(illegalArgumentException.getMessage()); } }
From source file:tools.IOHelper.java
public static void plate1ResultSheet(ANAPlate plate, File outputFolder) throws IOException { File outputFile = new File(outputFolder, plate.getPlateId() + "_" + TIME + ".xlsx"); Workbook excelFile = null; // if (outputFile.exists()) { // try { // excelFile = WorkbookFactory.create(outputFile); // } catch (EncryptedDocumentException ex) { // System.out.println("file with assigned name already exists but is encrypted..."); // Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex); // } catch (InvalidFormatException ex) { // Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex); // } // } else { // outputFile.createNewFile(); // excelFile = new XSSFWorkbook(); // }/*from w ww . j a v a2s .com*/ if (outputFile.exists()) { outputFile.delete(); } outputFile.createNewFile(); excelFile = new XSSFWorkbook(); if (excelFile == null) { throw new RuntimeException("fail to create the xlsx file"); } int rowCount = plate.getSampleNumber(); //not including the 2 control samples + "_"+time String sheetName = plate.getPlateId(); //create a working sheet Sheet sheet = excelFile.createSheet(sheetName); //starting row & col int rowIndex = 0; int colIndex = 0; int totalCol = 0; int pos = 0, neg = 0, all = 0; XSSFFont fontTitle = (XSSFFont) excelFile.createFont(); fontTitle.setFontHeightInPoints((short) 10); fontTitle.setFontName("Arial"); fontTitle.setColor(IndexedColors.GREEN.getIndex()); fontTitle.setBold(true); fontTitle.setItalic(false); XSSFCellStyle styleTitle = (XSSFCellStyle) excelFile.createCellStyle(); styleTitle.setAlignment(CellStyle.ALIGN_CENTER); styleTitle.setFont(fontTitle); Cell cell0 = sheet.createRow(rowIndex++).createCell(0); cell0.setCellValue(plate.getPlateId() + " Summary"); //title cell0.setCellStyle(styleTitle); // //optional set Cell Style // CellStyle styleTitle = null; // CellStyle style = null; Row row = sheet.createRow(rowIndex++); //names Cell column = row.createCell(colIndex++); column.setCellValue("Sample ID"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Chip Location"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Result"); totalCol++; // column = row.createCell(colIndex++); // column.setCellValue("Positivity 0.3P"); // totalCol++; // column = row.createCell(colIndex++); // column.setCellValue("Positivity0.275P+0.5N"); // totalCol++; column = row.createCell(colIndex++); column.setCellValue("Signal"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Comments"); totalCol++; for (ANATestResult result : plate.getTestResultList()) { row = sheet.createRow(rowIndex++); //data colIndex = 0; column = row.createCell(colIndex++); column.setCellValue(result.getJulien_barcode()); column = row.createCell(colIndex++); column.setCellValue(result.getPillarPosition()); column = row.createCell(colIndex++); if (result.getPositivity() == null) { column.setCellValue("Null Result"); } else { if (ANA_Result.POSITIVE.equals(result.getPositivity())) { pos++; } else if (ANA_Result.NEGATIVE.equals(result.getPositivity())) { neg++; } all++; column.setCellValue(result.getPositivity().name()); } //make sure all fis has not-null pn result // column = row.createCell(colIndex++); // if (result.positivity30 == null) { // column.setCellValue("Null Result"); // } else { // if (ANA_Result.POSITIVE.equals(result.positivity30)) { //// pos++; // } else if (ANA_Result.NEGATIVE.equals(result.positivity30)) { //// neg++; // } //// all++; // column.setCellValue(result.positivity30.name()); // } //make sure all fis has not-null pn result // column = row.createCell(colIndex++); // if (result.positivityCombined == null) { // column.setCellValue("Null Result"); // } else { // if (ANA_Result.POSITIVE.equals(result.positivityCombined)) { //// pos++; // } else if (ANA_Result.NEGATIVE.equals(result.positivityCombined)) { //// neg++; // } //// all++; // column.setCellValue(result.positivityCombined.name()); // } //make sure all fis has not-null pn result column = row.createCell(colIndex++); column.setCellValue(result.getFirstPlateSignal()); // if(result.getFirstPlateSignal()<0){ // column.setCellValue("ROI exception: unable to get signal for this sample"); // }else{ // column.setCellValue(result.getFirstPlateSignal()); // } column = row.createCell(colIndex++); //warning msg concat mthd; merge plateErr to sampErr column.setCellValue(result.concatWarningMsgs()); } if (rowIndex - rowCount == 2) { for (int i = 0; i < totalCol; i++) { sheet.autoSizeColumn(i); } } else { System.out .println((rowIndex - 2) + " records are writen into file while " + rowCount + " are expected"); } row = sheet.createRow(rowIndex++); //total colIndex = 0; column = row.createCell(colIndex++); column.setCellValue("all samples"); column = row.createCell(colIndex++); column.setCellValue(all); column = row.createCell(colIndex++); column.setCellValue("positive samples"); column = row.createCell(colIndex++); column.setCellValue(pos); column = row.createCell(colIndex++); column.setCellValue("negative samples"); column = row.createCell(colIndex++); column.setCellValue(neg); column = row.createCell(colIndex++); column = row.createCell(colIndex++); column = row.createCell(colIndex++); column.setCellValue("PosCtrl"); column = row.createCell(colIndex++); column.setCellValue(plate.getPosCtrl().getFirstPlateSignal()); column = row.createCell(colIndex++); column.setCellValue("NegCtrl"); column = row.createCell(colIndex++); column.setCellValue(plate.getNegCtrlSignal()); FileOutputStream fos = null; try { fos = new FileOutputStream(outputFile.getAbsolutePath());//,true excelFile.write(fos); } catch (FileNotFoundException ex) { Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex); } finally { if (fos != null) { try { fos.close(); } catch (IOException ex) { Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex); } } } }
From source file:tools.IOHelper.java
public static void plate2ResultSheet(ANAPlate plate, File outputFolder) throws IOException { File outputFile = new File(outputFolder, plate.getPlateId() + "_" + TIME + ".xlsx"); Workbook excelFile = null; if (outputFile.exists()) { outputFile.delete();//from ww w .jav a 2s . c o m } outputFile.createNewFile(); excelFile = new XSSFWorkbook(); if (excelFile == null) { throw new RuntimeException("fail to create the xlsx file"); } int rowCount = plate.getSampleNumber(); //not including the 2 control samples + "_"+time String sheetName = plate.getPlateId(); //create a working sheet Sheet sheet = excelFile.createSheet(sheetName); //starting row & col int rowIndex = 0; int colIndex = 0; int totalCol = 0; int pos = 0, neg = 0, all = 0; XSSFFont fontTitle = (XSSFFont) excelFile.createFont(); fontTitle.setFontHeightInPoints((short) 10); fontTitle.setFontName("Arial"); fontTitle.setColor(IndexedColors.GREEN.getIndex()); fontTitle.setBold(true); fontTitle.setItalic(false); XSSFCellStyle styleTitle = (XSSFCellStyle) excelFile.createCellStyle(); styleTitle.setAlignment(CellStyle.ALIGN_CENTER); styleTitle.setFont(fontTitle); Cell cell0 = sheet.createRow(rowIndex++).createCell(0); cell0.setCellValue(plate.getPlateId() + " Summary"); //title cell0.setCellStyle(styleTitle); // //optional set Cell Style // CellStyle styleTitle = null; // CellStyle style = null; // add column:Sample,Chip Location,Signal,Positivity,sample titer,plate titer, pattern, No of Cells, enableWatershed,comment Row row = sheet.createRow(rowIndex++); //names Cell column = row.createCell(colIndex++); column.setCellValue("Sample ID"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Chip Location"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Signal"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Positivity"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Sample Titer"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Plate Titer"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Pattern"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Number of Cells"); totalCol++; column = row.createCell(colIndex++); column.setCellValue("Comments"); totalCol++; for (ANATestResult result : plate.getTestResultList()) { row = sheet.createRow(rowIndex++); //data colIndex = 0; column = row.createCell(colIndex++); column.setCellValue(result.getJulien_barcode()); column = row.createCell(colIndex++); column.setCellValue(result.getPillarPosition()); column = row.createCell(colIndex++); column.setCellValue(result.getSecondPlateSignal()); column = row.createCell(colIndex++); if (result.getPositivity() == null) { column.setCellValue("Null Result"); } else { if (ANA_Result.POSITIVE.equals(result.getPositivity())) { pos++; } else if (ANA_Result.NEGATIVE.equals(result.getPositivity())) { neg++; } all++; column.setCellValue(result.getPositivity().name()); } //make sure all fis has not-null pn result column = row.createCell(colIndex++); if (result.getTiter() != null) { column.setCellValue(result.getTiter().name()); } column = row.createCell(colIndex++); if (plate.getPosCtrl().getTiter() != null) { column.setCellValue(plate.getPosCtrl().getTiter().name()); } //pattern, No of Cells, enableWatershed,comment column = row.createCell(colIndex++); if (result.getPattern() != null) { column.setCellValue(result.getPattern().name()); } column = row.createCell(colIndex++); column.setCellValue(result.cellCount()); column = row.createCell(colIndex++); //warning msg concat mthd; merge plateErr to sampErr column.setCellValue(result.concatWarningMsgs()); } if (rowIndex - rowCount == 2) { for (int i = 0; i < totalCol; i++) { sheet.autoSizeColumn(i); } } else { System.out .println((rowIndex - 2) + " records are writen into file while " + rowCount + " are expected"); } row = sheet.createRow(rowIndex++); //total colIndex = 0; column = row.createCell(colIndex++); column.setCellValue("all samples"); column = row.createCell(colIndex++); column.setCellValue(all); column = row.createCell(colIndex++); column.setCellValue("positive samples"); column = row.createCell(colIndex++); column.setCellValue(pos); column = row.createCell(colIndex++); column.setCellValue("negative samples"); column = row.createCell(colIndex++); column.setCellValue(neg); FileOutputStream fos = null; try { fos = new FileOutputStream(outputFile.getAbsolutePath());//,true excelFile.write(fos); } catch (FileNotFoundException ex) { Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex); } finally { if (fos != null) { try { fos.close(); } catch (IOException ex) { Logger.getLogger(IOHelper.class.getName()).log(Level.SEVERE, null, ex); } } } }