EXCEL导出代码全套2

package com.xxx.util.ExcelUtil;

import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;

public class A {

    @SuppressWarnings("rawtypes")
    public static void main(String[] args) throws IOException, NoSuchMethodException, SecurityException,
            IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        // int[] width = { 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000 }; //列宽限定
        // String[] head = { "列11", "列22", "列33", "列44", "列5", "列6", "列7", "列8" }; //
        @SuppressWarnings("rawtypes")
        List<ExcelUser> list = new ArrayList<ExcelUser>();
        for (int i = 0; i < 100000; i++) {
            list.add(new ExcelUser("姓名", 12, "住址", '男'));
        }
        ExcelExport2007.getExcelExport(list);
        // long s = System.currentTimeMillis();
        // ExportExcelUtil<ExcelUser> util = new ExportExcelUtil<ExcelUser>();
        // // 准备数据
        // List<ExcelUser> list = new ArrayList<>();
        // ExcelUser a = new ExcelUser("", 12, "abc", '男');
        // for (int i = 0; i < 10000; i++) {
        // list.add(a);
        // }
        // String[] columnNames = { "ID", "姓名", "性别" };
        // util.exportExcel("用户导出", columnNames, list, new FileOutputStream("E:/test.xlsx"), "");
        // System.out.println("运行时间:" + (System.currentTimeMillis() - s));
        // // File file = new File("E:/test.xlsx");
        // ArrayList<ExcelUser> list = new ArrayList<>();
        // ExcelUser a = new ExcelUser("", 12, "abc", '男');
        // for (int i = 0; i < 100000; i++) {
        // list.add(a);
        // }
        // Timestamp nowTimestamp = new Timestamp(new Date().getTime());
        // System.err.println("-----------------------------2007 100W导数开始时间：------------------------\n" + nowTimestamp);
        // SXSSFWorkbook workBook = new SXSSFWorkbook();
        // // 创建HSSFWorkbook对象(excel的文档对象) POI要操作excel 2007及以上的版本需要使用XSSF来代替上面代码的HSSF。
        // // XSSFWorkbook workBook = new XSSFWorkbook();
        // // 建立新的sheet对象（excel的表单）
        // Sheet sheet = workBook.createSheet("Excel 2007导出"); // 创建Excel工作表（页签）
        // Iterator<ExcelUser> it = list.iterator();
        // ExcelUser t = it.next();
        // Class tCls = t.getClass();
        // Method getMethod = tCls.getMethod("ExcelField");
        // LinkedHashMap value = (LinkedHashMap) getMethod.invoke(t, new Object[] {});
        // FileInputStream in = null;
        // try {
        // in = new FileInputStream(file);
        // List<ExcelHead> excelHeads = new ArrayList<ExcelHead>();
        // ExcelHead excelHead = new ExcelHead("姓名", "name");
        // ExcelHead excelHead1 = new ExcelHead("性别", "sex");
        // ExcelHead excelHead2 = new ExcelHead("年龄", "age");
        // ExcelHead excelHead3 = new ExcelHead("地址", "address", true);
        // excelHeads.add(excelHead);
        // excelHeads.add(excelHead1);
        // excelHeads.add(excelHead2);
        // excelHeads.add(excelHead3);
        // List<ExcelUser> list = ExcelUtils.readExcelToEntity(ExcelUser.class, in, file.getName(), excelHeads);
        // for (ExcelUser excelUser : list) {
        // System.out.println(excelUser.getName() + ":" + excelUser.getSex() + ":" + excelUser.getAge() + ":"
        // + excelUser.getAddress());
        // }
        // } catch (Exception e) {
        // e.printStackTrace();
        // } finally {
        // if (in != null) {
        // try {
        // in.close();
        // } catch (Exception e) {
        // e.printStackTrace();
        // }
        // }
        // }
    }

    private static void getExcelExport(int[] width, String[] head, ArrayList<ExcelUser> list, String string) {
        // TODO Auto-generated method stub
    }
}

package com.xxx.util.ExcelUtil;

/**  
 * @Title:  ExcelExport2007.java   
 * @Package com.kingdee.eas.custom.test.client     
 * @author: 谈荣涛   
 * @date:   2018-12-19 下午02:37:46   
 * @version V1.0 
 * @Description:    TODO(用一句话描述该文件做什么) 
 */
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Set;
import javax.swing.JFileChooser;
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.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

