Java读取Excel文件

@Component
public class TestExcel {

    /**
     * 读取excel表格中特定的列
     *
     * @param file
     *            文件
     * @param index
     *            第index列（0开始）
     * @throws Exception
     */
    public  ArrayList<String> readColumn(File file, int index) throws Exception {
        InputStream inputStream = new FileInputStream(file.getAbsoluteFile());
        Workbook workbook = Workbook.getWorkbook(inputStream);
        Sheet sheet = workbook.getSheet(0);
        int rows = sheet.getRows();
        int columns = sheet.getColumns();
        ArrayList<String> arrayList = new ArrayList<>();
        for (int i = 1; i < rows; i++) {
            Cell cell = sheet.getCell(index, i);
            arrayList.add(cell.getContents());
            System.out.println(cell.getContents());
        }
        return arrayList;
    }

    public void test() {
        try {
            File file = ResourceUtils.getFile("classpath:test导入模板.xls");
            System.out.println("正在读取书名...");
            //读取第一列
            ArrayList<String> list1 = readColumn(file, 0);
            //读取第二列
            ArrayList<String> list2 = readColumn(file, 1);
            System.out.println(list1.size());
            System.out.println(list2.size());
            System.out.println("读取完毕");
            HashMap<String, String> blacklistMap = new HashMap<>();
            for (int i = 0; i < list1.size(); i++) {
                blacklistMap.put(list1.get(i), list2.get(i));
            }
            blacklistMap.forEach((k, v) -> {
                System.out.println("key: " + k + " " + "value: " + v);
            });

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

//    public static void main(String[] args) {
//        try {
//            File file = new File("C:\\Users\\Administrator\\Desktop\\世界名著.xlsx");
//            System.out.println("正在读取书名...");
//            //读取第一列
//            ArrayList<String> list1 = readColumn(file, 0);
//            //读取第二列
//            ArrayList<String> list2 = readColumn(file, 1);
//            System.out.println(list1.size());
//            System.out.println(list2.size());
//            System.out.println("读取完毕");
//            HashMap<String, String> blacklistMap = new HashMap<>();
//            for (int i = 0; i < list1.size(); i++) {
//                blacklistMap.put(list1.get(i), list2.get(i));
//            }
//            blacklistMap.forEach((k, v) -> {
//                System.out.println("key: " + k + " " + "value: " + v);
//            });
//
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//    }
}

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.HashMap;

public class ExcelGenSql {
    /**
     * 读取excel表格中特定的列
     *
     * @param file 文件
     * @param index0 第index列（3开始）
     * @param index1 第index列（2开始）
     * @throws Exception
     */
    public static HashMap<Long, String> readColumn(File file, int index0, int index1) throws Exception {
        InputStream inputStream = new FileInputStream(file.getAbsoluteFile());
        Workbook workbook = Workbook.getWorkbook(inputStream);
        Sheet sheet = workbook.getSheet(0);
        int rows = sheet.getRows();
        HashMap<Long, String> blackListMap = new HashMap<>();
        for (int i = 1; i < rows; i++) {
            Cell cell0 = sheet.getCell(index0, i);
            Cell cell1 = sheet.getCell(index1, i);
            blackListMap.put(Long.valueOf(cell0.getContents()), cell1.getContents());
        }
        return blackListMap;
    }

    public static void test() {
        try {
            File file = new File("C:\\Users\\Desktop\\genSQL\\Test导入模板.xls");
            System.out.println("正在读取书名...");
            //读取第一列
            HashMap<Long, String> blacklistMap = readColumn(file, 2, 1);
            String sql = "insert into test_account_blacklist (create_time, creator_id, creator, test_id, test_name) values (now(), 1, 'SYSTEM', %d, '%s');";
            blacklistMap.forEach((k, v) -> {
                Long projectId = k;
                String blackList = v;
                String[] accountNameList = blackList.split("、");
                for (String accountName : accountNameList) {
                    System.out.println(String.format(sql, projectId, accountName.trim()));

                }
            });
            System.out.println("读取完毕");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        test();
    }
}

参考博客