java导出excel方法 小记

在web开发中，有一个经典的功能，就是数据的导入导出。特别是数据的导出，在生产管理或者财务系统中用的非常普遍，因为这些系统经常要做一些报表打印的工作。而数据导出的格式一般是EXCEL或者PDF，我这里就用两篇文章分别给大家介绍下。（注意，我们这里说的数据导出可不是数据库中的数据导出！么误会啦^_^）

 呵呵，首先我们来导出EXCEL格式的文件吧。现在主流的操作Excel文件的开源工具有很多,用得比较多的就是Apache的POI及JExcelAPI。这里我们用Apache POI！我们先去Apache的大本营下载POI的jar包：http://poi.apache.org/ ，我这里使用的是3.0.2版本。

首先是实体类：

package com.jewery.bean;

public class ExportjeweryBean {
	private String jeweryNo;
	private String jeweryName;
	private String jeweryValue;
	private String startTimeStr;
	private String endTimeStr;
	private String limitOrderAmount;
	private String minOrderAmount;
	public String getjeweryNo() {
		return jeweryNo;
	}
	public void setjeweryNo(String jeweryNo) {
		this.jeweryNo = jeweryNo;
	}
	public String getjeweryName() {
		return jeweryName;
	}
	public void setjeweryName(String jeweryName) {
		this.jeweryName = jeweryName;
	}
	public String getjeweryValue() {
		return jeweryValue;
	}
	public void setjeweryValue(String jeweryValue) {
		this.jeweryValue = jeweryValue;
	}
	public String getStartTimeStr() {
		return startTimeStr;
	}
	public void setStartTimeStr(String startTimeStr) {
		this.startTimeStr = startTimeStr;
	}
	public String getEndTimeStr() {
		return endTimeStr;
	}
	public void setEndTimeStr(String endTimeStr) {
		this.endTimeStr = endTimeStr;
	}
	public String getLimitOrderAmount() {
		return limitOrderAmount;
	}
	public void setLimitOrderAmount(String limitOrderAmount) {
		this.limitOrderAmount = limitOrderAmount;
	}
	public String getMinOrderAmount() {
		return minOrderAmount;
	}
	public void setMinOrderAmount(String minOrderAmount) {
		this.minOrderAmount = minOrderAmount;
	}
	
}

上面这两个类一目了然，就是两个简单的javabean风格的类。

添加一个生成excel头部的工具类：

package com.jewery.util.excel;

public class CellInfo {

    private String fieldName;
    private String cellName;
    private String datePattern = "yyyy-MM-dd";
    private int width = 5000;

    public CellInfo(String fieldName, String cellName) {
        super();
        this.fieldName = fieldName;
        this.cellName = cellName;
    }

    public CellInfo(String fieldName, String cellName, String datePattern) {
        super();
        this.fieldName = fieldName;
        this.cellName = cellName;
        this.datePattern = datePattern;
    }

    public CellInfo(String fieldName, String cellName, String datePattern, int width) {
        super();
        this.fieldName = fieldName;
        this.cellName = cellName;
        this.width = width;
        this.datePattern = datePattern;
    }

    public CellInfo(String fieldName, String cellName, int width) {
        super();
        this.fieldName = fieldName;
        this.cellName = cellName;
        this.width = width;
    }

    public String getFieldName() {
        return fieldName;
    }

    public void setFieldName(String fieldName) {
        this.fieldName = fieldName;
    }

    public String getCellName() {
        return cellName;
    }

    public void setCellName(String cellName) {
        this.cellName = cellName;
    }

    public int getWidth() {
        return width;
    }

    public void setWidth(int width) {
        this.width = width;
    }

    public String getDatePattern() {
        return datePattern;
    }

    public void setDatePattern(String datePattern) {
        this.datePattern = datePattern;
    }

}

导出的工具类：

package com.jewery.util.excel;

import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.List;

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

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.HSSFRichTextString;
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.hssf.util.Region;

import com.ibm.icu.text.SimpleDateFormat;

public class ExcelOpt {

	private HSSFWorkbook wb;
	private HSSFSheet sheet;

	public <T> void exportHttpExcel(HttpServletResponse response, String encodeFileName, String titleName, List<CellConfigInfo> configs, List<T> datas) throws Exception {
		response.reset();
		response.setHeader("Content-Disposition", "attachment;filename=" + encodeFileName + ".xls");// 指定下载的文件名
		response.setContentType("application/vnd.ms-excel");
		OutputStream output = response.getOutputStream();
		this.exportExcel(output, titleName, configs, datas);
	}

	public String encodeFileName(HttpServletRequest request, String filename) throws Exception {
		if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {
			filename = URLEncoder.encode(filename, "UTF-8");// IE浏览器
		} else {
			filename = new String(filename.getBytes("UTF-8"), "ISO8859-1");// firefox浏览器
		}
		return filename;
	}

