java 实现指定内容导出execl模板
业务层
/**
点击导出excel模板,触发的接口
*/
@PostMapping("exportData")
@ResponseBody
public void exportData(ServletRequest request, HttpServletResponse response) throws FileNotFoundException {
//业务逻辑部分,获取需要导出的数据
List<实体> list = selectList(null);
//获取要导出execl 的指定字段值
List<Excel模板实体> exportList=new ArrayList<Excel模板实体>();
Integer i=1;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//对excel 指定字段赋值
for(实体 c:list){ //需要导出的数据
Excel模板实体 ce=new Excel模板实体();
ce.setId(i);// 序号
ce.setxxx(xxx); // xxx1
ce.setxxx(xxx); // xxx2
...
...
exportList.add(ce);
i++;
}
//列名
String[] columnNames = { "序号","xxx1", "xxx2", ... , ... };
ExportExcelWrapper<Excel模板实体> util = new ExportExcelWrapper<Excel模板实体>();
//String path=Thread.currentThread().getContextClassLoader().getResource("/static/uploads/export").getPath();
String path = ResourceUtils.getURL("classpath:").getPath()+"/static/uploads/export";
String filename=System.currentTimeMillis()+ RandomUtil.randomInt(1111,9999)+".xls";
path=path+""+filename;
//开始导出excel模板
//util.exportExcel("Excel模板名称xxx", columnNames, exportList, new FileOutputStream(path), ExportExcelUtil.EXCEL_FILE_2003);
util.exportExcel("Excel模板名称xxx","案件信息", columnNames, exportList, response, ExportExcelUtil.EXCEL_FILE_2003);
}
包装类
import java.net.URLEncoder;
import java.util.Collection;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
/**
* 包装类
* @author
*
* @param <T>
*/
public class ExportExcelWrapper<T> extends ExportExcelUtil<T> {
/**
* <p>
* 导出带有头部标题行的Excel <br>
* 时间格式默认:yyyy-MM-dd hh:mm:ss <br>
* </p>
*
* @param title 表格标题
* @param headers 头部标题集合
* @param dataset 数据集合
* @param out 输出流
* @param version 2003 或者 2007,不传时默认生成2003版本
*/
public void exportExcel(String fileName, String title, String[] headers, Collection<T> dataset, HttpServletResponse response, String version) {
try {
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "UTF-8") + ".xls");
if(StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())){
exportExcel2003(title, headers, dataset, response.getOutputStream(), "yyyy-MM-dd hh:mm:ss");
}else{
exportExcel2007(title, headers, dataset, response.getOutputStream(), "yyyy-MM-dd hh:mm:ss");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
导出excel 模板工具类
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apa