/**
 * 
* @ClassName: ExcelExport2007
* @Description: TODO(这里用一句话描述这个类的作用)
* @author XEChen
* @date 2019年3月11日 下午4:01:55
*
 */
public class ExcelExport2007 {

    /**
     * @throws InvocationTargetException 
     * @throws IllegalArgumentException 
     * @throws IllegalAccessException 
     * @throws SecurityException 
     * @throws NoSuchMethodException 
     * 
    * @Title: getExcelExport
    * @Description: 导出EXCEL
    * @author XEChen
    * @param width 列宽数组
    * @param head 列标数据
    * @param list 数据集合
    * @param biao 第一行标题
    * @throws
     */
    @SuppressWarnings("unchecked")
    public static void getExcelExport(List<?> dataset) throws NoSuchMethodException, SecurityException,
            IllegalAccessException, IllegalArgumentException, InvocationTargetException {
        Timestamp nowTimestamp = new Timestamp(new Date().getTime());
        System.err.println("-----------------------------2007 100W导数开始时间：------------------------\n" + nowTimestamp);
        SXSSFWorkbook workBook = new SXSSFWorkbook();
        // 创建HSSFWorkbook对象(excel的文档对象) POI要操作excel 2007及以上的版本需要使用XSSF来代替上面代码的HSSF。
        // XSSFWorkbook workBook = new XSSFWorkbook();
        // 建立新的sheet对象（excel的表单）
        Sheet sheet = workBook.createSheet("Excel 2007导出"); // 创建Excel工作表（页签）
        // 获取EXCEL的设置
        Iterator<?> it = dataset.iterator();
        Object t = it.next();
        Class<?> tCls = t.getClass();
        Method getMethod = tCls.getMethod("ExcelField");
        LinkedHashMap<String, Object> value = (LinkedHashMap<String, Object>) getMethod.invoke(t, new Object[] {});
        // 设置列宽
        int[] width = (int[]) value.get("width");
        for (int i = 0; i < width.length; i++) {
            sheet.setColumnWidth(i, width[i]); // 设置列宽
        }
        value.remove("width");
        // 设置标题
        Row title = sheet.createRow(0); // 创建标题行
        String excelName = value.get("title").toString();
        title.createCell(0).setCellValue(excelName); // 给标题行单元格赋值
        getTitleStyle(workBook, title); // 创建并初始化标题样式
        value.remove("title");
        // 设置抬头
        Set<String> keys = value.keySet();
        String[] head = new String[keys.size()];
        String[] columns = new String[keys.size()];
        int temp = 0;
        for (String key : keys) {
            columns[temp] = key;
            head[temp] = value.get(key).toString();
            temp++;
        }
        // 合并单元格 构造参数依次为起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, head.length));
        InitExcelHead(workBook, sheet, head); // 初始化抬头和样式
        int index = 0;
        String getMethodName = "";
        while (it.hasNext()) {
            index++;
            Row row = sheet.createRow(index + 1); // 行
            t = it.next();
            for (int j = 0; j < columns.length; j++) {
                getMethodName = "get" + columns[j];
                tCls = t.getClass();
                getMethod = tCls.getMethod(getMethodName, new Class[] {});
                Object o = getMethod.invoke(t, new Object[] {});
                String val = o == null ? "" : o.toString();
                row.createCell(j).setCellValue(val); // 单元值
            }
        }
        excelExport(workBook, excelName); // 导出处理
        Timestamp nowTimestamp1 = new Timestamp(new Date().getTime());
        System.err.println("-----------------------------2007 100W导数结束时间：------------------------\n" + nowTimestamp1);
    }

    /**
     * 标题样式
     * @param workbook
     * 创建并初始化标题样式
     * 
     */
    public static void getTitleStyle(SXSSFWorkbook workbook, Row title) {
        CellStyle style = workbook.createCellStyle(); // 创建样式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 字体居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        Font font = workbook.createFont(); // 创建字体样式
        font.setFontName("宋体"); // 字体
        font.setFontHeightInPoints((short) 16); // 字体大小
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
        style.setFont(font); // 给样式指定字体
        title.getCell(0).setCellStyle(style); // 给标题设置样式
    }

