Using Apache POI how to read a specific excel column

Question

I m having a problem in excel while using Apache POI  I can read across rows  but sometimes I m in a situation where I would like to read a particular column only   So is it possible to read any particular column like only the  A  column only or the column  C  only   I m using the Java language for this

User · Answer

You could just loop the rows and read the same cell from each row  doesn t this comprise a column

User · Answer

heikkim is right  here is some sample code adapted from some code I have  import org apache poi ss usermodel Cell  import org apache poi ss usermodel Sheet  import org apache poi ss usermodel Row      for  int rowIndex   0  rowIndex  lt   sheet getLastRowNum    rowIndex        row   sheet getRow rowIndex     if  row    null        Cell cell   row getCell colIndex       if  cell    null             Found column and there is value in the cell        cellValueMaybeNull   cell getStringCellValue             Do something with the cellValueMaybeNull here              break                   For the colCount use something like row getPhysicalNumberOfCells

User · Answer

Sheet sheet    workBook getSheetAt 0      Get Your Sheet     for  Row row   sheet       For each Row        Cell cell   row getCell 0      Get the Cell at the Index   Column you want        My solution  a bit simpler code wise

User · Answer

import java io     import org apache poi hssf util CellReference  import org apache poi ss usermodel    import java text     public class XSLXReader       static DecimalFormat df   new DecimalFormat       0         public static void main String   args            FileWriter fostream          PrintWriter out   null          String strOutputPath    H   BLR Team  Kavitha  Excel-to-xml             String strFilePrefix    Master 5 2-B            try               InputStream inputStream   new FileInputStream new File  H   BLR Team  Kavitha  Excel-to-xml  Stack-up 20L pure storage 11-0039-01 ISU USA-A 1-30-17-Rev exm xls                 Workbook wb   WorkbookFactory create inputStream                 Sheet sheet   wb getSheet 0               Sheet sheet  null              Integer noOfSheets  wb getNumberOfSheets                 for int i 0 i lt noOfSheets i                     sheet   wb getSheetAt i                   System out println  Sheet     i         sheet getSheetName                     System out println  Sheet     i         sheet getFirstRowNum                     System out println  Sheet     i         sheet getLastRowNum                    Column 29             fostream   new FileWriter strOutputPath          strFilePrefix i    xml                out   new PrintWriter new BufferedWriter fostream                 out println   lt  xml version   1 0   encoding   UTF-8    gt                 out println   lt Bin-code gt                  boolean firstRow   true              for  Row row   sheet                    if  firstRow    true                        firstRow   false                      continue                                    out println   t lt DCT gt                     out println formatElement   t t    ID   formatCell row getCell 0                      out println formatElement   t t    Table name   formatCell row getCell 1                      out println formatElement   t t    isProddaten   formatCell row getCell 2                      out println formatElement   t t    isR3P01Data   formatCell row getCell 3                       out println formatElement   t t    LayerNo   formatCell row getCell 29                      out println   t lt  DCT gt                               CellReference ref   new CellReference  A13              Row r   sheet getRow ref getRow               if  r    null                 Cell c   r getCell ref getCol                System out println c getRichStringCellValue   getString                              for  Row row   sheet                      for  Cell cell   row                           CellReference cellRef   new CellReference row getRowNum    cell getColumnIndex                             switch  cell getCellType                            case Cell CELL TYPE STRING                            System out println cell getRichStringCellValue   getString                               break                        case Cell CELL TYPE NUMERIC                            if  DateUtil isCellDateFormatted cell                                   System out println cell getDateCellValue                                 else                                 System out println cell getNumericCellValue                                                           break                        case Cell CELL TYPE BOOLEAN                            System out println cell getBooleanCellValue                               break                        case Cell CELL TYPE FORMULA                            System out println cell getCellFormula                               break                        case Cell CELL TYPE BLANK                            System out println                              break                        default                            System out println                                                                       out write   lt  Bin-code gt                 out flush                out close                            catch  Exception e                e printStackTrace                         private static String formatCell Cell cell                if  cell    null                return                       switch cell getCellType                  case Cell CELL TYPE BLANK                  return                 case Cell CELL TYPE BOOLEAN                  return Boolean toString cell getBooleanCellValue                 case Cell CELL TYPE ERROR                  return   error                case Cell CELL TYPE NUMERIC                  return XSLXReader df format cell getNumericCellValue                 case Cell CELL TYPE STRING                  return cell getStringCellValue                default                  return   lt unknown value gt                         private static String formatElement String prefix  String tag  String value            StringBuilder sb   new StringBuilder prefix           sb append   lt             sb append tag           if  value    null  amp  amp  value length    gt  0                sb append   gt                 sb append value               sb append   lt                  sb append tag               sb append   gt               else               sb append    gt                       return sb toString              This code does 3 things    Excel to XML file generation     Eng  Name  Dong Kim     Prints the content of a particular cell   A13  Also print the excel content into normal text format  Jars to be imported  poi-3 9 jar poi-ooxml-3 9 jar poi-ooxml-schemas-3 9 jar xbea  n-2 3 0 jar xmlbeans  -xmlpublic-2 4 0 jar   dom4j-1 5 jar

User · Answer

Okay  from your question  you just simply want to read a particular column  So  while iterating over a row and then on its cells  your can simply check the index of the column   Iterator lt Row gt  rowIterator   mySheet iterator       Traversing over each row of XLSX file         while  rowIterator hasNext                  Row row   rowIterator next       For each row  iterate through each columns             Iterator lt Cell gt  cellIterator   row cellIterator                while  cellIterator hasNext                      Cell cell   cellIterator next                    println  column index  cell getColumnIndex    You will have your columns fixed in Excel file                 if cell getColumnIndex    3   for example of c                                   print  done                                                          I am using POI 3 12--  org apache poi poi 3 12  Hope it helps  Cheers

User · Answer

Please be aware  that iterating through the columns using row cell iterator   Iterator lt Cell gt  cellIterator   row cellIterator     may lead to silent skipping columns  I have just encountered a document that was exposing such behaviour   Iterating using indexes in a for loop and using row getCell i  was not skipping columns and was returning values at the correct column indexes

User · Answer

Here is the code to read the excel data by column   public ArrayList lt String gt  extractExcelContentByColumnIndex int columnIndex           ArrayList lt String gt  columndata   null          try               File f   new File  sample xlsx               FileInputStream ios   new FileInputStream f               XSSFWorkbook workbook   new XSSFWorkbook ios               XSSFSheet sheet   workbook getSheetAt 0               Iterator lt Row gt  rowIterator   sheet iterator                columndata   new ArrayList lt  gt                  while  rowIterator hasNext                      Row row   rowIterator next                    Iterator lt Cell gt  cellIterator   row cellIterator                    while  cellIterator hasNext                          Cell cell   cellIterator next                         if row getRowNum    gt  0     To filter column headings                         if cell getColumnIndex      columnIndex     To match column index                             switch  cell getCellType                                  case Cell CELL TYPE NUMERIC                                  columndata add cell getNumericCellValue                                        break                              case Cell CELL TYPE STRING                                  columndata add cell getStringCellValue                                     break                                                                                                                            ios close                System out println columndata             catch  Exception e                e printStackTrace                      return columndata

[java] Using Apache POI how to read a specific excel column

Examples related to java

Examples related to excel

Examples related to apache-poi