表格示例:
java代码如下:
public ModelAndView excel(HttpServletRequest request, HttpServletResponse response) throws IOException, ParseException {//需要抛出两个异常
// 创建excel文件对象
HSSFWorkbook wb = new HSSFWorkbook();
// 创建sheet
Sheet sheet = wb.createSheet("sheet1");
//表头字体
Font headerFont = wb.createFont();
headerFont.setFontName("微软雅黑");
headerFont.setFontHeightInPoints((short) 18);
headerFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
headerFont.setColor(HSSFColor.BLACK.index);
//正文字体
Font contextFont = wb.createFont();
contextFont.setFontName("微软雅黑");
contextFont.setFontHeightInPoints((short) 12);
contextFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
contextFont.setColor(HSSFColor.BLACK.index);
//表头样式,左右上下居中
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setFont(headerFont);
headerStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
headerStyle.setLocked(true);
headerStyle.setWrapText(false);// 自动换行
//单元格样式,左右上下居中 边框
CellStyle commonStyle = wb.createCellStyle();
commonStyle.setFont(contextFont);
commonStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
commonStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
commonStyle.setLocked(true);
commonStyle.setWrapText(false);// 自动换行
commonStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
commonStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
commonStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
commonStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//单元格样式,左右上下居中 边框
CellStyle commonWrapStyle = wb.createCellStyle();
commonWrapStyle.setFont(contextFont);
commonWrapStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
commonWrapStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
commonWrapStyle.setLocked(true);
commonWrapStyle.setWrapText(true);// 自动换行
commonWrapStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
commonWrapStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
commonWrapStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
commonWrapStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//单元格样式,竖向 边框
CellStyle verticalStyle = wb.createCellStyle();
verticalStyle.setFont(contextFont);
verticalStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
verticalStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
verticalStyle.setRotation((short) 255);//竖向
verticalStyle.setLocked(true);
verticalStyle.setWrapText(false);// 自动换行
verticalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
verticalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
verticalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
verticalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//单元格样式,左右上下居中 无边框
CellStyle commonStyleNoBorder = wb.createCellStyle();
commonStyleNoBorder.setFont(contextFont);
commonStyleNoBorder.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中
commonStyleNoBorder.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
commonStyleNoBorder.setLocked(true);
commonStyleNoBorder.setWrapText(false);// 自动换行
//单元格样式,左对齐 边框
CellStyle alignLeftStyle = wb.createCellStyle();
alignLeftStyle.setFont(contextFont);
alignLeftStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐
alignLeftStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
alignLeftStyle.setLocked(true);
alignLeftStyle.setWrapText(false);// 自动换行
alignLeftStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐
alignLeftStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
alignLeftStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
alignLeftStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
alignLeftStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
//单元格样式,左对齐 无边框
CellStyle alignLeftNoBorderStyle = wb.createCellStyle();
alignLeftNoBorderStyle.setFont(contextFont);
alignLeftNoBorderStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐
alignLeftNoBorderStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
alignLeftNoBorderStyle.setLocked(true);
alignLeftNoBorderStyle.setWrapText(false);// 自动换行
alignLeftNoBorderStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐
//单元格样式,右对齐
CellStyle alignRightStyle = wb.createCellStyle();
alignRightStyle.setFont(contextFont);
alignRightStyle.setAlignment(CellStyle.ALIGN_LEFT);// 左对齐
alignRightStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
alignRightStyle.setLocked(true);
alignRightStyle.setWrapText(false);// 自动换行
alignRightStyle.setAlignment(CellStyle.ALIGN_RIGHT);// 左对齐
String fileName = null;
// 行号
int rowNum = 0;
//设置列宽
for (int i = 0; i < 7; i++) {
sheet.setColumnWidth(i, 6000);
}
//第一行
Row r0 = sheet.createRow(rowNum++);
//行高
r0.setHeight((short) 800);
//第一行第一列
Cell c00 = r0.createCell(0);
c00.setCellValue("山东省总工会工作人员考勤登记表");
c00.setCellStyle(headerStyle);
//合并单元格(从左到右依次:要合并的初始行位置、结束行位置,初始列位置、结束列位置)
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
//考勤时间行
Row rn = sheet.createRow(rowNum++);
r0.setHeight((short) 800);
Cell c01 = rn.createCell(0);
c01.setCellValue("(考勤月份"+year+"年"+yue+"月)");
c01.setCellStyle(headerStyle);
//合并单元格
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
// 第二行
Row r1 = sheet.createRow(rowNum++);
r1.setHeight((short) 500);
String date =new SimpleDateFormat("yyyy-MM").format(new Date());
String[] row_first = {"部室(盖章):"+info.getDeptName(), "", "", "", "", "填表时间:", ""};
for (int i = 0; i < row_first.length; i++) {
Cell tempCell = r1.createCell(i);
tempCell.setCellStyle(alignLeftNoBorderStyle);
if (i == 0) {
tempCell.setCellValue(row_first[i]);
} else if (i == 5) {
tempCell.setCellStyle(alignRightStyle);
tempCell.setCellValue(row_first[i]+date.replace("-", "年")+"月");
} else {
tempCell.setCellValue(row_first[i]);
}
}
// 合并
sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 3));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 6));
//第三行
Row r3 = sheet.createRow(rowNum++);
r3.setHeight((short) 700);
String[] row_third = {"姓名", "病假天数", "事假天数", "带薪休假天数", "其他休假天数(需注明何种假)", "旷工天数", "是否销假"};
for (int i = 0; i < row_third.length; i++) {
Cell tempCell = r3.createCell(i);
tempCell.setCellValue(row_third[i]);
tempCell.setCellStyle(commonWrapStyle);
}
//从map中取出 key为list的集合
List<OutRecord> list=null;
Map<String, Object> map = this.checkingtotal(info);//调用本类中的方法获得map
Iterator<String> iter = map.keySet().iterator();
while(iter.hasNext()){
String key=iter.next();
if(("list").equals(key)) {
list = (List<OutRecord>) map.get(key);
}
}
//循环每一行
for (OutRecord outrecord : list) {
Row tempRow = sheet.createRow(rowNum++);
tempRow.setHeight((short) 500);
// 循环单元格填入数据
for (int j = 0; j < 7; j++) {
Cell tempCell = tempRow.createCell(j);
tempCell.setCellStyle(commonStyle);
String tempValue = null;
if (j == 0) {
// 姓名
tempValue = outrecord.getRegistrantName();
} else if (j == 1) {
// 病假
tempValue = outrecord.getBingjia()+"";
} else if (j == 2) {
// 事假
tempValue = outrecord.getShijia()+"";
} else if (j == 3) {
// 带薪年假
tempValue = outrecord.getNianjia()+"";
} else if (j == 4) {
// 其他休假
if(outrecord.getHunjia()!=null) {
tempValue = "婚假:"+outrecord.getHunjia();
}
if(outrecord.getChanjia()!=null) {
tempValue = "产假:"+outrecord.getChanjia();
}
} else if (j == 5) {
// 旷工天数
} else {
// 是否销假
}
if(("null").equals(tempValue)) {
tempValue="";
}
tempCell.setCellValue(tempValue);
}
}
// 注释行
Row remark = sheet.createRow(rowNum++);
remark.setHeight((short) 500);
String[] row_remark = {"注:每月考勤情况应于次月5日前按时报组织部", "", "", "", "", "", ""};
for (int i = 0; i < row_remark.length; i++) {
Cell tempCell = remark.createCell(i);
tempCell.setCellStyle(alignLeftNoBorderStyle);
tempCell.setCellValue(row_remark[i]);
}
int remarkRowNum = list.size() + 4;
// 合并
sheet.addMergedRegion(new CellRangeAddress(remarkRowNum, remarkRowNum, 0, 6));
// 尾行
Row foot = sheet.createRow(rowNum++);
foot.setHeight((short) 500);
String[] row_foot = {"考勤人员:", "", "", "", "", "部室主要负责人:", ""};
for (int i = 0; i < row_foot.length; i++) {
Cell tempCell = foot.createCell(i);
tempCell.setCellStyle(alignLeftNoBorderStyle);
if (i == 0) {
tempCell.setCellValue(row_foot[i]);
} else if (i == 5) {
tempCell.setCellValue(row_foot[i] );
} else {
tempCell.setCellValue(row_foot[i]);
}
}
int footRowNum = list.size() + 5;
// 合并
sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 0, 1));
sheet.addMergedRegion(new CellRangeAddress(footRowNum, footRowNum, 5, 6));
fileName = " 工作人员考勤登记表.xls";
//输出Excel文件
OutputStream stream = response.getOutputStream();
response.reset();
// 处理文件名包含特殊字符出现的乱码问题
String userAgent = request.getHeader("User-Agent");
if (StringUtils.isNotBlank(userAgent)) {
userAgent = userAgent.toLowerCase();
if (userAgent.contains("msie") || userAgent.contains("trident") || userAgent.contains("edge")) {
if (fileName.length() > 150) {// 解决IE 6.0问题
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
} else {
fileName = URLEncoder.encode(fileName, "UTF-8");
}
} else {
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
}
}
response.setHeader("Content-disposition", "attachment;filename=\"" + fileName + "\"");
response.setContentType("application/vnd.ms-excel");
wb.write(stream);
stream.close();
}
另附本次用poi的jar包: