导出功能
Action层
/**
* 导出 生命周期 报表
*
* @return
*/
@SuppressWarnings("unchecked")
public String exportAssetsLifeReport() {
String fileName = "";
String sheetName = "";
String reportName = "";
String finalFileName = "";
String projectName = "";
String deptName="";
if ("0".equals(deptId)) {
deptName = "全部机构";
}else {
deptName = deptService.getRecord(deptId).getDeptName();
}
try {
HttpServletRequest request = ServletActionContext.getRequest();
HttpServletResponse response = ServletActionContext.getResponse();
final String userAgent = request.getHeader("USER-AGENT");
if (StringUtil.isNotNull(cid)) {
assetsLifeReport.setCid(cid);
}
PageDto page = assetsLifeReportService.getAssetsLifeReport(this.getPageInfo(), assetsLifeReport, key);
PageDto total = assetsLifeReportService.getTotal(this.getPageInfo(), assetsLifeReport, key);
List<AssetsLifeReportDto> assetsLifeDtoList = (List<AssetsLifeReportDto>)page.getRecordList();
List<AssetsLifeReport> assetsLifeList = (List<AssetsLifeReport>)total.getRecordList();
if (assetsLifeList!=null && assetsLifeList.size()!=0) {
reportName = assetsLifeDtoList.get(0).getReportName();
}else {
return ConstantUtil.ACTION_ERROR;
}
sheetName = deptName;
fileName = reportName +" - "+ sheetName +".xls";
if (StringUtils.contains(userAgent, "Edge")) {// Edge浏览器
finalFileName = URLEncoder.encode(fileName, "UTF8");
} else if (StringUtils.contains(userAgent, "Trident")) {// IE浏览器
finalFileName = URLEncoder.encode(fileName, "UTF8");
} else if (StringUtils.contains(userAgent, "Mozilla")) {// google,火狐浏览器
finalFileName = new String(fileName.getBytes(), "ISO8859-1");
} else {
finalFileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器
}
response.setContentType("application/x-execl");
response.setHeader("Content-Disposition", "attachment;filename="
+ finalFileName);
ServletOutputStream outputStream = response.getOutputStream();
assetsLifeReportService.exportAssetsLifeReport(assetsLifeDtoList,assetsLifeList, outputStream,reportName,sheetName);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
Service层
public void exportAssetsLifeReport(List<AssetsLifeReportDto>assetsLifeDtoList,List<AssetsLifeReport> assetsLifeList, ServletOutputStream outputStream, String reportName, String sheetName) {
try {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(sheetName);
/* ********************* 画对角线 start ******************* */
// 加入一条对角线
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
//合并
sheet.addMergedRegion(new CellRangeAddress(2, 2, 12, 12));
//添加验证数据
//sheet.addValidationData(new HSSFDataValidation(null, null));
HSSFClientAnchor anchor = new HSSFClientAnchor();
//试着改变第二个参数和第六个参数来改变线的位置
anchor.setAnchor((short) 0, 2, 0, 0, (short) 1, 3, 0, 0);
HSSFSimpleShape line = patriarch.createSimpleShape(anchor);
line.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
line.setLineStyle(HSSFShape.LINESTYLE_SOLID);
// 在NPOI中线的宽度12700表示1pt,所以这里是0.5pt粗的线条。
line.setLineWidth(6350);
/* ********************* 画对角线 end ******************* */
HSSFCellStyle titleStyle = workbook.createCellStyle();
HSSFCellStyle rightHeader = workbook.createCellStyle();
HSSFCellStyle header = workbook.createCellStyle();
HSSFCellStyle content = workbook.createCellStyle();
// 读取准备好的样本excel的格式
String filePath = ContextUtil.getMsgByKey("FILE_UPLOAD_PATH");
FileInputStream is = new FileInputStream(new File(filePath+"templets/report/资产生命周期.xls"));
HSSFWorkbook wb_in = new HSSFWorkbook(is);
HSSFSheet sheet_in = wb_in.getSheetAt(0);
// 标题样式
titleStyle.cloneStyleFrom(sheet_in.getRow(0).getCell(0).getCellStyle());
rightHeader.cloneStyleFrom(sheet_in.getRow(2).getCell(0).getCellStyle());
header.cloneStyleFrom(sheet_in.getRow(2).getCell(1).getCellStyle());
content.cloneStyleFrom(sheet_in.getRow(3).getCell(0).getCellStyle());
for (int i = 0; i < 10; i++) {
sheet.setColumnWidth(i, sheet_in.getColumnWidth(i));
}
sheet.setDefaultRowHeightInPoints(sheet_in.getDefaultRowHeightInPoints());
// 设置表格默认列宽度为15个字节
CellRangeAddress cellRangeAddress0 =new CellRangeAddress(0, 0, 0, 9);
CellRangeAddress cellRangeAddress1 =new CellRangeAddress(1, 1, 0, 9);
//在sheet里增加合并单元格
sheet.addMergedRegion(cellRangeAddress0);
sheet.addMergedRegion(cellRangeAddress1);
// 生成一个样式 居中
HSSFCellStyle style1 = workbook.createCellStyle();
HSSFCellStyle style2 = workbook.createCellStyle();
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体 加粗
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style1.setFont(font);
String[] titles = new String[10];
titles[0] = " 状态 类型";
titles[1] = "使用中";
titles[2] = "已出库";
titles[3] = "维护中";
titles[4] = " 返修";
titles[5] = "测试中";
titles[6] = "终止";
titles[7] = "报废";
titles[8] = "未知";
titles[9] = "总计";
//生成第一行和第二行
Row row0 = sheet.createRow(0);
Row row1 = sheet.createRow(1);
Cell first = row0.createCell(0);
Cell second = row1.createCell(0);
first.setCellValue(reportName);
first.setCellStyle(style1);
first.setCellStyle(titleStyle);
second.setCellValue(sheetName);
second.setCellStyle(style1);
second.setCellStyle(titleStyle);
// 第三行,表头
HSSFRow row2 = sheet.createRow(2);
for (int i = 0; i < titles.length; i++) {
HSSFCell cell_row2 = row2.createCell(i);
cell_row2.setCellValue(titles[i]);
cell_row2.setCellStyle(style1);
if(i == 0 ){
cell_row2.setCellStyle(rightHeader);
}else{
cell_row2.setCellStyle(header);
}
}
// 数据行
for (int i = 0; i < assetsLifeList.size(); i++) {
AssetsLifeReport report = assetsLifeList.get(i);
Row row_data = sheet.createRow(i+3);
Cell cell0 = row_data.createCell(0);// "状态类型"
cell0.setCellValue("资产总数");
cell0.setCellStyle(style1);
cell0.setCellStyle(content);
Cell cell1 = row_data.createCell(1);// "使用中
cell1.setCellValue(report.getUse());
cell1.setCellStyle(style1);
Cell cell2 = row_data.createCell(2);// "已出库
cell2.setCellValue(report.getWarehouseOut());
cell2.setCellStyle(style1);
Cell cell3 = row_data.createCell(3);// "维护中";
cell3.setCellValue(report.getMaintenance());
cell3.setCellStyle(style1);
Cell cell4 = row_data.createCell(4);// 返修"
cell4.setCellValue(report.getRepair());
cell4.setCellStyle(style1);
Cell cell5 = row_data.createCell(5);// "测试中
cell5.setCellValue(report.getTest());
cell5.setCellStyle(style1);
Cell cell6 = row_data.createCell(6);// "终止";
cell6.setCellValue(report.getTermination());
cell6.setCellStyle(style1);
Cell cell7 = row_data.createCell(7);//"报废
cell7.setCellValue(report.getDiscard());
cell7.setCellStyle(style1);
Cell cell8 = row_data.createCell(8);// "未知"
cell8.setCellValue(report.getUnknow());
cell8.setCellStyle(style1);
Cell cell9 = row_data.createCell(9);// "总计
cell9.setCellValue(report.getUse()+report.getWarehouseOut()+report.getMaintenance()+report.getRepair()+report.getTest()+
report.getTermination()+report.getDiscard()+report.getUnknow());
cell9.setCellStyle(style1);
}
for (int i = 0; i < assetsLifeDtoList.size(); i++) {
AssetsLifeReportDto report = assetsLifeDtoList.get(i);
Row row_data = sheet.createRow(i+4);
Cell cell0 = row_data.createCell(0);// "状态类型"
cell0.setCellValue(report.getTypeName());
cell0.setCellStyle(style1);
cell0.setCellStyle(header);
Cell cell1 = row_data.createCell(1);// "使用中
cell1.setCellValue(report.getUse());
cell1.setCellStyle(style1);
Cell cell2 = row_data.createCell(2);// "已出库
cell2.setCellValue(report.getWarehouseOut());
cell2.setCellStyle(style1);
Cell cell3 = row_data.createCell(3);// "维护中";
cell3.setCellValue(report.getMaintenance());
cell3.setCellStyle(style1);
Cell cell4 = row_data.createCell(4);// 返修"
cell4.setCellValue(report.getRepair());
cell4.setCellStyle(style1);
Cell cell5 = row_data.createCell(5);// "测试中
cell5.setCellValue(report.getTest());
cell5.setCellStyle(style1);
Cell cell6 = row_data.createCell(6);// "终止";
cell6.setCellValue(report.getTermination());
cell6.setCellStyle(style1);
Cell cell7 = row_data.createCell(7);//"报废
cell7.setCellValue(report.getDiscard());
cell7.setCellStyle(style1);
Cell cell8 = row_data.createCell(8);// "未知"
cell8.setCellValue(report.getUnknow());
cell8.setCellStyle(style1);
Cell cell9 = row_data.createCell(9);// "总计
cell9.setCellValue(report.getUse()+report.getWarehouseOut()+report.getMaintenance()+report.getRepair()+report.getTest()+
report.getTermination()+report.getDiscard()+report.getUnknow());
cell9.setCellStyle(style1);
}
workbook.write(outputStream);
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
下一篇:导出Excel