POI操作(自)

package com.zqykj.tldw.cases.controller;

import com.zqykj.hyjj.casemgmt.entity.CaseCategory;
import com.zqykj.tldw.cases.dao.CaseCategoryDao;
import com.zqykj.tldw.cases.dao.CaseTypeMappingDao;
import com.zqykj.tldw.cases.entity.CaseTypeMapping;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

@RestController
public class POIExcelReadController {

    @Autowired
    private CaseCategoryDao caseCategoryDao;

    @Autowired
    private CaseTypeMappingDao caseTypeMappingDao;

    /**
     * 通过excel创建案件类型对应的关联表
     * @throws IOException
     */
    @GetMapping("/getCaseMapping")
    public void getCaseMapping() throws IOException {
        List<CaseCategory> list = caseCategoryDao.findAll();
        //创建Excel，读取文件内容
        File file=new File("D:/案件类型.xls");
        HSSFWorkbook workbook=new HSSFWorkbook(FileUtils.openInputStream(file));
        //两种方式读取工作表
        //  HSSFSheet sheet=workbook.getSheet("Sheet0");
        HSSFSheet sheet=workbook.getSheetAt(0);
        //获取sheet中最后一行行号
        int lastRowNum=sheet.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            HSSFRow row = sheet.getRow(i);
            //获取当前行最后单元格列号
            int lastCellNum = row.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                HSSFCell cell = row.getCell(j);
                String value = cell.getStringCellValue();
                if (j == 1) {
                    value = value.replace("罪", "案");
                }
                for (CaseCategory caseCategory : list) {
                    if (value.equals(caseCategory.getName())){
                        String uuid = caseCategory.getUuid();
                        String name = caseCategory.getName();
                        String code = row.getCell(0).getStringCellValue();
                        CaseTypeMapping caseTypeMapping = new CaseTypeMapping();
                        caseTypeMapping.setUuid(uuid);
                        caseTypeMapping.setName(name);
                        caseTypeMapping.setCode(code);
                        caseTypeMappingDao.insert(caseTypeMapping);
                    }
                }
                System.out.print(value + " ");
            }
            System.out.println();
        }
    }

    /**
     * 导出mongo案件类型数据存进表格
     * @throws IOException
     */
    @GetMapping("/postCaseMapping")
    public void postCaseMapping() throws IOException {
        //创建Excel文件薄
        HSSFWorkbook workbook=new HSSFWorkbook();
        //创建工作表sheeet
        HSSFSheet sheet=workbook.createSheet();
        //创建第一行
        HSSFRow row=sheet.createRow(0);
        String[] title={"uuid","name","creator","builtIn","order","investigatedStatus"};
        HSSFCell cell=null;
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }

        List<CaseCategory> list = caseCategoryDao.findAll();
        for (int i = 0; i < list.size(); i++) {
            HSSFRow nextrow=sheet.createRow(i+1);
            for (int j = 0; j < 6; j++) {
                CaseCategory caseCategory = list.get(i);
                HSSFCell cell1 = nextrow.createCell(j);
                switch (j){
                    case 0:
                        cell1.setCellValue(caseCategory.getUuid());
                        break;
                    case 1:
                        cell1.setCellValue(caseCategory.getName());
                        break;
                    case 2:
                        cell1.setCellValue(caseCategory.getCreator());
                        break;
                    case 3:
                        cell1.setCellValue(caseCategory.isBuiltIn());
                        break;
                    case 4:
                        cell1.setCellValue(caseCategory.getOrder());
                        break;
                    case 5:
                        cell1.setCellValue(caseCategory.isInvestigatedStatus());
                        break;
                    default:
                        System.out.println("error");
                }
            }
        }
        //创建一个文件
        File file=new File("D:/智器云案件类型导出.xls");
        file.createNewFile();
        FileOutputStream stream=FileUtils.openOutputStream(file);
        workbook.write(stream);
        stream.close();
    }


    /**
     * 创建案件类型映射表
     * @throws IOException
     */
    @GetMapping("/getCaseMapping1")
    public void getCaseMapping1() throws IOException {
        //创建Excel，读取文件内容
        File file=new File("D:/案件类型匹配版本一.xls");
        HSSFWorkbook workbook=new HSSFWorkbook(FileUtils.openInputStream(file));
        //两种方式读取工作表
        //  HSSFSheet sheet=workbook.getSheet("Sheet0");
        HSSFSheet sheet=workbook.getSheetAt(0);

        int lastRowNum=sheet.getLastRowNum();
        for (int i = 1; i <= lastRowNum; i++) {
            HSSFRow row = sheet.getRow(i);
            //获取当前行最后单元格列号
            int lastCellNum = row.getLastCellNum();
            CaseTypeMapping caseTypeMapping = new CaseTypeMapping();
            for (int j = 0; j < lastCellNum; j++) {
                HSSFCell cell = row.getCell(j);
                String value = cell.getStringCellValue();
                System.out.print(value + " ");
                switch (j){
                    case 0:
                        caseTypeMapping.setCode(value);
                        break;
                    case 1:
                        caseTypeMapping.setUuid(value);
                        break;
                    case 2:
                        caseTypeMapping.setName(value);
                        caseTypeMappingDao.insert(caseTypeMapping);
                        break;
                    default:
                        System.err.println("error");
                }
            }
            System.out.println();
        }
    }
}