Questions tagged [apache-poi]

Apache POI is a Java library for reading and writing various Microsoft file formats, especially Office related ones. It supports OLE2 and OOXML based formats, such as XLS, XLSX, DOC, DOCX, PPT and PPTX as well as a few others.

Filter by
Sorted by
Tagged with
165 votes
24 answers

How can I read numeric strings in Excel cells as string (not numbers)?

I have excel file with such contents: A1: SomeString A2: 2 All fields are set to String format. When I read the file in java using POI, it tells that A2 is in numeric cell format. The problem is ...
joycollector's user avatar
  • 1,986
145 votes
14 answers

Cannot import XSSF in Apache POI

I am referencing the version 3.7 of the Apache POI and I am getting a "cannot be resolved" error when I do: import org.apache.poi.xssf.usermodel.XSSFWorkbook; Other import statements that reference ...
remjx's user avatar
  • 4,134
137 votes
12 answers

Apache POI Excel - how to configure columns to be expanded?

I am using Apache POI API to generate excel spreadsheet to output some data. The problem I am facing is when the spreadsheet is created and opened, columns are not expanded so that some long text ...
Meow's user avatar
  • 18.5k
129 votes
7 answers

How do I set cell value to Date and apply default Excel date format?

I've been using Apache POI for some time to read existing Excel 2003 files programmatically. Now I have a new requirement to create entire .xls files in-memory (still using Apache POI) and then write ...
Jim Tough's user avatar
  • 14.9k
96 votes
4 answers

What is the better API to Reading Excel sheets in java - JXL or Apache POI [closed]

Which of the 2 APIs is simpler to read/write/edit excel sheets ? Do these APIs not support CSV extensions ? Using JXL for file.xls and file.xlsx, I get an exception like:
Swagatika's user avatar
  • 3,376
95 votes
3 answers

Apache POI Locking Header Rows

Is anyone out there familiar with a way to lock a row in a spreadsheet created with Apache POI 3.7? By locking I mean that I want the title row for the columns to remain visible when the user is ...
user avatar
88 votes
10 answers

How to read Excel cell having Date with Apache POI?

I'm using Apache POI 3.6, I want to read an excel file which has a date like this 8/23/1991. switch (cell.getCellType()) { ... ... case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC ...
Venkat's user avatar
  • 2,604
80 votes
5 answers

Merging cells in Excel using Apache POI

Is there any other way to merge cells in Excel using Apache POI library? I was trying using the following, but its not working // selecting the region in Worksheet for merging data CellRangeAddress ...
androidDev's user avatar
  • 1,179
80 votes
14 answers

Cannot get a text value from a numeric cell “Poi”

I'm trying to consume data from a spreadsheet in Excel, but always of this error, already tried formatting the worksheet to text and number and still the error persists. I saw a person using it ...
Paulo Roberto's user avatar
73 votes
6 answers

Java POI : How to read Excel cell value and not the formula computing it?

I am using Apache POI API to getting values from an Excel file. Everything is working great except with cells containing formulas. In fact, the cell.getStringCellValue() is returning the formula used ...
Aminoss's user avatar
  • 731
68 votes
5 answers

Setting Column width in Apache POI

I am writing a tool in Java using Apache POI API to convert an XML to MS Excel. In my XML input, I receive the column width in points. But the Apache POI API has a slightly queer logic for setting ...
Arun's user avatar
  • 689
65 votes
8 answers

Alternative to deprecated getCellType

I'm reading an excel-file (file extension xlsx) using org.apache.poi 3.15. This is my code: try (FileInputStream fileInputStream = new FileInputStream(file); XSSFWorkbook workbook = new ...
user1766169's user avatar
  • 1,932
64 votes
6 answers

How to apply bold text style for an entire row using Apache POI?

How to make an entire excel row cells bold text using Apache POI? E.g: Column headings should be in bold. Instead of applying style for each and every cell of heading row, how can I apply some style ...
Krishnamachary's user avatar
60 votes
2 answers

How can I convert POI HSSFWorkbook to bytes?

Calling Simple toBytes() does produce the bytes, but Excel throws Warning. Lost Document information Googling around gave me this link and looking at Javadocs for worksheet and POI HOW-TO say ...
Shahzeb's user avatar
  • 4,745
58 votes
8 answers

Apache POI error loading XSSFWorkbook class

I'm trying to write a program that works with Excel docs, but the HSSF format is too small for my requirements. I'm attempting to move to XSSF, but I keep getting errors when trying to use it. I ...
Cameron Zach's user avatar
58 votes
8 answers

How to insert a row between two rows in an existing excel with HSSF (Apache POI)

