execl 关于poi 的导入导出

　execl

package com.hainei.common.execl;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
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.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.text.SimpleDateFormat;
import java.util.Iterator;

/**
 * Created with IntelliJ IDEA.
 * User：wq
 * Date：2021/9/13
 * Time: 17:10
 * Description: execl 工具包
 */
@Component
public class ExeclUtil {

    public StringBuilder readXls(File file) {
        FileInputStream is = null;
        HSSFWorkbook execl = null;
        StringBuilder sb = new StringBuilder();
        try {
            is = new FileInputStream(file);
            execl = new HSSFWorkbook(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
        HSSFSheet sheet0 = execl.getSheetAt(0);
        int physicalNumberOfRows = sheet0.getPhysicalNumberOfRows();
        for (int i = 1; i < physicalNumberOfRows; i++) {
            HSSFRow row = sheet0.getRow(i);
            for (int j = 0; j <row.getPhysicalNumberOfCells(); j++) {
                HSSFCell cell = row.getCell(j);
                if(cell.getCellTypeEnum()== CellType.STRING)  sb.append(cell.getStringCellValue());
                else if(cell.getCellTypeEnum()==CellType.NUMERIC)  sb.append(cell.getNumericCellValue());
                else if(cell.getCellTypeEnum()==CellType.FORMULA) sb.append(cell.getCellFormula());
                sb.append(",");
            }
            sb.append(";");
        }
        return sb;
    }

    public StringBuilder realXlsx(File file) {
        FileInputStream is = null;
        XSSFWorkbook execl = null;
        StringBuilder sb = new StringBuilder();
        try {
            is = new FileInputStream(file);
            execl = new XSSFWorkbook(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
        XSSFSheet sheet0 = execl.getSheetAt(0);
        int physicalNumberOfRows = sheet0.getPhysicalNumberOfRows();
        for (int i = 1; i < physicalNumberOfRows; i++) {
            XSSFRow row = sheet0.getRow(i);
            for (int j = 0; j <row.getPhysicalNumberOfCells(); j++) {
                XSSFCell cell = row.getCell(j);
                if(cell.getCellTypeEnum()== CellType.STRING)  sb.append(cell.getStringCellValue());
                else if(cell.getCellTypeEnum()==CellType.NUMERIC) {
                    String dataFormatString = cell.getCellStyle().getDataFormatString();
                    if("yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString()) || "m/d/yy".equals(cell.getCellStyle().getDataFormatString())
                            || "yy/m/d".equals(cell.getCellStyle().getDataFormatString()) || "mm/dd/yy".equals(cell.getCellStyle().getDataFormatString())
                            || "dd-mmm-yy".equals(cell.getCellStyle().getDataFormatString())|| "yyyy/m/d".equals(cell.getCellStyle().getDataFormatString())){
                        //return new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
                        sb.append(new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue()));
                    }else {
                        sb.append(cell.getNumericCellValue());
                    }
                }
                else if(cell.getCellTypeEnum()==CellType.FORMULA) sb.append(cell.getCellFormula());
                sb.append(",");

            }
            sb.append(";");
        }
        return sb;
    }

    public HSSFWorkbook exporXls(String  json){
        //行数组
        String[] dataRowArray = json.split(";");
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet0 = workbook.createSheet("sheet0");
        //创建多少行
//        HSSFRow row = sheet0.createRow(dataRowArray.length);
        if(dataRowArray.length>0){
            for (int i = 0; i <dataRowArray.length ; i++) {
                //创建多少行
                HSSFRow row = sheet0.createRow(i);
                //每行单元格数组
                String[] dataRow = dataRowArray[i].split(",");
                for (int j = 0; j < dataRow.length; j++) {
                    //每行创建多少单元格
                    HSSFCell cell = row.createCell(j);
                    cell.setCellValue(dataRow[j]);
                }
            }
        }
        return workbook;
    }

    public XSSFWorkbook exporXlsx(String  json){
        //行数组
        String[] dataRowArray = json.split(";");
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet0 = workbook.createSheet("sheet0");
        if(dataRowArray.length>0){
            for (int i = 0; i <dataRowArray.length ; i++) {
                //创建多少行
                XSSFRow row = sheet0.createRow(i);
                //每行单元格数组
                String[] dataRow = dataRowArray[i].split(",");
                for (int j = 0; j < dataRow.length; j++) {
                    //每行创建多少单元格
                    XSSFCell cell = row.createCell(j);
                    cell.setCellValue(dataRow[j]);
                }
            }
        }
        return workbook;
    }
}

View Code

文件下载：

/**
     * 下载格式
     * @param sb
     * @param exprotFileName 导出文件名称
     * @return
     */
    String afterHandle(StringBuilder sb,String exprotFileName){
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            //String id = UUID.randomUUID().toString().replaceAll("-", "");
            String prefix =exprotFileName+ sdf.format(new Date());
            String uploadPath = applicationProperties.getUploadFile().getUploadPath()+EXECL_PATH;
            String readPath = applicationProperties.getUploadFile().getReadPath()+EXECL_PATH;
            String returnPath = uploadPath+prefix+EXECL;
            String returnReadPath = readPath+prefix+EXECL;
            File file = new File(returnPath);
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            XSSFWorkbook workbook = execlUtil.exporXlsx(sb.toString());
            workbook.write(fileOutputStream);
            fileOutputStream.close();
            return returnReadPath;
//            ServletOutputStream os = response.getOutputStream();
//            XSSFWorkbook workbook = execlUtil.exporXlsx(sb.toString());
//            response.setHeader("Content-disposition",
//                    "attachment; filename=" +new String(LdarImportExportConstant.PRODUCE_EXPORT_NAME.getBytes("GB2312"),"8859_1")  + ".xlsx");
//            response.setContentType("application/msexcel");
//            workbook.write(os);
//            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

View Code

业务参考代码

LdarDataImportExportServiceImpl

package com.hainei.service.impl.ldar;

import com.hainei.common.ApplicationProperties;
import com.hainei.common.constants.ldar.LdarImportExportConstant;
import com.hainei.common.exception.BusinessException;
import com.hainei.common.execl.ExeclUtil;
import com.hainei.common.utils.PageVO;
import com.hainei.mapper.ldar.LdarProduceAreaMapper;
import com.hainei.pojo.bo.ldar.*;
import com.hainei.pojo.dto.ldar.ExeclDTO;
import com.hainei.pojo.model.ldar.LdarCompany;
import com.hainei.pojo.vo.ldar.LdarCompanyVO;
import com.hainei.service.ldar.*;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Optional;
import java.util.UUID;
import java.util.concurrent.atomic.AtomicReference;

import static com.hainei.common.exception.code.BusinessResponseCode.TEMPLATE_IMPORT_UNUSAUAL_ERROR;

/**
 * Created with IntelliJ IDEA.
 * User：wq
 * Date：2021/9/13
 * Time: 16:56
 * Description: 企业档案-泄露检测-数据导入
 */
@Service
@Transactional
public class LdarDataImportExportServiceImpl implements LdarDataImportExportService {
    @Autowired
    private LdarProduceAreaService ldarProduceAreaService;
    @Autowired
    private LdarCompanyService ldarCompanyService;
    @Autowired
    private LdarProduceAreaMapper ldarProduceAreaMapper;
    @Autowired
    private LdarDeviceService ldarDeviceService;
    @Autowired
    private LdarFacilityService ldarFacilityService;
    @Autowired
    private LdarLetPointService ldarLetPointService;
    @Autowired
    private LdarLetPointValueService ldarLetPointValueService;
    @Autowired
    private ApplicationProperties applicationProperties;
    @Autowired
    private HttpServletRequest request;
    @Autowired
    private HttpServletResponse response;
    @Autowired
    private ExeclUtil execlUtil;

    private final static String EXECL_PATH ="/excel/";
    private final static String EXECL =".xlsx";

    /**
     * 导入预处理
     * @param multipartFile
     * @return
     * @throws Exception
     */
    public ExeclDTO preHandle(MultipartFile multipartFile){
        String id = UUID.randomUUID().toString().replaceAll("-", "");
        String fileName = multipartFile.getOriginalFilename();
        String suffix = fileName.substring(fileName.lastIndexOf(".")+1);
        String path = id +"."+ suffix;
        File file = new File(applicationProperties.getUploadFile().getUploadPath()+path);
        try {
            multipartFile.transferTo(file);
        } catch (IOException e) {
            e.printStackTrace();
        }
        ExeclDTO execlDTO = new ExeclDTO();
        execlDTO.setFile(file);
        execlDTO.setSuffix(suffix);
        return execlDTO;
    }

    /**
     * 下载格式
     * @param sb
     * @param exprotFileName 导出文件名称
     * @return
     */
    String afterHandle(StringBuilder sb,String exprotFileName){
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            //String id = UUID.randomUUID().toString().replaceAll("-", "");
            String prefix =exprotFileName+ sdf.format(new Date());
            String uploadPath = applicationProperties.getUploadFile().getUploadPath()+EXECL_PATH;
            String readPath = applicationProperties.getUploadFile().getReadPath()+EXECL_PATH;
            String returnPath = uploadPath+prefix+EXECL;
            String returnReadPath = readPath+prefix+EXECL;
            File file = new File(returnPath);
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            XSSFWorkbook workbook = execlUtil.exporXlsx(sb.toString());
            workbook.write(fileOutputStream);
            fileOutputStream.close();
            return returnReadPath;
//            ServletOutputStream os = response.getOutputStream();
//            XSSFWorkbook workbook = execlUtil.exporXlsx(sb.toString());
//            response.setHeader("Content-disposition",
//                    "attachment; filename=" +new String(LdarImportExportConstant.PRODUCE_EXPORT_NAME.getBytes("GB2312"),"8859_1")  + ".xlsx");
//            response.setContentType("application/msexcel");
//            workbook.write(os);
//            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 导入数据封装
     * @param multipartFile
     * @return
     * @throws Exception
     */
    @Override
    public String importData(MultipartFile multipartFile){
        AtomicReference<StringBuilder> sb = new AtomicReference<>(new StringBuilder());
        ExeclDTO execlDTO = preHandle(multipartFile);
        Optional.ofNullable(execlDTO.getSuffix()).ifPresent(p->{
            if(p.equals(LdarImportExportConstant.XLS)){
                sb.set(execlUtil.readXls(execlDTO.getFile()));
            }
            if(p.equals(LdarImportExportConstant.XLSX)){
                sb.set(execlUtil.realXlsx(execlDTO.getFile()));
            }
        });
       return sb.get().toString();
    }


    @Override
    public void importProduceAreaData(MultipartFile multipartFile) {
        String json = importData(multipartFile);
        String[] produceJsonArray = json.split(";");
        LdarProduceAreaBO ldarProduceAreaBO = new LdarProduceAreaBO();
            if(produceJsonArray.length>0){
                for (String  object:
                produceJsonArray) {
                    String[] produce = object.split(",");
                    if(produce.length>1){
                        ldarProduceAreaBO.setCompanyName(produce[0]);
                        ldarProduceAreaBO.setProduceName(produce[1]);
                        ldarProduceAreaBO.setManager(produce[2]);
                        ldarProduceAreaBO.setPhone(changeScientificToStr(produce[3]));
                        ldarProduceAreaBO.setProduceAreaStatus(produce[4]);
                        ldarProduceAreaBO.setCompanyAuth(produce[5]);
                        ldarProduceAreaService.insertRecords(ldarProduceAreaBO);
                    }
                }
            }
    }


    @Override
    public String exportProduceAreaData() {
        StringBuilder sb = new StringBuilder();
        sb.append(LdarImportExportConstant.PRODUCE_EXPORT_PREFIX);
        LdarProduceAreaQueryBO ldarProduceAreaQueryBO = new LdarProduceAreaQueryBO();
        ldarProduceAreaQueryBO.setPageNum(1);
        ldarProduceAreaQueryBO.setPageSize(10000);
        PageVO<LdarProduceAreaBO> ldarProduceAreaBOPageVO = ldarProduceAreaService.listRecords(ldarProduceAreaQueryBO);
        List<LdarProduceAreaBO> list = ldarProduceAreaBOPageVO.getList();
        list.stream().filter(p->CollectionUtils.isNotEmpty(list)).forEach(v->{
            Optional.ofNullable(v).ifPresent(s->{
                sb.append(s.getCompanyName()+",");
                sb.append(s.getProduceName()+",");
                sb.append(s.getManager()+",");
                sb.append(s.getPhone()+",");
                sb.append(s.getProduceAreaStatus()+",");
                sb.append(s.getCompanyAuth()+",");
                sb.append(";");
            });
        });
        String returnPath = afterHandle(sb,LdarImportExportConstant.PRODUCE_EXPORT_NAME);
        return returnPath;
    }

    @Override
    public String exportCompanyInfo() {
        StringBuilder sb = new StringBuilder();
        sb.append(LdarImportExportConstant.COMPANY_EXPORT_USED_BY_PRODUCE);
        LdarCompanyQueryBO ldarCompanyQueryBO = new LdarCompanyQueryBO();
        ldarCompanyQueryBO.setPageNum(1);
        ldarCompanyQueryBO.setPageSize(10000);
        PageVO<LdarCompanyVO> ldarCompanyVOPageVO = ldarCompanyService.listRecords(ldarCompanyQueryBO);
        List<LdarCompanyVO> list = ldarCompanyVOPageVO.getList();
        list.stream().filter(p->CollectionUtils.isNotEmpty(list)).forEach(v->{
            Optional.ofNullable(v).ifPresent(s->{
                sb.append(s.getCompanyName()+",");
                sb.append(s.getId()+",");
                sb.append(s.getCompanyAuth()+",");
                sb.append(";");
            });
        });
        String returnPath = afterHandle(sb,LdarImportExportConstant.COMPANY_EXPORT_USED_BY_PRODUCE_FILE_NAME);
        return returnPath;
    }

    @Override
    public void importDeviceData(MultipartFile multipartFile) {
        String json = importData(multipartFile);
        String[] deviceJsonArray = json.split(";");
        LdarDeviceBO ldarDeviceBO = new LdarDeviceBO();
            if(deviceJsonArray.length>0){
                for (String  object:
                        deviceJsonArray) {
                    String[] device = object.split(",");
                    if(device.length>1){
                        ldarDeviceBO.setCompanyName(device[0]);
                        ldarDeviceBO.setProduceAreaId(device[1]);
                        ldarDeviceBO.setDeviceName(device[2]);
                        ldarDeviceBO.setManager(device[3]);
                        ldarDeviceBO.setDeviceStatus(device[4]);
                        ldarDeviceBO.setCompanyAuth(device[5]);
                        ldarDeviceService.insertRecords(ldarDeviceBO);
                    }
                }
            }
    }

    @Override
    public String exportDeviceData() {
        StringBuilder sb = new StringBuilder();
        sb.append(LdarImportExportConstant.DEVICE_EXPORT_PREFIX);
        LdarDeviceQueryBO ldarDeviceQueryBO = new LdarDeviceQueryBO();
        ldarDeviceQueryBO.setPageNum(1);
        ldarDeviceQueryBO.setPageSize(10000);
        PageVO<LdarDeviceBO> ldarDeviceBOPageVO = ldarDeviceService.listRecords(ldarDeviceQueryBO);
        List<LdarDeviceBO> list = ldarDeviceBOPageVO.getList();
        list.stream().filter(p->CollectionUtils.isNotEmpty(list)).forEach(v->{
            Optional.ofNullable(v).ifPresent(s->{
                sb.append(s.getCompanyName()+",");
                sb.append(s.getProduceAreaId()+",");
                sb.append(s.getDeviceName()+",");
                sb.append(s.getManager()+",");
                sb.append(s.getDeviceStatus()+",");
                sb.append(s.getCompanyAuth()+",");
                sb.append(";");
            });
        });
        String returnPath = afterHandle(sb,LdarImportExportConstant.DEVICE_EXPORT_NAME);
        return returnPath;
    }

    @Override
    public String exportProduceInfo() {
        StringBuilder sb = new StringBuilder();
        sb.append(LdarImportExportConstant.PRODUCE_EXPORT_USED_BY_DEVICE);
        LdarProduceAreaQueryBO ldarProduceAreaQueryBO = new LdarProduceAreaQueryBO();
        ldarProduceAreaQueryBO.setPageNum(1);
        ldarProduceAreaQueryBO.setPageSize(10000);
        PageVO<LdarProduceAreaBO> ldarProduceAreaBOPageVO = ldarProduceAreaService.listRecords(ldarProduceAreaQueryBO);
        List<LdarProduceAreaBO> list = ldarProduceAreaBOPageVO.getList();
        list.stream().filter(p->CollectionUtils.isNotEmpty(list)).forEach(v->{
            Optional.ofNullable(v).ifPresent(s->{
                sb.append(s.getProduceName()+",");
                sb.append(s.getId()+",");
                sb.append(s.getCompanyAuth()+",");
                sb.append(";");
            });
        });
        String returnPath = afterHandle(sb,LdarImportExportConstant.PRODUCE_EXPORT_USED_BY_DEVICE_FILE_NAME);
        return returnPath;
    }

    @Override
    public void importFacilityData(MultipartFile multipartFile) {
        String json = importData(multipartFile);
        String[] facilityJsonArray = json.split(";");
        LdarFacilityBO ldarFacilityBO = new LdarFacilityBO();
            if(facilityJsonArray.length>1){
                for (String  object:
                        facilityJsonArray) {
                    String[] device = object.split(",");
                    if(device.length>0){
                        ldarFacilityBO.setCompanyName(device[0]);
                        ldarFacilityBO.setProduceId(device[1]);
                        ldarFacilityBO.setDeviceId(device[2]);
                        ldarFacilityBO.setFacilityNum(device[3]);
                        ldarFacilityBO.setFacilityName(device[4]);
                        ldarFacilityBO.setManager(device[5]);
                        ldarFacilityBO.setFacilityStatus(device[6]);
                        ldarFacilityBO.setCompanyAuth(device[7]);
                        ldarFacilityService.insertRecords(ldarFacilityBO);
                    }
                }
            }
    }

    @Override
    public String exportFacilityData() {
        StringBuilder sb = new StringBuilder();
        sb.append(LdarImportExportConstant.FACILITY_EXPORT_PREFIX);
        LdarFacilityQueryBO ldarFacilityQueryBO = new LdarFacilityQueryBO();
        ldarFacilityQueryBO.setPageNum(1);
        ldarFacilityQueryBO.setPageSize(10000);
        PageVO<LdarFacilityBO> ldarFacilityBOPageVO = ldarFacilityService.listRecords(ldarFacilityQueryBO);
//        List<LdarProduceAreaBO> list = ldarProduceAreaMapper.listAllRecords();
        List<LdarFacilityBO> list = ldarFacilityBOPageVO.getList();
        list.stream().filter(p->CollectionUtils.isNotEmpty(list)).forEach(v->{
            Optional.ofNullable(v).ifPresent(s->{
                sb.append(s.getCompanyName()+",");
                sb.append(s.getProduceId()+",");
                sb.append(s.getDeviceId()+",");
                sb.append(s.getFacilityNum()+",");
                sb.append(s.getFacilityName()+",");
                sb.append(s.getManager()+",");
                sb.append(s.getFacilityStatus()+",");
                sb.append(s.getCompanyAuth()+",");
                sb.append(";");
            });
        });
        String returnPath = afterHandle(sb,LdarImportExportConstant.FACILITY_EXPORT_NAME);
        return returnPath;
    }

    @Override
    public String exportDeviceInfo() {
        StringBuilder sb = new StringBuilder();
        sb.append(LdarImportExportConstant.DEVICE_EXPORT_USED_BY_FACILITY);
        LdarDeviceQueryBO ldarDeviceQueryBO = new LdarDeviceQueryBO();
        ldarDeviceQueryBO.setPageNum(1);
        ldarDeviceQueryBO.setPageSize(10000);
        PageVO<LdarDeviceBO> ldarDeviceBOPageVO = ldarDeviceService.listRecords(ldarDeviceQueryBO);
//        List<LdarProduceAreaBO> list = ldarProduceAreaMapper.listAllRecords();
        List<LdarDeviceBO> list = ldarDeviceBOPageVO.getList();
        list.stream().filter(p->CollectionUtils.isNotEmpty(list)).forEach(v->{
            Optional.ofNullable(v).ifPresent(s->{
                sb.append(s.getDeviceName()+",");
                sb.append(s.getId()+",");
                sb.append(s.getCompanyAuth()+",");
                sb.append(";");
            });
        });
        String returnPath = afterHandle(sb,LdarImportExportConstant.DEVICE_EXPORT_USED_BY_FACILITY_FILE_NAME);
        return returnPath;
    }

    @Override
    public void importLetPointData(MultipartFile multipartFile) {
        String json = importData(multipartFile);
        String[] letPointJsonArray = json.split(";");
        LdarLetPointBO ldarLetPointBO = new LdarLetPointBO();
            if(letPointJsonArray.length>0){
                for (String  object:
                        letPointJsonArray) {
                    String[] device = object.split(",");
                    if(device.length>1){
                        ldarLetPointBO.setCompanyName(device[0]);
                        ldarLetPointBO.setEncode(device[1]);
                        ldarLetPointBO.setProduceId(device[2]);
                        ldarLetPointBO.setDeviceId(device[3]);
                        ldarLetPointBO.setFacilityId(device[4]);
                        ldarLetPointBO.setLetPointName(device[5]);
                        ldarLetPointBO.setLetPointType(device[6]);
                        ldarLetPointBO.setFluidMedia(device[7]);
                        ldarLetPointBO.setFluidStatus(device[8]);
                        ldarLetPointBO.setFacilityType(device[9]);
                        ldarLetPointBO.setCompanyAuth(device[10]);
                        ldarLetPointService.insertRecords(ldarLetPointBO);
                    }
                }
            }
    }

    @Override
    public String exportLetPointData() {
        StringBuilder sb = new StringBuilder();
        sb.append(LdarImportExportConstant.LET_POINT_EXPORT_PREFIX);
        LdarLetPointQueryBO ldarLetPointQueryBO = new LdarLetPointQueryBO();
        ldarLetPointQueryBO.setPageNum(1);
        ldarLetPointQueryBO.setPageSize(10000);
        PageVO<LdarLetPointBO> ldarLetPointBOPageVO = ldarLetPointService.listRecords(ldarLetPointQueryBO);
//        List<LdarProduceAreaBO> list = ldarProduceAreaMapper.listAllRecords();
        List<LdarLetPointBO> list = ldarLetPointBOPageVO.getList();
        list.stream().filter(p->CollectionUtils.isNotEmpty(list)).forEach(v->{
            Optional.ofNullable(v).ifPresent(s->{
                sb.append(s.getCompanyName()+",");
                sb.append(s.getEncode()+",");
                sb.append(s.getProduceId()+",");
                sb.append(s.getDeviceId()+",");
                sb.append(s.getFacilityId()+",");
                sb.append(s.getLetPointName()+",");
                sb.append(s.getLetPointType()+",");
                sb.append(s.getFluidMedia()+",");
                sb.append(s.getFluidStatus()+",");
                sb.append(s.getFacilityType()+",");
                sb.append(s.getCompanyAuth()+",");
                sb.append(";");
            });
        });
        String returnPath = afterHandle(sb,LdarImportExportConstant.LET_POINT_EXPORT_NAME);
        return returnPath;
    }

    @Override
    public String exportFacilityInfo() {
        StringBuilder sb = new StringBuilder();
        sb.append(LdarImportExportConstant.FACILITY_EXPORT_USED_BY_LET_POINT);
        LdarFacilityQueryBO ldarFacilityQueryBO = new LdarFacilityQueryBO();
        ldarFacilityQueryBO.setPageNum(1);
        ldarFacilityQueryBO.setPageSize(10000);
        PageVO<LdarFacilityBO> ldarFacilityBOPageVO = ldarFacilityService.listRecords(ldarFacilityQueryBO);
        List<LdarFacilityBO> list = ldarFacilityBOPageVO.getList();
        list.stream().filter(p->CollectionUtils.isNotEmpty(list)).forEach(v->{
            Optional.ofNullable(v).ifPresent(s->{
                sb.append(s.getFacilityName()+",");
                sb.append(s.getId()+",");
                sb.append(s.getCompanyAuth()+",");
                sb.append(";");
            });
        });
        String returnPath = afterHandle(sb,LdarImportExportConstant.FACILITY_EXPORT_USED_BY_LET_POINT_FILE_NAME);
        return returnPath;
    }

    @Override
    public void importMonitorData(MultipartFile multipartFile) {
        String json = importData(multipartFile);
        String[] monitorFileJsonArray = json.split(";");
        LdarLetPointValueBO ldarLetPointValueBO = new LdarLetPointValueBO();
            if(monitorFileJsonArray.length>0){
                for (String  object:
                        monitorFileJsonArray) {
                    String[] device = object.split(",");
                    if(device.length>1){
                        ldarLetPointValueBO.setLetPointId(device[0]);
                        ldarLetPointValueBO.setLetPointName(device[1]);
                        ldarLetPointValueBO.setMonitorValue(changeStrToDecimal(device[2]));
                        ldarLetPointValueBO.setReportPeriod(strToDate(device[3]));
                        ldarLetPointValueService.insertLetPointValue(ldarLetPointValueBO);
                    }
                }
            }
    }

    @Override
    public String exportMonitorData() {
        StringBuilder sb = new StringBuilder();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        sb.append(LdarImportExportConstant.LET_POINT_VALUE_EXPORT_PREFIX);
        LdarLetPointValueQueryBO ldarLetPointValueQueryBO = new LdarLetPointValueQueryBO();
        ldarLetPointValueQueryBO.setPageNum(1);
        ldarLetPointValueQueryBO.setPageSize(10000);
        PageVO<LdarLetPointValueBO> ldarLetPointValueBOPageVO = ldarLetPointValueService.listRecords(ldarLetPointValueQueryBO);
        List<LdarLetPointValueBO> list = ldarLetPointValueBOPageVO.getList();
        list.stream().filter(p->CollectionUtils.isNotEmpty(list)).forEach(v->{
            Optional.ofNullable(v).ifPresent(s->{
                sb.append(s.getLetPointId()+",");
                sb.append(s.getLetPointName()+",");
                sb.append(s.getMonitorValue()+",");
                sb.append(sdf.format(s.getReportPeriod())+",");
                sb.append(";");
            });
        });
        String returnPath = afterHandle(sb,LdarImportExportConstant.LET_POINT_VALUE_EXPORT_NAME);
        return returnPath;
    }

    @Override
    public String exportLetPointInfo() {
        StringBuilder sb = new StringBuilder();
        sb.append(LdarImportExportConstant.LET_POINT_EXPORT_USED_BY_LET_POINT_VALUE);
        LdarLetPointQueryBO ldarLetPointQueryBO = new LdarLetPointQueryBO();
        ldarLetPointQueryBO.setPageNum(1);
        ldarLetPointQueryBO.setPageSize(10000);
        PageVO<LdarLetPointBO> ldarLetPointBOPageVO = ldarLetPointService.listRecords(ldarLetPointQueryBO);
//        List<LdarProduceAreaBO> list = ldarProduceAreaMapper.listAllRecords();
        List<LdarLetPointBO> list = ldarLetPointBOPageVO.getList();
        list.stream().filter(p->CollectionUtils.isNotEmpty(list)).forEach(v->{
            Optional.ofNullable(v).ifPresent(s->{
                sb.append(s.getLetPointName()+",");
                sb.append(s.getId()+",");
                sb.append(s.getCompanyAuth()+",");
                sb.append(";");
            });
        });
        String returnPath = afterHandle(sb,LdarImportExportConstant.LET_POINT_EXPORT_USED_BY_LET_POINT_VALUE_FILE_NAME);
        return returnPath;
    }

    String changeScientificToStr(String num){
        BigDecimal bd = new BigDecimal(num);
        return bd.toPlainString();
    }

    BigDecimal changeStrToDecimal(String value){
        BigDecimal bd = new BigDecimal(value);
        return bd;
    }

    Date strToDate(String json){
        Date date = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        try {
            date  = sdf.parse(json);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return date;
    }

}

View Code

新版：

execl

package com.hainei.common.execl;

import com.hainei.common.ApplicationProperties;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
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.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.UUID;

/**
 * Created with IntelliJ IDEA.
 * User：wq
 * Date：2021/9/13
 * Time: 17:10
 * Description: execl 工具包
 */
@Component
public class ExcelUtil {
    @Autowired
    private ApplicationProperties applicationProperties;
    private final static String EXCEL_PATH ="/excel/";
    private final static String EXCEL =".xlsx";

    public StringBuilder readXls(File file) {
        FileInputStream is = null;
        HSSFWorkbook execl = null;
        StringBuilder sb = new StringBuilder();
        try {
            is = new FileInputStream(file);
            execl = new HSSFWorkbook(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
        HSSFSheet sheet0 = execl.getSheetAt(0);
        int physicalNumberOfRows = sheet0.getPhysicalNumberOfRows();
        for (int i = 1; i < physicalNumberOfRows; i++) {
            HSSFRow row = sheet0.getRow(i);
            for (int j = 0; j <row.getPhysicalNumberOfCells(); j++) {
                HSSFCell cell = row.getCell(j);
                if(cell.getCellTypeEnum()== CellType.STRING) {
                    sb.append(cell.getStringCellValue());
                } else if(cell.getCellTypeEnum()==CellType.NUMERIC) {
                    sb.append(cell.getNumericCellValue());
                } else if(cell.getCellTypeEnum()==CellType.FORMULA) {
                    sb.append(cell.getCellFormula());
                }
                sb.append(",");
            }
            sb.append(";");
        }
        return sb;
    }

    public StringBuilder readXlsx(File file) {
        FileInputStream is = null;
        XSSFWorkbook execl = null;
        StringBuilder sb = new StringBuilder();
        try {
            is = new FileInputStream(file);
            execl = new XSSFWorkbook(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
        XSSFSheet sheet0 = execl.getSheetAt(0);
        int physicalNumberOfRows = sheet0.getPhysicalNumberOfRows();
        for (int i = 1; i < physicalNumberOfRows; i++) {
            XSSFRow row = sheet0.getRow(i);
            for (int j = 0; j <row.getPhysicalNumberOfCells(); j++) {
                XSSFCell cell = row.getCell(j);
                if(cell.getCellTypeEnum()== CellType.STRING) {
                    sb.append(cell.getStringCellValue());
                } else if(cell.getCellTypeEnum()==CellType.NUMERIC) {
                    String dataFormatString = cell.getCellStyle().getDataFormatString();
                    if("yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString()) || "m/d/yy".equals(cell.getCellStyle().getDataFormatString())
                            || "yy/m/d".equals(cell.getCellStyle().getDataFormatString()) || "mm/dd/yy".equals(cell.getCellStyle().getDataFormatString())
                            || "dd-mmm-yy".equals(cell.getCellStyle().getDataFormatString())|| "yyyy/m/d".equals(cell.getCellStyle().getDataFormatString())){
                        //return new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
                        sb.append(new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue()));
                    }else {
                        sb.append(cell.getNumericCellValue());
                    }
                }
                else if(cell.getCellTypeEnum()==CellType.FORMULA) {
                    sb.append(cell.getCellFormula());
                }
                sb.append(",");

            }
            sb.append(";");
        }
        return sb;
    }

    public HSSFWorkbook exportXls(String  json){
        //行数组
        String[] dataRowArray = json.split(";");
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet0 = workbook.createSheet("sheet0");
        //创建多少行
//        HSSFRow row = sheet0.createRow(dataRowArray.length);
        if(dataRowArray.length>0){
            for (int i = 0; i <dataRowArray.length ; i++) {
                //创建多少行
                HSSFRow row = sheet0.createRow(i);
                //每行单元格数组
                String[] dataRow = dataRowArray[i].split(",");
                for (int j = 0; j < dataRow.length; j++) {
                    //每行创建多少单元格
                    HSSFCell cell = row.createCell(j);
                    cell.setCellValue(dataRow[j]);
                }
            }
        }
        return workbook;
    }

    public XSSFWorkbook exportXlsx(String  json){
        //行数组
        String[] dataRowArray = json.split(";");
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet0 = workbook.createSheet("sheet0");
        if(dataRowArray.length>0){
            for (int i = 0; i <dataRowArray.length ; i++) {
                //创建多少行
                XSSFRow row = sheet0.createRow(i);
                //每行单元格数组
                String[] dataRow = dataRowArray[i].split(",");
                for (int j = 0; j < dataRow.length; j++) {
                    //每行创建多少单元格
                    XSSFCell cell = row.createCell(j);
                    cell.setCellValue(dataRow[j]);
                }
            }
        }
        return workbook;
    }

    /**
     * 导入预处理
     * @param multipartFile
     * @return
     * @throws Exception
     */
    public ExeclDTO preHandle(MultipartFile multipartFile){
        String id = UUID.randomUUID().toString().replaceAll("-", "");
        String fileName = multipartFile.getOriginalFilename();
        String suffix = fileName.substring(fileName.lastIndexOf(".")+1);
        String path = id +"."+ suffix;
        File file = new File(applicationProperties.getUploadFile().getUploadPath()+path);
        try {
            multipartFile.transferTo(file);
        } catch (IOException e) {
            e.printStackTrace();
        }
        ExeclDTO execlDTO = new ExeclDTO();
        execlDTO.setFile(file);
        execlDTO.setSuffix(suffix);
        return execlDTO;
    }

    /**
     * 下载格式
     * @param sb
     * @param exportFileName 导出文件名称
     * @return
     */
    public String afterHandle(StringBuilder sb,String exportFileName){
        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            //String id = UUID.randomUUID().toString().replaceAll("-", "");
            String prefix =exportFileName+ sdf.format(new Date());
            String uploadPath = applicationProperties.getUploadFile().getUploadPath()+EXCEL_PATH;
            String readPath = applicationProperties.getUploadFile().getReadPath()+EXCEL_PATH;
            String returnPath = uploadPath+prefix+EXCEL;
            String returnReadPath = readPath+prefix+EXCEL;
            File file = new File(returnPath);
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            XSSFWorkbook workbook = exportXlsx(sb.toString());
            workbook.write(fileOutputStream);
            fileOutputStream.close();
            return returnReadPath;
//            ServletOutputStream os = response.getOutputStream();
//            XSSFWorkbook workbook = execlUtil.exporXlsx(sb.toString());
//            response.setHeader("Content-disposition",
//                    "attachment; filename=" +new String(LdarImportExportConstant.PRODUCE_EXPORT_NAME.getBytes("GB2312"),"8859_1")  + ".xlsx");
//            response.setContentType("application/msexcel");
//            workbook.write(os);
//            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}

View Code

业务代码：

package com.hainei.service.impl.sp;

import com.github.pagehelper.PageHelper;
import com.hainei.common.constants.BaseConstant;
import com.hainei.common.constants.ldar.LdarImportExportConstant;
import com.hainei.common.execl.ExcelUtil;
import com.hainei.common.execl.ExeclDTO;
import com.hainei.common.execl.RevenueExcelUtil;
import com.hainei.common.token.JwtTokenUtil;
import com.hainei.common.utils.PageUtil;
import com.hainei.common.utils.PageVO;
import com.hainei.mapper.sp.SpRevenueMapper;
import com.hainei.pojo.bo.sp.SpRevenueBO;
import com.hainei.pojo.bo.sp.SpRevenueQueryBO;
import com.hainei.pojo.model.sp.SpAcceptCarriage;
import com.hainei.pojo.model.sp.SpRevenue;
import com.hainei.pojo.vo.sp.SpRevenueVO;
import com.hainei.service.base.BaseDataPermissionService;
import com.hainei.service.sp.SpRevenueService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;

/**
 * Created with IntelliJ IDEA.
 *
 * @Author : wq
 * Date：2023/10/19
 * Time: 19:33
 * Description: No Description
 */
@Transactional
@Service
public class SpRevenueServiceImpl implements SpRevenueService {
    @Autowired
    private SpRevenueMapper spRevenueMapper;
    @Autowired
    private BaseDataPermissionService baseDataPermissionService;
    @Autowired
    private HttpServletRequest request;
    @Autowired
    private RevenueExcelUtil excelUtil;

    public static final String XLSX = "xlsx";
    public static final String XLS ="xls" ;
    public static final String REVENUE_EXPORT_PREFIX ="单位名称,统计时间段,本年度开票销售额,上年度开票销售额,开票销售额同比增长,本年度入库税收,上年度入库税收,入库同比增长;" ;
    public static final String REVENUE_EXPORT_NAME ="税收" ;

    SimpleDateFormat ym = new SimpleDateFormat("yyyy-MM");

    @Override
    public void insertOrUpdate(SpRevenueBO spRevenueBO) {
        String accessToken = request.getHeader(BaseConstant.ACCESS_TOKEN);
        String userId = JwtTokenUtil.getUserId(accessToken);
        SpRevenue spRevenue = new SpRevenue();
        BeanUtils.copyProperties(spRevenueBO,spRevenue);
        if(StringUtils.isNotBlank(spRevenueBO.getId())){
            spRevenue.setGmtUpdatedBy(userId);
            spRevenue.setGmtUpdatedOn(new Date());
            spRevenueMapper.updateByPrimaryKeySelective(spRevenue);
        } else {
            String id = UUID.randomUUID().toString().replaceAll("-", "");
            spRevenue.setId(id);
            spRevenue.setGmtCreatedBy(userId);
            spRevenue.setGmtCreatedOn(new Date());
            spRevenue.setIsDeleted((byte)0);
            spRevenueMapper.insertSelective(spRevenue);
        }
    }

    @Override
    public void delete(List<String> ids) {
        ids.stream().filter(id -> id != null).forEach(id -> {
            SpRevenue spRevenue = new SpRevenue();
            spRevenue.setIsDeleted((byte) 1);
            spRevenue.setId(id);
            spRevenueMapper.updateByPrimaryKeySelective(spRevenue);
        });
    }

    @Override
    public SpRevenueVO getById(String id) {
        SpRevenueVO spAcceptCarriageVO =  spRevenueMapper.getById(id);
        return spAcceptCarriageVO;
    }

    @Override
    public PageVO<SpRevenueVO> listSelective(SpRevenueQueryBO queryBO) {
        String accessToken = request.getHeader(BaseConstant.ACCESS_TOKEN);
        String userId = JwtTokenUtil.getUserId(accessToken);
//        List<String> olists = baseDataPermissionService.getDataPermissionsByUserId(userId);
//        queryBO.setOlists(olists);
        PageHelper.startPage(queryBO.getPageNum(),queryBO.getPageSize());
        List<SpRevenueVO> spAcceptCarriageVOList = spRevenueMapper.listSelective(queryBO);
        PageVO<SpRevenueVO> pageVO = PageUtil.getPageVO(spAcceptCarriageVOList);
        return pageVO;
    }

    @Override
    public String importData(MultipartFile file) {
        AtomicReference<StringBuilder> sb = new AtomicReference<>(new StringBuilder());
        ExeclDTO execlDTO = excelUtil.preHandle(file);
        Optional.ofNullable(execlDTO.getSuffix()).ifPresent(p->{
            if(p.equals(XLS)){
                sb.set(excelUtil.readXls(execlDTO.getFile()));
            }
            if(p.equals(XLSX)){
                sb.set(excelUtil.readXlsx(execlDTO.getFile()));
            }
        });
        return sb.get().toString();
    }

    @Override
    public void importRevenue(MultipartFile file) {
        String json = importData(file);
        String[] revenueJsonArray = json.split(";");
        SpRevenueBO spRevenueBO = new SpRevenueBO();
        if(revenueJsonArray.length>0){
            for (String  object:
                    revenueJsonArray) {
                String[] spRevenue = object.split(",");
                if(spRevenue.length>1){
                    spRevenueBO.setCompanyName(spRevenue[0]);
                    BigDecimal bndkpxse = new BigDecimal(spRevenue[1]);
                    BigDecimal sndkpxde = new BigDecimal(spRevenue[2]);
                    BigDecimal bndrkss = new BigDecimal(spRevenue[4]);
                    BigDecimal sndrkss = new BigDecimal(spRevenue[5]);
                    NumberFormat percentInstance = NumberFormat.getPercentInstance();
                    percentInstance.setMaximumFractionDigits(4);
                    spRevenueBO.setBndkpxse(bndkpxse);
                    spRevenueBO.setSndkpxse(sndkpxde);
                    BigDecimal kpxsetbzz = bndkpxse.divide(sndkpxde, 4, RoundingMode.HALF_UP);
                     kpxsetbzz = kpxsetbzz.subtract(new BigDecimal("1"));
                    spRevenueBO.setKpxsetbzz(percentInstance.format(kpxsetbzz.doubleValue()));
                    spRevenueBO.setBndrkss(bndrkss);
                    spRevenueBO.setSndrkss(sndrkss);
                    BigDecimal rksstbzz = bndrkss.divide(sndrkss, 4, RoundingMode.HALF_UP);
                    rksstbzz = rksstbzz.subtract(new BigDecimal("1"));
                    spRevenueBO.setRksstbzz(percentInstance.format(rksstbzz.doubleValue()));
                    insertOrUpdate(spRevenueBO);
                }
            }
        }
    }

    @Override
    public String exportRevenue(SpRevenueQueryBO queryBO) {
        StringBuilder sb = new StringBuilder();
        sb.append(REVENUE_EXPORT_PREFIX);
        PageVO<SpRevenueVO> spRevenueVOPageVO = listSelective(queryBO);
        List<SpRevenueVO> revenueVOList = spRevenueVOPageVO.getList();
        revenueVOList.stream().filter(Objects::nonNull).forEach(spRevenueVO -> {
            sb.append(spRevenueVO.getCompanyName()+",");
            sb.append(ym.format(spRevenueVO.getGmtCreatedOn())+",");
            sb.append(spRevenueVO.getBndkpxse()+",");
            sb.append(spRevenueVO.getSndkpxse()+",");
            sb.append(spRevenueVO.getKpxsetbzz()+",");
            sb.append(spRevenueVO.getBndrkss()+",");
            sb.append(spRevenueVO.getSndrkss()+",");
            sb.append(spRevenueVO.getRksstbzz()+",");
            sb.append(";");

        });
        String returnPath = excelUtil.afterHandle(sb, REVENUE_EXPORT_NAME);
        return returnPath;
    }


}

View Code

参照大佬的总结的api写的

https://blog.csdn.net/qq_21137441/article/details/79226171

posted @ 2021-09-17 17:49 wq9 阅读(44) 评论(0) 收藏举报

刷新页面返回顶部

wq9

execl 关于poi 的导入导出

公告