Category Archives: apache-poi

Check a specific Excel column from different files Java Apache Poi

I have a folder having a lot of subfolders, that at their turn have subfolders too, so the idea is to make a test in a specific caracter to past all that in a new worksheet. Here's the format of my sheets:

     A         B        C        D       E
Command N° | Type | Delivery | date | Company

In one sheet we can find one or more command, for me it's important to get every line where the type has a specific caracter (V or U).

For this, I started by checking the root folder, then check the folders starting with 2018 because we make a folder every day and it's named YYYY_mm_dd Here's my code:

boolean bName;

 File repertoire = new File("O:/Network/Commands");
        File[] files=repertoire.listFiles();

    for(int i = 0; i <= files.length -1 ; i++){
               File repertory = new File(files[i].getPath());
               File[] subs=repertory.listFiles();

               for (int j = 0 ; j < subs.length - 1; j++){
                   if(subs[j].isDirectory() && subs[j].getName().startsWith("2018")){

                       File subrep = new File(subs[j].getPath());
                       File[] subsrep=subrep.listFiles();

                       for (int k = 0 ; k < subsrep.length - 1; k++){
                           String fileName = subsrep[k].getName();
                           Pattern uName = Pattern.compile("[a-zA-Z0-9_.+-]+\\.xls");
                           Matcher mUname = uName.matcher(fileName);
                           bName = mUname.matches();
                           if (bName) {

It returns the name of xls files that are saved in 2018, if someone has an idea please don't hesitate. Thanks.

Unable To Read Excel Numeric Value Using Apache POI [duplicate]

Hi I am trying to read excel data at the time of execution.Before I was sending all data as a string in excel sheet and my code was working fine but after some change i need to read one numeric column also using same code . But i am getting error

java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell
        at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(
        at org.apache.poi.xssf.usermodel.XSSFCell.getRichStringCellValue(
        at org.apache.poi.xssf.usermodel.XSSFCell.getStringCellValue(

This is my code .

public String[][] readFileAndExcelData(String sheetName) {
        int maxLAs = 1000;
        int maxRegs = 1000;
        String listUsers[][] = new String[maxLAs+1][9];
        try {
            //-Load the properties
            System.out.println("\n" + "---------------------------------------------------------------------------------"+ "\r\n");
            System.out.println("Read in PROPERTIES file"+ "\r\n");
            System.out.println("---------------------------------------------------------------------------------"+ "\r\n");

            Properties prop = new Properties();
            InputStream input = null;

            String inputFS = getAutomationInputDataPath()+"//";
            input = new FileInputStream(inputFS);

            //-Input file with test data 
            String userAuthenticationFile = prop.getProperty("users_test_file");
            System.out.println("\t" + "userAuthenticationFile =" + userAuthenticationFile + "\r\n");

            //-Load the path of the resources folder, containing files to be tested 
            String resourcesPath = getAutomationInputDataPath()+"/";
            System.out.println("\t" + "resourcesPath =" + resourcesPath + "\r\n");

            System.out.println("\n" + "---------------------------------------------------------------------------------"+ "\r\n");
            System.out.println("Read in INPUT file describing 051 User Authentication"+ "\r\n");
            System.out.println("---------------------------------------------------------------------------------"+ "\r\n");

            String inputDataFS = resourcesPath + userAuthenticationFile;
            InputStream inputData = null;
            inputData = new FileInputStream(inputDataFS);

            Workbook workbook = new XSSFWorkbook(inputData);        
            Sheet lasSheet = workbook.getSheet(sheetName);      
            Iterator<Row> rowIterator = lasSheet.iterator();

            //-Maximum maxLAs learning activities may be specified, at this time
            //-First row must contain labels

            int numberUserAuthentication = 0;
            int r = 0;
            int c = 0;
            while (rowIterator.hasNext()) {
                Row nextRow =;                                
                Iterator<Cell> cellIterator = nextRow.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell =;
                    listUsers[r][c] =cell.getStringCellValue();
                    c = c + 1;
                r = r + 1;
                c = 0;

            numberUserAuthentication = r-1;
            System.out.println("\t" + "Completed reading Excel file that describes User Authentication list.  # LAs = " + numberUserAuthentication + "\r\n");

        }catch (Exception e) {      
        return listUsers;

Can any one help me i want to read complete excel data . I have total 6 sheets and in every sheet i have multiple data in row and column . Unable to read numeric data from excel sheet.

How to skip all the values of a perticular column in an excel file by its name using apache POI [on hold]

I am parsing an excel file which have many columns among them there is a column Total.I have to completely ignore the Total column and all the value in the below cell it contains.My idea is to get the column Index and then skip the column here i am posting my code .

public List<String> processSheet(Sheet sheet) {
    ActivityLauncher.logConsoleMsg("Processing sheet " + sheet.getSheetName() + " started");
    List<String> rows = new ArrayList<String>();
    String sheetName = sheet.getSheetName().toUpperCase();
    /* skip the sheets name containing TOTAL or LOB */
    if (sheetName.contains("TOTAL") || sheetName.contains("LOB")) {
        ActivityLauncher.logConsoleMsg("Sheet " + sheet.getSheetName() + " skipped");
        return rows;

    int rowCountTotal = 0;
    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {
        if (rowCountTotal < 9) {
        Row currentRow =;

        StringBuilder row = new StringBuilder();
        for (int i = 0; i < currentRow.getLastCellNum(); i++) {

            Cell currentCell = currentRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
// Here i am trying to get the columnIndex and then skip but not working 

            int patchColumn = -1;
            for (int cn=0; cn<currentRow.getLastCellNum(); cn++) {
                currentCell = currentRow.getCell(cn);
                 if(currentCell.getCellType()!=Cell.CELL_TYPE_BLANK) {
                 if (currentCell!=null ) {
                      String text = currentCell.getStringCellValue();
                      if ("Total".equals(text)) {
                         patchColumn = cn;

            //*********************************************************************************//               String cellValue = excelManager.evalCell(currentCell);
            if (!cellValue.isEmpty()) {
            if(currentCell == null || currentCell.getCellType() == Cell.CELL_TYPE_BLANK) {
            }else {
        if (!row.toString().isEmpty()) {
    //ActivityLauncher.logConsoleMsg("Processing sheet " + sheet.getSheetName() + " completed");
    return rows;

Can anyone please help ?

How to skip the blank cell value and pick up the next cell while Iterating an excel file using POI

I am trying to parse an excel file where i have to read the header and the data Parallelly.I need a check ,while parsing the header if blank cell is found the i have to immediately skip the cell and pick up the next value but this can not be done in another iteration.Here i have tried but in my code i am getting no value in the skipped cell ,

       while (cellIterator.hasNext() && cellIteratorTotal.hasNext()) {

            Cell currentCell =;

            Cell currentCellTotal =;

            currentRow =  sheet.getRow(currentRowTotal.getRowNum() + 1); // here i am trying to hold the current row value 

            String cellValue = excelManager.evalCell(currentCell);

            String cellValueTotal = excelManager.evalCell(currentCellTotal);
            if(currentRow!=null) {
                String value = currentRow.getCell(currentCellTotal.getColumnIndex(), Row.CREATE_NULL_AS_BLANK).getStringCellValue(); //  here i am trying to get the next cell value 

Can anyone help me with the correct approach please, Thanks in advance

Apache POI – No external workbook with name

I tried to add a workbook, which is accessable online, to an excel file using the above mentioned library. However, I receive the following exception: "No external workbook with name ..."

I do not care if Apache POI can find the external workbook, as I know for sure it exists at that location. If I add it manually, it works.

My question is, can I remove this exception or diable the validation? I simply want to add the URL as a formular in excel. Plain text is not working sadly.

Thank you for your help!

Here the complete Stacktrace (Please do not suggest removing the G in front of HTTP, as I have tried this and it is not working):

Exception in thread "main" java.lang.RuntimeException: No external workbook with name 'G'
    at org.apache.poi.hssf.model.LinkTable.getExternalSheetIndex(
    at org.apache.poi.hssf.model.InternalWorkbook.getExternalSheetIndex(
    at org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook.getSheetExtIx(
    at org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook.get3DReferencePtg(
    at org.apache.poi.hssf.model.HSSFFormulaParser.parse(
    at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(

Here is the complete source code:

private static final String PATH_TO_XLS_FILE = "C:/Users/User/Desktop/folder/value.xls";

public static void main(String[] args) {
    FileInputStream fileInputStream = null;
    FileOutputStream fileOutputStream = null;
    Workbook workbook = null;

    try {
        fileInputStream = new FileInputStream(new File(PATH_TO_XLS_FILE));
        workbook = new HSSFWorkbook(fileInputStream);

        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        Sheet sheet = workbook.getSheet("Daten");
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            Row currectRow =;
            Iterator<Cell> cellIterator = currectRow.cellIterator();
            while (cellIterator.hasNext()) {
                Cell currectCell =;
                if (currectCell.getCellTypeEnum() == CellType.FORMULA
                        && currectCell.getCellFormula().contains("key")) {
                    String link = "'[G]value'!F4";

            fileOutputStream = new FileOutputStream(new File(PATH_TO_XLS_FILE));
    } catch (IOException e) {
        // TODO Auto-generated catch block
    } finally {

Repair error pop up in .xlsx file in mail in zip format in java apache poi

I have used apache poi 3.11 to generate .xlsx files. After that we were required to zip it and send it via email.

But then we try to open the file within the email we get the "We found a problem with some content in 'FileName.xlsx'.Do you want us to try to recover as much as we can? If you trust the source of this workbook, Click Yes" popup. and recovers our data as well. But i do not understand why this error occurs.

We have even tried to generate this code using fileOutputStream "Temp.xlsx" this time there is no error popup. But when we write the workbook to a byteoutputstream . It gives us this error. We have even set the mimeType as "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" We have attached the code snippet plus the popup image.

Please guide us further!

For reference below is the code:

    public ByteArrayOutputStream createWorkBookForEmailXSSF(ExtractionRequestBean extractionRequestBeans, ByteArrayOutputStream outputstream) throws FileNotFoundException, IOException {
        XSSFWorkbook xwb = new XSSFWorkbook();
        XSSFCellStyle headerStyle= SpreadSheetGenerator.settingHeaderStyle(xwb);
        XSSFSheet sheet = xwb.createSheet(extractionRequestBeans.getFileName());
        SpreadSheetGenerator.createHeader(sheet, extractionRequestBeans.getHeaderValues(), headerStyle);
        SpreadSheetGenerator.createRows(extractionRequestBeans, sheet, xwb);
        SpreadSheetGenerator.settingAutosizeColumns(sheet, extractionRequestBeans.getHeaderValues());
        return outputstream;

public static XSSFCellStyle settingHeaderStyle(XSSFWorkbook xwb) {
        XSSFCellStyle style = xwb.createCellStyle();
        XSSFFont font = xwb.createFont();
        return style;

public static XSSFCellStyle setttingNumberStyle(XSSFWorkbook xwb) {
        XSSFCellStyle style = xwb.createCellStyle();
        DataFormat format = xwb.createDataFormat();
        return style;

public static XSSFCellStyle setttingNumberDecimalStyle(XSSFWorkbook xwb) {
        XSSFCellStyle style = xwb.createCellStyle();
        DataFormat format = xwb.createDataFormat();
        return style;

public static XSSFCellStyle setttingDateStyle(XSSFWorkbook xwb) {
        XSSFCellStyle style = xwb.createCellStyle();
        DataFormat format = xwb.createDataFormat();
        style.setDataFormat(format.getFormat("m/d/yy h:mm"));
        return style;
public static void createHeader(XSSFSheet sheet, List<String> headerValues, XSSFCellStyle style) {
        addHeaderRow(sheet, 0, headerValues, style);

private static void addHeaderRow(XSSFSheet sheet, int i, List<String> headerValues, XSSFCellStyle style) {"Start : [SpreadSheetGenerator - createHeaderRow - Start Generating Header]");
        XSSFRow rowhead = sheet.createRow((int) 0);
        int cellIndex = 0;
        for (String headerLabel : headerValues) {
                    addHeaderCell(rowhead, cellIndex, headerLabel, style);
        }"End : [SpreadSheetGenerator - createHeaderRow - Start Generating Header]");

private static void addHeaderCell(XSSFRow rowhead, int cellIndex, String headerLabel, XSSFCellStyle style) {
        if (headerLabel != null && !headerLabel.equalsIgnoreCase("")) {
        } else {

public static void settingAutosizeColumns(XSSFSheet sheet, List<String> headerValues) {
        for (int autoSizeIndex = 0; autoSizeIndex < headerValues.size(); autoSizeIndex++) {

public static void createRows(ExtractionRequestBean extractionRequestBean, XSSFSheet sheet, XSSFWorkbook hwb) {"Start : [SpreadSheetGenerator - createRows - Start Generating Excel Rows ]");
        int rowIndex = 1;
        for (Object[] obj : extractionRequestBean.getDatas()) {
             addRow(sheet, rowIndex, obj, extractionRequestBean.getCellStyleMap(), hwb);
        }"End : [SpreadSheetGenerator - createRows - End Generating Excel Rows]");

private static void addRow(XSSFSheet sheet, int rowIndex, Object[] obj, Map<Integer, Integer> cellStyleMap, XSSFWorkbook hwb) {
        XSSFRow row = sheet.createRow((int) rowIndex);
        int cellIndex = 0;
        addCell(obj, row, cellIndex, cellStyleMap, hwb);

    private static void addCell(Object[] obj, XSSFRow row, int cellIndex, Map<Integer, Integer> cellStyleMap, XSSFWorkbook hwb) {
        for (Object object : obj) {
            if (cellStyleMap.get(cellIndex) == HSSFCellStyleEnum.ONLYSTRING.getStyleCode()) {
                row.createCell(cellIndex).setCellValue(object != null ? object.toString() : "");
            } else if (cellStyleMap.get(cellIndex) == HSSFCellStyleEnum.NUMERICWITHDECIMAL.getStyleCode()) {
                row.createCell(cellIndex).setCellValue(object != null ? Double.valueOf(object.toString()) : 0d);
            } else if (cellStyleMap.get(cellIndex) == HSSFCellStyleEnum.NUMERICWITHOUTDECIMAL.getStyleCode()) {
                row.createCell(cellIndex).setCellValue(object != null ? Double.valueOf(object.toString()) : 0d);
            } else if (cellStyleMap.get(cellIndex) == HSSFCellStyleEnum.ONLYDATE.getStyleCode()) {
                row.createCell(cellIndex).setCellValue(object != null ? DateUtil.formatDateOnly(new Date(object.toString()))  : "");

APACHE POI API is not merging ppt slides properly, its not opening in powerpoint 2016

After merging slides from different ppts with APACHE POI API 3.17 beta1, the new ppt was not opening in powerpoint 2016. I am merging using APACE POIs importContent(XSLFSlide srcSlide) method. Below is the peice of code that i am using for merging,


Here srcSlide is the array of slides from where we need to copy content to new ppt using importContent(srcSlide) method, where ppt.createSlide() method is used to create empty slide into a presentation object.

Here importContent(srcSlide) is not able to copy content from source slide.

How do I fix NotOLE2FileException error?

I am trying to delete a specific row in an excel sheet using Java, however when I run the program i get the following error:

org.apache.poi.poifs.filesystem.NotOLE2FileException: Invalid header signature; read 0x6C6F432C79657254, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document
    at org.apache.poi.poifs.filesystem.NPOIFSFileSystem.<init>(
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(
    at IA$77.actionPerformed(
    at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
    at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
    at java.awt.Component.processMouseEvent(Unknown Source)
    at javax.swing.JComponent.processMouseEvent(Unknown Source)
    at java.awt.Component.processEvent(Unknown Source)
    at java.awt.Container.processEvent(Unknown Source)
    at java.awt.Component.dispatchEventImpl(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Window.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$500(Unknown Source)
    at java.awt.EventQueue$ Source)
    at java.awt.EventQueue$ Source)
    at Method)
    at$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue$ Source)
    at java.awt.EventQueue$ Source)
    at Method)
    at$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at Source)

I cannot post my code due to plagiarism purposes. However, the file I am reading from is a csv file and I am using POI to delete the row. I will explain my code, I use a while loop and a buffered reader and compare the data in the first cell of each row to what I am searching for, if the data matches, then I must delete this entire row. So once I have found the row, for each of the columns I use the following:


Apache Poi 64K Error in Espresso Test

I have a problem in Espresso. I must compare a text with an excel cell. Studio says the following when I call apache poi methods.

Error:The number of method references in a .dex file cannot exceed 64K.

I've tried this in my other project and it works well. I've tried to enable multiDex but it gives some other errors because we are using async http master to get movies,music and other content I can't change main project files. There are so much methods, classes and layouts.

Consequently my Question is: is There any way to solve .dex file cannot exceed 64K Error.

Apache POI: How to update excel file with many formulas?

What am I doing wrong? Value of the cell D5 in file excelFileNew.xlsm is: #DIV/0! Why?

-- Program output:

Formula is: C34
1. case Cell.CELL_TYPE_NUMERIC --> Last evaluated as: 407.2521754511886
Formula is: C34

-- Cell values:

Value of the cell D5 in file excelFileOrig.xlsm is: 407,25
Value of the cell D5 in file excelFileNew.xlsm is: #DIV/0!
Value of the cell C8 in file excelFileOrig.xlsm is: 5,0
Value of the cell D5 in file excelFileNew.xlsm is: 15,0

-- Code: See in 1 Answer