-
Star
(163)
You must be signed in to star a gist -
Fork
(81)
You must be signed in to fork a gist
-
-
Save madan712/3912272 to your computer and use it in GitHub Desktop.
| 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(); | |
| } | |
| } |
@bibekkumar005 may be this is you needed:
<!-- POI : Excel library --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>${poi.version}</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>${poi.version}</version> </dependency>please write the dependency .....having trouble when including POI jars for hssf and xssf
how to add this in android studio Dependency
How to convert .xls to .xlsx and vice versa?
Try it for Gradle Dependency in Android Studio:
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '3.9'
Thank you.
After changing the gradle version everything worked fine.
Any luck with android studio? .xls works fine but .xlsx giving me a lots to error.
Any one with working solution for .xlsx generation?
Dependency
implementation 'org.apache.poi:poi:3.9'
implementation 'org.apache.poi:poi-ooxml:3.9'
implementation 'org.apache.xmlbeans:xmlbeans:2.3.0'
Try it for Gradle Dependency in Android Studio:
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '3.9'
hello I implemnet latest version of but it' throw an error while I run app
Here is the Error
#1.Failed to transform bcprov-jdk15on-1.68.jar
#2.Unsupported class file major version 59
Any luck with android studio? .xls works fine but .xlsx giving me a lots to error.
Any one with working solution for .xlsx generation?Dependency
implementation 'org.apache.poi:poi:3.9'
implementation 'org.apache.poi:poi-ooxml:3.9'
implementation 'org.apache.xmlbeans:xmlbeans:2.3.0'
Try it for Gradle Dependency in Android Studio:
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '3.9'
hello I implemnet latest version of but it' throw an error while I run app
Here is the Error
#1.Failed to transform bcprov-jdk15on-1.68.jar
#2.Unsupported class file major version 59
anybody here please share me the right dependancy to implemeant in anroid studio
when the line of the file is big enough，the code running below is very slow！
FileOutputStream outputStream = new FileOutputStream(outFile);
xssfWorkbook.write(outputStream);
outputStream.flush();
outputStream.close();
Hi
My code is running fine on local but on server it gives the given error, I am using apache poi 4.1.2, unable to debug and reproduce this
2021-07-07T14:18:52.477042302ZException in thread "pool-48-thread-1" java.lang.InternalError: java.lang.reflect.InvocationTargetException at java.desktop/sun.font.FontManagerFactory$1.run(FontManagerFactory.java:86) at java.base/java.security.AccessController.doPrivileged(Native Method) at java.desktop/sun.font.FontManagerFactory.getInstance(FontManagerFactory.java:74) at java.desktop/java.awt.Font.getFont2D(Font.java:497) at java.desktop/java.awt.Font.canDisplayUpTo(Font.java:2250) at java.desktop/java.awt.font.TextLayout.singleFont(TextLayout.java:469) at java.desktop/java.awt.font.TextLayout.(TextLayout.java:530) at org.apache.poi.ss.util.SheetUtil.getDefaultCharWidth(SheetUtil.java:273) at org.apache.poi.ss.util.SheetUtil.getColumnWidth(SheetUtil.java:248) at org.apache.poi.ss.util.SheetUtil.getColumnWidth(SheetUtil.java:233) at org.apache.poi.xssf.usermodel.XSSFSheet.autoSizeColumn(XSSFSheet.java:555) at org.apache.poi.xssf.usermodel.XSSFSheet.autoSizeColumn(XSSFSheet.java:537) at com.haulerhero.crm.data.service.ExportService.createCell(ExportService.java:118) at com.haulerhero.crm.data.service.ExportService.writeHeaderLine(ExportService.java:105) at com.haulerhero.crm.data.service.ExportService$1.run(ExportService.java:49) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:829) Caused by: java.lang.reflect.InvocationTargetException at java.base/jdk.internal.reflect.GeneratedConstructorAccessor151.newInstance(Unknown Source) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490) at java.desktop/sun.font.FontManagerFactory$1.run(FontManagerFactory.java:84) ... 17 more Caused by: java.lang.NullPointerException at java.desktop/sun.awt.FontConfiguration.getVersion(FontConfiguration.java:1262) at java.desktop/sun.awt.FontConfiguration.readFontConfigFile(FontConfiguration.java:225) at java.desktop/sun.awt.FontConfiguration.init(FontConfiguration.java:107) at java.desktop/sun.awt.X11FontManager.createFontConfiguration(X11FontManager.java:719) at java.desktop/sun.font.SunFontManager$2.run(SunFontManager.java:379) at java.base/java.security.AccessController.doPrivileged(Native Method) at java.desktop/sun.font.SunFontManager.(SunFontManager.java:324) at java.desktop/sun.awt.FcFontManager.(FcFontManager.java:35) at java.desktop/sun.awt.X11FontManager.(X11FontManager.java:56) ... 21 more
If flush() used right after write(f) no data is been updated to the file. Why so?
I am using this apache POI for reading excel sheet data. one of the column has numbers as string and need to remove the space after the string.But trim(), replaceAll() is not working for the data coming from the excel sheet.
Example : currentcell.getStringCellValue().trim() is also not working.It is returning the same string with the white space. Even tried with the replaceAll().
Can anybody please help me with this ?
Thank you in advance.