excel导出模板

excel表格生成通用类：ExportExcel.java

package com.bzyth.common;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

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

import org.apache.struts2.ServletActionContext;

public class ExportExcel {
	/**
	 * 单独导出,固定列宽
	 * 
	 * @param request
	 * @param response
	 * @param list
	 *            结果集
	 * @param en
	 *            配置文件英文
	 * @param zh
	 *            配置文件中文
	 * @param len
	 *            配置文件列宽
	 * @param toprow
	 *            合并单元格名称
	 */
	public void exportForExcel(HttpServletRequest request, HttpServletResponse response, List<Map> list, String en, String zh,String len, String toprow,String sheetname)  {
		try {
			
			//response.setCharacterEncoding("utf-8");
			response.setContentType("Application/excel");
			response.addHeader("Content-Disposition", "attachment;filename=" + new String((sheetname + ".xls").getBytes("GB2312"), "ISO8859-1"));
		
			// 查询的列名
			List<String> col_alis = new ArrayList<String>();
			// 查询的列名-对应的汉字名
			List<String> col_name = new ArrayList<String>();
			// 查询的列名-对应的汉字名
			List <String> col_len = new ArrayList<String>();

			for (String pro_en : en.split(",")) {
				col_alis.add(pro_en);
			}
			for (String pro_zh : zh.split(",")) {
				col_name.add(pro_zh);
			}
			for (String pro_zh : len.split(",")) {
				col_len.add(pro_zh);
			}
			ExcelUtils excel = new ExcelUtils(sheetname,col_len);
			int rowIndex = 0;
			// 是否显示合并单元格的内容
			if (!"".equals(toprow.trim())) {
				// 合并表头单元格
				excel.createTopRow(toprow, col_name.size() - 1);
				rowIndex++;
			}
			// 添加表头显示部分
			// 第一行显示标题
			excel.createRow(rowIndex++);
			for (int j = 0; j < col_name.size(); j++) {
				excel.setCell(j, col_name.get(j), "0");
			}
			if(list!=null&&list.size()>0){
				for (int k = 0; k < list.size(); k++) {
					excel.createRow(rowIndex++);
					for (int i = 0; i < col_alis.size(); i++) {
						String val = String.valueOf(list.get(k).get(col_alis.get(i).toString()));
						if (col_alis.get(i).toString().equals("GZ_MONTH")) {
							excel.setCellMerge(i,k, Integer.parseInt(list.get(k).get("COUNTNUM").toString()), val.equals("null") ? "" : val,list.get(k).get("MERGENUM").toString());// 将获取的指定列明的值写入单元格
						} else {
							excel.setCell(i, val.equals("null") ? "" : val);// 将获取的指定列明的值写入单元格
						}
					}
				}
			}
		

			excel.exportXls(response);
		} catch (Exception e) {
			e.printStackTrace();
		} 

	}

