Happy Coding with Vinay Kumar in techartifact…
Latest tip and information on Java and Oracle Fusion Middleware/Weblogic
Requirement – Setting styles on excel cells
Solutions– You can set FONT,Color, Bold etc.Use cell.setCellStyle(style) method.
Following is code for setting styles
HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Style example"); <strong> //Example 1 of setting styles in cell</strong> HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontName(HSSFFont.FONT_ARIAL); HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue("Setting text in BOLD and in Arial font"); cell.setCellStyle(style); <strong> //Example 2 of setting styles in cell</strong> font = workbook.createFont(); font .setItalic(true); // setting cell value as italic font setFontHeightInPoints((short) 12); //setting font height. font.setColor(HSSFColor.GREEN.index); // setting color as green style = workbook.createCellStyle(); style.setFont(font); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue("Setting text in italic & green"); cell.setCellStyle(style); try { FileOutputStream out = new FileOutputStream(new File("C:\\Vinaystyle.xls")); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }
You can also set the background color for the rows as well in sheet
// Aqua background CellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(IndexedColors.AQUA.getIndex()); style.setFillPattern(CellStyle.BIG_SPOTS); row.setRowStyle(style);
Setting Formula in Excel –
Row row= sheet.createRow(1); row.createCell(0).setCellValue(100); rpw.createCell(1).setCellValue(200); row.createCell(2).setCellValue(300); row.createCell(3).setCellFormula("SUM(A1:A4)"); or cell.setCellFormula("A2*B3*C4")
For evaluating formula you can use –
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
evaluateFormulaCell(Cell cell) will check to see if the supplied cell is a formula cell. If it isn’t, then no changes will be made to it. If it is, then the formula is evaluated. The value for the formula is saved alongside it, to be displayed in excel. The formula remains in the cell, just with a new value.
Happy coding with Vinay in techartifact
Requirment – How to write an excel file in Java
Solutions– Well Apache POI is answer.This API will help you a lot.you have to download and add the POI JAR file to your project’s class path. The Apache POI JAR file can be found http://poi.apache.org/download.html
Following is code –
public static void main(String[] args) throws IOException { // Directory path where the xls file will be created String filePath = "C:/Vinay/ApachePoi/VinayExcel.xls"; FileOutputStream fout = new FileOutputStream(filePath ); ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); HSSFWorkbook workBook = new HSSFWorkbook(); // Create the spreadsheet HSSFSheet spreadSheet = workBook.createSheet("vinay_sheet"); // Create the first row HSSFRow row = spreadSheet.createRow((short) 0); // Create the cells and write to the file HSSFCell cell; cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString("Vinay")); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString("Kumar")); workBook.write(outputStream); outputStream.writeTo(fout); outputStream.close(); fout.close(); }
Happy coding with Vinay in techartifact