CODEWITHSUNDEEP
javaexcelapache-poi
sarmila
sarmila

Reputation: 117

How to write data to an existing excel using apache poi

My excel sheet contains 5 row and 2 columns.I want to add one more column in that excel.But when i am using WorkbookFactory,it is showing error.I imported poi-3.8.jar and poi-ooxml-3.5-beta5.jar.It is giving error Exception in thread "main" java.lang.Error: Unresolved compilation problem: WorkbookFactory cannot be resolved.Please help me what to do.

Upvotes: 5

Views: 50161

Answers (4)

Yanish Pradhananga
Yanish Pradhananga

Reputation: 755

I'm Uploading my program for your reference. After some effort I've overcome this issue. Jars details: dom4j-1.6.1.jar, poi-3.9.jar,poi-ooxml-3.9.jar, poi-ooxml-schemas-3.11.jar, xmlbeans-2.6.0.jar Make sure you have at least above mentioned or new. I'm including details of import so that you don't need to bang you head. Hope you find it use

    ***Pojo: Employee.java***

        public class Employee {
         private int id;
         private String firstName;
         private String lastName;

         public Employee(){}

        public Employee(int id, String firstName, String lastName) {
            super();
            this.id = id;
            this.firstName = firstName;
            this.lastName = lastName;
        }

        public String getFirstName() {
            return firstName;
        }
        public void setFirstName(String firstName) {
            this.firstName = firstName;
        }
        public String getLastName() {
            return lastName;
        }
        public void setLastName(String lastName) {
            this.lastName = lastName;
        }
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        }

***Write Class: ApachePOIExcelWrite.java***

    import java.io.File;
    import java.io.FileOutputStream;
    import java.util.Map;
    import java.util.Set;
    import java.util.TreeMap;        
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    public class ApachePOIExcelWrite {
                public static void main(String[] args) 
                {
                    //Blank workbook
                    XSSFWorkbook workbook = new XSSFWorkbook(); 

                    //Create a blank sheet
                    XSSFSheet sheet = workbook.createSheet("Employee Data");

                    //This data needs to be written (Object[])
                    Map<String, Object[]> data = new TreeMap<String, Object[]>();
                    data.put("1", new Object[] {"ID", "NAME", "LASTNAME"});
                    data.put("2", new Object[] {1, "Amit", "Shukla"});
                    data.put("3", new Object[] {2, "Lokesh", "Gupta"});
                    data.put("4", new Object[] {3, "John", "Adwards"});
                    data.put("5", new Object[] {4, "Brian", "Schultz"});

                    //Iterate over data and write to sheet
                    Set<String> keyset = data.keySet();
                    int rownum = 0;
                    for (String key : keyset)
                    {
                        Row row = sheet.createRow(rownum++);
                        Object [] objArr = data.get(key);
                        int cellnum = 0;
                        for (Object obj : objArr)
                        {
                           Cell cell = row.createCell(cellnum++);
                           if(obj instanceof String)
                                cell.setCellValue((String)obj);
                            else if(obj instanceof Integer)
                                cell.setCellValue((Integer)obj);
                        }
                    }
                    try
                    {
                        //Write the workbook in file system
                        FileOutputStream out = new FileOutputStream(new File("/home/ohelig/eclipse/New Microsoft Office Excel Worksheet.xlsx"));
                        workbook.write(out);
                        out.close();
                        System.out.println("Write Successfully.");
                    } 
                    catch (Exception e) 
                    {
                        e.printStackTrace();
                    }
                }
    }

***Update Class: UpdateExcel.java***

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class UpdateExcel {

    public static void main(String[] args) {
        XSSFWorkbook workbook=null;
        XSSFSheet sheet;
        try{
        FileInputStream file = new FileInputStream(new File("/home/ohelig/eclipse/New Excel Worksheet.xlsx"));

          //Create Workbook instance holding reference to .xlsx file
          workbook = new XSSFWorkbook(file);

          //Get first/desired sheet from the workbook
          //Most of people make mistake by making new sheet by looking in tutorial
          sheet = workbook.getSheetAt(workbook.getActiveSheetIndex());

          Employee ess = new Employee(6,"Yanish","Pradhananga");
          //Get the count in sheet
          int rowCount = sheet.getLastRowNum()+1;
          Row empRow = sheet.createRow(rowCount);
          System.out.println();
          Cell c1 = empRow.createCell(0);
          c1.setCellValue(ess.getId());
          Cell c2 = empRow.createCell(1);
          c2.setCellValue(ess.getFirstName());
          Cell c3 = empRow.createCell(2);
          c3.setCellValue(ess.getLastName());
          }
        catch (Exception e) 
        {
            e.printStackTrace();
        }
         try
          {
              //Write the workbook in file system
              FileOutputStream out = new FileOutputStream(new 
                  File("/home/ohelig/eclipse/New Excel Worksheet.xlsx"));
              workbook.write(out);
              out.close();
              System.out.println("Update Successfully");
          } 
          catch (Exception e) 
          {
              e.printStackTrace();
          }
      }
}

Upvotes: 0

Gagravarr
Gagravarr

Reputation: 48326

See the Apache POI Components and Dependencies page for details. You're missing some of the jars, hence the compile error.

If you want to work with both HSSF (.xls) and XSSF (.xlsx), which I guess you do as you're talking about WorkbookFactory, you'll need to include both the main POI jar and the POI-OOXML jar, plus all of their dependencies. With those jars on your classpath, you'll be sorted

Also, you might want to think about using something like Apache Maven or Apache Ivy to handle your dependencies for you, that way you can avoid missing jar problems like this

Upvotes: 2

Biswajit
Biswajit

Reputation: 2496

try this


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelExample {

    public static void main(String[] args) throws IOException {

        try {

            FileInputStream file = new FileInputStream(new File("C:\\test.xls"));

            HSSFWorkbook workbook = new HSSFWorkbook(file);
            HSSFSheet sheet = workbook.getSheetAt(0);
            Cell cell = null;

            //Update the value of cell
            cell = sheet.getRow(1).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 2);
            cell = sheet.getRow(2).getCell(2);
            cell.setCellValue(cell.getNumericCellValue() * 2);
            Row row = sheet.getRow(0);
            row.createCell(3).setCellValue("Value 2");

            file.close();

            FileOutputStream outFile =new FileOutputStream(new File("C:\\update.xls"));
            workbook.write(outFile);
            outFile.close();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

Upvotes: 13

Prahalad Deshpande
Prahalad Deshpande

Reputation: 4767

Are you using Maven?

In case yes, then please refer to the last comment at the following link:

http://apache-poi.1045710.n5.nabble.com/Where-is-WorkbookFactory-td2307412.html

Upvotes: 1

Related Questions