excel文件追加内容，设置追加数据字体颜色，兼容.xlsx与.xls

关于IO的基础知识可见：IO

一、行追加

1、代码详细解释

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;

/**
 * @author zhangmeng
 * @Date 2019-11-22
 * @desc
 */
@Slf4j
public class Test {

    @org.junit.Test
    public void test() {
        String filePath = "/Users/zhangmeng/Desktop/工作簿2.xlsx";
        FileInputStream in = null;
        FileOutputStream out = null;
        XSSFWorkbook wb = null;
        try {
            //获取文件
            in = new FileInputStream(filePath);
            wb = new XSSFWorkbook(in);
            //获取到工作表，因为一个excel可能有多个工作表
            XSSFSheet sheet = wb.getSheetAt(0);
            //获取第一行（excel中的行默认从0开始，所以这就是为什么，一个excel必须有字段列头），即，字段列头，便于赋值
            XSSFRow row = sheet.getRow(0);
            //分别得到最后一行的行号，和一条记录的最后一个单元格
            System.out.println(sheet.getLastRowNum() + " " + row.getLastCellNum());

            out = new FileOutputStream(filePath);
            //创建现有行号的下一行
            row = sheet.createRow((short) (sheet.getLastRowNum() + 1));
            //设置第一个（从0开始）单元格的数据
            row.createCell(0).setCellValue("123");
            //设置第二个（从0开始）单元格的数据
            row.createCell(1).setCellValue(1);
        } catch (Exception e) {
            log.info("异常e:{}", e);
        } finally {
            try {
                if (in != null) {
                    in.close();
                }
                if (out != null) {
                    out.flush();
                    wb.write(out);
                    out.close();
                }
            } catch (Exception e) {
                log.info("e:{}", e);
            }
        }
    }
}

2、问题解决

org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007  XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)

原因：excel2003和excel2007版本的问题

（1）判断文件后缀名是xls，还是xlsx

（2）文件后缀名是xls，使用HSSFWorkbook；是xlsx，使用XSSFWorkbook

二、列追加

1、代码详细解释

excel处理数据，处理结果在指定行的最后一列添加备注，设置追加数据字体颜色

@Slf4j
public class Test {

    private static final Integer CURRENT_ROW = 1;

    @org.junit.Test
    public void test() {
        String filePath = "/Users/zhangmeng/Desktop/工作簿2.xlsx";
        FileInputStream in = null;
        FileOutputStream out = null;
        XSSFWorkbook wb = null;
        try {
            //获取文件
            in = new FileInputStream(filePath);
            wb = new XSSFWorkbook(in);
            //获取到工作表，因为一个excel可能有多个工作表
            XSSFSheet sheet = wb.getSheetAt(0);
            //写入数据
            out = new FileOutputStream(filePath);
            //设置追加字体颜色
            CellStyle redStyle = wb.createCellStyle();
            XSSFFont redFont = wb.createFont();
            redFont.setColor(new XSSFColor(new Color(255,0,0)));
            redStyle.setFont(redFont);
            //获取指定行（excel中的行默认从0开始）
            XSSFRow row = sheet.getRow(CURRENT_ROW);
            //在当前行最后一列的下一列追加数据
            XSSFCell cell = row.createCell(row.getLastCellNum());
            cell.setCellStyle(redStyle);
            cell.setCellValue("999");
        } catch (Exception e) {
            log.info("异常e:{}", e);
        } finally {
            try {
                if (in != null) {
                    in.close();
                }
                if (out != null) {
                    out.flush();
                    wb.write(out);
                    out.close();
                }
            } catch (Exception e) {
                log.info("e:{}", e);
            }
        }
    }
}

2、row.getLastCellNum()，获取的是最后一列的下一列，同size

三、兼容.xlsx与.xls，追加数据

private static final String EXCEL_EXTENSION_XLS = "xls";

private void appendExcelData(String filePath, Map<Integer, String> needAppendRows) {
    FileInputStream in = null;
    FileOutputStream out = null;
    Workbook wb = null;
    try {
        //获取文件
        in = new FileInputStream(filePath);
        if (filePath.endsWith(EXCEL_EXTENSION_XLS)) {
            wb = new HSSFWorkbook(in);
        } else {
            wb = new XSSFWorkbook(in);
        }
        //获取到工作表，因为一个excel可能有多个工作表
        Sheet sheet = wb.getSheetAt(0);
        //写入数据
        out = new FileOutputStream(filePath);
        //设置颜色
        CellStyle redStyle = wb.createCellStyle();
        Font redFont = wb.createFont();
        redFont.setColor(Font.COLOR_RED);
        redStyle.setFont(redFont);
        //获取指定行（excel中的行默认从0开始）
        for (Integer rows : needAppendRows.keySet()) {
            Row row = sheet.getRow(rows);
            //在当前行最后一列的下一列追加数据
            Cell cell = row.createCell(row.getLastCellNum());
            cell.setCellStyle(redStyle);
            cell.setCellValue(needAppendRows.get(rows));
        }
    } catch (Exception e) {
        log.info("appendExcelData, 追加数据出现异常e:{}", e);
    } finally {
        try {
            if (in != null) {
                in.close();
            }
            if (out != null) {
                out.flush();
                wb.write(out);
                out.close();
            }
        } catch (Exception e) {
            log.info("appendExcelData, 关闭流出现异常e:{}", e);
        }
    }
}