1. I have excel file with such contents: 

   * A1: SomeString

   * A2: 2

   All fields are set to String format.

2. When I read the file in java using POI, it tells that A2 is in numeric cell format.
3. The problem is that the value in A2 can be 2 or 2.0 (and I want to be able to distinguish them) so I can&#39;t just use `.toString()`.

What can I do to read the value as string?


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

I am referencing the version 3.7 of the Apache POI and I am getting a &quot;cannot be resolved&quot; error when I do:

    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

Other import statements that reference POI *DO NOT* give me errors, such as:

    import org.apache.poi.ss.usermodel.*;

Any ideas??

Cannot import XSSF in Apache POI

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 like Date formatted text is not showing up on first glance.

I could just double click the column border in excel to expand or drag the border to adjust the column width but there could be 20+ columns and there is no way I want to do that manually every time I open the spreadsheet :(

I found out (though could be wrong method) `groupRow()` and `setColumnGroupCollapsed()` might be able to do the trick but no luck. Maybe I&#39;m using it in wrong way.

**Sample Code snippet**

            Workbook wb = new HSSFWorkbook();
            CreationHelper createHelper = wb.getCreationHelper();
            //create sheet
            Sheet sheet = wb.createSheet(&quot;masatoSheet&quot;);
      
            //not really working yet.... :(
            //set group for expand/collapse
            //sheet.groupRow(0, 10); //just random fromRow toRow argument values...
            //sheet.setColumnGroupCollapsed(0, true);
    
            //create row
            Row row = sheet.createRow((short)0);
            //put a cell in the row and store long text data
            row.createCell(0).setCellValue(&quot;Loooooooong text not to show up first&quot;);

**When this spreadsheet is created, the &quot;Looooooong text not to show up first&quot; string is in the cell but since the column is not expanded only &quot;Loooooooo&quot; is showing up.**

How can I configure it so that when I open my spreadsheet, the column is already expanded???

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

I&#39;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 them to a file at the end. The only problem standing in my way is the handling of cells with dates.

Consider the following code:

    Date myDate = new Date();
    HSSFCell myCell;
    // code that assigns a cell from an HSSFSheet to &#39;myCell&#39; would go here...
    myCell.setCellValue(myDate);

When I write the workbook containing this cell out to a file and open it with Excel, the cell is displayed as a number. Yes, I do realize that Excel stores its &#39;dates&#39; as the number of days since January 1 1900 and that is what the number in the cell represents.

**QUESTION: What API calls can I use in POI to tell it that I want a default date format applied to my date cell?**

Ideally I want the spreadsheet cell to be displayed with the same default date format that Excel would have assigned it if a user had manually opened the spreadsheet in Excel and typed in a cell value that Excel recognized as being a date.

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

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: 

&lt;!-- language: lang-none --&gt;

    jxl.read.biff.BiffException: Unable to recognize OLE stream
    	at jxl.read.biff.CompoundFile.&lt;init&gt;(CompoundFile.java:116)
    	at jxl.read.biff.File.&lt;init&gt;(File.java:127)
    	at jxl.Workbook.getWorkbook(Workbook.java:268)
    	at core.ReadXLSheet.contentReading(ReadXLSheet.java:46)
    	at core.ReadXLSheet.init(ReadXLSheet.java:22)
    	at core.ReadXLSheet.main(ReadXLSheet.java:72)

Both for .xls and .xlsx extensions.
Java Version I am using is : JDK1.6

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

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 scrolling through the rows. My created spreadsheet will have 500 rows and it would be beneficial if the column’s names were always visible.

Apache POI Locking Header Rows

I&#39;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 = &quot;NUMERIC value=&quot; + cell.getNumericCellValue();
         break;
    
       ...
    
     }

But it takes the numeric value type and returns the value like this `33473.0`.

I&#39;ve tried to use Numeric Cell Type although with no luck.

     
    dbltemp=row.getCell(c, Row.CREATE_NULL_AS_BLANK).getNumericCellValue();

    if (c == 6 || c == 9) {
        strTemp= new String(dbltemp.toString().trim());
    
        long tempDate = Long.parseLong(strTemp);
        Date date = new Date(tempDate);
    
        strVal = date.toString();
    }
    

How can I fix my problem? 



