java中使用poi实现自定义excel文件的下载

1.首先在maven文件（pom.xml）中配置支持poi的包

    <!-- 支持Excel表格操作 -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.9</version>
    </dependency>

 2导入两个工具类：ExportExcelUtil  和  ExportExcelXSSFUtil

①ExportExcelUtil.java

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletOutputStream;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExportExcelUtil {
    /**
     * 导出多标题的EXCEL
     * @param titles
     * @param list
     * @param outputStream
     */
    public static void ExportMultiHeadExcel(String[] titles, ArrayList<List<Object>> list, ServletOutputStream outputStream) {
        // 创建一个workbook 对应一个excel应用文件
        Workbook workBook = new XSSFWorkbook();
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        //Sheet名称，可以自定义中文名称
        XSSFSheet sheet = (XSSFSheet) workBook.createSheet("Sheet1");
        ExportExcelXSSFUtil exportUtil = new ExportExcelXSSFUtil((XSSFWorkbook) workBook, sheet);
        XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
        // 构建表头
        XSSFRow headRow = sheet.createRow(0);
        XSSFCell cell = null;

        // 输出标题
        for (int i = 0; i < titles.length; i++) {
            //设置列宽
            sheet.setColumnWidth(i, 4000);
            //自动列宽
//        	sheet.autoSizeColumn(i,true);
            cell = headRow.createCell(i);
            cell.setCellStyle(bodyStyle);
            cell.setCellValue(titles[i]);
        }
        // 构建表体数据
        for (int j = 0; j < list.size(); j++) {
            XSSFRow bodyRow = sheet.createRow(j + 1);
            List<Object> rowList = list.get(j);
            for (int k = 0; k < rowList.size(); k++) {
                //设置列宽自适应
//            	sheet.autoSizeColumn(k,true);
                cell = bodyRow.createCell(k);
                cell.setCellStyle(bodyStyle);
                cell.setCellValue(rowList.get(k)+"");
            }
        }
        try {
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 导出单标题的EXCEL
     * @param title
     * @param list
     * @param outputStream
     */
    public static void ExportSingleHeadExcel(String title, ArrayList<List<Object>> list, ServletOutputStream outputStream) {
        // 创建一个workbook 对应一个excel应用文件
        Workbook workBook = new XSSFWorkbook();
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        //Sheet名称，可以自定义中文名称
        XSSFSheet sheet = (XSSFSheet) workBook.createSheet("Sheet1");
        ExportExcelXSSFUtil exportUtil = new ExportExcelXSSFUtil((XSSFWorkbook) workBook, sheet);
        XSSFCellStyle headStyle = exportUtil.getHeadStyle();
        XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
        //构建表头
        XSSFRow headRow = sheet.createRow(0);
        XSSFCell cell = null;
        //输出标题
        //设置列宽
        for (int i = 0; i < list.get(0).size(); i++) {
            sheet.setColumnWidth(i, 4000);
        }
        //创建(0，0)单元格
        cell = headRow.createCell(0);
        cell.setCellStyle(headStyle);
        cell.setCellValue(title);
        sheet.addMergedRegion(new CellRangeAddress(
            0,//第一行（基于0）
            0,//最后一行（从0开始）
            0,//第一列（基于0）
            list.get(0).size()-1 //最后一列（基于0）
        ));
        for (int j = 0; j < list.size(); j++) {
            XSSFRow bodyRow = sheet.createRow(j + 1);
            List<Object> rowList = list.get(j);
            for (int k = 0; k < rowList.size(); k++) {
                //设置列宽自适应
            	sheet.autoSizeColumn(k,true);
                cell = bodyRow.createCell(k);
                cell.setCellStyle(bodyStyle);
                cell.setCellValue(rowList.get(k)+"");
            }
        }
        try {
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 导出单标题的EXCEL
     * @param title
     * @param excelData
     * @param outputStream
     */
    public static void ExportSingleHeadExcel(String title, String[][] excelData, ServletOutputStream outputStream) {
        // 创建一个workbook 对应一个excel应用文件
        Workbook workBook = new XSSFWorkbook();
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        //Sheet名称，可以自定义中文名称
        XSSFSheet sheet = (XSSFSheet) workBook.createSheet("Sheet1");
        ExportExcelXSSFUtil exportUtil = new ExportExcelXSSFUtil((XSSFWorkbook) workBook, sheet);
        XSSFCellStyle headStyle = exportUtil.getHeadStyle();
        XSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
        //构建表头
        XSSFRow headRow = sheet.createRow(0);
        headRow.setHeight((short)600);
        XSSFCell cell = null;
        //设置列宽
        for (int i = 0; i < excelData[0].length; i++) {
            sheet.setColumnWidth(i, 4000);
        }
        //创建(0，0)单元格
        cell = headRow.createCell(0);
        cell.setCellStyle(headStyle);
        cell.setCellValue(title);
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(
                0,//第一行（基于0）
                0,//最后一行（从0开始）
                0,//第一列（基于0）
                excelData[0].length-1 //最后一列（基于0）
        ));
        for (int j = 1; j < excelData.length; j++) {
            XSSFRow bodyRow = sheet.createRow(j);
            for (int k = 0; k < excelData[j].length; k++) {
                cell = bodyRow.createCell(k);
                cell.setCellStyle(bodyStyle);
                cell.setCellValue(excelData[j][k]+"");
            }
        }
        try {
            workBook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

②ExportExcelXSSFUtil.java

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

public class ExportExcelXSSFUtil {
    private XSSFWorkbook wb = null;

    private XSSFSheet sheet = null;

    public ExportExcelXSSFUtil(XSSFWorkbook wb, XSSFSheet sheet) {
        this.wb = wb;
        this.sheet = sheet;
    }
    /**
     * 合并单元格后给合并后的单元格加边框
     *
     * @param region
     * @param cs
     */
    public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) {

        int toprowNum = region.getFirstRow();
        for (int i = toprowNum; i <= region.getLastRow(); i++) {
            XSSFRow row = sheet.getRow(i);
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                XSSFCell cell = row.getCell(j);// XSSFCellUtil.getCell(row,
                // (short) j);
                cell.setCellStyle(cs);
            }
        }
    }

    /**
     * 设置表头的单元格样式
     *
     * @return
     */
    public XSSFCellStyle getHeadStyle() {
        // 创建单元格样式
        XSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置单元格的背景颜色为白色
        cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(255,255,255)));
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置单元格水平居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置单元格垂直居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 创建单元格内容显示不下时自动换行
        cellStyle.setWrapText(true);
        // 设置单元格字体样式
        XSSFFont font = wb.createFont();
        // 设置字体加粗
        font.setBold(true);
        font.setFontName("宋体");
        font.setFontHeight((short) 500);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 设置表体的单元格样式
     *
     * @return
     */
    public XSSFCellStyle getBodyStyle() {
        // 创建单元格样式
        XSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置单元格居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置单元格垂直居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 创建单元格内容显示不下时自动换行
        cellStyle.setWrapText(true);
        // 设置单元格字体样式
        XSSFFont font = wb.createFont();
        // 设置字体
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyle.setFont(font);
        // 设置单元格边框为细线条
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        return cellStyle;
    }

    /**
     * 设置表体的单元格样式
     *
     * @return
     */
    public XSSFCellStyle getBodyStyleWithoutBorder() {
        // 创建单元格样式
        XSSFCellStyle cellStyle = wb.createCellStyle();
        // 设置单元格居中对齐
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置单元格垂直居中对齐
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 创建单元格内容显示不下时自动换行
        cellStyle.setWrapText(true);
        // 设置单元格字体样式
        XSSFFont font = wb.createFont();
        // 设置字体
        font.setFontName("宋体");
        font.setFontHeight((short) 200);
        cellStyle.setFont(font);
        return cellStyle;
    }

}

3.对所要下载的自定义excel的内容进行设置

 @RequestMapping("/excelDowned")
    /**
     * @Description  ：下载excel模板
     * @author       : bjh
     * @param        : [response]
     * @return       : void
     * @exception    :
     * @date         : 2018/11/13 15:27
     */
    public void excelDowned(HttpServletResponse response){
        try {
            ServletOutputStream outputStream = response.getOutputStream();
            String filename = "xxx模板.xls";
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"));
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setCharacterEncoding("utf-8");
            //表头
            String title = "xxxx表模板";
            //行列数
            int rows = 2;
            int cols = 18;
            //表格
            String[][] excelData = new String[rows][cols];
            /***********************************
                对excelData进行内容的填充
               Note:  数据从第1行开始设置，第0行用来设置表头
            **************************************/           
            ExportExcelUtil.ExportSingleHeadExcel(title, excelData, outputStream);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

4.对应javascript代码：

//下载excel模板
function downExcel() {
    document.getElementById("表单ID").action=""+getRealPath()+"/对应类名/excelDowned";
    document.getElementById("表单ID").submit();
    $.messager.alert('提示','操作成功！','info');
}

5.成功！