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

Posted on

Read Excel file using Apache POI in Java

Requirment – How to read 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
Please note

HSSF (Horrible SpreadSheet Format) – reads and writes Microsoft Excel (XLS) format files.
XSSF (XML SpreadSheet Format) – reads and writes Office Open XML (XLSX) format files.

Following snippet we will use

//..
FileInputStream file = new FileInputStream(new File("C:\\VinayTest.xls"));
             
//Get the workbook instance for XLS file 
HSSFWorkbook workbook = new HSSFWorkbook(file);
 
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
 
//Get iterator to all the rows in current sheet
Iterator<Row> rowIterator = sheet.iterator();
 
//Get iterator to all cells of current row
Iterator<Cell> cellIterator = row.cellIterator();

Following is code to read from excel file

public static void main(String[] args) throws IOException {
         
        // Location of the source file
        String sourceFilePath = "C:/Vinay/ApachePoi/TestFile.xls";
         
        FileInputStream fileInputStream = null;
         
        // Array List to store the excel sheet data
        List excelData = new ArrayList();
         
        try {
             
            // FileInputStream to read the excel file
            fileInputStream = new FileInputStream(sourceFilePath);
  
            // Create an excel workbook
            HSSFWorkbook excelWorkBook = new HSSFWorkbook(fileInputStream);
             
            // Retrieve the first sheet of the workbook.
            HSSFSheet excelSheet = excelWorkBook.getSheetAt(0);
  
            // Iterate through the sheet rows and cells. 
            // Store the retrieved data in an arrayList
            Iterator rows = excelSheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                Iterator cells = row.cellIterator();
  
                List cellData = new ArrayList();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    cellData.add(cell);
                }
  
                excelData .add(cellData);
            }
             
            // Print retrieved data to the console
            for (int rowNum = 0; rowNum < excelData.size(); rowNum++) {
                 
                List list = (List) excelData.get(rowNum);
                 
                for (int cellNum = 0; cellNum < list.size(); cellNum++) {
                     
                    HSSFCell cell = (HSSFCell) list.get(cellNum);
                     
                    if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        System.out.print(cell.getRichStringCellValue().getString() + " ");
                    } else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        System.out.print(cell.getNumericCellValue() + " ");
                    } else if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
                        System.out.println(cell.getBooleanCellValue() + " ");
                    }
                }
                System.out.println("");
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fileInputStream != null) {
                fileInputStream.close();
            }
        }
    }
}}

Happy coding with Vinay in techartifact

Posted on

Generate Excel file in Oracle ADF using Apache POI

Requirement:Generate excel file using open source “Apache POI” to generate excel spreadsheets from my ADF application.

Advantage of using POI over default export collectionListener of ADF –

1. Using Apache POI, you can not only export excels but also can read excel files (.xls or .xlsx) from local machines and upload them to ADF applications
2. User has more control on the excel output file (than using “Export Collection Action Listener”)
for e.g. you can put data formatting for your currency/date column, freeze panes, apply filters etc.
3. Size of generated excel is less than from export CollectionListener.

Solution-

You need to download the apache POI jar from poi.apache.org

–>> poi-3.7-20101029.jar
You need to add this jar in project libraries and classpath of project.

Creating a sample project (using Department and Employee tables) and attaching libararies.

I would like to keep it simple as we don’t want to focus on any steps other than excel here, here are quick steps to create a sample application

1. create view object using query on locations table.
2. Expose the VO to applicationModule.then drag drop VO from data control to page as table
3. Place a command button anywhere on the page and label it as “ExporToExcel”
4. Drop a “” component on this button.
Put property of af:fileDownloadActionListener as follows
-> contentType: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet or you can use “application/octet-stream” .
-> ExcelFromPoi.xls
-> Method: #{PoiBean.generateExcel} //custom method call to export data

Now writing a custom method in managed bean to export locations shown in the screen.
I am writing an generateExcel() method.

In short, follow the steps-
create a Workbook object.create a Sheet object.Get object of bindings .After that we will get page iterator and from that iterator we will get all rows in that range. Start creating row first and then cells. After completion of sheet , we will writing outputStream on the workBook.

Following is code of managed bean –

