excel导出

package com.mtoliv.sps.controller;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestPart;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.mtoliv.sps.model.MapImportHanlder;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportServer;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;

@RestController
@RequestMapping(value = "/api/v1/excel")
@Api(tags = "导入导出相关操作参考实现 ")
public class ExcelController {
    
    private static final Logger logger = LoggerFactory.getLogger(ExcelController.class);

    @GetMapping(value = "/exportExcels", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
    @ApiOperation(value = "导出数据")
    public void exportExcels(HttpServletResponse response) throws IOException {

        List<ExcelExportEntity> entityList = new ArrayList<>();
        entityList.add(new ExcelExportEntity("用户ID", "id", 15));
        entityList.add(new ExcelExportEntity("用户名", "name", 15));
        entityList.add(new ExcelExportEntity("用户年龄", "age", 15));
        List<Map<String, String>> dataResult = getData();

        ExcelExportServer server = new ExcelExportServer();
        Workbook workbook = new HSSFWorkbook();

        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName("用户列表");
        server.createSheetForMap(workbook, exportParams, entityList, dataResult);

        response.setCharacterEncoding("UTF-8");
        String filedisplay = "users.xls";
        filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + filedisplay);

        OutputStream out = response.getOutputStream();
        workbook.write(out);
        out.close();
    }
    
    @PostMapping(value = "/importUsers")
    public void importUsers(@RequestPart(value = "file") MultipartFile  file) {
        
        logger.info(file.getOriginalFilename());
        
        String originalFilename = file.getOriginalFilename();
        String extension = FilenameUtils.getExtension(originalFilename);
        logger.info(extension);
        if (!"xlsx".equals(extension) && !"xls".equals(extension)) return;
        
        ImportParams params = new ImportParams();
        params.setDataHanlder(new MapImportHanlder());
        try {
            
            List<Map<String, Object>> list = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, params);
            logger.info(list.size() + "");
        } catch (Exception e) {
            
            e.printStackTrace();
        }
        
    }

    private List<Map<String, String>> getData() {

        List<Map<String, String>> dataResult = new ArrayList<>();
        Map<String, String> u1 = new LinkedHashMap<>();
        u1.put("id", "1");
        u1.put("name", "cyf");
        u1.put("age", "21");
        Map<String, String> u2 = new LinkedHashMap<>();
        u2.put("id", "2");
        u2.put("name", "cy");
        u2.put("age", "22");
        dataResult.add(u1);
        dataResult.add(u2);
        return dataResult;
    }

}



-------------------------------------------------  以下为个人编写部分  -----------------------------------------------------



@Override
    public void exportMalfunctionExcels(@RequestParam(value = "beginUpdateTime", required = false) Long beginUpdateTime,
            @RequestParam(value = "lastUpdateTime", required = false) Long lastUpdateTime,
            @RequestParam(value = "deviceType", required = false) Integer deviceType,
            @RequestParam(value = "codeOrName", required = false) String codeOrName, HttpServletResponse response)
            throws Exception {
        String customerId = authManager.getCustomerId();
        List<AlertDeviceSite> malfunctionRecords = services.exportMalfunctionExcels(customerId, beginUpdateTime,
                lastUpdateTime, deviceType, codeOrName);

        List<ExcelExportEntity> entityList = new ArrayList<>();
        entityList.add(new ExcelExportEntity("单位编码", "site_code", 15));
        entityList.add(new ExcelExportEntity("单位名称", "site_name", 15));
        entityList.add(new ExcelExportEntity("设备编码", "device_code", 15));
        entityList.add(new ExcelExportEntity("设备名称", "device_name", 15));
        entityList.add(new ExcelExportEntity("故障类型", "deviceType", 15));
        entityList.add(new ExcelExportEntity("处理时间", "last_Update_Time", 15));

        Map<String, Object> dataResult;
        List<Map<String, Object>> malfunctionRecordList = new ArrayList<>();
        for (AlertDeviceSite malfunctionRecord : malfunctionRecords) {
            dataResult = new HashMap<String, Object>();
            dataResult.put("site_code", malfunctionRecord.getSiteCode());
            dataResult.put("site_name", malfunctionRecord.getSiteName());
            dataResult.put("device_code", malfunctionRecord.getDeviceCode());
            dataResult.put("device_name", malfunctionRecord.getDeviceName());
            if (null != malfunctionRecord.getDetectorId()) {

                dataResult.put("deviceType", "探测器");

            } else if (null != malfunctionRecord.getFacuId() && null == malfunctionRecord.getDetectorId()) {

                dataResult.put("deviceType", "控制器");

            } else if (null != malfunctionRecord.getRelayId() && null == malfunctionRecord.getFacuId()
                    && null == malfunctionRecord.getDetectorId()) {

                dataResult.put("deviceType", "传输器");

            }

            dataResult.put("last_Update_Time",
                    TimeUtils.timeStringFromLong(malfunctionRecord.getLastUpdateTime(), "yyyy-MM-dd HH:mm:ss", null));
            malfunctionRecordList.add(dataResult);
        }

        ExcelExportServer server = new ExcelExportServer();
        Workbook workbook = new HSSFWorkbook();

        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName("故障记录列表");
        server.createSheetForMap(workbook, exportParams, entityList, malfunctionRecordList);

        response.setCharacterEncoding("UTF-8");
        String filedisplay = "malfunctionRecord.xls";
        filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + filedisplay);

        OutputStream out = response.getOutputStream();
        workbook.write(out);
        out.close();

    }


注意:api部分:@GetMapping(value = "/api/v1/record/exportFireExcels", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值