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.
9,744
questions
165
votes
24
answers
332k
views
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 ...
- 1,986
145
votes
14
answers
248k
views
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 ...
- 4,134
137
votes
12
answers
268k
views
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 ...
- 18.5k
129
votes
7
answers
250k
views
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 ...
- 14.9k
96
votes
4
answers
134k
views
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:
jxl.read.biff....
- 3,376
95
votes
3
answers
54k
views
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 ...
user1795755
88
votes
10
answers
202k
views
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 ...
- 2,604
80
votes
5
answers
193k
views
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 ...
- 1,179
80
votes
14
answers
215k
views
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 ...
- 1,498
73
votes
6
answers
131k
views
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 ...
- 731
68
votes
5
answers
147k
views
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 ...
- 689
65
votes
8
answers
147k
views
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 ...
- 1,932
64
votes
6
answers
203k
views
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 ...
- 641
60
votes
2
answers
88k
views
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 ...
- 4,745
58
votes
8
answers
136k
views
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 ...
- 581
58
votes
8
answers
123k
views
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 ...
- 6,775
57
votes
8
answers
129k
views
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 ...
- 6,275
56
votes
7
answers
177k
views
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() ...
- 12.9k
55
votes
11
answers
95k
views
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 ...
- 1,826
55
votes
5
answers
66k
views
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?
- 10.2k
52
votes
8
answers
136k
views
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?
- 23.5k
52
votes
12
answers
163k
views
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) {
...
- 605
51
votes
3
answers
67k
views
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");
// ...
- 14.4k
49
votes
3
answers
140k
views
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 ...
- 2,233
48
votes
6
answers
134k
views
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());
...
- 4,094
47
votes
7
answers
155k
views
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 ...
- 618
46
votes
12
answers
107k
views
How to determine empty row?
How I can determine empty rows in .xls documents using Apache POI?
- 19.9k
46
votes
2
answers
47k
views
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 ...
- 491
46
votes
11
answers
117k
views
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....
- 3,925
45
votes
7
answers
110k
views
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 ...
- 461
45
votes
9
answers
112k
views
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 ...
- 451
41
votes
2
answers
31k
views
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 ...
- 135k
40
votes
9
answers
102k
views
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 = ...
- 8,200
39
votes
6
answers
138k
views
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)...
- 2,000
36
votes
15
answers
179k
views
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:...
- 33.4k
36
votes
3
answers
45k
views
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-...
- 7,244
36
votes
2
answers
36k
views
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 ...
- 23.4k
35
votes
4
answers
68k
views
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 ...
- 1,674
35
votes
3
answers
46k
views
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....
35
votes
10
answers
48k
views
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 ...
- 4,698
35
votes
5
answers
3k
views
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....
- 6,030
34
votes
2
answers
38k
views
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\...
- 18.3k
33
votes
10
answers
97k
views
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 ...
user1999397
33
votes
4
answers
37k
views
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....
- 959
32
votes
5
answers
79k
views
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 ...
- 32.6k
32
votes
10
answers
54k
views
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(?) ...
- 750
31
votes
1
answer
96k
views
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(...
- 1,482
31
votes
1
answer
36k
views
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 ...
- 383
31
votes
9
answers
114k
views
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<...
- 833
30
votes
3
answers
129k
views
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 ...
- 10.4k