java导出Excel表格

</pre><p><strong>1.ExcelUtil是做导出excel用到的公共类</strong></p><p><strong></strong><pre name="code" class="java">import java.io.ByteArrayOutputStream;import java.util.List;import java.util.Map;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;/** * 导出Excel文档工具类 *  * @author 那位先生 * @date 2014-8-6 */public class ExcelUtil {/** * 创建excel文档， *  * @param list *            数据 * @param keys *            list中map的key数组集合 * @param columnNames *            excel的列名 */public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) {// 创建excel工作簿Workbook wb = new HSSFWorkbook();// 创建第一个sheet（页），并命名Sheet sheet = wb.createSheet("page1");// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。for (int i = 0; i < keys.length; i++) {sheet.setColumnWidth((short) i, (short) (35.7 * 150));}// 创建第一行Row row = sheet.createRow((short) 0);// 创建两种单元格格式CellStyle cs = wb.createCellStyle();CellStyle cs2 = wb.createCellStyle();// 创建两种字体Font f = wb.createFont();Font f2 = wb.createFont();// 创建第一种字体样式（用于列名）f.setFontHeightInPoints((short) 10);f.setColor(IndexedColors.BLACK.getIndex());f.setBoldweight(Font.BOLDWEIGHT_BOLD);// 创建第二种字体样式（用于值）f2.setFontHeightInPoints((short) 10);f2.setColor(IndexedColors.BLACK.getIndex());// Font f3=wb.createFont();// f3.setFontHeightInPoints((short) 10);// f3.setColor(IndexedColors.RED.getIndex());// 设置第一种单元格的样式（用于列名）cs.setFont(f);cs.setBorderLeft(CellStyle.BORDER_THIN);cs.setBorderRight(CellStyle.BORDER_THIN);cs.setBorderTop(CellStyle.BORDER_THIN);cs.setBorderBottom(CellStyle.BORDER_THIN);cs.setAlignment(CellStyle.ALIGN_CENTER);// 设置第二种单元格的样式（用于值）cs2.setFont(f2);cs2.setBorderLeft(CellStyle.BORDER_THIN);cs2.setBorderRight(CellStyle.BORDER_THIN);cs2.setBorderTop(CellStyle.BORDER_THIN);cs2.setBorderBottom(CellStyle.BORDER_THIN);cs2.setAlignment(CellStyle.ALIGN_CENTER);// 设置列名for (int i = 0; i < columnNames.length; i++) {Cell cell = row.createCell(i);cell.setCellValue(columnNames[i]);cell.setCellStyle(cs);}// 设置每行每列的值for (short i = 0; i < list.size(); i++) {// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的// 创建一行，在页sheet上Row row1 = sheet.createRow((short) i+1);// 在row行上创建一个方格for (short j = 0; j < keys.length; j++) {Cell cell = row1.createCell(j);cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString());cell.setCellStyle(cs2);}}return wb;}/** * 根据xls数据参数创建字节数组 * @param list * @param keys * @param columnNames * @return */public static byte[] createData(List<Map<String, Object>> list, String[] keys, String columnNames[]) {return getXlsByte(createWorkBook(list, keys, columnNames));}/** * 将wb文档数据写入字节流 * @param wb * @return */public static byte[] getXlsByte(Workbook wb) {try {ByteArrayOutputStream os = new ByteArrayOutputStream();wb.write(os);return os.toByteArray();} catch (Exception e) {e.printStackTrace();}return null;}}

2.controller层代码

/** *导出功能涉及参数，names导出excel表格时的列名、keys为取得对应参数的key值  */private final String NAMES[] = { "套餐编码", "套餐名称", "套装价", "原价", "是否启用","设置类型", "创建时间", "最后修改时间" };private final String KEYS[] = { "pair_sku", "pair_name", "pair_price","prime_price", "actived", "pair_discount_type", "created","modified"};<pre name="code" class="java">/** * 导出套餐内容 *  * @param request * @param res * @throws IOException */@RequestMapping(value = "/export")@ResponseBodypublic void export(WebRequest request, HttpServletResponse res)throws IOException {    DataTablePageUtil<Map<String, Object>> dataTable = findComboInfo(request);    List<Map<String, Object>> list = dataTable.getData();    if (null == list || list.size() == 0)        return;    OutputStream os = res.getOutputStream();    try {res.reset();res.setHeader("Content-Disposition","attachment; filename=combo_data.xls");res.setContentType("application/octet-stream; charset=utf-8");os.write(ExcelUtil.createData(list, KEYS, NAMES));os.flush();    } finally {if (os != null) {    os.close();}    }}3.JavaScript层<pre name="code" class="javascript">$(document).on("click","#export",function(){    var form = $("#searchForm");    form.attr("action", ctx+"/export");    form.attr("target", "_blank");    form.submit();    form.removeAttr("action");    form.removeAttr("target");});