0

I am trying to copy the contents of one excel sheet into another sheet without the formatting applied to cells using Apache POI in java. But i have a NULL Pointer Exception Thrown in LINE
switch (cell.getCellType()) {
Why is this error appearing?

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

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

    public class Automate {

        public static void main(String[] args) {
            // TODO Auto-generated method stub
            final File myFile = new File(
                    "sample.xlsx");
            FileInputStream fis;
            try {
                fis = new FileInputStream(myFile);
                // Finds the workbook instance for XLSX file
                final XSSFWorkbook wb = new XSSFWorkbook(fis);
                // Return first sheet from the XLSX workbook
                XSSFSheet sheet = wb.getSheetAt(0);
                System.out.println(sheet.getLastRowNum());
                XSSFWorkbook outWorkbook = getFilteredWorkBook(sheet);

                // save merged file
                File outFile = new File("C:\\filtered.xlsx");
                if (!outFile.exists()) {
                    outFile.createNewFile();
                }
                FileOutputStream out = new FileOutputStream(outFile);
                outWorkbook.write(out);
                out.close();
                System.out.println("Files were created successfully");

            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }

        private static XSSFWorkbook getFilteredWorkBook(XSSFSheet sheet) {
            // TODO Auto-generated method stub
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet outSheet = workbook.createSheet();
            int i = 0;
            for (int rowCount = 0; rowCount < sheet.getLastRowNum(); rowCount++) {
                System.out.println(rowCount);
                XSSFRow outRow = outSheet.createRow(i);
                XSSFRow row = sheet.getRow(rowCount);
                int j = 0;

                for (int colCount = 0; colCount < 10; colCount++) {
                    System.out.println(colCount);
                    XSSFCell outCell = outRow.createCell(j);
                    XSSFCell cell = row.getCell(colCount);
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_FORMULA:
                        outCell.setCellFormula(cell.getCellFormula());
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        outCell.setCellValue(cell.getNumericCellValue());
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        outCell.setCellValue(cell.getStringCellValue());
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        outCell.setCellType(XSSFCell.CELL_TYPE_BLANK);
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        outCell.setCellValue(cell.getBooleanCellValue());
                        break;

                    default:
                        outCell.setCellValue(cell.getStringCellValue());
                        break;
                    }

                }
            }
            return workbook;
        }

    }
Arc676
  • 4,445
  • 3
  • 28
  • 44

0 Answers0