java导出导入excel，对jxl的封装

JExcelOperator.java导出导入的类public class JExcelOperator {/*定义默认的列宽*/private static final int DEFAULT_COLUMN_WIDTH = 10;/*定义默认的工作薄名称*/private static final String DEFAULT_WORK_TITLE = "教育";/*定义默认的工作表标题样式*/private static final ITitleFormat DEFAULT_TITLE_FORMAT = new DefaultTitleFormat();/*定义默认的工作表表头样式*/private static final IColumnFormat DEFAULT_COLUMN_FORMAT = new DefaultColumnFormat();/*定义默认的工作表数据样式*/private static final IDataCellFormat DEFAULT_DATACELL_FORMAT = new DefaultDataCellFormat();    /*指定工作表标题的 样式*/private ITitleFormat titleFormat ;        /*指定表头样式*/private IColumnFormat columnFormat ;    /*指定数据行 样式*/private IDataCellFormat dataFormat ;/** * 导出Excel .成功导出，返回true；否则返回false * @param request  * HTTP请求对象 * @param response  * HTTP响应对象 * @param workTitle  * 工作薄名称 * @param title * 工作表标题 * @param columns * 列名数组 * @param widths * 列宽度数组 * @param dataList * 要导出的对象集合 * @param objectMapper * 对象解析器，将一个对象解析成一行数据 */@SuppressWarnings("unchecked")public boolean exportExcel(HttpServletRequest request, HttpServletResponse response, String workTitle,String title,String[] columns, int[] widths,List dataList, IObjectMapper objectMapper) {OutputStream out = null;WritableWorkbook wbook = null;try {//断言 objectMapper  不能为空Assert.notNull(objectMapper, "对象解析器不能为空");if(workTitle == null || workTitle.trim().equals("")){workTitle = DEFAULT_WORK_TITLE;}out = response.getOutputStream();response.setContentType("application/msexcel");// 定义输出类型String fileName = workTitle;//获取浏览器类型String user_agent = request.getHeader("User-Agent").toLowerCase();//为不同的浏览器，对文件名进行不同的编码转换if(user_agent.indexOf("firefox") > 0){ fileName =  new String(workTitle.getBytes("UTF-8"), "iso8859-1");}else{fileName = URLEncoder.encode(workTitle, "UTF-8");}response.setHeader("Content-disposition", "attachment; filename="+ fileName + ".xls");         // 设定输出文件头wbook = Workbook.createWorkbook(out); // 建立excel文件WritableSheet wsheet;if(workTitle != null && !workTitle.equals("")){ wsheet = wbook.createSheet(workTitle, 0); // 创建一个工作薄}else{ wsheet = wbook.createSheet("", 0);       }int headerIndex = 0;  //记录表头所在的行if(title != null && !title.equals("")){headerIndex = 1;// 设置excel标题wsheet.addCell(new Label(0, 0, title, this.getTitleFormat().getTitleCellFormat()));}// 在一新行中, 为表格添加表头if(columns != null){for (int i = 0; i < columns.length; i++) {if(widths != null && widths.length > 0){if((i + 1) <= widths.length ){wsheet.setColumnView(i, widths[i]);}else{wsheet.setColumnView(i, DEFAULT_COLUMN_WIDTH);}}else{wsheet.setColumnView(i, DEFAULT_COLUMN_WIDTH);}wsheet.addCell(new Label(i, headerIndex, columns[i],this.getColumnFormat().getTitleCellFormat()));}}//将集合中的数据添加到excel的工作表中if(dataList != null){for (int i = 0; i < dataList.size(); i++) {  Object obj = dataList.get(i);objectMapper.toExcelRow(wsheet,this.dataFormat, i + headerIndex + 1, obj);}}// 主体内容生成结束wbook.write(); // 写入文件return true;} catch (Exception ex) {ex.printStackTrace();return false;} finally {if (wbook != null) {try {wbook.close();} catch (WriteException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}/** * 导入Excel,将Excel 中的数据导入到List 中  * @param input * 指定输入流  * @param index * 工作博的序号 (从0开始) * @param startIndex * 指定从第几行开始读入 (第一行对应的index为0)  * @param rowMapper * 行包装器(负责将一行数据转变成一个数据对象) */@SuppressWarnings("unchecked")public List getDataFromExcel(InputStream input, int index, int startIndex,IRowMapper rowMapper) {// TODO Auto-generated method stubWorkbook rwb = null;try {List list = new ArrayList(); rwb = Workbook.getWorkbook(input); Assert.isTrue(index < rwb.getSheets().length && index >= 0, "为index指定的值  " + index + " 无效 "); Sheet st = rwb.getSheet(index);startIndex = (startIndex < 0)?0:startIndex;Assert.isTrue(startIndex < st.getRows(), "为startIndex指定的值   " + startIndex + " 无效");Assert.notNull(rowMapper, "行包装器没有指定");for (int i = startIndex; i < st.getRows(); i++) {Object obj = rowMapper.toObject(st, i);if(obj != null){list.add(obj);}}return list;} catch (Exception e) {e.printStackTrace();return null;}finally{if(rwb != null){rwb.close();}}}/** * 导入Excel,将Excel中的数据导入到List 中  * @param input * 指定输入流  * @param title * 工作博的名称 * @param startIndex * 指定从第几行开始读入（第一行对应的index为0） * @param rowMapper * 行包装器（负责将一行数据转变成一个数据对象） */@SuppressWarnings("unchecked")public List getDataFromExcel(InputStream input, String title, int startIndex,IRowMapper rowMapper) {Workbook rwb = null;try {List list = new ArrayList(); rwb = Workbook.getWorkbook(input);Assert.notNull(title,"title参数不能为空");Sheet st = rwb.getSheet(title);Assert.notNull(st,"为title参数指定的值:" + title + "无效");startIndex = (startIndex < 0)?0:startIndex;Assert.isTrue(startIndex < st.getRows(), "为startIndex指定的值   " + startIndex + " 无效");Assert.notNull(rowMapper, "行包装器没有指定");for (int i = startIndex; i < st.getRows(); i++) {Object obj = rowMapper.toObject(st, i);if(obj != null){list.add(obj);}}return list;} catch (Exception e) {e.printStackTrace();return null;}finally{if(rwb != null){rwb.close();}}}@SuppressWarnings("unchecked")public void setTitleFormat(ITitleFormat titleFormat) {this.titleFormat = titleFormat;}@SuppressWarnings("unchecked")public void setColumnFormat(IColumnFormat columnFormat) {this.columnFormat = columnFormat;}@SuppressWarnings("unchecked")public void setDataFormat(IDataCellFormat dataFormat) {this.dataFormat = dataFormat;}@SuppressWarnings("unchecked")public ITitleFormat getTitleFormat() {//如果没有设置工作表标题样式，就给它一个默认样式if(this.titleFormat == null || this.titleFormat.getTitleCellFormat() == null){this.titleFormat = DEFAULT_TITLE_FORMAT;}return titleFormat;}@SuppressWarnings("unchecked")public IColumnFormat getColumnFormat() {//如果没有设置工作表标题样式，就给它一个默认样式if(this.columnFormat == null || this.columnFormat.getTitleCellFormat() == null){this.columnFormat = DEFAULT_COLUMN_FORMAT;}return columnFormat;}@SuppressWarnings("unchecked")public IDataCellFormat getDataFormat() {if(this.dataFormat == null){this.dataFormat = DEFAULT_DATACELL_FORMAT;}return dataFormat;}}/* * 该类负责将一个对象转变成Excel 中的一行数据 */public interface IObjectMapper {/* * 将obj 对象 转变成sheet工作表 中的第 rowNum行数据 * sheet  操作的工作表 * dataFormat 指定数据行 样式 * rowNum 表示 行号 * obj    要转换的对象 */    public void toExcelRow(WritableSheet sheet,IDataCellFormat dataFormat, int rowNum,Object obj)  throws RowsExceededException, WriteException;    }/* * Excel 行包装器, 负责将excel 中一行数据转变成一个对象 */public interface IRowMapper {/* * 定义如何将一个excel 中的一行 转变成一个对象,返回装有excel数据的对象 * sheet  操作的工作表 * rowNum 表示 行号 */    public Object toObject(Sheet sheet,int rowNum)  throws RowsExceededException, WriteException;}

接下来是定义默认样式的类

//描述工作博单元格样式的根接口public interface ICellFormat {public WritableCellFormat getTitleCellFormat();}//该接口为 标识接口，所有描述工作博标题样式的类都要实现此接口public interface ITitleFormat extends ICellFormat{public WritableCellFormat getTitleCellFormat();}//该接口为 标识接口，所有描述工作博列名样式的类都要实现此接口public interface IColumnFormat extends ICellFormat{}/* * 该接口定义了如何为不同的数据类型指定不同的样式的方法 */public interface IDataCellFormat {public WritableCellFormat getDataCellFormat(CellType type);}/* * 定义一个工作博标题样式的默认实现 */@Componentpublic class DefaultTitleFormat implements ITitleFormat{public WritableCellFormat getTitleCellFormat() {// TODO Auto-generated method stub    WritableCellFormat wcf = null;          try {              //字体样式              WritableFont wf = new WritableFont(WritableFont.TIMES,20, WritableFont.BOLD,true);//最后一个为是否italic              wf.setColour(Colour.RED);              wcf = new WritableCellFormat(wf);              //对齐方式              wcf.setAlignment(Alignment.CENTRE);              wcf.setVerticalAlignment(VerticalAlignment.CENTRE);              //边框              wcf.setBorder(Border.ALL,BorderLineStyle.THIN);                            //背景色              wcf.setBackground(Colour.GREY_25_PERCENT);          } catch (WriteException e) {           e.printStackTrace();          }          return wcf;    }}/* * 定义一个工作博表头样式的默认实现 */@Componentpublic class DefaultColumnFormat  implements IColumnFormat{@Overridepublic WritableCellFormat getTitleCellFormat() {    WritableCellFormat wcf = null;          try {              //字体样式              WritableFont wf = new WritableFont(WritableFont.TIMES,15, WritableFont.NO_BOLD,false);//最后一个为是否italic              wf.setColour(Colour.BLUE);              wcf = new WritableCellFormat(wf);              //对齐方式              wcf.setAlignment(Alignment.CENTRE);              wcf.setVerticalAlignment(VerticalAlignment.CENTRE);              //边框              wcf.setBorder(Border.ALL,BorderLineStyle.THIN);                            //背景色              wcf.setBackground(Colour.GREY_25_PERCENT);          } catch (WriteException e) {           e.printStackTrace();          }          return wcf;    }}/* * 定义一个工作博数据行样式的默认实现 */@Componentpublic class DefaultDataCellFormat implements IDataCellFormat{@Overridepublic WritableCellFormat getDataCellFormat(CellType type) {// TODO Auto-generated method stub  WritableCellFormat wcf = null;          try {              //字体样式              if(type == CellType.NUMBER || type == CellType.NUMBER_FORMULA){//数字                 NumberFormat nf = new NumberFormat("#.00");                 wcf = new WritableCellFormat(nf);               }else if(type == CellType.DATE || type == CellType.DATE_FORMULA){//日期                  jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd hh:mm");                   wcf = new jxl.write.WritableCellFormat(df);               }else{                  WritableFont wf = new WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD,false);//最后一个为是否italic                  wcf = new WritableCellFormat(wf);              }              //对齐方式              wcf.setAlignment(Alignment.CENTRE);              wcf.setVerticalAlignment(VerticalAlignment.CENTRE);              //边框              wcf.setBorder(Border.LEFT,BorderLineStyle.THIN);              wcf.setBorder(Border.BOTTOM,BorderLineStyle.THIN);              wcf.setBorder(Border.RIGHT,BorderLineStyle.THIN);              //背景色              wcf.setBackground(Colour.WHITE);                            wcf.setWrap(true);//自动换行                        } catch (WriteException e) {           e.printStackTrace();          }          return wcf;    }}

用到第三方jar包：

spring3，jxl.jar

在spring mvc action里面如何使用？

@Controller@RequestMapping(value = "/example/excel")public class ExcelExampleAction {private IBaseService<SysStudycentre> sysStudycentreService; // 定义学习中心服务引用private JExcelOperator excelOperator;   // 注入名为sysStudycentreService 的IBaseService<SysStudycentre> 对象@Resource(name = "sysStudycentreService")public void setSysStudycentreService(IBaseService<SysStudycentre> sysStudycentreService) {this.sysStudycentreService = sysStudycentreService;}// 注入名为 excelOperator 的JExcelOperator 对象@Resource(name = "excelOperator")public void setExcelOperator(JExcelOperator excelOperator) {this.excelOperator = excelOperator;}// 导出excel@RequestMapping(value = "getexcel")@ResponseBody@SuppressWarnings("unchecked")public void exportToExcel(Model model,HttpServletRequest request,HttpServletResponse response) {List<SysStudycentre> sysStudyCenters = this.sysStudycentreService.findAll();   //导出到excel,并将导出文件发往客户端excelOperator.exportExcel(request,response, "学习中心",null, new String[]{"名称","联系人","联系方式","更新时间"},new int[]{10,20,30,30}, sysStudyCenters, new IObjectMapper(){@Overridepublic void toExcelRow(WritableSheet sheet,IDataCellFormat dataFormat, int rowNum, Object obj)throws RowsExceededException, WriteException {// TODO Auto-generated method stubif(dataFormat != null){    //dataFormat.getDataCellFormat(CellType.STRING_FORMULA)    指定单元格 数据使用的格式    sheet.addCell(new Label(0,  rowNum, ((SysStudycentre)obj).getFname(),dataFormat.getDataCellFormat(CellType.STRING_FORMULA))); // 学习中心名称    sheet.addCell(new Label(1, rowNum,((SysStudycentre)obj).getFcontactor(),dataFormat.getDataCellFormat(CellType.STRING_FORMULA) )); // 联系人    sheet.addCell(new Label(2, rowNum,((SysStudycentre)obj).getFmobilephone(),dataFormat.getDataCellFormat(CellType.NUMBER_FORMULA) )); // 联系方式    sheet.addCell(new Label(3, rowNum,((SysStudycentre)obj).getFupdatetime().toString(),dataFormat.getDataCellFormat(CellType.DATE_FORMULA) )); // 更新时间}else{    sheet.addCell(new Label(0,  rowNum, ((SysStudycentre)obj).getFname())); // 学习中心名称    sheet.addCell(new Label(1, rowNum,((SysStudycentre)obj).getFcontactor() )); // 联系人    sheet.addCell(new Label(2, rowNum,((SysStudycentre)obj).getFmobilephone() )); // 联系方式    sheet.addCell(new Label(3, rowNum,((SysStudycentre)obj).getFupdatetime().toString() )); // 更新时间}}});}// 从excel 中导入数据@RequestMapping(value = "exportToList")@SuppressWarnings("unchecked")public String exportToList(Model model,HttpServletRequest request,HttpServletResponse response) {String filePath = "";try{//取得文件路径 filePath = request.getSession().getServletContext().getRealPath("") + "\\WEB-INF\\classes\\data\\studyCenter2.xls";FileInputStream in = new FileInputStream(filePath);List list = new JExcelOperator().getDataFromExcel(in, 0, 3, new IRowMapper(){@Overridepublic Object toObject(Sheet sheet, int rowNum)throws RowsExceededException, WriteException {// TODO Auto-generated method stubSysStudycentre studyCentre = new SysStudycentre();studyCentre.setFname(sheet.getCell(0, rowNum).getContents());studyCentre.setFcontactor(sheet.getCell(1, rowNum).getContents());studyCentre.setFmobilephone(sheet.getCell(2, rowNum).getContents());return studyCentre;}});for(Object obj:list){System.out.println(((SysStudycentre)obj).getFname());}//将集合存入model 中model.addAttribute("studyCenterList",list);return "example/exceldata";} catch (FileNotFoundException e) {request.setAttribute("message",  filePath + "文件没找到");return "example/error";}}}