一、使用POI做execl下载,封装列为String[]数组做为列数组,封装List<String[]>集合数组做为execl内容集合数组,最后数据库查询集合对象类封装为集合数组,不说废话直接上代码。
二、代码
1、加入pom.xml坐标
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>RELEASE</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>RELEASE</version>
</dependency>
2、Controller 中实现
excelData.setFileName(CommunalUtils.userFileName);
String[] strHead = new String[]{"用户ID","姓名","性别","年龄","手机","邮箱","角色","注册时间"};
excelData.setHead(strHead);
List<SystemUserDownload> listuser = systemUserMapper.sysUserDownload();
List<String[]> list = new ArrayList<String[]>();
for (int i = 0; i < listuser.size(); i++) {
String[] str = ExcelUtil.getFiledValues(listuser.get(i));
list.add(str);
}
excelData.setData(list);
ExcelUtil.exportExcel(response,excelData,CommunalUtils.userExeclName);
}
3、对象集合转数组集合 有利于统一转化 不需要对POI进行再生成。
@Data
public class SystemUserDownload{
private Integer sysId;
private String username;
private Integer sysSex;
private Integer sysAge;
private String sysMobile;
private String sysEamil;
private String roleName;
private Date sysDate;
}
@Data
public class ExcelData {
private String fileName;
private String[] head;
private List<String[]> data;
}
**
* 获取对象的所有属性值,返回一个对象数组
* */
public static String[] getFiledValues(Object o){
String[] fieldNames=getFiledName(o);
String[] value=new String[fieldNames.length];
for(int i=0;i<fieldNames.length;i++){
value[i]=getFieldValueByName(fieldNames[i], o);
}
return value;
}
/**
* 获取属性名数组
* */
private static String[] getFiledName(Object o){
Field[] fields=o.getClass().getDeclaredFields();
String[] fieldNames=new String[fields.length];
for(int i=0;i<fields.length;i++){
fieldNames[i]=fields[i].getName();
}
return fieldNames;
}
/**
* 根据属性名获取属性值
* */
private static String getFieldValueByName(String fieldName, Object o) {
try {
String firstLetter = fieldName.substring(0, 1).toUpperCase();
String getter = "get" + firstLetter + fieldName.substring(1);
Method method = o.getClass().getMethod(getter, new Class[] {});
Object value = method.invoke(o, new Object[] {});
if (fieldName.contains("Date")){
return DateUtils.getDateString(value,"yyyy-MM-dd");
}
return String.valueOf(value);
} catch (Exception e) {
log.error(e.getMessage(),e);
return null;
}
}
4、进行execl进行文件生成下载
/**
* 方法名:exportExcel
* 功能:导出Excel
*/
public static void exportExcel(HttpServletResponse response, ExcelData data,String execlName) {
try {
HSSFWorkbook workbook = new HSSFWorkbook();//实例化HSSFWorkbook
HSSFSheet sheet = workbook.createSheet(execlName);//创建一个Excel表单,参数为sheet的名字
setTitle(workbook, sheet, data.getHead());//设置表头
setData(sheet, data.getData());//设置单元格并赋值
setBrowser(response, workbook, data.getFileName()); //设置浏览器下载
log.info("=============导出解析成功!==============");
} catch (Exception e) {
log.info("===========导出解析失败!===============");
e.printStackTrace();
}
}
/**
* 方法名:setTitle
* 功能:设置表头
*/
private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
try {
HSSFRow row = sheet.createRow(0);
//设置列宽,setColumnWidth的第二个参数要乘以256,这个参数的单位是1/256个字符宽度
for (int i = 0; i <= str.length; i++) {
sheet.setColumnWidth(i, 15 * 256);
}
HSSFCellStyle style = workbook.createCellStyle(); //设置为居中加粗,格式化时间格式
HSSFFont font = workbook.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 12);//字号
style.setFont(font);
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
HSSFCell cell;//创建表头名称
for (int j = 0; j < str.length; j++) {
cell = row.createCell(j);
cell.setCellValue(str[j]);
cell.setCellStyle(style);
}
} catch (Exception e) {
log.info("=================导出时设置表头失败!==================");
e.printStackTrace();
}
}
/**
* 方法名:setData
* 功能:表格赋值
*/
private static void setData(HSSFSheet sheet, List<String[]> data) {
try{
int rowNum = 1;
for (int i = 0; i < data.size(); i++) {
HSSFRow row = sheet.createRow(rowNum);
for (int j = 0; j < data.get(i).length; j++) {
row.createCell(j).setCellValue(data.get(i)[j]);
}
rowNum++;
}
log.info("==============表格赋值成功!================");
}catch (Exception e){
log.info("================表格赋值失败!==============");
e.printStackTrace();
}
}
/**
* 方法名:setBrowser
* 功能:使用浏览器下载
*/
private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
try {
response.reset();//清空response
//设置response的Header
response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
OutputStream os = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=utf-8");
workbook.write(os);//将excel写入到输出流中
os.flush();
os.close();
log.info("==============设置浏览器下载成功!====================");
} catch (Exception e) {
log.info("===============设置浏览器下载失败!===================");
e.printStackTrace();
}
}
5、注意事项
js中ajax请求不支持execl返回,故可生成一个from表单提交