该功能涉及到两个知识点,一是制作excel相关插件的使用,二是导出文件,spring-mvc该如何配置 先说excel插件的使用,这次使用的是org.apache.poi包,版本是3.9,下面粘完整能运行的代码 maven依赖
<dependency >
<groupId > org.apache.poi</groupId >
<artifactId > poi</artifactId >
<version > 3.9</version >
</dependency >
插件的实体类,也可以理解为参数,这里采用了build设计模式,好处是初始化了一些默认值,使用的时候代码写的方便。针对表格导出,我觉得三个参数足够了,标题、表头以及数据,所以,这三个参数构造方法我设计的必传,其它使用默认参数
package com.util;
import java.util.List;
public class ExcelParam {
String name;
int width;
String font;
String[] headers;
/**
* 导出数据的样式
* 1:String left;
* 2:String center
* 3:String right
* 4 int right
* 5:float ###,###.## right
* 6:number: #.00% 百分比 right
*/
int [] ds_format;
/**
* 每列表格的宽度,默认为256 * 14
*/
int [] widths;
List<String[]> data;
private ExcelParam () {
}
public static class Builder {
String name;
int width = 256 * 14 ;
String font = "微软雅黑" ;
String[] headers;
int [] ds_format;
int [] widths;
List<String[]> data;
public Builder (String name) {
this .name = name;
}
public Builder font (String font) {
this .font = font;
return this ;
}
public Builder width (int width) {
this .width = width;
return this ;
}
public Builder headers (String[] headers) {
this .headers = headers;
return this ;
}
public Builder ds_format (int [] ds_format) {
this .ds_format = ds_format;
return this ;
}
public Builder widths (int [] widths) {
this .widths = widths;
return this ;
}
public Builder data (List<String[]> data) {
this .data = data;
return this ;
}
public ExcelParam build () {
ExcelParam excelParam = new ExcelParam();
excelParam.name = this .name;
excelParam.data = this .data;
excelParam.widths = this .widths;
excelParam.ds_format = this .ds_format;
excelParam.headers = this .headers;
excelParam.font = this .font;
excelParam.width = this .width;
return excelParam;
}
}
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
package com .ucredit .util
import org.apache .poi .hssf .usermodel .*
import javax.servlet .http .HttpServletResponse
import java.io .IOException
import java.io .OutputStream
import java.net .URLEncoder
public class ExcelUtil {
private ExcelUtil() {
}
public static void export(ExcelParam excelParam, HttpServletResponse response) throws IOException {
if (excelParam.widths == null) {
excelParam.widths = new int[excelParam.headers .length ]
for (int i = 0
excelParam.widths [i] = excelParam.width
}
}
if (excelParam.ds _format == null) {
excelParam.ds _format = new int[excelParam.headers .length ]
for (int i = 0
excelParam.ds _format[i] = 1
}
}
//创建一个工作薄
HSSFWorkbook wb = new HSSFWorkbook()
//创建一个sheet
HSSFSheet sheet = wb.createSheet ("excel" )
int rowCount = 0
if (excelParam.headers != null) {
HSSFRow row = sheet.createRow (rowCount)
//表头样式
HSSFCellStyle style = wb.createCellStyle ()
HSSFFont font = wb.createFont ()
font.setBoldweight (HSSFFont.BOLDWEIGHT _BOLD)
font.setFontHeightInPoints ((short) 11 )
style.setFont (font)
style.setAlignment (HSSFCellStyle.ALIGN _CENTER)
for (int i = 0
sheet.setColumnWidth (i, excelParam.widths [i])
HSSFCell cell = row.createCell (i)
cell.setCellValue (excelParam.headers [i])
cell.setCellStyle (style)
}
rowCount++
}
HSSFCellStyle style = wb.createCellStyle ()
style.setAlignment (HSSFCellStyle.ALIGN _CENTER)
//表格主体 解析list
for (int i = 0
HSSFRow row = sheet.createRow (rowCount)
for (int j = 0
HSSFCell cell = row.createCell (j)
cell.setCellValue (excelParam.data .get (i)[j])
cell.setCellStyle (style)
}
rowCount++
}
//设置文件名
String fileName = excelParam.name + ".xls"
response.setContentType ("application/vnd.ms-excel" )
response.setHeader ("Content-disposition" , "attachment;filename=" + URLEncoder.encode (fileName, "UTF-8" ))
response.setHeader ("Pragma" , "No-cache" )
OutputStream outputStream = response.getOutputStream ()
wb.write (outputStream)
outputStream.flush ()
outputStream.close ()
}
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72
其实从这个方法就能看出,文件最后导出,实际上是往response中的outputStream中写入文件,之后的工作是由浏览器来完成的 controller层写法
@RequestMapping(value = "download" )
public void download(@RequestParam String username,
@RequestParam String operation_module,
@RequestParam String operation_type,
@RequestParam String start_time,
@RequestParam String end_time,
HttpServletResponse response ) throws Exception {
List<OperationLogEntity> list = operationLogDao.get (username, operation_module, operation_type, start_time, end_time);
String [] heads = {"序号" , "账号" , "姓名" , "操作类型" , "操作模块" , "处理员工号码" , "处理结果" , "登录IP" , "时间" , "所在城市" };
List<String []> data = new LinkedList<>();
for (int i = 0 ; i < list.size(); i++) {
OperationLogEntity entity = list.get (i);
String [] temp = new String [10 ];
temp[0 ] = String .valueOf(i + 1 );
temp[1 ] = entity.getUsername();
temp[2 ] = entity.getName();
temp[3 ] = entity.getOperation_type();
temp[4 ] = entity.getOperation_module();
temp[5 ] = entity.getProcess_number();
temp[6 ] = entity.getProcess_result();
temp[7 ] = entity.getIp();
temp[8 ] = DateUtils.getDateBeforeOrAfterStrCN(entity.getOperation_time(), 0 );
temp[9 ] = entity.getCity();
data.add(temp);
}
ExcelParam param = new ExcelParam.Builder("操作日志" ).headers(heads).data(data).build();
ExcelUtil.export(param, response );
}