excel 工具类

最新推荐文章于 2023-02-28 10:45:46 发布
最新推荐文章于 2023-02-28 10:45:46 发布
阅读量144 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_38316999/article/details/80305055
版权 
package com.stdsoft.wisdomh.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
/**
 * @author pengXiaoLin
 * @date 2018-03-16 10:38
 **/
public class ExcelUtil {

    private static HSSFWorkbook wb;

    private static CellStyle titleStyle;        // 标题行样式
    private static Font titleFont;              // 标题行字体
    private static CellStyle dateStyle;         // 日期行样式
    private static Font dataFont;               // 数据行字体
    private static CellStyle headStyle;         // 表头行样式
    private static Font headFont;               // 表头行字体
    private static CellStyle contentStyle;      // 内容行样式
    private static Font contentFont;            // 内容行字体

    /**
     * 将Map里的集合对象数据输出Excel数据流
     *
     * @param setInfo
     * @throws IOException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static void export2Excel(ExportSetInfo setInfo) throws IOException, IllegalArgumentException, IllegalAccessException {
        init();
        Set<Map.Entry<String, List>> set = setInfo.getObjsMap().entrySet();
        String[] sheetNames = new String[setInfo.getObjsMap().size()];
        int sheetNameNum = 0;
        for (Map.Entry<String, List> entry : set) {
            sheetNames[sheetNameNum] = entry.getKey();
            sheetNameNum++;
        }
        HSSFSheet[] sheets = getSheets(setInfo.getObjsMap().size(), sheetNames);
        int sheetNum = 0;
        for (Map.Entry<String, List> entry : set) {
            // Sheet
            List objs = entry.getValue();
            // 标题行
            //createTableTitleRow(setInfo, sheets, sheetNum);
            // 日期行
            //createTableDateRow(setInfo, sheets, sheetNum);
            // 表头
            creatTableHeadRow(setInfo, sheets, sheetNum);
            //sheets[sheetNum].createFreezePane( 0, 1, 0, 1 );// 冻结表头
            // 表体
            String[] fieldNames = setInfo.getFieldNames().get(sheetNum);
            //int rowNum = 3;
            int rowNum = 1;
            for (Object obj : objs) {
                HSSFRow contentRow = sheets[sheetNum].createRow(rowNum);
                contentRow.setHeight((short) 300);
                HSSFCell[] cells = getCells(contentRow, setInfo.getFieldNames().get(sheetNum).length);
                int cellNum = 1;                    // 去掉一列序号，因此从1开始
                if (fieldNames != null) {
                    for (int num = 0; num < fieldNames.length; num++) {
                        Object object = ReflectionUtils.invokeGetterMethod(obj, fieldNames[num]);
                        setObject2Cell(cells[cellNum], object);
                        cellNum++;
                    }
                }
                rowNum++;
            }
            adjustColumnSize(sheets, sheetNum, fieldNames); // 自动调整列宽
            sheetNum++;
        }
        if (!StringUtils.isEmpty(setInfo.getPath())) {// 存到本地文件中
            File file = new File(setInfo.getPath());
            if (!file.exists()) {
                file.mkdirs();
            }
            String realPath = setInfo.getPath() + "\\" + System.currentTimeMillis() + ".xls";
            File realFile = new File(realPath);
            if (realFile.exists()) {
                realFile.delete();
            }
            realFile.createNewFile();
            FileOutputStream fos = new FileOutputStream(realFile);
            wb.write(fos);
            setInfo.setPath(realPath);
        } else {// 返回输出流
            wb.write(setInfo.getOut());
        }
    }

    /**
     * 初始化
     *
     * @throws IOException
     */
    private static void init() throws IOException {
        wb = new HSSFWorkbook();

        titleFont = wb.createFont();
        titleStyle = wb.createCellStyle();
        dateStyle = wb.createCellStyle();
        dataFont = wb.createFont();
        headStyle = wb.createCellStyle();
        headFont = wb.createFont();
        contentStyle = wb.createCellStyle();
        contentFont = wb.createFont();

        initTitleCellStyle();
        initTitleFont();
        initDateCellStyle();
        initDataFont();
        initHeadCellStyle();
        initHeadFont();
        initContentCellStyle();
        initContentFont();
    }

    /**
     * 自动调整列宽
     *
     * @param sheets
     * @param sheetNum
     * @param fieldNames
     */
    private static void adjustColumnSize(HSSFSheet[] sheets, int sheetNum, String[] fieldNames) {
        for (int i = 0; i < fieldNames.length + 1; i++) {
            sheets[sheetNum].autoSizeColumn(i, true);
        }
    }