Somehow I manage to create new rows between two rows in an existing excel file. The problem is, some of the formatting were not include along the shifting of the rows. One of this, is the row that ...
ace's user avatar
  • 6,775
57 votes
8 answers

How to load a large xlsx file with Apache POI?

I have a large .xlsx file (141 MB, containing 293413 lines with 62 columns each) I need to perform some operations within. I am having problems with loading this file (OutOfMemoryError), as POI has ...
CosmicGiant's user avatar
  • 6,275
56 votes
7 answers

How to get row count in an Excel file using POI library?

Guys I'm currently using the POI 3.9 library to work with excel files. I know of the getLastRowNum() function, which returns a number of rows in an Excel file. The only problem is getLastRowNum() ...
Ahmad's user avatar
  • 12.9k
55 votes
11 answers

Replacing a text in Apache POI XWPF

I just found Apache POI library very useful for editing Word files using Java. Specifically, I want to edit a DOCX file using Apache POI's XWPF classes. I found no proper method / documentation ...
Gagan93's user avatar
  • 1,826
55 votes
5 answers

Setting filter on headers of an Excel sheet via POI

I generate a sheet, pretty bog standard headers and columns of data. I want to turn on the "Filter" function for the sheet, so the user can easily sort and filter the data. Can I so this using POI?
Anders Johansen's user avatar
52 votes
8 answers

Add borders to cells in POI generated Excel File

I am using POI to generate an Excel File. I need to add borders to specific cells in the worksheet. How can I accomplish this?
jzd's user avatar
  • 23.5k
52 votes
12 answers

How to check if an excel cell is empty using Apache POI?

