CODEWITHSUNDEEP
javaexcelapache-poi
JsNinja
JsNinja

Reputation: 129

How can i get name box from excel?

I'm comparing 2 excel files cell by cell and when i found a difference i print it example DIFF Cell values at: Sch HI (1 of 4)!K40 => '6.0' v/s '5.0' cell position old value and new value

so instead of cell position i need to print the box name 

 @Override
    public void reportDiffCell(CellPos c1, CellPos c2) {
        sheets.add(c1.getSheetName());
        rows.add(c1.getRow());
        cols.add(c1.getColumn());
        results.add("DIFF Cell values at: " + c1.getCellPosition() + " => '" + c1.getCellValue()
                + "' v/s '" + c2.getCellValue() + "'");
    }

enter image description here

Upvotes: 1

Views: 580

Answers (2)

andrewJames
andrewJames

Reputation: 21902

An example of gathering the range names from a spreadsheet, so that they can be compared for a "diff" report...

For example, here is a spreadsheet with two named ranges:

Name     : animals
Refers to: Sheet1!$C$3:$D$4,Sheet1!$C$5

Name     : birds
Refers to: Sheet1!$B$8:$B$9

named ranges

The following code populates the range names and references into a map:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;
import java.util.HashMap;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Name;

...

public Map<String, String> compare(String fileName) {

    Map<String, String> namesMap = new HashMap();

    File file = new File(fileName);
    try (InputStream is = new FileInputStream(file)) {
        Workbook wb = WorkbookFactory.create(is);
        List<? extends Name> names = wb.getAllNames();

        names.forEach((name) -> {
            namesMap.put(name.getNameName(), name.getRefersToFormula());
        });

    } catch (FileNotFoundException ex) {
        // handler
    } catch (IOException ex) {
        // handler
    }

    return namesMap;
}

Now you can repeat this for each of your two Excel files, and then compare the keys and values in the two map objects (different range names; same names but different ranges of cells).

UPDATE: THe above sample was written using Open JDK 13. The following POI dependencies were used (assuming Maven):

<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.2</version>
    </dependency>
    <dependency> 
        <groupId>org.apache.poi</groupId> 
        <artifactId>poi-ooxml</artifactId> 
        <version>4.1.2</version>
    </dependency> 
</dependencies>

Upvotes: 2

Excel Hero
Excel Hero

Reputation: 14754

You could add a VBA function to the workbook and call that from java...

Function CellName(r As Range)
    On Error Resume Next
    CellName = r.Name.Name
    If Err Then CellName = r.Address(0, 0)
End Function

Upvotes: 0

Related Questions