    /**
     * 创建标题行(需合并单元格)
     *
     * @param setInfo
     * @param sheets
     * @param sheetNum
     */
    private static void createTableTitleRow(ExportSetInfo setInfo, HSSFSheet[] sheets, int sheetNum) {
        CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0, setInfo.getFieldNames().get(sheetNum).length);
        sheets[sheetNum].addMergedRegion(titleRange);
        HSSFRow titleRow = sheets[sheetNum].createRow(0);
        titleRow.setHeight((short) 400);
        HSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(titleStyle);
        titleCell.setCellValue(setInfo.getTitles()[sheetNum]);
    }

    /**
     * 创建日期行(需合并单元格)
     *
     * @param setInfo
     * @param sheets
     * @param sheetNum
     */
    private static void createTableDateRow(ExportSetInfo setInfo, HSSFSheet[] sheets, int sheetNum) {
        CellRangeAddress dateRange = new CellRangeAddress(1, 1, 0, setInfo.getFieldNames().get(sheetNum).length);
        sheets[sheetNum].addMergedRegion(dateRange);
        HSSFRow dateRow = sheets[sheetNum].createRow(1);
        dateRow.setHeight((short) 350);
        HSSFCell dateCell = dateRow.createCell(0);
        dateCell.setCellStyle(dateStyle);
        dateCell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
    }

    /**
     * 创建表头行(需合并单元格)
     *
     * @param setInfo
     * @param sheets
     * @param sheetNum
     */
    private static void creatTableHeadRow(ExportSetInfo setInfo, HSSFSheet[] sheets, int sheetNum) {
        // 表头
        //HSSFRow headRow = sheets[sheetNum].createRow(2);
        HSSFRow headRow = sheets[sheetNum].createRow(0);
        headRow.setHeight((short) 350);
        // 序号列
        HSSFCell snCell = headRow.createCell(0);
        snCell.setCellStyle(headStyle);
        snCell.setCellValue("序号");
        // 列头名称
        for (int num = 1, len = setInfo.getHeadNames().get(sheetNum).length; num <= len; num++) {
            HSSFCell headCell = headRow.createCell(num);
            headCell.setCellStyle(headStyle);
            headCell.setCellValue(setInfo.getHeadNames().get(sheetNum)[num - 1]);
        }
    }

    /**
     * @throws Exception
     */
    public static void readFromExcel() throws Exception {
        //HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File("F:\\test\\12\\1521683795769.xls")));
        HSSFSheet sheet = null;

        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {// 获取每个Sheet表
            sheet = workbook.getSheetAt(i);
            for (int j = 0; j < sheet.getLastRowNum() + 1; j++) {// getLastRowNum，获取最后一行的行标
                HSSFRow row = sheet.getRow(j);
                if (row != null) {
                    for (int k = 0; k < row.getLastCellNum(); k++) {// getLastCellNum，是获取最后一个不为空的列是第几个
                        if (row.getCell(k) != null) { // getCell 获取单元格数据
                            switch (row.getCell(k).getCellType()) {
                                case Cell.CELL_TYPE_STRING:
                                    System.out.print(row.getCell(k).getStringCellValue() + "\t");
                                    break;
                                case Cell.CELL_TYPE_NUMERIC:
                                    if (HSSFDateUtil.isCellDateFormatted(row.getCell(k))) {// 是否是时间类型
                                        System.out.print(row.getCell(k).getDateCellValue() + "\t");
                                        break;
                                    }
                                    System.out.print(row.getCell(k).getNumericCellValue() + "\t");
                                    break;
                                case Cell.CELL_TYPE_FORMULA:// 公式类型
                                    System.out.print(row.getCell(k).getNumericCellValue() + "\t");
                                    break;
                                case Cell.CELL_TYPE_BOOLEAN:// boolean类型
                                    System.out.print(row.getCell(k).getBooleanCellValue() + "\t");
                                    break;
                                case Cell.CELL_TYPE_BLANK:// 空值
                                    System.out.print("NULL" + "\t");
                                    break;
                            }
                        } else {
                            System.out.print("\t");
                        }
                    }
                }
                System.out.println(""); // 读完一行后换行
            }
            System.out.println("读取sheet表：" + workbook.getSheetName(i) + " 完成");
        }
    }

    public static void main(String[] args) {
        try {
            readFromExcel();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建所有的Sheet
     *
     * @param num
     * @param names
     * @return
     */
    private static HSSFSheet[] getSheets(int num, String[] names) {
        HSSFSheet[] sheets = new HSSFSheet[num];
        for (int i = 0; i < num; i++) {
            sheets[i] = wb.createSheet(names[i]);
        }
        return sheets;
    }

    /**
     * 创建内容行的每一列(附加一列序号)
     *
     * @param contentRow
     * @param num
     * @return
     */
    private static HSSFCell[] getCells(HSSFRow contentRow, int num) {
        HSSFCell[] cells = new HSSFCell[num + 1];

        for (int i = 0, len = cells.length; i < len; i++) {
            cells[i] = contentRow.createCell(i);
            cells[i].setCellStyle(contentStyle);
        }
        // 设置序号列值，因为出去标题行和日期行，所有-2
        cells[0].setCellValue(contentRow.getRowNum());
        return cells;
    }

    /**
     * 初始化标题行样式
     */
    private static void initTitleCellStyle() {
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        titleStyle.setFont(titleFont);
        titleStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index);
    }

    /**
     * 初始化日期行样式
     */
    private static void initDateCellStyle() {
        dateStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
        dateStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        dateStyle.setFont(dataFont);
        dateStyle.setFillBackgroundColor(IndexedColors.SKY_BLUE.index);
    }

    /**
     * 设置单元格的内容
     *
     * @param cell
     * @param object
     */
    public static void setObject2Cell(HSSFCell cell, Object object) {
        if (null == object || object instanceof String) {
            cell.setCellValue(object == null ? "" : object.toString());
        } else if (object instanceof Integer) {
            //cell.getCellStyle().setDataFormat(HSSFDataFormat.getBuiltinFormat("#,#0"));
            cell.setCellValue((Integer) object);
        } else if (object instanceof Double) {
            cell.setCellValue((Double) object);
            //cell.getCellStyle().setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
        } else if (object instanceof Date || object instanceof java.sql.Date) {
            cell.setCellStyle(contentStyle);
            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(object));
        } else {
            cell.setCellValue(object == null ? "" : object.toString());
        }
    }

    /**
     * 初始化表头行样式
     */
    private static void initHeadCellStyle() {
        headStyle.setAlignment(CellStyle.ALIGN_CENTER);
        headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        headStyle.setFont(headFont);
        headStyle.setFillBackgroundColor(IndexedColors.YELLOW.index);
        headStyle.setBorderTop(CellStyle.BORDER_THIN);
        headStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headStyle.setBorderLeft(CellStyle.BORDER_THIN);
        headStyle.setBorderRight(CellStyle.BORDER_THIN);
        headStyle.setTopBorderColor(IndexedColors.BLUE_GREY.index);
        headStyle.setBottomBorderColor(IndexedColors.BLUE_GREY.index);
        headStyle.setLeftBorderColor(IndexedColors.BLUE_GREY.index);
        headStyle.setRightBorderColor(IndexedColors.BLUE_GREY.index);
    }

    /**
     * @Description: 初始化内容行样式
     */
    private static void initContentCellStyle() {
        contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
        contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        contentStyle.setFont(contentFont);
        contentStyle.setBorderTop(CellStyle.BORDER_THIN);
        contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
        contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
        contentStyle.setBorderRight(CellStyle.BORDER_THIN);
        contentStyle.setTopBorderColor(IndexedColors.BLUE_GREY.index);
        contentStyle.setBottomBorderColor(IndexedColors.BLUE_GREY.index);
        contentStyle.setLeftBorderColor(IndexedColors.BLUE_GREY.index);
        contentStyle.setRightBorderColor(IndexedColors.BLUE_GREY.index);
        contentStyle.setWrapText(true); // 字段换行
    }

    /**
     * @Description: 初始化标题行字体
     */
    private static void initTitleFont() {
        titleFont.setFontName("宋体");
        titleFont.setFontHeightInPoints((short) 12);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        titleFont.setCharSet(Font.DEFAULT_CHARSET);
        titleFont.setColor(IndexedColors.BLUE_GREY.index);
    }

    /**
     * @Description: 初始化日期行字体
     */
    private static void initDataFont() {
        dataFont.setFontName("宋体");
        dataFont.setFontHeightInPoints((short) 10);
        dataFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        dataFont.setCharSet(Font.DEFAULT_CHARSET);
        dataFont.setColor(IndexedColors.BLUE_GREY.index);
    }

    /**
     * @Description: 初始化表头行字体
     */
    private static void initHeadFont() {
        headFont.setFontName("宋体");
        headFont.setFontHeightInPoints((short) 10);
        headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headFont.setCharSet(Font.DEFAULT_CHARSET);
        headFont.setColor(IndexedColors.BLUE_GREY.index);
    }

    /**
     * @Description: 初始化内容行字体
     */
    private static void initContentFont() {
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short) 10);
        contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        contentFont.setCharSet(Font.DEFAULT_CHARSET);
        contentFont.setColor(IndexedColors.BLACK.index);
    }


    /**
     * 封装Excel导出的设置信息
     */
    public static class ExportSetInfo {

        /**
         * 数据对象
         */
        private LinkedHashMap<String, List> objsMap;

        /**
         * 对应每个sheet里的标题，即顶部大字
         */
        private String[] titles;
        /**
         * 对应每个页签的表头的每一列的名称
         */
        private List<String[]> headNames;

        /**
         * 对应每个sheet里的每行数据的对象的属性名称
         */
        private List<String[]> fieldNames;
        /**
         * Excel数据将输出到该输出流
         */
        private OutputStream out;
        /**
         * 文件地址
         */
        private String path;

        public String getPath() {
            return path;
        }

        public void setPath(String path) {
            this.path = path;
        }

        public LinkedHashMap<String, List> getObjsMap() {
            return objsMap;
        }

        public void setObjsMap(LinkedHashMap<String, List> objsMap) {
            this.objsMap = objsMap;
        }

        public List<String[]> getFieldNames() {
            return fieldNames;
        }

        public void setFieldNames(List<String[]> fieldNames) {
            this.fieldNames = fieldNames;
        }

        public String[] getTitles() {
            return titles;
        }

        public void setTitles(String[] titles) {
            this.titles = titles;
        }

        public List<String[]> getHeadNames() {
            return headNames;
        }

        public void setHeadNames(List<String[]> headNames) {
            this.headNames = headNames;
        }

        public OutputStream getOut() {
            return out;
        }

        public void setOut(OutputStream out) {
            this.out = out;
        }
    }

    public static class ReadSetInfo {
        /**
         * 对应每个页签的表头的每一列的名称
         */
        private List<String[]> headNames;

        /**
         * 对应每个sheet里的每行数据的对象的属性名称
         */
        private List<String[]> fieldNames;

        /**
         * 数据对象
         */
        private LinkedHashMap<String, List> objsMap;

        /**
         * 数据流
         */
        FileInputStream fileInputStream;

        public List<String[]> getHeadNames() {
            return headNames;
        }

        public void setHeadNames(List<String[]> headNames) {
            this.headNames = headNames;
        }

        public List<String[]> getFieldNames() {
            return fieldNames;
        }

        public void setFieldNames(List<String[]> fieldNames) {
            this.fieldNames = fieldNames;
        }

        public LinkedHashMap<String, List> getObjsMap() {
            return objsMap;
        }

        public void setObjsMap(LinkedHashMap<String, List> objsMap) {
            this.objsMap = objsMap;
        }

        public FileInputStream getFileInputStream() {
            return fileInputStream;
        }

        public void setFileInputStream(FileInputStream fileInputStream) {
            this.fileInputStream = fileInputStream;
        }
    }
}

 

