几乎没人教你的用poi导出如此复杂的考勤表
程序员文章站
2022-04-30 18:21:35
...
先上效果图
这种考勤表相对复杂一些，看需求情况，有需求的可继续看下去
废话不多说 上代码
1.查询考勤表数据的方法
/**
*查询考勤数据并将数据交给工具类执行
*/
public void expAttendanceExcel() {
Employee employee = (Employee) getRequest().getSession().getAttribute(MyConstants.loginEmployee);// 获取当前登录用户
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
//选择的年月
String date = getRequest().getParameter("date")==null?sdf.format(new Date()):getRequest().getParameter("date").trim();
String mrkqdeptId = employee.getDepartmentGuid();
String deptName = employee.getDeptName();
Map<String, Object> map = new HashMap<String, Object>();
map.put("deptId", mrkqdeptId);
map.put("deptName", deptName);
String[] dates = date.split("-");
String year = dates[0];
String month = dates[1];
map.put("year", Integer.parseInt(year));
map.put("month", month);
int count = dailyService.countDailyAttendance1(map);
Paging.setPagingParams(getRequest(), count, count);
map.put("pageIndex", Paging.pageIndex);
List<DailyAttendance> list = this.dailyService.findDailyAttendanceList1(map, Paging.pageIndex, Paging.pageSize);
List<Employee> empList = employeeService.findEmployeeListByDepId(mrkqdeptId);
List<String[]> excelData = new ArrayList<String[]>();
List<String[]> excelEmpData = new ArrayList<String[]>();
Calendar c = Calendar.getInstance();
c.set(Calendar.YEAR, Integer.parseInt(year));
c.set(Calendar.MONTH, Integer.parseInt(month));
int days = c.getActualMaximum(Calendar.DAY_OF_MONTH);
String d = "";
for(int i = 1;i <= days;i++) {
d += i+",";
}
if(empList == null || empList.size()==0) {
String[] temp = new String[] {};
excelEmpData.add(temp);
}else {
for(Employee e : empList) {
String[] temp = new String[] {
e.getEmployeeName()
};
excelEmpData.add(temp);
}
}
if (list==null || list.size()==0){
/*String[] temp = new String[] {};
excelData.add(temp);*/
}else{
for (DailyAttendance da : list) {
String[] temp = new String[] {
da.getDailyAttendanceTime(),
da.getSxw(),
da.getMrkqclockingNames(),
da.getMrkqabsenteeismNames(),
da.getMrkqsickNames(),
da.getMrkqtravelNames(),
da.getMrkqholidayNames(),
da.getMrkqvisitNames(),
da.getMrkqweddingNames(),
da.getMrkqbirthNames()
};
excelData.add(temp);
}
}
String title=date+deptName+"考勤表";
String[] headers = new String[] { "姓名", "日期", d,"出勤天数",date};
//生成考勤表的工具类
ExcelOperationUtil.createComplicatedExcel(getResponse(), headers, excelData,excelEmpData, title);
}2.导出考勤表的工具类执行导出考勤表
public static void createComplicatedExcel(HttpServletResponse response,String[] headers, List<String[]> excelData,List<String[]> excelEmpData, String title){
OutputStream outputStream = null;
try {
//清空response
response.reset();//需要此步骤
//设置文件内容下载方式
response.setHeader("Content-Disposition","attachment;filename="+toUtf8String(title+".xls"));//new String((title+".xls").getBytes("utf-8"), "iso-8859-1"));
//设置以excel方式
response.setContentType("application/vnd.ms-excel; charset=UTF-8");
// 创建一个 workbook，对应一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
int sheetSize = 0;
if (excelData.size()%60000>0){
sheetSize = excelData.size()/60000+1;
}else {
sheetSize = excelData.size()/60000;
}
for (int z = 0;z<sheetSize;z++){
// 在 workbook 中添加一个 sheet，对应 Excel 文件中的 sheet
String sheetName = z*60000 + "-" + (z+1)*60000;
if ((z+1)*60000 > excelData.size()){
sheetName = z*60000 + "-" + excelData.size();
}
HSSFSheet sheet = workbook.createSheet("sheet1");
//标题样式
HSSFCellStyle titleStyle = createCellStyle(workbook,(short)20,true,true);
//设置每列的宽度自适应
sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);
for(int i = 0;i < headers[2].split(",").length;i++) {
sheet.setColumnWidth(i+2, 4*256);
}
sheet.autoSizeColumn(headers[2].split(",").length+2);
sheet.autoSizeColumn(headers[2].split(",").length+3);
sheet.autoSizeColumn(headers[2].split(",").length+4);
sheet.autoSizeColumn(headers[2].split(",").length+5);
sheet.autoSizeColumn(headers[2].split(",").length+6);
sheet.autoSizeColumn(headers[2].split(",").length+7);
sheet.autoSizeColumn(headers[2].split(",").length+8);
sheet.autoSizeColumn(headers[2].split(",").length+9);
sheet.autoSizeColumn(headers[2].split(",").length+10);
//合并单元格 标题
//sheet.addMergedRegion(new Region((int)0 , (short) 0, (int)0, (short)(10+headers[2].split(",").length)));
CellRangeAddress cr = new CellRangeAddress(0, 0, 0, (int)(10+headers[2].split(",").length));
sheet.addMergedRegion(cr);
//合并单元格 姓名
//sheet.addMergedRegion(new Region((int)1 , (short) 0, (int)2, (short)0));
CellRangeAddress cr1 = new CellRangeAddress(1, 2, 0, 0);
sheet.addMergedRegion(cr1);
//合并单元格 出勤天数
//sheet.addMergedRegion(new Region((int)1 , (short)(headers[2].split(",").length+2), (int)1, (short)(headers[2].split(",").length+10)));
CellRangeAddress cr2 = new CellRangeAddress(1,1,(int)(headers[2].split(",").length+2),(int)(headers[2].split(",").length+10));
sheet.addMergedRegion(cr2);
//合并单元格 人名
int y = 0;
for(int i = 0;i<excelEmpData.size();i++) {
sheet.addMergedRegion(new Region((int)(y+3) , (short)0 , (int)(y+4), (short)0));
y = y + 2;
}
//合并单元格 出勤、出差、病事假、公休假、探亲假、婚丧假、生育假、迟到早退、旷工
int t = 0;
for(int i = 0;i<excelEmpData.size();i++) {
sheet.addMergedRegion(new Region((int)(t+3) , (short) (headers[2].split(",").length+2), (int)(t+4), (short)(headers[2].split(",").length+2)));
sheet.addMergedRegion(new Region((int)(t+3) , (short) (headers[2].split(",").length+3), (int)(t+4), (short)(headers[2].split(",").length+3)));
sheet.addMergedRegion(new Region((int)(t+3) , (short) (headers[2].split(",").length+4), (int)(t+4), (short)(headers[2].split(",").length+4)));
sheet.addMergedRegion(new Region((int)(t+3) , (short) (headers[2].split(",").length+5), (int)(t+4), (short)(headers[2].split(",").length+5)));
sheet.addMergedRegion(new Region((int)(t+3) , (short) (headers[2].split(",").length+6), (int)(t+4), (short)(headers[2].split(",").length+6)));
sheet.addMergedRegion(new Region((int)(t+3) , (short) (headers[2].split(",").length+7), (int)(t+4), (short)(headers[2].split(",").length+7)));
sheet.addMergedRegion(new Region((int)(t+3) , (short) (headers[2].split(",").length+8), (int)(t+4), (short)(headers[2].split(",").length+8)));
sheet.addMergedRegion(new Region((int)(t+3) , (short) (headers[2].split(",").length+9), (int)(t+4), (short)(headers[2].split(",").length+9)));
sheet.addMergedRegion(new Region((int)(t+3) , (short) (headers[2].split(",").length+10), (int)(t+4), (short)(headers[2].split(",").length+10)));
t = t + 2;
}
//合并单元格 备注
//sheet.addMergedRegion(new Region((int)(excelEmpData.size()*2+3) , (short)0, (int)(excelEmpData.size()*2+3), (short)(headers[2].split(",").length+10)));
CellRangeAddress crbz = new CellRangeAddress((int)(excelEmpData.size()*2+3), (int)(excelEmpData.size()*2+3), 0, (int)(headers[2].split(",").length+10));
sheet.addMergedRegion(crbz);
//第1行开始-------------------------------------------------------
/*Calendar c = Calendar.getInstance();
int month = c.get(Calendar.MONTH) + 1; //当前月份
int year = c.get(Calendar.YEAR);
c.set(Calendar.YEAR, year);
c.set(Calendar.MONTH, month-2); //上个月
int monthNew = c.get(Calendar.MONTH)+1;
int yearNew = c.get(Calendar.YEAR);*/
String[] dates = headers[4].split("-");
String year = dates[0];
String month = dates[1];
int monthNew = Integer.parseInt(month);
int yearNew = Integer.parseInt(year);
//第一行作为标题
HSSFRow row0 = sheet.createRow((int) 0);
//设置行高
row0.setHeight((short)800);
// 创建第 i 个单元格
HSSFCell createCell = row0.createCell(0);
// 给单元格赋值
createCell.setCellValue(title);
// 设置单元格格式
createCell.setCellStyle(titleStyle);
//第1行结束-------------------------------------------------------
//第2行开始-------------------------------------------------------
// 在 sheet 中添加第 2行（表头）
HSSFRow row1 = sheet.createRow((int) 1);
//表头样式
HSSFCellStyle headStyle = createCellStyle(workbook,(short)11,false,true);
// 创建第 1 个单元格 姓名
HSSFCell row1Cell0 = row1.createCell(0);
// 给单元格赋值
row1Cell0.setCellValue(headers[0]);
// 设置单元格格式
row1Cell0.setCellStyle(headStyle);
// 创建第 2 个单元格 日期
HSSFCell row1Cell1 = row1.createCell(1);
// 给单元格赋值
row1Cell1.setCellValue(headers[1]);
// 设置单元格格式
row1Cell1.setCellStyle(headStyle);
for(int i = 0;i<headers[2].split(",").length;i++) {
//具体日期
HSSFCell row1Celli = row1.createCell(i+2);
row1Celli.setCellValue(headers[2].split(",")[i]);
row1Celli.setCellStyle(headStyle);
}
//创建第33个单元格 出勤天数
HSSFCell row1Cell33 = row1.createCell(headers[2].split(",").length+2);
row1Cell33.setCellValue(headers[3]);
row1Cell33.setCellStyle(headStyle);
//第2行结束-------------------------------------------------------
//第3行开始-------------------------------------------------------
HSSFRow row2 = sheet.createRow((int) 2);
// 创建第 1 个单元格 星期
HSSFCell row2Cell1 = row2.createCell(1);
// 给单元格赋值
row2Cell1.setCellValue("星期");
// 设置单元格格式
row2Cell1.setCellStyle(headStyle);
for(int i = 0; i < headers[2].split(",").length;i++) {
String datetime = yearNew+"-"+monthNew+"-"+headers[2].split(",")[i];
String week = dateToWeek(datetime);
// 创建第 i 个单元格 星期几
HSSFCell row2Celli = row2.createCell(i+2);
// 给单元格赋值
row2Celli.setCellValue(week);
// 设置单元格格式
row2Celli.setCellStyle(headStyle);
}
//创建第33个单元格 出勤
HSSFCell row2Cell33 = row2.createCell(headers[2].split(",").length+2);
row2Cell33.setCellValue("出勤");
row2Cell33.setCellStyle(headStyle);
//创建第34个单元格 出差
HSSFCell row2Cell34 = row2.createCell(headers[2].split(",").length+3);
row2Cell34.setCellValue("出差");
row2Cell34.setCellStyle(headStyle);
//创建第35个单元格 病事假
HSSFCell row2Cell35 = row2.createCell(headers[2].split(",").length+4);
row2Cell35.setCellValue("病、事假");
row2Cell35.setCellStyle(headStyle);
//创建第36个单元格 公休假
HSSFCell row2Cell36 = row2.createCell(headers[2].split(",").length+5);
row2Cell36.setCellValue("公休假");
row2Cell36.setCellStyle(headStyle);
//创建第37个单元格 探亲假
HSSFCell row2Cell37 = row2.createCell(headers[2].split(",").length+6);
row2Cell37.setCellValue("探亲假");
row2Cell37.setCellStyle(headStyle);
//创建第38个单元格 婚丧假
HSSFCell row2Cell38 = row2.createCell(headers[2].split(",").length+7);
row2Cell38.setCellValue("婚丧假");
row2Cell38.setCellStyle(headStyle);
//创建第39个单元格 生育假
HSSFCell row2Cell39 = row2.createCell(headers[2].split(",").length+8);
row2Cell39.setCellValue("生育假");
row2Cell39.setCellStyle(headStyle);
//创建第40个单元格 迟到、早退
HSSFCell row2Cell40 = row2.createCell(headers[2].split(",").length+9);
row2Cell40.setCellValue("迟到、早退");
row2Cell40.setCellStyle(headStyle);
//创建第41个单元格 旷工
HSSFCell row2Cell41 = row2.createCell(headers[2].split(",").length+10);
row2Cell41.setCellValue("旷工");
row2Cell41.setCellStyle(headStyle);
//第3行结束-------------------------------------------------------
//数据行开始-------------------------------------------------------
int w = 0;
for(int i = 0; i < excelEmpData.size();i++) {
double chuqincount = 0; //出勤天数
double chuchaicount = 0; //出差天数
double bingshijiacount = 0; //病事假天数
double gongxiucount = 0; //公休假天数
double tanqincount = 0; //探亲假天数
double hunsangcount = 0; //婚丧假天数
double shengyucount = 0; //生育假天数
double chidaozaotuicount = 0; //迟到早退天数
double kuanggongcount = 0; //旷工假天数
HSSFRow row4 = sheet.createRow((int) w+3); //第4行
HSSFRow row5 = sheet.createRow((int) w+4); //第5行
//人名
HSSFCell row4Cell0= row4.createCell(0);
row4Cell0.setCellValue(excelEmpData.get(i)[0]);
row4Cell0.setCellStyle(headStyle);
HSSFCell row5Cell0= row5.createCell(0);
HSSFCellStyle leftBorderStyle = workbook.createCellStyle();
leftBorderStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
row5Cell0.setCellStyle(leftBorderStyle);
//上午
HSSFCell row4Cell1= row4.createCell(1);
row4Cell1.setCellValue("上午");
row4Cell1.setCellStyle(headStyle);
//下午
HSSFCell row5Cell1= row5.createCell(1);
row5Cell1.setCellValue("下午");
row5Cell1.setCellStyle(headStyle);
//第4行 1号-31号
for(int j = 0;j < headers[2].split(",").length;j++) {
HSSFCell row4Cellj= row4.createCell(j+2);
row4Cellj.setCellStyle(headStyle);
row4Cellj.setCellType(Cell.CELL_TYPE_STRING);
if(excelData != null && excelData.size()>0) {
for(int k = 0; k < excelData.size();k++) {
String[] rowData = excelData.get(k);
if(rowData[0] != null || !rowData[0].equals("")) {
String day = rowData[0].substring(rowData[0].length()-2, rowData[0].length()); //考勤日期
String DAY = headers[2].split(",")[j]; //表格里的日期
if(Integer.parseInt(day) == Integer.parseInt(DAY)) {
if(rowData[2].contains(excelEmpData.get(i)[0])) {
if(row4Cellj.getStringCellValue().equals("")) {
row4Cellj.setCellValue("△");
chidaozaotuicount = chidaozaotuicount + 0.5;
}
if(row4Cellj.getStringCellValue().equals("√")) {
row4Cellj.setCellValue("△");
chidaozaotuicount = chidaozaotuicount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[3].contains(excelEmpData.get(i)[0])) {
if(row4Cellj.getStringCellValue().equals("")) {
row4Cellj.setCellValue("×");
kuanggongcount = kuanggongcount + 0.5;
}
if(row4Cellj.getStringCellValue().equals("√")) {
row4Cellj.setCellValue("×");
kuanggongcount = kuanggongcount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[4].contains(excelEmpData.get(i)[0])) {
if(rowData[1].equals("上午") || rowData[1].equals("全天")) {
if(row4Cellj.getStringCellValue().equals("")) {
row4Cellj.setCellValue("○");
bingshijiacount = bingshijiacount + 0.5;
}
if(row4Cellj.getStringCellValue().equals("√")) {
row4Cellj.setCellValue("○");
bingshijiacount = bingshijiacount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[1].equals("下午")) {
if(row4Cellj.getStringCellValue().equals("")) {
row4Cellj.setCellValue("√");
chuqincount = chuqincount + 0.5;
}
}
}else if(rowData[5].contains(excelEmpData.get(i)[0])) {
if(row4Cellj.getStringCellValue().equals("")) {
row4Cellj.setCellValue("☆");
chuchaicount = chuchaicount + 0.5;
}
if(row4Cellj.getStringCellValue().equals("√")) {
row4Cellj.setCellValue("☆");
chuchaicount = chuchaicount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[6].contains(excelEmpData.get(i)[0])) {
if(row4Cellj.getStringCellValue().equals("")) {
row4Cellj.setCellValue("□");
gongxiucount = gongxiucount + 0.5;
}
if(row4Cellj.getStringCellValue().equals("√")) {
row4Cellj.setCellValue("□");
gongxiucount = gongxiucount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[7].contains(excelEmpData.get(i)[0])) {
if(row4Cellj.getStringCellValue().equals("")) {
row4Cellj.setCellValue("◇");
tanqincount = tanqincount + 0.5;
}
if(row4Cellj.getStringCellValue().equals("√")) {
row4Cellj.setCellValue("◇");
tanqincount = tanqincount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[8].contains(excelEmpData.get(i)[0])) {
if(row4Cellj.getStringCellValue().equals("")) {
row4Cellj.setCellValue("▲");
hunsangcount = hunsangcount + 0.5;
}
if(row4Cellj.getStringCellValue().equals("√")) {
row4Cellj.setCellValue("▲");
hunsangcount = hunsangcount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[9].contains(excelEmpData.get(i)[0])) {
if(row4Cellj.getStringCellValue().equals("")) {
row4Cellj.setCellValue("▼");
shengyucount = shengyucount + 0.5;
}
if(row4Cellj.getStringCellValue().equals("√")) {
row4Cellj.setCellValue("▼");
shengyucount = shengyucount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else {
if(row4Cellj.getStringCellValue().equals("")) {
row4Cellj.setCellValue("√");
chuqincount = chuqincount + 0.5;
}
}
}
}
}
}
}
// 第5行 1号-31号
for(int j = 0;j < headers[2].split(",").length;j++) {
HSSFCell row5Cellj= row5.createCell(j+2);
row5Cellj.setCellStyle(headStyle);
row5Cellj.setCellType(Cell.CELL_TYPE_STRING);
if(excelData != null && excelData.size()>0) {
for(int k = 0; k < excelData.size();k++) {
String[] rowData = excelData.get(k);
if(rowData[0] != null || !rowData[0].equals("")) {
String day = rowData[0].substring(rowData[0].length()-2, rowData[0].length()); //考勤日期
String DAY = headers[2].split(",")[j]; //表格里的日期
if(Integer.parseInt(day) == Integer.parseInt(DAY)) {
if(rowData[2].contains(excelEmpData.get(i)[0])) {
if(row5Cellj.getStringCellValue().equals("")) {
row5Cellj.setCellValue("△");
chidaozaotuicount = chidaozaotuicount + 0.5;
}
if(row5Cellj.getStringCellValue().equals("√")) {
row5Cellj.setCellValue("△");
chidaozaotuicount = chidaozaotuicount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[3].contains(excelEmpData.get(i)[0])) {
if(row5Cellj.getStringCellValue().equals("")) {
row5Cellj.setCellValue("×");
kuanggongcount = kuanggongcount + 0.5;
}
if(row5Cellj.getStringCellValue().equals("√")) {
row5Cellj.setCellValue("×");
kuanggongcount = kuanggongcount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[4].contains(excelEmpData.get(i)[0])) {
if(rowData[1].equals("下午") || rowData[1].equals("全天")) {
if(row5Cellj.getStringCellValue().equals("")) {
row5Cellj.setCellValue("○");
bingshijiacount = bingshijiacount + 0.5;
}
if(row5Cellj.getStringCellValue().equals("√")) {
row5Cellj.setCellValue("○");
bingshijiacount = bingshijiacount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[1].equals("上午")) {
if(row5Cellj.getStringCellValue().equals("")) {
row5Cellj.setCellValue("√");
chuqincount = chuqincount + 0.5;
}
}
}else if(rowData[5].contains(excelEmpData.get(i)[0])) {
if(row5Cellj.getStringCellValue().equals("")) {
row5Cellj.setCellValue("☆");
chuchaicount = chuchaicount + 0.5;
}
if(row5Cellj.getStringCellValue().equals("√")) {
row5Cellj.setCellValue("☆");
chuchaicount = chuchaicount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[6].contains(excelEmpData.get(i)[0])) {
if(row5Cellj.getStringCellValue().equals("")) {
row5Cellj.setCellValue("□");
gongxiucount = gongxiucount + 0.5;
}
if(row5Cellj.getStringCellValue().equals("√")) {
row5Cellj.setCellValue("□");
gongxiucount = gongxiucount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[7].contains(excelEmpData.get(i)[0])) {
if(row5Cellj.getStringCellValue().equals("")) {
row5Cellj.setCellValue("◇");
tanqincount = tanqincount + 0.5;
}
if(row5Cellj.getStringCellValue().equals("√")) {
row5Cellj.setCellValue("◇");
tanqincount = tanqincount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[8].contains(excelEmpData.get(i)[0])) {
if(row5Cellj.getStringCellValue().equals("")) {
row5Cellj.setCellValue("▲");
hunsangcount = hunsangcount + 0.5;
}
if(row5Cellj.getStringCellValue().equals("√")) {
row5Cellj.setCellValue("▲");
hunsangcount = hunsangcount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else if(rowData[9].contains(excelEmpData.get(i)[0])) {
if(row5Cellj.getStringCellValue().equals("")) {
row5Cellj.setCellValue("▼");
shengyucount = shengyucount + 0.5;
}
if(row5Cellj.getStringCellValue().equals("√")) {
row5Cellj.setCellValue("▼");
shengyucount = shengyucount + 0.5;
chuqincount = chuqincount - 0.5;
}
}else {
if(row5Cellj.getStringCellValue().equals("")) {
row5Cellj.setCellValue("√");
chuqincount = chuqincount + 0.5;
}
}
}
}
}
}
}
//出勤
HSSFCell row4Cellchuqin= row4.createCell(headers[2].split(",").length+2);
row4Cellchuqin.setCellType(Cell.CELL_TYPE_NUMERIC);
row4Cellchuqin.setCellValue(chuqincount);
row4Cellchuqin.setCellStyle(headStyle);
//出差
HSSFCell row4Cellchuchai= row4.createCell(headers[2].split(",").length+3);
row4Cellchuchai.setCellType(Cell.CELL_TYPE_NUMERIC);
row4Cellchuchai.setCellValue(chuchaicount);
row4Cellchuchai.setCellStyle(headStyle);
//病事假
HSSFCell row4Cellbingshijia= row4.createCell(headers[2].split(",").length+4);
row4Cellbingshijia.setCellType(Cell.CELL_TYPE_NUMERIC);
row4Cellbingshijia.setCellValue(bingshijiacount);
row4Cellbingshijia.setCellStyle(headStyle);
//公休假
HSSFCell row4Cellgongxiu= row4.createCell(headers[2].split(",").length+5);
row4Cellgongxiu.setCellType(Cell.CELL_TYPE_NUMERIC);
row4Cellgongxiu.setCellValue(gongxiucount);
row4Cellgongxiu.setCellStyle(headStyle);
//探亲假
HSSFCell row4Celltanqin= row4.createCell(headers[2].split(",").length+6);
row4Celltanqin.setCellType(Cell.CELL_TYPE_NUMERIC);
row4Celltanqin.setCellValue(tanqincount);
row4Celltanqin.setCellStyle(headStyle);
//婚丧假
HSSFCell row4Cellhunsang= row4.createCell(headers[2].split(",").length+7);
row4Cellhunsang.setCellType(Cell.CELL_TYPE_NUMERIC);
row4Cellhunsang.setCellValue(hunsangcount);
row4Cellhunsang.setCellStyle(headStyle);
//生育假
HSSFCell row4Cellshengyu= row4.createCell(headers[2].split(",").length+8);
row4Cellshengyu.setCellType(Cell.CELL_TYPE_NUMERIC);
row4Cellshengyu.setCellValue(shengyucount);
row4Cellshengyu.setCellStyle(headStyle);
//迟到早退
HSSFCell row4Cellchidaozaotui= row4.createCell(headers[2].split(",").length+9);
row4Cellchidaozaotui.setCellType(Cell.CELL_TYPE_NUMERIC);
row4Cellchidaozaotui.setCellValue(chidaozaotuicount);
row4Cellchidaozaotui.setCellStyle(headStyle);
//旷工
HSSFCell row4Cellkuanggong= row4.createCell(headers[2].split(",").length+10);
row4Cellkuanggong.setCellType(Cell.CELL_TYPE_NUMERIC);
row4Cellkuanggong.setCellValue(kuanggongcount);
row4Cellkuanggong.setCellStyle(headStyle);
//为没有右边框的格子设置右边框
HSSFCellStyle rightBorderStyle = workbook.createCellStyle();
rightBorderStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//出勤
HSSFCell row5Cellchuqin= row5.createCell(headers[2].split(",").length+2);
row5Cellchuqin.setCellStyle(rightBorderStyle);
//出差
HSSFCell row5Cellchuchai= row5.createCell(headers[2].split(",").length+3);
row5Cellchuchai.setCellStyle(rightBorderStyle);
//病事假
HSSFCell row5Cellbingshijia= row5.createCell(headers[2].split(",").length+4);
row5Cellbingshijia.setCellStyle(rightBorderStyle);
//公休假
HSSFCell row5Cellgongxiu= row5.createCell(headers[2].split(",").length+5);
row5Cellgongxiu.setCellStyle(rightBorderStyle);
//探亲假
HSSFCell row5Celltanqin= row5.createCell(headers[2].split(",").length+6);
row5Celltanqin.setCellStyle(rightBorderStyle);
//婚丧假
HSSFCell row5Cellhunsang= row5.createCell(headers[2].split(",").length+7);
row5Cellhunsang.setCellStyle(rightBorderStyle);
//生育假
HSSFCell row5Cellshengyu= row5.createCell(headers[2].split(",").length+8);
row5Cellshengyu.setCellStyle(rightBorderStyle);
//迟到早退
HSSFCell row5Cellchidaozaotui= row5.createCell(headers[2].split(",").length+9);
row5Cellchidaozaotui.setCellStyle(rightBorderStyle);
//旷工
HSSFCell row5Cellkuanggong= row5.createCell(headers[2].split(",").length+10);
row5Cellkuanggong.setCellStyle(rightBorderStyle);
w = w + 2;
}
//数据行结束----------------------------------------------------------
//备注行开始----------------------------------------------------------
HSSFRow rowbz = sheet.createRow((int) excelEmpData.size()*2+3); //备注行
HSSFCell rowbzCell0= rowbz.createCell(0);
rowbzCell0.setCellValue("备注：\"√\"出勤 \"△\"迟到、早退 \"×\"旷工 \"☆\"出差 \"○\"病、事假 \"□\"公休假 \"◇\"探亲假 \"▲\"婚丧假 \"▼\"生育假");
HSSFCellStyle bzStyle = createCellStyle(workbook,(short)11,false,false);
rowbzCell0.setCellStyle(bzStyle);
//备注行结束----------------------------------------------------------
//设置合并单元格边框 要放在最后 不然createRow()会覆盖已设置的边框
setBorderStyle(sheet,cr, workbook);
setBorderStyle(sheet,cr1, workbook);
setBorderStyle(sheet,cr2, workbook);
setBorderStyle(sheet,crbz, workbook);
}
// 创建输出流，并将 Excel 保存到本地
outputStream = response.getOutputStream();
// 将 workbook 写入输出流中
workbook.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
// 关闭 outputStream
if(outputStream != null) {
outputStream.flush();
outputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}这里先建议用excel画个空表格 然后照着空表格数行数和列数，这样创建行和列的时候不会乱，创建的行和列index从0开始数
后面的出勤、出差等天数统计看需求情况酌情增删就可以
打完收工！
上一篇： 输入输出 分支语句 字符串
下一篇： Python字符串输入输出简述