package vinay.view;

import com.sun.jmx.snmp.Timestamp;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;

import java.util.HashMap;
import java.util.Map;

import javax.faces.context.FacesContext;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import oracle.adf.model.BindingContainer;
import oracle.adf.model.BindingContext;
import oracle.adf.model.binding.DCBindingContainer;

import oracle.adf.model.binding.DCIteratorBinding;



public class PoiBean {
    public PoiBean() {
    }


        public static BindingContainer getBindingContainer() {
            //return (BindingContainer)JSFUtils.resolveExpression("#{bindings}");
            return (BindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
        }

        public static DCBindingContainer getDCBindingContainer() {
            return (DCBindingContainer)getBindingContainer();
        }
      
      public void generateExcel(FacesContext facesContext, OutputStream outputStream) throws IOException {
          try {
         
                            
                                HSSFWorkbook workbook = new HSSFWorkbook();
                                    HSSFSheet worksheet = workbook.createSheet("POI Worksheet");
              
                                DCBindingContainer bindings = (DCBindingContainer)BindingContext.getCurrent().getCurrentBindingsEntry();
                                DCIteratorBinding dcIteratorBindings = bindings.findIteratorBinding("ViewObj1Iterator");
                                HSSFRow  excelrow = null;
              
                                        // Get all the rows of a iterator
                                        oracle.jbo.Row[] rows = dcIteratorBindings.getAllRowsInRange();
                                    int i = 0;
                             

                                            for (oracle.jbo.Row row : rows) {

                                                //print header on first row in excel
                                                if (i == 0) {
                                                    excelrow = (HSSFRow)worksheet.createRow((short)i);
                                                    short j = 0;
                                                    for (String colName : row.getAttributeNames()) {
                                                      
                                                            HSSFCell cellA1 = excelrow.createCell((short) j);
                                                            cellA1.setCellValue(colName);
                                                            j++;
                                                        
                                                    }
                                                }

                                                //print data from second row in excel
                                                ++i;
                                                short j = 0;
                                                excelrow = worksheet.createRow((short)i);
                                                for (String colName : row.getAttributeNames()) {
                                                    System.out.println("hello "+row.getAttribute(colName));
                                                    System.out.println("hello "+colName);
                                                        HSSFCell  cell = excelrow.createCell(j);
                                                    if (colName.equalsIgnoreCase("DepartmentId")) {

                                                       
                                                        cell.setCellValue(row.getAttribute(colName).toString());
                                                        System.out.println("colName "+colName+"row.getAttribute(colName).toString()"+row.getAttribute(colName).toString());
                                                    }
                                                        //logic for cell formatting
                                                        if (colName.equalsIgnoreCase("DepartmenName")) {
                                                            cell.setCellValue(row.getAttribute(colName).toString());
                                                            
                                                        }
                                                        //make it double if you want and convert accordingly
                                                        else if (colName.equalsIgnoreCase("LocationId")){
                                                            cell.setCellValue(row.getAttribute(colName).toString());
                                                        }
                                                        else if (colName.equalsIgnoreCase("ManagerId")){
                                                            if(null!=row.getAttribute(colName)){
                                                            cell.setCellValue(row.getAttribute(colName).toString());
                                                            }
                                                        } else
                                                         cell.setCellValue(row.getAttribute(colName).toString());

                                                        j++;
                                                      
                                                    }
                                                
                                                   worksheet.createFreezePane(0, 1, 0, 1);
                                               } 
              workbook.write(outputStream);
              outputStream.flush();
              
          }
                                            catch (Exception e) {
                                          e.printStackTrace();
                                            }
                                        
      }
      }

You can freeze the header like this —

worksheet.createFreezePane(0, 1, 0, 1);

Resize the column automatically –

            sheet.autoSizeColumn(0);
            sheet.autoSizeColumn(1);
            sheet.autoSizeColumn(2);
            sheet.autoSizeColumn(3);

And finally you can get the generated excel report like below

you can download the sample application from below, I have also illustrat how you can generate excel using export collectionListner in sample application.Button label as exportCollectionActionListener –

exportToExcel

Happy coding with Vinay kumar in Techartifact