Setting up borders in the style used in the cells will accomplish this. Example: style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
Save the first and last cell from filter area, and execute: sheet.setAutoFilter(new CellRangeAddress(firstCell.getRow(), lastCell.getRow(), firstCell.getCol(), lastCell.getCol())); For example, from the below sheet. >x (x, y) 0123456 0|–hhh–| h = header 1|–+++–| + = values 2|–+++–| – = empty fields 3|–+++–| 4|——-| first cell will be the header above the first + (2,1) cell. The last … Read more
Try Sheet.getPhysicalNumberOfRows()
The method you need is XWPFRun.setText(String). Simply work your way through the file until you find the XWPFRun of interest, work out what you want the new text to be, and replace it. (A run is a sequence of text with the same formatting) You should be able to do something like: XWPFDocument doc = … Read more
I was in a similar situation with a webserver environment. The typical size of the uploads were ~150k rows and it wouldn’t have been good to consume a ton of memory from a single request. The Apache POI Streaming API works well for this, but it requires a total redesign of your read logic. I … Read more
As that mailing list post said Invoking HSSFWorkbook.getBytes() does not return all of the data necessary to re- construct a complete Excel file. You can use the write method with a ByteArrayOutputStream to get at the byte array. ByteArrayOutputStream bos = new ByteArrayOutputStream(); try { workbook.write(bos); } finally { bos.close(); } byte[] bytes = bos.toByteArray(); … Read more
Helper function to copy rows shamelessly adapted from here import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.util.CellRangeAddress; import java.io.FileInputStream; import java.io.FileOutputStream; public class RowCopy { public static void main(String[] args) throws Exception{ HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(“c:/input.xls”)); HSSFSheet sheet = workbook.getSheet(“Sheet1”); copyRow(workbook, sheet, 0, 1); FileOutputStream out = new FileOutputStream(“c:/output.xls”); workbook.write(out); out.close(); } private static void … Read more
Add commons-collections4-x.x.jar file in your build path and try it again. It will work. You can download it from https://mvnrepository.com/artifact/org.apache.commons/commons-collections4/4.0
This should work fine. Workbook wb = new XSSFWorkbook(“myWorkbook.xlsx”); Row row=sheet.getRow(0); CellStyle style=null; XSSFFont defaultFont= wb.createFont(); defaultFont.setFontHeightInPoints((short)10); defaultFont.setFontName(“Arial”); defaultFont.setColor(IndexedColors.BLACK.getIndex()); defaultFont.setBold(false); defaultFont.setItalic(false); XSSFFont font= wb.createFont(); font.setFontHeightInPoints((short)10); font.setFontName(“Arial”); font.setColor(IndexedColors.WHITE.getIndex()); font.setBold(true); font.setItalic(false); style=row.getRowStyle(); style.setFillBackgroundColor(IndexedColors.DARK_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFont(font); If you do not create defaultFont all your workbook will be using the other one as default.
The accepted answer shows the reason for the deprecation but misses to name the alternative: CellType getCellTypeEnum() where the CellType is the enum decribing the type of the cell. The plan is to rename getCellTypeEnum() back to getCellType() in POI 4.0.