Excel导入导出

 Excel导入

• 工具类

import com.znfx.stockholder.api.exception.ExcelException;
import lombok.Getter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Getter
public class ReadExcel {
    // 总行数
    private final int totalRows = 0;
    // 总条数
    private final int totalCells = 0;

    /**
     * 读EXCEL文件，获取信息集合
     *
     * @param file :上传excel文件
     * @return list
     */
    public List<Map<String, String>> getExcelInfo(File file, String fileName) {
        List<Map<String, String>> list = null;
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            assert fileName != null;
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            list = createExcel(new FileInputStream(file), isExcel2003);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 根据excel里面的内容读取客户信息
     *
     * @param is:输入流
     * @param isExcel2003 excel是2003还是2007版本
     * @return list
     */
    public List<Map<String, String>> createExcel(InputStream is, boolean isExcel2003) {
        List<Map<String, String>> list = null;
        try {
            Workbook wb;
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            list = readExcelValue(wb);// 读取Excel里面客户的信息
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 读取Excel里面客户的信息
     *
     * @param wb:文件内容
     * @return list
     */
    public List<Map<String, String>> readExcelValue(Workbook wb) {
        List<Map<String, String>> list = new ArrayList<>();
        try {
            // sheet 从0开始
            Sheet sheet = wb.getSheetAt(0);
            // 取得最后一行的行号
            int rowNum = sheet.getLastRowNum() + 1;
            Row rowTitle = sheet.getRow(0);
            // 标题行的最后一个单元格位置
            int cellTitleNum = rowTitle.getLastCellNum();
            String[] title = new String[cellTitleNum];
            for (int i = 0; i < cellTitleNum; i++) {
                Cell cell = rowTitle.getCell(Short.parseShort(i + ""));
                if (cell != null) {
                    cell.setCellType(CellType.STRING);
                    title[i] = cell.getStringCellValue();
                } else {
                    title[i] = "";
                }
            }
            // 行循环开始
            int i = 1;
            while (i < rowNum) {
                Map<String, String> map = new HashMap<>();
                // 行
                Row row = sheet.getRow(i);
                // 列循环开始
                int j = 0;
                while (j < cellTitleNum) {
                    Cell cell = row.getCell(Short.parseShort(j + ""));
                    String cellValue = "";
                    if (cell != null) {
                        // 把类型先设置为字符串类型
                        cell.setCellType(CellType.STRING);
                        cellValue = cell.getStringCellValue();
                    }
                    map.put(title[j], cellValue);
                    j++;
                }
                list.add(map);
                i++;
            }
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath：文件路径
     */
    public boolean validateExcel(String filePath) throws ExcelException {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            throw new ExcelException("文件名不是excel格式");
        }
        return true;
    }

    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    // @描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }
}

• controller 

package com.znfx.stockholder.stockholder.excel;

import com.znfx.stockholder.api.stockholder.stockholder.createStockholder.CreateStockholderCommand;
import com.znfx.stockholder.base.DomainCommandProcessorManager;
import com.znfx.stockholder.base.SpringContextUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.util.List;
import java.util.Map;
import java.util.Scanner;

@Slf4j
@RestController
public class ExcelController {
    private final DomainCommandProcessorManager processorManager;

    @Autowired
    public ExcelController(DomainCommandProcessorManager processorManager) {
        this.processorManager = processorManager;
    }

    @RequestMapping("/import")
    public void main() throws Exception {
        File file = new File("E:\\file.xlsx");
        ExcelController controller = SpringContextUtil.getApplicationContext().getBean(ExcelController.class);
        controller.impExcel(file);
    }

    /**
     * 导入excel模板
     */
    @RequestMapping(value = "/impExcel", method = RequestMethod.POST)
    @ResponseBody
    public List<Map<String, String>> impExcel(File file) throws Exception {
        // 创建处理EXCEL的类
        ReadExcel readExcel = new ReadExcel();
        // 解析excel，获取上传的事件单
        List<Map<String, String>> list = readExcel.getExcelInfo(file, file.getName());
        // 至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作
        if (list != null && !list.isEmpty()) {
            for (Map<String, String> m : list) {
                ReadExcel.toString(m);
                Integer sex = ReadExcel.getSex(m);
                String national = ReadExcel.getNational(m);
                String politic = ReadExcel.getPolitic(m);
                Boolean expiryDateEndLongTerm = ReadExcel.getBoolean(m);
                for (String key : m.keySet()) {
                    String value = m.get(key);
                    System.out.println(key + ":" + value);
                }
                System.out.println("请确定是否要创建股东:" + m.get("姓名") + ",创建请任意输入，点击回车");
                Scanner scanner = new Scanner(System.in);
                scanner.nextLine();
                // 设置模板的表头
                CreateStockholderCommand command = CreateStockholderCommand.build(m.get("姓名"), m.get("身份证号"),
                        m.get("电话"), sex, national, politic, Long.valueOf(m.get("出生日期")), m.get("学历"),
                        m.get("开户行"), m.get("银行卡号"), m.get("工作单位"), m.get("职务"), m.get("住址"), m.get("邮箱"),
                        m.get("邮寄地址"), Long.valueOf(m.get("身份证失效日期起")), Long.valueOf(m.get("身份证失效日期止")),
                        expiryDateEndLongTerm);
                this.processorManager.process(command);
                System.out.println("股东" + m.get("姓名") + "创建成功");
            }
            System.out.println("该excel表格中的股东已创建完毕");
        }
        return list;
    }
}

 Excel导出

• 工具类

package com.znfx.scale.util;

import com.znfx.scale.entity.dto.ExcelData;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

/**
 * 导出Excel
 */
@Slf4j
public class ExcelUtil {
    public static void exportExcel(HttpServletResponse response, ExcelData data) {
        log.info("导出解析开始，fileName:{}", data.getFileName());
        try {
            //实例化HSSFWorkbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建一个Excel表单，参数为sheet的名字
            HSSFSheet sheet = workbook.createSheet("sheet");
            //设置表头
            setTitle(workbook, sheet, data.getHead());
            //设置单元格并赋值
            setData(sheet, data.getData());
            //设置浏览器下载
            setBrowser(response, workbook, data.getFileName());
            log.info("导出解析成功!");
        } catch (Exception e) {
            log.info("导出解析失败!");
            e.printStackTrace();
        }
    }

    /**
     * 方法名：setTitle
     * 功能：设置表头
     */
    private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
        try {
            HSSFRow row = sheet.createRow(0);
            //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
            for (int i = 0; i <= str.length; i++) {
                sheet.setColumnWidth(i, 15 * 256);
            }
            //设置为居中加粗,格式化时间格式
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setFont(font);
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd"));
            //创建表头名称
            HSSFCell cell;
            for (int j = 0; j < str.length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(str[j]);
                cell.setCellStyle(style);
            }
        } catch (Exception e) {
            log.info("导出时设置表头失败！");
            e.printStackTrace();
        }
    }

    /**
     * 方法名：setData
     * 功能：表格赋值
     */
    private static void setData(HSSFSheet sheet, List<String[]> data) {
        try {
            int rowNum = 1;
            for (int i = 0; i < data.size(); i++) {
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = 0; j < data.get(i).length; j++) {
                    row.createCell(j).setCellValue(data.get(i)[j]);
                }
                rowNum++;
            }
            log.info("表格赋值成功！");
        } catch (Exception e) {
            log.info("表格赋值失败！");
            e.printStackTrace();
        }
    }

    /**
     * 方法名：setBrowser
     * 功能：使用浏览器下载
     */
    private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
        try {
            //清空response
            response.reset();
            //设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" +
                    URLEncoder.encode(fileName, "UTF-8") + ".xls");
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            //将excel写入到输出流中
            workbook.write(os);
            os.flush();
            os.close();
            log.info("设置浏览器下载成功！");
        } catch (Exception e) {
            log.info("设置浏览器下载失败！");
            e.printStackTrace();
        }

    }
}

