java通用excel导入导出(适合n张表)

通用Excel导入导出模板

• 我们平时导入导出Excel都是一个Excel对应于一张表，操作多张表的时候不是很方便，所以本文就针对这一情况对导入导出功能进行优化:导入导出的excel可以对应多张表，excel的表头，数据可以是不固定的。通过数据库一张t_fieldorder表对excel导入导出字段进行控制。
• Excel工具类

package com.location.util;

import com.location.bjgwserver.domain.ExcelData;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;
import org.thymeleaf.util.StringUtils;

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

/**
 * @author csz
 * @date 2020/3/6 20:05
 */
public class ExportExcelUtils {

    /**
     * 下载文件
     * @param response
     * @param fileName
     * @param data
     * @throws Exception
     */
    public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
        response.reset();
        // 告诉浏览器用什么软件可以打开此文件
        response.addHeader("content-Type", "application/ms-excel;charset=utf-8");
        // 下载文件的默认名称
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
        exportExcel(data, response.getOutputStream());
    }

    /**
     * 创建 表格
     * @param data
     * @param out
     * @throws Exception
     */
    public static void exportExcel(ExcelData data, OutputStream out) throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            String sheetName = data.getName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            XSSFSheet sheet = wb.createSheet(sheetName);
            writeExcel(wb, sheet, data);
            wb.write(out);
        } finally {
        }
    }

    /**
     * 将数据写入表格
     * @param wb
     * @param sheet
     * @param data
     */
    private static void writeExcel(XSSFWorkbook wb, XSSFSheet sheet, ExcelData data) {
        int rowIndex = 0;
        rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
        writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
        autoSizeColumns(sheet, data.getTitles().size() + 1);
    }

    /**
     * 写入表头
     * @param wb
     * @param sheet
     * @param titles
     * @return
     */
    private static int writeTitlesToExcel(XSSFWorkbook wb, XSSFSheet sheet, List<String> titles) {
        int rowIndex = 0;
        int colIndex = 0;
        Font titleFont = wb.createFont();//获取字体
        titleFont.setFontName("simsun");//设置字体名称（宋体）
        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//设置字体加粗
        titleFont.setColor(IndexedColors.BLACK.getIndex());//设置字体颜色 黑色
        XSSFCellStyle titleStyle = wb.createCellStyle();//获取单元格样式
        titleStyle.setAlignment(HorizontalAlignment.CENTER);//设置单元格的水平对齐类型(这里是水平居中)
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置单元格的垂直对齐类型（这里是居中）
        titleStyle.setFillForegroundColor(createXssfColor("#FFFFFF"));//设置单元格前景色（白色）
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//指定图案和纯色单元格填充的单元格填充信息（实心前景）
        titleStyle.setFont(titleFont);//设置字体样式
        setBorder(titleStyle, BorderStyle.THIN, createXssfColor("#000000"));//设置边框样式（细线、黑色）
        Row titleRow = sheet.createRow(rowIndex);//在该工作簿中创建第一行.
        colIndex = 0;
        for (String field : titles) {//循环创建列
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle(titleStyle);
            colIndex++;
        }
        rowIndex++;//将行数++ 返回用于下面添加数据
        return rowIndex;
    }

    /**
     * 将数据写入
     * @param wb
     * @param sheet
     * @param rows
     * @param rowIndex
     * @return
     */
    private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
        int colIndex = 0;
        Font dataFont = wb.createFont();//获取字体
        dataFont.setFontName("simsun");//设置字体名称（宋体）
        dataFont.setColor(IndexedColors.BLACK.getIndex());//设置字体颜色 黑色
        XSSFCellStyle dataStyle = wb.createCellStyle();//获取单元格样式
        dataStyle.setAlignment(HorizontalAlignment.CENTER);//设置单元格的水平对齐类型(这里是水平居中)
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置单元格的垂直对齐类型（这里是居中）
        dataStyle.setFont(dataFont);//设置字体样式
        setBorder(dataStyle, BorderStyle.THIN, createXssfColor("#000000"));//设置边框样式（细线、黑色）
        for (List<Object> rowData : rows) {//循环写入数据
            Row dataRow = sheet.createRow(rowIndex);
            colIndex = 0;
            for (Object cellData : rowData) {
                Cell cell = dataRow.createCell(colIndex);
                if (cellData != null) {
                    cell.setCellValue(cellData.toString());
                } else {
                    cell.setCellValue("");
                }

                cell.setCellStyle(dataStyle);
                colIndex++;
            }
            rowIndex++;
        }
        return rowIndex;
    }

    /**
     * 自动调整大小
     * @param sheet
     * @param columnNumber
     */
    private static void autoSizeColumns(Sheet sheet, int columnNumber) {
        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            int newWidth = (int) (sheet.getColumnWidth(i) + 100);
            if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    /**
     * 设置表格样式
     * @param style
     * @param border
     * @param color
     */
    private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
        style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);
        style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);
    }

    /**
     * 将rgb颜色码 转换为 XSSFColor
     * @param color
     * @return
     */
    private static XSSFColor createXssfColor(String color) {
        int[] rgbColor = hexToRgb(color);
        XSSFColor xssfColor = new XSSFColor(new java.awt.Color(rgbColor[0], rgbColor[1], rgbColor[2]));
        return xssfColor;
    }

    /**
     * 将颜色码 转换为 r g b
     * @param hex
     * @return
     */
    public static int[] hexToRgb(String hex) {
        String colorStr = hex;
        if (hex.startsWith("#")) {
            colorStr = hex.substring(1);
        }
        if (StringUtils.length(colorStr) == 8) {
            colorStr = hex.substring(2);
        }
        int  r=  Integer.valueOf( colorStr.substring( 0, 2 ), 16 );
        int  g=  Integer.valueOf( colorStr.substring( 2, 4 ), 16 );
        int  b=  Integer.valueOf( colorStr.substring( 4, 6 ), 16 );

        return new int[] { r, g, b };
    }




}

