Article From:https://www.cnblogs.com/fengnan/p/9967376.html

First convert the type to File

Then go through the first page, my EXECL has three lines. If there is a null value in the three lines, it will return the null value’s line number. Otherwise, it will parse the data into the entity set.

package com.docc.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.junit.Test;
import org.springframework.web.multipart.MultipartFile;

import com.docc.model.Inventory;
import com.docc.util.ExcelHelper;
import com.sun.org.apache.regexp.internal.recompile;

import oracle.net.aso.f;

public class ReadInventory {    
    //Parsing Excel files with POI
    @Test
    public static List<Inventory> Read(MultipartFile file) throws FileNotFoundException, IOException{                
        //Turn File
        File f = null;
        if(file.equals("")||file.getSize()<=0){
            file = null;
        }else{
            InputStream ins = file.getInputStream();
            f=new File(file.getOriginalFilename());
            inputStreamToFile(ins, f);
        }                                                                                     
        //Wrap an Excel file object
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(f));
        //Read the first Sheet tab in the file temporarily
        HSSFSheet hssfSheet = workbook.getSheetAt(0);
        
        /*
         * Traverse all the rows in the tab and put the data into the collection inventorys*/
        List <Inventory> inventorys = new ArrayList<Inventory>();
        try {            
            for (Row row : hssfSheet) {
                
                
                /*
                 * Skip the first line*/
                int rowNum = row.getRowNum();
                if(rowNum == 0){
                    continue;
                }        
                
                
                /*
                 * Judging Three Behaviors to Jump Out of Empty End*/
                if(row.getCell(0)==null || row.getCell(0).equals("") || row.getCell(0).getCellType() ==HSSFCell.CELL_TYPE_BLANK){
                    
                    if(row.getCell(1)==null || row.getCell(1).equals("") || row.getCell(1).getCellType() ==HSSFCell.CELL_TYPE_BLANK){
                        
                        if(row.getCell(2)==null || row.getCell(2).equals("") || row.getCell(2).getCellType() ==HSSFCell.CELL_TYPE_BLANK){
                            
                            break;
                        }
                    }
                }
                
                
                /*
                 * After judging the occurrence of empty lines, it traverses again and returns a set containing empty line numbers.*/
                if(row.getCell(0)==null || row.getCell(0).equals(" ") || row.getCell(0).getCellType() ==HSSFCell.CELL_TYPE_BLANK
                   || row.getCell(1)==null || row.getCell(1).equals(" ") || row.getCell(0).getCellType() ==HSSFCell.CELL_TYPE_BLANK
                   || row.getCell(2)==null || row.getCell(2).equals(" ") || row.getCell(0).getCellType() ==HSSFCell.CELL_TYPE_BLANK){
                    /*
                     * Processing, creating collections*/
                    List <Inventory> inventorynull = new ArrayList<Inventory>();
                    Inventory inventoryn = new Inventory();
                    inventoryn.setCompany("The following main field is empty:");
                    inventorynull.add(inventoryn);
                    /*
                     * Loop counting, put in the set*/
                     for(Row rownull : hssfSheet){
                         /*
                          * Skip the first line*/
                         int rowji = rownull.getRowNum();
                          if(rowji == 0){
                              continue;
                          }
                          /*
                           * End exit*/
                         if(row.getCell(0)==null || row.getCell(0).equals("") || row.getCell(0).getCellType() ==HSSFCell.CELL_TYPE_BLANK){
                            if(row.getCell(1)==null || row.getCell(1).equals("") || row.getCell(1).getCellType() ==HSSFCell.CELL_TYPE_BLANK){
                                if(row.getCell(2)==null || row.getCell(2).equals("") || row.getCell(2).getCellType() ==HSSFCell.CELL_TYPE_BLANK){
                                    break;
                                }
                            }
                        }  
                         /*
                          * Counting begins*/
                         if(rownull.getCell(0)==null || rownull.getCell(0).equals(" ") || rownull.getCell(0).getCellType() ==HSSFCell.CELL_TYPE_BLANK
                                || rownull.getCell(1)==null || rownull.getCell(1).equals(" ") || rownull.getCell(0).getCellType() ==HSSFCell.CELL_TYPE_BLANK
                                || rownull.getCell(2)==null || rownull.getCell(2).equals(" ") || rownull.getCell(0).getCellType() ==HSSFCell.CELL_TYPE_BLANK){
                             int ji = rownull.getRowNum() +1;
                             String jiString = ji + "";
                             Inventory inventory = new Inventory();
                             inventory.setCompany(jiString);
                             inventorynull.add(inventory);
                          }//Single count completed
                      }//The loop count is over, and the count has been put into the collection//You can return the collection, which is the result of the count.
                     return  inventorynull;
                }//Judging that the processing of empty lines is over, a set containing empty line numbers has been returned
                    
                
                    /*
                     * After checking the empty processing, start to get three rows of data and put them in inventorys*/
                    String company = getValue(row.getCell(0),"####");                    
                    String reportdate = getValue(row.getCell(1),"####");               
                    String type = getValue(row.getCell(2),"####");                    
                    Inventory inventory = new Inventory();
                    inventory.setCompany(company);
                    inventory.setReportdate(reportdate);
                    inventory.setType(type);
                    inventorys.add(inventory);    
                    
            }//Traveling through all the row loops, we get the data set inventorys
        } catch (Exception e) {
            // TODO: handle exception
            /*
             * Anomalies occurring in the process of parsing*/
            List <Inventory> inventoryno = new ArrayList<Inventory>();
            Inventory inventory = new Inventory();
            inventory.setCompany("Parsing data abnormity");
            inventoryno.add(inventory);
            return  inventoryno;
        }//    Get the data set inventorys
        return inventorys;    
    }
    
    
    /*
     * File type conversion*/
    public static  void inputStreamToFile(InputStream ins,File file) {
          try {
           OutputStream os = new FileOutputStream(file);
           int bytesRead = 0;
           byte[] buffer = new byte[8192];
           while ((bytesRead = ins.read(buffer, 0, 8192)) != -1) {
            os.write(buffer, 0, bytesRead);
           }
           os.close();
           ins.close();
          } catch (Exception e) {
           e.printStackTrace();
          }
         }

    /*
     *Read excel column*/
    public static String getValue(Cell cell, String format) {
        String cellValue = "";
        switch (cell.getCellTypeEnum()) {
        case NUMERIC: // number
          if(DateUtil.isCellDateFormatted(cell)){
              SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
              cellValue=sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
          }else{
              DecimalFormat df = new DecimalFormat(format);
              cellValue=df.format(cell.getNumericCellValue());
             // cellValue=String.valueOf(cell.getNumericCellValue());
          }
        break;
        case STRING: // Character string
            cellValue = String.valueOf(cell.getStringCellValue());
            break;
        case BOOLEAN: // Boolean
            cellValue = String.valueOf(cell.getBooleanCellValue());
            break;
        case FORMULA: // formula
            cellValue = String.valueOf(cell.getCellFormula());
            break;
        case BLANK: // Null value
            cellValue = "";
            break;
        case ERROR: // fault
            cellValue = "Illegal character";
            break;
        default:
            cellValue = "unknown type";
            break;
        }
        return cellValue;
    }
                
    
}

 

Link of this Article: Parsing Excel files with POI

Leave a Reply

Your email address will not be published. Required fields are marked *