在整理表格数据做工资表的时候遇到一个问题,由于员工入职离职较为频繁,文员没有及时整理,多张表中数据无法对接,下面上图描述一下问题:
source表是上月的人员表,表中有md、rzsj、tbsj、lzsj等信息
这张是新的表,这里是我整理之后把数据删掉了,之前是信息参差不齐,有的有,有的没有,而且名字的顺序和source表中的名字顺序不一致,首先想到的办法是用WPS的“VLOOKUP”函数实现数据的引用,但是试了多次都会报错,只有选定的列能引用成功,向下拖拽的时候,下面的列都会报错,百度了几个答案也没解决掉,所以决定通过Java代码的方式实现数据的引用。
这里从excel中读取数据用的是jxl,写数据用的是poi,版本如下图所示
代码如下:
package com.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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 com.util.domain.TargetExcel;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
public class ExcelReadWrite {
public static void main(String[] args) {
File file = new File("D:/source.xls");
try {
// 创建输入流,读取Excel
InputStream is = new FileInputStream(file.getAbsolutePath());
// jxl提供的Workbook类
Workbook wb = Workbook.getWorkbook(is);
// 只有一个sheet,直接处理
//创建一个Sheet对象
Sheet sheet = wb.getSheet(0);
// 得到所有的行数
int rows = sheet.getRows();
// 所有的数据
Map<String, String> mdMap = new HashMap<>();
Map<String, String> rzsjMap = new HashMap<>();
Map<String, String> tbsjMap = new HashMap<>();
Map<String, String> lzsjMap = new HashMap<>();
String name = null;
// 越过第一行 它是列名称
for (int j = 1; j < rows; j++) {
// 得到每一行的单元格的数据
Cell[] cells = sheet.getRow(j);
name = cells[0].getContents().trim();
mdMap.put(name, cells[1].getContents().trim());
rzsjMap.put(name, cells[2].getContents().trim());
tbsjMap.put(name, cells[3].getContents().trim());
lzsjMap.put(name, cells[4].getContents().trim());
}
//读取目标文件
file = new File("D:/target.xls");
is = new FileInputStream(file.getAbsolutePath());
wb = Workbook.getWorkbook(is);
// 只有一个sheet,直接处理
//创建一个Sheet对象
sheet = wb.getSheet(0);
// 得到所有的行数
rows = sheet.getRows();
List<TargetExcel> list = new ArrayList<>();
for(int i = 1; i < rows; i++){
Cell[] cells = sheet.getRow(i);
TargetExcel targetExcel = new TargetExcel();
name = cells[0].getContents().trim();
targetExcel.setName(name);
targetExcel.setMd(mdMap.get(name));
targetExcel.setRzsj(rzsjMap.get(name));
targetExcel.setTbsj(tbsjMap.get(name));
targetExcel.setLzsj(lzsjMap.get(name));
list.add(targetExcel);
}
makeExcel(list);
}catch (Exception e){
e.printStackTrace();
}
}
/**
* 将数据写入到excel中
*/
public static void makeExcel(List<TargetExcel> list) {
//第一步,创建一个workbook对应一个excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
//第二部,在workbook中创建一个sheet对应excel中的sheet
HSSFSheet sheet = workbook.createSheet("目标sheet");
//第三部,在sheet表中添加表头第0行,老版本的poi对sheet的行列有限制
HSSFRow row = sheet.createRow(0);
//第四步,创建单元格,设置表头
HSSFCell cell = row.createCell(0);
cell.setCellValue("name");
cell = row.createCell(1);
cell.setCellValue("md");
cell = row.createCell(2);
cell.setCellValue("rzsj");
cell = row.createCell(3);
cell.setCellValue("tbsj");
cell = row.createCell(4);
cell.setCellValue("lzsj");
//第五步,写入数据
for(int i=0;i<list.size();i++) {
TargetExcel oneData = list.get(i);
HSSFRow row1 = sheet.createRow(i + 1);
//创建单元格设值
row1.createCell(0).setCellValue(oneData.getName());
row1.createCell(1).setCellValue(oneData.getMd());
row1.createCell(2).setCellValue(oneData.getRzsj());
row1.createCell(3).setCellValue(oneData.getTbsj());
row1.createCell(4).setCellValue(oneData.getLzsj());
}
//将文件保存到指定的位置
try {
FileOutputStream fos = new FileOutputStream("D:\\target.xls");
workbook.write(fos);
System.out.println("写入成功");
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
执行之后,数据已经引用成功
经过随机抽取查验,发现数据没有问题。
注意一个问题,jxl这个版本只支持xls格式文件的读取,对.xlsx格式的文件进行读取会报如下错误:
jxl.read.biff.BiffException: Unable to recognize OLE stream
at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
at jxl.read.biff.File.<init>(File.java:127)
at jxl.Workbook.getWorkbook(Workbook.java:268)
at jxl.Workbook.getWorkbook(Workbook.java:253)
at com.util.ExcelReadWrite.main(ExcelReadWrite.java:31
这里由于时间比较紧张,就另存为xls格式的文件,先整理数据了,后面有时间再研究是否是jxl版本问题,或者读取文件也用poi的api来实现。