jxl 导出excel springMVC

参考网上的资料，改了改，顺便记录下：

方法一：

Controller 调用

/*-------------------改造自 AbstractJExcelView-------------------------------------*/
// 创建表格标题
String[] titles = {"", "", "", "", "",
 "", "","","",""};
//列名
String[] columns = {"你自己的列名", "对应你数据库的字段", "", "", "",
		 "", "","","",""};
//列宽
Integer[] widths = {25, 20, 30, 20, 20, 20, 20, 20, 20, 30};
//sheet名称
String sheetName = "考试场次"; 
String fileName = "考试场次.xls";
//excel 名称
String excel_name = new String(fileName.getBytes("GBK"), "ISO8859-1");

//导出的数据
List<XxBean> xxBeanList = xxService
		.queryXxList(xxBean);

model.put("list", kaoShiChangCiBeanList);		
model.put("columns", columns);
model.put("titles", titles);
model.put("widths", widths);

//request 没有用，暂时传过去
new MyJxlExcelUtil().buildExcelDocument(model, sheetName, excel_name, request, response);
/*--------------------------------------------------------*/

方法二：

Controller 调用

/*-------------------另一种方法-------------------------------------*/
String filename = "考试场次"; 
String excel_name = new String(filename.getBytes("GBK"), "ISO8859-1"); 

response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", "attachment; filename="
		+ excel_name+ ".xls");

//导出的数据
List<XxBean> xxBeanList = xxService
		.queryXxList(xxBean);
		
LinkedHashMap<String, String> keyMap = new LinkedHashMap<String, String>();
keyMap.put("你的列名", "对应的excel列名");
keyMap.put("status_name", "状态");
//省略....

//jxl的自动列宽对中文支持不好，设置列宽度
Integer[] columns_widths = {30, 25, 25, 25, 25, 25, 25, 25, 25, 40};

// 设置调用的方法名
Map<String, String> methodMap = new HashMap<String, String>();
// 方法可以在JxlExcelUtils定义，会根据名字进行调用，可以用来处理一些数据，如decode之类的功能
// methodMap.put("cardType", "cardTypeConverter");

JxlExcelUtil jxlExcelUtil = new JxlExcelUtil();
OutputStream out = response.getOutputStream();
if (null != xxBeanList
		&& xxBeanList.size() > 0) {
	//生成Excel
	jxlExcelUtil.getExcelStream(response,filename, keyMap,
			xxBeanList, out, methodMap,columns_widths);
}
/*--------------------------------------------------------*/

MyJxlExcelUtil.java

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

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

import jxl.SheetSettings;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.beanutils.PropertyUtils;

/**
 * @Description: Excel导出
 * @author lzh_me@126.com
 * @date 2017年1月11日 下午1:50:58
 * @version V1.0
 */
public class MyJxlExcelUtil {
	
	//默认值
	private String[] columnNames = new String[] {};

	private String[] dbColumnNames = new String[] {};

	private Integer[] columnWidths = new Integer[] {};

	@SuppressWarnings("unchecked")
	public void buildExcelDocument(Map<String, Object> map, String sheetName, String excelName,  HttpServletRequest request,
			HttpServletResponse response) {
		
		String[] titles = (String[]) map.get("titles");
		if (null != titles && titles.length > 0) {
			columnNames = titles;
		}
		String[] columns = (String[]) map.get("columns");
		if (null != columns && columns.length > 0) {
			dbColumnNames = columns;
		}

		Integer[] widths = (Integer[]) map.get("widths");
		if (null != widths && widths.length > 0) {
			columnWidths = widths;
		}
		
		
		OutputStream os = null;
		WritableWorkbook workbook = null;
		try {
			os = response.getOutputStream();
			workbook = Workbook.createWorkbook(os);

			// 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
			response.setContentType("APPLICATION/OCTET-STREAM");
			response.setHeader("Content-Disposition", "attachment; filename="
					+ excelName);
			os = response.getOutputStream();

			// 全局设置
			WorkbookSettings setting = new WorkbookSettings();
			java.util.Locale locale = new java.util.Locale("zh", "CN");
			setting.setLocale(locale);
			setting.setEncoding("UTF-8");
			
			// 创建工作薄
			workbook = Workbook.createWorkbook(os); // 建立excel文件
			// 创建第一个工作表
			jxl.write.WritableSheet ws = workbook.createSheet(sheetName, 0); // sheet名称
			SheetSettings ss = ws.getSettings();
			ss.setVerticalFreeze(1);// 冻结表头
			
			// 添加标题
			this.addColumNameToWsheet(ws);

			List<Object> list = (List<Object>) map.get("list");
			this.writeContext(ws, list);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 写入文件
			try {
				workbook.write();
				workbook.close();
				os.flush();
				os.close();
			} catch (WriteException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}

		}

	}