    /**   
     * @param head 
     * @param fields 
     * @Title: CreateExcelHead   
     * @Description: TODO(初始化Excel表头)   
     * @param:       
     * @return: void      
     * @throws   
     */
    private static Row InitExcelHead(SXSSFWorkbook workBook, Sheet sheet, String[] head) {
        Row row = sheet.createRow(1);
        CellStyle style = getHeaderStyle(workBook); // 获取表头样式
        for (int i = 0; i < head.length; i++) {
            row.createCell(i).setCellValue(head[i]);
            row.getCell(i).setCellStyle(style); // 设置标题样式
        }
        return row;
    }

    /**   
     * @Title: setExcelValue   
     * @Description: TODO( excel正文内容的填充 )   
     * @param: @param sheet  Excel页签对象名      
     * @return: void      
     * @throws   
     */
    private static void setExcelValue(Sheet sheet, String[] head, String val) {
        // sheet.createRow(i+2) 2003excel参数里面的类型是int，所以一次只能导出65535条数据
    }

    /**   
     * @Title: excelExport   
     * @Description: TODO(excel导出类)   
     * @param: @param sheet      
     * @return: void      
     * @throws   
     */
    private static void excelExport(SXSSFWorkbook workBook, String ExcelName) {
        String filePath = getSavePath(); // 获取文件保存路径
        if (filePath == null) {
            // SysUtil.abort(); // 终止程序
        }
        String srcFile = filePath + "\\" + ExcelName + ".xlsx";
        FileOutputStream fileOut = null;
        try {
            File file = new File(srcFile);
            if (file.exists()) { // 当文件已存在时
                // 删除原Excel 打开新导出的Excel时，最好刷新下当前文件夹，以免重复操作有时出现缓存。
                file.delete();
            }
            fileOut = new FileOutputStream(file);
            workBook.write(fileOut);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            // MsgBox.showError(e.getMessage());
        } catch (IOException e) {
            e.printStackTrace();
            // MsgBox.showError(e.getMessage());
        } finally {
            try {
                fileOut.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**   
     * @Title: getSavePath   
     * @Description: TODO(获取文件保存路径)   
     * @param: @return      
     * @return: String      
     * @throws   
     */
    private static String getSavePath() {
        // 选择保存路径
        String selectPath = null;
        JFileChooser chooser = new JFileChooser();
        chooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);// 设置只能选择目录
        int returnVal = chooser.showOpenDialog(null);
        if (returnVal == JFileChooser.APPROVE_OPTION) {
            selectPath = chooser.getSelectedFile().getPath();
        }
        return selectPath;
    }

    /**
     * 获取Excel表头样式(第二行)
     * @param workbook
     * @return
     */
    public static CellStyle getHeaderStyle(SXSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); // 下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); // 左边框
        style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
        style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        style.setTopBorderColor(HSSFColor.BLACK.index); // 上边框颜色
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        Font font = workbook.createFont(); // 创建字体样式
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 14); // 字体大小
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
        style.setFont(font); // 给样式指定字体
        return style;
    }
}

package com.xxx.util.ExcelUtil;

import java.util.LinkedHashMap;

public interface ExcelInitialization {

    /**
     * 
    * @Title: ExcelField
    * @Description: 获取属性字段及字段名称
    * @author XEChen
    * @return
    * @throws
     */
    // 标题行 title
    // EXCEL第一为标题
    // map.put("title", "测试");
    // EXCEL第二行为字段显示名称
    // map.put("Name", "姓名");// 利用反射，字段首字母大写，后面不用转换直接拼接
    // EXCEL行宽限定
    LinkedHashMap<String, Object> ExcelField();
}

package com.xxx.util.ExcelUtil;

import java.util.LinkedHashMap;

public class ExcelUser implements ExcelInitialization {

    private String name;
    private Integer age;
    private String address;
    private Character sex;

    @Override
    public LinkedHashMap<String, Object> ExcelField() {
        LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();
        map.put("Name", "姓名");
        map.put("Age", "年龄");
        map.put("Address", "住址");
        map.put("Sex", "性别");
        map.put("width", new int[] { 2000, 2000, 2000, 2000 }); // 个数跟EXCEL要导出属性个数相同
        // EXCEL第一行
        map.put("title", "测试");
        return map;
    }

    public ExcelUser() {
        super();
    }

    public ExcelUser(String name, Integer age, String address, Character sex) {
        super();
        this.name = name;
        this.age = age;
        this.address = address;
        this.sex = sex;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Character getSex() {
        return sex;
    }

    public void setSex(Character sex) {
        this.sex = sex;
    }
}