Tuesday, November 20, 2012

Read Data From Excel and Write it into Text file



import java.io.BufferedInputStream;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.Writer;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
 
public class ReadExcel {
 
    public static void main( String [] args ) {
    Writer writer = null;
        try {
   
             InputStream input = new BufferedInputStream(
                        new FileInputStream("C:/MyWorkSpace/Temp/poi-test.xls"));
            POIFSFileSystem fs = new POIFSFileSystem( input );
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0); //sheet of excel
         
            File file = new File("writedatasamp.txt");  
            writer = new BufferedWriter(new FileWriter(file));
            Iterator rows = sheet.rowIterator();
            while( rows.hasNext() ) {  
                HSSFRow row = (HSSFRow) rows.next();
                System.out.println("\n");
                Iterator cells = row.cellIterator();
               
                writer.write("insert into Emp values(");
                while( cells.hasNext() ) {
                     
                    HSSFCell cell = (HSSFCell) cells.next();
                    if(HSSFCell.CELL_TYPE_NUMERIC==cell.getCellType()) {
                    System.out.print( cell.getNumericCellValue()+"     "+cell.getColumnIndex() );
                    if(cell.getColumnIndex()==3)
                  writer.write(String.valueOf(cell.getNumericCellValue()));
                    else
                    writer.write(String.valueOf(cell.getNumericCellValue()+","));
                    }
                    else
                    if(HSSFCell.CELL_TYPE_STRING==cell.getCellType()) {
                        System.out.print( cell.getStringCellValue()+"     " );
                        writer.write("'"+cell.getStringCellValue()+"',");
                    }
                    else
                        if(HSSFCell.CELL_TYPE_BOOLEAN==cell.getCellType()) {
                        System.out.print( cell.getBooleanCellValue()+"     " );
                       writer.write("'"+String.valueOf(cell.getBooleanCellValue()+"',"));
                        }
                        else
                            if(HSSFCell.CELL_TYPE_BLANK==cell.getCellType())
                                System.out.print( "BLANK     " );
                                else
                            System.out.print("Unknown cell type");
             
                   
                }
                 
                writer.write(");"+"\n");
            }
           
             
        } catch ( IOException ex ) {
            ex.printStackTrace();
        } finally {             try {                 if (writer != null) {                     writer.close();                 }             } catch (IOException e) {                 e.printStackTrace();             }         }
    }
     
}


16 comments:

  1. cell.getColumnIndex() shows error.

    ReplyDelete
    Replies
    1. use this cell..getCellNum() instead of cell.getColumnIndex() will solve your problem

      Delete
  2. import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;


    those lines show error

    ReplyDelete
  3. can we have text file data to excel file converter??

    ReplyDelete
  4. means read from text file and write in excel file

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. how to remove last comma from value

    ReplyDelete
  7. C:\training workspace\ParseExcel.xls (The system cannot find the file specified)
    at java.io.FileInputStream.open0(Native Method)
    at java.io.FileInputStream.open(Unknown Source)
    at java.io.FileInputStream.(Unknown Source)
    at java.io.FileInputStream.(Unknown Source)
    at ReadExcel.main(ReadExcel.java:23)
    I have this error...please help

    ReplyDelete
  8. you need to put your slashes as / not \

    ReplyDelete
  9. I have an error;
    Exception in thread "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
    at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:131)
    at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:104)
    at org.apache.poi.poifs.filesystem.POIFSFileSystem.(POIFSFileSystem.java:138)
    at excelconverter.ReadExcel.main(ReadExcel.java:27)
    Picked up JAVA_TOOL_OPTIONS: -Djava.vendor="Sun Microsystems Inc."
    Java Result: 1

    ReplyDelete
  10. [ask]
    if convert successfully, so how i can save that convert into txt ?
    because, convert just in my log netbeans

    ReplyDelete
  11. [ASK]
    Exception in thread "main" java.lang.Error: Unresolved compilation problems:
    CELL_TYPE_NUMERIC cannot be resolved or is not a field
    Syntax error on token ")", delete this token
    CELL_TYPE_STRING cannot be resolved or is not a field
    CELL_TYPE_BOOLEAN cannot be resolved or is not a field
    CELL_TYPE_BLANK cannot be resolved or is not a field
    please help

    ReplyDelete
  12. I have an error;
    Exception in thread "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
    at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:131)
    at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:104)
    at org.apache.poi.poifs.filesystem.POIFSFileSystem.(POIFSFileSystem.java:138)
    at excelconverter.ReadExcel.main(ReadExcel.java:27)
    Picked up JAVA_TOOL_OPTIONS: -Djava.vendor="Sun Microsystems Inc."
    Java Result: 1

    ReplyDelete