JeeSite中实现导入导出Excel步骤

闲话 少说  直接 上 代码

springMVC项目 

JSP页面代码如下

</shiro:hasPermission><shiro:hasPermission name="echarts:memPutInto:import"><table:importExcel url="${ctx}/echarts/memPutInto/import"></table:importExcel><!-- 导入按钮 --></shiro:hasPermission><shiro:hasPermission name="echarts:memPutInto:export">       <table:exportExcel url="${ctx}/echarts/memPutInto/export"></table:exportExcel><!-- 导出按钮 -->       </shiro:hasPermission>

JSP页面 引用的table标签

importExcel.tag

<%@ tag language="java" pageEncoding="UTF-8"%><%@ include file="/webpage/include/taglib.jsp"%><%@ attribute name="url" type="java.lang.String" required="true"%><%-- 使用方法： 1.将本tag写在查询的form之前；2.传入controller的url --%><button id="btnImport" class="btn btn-white btn-sm " data-toggle="tooltip" data-placement="left" title="导入"><i class="fa fa-folder-open-o"></i> 导入</button><div id="importBox" class="hide"><form id="importForm" action="${url}" method="post" enctype="multipart/form-data" style="padding-left:20px;text-align:center;" onsubmit="loading('正在导入，请稍等...');"><br/><input id="uploadFile" name="file" type="file" style="width:330px"/>导入文件不能超过5M，仅允许导入“xls”或“xlsx”格式文件！<br/>　　</form></div><script type="text/javascript">$(document).ready(function() {$("#btnImport").click(function(){top.layer.open({    type: 1,     area: [500, 300],    title:"导入数据",    content:$("#importBox").html() ,    btn: ['下载模板','确定', '关闭'],    btn1: function(index, layero){  window.location.href='${url}/template';  },    btn2: function(index, layero){        var inputForm =top.$("#importForm");        var top_iframe = top.getActiveTab().attr("name");//获取当前active的tab的iframe         inputForm.attr("target",top_iframe);//表单提交成功后，从服务器返回的url在当前tab中展示           top.$("#importForm").submit();    top.layer.close(index);  },   btn3: function(index){   top.layer.close(index);           }}); });    });</script>

exportExcel.tag

<%@ tag language="java" pageEncoding="UTF-8"%><%@ include file="/webpage/include/taglib.jsp"%><%@ attribute name="url" type="java.lang.String" required="true"%><%-- 使用方法： 1.将本tag写在查询的form之前；2.传入url --%><button id="btnExport" class="btn btn-white btn-sm " data-toggle="tooltip" data-placement="left" title="导出"><i class="fa fa-file-excel-o"></i> 导出</button><script type="text/javascript">$(document).ready(function() {$("#btnExport").click(function(){top.layer.confirm('确认要导出Excel吗?', {icon: 3, title:'系统提示'}, function(index){    //do something    //导出之前备份    var url =  $("#searchForm").attr("action");    var pageNo =  $("#pageNo").val();    var pageSize = $("#pageSize").val();    //导出excel        $("#searchForm").attr("action","${url}");    $("#pageNo").val(-1);$("#pageSize").val(-1);$("#searchForm").submit();//导出excel之后还原$("#searchForm").attr("action",url);    $("#pageNo").val(pageNo);$("#pageSize").val(pageSize);    top.layer.close(index);});});    });</script>

实现 导入导入 Excel功能的Controller

@Controller
@RequestMapping(value = "${adminPath}/echarts/memPutInto")

/** * 导出excel文件 */@RequiresPermissions("echarts:memPutInto:export")    @RequestMapping(value = "export", method=RequestMethod.POST)    public String exportFile(MemPutInto memPutInto, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) {try {            String fileName = "员工投入状况"+DateUtils.getDate("yyyyMMddHHmmss")+".xlsx";            Page<MemPutInto> page = memPutIntoService.findPage(new Page<MemPutInto>(request, response, -1), memPutInto);    new ExportExcel("员工投入状况", MemPutInto.class).setDataList(page.getList()).write(response, fileName).dispose();    return null;} catch (Exception e) {addMessage(redirectAttributes, "导出员工投入状况记录失败！失败信息："+e.getMessage());}return "redirect:"+Global.getAdminPath()+"/echarts/memPutInto/?repage";    }/** * 导入Excel数据 */@RequiresPermissions("echarts:memPutInto:import")    @RequestMapping(value = "import", method=RequestMethod.POST)    public String importFile(MultipartFile file, RedirectAttributes redirectAttributes) {try {int successNum = 0;int failureNum = 0;StringBuilder failureMsg = new StringBuilder();ImportExcel ei = new ImportExcel(file, 1, 0);List<MemPutInto> list = ei.getDataList(MemPutInto.class);for (MemPutInto memPutInto : list){try{memPutIntoService.save(memPutInto);successNum++;}catch(ConstraintViolationException ex){failureNum++;}catch (Exception ex) {failureNum++;}}if (failureNum>0){failureMsg.insert(0, "，失败 "+failureNum+" 条员工投入状况记录。");}addMessage(redirectAttributes, "已成功导入 "+successNum+" 条员工投入状况记录"+failureMsg);} catch (Exception e) {addMessage(redirectAttributes, "导入员工投入状况失败！失败信息："+e.getMessage());}return "redirect:"+Global.getAdminPath()+"/echarts/memPutInto/?repage";    }/** * 下载导入员工投入状况数据模板 */@RequiresPermissions("echarts:memPutInto:import")    @RequestMapping(value = "import/template")    public String importFileTemplate(HttpServletResponse response, RedirectAttributes redirectAttributes) {try {            String fileName = "员工投入状况数据导入模板.xlsx";    List<MemPutInto> list = Lists.newArrayList();     new ExportExcel("员工投入状况数据", MemPutInto.class, 1).setDataList(list).write(response, fileName).dispose();    return null;} catch (Exception e) {addMessage(redirectAttributes, "导入模板下载失败！失败信息："+e.getMessage());}return "redirect:"+Global.getAdminPath()+"/echarts/memPutInto/?repage";    }

Controller调用的工具类

ImportExcel.java

/** * Copyright &copy; 2015-2020 <a href="http://www.jeeplus.org/">JeePlus</a> All rights reserved. */package com.jeeplus.common.utils.excel;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.text.NumberFormat;import java.text.SimpleDateFormat;import java.util.Collections;import java.util.Comparator;import java.util.Date;import java.util.List;import org.apache.commons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.web.multipart.MultipartFile;import com.google.common.collect.Lists;import com.jeeplus.common.utils.Reflections;import com.jeeplus.common.utils.excel.annotation.ExcelField;import com.jeeplus.modules.sys.entity.Area;import com.jeeplus.modules.sys.entity.Office;import com.jeeplus.modules.sys.entity.User;import com.jeeplus.modules.sys.utils.DictUtils;import com.jeeplus.modules.sys.utils.UserUtils;/** * 导入Excel文件（支持“XLS”和“XLSX”格式） * @author jeeplus * @version 2013-03-10 */public class ImportExcel {private static Logger log = LoggerFactory.getLogger(ImportExcel.class);/** * 工作薄对象 */private Workbook wb;/** * 工作表对象 */private Sheet sheet;/** * 标题行号 */private int headerNum;/** * 构造函数 * @param path 导入文件，读取第一个工作表 * @param headerNum 标题行号，数据行号=标题行号+1 * @throws InvalidFormatException  * @throws IOException  */public ImportExcel(String fileName, int headerNum) throws InvalidFormatException, IOException {this(new File(fileName), headerNum);}/** * 构造函数 * @param path 导入文件对象，读取第一个工作表 * @param headerNum 标题行号，数据行号=标题行号+1 * @throws InvalidFormatException  * @throws IOException  */public ImportExcel(File file, int headerNum) throws InvalidFormatException, IOException {this(file, headerNum, 0);}/** * 构造函数 * @param path 导入文件 * @param headerNum 标题行号，数据行号=标题行号+1 * @param sheetIndex 工作表编号 * @throws InvalidFormatException  * @throws IOException  */public ImportExcel(String fileName, int headerNum, int sheetIndex) throws InvalidFormatException, IOException {this(new File(fileName), headerNum, sheetIndex);}/** * 构造函数 * @param path 导入文件对象 * @param headerNum 标题行号，数据行号=标题行号+1 * @param sheetIndex 工作表编号 * @throws InvalidFormatException  * @throws IOException  */public ImportExcel(File file, int headerNum, int sheetIndex) throws InvalidFormatException, IOException {this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);}/** * 构造函数 * @param file 导入文件对象 * @param headerNum 标题行号，数据行号=标题行号+1 * @param sheetIndex 工作表编号 * @throws InvalidFormatException  * @throws IOException  */public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) throws InvalidFormatException, IOException {this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);}/** * 构造函数 * @param path 导入文件对象 * @param headerNum 标题行号，数据行号=标题行号+1 * @param sheetIndex 工作表编号 * @throws InvalidFormatException  * @throws IOException  */public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) throws InvalidFormatException, IOException {if (StringUtils.isBlank(fileName)){throw new RuntimeException("导入文档为空!");}else if(fileName.toLowerCase().endsWith("xls")){    this.wb = new HSSFWorkbook(is);            }else if(fileName.toLowerCase().endsWith("xlsx")){          this.wb = new XSSFWorkbook(is);        }else{          throw new RuntimeException("文档格式不正确!");        }  if (this.wb.getNumberOfSheets()<sheetIndex){throw new RuntimeException("文档中没有工作表!");}this.sheet = this.wb.getSheetAt(sheetIndex);this.headerNum = headerNum;log.debug("Initialize success.");}/** * 获取行对象 * @param rownum * @return */public Row getRow(int rownum){return this.sheet.getRow(rownum);}/** * 获取数据行号 * @return */public int getDataRowNum(){return headerNum+1;}/** * 获取最后一个数据行号 * @return */public int getLastDataRowNum(){return this.sheet.getLastRowNum()+headerNum;}/** * 获取最后一个列号 * @return */public int getLastCellNum(){return this.getRow(headerNum).getLastCellNum();}/** * 获取单元格值 * @param row 获取的行 * @param column 获取单元格列号 * @return 单元格值 */public Object getCellValue(Row row, int column) {Object val = "";try {Cell cell = row.getCell(column);if (cell != null) {if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {// val = cell.getNumericCellValue();// 当excel 中的数据为数值或日期是需要特殊处理if (HSSFDateUtil.isCellDateFormatted(cell)) {double d = cell.getNumericCellValue();Date date = HSSFDateUtil.getJavaDate(d);SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");val = dformat.format(date);} else {NumberFormat nf = NumberFormat.getInstance();nf.setGroupingUsed(false);// true时的格式：1,234,567,890val = nf.format(cell.getNumericCellValue());// 数值类型的数据为double，所以需要转换一下}} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {val = cell.getStringCellValue();} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {val = cell.getCellFormula();} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {val = cell.getBooleanCellValue();} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {val = cell.getErrorCellValue();}}} catch (Exception e) {return val;}return val;}/** * 获取导入数据列表 * @param cls 导入对象类型 * @param groups 导入分组 */public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{List<Object[]> annotationList = Lists.newArrayList();// Get annotation field Field[] fs = cls.getDeclaredFields();for (Field f : fs){ExcelField ef = f.getAnnotation(ExcelField.class);if (ef != null && (ef.type()==0 || ef.type()==2)){if (groups!=null && groups.length>0){boolean inGroup = false;for (int g : groups){if (inGroup){break;}for (int efg : ef.groups()){if (g == efg){inGroup = true;annotationList.add(new Object[]{ef, f});break;}}}}else{annotationList.add(new Object[]{ef, f});}}}// Get annotation methodMethod[] ms = cls.getDeclaredMethods();for (Method m : ms){ExcelField ef = m.getAnnotation(ExcelField.class);if (ef != null && (ef.type()==0 || ef.type()==2)){if (groups!=null && groups.length>0){boolean inGroup = false;for (int g : groups){if (inGroup){break;}for (int efg : ef.groups()){if (g == efg){inGroup = true;annotationList.add(new Object[]{ef, m});break;}}}}else{annotationList.add(new Object[]{ef, m});}}}// Field sortingCollections.sort(annotationList, new Comparator<Object[]>() {public int compare(Object[] o1, Object[] o2) {return new Integer(((ExcelField)o1[0]).sort()).compareTo(new Integer(((ExcelField)o2[0]).sort()));};});//log.debug("Import column count:"+annotationList.size());// Get excel dataList<E> dataList = Lists.newArrayList();for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {E e = (E)cls.newInstance();int column = 0;Row row = this.getRow(i);StringBuilder sb = new StringBuilder();for (Object[] os : annotationList){Object val = this.getCellValue(row, column++);if (val != null){ExcelField ef = (ExcelField)os[0];// If is dict type, get dict valueif (StringUtils.isNotBlank(ef.dictType())){val = DictUtils.getDictValue(val.toString(), ef.dictType(), "");//log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);}// Get param type and type castClass<?> valType = Class.class;if (os[1] instanceof Field){valType = ((Field)os[1]).getType();}else if (os[1] instanceof Method){Method method = ((Method)os[1]);if ("get".equals(method.getName().substring(0, 3))){valType = method.getReturnType();}else if("set".equals(method.getName().substring(0, 3))){valType = ((Method)os[1]).getParameterTypes()[0];}}//log.debug("Import value type: ["+i+","+column+"] " + valType);try {//如果导入的java对象，需要在这里自己进行变换。if (valType == String.class){String s = String.valueOf(val.toString());if(StringUtils.endsWith(s, ".0")){val = StringUtils.substringBefore(s, ".0");}else{val = String.valueOf(val.toString());}}else if (valType == Integer.class){val = Double.valueOf(val.toString()).intValue();}else if (valType == Long.class){val = Double.valueOf(val.toString()).longValue();}else if (valType == Double.class){val = Double.valueOf(val.toString());}else if (valType == Float.class){val = Float.valueOf(val.toString());}else if (valType == Date.class){SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");val=sdf.parse(val.toString()); }else if (valType == User.class){val = UserUtils.getByUserName(val.toString());}else if (valType == Office.class){val = UserUtils.getByOfficeName(val.toString());}else if (valType == Area.class){val = UserUtils.getByAreaName(val.toString());}else{if (ef.fieldType() != Class.class){val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());}else{val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString());}}} catch (Exception ex) {log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());val = null;}// set entity valueif (os[1] instanceof Field){Reflections.invokeSetter(e, ((Field)os[1]).getName(), val);}else if (os[1] instanceof Method){String mthodName = ((Method)os[1]).getName();if ("get".equals(mthodName.substring(0, 3))){mthodName = "set"+StringUtils.substringAfter(mthodName, "get");}Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val});}}sb.append(val+", ");}dataList.add(e);log.debug("Read success: ["+i+"] "+sb.toString());}return dataList;}///**// * 导入测试// *///public static void main(String[] args) throws Throwable {////ImportExcel ei = new ImportExcel("target/export.xlsx", 1);////for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) {//Row row = ei.getRow(i);//for (int j = 0; j < ei.getLastCellNum(); j++) {//Object val = ei.getCellValue(row, j);//System.out.print(val+", ");//}//System.out.print("\n");//}////}}

工具类

ExportExcel.java

/** * Copyright &copy; 2015-2020 <a href="http://www.jeeplus.org/">JeePlus</a> All rights reserved. */package com.jeeplus.common.utils.excel;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.util.Collections;import java.util.Comparator;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang3.StringUtils;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Comment;import org.apache.poi.ss.usermodel.DataFormat;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;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFClientAnchor;import org.apache.poi.xssf.usermodel.XSSFRichTextString;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import com.google.common.collect.Lists;import com.jeeplus.common.utils.Encodes;import com.jeeplus.common.utils.Reflections;import com.jeeplus.common.utils.excel.annotation.ExcelField;import com.jeeplus.modules.sys.utils.DictUtils;/** * 导出Excel文件（导出“XLSX”格式，支持大数据量导出   @see org.apache.poi.ss.SpreadsheetVersion） * @author jeeplus * @version 2013-04-21 */public class ExportExcel {private static Logger log = LoggerFactory.getLogger(ExportExcel.class);/** * 工作薄对象 */private SXSSFWorkbook wb;/** * 工作表对象 */private Sheet sheet;/** * 样式列表 */private Map<String, CellStyle> styles;/** * 当前行号 */private int rownum;/** * 注解列表（Object[]{ ExcelField, Field/Method }） */List<Object[]> annotationList = Lists.newArrayList();/** * 构造函数 * @param title 表格标题，传“空值”，表示无标题 * @param cls 实体对象，通过annotation.ExportField获取标题 */public ExportExcel(String title, Class<?> cls){this(title, cls, 1);}/** * 构造函数 * @param title 表格标题，传“空值”，表示无标题 * @param cls 实体对象，通过annotation.ExportField获取标题 * @param type 导出类型（1:导出数据；2：导出模板） * @param groups 导入分组 */public ExportExcel(String title, Class<?> cls, int type, int... groups){// Get annotation field Field[] fs = cls.getDeclaredFields();for (Field f : fs){ExcelField ef = f.getAnnotation(ExcelField.class);if (ef != null && (ef.type()==0 || ef.type()==type)){if (groups!=null && groups.length>0){boolean inGroup = false;for (int g : groups){if (inGroup){break;}for (int efg : ef.groups()){if (g == efg){inGroup = true;annotationList.add(new Object[]{ef, f});break;}}}}else{annotationList.add(new Object[]{ef, f});}}}// Get annotation methodMethod[] ms = cls.getDeclaredMethods();for (Method m : ms){ExcelField ef = m.getAnnotation(ExcelField.class);if (ef != null && (ef.type()==0 || ef.type()==type)){if (groups!=null && groups.length>0){boolean inGroup = false;for (int g : groups){if (inGroup){break;}for (int efg : ef.groups()){if (g == efg){inGroup = true;annotationList.add(new Object[]{ef, m});break;}}}}else{annotationList.add(new Object[]{ef, m});}}}// Field sortingCollections.sort(annotationList, new Comparator<Object[]>() {public int compare(Object[] o1, Object[] o2) {return new Integer(((ExcelField)o1[0]).sort()).compareTo(new Integer(((ExcelField)o2[0]).sort()));};});// InitializeList<String> headerList = Lists.newArrayList();for (Object[] os : annotationList){String t = ((ExcelField)os[0]).title();// 如果是导出，则去掉注释if (type==1){String[] ss = StringUtils.split(t, "**", 2);if (ss.length==2){t = ss[0];}}headerList.add(t);}initialize(title, headerList);}/** * 构造函数 * @param title 表格标题，传“空值”，表示无标题 * @param headers 表头数组 */public ExportExcel(String title, String[] headers) {initialize(title, Lists.newArrayList(headers));}/** * 构造函数 * @param title 表格标题，传“空值”，表示无标题 * @param headerList 表头列表 */public ExportExcel(String title, List<String> headerList) {initialize(title, headerList);}/** * 初始化函数 * @param title 表格标题，传“空值”，表示无标题 * @param headerList 表头列表 */private void initialize(String title, List<String> headerList) {this.wb = new SXSSFWorkbook(500);this.sheet = wb.createSheet("Export");this.styles = createStyles(wb);// Create titleif (StringUtils.isNotBlank(title)){Row titleRow = sheet.createRow(rownum++);titleRow.setHeightInPoints(30);Cell titleCell = titleRow.createCell(0);titleCell.setCellStyle(styles.get("title"));titleCell.setCellValue(title);sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));}// Create headerif (headerList == null){throw new RuntimeException("headerList not null!");}Row headerRow = sheet.createRow(rownum++);headerRow.setHeightInPoints(16);for (int i = 0; i < headerList.size(); i++) {Cell cell = headerRow.createCell(i);cell.setCellStyle(styles.get("header"));String[] ss = StringUtils.split(headerList.get(i), "**", 2);if (ss.length==2){cell.setCellValue(ss[0]);Comment comment = this.sheet.createDrawingPatriarch().createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));comment.setString(new XSSFRichTextString(ss[1]));cell.setCellComment(comment);}else{cell.setCellValue(headerList.get(i));}sheet.autoSizeColumn(i);}for (int i = 0; i < headerList.size(); i++) {  int colWidth = sheet.getColumnWidth(i)*2;        sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);  }log.debug("Initialize success.");}/** * 创建表格样式 * @param wb 工作薄对象 * @return 样式列表 */private Map<String, CellStyle> createStyles(Workbook wb) {Map<String, CellStyle> styles = new HashMap<String, CellStyle>();CellStyle style = wb.createCellStyle();style.setAlignment(CellStyle.ALIGN_CENTER);style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);Font titleFont = wb.createFont();titleFont.setFontName("Arial");titleFont.setFontHeightInPoints((short) 16);titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);style.setFont(titleFont);styles.put("title", style);style = wb.createCellStyle();style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);style.setBorderRight(CellStyle.BORDER_THIN);style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setBorderLeft(CellStyle.BORDER_THIN);style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setBorderTop(CellStyle.BORDER_THIN);style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setBorderBottom(CellStyle.BORDER_THIN);style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());Font dataFont = wb.createFont();dataFont.setFontName("Arial");dataFont.setFontHeightInPoints((short) 10);style.setFont(dataFont);styles.put("data", style);style = wb.createCellStyle();style.cloneStyleFrom(styles.get("data"));style.setAlignment(CellStyle.ALIGN_LEFT);styles.put("data1", style);style = wb.createCellStyle();style.cloneStyleFrom(styles.get("data"));style.setAlignment(CellStyle.ALIGN_CENTER);styles.put("data2", style);style = wb.createCellStyle();style.cloneStyleFrom(styles.get("data"));style.setAlignment(CellStyle.ALIGN_RIGHT);styles.put("data3", style);style = wb.createCellStyle();style.cloneStyleFrom(styles.get("data"));//style.setWrapText(true);style.setAlignment(CellStyle.ALIGN_CENTER);style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);Font headerFont = wb.createFont();headerFont.setFontName("Arial");headerFont.setFontHeightInPoints((short) 10);headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);headerFont.setColor(IndexedColors.WHITE.getIndex());style.setFont(headerFont);styles.put("header", style);return styles;}/** * 添加一行 * @return 行对象 */public Row addRow(){return sheet.createRow(rownum++);}/** * 添加一个单元格 * @param row 添加的行 * @param column 添加列号 * @param val 添加值 * @return 单元格对象 */public Cell addCell(Row row, int column, Object val){return this.addCell(row, column, val, 0, Class.class);}/** * 添加一个单元格 * @param row 添加的行 * @param column 添加列号 * @param val 添加值 * @param align 对齐方式（1：靠左；2：居中；3：靠右） * @return 单元格对象 */public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){Cell cell = row.createCell(column);CellStyle style = styles.get("data"+(align>=1&&align<=3?align:""));try {if (val == null){cell.setCellValue("");} else if (val instanceof String || val instanceof Integer || val instanceof Long || val instanceof Short || val instanceof Double || val instanceof Float) {cell.setCellValue(val.toString());} else if (val instanceof Date) {DataFormat format = wb.createDataFormat();            style.setDataFormat(format.getFormat("yyyy-MM-dd"));cell.setCellValue((Date) val);} else {if (fieldType != Class.class){cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));}else{cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));}}} catch (Exception ex) {log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString());cell.setCellValue(val.toString());}cell.setCellStyle(style);return cell;}/** * 添加数据（通过annotation.ExportField添加数据） * @return list 数据列表 */public <E> ExportExcel setDataList(List<E> list){for (E e : list){int colunm = 0;Row row = this.addRow();StringBuilder sb = new StringBuilder();for (Object[] os : annotationList){ExcelField ef = (ExcelField)os[0];Object val = null;// Get entity valuetry{if (StringUtils.isNotBlank(ef.value())){val = Reflections.invokeGetter(e, ef.value());}else{if (os[1] instanceof Field){val = Reflections.invokeGetter(e, ((Field)os[1]).getName());}else if (os[1] instanceof Method){val = Reflections.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});}}// If is dict, get dict labelif (StringUtils.isNotBlank(ef.dictType())){val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), "");}}catch(Exception ex) {// Failure to ignorelog.info(ex.toString());val = "";}this.addCell(row, colunm++, val, ef.align(), ef.fieldType());sb.append(val + ", ");}log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString());}return this;}/** * 输出数据流 * @param os 输出数据流 */public ExportExcel write(OutputStream os) throws IOException{wb.write(os);return this;}/** * 输出到客户端 * @param fileName 输出文件名 */public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{response.reset();        response.setContentType("application/octet-stream; charset=utf-8");        response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));write(response.getOutputStream());return this;}/** * 输出到文件 * @param fileName 输出文件名 */public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{FileOutputStream os = new FileOutputStream(name);this.write(os);return this;}/** * 清理临时文件 */public ExportExcel dispose(){wb.dispose();return this;}///**// * 导出测试// *///public static void main(String[] args) throws Throwable {////List<String> headerList = Lists.newArrayList();//for (int i = 1; i <= 10; i++) {//headerList.add("表头"+i);//}////List<String> dataRowList = Lists.newArrayList();//for (int i = 1; i <= headerList.size(); i++) {//dataRowList.add("数据"+i);//}////List<List<String>> dataList = Lists.newArrayList();//for (int i = 1; i <=1000000; i++) {//dataList.add(dataRowList);//}////ExportExcel ee = new ExportExcel("表格标题", headerList);////for (int i = 0; i < dataList.size(); i++) {//Row row = ee.addRow();//for (int j = 0; j < dataList.get(i).size(); j++) {//ee.addCell(row, j, dataList.get(i).get(j));//}//}//ee.writeFile("target/export.xlsx");////ee.dispose();////log.debug("Export success.");////}}

工具类

定义注解

ExcelField.java

 /** * Copyright &copy; 2015-2020 <a href="http://www.jeeplus.org/">JeePlus</a> All rights reserved. */package com.jeeplus.common.utils.excel.annotation;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/** * Excel注解定义 * @author jeeplus * @version 2013-03-10 */@Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})@Retention(RetentionPolicy.RUNTIME)public @interface ExcelField {/** * 导出字段名（默认调用当前字段的“get”方法，如指定导出字段为对象，请填写“对象名.对象属性”，例：“area.name”、“office.name”） */String value() default "";/** * 导出字段标题（需要添加批注请用“**”分隔，标题**批注，仅对导出模板有效） */String title();/** * 字段类型（0：导出导入；1：仅导出；2：仅导入） */int type() default 0;/** * 导出字段对齐方式（0：自动；1：靠左；2：居中；3：靠右） */int align() default 0;/** * 导出字段字段排序（升序） */int sort() default 0;/** * 如果是字典类型，请设置字典的type值 */String dictType() default "";/** * 反射类型 */Class<?> fieldType() default Class.class;/** * 字段归属组（根据分组导出导入） */int[] groups() default {};}

/** * Copyright &copy; 2015-2020 <a href="http://www.jeeplus.org/">JeePlus</a> All rights reserved. */package com.jeeplus.common.utils.excel.fieldtype;import com.jeeplus.common.utils.StringUtils;import com.jeeplus.modules.sys.entity.Area;import com.jeeplus.modules.sys.utils.UserUtils;/** * 字段类型转换 * @author jeeplus * @version 2013-03-10 */public class AreaType {/** * 获取对象值（导入） */public static Object getValue(String val) {for (Area e : UserUtils.getAreaList()){if (StringUtils.trimToEmpty(val).equals(e.getName())){return e;}}return null;}/** * 获取对象值（导出） */public static String setValue(Object val) {if (val != null && ((Area)val).getName() != null){return ((Area)val).getName();}return "";}}

/** * Copyright &copy; 2015-2020 <a href="http://www.jeeplus.org/">JeePlus</a> All rights reserved. */package com.jeeplus.common.utils.excel.fieldtype;import com.jeeplus.common.utils.StringUtils;import com.jeeplus.modules.sys.entity.Office;import com.jeeplus.modules.sys.utils.UserUtils;/** * 字段类型转换 * @author jeeplus * @version 2013-03-10 */public class OfficeType {/** * 获取对象值（导入） */public static Object getValue(String val) {for (Office e : UserUtils.getOfficeList()){if (StringUtils.trimToEmpty(val).equals(e.getName())){return e;}}return null;}/** * 设置对象值（导出） */public static String setValue(Object val) {if (val != null && ((Office)val).getName() != null){return ((Office)val).getName();}return "";}}

/** * Copyright &copy; 2015-2020 <a href="http://www.jeeplus.org/">JeePlus</a> All rights reserved. */package com.jeeplus.common.utils.excel.fieldtype;import java.util.List;import com.google.common.collect.Lists;import com.jeeplus.common.utils.Collections3;import com.jeeplus.common.utils.SpringContextHolder;import com.jeeplus.common.utils.StringUtils;import com.jeeplus.modules.sys.entity.Role;import com.jeeplus.modules.sys.service.SystemService;/** * 字段类型转换 * @author jeeplus * @version 2013-5-29 */public class RoleListType {private static SystemService systemService = SpringContextHolder.getBean(SystemService.class);/** * 获取对象值（导入） */public static Object getValue(String val) {List<Role> roleList = Lists.newArrayList();List<Role> allRoleList = systemService.findAllRole();for (String s : StringUtils.split(val, ",")){for (Role e : allRoleList){if (StringUtils.trimToEmpty(s).equals(e.getName())){roleList.add(e);}}}return roleList.size()>0?roleList:null;}/** * 设置对象值（导出） */public static String setValue(Object val) {if (val != null){@SuppressWarnings("unchecked")List<Role> roleList = (List<Role>)val;return Collections3.extractToString(roleList, "name", ", ");}return "";}}

具体 需要 jar包 可能有
相关 的jar包   poi-3.9.jar      poi-ooxml-3.9.jar       poi-ooxml-schemas-3.9.jar

最后 JeeSite只能导入导出单表   如果 想导入多表 数据 可能  需要 自己 定义一个   VO实体 类

博主 本人 没有 亲自 试过