poi 实现读取写入创建excel文件

`import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.;

public class ExcelUtil {

private static final DataFormatter FORMATTER = new DataFormatter();

public static List<ImportBean> importExcel(String path) throws ExcelException{
    List<ImportBean> list = new ArrayList<> ();
    ImportBean importBean = null;
    File file = new File(path);
    if(!file.exists()){
        throw new ExcelException("文件不存在！");
    }
    Workbook wb = null;
    InputStream in = null;
   try{
       in = new FileInputStream(file);
       //通过后缀判断格式
       String fileName = path;
       String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
       in = new FileInputStream(file);
       if( fileType.equals("xls") ) {
           //创建excel文件对象
           wb = new HSSFWorkbook(in);
       } else if (fileType.equals("xlsx")){
           wb = new XSSFWorkbook(in);
       } else {
           throw new ExcelException("模板格式不正确，请选择xls或者xlsx文件！");
       }

       //获取第一张表
       Sheet s = wb.getSheetAt(0);
       String[] cloumnTitle = new String[]{"发货单位", "发货人姓名", "发货人联系电话", "发货人备用电话", "启运地（省）", "启运地（市）", "启运地（区）", "具体发货地址"};

       //判断第二行，表格式是否正确
       Row rowTwo = s.getRow(1);
       if(null ==rowTwo){
           throw new ExcelException("模板导入有误！");
       }
       int cells = rowTwo.getPhysicalNumberOfCells();
       //getPhysicalNumberOfCells()获得的是不为空的列数，getLastCellNum()最后一不为空列数；实际列数比设定表头多即可，有一个cell空值判断
       if(cells < cloumnTitle.length){
           throw new ExcelException("模板格式不正确，请选择xls或者xlsx文件！");
       }
       for(int i = 0; i < cells; i++){
           //获取列的值,校验
           Cell cell = rowTwo.getCell(i);
           if(null != cell){
               String cloumnValue = FORMATTER.formatCellValue(cell);
               if(!cloumnTitle[i].equals(cloumnValue)){
                   throw new ExcelException("模板格式不正确，请选择xls或者xlsx文件！");
               }
           }
       }

       SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
       for(int i = 2; i <= s.getLastRowNum(); i++){
           Row row = s.getRow(i);
           String value = "";
           if(null != row){
               Map<String,Object> rowMap = new HashMap<>();
               int firstCellIndex = row.getFirstCellNum();
               int lastCellIndex = row.getLastCellNum();
               for(int j=firstCellIndex; j < lastCellIndex; j++){
                   Cell cell = row.getCell(j);
                   if(null == cell) {
                       continue;
                   }
                   int type = cell.getCellType();
                   if (type==Cell.CELL_TYPE_NUMERIC){
                       if(HSSFDateUtil.isCellDateFormatted(cell)) {
                           Date date = cell.getDateCellValue();
                           value = sdf.format(date);
                       } else {
                           double doubleValue = cell.getNumericCellValue();
                           DecimalFormat df = new DecimalFormat("#，######");
                           value = df.format(cell.getNumericCellValue());
                       }
                       rowMap.put(j+"", value);
                   } else {
                       //Cell有6种值类型，未知类型，数值类型（整小时间），字符串，布尔，空，error。校验剩下的只有
                       //字符串，布尔
                       value = cell.toString();
                       rowMap.put(j+"", value);
                   }
               }
               importBean = new ImportBean();
               String consCompanyName = "";
               String consName = "";
               String consMobile = "";
               String spareMobile = "";
               String consProvince = "";
               String consCity = "";
               String consArea = "";
               String consDetailAddr = "";
               if(null != rowMap.get("0") && !"".equals(rowMap.get("0"))){
                   consCompanyName = rowMap.get("0").toString().trim();
               }
               if(null != rowMap.get("1") && !"".equals(rowMap.get("1"))){
                   consName = rowMap.get("1").toString().trim();
               }
               if(null != rowMap.get("2") && !"".equals(rowMap.get("2"))){
                   consMobile = rowMap.get("2").toString().trim();
               }
               if(null != rowMap.get("3") && !"".equals(rowMap.get("3"))){
                   spareMobile = rowMap.get("3").toString().trim();
               }
               if(null != rowMap.get("4") && !"".equals(rowMap.get("4"))){
                   consProvince = rowMap.get("4").toString().trim();
               }
               if(null != rowMap.get("5") && !"".equals(rowMap.get("5"))){
                   consCity = rowMap.get("5").toString().trim();
               }
               if(null != rowMap.get("6") && !"".equals(rowMap.get("6"))){
                   consArea = rowMap.get("6").toString().trim();
               }
               if(null != rowMap.get("7") && !"".equals(rowMap.get("7"))){
                   consDetailAddr = rowMap.get("7").toString().trim();
               }
               importBean.setConsCompanyName(consCompanyName);
               importBean.setConsName(consName);
               importBean.setConsProvince(consProvince);
               importBean.setConsCity(consCity);
               importBean.setConsArea(consArea);
               importBean.setConsDetailAddr(consDetailAddr);
               importBean.setConsMobile(consMobile);
               importBean.setSpareMobile(spareMobile);
               list.add(importBean);
           }
       }
   }catch (IOException e){
       e.printStackTrace();
   }

    return list;
}

public static void exportExcel(List<ImportBean> list){
   try {
       SimpleDateFormat sdf = new SimpleDateFormat("yyMMddhhmmss");
       Date date = new Date();
       String dateString = sdf.format(date);
       String path = "consignor_" + dateString + ".xls";
       File file = new File(path);
       if(!file.exists()){
           file.createNewFile();
       }
       OutputStream out = new FileOutputStream(file);
       Workbook wb = new HSSFWorkbook();
       Sheet sheet = wb.createSheet("发货人导出表");
       //设置表头
       Row rowOne = sheet.createRow(0);
       String[] cloumnTitle = new String[]{"发货单位", "发货人姓名", "发货人联系电话", "发货人备用电话", "启运地（省）", "启运地（市）", "启运地（区）", "具体发货地址"};
       for (int i = 0; i < cloumnTitle.length; i++) {
           Cell cell = rowOne.createCell(i);
           cell.setCellValue(cloumnTitle[i]);
       }
       //导入各行数据
       for (int i = 0; i < list.size(); i++) {
           ImportBean importBean = list.get(i);
           if (null == importBean) {
               continue;
           }
           Row row = sheet.createRow(i + 1);
           row.createCell(0).setCellValue(importBean.getConsCompanyName());
           row.createCell(1).setCellValue(importBean.getConsName());
           row.createCell(2).setCellValue(importBean.getConsMobile());
           row.createCell(3).setCellValue(importBean.getSpareMobile());
           row.createCell(4).setCellValue(importBean.getConsProvince());
           row.createCell(5).setCellValue(importBean.getConsCity());
           row.createCell(6).setCellValue(importBean.getConsArea());
           row.createCell(7).setCellValue(importBean.getConsDetailAddr());
       }
       wb.write(out);
       out.flush();
       out.close();
   }catch(Exception e){
       e.printStackTrace();
   }
   }
}