Getting cell value from excel file with format

Question

I'm trying to append all the content from an excel file sheet to another excel file sheet. Everything is ok, excepting the cell's format.

To take the same format from the original excel file, I'm using HSSFCellStyle.

This is my code:

Private declarations:

private HSSFRow row1;
private HSSFCell cell1;
private HSSFCellStyle cellStyle1;
private FileInputStream inFile1,inFile2;
private HSSFSheet excelSheet1=null,excelSheet2=null;
private HSSFWorkbook excelBook1=null,excelBook2=null;

Main method:

public static void main(String args[]){
    appendToExcelClass test = new appendToExcelClass();
    test.appendToExcel(new File("C:\\excel1.xls"),new File("C:\\excel2.xls"));
}

Method to append the content:

public void appendToExcel(File file1,File file2){
    try{
        if(file1.exists() && file2.exists()){
            inFile1 = new FileInputStream(file1);
            inFile2 = new FileInputStream(file2);
            excelBook1 = new HSSFWorkbook(inFile1);
            excelBook2 = new HSSFWorkbook(inFile2);
            excelSheet1 = excelBook1.getSheetAt(0);
            excelSheet2 = excelBook2.getSheetAt(0);
            Iterator rowIter = excelSheet2.rowIterator();
            while(rowIter.hasNext()){
                HSSFRow myRow = (HSSFRow) rowIter.next();
                Iterator cellIter = myRow.cellIterator();
                List<String> cellStoreVector;
                cellStoreVector = new ArrayList<>();
                while(cellIter.hasNext()){
                    HSSFCell myCell = (HSSFCell) cellIter.next();
                    String cellvalue = myCell.getStringCellValue();
                    cellStyle1 = myCell.getCellStyle();  /*The problem is in this part, I think I didn't get well how get the cell's format*/
                    cellStoreVector.add(cellvalue);
                }
                row1 = excelSheet1.createRow(excelSheet1.getLastRowNum()+1);
                cell1 = row1.createCell(0);
                cell1.setCellStyle(cellStyle1);  /*At the moment to execute this part, it throws this: This Style does not belong to the supplied Workbook. Are you trying to assign a style from one workbook to the cell of a different workbook?*/
                cell1.setCellValue(cellStoreVector.get(0).toString());
            }
            FileOutputStream outFile1 = new FileOutputStream(file1);
            excelBook1.write(outFile1);
            outFile1.close();
        }
    }catch(Exception ex){
        ex.printStackTrace();
    }
}

I'm not sure with the part that throws me the error.

In advance, thanks.

You should paste in some of the error so we can see the StackTrace else we are just guessing. — Russell Shingleton
– Russell Shingleton, Commented Dec 30, 2013 at 21:25
Sorry for not detail more the error, but basically, it's only the message " This Style does not belong to the supplied Workbook. Are you trying to assign a style from one workbook to the cell of a different workbook? ". @Howard Schuzmant give me a probably solution, if the problem persists, I will do it. Thanks. — Vlad
– Vlad, Commented Dec 31, 2013 at 8:31

Howard Schutzman · Accepted Answer · 2013-12-31 00:46:41Z

2

The error message pretty much explains the issue. A HSSFWorkbook contains a table of all the styles that you can use. Whenever you call setCellStyle, the HSSFCellStyle that you pass in must be in that table. What has happened is that the HSSFCellStyle that you extracted from the cell in excelBook2 does not exist in excelBook1.

To correct this you can call excelBook1.createCellStyle to create a new style, and clone its attributes from the extracted style. Here is an example of how to do this.

HSSFCellStyle newStyle = excelBook1.createCellStyle();
newStyle.cloneStyleFrom(cellStyle1);

answered Dec 31, 2013 at 0:46

Howard Schutzman

2,1351 gold badge15 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Vlad Over a year ago

It has sense. I will probe it, and I will tell you how it works later. Thanks a lot for your help!!!

Vlad Over a year ago

I try the code, the error dissapears, but the excel didn't copy the style, I don't know if I'm doing wrong the process... Before the first while I put this HSSFCellStyle newStyle = excelBook1.createCellStyle();, after, inside the second while I put this cellStyle1 = myCell.getCellStyle(), before the for this newStyle.cloneStyleFrom(cellStyle1), and inside the for this cell1.setCellStyle(newStyle). Sorry for asking and asking, I hope you can help me.

Howard Schutzman Over a year ago

I am away for a few days, but I will see if I can write some example code for you when I get back

Vlad Over a year ago

No problem, I pause that part of the project because I started another independent part that is necessary at the moment. Thanks for your big help.

Howard Schutzman Over a year ago

This turns out to be a much messier problem than I thought. It is solvable, but not without a lot of code. I will post another answer below giving you an outline of how to solve the problem, but you will have to write the actual code because it is quite extensive.

Community · Accepted Answer · 2017-05-23 12:33:45Z

I am posting this second answer because I think the first answer, although incomplete, is useful. The basic problem is what I identified in the first answer -- each workbook has a table of associated styles and you have to make sure that all the styles from excelBook2 are also present in excelBook1.

However, the big problem is, how do you know which styles from excelBook2 are missing from excelBook1? Here are two links which explain the problem in more detail:

Why is a cloned HSSFCellStyle not equal to the style it was cloned from?

http://apache-poi.1045710.n5.nabble.com/HSSFCellStyle-help-td2295062.html

Now let me outline my suggestion of what you have to do to overcome this issue.

a) Write a method with the following footprint:

public boolean stylesAreEquivalent(HSSFCellStyle style1, HSSFCellStyle style2)

In this method, you compare every property in style1 with the same property in style2. That includes the alignment, border styles, rgb values of the various colors, font name, font size, and so forth. The method would return a true if all of the underlying properties are the same in both styles.

b) Create the following HashMap

HashMap<short,HSSFCellStyle> book2Styles = new HashMap<short,HSSFCellStyle>();

This map will be indexed by the value you get from HSSFCellStyle.getIndex().

c) Get the style from the cell you wish to copy from book2 to book1. Get its index using getIndex. Execute the following code:

HSSFCellStyle styleFromMap = book2Styles.get(index);

If styleFromMap is not null, go to step d) below.

If styleFromMap is null, then what you need to do is find out if there already is an equivalent style in book1 that you can use. You can find that out by calling your stylesAreEquivalent method on every style in book1 to see if any match. If there is a match, then execute the following code:

book2Styles.put(index, equivalentBook1Style);
styleFromMap = equivalentBook1Style;

If there is no equivalent style, then you need to create a new style and clone it using the code from my previous answer.

HSSFCellStyle newStyle = excelBook1.createCellStyle();
newStyle.cloneStyleFrom(cellStyle1);
book2Styles.put(index, newStyle);
styleFromMap = newStyle;

d) Copy the cell from book2 to book1, using the style in styleFromMap.

Using the approach will minimize the number of new styles you have to create in book1. Since there is a limit of 400 styles, this is important.

I wish there were an easier way, but this is definitely doable and will not be that much code.

I was reading your post and I think I've caught the idea, really interesting, I will check it out and I'll implement in my code. I'll tell if it works me. Thanks again for all your help!

Collectives™ on Stack Overflow

Getting cell value from excel file with format

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related