关于Excel的相关操作

两种方式操作

在工作中我们经常会用到对excel的读入和写出的一些操作，之前这个一直是我避免的，这次理解完之后写下demo，记录一下。注释放在代码里面，可以直接跑一次尝试（先看效果，再看逻辑代码实现），两种方式都是比较好理解的，会其一，其二自然好理解，先明白后尝试写代码。

JXL方式

这是之前的方式，但是对excel版本可能有存在报错，记得好像是2007版本之后会有些错误；Demo如下：

package com.yqx.demo;

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

import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;


/**
 * jxl辅助测试
 *
 * @author YangChingyu-k
 * @date 2019/9/4 17:03
 */
public class DemoSqlTest {

    public static void main(String[] args) {

        // 读取Excel文件
        File readfile = new File("D:/work_to_space/textFile/msg.xls");
        try {
            //得到所有数据
            List<List<String>> allData = readExcel(readfile);
            //处理数据
            List<List<String>> result = dealData(allData);
            // 写入
            makeExcel(result);

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


    /**
     * 获取数据
     * @param file
     * @return
     * @throws Exception
     */
    private static List<List<String>> readExcel (File file) throws Exception {

        // 创建输入流，读取Excel
        InputStream is = new FileInputStream(file.getAbsolutePath());
        // jxl提供的Workbook类
        Workbook wb = Workbook.getWorkbook(is);

        Sheet sheet = wb.getSheet(0);
        int rows = sheet.getRows();
        List<List<String>> allData = new ArrayList<List<String>>();
        // 前面4行不要
        for (int j = 4; j < rows; j++) {

            List<String> oneData = new ArrayList<String>();
            // 得到每一行的单元格的数据
            Cell[] cells = sheet.getRow(j);
            for (int k = 0; k < cells.length; k++) {

                oneData.add(cells[k].getContents().trim());
            }
            allData.add(oneData);

        }
        return allData;

    }


    /**
     * 处理数据
     */
    public static List<List<String>> dealData (List < List < String >> allData) {

        List<List<String>> result = new ArrayList<List<String>>();
        List<String> lmsgList = new ArrayList<String>();
        List<String> operList = new ArrayList<String>();

        for (int i = 0; i < allData.size(); i++) {

            List<String> oneDatai = allData.get(i);

            // LM_ID
            String id = getUUID();

            // 登记时间
            String createTime = oneDatai.get(1);
            // 姓名
            String username = oneDatai.get(2);
            if (username.contains("'")) {
                StringBuffer sb = new StringBuffer(username);
                sb.insert(username.indexOf("'"), "'");
                username = sb.toString();
            }
            // 联系电话
            String mobile = oneDatai.get(3);
            if (mobile.contains("'")) {
                StringBuffer sb = new StringBuffer(mobile);
                sb.insert(mobile.indexOf("'"), "'");
                mobile = sb.toString();
            }
            if (mobile.contains("\\")) {
                StringBuffer sb = new StringBuffer(mobile);
                sb.insert(mobile.indexOf("\\"), "\\");
                mobile = sb.toString();
            }
            // 内容
            String content = oneDatai.get(4);

            // 留言表插入SQL
            String leaveMsgSql = "INSERT INTO LM_LEAVE_MSG (ID, LM_SERIAL_NUM, LM_STATUS, LM_PRIORITY,\n" +
                    "        WORKGROUP, AGENT, CUSTOMER_ID, IP, FROM_PAGE, CHANNEL_NO, MOBILE, EMAIL, CONTENT, CREATE_TIME,\n" +
                    "        CLOSE_TIME, UPDATE_TIME, LAST_AGENT_REPLY_TIME, LAST_AGENT_REPLY, STRATEGY, USER_NAME, QQ, WECHAT, ENTERPRISE_ID, AREA, ENGINE, LM_FROM, SUBORG_ID,LM_TYPE)\n" +
                    "        VALUES(\n" +
                    "            '"+id+"', NEXTVAL('LM_ID'), '3', null, null, null, null, null, null\n" +
                    "            , null, '"+mobile+"', null, '"+content+"', '"+createTime+"', null, '"+createTime+"', null, null\n" +
                    "            , '1', '"+username+"', null, null, '{orgName}', null, null, null, '{suborgId}', '2' );";
            lmsgList.add(leaveMsgSql);

            // 操作记录ID
            String operId = getUUID();
            // 备注添加到操作记录表中
            String operContent = oneDatai.get(7);
            // 操作记录表插入SQL
            String operSql = "INSERT INTO LM_OPERATION(ID, LM_ID, OPERATOR, WORKGROUP_NAME, OPER_TYPE, OPER_CONTENT, OPER_TIME, HAS_FILES) \n" +
                    "VALUES( '"+operId+"', '"+id+"', '{operator}', '{workgroupName}', null, '<p>"+operContent+"</p>', null, '0' );";
            operList.add(operSql);
        }
        result.add(lmsgList);
        result.add(operList);

        return result;

    }


    /**
     * 将数据写入到excel中
     */
    public static void makeExcel (List < List < String >> result) {
        String msgFile = "C:/Users/YangChingyu-k/Desktop/留言SQL/20181001_1231_msg.sql";
        String operFile = "C:/Users/YangChingyu-k/Desktop/留言SQL/20181001_1231_oper.sql";
        String msgSql = "";
        String operSql = "";

        for (String s : result.get(0)) {
            msgSql += s + "\r\n";
        }

        for (String str : result.get(1)) {
            operSql += str + "\r\n";
        }

        try {
            // 写入文件
            File writeMsg = new File(msgFile);
            writeMsg.createNewFile(); // 创建新文件
            BufferedWriter out = new BufferedWriter(new FileWriter(writeMsg));
            out.write(msgSql);
            out.flush();
            out.close();
            System.out.print("--- 生成msgSql文件成功 ---\n");

            System.out.println("-----------------------\n");

            File writeOper = new File(operFile);
            writeOper.createNewFile(); // 创建新文件
            BufferedWriter outOper = new BufferedWriter(new FileWriter(writeOper));
            outOper.write(operSql);
            outOper.flush();
            outOper.close();
            System.out.print("--- 生成operSql文件成功 ---\n");

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

    }

    /**
     * 生成ID
     * @return
     */
    public static String getUUID() {
        return "" + UUID.randomUUID();
    }

}

POI方式

package com.yqx.demo;

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 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;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>利用poi读取excel</p>
 *
 * 1.先用InputStream获取excel文件的io流
 * 2.然后创建一个内存中的excel文件HSSFWorkbook类型对象
 * 3.对这个excel文件的每页做循环处理
 * 4.对每页中每行做循环处理
 * 5.对每行中的每个单元格做处理，获取这个单元格的值
 * 6.把这行添加到一个List数组中
 * 7.把每行添加到最后的总结果中
 * 8.解析完以后就获取了一个List<List<String>>类型的对象了
 *
 * <p>xls和xlsx步骤相同，只是提供的类不同</p>
 *
 * @author YangChingyu-k
 * @date 2019/9/5 15:52
 */
public class DemoReadExcelText {

    public static void main(String[] args) {

        String path = "D:/work_to_space/textFile/msg.xls";
        try {
            List<List<String>> result = new DemoReadExcelText().readXls(path);
            System.out.println("---- size ----" + result.size());
            for (int i=0;i<result.size();i++) {
                List<String> list = result.get(i);
                System.out.println("------ list: ------" + list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取数据xls类型
     *
     * @param path
     * @return
     * @throws Exception
     */
    private List<List<String>> readXls(String path) throws Exception{

        InputStream is = new FileInputStream(path);
        // xls类型
        HSSFWorkbook workbook = new HSSFWorkbook(is);
        List<List<String>> result = new ArrayList<List<String>>();
        int size = workbook.getNumberOfSheets();
        // 循环每一页，并处理当前页
        for (int i=0; i<size; i++) {
            // 标识某一页
            HSSFSheet sheet = workbook.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            // 遍历，表头不要
            for (int rowNum=1; rowNum<=sheet.getLastRowNum();rowNum++) {
                // HSSFRow 表示行
                HSSFRow hssfRow = sheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }
                int minCol = hssfRow.getFirstCellNum();
                int maxCol = hssfRow.getLastCellNum();
                List<String> rowList = new ArrayList<String>();
                //遍历该行，获取处理每个cell元素
                for (int colIx = minCol; colIx<maxCol; colIx++) {
                    // HSSFCell 表示单元格
                    HSSFCell cell = hssfRow.getCell(colIx);
                    if (cell == null) {
                        continue;
                    }
                    rowList.add(getStringVal(cell));
                }
                result.add(rowList);
            }

        }
        return result;

    }

    /**
     * 读取数据xlsx类型
     *
     * @param path
     * @return
     * @throws Exception
     */
    private List<List<String>> readXlsx(String path) throws Exception{
        InputStream is = new FileInputStream(path);
        // xlsx
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        List<List<String>> result = new ArrayList<List<String>>();
        // 循环每一页
        for (int i=0;i<workbook.getNumberOfSheets();i++) {
            XSSFSheet xssfSheet = workbook.getSheetAt(i);
            if (xssfSheet == null) {
                continue;
            }
            // 遍历，表头不要
            for (int rowNum=1;rowNum<=xssfSheet.getLastRowNum();rowNum++) {
                // XSSFRow 表示行
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                int minCol = xssfRow.getFirstCellNum();
                int maxCol = xssfRow.getLastCellNum();
                List<String> rowList = new ArrayList<String>();
                // 遍历该行，获取每个cell
                for (int j=minCol;j<maxCol;j++) {
                    // 单元格
                    XSSFCell cell = xssfRow.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    rowList.add(cell.toString());
                }
                result.add(rowList);
            }
        }
        return result;
    }


    /**
     * 类型修改
     *
     * @param cell
     * @return
     */
    private String getStringVal(HSSFCell cell) {

        String value = "";

        // 旧版
        // switch (cell.getCellType()) {
        //     case Cell.CELL_TYPE_BOOLEAN:
        //         return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
        //     case Cell.CELL_TYPE_FORMULA:
        //         return cell.getCellFormula();
        //     case Cell.CELL_TYPE_NUMERIC:
        //         cell.setCellType(Cell.CELL_TYPE_STRING);
        //         return cell.getStringCellValue();
        //     case Cell.CELL_TYPE_STRING:
        //         return cell.getStringCellValue();
        //     default:
        //         return "";
        // }

        // 新版
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                value =  cell.getBooleanCellValue() ? "TRUE" : "FALSE";
                break;
            case FORMULA:
                value = "" + cell.getCellFormula();
                break;
            case NUMERIC:
                value = "" + cell.getNumericCellValue();
                break;
            case STRING:
                value = "" + cell.getStringCellValue();
                break;
            case ERROR:
                value = "" + cell.getErrorCellValue();
                break;
            default:
                break;
        }
        return value;

    }

}

注意点：包和路径不能错，尤其是包，不然不会发现方法不对，写出的时候看具体要求，要生成什么格式什么数据，处理好，再进行操作。谢谢观看~