POI导入和导出Excel

最近老大让我对账，看着每天几千条订单的表格，总感觉老大在背后让我乖乖站好。

为了偷懒，索性写了个方法完成对账的功能，明明能让机器做的事情，干嘛要人工呢。

废话不多说，直接撸代码：

 <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>3.5-FINAL</version> </dependency>

/** * 读写Excel 文件，支持office2003的xls文件和 office2007的xlsx文件。 */import org.apache.commons.lang.StringUtils;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;public class ExcelUtil {    public static boolean isXlsFile(String fileName) {        String excel2003Suffix = ".xls";        return (fileName.indexOf(excel2003Suffix) == fileName.length()                - excel2003Suffix.length());    }    public static boolean isXlsxFile(String fileName) {        String excel2007Suffix = ".xlsx";        return (fileName.indexOf(excel2007Suffix) == fileName.length()                - excel2007Suffix.length());    }    /**     * 检查是否是excel文件     *     * @param fileName  文件名     * @return true/false     */    public static boolean isExcelFile(String fileName) {        if (StringUtils.isBlank(fileName)) {            return false;        }        return isXlsFile(fileName) || isXlsxFile(fileName);    }    /**     * 判断是否为空行     *     * @param row 行对象     * @return true 空 false 非空     */    public static boolean isEmptyRow(Row row) {        if (row == null) {            return true;        }        boolean result = true;        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {            Cell cell = row.getCell(i, HSSFRow.RETURN_BLANK_AS_NULL);            String value = "";            if (cell != null) {                switch (cell.getCellType()) {                    case Cell.CELL_TYPE_STRING:                        value = cell.getStringCellValue();                        break;                    case Cell.CELL_TYPE_NUMERIC:                        value = String.valueOf((int) cell.getNumericCellValue());                        break;                    case Cell.CELL_TYPE_BOOLEAN:                        value = String.valueOf(cell.getBooleanCellValue());                        break;                    case Cell.CELL_TYPE_FORMULA:                        value = String.valueOf(cell.getCellFormula());                        break;                    default:                        break;                }                if (StringUtils.isNotBlank(value.trim())) {                    result = false;                    break;                }            }        }        return result;    }    /**     * 读取excel文件内容到数组。     *     * @param fileName   excel文件名     * @return 每行数据放入一个数组A，多行数据生成的多个数组A再放入一个数组B，即数组的数组 B[A[]]     */    public static ArrayList<Object> readXlsxFileToArray(String fileName) {        if (!isExcelFile(fileName)) {            System.out.println("readXlsxFileToArray: " + "不是excel文件");            return null;        }        ArrayList<Object> result = new ArrayList<Object>();        InputStream stream = null;        try {            stream = new FileInputStream(fileName);            Workbook wb = null;            if (isXlsFile(fileName)) {                wb = new HSSFWorkbook(stream);            } else if (isXlsxFile(fileName)) {                wb = new XSSFWorkbook(stream);            }            if (wb == null) {                System.out.println("readXlsxFileToArray: " + "文件打开失败");                return null;            }            Sheet sheet1 = wb.getSheetAt(0);            int maxCellNum = 0;            for (int i = 0; i <= sheet1.getLastRowNum(); i++) {                Row row = sheet1.getRow(i);                if (row == null || isEmptyRow(row)) {                    break;                }                /**                 * 最大列数由第一行列数决定,因为一般第一行为标题，后续行的列里面有空列                 */                if (i == 0) {                    maxCellNum = row.getLastCellNum();                }                ArrayList<String> cellResult = new ArrayList<String>();                for (int j = 0; j < maxCellNum; j++) {                    Cell cell = row.getCell(j);                    String value = "";                    if (cell == null) {                        cellResult.add(value);                        continue;                    }                    switch (cell.getCellType()) {                        case Cell.CELL_TYPE_STRING:                            value = cell.getStringCellValue();                            break;                        case Cell.CELL_TYPE_NUMERIC:                            // 判断当前的cell是否为Date                            if (HSSFDateUtil.isCellDateFormatted(cell)) {                                // 如果是Date类型则，转化为Data格式                                Date date = cell.getDateCellValue();                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");                                value = sdf.format(date);                            }else{                                value = String                                        .valueOf((int) cell.getNumericCellValue());                            }                            break;                        case Cell.CELL_TYPE_BOOLEAN:                            value = String.valueOf(cell.getBooleanCellValue());                            break;                        case Cell.CELL_TYPE_FORMULA:                            // 判断当前的cell是否为Date                            if (HSSFDateUtil.isCellDateFormatted(cell)) {                                // 如果是Date类型则，转化为Data格式                                Date date = cell.getDateCellValue();                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");                                value = sdf.format(date);                            }else{                                value = String.valueOf(cell.getCellFormula());                            }                            break;                        case Cell.CELL_TYPE_BLANK:                            value = "";                            break;                        case Cell.CELL_TYPE_ERROR:                            System.out.println("readXlsxFileToArray: " + "错误单元格");                            return null;                        default:                            break;                    }                    cellResult.add(value);                }                result.add(cellResult);            }        } catch (Exception e) {            System.out.println("readXlsxFileToArray: " + e.getMessage());        } finally {            try {                if (stream != null) {                    stream.close();                }            } catch (IOException e) {                System.out.println("[readXlsxFileToArray]：关闭excel文件流异常:"                        + e.getMessage());            }        }        System.out.println("[readXlsxFileToArray] 完成");        return result;    }}

测试方法：直接调用readXlsxFileToArray方法，获取的二维数组存入内存中，我这里做的操作是存到DB中，这样可以通过SQL进行关联操作。

List<Object> lists = ExcelUtil.readXlsxFileToArray(fileName);        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");            for (Object obj : lists) {                List<Object> list = (ArrayList<Object>) obj;                String id = (String) list.get(0);                String name = (String) list.get(3);                Byte r = Byte.parseByte((String) list.get(6));                Byte c = Byte.parseByte((String) list.get(7));                Byte s = Byte.parseByte((String) list.get(8));                String amountPaid = (String) list.get(10);                String dateStr = (String) list.get(12);                String contractName = (String) list.get(19);                String purchaseFundsId = (String) list.get(29);

然后，我通过SQL查出对账后的结果，存在一个List<Map<String,Object>>中，再将其通过流写到硬盘上就ok啦，代码如下：

public void ExportExcel(){        //创建一个workbook对象        HSSFWorkbook wb = new HSSFWorkbook();        //根据workbook生成一个表sheet        HSSFSheet sheet = wb.createSheet("对账表");        //根据sheet获取第一行        HSSFRow row = sheet.createRow((int) 0);        //创建单元格，并设置值表头 设置表头居中        HSSFCellStyle style = wb.createCellStyle();        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式        //向第一行单元格中set值        HSSFCell cell = row.createCell((short) 0);        cell.setCellValue("id");        cell.setCellStyle(style);        cell = row.createCell((short) 1);        cell.setCellValue("amount_paid");        cell.setCellStyle(style);        cell = row.createCell((short) 2);        cell.setCellValue("recharge_success");        cell.setCellStyle(style);        cell = row.createCell((short) 3);        cell.setCellValue("purchase_money");        cell.setCellStyle(style);        cell = row.createCell((short) 4);        cell.setCellValue("create_date");        cell.setCellStyle(style);        cell = row.createCell((short) 5);        cell.setCellValue("status");        cell.setCellStyle(style);        cell = row.createCell((short) 6);        cell.setCellValue("name");        cell.setCellStyle(style);        cell = row.createCell((short) 7);        cell.setCellValue("contract_success");        cell.setCellStyle(style);        cell = row.createCell((short) 8);        cell.setCellValue("transfer_detail_id");        cell.setCellStyle(style);        cell = row.createCell((short) 9);        cell.setCellValue("recharge_record_id");        cell.setCellStyle(style);        //从数据库中获取需要导出的值        List<Map<String, Object>> list = orderHanderService.reconciliation();        for (int i = 0; i < list.size(); i++){            row = sheet.createRow((int) i + 1);            Map<String, Object> map = list.get(i);            //创建单元格，并设置值            row.createCell((short) 0).setCellValue((String) map.get("id"));            row.createCell((short) 1).setCellValue((String) map.get("amount_paid"));            row.createCell((short) 2).setCellValue(String.valueOf(map.get("recharge_success")));            row.createCell((short) 3).setCellValue(String.valueOf(map.get("purchase_money")));            cell = row.createCell((short) 4);            cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format(map.get("create_date")));            row.createCell((short) 5).setCellValue(String.valueOf(map.get("status")));            row.createCell((short) 6).setCellValue((String) map.get("name"));            row.createCell((short) 7).setCellValue(map.get("contract_success")!=null?map.get("contract_success").toString():null);            row.createCell((short) 8).setCellValue((String) map.get("transfer_detail_id"));            row.createCell((short) 9).setCellValue((String) map.get("recharge_record_id"));        }        //使用流写文件        try {            FileOutputStream fout = new FileOutputStream("E:/order.xls");            wb.write(fout);            fout.close();        }        catch (Exception e) {            logger.error("异常", e);        }    }

好啦，就是这样啦，程序员就是要偷懒，O(∩_∩)O哈哈~