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

- 日理万妓 2023-07-16 05:58 5阅读 0赞

## 通用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}
        	
        	
            <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结构  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21hc3NkZHNz_size_16_color_FFFFFF_t_70]  
    ![在这里插入图片描述][2020031809382538.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21hc3NkZHNz_size_16_color_FFFFFF_t_70]: /images/20230528/4673c1357afb4da5b9dc4e5790396ef0.png
[2020031809382538.png]: /images/20230528/c265092ac15e453e809e33de68d3f79a.png