Java SpringMVC项目导出excel多种类对应工具类整理（util）

续上一章【Java SpringMVC项目导入excel2003以及2007多版本自动识别对应工具类（util）】

上一章介绍如何导入excel进系统处理成MAP集合（详情请看上提供的链接），这次我又整理了一下如何快速导出excel的工具。

1.导出无表头excel 文件单个工作表(sheet)，【fileName是标题名，titleList是列名，list就是列的内容了】

/** * 导出无表头excel 文件单个工作表(sheet) *  * @param fileName * @param titleList * @param list * @param response */public static void exportNoHeadExcel(String fileName, String[] titleList,List list, HttpServletResponse response) {SimpleDateFormat df = new java.text.SimpleDateFormat("yyyyMMdd");String todayStr = df.format(new Date());OutputStream os = null;try {os = response.getOutputStream();String localFileName = fileName;fileName = java.net.URLEncoder.encode(fileName, "UTF-8");// 处理中文文件名的问题fileName = new String(fileName.getBytes("UTF-8"), "GBK");// 处理中文文件名的问题response.setContentType("application/vnd.ms-excel;");response.setHeader("Content-disposition", "attachment; filename=\""+ fileName + "_" + todayStr + ".xls\"");// 开始写入excel// 字段字体jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(WritableFont.COURIER, 10, WritableFont.NO_BOLD, true);jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(wfc1);wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 结果字体jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat();wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);WritableWorkbook wbook = Workbook.createWorkbook(os);// 写sheet名称WritableSheet wsheet = wbook.createSheet(localFileName, 0);for (int m = 0; m < titleList.length; m++) {wsheet.setColumnView(m, 30);}// 加入字段名for (int n = 0; n < titleList.length; n++) {wsheet.addCell(new jxl.write.Label(n, 0, titleList[n], wcfFC1));}// 写入流中int row = 0;for (int r = 0; r < list.size(); r++) {Object[] obj = (Object[]) list.get(r);for (int x = 0; x < titleList.length; x++) {wsheet.addCell(new jxl.write.Label(x, row + 1,obj[x] == null ? " " : obj[x].toString(), wcfFC1));}row++;if (row % 60000 == 0) {row = 0;// 写sheet名称wsheet = wbook.createSheet(localFileName, 0);for (int m = 0; m < titleList.length; m++) {wsheet.setColumnView(m, 30);}// 加入字段名for (int n = 0; n < titleList.length; n++) {wsheet.addCell(new jxl.write.Label(n, 0, titleList[n],wcfFC1));}}}wbook.write();wbook.close();os.flush();} catch (Exception e) {e.printStackTrace();} finally {if (os == null) {Log.info("os is null");} else {try {os.close();os = null;} catch (IOException e) {e.printStackTrace();}}}}

2.首先导出无表头excel 文件带多个工作表(sheet)，【sheetList存放的就是Map包含fileName是标题名，titleList是列名，list就是列的内容】

/** * 导出无表头excel文件 *  * @param fileName * @param titleList * @param list * @param response */public static void exportNoHeadExcel(List<Map<String,Object>> sheetList, HttpServletResponse response) {SimpleDateFormat df = new java.text.SimpleDateFormat("yyyyMMdd");String todayStr = df.format(new Date());OutputStream os = null;try {os = response.getOutputStream();WritableWorkbook wbook = Workbook.createWorkbook(os);for(int i=0;i<sheetList.size();i++){Map<String,Object> map=sheetList.get(i);String fileName=(String) map.get("fileName");String[] titleList=(String[]) map.get("titleList");List list=(List) map.get("list");String localFileName = fileName;fileName = java.net.URLEncoder.encode(fileName, "UTF-8");// 处理中文文件名的问题fileName = new String(fileName.getBytes("UTF-8"), "GBK");// 处理中文文件名的问题response.setContentType("application/vnd.ms-excel;");response.setHeader("Content-disposition", "attachment; filename=\""+ fileName + "_" + todayStr + ".xls\"");// 开始写入excel// 字段字体jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(WritableFont.COURIER, 10, WritableFont.NO_BOLD, true);jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(wfc1);wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 结果字体jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat();wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 写sheet名称WritableSheet wsheet = wbook.createSheet(localFileName,i);for (int m = 0; m < titleList.length; m++) {wsheet.setColumnView(m, 30);}// 加入字段名for (int n = 0; n < titleList.length; n++) {wsheet.addCell(new jxl.write.Label(n, 0, titleList[n], wcfFC1));}// 写入流中int row = 0;for (int r = 0; r < list.size(); r++) {Object[] obj = (Object[]) list.get(r);for (int x = 0; x < titleList.length; x++) {wsheet.addCell(new jxl.write.Label(x, row + 1,obj[x] == null ? " " : obj[x].toString(), wcfFC1));}row++;if (row % 60000 == 0) {row = 0;// 写sheet名称wsheet = wbook.createSheet(localFileName, 0);for (int m = 0; m < titleList.length; m++) {wsheet.setColumnView(m, 30);}// 加入字段名for (int n = 0; n < titleList.length; n++) {wsheet.addCell(new jxl.write.Label(n, 0, titleList[n],wcfFC1));}}}}wbook.write();wbook.close();os.flush();} catch (Exception e) {e.printStackTrace();} finally {if (os == null) {Log.info("os is null");} else {try {os.close();os = null;} catch (IOException e) {e.printStackTrace();}}}}

3.导出excel文件带标题【fileName是标题名，titleList是列名，list就是列的内容了】

/** * 导出excel 文件  带标题 *  * @param fileName  * @param titleList * @param list * @param response */public static void exportWithHeadExcel(String fileName, String[] titleList,List list, HttpServletResponse response) {Date now = new Date();SimpleDateFormat dateformat = new java.text.SimpleDateFormat("yyyy年MM月dd日HH时mm分");SimpleDateFormat df = new java.text.SimpleDateFormat("yyyyMMdd");String todayStr = df.format(new Date());String today = dateformat.format(now);OutputStream os = null;try {os = response.getOutputStream();String localFileName = fileName;fileName = java.net.URLEncoder.encode(fileName, "UTF-8");// 处理中文文件名的问题fileName = new String(fileName.getBytes("UTF-8"), "GBK");// 处理中文文件名的问题response.setContentType("application/vnd.ms-excel;");response.setHeader("Content-disposition", "attachment; filename=\""+ fileName + "_" + todayStr + ".xls\"");// 开始写入excel// 加标题// 标题字体jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.COURIER, 18, WritableFont.NO_BOLD, false);jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);wcfFC.setAlignment(jxl.format.Alignment.CENTRE);wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 字段字体jxl.write.WritableFont wfc1 = new jxl.write.WritableFont(WritableFont.COURIER, 10, WritableFont.NO_BOLD, false);jxl.write.WritableCellFormat wcfFC1 = new jxl.write.WritableCellFormat(wfc1);wcfFC1.setAlignment(jxl.format.Alignment.CENTRE);wcfFC1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 结果字体jxl.write.WritableCellFormat wcfFC2 = new jxl.write.WritableCellFormat();wcfFC2.setAlignment(jxl.format.Alignment.CENTRE);wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);WritableWorkbook wbook = Workbook.createWorkbook(os);// 写sheet名称WritableSheet wsheet = wbook.createSheet(localFileName, 0);int i = 3;for (int m = 0; m < titleList.length; m++) {wsheet.setColumnView(m, 30);}// 加入字段名for (int n = 0; n < titleList.length; n++) {wsheet.addCell(new jxl.write.Label(n, 3, titleList[n], wcfFC1));}// 加入标题wsheet.mergeCells(0, 0, i - 1, 0);wsheet.addCell(new Label(0, 0, localFileName, wcfFC));// 加入打印时间wsheet.addCell(new Label(i - 2, 1, "打印日期:"));wsheet.addCell(new Label(i - 1, 1, today));// 写入流中int row = 0;for (int r = 0; r < list.size(); r++) {Object[] obj = (Object[]) list.get(r);for (int x = 0; x < titleList.length; x++) {wsheet.addCell(new jxl.write.Label(x, row + 4,obj[x] == null ? " " : obj[x].toString(), wcfFC1));}row++;if (row % 60000 == 0) {row = 0;// 写sheet名称wsheet = wbook.createSheet(localFileName, 0);i = 3;for (int m = 0; m < titleList.length; m++) {wsheet.setColumnView(m, 30);}// 加入字段名for (int n = 0; n < titleList.length; n++) {wsheet.addCell(new jxl.write.Label(n, 3, titleList[n],wcfFC1));}// 加入标题wsheet.mergeCells(0, 0, i - 1, 0);wsheet.addCell(new Label(0, 0, localFileName, wcfFC));// 加入打印时间wsheet.addCell(new Label(i - 2, 1, "打印日期:"));wsheet.addCell(new Label(i - 1, 1, today));}}wbook.write();wbook.close();os.flush();} catch (Exception e) {e.printStackTrace();} finally {if (os == null) {Log.info("os is null");} else {try {os.close();os = null;} catch (IOException e) {e.printStackTrace();}}}}

目前大概整理了这3个，以后用到还会整理更新，今天就先到在这里吧..待续...

....谢谢阅读!