Excel导出页面数据到本地

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;    
	       }    
	   } 
}