本文主要是介绍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.Testpublic 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.Testpublic 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);}}
}
这篇关于excel文件追加内容，设置追加数据字体颜色，兼容.xlsx与.xls的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