package com.stdsoft.wisdomh.util;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;


public class ExportUtils {


    /**
     * 导出excel通用方法
     *
     * @param headers    excel头信息
     * @param contents   excel内容信息
     * @param fieldsName 内容映射实体字段名称
     * @return 文件路径（不包括服务器前缀路径）
     * @throws Exception
     */
    @Deprecated
    public static void exportExcel(String[] headers, Collection<?> contents, String[] fieldsName, OutputStream out) throws Exception {
        ExportUtils.exportExcel(headers, contents, fieldsName, false, out);
    }

    /**
     * 导出excel通用方法
     *
     * @param headers     excel头信息
     * @param contents    excel内容信息
     * @param fieldsName  内容映射实体字段名称
     * @param hiddenFirst 是否隐藏第一列
     * @return 文件路径（不包括服务器前缀路径）
     * @throws Exception
     */
    @Deprecated
    public static void exportExcel(String[] headers, Collection<?> contents, String[] fieldsName, boolean hiddenFirst, OutputStream out) throws Exception {
        //ByteArrayOutputStream out = new ByteArrayOutputStream();
        //生成文件名
        String fileName = UUID.randomUUID().toString() + ".xlsx";

        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet("sheet1");
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);

        //判断是否隐藏第一列
        if (hiddenFirst) {
            sheet.setColumnHidden(0, true);
        }
        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();