How to read Excel cell having Date with 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 region = CellRangeAddress.valueOf(&quot;A&quot; + rowNo + &quot;:D&quot;
				+ rowNo);

    // merging the region
    sheet1.addMergedRegion(region);



Merging cells in Excel using Apache POI

I&#39;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 resolved `cell.setCellType ( Cell.CELL_TYPE_STRING ) ;` but I do not know where I fit this passage in my code.

    WebElement searchbox = driver.findElement(By.name(&quot;j_username&quot;));
    WebElement searchbox2 = driver.findElement(By.name(&quot;j_password&quot;));         

    try {
        FileInputStream file = new FileInputStream(new File(&quot;C:\\paulo.xls&quot;)); 
        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheetAt(0);

        for (int i=1; i &lt;= sheet.getLastRowNum(); i++){
            String j_username = sheet.getRow(i).getCell(0).getStringCellValue();
            String j_password = sheet.getRow(i).getCell(0).getStringCellValue();

            searchbox.sendKeys(j_username);
            searchbox2.sendKeys(j_password);
            searchbox.submit();  
     
            driver.manage().timeouts().implicitlyWait(10000, TimeUnit.MILLISECONDS);
        }

        workbook.close();
        file.close();
    } catch (FileNotFoundException fnfe) {
        fnfe.printStackTrace();
    } catch (IOException ioe) {
        ioe.printStackTrace();

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

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 in the cell and not the value of the cell. 

I tried to use `evaluateFormulaCell()` method but it&#39;s not working because I am using GETPIVOTDATA Excel formula and this formula is not implemented in the API:

    Exception in thread &quot;main&quot; org.apache.poi.ss.formula.eval.NotImplementedException: Error evaluating cell Landscape!K11
    	at org.apache.poi.ss.formula.WorkbookEvaluator.addExceptionInfo(WorkbookEvaluator.java:321)
    	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
    	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:221)
    	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCellValue(HSSFFormulaEvaluator.java:320)
    	at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateFormulaCell(HSSFFormulaEvaluator.java:213)
    	at fromExcelToJava.ExcelSheetReader.unAutreTest(ExcelSheetReader.java:193)
    	at fromExcelToJava.ExcelSheetReader.main(ExcelSheetReader.java:224)
    Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: GETPIVOTDATA
    	at org.apache.poi.hssf.record.formula.functions.NotImplementedFunction.evaluate(NotImplementedFunction.java:42)


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

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 column width based on font size etc. (refer [API docs](http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#setColumnWidth%28int,%20int%29))

Is there a formula for converting points to the width as expected by Excel? Has anyone done this before?

There is a `setRowHeightInPoints()` method though :( but none for column.

P.S.: The input XML is in ExcelML format which I have to convert to MS Excel.

Setting Column width in Apache POI

I&#39;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 XSSFWorkbook(file)) {
        XSSFSheet sheet = workbook.getSheetAt(0);
        Iterator&lt;Row&gt; rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
    
            Iterator&lt;Cell&gt; cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + &quot;(Integer)\t&quot;);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + &quot;(String)\t&quot;);
                        break;
                }
            }
            System.out.println(&quot;&quot;);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

I get a warning that `cell.getCellType()` is deprecated. Can anyone tell me the alternative?

Alternative to deprecated getCellType

I&#39;m trying to write a program that works with Excel docs, but the HSSF format is too small for my requirements. I&#39;m attempting to move to XSSF, but I keep getting errors when trying to use it.

I managed to solve the first two by adding xmlbeans-2.3.0.jar and dom4j-1.6.jar to my program, but now this error is coming up, which doesn&#39;t seem to be resolved by adding the Apache commons jar available on the Apache website.

The error is as follows:

    Exception in thread &quot;main&quot; java.lang.NoClassDefFoundError: org/apache/commons/collections4/ListValuedMap
    	at hot.memes.ExcelCreator.main(ExcelCreator.java:66)
    Caused by: java.lang.ClassNotFoundException: org.apache.commons.collections4.ListValuedMap
    	at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
    	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    	... 1 more


Apache POI error loading XSSFWorkbook class

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 are hide are not relatively go along during the shift. What I mean is(ex.), rows from 20 to 30 is hidden, but when a create new rows the formating still there. The hidden rows must also move during the insertion/creation of new rows, it should be 21 to 31. 

