导出 Excl 格式

 //导出
   @Autowired
 private DownloadUtil downloadUtil;
 @RequestMapping(value="/product/yield/out.action")
   public void printExcel(HttpServletRequest request,HttpServletResponse 	response) throws Exception{
   String ids = request.getParameter("ids");
//       1、准备导出一个Excel
    XSSFWorkbook workbook = new XSSFWorkbook();
//        2、创建一个工作表
    XSSFSheet sheet = workbook.createSheet("出货数据");
//        3、设置列宽
    sheet.setColumnWidth(1,26*256);
    sheet.setColumnWidth(2,28*256);
    sheet.setColumnWidth(3,26*256);
    sheet.setColumnWidth(4,28*256);
    sheet.setColumnWidth(5,28*256);
    sheet.setColumnWidth(6,28*256);
    sheet.setColumnWidth(7,25*256);
    sheet.setColumnWidth(8,15*256);

//        大标题
    XSSFRow bigTitleRow = sheet.createRow(0);
    bigTitleRow.setHeightInPoints(36);  //行高
//        创建单元格
    for (int i = 0; i < 9; i++) {
        bigTitleRow.createCell(i);
    }
    sheet.addMergedRegion(new CellRangeAddress(0,0,1,8));
    XSSFCell bigTitleRowCell = bigTitleRow.getCell(1);
    bigTitleRowCell.setCellStyle(bigTitle(workbook));
//        inputDate:2019年01    2018年10
    bigTitleRowCell.setCellValue("亩产报表");

    String[] titles = new String[]{"企业编号","企业名称","小标题单位用地税收","单位能耗销售收入","单位主要污染物排放销售收入","研发经费投入强度","全员劳动生产率","年份"};
    XSSFRow titleRow = sheet.createRow(1);
    for (int i = 0; i < titles.length; i++) {
        XSSFCell cell = titleRow.createCell(i+1);
        cell.setCellValue(titles[i]);
        cell.setCellStyle(title(workbook));
    }
//        数据库中查询的需要的数据
    List<SystemBenefit> cpvo = service.selectYield(ids);
    int rowIndex = 2;
    XSSFRow row = null;
    XSSFCell cell = null;
   for (int i = 0; i < cpvo.size(); i++) {
        row = sheet.createRow(rowIndex);
        cell = row.createCell(1);
        cell.setCellStyle(text(workbook));
       cell.setCellValue(cpvo.get(i).getCode());

       cell = row.createCell(2);
       cell.setCellStyle(text(workbook));
       cell.setCellValue(cpvo.get(i).getName());

       cell = row.createCell(3);
        cell.setCellStyle(text(workbook));
        cell.setCellValue(cpvo.get(i).getUnitlandtax());

        cell = row.createCell(4);
        cell.setCellStyle(text(workbook));
        cell.setCellValue(cpvo.get(i).getEnergysales());
        
        cell = row.createCell(5);
        cell.setCellStyle(text(workbook));
        cell.setCellValue(cpvo.get(i).getPollutantdischarge());
        
        cell = row.createCell(6);
        cell.setCellStyle(text(workbook));
        cell.setCellValue(cpvo.get(i).getStrength());

        cell = row.createCell(7);
        cell.setCellStyle(text(workbook));
        cell.setCellValue(cpvo.get(i).getProductivity());

        cell = row.createCell(8);
        cell.setCellStyle(text(workbook));
        cell.setCellValue(cpvo.get(i).getYear());
        rowIndex++;
    }

//        文件的下载
//        一个流  文件的输出流
//        两个头  文件mime类型  文件的打开方式 in-line(在线打开) attachment
//        ServletOutputStream outputStream = response.getOutputStream();
//        response.setContentType("application/vnd.openxmlformats-	officedocument.spreadsheetml.sheet");//设置MIME类型 常用的文件的mime类型可以省略
//        response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode("出货表.xlsx", "UTF-8"));
//        workbook.write(outputStream);
    ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); //创建一个缓存流
//        byteArrayOutputStream.
    workbook.write(byteArrayOutputStream); //把workbook写入到缓存流中
    downloadUtil.download(byteArrayOutputStream,response,"亩产报表.xlsx");
}

