poi excel导入导出

 

pom

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        
        <dependency>
            <groupId>com.monitorjbl</groupId>
            <artifactId>xlsx-streamer</artifactId>
            <version>1.2.1</version>
        </dependency>

 

 

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import com.chinaway.g7s.common.G7sException;
import com.chinaway.g7s.service.BaseService;
import com.chinaway.intelli.intelli_core.common.ErrorEnum;
import com.github.pagehelper.PageInfo;
import com.monitorjbl.xlsx.StreamingReader;

/**
 * excel导入导出工具类
 * @author shihaiming
 *
 */
public class ExcelUtil {
    
     private static Logger log = LoggerFactory.getLogger(ExcelUtil.class);
     
    /**
     * @param filePath      文件保存绝对路径+文件名称
     * @param params        分页查询数据的条件
     * @param baseService   相关service
     * @param cls           相关实体类
     * @param titles        excel表头
     * @param headFields    导出的实体类的字段名
     * @param isDownload    false时不弹出下载文件，为true时浏览器会弹出下载文件
     * @throws Exception
     */
    public static void exportExcel(String filePath, Map<String, Object> params, BaseService baseService, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception {
        long startTime = System.currentTimeMillis(); // 开始时间
        Sheet sheet = null; // 工作表对象
        Row nRow = null; // 行对象
        Cell nCell = null; // 列对象

        int pageSize = 10000; //每次查询的数据量
        params.put("pageSize", pageSize);
        params.put("pageNum", 1);
        List list = baseService.findByPage(params);
        
        PageInfo pageInfo = new PageInfo(list);
        List dataList = pageInfo.getList();
        List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls);
        
        // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。
        Workbook wb = new SXSSFWorkbook(500); // 关键语句
        Font font = wb.createFont();
        font.setBold(true);
        CellStyle headCellStyle = wb.createCellStyle();
        headCellStyle.setFont(font);
                
        int total = (int) pageInfo.getTotal();
        //每个sheet保存多少行
        int sheetNum = 1000000;
        int rowNo = 0; // 总行号
        int pageRowNo = 0; // 页行号
        
        // 根据行数求数据提取次数  
        int export_times = total % pageSize > 0 ? total / pageSize  + 1 :total / pageSize;  
        
        if(export_times > 0){
            for (int i = 1; i <= export_times; i++) {  
                for (int n = 0; n < dataMap.size(); n++) {
                    //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
                       if(rowNo%sheetNum==0){
                           sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
                           sheet = wb.getSheetAt(rowNo/sheetNum);        //动态指定当前的工作表
                           pageRowNo = 0;        //每当新建了工作表就将当前工作表的行号重置为0
                           
                           Row row = sheet.createRow(pageRowNo++);    //新建行对象
                           Cell cell = row.createCell(0);
                           for (int k = 0; k < titles.length; k++) {
                               cell = row.createCell(k);
                               cell.setCellStyle(headCellStyle);  
                               cell.setCellValue(new XSSFRichTextString(titles[k]));
                           }
                       }    
                       
                       nRow = sheet.createRow(pageRowNo++);    //新建行对象
            
                       // 打印每行, 列属性的个数
                       for(int j=0;j<headFields.length;j++){
                           nCell = nRow.createCell(j);
                           nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString());
                       }
                       rowNo++;
                }
                
                dataList.clear();
                params.put("pageNum", i+1);
                params.put("count", false);//导出时，只让第一次分页查询执行count，后面的查询不执行count，加快执行速度
                list = baseService.findByPage(params);
                pageInfo = new PageInfo(list);
                dataList = pageInfo.getList();
                dataMap = convertListBeanToListMap(dataList, cls); //list<Bean>转成list<Map>，方便通用各个模块
            }
        } else {
               sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
               sheet = wb.getSheetAt(rowNo/sheetNum);        //动态指定当前的工作表
               pageRowNo = 0;        //每当新建了工作表就将当前工作表的行号重置为0
               
               Row row = sheet.createRow(pageRowNo++);    //新建行对象
               Cell cell = row.createCell(0);
               for (int k = 0; k < titles.length; k++) {
                   cell = row.createCell(k);
                   cell.setCellStyle(headCellStyle);  
                   cell.setCellValue(new XSSFRichTextString(titles[k]));
               }
               nRow = sheet.createRow(pageRowNo++);    //新建行对象
        }
        
