1

How to copy a 2016 excel to another(including formulas, cell style and date format) using apache poi.Below is the code that i have tried. It copies the file, but does not copy cell style. Also the cell type method is shown as deprecated, is their a new approach to this ? Also i need to try it out using XSSF and not HSSF.

public void copy(String origFileName,String newFileName) {
            // TODO Auto-generated method stub    
            try {
                BufferedInputStream bis = new BufferedInputStream(new FileInputStream(origFileName));
                XSSFWorkbook origWorkbook = new XSSFWorkbook(bis);
                XSSFWorkbook copyWorkbook = new XSSFWorkbook();
                XSSFSheet origSheet = null;
                XSSFRow origRow = null;
                XSSFCell origCell = null;
                XSSFSheet copySheet = null;
                XSSFRow copyRow = null;
                XSSFCell copyCell = null;
                int origSheets = origWorkbook.getNumberOfSheets();
                int fCell = 0;
                int lCell = 0;
                int fRow = 0;
                int lRow = 0;
                for (int iSheet = 0; iSheet < origSheets; iSheet++) {
                    origSheet = origWorkbook.getSheetAt(iSheet);
                    if (origSheet != null) {
                        copySheet = copyWorkbook.createSheet(origSheet.getSheetName());
                        fRow = origSheet.getFirstRowNum();
                        lRow = origSheet.getLastRowNum();
                        for (int iRow = fRow; iRow <= lRow; iRow++) {
                            origRow = origSheet.getRow(iRow);
                            copyRow = copySheet.createRow(iRow);
                            if (origRow != null) {
                                fCell = origRow.getFirstCellNum();
                                lCell = origRow.getLastCellNum();
                                for (int iCell = fCell; iCell < lCell; iCell++) {
                                    origCell = origRow.getCell(iCell);
                                    copyCell = copyRow.createCell(iCell);
                                    if (origCell != null) {
                                        CellStyle cellStyle = setCellStyle(copyWorkbook, origCell);
                                        copyCell.setCellType(origCell.getCellType());
                                        switch (origCell.getCellType()) {
                                        case XSSFCell.CELL_TYPE_BLANK:
                                            copyCell.setCellValue("");
                                            break;

                                        case XSSFCell.CELL_TYPE_BOOLEAN:
                                            copyCell.setCellValue(origCell.getBooleanCellValue());
                                            break;

                                        case XSSFCell.CELL_TYPE_ERROR:
                                            copyCell.setCellErrorValue(origCell.getErrorCellValue());
                                            break;

                                        case XSSFCell.CELL_TYPE_FORMULA:
                                            copyCell.setCellFormula(origCell.getCellFormula());
                                            break;

                                        case XSSFCell.CELL_TYPE_NUMERIC:
                                            copyCell.setCellValue(origCell.getNumericCellValue());
                                            break;

                                        case XSSFCell.CELL_TYPE_STRING:
                                            copyCell.setCellValue(origCell.getStringCellValue());
                                            break;
                                        default:
                                            copyCell.setCellFormula(origCell.getCellFormula());
                                        }
                                        copyCell.setCellStyle(cellStyle);
                                    }
                                }
                            }
                        }
                    }
                }

                bis.close();
                BufferedOutputStream bos;
                bos = new BufferedOutputStream(new FileOutputStream(newFileName, true));
                copyWorkbook.write(bos);
                bos.close();
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }
    private CellStyle setCellStyle(XSSFWorkbook copyWorkbook, XSSFCell origCell) {
            CellStyle cellStyle = copyWorkbook.createCellStyle();
            //cellStyle.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
            cellStyle.setFillForegroundColor(origCell.getCellStyle().getFillForegroundColor());
            cellStyle.setFont(origCell.getCellStyle().getFont());
            cellStyle.setBottomBorderColor(origCell.getCellStyle().getBottomBorderColor());
            cellStyle.setLeftBorderColor(origCell.getCellStyle().getLeftBorderColor());
            cellStyle.setLocked(origCell.getCellStyle().getLocked());
            cellStyle.setQuotePrefixed(origCell.getCellStyle().getQuotePrefixed());
            cellStyle.setRightBorderColor(origCell.getCellStyle().getRightBorderColor());
            cellStyle.setRotation(origCell.getCellStyle().getRotation());
            cellStyle.setShrinkToFit(origCell.getCellStyle().getShrinkToFit());
            cellStyle.setTopBorderColor(origCell.getCellStyle().getTopBorderColor());
            cellStyle.setVerticalAlignment(origCell.getCellStyle().getVerticalAlignmentEnum());
            cellStyle.setWrapText(origCell.getCellStyle().getWrapText());
            cellStyle.setHidden(origCell.getCellStyle().getHidden());
            cellStyle.setIndention(origCell.getCellStyle().getIndention());
            cellStyle.setAlignment(origCell.getCellStyle().getAlignmentEnum());
            cellStyle.setBorderBottom(origCell.getCellStyle().getBorderBottomEnum());
            cellStyle.setBorderLeft(origCell.getCellStyle().getBorderLeftEnum());
            cellStyle.setBorderRight(origCell.getCellStyle().getBorderRightEnum());
            cellStyle.setBorderTop(origCell.getCellStyle().getBorderTopEnum());
            cellStyle.setFillPattern(origCell.getCellStyle().getFillPatternEnum());
            return cellStyle;
        }
user123
  • 111
  • 1
  • 6

1 Answers1

0

to copy the style see: Copy a XSSF/HSSF-Cells into a new XSSFWorkbook
I modified your code and its working fine.
See the modified code for new way to handle cell type.

Assuming workbook exist under c:\temp\1.xlsx

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CopyCat {

    public void copy(String origFileName,String newFileName) {
        // TODO Auto-generated method stub    
        try {
            BufferedInputStream bis = new BufferedInputStream(new FileInputStream(origFileName));
            XSSFWorkbook origWorkbook = new XSSFWorkbook(bis);
            XSSFWorkbook copyWorkbook = new XSSFWorkbook();
            XSSFSheet origSheet = null;
            XSSFRow origRow = null;
            XSSFCell origCell = null;
            XSSFSheet copySheet = null;
            XSSFRow copyRow = null;
            XSSFCell copyCell = null;
            int origSheets = origWorkbook.getNumberOfSheets();
            int fCell = 0;
            int lCell = 0;
            int fRow = 0;
            int lRow = 0;
            for (int iSheet = 0; iSheet < origSheets; iSheet++) {
                origSheet = origWorkbook.getSheetAt(iSheet);
                if (origSheet != null) {
                    copySheet = copyWorkbook.createSheet(origSheet.getSheetName());
                    fRow = origSheet.getFirstRowNum();
                    lRow = origSheet.getLastRowNum();
                    for (int iRow = fRow; iRow <= lRow; iRow++) {
                        origRow = origSheet.getRow(iRow);
                        copyRow = copySheet.createRow(iRow);
                        if (origRow != null) {
                            fCell = origRow.getFirstCellNum();
                            lCell = origRow.getLastCellNum();
                            for (int iCell = fCell; iCell < lCell; iCell++) {
                                origCell = origRow.getCell(iCell);
                                copyCell = copyRow.createCell(iCell);
                                if (origCell != null) {
                                    // read more here
                                    //https://stackoverflow.com/questions/32067614/copy-a-xssf-hssf-cells-into-a-new-xssfworkbook

                                    XSSFCellStyle origCellStyle = origCell.getCellStyle();
                                    XSSFCellStyle copyCellStyle = copyWorkbook.createCellStyle();
                                    copyCellStyle.cloneStyleFrom(origCellStyle);
                                    copyCell.setCellStyle(copyCellStyle);

                                    CellType origCellType = origCell.getCellTypeEnum();
                                    copyCell.setCellType(origCellType);  

// This is the new way :getCellTypeEnum  

switch (origCellType) {
                                    case BLANK:
                                        copyCell.setCellValue("");
                                        break;

                                    case BOOLEAN:
                                        copyCell.setCellValue(origCell.getBooleanCellValue());
                                        break;

                                    case ERROR:
                                        copyCell.setCellErrorValue(origCell.getErrorCellValue());
                                        break;

                                    case FORMULA:
                                        copyCell.setCellFormula(origCell.getCellFormula());
                                        break;

                                    case NUMERIC:
                                        copyCell.setCellValue(origCell.getNumericCellValue());
                                        break;

                                    case STRING:
                                        copyCell.setCellValue(origCell.getStringCellValue());
                                        break;
                                    default:
                                        copyCell.setCellFormula(origCell.getCellFormula());
                                    }
                                }
                            }
                        }
                    }
                }
            }

            bis.close();
            BufferedOutputStream bos;
            bos = new BufferedOutputStream(new FileOutputStream(newFileName, true));
            copyWorkbook.write(bos);
            bos.close();
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

public static void main(String[] args) {
    new CopyCat().copy("c:\\temp\\1.xlsx", "c:\\temp\\2.xlsx");
}
}
JavaSheriff
  • 7,074
  • 20
  • 89
  • 159