一、文件上传
1. controller层
@RestController
@RequestMapping(value = "/excel")
public class UploadController {
@Autowired
private UploadExcelPoiService uploadExcelPoiService;
/**
* 导入excel文件
*
* @param file param
* @return rep
* @throws ApplicationException ex
*/
@PostMapping("/temporaryIncentive/import")
@ResponseBody
ResponseVO<List<List<MonthValueVO>>> importIncentive(@RequestParam("file") MultipartFile file)
throws ApplicationException {
return uploadExcelPoiService.importTemporaryIncentive(file);
}
}
2. service层
@Service
public class UploadExcelPoiService implements IUploadExcelPoiService {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelTaskService.class);
/**
* import TemporaryIncentive
*
* @param file param
* @return ResponseVO
* @throws ApplicationException ex
*/
@Override
public ResponseVO<List<List<MonthValueVO>>> importTemporaryIncentive(MultipartFile file)
throws ApplicationException {
if (file == null) {
return ResponseUtil.resultError("com.xxx.it.iprice.00010104", new ArrayList<>());
}
String fileName = file.getOriginalFilename();
if (fileName == null) {
return ResponseUtil.resultError("com.xxx.it.iprice.00010105", new ArrayList<>());
}
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
return ResponseUtil.resultError("com.xxx.it.iprice.00010105", new ArrayList<>());
}
boolean isExcel2003 = !fileName.matches("^.+\\.(?i)(xlsx)$");
List<List<MonthValueVO>> monthList = new LinkedList<>();
File outputFile = null;
try {
String outputFilePath = System.getProperty("java.io.tmpdir") + fileName;
outputFile = new File(outputFilePath);
file.transferTo(outputFile);
LOGGER.info("上传文件名{}, 路径{}", fileName, outputFile.getAbsolutePath());
InputStream is = new FileInputStream(outputFile);
Workbook wb;
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
if (sheet == null) {
return ResponseUtil.resultError("com.xxx.it.iprice.00010106", new ArrayList<>());
}
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum <= 0) {
return ResponseUtil.resultError("com.xxx.it.iprice.00010107", new ArrayList<>());
}
// 读取首行
Row row0 = sheet.getRow(0);
if (row0 == null) {
return ResponseUtil.resultError("com.xxx.it.iprice.00010107", new ArrayList<>());
}
int lastCloNum = row0.getPhysicalNumberOfCells();
List<MonthValueVO> singleList = new LinkedList<>();
int rateIndex = 3;
// 共lastCloNum-rateIndex+1个月
for (int c = rateIndex; c <= lastCloNum; c++) {
String month = row0.getCell(c).getStringCellValue();
MonthValueVO tmp = new MonthValueVO();
tmp.setMonth(month);
singleList.add(tmp);
}
for (int r = 1; r <= lastRowNum; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
List<MonthValueVO> monthValueVoList = new LinkedList<>();
for (int colIdx = rateIndex; colIdx <= lastCloNum; colIdx++) {
String monthRate = row.getCell(colIdx).getStringCellValue();
if (!BasicUtil.isValid(monthRate)) {
throw new ExcelImportException("com.xxx.it.iprice.00010108", r + 1, colIdx + 1);
}
MonthValueVO monthValueVo = new MonthValueVO();
monthValueVo.setMonth(singleList.get(colIdx - rateIndex).getMonth());
monthValueVo.setValue(monthRate.substring(0, monthRate.length() - 1));
monthValueVoList.add(monthValueVo);
}
monthList.add(monthValueVoList);
}
} catch (IOException e) {
LOGGER.error(e.getMessage(), e);
} finally {
if (outputFile != null) {
outputFile.delete();
}
}
return ResponseUtil.resultSuccess(monthList);
}
}
3. postman调试
![]()
二、文件下载
1. 服务接口
@GET
@Path("/temporaryIncentive/export")
@Consumes(MediaTypes.JSON_UTF8)
void exportTemporaryIncentive(@QueryParam("sortType") String sortType, @QueryParam("versionId") String versionId,
@Context HttpServletRequest req, @Context HttpServletResponse resp) throws ApplicationException;
2. 服务实现
private void exportIncentiveToExcel(List<VerticalPriceVO> verticalPriceList, HttpServletRequest req,
HttpServletResponse resp) {
if (verticalPriceList == null || verticalPriceList.size() <= 0) {
return;
}
XSSFWorkbook inWb = new XSSFWorkbook();
final XSSFSheet inSheet = inWb.createSheet("Temporary Incentive");
int rowIdx = 0;
int colIdx = 0;
XSSFRow row1 = inSheet.createRow(rowIdx);
final CellStyle dataLStyle = setDataCellStyle(inWb, 8, HorizontalAlignment.LEFT, true);
final CellStyle dataRStyle = setDataCellStyle(inWb, 8, HorizontalAlignment.RIGHT, true);
createCell("临时激励", dataLStyle, colIdx, row1);
colIdx += 3;
VerticalPriceVO single = verticalPriceList.get(0);
List<MonthValueVO> monthValueVoList = single.getTempIncentiveRate();
for (MonthValueVO monthValueVo : monthValueVoList) {
createCell(monthValueVo.getMonth(), dataRStyle, colIdx, row1);
colIdx++;
}
rowIdx++;
// 先将所有渠道类型国际化信息查到本地，减少服务调用
Map<String, String> categoryMap = new HashMap<>();
try {
List<LookupItemVO> channelCategoryList =
lookupItemQueryService.findItemListByClassify(LookupConstants.CHANNEL_CATEGORY);
categoryMap = channelCategoryList.stream()
.collect(Collectors.toMap(item -> item.getItemCode() + item.getLanguage(), LookupItemVO::getItemName));
} catch (ApplicationException e) {
LOGGER.error("getCountryRegionPricingList error --- ", e);
}
if (categoryMap.size() <= 0) {
LOGGER.error("------------ categoryMap.size()<=0 ----------------");
return;
}
for (VerticalPriceVO ele : verticalPriceList) {
XSSFRow inRow = inSheet.createRow(rowIdx);
List<MonthValueVO> tempIncentiveRateList = ele.getTempIncentiveRate();
String lang = CommonUtils.getLanguage();
String channelTypeName = categoryMap.get(ele.getChannelType() + lang);
String[] productArr = new String[] {ele.getOfferingName(), channelTypeName, ele.getDirectAccountName()};
int colIndex = 0;
for (; colIndex < productArr.length; colIndex++) {
createCell(productArr[colIndex], dataLStyle, colIndex, inRow);
}
for (MonthValueVO monthValueVo : tempIncentiveRateList) {
String tempIncentiveRate = monthValueVo.getValue();
String percent = BasicUtil.isBigDecimal(tempIncentiveRate);
createCell(percent, dataRStyle, colIndex, inRow);
colIndex++;
}
rowIdx++;
}
String fileName = "临时激励";
if (Constants.LANGUAGE_EN_US.equalsIgnoreCase(CommonUtils.getLanguage())) {
fileName = "temporaryIncentive";
}
writeToExcel(inWb, req, resp, fileName);
}
/**
* 写入excel
*
* @param workBook param
* @param req param
* @param resp param
* @param name param
*/
public void writeToExcel(XSSFWorkbook workBook, HttpServletRequest req, HttpServletResponse resp, String name) {
DateFormat dfNow = new SimpleDateFormat("yyyyMMddHHmmss");
String fileName = name + BasicUtil.UNDERLINE + dfNow.format(new Date()) + ".xlsx";
String checkPath = StringVerifyUtil.dealSpecialChar(fileName);
File outputFile = new File(checkPath);
FileOutputStream out = null;
try {
out = new FileOutputStream(outputFile);
workBook.write(out); // 保存Excel文件
} catch (IOException e) {
LOGGER.error(e.getMessage(), e);
} finally {
if (out != null) {
try {
// 关闭文件流
out.close();
} catch (IOException ex) {
LOGGER.error(ex.getMessage(), ex);
}
}
}
downloadExcel(req, resp, outputFile);
}
private void downloadExcel(HttpServletRequest req, HttpServletResponse resp, File file) {
InputStream in = null;
DownloadOutput download = new DownloadOutput();
try {
// 尝试下载文件
// 根据浏览器判断下载文件名转义
String agent = req.getHeader("User-Agent").toLowerCase();
File checkFile = StringVerifyUtil.getValidDirectoryPath(file);
in = new FileInputStream(checkFile);
if (agent.contains("msie") || agent.contains("trident")) {
download.outputFromStream(req, resp, in, file.getName(), true, 0L);
} else {
download.outputFromStream(req, resp, in,
new String(file.getName().getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1), false,
0L);
}
} catch (FileNotFoundException e) {
LOGGER.error("file: {} not found -- ", file.getPath(), e);
} catch (Exception e) {
LOGGER.error("{} download error -- ", file.getPath(), e);
} finally {
StreamUtil.closeStreams(in);
if (in != null) {
try {
in.close();
} catch (IOException e) {
LOGGER.error(e.getMessage(), e);
}
}
}
if (file.delete()) {
LOGGER.info("{} delete success -- ", file.getPath());
}
}
3. postman测试
![]()
浙公网安备 33010602011771号