导Excel工程实现及问题解决.docx

导出Excel实现步骤：1.导入jxl.jar到maven jxl jxl 2.cmd 导入jar到maven本地仓库3.编写ExportExcel通用类实现思路：1.判断是否生成多个(sheet) .使用双重循环,循环生成多个(sheet) .以Map 为参数获取当中封装好了的key值 . Map由 /导出路径(url) /导出文件名称(fileName) /多个列名称(labels 类型为String) /是否页签(severalArr 类型为int)如果大于1表示使用页签,然后以(severalArr)为外层循环的长度循环生成(sheet) /使用二维数组存储多个(sheet的列名)(arr 类型为String)组成实现代码主要代码：代码1/* * 导出 excel 筛选 */private Map excelMap = new HashMap();private String url;private InputStream inputStream;private String fileName;/文件下载注解配置Action(value=exportExcel,results=Result(name = list, type = stream, params = contentType,application/octet-stream;charset=ISO8859-1, inputName,inputStream, contentDisposition,attachment;filename=$fileName, bufferSize, 4096 )public String exportExcel() param.setAgencyIds(getAuthAgencyIds();this.alerts = this.alertService.findList(param);if (this.alerts != null) /导出Excel到服务器excelMap.put(fileName,GetCurrentDetailDate.getCurrentDetailDate() + (报警记录);excelMap.put(labels, new String 报警人员, 围栏名称, 报警类型,报警时间 );excelMap.put(url, url);excelMap.put(severalArr, 0);String arr = new Stringthis.alerts.size()4;for (int i = 0; i arr.length; i+) int j = 0;arrij+ = this.alerts.get(i).getRecName() = null ? : this.alerts.get(i).getRecName();arrij+ = this.alerts.get(i).getFenceName() = null ? : this.alerts.get(i).getFenceName();arrij+ = this.alerts.get(i).getAlertType() = null ? : this.alerts.get(i).getAlertType();arrij+ = this.alerts.get(i).getAlertTime() = null ? : this.alerts.get(i).getAlertTime().toString();excelMap.put(arr, arr);ExportExcel.exportExcel(excelMap);/文件下载this.fileName = excelMap.get(fileName) + .xls;try this.inputStream = new FileInputStream(new File(ServletActionContext.getServletContext().getRealPath(/fileDownload/)+ excelMap.get(fileName) + .xls); catch (FileNotFoundException e) e.printStackTrace();return list;/文件名称编码格式设置public String getFileName() try return new String(this.fileName.getBytes(), iso8859-1); catch (UnsupportedEncodingException e) e.printStackTrace();return this.getFileName();代码2：/生成Excel文件代码package com.code.player.judicature.location.util;import java.io.File;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.util.ArrayList;import java.util.List;import java.util.Map;import jxl.Workbook;import jxl.format.Alignment;import jxl.write.Label;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.struts2.ServletActionContext;/* * 导出excel * * author Lenovo * */public class ExportExcel /* * url 导出路径 /不页签的话 以此为命名 fileName 文件名称 /不页签的话 以此为命名 severalArr 是否有多个页签 * /不页签的话 以此为命名 arr 字符串数组 可以是多个 如 arr1 arr2 arr3 有多少个页签 就有多少个数组 /不页签的话 * 以此为命名 sheetName 工作表名称 可以是多个 如 .1 .2 .3 有多少个页签 就有多少个名称 /不页签的话 以此为命名 * labels 列头 名称 可以是多个 如 .1 .2 .3 有多少个页签 就有多少个名称 /不页签的话 以此为命名 * * param map * return */public static String exportExcel(Map map) / 导出路径String url = ServletActionContext.getServletContext().getRealPath(/fileDownload/);/ 文件名称String fileName = (String) map.get(fileName);/ 是否有对个页签Integer severalArr = (Integer) map.get(severalArr);if (severalArr 1 & severalArr != null) / 存储多个sheet数组List arrList = new ArrayList();/ 存储多个labelList labelsList = new ArrayList();/ 存储多个sheet名字List strList = new ArrayList();for (int i = 0; i severalArr; i+) arrList.add(String) map.get(arr + (i + 1);strList.add(String) map.get(sheetName + (i + 1);labelsList.add(String) map.get(labels + (i + 1);/ 输出流OutputStream out = null;/ 工作簿对象WritableWorkbook workBook = null;try url = url + fileName + .xls;out = new FileOutputStream(new File(url);/ 创建一个工作簿对象workBook = Workbook.createWorkbook(out);for (int i = 0; i arrList.size(); i+) / 记录行号int row = 0;/ 创建一个工作表Sheet0 位于xx位置WritableSheet sheet = workBook.createSheet(strList.get(i),i);/ 表头WritableFont font = new WritableFont(WritableFont.ARIAL,18, WritableFont.BOLD);WritableCellFormat format = new WritableCellFormat(font);format.setAlignment(Alignment.CENTRE);Label label = new Label(0, row+, strList.get(i), format);sheet.addCell(label);/ 表头合并13列sheet.mergeCells(0, 0, arrList.get(i).length, 0);for (int j = 0; j labelsList.get(i).length; j+) / 设置列宽sheet.setColumnView(j, 13);font = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD);format = new WritableCellFormat(font);label = new Label(j, row, labelsList.get(i)j, format);sheet.addCell(label);row+;for (int j = 0; j arrList.get(i).length; j+) for (int k = 0; k 0) try url = url + fileName + .xls;out = new FileOutputStream(new File(url);/ 记录行号int row = 0;/ 创建一个工作簿对象workBook = Workbook.createWorkbook(out);/ 创建一个工作表Sheet0 位于第0个位置sheet = workBook.createSheet(fileName, 0);/ 表头font = new WritableFont(WritableFont.ARIAL, 18,WritableFont.BOLD);format = new WritableCellFormat(font);format.setAlignment(Alignment.CENTRE);label = new Label(0, row+, fileName, format);sheet.addCell(label);/ 表头合并13列sheet.mergeCells(0, 0, arr0.length, 0);for (int i = 0; i labels.length; i+) / 设置列宽sheet.setColumnView(i, 13);font = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD);format = new WritableCellFormat(font);label = new Label(i, row, labelsi, format);sheet.addCell(label);row+;for (int i = 0; i arr.length; i+) for (int j = 0; j arri.length; j+) font = new WritableFont(WritableFont.ARIAL, 10);format = new WritableCellFormat(font);label = new Label(j, row, arrij, format);sheet.addCell(label);row+; catch (RowsExceededException e) e.printStackTrace(); catch (FileNotFoundException e) e.printStackTrace(); catch (WriteException e) e.printStackTrace(); catch (IOException e) e.printStackTrace(); finally try workBook.write();workBook.close();out.close(); catch (Exception e) e.printStackTrace();return null;代码3:javascript:以触发click点击事件，动态设置参数Excel导出$(#export).click(function() var startLocationTime = $(#startLocationTime).val();var endLocationTime = $(#endLocationTime).val();var telephone = $(#telephone).val();var errorCode = $(#errorCode).val();$(#export).attr(href,exportExcel.action?positionParam.startLocationTime= + startLocationTime+ &positionParam.endLocationTime= + endLocationTime+ &positionParam.telephone= + telephone+ &positionParam.errorCode= + errorCode);alert($(#export).attr(href););/删除服务器中的文件action中的代码：this.contents = ServletActionContext.getServletContext().getRealPath(/fileDownload/).replaceAll(, /);if (RemoveFile.delAllFile(this.contents) System.out.println(文件删除成功！); else System.out.println(文件删除失败！);文件删除静态类：package com.code.player.judicature.location.util;import java.io.File;public class RemoveFile / 删除文件夹/ param folderPath 文件夹完整绝对路径public static void delFolder(String folderPath) try delAllFile(folderPath); / 删除完里面所有内容String filePath = folderPath;filePath = filePath.toString();java.io.File myFilePath = new java.io.File(filePath);myFilePath.delete(); / 删除空文件夹 catch (Exception e) e.printStackTrace();/ 删除指定文件夹下所有文件/ param path 文件夹完整绝对路径public static boolean delAllFile(String path) boolean flag = false;File file = new File(path);if (!file.exists() return flag;if (!file.isDirectory() return flag;String tempList = file.list();File temp = null;for (int i = 0; i tempList.length; i+) if (path.endsWith(File.separator) temp = new File(path + tempListi); else temp = new File(path + File.separator + tempListi);if (temp.isFile() temp.delete();/ if (temp.isDirectory() / delAllFile(path + / + tempListi);/ 先删除文件夹里面的文件/ / delFolder(path + / + tempListi);/ 再删除空文件夹/ return true;/ if (file.list().length number of parameters, which is 2).# The error may exist in com/code/player/judicature/location/mapper/RectificationMapper.xml# The error may involve com.code.player.judicature.location.mapper.RectificationMapper.serch-Inline# The error occurred while setting parameters# SQL: SELECT a.id, a.pictureQuote, a.no, , a.agencyId, as agencyName, b.positionMobile, b.rectificationStartDate, b.rectificationEndDate from t_rectification a left join t_penalty b on a.id = b.rectificationId inner join t_agency c on a.agencyId=c.id WHERE 1=1 or LIKE %?% or LIKE %?% limit ?, ?# Cause: java.sql.SQLException: Parameter index out of range (3 number of parameters, which is 2).; SQL ; Parameter index out of range (3 number of parameters, which is 2).; nested exception is java.sql.SQLException: Parameter index out of range (3 number of parameters, which is 2).解决 LIKE %$item% 不能写为 LIKE %#item%7. Mybatis TooManyResultsExceptionorg.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 10解决：Mapper.java接口里的返回类型写错了理解过程：pojo 里 定义 Map 保存key=,value=list ,以pojo为参数传递 到 mapper接口,mapper接口的返回类型为List, mapper.xml , 当中的select 的返回结果类型为resultMap ,resultMapType=pojo,resultMap当中为pojo中定义的map名称, or LIKE %$item%AND c.id in($agencyIds)注意：1.LIKE %$item% 不能写为 LIKE %#item% 2. collection=rectificationMs resultMap当中定义的map ，在foreach中 collection 用.来取值8.Caused by: org.apache.ibatis.executor.ExecutorException: A query was run and no Result Maps were found for the Mapped Statement com.code.player.judicature.location.mapper.NoRecMapper.getSerchCount. Its likely that neither a Result Type nor a Result Map was specified.解决：mapper.xml 未指定参数类型 结果类型8.Caused by: java.lang.OutOfMemoryError: Java heap space这个问题的根源是jvm虚拟机的默认Heap大小是64M,可以通过设置其最大和最小值来实现.设置的方法主要是几个.1.可以在windows 更改系统环境变量加上JAVA_OPTS=-Xms64m -Xmx512m2,如果用的tomcat,在windows下,可以在C:tomcat5.5.9bincatalina.bat 中加上:set JAVA_OPTS=-Xms64m -Xmx256m位置在: rem Guess CATALINA_HOME if not defined 这行的下面加合适.3.如果是linux系统Linux 在tomcat_home/bin/catalina.sh的前面，加 set JAVA_OPTS=-Xms64 -Xmx5124.struts2下载注解配置private InputStream inputStream;private String fileName;Action(value=exportExcel,results=Result(name = list, type = strea