package pwc.taxtech.atms.service.impl; import com.google.common.collect.Lists; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.*; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.web.multipart.MultipartFile; import pwc.taxtech.atms.common.message.ErrorMessage; import pwc.taxtech.atms.exception.ServiceException; import pwc.taxtech.atms.vat.dao.PeriodCellDataMapper; import pwc.taxtech.atms.vat.dao.PeriodCellTemplateMapper; import pwc.taxtech.atms.vat.dao.PeriodReportMapper; import pwc.taxtech.atms.vat.entity.*; import java.io.InputStream; import java.util.Date; import java.util.List; @Service public class ExcelDataServiceImpl extends AbstractService { @Autowired private PeriodCellTemplateMapper periodCellTemplateMapper; @Autowired private PeriodCellDataMapper periodCellDataMapper; @Autowired private PeriodReportMapper periodReportMapper; public void parseExcelData(MultipartFile file, Long periodTemplateId, String projectId) throws ServiceException{ if (null == file) { throw new ServiceException(ErrorMessage.NoFile); } try{ InputStream inputStream = file.getInputStream(); Workbook workbook = WorkbookFactory.create(inputStream); Sheet sheet = workbook.getSheetAt(0); Date now = new Date(); // insert a record into PERIOD_REPORT PeriodReport pr = new PeriodReport(); pr.setProjectId(projectId); pr.setCreateBy("Admin"); pr.setCreateTime(now); pr.setPeriod(0); pr.setTemplateId(periodTemplateId); pr.setUpdateBy("Admin"); pr.setUpdateTime(now); pr.setId(distributedIdService.nextId()); periodReportMapper.insertSelective(pr); // get cell template according to templateId PeriodCellTemplateExample pctExample = new PeriodCellTemplateExample(); pctExample.createCriteria().andReportTemplateIdEqualTo(periodTemplateId); List<PeriodCellTemplate> templates = periodCellTemplateMapper.selectByExample(pctExample); // save cell data into PERIOD_CELL_DATA List<PeriodCellData> list = Lists.newArrayList(); for(PeriodCellTemplate template : templates){ Row r = sheet.getRow(template.getRowIndex()); Cell c = r.getCell(template.getColumnIndex()); if(StringUtils.isBlank(c.getStringCellValue())){ continue; } PeriodCellData periodCellData = new PeriodCellData(); periodCellData.setData(c.getStringCellValue()); periodCellData.setId(distributedIdService.nextId()); periodCellData.setCellTemplateId(template.getId()); periodCellData.setUpdateTime(now); periodCellData.setCreateBy("Admin"); periodCellData.setCreateTime(now); periodCellData.setPeriod(1); periodCellData.setUpdateBy("Admin"); periodCellData.setFormulaExp(periodCellData.getData()); periodCellData.setProjectId(projectId); periodCellData.setReportId(pr.getId()); list.add(periodCellData); } periodCellDataMapper.batchInsert(list); // periodCellDataMapper.batchInsert2(list); } catch (Exception e){ logger.error("importTemplateExcelFile error.", e); throw new ServiceException(ErrorMessage.SystemError); } } }