Posted on

Update or edit existing Excel files in Java using Apache POI

Requirement -Programatically update an Excel spreadsheet in Java.

Solutions – Here an example of Apache POI to update a XLS workbook. Apache POI is a very good API , that can be handy in manipulating Excel documents.This tutorial focuses on XLS documents (Office 97 – 2003).

The existing excel file like below –

sdsdsdsdsdsdsd

Below is code to update the excel file

import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import java.util.Iterator;
public class updateExcels {  

        public static void main(String[] args) throws Exception{
                
                FileInputStream fsIP= new FileInputStream(new File("C:\\TechartifactExcel.xls")); //Read the spreadsheet that needs to be updated
                 
                HSSFWorkbook wb = new HSSFWorkbook(fsIP); //Access the workbook
                 
                HSSFSheet worksheet = wb.getSheetAt(0); //Access the worksheet, so that we can update / modify it.
                 
                Cell cell = null; // declare a Cell object
               
                cell = worksheet.getRow(2).getCell(2);   // Access the second cell in second row to update the value
                 
                cell.setCellValue("OverRide Last Name");  // Get current cell value value and overwrite the value
                 
                fsIP.close(); //Close the InputStream
                
                FileOutputStream output_file =new FileOutputStream(new File("C:\\TechartifactExcel.xls"));  //Open FileOutputStream to write updates
                 
                wb.write(output_file); //write changes
                 
                output_file.close();  //close the stream    
        }
}

after updating excel file would look like below –

1updateexcel2

happy coding with Vinay in techartifact ….

Posted on

Setting Styles & Formula in excel file using Apache POI

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

Posted on

Write an excel file in java using Apache POI

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