•  service实现类

package com.znfx.scale.service.impl;

import com.znfx.scale.entity.dto.ExcelData;
import com.znfx.scale.entity.dto.StockDto;
import com.znfx.scale.entity.dto.StockInDto;
import com.znfx.scale.entity.dto.StockOutDto;
import com.znfx.scale.mapper.StockInMapper;
import com.znfx.scale.mapper.StockMapper;
import com.znfx.scale.mapper.StockOutMapper;
import com.znfx.scale.service.ExcelExportService;
import com.znfx.scale.util.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@Slf4j
@Service
public class ExcelExportServiceImpl implements ExcelExportService {

    @Resource
    private StockInMapper stockInMapper;

    @Resource
    private StockOutMapper stockOutMapper;

    @Resource
    private StockMapper stockMapper;

    /**
     * 导出入库表
     */
    public Boolean exportStockInExcel(HttpServletResponse response, String fileName, Integer sessid, Integer store) {
        log.info("导出数据开始..............");
        //查询数据并赋值给ExcelData
        List<StockInDto> inDtoList = stockInMapper.selectAllStockIn(sessid, store);
        List<String[]> list = new ArrayList<>();
        for (StockInDto dto : inDtoList) {
            String[] arrs = new String[7];
            arrs[0] = String.valueOf(dto.getStoreName());
            arrs[1] = String.valueOf(dto.getKindName());
            arrs[2] = String.valueOf(dto.getLevelName());
            arrs[3] = String.valueOf(dto.getNumber());
            arrs[4] = String.valueOf(dto.getPrice());
            arrs[5] = String.valueOf(dto.getOrigin());
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String dateString = simpleDateFormat.format(dto.getDate());
            arrs[6] = dateString;
            list.add(arrs);
        }
        //表头赋值
        String[] head = {"仓库名", "品种名", "品级名", "入库量", "价格", "产地", "入库日期"};
        ExcelData data = new ExcelData();
        data.setHead(head);
        data.setData(list);
        data.setFileName(fileName);
        //实现导出
        try {
            ExcelUtil.exportExcel(response, data);
            log.info("导出数据结束..............");
            return true;
        } catch (Exception e) {
            log.info("导出数据失败..............");
            return false;
        }
    }

