首先，在contorller层中方法代码
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bytx.salary.controller.oa.ExcelUtil;
import com.bytx.salary.controller.sys.BaseController;
import com.bytx.salary.mapper.emp.EmpEntryMapper;
import com.bytx.salary.mapper.emp.InviteMapper;
import com.bytx.salary.model.emp.EmpEntry;
import com.bytx.salary.model.emp.Invite;
import com.bytx.salary.model.emp.InviteResult;
import com.bytx.salary.model.oa.ParameterTable;
import com.bytx.salary.service.emp.EmpEntryService;
import com.bytx.salary.service.emp.InviteService;
import com.bytx.salary.service.oa.TestTableImpl;
import com.bytx.salary.utils.CustomPage;
import com.bytx.salary.utils.ReturnUtil;
import com.bytx.salary.utilsc.ChineseToSpell;
import com.bytx.salary.utilsc.ImportExeclUtil;
import com.bytx.salary.utilsc.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* 导出通用单表接口中的字段选取方法
*
* @return
*/
@ResponseBody
@RequestMapping(value = "/getField", method = RequestMethod.POST)
public JSONObject getField(@RequestBody JSONObject params) throws Exception {
String table_schema = "bytx_salary";//如果是一个库的话直接设置常量
String table_name = params.getString("tableName");//这个是页面传过来的参数，也可以传多个表名
List<Map<String, String>> columns = testTableImpl.getField(table_name, table_schema);
for (int j = 0; j < columns.size(); j++) {
Map<String, String> map = columns.get(j);
if (map.get("COLUMN_NAME").toString().equals("uuid") || map.get("COLUMN_NAME").toString().equals("creator_id") || map.get("COLUMN_NAME").toString().equals("creator")||
map.get("COLUMN_NAME").toString().equals("create_time")|| map.get("COLUMN_NAME").toString().equals("modifier") || map.get("COLUMN_NAME").toString().equals("modifier_id")|| map.get("COLUMN_NAME").toString().equals("modify_time") || map.get("COLUMN_NAME").toString().equals("creator_org_id")) {
columns.remove(j);
j--;
}
}
return ReturnUtil.ajaxDoneSuccess("查询成功!", columns);
}
将上边的字段传给前端，前端进行字段的选取后再将这个当成JSONArray数组
参数传到下面真正导出的方法中去：
/**
* 导出通用单表接口
*
* @return
*/
@ResponseBody
@RequestMapping(value = "/getExcel", method = RequestMethod.POST)
public void getExcel(@RequestBody JSONObject params) throws Exception {
Map<String, Object> paramMap = new HashMap<>();
JSONArray demo = (JSONArray) params.get("columns");//这个是页面传过来的字段选取（就是页面上选择导出字段）
String d = demo.toJSONString();
List<Map<String, String>> columns = (List<Map<String, String>>) JSONArray.parse(d);
paramMap.put("staffName", params.get("staffName"));
paramMap.put("staffSex", params.get("staffSex"));
paramMap.put("staffProperty", params.get("staffProperty"));
paramMap.put("baseCode", params.get("baseCode"));
paramMap.put("tryState", params.get("tryState"));
paramMap.put("reexState", params.get("reexState"));
paramMap.put("inputTimeStart", params.get("inputTimeStart"));
paramMap.put("inputTimeEnd", params.get("inputTimeEnd"));
String table_schema = "bytx_salary";//如果是一个库的话直接设置常量
String table_name = params.getString("tableName");//这个是页面传过来的参数，也可以传多个表名
String flag = params.getString("flag");
Date now = new Date();
String createTime = dateFormat.format(now);//格式化然后放入字符串中
String fileName = params.getString("fileName") + createTime;
String sheetName = null;
int sheetCount = 1;
// List<Map<String, String>> columns = testTableImpl.getField(table_name, table_schema);
// for (int j = 0; j < columns.size(); j++) {
// Map<String, String> map = columns.get(j);
// if (map.get("COLUMN_NAME").toString().equals("uuid") || map.get("COLUMN_NAME").toString().equals("creator_id") || map.get("COLUMN_NAME").toString().equals("creator")||
// map.get("COLUMN_NAME").toString().equals("create_time")|| map.get("COLUMN_NAME").toString().equals("modifier") || map.get("COLUMN_NAME").toString().equals("modifier_id")|| map.get("COLUMN_NAME").toString().equals("modify_time") || map.get("COLUMN_NAME").toString().equals("creator_org_id")) {
// columns.remove(j);
// j--;
// }
// }
String src = null;
List<ParameterTable> listTable = new ArrayList<>();
for (int i = 0; i < columns.size(); i++) { //这是将字段的英文和表中的注释分别取出放到list集合中去
ParameterTable parameterTable = new ParameterTable();
parameterTable.setCOLUMN_NAME(columns.get(i).get("COLUMN_NAME"));
parameterTable.setCOLUMN_COMMENT(columns.get(i).get("COLUMN_COMMENT"));
listTable.add(parameterTable);
}
String headers[] = new String[listTable.size()];
String tableNames[] = new String[listTable.size()];
for (int i = 0; i < listTable.size(); i++) { //这个是将表字段和表注释分别放入数组中去，一会导出的时候根据这个相互对应
tableNames[i] = listTable.get(i).getCOLUMN_NAME().toString();
headers[i] = listTable.get(i).getCOLUMN_COMMENT().toString();
}
src = StringUtils.join(tableNames, ",");
HSSFWorkbook workbook = new HSSFWorkbook();
// String headers[] = {"ID", "姓名", "性别"};
// String tableNames[]={"id","name","sex"};
List<Map> list = null;
if (flag.equals("1")) {//传入标识是1那就导出选中字段的数据
list = testTableImpl.getList(src, table_name);
} else {
list = testTableImpl.getLoad(src, table_name);//这是模板的下载
}
ExcelUtil.downLoadExcel(false, workbook, fileName, sheetName, sheetCount, headers, list, tableNames, response);//response是从下图中获取的
}
这是service层
import com.bytx.salary.mapper.oa.TestMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
/**
* Created by 13671 on 2020/6/2.
*/
@Service
public class TestTableImpl {
@Autowired
private TestMapper testMapper;
public List<Map<String, String>> getField(String table_name, String table_schema){
return testMapper.getField(table_name,table_schema);
}
public List<Map> getList(String src,String table_name){
return testMapper.getList(src,table_name);
}
public List<Map> getLoad(String src,String table_name){
return testMapper.getLoad(src,table_name);
}
}
mapper层接口：
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
/**
* Created by 13671 on 2020/6/2.
*/
@Mapper
public interface TestMapper {
List<Map<String, String>> getField(@Param("table_name") String table_name, @Param("table_schema") String table_schema);
List<Map> getList(@Param("src")String src,@Param("table_name") String table_name);
List<Map> getLoad(@Param("src")String src,@Param("table_name") String table_name);
}
mybatis层的SQL语句
<select id="getField" parameterType="string" resultType="map">
select COLUMN_NAME,COLUMN_COMMENT FROM information_schema.COLUMNS WHERE table_name=#{table_name} and table_schema=#{table_schema}
</select>
<select id="getList" resultType="map" statementType="STATEMENT">
select ${src} FROM ${table_name}
</select>
<select id="getLoad" resultType="map" statementType="STATEMENT">
select ${src} FROM ${table_name} where uuid='111111111111111'
</select>
导出工具类中的方法：
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
/**
* @author hz
* @Description: 封装 excel文件导出
* @create 2019-07-05
*/
public class ExcelUtil {
// private static final Logger log = (Logger) LoggerFactory.getLogger(ExcelUtil.class);
/**
*
* @param flag true 第一列数据为数字序号，false 第一列为正常数据显示
* @param workbook HSSFWorkbook对象
* @param fileName 下载后文件名称
* @param sheetName 工作簿sheet表名称 null为默认名称“sheet0”
* @param sheetCount 表列数总合
* @param headers 表第一列 标题名称数组
* @param list 表需要填入数据对象
* @param tableNames 根据标题名称一一对应数据库映射字段（为了从map.get(key)取值）
* @param response
* @return
*/
public static boolean downLoadExcel(boolean flag, HSSFWorkbook workbook, String fileName, String sheetName, int sheetCount, String headers[], List<Map> list, String tableNames[], HttpServletResponse response)
throws Exception{
//第一列为数字序号
// if(flag){
// if(sheetCount!=headers.length || sheetCount-1 != tableNames.length){
// // log.info("第一列为序号时，sheetCount参数和headers数组长度一致，且比tableNames数组长度大1，请核对代码");
// System.out.println("请核对sheet长度和headers，tableNames");
// return false;
// }
// }
// //第一列为正常数据，不为数字
// if(flag==false){
// if(sheetCount!=headers.length || sheetCount != tableNames.length){
// //log.info("sheetCount参数和headers数组tableNames数组长度应一致，请核对代码");
// System.out.println("请核对sheet长度，headers，tableNames");
// return false;
// }
// }
int rowNum = 1;
HSSFSheet sheet = null;
if(sheetName==null){
sheet = workbook.createSheet();//默认名称 “sheet0”
}else{
sheet = workbook.createSheet(sheetName);
}
for(int i=0;i<sheetCount;i++){
sheet.setColumnWidth(i, 3766);
}
HSSFRow row = sheet.createRow(0);//设置第一行所有标题字段
//表格每列所有字段名称赋值
for (int i = 0; i < headers.length; i++) {
row.createCell(i).setCellValue(new HSSFRichTextString(headers[i]));
}
for (Map map : list) {
HSSFRow row1 = sheet.createRow(rowNum);
if(flag){
for(int i=0;i<headers.length;i++){
if(i==0){
row1.createCell(i).setCellValue(rowNum);
continue;
}
row1.createCell(i).setCellValue( map.get(tableNames[i-1]).toString());
}
}else{
for(int i=0;i<headers.length;i++){
row1.createCell(i).setCellValue((String)((map.get(tableNames[i])==null ? "" :map.get(tableNames[i])).toString()));
}
}
rowNum++;
}
OutputStream out = null;
response.setContentType("application/ms-excel;charset=utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xls");
response.flushBuffer();
out=response.getOutputStream();
workbook.write(out);
out.close();
return true;
}
//正则表达式 用于匹配属性的第一个字母
private static final String REGEX = "[a-zA-Z]";
}