        /**如有特殊样式和字体，可在这里新增或扩展**/
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 生成一个字体
        XSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);

        //设置excel第一行头信息显示
        XSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //冻结第一行
        sheet.createFreezePane(0, 1, 0, 1);

        //遍历集合数据，产生数据行
        Iterator<?> it = contents.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Object obj = it.next();
            for (int i = 0; i < fieldsName.length; i++) {
                XSSFCell cell = row.createCell(i);
                String getMethodName = "get"
                        + fieldsName[i].substring(0, 1).toUpperCase()
                        + fieldsName[i].substring(1);
                Class<?> tCls = obj.getClass();
                Method getMethod = tCls.getMethod(getMethodName,
                        new Class[]{});
                Object value = getMethod.invoke(obj, new Object[]{});
                /**如获取值比较特殊，可在这里扩展值的转换方法**/
                if (value == null) {
                    value = new String("");
                }
                XSSFRichTextString richString = new XSSFRichTextString(value.toString());
                cell.setCellValue(richString);
            }
        }
        //生成文件夹路径
        /*String path = ExportUtils.newFilePath();
        File file = new File(path + fileName);
        OutputStream os = new FileOutputStream(file);
        workbook.write(os);
        os.close();

        return path + fileName;*/
        workbook.write(out);
        out.close();
        //return out;
    }


    /**
     * 导出客户信息 excel
     *
     * @param headers     excel头信息
     * @param contents    excel内容信息
     * @param fieldsName  内容映射实体字段名称
     * @param hiddenFirst 是否隐藏第一列
     * @return 文件路径（不包括服务器前缀路径）
     * @throws Exception
     */
    @Deprecated
    public static void exportCustomerExcel(String[] headers, Collection<?> contents, String[] fieldsName, boolean hiddenFirst, Map<Integer, List<String>> map, OutputStream out) throws Exception {
        //ByteArrayOutputStream out = new ByteArrayOutputStream();
        //生成文件名
        String fileName = UUID.randomUUID().toString() + ".xlsx";

        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet("sheet1");
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);

        //判断是否隐藏第一列
        if (hiddenFirst) {
            sheet.setColumnHidden(0, true);
        }

        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();

        /**如有特殊样式和字体，可在这里新增或扩展**/
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 生成一个字体
        XSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);

        //设置excel第一行头信息显示
        XSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        //遍历集合数据，产生数据行
        Iterator<?> it = contents.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Object obj = it.next();
            int i;
            for (i = 0; i < fieldsName.length; i++) {
                XSSFCell cell = row.createCell(i);
                String getMethodName = "get"
                        + fieldsName[i].substring(0, 1).toUpperCase()
                        + fieldsName[i].substring(1);
                Class<?> tCls = obj.getClass();
                Method getMethod = tCls.getMethod(getMethodName,
                        new Class[]{});
                Object value = getMethod.invoke(obj, new Object[]{});
                /**如获取值比较特殊，可在这里扩展值的转换方法**/
                if (value == null) {
                    value = new String("");
                }

                XSSFRichTextString richString = new XSSFRichTextString(value.toString());
                cell.setCellValue(richString);
            }

            String getMethodName = "getCustomerId";
            Class<?> tCls = obj.getClass();
            Method getMethod = tCls.getMethod(getMethodName,
                    new Class[]{});
            Object value = getMethod.invoke(obj, new Object[]{});
            List<String> stringList = map.get(Integer.parseInt(value.toString()));
            for (String str : stringList) {
                XSSFCell cell = row.createCell(i);
                XSSFRichTextString richString = new XSSFRichTextString(str);
                cell.setCellValue(richString);
                i++;
            }
        }
        //生成文件夹路径
        /*String path = ExportUtils.newFilePath();
        File file = new File(path + fileName);
        OutputStream os = new FileOutputStream(file);
        workbook.write(os);
        os.close();

        return path + fileName;*/
        workbook.write(out);
        out.close();
        //return out;
    }


    public static void exportExcel(String[] headers, Collection<?> contents, String[] fieldsName, boolean hiddenFirst, String exportName, OutputStream out) throws Exception {
        exportExcel(headers, contents, fieldsName, hiddenFirst, null, exportName, out);
    }

    /**
     * @param headers
     * @param contents
     * @param fieldsName
     * @param hiddenFirst
     * @param exportName
     * @param width
     * @return
     * @throws Exception
     */
    public static void exportExcelWidth(String[] headers, Collection<?> contents, String[] fieldsName, boolean hiddenFirst, String exportName, Map<Integer, Integer> width, OutputStream out) throws Exception {
        exportExcelByWidth(headers, contents, fieldsName, hiddenFirst, null, exportName, width, out);
    }

    public static void orderExportExcel(String[] headers, Collection<?> contents, String[] fieldsName, boolean hiddenFirst, String exportName, OutputStream out) throws Exception {
        exportExcel(headers, contents, fieldsName, hiddenFirst, null, exportName, out);
    }


    public static void main(String[] args) {

        // 声明一个工作薄
        try {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheetAt = workbook.createSheet();
            sheetAt.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
            sheetAt.addMergedRegion(new CellRangeAddress(0, 0, 1, 3));
            for (int i = 0; i < args.length; i++) {

            }

            //生成文件夹路径
            String path = ExportUtils.newFilePath();
            File file = new File(path + "export.xlsx");
            OutputStream os = new FileOutputStream(file);
            workbook.write(os);
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 桉模板导出excel
     *
     * @param templatePath 模板路径
     * @param dataBeginRow 数据起始行 从0开始
     * @param contents     结果集内容
     * @param fieldsName   结果集字段映射
     * @param exportName   导出文件名
     * @param title        要修改第一行的title，key表示第几行
     * @return
     * @throws InvocationTargetException
     * @throws IllegalArgumentException
     * @throws Exception
     */
    public static void exportByTemplate(String templatePath, Integer dataBeginRow, Collection<?> contents, String[] fieldsName, String exportName, Map<Integer, String> title, OutputStream out) throws Exception {
        //生成文件名
        //ByteArrayOutputStream out = new ByteArrayOutputStream();
        String fileName = exportName + "_" + DateUtil.formatTime(new Date().getTime(), "yyyyMMddHHmmssSSS") + ".xlsx";
        templatePath = "/moban/downloadExcel/template/" + templatePath;
        // 根据模板路径读取一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(templatePath));

        // 获取第一个表格
        XSSFSheet sheetAt = workbook.getSheetAt(0);

        for (Integer key : title.keySet()) {
            sheetAt.getRow(0).getCell(key).setCellValue(title.get(key));
        }
        boolean bool = true;
        //遍历集合数据，产生数据行
        if (null == contents) {
            bool = false;
        }
        Iterator<?> it = contents.iterator();
        int index = dataBeginRow;

        CellStyle decimalStyle = workbook.createCellStyle();
        XSSFDataFormat dataFormat = workbook.createDataFormat();
        decimalStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
        while (bool && it.hasNext()) {
            XSSFRow row = sheetAt.createRow(index);
            index++;
            Object obj = it.next();
            for (int i = 0; i < fieldsName.length; i++) {
                XSSFCell cell = row.createCell(i);
                String getMethodName = "get"
                        + fieldsName[i].substring(0, 1).toUpperCase()
                        + fieldsName[i].substring(1);
                Class<?> tCls = obj.getClass();
                Method getMethod = tCls.getMethod(getMethodName,
                        new Class[]{});
                Object value = getMethod.invoke(obj, new Object[]{});
                /**如获取值比较特殊，可在这里扩展值的转换方法**/
                if (value == null) {
                    value = new String("");
                }
                if (value instanceof Integer) {
                    cell.setCellValue((Integer) value);
                } else if (value instanceof BigDecimal) {
                    DecimalFormat df = new DecimalFormat("0.00");
                    cell.setCellValue(df.format(((BigDecimal) value).doubleValue()));
                    /*cell.setCellStyle(decimalStyle);
                    cell.setCellValue(((BigDecimal)value).doubleValue());*/
                } else if (value instanceof Date) {
                    CellStyle styleB = workbook.createCellStyle();
                    styleB.setDataFormat(workbook.createDataFormat().getFormat("m/d/yy"));
                    cell.setCellStyle(styleB);
                    cell.setCellValue((Date) value);
                } else {
                    XSSFRichTextString richString = new XSSFRichTextString(value.toString());
                    cell.setCellValue(richString);
                }
            }
        }
        //生成文件夹路径
        /*String path = ExportUtils.newFilePath();
        File file = new File(path + fileName);
        OutputStream os = new FileOutputStream(file);
        workbook.write(os);
        os.close();

        return path + fileName;*/
        workbook.write(out);
        out.close();
        //return out;
    }

    /**
     * 桉模板导出excel
     *
     * @param templatePath 模板路径
     * @param dataBeginRow 数据起始行 从0开始
     * @param contents     结果集内容
     * @param fieldsName   结果集字段映射
     * @param exportName   导出文件名
     * @return
     * @throws InvocationTargetException
     * @throws IllegalArgumentException
     * @throws Exception
     */
    public static void exportByTemplate(String templatePath, Integer dataBeginRow, Collection<?> contents, String[] fieldsName, String exportName, OutputStream out) throws Exception {
        //ByteArrayOutputStream out = new ByteArrayOutputStream();
        //生成文件名
        String fileName = exportName + "_" + DateUtil.formatTime(new Date().getTime(), "yyyyMMddHHmmssSSS") + ".xlsx";
        templatePath = "/moban/downloadExcel/template/" + templatePath;
        // 根据模板路径读取一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(templatePath));

        // 获取第一个表格
        XSSFSheet sheetAt = workbook.getSheetAt(0);

        //遍历集合数据，产生数据行
        Iterator<?> it = contents.iterator();
        int index = dataBeginRow;

        CellStyle decimalStyle = workbook.createCellStyle();
        XSSFDataFormat dataFormat = workbook.createDataFormat();
        decimalStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));
        while (it.hasNext()) {
            XSSFRow row = sheetAt.createRow(index);
            index++;
            Object obj = it.next();
            for (int i = 0; i < fieldsName.length; i++) {
                XSSFCell cell = row.createCell(i);
                String getMethodName = "get"
                        + fieldsName[i].substring(0, 1).toUpperCase()
                        + fieldsName[i].substring(1);
                Class<?> tCls = obj.getClass();
                Method getMethod = tCls.getMethod(getMethodName,
                        new Class[]{});
                Object value = getMethod.invoke(obj, new Object[]{});
                /**如获取值比较特殊，可在这里扩展值的转换方法**/
                if (value == null) {
                    value = new String("");
                }
                if (value instanceof Integer) {
                    cell.setCellValue((Integer) value);
                } else if (value instanceof BigDecimal) {
                    cell.setCellStyle(decimalStyle);
                    cell.setCellValue(((BigDecimal) value).doubleValue());
                } else if (value instanceof Date) {
                    CellStyle styleB = workbook.createCellStyle();
                    styleB.setDataFormat(workbook.createDataFormat().getFormat("m/d/yy"));
                    cell.setCellStyle(styleB);
                    cell.setCellValue((Date) value);
                } else {
                    XSSFRichTextString richString = new XSSFRichTextString(value.toString());
                    cell.setCellValue(richString);
                }
            }
        }
        //生成文件夹路径
        /*String path = ExportUtils.newFilePath();
        File file = new File(path + fileName);
        OutputStream os = new FileOutputStream(file);
        workbook.write(os);
        os.close();

        return path + fileName;*/
        workbook.write(out);
        out.close();
        // return out;
    }


    public static void exportExcel(String[] headers, Collection<?> contents, String[] fieldsName, boolean hiddenFirst, String exportType, String exportName, OutputStream out) throws Exception {
        //ByteArrayOutputStream out = new ByteArrayOutputStream();
        String export = null;
        if (exportType != null) {
            export = exportType + "_" + exportName;
        } else {
            export = exportName;
        }

        //生成文件名
        String fileName = export + "_" + DateUtil.formatTime(new Date().getTime(), "yyyyMMddHHmmssSSS") + ".xlsx";

        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet(export);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);

        //判断是否隐藏第一列
        if (hiddenFirst) {
            sheet.setColumnHidden(0, true);
        }
        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();

        /**如有特殊样式和字体，可在这里新增或扩展**/
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 生成一个字体
        XSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);

        //设置excel第一行头信息显示
        XSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //冻结第一行
        sheet.createFreezePane(0, 1, 0, 1);

        //遍历集合数据，产生数据行
        Iterator<?> it = contents.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Object obj = it.next();
            for (int i = 0; i < fieldsName.length; i++) {
                XSSFCell cell = row.createCell(i);
                String getMethodName = "get"
                        + fieldsName[i].substring(0, 1).toUpperCase()
                        + fieldsName[i].substring(1);
                Class<?> tCls = obj.getClass();
                Method getMethod = tCls.getMethod(getMethodName,
                        new Class[]{});
                Object value = getMethod.invoke(obj, new Object[]{});
                /**如获取值比较特殊，可在这里扩展值的转换方法**/
                if (value == null) {
                    value = new String("");
                }
                if (value instanceof Integer) {
                    cell.setCellValue((Integer) value);
                } else if (value instanceof BigDecimal) {
                    DecimalFormat df = new DecimalFormat("0.00");
                    cell.setCellValue(df.format(((BigDecimal) value).doubleValue()));
                    /*cell.setCellValue(((BigDecimal)value).doubleValue());*/
                } else if (value instanceof Date) {
                    CellStyle styleB = workbook.createCellStyle();
                    styleB.setDataFormat(workbook.createDataFormat().getFormat("m/d/yy"));
                    cell.setCellStyle(styleB);
                    cell.setCellValue((Date) value);
                } else {
                    XSSFRichTextString richString = new XSSFRichTextString(value.toString());
                    cell.setCellValue(richString);
                }
            }
        }
        //生成文件夹路径
       /* String path = ExportUtils.newFilePath();
        File file = new File(path + fileName);
        OutputStream os = new FileOutputStream(file);
        workbook.write(os);
        os.close();

        return path + fileName;*/

        workbook.write(out);
        out.close();
        //return out;
    }

    /**
     * @param headers
     * @param contents
     * @param fieldsName
     * @param hiddenCloumnIndex
     * @param exportType
     * @param exportName
     * @param out                     response的输出流
     * @param isByteArrayOutputStream 是否返回ByteArrayOutputStream输出流
     * @return
     * @throws Exception
     */
    public static ByteArrayOutputStream exportExcel(String[] headers, Collection<?> contents, String[] fieldsName, List<Integer> hiddenCloumnIndex, String exportType, String exportName, OutputStream out, boolean isByteArrayOutputStream) throws Exception {
        ByteArrayOutputStream _out = new ByteArrayOutputStream();
        InputStream excelStream = null;

        String export = null;
        if (exportType != null) {
            export = exportType + "_" + exportName;
        } else {
            export = exportName;
        }

        //生成文件名
        String fileName = export + "_" + DateUtil.formatTime(new Date().getTime(), "yyyyMMddHHmmssSSS") + ".xlsx";

        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet(export);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);

        //判断是否隐藏第一列
        if (hiddenCloumnIndex != null && !hiddenCloumnIndex.isEmpty()) {
            for (Integer i : hiddenCloumnIndex) {
                sheet.setColumnHidden(i, true);
            }
        }
        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();

        /**如有特殊样式和字体，可在这里新增或扩展**/
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 生成一个字体
        XSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);

        //设置excel第一行头信息显示
        XSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //冻结第一行
        sheet.createFreezePane(0, 1, 0, 1);

        //遍历集合数据，产生数据行
        Iterator<?> it = contents.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Object obj = it.next();
            for (int i = 0; i < fieldsName.length; i++) {
                XSSFCell cell = row.createCell(i);
                String getMethodName = "get"
                        + fieldsName[i].substring(0, 1).toUpperCase()
                        + fieldsName[i].substring(1);
                Class<?> tCls = obj.getClass();
                Method getMethod = tCls.getMethod(getMethodName,
                        new Class[]{});
                Object value = getMethod.invoke(obj, new Object[]{});
                /**如获取值比较特殊，可在这里扩展值的转换方法**/
                if (value == null) {
                    value = new String("");
                }
                if (value instanceof Integer) {
                    cell.setCellValue((Integer) value);
                } else if (value instanceof BigDecimal) {
                    DecimalFormat df = new DecimalFormat("0.00");
                    cell.setCellValue(df.format(((BigDecimal) value).doubleValue()));
                    /*cell.setCellValue(((BigDecimal)value).doubleValue());*/
                } else if (value instanceof Date) {
                    CellStyle styleB = workbook.createCellStyle();
                    styleB.setDataFormat(workbook.createDataFormat().getFormat("m/d/yy"));
                    cell.setCellStyle(styleB);
                    cell.setCellValue((Date) value);
                } else {
                    XSSFRichTextString richString = new XSSFRichTextString(value.toString());
                    cell.setCellValue(richString);
                }
            }
        }
        //生成文件夹路径
        /*String path = ExportUtils.newFilePath();
        File file = new File(path+fileName);
        OutputStream os = new FileOutputStream(file);
        workbook.write(os);
        os.close();
        return path + URLEncoder.encode(fileName, "utf-8");*/
        if (isByteArrayOutputStream) {
            workbook.write(_out);
            _out.close();
        }else{
            workbook.write(out);
            out.close();
        }

        return _out;
    }


    public static void exportExcelByWidth(String[] headers, Collection<?> contents, String[] fieldsName, boolean hiddenFirst, String exportType, String exportName, Map<Integer, Integer> width, OutputStream out) throws Exception {
        //ByteArrayOutputStream out = new ByteArrayOutputStream();
        String export = null;
        if (exportType != null) {
            export = exportType + "_" + exportName;
        } else {
            export = exportName;
        }


        //生成文件名
        String fileName = export + "_" + DateUtil.formatTime(new Date().getTime(), "yyyyMMddHHmmssSSS") + ".xlsx";

        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet(export);
        // 设置表格默认列宽度 汉字是512，数字是256.
        for (Integer key : width.keySet()) {
            sheet.setColumnWidth(key, width.get(key));
        }

        //判断是否隐藏第一列
        if (hiddenFirst) {
            sheet.setColumnHidden(0, true);
        }
        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();

        /**如有特殊样式和字体，可在这里新增或扩展**/
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        // 生成一个字体
        XSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);

        //设置excel第一行头信息显示
        XSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //冻结第一行
        sheet.createFreezePane(0, 1, 0, 1);

        //遍历集合数据，产生数据行
        Iterator<?> it = contents.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Object obj = it.next();
            for (int i = 0; i < fieldsName.length; i++) {
                XSSFCell cell = row.createCell(i);
                String getMethodName = "get"
                        + fieldsName[i].substring(0, 1).toUpperCase()
                        + fieldsName[i].substring(1);
                Class<?> tCls = obj.getClass();
                Method getMethod = tCls.getMethod(getMethodName,
                        new Class[]{});
                Object value = getMethod.invoke(obj, new Object[]{});
                /**如获取值比较特殊，可在这里扩展值的转换方法**/
                if (value == null) {
                    value = new String("");
                }
                if (value instanceof Integer) {
                    cell.setCellValue((Integer) value);
                } else if (value instanceof BigDecimal) {
                    DecimalFormat df = new DecimalFormat("0.00");
                    cell.setCellValue(df.format(((BigDecimal) value).doubleValue()));
                } else if (value instanceof Date) {
                    CellStyle styleB = workbook.createCellStyle();
                    styleB.setDataFormat(workbook.createDataFormat().getFormat("m/d/yy"));
                    cell.setCellStyle(styleB);
                    cell.setCellValue((Date) value);
                } else {
                    XSSFRichTextString richString = new XSSFRichTextString(value.toString());
                    cell.setCellValue(richString);
                }
            }
        }
        //生成文件夹路径
        /*String path = ExportUtils.newFilePath();
        File file = new File(path + fileName);
        OutputStream os = new FileOutputStream(file);
        workbook.write(os);
        os.close();

        return path + fileName;*/

        workbook.write(out);
        out.close();
        // return out;
    }

    public static void exportTemplatePointExcel(String[] headers, Collection<?> contents, String[] fieldsName, boolean hiddenFirst, String exportType, String exportName, OutputStream out) throws Exception {
        //ByteArrayOutputStream out = new ByteArrayOutputStream();
        String export = null;
        if (exportType != null) {
            export = exportType + "_" + exportName;
        } else {
            export = exportName;
        }

        //生成文件名
        String fileName = export + "_" + DateUtil.formatTime(new Date().getTime(), "yyyyMMddHHmmssSSS") + ".xlsx";

        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet(export);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);

        //判断是否隐藏第一列
        if (hiddenFirst) {
            sheet.setColumnHidden(0, true);
        }
        // 生成一个样式
        XSSFCellStyle style = workbook.createCellStyle();

        /**如有特殊样式和字体，可在这里新增或扩展**/
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 生成一个字体
        XSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);

        //设置excel第一行头信息显示
        XSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //冻结第一行
        sheet.createFreezePane(0, 1, 0, 1);

        //遍历集合数据，产生数据行
        Iterator<?> it = contents.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Object obj = it.next();
            for (int i = 0; i < fieldsName.length; i++) {
                XSSFCell cell = row.createCell(i);
                String getMethodName = "get"
                        + fieldsName[i].substring(0, 1).toUpperCase()
                        + fieldsName[i].substring(1);
                Class<?> tCls = obj.getClass();
                Method getMethod = tCls.getMethod(getMethodName,
                        new Class[]{});
                Object value = getMethod.invoke(obj, new Object[]{});
                /**如获取值比较特殊，可在这里扩展值的转换方法**/
                if (value == null) {
                    value = new String("");
                }
                if (value instanceof Integer) {
                    cell.setCellValue((Integer) value);
                } else if (value instanceof BigDecimal) {
                    XSSFCellStyle styleB = getCellStyleStatic(workbook);
                    styleB.setDataFormat(workbook.createDataFormat().getFormat("0.000000"));
                    cell.setCellStyle(styleB);
                    cell.setCellValue(((BigDecimal) value).doubleValue());
                } else {
                    XSSFRichTextString richString = new XSSFRichTextString(value.toString());
                    cell.setCellValue(richString);
                }
            }
        }
        //生成文件夹路径
        /*String path = ExportUtils.newFilePath();
        File file = new File(path + fileName);
        OutputStream os = new FileOutputStream(file);
        workbook.write(os);
        os.close();

        return path + fileName;*/
        workbook.write(out);
        out.close();
        //return out;
    }


    public static String newFilePath() {
        //当前日期
        Date date = new Date();
        //格式化并转换String类型
        String path = "F:/";
        //String path="/moban/downloadExcel/"+new SimpleDateFormat("yyyy-MM-dd").format(date)+"/";
        //创建文件夹
        File f = new File(path);
        if (!f.exists())
            f.mkdirs();
        return path;
    }


    /**
     * 获取excel列名
     *
     * @param index
     * @return
     */
    private static String getExcelColName(int index) {
        StringBuffer retStr = new StringBuffer();
        index--;
        char[] a = "a".toCharArray();
        int c = a[0];
        if (index < 26) {
            retStr.append((char) (c + index));
        } else {
            retStr.append((char) (c + (int) (index / 26) - 1)).append((char) (c + index % 26));
        }
        return retStr.toString();
    }

    private static XSSFCellStyle getCellStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        XSSFFont fontBody = workbook.createFont();
        fontBody.setFontName("宋体");
        fontBody.setFontHeightInPoints((short) 8);
        style.setFont(fontBody);
        return style;
    }

    private static XSSFCellStyle getCellStyleStatic(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFDataFormat format = workbook.createDataFormat();
        style.setDataFormat(format.getFormat("#,##0.00"));
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 8);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        return style;
    }

    public static void exportExcelForSheets(List<SheetVO> results, String exportName, OutputStream out) throws Exception {
        //ByteArrayOutputStream out = new ByteArrayOutputStream();
        String path = ExportUtils.newFilePath();
        //生成文件名
        String fileName = exportName + "_" + DateUtil.formatTime(new Date().getTime(), "yyyyMMddHHmmssSSS") + ".xlsx";
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();

        for (SheetVO sheetvo : results) {
            // 生成一个表格
            XSSFSheet sheet = workbook.createSheet(sheetvo.getSheetName());
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth((short) 15);
            // 生成一个样式
            XSSFCellStyle style = workbook.createCellStyle();

            /**如有特殊样式和字体，可在这里新增或扩展**/
            // 设置这些样式
            style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

            // 生成一个字体
            XSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.BLACK.index);
            font.setFontHeightInPoints((short) 12);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            // 把字体应用到当前的样式
            style.setFont(font);

            //设置excel第一行头信息显示
            XSSFRow row = sheet.createRow(0);
            List<String> headers = sheetvo.getHeader();
            for (short i = 0; i < headers.size(); i++) {
                XSSFCell cell = row.createCell(i);
                cell.setCellStyle(style);
                XSSFRichTextString text = new XSSFRichTextString(headers.get(i));
                cell.setCellValue(text);
            }
            //冻结第一行
            sheet.createFreezePane(0, 1, 0, 1);

            List<?> content = sheetvo.getContent();
            List<String> fieldName = sheetvo.getFieldName();
//          String contentClazzName = sheetvo.getContentClazzName();


            //遍历集合数据，产生数据行
            Iterator<?> it = content.iterator();
            int index = 0;
            while (it.hasNext()) {
                index++;
                row = sheet.createRow(index);
                Object obj = it.next();
                for (int i = 0; i < fieldName.size(); i++) {
                    XSSFCell cell = row.createCell(i);
                    String getMethodName = "get"
                            + fieldName.get(i).substring(0, 1).toUpperCase()
                            + fieldName.get(i).substring(1);
                    Class<?> tCls = obj.getClass();
                    Method getMethod;
                    getMethod = tCls.getMethod(getMethodName,
                            new Class[]{});
                    Object value = getMethod.invoke(obj, new Object[]{});
                    /**如获取值比较特殊，可在这里扩展值的转换方法**/
                    if (value == null) {
                        value = new String("");
                    }
                    if (value instanceof Integer) {
                        cell.setCellValue((Integer) value);
                    } else if (value instanceof BigDecimal) {
                        cell.setCellValue(((BigDecimal) value).doubleValue());
                    } else if (value instanceof Date) {
                        CellStyle styleB = workbook.createCellStyle();
                        styleB.setDataFormat(workbook.createDataFormat().getFormat("m/d/yy"));
                        cell.setCellStyle(styleB);
                        cell.setCellValue((Date) value);
                    } else {
                        XSSFRichTextString richString = new XSSFRichTextString(value.toString());
                        cell.setCellValue(richString);
                    }

                }
            }
        }
        //生成文件夹路径
        /*File file = new File(path + fileName);
        try (OutputStream os = new FileOutputStream(file)) {
            workbook.write(os);
        }
        return path + fileName;*/
        workbook.write(out);
        out.close();
        // return out;
    }

}

 

