api导入导出

api导入导出

一.api导出

1.1导入api依赖

 		
       <!--ecxel导入导出-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

1.2.excel导出工具类

public class ExcelExport<T> {
 
    /**
    *@Description:
    *@Parameter:
     * [ title  表格标题名
     * , headers  excel表格属性列名数组
     * , dataSet  需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的,javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
     * , excelName 要导出的excel名称
     * , pattern  如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     * , response] 使用response可以导出到浏览器
    *@Return: void
    *@Author: jxj
    *@Date: 2022/6/22
    **/

    @SneakyThrows
    @SuppressWarnings({"unchecked", "rawtypes"})
    public void exportExcel(String title, String[] headers,
                            Collection<T> dataSet, String excelName, String pattern, HttpServletResponse response) {

        ServletOutputStream outputStream = response.getOutputStream();

        response.reset();
        response.setContentType("application/vnd.ms-excel"); // 改成输出excel文件
        response.setHeader("Content-disposition",
                "attachment; filename=" +new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()).toString()+ new String(excelName.getBytes("gb2312"), "ISO-8859-1") + ".xls");
        // 声明一个工作薄，将1000条写入内存，其余在硬盘，以防止内存溢出
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        // 生成一个表格
        Sheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为10个字节
        sheet.setDefaultColumnWidth((short) 10);
        //设置表格头样式
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);//设置背景色
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        // 生成一个字体
        Font font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 产生表格标题行
        Row row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(style);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        // 设置表格内容样式
        CellStyle style2 = workbook.createCellStyle();
        style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 生成另一个字体
        Font font2 = workbook.createFont();
        font.setFontName("仿宋_GB2312");
        font.setColor((short) 64);//黑色
        // 把字体应用到当前的样式
        style2.setFont(font2);
        // 遍历集合数据，产生数据行
        Iterator<T> it = dataSet.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = (T) it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();
            for (short i = 0; i < fields.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellStyle(style2);
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                try {
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
                    Object value = getMethod.invoke(t, new Object[]{});
                    // 根据javabean的方法类型，判断值的类型后进行强制类型转换（具体需要自己根据情况修改）
                    String textValue = null;
                    //javabean里边的get和set方法为Integer时做处理
                    if (value instanceof Integer) {
                        int intValue = (Integer) value;
                        cell.setCellValue(intValue);
                    }
                    else if (value instanceof Double) {
                        double intValue = (Double) value;
                        cell.setCellValue(intValue);
                    }
                    else if (value instanceof Boolean) {
                        boolean bValue = (Boolean) value;
                        textValue = bValue ? "是" : "否";
                        cell.setCellValue(textValue);
                    }
                    else if (value instanceof Date) {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        textValue = sdf.format(date);
                        cell.setCellValue(textValue);
                    } else if (value instanceof byte[]) {
                        // 声明一个画图的顶级管理器
                        Drawing drawing= sheet.createDrawingPatriarch();
                        row.setHeightInPoints(60);
                        sheet.setColumnWidth(i, (short) (35.7 * 80));
                        byte[] bsValue = (byte[]) value;
                        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, i, index, i+1, index+1);
                        anchor.setAnchorType(ClientAnchor.AnchorType.byId(0));
                        drawing.createPicture(anchor, workbook.addPicture(bsValue, XSSFWorkbook.PICTURE_TYPE_JPEG));
                    } else {
                        if (value != null) {
                            textValue = value.toString();
                        } else {
                            textValue = "";
                        }
                        cell.setCellValue(textValue);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

1.3.实体类

/**
 * @ClassName -> Good
 * @Description
 * @Author JXJ
 * @Date 2022/6/22 9:51 星期三
 * @Version 1.0
 */
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class Good {
        private Integer goodsId;
        private String goodsName;
        private Double price;
        private Date effectiveDate;
        private String image;
        private String goodShow;
    }

1.4.controller

	/**
     * Excel文件下载服务
     */
    @GetMapping(value = "/exportExcel")
    public void exportExcel(HttpServletRequest request, HttpServletResponse response)  			{
            List<Good> goodList = goodMapper.selectList(new QueryWrapper<>());
            //得到所要导出的数据
            String str[] = new String[]{"商品id", "商品名称", "商品价格", "有效期", "商品图片", "是否显示"};
            ExcelExport<Good> export = new ExcelExport<Good>();
            export.exportExcel("测试导出工具类", str, goodList, "用户excel", "yyyy-MM-dd HH:mm:ss",response);
   		 }

二.api导入

11.excel导入工具类

package com.xxxx.seckill.util;
 
import com.xxxx.seckill.mapper.excel.FieldDao;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelImport {
  
    /**
     *
     * @param fileName 文件名称
     * @param is  文件流
     * @param pattern 返回日期格式
     * @param isExistTitle 是否存在表头
     * @param fieldList 表头（库里字段）
     * @param tableName 表名
     * @return map
     */
    public void getExcelRow(String fileName, InputStream is,String pattern, boolean isExistTitle,List<String>  fieldList,String tableName){
        HashMap<String, Object> mapHead = new HashMap<>();
        for (String field:fieldList) {
            mapHead.put(field," ");
        }
        //List<List<Object>> list = null;
        List<Map<String, Object>> list = new ArrayList<>();
        Map<Integer,List<List<Object>>> map = null;
        Workbook workbook = null;
        boolean isExcel03 = fileName.matches("^.+\\.(?i)(xls)$");
        boolean isExcel07 = fileName.matches("^.+\\.(?i)(xlsx)$");
        try {
            if (isExcel03) {
                workbook = new HSSFWorkbook(is);
            }else if (isExcel07) {

                workbook = new XSSFWorkbook(is);
            }
            int numberOfSheets = workbook.getNumberOfSheets();
            map = new HashMap<Integer,List<List<Object>>>();
            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheetAt = workbook.getSheetAt(i);
                int lastRowNum = sheetAt.getLastRowNum();//行数从0开始
                int firstRowNum = sheetAt.getFirstRowNum();//行数从0开始

                for (int j = 0; j <= lastRowNum; j++) {
                    if (j == 0 && isExistTitle) {
                       // j++;
                        continue;
                    }
                    Row row = sheetAt.getRow(j);
                    short lastCellNum = row.getLastCellNum();
                    //List<Object> li = new ArrayList<Object>();
                    HashMap<String, Object> hashMap = new HashMap<>();
                    for (int k = 0; k < lastCellNum; k++) {
                        //li.add(getCellValue(row.getCell(k), pattern));
                        hashMap.put(fieldList.get(k),(getCellValue(row.getCell(k), pattern)));
                    }
                    list.add(hashMap);
                }

            }
        } catch(Exception e){
            e.printStackTrace();
        }
        //批量插入
        Map<String, Object> batchList = new HashMap<>();
        batchList.put("tableName", tableName);
        batchList.put("mapHead",mapHead);
        batchList.put("list",list);
        System.out.println(list);
        fieldDao.insertTableListDynamic(batchList);
    }
 
    //判断行的值，是时间的转
    private Object getCellValue(Cell cell,String pattern){
        Object value = null;
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
        SimpleDateFormat sdf = new SimpleDateFormat(pattern);  //日期格式化
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                if("General".equals(cell.getCellStyle().getDataFormatString())){
                    value = df.format(cell.getNumericCellValue());
                }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
                    value = sdf.format(cell.getDateCellValue());
                }else{
                    value = df2.format(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case BLANK:
                value = "";
                break;
            default:
                break;
        }
        return value;
    }
}

1.2mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.xxxx.seckill.mapper.excel.FieldDao">

    <select id="findFieldByTableName" parameterType="java.lang.String" resultType="java.lang.String">
        SELECT COLUMN_NAME
        FROM information_schema.COLUMNS
        WHERE table_name = #{tableName}
          and table_schema = 'wlfy';
    </select>

    <insert id="insertTableListDynamic" parameterType="java.util.HashMap">
        insert into
        ${map.tableName}
        (
        <foreach collection="map.mapHead" index="key" item="value"
                 separator=",">
            `${key}`
        </foreach>
        )
        values
        <foreach collection="map.list" item="line" separator=",">
            (
            <foreach collection="line" index="key" item="value"
                     separator=",">
                #{value}
            </foreach>
            )
        </foreach>
    </insert>


</mapper>

1.3dao

@Repository
@Mapper
public interface FieldDao {

    List<String> findFieldByTableName(@Param("tableName") String tableName);
	
    void insertTableListDynamic(@Param("map") Map<String, Object> map);

}

1.4service

/**
     * 文件导入
     * @param
     * @param
     * @return
     */
    public void importExcel(MultipartFile file) {
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        String tableName = "good";
        List<String> fieldList = new ArrayList<String>() {{
            add("goods_id");
            add("goods_name");
            add("price");
            add("effective_date");
            add("image");
            add("good_show");
        }};
        ExcelImport excelImport = new ExcelImport();
        excelImport.getExcelRow(file.getOriginalFilename(), inputStream, "yyyy年MM月dd日",true,fieldList,tableName);
    }

1.5controller

	 /**
     * Excel文件上传服务
     */
    @Autowired
    private ExcelServiceImpl excelService;
    @PostMapping(value = "/importExcel")
    public void importExcel(MultipartFile file){
        excelImport.importExcel(file);

    }