Java实现Excel表中数据的引用

在整理表格数据做工资表的时候遇到一个问题，由于员工入职离职较为频繁，文员没有及时整理，多张表中数据无法对接，下面上图描述一下问题：

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来实现。