Unfortunately there is only the function setColumnWidth(int columnIndex, int width) from class Sheet; in which width is a number of characters in the standard font (first font in the workbook) if your fonts are changing you cannot use it. There is explained how to calculate the width in function of a font size. The formula … Read more
For formula cells, excel stores two things. One is the Formula itself, the other is the “cached” value (the last value that the forumla was evaluated as) If you want to get the last cached value (which may no longer be correct, but as long as Excel saved the file and you haven’t changed it … Read more
You can use sheet.addMergedRegion(rowFrom,rowTo,colFrom,colTo); example sheet.addMergedRegion(new CellRangeAddress(1,1,1,4)); will merge from B2 to E2. Remember it is zero based indexing (ex. POI version 3.12). for detail refer BusyDeveloper’s Guide
Formatter will work fine in this case. import org.apache.poi.ss.usermodel.DataFormatter; FileInputStream fis = new FileInputStream(workbookName); Workbook workbook = WorkbookFactory.create(fis); Sheet sheet = workbook.getSheet(sheetName); DataFormatter formatter = new DataFormatter(); String val = formatter.formatCellValue(sheet.getRow(row).getCell(col)); list.add(val); //Adding value to list
NOTE: HSSFDateUtil is deprecated If you know which cell i.e. column position say 0 in each row is going to be a date, you can go for row.getCell(0).getDateCellValue() directly. http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html#getDateCellValue() UPDATE: Here is an example – you can apply this in your switch case code above. I am checking and printing the Numeric as well … Read more
In case you need to Freeze any particular row anywhere in the sheet you can use (Within org.apache.poi.ss.usermodel.Sheet) (Available in POI 3.7 as well) Sheet.createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow) In your case if you want to freeze just your first x rows then the int leftmostColumn, int topRow section will get removed … Read more
I have used both JXL (now “JExcel”) and Apache POI. At first I used JXL, but now I use Apache POI. First, here are the things where both APIs have the same end functionality: Both are free Cell styling: alignment, backgrounds (colors and patterns), borders (types and colors), font support (font names, colors, size, bold, … Read more
http://poi.apache.org/spreadsheet/quick-guide.html#CreateDateCells CellStyle cellStyle = wb.createCellStyle(); CreationHelper createHelper = wb.getCreationHelper(); cellStyle.setDataFormat( createHelper.createDataFormat().getFormat(“m/d/yy h:mm”)); cell = row.createCell(1); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle);
After you have added all your data to the sheet, you can call autoSizeColumn(int column) on your sheet to autofit the columns to the proper size Here is a link to the API. See this post for more reference Problem in fitting the excel cell size to the size of the content when using apache … Read more
For OOXML to work you need the POI-OOXML jar which is separately packaged from the POI jar. Download the POI-OOXML jar from the following location – http://repo1.maven.org/maven2/org/apache/poi/poi-ooxml/3.11/poi-ooxml-3.11.jar For Maven2 add the below dependency – <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.11</version> </dependency>