Excel导入导出代码

针对bootstrap table 这里做出对列表数据Excel导出。

使用pom.xml 需要用的jar

        <!--xxl-excel -->
        <dependency>
            <groupId>com.xuxueli</groupId>
            <artifactId>xxl-excel</artifactId>
            <version>1.0.0</version>
        </dependency>

xxl-excel默认必须指定导出的文件路径，为了避免这个问题。我们自行增加一个方法

import java.io.OutputStream;
import java.util.List;

import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.xuxueli.poi.excel.ExcelExportUtil;

public class ExcelExportUtil4DIY extends ExcelExportUtil{
    private static Logger logger = LoggerFactory.getLogger(ExcelExportUtil.class);
      /**
     * 导出Excel文件到磁盘
     * @param dataList
     * @param outputStream
     */
    public static void exportToFile(List<?> dataList, OutputStream outputStream){
        // workbook
        Workbook workbook = exportWorkbook(dataList);
        try {
            // workbook 2 FileOutputStream
            workbook.write(outputStream);
            // flush
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            throw new RuntimeException(e);
        } finally {
            try {
                if (outputStream!=null) {
                    outputStream.close();
                }
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
                throw new RuntimeException(e);
            }
        }
    }
}

模块对应的html做以下修改

<button type="button" class="btn btn-primary" onclick="exportExcel(1)">
<i class="fa fa-export" aria-hidden="true"></i>导出当页数据
</button>
<button type="button" class="btn btn-primary" onclick="exportExcel(2)">
<i class="fa fa-export" aria-hidden="true"></i>导出全部数据
</button>
<button type="button" class="btn btn-primary" onclick="exportExcel(3)">
<i class="fa fa-export" aria-hidden="true"></i>导出符合条件全部数据
</button>

模块对应的JS做以下修改

    //导出Excel type导出的类型
    //type 1 导出当页数据 2 导出全部数据 3 导出符合条件全部数据
    function exportExcel(type) {
        //获取table的分页参数值
        var offset = $('#exampleTable').bootstrapTable('getOptions').pageSize;
        var limit = $('#exampleTable').bootstrapTable('getOptions').pageNumber * offset;
        data = 'limit='+limit+'&offset='+offset;
        //后端导出的方法
        document.location.href = prefix + "/exportExcel?type="+type+"&"+ data;
    }

模块对应Controller增加以下方法

 /**
     * 导出
     * @param request
     * @param response
     * @author Jeff
     * @return
     * @throws Exception
     */
    @RequestMapping(value="/exportExcel")
    public void exportExcel(@RequestParam Map<String, Object> params,HttpServletRequest request,HttpServletResponse response) throws Exception{
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String filename = "测试导出功能"+format.format(new Date().getTime())+".xls";
        response.setContentType("application/ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename="+new String(filename.getBytes(),"iso-8859-1"));
        OutputStream out = response.getOutputStream();
        try {
            Query query = new Query(params);
            String type = request.getParameter("type");
            //导出当前页面数据
            if(type.equals("1")){
  query.remove("type");
                List<XXXDO> XxxDOs = XxxService.list(query);
                ExcelExportUtil4DIY.exportToFile(XxxDOs,out);
            }
            //导出全部数据
            if(type.equals("2")){
                List<XXXDO> XxxDOs = XxxService.list(null);
                ExcelExportUtil4DIY.exportToFile(XxxDOs,out);
            }
            //导出符合条件的全部数据
            if(type.equals("3")){
  query.remove("type");
                query.remove("offset");
                query.remove("limit");
                List<XXXDO> XxxDOs = XxxService.list(query);
                ExcelExportUtil4DIY.exportToFile(XxxDOs,out);
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.info("exportExcel出错"+e.getMessage());
        }finally{
            out.close();
        }
    }

Excel导入

模块对应的html

<form class="avatar-form" action="/common/dict/excle" enctype="multipart/form-data" method="post">
     <div class="avatar-upload">
          <input class="avatar-input" id=excle name="excle" type="file">
          <button class="btn btn-primary btn-block avatar-save" type="submit">导入</button>
     </div>
</form>

模块对应的Controller添加方法

 /**
     * 导入
     * @author Jeff
     * @param excelFilev
     * @throws Exception
     */
    @RequestMapping(value="/excle")
    public String importExcel(@RequestParam("excle") MultipartFile file) throws Exception{
    	Workbook workbook = WorkbookFactory.create(file.getInputStream());
    	List<Object> dictList = ExcelImportUtil4DIY.importExcel(DictDO.class, workbook);
    	if(dictList != null && dictList.size()>0){
    		for (Object object : dictList) {
    			dictService.save((DictDO)object);
			}
    	}
    	return "common/dict/dict";
    }

模块对应的工具类

import java.util.List;

import org.apache.poi.ss.usermodel.Workbook;

import com.xuxueli.poi.excel.ExcelImportUtil;

public class ExcelImportUtil4DIY extends ExcelImportUtil {
    /**
    * 导入Excel文件到数据库
    * @param sheetClass
    * @param excelFile
    */
   public static List<Object> importFile(Class<?> sheetClass,Workbook workbook){
	   return importExcel(sheetClass,workbook);
   }
}