1.将页面上的数据导出,以excel形式保存到本地
2、pom需要引入的包
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>3.2.9.RELEASE</version>
</dependency>
3.Controller代码
public void exportExcal(OaLeaveApply oaLeaveApply, HttpServletResponse response,HttpServletRequest request) {
OutputStream out = null;
response.setContentType("application/x-xls;charset=utf-8");
//文件名 passenger+后缀
response.setHeader("Content-disposition","inline; filename = passenger" + ".xls");
try {
//这里是要导出的list数据集
List<Map<String, Object>> dataList =getDataList(oaLeaveApply);
//得到输出流
out = response.getOutputStream();
// 生成excel文件
HSSFWorkbook wb = ExcelUtil.createWorkBook(dataList, getHeaderKeys(), getHeaders());
wb.write(out);
} catch (Exception e1) {
e1.printStackTrace();
} finally {
try {
if (out != null) {
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
3.Service 拼接数据表头和与之对应的字段名
private List<Map<String, Object>> getDataList(OaLeaveApply oaLeaveApply) {
List<Map<String, Object>> dataList = new ArrayList<Map<String,Object>>();
List<OaLeaveApply> leaveApplies=oaLeaveApplyService.findList(oaLeaveApply);
//2.根据时间获取某个时间段期间请假天数和
if(oaLeaveApply.getBeginApplyTime()==null){
oaLeaveApply.setBeginApplyTime(DateUtils.parseDate("2020-01-01"));
oaLeaveApply.setEndApplyTime(DateUtils.parseDate("2020-12-31"));
}
oaLeaveApply.setState("审核通过");
List<OaLeaveApplyStatistics> list2= oaLeaveApplyService.getStatisticsListByTime(oaLeaveApply);
String type="";
for (OaLeaveApply oaLeaveApply2 : leaveApplies) {
if("1".equals(oaLeaveApply2.getLeaveType())){
type="公休";
}else if("2".equals(oaLeaveApply2.getLeaveType())){
type="病假";
}else if("3".equals(oaLeaveApply2.getLeaveType())){
type="事假";
}else if("4".equals(oaLeaveApply2.getLeaveType())){
type="调休";
}else if("5".equals(oaLeaveApply2.getLeaveType())){
type="婚假";
}
Map<String,Object> data = new HashMap<String, Object>();
data.put(getHeaderKeys()[0], oaLeaveApply2.getUser().getName());
data.put(getHeaderKeys()[1] ,oaLeaveApply2.getOffice());
data.put(getHeaderKeys()[2], oaLeaveApply2.getState());
data.put(getHeaderKeys()[3], type);
data.put(getHeaderKeys()[4], oaLeaveApply2.getReason());
data.put(getHeaderKeys()[5], oaLeaveApply2.getCnum());
dataList.add(data);
}
return dataList;
}
private String[] getHeaderKeys() {
String[] headerKeys = new String[] {"name","office","state","leaveType","reason","cnum"};
return headerKeys;
}
private String[] getHeaders() {
String[] headers = new String[] {"请假人","所属科室","当前状态","请假类型","请假事由","请假天数"};
return headers;
}
4.ExcelUtil处理工具类
package utils.excel;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
public class ExcelUtil {
/**
* createWorkBook:创建excel工作表.
*
* @author ZhangTao
* @param dataList
* 数据Map集合
* @param dataKeys
* 数据Map Key
* @param columnNames
* 表头
* @return
*/
public static HSSFWorkbook createWorkBook(
List<Map<String, Object>> dataList, String[] headersKeys,
String[] headers) {
// 定义exclen
int rowNumber = 0;
// 创建excel工作簿
HSSFWorkbook wb = new HSSFWorkbook();
// 创建第一个sheet(页),并命名
Sheet sheet = wb.createSheet();
// 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。
for (int i = 0; i < headers.length; i++) {
sheet.setColumnWidth((short) i, (short) (35 * 150));
}
// 创建第一行
Row row = sheet.createRow((short) 0);
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
// 创建两种字体
Font f = wb.createFont();
Font f2 = wb.createFont();
// 创建第一种字体样式(用于列名)
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
//f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 创建第二种字体样式(用于值)
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// 设置第一种单元格的样式(用于列名)
cs.setFont(f);
//cs.setBorderLeft(CellStyle.BORDER_THIN);
//cs.setBorderRight(CellStyle.BORDER_THIN);
//cs.setBorderTop(CellStyle.BORDER_THIN);
//cs.setBorderBottom(CellStyle.BORDER_THIN);
//cs.setAlignment(CellStyle.ALIGN_CENTER);
// 设置第二种单元格的样式(用于值)
cs2.setFont(f2);
//cs2.setBorderLeft(CellStyle.BORDER_THIN);
//cs2.setBorderRight(CellStyle.BORDER_THIN);
//cs2.setBorderTop(CellStyle.BORDER_THIN);
//cs2.setBorderBottom(CellStyle.BORDER_THIN);
//cs2.setAlignment(CellStyle.ALIGN_CENTER);
// 设置列名
for (int i = 0; i < headers.length; i++) {
Cell cell = row.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(cs);
}
short cellNumber = 1;
// 设置每行每列的值
for (short i = 0; i < dataList.size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行,在页sheet上
Row row1 = sheet.createRow((short) i + 1);
// 在row行上创建一个方格
for (short j = 0; j < headersKeys.length; j++) {
Cell cell = row1.createCell(j);
cell.setCellValue(dataList.get(i).get(headersKeys[j]) == null ? " "
: dataList.get(i).get(headersKeys[j]).toString());
cell.setCellStyle(cs2);
}
}
return wb;
}
/**
* 添加合计
* @param wb
* @param statisticsMap
* @param headersKeys
* @return
*/
public static HSSFWorkbook statisticsRow(HSSFWorkbook wb,
Map<String, Object> statisticsMap, String[] headersKeys) {
// 创建第一个sheet(页),并命名
Sheet sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum();
rowNum = rowNum + 1;
// 创建第一行
Row row = sheet.createRow((short) rowNum);
// 创建两种单元格格式
CellStyle cs2 = wb.createCellStyle();
// 创建两种字体
Font f2 = wb.createFont();
// 创建第二种字体样式(用于值)
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// 设置第二种单元格的样式(用于值)
cs2.setFont(f2);
//cs2.setBorderLeft(CellStyle.BORDER_THIN);
//cs2.setBorderRight(CellStyle.BORDER_THIN);
//cs2.setBorderTop(CellStyle.BORDER_THIN);
//cs2.setBorderBottom(CellStyle.BORDER_THIN);
//cs2.setAlignment(CellStyle.ALIGN_CENTER);
// 第一列针对订单数量进行统计
Cell cell_0 = row.createCell(0);
cell_0.setCellValue("合计");
cell_0.setCellStyle(cs2);
for (short j = 1; j < headersKeys.length; j++) {
Cell cell = row.createCell(j);
if (headersKeys[j].equals("orderAmount")) {
cell.setCellValue(statisticsMap.get("orderAmount").toString());
} else if (headersKeys[j].equals("orderNo")) {
cell.setCellValue(statisticsMap.get("orderNo").toString() + "张");
} else {
cell.setCellValue("");
}
cell.setCellStyle(cs2);
}
return wb;
}
/**
* 封装表格所需数据
* @param list
* @return
*/
public static <T> List<Map<String, Object>> packageExcelDataList(List<T> list){
List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
for(int i=0;i<list.size();i++){
Field[] fields=list.get(i).getClass().getDeclaredFields();
Map<String, Object> rowMap = new HashMap<String, Object>();
for(int j=0;j<fields.length-1;j++){
rowMap.put(fields[j].getName(),getFieldValueByName(fields[j].getName(),fields[j].getType().toString(),list.get(i)));
}
dataList.add(rowMap);
}
return dataList;
}
public static Object getFieldValueByName(String fieldName,String fieldType, 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(fieldType.equals("class java.lang.Integer")){
return ((Integer) value);
}else if(fieldType.equals("class java.util.Date")){
SimpleDateFormat s=new SimpleDateFormat("yyyy-MM-dd");
return (s.format((Date)value));
}else if(fieldType.equals("class java.sql.Date")){
SimpleDateFormat s=new SimpleDateFormat("yyyy/M/d");
return (s.format((Date)value));
}else if(fieldType.equals("class java.lang.Double")){
return ((Double) value);
}else if(fieldType.equals("double")){
return value;
}else{
return ((String)value);
}
} catch (Exception e) {
return null;
}
}
}