依赖POI实现EXCEL导入数据并生成javaBean和EXCEL根据数据库表导出

首先是excel导入导出的工具类代码

public class ExportExcel {
    // 测试123
    private ExportExcel() {

    }

    /***
     * 工作簿
     */
    private static HSSFWorkbook workbook;

    /***
     * sheet
     */
    private static HSSFSheet sheet;

    /***
     * 表头行开始位置
     */
    private static final int HEAD_START_POSITION = 0;

    /***
     * 文本行开始位置
     */
    private static final int CONTENT_START_POSITION = 1;

    /**
     * 
     * @param dataList
     *            对象集合
     * @param titleMap
     *            表头信息（对象属性名称->要显示的标题值)[按顺序添加]
     * @param sheetName
     *            sheet名称和表头值
     */
    public static void excelExport(List<?> dataList, Map<String, String> titleMap, String sheetName, String ExcelPath) {
        // 初始化workbook
        initHSSFWorkbook(sheetName);
        // 表头行
        createHeadRow(titleMap);
        // 文本行
        createContentRow(dataList, titleMap);
        // 写入处理结果
        try {
            String filedisplay = sheetName;
            // 如果web项目，1、设置下载框的弹出（设置response相关参数)；2、通过httpservletresponse.getOutputStream()获取
            OutputStream out = new FileOutputStream(ExcelPath + filedisplay);
            workbook.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /***
     * 
     * @param sheetName
     *            sheetName
     */
    private static void initHSSFWorkbook(String sheetName) {
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet(sheetName);
    }

    /**
     * 创建表头行
     * 
     * @param titleMap
     *            对象属性名称->表头显示名称
     */
    private static void createHeadRow(Map<String, String> titleMap) {
        // 第1行创建
        HSSFRow headRow = sheet.createRow(HEAD_START_POSITION);
        int i = 0;
        Set<Entry<String, String>> entrySet = titleMap.entrySet();
        for (Entry<String, String> entry : entrySet) {
            HSSFCell headCell = headRow.createCell(i);
            headCell.setCellValue(entry.getValue());
            i++;
        }
    }

    /**
     * 
     * @param dataList
     *            对象数据集合
     * @param titleMap
     *            表头信息
     */
    private static void createContentRow(List<?> dataList, Map<String, String> titleMap) {
        try {
            int i = 0;
            for (Object obj : dataList) {
                HSSFRow textRow = sheet.createRow(CONTENT_START_POSITION + i);
                int j = 0;
                for (String entry : titleMap.keySet()) {
                    String method = "get" + entry.substring(0, 1).toUpperCase() + entry.substring(1);
                    Method m = obj.getClass().getMethod(method, null);
                    String value;
                    if (m.invoke(obj, null) == null) {
                        value = "";
                    } else {
                        value = m.invoke(obj, null).toString();
                    }
                    HSSFCell textcell = textRow.createCell(j);
                    textcell.setCellValue(value);
                    j++;
                }
                i++;
            }

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

    public static List<Map<String, Object>> loadExcel(String filepath, int count) {
        // 创建Excel工作簿文件的引用
        HSSFWorkbook wookbook = null;
        try {
            wookbook = new HSSFWorkbook(new FileInputStream(filepath));// 根据路劲创建引用
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        // 在excel文档中，第一个工作表的缺省索引是0
        HSSFSheet sheet = wookbook.getSheetAt(count);
        // 获取到excel文件中的所有行数
        int rows = sheet.getPhysicalNumberOfRows();
        List<Map<String, Object>> li = new ArrayList<Map<String, Object>>();
        // boolean boo = false;
        for (int i = 1; i < rows; i++) {
            HSSFRow row = sheet.getRow(i);

            if (row != null) {
                // 获取文件中的所有列
                int cells = row.getPhysicalNumberOfCells();
                Map<String, Object> map = new HashMap<>();
                // 遍历列
                for (int j = 0; j < cells; j++) {
                    HSSFCell cell = row.getCell((short) j);
                    if (cell != null) {
                        HSSFRow title = sheet.getRow(0);
                        HSSFCell titleCell = title.getCell(cell.getColumnIndex());
                        map.put(titleCell.getStringCellValue(), cell.getStringCellValue());
                    }
                }
                li.add(map);
            }
        }
        return li;
    }

}

下面是调用服务代码

@Service
@Transactional
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    private GoodsMapper goodsMapper;

    @Autowired
    private GoodsKindMapper goodsKindMapper;

    @Value("${SheetName}")
    private String SheetName;

    @Value("${ExcelPath}")
    private String ExcelPath;

    @Value("${UploadPath}")
    private String UploadPath;

    @Override
    public void export() throws Exception {
        // TODO Auto-generated method stub
        GoodsExample example=new GoodsExample();
        List<Goods> list = goodsMapper.selectByExample(example);
        Map<String, String> titleMap=getTitleMap();
        ExportExcel.excelExport(list, titleMap, SheetName,ExcelPath);
    }


    private Map<String, String> getTitleMap() {
        // TODO Auto-generated method stub
        Map<String, String> titleMap=new TreeMap<String, String>(new Comparator<String>() {
            @Override
            public int compare(String o1, String o2) {
                // TODO Auto-generated method stub
                return 1;
            }
        });
        titleMap.put("id","id" );
        titleMap.put("title","title" );
        titleMap.put("sellPoint","sellPoint" );
        titleMap.put("price","price" );
        titleMap.put("num","num" );
        titleMap.put("barcode","barcode" );
        titleMap.put("image","image" );
        titleMap.put("kid","kid" );
        titleMap.put("status","status" );
        titleMap.put("createDate","createDate" );
        titleMap.put("created","created" );
        titleMap.put("updated","updated" );
        return titleMap;
    }

    @Override
    @Transactional
    public void importExcel(String string, int i) throws Exception {
        // TODO Auto-generated method stub
        List<Map<String, Object>> loadExcel = ExportExcel.loadExcel(string, i);
        List<Goods> goods=new ArrayList<>();
        for (Map<String, Object> map : loadExcel) {
            Goods good=new Goods();
            good.setId(Long.parseLong(map.get("id").toString()));
            good.setTitle((String) map.get("title"));
            good.setSellPoint((String) map.get("sellPoint"));
            good.setPrice(Long.parseLong(map.get("price").toString()));
            good.setNum(Integer.parseInt(map.get("num").toString()));
            good.setBarcode((String) map.get("barcode"));
            good.setImage((String) map.get("image"));
            good.setKid(Long.parseLong(map.get("kid").toString()));
            good.setStatus(Byte.parseByte(map.get("status").toString()));
            good.setCreateDate(map.get("createDate").toString());
            good.setCreated(new Date(map.get("created").toString()));
            good.setUpdated(new Date(map.get("updated").toString()));
            goods.add(good);
        }
        goodsMapper.insertList(goods);
    }


    @Override
    public void buyImportExcel(String string, int i) throws Exception {
        // TODO Auto-generated method stub
        List<Map<String, Object>> loadExcel = ExportExcel.loadExcel(string, i);
        List<Goods> goods=new ArrayList<>();
        for (Map<String, Object> map : loadExcel) {
            Goods good=new Goods();
            good.setId(IDUtils.genItemId());
            good.setTitle((String) map.get("title"));
            good.setSellPoint((String) map.get("sellPoint"));
            good.setPrice(Long.parseLong(map.get("price").toString()));
            good.setNum(Integer.parseInt(map.get("num").toString()));
            good.setBarcode((String) map.get("barcode"));
            if(map.get("kname")!=null){
                String name = map.get("kname").toString().trim();
                GoodsKindExample example=new GoodsKindExample();
                Criteria createCriteria = example.createCriteria();
                createCriteria.andNameEqualTo(name);
                List<GoodsKind> list = goodsKindMapper.selectByExample(example);
                if(list!=null&&list.size()!=0){
                    for (GoodsKind goodsKind : list) {
                        if(!goodsKind.getIsParent()){
                            good.setKid(goodsKind.getId());
                        }
                    }
                }
            }
            good.setCreateDate(map.get("createDate").toString());
            good.setStatus(Byte.parseByte("1"));
            Date date = new Date();
            good.setCreated(date);
            good.setUpdated(date);
            goods.add(good);
        }
        goodsMapper.insertList(goods);
    }
}

getTitleMap方法中的map对应的是excel表格导出中的第一列列名

下面是导入和导出的表格样式

数据库表结构