springboot使用poi技术 导出工单和周日历的方式,本人也是查阅很多网址资料才实现成功,本文仅供参考,为自己做一个记录。
第一步引入依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.4</version>
<exclusions>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.6</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
从第一个比较简单的模板方式导入(前端vue+后端springboot):
在后端的静态资源文件中templates中加入模板,模板的制作可以百度。例如下面我用到的:
注意里面 每个单元格 变量的名字,模板excel放在的位置是
之后就是上硬菜vue代码 加 springboot后端业务逻辑层代码:
vue2代码:
import {plant,downLoadFile} from '@/api/modules/productpact'
plants(workid){
plant({workId:workid}).then(res=>{
console.log(res);
downLoadFile(res);
})
}
export function plant (params) {
return request({
url: '/amtt/productpact/plant',
method: 'get',
params:params,
responseType:"blob" //设置响应数据类型为 blob
})
}
/**
* 下载文件
* @param fileStream 文件流程
* @param fileName 文件名
*/
export function downLoadFile(res){
// 处理返回的文件流
let blob = new Blob([res.data], { type: res.data.type });
//获取fileName,截取content-disposition的filename;按=分割,取最后一个
// const fileName = decodeURI(res.headers['content-disposition'].split("=")[1], "UTF-8");
let downloadElement = document.createElement("a");
let href = window.URL.createObjectURL(blob); //创建下载的链接
downloadElement.href = href;
downloadElement.download = `工程师工单.xlsx`; //下载后文件名
document.body.appendChild(downloadElement);
downloadElement.click(); //点击下载
document.body.removeChild(downloadElement); //下载完成移除元素
window.URL.revokeObjectURL(href); //释放blob
this.$message.success("已成功导出!");
}
java后端:实体类WorkOrderEntity是查询出来的数据,可以替换为自己的:查询数据并且插入:
@GetMapping("/plant")
public void plant(@RequestParam Map<String, Object> params, HttpServletResponse response){
try {
productpactService.plant(params,response);
} catch (IOException e) {
e.printStackTrace();
}
}
@Override
public void plant(Map<String, Object> params, HttpServletResponse response) throws IOException {
// WorkOrderEntity workOrderEntity = workOrderDao.selectByPid(params.get("pid"));
WorkOrderEntity workOrderEntity = workOrderDao.selectById(params.get("workId").toString());
//服务器
// String localFilePath = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest().getSession().getServletContext()
// .getRealPath("//downloadFiles");
InputStream template = new PathMatchingResourcePatternResolver()
.getResource("templates/store_template.xlsx").getInputStream();
// response.setContentType("application/octet-stream");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码
response.setHeader("Content-Disposition",
"attachment;filename=" + java.net.URLEncoder.encode("工程师.xlsx", "UTF-8"));
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(template)
.excelType(ExcelTypeEnum.XLSX).build();
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//构建excel的sheet
WriteSheet writeSheet = EasyExcel.writerSheet().build();
Map<String, String> fileData = new HashMap<>();
fileData.put("hotelNameEn", workOrderEntity.getHotelename());//酒店英文
fileData.put("hotelNameCn", workOrderEntity.getHotelname());//酒店中文
fileData.put("groupNameEn", workOrderEntity.getGroupename());//集团英文
fileData.put("groupNameCn", workOrderEntity.getGroupname());//集团中文
fileData.put("applyTime", format.format(workOrderEntity.getApplytime()));//申请时间
fileData.put("contactName", workOrderEntity.getHotelcontactname());//联系人
fileData.put("contactPhone", workOrderEntity.getHotelcontactmobile());//联系电话
fileData.put("content", workOrderEntity.getWorkcontent());//项目内容
fileData.put("travel", workOrderEntity.getIsbusiness().equals("1")?"是":"否");//是否出差
fileData.put("engineer", workOrderEntity.getEmployeename());//工程师
fileData.put("engineerPhone", workOrderEntity.getEmployeemobile());//工程师电话
fileData.put("startTime", format.format(workOrderEntity.getStarttime()));//开始时间
fileData.put("endTime", format.format(workOrderEntity.getEndtime()));//结束时间
fileData.put("hotelAddress", workOrderEntity.getHoteladdr());//酒店地址
fileData.put("travelArrange", workOrderEntity.getBusinessarrange());//差旅安排
excelWriter.fill(fileData, writeSheet);
excelWriter.finish();
}
package com.analysis.modules.amtt.entity;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import org.springframework.format.annotation.DateTimeFormat;
import java.io.Serializable;
import java.util.Date;
/**
*
*
* @author zka
* @email zka
* @date 2022-11-24 17:39:26
*/
@TableName("work_order")
public class WorkOrderEntity implements Serializable {
private static final long serialVersionUID = 1L;
/**
*
*/
@TableId
private String workid;
/**
* 合同id
*/
private String pid;
/**
* 工作内容
*/
private String workcontent;
/**
* 酒店客户ID
*/
private String hotelid;
/**
* 客户名称
*/
private String hotelname;
/**
* 客户的英文名
*/
private String hotelename;
/**
* 酒店的地址
*/
private String hoteladdr;
/**
* 集团id
*/
private String groupid;
/**
* 集团中文名
*/
private String groupname;
/**
* 集团英文名
*/
private String groupename;
/**
* 联系人姓名
*/
private String contactname;
/**
* 联系人电话
*/
private String mobilephone;
/**
* 员工的工号(序号)
*/
private String recordid;
/**
* 工程师的名字
*/
private String employeename;
/**
* 工程师的电话
*/
private String employeemobile;
/**
* 是否出差
*/
private String isbusiness;
/**
* 差旅安排
*/
private String businessarrange;
/**
* 工程的开始时间
*/
@DateTimeFormat(pattern ="yyyy-MM-dd")
@JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
private Date starttime;
/**
* 工程的结束时间
*/
@DateTimeFormat(pattern ="yyyy-MM-dd")
@JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")
private Date endtime;
/**
* 申请时间
*/
private Date applytime;
/**
* (工单创建时间)
*/
private Date addtime;
private String hotelcontactid;
private String hotelcontactname;
private String hotelcontactmobile;
private String dispatchingstatus;
private String calendarstatus;
private String colorstatus;
public String getCalendarstatus() {
return calendarstatus;
}
public void setCalendarstatus(String calendarstatus) {
this.calendarstatus = calendarstatus;
}
public String getColorstatus() {
return colorstatus;
}
public void setColorstatus(String colorstatus) {
this.colorstatus = colorstatus;
}
public String getDispatchingstatus() {
return dispatchingstatus;
}
public void setDispatchingstatus(String dispatchingstatus) {
this.dispatchingstatus = dispatchingstatus;
}
public String getHotelcontactname() {
return hotelcontactname;
}
public void setHotelcontactname(String hotelcontactname) {
this.hotelcontactname = hotelcontactname;
}
public String getHotelcontactmobile() {
return hotelcontactmobile;
}
public void setHotelcontactmobile(String hotelcontactmobile) {
this.hotelcontactmobile = hotelcontactmobile;
}
public String getHotelcontactid() {
return hotelcontactid;
}
public void setHotelcontactid(String hotelcontactid) {
this.hotelcontactid = hotelcontactid;
}
public static long getSerialVersionUID() {
return serialVersionUID;
}
public String getPid() {
return pid;
}
public void setPid(String pid) {
this.pid = pid;
}
public String getBusinessarrange() {
return businessarrange;
}
public void setBusinessarrange(String businessarrange) {
this.businessarrange = businessarrange;
}
/**
* 设置:
*/
public void setWorkid(String workid) {
this.workid = workid;
}
/**
* 获取:
*/
public String getWorkid() {
return workid;
}
/**
* 设置:工作内容
*/
public void setWorkcontent(String workcontent) {
this.workcontent = workcontent;
}
/**
* 获取:工作内容
*/
public String getWorkcontent() {
return workcontent;
}
/**
* 设置:酒店客户ID
*/
public void setHotelid(String hotelid) {
this.hotelid = hotelid;
}
/**
* 获取:酒店客户ID
*/
public String getHotelid() {
return hotelid;
}
/**
* 设置:客户名称
*/
public void setHotelname(String hotelname) {
this.hotelname = hotelname;
}
/**
* 获取:客户名称
*/
public String getHotelname() {
return hotelname;
}
/**
* 设置:客户的英文名
*/
public void setHotelename(String hotelename) {
this.hotelename = hotelename;
}
/**
* 获取:客户的英文名
*/
public String getHotelename() {
return hotelename;
}
/**
* 设置:酒店的地址
*/
public void setHoteladdr(String hoteladdr) {
this.hoteladdr = hoteladdr;
}
/**
* 获取:酒店的地址
*/
public String getHoteladdr() {
return hoteladdr;
}
/**
* 设置:集团id
*/
public void setGroupid(String groupid) {
this.groupid = groupid;
}
/**
* 获取:集团id
*/
public String getGroupid() {
return groupid;
}
/**
* 设置:集团中文名
*/
public void setGroupname(String groupname) {
this.groupname = groupname;
}
/**
* 获取:集团中文名
*/
public String getGroupname() {
return groupname;
}
/**
* 设置:集团英文名
*/
public void setGroupename(String groupename) {
this.groupename = groupename;
}
/**
* 获取:集团英文名
*/
public String getGroupename() {
return groupename;
}
/**
* 设置:联系人姓名
*/
public void setContactname(String contactname) {
this.contactname = contactname;
}
/**
* 获取:联系人姓名
*/
public String getContactname() {
return contactname;
}
/**
* 设置:联系人电话
*/
public void setMobilephone(String mobilephone) {
this.mobilephone = mobilephone;
}
/**
* 获取:联系人电话
*/
public String getMobilephone() {
return mobilephone;
}
/**
* 设置:员工的工号(序号)
*/
public void setRecordid(String recordid) {
this.recordid = recordid;
}
/**
* 获取:员工的工号(序号)
*/
public String getRecordid() {
return recordid;
}
/**
* 设置:工程师的名字
*/
public void setEmployeename(String employeename) {
this.employeename = employeename;
}
/**
* 获取:工程师的名字
*/
public String getEmployeename() {
return employeename;
}
/**
* 设置:工程师的电话
*/
public void setEmployeemobile(String employeemobile) {
this.employeemobile = employeemobile;
}
/**
* 获取:工程师的电话
*/
public String getEmployeemobile() {
return employeemobile;
}
/**
* 设置:是否出差
*/
public void setIsbusiness(String isbusiness) {
this.isbusiness = isbusiness;
}
/**
* 获取:是否出差
*/
public String getIsbusiness() {
return isbusiness;
}
/**
* 设置:工程的开始时间
*/
public void setStarttime(Date starttime) {
this.starttime = starttime;
}
/**
* 获取:工程的开始时间
*/
public Date getStarttime() {
return starttime;
}
/**
* 设置:工程的结束时间
*/
public void setEndtime(Date endtime) {
this.endtime = endtime;
}
/**
* 获取:工程的结束时间
*/
public Date getEndtime() {
return endtime;
}
/**
* 设置:申请时间
*/
public void setApplytime(Date applytime) {
this.applytime = applytime;
}
/**
* 获取:申请时间
*/
public Date getApplytime() {
return applytime;
}
/**
* 设置:(工单创建时间)
*/
public void setAddtime(Date addtime) {
this.addtime = addtime;
}
/**
* 获取:(工单创建时间)
*/
public Date getAddtime() {
return addtime;
}
}
之后 运行就可以了:第一种方法完成;
现在开始第二种方式 每个单元格的去设置 周日历。有点麻烦 每个单元格的颜色都是由 表中字段控制,而且 工单有相互冲突的,数据操作起来很麻烦。 而且 注意有一些 背景设置出来后,office和wps不兼容。就是有些office正常显示,但是wps就是不显示。最后把背景颜色去掉了 才解决。
vue前端依旧:
week(){
weekPlan({workDate:this.dataForm.workDate}).then(res=>{
//将文件流转成blob形式
const blob = new Blob([res.data],{type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});
let filename ='周日历.xls';
//创建一个超链接,将文件流赋进去,然后实现这个超链接的单击事件
const elink = document.createElement('a');
elink.download = filename;
elink.style.display = 'none';
elink.href = URL.createObjectURL(blob);
document.body.appendChild(elink);
elink.click();
URL.revokeObjectURL(elink.href); // 释放URL 对象
document.body.removeChild(elink);
})
},
export function weekPlan (params) {
return request({
url: '/amtt/workorder/weekPlan',
method: 'get',
params: params,
responseType:"blob" //设置响应数据类型为 blob
})
}
后端java 是超级硬菜: 里面要设置周一到周日的数据,以及是前端传进来日期的那一周。然后整理数据都是到类ModelExcel中,其中类每一个list,存的是【颜色状态,这天安排的工作】。
@RequestMapping("/weekPlan")
public void weekPlan(@RequestParam(value="workDate",required=false) String workDate,HttpServletResponse response){
try {
workOrderService.weekPlan(workDate,response);
}catch (IOException e){
e.printStackTrace();
}
}
硬菜要来啦:(用的是HSSFWorkBook)如果要是用XAAF的话,注意前端的导出excel后缀要变成.xlsx 导出来的后缀名是不一样的。 里面的逻辑 我没有删除。总体思路就是 从数据库中拿到数据,然后存入到ModelExcel中,然后再取出来放到 单元格里面。
@Override
public void weekPlan(String workDate,HttpServletResponse response) throws IOException {
// XSSFWorkbook workbook = new XSSFWorkbook();
HSSFWorkbook workbook = new HSSFWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
// cellStyle.setBorderBottom(BorderStyle.THIN);
// cellStyle.setBorderLeft(BorderStyle.THIN);
// cellStyle.setBorderRight(BorderStyle.THIN);
// cellStyle.setBorderTop(BorderStyle.THIN);
// cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font font = workbook.createFont();
font.setFontName("华文中宋");
font.setFontHeightInPoints((short) 36);
font.setBold(true);
cellStyle.setFont(font);
HSSFSheet sheet = workbook.createSheet("Sheet1");
// XSSFSheet sheet = workbook.createSheet("Sheet1");
sheet.setDefaultRowHeight((short)(27*20));
sheet.setDefaultColumnWidth(6);
sheet.setColumnWidth(1, 22 * 256);
sheet.setColumnWidth(9, 21 * 256);
for (int i = 2;i<=8;i++) {
sheet.setColumnWidth(i, 40 * 256);
// sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
}
HSSFRow row0 = sheet.createRow(0);
row0.setHeight((short) (56.3*20)); //默认行高27
Cell cell0 = row0.createCell(1);
cell0.setCellValue("项目工程师任务日历表");
cell0.setCellStyle(cellStyle);
// Sheet sheet1 = sheet;
//画斜线:
drawLine(sheet, 2, 1, 50, 40);
//合并单元格
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 1, 8);
CellRangeAddress cellRangeAddress1 = new CellRangeAddress(2, 3, 1, 1);
sheet.addMergedRegion(cellRangeAddress);
sheet.addMergedRegion(cellRangeAddress1);
HSSFRow row2 = sheet.createRow(2);
row2.setHeight((short) (43.4*20));
//有边框
HSSFCell cell2 = row2.createCell(0);
HSSFCellStyle cellStyle4 = workbook.createCellStyle();
cellStyle4.setBorderRight(BorderStyle.THIN);
cell2.setCellStyle(cellStyle4);
HSSFRow row1 = sheet.createRow(1);
HSSFCell cell4 = row1.createCell(1);
HSSFCellStyle cellStyle5 = workbook.createCellStyle();
cellStyle5.setBorderBottom(BorderStyle.THIN);
cell4.setCellStyle(cellStyle5);
HSSFRow row3 = sheet.createRow(3);
row3.setHeight((short) (27*20));
//右边框
HSSFCell cell3 = row3.createCell(0);
cell3.setCellStyle(cellStyle4);
//单独设置 说明 第四行和第九列
// Row row4_9 = sheet.createRow(4);
// Cell cell4_9 = row4_9.createCell(9);
// cell4_9.setCellValue("1. 绿色表示空闲,可以安排紧急工作;黄色表示外勤;白色表示休息;调休用灰色表示\n");
//单独设置 说明
Cell cell3_9 = row3.createCell(9);
cell3_9.setCellValue("说明");
HSSFFont font3_9 = workbook.createFont();
// XSSFFont font3_9 = workbook.createFont();
font3_9.setFontName("等线");
font3_9.setFontHeightInPoints((short) 11);
CellRangeAddress cellRangeAddress2 = new CellRangeAddress(4, 7, 9, 10);
sheet.addMergedRegion(cellRangeAddress2);
HSSFCellStyle cellStyle1 = workbook.createCellStyle();
// XSSFCellStyle cellStyle1 = workbook.createCellStyle();
cellStyle1.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
cellStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle1.setFont(font3_9);
cell3_9.setCellStyle(cellStyle1);
HSSFCellStyle cellStyle2 = workbook.createCellStyle();
// XSSFCellStyle cellStyle2 = workbook.createCellStyle();
cellStyle2.setWrapText(true);
// cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
// cellStyle2.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
// cellStyle2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle2.setFont(font3_9);
// cell4_9.setCellStyle(cellStyle2);
//日期
SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
List<String> data1 = getDayByWeek(workDate);
Date weekStart = null;
Date weekEnd = null;
try {
weekStart = sdf.parse(data1.get(0));
weekEnd = sdf.parse(data1.get(data1.size() - 1));
} catch (ParseException e) {
e.printStackTrace();
}
// List<String> data1 = Arrays.asList(new String[]{"2022年9月19日", "2022年9月20日", "2022年9月21日", "2022年9月22日", "2022年9月23日", "2022年9月24日", "2022年9月25日"});
List<String> data2 = Arrays.asList(new String[]{"星期一", "星期二", "星期三", "星期四", "星期五", "星期六", "星期天"});
// List<Date> data1 = Arrays.asList(new Date[]{new Date(), new Date(), new Date(), new Date(), new Date(), new Date(), new Date()});
List<ModelExcel> list = new ArrayList<>();
//查找数据:
List<DateVo> dateVos = engineerService.selectAllDate(weekStart,weekEnd);
List<EngineerEntity> engineerEntities = engineerService.selectList(new EntityWrapper<EngineerEntity>());
for (EngineerEntity engineerEntity:engineerEntities){
List<WorkOrderEntity> workOrderEntities = workOrderService.selectAllDate(engineerEntity.getId(),weekStart,weekEnd);
// TextClazz textClazz = new TextClazz(engineerEntity.getName(), "", "", "", "", "", "休假", "休假");
//存储格式为 list 长度为2,第一位存的是颜色 第二位存的是 内容。
ModelExcel modelExcel = new ModelExcel(engineerEntity.getName(),new ArrayList<String>(),new ArrayList<String>(),new ArrayList<String>(),new ArrayList<String>(),new ArrayList<String>(),new ArrayList<String>(),new ArrayList<String>());
modelExcel.getMonday().add("0"); //默认颜色为0 是空闲 绿色
modelExcel.getTuesday().add("0");
modelExcel.getWednesday().add("0");
modelExcel.getThursday().add("0");
modelExcel.getFriday().add("0");
modelExcel.getSaturday().add("2");
// modelExcel.getSaturday().add("休假"); //休假2和4 用灰色表示
modelExcel.getSunday().add("2");
// modelExcel.getSunday().add("休假");
if (workOrderEntities!=null && workOrderEntities.size()>0){
//结束时间,和 开始时间 进行比较 以及 以及具有冲突的。
for (WorkOrderEntity workOrderEntity:workOrderEntities){
//星期加一 , 以及 循环中的textClazz判断。
// if (workOrderEntity.getStarttime()<)
for (int i = 0;i<data1.size();i++){
Date parse = null;
try {
parse = sdf.parse(data1.get(i));
} catch (ParseException e) {
e.printStackTrace();
}
if (!parse.before(workOrderEntity.getStarttime()) && !parse.after(workOrderEntity.getEndtime())){
switch (i){
case 0:
ArrayList<String> str = new ArrayList<>();
str.add(workOrderEntity.getColorstatus()); //用于颜色判断 下面是判断 第二位是否存在
if (modelExcel.getMonday()!=null && modelExcel.getMonday().size()>1){
str.add(modelExcel.getMonday().get(1)+" "+workOrderEntity.getHotelname());
}else{
str.add(workOrderEntity.getHotelname());
}
modelExcel.setMonday(str);
// textClazz.setMonday(textClazz.getMonday()+" "+workOrderEntity.getHotelname());
break;
case 1:
ArrayList<String> str1 = new ArrayList<>();
str1.add(workOrderEntity.getColorstatus()); //用于颜色判断
if (modelExcel.getTuesday()!=null && modelExcel.getTuesday().size()>1){
str1.add(modelExcel.getTuesday().get(1)+" "+workOrderEntity.getHotelname());
}else{
str1.add(workOrderEntity.getHotelname());
}
modelExcel.setTuesday(str1);
// textClazz.setTuesday(textClazz.getTuesday()+" "+workOrderEntity.getHotelname());
break;
case 2:
ArrayList<String> str2 = new ArrayList<>();
str2.add(workOrderEntity.getColorstatus()); //用于颜色判断
if (modelExcel.getWednesday()!=null && modelExcel.getWednesday().size()>1){
str2.add(modelExcel.getWednesday().get(1)+" "+workOrderEntity.getHotelname());
}else{
str2.add(workOrderEntity.getHotelname());
}
modelExcel.setWednesday(str2);
// textClazz.setWednesday(textClazz.getWednesday()+" "+workOrderEntity.getHotelname());
break;
case 3:
ArrayList<String> str3 = new ArrayList<>();
str3.add(workOrderEntity.getColorstatus()); //用于颜色判断
if (modelExcel.getThursday()!=null && modelExcel.getThursday().size()>1){
str3.add(modelExcel.getThursday().get(1)+" "+workOrderEntity.getHotelname());
}else{
str3.add(workOrderEntity.getHotelname());
}
modelExcel.setThursday(str3);
// textClazz.setThursday(textClazz.getThursday()+" "+workOrderEntity.getHotelname());
break;
case 4:
ArrayList<String> str4 = new ArrayList<>();
str4.add(workOrderEntity.getColorstatus()); //用于颜色判断
if (modelExcel.getFriday()!=null && modelExcel.getFriday().size()>1){
str4.add(modelExcel.getFriday().get(1)+" "+workOrderEntity.getHotelname());
}else{
str4.add(workOrderEntity.getHotelname());
}
modelExcel.setFriday(str4);
// textClazz.setFriday(textClazz.getFriday()+" "+workOrderEntity.getHotelname());
break;
case 5:
ArrayList<String> str5 = new ArrayList<>();
str5.add(workOrderEntity.getColorstatus()); //用于颜色判断
if (modelExcel.getSaturday()!=null && modelExcel.getSaturday().size()>1){
str5.add(modelExcel.getSaturday().get(1)+" "+workOrderEntity.getHotelname());
}else{
str5.add(workOrderEntity.getHotelname());
}
modelExcel.setSaturday(str5);
// textClazz.setFriday(textClazz.getFriday()+" "+workOrderEntity.getHotelname());
break;
case 6:
ArrayList<String> str6 = new ArrayList<>();
str6.add(workOrderEntity.getColorstatus()); //用于颜色判断
if (modelExcel.getSunday()!=null && modelExcel.getSunday().size()>1){
str6.add(modelExcel.getSunday().get(1)+" "+workOrderEntity.getHotelname());
}else{
str6.add(workOrderEntity.getHotelname());
}
modelExcel.setSunday(str6);
// textClazz.setFriday(textClazz.getFriday()+" "+workOrderEntity.getHotelname());
break;
}
}
}
}
list.add(modelExcel);
}else{
list.add(modelExcel);
}
}
//单元格中的数据;
/
1 HSSFCellStyle style = workbook.createCellStyle();
// XSSFCellStyle style = workbook.createCellStyle();
// style = setCellStype(style);
// style.setFillForegroundColor(IndexedColors.AUTOMATIC.getIndex()); //黑色
// style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// style.setVerticalAlignment(VerticalAlignment.CENTER);
// style.setAlignment(HorizontalAlignment.CENTER);
//
//
1 HSSFFont font1 = setMyFont(workbook.createFont(),"等线",(short) 18,IndexedColors.WHITE.getIndex());
// XSSFFont font1 = setMyFont(workbook.createFont(),"等线",(short) 18,IndexedColors.WHITE.getIndex());
//
// style.setFont(font1);
///
for (int i = 2;i<=8;i++){
//
//1 HSSFCellStyle style = workbook.createCellStyle();
HSSFCellStyle style = workbook.createCellStyle();
style = setCellStype(style);
// style.setFillForegroundColor(IndexedColors.AUTOMATIC.getIndex()); //黑色
// style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setAlignment(HorizontalAlignment.CENTER);
HSSFFont font1 = setMyFont(workbook.createFont(),"等线",(short) 18,IndexedColors.BLACK.getIndex());
// XSSFFont font1 = setMyFont(workbook.createFont(),"等线",(short) 18,IndexedColors.BLACK.getIndex());
style.setFont(font1);
///
Cell cell = row2.createCell(i);
cell.setCellValue(data1.get(i-2));
cell.setCellStyle(style);
Cell cell1 = row3.createCell(i);
cell1.setCellValue(data2.get(i-2));
cell1.setCellStyle(style);
}
for (int i=0;i<list.size();i++){
HSSFRow row = sheet.createRow(4 + i);
// XSSFRow row = sheet.createRow(4 + i);
if(i==0){
Cell cell4_9 = row.createCell(9);
HSSFCellStyle cellStyle4_9 = setCellStype(workbook.createCellStyle());
// XSSFCellStyle cellStyle4_9 = setCellStype(workbook.createCellStyle());
cellStyle4_9.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle4_9.setAlignment(HorizontalAlignment.CENTER);
cell4_9.setCellValue("1. 绿色表示空闲,可以安排紧急工作;黄色表示外勤;白色表示休息;调休用灰色表示\n" +
"2. 重要办公室任务安排写在对应空格内,红色字体\n" +
"3. 项目安排用蓝色条状表示\n" +
"4. 在办公室学习期间,需要交学习反馈和心得,如果需要,要安排导师,导师要负责提问,反馈学习结果\n" +
"5. 外勤,项目期间原则上需要每日递交工作报告,项目经理特许情况下,可以隔天或者统一汇报");
}
row.setHeight((short) (78*20));
for (int j =1;j<=8;j++){
HSSFCell cell = row.createCell(j);
HSSFCellStyle cellStyle3 = setCellStype(workbook.createCellStyle());
// XSSFCell cell = row.createCell(j);
// XSSFCellStyle cellStyle3 = setCellStype(workbook.createCellStyle());
cellStyle3.setFont(setMyFont(workbook.createFont(),"等线",(short)11,IndexedColors.BLACK.getIndex()));
cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle3.setAlignment(HorizontalAlignment.CENTER);
HSSFFont font2 = setMyFont(workbook.createFont(),"等线",(short) 16,IndexedColors.WHITE.getIndex());
// XSSFFont font2 = setMyFont(workbook.createFont(),"等线",(short) 16,IndexedColors.WHITE.getIndex());
switch (j){
case 1:
cell.setCellValue(list.get(i).getName());
cell.setCellStyle(cellStyle3);
break;
case 2:
if (list.get(i).getMonday().size()>1){
cell.setCellValue(list.get(i).getMonday().get(1));
}else{
cell.setCellValue("空闲");
}
if (list.get(i).getMonday().get(0).equals("2") || list.get(i).getMonday().get(0).equals("4")){ //休假
cellStyle3.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
}else if (list.get(i).getMonday().get(0).equals("1")){ //派工
cellStyle3.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle3.setFont(font2);
}else if (list.get(i).getMonday().get(0).equals("3")) { //日常工作
cellStyle3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
}else{
cellStyle3.setFillForegroundColor(IndexedColors.LIME.getIndex());
}
cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// if (list.get(i).getMonday()!=null && !"".equals(list.get(i).getMonday())){
// cellStyle3.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cellStyle3.setFont(font2);
// }
cell.setCellStyle(cellStyle3);
break;
case 3:
if (list.get(i).getTuesday().size()>1) {
cell.setCellValue(list.get(i).getTuesday().get(1));
}else{
cell.setCellValue("空闲");
}
if (list.get(i).getTuesday().get(0).equals("2") || list.get(i).getTuesday().get(0).equals("4")){
cellStyle3.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
}else if (list.get(i).getTuesday().get(0).equals("1")){
cellStyle3.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle3.setFont(font2);
}else if (list.get(i).getTuesday().get(0).equals("3")) { //日常工作
cellStyle3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
}else{
cellStyle3.setFillForegroundColor(IndexedColors.LIME.getIndex());
}
cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cell.setCellValue(list.get(i).getTuesday());
// if (list.get(i).getTuesday()!=null && !"".equals(list.get(i).getTuesday())){
// cellStyle3.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cellStyle3.setFont(font2);
// }
cell.setCellStyle(cellStyle3);
break;
case 4:
if (list.get(i).getWednesday().size()>1) {
cell.setCellValue(list.get(i).getWednesday().get(1));
}else{
cell.setCellValue("空闲");
}
if (list.get(i).getWednesday().get(0).equals("2") || list.get(i).getWednesday().get(0).equals("4")){
cellStyle3.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
}else if (list.get(i).getWednesday().get(0).equals("1")){
cellStyle3.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle3.setFont(font2);
}else if (list.get(i).getWednesday().get(0).equals("3")) { //日常工作
cellStyle3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
}else{
cellStyle3.setFillForegroundColor(IndexedColors.LIME.getIndex());
}
cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cell.setCellValue(list.get(i).getWednesday());
// if (list.get(i).getWednesday()!=null && !"".equals(list.get(i).getWednesday())){
// cellStyle3.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cellStyle3.setFont(font2);
// }
cell.setCellStyle(cellStyle3);
break;
case 5:
if (list.get(i).getThursday().size()>1) {
cell.setCellValue(list.get(i).getThursday().get(1));
}else{
cell.setCellValue("空闲");
}
// cell.setCellValue(list.get(i).getThursday().get(1));
if (list.get(i).getThursday().get(0).equals("2") || list.get(i).getThursday().get(0).equals("4")){
cellStyle3.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
}else if (list.get(i).getThursday().get(0).equals("1")){
cellStyle3.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle3.setFont(font2);
}else if (list.get(i).getThursday().get(0).equals("3")) { //日常工作
cellStyle3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
}else{
cellStyle3.setFillForegroundColor(IndexedColors.LIME.getIndex());
}
cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cell.setCellValue(list.get(i).getThursday());
// if (list.get(i).getThursday()!=null && !"".equals(list.get(i).getThursday())){
// cellStyle3.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cellStyle3.setFont(font2);
// }
cell.setCellStyle(cellStyle3);
break;
case 6:
if (list.get(i).getFriday().size()>1) {
cell.setCellValue(list.get(i).getFriday().get(1));
}else{
cell.setCellValue("空闲");
}
// cell.setCellValue(list.get(i).getFriday().get(1));
if (list.get(i).getFriday().get(0).equals("2") || list.get(i).getFriday().get(0).equals("4")){
cellStyle3.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
}else if (list.get(i).getFriday().get(0).equals("1")){
cellStyle3.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle3.setFont(font2);
}else if (list.get(i).getFriday().get(0).equals("3")) { //日常工作
cellStyle3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
}else{
cellStyle3.setFillForegroundColor(IndexedColors.LIME.getIndex());
}
cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cell.setCellValue(list.get(i).getFriday());
// if (list.get(i).getFriday()!=null && !"".equals(list.get(i).getFriday())){
// cellStyle3.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cellStyle3.setFont(font2);
// }
cell.setCellStyle(cellStyle3);
break;
case 7:
if (list.get(i).getSaturday().size()>1) {
cell.setCellValue(list.get(i).getSaturday().get(1));
}else{
cell.setCellValue("休假");
}
// cell.setCellValue(list.get(i).getSaturday().get(1));
if (list.get(i).getSaturday().get(0).equals("2") || list.get(i).getSaturday().get(0).equals("4")){
cellStyle3.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
}else if (list.get(i).getSaturday().get(0).equals("1")){
cellStyle3.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle3.setFont(font2);
}else if (list.get(i).getSaturday().get(0).equals("3")) { //日常工作
cellStyle3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
}else{
cellStyle3.setFillForegroundColor(IndexedColors.LIME.getIndex());
}
cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cell.setCellValue(list.get(i).getSaturday());
// cellStyle3.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
cellStyle3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(cellStyle3);
break;
case 8:
if (list.get(i).getSunday().size()>1) {
cell.setCellValue(list.get(i).getSunday().get(1));
}else{
cell.setCellValue("休假");
}
// cell.setCellValue(list.get(i).getSunday().get(1));
if (list.get(i).getSunday().get(0).equals("2") || list.get(i).getSunday().get(0).equals("4")){
cellStyle3.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
}else if (list.get(i).getSunday().get(0).equals("1")){
cellStyle3.setFillForegroundColor(IndexedColors.BLUE.getIndex());
cellStyle3.setFont(font2);
}else if (list.get(i).getSunday().get(0).equals("3")) { //日常工作
cellStyle3.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
}else{
cellStyle3.setFillForegroundColor(IndexedColors.LIME.getIndex());
}
cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cell.setCellValue(list.get(i).getSunday());
// cellStyle3.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
// cellStyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(cellStyle3);
break;
}
}
}
// response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment;filename=balancelog.xlsx");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// 这里URLEncoder.encode可以防止中文乱码
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
package com.analysis.modules.amtt.vo;
import java.util.List;
public class ModelExcel {
private String name;
//每一个list存入的是两位,第一位是颜色的状态,第二个是周几 做的什么 也就是单元格中的文字
private List<String> Monday;
private List<String> Tuesday;
private List<String> Wednesday;
private List<String> Thursday;
private List<String> Friday;
private List<String> Saturday;
private List<String> Sunday;
public ModelExcel() {
}
public ModelExcel(String name, List<String> monday, List<String> tuesday, List<String> wednesday, List<String> thursday, List<String> friday, List<String> saturday, List<String> sunday) {
this.name = name;
Monday = monday;
Tuesday = tuesday;
Wednesday = wednesday;
Thursday = thursday;
Friday = friday;
Saturday = saturday;
Sunday = sunday;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<String> getMonday() {
return Monday;
}
public void setMonday(List<String> monday) {
Monday = monday;
}
public List<String> getTuesday() {
return Tuesday;
}
public void setTuesday(List<String> tuesday) {
Tuesday = tuesday;
}
public List<String> getWednesday() {
return Wednesday;
}
public void setWednesday(List<String> wednesday) {
Wednesday = wednesday;
}
public List<String> getThursday() {
return Thursday;
}
public void setThursday(List<String> thursday) {
Thursday = thursday;
}
public List<String> getFriday() {
return Friday;
}
public void setFriday(List<String> friday) {
Friday = friday;
}
public List<String> getSaturday() {
return Saturday;
}
public void setSaturday(List<String> saturday) {
Saturday = saturday;
}
public List<String> getSunday() {
return Sunday;
}
public void setSunday(List<String> sunday) {
Sunday = sunday;
}
}
已经没有了。能看到这 已经不容易了 ,因为即使是我写的,我也不想看。太杂太乱。就记录一下记录一下。