	/**
	 * 
	 * @Description: 添加内容
	 * @param wsheet
	 * @param list
	 *            void
	 */
	private <T> void writeContext(WritableSheet wsheet, List<T> list) {
		int rows = list.size();
		jxl.write.Label wlabel = null;
		jxl.write.WritableCellFormat wcf = getFormat();
		int cols = dbColumnNames.length;
		String columnName = null;
		Object value = null;
		try {
			for (int i = 0; i < rows; i++) {
				T t = (T) list.get(i);
				for (int j = 0; j < cols; j++) {
					columnName = dbColumnNames[j].toLowerCase();
					//null 转为空字符串
					value = PropertyUtils.getProperty(t, columnName)==null? "":PropertyUtils.getProperty(t, columnName);
					wlabel = new jxl.write.Label(j, (i + 1), value + "", wcf);
					wlabel = new jxl.write.Label(j, (i + 1), value + "");
					wsheet.addCell(wlabel);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	// 添加标题样式
	private void addColumNameToWsheet(jxl.write.WritableSheet wsheet)
			throws RowsExceededException, WriteException {

		// 设置excel标题
		jxl.write.WritableFont wfont = getFont();
		if (null == wfont) {
			wfont = new WritableFont(WritableFont.ARIAL,
					WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);

		}
		jxl.write.WritableCellFormat wcfFC = getFormat();
		if (null == wcfFC) {
			wcfFC = new jxl.write.WritableCellFormat(wfont);
			try {
				wcfFC.setWrap(true);// 自动换行
				wcfFC.setAlignment(Alignment.CENTRE);
				wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);// 设置对齐方式
			} catch (WriteException e) {
				e.printStackTrace();
			}
		}

		jxl.write.Label wlabel1 = null;
		String[] columNames = columnNames;
		if (null == columNames)
			return;
		int colSize = columNames.length;

		Integer[] colsWidth = columnWidths;
		if (null == colsWidth) {
			colsWidth = new Integer[colSize];
			for (int i = 0; i < colSize; i++) {
				colsWidth[i] = 20;
			}
		}

		int temp = 0;
		String colName = null;
		for (int i = 0; i < colSize; i++) {
			colName = columNames[i];
			if (null == colName || "".equals(colName))
				colName = "";
			wlabel1 = new jxl.write.Label(i, 0, colName, wcfFC);
			wsheet.addCell(wlabel1);
			temp = colsWidth[i].intValue();
			// 默认设置列宽
			temp = temp == 0 ? 20 : temp;
			wsheet.setColumnView(i, temp);
		}

	}

	// 设置格式
	private WritableCellFormat getFormat() {

		jxl.write.WritableFont wfont = getFont();
		jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(
				wfont);
		try {
			wcfFC.setWrap(true);
			wcfFC.setAlignment(Alignment.CENTRE);
			wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);
		} catch (WriteException e) {
			e.printStackTrace();
		}
		return wcfFC;
	}

	// 设置字体
	private WritableFont getFont() {
		return new WritableFont(WritableFont.ARIAL,
				WritableFont.DEFAULT_POINT_SIZE, WritableFont.BOLD);
	}

}

JxlExcelUtil.java

import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * @Title: JxlExcelUtils.java
 * @Description: Jxl Excel 工具类
 * @author lzh_me@126.com
 * @date 2017年1月12日 上午10:57:30
 * @version V1.0
 */
public class JxlExcelUtil {
	/**
	 * 
	 * @Description: 导出excel
	 * @param response
	 * @param sheetName
	 * @param keyMap
	 * @param listContent
	 * @param os
	 * @param methodMap
	 * @param columns_widths
	 * @return
	 * int
	 */
	@SuppressWarnings("unchecked")
	public int getExcelStream(HttpServletResponse response,String sheetName,
			LinkedHashMap<String, String> keyMap, List<?> listContent,
			OutputStream os, Map<String, String> methodMap,Integer[] columns_widths) {
		int flag = 0;
		// 声明工作簿
		WritableWorkbook workbook;
		try {
			// 根据传进来的file对象创建可写入的Excel工作薄
			workbook = Workbook.createWorkbook(os);
			// 创建一个工作表
			WritableSheet ws = workbook.createSheet(sheetName, 0);

			SheetSettings ss = ws.getSettings();
			ss.setVerticalFreeze(1);// 冻结表头

			// 设置字体
			WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 12);
			WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 12,
					WritableFont.BOLD);

			// 标题居中
			WritableCellFormat titleFormat = new WritableCellFormat(BoldFont);
			titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
			titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
			titleFormat.setAlignment(Alignment.CENTRE); // 文字水平对齐
			titleFormat.setWrap(false); // 文字是否换行

			// 正文居中
			WritableCellFormat contentCenterFormat = new WritableCellFormat(
					NormalFont);
			contentCenterFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			contentCenterFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
			contentCenterFormat.setAlignment(Alignment.CENTRE);
			contentCenterFormat.setWrap(false);

			// 正文右对齐
			WritableCellFormat contentRightFormat = new WritableCellFormat(
					NormalFont);
			contentRightFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
			contentRightFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
			contentRightFormat.setAlignment(Alignment.RIGHT);
			contentRightFormat.setWrap(false);

			// 设置标题,标题内容为keyMap中的value值,标题居中粗体显示
			Iterator<?> titleIter = keyMap.entrySet().iterator();
			int titleIndex = 0;
			while (titleIter.hasNext()) {
				Map.Entry<String, String> entry = (Map.Entry<String, String>) titleIter
						.next();
				//设置列宽
				ws.setColumnView(titleIndex, columns_widths[titleIndex]);
				
				ws.addCell(new Label(titleIndex++, 0, entry.getValue(),
						titleFormat));
				
			}

			// 设置正文内容
			for (int i = 0; i < listContent.size(); i++) {
				Iterator<?> contentIter = keyMap.entrySet().iterator();
				int colIndex = 0;
				while (contentIter.hasNext()) {
					Map.Entry<String, String> entry = (Map.Entry<String, String>) contentIter
							.next();
					String key = entry.getKey().toString();
					Field field = listContent.get(i).getClass()
							.getDeclaredField(key);
					field.setAccessible(true);
					Object content = field.get(listContent.get(i));
					String value = "";
					if (null != content) {
						value = content.toString();
					}
					if (methodMap != null) {
						String methodName = methodMap.get(key);
						if (methodName != null) {
							Method convertMethod = this
									.getClass()
									.getDeclaredMethod(methodName, String.class);
							value = (String) convertMethod.invoke(this, value);
						}
					}
					ws.addCell(new Label(colIndex++, i + 1, value,
							contentCenterFormat));
				}

			}

			// 宽度自适应。能够根据内容增加宽度，但对中文的支持不好，如果内容中包含中文，会有部分内容被遮盖
			/*for (int i = 0; i < keyMap.size(); i++) {
				CellView cell = ws.getColumnView(i);
				cell.setAutosize(true); //自动宽度
				cell.setSize(30);//最小宽度
			}*/
			// 写入Exel工作表
			workbook.write();

			// 关闭Excel工作薄对象
			workbook.close();

			// 关闭流
			os.flush();
			os.close();
			os = null;
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} catch (Exception ex) {
			flag = 0;
			ex.printStackTrace();
		}
		return flag;
	}
}

jsp页面：

//导出
$('#export_but').on('click', function() {
	//参数
	var xx = $("#xx").val();
	//查询条件
	var param = {
		"xx1" : xx1,
		"xx2" : xx2,
		"xx3" : xx2
	};
	
	var params = $.param(param);
	var url = "${pageContext.request.contextPath}/你的url"+ "?" + params;
	//提交方式都可以
	//window.location.href = url;
	$('<form method="post" action="' + url + '"></form>').appendTo('body').submit().remove();
});

注意事项：

1、excel导出不能用ajax，或者你可以使用ajax的导出控件，我没有用过，暂时不讨论

2、记得设置

response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", "attachment; filename="
					+ excelName);

3、url传参，中文乱码问题，在后台需要转码，比如

new String(xxBean.getXx().getBytes("ISO8859-1"), "UTF-8")