    @Override
    public Boolean exportStockOutExcel(HttpServletResponse response, String fileName, Integer sessid, Integer store) {
        //查询数据并赋值给ExcelData
        List<StockOutDto> outDtoList = stockOutMapper.selectAllStockOut(sessid, store);
        List<String[]> list = new ArrayList<>();
        for (StockOutDto dto : outDtoList) {
            String[] arrs = new String[7];
            arrs[0] = String.valueOf(dto.getStoreName());
            arrs[1] = String.valueOf(dto.getKindName());
            arrs[2] = String.valueOf(dto.getLevelName());
            arrs[3] = String.valueOf(dto.getNumber());
            arrs[4] = String.valueOf(dto.getPrice());
            arrs[5] = String.valueOf(dto.getAddress());
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String dateString = simpleDateFormat.format(dto.getDate());
            arrs[6] = dateString;
            list.add(arrs);
        }
        //表头赋值
        String[] head = {"仓库名", "品种名", "品级名", "出库量", "价格", "目的地", "出库日期"};
        ExcelData data = new ExcelData();
        data.setHead(head);
        data.setData(list);
        data.setFileName(fileName);
        //实现导出
        try {
            ExcelUtil.exportExcel(response, data);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    @Override
    public Boolean exportStockExcel(HttpServletResponse response, String fileName, Integer sessid, Integer store) {
        //查询数据并赋值给ExcelData
        List<StockDto> dtoList = stockMapper.selectAllStock(sessid, store);
        List<String[]> list = new ArrayList<>();
        for (StockDto dto : dtoList) {
            String[] arrs = new String[5];
            arrs[0] = String.valueOf(dto.getStoreName());
            arrs[1] = String.valueOf(dto.getKindName());
            arrs[2] = String.valueOf(dto.getLevelName());
            arrs[3] = String.valueOf(dto.getNumber());
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            String dateString = simpleDateFormat.format(dto.getDate());
            arrs[4] = dateString;
            list.add(arrs);
        }
        //表头赋值
        String[] head = {"仓库名", "品种名", "品级名", "库存量", "出库日期"};
        ExcelData data = new ExcelData();
        data.setHead(head);
        data.setData(list);
        data.setFileName(fileName);
        //实现导出
        try {
            ExcelUtil.exportExcel(response, data);
            return true;
        } catch (Exception e) {
            return false;
        }
    }
}

• controller

package com.znfx.scale.controller;

import com.znfx.scale.service.ExcelExportService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

@Slf4j
@RestController
@RequestMapping("/export")
public class ExcelExportController {

    @Resource
    private ExcelExportService excelService;

    /**
     * 导出入库信息
     *
     * @param response response
     * @param sessid   用户id
     * @param store    仓库id
     * @return Excel
     */
    @RequestMapping("/in")
    public String exportStockInExcel(HttpServletResponse response, Integer sessid, Integer store) {
        Boolean isOk = excelService.exportStockInExcel(response, "入库信息表", sessid, store);
        if (isOk) {
            return "导出成功！";
        } else {
            return "导出失败！";
        }
    }

    @RequestMapping("/out")
    public String exportStockOutExcel(HttpServletResponse response, Integer sessid, Integer store) {
        Boolean isOk = excelService.exportStockOutExcel(response, "出库信息表", sessid, store);
        if (isOk) {
            return "导出成功！";
        } else {
            return "导出失败！";
        }
    }

    @RequestMapping("/stock")
    public String exportStockExcel(HttpServletResponse response, Integer sessid, Integer store) {
        Boolean isOk = excelService.exportStockExcel(response, "库存信息表", sessid, store);
        if (isOk) {
            return "导出成功！";
        } else {
            return "导出失败！";
        }
    }
}