Another thing is, the other object in the sheet that are not in the cell. Like the text box are not move along after the new row is created. Its like the position of these object are fixed. But I want it to move, the same thing as I insert a new row or paste row in excel. If there is a function of inserting a new row, please let me know.

This what I have right now, just a snippet from my code.

    HSSFWorkbook wb = new HSSFWorkbook(template); //template is the source of file
    HSSFSheet sheet = wb.getSheet(&quot;SAMPLE&quot;);
    HSSFRow newRow;
    HSSFCell cellData;

    int createNewRowAt = 9; //Add the new row between row 9 and 10
    
    sheet.shiftRows(createNewRowAt, sheet.getLastRowNum(), 1, true, false);
    newRow = sheet.createRow(createNewRowAt);
    newRow = sheet.getRow(createNewRowAt);

If copy and paste of rows is possible that would be big help. But I already ask it here and can&#39;t find a solution. So I decided to create a row as an interim solution. I&#39;m done with it but having a problem like this. 

Any help will be much appreciated. Thanks!

How to insert a row between two rows in an existing excel with HSSF (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 a large memory footprint on XSSF (xlsx) workbooks.

[This SO question][1] is similar, and the solution presented is to increase the VM&#39;s allocated/maximum memory.

It seems to work for that kind of file-size (9MB), but for me, it just simply doesn&#39;t work even if a allocate all available system memory. (Well, it&#39;s no surprise considering the file is over 15 times larger)

I&#39;d like to know if there is any way to load the workbook in a way it won&#39;t consume all the memory, and yet, without doing the processing based (going into) the XSSF&#39;s underlying XML. (In other words, maintaining a puritan POI solution)

If there isn&#39;t tough, you are welcome to say it (&quot;There isn&#39;t.&quot;) and point me the ways to a &quot;XML&quot; solution.

  [1]: https://stackoverflow.com/questions/6069847/java-lang-outofmemoryerror-java-heap-space-while-reading-excel-with-apache-poi

How to load a large xlsx file with Apache POI?

Guys I&#39;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()` returns a number with the count starting from 0.

So if an Excel file uses the first 3 rows, `getLastRowNum()` returns 2.
If an Excel file has just 1 row, `getLastRowNum()` returns 0.

The problem occurs when the Excel file is completely empty. `getLastRowNum()` still returns 0, so I cannot determine if the Excel file has 1 row of data, or if its empty.

So how can I detect if an Excel file is empty or not ?

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

Calling Simple [`toBytes()`][1] does produce the bytes but exel throws Warning.

&gt; Lost Document information


Googling around gave me this [link][2] and looking at [Javadocs][3] for worksheet and POI [HOW-TO][4] say similar things . Basically I can not get `Bytes` without loosing some information and should use the `write` method instead. 

While write does work fine I really need to send the bytes over . Is there any way I can do that ? That is get the bytes with out getting any warning .


  [1]: http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html#getBytes%28%29
  [2]: http://mail-archives.apache.org/mod_mbox/poi-user/200610.mbox/%3Cloom.20061017T235349-739@post.gmane.org%3E
  [3]: http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html#getBytes%28%29
  [4]: http://poi.apache.org/hpsf/how-to.html

How can I convert POI HSSFWorkbook to bytes?

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 to an entire row?

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

I generate a sheet, pretty bog standard headers and columns of data.

I want to turn on the &quot;Filter&quot; function for the sheet, so the user can easily sort and filter the data. 

Can I so this using POI?

Setting filter on headers of an Excel sheet via POI

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(&quot;Report&quot;);
    
    // some more code
    
    HSSFRow row = sheet.createRow(0);
    
    HSSFCell cell = row.createCell(cellNumber);
    HSSFCellStyle cellStyle = wb.createCellStyle();
    
    cellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    		    		
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    		            
    cellStyle.setFont(font);
    cell.setCellStyle(cellStyle);

The issue I am having is that setting the fill background color on the cell always comes out black, no matter what color I pick. What am I doing wrong? If I don&#39;t use the &quot;setFillPattern&quot; line, no color shows up at all.

All Apache Poi Solutions on Gang of Coders

Total of 20 Apache Poi Solutions