最近在弄一个项目时,服药上传Excel并且解析Excel中的数据,由于是一个新的项目,就重新写了一个方法,这个Excel很简单,就四列,使用的是POI解析的。代码如下:
@RequestMapping(value = "/importServiceStandardExcel", method = RequestMethod.GET)
public void importServiceStandardExcel(@RequestParam String attId,@RequestParam String projectId) {
try {
Attachment attachment = attachmentService.getById(attId);
contractService.getServiceStandardByContractId(attachment.getId());
String path = attachmentService.generateAttachmentPath(attachment);//获取excel存放的路径
XSSFWorkbook workbook = new XSSFWorkbook(
new FileInputStream(path));
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row;
XSSFCell cell1;
int rows = sheet.getLastRowNum();
for (int icount = 1; icount <= rows; icount++) {
SupplierEvaluateCriteria supplierEvaluateCriteria = new SupplierEvaluateCriteria();
row = sheet.getRow(icount);
supplierEvaluateCriteria.setId(IdUtils.getUUID());
supplierEvaluateCriteria.setContractId(attachment.getId());
supplierEvaluateCriteria.setProjectId(projectId);
supplierEvaluateCriteria.setIndexNumber(row.getCell(0).toString());
supplierEvaluateCriteria.setExamingItem(row.getCell(1).toString());
supplierEvaluateCriteria.setScore(row.getCell(2).toString());
supplierEvaluateCriteria.setEvaluateCriteria(row.getCell(3).toString());
contractService.saveSupplierEvaluateCriteria(supplierEvaluateCriteria);
}
} catch (FileNotFoundException e) {
logger.error(e);
} catch (IOException e) {
logger.error(e);
}
}
Excel很简单,代码也很简单,其中关键的一点是获取excel存放的位置,转化为流文件,后面的解析POI提供了很多方法,很容易弄懂,就不多赘述。重新写的时候遇到一个问题,希望大家注意HSSFWorkbook和XSSFWorkbook的区别,前者只能解析.xls为后缀的excel文件(2007之前的版本),后者支持.xlsx为后缀的excel文件,大家注意一下就行,这个主要是解决office版本兼容性问题,谢谢!