import java.io.FileInputStream; | |
import java.io.FileOutputStream; | |
import java.io.IOException; | |
import java.io.InputStream; | |
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.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 ReadWriteExcelFile { | |
public static void readXLSFile() throws IOException | |
{ | |
InputStream ExcelFileToRead = new FileInputStream("C:/Test.xls"); | |
HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead); | |
HSSFSheet sheet=wb.getSheetAt(0); | |
HSSFRow row; | |
HSSFCell cell; | |
Iterator rows = sheet.rowIterator(); | |
while (rows.hasNext()) | |
{ | |
row=(HSSFRow) rows.next(); | |
Iterator cells = row.cellIterator(); | |
while (cells.hasNext()) | |
{ | |
cell=(HSSFCell) cells.next(); | |
if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) | |
{ | |
System.out.print(cell.getStringCellValue()+" "); | |
} | |
else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) | |
{ | |
System.out.print(cell.getNumericCellValue()+" "); | |
} | |
else | |
{ | |
//U Can Handel Boolean, Formula, Errors | |
} | |
} | |
System.out.println(); | |
} | |
} | |
public static void writeXLSFile() throws IOException { | |
String excelFileName = "C:/Test.xls";//name of excel file | |
String sheetName = "Sheet1";//name of sheet | |
HSSFWorkbook wb = new HSSFWorkbook(); | |
HSSFSheet sheet = wb.createSheet(sheetName) ; | |
//iterating r number of rows | |
for (int r=0;r < 5; r++ ) | |
{ | |
HSSFRow row = sheet.createRow(r); | |
//iterating c number of columns | |
for (int c=0;c < 5; c++ ) | |
{ | |
HSSFCell cell = row.createCell(c); | |
cell.setCellValue("Cell "+r+" "+c); | |
} | |
} | |
FileOutputStream fileOut = new FileOutputStream(excelFileName); | |
//write this workbook to an Outputstream. | |
wb.write(fileOut); | |
fileOut.flush(); | |
fileOut.close(); | |
} | |
public static void readXLSXFile() throws IOException | |
{ | |
InputStream ExcelFileToRead = new FileInputStream("C:/Test.xlsx"); | |
XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); | |
XSSFWorkbook test = new XSSFWorkbook(); | |
XSSFSheet sheet = wb.getSheetAt(0); | |
XSSFRow row; | |
XSSFCell cell; | |
Iterator rows = sheet.rowIterator(); | |
while (rows.hasNext()) | |
{ | |
row=(XSSFRow) rows.next(); | |
Iterator cells = row.cellIterator(); | |
while (cells.hasNext()) | |
{ | |
cell=(XSSFCell) cells.next(); | |
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) | |
{ | |
System.out.print(cell.getStringCellValue()+" "); | |
} | |
else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) | |
{ | |
System.out.print(cell.getNumericCellValue()+" "); | |
} | |
else | |
{ | |
//U Can Handel Boolean, Formula, Errors | |
} | |
} | |
System.out.println(); | |
} | |
} | |
public static void writeXLSXFile() throws IOException { | |
String excelFileName = "C:/Test.xlsx";//name of excel file | |
String sheetName = "Sheet1";//name of sheet | |
XSSFWorkbook wb = new XSSFWorkbook(); | |
XSSFSheet sheet = wb.createSheet(sheetName) ; | |
//iterating r number of rows | |
for (int r=0;r < 5; r++ ) | |
{ | |
XSSFRow row = sheet.createRow(r); | |
//iterating c number of columns | |
for (int c=0;c < 5; c++ ) | |
{ | |
XSSFCell cell = row.createCell(c); | |
cell.setCellValue("Cell "+r+" "+c); | |
} | |
} | |
FileOutputStream fileOut = new FileOutputStream(excelFileName); | |
//write this workbook to an Outputstream. | |
wb.write(fileOut); | |
fileOut.flush(); | |
fileOut.close(); | |
} | |
public static void main(String[] args) throws IOException { | |
writeXLSFile(); | |
readXLSFile(); | |
writeXLSXFile(); | |
readXLSXFile(); | |
} | |
} |
This comment has been minimized.
This comment has been minimized.
bibekkumar005
commented
Aug 28, 2015
please write the dependency .....having trouble when including POI jars for hssf and xssf |
This comment has been minimized.
This comment has been minimized.
hermanwhyd
commented
Sep 8, 2015
@bibekkumar005 may be this is you needed:
|
This comment has been minimized.
This comment has been minimized.
vijiniL
commented
Dec 30, 2015
Thank you :) |
This comment has been minimized.
This comment has been minimized.
willykraisler
commented
Jan 24, 2016
Thank you man |
This comment has been minimized.
This comment has been minimized.
SantiMA10
commented
Feb 4, 2016
very helpful |
This comment has been minimized.
This comment has been minimized.
Sourav0512
commented
Mar 15, 2016
Type mismatch: cannot convert from Iterator to Descriptor.Iterator getting this error |
This comment has been minimized.
This comment has been minimized.
ghost
commented
Mar 17, 2016
+1 thank you !!! |
This comment has been minimized.
This comment has been minimized.
zhvaleuskaya
commented
Apr 18, 2016
thank you |
This comment has been minimized.
This comment has been minimized.
hendisantika
commented
Apr 28, 2016
Thank you very much. |
This comment has been minimized.
This comment has been minimized.
CodeMines
commented
May 2, 2016
Which version of poi you have used |
This comment has been minimized.
This comment has been minimized.
ShreeshaN
commented
May 5, 2016
Thanks man ! |
This comment has been minimized.
This comment has been minimized.
ouyang111
commented
May 20, 2016
thanks |
This comment has been minimized.
This comment has been minimized.
ouyang111
commented
May 20, 2016
very helpful |
This comment has been minimized.
This comment has been minimized.
jcgofredo
commented
May 25, 2016
thank you! |
This comment has been minimized.
This comment has been minimized.
pontiyaraja
commented
May 26, 2016
Thank you |
This comment has been minimized.
This comment has been minimized.
ShanikaEdiriweera
commented
May 28, 2016
.xlsx reader and writer is not working. |
This comment has been minimized.
This comment has been minimized.
adilearner
commented
Sep 7, 2016
It was helpful......do u have any code to read xls file using Apache POI Streaming (SXSSF) for Reading?? -Thanks |
This comment has been minimized.
This comment has been minimized.
robin2919
commented
Nov 16, 2016
Can you please provide the link for jar files |
This comment has been minimized.
This comment has been minimized.
jalvarada
commented
Nov 20, 2016
•
Does anyone why this error shows up when I run the file? Error: Could not find or load main class excel.Excel |
This comment has been minimized.
This comment has been minimized.
aakashsinghec90
commented
Apr 10, 2017
•
@ShanikaEdiriweera: Please download the dependency for XMLbeans too. |
This comment has been minimized.
This comment has been minimized.
tayorh27
commented
May 5, 2017
Please what is the full dependency sure has this compile 'com.mcxiaoke.volley:library:1.0.17'??? |
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
mvictorl
commented
May 5, 2017
Please, how read data if some cells are merged (by row)? |
This comment has been minimized.
This comment has been minimized.
sadev5264
commented
May 28, 2017
Thank you !! |
This comment has been minimized.
This comment has been minimized.
guptaankit95
commented
Jun 7, 2017
not able to pass ExcelFileToRead into the XSSFWorkbook. showing error (remove argument to match xssfworkbook) please help |
This comment has been minimized.
This comment has been minimized.
ranjithkumarrm
commented
Jul 5, 2017
Do we need MS Excel Application to be installed in local/server ,To run the code working ?? |
This comment has been minimized.
This comment has been minimized.
12342222
commented
Jul 20, 2017
•
Hi Sir/Madam xlsx(Excel) format file Read content issues that attaching file from Email and store into local drive, Please any help on this and that really appreciated.Below java code now we are using
// String saveDirectory = (String) resources.get(SystemRqstAppConstants.WebConstants.CUSTOMERITEMVENDORPO_PATH);
// my_xlsx_workbook.write(fileOutStream);
|
This comment has been minimized.
This comment has been minimized.
prs978
commented
Jul 25, 2017
unable to save as the xlsx file by using the Apache POI Objects and other java logic and please help on this that really great appreciation. Regards |
This comment has been minimized.
This comment has been minimized.
amisiuryk
commented
Aug 10, 2017
@prs978 To create xlsx file you have to use XSSF**** files instead of HSSF* |
This comment has been minimized.
This comment has been minimized.
ramutha
commented
Sep 19, 2017
Hi. I am facing this error while executing the above code. Cell 0 0 Cell 0 1 Cell 0 2 Cell 0 3 Cell 0 4 |
This comment has been minimized.
This comment has been minimized.
arvindbakaya
commented
Sep 19, 2017
I get the below error while running this code : |
This comment has been minimized.
This comment has been minimized.
arjunpatelcs13
commented
Oct 5, 2017
great Thanks |
This comment has been minimized.
This comment has been minimized.
jenniferzoe123
commented
Oct 17, 2017
I am loading a large 5000 row excell file with extention .xlsx, it takes more time maybe more than 10 mins, how to dignose this issue, i am using your above function readXLSXFile() , Plz help. |
This comment has been minimized.
This comment has been minimized.
vikassun
commented
Nov 8, 2017
Hi Madan, Please help me solving one issue i am facing while incorporating your code in one of my project!! --> Reading .xlsx file I am working on some client Project where it was required to have this functionality of reading .xlsx file where i tried incorporating this code but it cries with ERROR!! When i placed reading .xls code it is perfectly working. Note: a) I have used same POI jar in my independent dynamic web project and in client Project. ERROR Message I get I am using apache POI 3.17 lib.. My doubt : its a struts 1 Project , might be some where some jars are clashed Pls help |
This comment has been minimized.
This comment has been minimized.
chintamahesh
commented
Nov 22, 2017
Many Thanks |
This comment has been minimized.
This comment has been minimized.
callicoder
commented
Dec 27, 2017
•
This is great. But It's better to use a private void readExcelFile(filePath) {
Workbook workbook = WorkbookFactory.create(new File(filePath));
Sheet sheet = workbook.getSheetAt(0);
DataFormatter dataFormatter = new DataFormatter();
sheet.forEach(row -> {
row.forEach(cell -> {
String cellValue = dataFormatter.formatCellValue(cell);
System.out.print(cellValue + "\t");
});
System.out.println();
});
} The complete code can be found at The CalliCoder Blog. |
This comment has been minimized.
This comment has been minimized.
baofengyv
commented
Jan 31, 2018
Thank you! |
This comment has been minimized.
This comment has been minimized.
XxZhang2017
commented
Feb 3, 2018
I add the xmlbeas-2.60.jar. But I get this error. WARNING: Illegal reflective access by org.apache.poi.util.DocumentHelper (file:/Users/zhangxue/java_lib/poi-3.17/poi-ooxml-3.17.jar) to method com.sun.org.apache.xerces.internal.util.SecurityManager.setEntityExpansionLimit(int) |
This comment has been minimized.
This comment has been minimized.
SoumiaMokrane
commented
Feb 11, 2018
Slt;
|
This comment has been minimized.
This comment has been minimized.
SoumiaMokrane
commented
Feb 11, 2018
Thanks |
This comment has been minimized.
This comment has been minimized.
kkgouda
commented
Mar 8, 2018
Hi, |
This comment has been minimized.
This comment has been minimized.
pratikbutani
commented
Apr 4, 2018
•
Try it for Gradle Dependency in Android Studio:
|
This comment has been minimized.
This comment has been minimized.
ozgurgul
commented
Jun 1, 2018
Just in case you need further help... |
This comment has been minimized.
This comment has been minimized.
blake-edwards
commented
Aug 9, 2018
Awesome, thank you! |
This comment has been minimized.
This comment has been minimized.
gabykant
commented
Aug 30, 2018
•
This code has a problem and I am trying to solve. If your have a file with multiple lines (my case 10000) it throws an exception |
This comment has been minimized.
This comment has been minimized.
shilpaluthra
commented
Oct 29, 2018
How can we get value from readXLSFile class , that can be used as Input for another test case. |
This comment has been minimized.
She110ck commentedAug 13, 2015
thank you ;)