excel工具类

方法

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @Despriction: 用于 excel的通用导出和配置
 * @author: 段定     2018/6/15
 */
public class ExcelUtils {

    private final static Logger LOG = LoggerFactory.getLogger(ExcelUtils.class);

    /**
     * 对 Workbook 的数据初始化 ,稍微的集成
     *
     * @param beanList
     * @param sheetName
     * @param tableName
     * @param titles
     * @param propertyName
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(List beanList, String sheetName, String tableName, String[] titles, String[] propertyName) {
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();//工作簿
        hssfWorkbook.createSheet(sheetName);
        initHSSFWorkbookTableTitles(hssfWorkbook, sheetName, tableName, titles, 4);//初始化表头,加数据
        initHSSFWorkbookData(hssfWorkbook, sheetName, tableName, beanList, propertyName);//数据初始化
        return hssfWorkbook;
    }

    /**
     * 这是 对表头和sheet做初始化
     *
     * @param hssfWorkbook
     * @param sheetName
     * @param tableName
     * @param titles
     * @param widthParam   这个系数最好推荐是 3
     * @return
     */
    public static void initHSSFWorkbookTableTitles(HSSFWorkbook hssfWorkbook, String sheetName, String tableName, String[] titles, Integer widthParam) {
        Sheet sheet = hssfWorkbook.getSheet(sheetName);
        if (null != tableName) {
            //对tableName做处理
            CellStyle tableStyle = getStyle(hssfWorkbook, (short) 20); // tableName 的样式
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titles.length - 1));//表头合并单元格
            Row row0 = sheet.createRow(0);
            row0.setHeightInPoints(25);//行高
            Cell tableCell = row0.createCell(0);//表头值
            tableCell.setCellStyle(tableStyle);
            tableCell.setCellValue(tableName);

            //titles的样式处理
            CellStyle style = getStyle(hssfWorkbook, (short) 12);//titles 的样式
            Row row = sheet.createRow(1);
            row.setHeightInPoints(16);

