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(); } }
}
}
Subscribe to:
Post Comments (Atom)
excellent !!
ReplyDeletecell.getColumnIndex() shows error.
ReplyDeleteuse this cell..getCellNum() instead of cell.getColumnIndex() will solve your problem
Deletegreat job...
ReplyDeleteimport org.apache.poi.hssf.usermodel.HSSFCell;
ReplyDeleteimport 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
can we have text file data to excel file converter??
ReplyDeletemeans read from text file and write in excel file
ReplyDeleteThis comment has been removed by the author.
ReplyDeletehow to remove last comma from value
ReplyDeletesuper... perfect code
ReplyDeleteC:\training workspace\ParseExcel.xls (The system cannot find the file specified)
ReplyDeleteat 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
you need to put your slashes as / not \
ReplyDeleteI have an error;
ReplyDeleteException 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
[ask]
ReplyDeleteif convert successfully, so how i can save that convert into txt ?
because, convert just in my log netbeans
[ASK]
ReplyDeleteException 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
I have an error;
ReplyDeleteException 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