0

I am having some trouble with my Java code. I use NetBeans 8.0.1.

I am trying to read an Excel file, with several sheets and then print the reading to the output window. Something in the code is not allowing the printing. I add a for-loop in order to loop over the sheets -- will that work?

Also I've been searching for how to print the reading into a .txt file, but I can't find a thing about it. Can anybody help me?

This is my code:

package read_write_excel_v2;

import java.io.IOException;
import java.util.Iterator;
import javax.swing.JOptionPane;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class JIA_ReadingRewritingWorbooks_v5 {

    public static String GetFileExtension(String path2){
        String pathName = path2;
        String path = "";
        String ext = "";
        int mid = pathName.lastIndexOf(".");
        path = pathName.substring(0, mid);
        ext = pathName.substring(mid + 1, pathName.length());
        return ext;
   }

    /**
     * Excel 2007 (and newer) file reader function. Uses Apache POI. Returns            excel content
     * @throws org.apache.poi.openxml4j.exceptions.InvalidFormatException
     * @throws java.io.IOException
     */
    public static void main(String[] args) throws IOException, InvalidFormatException { 
        String ExcelPath;
        //ExcelPath = JOptionPane.showInputDialog(null, "Enter Excel path: ");
        ExcelPath = "ExcelJAVAWrite_demo.xlsx";

        try{
            // OPCPackage eats less memory than a InputStream, which requires
            // more memory
            OPCPackage pkg;
            pkg = OPCPackage.open(ExcelPath);
            XSSFWorkbook wb = new XSSFWorkbook(pkg);       // Declare XSSF WB
            String fileExtn = GetFileExtension(ExcelPath);
            //Sheet sheet = null;

            for(int i = 0; i < wb.getNumberOfSheets(); i++){
                Sheet sheet = wb.getSheetAt(i);

                // Iteration of rows - rows are ready from the sheet
                Iterator<Row> rows = sheet.rowIterator();

                while(rows.hasNext()){
                    Row row = (Row) rows.next();

                    Iterator<Cell> cells = row.cellIterator();

                    while(cells.hasNext()){
                        Cell cell = (Cell) cells.next();

                        // Ready with the iteration of cells
                        // Now we get the cell type and display the values                    
                        switch(cell.getCellType()){
                            case Cell.CELL_TYPE_BLANK:
                                System.out.print(cell.getStringCellValue() + "\t");
                                break;

                            case Cell.CELL_TYPE_BOOLEAN:
                                System.out.print(cell.getBooleanCellValue() + "\t");
                                break;

                            case Cell.CELL_TYPE_ERROR:
                                System.out.print(cell.getErrorCellValue() + "\t");
                                break;

                            case Cell.CELL_TYPE_FORMULA:
                                System.out.print(cell.getCellFormula());
                                break;
                            default:
                                System.out.println();

                            case Cell.CELL_TYPE_NUMERIC:
                                if(DateUtil.isCellDateFormatted(cell)){
                                    System.out.print(cell.getDateCellValue() + "\t");
                                } else {
                                    System.out.print(cell.getNumericCellValue() + "\t");
                                }
                                break;

                            case Cell.CELL_TYPE_STRING:
                                System.out.print(cell.getRichStringCellValue().getString() + "\t");
                                break;                            
                        }
                    } 
                    System.out.println("");
                } 
            }
            //wb.write(pkg);
            pkg.close();
            System.out.println("Excel file written successfully on screen");
        }
        catch ( IOException ex ) {
            ex.printStackTrace();
        }
    }
}
Roddy of the Frozen Peas
  • 14,380
  • 9
  • 49
  • 99
N4K2
  • 13
  • 1
  • 6
  • 1
    Looking at your code, it looks like your greatest risk is that the file is not actually where the program is looking. What does it actually print when you run it? (Once you get it printing to screen, you can easily convert it to print to String or text file. One step at a time ...) – Roddy of the Frozen Peas Oct 14 '14 at 20:51
  • If all you want is the plain text contents of an Excel file, why aren't you just using one of the build in POI Excel to Text converters, or Apache Tika? – Gagravarr Oct 14 '14 at 21:03
  • @RoddyoftheFrozenPeas I have a path of a file from the desktop. The file path that I put in the code was just as an example of file name. I did the modification that you suggest and the printing is the message "Excel file written successfully on screen" but not the excel data. I know, step by step. I am learning Java, but I been asked to do this code. – N4K2 Oct 14 '14 at 21:12
  • @Gagravarr, I will search for it, because I am not familiar to those Apache POI converters. Totally new in this. Do you have any tutorial that you recommend? – N4K2 Oct 14 '14 at 21:14
  • If you just want plain text, download the [Apache Tika App](http://tika.apache.org/download.html) and run it with the `--text` flag on the Excel file, it'll call POI for you and return the text – Gagravarr Oct 14 '14 at 22:48
  • Update: My bad, I had a problem with the excel worbook, is printing everything! Thanks – N4K2 Oct 15 '14 at 14:05
  • @RoddyoftheFrozenPeas how can I convert the system.out to a text file? – N4K2 Oct 15 '14 at 16:20
  • @N4K2 - I'd append to a String instead of using System.out, then reference a question like this one for actually writing to file: http://stackoverflow.com/questions/2885173/java-how-to-create-and-write-to-a-file – Roddy of the Frozen Peas Oct 15 '14 at 16:28

0 Answers0