POI：EXCEL导出与追加

一、 导出excel

    /**
     * 导出excel
     * @param dataList 数据
     * @param name 文件名称
     */
    public static void ExportExecl(List<LinkedHashMap<String,Object>> dataList, String name){
        LinkedHashMap<String,Object> titleList = dataList.get(0);
        // 创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建HSSFSheet对象
        HSSFSheet sheet = wb.createSheet(name);
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        // -----------------------------数据填充 ---------------------------------
        // 设置标题
        HSSFRow titleRow = sheet.createRow(0);
        HSSFCell titleCell=titleRow.createCell(0);
        titleCell.setCellValue(name);
        titleCell.setCellStyle(cellStyle);
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,titleList.size()-1));
        // 设置列名称
        HSSFRow cellTitleRow = sheet.createRow(1);
        AtomicInteger jt = new AtomicInteger();
        LinkedHashMap<String ,Object> tem = dataList.get(0);
        tem.forEach((k,v)-> {
            HSSFCell cell=cellTitleRow.createCell(jt.get());
            cell.setCellValue(k);
            cell.setCellStyle(cellStyle);
            jt.getAndIncrement();
        });
        // 填充sql结果
        for (int i = 0; i < dataList.size(); i++){
            HSSFRow row = sheet.createRow(i+2);
            AtomicInteger j = new AtomicInteger();
            LinkedHashMap<String ,Object> ltem = dataList.get(i);
            ltem.forEach((k,v)-> {
                HSSFCell cell=row.createCell(j.get());
                cell.setCellValue(v.toString());
                cell.setCellStyle(cellStyle);
                j.getAndIncrement();
            });
        }
        // -----------------------------------------------------------------------
        // 输出Excel文件
        try {
            FileOutputStream output = new FileOutputStream("d:\\"+name+".xls");
            wb.write(output);
            output.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

二、 追加到excel

    /**
     * 追加到已有excel
     * @param dataList 数据
     * @param name 文件名
     */

    public static void addExcel(List<LinkedHashMap<String,Object>> dataList, String name) throws IOException {
        FileInputStream fileInputStream=new FileInputStream("d://"+name+".xls");  //获取d://test.xls,建立数据的输入通道
        POIFSFileSystem poifsFileSystem=new POIFSFileSystem(fileInputStream);  //使用POI提供的方法得到excel的信息
        HSSFWorkbook Workbook=new HSSFWorkbook(poifsFileSystem);//得到文档对象
        HSSFSheet sheet=Workbook.getSheet(name);  //根据name获取sheet表
        HSSFCellStyle cellStyle = Workbook.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        // HSSFRow row=sheet.getRow(0);  //获取第一行
        System.out.println("最后一行的行号 :"+sheet.getLastRowNum() + 1);  //分别得到最后一行的行号，和第3条记录的最后一个单元格
        System.out.println("最后一个单元格 :"+row.getLastCellNum());  //分别得到最后一行的行号，和第3条记录的最后一个单元格
        // HSSFRow startRow=sheet.createRow((short)(sheet.getLastRowNum()+1)); // 追加开始行
        // -----------------追加数据-------------------
        int start = sheet.getLastRowNum() + 1; //插入数据开始行
        for (int i = 0; i < dataList.size(); i++){
            HSSFRow startRow = sheet.createRow(i+start);
            AtomicInteger j = new AtomicInteger();
            LinkedHashMap<String ,Object> ltem = dataList.get(i);
            ltem.forEach((k,v)-> {
                HSSFCell cell=startRow.createCell(j.get());
                cell.setCellValue(v.toString());
                cell.setCellStyle(cellStyle);
                j.getAndIncrement();
            });
        }
        // 输出Excel文件
        FileOutputStream out=new FileOutputStream("d://"+name+".xls");  //向d://test.xls中写数据
        out.flush();
        Workbook.write(out);
        out.close();
    }

三、读取excel

/**
     * 读取excel
     * @param dataList 数据
     * @param name 文件名
     */

    public static void readExcel(String name) throws IOException {
        FileInputStream fileInputStream=new FileInputStream("d:\\"+name+".xls");  //获取d://test.xls,建立数据的输入通道
        POIFSFileSystem poifsFileSystem=new POIFSFileSystem(fileInputStream);  //使用POI提供的方法得到excel的信息
        HSSFWorkbook Workbook=new HSSFWorkbook(poifsFileSystem);//得到文档对象
        HSSFSheet sheet=Workbook.getSheet(name);  //根据name获取sheet表
        HSSFRow row=sheet.getRow(1);  //获取第二行（第一行一般是标题）
        int lastRow = sheet.getLastRowNum(); // 返回的是值从0开始的
        System.out.println("总行数：" + (lastRow + 1));
        int lastCell = row.getLastCellNum(); // 返回的值是从1开始的.....
        System.out.println("总列数：" + lastCell);

        for (int i = 0; i <= lastRow; i++){
            row=sheet.getRow(i);
            if (row != null){
                for (int j = 0; j < lastCell; j++){
                    HSSFCell cell  = row.getCell(j);
                    if (cell != null) System.out.println(cell.getStringCellValue());
                }
            }

        }
    }

四 、test

package excel;

import java.io.IOException;
import java.util.*;

public class test {
    public static void main(String[] args) throws IOException {

        List<LinkedHashMap<String,Object>> dataList = new ArrayList<>();
        LinkedHashMap<String ,Object> map = new LinkedHashMap<>();
        map.put("name", "鸡蛋");
        map.put("code", "001");
        map.put("price", "3.94");
        dataList.add(map);
        LinkedHashMap<String ,Object> map1 = new LinkedHashMap<>();
        map1.put("name", "鸡肉");
        map1.put("code", "101");
        map1.put("price", "19.94");
        dataList.add(map1);
        LinkedHashMap<String ,Object> map2 = new LinkedHashMap<>();
        map2.put("name", "方便面");
        map2.put("code", "123001");
        map2.put("price", "1.00");
        dataList.add(map2);
        LinkedHashMap<String ,Object> map3 = new LinkedHashMap<>();
        map3.put("name", "五花肉");
        map3.put("code", "666");
        map3.put("price", "10.00");
        dataList.add(map3);

        String name = "测试数据";
        ExportExeclUnit.ExportExecl(dataList, name);
        ExportExeclUnit.addExcel(dataList, name);
        ExportExeclUnit.readExcel(name);
    }
}