@RequestMapping(value = "/excel/check_project_material_list", method
= RequestMethod.GET)
@ApiOperation(value = "检测项目物料清单报表导出")
@ApiImplicitParam(value = "任务id", name = "taskId", required = true)
public void excelDownload(HttpServletRequest request, HttpServletResponse response, Integer taskId, String module) {
new ExcelUtils().downloadExcelByTaskId(taskId, module, response);
}
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;
@Component
public class ExcelUtils {
private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
private static ExcelUtils excelUtils;
private static ReportMongoDao reportMongoDao;
private static SysTaskDao sysTaskDao;
@Autowired
ReportMongoDao mongoDao;
@Autowired
SysTaskDao taskDao;
@PostConstruct
public void init() {
excelUtils = this;
reportMongoDao = mongoDao;
sysTaskDao = taskDao;
}
/**
* @return void
* @Author ChengYuYun
* @Description //TODO 通过taskid type 获取数据填充excel
* @Date 10:44 2020/12/2
* @Param [taskId, type, response, logo]
**/
public void downloadExcelByTaskId(Integer taskId, String type, HttpServletResponse response) {
SysTaskEntity taskEntity = sysTaskDao.selectTaskByTaskId(taskId);
String taskName = taskEntity.getProjectName();
Map<String, Object> map = getTemplateAndFilenameByType(taskName, type);
//SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HH:mm");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String filename = map.get("filename") + "_" + sdf.format(new Date()) + ".xlsx";
FileInputStream fileInputStream = null;
try {
InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("templates/excel/" + map.get
("template"));
downloadExcel(is, response, filename, taskId, type);
} catch (Exception ex) {
logger.error("downloadExcelByTaskid发生异常", ex);
} finally {
if (fileInputStream != null) {
try {
fileInputStream.close();
} catch (IOException e) {
logger.error("downloadExcelByTaskid发生异常", e);
}
}
}
}
/**
* @Author ChengYuYun
* @Description //TODO 下载excel
* @Date 13:44 2020/12/2
* @Param [fis, response, filename, taskId, type]
* @return void
**/
public void downloadExcel(InputStream fis, HttpServletResponse response, String filename, int taskId,
String type) {
OutputStream out = null;
try {
XSSFWorkbook workBook = new XSSFWorkbook(fis);
filename = new String((filename).getBytes(), "iso-8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
out = response.getOutputStream();
// 检测项目物料清单
if (type.equals(ExeclTemplate.CHECK_PROJECT_MATERIAL_lIST)) {
List<UseFile> useFiles = reportMongoDao.selectAllUseFiles(taskId);
insertUseFilesInfoDataToExcel(workBook, useFiles, 0, taskId);
}
workBook.write(out);
fis.close();
out.flush();
out.close();
} catch (Exception ex) {
logger.error("downloadexcel下载excel异常:", ex);
} finally {
try {
if (fis != null) {
fis.close();
}
if (out != null) {
out.flush();
out.close();
}
} catch (Exception ex) {
logger.error("downloadExcel下载excel异常:", ex);
}
}
}
/**
* @return void
* @Author ChengYuYun
* @Description //TODO 检测项目物料清单--有效文件详情
* @Date 10:11 2020/12/2
* @Param [workBook, list, sheetId]
**/
public void insertUseFilesInfoDataToExcel(XSSFWorkbook workBook, List<UseFile> list, int sheetId, int taskId) {
XSSFSheet sheet = workBook.getSheetAt(sheetId);
int rowNum = sheet.getLastRowNum();
if (list != null && list.size() > 0) {
// 填充数据
for (int i = 0; i < list.size(); i++) {
try {
UseFile useFile = list.get(i);
XSSFRow row = sheet.getRow(i + 3);
if (row == null) {
row = sheet.createRow(i + 3);
}
for (int j = 0; j < 6; j++) {
XSSFCell cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
}
}
row.getCell(0).setCellValue(i + 1);
row.getCell(1).setCellValue(useFile.getPath());
row.getCell(2).setCellValue(useFile.getSize());
row.getCell(3).setCellValue(useFile.getLine());
LicenseCopyright licenseCopyright = reportMongoDao.selectLicenseCopyrightByCheckpath(taskId, useFile.getPath());
String license = licenseCopyright.getLicense();
String copyRight = licenseCopyright.getCopyright();
row.getCell(4).setCellValue(license);
row.getCell(5).setCellValue(copyRight);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* @return java.util.Map<java.lang.String , java.lang.Object>
* @Author ChengYuYun
* @Description //TODO 通过taskName 和type获取模板名称和导出文件名
* @Date 10:47 2020/12/2
* @Param [taskName, type]
**/
public static Map<String, Object> getTemplateAndFilenameByType(String taskName, String type) {
Map<String, Object> map = new HashMap<>();
String template = "";
String filename = "";
switch (type) {
case ExeclTemplate.CHECK_PROJECT_MATERIAL_lIST:
template = ExeclTemplate.CHECK_PROJECT_MATERIAL_lIST;
filename = taskName + "_检测项目物料清单";
break;
/*case ExeclTemplate.CLONEDETAIL_TYPE:
template = !logo ? ExeclTemplate.CLONEDETAIL_LOGO_XLSX : ExeclTemplate.CLONEDETAIL_XLSX;
filename = taskName + "_开源代码匹配清单";
break;
……
*/
}
map.put("template", template);
map.put("filename", filename);
return map;
}
}