package com.stdsoft.wisdomh;

import com.stdsoft.wisdomh.util.ExcelUtil;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;

/**
 * @author pengXiaoLin
 * @date 2018-03-16 10:53
 **/
public class Test2 {
    public static LinkedHashMap<String, List> getExportData() {
        LinkedHashMap<String, List> map = new LinkedHashMap<String, List>();
        List<User> dataList = new ArrayList<User>();
        for (int i = 0; i < 2; i++) {
            User user = new User(i, "张三ffffffffffffffffff" + i, "深圳市", i + 1, i + 0.55555, new Date());
            dataList.add(user);
        }
        map.put("后台用户信息", dataList);
        map.put("后台用户信息2", dataList);
        return map;
    }

    public static InputStream getUserExcel() {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        List<String[]> headNames = new ArrayList<String[]>();
        headNames.add(new String[]{"Id", "姓名", "地址", "年龄", "工资", "日期"});
        headNames.add(new String[]{"Id", "姓名2", "地址2", "年龄2", "工资2", "日期"});

        List<String[]> fieldNames = new ArrayList<String[]>();
        fieldNames.add(new String[]{"id", "userName", "address", "age", "money", "date"});
        fieldNames.add(new String[]{"id", "userName", "address", "age", "money", "date"});

        ExcelUtil.ExportSetInfo setInfo = new ExcelUtil.ExportSetInfo();
        setInfo.setObjsMap(getExportData());
        setInfo.setFieldNames(fieldNames);

        setInfo.setTitles(new String[]{"测试表格1", "测试表格2"});

        setInfo.setHeadNames(headNames);
        setInfo.setOut(baos);
        setInfo.setPath("F:\\test\\12");

        // 将需要导出的数据输出到baos
        try {
            ExcelUtil.export2Excel(setInfo);
            System.out.println("文件地址:" + setInfo.getPath());
        } catch (IOException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return new ByteArrayInputStream(baos.toByteArray());
    }

    public static void main(String[] args) {
        getUserExcel();
    }
}

 

 


                

        

        

    




    

      

        

            

              
                
