Excel poi导出

package com.manyi.iw.agent.sale.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by kehui on 2014/11/14.
 */
public class PoiUtil {

    public static HSSFWorkbook initWorkbookByTemplate(List<List<String>> data, InputStream is, int offsetRow)
            throws IOException {
        return initWorkbook(data, null, is, offsetRow);
    }

    public static HSSFWorkbook initWorkbookByData(List<List<String>> data, List<String> titles, int offsetRow)
            throws IOException {
        return initWorkbook(data, titles, null, offsetRow);
    }

    /**
     * 可支持两种方式的xls输出
     * 1.根据已有xls文件的输入流，初始化workbook，填充数据
     * 2.新建workbook，根据传入数据和标题填充workbook
     * @param data 需要填充的数据
     * @param titles  标题
     * @param is 模板的输入流
     * @param offsetRow 行偏移量
     * @return
     * @throws IOException
     */
    public static HSSFWorkbook initWorkbook(List<List<String>> data, List<String> titles, InputStream is, int offsetRow)
            throws IOException {
        HSSFWorkbook workbook = null;
        HSSFSheet sheet = null;
        if (is == null) {
            workbook = new HSSFWorkbook();
            sheet = workbook.createSheet();
        } else {
            workbook = new HSSFWorkbook(is);
            sheet = workbook.getSheetAt(0);
        }
        if (data == null || data.size() == 0)
            return workbook;
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        //HSSFFont font = workbook.createFont();
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        //cellStyle.setFont(font);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        for (int i = 0; i < data.size(); i++) {
            List<String> rowData = data.get(i);
            HSSFRow row = sheet.createRow(offsetRow + i);
            if (i == 0 && (null != titles && titles.size() > 0)) {
                for (int j = 0; j < titles.size(); j++) {
                    //HSSFCell cell = createTitleCell(row, workbook, j, rowData.get(j));
                    HSSFCell cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cell.setCellValue(rowData.get(j));
                }
                continue;
            }
            for (int i1 = 0; i1 < rowData.size(); i1++) {
                //HSSFCell cell = createTitleCell(row, workbook, i1, rowData.get(i1));
                HSSFCell cell = row.createCell(i1);
                cell.setCellStyle(cellStyle);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(rowData.get(i1));
            }
        }
        return workbook;
    }

    public static HSSFCell createTitleCell(HSSFRow row, HSSFWorkbook workbook, int cellNumber, String cellValue) {
        HSSFCell cell = row.createCell(cellNumber);
        //cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        //HSSFFont font = workbook.createFont();
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        //cellStyle.setFont(font);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cell.setCellStyle(cellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(cellValue);
        return cell;
    }

    public static void main(String[] args) throws Exception{

    }

    /**
     * SXSS导出
     * @param data
     * @param titles
     * @param is
     * @param offsetRow
     * @return
     * @throws IOException
     */
    public static  SXSSFWorkbook  initSXSSWorkbook(List<List<String>> data, List<String> titles,InputStream is, int offsetRow) throws IOException {
        XSSFWorkbook workbook = null;
        XSSFSheet sheet = null;
        if (is == null) {
            workbook  =new XSSFWorkbook();
            sheet = workbook.createSheet();
        } else {
            workbook = new XSSFWorkbook(new BufferedInputStream(is));
            sheet = workbook.getSheetAt(0);
        }

        SXSSFWorkbook newWorkbook = new SXSSFWorkbook(workbook,100);
        Sheet newSheet = newWorkbook.getSheetAt(0);
        CellStyle cellStyle = newWorkbook.createCellStyle();


        if (data == null || data.size() == 0){
            return newWorkbook;
        }

            for (int i = 0; i < data.size(); i++) {
                List<String> rowData = data.get(i);
                Row newRow = newSheet.createRow(offsetRow + i);
                if (i == 0 && (null != titles && titles.size() > 0)) {
                    for (int j = 0; j < titles.size(); j++) {
                        Cell cell = newRow.createCell(j);
                        cell.setCellStyle(cellStyle);
                        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue(rowData.get(j));
                    }
                    continue;
                }
                for (int i1 = 0; i1 < rowData.size(); i1++) {
                    Cell cell = newRow.createCell(i1);
                    cell.setCellStyle(cellStyle);
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                    cell.setCellValue(rowData.get(i1));
                }
            }
            return newWorkbook;
    }

}

 

    /**
     * 根据一个实体集合,获得用于填充Excel的数据
     *
     * @param ts         实体集合
     * @param fieldNames 实体中的字段名称(一定要输入正确)
     * @return
     */
    public <T> List<List<String>> getExportData(List<T> ts, String... fieldNames) {
        if(CollectionUtils.isEmpty(ts))
            return null;
        List<List<String>> data = new ArrayList<>();
        for(T t : ts) {
            List<String> strs = new ArrayList<>();
            for(String fn : fieldNames) {
                String value = "";
                try {
                    value = BeanUtils.getProperty(t, fn);
                } catch(IllegalAccessException e) {
                    value = fn;
                } catch(InvocationTargetException e) {
                    value = fn;
                } catch(NoSuchMethodException e) {
                    value = fn;
                }
                strs.add(value);
            }
            data.add(strs);
        }
        return data;
    }
    /**
     * 报表导出通用方法
     *
     * @param response
     * @param data         报表的填充数据
     * @param templateType 导出文件所使用的模板类型
     * @param offset       行偏移量
     */
    public void exportExcel(HttpServletResponse response,
                            HttpServletRequest request,
                            List<List<String>> data,
                            String fileName,
                            int templateType,
                            int offset) throws IOException {
        ExportTemplateEnum exportTemplate = ExportTemplateEnum.getTemplate(templateType);
        if(null == exportTemplate)
            return;
        InputStream is = this.getClass().getClassLoader().getResourceAsStream("excel-template/" + exportTemplate.getFileName());
        if(is == null)
            return;
        String exportFileName = new String((StringUtils.isBlank(fileName) ?
                exportTemplate.getDesc() :
                fileName).getBytes(request.getCharacterEncoding()), "ISO8859-1");
        //后辍
        exportFileName += exportTemplate.getFileName().substring(exportTemplate.getFileName().lastIndexOf("."));
        response.addHeader("Content-Disposition", "attachment;filename=\"" + exportFileName + "\"");
        response.setContentType("application/x-download");
        response.addHeader("Content-Encoding", "utf-8");
        OutputStream os = response.getOutputStream();
        HSSFWorkbook workbook = PoiUtil.initWorkbookByTemplate(data, is, offset);
        workbook.write(os);
        os.flush();
        os.close();
        is.close();
    }

    public void exportSXSSFExcel(HttpServletResponse response,
                            HttpServletRequest request,
                            List<List<String>> data,
                            String fileName,
                            int templateType,
                            int offset) throws IOException {
        ExportTemplateEnum exportTemplate = ExportTemplateEnum.getTemplate(templateType);
        if(null == exportTemplate)
            return;
        FileInputStream is = new FileInputStream(this.getClass().getClassLoader().getResource("excel-template/" + exportTemplate.getFileName()).getFile());
        if(is == null)
            return;
        String exportFileName = new String((StringUtils.isBlank(fileName) ?
                exportTemplate.getDesc() :
                fileName).getBytes(request.getCharacterEncoding()), "ISO8859-1");
        //后辍
        exportFileName += exportTemplate.getFileName().substring(exportTemplate.getFileName().lastIndexOf("."));
        response.addHeader("Content-Disposition", "attachment;filename=\"" + exportFileName + "\"");
        response.setContentType("application/x-download");
        response.addHeader("Content-Encoding", "utf-8");
        OutputStream os = response.getOutputStream();
        SXSSFWorkbook workbook = PoiUtil.initSXSSWorkbook(data, null,is, offset);
        workbook.write(os);
        os.flush();
        os.close();
        is.close();
    }

 

List<QuerySignExportResult> list4 = agentService.querySignOther(cityName, cityId, beginDate, endDate);
				List<List<String>> data4 = getExportData(list4,
						"cityName",
						"createTime",
						"agentId",
						"agentName",
						"agentMobile",
						"groupName",
						"mendianName",
						"areaName",
						"bigAreaName",
						"callMobile",
						"signName",
						"remark");
				exportSXSSFExcel(response, request, data4, "标为其他", ExportTemplateEnum.signOther.getType(), 1);

 

 

			<dependency>
				<groupId>org.apache.poi</groupId>
				<artifactId>poi</artifactId>
				<version>${poi.version}</version>
			</dependency>
			<dependency>
				<groupId>org.apache.poi</groupId>
				<artifactId>poi-ooxml</artifactId>
				<version>${poi.version}</version>
			</dependency>
			<dependency>
				<groupId>org.apache.poi</groupId>
				<artifactId>poi-ooxml-schemas</artifactId>
				<version>${poi.version}</version>
			</dependency>
<poi.version>3.9</poi.version>