java 利用poi实现解析EXCEL，并根据某列版本号进行分组，把分组后的数据生成新的EXCEL文件。

java 利用poi实现解析EXCEL，并根据某列版本号进行分组，把分组后的数据生成新的EXCEL文件。
controller（逻辑层）

import com.example.model.Excelmodel;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;

import static com.example.utils.CellFormatValue.getCellFormatValue;
import static com.example.utils.ReadExcelR.readExcel;


@Controller
public class CreatExcelController {

    String filepath = "E:\\Desktop\\Excel分组\\";

    @RequestMapping(value = "/indexXml", method = RequestMethod.POST)
    public String ftlIndex(@RequestParam(value = "heardfile") MultipartFile heardfile, HttpServletResponse response) throws Exception {
        Workbook wb = null;
        String columns[] = {"编码", "类型", "分类", "描述（名称）", "版本", "a", "b", "c", "d", "e", "f", "g"};
        wb = readExcel(heardfile);
        File files = new File(filepath);
        if (!files.exists()) {
            files.mkdir();
        }
        if (wb != null) {
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                Sheet sheet = wb.getSheetAt(i);
                if (sheet.getSheetName().equals("列表")) {
                    break;
                }
                As1(columns, sheet);
            }
        }
        return "indexAddXML";
    }

    public void As1(String columns[], Sheet sheet) throws Exception {
        String cellData = null;
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        int rownum = sheet.getPhysicalNumberOfRows();
        Row row = sheet.getRow(0);
        int colnum = row.getPhysicalNumberOfCells();
        for (int i = 0; i < rownum; i++) {
            Map<String, String> map = new LinkedHashMap<String, String>();
            row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < colnum; j++) {
                    cellData = (String) getCellFormatValue(row.getCell(j));
                    map.put(columns[j], cellData);
                }
            } else {
                break;
            }
            list.add(map);
        }
        List<Excelmodel> v1 = new ArrayList<>();
        List<Excelmodel> v2 = new ArrayList<>();
        //遍历解析出来的list
        int is = 0;
        for (Map<String, String> map : list) {
            Excelmodel excelmodel = new Excelmodel();
            for (Map.Entry<String, String> entry : map.entrySet()) {
                if (entry.getKey().equals("编码")) {
                    excelmodel.setA(entry.getValue());
                }
                if (entry.getKey().equals("类型")) {
                    excelmodel.setB(entry.getValue());
                }
                if (entry.getKey().equals("分类")) {
                    excelmodel.setC(entry.getValue());
                }
                if (entry.getKey().equals("描述（名称）")) {
                    excelmodel.setD(entry.getValue());
                }
                if (entry.getKey().equals("版本")) {
                    excelmodel.setE(entry.getValue());
                }
                if (entry.getKey().equals("a")) {
                    excelmodel.setF(entry.getValue());
                }
                if (entry.getKey().equals("b")) {
                    excelmodel.setG(entry.getValue());
                }
                if (entry.getKey().equals("c")) {
                    excelmodel.setH(entry.getValue());
                }
                if (entry.getKey().equals("d")) {
                    excelmodel.setI(entry.getValue());
                }
            }
            if (is == 0) {
                v1.add(excelmodel);
            } else {
                v2.add(excelmodel);
            }
            is++;
        }
        //根据版本号进行分组
        Map<String, List<Excelmodel>> listMap = new HashMap<>();
        for (Excelmodel excelmodel : v2) {
            List<Excelmodel> tempList = listMap.get(excelmodel.getE());
            if (tempList == null) {
                tempList = new ArrayList<>();
                tempList.add(excelmodel);
                listMap.put(excelmodel.getE(), tempList);
            } else {
                tempList.add(excelmodel);
            }
        }
        for (String skuId : listMap.keySet()) {
            List<Excelmodel> excelmodels = listMap.get(skuId);
            setFilepath(sheet.getSheetName() + excelmodels.get(0).getE(), excelmodels, v1);
        }
    }

    public void setFilepath(String sheetName, List<Excelmodel> v2, List<Excelmodel> v3) throws IOException {
        if (v2.size() < 1) {
            return;
        }
        //---------------
        HSSFWorkbook wkb = new HSSFWorkbook();
        HSSFSheet sheet1 = wkb.createSheet(sheetName);
        sheet1.setDefaultColumnWidth(20);
        sheet1.setDefaultRowHeight((short) (30 * 20));
        HSSFRow row1 = sheet1.createRow(0);
        Excelmodel excelmodel1 = v3.get(0);
        row1.createCell(0).setCellValue(excelmodel1.getA());
        row1.createCell(1).setCellValue(excelmodel1.getB());
        row1.createCell(2).setCellValue(excelmodel1.getC());
        row1.createCell(3).setCellValue(excelmodel1.getD());
        row1.createCell(4).setCellValue(excelmodel1.getE());
        row1.createCell(5).setCellValue(excelmodel1.getF());
        row1.createCell(6).setCellValue(excelmodel1.getG());
        row1.createCell(7).setCellValue(excelmodel1.getH());
        row1.createCell(8).setCellValue(excelmodel1.getI());
        for (int i = 0; i < v2.size(); i++) {
            HSSFRow row2 = sheet1.createRow(i + 1);
            Excelmodel excelmodel = v2.get(i);
            row2.createCell(0).setCellValue(excelmodel.getA());
            row2.createCell(1).setCellValue(excelmodel.getB());
            row2.createCell(2).setCellValue(excelmodel.getC());
            row2.createCell(3).setCellValue(excelmodel.getD());
            row2.createCell(4).setCellValue(excelmodel.getE());
            row2.createCell(5).setCellValue(excelmodel.getF());
            row2.createCell(6).setCellValue(excelmodel.getG());
            row2.createCell(7).setCellValue(excelmodel.getH());
            row2.createCell(8).setCellValue(excelmodel.getI());
        }
        FileOutputStream output = new FileOutputStream(filepath + sheetName + ".xls");
        wkb.write(output);
        output.close();
    }
}