                  weixin_38316999
                
              
            

            

                关注
              
            

        

        

          
    
            
  • 
              
                
                
                
                
                      0
                
              
              
    点赞
    
            
    • 
            
  • 
              
                
                
                
              
              
    踩
    
            
    • 
            
  • 
              
                
                
                
                
                    0
                
              
              
    
                
    
                  
                    收藏
                  
                
    
              
    
              
    
                
    
                  觉得还不错?
                  
                    一键收藏
                  
                 
                
    
              
    
            
    • 
          
  • 
            
    
              
              
            
    
              
              
              
                    0
              
            
            
    评论
    
          
    • 
          
  • 
          
    • 
          
  • 
            
              
            
              
    
            
              
                
            
    
          
    • 
        

      

      










                



	

		

			

				
					
Excel工具类

				
			

			

				

					01-16
				

			

		

		

			
				
配合它 使用：https://blog.csdn.net/youngyouth/article/details/86510724

竟然不能免费下载，LJ CSDN

			
		

	



                

              
                



	

		

			

				
					
操作Excel工具类：ExcelUtils.java

				
			

			

				

					直到日落的博客
				

				

					08-19
					
					8819
					
				

			

		

		

			
				
项目中，时常会需要导入导出Excel的需求，因此我专门花时间设计了工具类。

所需依赖
        <!-- 处理xls或xlsx格式的Excel表格导入导出的依赖 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <versio.

			
		

	



                


  
              

                


	

		

			

				
					
excel工具类

				
			

			

				

