/**
* 导出通⽤⽅法
*
* @param title 表格第⼀⾏标题
* @param templateName 模板⽂件名称
* @param newFileName 新⽣成的⽂件名
* @param mapList 数据集合 list中的map 必须是LinkedHashMap，按照put的先后排序防⽌数据与标题头不对应
* @param digitPosition 数字索引集合，⽤于处理导出的表格格式为数字类型
* @param h 从表格第⼏⾏开始 0开始
* @author Xing
* @date 2019-07-23
*/
public void exportExcel(HttpServletResponse response, String title, String templateName, String newFileName, List<Map<String, Object>> mapList, Inte ger[] digitPosition, Integer h){
FileInputStream in=null;
try{
in=new FileInputStream(new Catalogue()+ templateName));
XSSFWorkbook workbook =new XSSFWorkbook(in);
// 设置字体
CellStyle redStyle = ateCellStyle();
HSSFFont redFont = ateFont();
//颜⾊
redFont.setColor(Font.COLOR_RED);
//设置字体⼤⼩
redFont.setFontHeightInPoints((short)10);
//字体
//redFont.setFontName("宋体");
redStyle.setFont(redFont);
XSSFSheet sheet = SheetAt(0);
if(sheet !=null){
XSSFRow row = Row(0);
if(row ==null){
row = ateRow(0);
}
XSSFCell cell = Cell(0);
if(cell ==null){
cell = ateCell(0);
}
cell.setCellValue(title);
List<Integer> integers = Arrays.asList(digitPosition);//数字格式索引
for(int i =0; i < mapList.size(); i++){
Map<String, Object> map = (i);
Object[] values = map.values().toArray();
row = ateRow(i + h);//从第三⾏开始
//根据excel模板格式写⼊数据....
for(int k =0; k < values.length; k++){
double height=400;
row.setHeight((short)height);
cell = Cell(k)==null? ateCell(k): Cell(k);
cell.setCellStyle(redStyle);//数据写⼊字体
ains(k)){//特殊标记索引特殊处理
BigDecimal money = values[k]==null? BigDecimal.ZERO:((BigDecimal) values[k]);
cell.setCellValue(money.doubleValue());
}else{
cell.setCellValue(values[k]==null?"": values[k].toString());
}
}
}
}
String pageName = newFileName + System.currentTimeMillis()+".xlsx";
setResponseHeader(response, pageName);
workbook.OutputStream());
}catch(Exception e){
e.printStackTrace();
}finally{
try{
in.close();
}catch(IOException e){
e.printStackTrace();
}
}
}
protected void setResponseHeader(HttpServletResponse response, String fileName) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment;filename=\""+ de(fileName,"UTF-8")+"\""); response.setHeader("Cache-Control","must-revali
date, post-check=0, pre-check=0");
response.setHeader("Pragma","public");
response.setDateHeader("Expires",0);
}
/**
* 根据当前row⾏，来创建index标记的列数，并赋值数据
*
* @author Xing
* @date 2019-07-23
*/
private void createRowAndCell(Object obj, XSSFRow row, XSSFCell cell, int index){
cell = Cell(index)==null? ateCell(index): Cell(index);
cell.setCellValue(obj ==null?"": String());
}
}
3、创建ExportConfig（和第⼆步是同⽬录）
package com.soa.ssage;
import org.t.properties.ConfigurationProperties;
layui下载import org.springframework.stereotype.Component;
/**
* 导出配置类
*
* @author Xing
* @date 2019-07-11
*/
@Component
@ConfigurationProperties(prefix ="export")
public class ExportConfig {
/**
* 模版⽬录
*/
private String catalogue;
public String getCatalogue(){
return catalogue;
}
public void setCatalogue(String catalogue){
this.catalogue = catalogue;
}
}
4、html页⾯写按钮
<permission value="sys:client:export">
<button type="button" lay-submit=""class="layui-btn layui-btn-warm" id="exportExcel" name="exportExcel">
<i class="layui-icon"> </i>导出Excel
</button>
</permission>
5、js
$("#exportExcel").bind("click",function(){
excel("searchId","back/soaexport/userInfoExcel");
});
//导出通⽤⽅法 Xing
function excel(searchId, urls){
urls = requestLink + urls;
if(searchId !=""){
$("#"+ searchId).find("input,select").each(function(){
var id =this.id;
var value =this.value;
if(id.length >0){
if(value !=""&& value !=null){
if(urls.indexOf("?")>-1){
urls +="&"+ id +"="+ value;
}else{
urls +="?"+ id +"="+ value;
}
}
}
});
}
window.location.href = urls;
}
6、控制器调⽤
package com.soa.llerBack;
import com.soa.bus.rpc.api.*;
import com.soamon.web.BaseController;
import com.soa.ssage.ExportUtils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.*;
/**
* 导出管理
*
* @author Xing
* @return
* @date 2019-12-05
*/
@RestController
@RequestMapping("/back/soaexport")
@Api(value ="ExportController", description ="导出管理API")
public class ExportController extends BaseController {
//⽤户信息模版
private final String USER_INFO_TEMPLATE="user_info_template.xlsx";
/
**
* 导出下载⼯具类
*/
@Autowired
private ExportUtils exportUtils;
@Autowired
private IHjClientService hjClientService;
/**
* 客户信息导出
*
* @return java.lang.String
* @author Xing
* @date 2019-12-04
*/
@ApiOperation(value ="客户信息导出", notes ="客户信息导出")
@GetMapping("/userInfoExcel")
@RequiresPermissions("sys:client:export")
public String userInfoExcel(HttpServletRequest request, HttpServletResponse response){
String templateName =this.USER_INFO_TEMPLATE;//模板名称
String title ="客户信息";//标题
String newFileName ="客户信息";//⽂件名
Integer[] digitPosition =new Integer[]{};
Map cmap =new HashMap();
List<Map<String, Object>> mapList =this.hjClientService.findExcelList(cmap);//数据源 portExcel(response, title, templateName, newFileName, mapList, digitPosition,2);
return"succeed";
}
}
7、业务层处理数据
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论