model（实体类）

public class Excelmodel {

    private String a;
    private String b;
    private String c;
    private String d;
    private String e;
    private String f;
    private String g;
    private String h;
    private String i;

    public String getA() {
        return a;
    }

    public void setA(String a) {
        this.a = a;
    }

    public String getB() {
        return b;
    }

    public void setB(String b) {
        this.b = b;
    }

    public String getC() {
        return c;
    }

    public void setC(String c) {
        this.c = c;
    }

    public String getD() {
        return d;
    }

    public void setD(String d) {
        this.d = d;
    }

    public String getE() {
        return e;
    }

    public void setE(String e) {
        this.e = e;
    }

    public String getF() {
        return f;
    }

    public void setF(String f) {
        this.f = f;
    }

    public String getG() {
        return g;
    }

    public void setG(String g) {
        this.g = g;
    }

    public String getH() {
        return h;
    }

    public void setH(String h) {
        this.h = h;
    }

    public String getI() {
        return i;
    }

    public void setI(String i) {
        this.i = i;
    }
}

utils（公共类）

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;

public class CellFormatValue {
    public static Object getCellFormatValue(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            //判断cell类型
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC: {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case Cell.CELL_TYPE_FORMULA: {
                    //判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    } else {
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }
}

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;

public class ReadExcelR {
    public static Workbook readExcel(MultipartFile file) {
        String filename = file.getOriginalFilename();
        Workbook wb = null;
        // 获取文件后缀
        String prefix = filename.substring(filename.lastIndexOf(".") + 1);
        if (prefix.equals("xlsx")) {
            try {
                wb = new XSSFWorkbook(file.getInputStream());
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } else if (prefix.equals("xls")) {
            try {
                wb = new HSSFWorkbook(file.getInputStream());
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } else {
            return null;
        }
        return wb;
    }
}

pom(坐标)

    <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>