					weixin_52446536的博客
				

				

					02-28
					
					134
					
				

			

		

		

			
				
excel工具类

			
		

	





	

		

			

				
					
Excel工具类.zip

				
			

			

				

					04-02
				

			

		

		

			
				
在IT行业中，Excel工具类是常见的一种实用程序，特别是在数据处理和分析的场景下。SpringBoot框架结合Excel工具类，可以方便地实现从数据库中获取数据并导出为Excel文件，便于用户查看、分析或者进一步处理。这个...

			
		

	



		

			
		



	

		

			

				
					
java使用POI导出 Excel工具类

				
			

			

				

					08-14
				

			

		

		

			
				
java使用POI导出 Excel+图片工具类 ，里面含有poi jar包，只调用接口即可直接保存Excel。使用的时候需先把数据封装，具体包装需根据实际导出数据进行处理。文件demo中只提供包装格式。

			
		

	





	

		

			

				
					
Java poi导入导出EXCEL工具类（兼容各版本）

				
			

			

				

					08-21
				

			

		

		

			
				
概述：Java poi导入导出EXCEL工具类（兼容各版本）  一、功能说明  允许同时导入或导出多个sheet，同一sheet可同时存在多个数据块，按数据块划分处理数据。 二、配置文件示例及详细说明  1、导入xml配置示例如下（见...

			
		

	





	

		

			

				
					
java操作excel工具类

				
			

			

				