	/**
	 * 单独导出,可调整列宽
	 * 
	 * @param request
	 * @param response
	 * @param list
	 *            结果集
	 * @param en
	 *            配置文件英文
	 * @param zh
	 *            配置文件中文
	 * @param len
	 *            配置文件各长度
	 * @param toprow
	 *            合并单元格名称
	 */
	public void exportForExcelFreeLen(HttpServletRequest request, HttpServletResponse response, List<Map> list, String en, String zh, String len, String toprow, String sheet1name,String xlsname) {
		String sheetname = "data";
		if (!sheet1name.equals("")) {
			sheetname = sheet1name;
		}
		response.setContentType("Application/excel");
		if (!xlsname.equals("")) {
			try {
				response.addHeader("Content-Disposition", "attachment;filename=" + new String((xlsname + ".xls").getBytes("GB2312"), "ISO8859-1"));
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
		} else {
			response.addHeader("Content-Disposition", "attachment;filename=data.xls");
		}
		export(request, response, list, en, zh, len, toprow, sheetname);

	}

	/**
	 * 导出：导入功能中的下载
	 * 
	 * @param request
	 * @param response
	 * @param list
	 *            结果集
	 * @param en
	 *            配置文件英文
	 * @param zh
	 *            配置文件中文
	 * @param xlsname
	 *            导出文件名称
	 * @param sheet1name
	 *            EXCEL页签名称
	 */
	public void exportForExcelOfImp(HttpServletRequest request, HttpServletResponse response, List<Map> list, String en, String zh, String len, String xlsname, String sheet1name) {
		String sheetname = "";
		response.setContentType("Application/excel");
		if (!xlsname.equals("")) {
			try {
				response.addHeader("Content-Disposition", "attachment;filename=" + new String((xlsname + ".xls").getBytes("GB2312"), "ISO8859-1"));
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
		} else {
			response.addHeader("Content-Disposition", "attachment;filename=data.xls");
		}
		if (!sheet1name.equals("")) {
			sheetname = sheet1name;
		}
		// String sheetname = "data";
		
		export(request, response, list, en, zh, len, "", sheetname);
		
	}

	/**
	 * 导入，写值
	 * 
	 * @param request
	 * @param response
	 * @param list
	 *            结果集
	 * @param en
	 *            配置文件英文
	 * @param zh
	 *            配置文件中文
	 * @param len
	 *            配置文件上各列长度
	 * @param toprow
	 *            EXCEL第一行表头（合并单元格）
	 * @param sheetname
	 *            页签名称
	 */
	private void export(HttpServletRequest request, HttpServletResponse response, List<Map> list, String en, String zh, String len, String toprow, String sheetname) {
		Properties properties = new Properties();
		InputStream inputStream = null;

		String pathServer = ServletActionContext.getServletContext().getRealPath(File.separator);// 得到的绝对路径

		try {
			inputStream = new BufferedInputStream(new FileInputStream(pathServer + "/WEB-INF" + File.separator + "colcomments.properties"));
			properties.load(inputStream);

			// 查询的列名
			List<String> col_alis = new ArrayList<String>();
			// 查询的列名-对应的汉字名
			List<String> col_name = new ArrayList<String>();
			// 查询的列名-对应的长度
			List<String> col_length = new ArrayList<String>();

			for (String pro_en : properties.getProperty(en).split(",")) {
				col_alis.add(pro_en);
			}
			for (String pro_zh : properties.getProperty(zh).split(",")) {
				col_name.add(pro_zh);
			}
			if (!len.equals("")) {
				for (String pro_len : properties.getProperty(len).split(",")) {
					col_length.add(pro_len);
				}
			}
			ExcelUtils excel = new ExcelUtils(sheetname, col_length);
			int rowIndex = 0;
			// 是否显示合并单元格的内容
			if (!"".equals(toprow.trim())) {
				// 合并表头单元格
				excel.createTopRow(toprow, col_name.size() - 1);
				rowIndex++;
			}
			// 添加表头显示部分
			// 第一行显示标题
			excel.createRow(rowIndex++);
			for (int j = 0; j < col_name.size(); j++) {
				excel.setCell(j, col_name.get(j), "0");
			}
			for (int k = 0; k < list.size(); k++) {
				excel.createRow(rowIndex++);
				for (int i = 0; i < col_alis.size(); i++) {
					String val = String.valueOf(list.get(k).get(col_alis.get(i).toString()));
					if(en.equals("FWHTXX_EN")){
						if(!val.equals("null")&&!val.startsWith("0")&&(val.indexOf(".")<0)&&isInteger(val)){
							excel.setCell(i, Integer.parseInt(val));// 将获取的指定列明的值写入单元格
						}else if(!val.equals("null")&&isDouble(val)){
							excel.setCell(i, Double.parseDouble(val));// 将获取的指定列明的值写入单元格
						}else{
							excel.setCell(i, val.equals("null") ? "" : val);// 将获取的指定列明的值写入单元格
						}
					}else{
						excel.setCell(i, val.equals("null") ? "" : val);// 将获取的指定列明的值写入单元格
					}
				}
			}

			excel.exportXls(response);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				inputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}

		}

	}

	/**
	 * 判断字符串是否是整数
	 */
	private static boolean isInteger(String value) {
		try {
			Integer.parseInt(value);
			return true;
		} catch (NumberFormatException e) {
			return false;
		}
	}

	/**
	 * 判断字符串是否是浮点数
	 */
	private static boolean isDouble(String value) {
		try {
			Double.parseDouble(value);
			if (value.contains("."))
				return true;
			return false;
		} catch (NumberFormatException e) {
			return false;
		}
	}
	
	
}

实例：ExportTest.java

package com.bzyth.action.problemManager;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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

import com.bzyth.common.ExportExcel;

public class ExportTest {

	/**
	 * Http Request
	 */
	protected static HttpServletRequest request;
	/**
	 * Http Response
	 */
	protected static HttpServletResponse response;

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			List<HashMap> list = new ArrayList<HashMap>();
			//sql:select * from bz_wtsl s;
			list = getDataList();//取得数据源
			
			//List<HashMap> 转 List<Map>
			List<Map> lists = new ArrayList<Map>();
			Map map1 = new HashMap();
			for (int i = 0; i < list.size(); i++) {
				map1 = list.get(i);
				lists.add(map1);
			}
			
			//new ExportExcel()
			ExportExcel exp = new ExportExcel();
			String en = "", zh = "", len = "";

			//英文表头
			en = "TBR,TBSJ,DW,ZJ,SJ,XTMC,WTFL,WTJB,WTXF,YXFW,WTMS,WTLY,FPSJ,CLR,CLQK,CLSJ,WTZT,RK,BXXS,SPR,THYY";
			//中文表头
			zh = "提报人,提报时间,提报单位,座机,手机,系统名称,问题分类,问题级别,问题细分,影响范围,问题描述,问题来源,分派时间,处理人,处理情况,处理时间,问题状态,是否入库,表现形式,审批人,退回原因";
			//列宽
			len = "3000,5000,5000,3000,3000,5000,3000,3000,3000,3000,10000,3000,5000,3000,5000,5000,3000,3000,3000,3000,3000";
			//数据导出
			exp.exportForExcel(request, response, lists, en, zh, len, "问题受理列表",
					"问题受理列表");

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public HttpServletRequest getRequest() {
		return request;
	}

	public void setRequest(HttpServletRequest request) {
		this.request = request;
	}

	public HttpServletResponse getResponse() {
		return response;
	}

	public void setResponse(HttpServletResponse response) {
		this.response = response;
	}

	public ProblemManagerBiz getProblemManagerBiz() {
		return problemManagerBiz;
	}

	public void setProblemManagerBiz(ProblemManagerBiz problemManagerBiz) {
		this.problemManagerBiz = problemManagerBiz;
	}

}

结果展示：