• ExcelData.java 实体

package com.location.bjgwserver.domain;

import java.io.Serializable;
import java.util.List;
import java.util.Map;

/**
 * @author Thinkpad
 * @date 2020/3/6 20:06
 */
public class ExcelData implements Serializable {
    private static final long serialVersionUID = 4454016249210520899L;

    /**
     * 表头
     */
    private List<String> titles;

    /**
     * 数据
     */
    private List<List<Object>> rows;
    //private List<Map<String,String>> rows;

    /**
     * 页签名称
     */
    private String name;

    public static long getSerialVersionUID() {
        return serialVersionUID;
    }

    public List<String> getTitles() {
        return titles;
    }

    public void setTitles(List<String> titles) {
        this.titles = titles;
    }


    public List<List<Object>> getRows() {
        return rows;
    }

    public void setRows(List<List<Object>> rows) {
        this.rows = rows;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

• ExcelController 控制层

package com.location.bjgwserver.controller;


import com.location.bjgwserver.domain.ExcelData;
import com.location.bjgwserver.service.ExcelService;
import com.location.general.domain.ResultCode;
import com.location.util.ExportExcelUtils;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

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

/**
 * Excel控制层
 * @author csz
 * @date 2020/3/10
 */

@RestController
@RequestMapping("/services/excel")
public class ExcelController {


    @Resource
    private ExcelService excelService;



    /**
     * 导入Excelexcel
     * @param excelFile
     * @author csz
     * @data 2020/3/5
     * @return
     */
    @RequestMapping("/importExcel")
    @ResponseBody
    public ResultCode importExcel(MultipartFile excelFile,String tableName) {

        try {
            excelService.importExcel(excelFile,tableName);
            return ResultCode.success("添加成功");
        } catch (Exception e) {
            e.printStackTrace();
            return ResultCode.errorParam("添加失败！");
        }

    }


    /**
     * 导出Excelexcel
     * @param tableName   传入要导出的表名
     * @param fileName   传入要导出的文件名
     * @param guides    传入要导出的guides
     * @author csz
     * @data 2020/3/11
     * @return
     */
    @RequestMapping(value = "/exportExcel", method = RequestMethod.GET)
    public void exportExcel(HttpServletResponse response,String tableName, String fileName, @RequestParam  String guides) throws Exception {

        //添加excel  sheet名字
        ExcelData data = new ExcelData();
        data.setName("sheet1");

        //根据表名获取标题名称
        List<String> titles = excelService.findTitlesByTableName(tableName);
        data.setTitles(titles);

        //根据表名获取字段值和数据库对应
        List<Map<String,Object>> fields = excelService.findFieldByTableName(tableName);

        //代表整个excel
        List<List<Object>> rows = new ArrayList();

        //根据guid获取所有要导出的信息
        List<Map<String,Object>> ExcelList = excelService.findExcelByIdAndTableName(tableName,guides.split(","));
        for(Map<String,Object> map : ExcelList ){
            //代表excel的每一行
            List<Object> row1 = new ArrayList();
            for (Map<String, Object> map1 : fields) {
                String field_name = map1.get("field_name").toString();
                String dataValue = map.get(field_name).toString();
                row1.add(dataValue);
            }
            //将每一行添加到excel中
            rows.add(row1);
        }
        //将整个excel添加到ExcelData实体中
        data.setRows(rows);
        //通过excel工具类将创建好的excel导出
        ExportExcelUtils.exportExcel(response,fileName+".xlsx",data);
    }

}

• ExcelService.java 业务层

package com.location.bjgwserver.service;


import com.location.bjgwserver.domain.FieldOrderEntity;
import com.location.bjgwserver.mapper.ExcelMapper;
import com.location.bjgwserver.mapper.FieldOrderMapper;
import org.apache.poi.ss.usermodel.Cell;
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.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * Excel业务层
 * @author csz
 * @date 2020/3/10
 */
@Service
public class ExcelService {


    @Resource
    private ExcelMapper excelMapper;

    @Resource
    private FieldOrderMapper fieldOrderMapper;

    /**
     * 根据表名查询excel第一行的title
     * @return
     */
    public List<String> findTitlesByTableName(String tableName) {

        List<String> list = new ArrayList();
        //查询t_fieldorder表中field_alias_name的值
        List<FieldOrderEntity> tFieldOrderList = fieldOrderMapper.findTitlesByTableName(tableName);
        for(int i=0;i<tFieldOrderList.size();i++){
            //遍历集合查询大集合中field_name的值，并将其存入新的集合中
            list.add(tFieldOrderList.get(i).getField_alias_name());
        }
        return list;
    }


    /**
     * 根据表名查询字段值和数据库字段对应
     * @return
     */
    public List<Map<String,Object>> findFieldByTableName(String tableName) {

        //查询t_fieldorder表中field_name的值
        String  sql = "SELECT  *  FROM  t_fieldorder where table_name "+" = "  +"'"+tableName+"'" + " ORDER BY id" ;
        List<Map<String,Object>>  list = excelMapper.findFieldByTableName(sql);
        return list;
    }


    /**
     * 根据表名,guides查询需要导出的数据
     * @return
     */
    public List<Map<String,Object>> findExcelByIdAndTableName(String tableName,String[] guides) {
        String guid = "";

        //加工in()后边的字符串，使其匹配in('0001','0002')这种格式
        for(int i=0;i<guides.length;i++){
             guid = guid+"'"+guides[i]+"'"+",";
        }
        //截取字符串最后的逗号
        guid = guid.substring(0,guid.length()-1);

        //查询需要导出的信息
        String  sql = "SELECT  *  FROM  "+""+tableName+"" +" where guid  "+" in(" +guid+")" ;
        List<Map<String,Object>> list = excelMapper.findExcelByIdAndTableName(sql);
        return list;

    }



    /**
     * 导入Excelexcel
     * @author csz
     * @data 2020/3/10
     */

    public void importExcel(MultipartFile testFile,String tableName) {

        try {
            //获取文件的输入流
            InputStream inputStream = testFile.getInputStream();
            //根据不同类型excel创建book页。
            String fileName = testFile.getOriginalFilename();

            XSSFWorkbook book = new XSSFWorkbook(inputStream);
            if(book != null){
                //第一个工作簿
                XSSFSheet sheet = book.getSheetAt(0);
                //将结果转换成集合
                List<Map<String,Object>> Excels = convert(sheet,tableName);
                for (Map<String,Object> map : Excels) {
                    //map.put("table_name",tableName);
                    excelMapper.importExcel(map,tableName);
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }



       /**
     　*　　将每行数据封装成一个对象
     　*/

    private List<Map<String,Object>> convert(XSSFSheet sheet,String tableName){
        List<Map<String,Object>> ExcelEntityList = new ArrayList<>();

        for (int i = 1; i <= sheet.getLastRowNum() ; i++) {
            //第一行跳过，字段名，从第2行开始
            XSSFRow row = sheet.getRow(i);
            Map<String,Object> excel = new HashMap<>();
            String guid = UUID.randomUUID().toString().replace("-", "");
            excel.put("guid",guid);

            //查询t_fieldorder表中field_name的值
            String  sql = "SELECT  *  FROM  t_fieldorder where table_name "+" = "  +"'"+tableName+"'" + " AND is_export > 0 ORDER BY is_export" ;
            List<Map<String,Object>>  list = excelMapper.findFieldByTableName(sql);
            Iterator<Cell> iterator = row.cellIterator();
            while (iterator.hasNext()){
                Cell cell = iterator.next();

                for(int j =0 ;j<list.size(); j++){
                    if(cell.getColumnIndex() == j){
                        //循环插入
                        switch(list.get(j).get("type").toString())
                        {
                            case "string" :
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                break;
                            case "float" :
                                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                                break;
                            case "boolean" :
                                cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
                                break;
                            default :
                                System.out.println("未知格式");
                        }
                        excel.put(list.get(j).get("field_name").toString(),cell.getStringCellValue());
                    }
                }


            }
            ExcelEntityList.add(excel);
        }
        return ExcelEntityList;
    }




}

• ExcelMapper.java dao层

package com.location.bjgwserver.mapper;


import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * Exceldao层
 * @author csz
 * @date 2020/3/17
 */

@Repository
public interface ExcelMapper  {


    //根据表名查询字段名称
    List<Map<String,Object>> findFieldByTableName(@Param(value="value") String sql);


    //查询台账信息根据名称
    List<Map<String,Object>> findExcelByIdAndTableName(@Param(value="value") String sql);


    //导入Excel
    void importExcel(@Param(value="map") Map<String,Object> map,@Param(value="table_name") String tableName);



}

• ExcelMapper.XML 操作数据库

<?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.location.bjgwserver.mapper.ExcelMapper">


    <select id="findFieldByTableName" resultType="java.util.Map">
        ${value}
    </select>


    <select id="findExcelByIdAndTableName" resultType="java.util.Map">
        ${value}
    </select>


    <insert id="importExcel"  >
    insert into ${table_name}
    	<!-- map表示dao层的参数, key表示map的键,value表示map的值-->
    	<!-- 利用foreach对map进行循环,取出里面的值-->
        <foreach item="value" collection="map" index="key" open="("   separator="," close=")">
            ${key}
        </foreach>
    values
        <foreach item="value" collection="map" index="key"  open="("   separator="," close=")">
            #{value}
        </foreach>
    </insert>

</mapper>

• 数据库t_fieldorder结构