					11-15
				

			

		

		

			
				
Java操作Excel工具类是开发过程中常见的一种需求，尤其是在数据导入导出、数据分析或者报表生成的场景下。在Java中，我们可以使用多种库来处理Excel文件，例如Apache POI、JExcelAPI、OpenCSV等。本篇文章将重点介绍...

			
		

	





	

		

			

				
					
Excel工具类ExcelUtil 

				
			

			

				

					04-19
				

			

		

		

			
				
工具类JAVA代码
博文链接：https://uule.iteye.com/blog/1864863

			
		

	





	

		

			

				
					
java中导出数据到Excel

				
			

			

				

					peng_huaiyang的博客
				

				

					06-02
					
					414
					
				

			

		

		

			
				
最近在项目中用到了数据导出到Excel功能，虽然不难，但觉得很多没有做过的程序猿们 ，应该还是会有些迷茫的。所以在这里和大家分享一下 。


List list = new ArrayList();
list = Service.findALLByQueryParas();
ExcelUtil aaa = new ExcelUtil(T.class);
OutputStream os 

			
		

	





	

		

			

				
					
一个Excel工具类

				
			

			

				

					程高伟
				

				

					10-17
					
					563
					
				

			

		

		

			
				
在工作中有很多场景需要读取excel，导出excel
搜了是这位大佬写的excel工具类，
https://blog.csdn.net/l1028386804/article/details/79659605
在工作中遇到了一点问题进行了优化，
主要包括
读取excel：统一返回二位数组，（日期，数字格式的处理）
导出excel：自适应宽度，科学计数法优化（2003），导出这里只优化了2003，因...

			
		

	





	

		

			

				
					
excel工具类（EasyExcel）

				
			

			

				

					qq_50105506的博客
				

				

					11-24
					
					361
					
				

			

		

		

			
				
package com.briup.server.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.suppor

			
		

	





	

		

			

				
					
springboot导出excel工具类

					
最新发布

				
			

			

				

					07-28
				

			

		

		

			
				
- *2* [SpringBoot导出Excel工具类](https://blog.csdn.net/qq_41341640/article/details/109067688)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_...

			
		

	



              




          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值