java操作excel相关（使用poi）

实际中excel是非常常用的一种数据载体，业务中常常需要使用excel向数据库中导入数据，但是业务代码中能识别的excel格式往往与数据原件有格式上的区别，这时需要进行格式的转换，学习使用java操作excel很有必要。

具体场景： 原型.png
需要转换为：
data.png

使用的工具为Apache POI
先熟悉下会使用到的poi中的几个对象：XSSFWorkbook 即excel对象
XSSFSheet 对应excel中的一个工作页
XSSFRow 对应行
XSSFCell 对应格子
由上至下为包含关系。
实现的方案：读取对应的有效数据封装到vo对象中，将vo对象装到list中，从list中读取幷写入到data目标文件中。

vo对象为：

package com.example.format;

public class DataVO {
    private String type;
    private String sex;
    private int age;
    private String grade;
    private int month;
    private double rate;

    @Override
    public String toString() {
        return "DataVO {type=" + type + ", sex=" + sex + ", age=" + age + ", grade=" + grade + ", month=" + month + ", rate=" + rate
                + ", getType()=" + getType() + ", getSex()=" + getSex() + ", getAge()=" + getAge() + ", getGrade()=" + getGrade()
                + ", getMonth()=" + getMonth() + ", getRate()=" + getRate() + ", getClass()=" + getClass() + ", hashCode()=" + hashCode()
                + "}";
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getGrade() {
        return grade;
    }

    public void setGrade(String grade) {
        this.grade = grade;
    }

    public int getMonth() {
        return month;
    }

    public void setMonth(int month) {
        this.month = month;
    }

    public double getRate() {
        return rate;
    }

    public void setRate(double rate) {
        this.rate = rate;
    }

}

操作类为：

package com.example.format;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class FormatExcel {

    public static void main(String[] args) throws Exception, IOException {
        XSSFWorkbook workbook = new XSSFWorkbook(new File("D:/work/123.xlsx"));
        List<DataVO> dataList = readExcel(workbook);
        writeExcel(dataList);
    }

    // 读
    private static List<DataVO> readExcel(XSSFWorkbook workbook) {
        List<DataVO> dataList = new ArrayList<>();
        XSSFSheet sheet = workbook.getSheetAt(0);
        int i = 0;
        while (i < 120) {
            XSSFRow row = sheet.getRow(i);
            if ((row != null) && (row.getCell(0) != null) && (!String.valueOf(row.getCell(0)).equals(""))) {
                int dataARow = i;
                XSSFCell cell = row.getCell(0);
                String str = cell.getStringCellValue();
                Map<String, Object> typeMap = getType(str);
                int type = (int) typeMap.get("type");
                String sex = (String) typeMap.get("sex");
                int grade = (int) typeMap.get("grade");
                dataARow += 1;
                XSSFRow prRow = sheet.getRow(dataARow);
                Map<Integer, Double> prMap = new HashMap<>();
                int m = 1;
                while ((prRow.getCell(m) != null) && (!String.valueOf(prRow.getCell(m)).equals(""))) {
                    Double dou = Double.parseDouble(String.valueOf(prRow.getCell(m)));
                    prMap.put(m, dou);
                    m++;
                }
                dataARow += 1;
                while ((sheet.getRow(dataARow) != null) && (sheet.getRow(dataARow).getCell(0) != null)
                        && (!String.valueOf(sheet.getRow(dataARow).getCell(0)).equals(""))) {
                    XSSFRow dataPRow = sheet.getRow(dataARow);
                    String age = String.valueOf(dataPRow.getCell(0));
                    double arg = Double.parseDouble(age);
                    int n = 1;
                    while ((dataPRow.getCell(n) != null) && (!String.valueOf(dataPRow.getCell(n)).equals(""))) {
                        DataVO dao = new DataVO();
                        Double rate = Double.parseDouble(String.valueOf(dataPRow.getCell(n)));
                        dao.setRate(rate);
                        dao.setAge((int) Math.ceil(arg));
                        dao.setMonth((int) Math.ceil(prMap.get(n)));
                        dao.setType(type);
                        dao.setGrade(grade);
                        dao.setSex(sex);
                        dataList.add(dao);
                        n++;
                    }
                    dataARow++;
                }
                System.out.println("读取到第" + i + "行!!!!!!!!!");
                i = dataARow + 1;
            } else {
                i++;
            }
        }
        return dataList;
    }

    // 写
    @SuppressWarnings("unchecked")
    private static void writeExcel(List<DataVO> dataList) throws Exception {
        int dataRowNum = dataList.size();
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("data");
        XSSFRow fristRow = sheet.createRow(0);
        String[] titles = new String[6];
        titles[0] = "方案";
        titles[1] = "性别";
        titles[2] = "年龄";
        titles[3] = "级别";
        titles[4] = "月份";
        titles[5] = "费率";
        for (int i = 0; i < 6; i++) {
            XSSFCell fristRowCell = fristRow.createCell(i);
            fristRowCell.setCellValue(titles[i]);
        }
        for (int i = 0; i < dataRowNum; i++) {
            XSSFRow row = sheet.createRow(i + 1);
            DataVO dao = dataList.get(i);
            XSSFCell cell0 = row.createCell(0);
            cell0.setCellValue(dao.getType());
            XSSFCell cell1 = row.createCell(1);
            cell1.setCellValue(dao.getSex());
            XSSFCell cell2 = row.createCell(2);
            cell2.setCellValue(dao.getAge());
            XSSFCell cell3 = row.createCell(3);
            cell3.setCellValue(dao.getGrade());
            XSSFCell cell4 = row.createCell(4);
            cell4.setCellValue(dao.getMonth());
            XSSFCell cell5 = row.createCell(5);
            cell5.setCellValue(dao.getRate());
        }
        OutputStream out = new FileOutputStream("D:/work/data.xlsx");
        workbook.write(out);
        out.close();
        System.out.println("over!");
    }

    // 识别标题
    private static Map<String, Object> getType(String str) {
        Map<String, Object> map = new HashMap<>();
        String stri = str.replace("\n", "");
        String[] strs = stri.split(" ");
        String str1 = strs[0];
        String str2 = strs[1];
        String str3 = strs[2];
        String type = "type";
        String sex = "sex";
        String grade = "grade";
        switch (str1) {
        case "甲级":
            map.put(type, 1);
            break;
        case "乙级":
            map.put(type, 2);
            break;
        case "丙级":
            map.put(type, 3);
            break;
        }
        switch (str2) {
        case "男性":
            map.put(sex, "M");
            break;
        case "女性":
            map.put(sex, "F");
            break;
        }
        switch (str3) {
        case "方案一":
            map.put(grade, 1);
            break;
        case "方案二":
            map.put(grade, 2);
            break;
        case "方案三":
            map.put(grade, 3);
            break;
        case "方案四":
            map.put(grade, 4);
            break;
        case "方案五":
            map.put(grade, 5);
            break;
        }
        return map;
    }

}

能实现基本要求，但是一次性把所有数据加载到内存中，在数据量过大的时候有很大风险。

第二版，预计通过多线程来实现，可以大大降低资源消耗