实际中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;
}
}
能实现基本要求，但是一次性把所有数据加载到内存中，在数据量过大的时候有很大风险。
第二版，预计通过多线程来实现，可以大大降低资源消耗
网友评论