JAVA工具类系列之Excel

package com.device.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Font;

import java.io.File;
import java.io.FileOutputStream;
import java.util.*;

;

/**
 * @Author: zsh
 * @Date: 2019/11/27 15:26
 * @Description:
 * @DateTime: 15:26
 */
public class MyExcelUtil {

    /**
     * @author: zsh
     * @Descirpt: 将数据写入到excle文件中，格式为.xls
     * @date: 2019/11/27 15:27
     */
    public static File excelExport(List<Map<String, String>> param, String sheetName) {
        try {
            Map<String, String> cMap = System.getenv();
            File tempFile = new File(new File(System.getProperty("java.io.tmpdir")),
                    UUID.randomUUID().toString() + ".xlsx");
            tempFile.createNewFile();
            FileOutputStream xlsxFileOutputStream = new FileOutputStream(tempFile);
            List<List<String>> listList = new ArrayList<>();
            List<String> headList = new ArrayList<>();
            for (int i = 0; i < param.size(); i++) {
                Map<String, String> map = param.get(i);
                List<String> list = new ArrayList<>();
                Set<Map.Entry<String, String>> entries = map.entrySet();
                Iterator<Map.Entry<String, String>> iterator = entries.iterator();
                while (iterator.hasNext()) {
                    Map.Entry<String, String> next = iterator.next();
                    if (i == 0) {
                        headList.add(next.getKey());
                    }

                    list.add(next.getValue());
                }
                listList.add(list);
            }

            HSSFWorkbook fworkbook = new HSSFWorkbook();
            HSSFSheet sheet = fworkbook.createSheet(sheetName + "_0");
            sheet.setActive(true);

            HSSFCellStyle cellStyle = fworkbook.createCellStyle();
            Font font = fworkbook.createFont();
            font.setFontHeightInPoints((short) 16);
            font.setFontName("宋体");
            font.setBold(true);
            cellStyle.setFont(font);

            int sheetnumber = 0;
            HSSFSheet crrsheet = sheet;
            Integer listsize = listList.size();
            for (int j = 0; j < listsize; j++) {
                boolean flag = false;
                if ((j / 60000) > sheetnumber) {
                    flag = true;
                    sheetnumber++;
                    crrsheet = fworkbook.createSheet(sheetName + "_" + sheetnumber);
                    crrsheet.setActive(true);
                }
                if (flag || j == 0) {
                    crrsheet.setColumnWidth(0, 4000);
                    HSSFRow row = crrsheet.createRow(0);
                    row.setHeightInPoints(25);
                    for (int n = 0; n < headList.size(); n++) {
                        HSSFCell cell = row.createCell(n);
                        cell.setCellValue(headList.get(n));
                        cell.setCellStyle(cellStyle);
                    }
                }
                crrsheet.setColumnWidth(j, 4000);
                HSSFRow row = crrsheet.createRow(j - sheetnumber * 60000 + 1);

                row.setHeightInPoints(25);
                for (int n = 0; n < headList.size(); n++) {
                    HSSFCell cell = row.createCell(n);
                    cell.setCellValue(listList.get(j).get(n));
                }
            }

            fworkbook.write(xlsxFileOutputStream);
            fworkbook.close();
            return tempFile;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * @author: zsh
     * @param: * @param param list的map，sheetname和文件名
     * @Descirpt: 将数据写入到excle文件中，格式为.xls，支持续写，不过这个续写是重复的读写，效率比较低，
     * 在数量多的时候不建议使用
     * @date: 2019/11/27 15:27
     */
    public static File write(List<Map<String, String>> param, String sheetName) {
        try {
            Map<String, String> cMap = System.getenv();
            File tempFile = new File(new File(System.getProperty("java.io.tmpdir")),
                    UUID.randomUUID().toString() + ".xlsx");
            tempFile.createNewFile();
            FileOutputStream xlsxFileOutputStream = new FileOutputStream(tempFile);
            List<List<String>> listList = new ArrayList<>();
            List<String> headList = new ArrayList<>();
            for (int i = 0; i < param.size(); i++) {
                Map<String, String> map = param.get(i);
                List<String> list = new ArrayList<>();
                Set<Map.Entry<String, String>> entries = map.entrySet();
                Iterator<Map.Entry<String, String>> iterator = entries.iterator();
                while (iterator.hasNext()) {
                    Map.Entry<String, String> next = iterator.next();
                    if (i == 0) {
                        headList.add(next.getKey());
                    }

                    list.add(next.getValue());
                }
                listList.add(list);
            }

            HSSFWorkbook fworkbook = new HSSFWorkbook();
            HSSFSheet sheet = fworkbook.createSheet(sheetName);
            sheet.setActive(true);

            HSSFCellStyle cellStyle = fworkbook.createCellStyle();
            Font font = fworkbook.createFont();
            font.setFontHeightInPoints((short) 16);
            font.setFontName("宋体");
            font.setBold(true);
            cellStyle.setFont(font);

            for (int j = 0; j < listList.size(); j++) {
                sheet.setColumnWidth(j, 4000);
                HSSFRow row = sheet.createRow(j);

                row.setHeightInPoints(25);
                for (int n = 0; n < headList.size(); n++) {
                    HSSFCell cell = row.createCell(n);
                    if (j == 0) {
                        cell.setCellValue(headList.get(n));
                        cell.setCellStyle(cellStyle);
                    } else {
                        cell.setCellValue(listList.get(j).get(n));
                    }
                }
            }

            fworkbook.write(xlsxFileOutputStream);
            fworkbook.close();
            return tempFile;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * @author: zsh
     * @Descirpt: 比较两个list是否相同
     * @date: 2019/11/27 15:29
     */
    public static boolean dif2List(List<String> l1, List<String> l2) {
        for (int i = 0; i < l1.size(); i++) {
            if (!l1.get(i).equals(l2.get(i))) {
                return false;
            }
        }
        return true;
    }

//    /**
//     * @author: zsh
//     * @Descirpt: 读取excel文件
//     * @date: 2019/11/27 15:35
//     */
//    public static List<Map<String, String>> readExcel(String filePath) {
//        try {
//            Workbook wb = null;
//            Sheet sheet = null;
//            Row row = null;
//            List<Map<String, String>> list = new ArrayList<>();
//            if (checkRoute(filePath)) {
//                list = dealExcel(filePath, wb, sheet, row);
//                return list;
//            }
//            return null;
//        } catch (IOException e) {
//            return null;
//        }
//    }

    /**
     * @author: zsh
     * @Descirpt: 检测路径，文件格式是否正确
     * @date: 2019/11/27 15:31
     */
    public static boolean checkRoute(String path) {
        File file = new File(path);
        if (file.exists()) {
            String type = path.substring(path.lastIndexOf("."));
            if (!".xls".equals(type) && !".xlsx".equals(type)) {
                System.out.println("文件名称既不是xls又不是xlsx，此文件不能进行处理！");
                return false;
            }
            return true;
        } else {
            System.out.println("文件不存在！");
            return false;
        }
    }

}