I have an xlxs parser. In the code. I can't change my approach much

Question

      public ResponseEntity<Object> importPcpXlsx(MultipartFile xlsx) {
      try {
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(xlsx.getInputStream());
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        Row row;
        Cell cell;
        int i = 1;
      
        List<String> number = new ArrayList<>();
        while (true) {
         if (xssfSheet.getRow(i) != null) {
           row = xssfSheet.getRow(i);
           cell = row.getCell(0);
 
           if (cell.getSheet()
              .getRow(0)
              .getCell(0)
              .getRichStringCellValue()
              .getString()
              .equals("Number".trim())) {
            
             number.add(cell.getStringCellValue().trim());
 
             log.info("number: " + i + ".  № " + cell.getStringCellValue());
           }
         } else {
           i = 1;
           break;
         }
         i++;
        }
     
        List<String> kpgz = new ArrayList<>();
        while (true) {
          if (xssfSheet.getRow(i) != null) {
          
            row = xssfSheet.getRow(i);
            cell = row.getCell(1);
 
            if (cell.getSheet()
               .getRow(0)
               .getCell(1)
               .getRichStringCellValue()
               .getString()
               .equals("kpgz".trim())) {
 
             kpgz.add(cell.getStringCellValue().trim());
 
             log.info("kpgz: " + i + ". " + cell.getStringCellValue());
           }
         } else {
           i = 1;
           break;
         }
         i++;
       }

I go through the columns and read all the lines under each column. But what if I have some columns will not come in xlxs file. That is, I need an option where I do not have to strictly specify columns. Please predict. In code Iread by columns, since columns have different types and should be processed differently. I need to do this so that I do not specify the number of columns in the column number code, as I did cell = row.getCell(0); or row.getCell(1) or row.getCell(2)

Please explain more correct. It is not understandable. You don't know the type of data in the cell, don't you? — Игорь Ходыко, Jan 16 '23 at 18:46
Why not read in row order like [this](https://stackoverflow.com/a/3562214/230513)? You can still `switch` on `getCellType()`. — trashgod, Jan 16 '23 at 18:46
Игорь Ходыко, now I'm binding to specific columns. For example getCell(0) and get.cell(1). I don’t understand how to do it so as not to become attached like that — aleksandr1994, Jan 16 '23 at 19:49
trashgod, I can't do this, I have to do this in many places( — aleksandr1994, Jan 16 '23 at 19:50
@trashgod, my version is suitable when the columns in the file do not change, but if some columns do not come into xlxs, then the code will not work correctly — aleksandr1994, Jan 16 '23 at 19:58
@Игорь Ходыко my version is suitable when the columns in the file do not change, but if some columns do not come into xlxs, then the code will not work correctly — aleksandr1994, Jan 16 '23 at 20:01
Try setting https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Row.MissingCellPolicy.html when you fetch the cell to control what to do if there's no cell in that column? — Gagravarr, Jan 16 '23 at 23:17
@Gagravarr I am checking xssfSheet.getRow(i).getCell(i, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL) == null. But but my main goal is not to be attached to cell numbers — aleksandr1994, Jan 17 '23 at 06:08
Explain please how is xlxs forming? And what do you do there. I need a little more context. May be you can make a small example. — Игорь Ходыко, Jan 17 '23 at 06:30
@Игорь Ходыко, xlxs comes to me with already known column names. But there may be such a case that some speakers are missing, and then I have the wrong flash memory. I usually always import and parse xlxs with the same number of columns, but here the number is different — aleksandr1994, Jan 17 '23 at 06:42
So, but you can check the columns on runtime and bind its name to your data? — Игорь Ходыко, Jan 17 '23 at 06:46
@ИгорьХодыко I need to do something similar - do not specify the number of columns in the column number code, as I did cell = row.getCell(0); or row.getCell(1) or row.getCell(2). But I don't do it in code.. — aleksandr1994, Jan 17 '23 at 06:52
Do you know maximum size of columns and the number of row where title of columns is? — Игорь Ходыко, Jan 17 '23 at 07:25
@ИгорьХодыко yes(I need just count of colums) int noOfColumns = xssfSheet.getRow(0).getPhysicalNumberOfCells(); , i tried that using — aleksandr1994, Jan 17 '23 at 07:29
@ИгорьХодыко I think the approach is a little confused and a little wrong.. — aleksandr1994, Jan 17 '23 at 08:00
Let's look, as I see you have dynamically changing columns, but you can know about their existence only by cells. Or you need to open more info or it is only the way. — Игорь Ходыко, Jan 17 '23 at 08:06
Maybe you can change behavior of file forming? Why is it dynamical? — Игорь Ходыко, Jan 17 '23 at 08:46
@ИгорьХодыко because another service can send 20 columns, or maybe 10. I just made a binding to the size and increment with checks, but I don’t think this is a good option — aleksandr1994, Jan 17 '23 at 08:57
Read the first row, record what column indexes all the column headings that you're interested live in for that file, then when you process the rest of the rows map based on the column index to column heading? — Gagravarr, Jan 17 '23 at 09:08
@Gagravarr Yes thanks, I'm trying to do that. to get a universal version — aleksandr1994, Jan 17 '23 at 09:19
If you can definetly identify name of column and correlate with data, I don't think it is a huge problem. But if it is not, you should change api from service side, I think. — Игорь Ходыко, Jan 17 '23 at 09:21

Игорь Ходыко · Accepted Answer · 2023-01-17T09:41:57.433

1

Read the columns one by one and bind their type, then check data under these columns.

See example of type checking there: Best language to parse extremely large Excel 2007 files

If you can definetely identify column, it is not a problem. If not, you should think about changing api.

edited Jan 17 '23 at 09:41

answered Jan 17 '23 at 09:36

Игорь Ходыко

155
5

1

An example from 2010? - that will not work with latest POI code - latest code has a CellType enum instead of using ints. – PJ Fanning Jan 17 '23 at 13:22
@PJFanning: Thanks! Updated [here](https://stackoverflow.com/a/3562214/230513). – trashgod Jan 17 '23 at 18:15

I have an xlxs parser. In the code. I can't change my approach much

1 Answers1