cn.mypandora.util.MyExcelUtil.java Source code

Java tutorial

Introduction

Here is the source code for cn.mypandora.util.MyExcelUtil.java

Source

/**
 * Copyright  2015.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 */
package cn.mypandora.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

/**
 * Excel
 * <p>User: kaibo
 * <p>Date: 2015/7/17
 * <p>Version: 1.0
 */
public class MyExcelUtil {
    private static final Logger logger = LoggerFactory.getLogger(MyExcelUtil.class);

    /**
     * ??ExcelTitle
     *
     * @param excelFile
     * @param sheetName sheet???
     * @return
     */
    public static List<String> scanExcelTitles(File excelFile, String... sheetName) {
        List<String> titles = new ArrayList<>();
        try {
            Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFile));

            Sheet sheet;
            if (sheetName.length == 0) {
                sheet = workbook.getSheetAt(0);
            } else {
                sheet = workbook.getSheet(sheetName[0]);
            }
            Row row = sheet.getRow(0);
            if (row != null) {
                int i = 0;
                while (true) {
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        break;
                    }
                    titles.add(cell.getStringCellValue());
                    i++;
                }
            }
        } catch (Exception e) {
            logger.debug("Scan Excel [" + excelFile.getPath() + excelFile.getName() + "] Error");
            throw new RuntimeException(e);
        }
        return titles;
    }

    /**
     * ?Excel List<Map<String K,String V>>?
     *
     * @param excelFile  Excel
     * @param fieldNames MapKeyValuesheet?
     * @param sheetName  ??
     * @return
     */
    public static List<Map<String, String>> readExcelToMap(File excelFile, String fieldNames, String... sheetName) {
        List<Map<String, String>> list = Collections.EMPTY_LIST;

        try {
            Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFile));
            list = execRead(workbook, fieldNames, sheetName);
        } catch (Exception e) {
            logger.error("?:" + e);
        }

        return list;
    }

    /**
     * ?Excel List<Map<String K,String V>>?
     *
     * @param excelFile  ?
     * @param fieldNames Key
     * @param sheetName  sheet??
     * @return
     */
    public static List<Map<String, String>> readExcelToMap(InputStream excelFile, String fieldNames,
            String... sheetName) {
        List<Map<String, String>> list = Collections.EMPTY_LIST;
        try {
            Workbook workbook = WorkbookFactory.create(excelFile);
            list = execRead(workbook, fieldNames, sheetName);
        } catch (Exception e) {
            logger.error("?:" + e);
        }
        return list;
    }

    /**
     * @param workbook 
     * @param fieldNames ??
     * @param sheetName ???
     * @return
     */
    private static List<Map<String, String>> execRead(Workbook workbook, String fieldNames, String... sheetName) {
        String[] strKey = fieldNames.split(",");
        List<Map<String, String>> listMap = new ArrayList<>();
        int i = 1;
        try {
            Sheet sheet;
            if (sheetName.length == 0) {
                sheet = workbook.getSheetAt(0);
            } else {
                sheet = workbook.getSheet(sheetName[0]);
            }
            while (true) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    break;
                }
                Map<String, String> map = new HashMap<String, String>();
                map.put("rowid", String.valueOf(row.getRowNum()));
                for (int keyIndex = 0; keyIndex < strKey.length; keyIndex++) {
                    Cell cell;
                    cell = row.getCell(keyIndex);
                    String cellValue = "";
                    if (cell != null) {
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC: {
                            // ?cell?Date
                            if (DateUtil.isCellDateFormatted(cell)) {
                                // Date?CellDate
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                            }
                            // 
                            else {
                                // ??Cell
                                Integer num = new Integer((int) cell.getNumericCellValue());
                                cellValue = String.valueOf(num);
                            }
                            break;
                        }
                        case Cell.CELL_TYPE_STRING:
                            cellValue = cell.getRichStringCellValue().getString();
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            System.out.println(cell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            System.out.println(cell.getCellFormula());
                            break;
                        default:
                            cellValue = " ";
                        }
                    }
                    map.put(strKey[keyIndex], cellValue);
                }
                listMap.add(map);
                i++;
            }
        } catch (Exception e) {
            logger.debug("?" + i + "??");
            throw new RuntimeException(e);
        }
        return listMap;
    }

    /**
     * Excel, ?
     *
     * @param filepath    ?
     * @param sheetTitle  Sheet??
     * @param fieldTitles Sheet????
     */
    public static void writeExcel(String filepath, String sheetTitle, String fieldTitles) {
        Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };
        for (int i = 0; i < wbs.length; i++) {
            Workbook wb = wbs[i];
            // ExcelSheet
            Sheet sheet = wb.createSheet();
            wb.setSheetName(0, sheetTitle);

            // Sheet
            createTitle(sheet, fieldTitles);

            // ?Excel
            saveExcelFile(wb, filepath);
        }
    }

    /**
     * Excel ????List<Map<String K,String V>>
     *
     * @param filepath    ?
     * @param sheetTitle  Sheet??
     * @param fieldTitles Sheet????
     * @param objList     ??
     * @param fieldNames  ?objClassfield??
     */
    public static void writeExcel(String filepath, String sheetTitle, String fieldTitles,
            List<Map<String, String>> objList, String fieldNames) {
        Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };
        for (int j = 0; j < wbs.length; j++) {
            Workbook workbook = wbs[j];
            CreationHelper creationHelper = workbook.getCreationHelper();

            // ExcelSheet
            Sheet sheet = workbook.createSheet(sheetTitle);
            workbook.setSheetName(0, sheetTitle);

            // Sheet
            createTitle(sheet, fieldTitles);

            // Sheet?
            String[] strArray = fieldNames.split(",");
            for (int objIndex = 0; objIndex < objList.size(); objIndex++) {
                Map<String, String> map = objList.get(objIndex);
                Row row = sheet.createRow(objIndex + 1);
                for (int cellNum = 0; cellNum < strArray.length; cellNum++) {
                    Cell cell = row.createCell(cellNum);
                    cell.setCellType(CellType.STRING);
                    if (map.get(strArray[cellNum]) != null)
                        cell.setCellValue(map.get(strArray[cellNum]).toString());
                    else {
                        cell.setCellValue("");
                    }
                }
            }

            // ?Excel
            saveExcelFile(workbook, filepath);
        }

    }

    /**
     * Excle
     *
     * @param filepath    ?
     * @param sheetTitle  Sheet??
     * @param fieldTitles Sheet????
     * @param objList     ??
     * @param objClass    ???
     * @param fieldNames  ?objClassfield??
     */
    public static void writeExcel(String filepath, String sheetTitle, String fieldTitles, List<?> objList,
            Class<?> objClass, String fieldNames) {
        Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };
        for (int j = 0; j < wbs.length; j++) {
            Workbook workbook = wbs[j];
            CreationHelper creationHelper = workbook.getCreationHelper();

            Sheet sheet = workbook.createSheet();
            workbook.setSheetName(0, sheetTitle);

            createTitle(sheet, fieldTitles);
            createBody(sheet, objList, objClass, fieldNames);
            // ?Excel
            saveExcelFile(workbook, filepath);
        }

    }

    /**
     * Excel?sheet?
     *
     * @param sheet       Excelsheet
     * @param fieldTitles sheet?(sheet?)
     */
    private static void createTitle(Sheet sheet, String fieldTitles) {
        Row row = sheet.createRow(0);
        Cell cell;

        String[] strArray = fieldTitles.split(",");
        for (int i = 0; i < strArray.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(CellType.STRING);
            cell.setCellValue(strArray[i]);
        }
    }

    /**
     * Excel?sheet
     *
     * @param sheet      sheet
     * @param objList    ??
     * @param objClass   ???
     * @param fieldNames ?objClassfield??
     */
    private static void createBody(Sheet sheet, List<?> objList, Class<?> objClass, String fieldNames) {
        String[] targetMethod = fieldNames.split(",");
        Method[] ms = objClass.getMethods();
        Pattern pattern = Pattern.compile("^get.*");

        // objList?sheet
        for (int objIndex = 0; objIndex < objList.size(); objIndex++) {
            Object obj = objList.get(objIndex);
            Row row = sheet.createRow(objIndex + 1);
            // strBody?sheet
            for (int strIndex = 0; strIndex < targetMethod.length; strIndex++) {
                String targetMethodName = targetMethod[strIndex];
                // msstrBody
                for (int i = 0; i < ms.length; i++) {
                    Method srcMethod = ms[i];
                    if (pattern.matcher(srcMethod.getName()).matches()) {
                        int len = targetMethodName.indexOf(".") < 0 ? targetMethodName.length()
                                : targetMethodName.indexOf(".");
                        if (srcMethod.getName()
                                .equals(("get"
                                        + String.valueOf(targetMethodName.substring(0, len).charAt(0)).toUpperCase()
                                        + targetMethodName.substring(1, len)))) {
                            Cell cell = row.createCell(strIndex);
                            cell.setCellType(CellType.STRING);
                            try {
                                // 
                                if (targetMethodName.contains(".")) {
                                    cell.setCellValue(referenceInvoke(targetMethodName, obj));
                                    // 
                                } else {
                                    cell.setCellValue((srcMethod.invoke(obj)).toString());
                                }
                            } catch (Exception e) {
                                throw new RuntimeException(e);
                            }
                        }
                    }
                }
            }
        }

    }

    /**
     * ?Excel
     *
     * @param workbook   Excel
     * @param outputPath ?
     */
    private static void saveExcelFile(Workbook workbook, String outputPath) {
        try {
            FileOutputStream fos = new FileOutputStream(outputPath);
            workbook.write(fos);

            fos.flush();
            fos.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * getHomeplace.getName?
     * ???
     *
     * @param targetMethod ?
     * @param obj          ?
     * @return
     */
    private static String referenceInvoke(String targetMethod, Object obj) {
        // ??(??objgetHomeplace())
        String refMethod = targetMethod.substring(0, targetMethod.indexOf("."));
        // ??(getName())
        targetMethod = targetMethod.substring(targetMethod.indexOf(".") + 1);
        try {
            // (?objobj.getHomeplace())
            obj = obj.getClass()
                    .getMethod("get" + String.valueOf(refMethod.charAt(0)).toUpperCase() + refMethod.substring(1))
                    .invoke(obj);
        } catch (Exception e) {
            e.printStackTrace();
        }

        // ??
        if (targetMethod.contains(".")) {
            return referenceInvoke(targetMethod, obj);
            // ??
        } else {
            try {
                // obj?
                Method tarMethod = obj.getClass().getMethod(
                        "get" + String.valueOf(targetMethod.charAt(0)).toUpperCase() + targetMethod.substring(1));
                return tarMethod.invoke(obj).toString();
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void main(String[] args) {
        // ?
        String fileName = "C:\\Users\\JUSFOUN\\Desktop\\10.xlsx";
        List<String> titles = scanExcelTitles(new File(fileName));
        List<Map<String, String>> listMap = readExcelToMap(new File(fileName), StringUtils.join(titles, ','),
                "Sheet1");

        // ?
        writeExcel("C:\\Users\\JUSFOUN\\Desktop\\11.xlsx", "test1", "name,leader", listMap,
                StringUtils.join(titles, ','));
    }
}