        long finishedTime = System.currentTimeMillis(); // 处理完成时间
        log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000);

        FileOutputStream fOut = new FileOutputStream(filePath);
        if (fOut != null) {
            try {
                wb.write(fOut);
                fOut.flush(); // 刷新缓冲区
            } catch (Exception e) {
                e.printStackTrace();
            }
            fOut.close();
        }
        if (wb != null){
            try {
                wb.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        long stopTime = System.currentTimeMillis(); // 写文件时间
        log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 );
        
        //isDownload为true时，让浏览器弹出下载文件
        if (isDownload) {
           HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
           HttpServletResponse response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse();
           try {
               File file = new File(filePath);
               String fileName = file.getName();// 获取日志文件名称
               InputStream fis = new BufferedInputStream(new FileInputStream(filePath));
               byte[] buffer = new byte[fis.available()];
               fis.read(buffer);
               fis.close();
               response.reset();
               //然后转换编码格式为utf-8,保证不出现乱码,这个文件名称用于浏览器的下载框中自动显示的文件名
                  // 判断是否火狐浏览器
               String agent = request.getHeader("User-Agent");
                  boolean isFirefox = (agent != null && agent.contains("Firefox"));
                  if (isFirefox) {
                       fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
                  } else {
                       fileName = URLEncoder.encode(fileName, "UTF8");
                  }
                  response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
               response.addHeader("Content-Length", "" + file.length());
               OutputStream os = new BufferedOutputStream(response.getOutputStream());
               response.setContentType("application/octet-stream");
               os.write(buffer);// 输出文件
               os.flush();
               os.close();
            } catch (Exception e) {
                log.error("exportExcel download出错: {}", e.getMessage());
            }
       }
    }

    public static String exportExcelAndUpload(String fileName, Map<String, Object> params, BaseService baseService, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception {
        String path = null;
        long startTime = System.currentTimeMillis(); // 开始时间
        Sheet sheet = null; // 工作表对象
        Row nRow = null; // 行对象
        Cell nCell = null; // 列对象

        int pageSize = 10000; //每次查询的数据量
        params.put("pageSize", pageSize);
        params.put("pageNum", 1);
        List list = baseService.findByPage(params);
        
        PageInfo pageInfo = new PageInfo(list);
        List dataList = pageInfo.getList();
        List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls);
        
        // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。
        Workbook wb = new SXSSFWorkbook(500); // 关键语句
        Font font = wb.createFont();
        font.setBold(true);
        CellStyle headCellStyle = wb.createCellStyle();
        headCellStyle.setFont(font);
                
        int total = (int) pageInfo.getTotal();
        //每个sheet保存多少行
        int sheetNum = 1000000;
        int rowNo = 0; // 总行号
        int pageRowNo = 0; // 页行号
        
        // 根据行数求数据提取次数  
        int export_times = total % pageSize > 0 ? total / pageSize  + 1 :total / pageSize;  
        
        if(export_times > 0){
            for (int i = 1; i <= export_times; i++) {  
                for (int n = 0; n < dataMap.size(); n++) {
                    //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
                       if(rowNo%sheetNum==0){
                           sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
                           sheet = wb.getSheetAt(rowNo/sheetNum);        //动态指定当前的工作表
                           pageRowNo = 0;        //每当新建了工作表就将当前工作表的行号重置为0
                           
                           Row row = sheet.createRow(pageRowNo++);    //新建行对象
                           Cell cell = row.createCell(0);
                           for (int k = 0; k < titles.length; k++) {
                               cell = row.createCell(k);
                               cell.setCellStyle(headCellStyle);  
                               cell.setCellValue(new XSSFRichTextString(titles[k]));
                           }
                       }    
                       
                       nRow = sheet.createRow(pageRowNo++);    //新建行对象
            
                       // 打印每行, 列属性的个数
                       for(int j=0;j<headFields.length;j++){
                           nCell = nRow.createCell(j);
                           nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString());
                       }
                       rowNo++;
                }
                
                dataList.clear();
                params.put("pageNum", i+1);
                params.put("count", false);//导出时，只让第一次分页查询执行count，后面的查询不执行count，加快执行速度
                list = baseService.findByPage(params);
                pageInfo = new PageInfo(list);
                dataList = pageInfo.getList();
                dataMap = convertListBeanToListMap(dataList, cls); //list<Bean>转成list<Map>，方便通用各个模块
            }
        } else {
               sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
               sheet = wb.getSheetAt(rowNo/sheetNum);        //动态指定当前的工作表
               pageRowNo = 0;        //每当新建了工作表就将当前工作表的行号重置为0
               
               Row row = sheet.createRow(pageRowNo++);    //新建行对象
               Cell cell = row.createCell(0);
               for (int k = 0; k < titles.length; k++) {
                   cell = row.createCell(k);
                   cell.setCellStyle(headCellStyle);  
                   cell.setCellValue(new XSSFRichTextString(titles[k]));
               }
               nRow = sheet.createRow(pageRowNo++);    //新建行对象
        }
        
        long finishedTime = System.currentTimeMillis(); // 处理完成时间
        log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000);

        long stopTime = System.currentTimeMillis(); // 写文件时间
        log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 );
        
        ByteArrayOutputStream bos = null;
        InputStream is = null;
        try {
            bos = new ByteArrayOutputStream();
            wb.write(bos);
            byte[] barray = bos.toByteArray();
            is = new ByteArrayInputStream(barray);
            path = OSSUtil.upload(is, fileName, "xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (wb != null){
                try {
                    wb.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (bos != null) {
                try {
                    bos.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (is != null) {
                try {
                    is.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return path;
    }
    
    public static String exportExcelAndUploadOSS(String fileName, Map<String, Object> params, BaseService baseService, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception {
        String path = null;
        long startTime = System.currentTimeMillis(); // 开始时间
        Sheet sheet = null; // 工作表对象
        Row nRow = null; // 行对象
        Cell nCell = null; // 列对象

        int pageSize = 10000; //每次查询的数据量
        params.put("pageSize", pageSize);
        params.put("pageNum", 1);
        List list = baseService.findByPage(params);
        
        PageInfo pageInfo = new PageInfo(list);
        List dataList = pageInfo.getList();
        List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls);
        
        // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。
        Workbook wb = new SXSSFWorkbook(500); // 关键语句
        Font font = wb.createFont();
        font.setBold(true);
        CellStyle headCellStyle = wb.createCellStyle();
        headCellStyle.setFont(font);
                
        int total = (int) pageInfo.getTotal();
        //每个sheet保存多少行
        int sheetNum = 1000000;
        int rowNo = 0; // 总行号
        int pageRowNo = 0; // 页行号
        
        // 根据行数求数据提取次数  
        int export_times = total % pageSize > 0 ? total / pageSize  + 1 :total / pageSize;  
        
        if(export_times > 0){
            for (int i = 1; i <= export_times; i++) {  
                for (int n = 0; n < dataMap.size(); n++) {
                    //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
                       if(rowNo%sheetNum==0){
                           sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
                           sheet = wb.getSheetAt(rowNo/sheetNum);        //动态指定当前的工作表
                           pageRowNo = 0;        //每当新建了工作表就将当前工作表的行号重置为0
                           
                           Row row = sheet.createRow(pageRowNo++);    //新建行对象
                           Cell cell = row.createCell(0);
                           for (int k = 0; k < titles.length; k++) {
                               cell = row.createCell(k);
                               cell.setCellStyle(headCellStyle);  
                               cell.setCellValue(new XSSFRichTextString(titles[k]));
                           }
                       }    
                       
                       nRow = sheet.createRow(pageRowNo++);    //新建行对象
            
                       // 打印每行, 列属性的个数
                       for(int j=0;j<headFields.length;j++){
                           nCell = nRow.createCell(j);
                           nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString());
                       }
                       rowNo++;
                }
                
                dataList.clear();
                params.put("pageNum", i+1);
                params.put("count", false);//导出时，只让第一次分页查询执行count，后面的查询不执行count，加快执行速度
                list = baseService.findByPage(params);
                pageInfo = new PageInfo(list);
                dataList = pageInfo.getList();
                dataMap = convertListBeanToListMap(dataList, cls); //list<Bean>转成list<Map>，方便通用各个模块
            }
        } else {
               sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
               sheet = wb.getSheetAt(rowNo/sheetNum);        //动态指定当前的工作表
               pageRowNo = 0;        //每当新建了工作表就将当前工作表的行号重置为0
               
               Row row = sheet.createRow(pageRowNo++);    //新建行对象
               Cell cell = row.createCell(0);
               for (int k = 0; k < titles.length; k++) {
                   cell = row.createCell(k);
                   cell.setCellStyle(headCellStyle);  
                   cell.setCellValue(new XSSFRichTextString(titles[k]));
               }
               nRow = sheet.createRow(pageRowNo++);    //新建行对象
        }
        
        long finishedTime = System.currentTimeMillis(); // 处理完成时间
        log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000);
        
        long stopTime = System.currentTimeMillis(); // 写文件时间
        log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 );
        ByteArrayOutputStream bos = null;
        InputStream is = null;
        try {
            bos = new ByteArrayOutputStream();
            wb.write(bos);
            byte[] barray = bos.toByteArray();
            is = new ByteArrayInputStream(barray);
            path =  OSSUtil.upload(is, fileName, "xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (wb != null){
                try {
                    wb.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (bos != null) {
                try {
                    bos.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (is != null) {
                try {
                    is.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return path;
    }
    
    public static String exportExcelAndUploadOSS(String fileName, Map<String, Object> params, BaseService baseService, String methodName, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception {
        String path = null;
        long startTime = System.currentTimeMillis(); // 开始时间
        Sheet sheet = null; // 工作表对象
        Row nRow = null; // 行对象
        Cell nCell = null; // 列对象

        int pageSize = 10000; //每次查询的数据量
        params.put("pageSize", pageSize);
        params.put("pageNum", 1);
        
        Class clz = baseService.getClass();
        Object obj = clz.newInstance();
        //获取方法  
        Method m = clz.getDeclaredMethod(methodName, Map.class);
        //调用方法  
        List list = (List) m.invoke(obj, params);
          
//        Method method2 = baseService.getClass().getMethod("methodName", Class.forName("java.util.Map"));
//        List list = (List) method2.invoke( params);
//        List list = baseService.findByPage(params);
        
        PageInfo pageInfo = new PageInfo(list);
        List dataList = pageInfo.getList();
        List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls);
        
        // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。
        Workbook wb = new SXSSFWorkbook(500); // 关键语句
        Font font = wb.createFont();
        font.setBold(true);
        CellStyle headCellStyle = wb.createCellStyle();
        headCellStyle.setFont(font);
                
        int total = (int) pageInfo.getTotal();
        //每个sheet保存多少行
        int sheetNum = 1000000;
        int rowNo = 0; // 总行号
        int pageRowNo = 0; // 页行号
        
        // 根据行数求数据提取次数  
        int export_times = total % pageSize > 0 ? total / pageSize  + 1 :total / pageSize;  
        
        if(export_times > 0){
            for (int i = 1; i <= export_times; i++) {  
                for (int n = 0; n < dataMap.size(); n++) {
                    //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
                       if(rowNo%sheetNum==0){
                           sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
                           sheet = wb.getSheetAt(rowNo/sheetNum);        //动态指定当前的工作表
                           pageRowNo = 0;        //每当新建了工作表就将当前工作表的行号重置为0
                           
                           Row row = sheet.createRow(pageRowNo++);    //新建行对象
                           Cell cell = row.createCell(0);
                           for (int k = 0; k < titles.length; k++) {
                               cell = row.createCell(k);
                               cell.setCellStyle(headCellStyle);  
                               cell.setCellValue(new XSSFRichTextString(titles[k]));
                           }
                       }    
                       
                       nRow = sheet.createRow(pageRowNo++);    //新建行对象
            
                       // 打印每行, 列属性的个数
                       for(int j=0;j<headFields.length;j++){
                           nCell = nRow.createCell(j);
                           nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString());
                       }
                       rowNo++;
                }
                
                dataList.clear();
                params.put("pageNum", i+1);
                params.put("count", false);//导出时，只让第一次分页查询执行count，后面的查询不执行count，加快执行速度
                list = (List) m.invoke(obj, params);
                pageInfo = new PageInfo(list);
                dataList = pageInfo.getList();
                dataMap = convertListBeanToListMap(dataList, cls); //list<Bean>转成list<Map>，方便通用各个模块
            }
        } else {
               sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
               sheet = wb.getSheetAt(rowNo/sheetNum);        //动态指定当前的工作表
               pageRowNo = 0;        //每当新建了工作表就将当前工作表的行号重置为0
               
               Row row = sheet.createRow(pageRowNo++);    //新建行对象
               Cell cell = row.createCell(0);
               for (int k = 0; k < titles.length; k++) {
                   cell = row.createCell(k);
                   cell.setCellStyle(headCellStyle);  
                   cell.setCellValue(new XSSFRichTextString(titles[k]));
               }
               nRow = sheet.createRow(pageRowNo++);    //新建行对象
        }
        
        long finishedTime = System.currentTimeMillis(); // 处理完成时间
        log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000);
        
        long stopTime = System.currentTimeMillis(); // 写文件时间
        log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 );
        ByteArrayOutputStream bos = null;
        InputStream is = null;
        try {
            bos = new ByteArrayOutputStream();
            wb.write(bos);
            byte[] barray = bos.toByteArray();
            is = new ByteArrayInputStream(barray);
            path =  OSSUtil.upload(is, fileName, "xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (wb != null){
                try {
                    wb.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (bos != null) {
                try {
                    bos.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (is != null) {
                try {
                    is.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return path;
    }
    
     public static void main(String[] args) throws Exception {
            long beginTime = System.currentTimeMillis();
//            List[] list = getDataFromExcel("G:"+ File.separator +"初始化业务员8 - 副本.xlsx", 0);
//            List[] list = getDataFromExcelByCache("G:"+ File.separator +"初始化业务员8.xlsx", 0);
//            List[] list = getDataFromExcelByCache("G:"+ File.separator +"初始化业务员8 - 副本.xlsx", 0);
            long endTime = System.currentTimeMillis();
           log.info("Cast time : " + (endTime - beginTime));
            
//            if (list != null && list.length > 0){
//                for (int i = 0; i < list.length; i++) {
//                    System.out.println(list[i]);
//                }
//            }
            
      }
    
     /**
      * 读取出filePath中的所有数据信息
      * @param filePath excel文件的绝对路径
      * @param sheetIndex 第几个sheet
         * @throws IOException 
      * 
      */
     public static List[] getDataFromExcelByCache(InputStream fis, int sheetIndex) throws Exception {
          List[] data = null;
         Workbook wookbook = null;
         
         try {
                //获取一个绝对地址的流
//                fis = new FileInputStream(filePath);
                
                wookbook = StreamingReader.builder()
                        .rowCacheSize(100)  //缓存到内存中的行数，默认是10
                        .bufferSize(4096)  //读取资源时，缓存到内存的字节大小，默认是1024
                        .open(fis);  //打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
                Sheet sheet = wookbook.getSheetAt(sheetIndex);
                
                //获得数据的总行数
                int totalRowNum = sheet.getLastRowNum();
                log.info("====总行数"+totalRowNum);
//                if (totalRowNum > 200000) {
//                    throw new G7sException(ErrorEnum.ERROR_DEFAULT.getErrorCode(), "Excel读取数量不能大于200000行");
//                }
                
                data = new List[totalRowNum];
                List<Object> rowData = null;
                int totalCell = 0;
                //遍历所有的行
                for (Row row : sheet) {
                    if(row.getRowNum() == 0) {
                        totalCell = row.getLastCellNum();
                    }
                    //遍历所有的列
                    if (row.getRowNum() > 0) {
                        rowData = new ArrayList<Object>();
                        for (int i = 0; i < totalCell; i++) {
                            if(row == null || row.getCell(i) == null) {
                                rowData.add("");
                            } else {
                                rowData.add(row.getCell(i).getStringCellValue());
                            }
                        }
                        data[row.getRowNum()-1] = rowData;
                    }
                }
            } catch (Exception e) {
                log.error("excel读取报错：{}", e.getMessage());
            } finally {
                if (fis != null) {
                    try {
                        fis.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                if (wookbook != null) {
                    try {
                        wookbook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
         }
         return data;
     }
     
     /**
      * 读取出filePath中的所有数据信息
      * @param filePath excel文件的绝对路径
      * @param sheetIndex 第几个sheet
         * @throws IOException 
      * 
      */
     public static List[] getDataFromExcel(String filePath, int sheetIndex) throws IOException {
         List[] data = null;
         //判断是否为excel类型文件
         if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx")) {
             throw new G7sException(ErrorEnum.ERROR_DEFAULT.getErrorCode(), "Excel格式无效");
         }
         
         FileInputStream fis =null;
         Workbook wookbook = null;
         
         try {
                //获取一个绝对地址的流
                fis = new FileInputStream(filePath);
                
                if(filePath.endsWith(".xls")) {
                    //2003版本的excel，用.xls结尾
                    wookbook = new HSSFWorkbook(fis);
                } else if(filePath.endsWith(".xlsx")) {
                    //2007版本的excel，用.xlsx结尾
                    wookbook = new XSSFWorkbook(fis);
                }
                
                //得到一个工作表
                Sheet sheet = wookbook.getSheetAt(sheetIndex);
                
                //获得表头
                Row rowHead = sheet.getRow(0);
                
                log.info("表头的数量：" + rowHead.getPhysicalNumberOfCells());
                
                //获得数据的总行数
                int totalRowNum = sheet.getLastRowNum();
                log.info("总数量："+totalRowNum);
//                if (totalRowNum > 200000) {
//                    throw new G7sException(ErrorEnum.ERROR_DEFAULT.getErrorCode(), "Excel读取数量不能大于200000行");
//                }
                data = new List[totalRowNum];
                List<Object> rowData = null;
                //获得所有数据
                for(int i = 1 ; i <= totalRowNum ; i++) {
                   //获得第i行对象
                   Row row = sheet.getRow(i);
    //         //获得获得第i行第0列的 String类型对象
    //         Cell cell = row.getCell((short)0);
    //         name = cell.getStringCellValue().toString();
    //         
    //         //获得一个数字类型的数据
    //         cell = row.getCell((short)1);
    //         latitude = cell.getStringCellValue();
                    
                    rowData = new ArrayList<Object>();
                    for (int k = 0; k < row.getLastCellNum(); k++) {
                        Cell cell = row.getCell((short)k);
                        rowData.add(cell);
                    }
                    data[i-1] = rowData;
                }
            } catch (Exception e) {
                log.error("excel读取报错：{}", e.getMessage());
            } finally {
                if (fis != null) {
                    fis.close();
                }
                if (wookbook != null) {
                    wookbook.close();
                }
            }
         return data;
     }
 
    /**
     * 将 List<JavaBean>对象转化为List<Map>
     * @param beanList
     * @return
     * @throws Exception
     */
    public static <T> List<Map<String, Object>> convertListBeanToListMap(List<T> beanList, Class<T> T)
        throws Exception {
        List<Map<String, Object>> mapList = new ArrayList<>();
        for (int i = 0, n = beanList.size(); i < n; i++) {
            Object bean = beanList.get(i);
            Map<String, Object> map = convertBeanToMap(bean);
            mapList.add(map);
        }
        return mapList;
    }
    
    /**
     * 将一个 JavaBean 对象转化为一个 Map
     * @param bean
     * @return
     * @throws IntrospectionException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
    public static Map<String, Object> convertBeanToMap(Object bean)
        throws IntrospectionException, IllegalAccessException, InvocationTargetException
    {
        Class<? extends Object> type = bean.getClass();
        Map<String, Object> returnMap = new HashMap<>();
        BeanInfo beanInfo = Introspector.getBeanInfo(type);
        
        PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
        for (int i = 0; i < propertyDescriptors.length; i++)
        {
            PropertyDescriptor descriptor = propertyDescriptors[i];
            String propertyName = descriptor.getName();
            if (!"class".equals(propertyName))
            {
                Method readMethod = descriptor.getReadMethod();
                Object result = readMethod.invoke(bean, new Object[0]);
                if (result != null)
                {
                    returnMap.put(propertyName, result);
                }
                else
                {
                    returnMap.put(propertyName, null);
                }
            }
        }
        return returnMap;
    }
   
    
    public static void exportExcel(String filePath, List list, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception {
        long startTime = System.currentTimeMillis(); // 开始时间
        Sheet sheet = null; // 工作表对象
        Row nRow = null; // 行对象
        Cell nCell = null; // 列对象

        // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。
        Workbook wb = new SXSSFWorkbook(500); // 关键语句
        Font font = wb.createFont();
        font.setBold(true);
        CellStyle headCellStyle = wb.createCellStyle();
        headCellStyle.setFont(font);
        
        //每个sheet保存多少行
        int sheetNum = 1000000;
        int rowNo = 0; // 总行号
        int pageRowNo = 0; // 页行号
        
        
        if(list != null && list.size() > 0) {
            PageInfo pageInfo = new PageInfo(list);
            List dataList = pageInfo.getList();
            List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls);
            
            
            for (int n = 0; n < dataMap.size(); n++) {
                //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
                   if(rowNo%sheetNum==0){
                       sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
                       sheet = wb.getSheetAt(rowNo/sheetNum);        //动态指定当前的工作表
                       pageRowNo = 0;        //每当新建了工作表就将当前工作表的行号重置为0
                       
                       Row row = sheet.createRow(pageRowNo++);    //新建行对象
                       Cell cell = row.createCell(0);
                       for (int k = 0; k < titles.length; k++) {
                           cell = row.createCell(k);
                           cell.setCellStyle(headCellStyle);  
                           cell.setCellValue(new XSSFRichTextString(titles[k]));
                       }
                   }    
                   
                   nRow = sheet.createRow(pageRowNo++);    //新建行对象
        
                   // 打印每行, 列属性的个数
                   for(int j=0;j<headFields.length;j++){
                       nCell = nRow.createCell(j);
                       nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString());
                   }
                   rowNo++;
            }
            dataList.clear();
            long finishedTime = System.currentTimeMillis(); // 处理完成时间
            log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000);

        } else {
            sheet = wb.createSheet("第1个工作簿");//建立新的sheet对象
            sheet = wb.getSheetAt(0);        //动态指定当前的工作表
            
            Row row = sheet.createRow(pageRowNo++);    //新建行对象
            Cell cell = row.createCell(0);
            for (int k = 0; k < titles.length; k++) {
                cell = row.createCell(k);
                cell.setCellStyle(headCellStyle);  
                cell.setCellValue(new XSSFRichTextString(titles[k]));
            }
            nRow = sheet.createRow(pageRowNo++);    //新建行对象
        }
        
        FileOutputStream fOut = new FileOutputStream(filePath);
        if (fOut != null) {
            try {
                wb.write(fOut);
                fOut.flush(); // 刷新缓冲区
            } catch (Exception e) {
                e.printStackTrace();
            }
            fOut.close();
        }
        if (wb != null){
            try {
                wb.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        long stopTime = System.currentTimeMillis(); // 写文件时间
        log.info("exportExcel write xlsx file time: {}s", (stopTime - startTime) / 1000 );
        
        //isDownload为true时，让浏览器弹出下载文件
        if (isDownload) {
           HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
           HttpServletResponse response = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getResponse();
           try {
               File file = new File(filePath);
               String fileName = file.getName();// 获取日志文件名称
               InputStream fis = new BufferedInputStream(new FileInputStream(filePath));
               byte[] buffer = new byte[fis.available()];
               fis.read(buffer);
               fis.close();
               response.reset();
               //然后转换编码格式为utf-8,保证不出现乱码,这个文件名称用于浏览器的下载框中自动显示的文件名
                  // 判断是否火狐浏览器
               String agent = request.getHeader("User-Agent");
                  boolean isFirefox = (agent != null && agent.contains("Firefox"));
                  if (isFirefox) {
                       fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
                  } else {
                       fileName = URLEncoder.encode(fileName, "UTF8");
                  }
                  response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
               response.addHeader("Content-Length", "" + file.length());
               OutputStream os = new BufferedOutputStream(response.getOutputStream());
               response.setContentType("application/octet-stream");
               os.write(buffer);// 输出文件
               os.flush();
               os.close();
            } catch (Exception e) {
                log.error("exportExcel download出错: {}", e.getMessage());
            }
       }
    }
    
    public static String exportListExcelAndUpload(String fileName, List list, Class cls, String[] titles, String[] headFields, boolean isDownload) throws Exception {
        long startTime = System.currentTimeMillis(); // 开始时间
        Sheet sheet = null; // 工作表对象
        Row nRow = null; // 行对象
        Cell nCell = null; // 列对象
        String path = null;
        
        // 内存中只创建500个对象，写临时文件，当超过500条，就将内存中不用的对象释放。
        Workbook wb = new SXSSFWorkbook(500); // 关键语句
        Font font = wb.createFont();
        font.setBold(true);
        CellStyle headCellStyle = wb.createCellStyle();
        headCellStyle.setFont(font);
        
        //每个sheet保存多少行
        int sheetNum = 1000000;
        int rowNo = 0; // 总行号
        int pageRowNo = 0; // 页行号
        
        
        if(list != null && list.size() > 0) {
            PageInfo pageInfo = new PageInfo(list);
            List dataList = pageInfo.getList();
            List<Map<String, Object>> dataMap = convertListBeanToListMap(dataList, cls);
            
            
            for (int n = 0; n < dataMap.size(); n++) {
                //打印300000条后切换到下个工作表，可根据需要自行拓展，2百万，3百万...数据一样操作，只要不超过1048576就可以
                   if(rowNo%sheetNum==0){
                       sheet = wb.createSheet("第"+(rowNo/sheetNum+1)+"个工作簿");//建立新的sheet对象
                       sheet = wb.getSheetAt(rowNo/sheetNum);        //动态指定当前的工作表
                       pageRowNo = 0;        //每当新建了工作表就将当前工作表的行号重置为0
                       
                       Row row = sheet.createRow(pageRowNo++);    //新建行对象
                       Cell cell = row.createCell(0);
                       for (int k = 0; k < titles.length; k++) {
                           cell = row.createCell(k);
                           cell.setCellStyle(headCellStyle);  
                           cell.setCellValue(new XSSFRichTextString(titles[k]));
                       }
                   }    
                   
                   nRow = sheet.createRow(pageRowNo++);    //新建行对象
        
                   // 打印每行, 列属性的个数
                   for(int j=0;j<headFields.length;j++){
                       nCell = nRow.createCell(j);
                       nCell.setCellValue(dataMap.get(n).get(headFields[j])==null?"":dataMap.get(n).get(headFields[j]).toString());
                   }
                   rowNo++;
            }
            dataList.clear();
            long finishedTime = System.currentTimeMillis(); // 处理完成时间
            log.info("exportExcel finished execute time: {}s", (finishedTime - startTime) / 1000);

        } else {
            sheet = wb.createSheet("第1个工作簿");//建立新的sheet对象
            sheet = wb.getSheetAt(0);        //动态指定当前的工作表
            
            Row row = sheet.createRow(pageRowNo++);    //新建行对象
            Cell cell = row.createCell(0);
            for (int k = 0; k < titles.length; k++) {
                cell = row.createCell(k);
                cell.setCellStyle(headCellStyle);  
                cell.setCellValue(new XSSFRichTextString(titles[k]));
            }
            nRow = sheet.createRow(pageRowNo++);    //新建行对象
        }
        
        long stopTime = System.currentTimeMillis(); // 写文件时间
        log.info("exportListExcelAndUpload write xlsx file time: {}s", (stopTime - startTime) / 1000 );
        
        ByteArrayOutputStream bos = null;
        InputStream is = null;
        try {
            bos = new ByteArrayOutputStream();
            wb.write(bos);
            byte[] barray = bos.toByteArray();
            is = new ByteArrayInputStream(barray);
            path = OSSUtil.upload(is, fileName, "xlsx");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (wb != null){
                try {
                    wb.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (bos != null) {
                try {
                    bos.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (is != null) {
                try {
                    is.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return path;
    }
    
}