//大标题的样式
public CellStyle bigTitle(Workbook wb){
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setFontName("黑体");
    font.setFontHeightInPoints((short)16);
    //font.setBold(true);//字体加粗
    style.setFont(font);
    ((XSSFCellStyle) style).setAlignment(HorizontalAlignment.CENTER);				//横向居中
    ((XSSFCellStyle) style).setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
    return style;
}
//小标题的样式
public CellStyle title(Workbook wb){
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setFontName("黑体");
    font.setFontHeightInPoints((short)12);
    style.setFont(font);
    ((XSSFCellStyle) style).setAlignment(HorizontalAlignment.CENTER);				//横向居中
    ((XSSFCellStyle) style).setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
    ((XSSFCellStyle) style).setBorderTop(BorderStyle.THIN);						//上细线
    ((XSSFCellStyle) style).setBorderBottom(BorderStyle.THIN);					//下细线
    ((XSSFCellStyle) style).setBorderLeft(BorderStyle.THIN);						//左细线
    ((XSSFCellStyle) style).setBorderRight(BorderStyle.THIN);						//右细线
    return style;
}
//文字样式
public CellStyle text(Workbook wb){
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setFontName("Times New Roman");
    font.setFontHeightInPoints((short)10);
    style.setFont(font);
    ((XSSFCellStyle) style).setAlignment(HorizontalAlignment.LEFT);				//横向居左
    ((XSSFCellStyle) style).setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
    ((XSSFCellStyle) style).setBorderTop(BorderStyle.THIN);						//上细线
    ((XSSFCellStyle) style).setBorderBottom(BorderStyle.THIN);					//下细线
    ((XSSFCellStyle) style).setBorderLeft(BorderStyle.THIN);						//左细线
    ((XSSFCellStyle) style).setBorderRight(BorderStyle.THIN);						//右细线
    return style;
}

工具类：DownloadUtil

package com.product.yield;
import org.springframework.stereotype.Component;

 import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
@Component
public class DownloadUtil {

/**
 * @param filePath 要下载的文件路径
 * @param returnName 返回的文件名
 * @param response HttpServletResponse
 * @param delFlag 是否删除文件
 */
protected void download(String filePath,String returnName,HttpServletResponse response,boolean delFlag){
	this.prototypeDownload(new File(filePath), returnName, response, delFlag);
}

/**
 * @param file 要下载的文件
 * @param returnName 返回的文件名
 * @param response HttpServletResponse
 * @param delFlag 是否删除文件
 */
protected void download(File file,String returnName,HttpServletResponse response,boolean delFlag){
	this.prototypeDownload(file, returnName, response, delFlag);
}

/**
 * @param file 要下载的文件
 * @param returnName 返回的文件名
 * @param response HttpServletResponse
 * @param delFlag 是否删除文件
 */
public void prototypeDownload(File file,String returnName,HttpServletResponse response,boolean delFlag){
	// 下载文件
	FileInputStream inputStream = null;
	ServletOutputStream outputStream = null;
	try {
		if(!file.exists()) return;
		response.reset();
		//设置响应类型	PDF文件为"application/pdf"，WORD文件为："application/msword"， EXCEL文件为："application/vnd.ms-excel"。  
		response.setContentType("application/octet-stream;charset=utf-8");

		//设置响应的文件名称,并转换成中文编码
		//returnName = URLEncoder.encode(returnName,"UTF-8");
		returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));	//保存的文件名,必须和页面编码一致,否则乱码
		
		//attachment作为附件下载；inline客户端机器有安装匹配程序，则直接打开；注意改变配置，清除缓存，否则可能不能看到效果
		response.addHeader("Content-Disposition",   "attachment;filename="+returnName);  
		
		//将文件读入响应流
		inputStream = new FileInputStream(file);
		outputStream = response.getOutputStream();
		int length = 1024;
		int readLength=0;
		byte buf[] = new byte[1024];
		readLength = inputStream.read(buf, 0, length);
		while (readLength != -1) {
			outputStream.write(buf, 0, readLength);
			readLength = inputStream.read(buf, 0, length);
		}
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		try {
			outputStream.flush();
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {
			outputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {
			inputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		//删除原文件
		
		if(delFlag) {				
			file.delete();
		}
	}
}

/**
 * by tony 2013-10-17
 * @param byteArrayOutputStream 将文件内容写入ByteArrayOutputStream
 * @param response HttpServletResponse	写入response
 * @param returnName 返回的文件名
 */
public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException{
	response.setContentType("application/octet-stream;charset=utf-8");
	returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));			//保存的文件名,必须和页面编码一致,否则乱码
	response.addHeader("Content-Disposition",   "attachment;filename=" + returnName);  
	response.setContentLength(byteArrayOutputStream.size());
	
	ServletOutputStream outputstream = response.getOutputStream();	//取得输出流
	byteArrayOutputStream.writeTo(outputstream);					//写到输出流
	byteArrayOutputStream.close();									//关闭
	outputstream.flush();											//刷数据
}

}