excel导出工具类（做接口用get请求，别用post，post乱码后期有机会在研究把）

package com.bj58.wuba.adbiz.web.util;

import com.bj58.wuba.adbiz.web.dto.PutAssetsProject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

public class POIUtils {
// public void writeExcel(String sheetname, HttpServletResponse response, List result){
// SimpleDateFormat formatter = new SimpleDateFormat(“yyyy-MM-dd”);
// try {
// //创建工作簿，SXSSFWorkbook 支持大数据量的导出
// SXSSFWorkbook workbook = new SXSSFWorkbook();
// //创建sheet表
// Sheet sheet = workbook.createSheet(“项目报表”);
// //String[] strings = {“序号”, “项目编号”, “项目名称”, “项目描述”, “创建人”, “项目状态”, “绑定项目账户”, “项目开始时间”, “项目结束时间”};
//
// //创建标题
// Row rowTitle = sheet.createRow(0);
// Cell cellTitle = null;
// cellTitle = rowTitle.createCell(0);
// cellTitle.setCellValue(“序号”);
// cellTitle = rowTitle.createCell(1);
// cellTitle.setCellValue(“项目编号”);
// cellTitle = rowTitle.createCell(2);
// cellTitle.setCellValue(“项目名称”);
// cellTitle = rowTitle.createCell(3);
// cellTitle.setCellValue(“项目描述”);
// cellTitle = rowTitle.createCell(4);
// cellTitle.setCellValue(“创建人”);
// cellTitle = rowTitle.createCell(5);
// cellTitle.setCellValue(“项目状态”);
// cellTitle = rowTitle.createCell(6);
// cellTitle.setCellValue(“绑定项目账户”);
// cellTitle = rowTitle.createCell(7);
// cellTitle.setCellValue(“项目开始时间”);
// cellTitle = rowTitle.createCell(8);
// cellTitle.setCellValue(“项目结束时间”);
// cellTitle = rowTitle.createCell(9);
// cellTitle.setCellValue(“房天下ID”);
// cellTitle = rowTitle.createCell(10);
// cellTitle.setCellValue(“安居客ID”);
//
// for (int i = 0; i < result.size(); i++) {
// //创建行
// Row row = sheet.createRow(i + 1);
// Cell cell = null; //创建单元格
// cell = row.createCell(0);
// cell.setCellValue(i + 1);
// cell = row.createCell(1);
// cell.setCellValue(result.get(i).getProjectCode());
// cell = row.createCell(2);
// cell.setCellValue(result.get(i).getProjectName());
// cell = row.createCell(3);
// cell.setCellValue(result.get(i).getDescription());
// cell = row.createCell(4);
// cell.setCellValue(result.get(i).getCreateUser());
// cell = row.createCell(5);
// cell.setCellValue(status(result.get(i).getStatus()));
// cell = row.createCell(6);
// cell.setCellValue(result.get(i).getAccountListStr());
// cell = row.createCell(7);
// cell.setCellValue(formatter.format(result.get(i).getStartTime()));
// cell = row.createCell(8);
// cell.setCellValue(formatter.format(result.get(i).getEndTime()));
// cell = row.createCell(9);
// cell.setCellValue(result.get(i).getFangId());
// cell = row.createCell(10);
// cell.setCellValue(result.get(i).getAnjukeId());
// }
//
// //输出Excel文件
// OutputStream output=response.getOutputStream();
// response.reset();
// //设置响应头
// final String name = new String(“项目报表”.getBytes(“UTF-8”), “ISO8859-1”);
// response.setContentType(“application/msexcel”);
// response.setHeader(“Content-Disposition”,“attachment;filename=”+ name +".xlsx");
// workbook.write(output);
// output.close();
// } catch (IOException e) {
// e.printStackTrace();
// }
// }
/**
*
* 方法描述: Excel导出

* 初始作者: JangSinyu

* 创建日期: 2018年12月20日-上午11:10:45

* 开始版本: 1.0.0

* =================================================

* 修改记录：

* 修改作者 日期 修改内容

* ================================================

*
* @param columnNames 到导出的对象属性名
* @param keyList 要导出的对象自己起属性对应的名字—（必须与属性名顺序相同）
* @param objList 要导出的对象集合
* @param fileName 导出文件名称
* @throws Exception
*/
public static void downLoadExcel(String[] columnNames, String[] keyList, List<?> objList, String fileName,HttpServletResponse response){
if (objList.size() > 1000000){
List<List<?>> lists = fixedGrouping(objList, 1000000);
for (int i = 0; i < lists.size(); i++) {
downLoadExcelManySheet(columnNames,keyList,lists,fileName,0,response);
}
}else {
downLoadExcelSingleSheet(columnNames,keyList,objList,fileName,response);
}
}

private static void downLoadExcelManySheet(String[] columnNames, String[] keyList, List<List<?>> objList, String fileName,int num,HttpServletResponse response) {
    Workbook wb = null;
    //判断文件类型 03或是07
    wb = new SXSSFWorkbook();

// if (isExcel2007(fileName)) {
// wb = new SXSSFWorkbook();
// }
// if (isExcel2003(fileName)) {
// wb = new HSSFWorkbook();
// }
//创建sheet
for (int a = 0; a < objList.size(); a++) {
List<?> objects = objList.get(a);
Sheet sheet = wb.createSheet(fileName + “_” + a);
//创建第一行，存放key
Row row = sheet.createRow(0);
for (int i = 0; i < keyList.length; i++) {
row.createCell(i).setCellValue(keyList[i]);
}
//先创建object空对象
Object project = null;
for (int i = 0; i < objects.size(); i++) {
Row row1 = sheet.createRow(i+1);
for (int j = 0; j < columnNames.length; j++) {
//创建obj实例
project = objects.get(i);
row1.createCell(j).setCellValue(getValueByName(columnNames[j],project)+"");
}
}
}
//将文件响应到电脑
try {
// FileOutputStream fileOut = new FileOutputStream(filePath+"\"+fileName);
// wb.write(fileOut);
//输出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
//设置响应头
// final String name = new String(fileName.getBytes(“UTF-8”), “UTF-8”);
final String name = new String(“项目报表”.getBytes(“UTF-8”), “ISO8859-1”);
response.setContentType(“application/octet-stream;charset=UTF-8”);
response.setHeader(“Content-Disposition”,“attachment;filename=”+ name +".xlsx");
wb.write(output);
output.close();
} catch (Exception e) {
e.printStackTrace();
}
}

private static void downLoadExcelSingleSheet(String[] columnNames, String[] keyList, List<?> objList, String fileName,HttpServletResponse response) {
    Workbook wb = null;
    wb = new SXSSFWorkbook();
    //判断文件类型 03或是07

// if (isExcel2007(fileName)) {
// wb = new SXSSFWorkbook();
// }
// if (isExcel2003(fileName)) {
// wb = new HSSFWorkbook();
// }
//创建sheet
Sheet sheet = wb.createSheet(fileName);

    //创建第一行，存放key
    Row row = sheet.createRow(0);
    for (int i = 0; i < keyList.length; i++) {
        row.createCell(i).setCellValue(keyList[i]);
    }
    //先创建object空对象
    Object project = null;
    for (int i = 0; i < objList.size(); i++) {
        Row row1 = sheet.createRow(i+1);
        for (int j = 0; j < columnNames.length; j++) {
            //创建obj实例
            project = objList.get(i);
            row1.createCell(j).setCellValue(getValueByName(columnNames[j],project)+"");
        }
    }
    //将文件响应到电脑
    try {

// FileOutputStream fileOut = new FileOutputStream(filePath+"\"+fileName);
// wb.write(fileOut);
//输出Excel文件
OutputStream output=response.getOutputStream();
response.reset();
//设置响应头
final String name = new String(fileName.getBytes(“UTF-8”), “ISO8859-1”);
response.setContentType(“application/msexcel”);
response.setHeader(“Content-Disposition”,“attachment;filename=”+ name +".xlsx");
wb.write(output);
output.close();
} catch (Exception e) {
e.printStackTrace();
}
}

//利用反射获得对象的值
private static Object getValueByName(String fieldName, Object obj){
    try {
        String firstLetter = fieldName.substring(0, 1).toUpperCase();
        String getter = "get" + firstLetter + fieldName.substring(1);
        Method method = obj.getClass().getMethod(getter, new Class[] {});
        Object value = method.invoke(obj, new Object[] {});
        return value;
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
}

/**
 * 将一组数据固定分组，每组n个元素
 *
 * @param source 要分组的数据源
 * @param n      每组n个元素
 * @return
 */
private static List<List<?>> fixedGrouping(List<?> source, Integer n) {
    if (null == source || source.size() == 0 || n <= 0)
        return null;
    List<List<?>> result = new ArrayList<>();
    int remainder = source.size() % n;
    int size = (source.size() / n);
    for (int i = 0; i < size; i++) {
        List<?> subset = null;
        subset = source.subList(i * n, (i + 1) * n);
        result.add(subset);
    }
    if (remainder > 0) {
        List<?> subset = null;
        subset = source.subList(size * n, size * n + remainder);
        result.add(subset);
    }
    return result;
}

private static String suffix_xls = ".xls";

private static String suffix_xlsx = ".xlsx";

// 判断是否是03的excel:xls
private static boolean isExcel2003(String filePath) {
    return filePath.matches("^.+\\.(?i)(xls)$");
}

// 判断是否是07的excel:xlsx
private static boolean isExcel2007(String filePath) {
    return filePath.matches("^.+\\.(?i)(xlsx)$");
}

// 根据后缀名判断excel是否合法
private static boolean isCorrectExcel(String filePath) {
    if (isExcel2003(filePath) || isExcel2003(filePath)) {
        return true;
    } else {
        return false;
    }
}

}