CODEWITHSUNDEEP
javaexcelapache-poi
Cosmin
Cosmin

Reputation: 954

Using Apache POI to get/modify cell values in one or more Excel sheets

I have a small application written in Java which uses Apache POI to read/modify values in an Excel document. I'm referencing cells using sheet name, like for cell A1 in sheet "Sheet1", I use "Sheet1!A1".

The application runs from the command line with three arguments: the document name, the cells with values I want to replace, the cells from where I want to get the output.

Example: ReadExcel test.xls Sheet1!B2=10;Sheet1!B3=20 Sheet1!B7

The above example works fine.

The problem is when I want to modifiy cells or get the output from another sheet.

Example: ReadExcel test.xls Sheet1!B2=10;Sheet1!B3=20 Sheet2!B2

My code is bellow:

package poitest;

import java.util.List;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
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.CellReference;
import org.apache.poi.ss.usermodel.*;

public class ReadExcel {
    public static void main(String[] args) throws FileNotFoundException, IOException {
        // Will contain cell name / value pair for input cells          
        Map<String, String> inputCellsMap = new HashMap<String, String>();

        // Will contain cell name for output cells
        List<String> outputCells = new ArrayList<String>();

        // Open the Excel file
        FileInputStream file = new FileInputStream(new File(args[0]));

        // Get the current workbook
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        // Get the first sheet of the workbook
        HSSFSheet sheet = workbook.getSheetAt(0);

        // Get the input cells that need to be modified and
        // store their name and value in the inputCellsMap
        for (String element : args[1].split(";")) {
            inputCellsMap.put(element.split("=")[0], element.split("=")[1]);
        }

        // Get the output cells that will be accessed for resulting values
        for (String element : args[2].split(";")) {
            outputCells.add(element);           
        }

        // Loop through the cells that need to be modified and 
        // set the new value in the Excel document
        Iterator<Entry<String,String>> inputIterator = inputCellsMap.entrySet().iterator();
        while (inputIterator.hasNext()) {
            Map.Entry<String,String> inputEntry = (Map.Entry<String,String>) inputIterator.next();

            CellReference cellReferenceInput = new CellReference(inputEntry.getKey());
            int cellReferenceInputRow = cellReferenceInput.getRow();
            int cellReferenceInputColumn = cellReferenceInput.getCol();

            Row rowInput = sheet.getRow(cellReferenceInputRow);
            if (rowInput == null)
                rowInput = sheet.createRow(cellReferenceInputRow);
            Cell cellInput = rowInput.getCell(cellReferenceInputColumn, Row.CREATE_NULL_AS_BLANK);              
            cellInput.setCellValue(Integer.parseInt(inputEntry.getValue()));        
        }

        // Apply all formulas after altering cell values
        HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);

        // Get the results from the output cells
        for (int i = 0; i < outputCells.size(); i++) {
            CellReference cellReferenceOutput = new CellReference(outputCells.get(i));
            int cellReferenceOutputRow = cellReferenceOutput.getRow();
            int cellReferenceOutputColumn = cellReferenceOutput.getCol();

            Row rowOutput = sheet.getRow(cellReferenceOutputRow);
            Cell cellOutput = rowOutput.getCell(cellReferenceOutputColumn, Row.CREATE_NULL_AS_BLANK);

            // Display results
            System.out.println(cellOutput.getNumericCellValue());                       
        }           

        workbook.close();       
    }
}

Upvotes: 0

Views: 1204

Answers (1)

Andreas
Andreas

Reputation: 159185

If you look at the longest constructor of CellReference, you'd notice that a reference consists of 5 properties:

  • String sheetName (can be null)
  • int row
  • int col
  • boolean rowAbsolute
  • boolean colAbsolute

Your command-line arguments include the sheet name, but you're not using it.

First, remove the following line from your code: HSSFSheet sheet = workbook.getSheetAt(0);

Instead, you'll need to lookup the sheet by name using getSheet(String), right after you create the CellReference:

HSSFSheet sheet = workbook.getSheet(cellReferenceInput.getSheetName());

HSSFSheet sheet = workbook.getSheet(cellReferenceOutput.getSheetName());

Upvotes: 2

Related Questions