cpm system

<div>
 <a href="/home/export">導出</a>
</div>

地區公司部門員工姓名

package com.xincheng.cpm.common;/**
 * Created by hdwang on 2017/6/22.
 * 分頁參數 */public class PageParam {/** * 第幾次繪畫（前端標識） */private int draw;/** * 起始記錄(從0開始)，mysql也是從0開始，吻合，good！ */private int start;/** * 頁大小 */private int length;public int getDraw() {return draw;
 }public void setDraw(int draw) {this.draw = draw;
 }public int getStart() {return start;
 }public void setStart(int start) {this.start = start;
 }public int getLength() {return length;
 }public void setLength(int length) {this.length = length;
 }/** * 第幾頁(0-n) */public int getPage(){return this.start/this.length;
 }
}

package com.xincheng.cpm.common;import java.util.List;/**
 * Created by hdwang on 2017/6/22.
 * 表格數據（datatables） */public class TableData<T> {/** * 第幾次繪畫（前端標識） */private int draw;/** * 行過濾（不知道干嘛的） */private int recordsFiltered;/** * 總行數 */private int recordsTotal;/** * 行數據 */private List<T> data;/** * 起始記錄(用于前端初始化序列號用的) */private int start;/** * 錯誤信息 */private String error;public int getDraw() {return draw;
 }public void setDraw(int draw) {this.draw = draw;
 }public int getRecordsFiltered() {return recordsFiltered;
 }public void setRecordsFiltered(int recordsFiltered) {this.recordsFiltered = recordsFiltered;
 }public int getRecordsTotal() {return recordsTotal;
 }public void setRecordsTotal(int recordsTotal) {this.recordsTotal = recordsTotal;
 }public List<T> getData() {return data;
 }public void setData(List<T> data) {this.data = data;
 }public int getStart() {return start;
 }public void setStart(int start) {this.start = start;
 }public String getError() {return error;
 }public void setError(String error) {this.error = error;
 }
}

package com.xincheng.cpm.common;import java.io.Serializable;/**
 * Created by hdwang on 2017/7/14. */public class Member{private String area;private String company;private String department;private String userName;private Integer areaRowSpan;private Integer companyRowSpan;private Integer departmentRowSpan;public Member(String area,String company,String department,String userName){this.area = area;this.company = company;this.department = department;this.userName = userName;
 }public String getArea() {return area;
 }public void setArea(String area) {this.area = area;
 }public String getCompany() {return company;
 }public void setCompany(String company) {this.company = company;
 }public String getDepartment() {return department;
 }public void setDepartment(String department) {this.department = department;
 }public String getUserName() {return userName;
 }public void setUserName(String userName) {this.userName = userName;
 }public Integer getAreaRowSpan() {return areaRowSpan;
 }public void setAreaRowSpan(Integer areaRowSpan) {this.areaRowSpan = areaRowSpan;
 }public Integer getCompanyRowSpan() {return companyRowSpan;
 }public void setCompanyRowSpan(Integer companyRowSpan) {this.companyRowSpan = companyRowSpan;
 }public Integer getDepartmentRowSpan() {return departmentRowSpan;
 }public void setDepartmentRowSpan(Integer departmentRowSpan) {this.departmentRowSpan = departmentRowSpan;
 }
}

package com.xincheng.cpm.common;/**
 * Created by hdwang on 2017/7/14. */public class ExcelData {private String value;//單元格的值private int colSpan = 1;//單元格跨幾列private int rowSpan = 1;//單元格跨幾行private boolean alignCenter;//單元格是否居中，默認不居中，如果選擇是，則水平和上下都居中public boolean isAlignCenter() {return alignCenter;
 }public void setAlignCenter(boolean alignCenter) {this.alignCenter = alignCenter;
 }public String getValue() {return value;
 }public void setValue(String value) {this.value = value;
 }public int getColSpan() {return colSpan;
 }public void setColSpan(int colSpan) {this.colSpan = colSpan;
 }public int getRowSpan() {return rowSpan;
 }public void setRowSpan(int rowSpan) {this.rowSpan = rowSpan;
 }
}