I am taking input from an excel sheet using Poi.jar and wanted to know how to check if a cell is empty or not. Right now I m using the below code. cell = myRow.getCell(3); if (cell != null) { ...
user1312312's user avatar
51 votes
3 answers

Setting foreground color for HSSFCellStyle is always coming out black

I am using POI to create an Excel spreadsheet in Java. I have the following code used for creating a header row: HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Report"); // ...
Ascalonian's user avatar
  • 14.4k
49 votes
3 answers

get number of columns of a particular row in given excel using Java

I want the number of columns of a particular row in excel. How is that possible? I used POI API but I could get only columns count to 7 . try { fileInputStream = new ...
muthukumar's user avatar
  • 2,233
48 votes
6 answers

Apache POI XSSFColor from hex code

I want to set the foreground color of a cell to a given color in hex code. For example, when I try to set it to red: style.setFillForegroundColor(new XSSFColor(Color.decode("#FF0000")).getIndexed()); ...
Neets's user avatar
  • 4,094
47 votes
7 answers

Writing a large resultset to an Excel file using POI

This is sort of inline w/ Writing a large ResultSet to a File but the file in question is an Excel file. I'm using the Apache POI library to write an Excel file with a large data set retrieved from a ...
Ranga's user avatar
  • 618
46 votes
12 answers

How to determine empty row?

How I can determine empty rows in .xls documents using Apache POI?
WelcomeTo's user avatar
  • 19.9k
46 votes
2 answers

Display percentage values in Excel using POI API

I need to display a value in an excel cell formatted like a percentage, e.g. like 12.3%. By default the value is displayed as Text, but I need to display it as a number. What is the appropriate ...
nagireddy's user avatar
  • 491
46 votes
11 answers

Auto size height for rows in Apache POI

I am inputting values into a spreadsheet using Apache POI. These values have newlines, and I was able to use this code successfully: CellStyle style = cell.getCellStyle() style.setWrapText(true) cell....
user1007895's user avatar
  • 3,925
45 votes
7 answers

How to set formulas in cells using Apache POI?

I am currently using Apache POI for Java to set formulas in cells. But after I run the program and open the Excel file that I created and processed, the cells with the formula include the formula as ...
vamsi's user avatar
  • 461
45 votes
9 answers

How to get an Excel Blank Cell Value in Apache POI?

I have a huge excel file with tons of columns which looks like this :- Column1 Column2 Column3 Column4 Column5 abc def ghi mno pqr ...... This is the code ...
user607429's user avatar
41 votes
2 answers

Using Apache POI HSSF, how can I refresh all formula cells at once?

I am filling cells of an Excel file using Apache POI, and there are a lot of formula cells in the document. However, their values are not refreshed when I open the document in Excel. It's my ...
zneak's user avatar
  • 135k
40 votes
9 answers

Processing large xlsx file

I need to auto-fit all rows in large (30k+ rows) xlsx file. The following code via apache poi works on small files, but goes out with OutOfMemoryError on large ones: Workbook workbook = ...
miah's user avatar
  • 8,200
39 votes
6 answers

POI setting Cell Background to a Custom Color

I want to set custom color to a cell's background. I use HSSFWorkbook (can't use anything else). HSSFPalette palette = aWorkBook.getCustomPalette(); Color col = new Color(backgroundColor)...
kenny's user avatar
  • 2,000
36 votes
15 answers

Required maven dependencies for Apache POI to work

I want to use Apache POI library to parse excel files (old versions and newer versions of excel). So I was wondering what jars do i need to include from the Apache POI because in following link: http:...
Mahmoud Saleh's user avatar
36 votes
3 answers

Multiline text in Excel cells

I'm trying to write multiline text to excel cells. cell.setCellValue("line1 \n line2"); But when I open the document, I see only one line until I double-click it for editing, then it becomes two-...
Timofei Davydik's user avatar
36 votes
2 answers

How can I create a simple docx file with Apache POI?

I'm searching for a simple example code or a complete tutorial how to create a docx file with Apache POI and its underlying openxml4j. I tried the following code (with a lot of help from the Content ...
guerda's user avatar
  • 23.4k
35 votes
4 answers

Basic Excel currency format with Apache POI

I'm able to get cells to format as Dates, but I've been unable to get cells to format as currency... Anyone have an example of how to create a style to get this to work? My code below show the styles ...
Dave K's user avatar
  • 1,674
35 votes
3 answers

is it possible to change sheet name with apache poi MS excel java android

is there any way to update existing sheet name of MS Excel file knowing that I am using apache poi in my android app I can create a sheet with my custom name HSSFSheet sheet = workbook....
funfordevelopping's user avatar
35 votes
10 answers

How to Cache InputStream for Multiple Use

I have an InputStream of a file and i use apache poi components to read from it like this: POIFSFileSystem fileSystem = new POIFSFileSystem(inputStream); The problem is that i need to use the same ...
Azder's user avatar
  • 4,698
35 votes
5 answers

Damaged file handling

I would like to know if anyone has any advice on handling damaged files with Apache POI I am trying to open a file and am receiving this message: Exception in thread "main" org.apache.poi.hssf....
Farmer Joe's user avatar
  • 6,030
34 votes
2 answers

How to insert a linebreak as the data of a cell using Apache POI?

I use Apache POI 3.16 to create an Excel file. I want to set the data inside a particular cell to have a linebreak : rowConsommationEtRealisation.createCell(0).setCellValue("Consommation (crédits)\r\...
pheromix's user avatar
  • 18.3k
33 votes
10 answers

Converting docx into pdf in java

I am trying to convert a docx file which contains table and images into a pdf format file. I have been searching everywhere but did not get proper solution, request to give proper and correct ...
user avatar
33 votes
4 answers

How to speed up autosizing columns in apache POI?

I use the following code in order to autosize columns in my spreadsheet: for (int i = 0; i < columns.size(); i++) { sheet.autoSizeColumn(i, true); sheet.setColumnWidth(i, sheet....
antken's user avatar
  • 959
32 votes
5 answers

Reading date values from excel cell using POI HSSF API

I'm using POI HSSF API for my excel manipulations in Java. I've a date value "8/1/2009" in one of my excel cell and while I try to read this value using HSSF API, it detects the cell type as Numeric ...
Veera's user avatar
  • 32.6k
32 votes
10 answers

Apache POI autoSizeColumn Resizes Incorrectly

I'm using Apache POI in java to create an excel file. I fill in the data then try to autosize each column, however the sizes are always wrong (and I think consistent). The first two rows are always(?) ...
Jaws212's user avatar
  • 750
31 votes
1 answer

How to change font color of particular cell apache poi 3.9

I can change foreground color with the following code in apache POI. Now I want to change font color of a single cell. CellStyle style = wb.createCellStyle(); style.setFillForegroundColor(...
H4SN's user avatar
  • 1,482
31 votes
1 answer

Can't Set Fill Color Apache POI Excel Workbook

I have scanned this forum over and over and tried every method mentioned on here and still can't get Apache POI to change to fill background color of my excel document. Here is my code: errorOccured ...
Richie Episcopo's user avatar
31 votes
9 answers

Merge and align center cell using apache poi

I want to export data to excel using Apache poi. Now the problem that I am facing is that I am unable to merge rows and align them in the center. Code for export data is: List<LinkedHashMap<...
shrey's user avatar
  • 833
30 votes
3 answers

Java POI the supplied data appears to be in the Office 2007+ XML

I am getting this error: 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 ...
Marco Dinatsoli's user avatar

2 3 4 5