java导出excel使用poi

 controller层，我这里的获得到的fieldJson为:

[{"ck":true,"SeqNo":1,"FieldName":"CreationTime","ColumnName":"登记日期","DataFormat":"","Width":269},
{"ck":true,"SeqNo":2,"FieldName":"BsId","ColumnName":"组织机构代码","DataFormat":"","Width":269},
{"ck":true,"SeqNo":3,"FieldName":"BsName","ColumnName":"企业名称","DataFormat":"","Width":269},
{"ck":true,"SeqNo":4,"FieldName":"State","ColumnName":"企业状态","DataFormat":"","Width":269},
{"ck":true,"SeqNo":5,"FieldName":"BsType","ColumnName":"业务类型","DataFormat":"","Width":269},
{"ck":false,"SeqNo":6,"FieldName":"id","ColumnName":"id","DataFormat":"","Width":275}]

/**
	 * 根据用户自定义字段导出excel
	 * @param map
	 * @param response
	 * @return
	 * @throws Exception
	 */

	@SuppressWarnings("rawtypes")
	@RequestMapping("/exportxls")
	public ModelAndView export(@RequestParam Map map,javax.servlet.http.HttpServletResponse response) throws Exception {
		String fieldJson = (String) map.get("fieldJson");
		if (!StringUtils.isEmpty(fieldJson)) {
			List<Map<String, Object>> listObjectFir = (List)JSONArray.parse(fieldJson);
			// 创建HSSFWorkbook对象(excel的文档对象)
			HSSFWorkbook wb = informService.exprot(listObjectFir);
			// 输出Excel文件
			OutputStream output = response.getOutputStream();
			// response.reset();
			String fileName="企业信息导出";
			fileName = new String(fileName.getBytes(), "ISO-8859-1");//解决下载时文件名丢失
			response.setHeader("Content-Disposition","attachment;filename="+ fileName +".xls");
			response.setContentType("application/octet-stream;charset=uft-8");
			wb.write(output);
			output.write(wb.getBytes());
			output.close();
		}
		return jsonModelAndView("");
	}

service层

/**
	 * 导出excel组装表格和数据
	 * @param list
	 * @return
	 */
	
	public HSSFWorkbook exprot(List list) {
		// 创建HSSFWorkbook对象(excel的文档对象)
		HSSFWorkbook wb = new HSSFWorkbook();
		// 建立新的sheet对象（excel的表单）
		HSSFSheet sheet = wb.createSheet("企业信息");
		// 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
		HSSFRow row1 = sheet.createRow(0);
		// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
		HSSFCell cell = row1.createCell(0);
		//创建ExportExcel util获取样式
		ExportExcel exportExcel=new ExportExcel();
		HSSFCellStyle columnTopStyle = exportExcel.getColumnTopStyle(wb);//获取列头样式对象
		HSSFCellStyle style = exportExcel.getStyle(wb);                    //单元格样式对象
		// 设置单元格内容,设置title
		cell.setCellValue("企业信息表");
		cell.setCellStyle(columnTopStyle);
		//设置行高
		row1.setHeight((short) (25 * 30)); //设置高度
		
		// 在sheet里创建第二行
		HSSFRow row2 = sheet.createRow(1);
		row2.setHeight((short) (25 * 25)); //设置高度
		int count=0;
		HashMap<Integer, String> field=new HashMap();
		HSSFCell cell1=null;
		cell1=row2.createCell(0);
		cell1.setCellValue("序号");
		cell1.setCellStyle(columnTopStyle);
		sheet.setColumnWidth(0,269*20);//设置列宽
		for (int i = 1; i < list.size()+1; i++) {//遍历设置表头
			Map map=(Map)list.get(i-1);
			if((boolean)map.get("ck")){
				String str=(String)map.get("FieldName");
				field.put(count, str);
				// 创建单元格并设置单元格内容
				cell1=row2.createCell(i);
				cell1.setCellValue((String)map.get("ColumnName"));
				cell1.setCellStyle(columnTopStyle);
				sheet.setColumnWidth(i,(Integer)map.get("Width")*20);//设置列宽
				count++;
			}
		}
		// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, count));
		
		// 在sheet里创建第三行
		List<Map> cList =findInformList();

		HSSFRow row;

		for (int i = 0; i < cList.size(); i++) {
			row = sheet.createRow(i + 2);
			row.setHeight((short) (25 * 20)); //设置高度
			HSSFCell indexCell=row.createCell(0);//设置序号列
			indexCell.setCellValue(i+1);//值
			indexCell.setCellStyle(style);//样式
			for (int j = 0; j < count; j++) {
				HSSFCell  hssfcell=row.createCell(j+1);//创建cell写入数据
				if("CreationTime".equals(field.get(j))){
					if(cList.get(i).get(field.get(j))!=null){
						hssfcell.setCellValue(DateUtils.parseDate((Date)cList.get(i).get(field.get(j))));
					}
				}else if("State".equals(field.get(j))){
					if(cList.get(i).get("State")!=null){
						if((Integer)(cList.get(i).get("State"))==1){
							hssfcell.setCellValue("正常");
						}else if((Integer)(cList.get(i).get(field.get(j)))==-1){
							hssfcell.setCellValue("已删除");
						}
					}
				}else{
					hssfcell.setCellValue((String)cList.get(i).get(field.get(j)));
				}
				hssfcell.setCellStyle(style);
				
			}
			
		}
		return wb;
	}


/**
	 * 获取list页面的信息
	 * @return
	 */
	public List findInformList(){
		String sql="select a.id,a.BsId,a.BsName,a.State,GROUP_CONCAT(c.BsType SEPARATOR ',') BsType,a.CreationTime from bus_information a left JOIN bus_certificate c ON a.id=c.BsId and c.State !=-1 GROUP BY a.id,a.bsid,a.BsName,a.State,a.CreationTime";
		List<Map> list = super.getRepository().findBySQL(sql);
		return list;
	}

 

utils类主要设置样式

/**
 * excel表格样式
 * @author Administrator
 *
 */
@SuppressWarnings("deprecation")
public class ExportExcel{
 

    /*
     * 列头单元格样式
     */
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
 
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)11);
        //字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
 
        //设置单元格背景颜色
        style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
 
        return style;
 
    }
 
    /*
     * 列数据信息单元格样式
     */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        //font.setFontHeightInPoints((short)10);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
 
        return style;
    }
}