            Cell cell;
            for (int i = 0; i < titles.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(titles[i].trim());
                cell.setCellStyle(style);
                sheet.setColumnWidth(i, 256 * (widthParam == null ? 3 : widthParam) * titles[i].length());//根据title的长度动态设列宽
            }
        } else {

            CellStyle style = getStyle(hssfWorkbook, (short) 12);//titles 的样式
            Row row = sheet.createRow(0);
            row.setHeightInPoints(16);

            Cell cell;
            for (int i = 0; i < titles.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(titles[i].trim());
                cell.setCellStyle(style);
                sheet.setColumnWidth(i, 256 * (widthParam == null ? 3 : widthParam) * titles[i].length());
            }
        }
    }

    /**
     * 对数据做初始化
     *
     * @param hssfWorkbook
     * @param sheetName
     * @param tableName
     * @param beanList
     * @param propertyName
     */
    public static void initHSSFWorkbookData(HSSFWorkbook hssfWorkbook, String sheetName, String tableName, List beanList, String[] propertyName) {

        //根据tableName是否为null做处理
        int dataRow = (tableName == null ? 1 : 2);
        HSSFSheet sheet = hssfWorkbook.getSheet(sheetName);
        if (!(beanList.size() == 0)) {//对查出的数据做兼容处理
            List<List<String>> lists = dataTransformer(beanList, propertyName);//动态生成数据
            Row row;
            for (int i = 0; i < lists.size(); i++) {//遍历写入数据
                row = sheet.createRow(dataRow + i);
                // 创建单元格，并设置值
                List<String> dataList = lists.get(i);
                for (int j = 0; j < dataList.size(); j++) {
                    row.createCell(j).setCellValue(StrUtil.obj2str(dataList.get(j)));
                }
            }
        } else {
            LOG.info("查询出的数据为空,excel未插入数据");
        }
    }

    /**
     * 获得需要的数据
     * <p>
     * 思路很简单,获得list对象的的类,根据需要的属性按顺序分别添加
     *
     * @param beanList
     * @param propertyName
     * @return
     */
    public static List<List<String>> dataTransformer(List beanList, String[] propertyName) {   //这里不要用List<Object>  会擦掉类型

        List<List<String>> datasList = new ArrayList<>();
        //获得类
        Class obj = beanList.get(0).getClass();
        //获得类的所有属性
        Field[] fields = beanList.get(0).getClass().getFields();
        //根据条件判断添加数据
        for (int i = 0; i < beanList.size(); i++) {
            ArrayList<String> dataList = new ArrayList<String>();
            for (int j = 0; j < propertyName.length; j++) {
                String name = propertyName[j].trim();//去掉头尾空格符
                try {
                    if (beanList.get(0) instanceof Map) {
                        Map map = (Map) beanList.get(i);
                        dataList.add(map.get(name).toString());
                    } else {
                        Method method = obj.getMethod(
                                "get" + name.substring(0, 1).toUpperCase() + name.substring(1, name.length()));// 对应方法
                        String value = StrUtil.obj2str(method.invoke(beanList.get(i)));// 对应值
                        dataList.add(value);
                    }
                } catch (Exception e) {
                    LOG.info("dataTransformer转换数据失败: 请确认字段名正确且有get方法");
                }
            }
            datasList.add(dataList);
        }
        return datasList;
    }

    private static CellStyle getStyle(HSSFWorkbook hssfWorkbook, short fontSize) {
        CellStyle style = hssfWorkbook.createCellStyle();// HSSFWorkbook创建单元格的样式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //创建一个居中格    HSSFCellStyle.ALIGN_CENTER 水平居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// HSSFCellStyle.VERTICAL_CENTER 垂直居中
        HSSFFont font = hssfWorkbook.createFont();// 字体样式
        font.setBold(true);
        font.setFontHeightInPoints(fontSize);
        style.setFont(font);
        return style;
    }

    /**
     * 对 Workbook 的数据初始化  多sheet数据统计
     *
     * @param beanList
     * @param sheetName
     * @param tableName
     * @param titles
     * @param propertyName
     * @return
     */
    public static HSSFWorkbook getHSFWorkbook(List<List> beanList, String[] sheetName, String[] tableName, List<String[]> titles, List<String[]> propertyName) {

        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();//工作簿
        for (int i = 0; i < sheetName.length; i++) {
            hssfWorkbook.createSheet(sheetName[i]);
            initHSSFWorkbookTableTitles(hssfWorkbook, sheetName[i], tableName[i], titles.get(i), 4);//初始化表头,加数据
            initHSSFWorkbookData(hssfWorkbook, sheetName[i], tableName[i], beanList.get(i), propertyName.get(i));//数据初始化
        }
        return hssfWorkbook;
    }

    /**
     * @param book
     * @param rowColStart
     * @param beanList
     * @return
     */
    public static HSSFWorkbook writeData(HSSFWorkbook book, String sheetName, Integer[] rowColStart, List beanList, String[] propertyName) {

        HSSFSheet sheet = book.getSheet(sheetName);
        Integer rowIndex = rowColStart[0];
        Integer colIndex = rowColStart[1];

        if (!(beanList.size() == 0)) {//对查出的数据做兼容处理
            List<List<String>> lists = dataTransformer(beanList, propertyName);//动态生成数据
            Row row;
            for (int i = 0; i < lists.size(); i++) {//遍历写入数据
                row = sheet.createRow(rowIndex + i);
                // 创建单元格，并设置值
                List<String> dataList = lists.get(i);
                for (int j = 0; j < dataList.size(); j++) {
                    row.createCell(colIndex + j).setCellValue(StrUtil.obj2str(dataList.get(j)));
                }
            }
        } else {
            LOG.info("查询出的数据为空,excel未插入数据");
        }
        return book;
    }

    //测试
    public static void main(String[] args) {

    }
}

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import org.apache.poi.hssf.usermodel.*;
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.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Despriction: 用于
 * @author: 段定     2018/6/27
 */
public class ImportExcelUntil {

    /**
     * 处理val（暂时只处理string和number，可以自己添加自己需要的val类型）
     *
     * @param hssfCell
     * @return
     */
    public static String getVal(HSSFCell hssfCell) {
        if (null != hssfCell) {
            if (hssfCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                return hssfCell.getStringCellValue();
            } else {
                return String.valueOf(hssfCell.getNumericCellValue());
            }
        }
        return null;
    }

    /*
     * 对文件名字做处理
     * */
    public static String getFileNameWithTime(String originalFilename) {
        String fileNameModel = originalFilename.substring(originalFilename.lastIndexOf("."));
        String fileName = originalFilename.substring(0, originalFilename.lastIndexOf("."));
        return fileName + DateFormatter.long2YYYYMMDDHHmmss(new Date()) + fileNameModel;
    }