package com.xincheng.cpm.common;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFFont;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.util.List;/**
 * Created by hdwang on 2017/7/14. */public class ExcelUtil {/** * 生成excel工作簿
 * @param sheetTitle sheet名稱
 * @param titles 標題
 * @param rows 行數據
 * @return 工作簿 */public XSSFWorkbook execute(String sheetTitle,String[] titles,List<List<ExcelData>> rows) {//定義工作簿XSSFWorkbook workbook = new XSSFWorkbook();//th樣式CellStyle titleStyle = workbook.createCellStyle();
 titleStyle.setBorderBottom((short) 1);
 titleStyle.setBorderRight((short)1);
 titleStyle.setBorderLeft((short)1);
 titleStyle.setBorderTop((short)1);
 titleStyle.setVerticalAlignment((short)1);
 titleStyle.setAlignment((short)2);
 XSSFFont font = workbook.createFont();
 font.setBold(true);
 titleStyle.setFont(font);//td樣式CellStyle style = workbook.createCellStyle();
 style.setBorderBottom((short)1);
 style.setBorderRight((short)1);
 style.setBorderLeft((short)1);
 style.setBorderTop((short)1);
 style.setVerticalAlignment((short)1);//創建工作表XSSFSheet sheet = workbook.createSheet(sheetTitle);
 sheet.setDefaultRowHeightInPoints(20.0F);//創建標題行XSSFRow titleRow = sheet.createRow(0);for(int col=0;col<titles.length;col++) { //遍歷列Cell cell = titleRow.createCell(col);
 cell.setCellStyle(titleStyle);
 cell.setCellValue(titles[col]);for(int row=0;row<rows.size();row++){ //遍歷行int rowIndex = row+1;
 XSSFRow contentRow = sheet.getRow(rowIndex);if(contentRow == null){
 contentRow = sheet.createRow(rowIndex);
 }
 ExcelData data = rows.get(row).get(col);
 Cell contentRowCell = contentRow.createCell(col);
 contentRowCell.setCellStyle(style);
 contentRowCell.setCellValue(data.getValue());//合并單元格if (data.getColSpan() > 1 || data.getRowSpan() > 1) {
 CellRangeAddress cra = new CellRangeAddress(rowIndex, rowIndex + data.getRowSpan() - 1, col, col + data.getColSpan() - 1);
 sheet.addMergedRegion(cra);
 }
 }
 }return workbook;
 }
}

package com.xincheng.cpm.controller;import com.chenrd.common.excel.ExportExcel;import com.xincheng.cpm.common.*;import com.xincheng.cpm.entity.cpm.User;import com.xincheng.cpm.service.UserService;import com.xincheng.cpm.vo.IncomeDailyVO;import org.apache.commons.lang3.StringUtils;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.data.domain.Page;import org.springframework.data.domain.PageImpl;import org.springframework.data.domain.PageRequest;import org.springframework.data.domain.Pageable;import org.springframework.stereotype.Controller;import org.springframework.ui.ModelMap;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.ResponseBody;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.servlet.http.HttpSession;import java.io.IOException;import java.io.OutputStream;import java.net.URLEncoder;import java.util.*;/**
 * Created by hdwang on 2017/6/19. */@Controller
