EXCEL导出功能
/**
* 导出数据
*
* @param
*/
@RequestMapping(value = "exportexcle")
public void ExportExcle(String years, HttpServletResponse response) {
List<Calculus> list = calculusDao.getCalListByYear(years);
Calendar cal = Calendar.getInstance();
try {
//创建webbook
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = null;
//创建sheet工作表
if (StringUtils.isEmpty(years)) {
sheet = workbook.createSheet("太极实业综合党支部“微积分”制考核党员" + String.valueOf(cal.get(Calendar.YEAR)) + "年度积分管理表");
} else {
sheet = workbook.createSheet("太极实业综合党支部“微积分”制考核党员" + years + "年度积分管理表");
}
//设置列宽个字符
sheet.setColumnWidth(0, 5 * 256);
sheet.setColumnWidth(1, 10 * 256);
sheet.setColumnWidth(2, 15 * 256);
sheet.setColumnWidth(3, 15 * 256);
sheet.setColumnWidth(4, 15 * 256);
sheet.setColumnWidth(5, 20 * 256);
sheet.setColumnWidth(6, 15 * 256);
sheet.setColumnWidth(7, 15 * 256);
sheet.setColumnWidth(8, 15 * 256);
sheet.setColumnWidth(9, 20 * 256);
sheet.setColumnWidth(10, 15 * 256);
sheet.setColumnWidth(11, 15 * 256);
sheet.setColumnWidth(12, 15 * 256);
sheet.setColumnWidth(13, 15 * 256);
//获取单元格
//第一行
HSSFRow oneRow = sheet.createRow(0);
//第一列
HSSFCell a0 = oneRow.createCell(0);
if (StringUtils.isEmpty(years)) {
a0.setCellValue("太极实业综合党支部“微积分”制考核党员" + String.valueOf(cal.get(Calendar.YEAR)) + "年度积分管理表");
} else {
a0.setCellValue("太极实业综合党支部“微积分”制考核党员" + years + "年度积分管理表");
}
//第二行
HSSFRow twoRow = sheet.createRow(1);
//第一列
HSSFCell b0 = twoRow.createCell(0);
b0.setCellValue("（每名党员年度*测议得分×15%）");
//第三行
HSSFRow threeRow = sheet.createRow(2);
//第一列
HSSFCell c0 = threeRow.createCell(0);
c0.setCellValue("排名");
//第二列
HSSFCell c1 = threeRow.createCell(1);
c1.setCellValue("姓名");
//第三列
HSSFCell c2 = threeRow.createCell(2);
c2.setCellValue("积分项");
//第十四列
HSSFCell c3 = threeRow.createCell(13);
c3.setCellValue("最终年度得分");
//第四行
HSSFRow fourRow = sheet.createRow(3);
//第三列
HSSFCell d0 = fourRow.createCell(2);
d0.setCellValue("基础分 （70分）");
//第六列
HSSFCell d1 = fourRow.createCell(5);
d1.setCellValue("年度*测评");
//第八列
HSSFCell d2 = fourRow.createCell(7);
d2.setCellValue("创优积分 （ 上不封顶）");
//第十一列
HSSFCell d3 = fourRow.createCell(10);
d3.setCellValue("扣分项 （ 扣完为止）");
//第五行
HSSFRow fiveRow = sheet.createRow(4);
//第三列
HSSFCell e3 = fiveRow.createCell(2);
e3.setCellValue("上半年扣减");
//第四列
HSSFCell e4 = fiveRow.createCell(3);
e4.setCellValue("下半年扣减");
//第五列
HSSFCell e5 = fiveRow.createCell(4);
e5.setCellValue("年度最终得分）");
//第六列
HSSFCell e6 = fiveRow.createCell(5);
e6.setCellValue("年度*评议均分");
//第七列
HSSFCell e7 = fiveRow.createCell(6);
e7.setCellValue("年度民测积分");
//第八列
HSSFCell e8 = fiveRow.createCell(7);
e8.setCellValue("上半年得分");
//第九列
HSSFCell e9 = fiveRow.createCell(8);
e9.setCellValue("下半年得分");
//第十列
HSSFCell e10 = fiveRow.createCell(9);
e10.setCellValue("年度累计创优得分");
//第十一列
HSSFCell e11 = fiveRow.createCell(10);
e11.setCellValue("上半年扣分");
//第十二列
HSSFCell e12 = fiveRow.createCell(11);
e12.setCellValue("下半年扣分");
//第十三列
HSSFCell e13 = fiveRow.createCell(12);
e13.setCellValue("年度累计扣分");
//样式
HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle();// 创建标题样式
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直居中
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置水平居中
HSSFFont headerFont = (HSSFFont) workbook.createFont(); //创建字体样式
headerFont.setFontName("黑体"); //设置字体类型
headerFont.setFontHeightInPoints((short) 20); //设置字体大小
headerStyle.setFont(headerFont); //为标题样式设置字体样式
a0.setCellStyle(headerStyle);
HSSFCellStyle headerStyle1 = (HSSFCellStyle) workbook.createCellStyle();// 创建标题样式
headerStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直居中
headerStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置水平居中
HSSFFont headerFont1 = (HSSFFont) workbook.createFont(); //创建字体样式
headerFont1.setFontName("楷体"); //设置字体类型
headerFont1.setFontHeightInPoints((short) 20); //设置字体大小
headerStyle1.setFont(headerFont1); //为标题样式设置字体样式
b0.setCellStyle(headerStyle1);
HSSFCellStyle headerStyle2 = (HSSFCellStyle) workbook.createCellStyle();// 创建标题样式
headerStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直居中
headerStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //设置水平居中
HSSFFont headerFont2 = (HSSFFont) workbook.createFont(); //创建字体样式
headerFont2.setFontName("宋体"); //设置字体类型
headerFont2.setFontHeightInPoints((short) 11); //设置字体大小
headerStyle2.setFont(headerFont2); //为标题样式设置字体样式
HSSFCellStyle headerStyle3 = (HSSFCellStyle) workbook.createCellStyle();// 创建标题样式
headerStyle3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直居中
headerStyle3.setAlignment(HSSFCellStyle.ALIGN_LEFT); //设置水平居中
HSSFFont headerFont3 = (HSSFFont) workbook.createFont(); //创建字体样式
headerFont3.setFontName("黑体"); //设置字体类型
headerFont3.setFontHeightInPoints((short) 11); //设置字体大小
headerStyle3.setFont(headerFont3); //为标题样式设置字体样式
c0.setCellStyle(headerStyle2);
c1.setCellStyle(headerStyle2);
c2.setCellStyle(headerStyle2);
c3.setCellStyle(headerStyle2);
d0.setCellStyle(headerStyle2);
d1.setCellStyle(headerStyle2);
d2.setCellStyle(headerStyle2);
d3.setCellStyle(headerStyle2);
e3.setCellStyle(headerStyle2);
e4.setCellStyle(headerStyle2);
e5.setCellStyle(headerStyle2);
e6.setCellStyle(headerStyle2);
e7.setCellStyle(headerStyle2);
e8.setCellStyle(headerStyle2);
e9.setCellStyle(headerStyle2);
e10.setCellStyle(headerStyle2);
e11.setCellStyle(headerStyle2);
e12.setCellStyle(headerStyle2);
e13.setCellStyle(headerStyle2);
//合并
sheet.addMergedRegion(new CellRangeAddress(0, 0, (short) 0, (short) 13));//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheet.addMergedRegion(new CellRangeAddress(1, 1, (short) 0, (short) 13));//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheet.addMergedRegion(new CellRangeAddress(2, 4, (short) 0, (short) 0));//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheet.addMergedRegion(new CellRangeAddress(2, 4, (short) 1, (short) 1));//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheet.addMergedRegion(new CellRangeAddress(2, 2, (short) 2, (short) 12));//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheet.addMergedRegion(new CellRangeAddress(3, 3, (short) 2, (short) 4));//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheet.addMergedRegion(new CellRangeAddress(3, 3, (short) 5, (short) 6));//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheet.addMergedRegion(new CellRangeAddress(3, 3, (short) 7, (short) 9));//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheet.addMergedRegion(new CellRangeAddress(3, 3, (short) 10, (short) 12));//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheet.addMergedRegion(new CellRangeAddress(2, 4, (short) 13, (short) 13));//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
for (int i = 0; i < list.size(); i++) {
HSSFRow row = sheet.createRow(i + 5);
//第一列
HSSFCell oneCell = row.createCell(0);
oneCell.setCellValue(i + 1);
oneCell.setCellStyle(headerStyle2);
//第二列
HSSFCell twoCell = row.createCell(1);
twoCell.setCellValue(list.get(i).getName());
twoCell.setCellStyle(headerStyle2);
//第三列
HSSFCell threeCell = row.createCell(2);
threeCell.setCellValue(list.get(i).getJcfs());
threeCell.setCellStyle(headerStyle2);
//第四列
HSSFCell fourCell = row.createCell(3);
fourCell.setCellValue(list.get(i).getJcfx());
fourCell.setCellStyle(headerStyle2);
//第五列
HSSFCell fiveCell = row.createCell(4);
fiveCell.setCellValue(list.get(i).getNdjcf());
fiveCell.setCellStyle(headerStyle2);
//第六列
HSSFCell sixCell = row.createCell(5);
sixCell.setCellValue(list.get(i).getMzcejf());
sixCell.setCellStyle(headerStyle2);
//第七列
HSSFCell sevenCell = row.createCell(6);
sevenCell.setCellValue(list.get(i).getMzcef());
sevenCell.setCellStyle(headerStyle2);
//第八列
HSSFCell eightCell = row.createCell(7);
eightCell.setCellValue(list.get(i).getCyjfs());
eightCell.setCellStyle(headerStyle2);
//第九列
HSSFCell nineCell = row.createCell(8);
nineCell.setCellValue(list.get(i).getCyjfx());
nineCell.setCellStyle(headerStyle2);
//第十列
HSSFCell tenCell = row.createCell(9);
tenCell.setCellValue(list.get(i).getCyjf());
tenCell.setCellStyle(headerStyle2);
//第十一列
HSSFCell elevenCell = row.createCell(10);
elevenCell.setCellValue(list.get(i).getNdkfs());
elevenCell.setCellStyle(headerStyle2);
//第十二列
HSSFCell twelveCell = row.createCell(11);
twelveCell.setCellValue(list.get(i).getNdkfx());
twelveCell.setCellStyle(headerStyle2);
//第十三列
HSSFCell thirteenCell = row.createCell(12);
thirteenCell.setCellValue(list.get(i).getNdkfx());
thirteenCell.setCellStyle(headerStyle2);
//第十四列
HSSFCell fourteenCell = row.createCell(13);
fourteenCell.setCellValue(list.get(i).getNdzjf());
fourteenCell.setCellStyle(headerStyle2);
}
HSSFRow listSizeRow = sheet.createRow(list.size() + 5);
HSSFCell listSizecell = listSizeRow.createCell(0);
listSizecell.setCellValue("支委员会委员: (签名)");
sheet.addMergedRegion(new CellRangeAddress(list.size() + 5, list.size() + 6, (short) 0, (short) 13));//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
listSizecell.setCellStyle(headerStyle3);
response.reset();
//设置服务器与浏览器编码格式
response.setContentType("application/octet-stream; charset=utf-8");
//中文名字转码
if (StringUtils.isEmpty(years)) {
response.setHeader("Content-Disposition", "attachment; filename=" + EncodeUtils.encodeUrl("太极实业综合党支部“微积分”制考核党员" + String.valueOf(cal.get(Calendar.YEAR)) + "年度积分管理表.xls"));
} else {
response.setHeader("Content-Disposition", "attachment; filename=" + EncodeUtils.encodeUrl("太极实业综合党支部“微积分”制考核党员" + years + "年度积分管理表.xls"));
}
//声明输出流
OutputStream out = response.getOutputStream();
workbook.write(out);
//关闭输出流
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}