    /**
     * 读取excel数据
     *
     * @param file
     * @return
     * @throws IOException
     */
    public static List<List<String>> getExcelData(File file) throws IOException {
        String fileName = file.getName();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            return toRead2003Excel(file);
        } else if ("xlsx".equals(extension)) {
            return toRead2007Excel(file);
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /**
     * 兼容 2003excel
     *
     * @param file
     * @return
     * @throws IOException
     */
    private static List<List<String>> toRead2003Excel(File file) throws IOException {

        List<List<String>> list = new LinkedList<>();
        HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheet = hwb.getSheetAt(0);
        HSSFRow row = null;
        HSSFCell cell = null;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            //每行数据
            List<String> rowData = new ArrayList<>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    rowData.add(null);
                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date d = cell.getDateCellValue();
                        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        rowData.add(dateFormat.format(d).toString());
                    } else {
                        double value = cell.getNumericCellValue();
                        if (value == (long) cell.getNumericCellValue()) {
                            rowData.add(String.valueOf((long) cell.getNumericCellValue()));
                        } else {
                            rowData.add(cell.toString());
                        }
                    }
                } else {
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        rowData.add(cell.getStringCellValue());
                    } else {
                        double value = cell.getNumericCellValue();
                        if (value == (long) cell.getNumericCellValue()) {
                            rowData.add(String.valueOf((long) cell.getNumericCellValue()));
                        } else {
                            rowData.add(cell.toString());
                        }
                    }

                }
            }
            list.add(rowData);
        }
        return list;
    }

    /**
     * 兼容 2007excel
     *
     * @param file
     * @return
     * @throws IOException
     */
    private static List<List<String>> toRead2007Excel(File file) throws IOException {

        List<List<String>> list = new LinkedList<>();
        XSSFWorkbook hwb = new XSSFWorkbook(new FileInputStream(file));
        XSSFSheet sheet = hwb.getSheetAt(0);
        XSSFRow row = null;
        XSSFCell cell = null;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            //每行数据
            List<String> rowData = new ArrayList<>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    rowData.add(null);
                } else {
                    double value = cell.getNumericCellValue();
                    if (value == (long) cell.getNumericCellValue()) {
                        rowData.add(String.valueOf((long) cell.getNumericCellValue()));
                    } else {
                        rowData.add(cell.toString());
                    }
                }
            }
            list.add(rowData);
        }
        return list;
    }

    /**
     * @param file   从哪个文件读取
     * @param obj    需要解析成的类
     * @param titles 对应的字段属性
     * @param row    第几行开始解析数据
     * @param <T>    泛型
     * @return
     * @throws IOException
     */
    public static <T> List<T> getExcelData(File file, Class<T> obj, String[] titles, int row) throws IOException {
        String fileName = file.getName();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            List<List<String>> lists = toRead2003Excel(file);

            //这里对数据做一下处理   后面有几行空数据
            List<List<String>> handleDatas = new ArrayList<>();
            for (int i = 0; i < lists.size(); i++) {

                List<String> list = lists.get(i);
                int num = 0; //计数器
                for (String s : list) {
                    if (null == s || "0".equals(s)) {
                        num = num + 1;
                    }
                }
                //如果计数器的结果显示 不是 全为 null 或者 0 那么这组数据可用
                if (list.size() > num) {
                    handleDatas.add(lists.get(i));
                }
            }
            //赋值 处理后的数据
            lists = handleDatas;

            List<T> data = transformData(lists, obj, titles, row);
            return data;
        } else if ("xlsx".equals(extension)) {

            List<List<String>> lists = toRead2007Excel(file);
            List<T> data = transformData(lists, obj, titles, row);
            return data;
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /**
     * @param lists  数据
     * @param obj    需要解析成的类
     * @param titles 对应的字段属性
     * @param row    第几行开始解析数据
     * @param <T>    泛型
     * @return
     */
    public static <T> List<T> transformData(List<List<String>> lists, Class<T> obj, String[] titles, int row) {

        List<Map<String, String>> maps = new ArrayList<>();
        for (int i = row; i < lists.size(); i++) {
            HashMap<String, String> map = new HashMap<>();
            for (int j = 0; j < titles.length; j++) {
                if (lists.get(i).size() > j) {
                    map.put(titles[j], lists.get(i).get(j));
                } else {
                    map.put(titles[j], null);
                }
            }
            maps.add(map);
        }
        String pojos = JSON.toJSONString(maps);
        System.out.println(pojos);
        List<T> tList = JSONArray.parseArray(pojos, obj);
        return tList;
    }

    /**
     * 测试
     *
     * @param args
     */
    public static void main(String[] args) {


    }

}