java导出Excel并下载

前端界面：

controller层：

ExcelExportUtil工具 ：

package nc.utils.exportexcel;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.List;

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

import nc.bs.framework.common.RuntimeEnv;
import nc.vo.ebvp.FhDInfoBody;
import nc.vo.ebvp.FhDInfoHeadVO;
import nc.vo.pub.BusinessException;
import nc.vo.pub.lang.UFDateTime;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * 导出Excel
 * @author ChenSiyi
 *
 */
@SuppressWarnings("restriction")
public class ExcelExportUtil {

	@SuppressWarnings({"unchecked" })
	public void exportData(HttpServletRequest request,HttpServletResponse response, 
			List<Object> list, String pkSupply) throws BusinessException{
		try{
			
			//1.判断是否获取数据
			if(list ==null || list.isEmpty()){
				throw new BusinessException("未获取到发货安排相关信息！");
			}
			//2.创建HSSFWorkbook对象
			HSSFWorkbook workbook = new HSSFWorkbook();
			//3.创建HSSFSheet对象
			HSSFSheet sheet = workbook.createSheet("发货安排 ");
			
			//4.创建合并单元格对象
			this.creatCellRange(sheet);
			
			//5.创建行，并加载值
			//5.1 标题行
			HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);
            //加载单元格样式
            HSSFCellStyle headStyle = createCellStyle(workbook, (short)16, false, true);//HSSFWorkbook对象, 字体大小， 是否加粗， 是否水平居中
            cell.setCellStyle(headStyle);
            cell.setCellValue("发货安排");
            
            //5.2 获取值
            FhDInfoHeadVO dInfoHeadVO = (FhDInfoHeadVO)list.get(0);
            
            //5.3 赋值
            HSSFRow row1 = sheet.createRow(1);
            HSSFCell fhdhCell = row1.createCell(0);
            fhdhCell.setCellValue("发货单号");
            HSSFCell fhdhValueCell = row1.createCell(2);
            fhdhValueCell.setCellValue(dInfoHeadVO.getFhdh());
            HSSFCell fhrqCell = row1.createCell(8);
            fhrqCell.setCellValue("发货日期");
            HSSFCell fhrqValueCell = row1.createCell(10);
            fhrqValueCell.setCellValue(dInfoHeadVO.getFhrq());
            
            HSSFRow row2 = sheet.createRow(2);
            HSSFCell cygsCell = row2.createCell(0);
            cygsCell.setCellValue("承运公司");
            HSSFCell cygsValueCell = row2.createCell(2);
            cygsValueCell.setCellValue(dInfoHeadVO.getCygs());
            HSSFCell cphCell = row2.createCell(8);
            cphCell.setCellValue("车牌号");
            HSSFCell cphValueCell = row2.createCell(10);
            cphValueCell.setCellValue(dInfoHeadVO.getCph());
            
            HSSFRow row3 = sheet.createRow(3);
            HSSFCell cyrCell = row3.createCell(0);
            cyrCell.setCellValue("承运人");
            HSSFCell cyrValueCell = row3.createCell(2);
            cyrValueCell.setCellValue(dInfoHeadVO.getCyr());
            HSSFCell cyrdhCell = row3.createCell(8);
            cyrdhCell.setCellValue("承运人电话");
            HSSFCell cyrdhValueCell = row3.createCell(10);
            cyrdhValueCell.setCellValue(dInfoHeadVO.getCyrsj());
            
            HSSFRow row4 = sheet.createRow(4);
            HSSFCell shgsCell = row4.createCell(0);
            shgsCell.setCellValue("收货公司");
            HSSFCell shgsValueCell = row4.createCell(2);
            shgsValueCell.setCellValue(dInfoHeadVO.getShgs());
            HSSFCell gysCell = row4.createCell(8);
            gysCell.setCellValue("供应商");
            HSSFCell gysValueCell = row4.createCell(10);
            gysValueCell.setCellValue(dInfoHeadVO.getGysmc());
            
            List<FhDInfoBody> list_body = (List<FhDInfoBody>) list.get(1);
            if(list_body == null || list_body.isEmpty()) {
            	throw new BusinessException("未获取表格数据！");
            }
            
            HSSFRow row5 = sheet.createRow(5);
            HSSFCell shrCell = row5.createCell(0);
            shrCell.setCellValue("收货人");
            HSSFCell shrValueCell = row5.createCell(2);
            shrValueCell.setCellValue(list_body.get(0).getShr());
            HSSFCell shrdhCell = row5.createCell(8);
            shrdhCell.setCellValue("收货人电话");
            HSSFCell shrdhValueCell = row5.createCell(10);
            shrdhValueCell.setCellValue(list_body.get(0).getShrdh());
            
            HSSFRow row6 = sheet.createRow(6);
            HSSFCell shdzCell = row6.createCell(0);
            shdzCell.setCellValue("收货地址");
            HSSFCell shdzValueCell = row6.createCell(2);
            shdzValueCell.setCellValue(dInfoHeadVO.getShdz());
            
            //表格
            HSSFRow row7 = sheet.createRow(7);
            String[] strs = {"SAP订单号","订单号","物资编码","物资名称","规格","型号","计量单位","需求到货日期","订单数量","含税单价","价税合计",
            		"供应商批次","实发数量","收票公司","需求人","备注（需求说明）"};
            for(int i=0;i<strs.length;i++){
            	HSSFCell cell2 = row7.createCell(i);
                cell2.setCellValue(strs[i]);
            }
            
            if(list_body != null && list_body.size() > 0) {
            	for(int i=0;i<list_body.size();i++){
            		FhDInfoBody temp = list_body.get(i);
            		
            		//生成列
            		HSSFRow rowBody = sheet.createRow(i+1+7);
            		HSSFCell sapddhCell = rowBody.createCell(0);
            		sapddhCell.setCellValue(temp.getSapddh());//SAP订单号
            		
            		HSSFCell ddhCell = rowBody.createCell(1);
            		ddhCell.setCellValue(temp.getDdh());//订单号
            		
            		HSSFCell wzbmCell = rowBody.createCell(2);
            		wzbmCell.setCellValue(temp.getWzbm());//物资编码
            		
            		HSSFCell wzmcCell = rowBody.createCell(3);
            		wzmcCell.setCellValue(temp.getWzmc());//物资名称
            		
            		HSSFCell guigeCell = rowBody.createCell(4);
            		guigeCell.setCellValue(temp.getGuige());//规格
            		
            		HSSFCell xinghaoCell = rowBody.createCell(5);
            		xinghaoCell.setCellValue(temp.getXinghao());//型号
            		
            		HSSFCell jldwCell = rowBody.createCell(6);
            		jldwCell.setCellValue(temp.getJldw());//计量单位
            		
            		HSSFCell xqdhrqCell = rowBody.createCell(7);
            		xqdhrqCell.setCellValue(temp.getXqdhrq());//需求到货日期
            		
            		HSSFCell ddslCell = rowBody.createCell(8);
            		ddslCell.setCellValue(temp.getDdsl());//订单数量
            		
            		HSSFCell hsdjCell = rowBody.createCell(9);
            		hsdjCell.setCellValue(temp.getHsdj());//含税单价
            		
            		HSSFCell jshjCell = rowBody.createCell(10);
            		jshjCell.setCellValue(temp.getJshj());//价税合计
            		
            		HSSFCell gyspcCell = rowBody.createCell(11);
            		gyspcCell.setCellValue(temp.getGyspc());//供应商批次
            		
            		HSSFCell sfslCell = rowBody.createCell(12);
            		sfslCell.setCellValue(temp.getSfsl());//实发数量
            		
            		HSSFCell spgsCell = rowBody.createCell(13);
            		spgsCell.setCellValue(temp.getSpgs());//收票公司
            		
            		HSSFCell xqrCell = rowBody.createCell(14);
            		xqrCell.setCellValue(temp.getXqr());//需求人
            		
            		HSSFCell bzCell = rowBody.createCell(15);
            		bzCell.setCellValue(temp.getBz());//备注（需求说明）
            	}
            	
            }
            
            String path = RuntimeEnv.getInstance().getNCHome();
            path = path+"/work/"+pkSupply+new UFDateTime().getMillis()+".xls";
            
            FileOutputStream output=new FileOutputStream(path);
            workbook.write(output);
            output.flush();
            
            response.setHeader("Content-Disposition", "attachment;Filename=" + pkSupply + System.currentTimeMillis() + ".xls");
            OutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
            outputStream.close();
			
		}catch(Exception e){
			throw new BusinessException(e.getMessage());
		}
	}
	
	/**
	 * 创建合并单元格对象
	 * @param sheet
	 * @throws BusinessException
	 */
	private void creatCellRange(HSSFSheet sheet) throws BusinessException{
		try{
			//创建合并单元格对象-标题
			CellRangeAddress titleAddress = new CellRangeAddress(0,0,0,15);//起始行,结束行,起始列,结束列
			
			//创建合并单元格对象-发货单号
			CellRangeAddress fhdhAddress = new CellRangeAddress(1,1,0,1);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-发货单号值
			CellRangeAddress fhdhValueAddress = new CellRangeAddress(1,1,2,7);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-发货日期
			CellRangeAddress fhrqAddress = new CellRangeAddress(1,1,8,9);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-发货日期值
			CellRangeAddress fhrqValueAddress = new CellRangeAddress(1,1,10,15);//起始行,结束行,起始列,结束列
			
			//创建合并单元格对象-承运公司
			CellRangeAddress cygsAddress = new CellRangeAddress(2,2,0,1);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-承运公司值
			CellRangeAddress cygsValueAddress = new CellRangeAddress(2,2,2,7);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-车牌号
			CellRangeAddress cphAddress = new CellRangeAddress(2,2,8,9);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-车牌号值
			CellRangeAddress cphValueAddress = new CellRangeAddress(2,2,10,15);//起始行,结束行,起始列,结束列
			
			//创建合并单元格对象-承运人
			CellRangeAddress cyrAddress = new CellRangeAddress(3,3,0,1);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-承运人值
			CellRangeAddress cyrValueAddress = new CellRangeAddress(3,3,2,7);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-承运人电话
			CellRangeAddress cyrsjAddress = new CellRangeAddress(3,3,8,9);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-承运人电话值
			CellRangeAddress cyrsjValueAddress = new CellRangeAddress(3,3,10,15);//起始行,结束行,起始列,结束列
			
			//创建合并单元格对象-收货公司
			CellRangeAddress shgsAddress = new CellRangeAddress(4,4,0,1);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-收货公司值
			CellRangeAddress shgsValueAddress = new CellRangeAddress(4,4,2,7);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-供应商
			CellRangeAddress gysmcAddress = new CellRangeAddress(4,4,8,9);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-供应商值
			CellRangeAddress gysmcValueAddress = new CellRangeAddress(4,4,10,15);//起始行,结束行,起始列,结束列
			
			//创建合并单元格对象-收货人
			CellRangeAddress shrAddress = new CellRangeAddress(5,5,0,1);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-收货人值
			CellRangeAddress shrValueAddress = new CellRangeAddress(5,5,2,7);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-收货人电话
			CellRangeAddress shrdhAddress = new CellRangeAddress(5,5,8,9);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-收货人电话值
			CellRangeAddress shrdhValueAddress = new CellRangeAddress(5,5,10,15);//起始行,结束行,起始列,结束列
			
			//创建合并单元格对象-收货地址
			CellRangeAddress shrdzAddress = new CellRangeAddress(6,6,0,1);//起始行,结束行,起始列,结束列
			//创建合并单元格对象-收货地址值
			CellRangeAddress shrdzValueAddress = new CellRangeAddress(6,6,2,15);//起始行,结束行,起始列,结束列
			
			//加载合并单元格对象
            sheet.addMergedRegion(titleAddress);//标题合并单元格
            
            sheet.addMergedRegion(fhdhAddress);//发货单号合并单元格
            sheet.addMergedRegion(fhdhValueAddress);//发货单号值合并单元格
            sheet.addMergedRegion(fhrqAddress);//发货日期合并单元格
            sheet.addMergedRegion(fhrqValueAddress);//发货日期值合并单元格
            
            sheet.addMergedRegion(cygsAddress);//承运公司合并单元格
            sheet.addMergedRegion(cygsValueAddress);//承运公司值合并单元格
            sheet.addMergedRegion(cphAddress);//车牌号合并单元格
            sheet.addMergedRegion(cphValueAddress);//车牌号值合并单元格
            
            sheet.addMergedRegion(cyrAddress);//承运人合并单元格
            sheet.addMergedRegion(cyrValueAddress);//承运人值合并单元格
            sheet.addMergedRegion(cyrsjAddress);//承运人合并单元格
            sheet.addMergedRegion(cyrsjValueAddress);//承运人值合并单元格
            
            sheet.addMergedRegion(shgsAddress);//收货公司合并单元格
            sheet.addMergedRegion(shgsValueAddress);//收货公司值合并单元格
            sheet.addMergedRegion(gysmcAddress);//供应商合并单元格
            sheet.addMergedRegion(gysmcValueAddress);//供应商值合并单元格
            
            sheet.addMergedRegion(shrAddress);//收货人合并单元格
            sheet.addMergedRegion(shrValueAddress);//收货人值合并单元格
            sheet.addMergedRegion(shrdhAddress);//收货人电话合并单元格
            sheet.addMergedRegion(shrdhValueAddress);//收货人电话值合并单元格
            
            sheet.addMergedRegion(shrdzAddress);//收货地址合并单元格
            sheet.addMergedRegion(shrdzValueAddress);//收货地址值合并单元格
            
            //设置默认列宽
            sheet.setDefaultColumnWidth(15);
            
		}catch(Exception e){
			throw new BusinessException(e.getMessage());
		}
	}
	
    /**
     * 
     * @param workbook
     * @param fontsize
     * @return 单元格样式
     */
    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize,boolean flag,boolean flag1) {
        HSSFCellStyle style = workbook.createCellStyle();
        //是否水平居中
        if(flag1){
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        }
       
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        //创建字体
        HSSFFont font = workbook.createFont();
        //是否加粗字体
        if(flag){
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }
	
}

导出结果：