@RequestMapping("/home")public class HomeController {

 @Autowired
 UserService userService;

 @RequestMapping("")public String index(HttpSession session, ModelMap map, HttpServletRequest request){
 User user = (User) session.getAttribute("user");
 map.put("user",user);return "home";
 }


 @RequestMapping(value="/query",method= RequestMethod.POST)
 @ResponseBodypublic TableData<Member> getUserByPage(PageParam pageParam, User user){
 Page<Member> userPage = this.getMembers(pageParam);
 TableData<Member> datas = new TableData<>();
 datas.setDraw(pageParam.getDraw());
 datas.setStart(pageParam.getStart());
 datas.setData(userPage.getContent());
 datas.setRecordsFiltered((int)userPage.getTotalElements());
 datas.setRecordsTotal((int)userPage.getTotalElements());return datas;
 }private Page<Member> getMembers(PageParam pageParam) {//1.模擬數據庫查詢Pageable pageable = new PageRequest(pageParam.getPage(), pageParam.getLength());long count = 6;
 List<Member> members = getMembersFromDb();//2.計算rowspanthis.countRowspan(members);


 Page<Member> memberPage = new PageImpl<Member>(members,pageable,count);return memberPage;
 }private void countRowspan(List<Member> members) {
 Map<String,Integer> propertyCountMap = this.countPropertyCount(members);
 List<String> hadGetKeys = new ArrayList<>(); //曾經取過的keyfor(Member member:members){
 String areaKey = member.getArea();
 String companyKey = areaKey+member.getCompany();
 String departmentKey = companyKey+ member.getDepartment();

 Integer areaCount = propertyCountMap.get(areaKey);if(areaCount == null){
 member.setAreaRowSpan(1);
 }else{if(hadGetKeys.contains(areaKey)){
 member.setAreaRowSpan(0); //曾經取過}else{
 member.setAreaRowSpan(areaCount); //第一次取 hadGetKeys.add(areaKey);
 }
 }

 Integer companyCount = propertyCountMap.get(companyKey);if(companyCount == null){
 member.setCompanyRowSpan(1);
 }else {if(hadGetKeys.contains(companyKey)){
 member.setCompanyRowSpan(0);
 }else{
 member.setCompanyRowSpan(companyCount);
 hadGetKeys.add(companyKey);
 }
 }

 Integer departmentCount = propertyCountMap.get(departmentKey);if(companyCount == null){
 member.setDepartmentRowSpan(1);
 }else {if(hadGetKeys.contains(departmentKey)){
 member.setDepartmentRowSpan(0);
 }else{
 member.setDepartmentRowSpan(departmentCount);
 hadGetKeys.add(departmentKey);
 }
 }
 }
 }private List<Member> getMembersFromDb() {
 Member member1 = new Member("安徽","A","人力資源部"," 小紅");
 Member member2 = new Member("安徽","B","人力資源部"," 小明");
 Member member3 = new Member("浙江","C","人力資源部"," 小君");
 Member member4 = new Member("浙江","C","技術部"," 小王");
 Member member5 = new Member("浙江","D","技術部"," 小李");
 Member member6 = new Member("浙江","D","人力資源部"," 小剛");
 List<Member> members = new ArrayList<>();
 members.add(member1);
 members.add(member2);
 members.add(member3);
 members.add(member4);
 members.add(member5);
 members.add(member6);return members;
 }/** * 統計每個字段的每組成員個數
 * @param rows 記錄
 * @return 每個字段的每組成員個數 */private Map<String,Integer> countPropertyCount(List<Member> rows){

 Map<String,Integer> propertyCountMap = new HashMap<>();for(Member member:rows){// "area": 無父級分組String area = member.getArea();if(propertyCountMap.get(area) == null){
 propertyCountMap.put(area,1);
 }else{int count = propertyCountMap.get(area);
 propertyCountMap.put(area,count+1);
 }// "company":有area父組String company = member.getCompany();
 String uniqueParent = member.getArea();
 String key = uniqueParent + company;if(propertyCountMap.get(key) == null){
 propertyCountMap.put(key,1);
 }else{int count = propertyCountMap.get(key);
 propertyCountMap.put(key,count+1);
 }// "department": 有area,company這兩個父組String department = member.getDepartment();
 uniqueParent = member.getArea()+member.getCompany();
 key = uniqueParent + department;if(propertyCountMap.get(key) == null){
 propertyCountMap.put(key,1);
 }else{int count = propertyCountMap.get(key);
 propertyCountMap.put(key,count+1);
 }
 }return propertyCountMap;
 }


 @RequestMapping("/export")public void export(HttpServletResponse response) throws IOException {
 List<Member> members = this.getMembersFromDb();this.countRowspan(members);

 List<List<ExcelData>> rows = new ArrayList<>();for(Member member:members){
 List<ExcelData> row = new ArrayList<>();
 ExcelData col1 = new ExcelData();
 col1.setValue(member.getArea());
 col1.setRowSpan(member.getAreaRowSpan());
 row.add(col1);

 ExcelData col2 = new ExcelData();
 col2.setValue(member.getCompany());
 col2.setRowSpan(member.getCompanyRowSpan());
 row.add(col2);

 ExcelData col3 = new ExcelData();
 col3.setValue(member.getDepartment());
 col3.setRowSpan(member.getDepartmentRowSpan());
 row.add(col3);

 ExcelData col4 = new ExcelData();
 col4.setValue(member.getUserName());
 row.add(col4);

 rows.add(row);
 }

 OutputStream outputStream = response.getOutputStream();try {
 String filename = URLEncoder.encode("員工" + ".xlsx", "UTF-8");
 response.setContentType("application/vnd.ms-excel");
 response.addHeader("Content-Disposition", "octet-stream;filename=" + filename);

 ExcelUtil excelUtil = new ExcelUtil();
 XSSFWorkbook workbook = excelUtil.execute("sheet1",new String[]{"地區","公司","部門","員工姓名"},rows);
 workbook.write(outputStream);
 } finally {if (outputStream != null) outputStream.close();
 }
 }




}