	public <T> void exportExcel(OutputStream output, String titleName, List<CellConfigInfo> configs, List<T> datas) throws Exception {
		wb = new HSSFWorkbook();
		sheet = wb.createSheet(titleName);
		for (int i = 0; i < configs.size(); i++) {
			sheet.setColumnWidth(i, configs.get(i).getWidth());
		}
		int startRow = 0;
		startRow += createTitle(titleName, configs.size());
		// 创建单元格样式
		startRow += createHead(configs);
		// 循环创建中间的单元格的各项的值

		HSSFCellStyle cellstyle = wb.createCellStyle();
		for (int i = 0; i < datas.size(); i++) {
			HSSFRow row = sheet.createRow((short) (startRow + i));
			// 设置行高
			row.setHeight((short) 500);
			T data = datas.get(i);
			for (int j = 0; j < configs.size(); j++) {
				HSSFCell cell = row.createCell((short) j);
				cell.setCellType(HSSFCell.ENCODING_UTF_16);
				cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
				cell.setCellStyle(cellstyle);
				// cell.setCellValue(new HSSFRichTextString("data"));
				setValue(cell, configs.get(j), data);
			}
			//
		}
		output.flush();
		wb.write(output);
		output.close();
	}

	private <T> void setValue(HSSFCell cell, CellConfigInfo config, T data) throws NoSuchMethodException, SecurityException, IllegalAccessException,
			IllegalArgumentException, InvocationTargetException {
		String methodName = "get" + config.getFieldName().substring(0, 1).toUpperCase() + config.getFieldName().substring(1);
		Method getMethod = data.getClass().getMethod(methodName, new Class[] {});
		Class<?> returnType = getMethod.getReturnType();
		Object filedData = getMethod.invoke(data, new Object[] {});
		if (returnType.getName().equals("java.util.Date")) {
			SimpleDateFormat sf = new SimpleDateFormat(config.getDatePattern());
			String formatDate = sf.format((java.util.Date) filedData);
			cell.setCellValue(new HSSFRichTextString(formatDate));
		} else {
			cell.setCellValue(new HSSFRichTextString(filedData.toString()));
		}
	}

	public int createHead(List<CellConfigInfo> configs) {
		// 创建单元格样式
		HSSFCellStyle cellStyle = wb.createCellStyle();

		// 指定单元格居中对齐
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 指定单元格垂直居中对齐
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		// 指定当单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);

		// 设置单元格字体
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeight((short) 200);
		cellStyle.setFont(font);

		HSSFRow row2 = sheet.createRow(1);
		// 设置行高
		row2.setHeight((short) 1000);

		HSSFCell cell0;

		for (int i = 0; i < configs.size(); i++) {
			cell0 = row2.createCell(i);
			cell0.setCellStyle(cellStyle);
			cell0.setCellValue(new HSSFRichTextString(configs.get(i).getCellName()));
		}
		return 1;
	}

	public int createTitle(String headString, int colSum) {
		HSSFRow row = sheet.createRow(0);
		// 设置第一行
		HSSFCell cell = row.createCell(0);
		row.setHeight((short) 400);
		// 定义单元格为字符串类型
		cell.setCellType(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(new HSSFRichTextString(headString));
		// 指定合并区域
		sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (colSum - 1)));
		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
		cellStyle.setWrapText(true);// 指定单元格自动换行
		// 设置单元格字体
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		font.setFontName("宋体");
		font.setFontHeight((short) 300);
		cellStyle.setFont(font);
		cell.setCellStyle(cellStyle);
		return 1;
	}

}

调用：

<a class="btn btn-xs btn-success" href="${ctx}/catering/jewery/exportJewery/{{jewery.id}}" >
	<i class="icon-th"></i>
	导出
</a>

这里要用a标签，开始我是用的button进行ajax提交的相应的值毫无疑问就是乱码

controller：

@RequestMapping("/exportjewery/{id}")
	public void exportjewery(@PathVariable String id,HttpServletRequest request,HttpServletResponse response) throws UnsupportedEncodingException{
		request.setCharacterEncoding("utf-8");
		try {
			if(id == null){
				id = "";
			}
			List<ExportjeweryBean> jeweryBeans = dishesjeweryService.findAllexportJewery(id);
			List<CellInfo> configs = new ArrayList<CellInfo>(){
				private static final long serialVersionUID = 1L;
				{
					add(new CellInfo("jeweryName", "名称"));
					add(new CellInfo("jeweryNo", "号", 6000));
					add(new CellInfo("jeweryValue", "面值"));
					add(new CellInfo("minOrderAmount", "金额"));
					add(new CellInfo("startTimeStr", "起始时间"));
					add(new CellInfo("endTimeStr", "截止时间"));
				}
			};
			ExcelOpt opt = new ExcelOpt();
			opt.exportHttpExcel(response, excelOpt.encodeFileName(request, "优惠券列表"), "优惠券列表", configs, jeweryBeans);
			dto.message = "导出信息成功";
		} catch (Exception e) {
			e.printStackTrace();
		}
	}