pom.xml依赖
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>RELEASE</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>RELEASE</version></dependency><!-- file文件转化为mutifile--><dependency><groupId>org.apache.httpcomponents</groupId><artifactId>httpcore</artifactId><version>4.4.9</version></dependency>
读取
package com.example.test;import org.apache.http.entity.ContentType;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.mock.web.MockMultipartFile;import org.springframework.web.multipart.MultipartFile;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;public class ExcelReadTest {/***根据文件路径得到MultipartFile* @param url* @return* @throws IOException*/public MultipartFile fileToMultipartFile(String url) throws IOException {File file = new File(url);FileInputStream fileInputStream = new FileInputStream(file);MultipartFile multipartFile = new MockMultipartFile(file.getName(), file.getName(),ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream);return multipartFile;}/*** 将文件转化为可操作的类型* @param multipartFile* @return* @throws IOException*/public Workbook getWorkFile(MultipartFile multipartFile) throws IOException {Workbook workbook = null;//获取文件的类型String type = multipartFile.getOriginalFilename().substring(multipartFile.getOriginalFilename().lastIndexOf(".")+1);//获取文件字节输入流InputStream in = multipartFile.getInputStream();if ("xls".equals(type)) {workbook = new HSSFWorkbook(in);} else if ("xlsx".equals(type)) {workbook = new XSSFWorkbook(in);}return workbook;}/*** 遍历读取excel的每一个单元格* @param workbook*/public void readFile(Workbook workbook) {//遍历sheetfor (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {//得到单个sheetSheet sheet = workbook.getSheetAt(numSheet);if (sheet == null) {continue;}//得到单个sheet的行数int rowCount = sheet.getLastRowNum();//从第二行开始，遍历Sheet的每一行(第一行一般是标题，所以不遍历)for (int rowNum = 1; rowNum <= rowCount; rowNum++) {try {//得到单行数据Row row = sheet.getRow(rowNum);if (row != null) {int cellCount = row.getLastCellNum();for (int cellNum = 0;cellNum<cellCount;cellNum++){Cell cell = row.getCell(cellNum);String cellValue = "";if (cell!=null){// cell.setCellType(CellType.STRING);//提前设置String类型，防止数字后加.0// cellValue = cell.getStringCellValue();//如果上面的setCellType(CellType.STRING)过期，可以先将Cell转化为CellBase，然后再定义类型CellBase cellBase = (CellBase) cell;//设置单元格数据类型cellBase.setCellType(CellType.STRING);//此处得到每一个单元格的值cellValue = cellBase.getStringCellValue();System.out.print("cellValue:" + cellValue+";");}}System.out.println();}} catch (Exception e) {e.printStackTrace();}}}}public void excelUtil(String url) throws IOException {MultipartFile multipartFile = fileToMultipartFile(url);Workbook workbook = getWorkFile(multipartFile);readFile(workbook);}public static void main(String[] args) throws IOException {String file = "G:\\IDEAWorkspeace\\test\\excle\\src\\main\\resources\\excel\\试点团队配置模板.xls";ExcelReadTest test = new ExcelReadTest();test.excelUtil(file);}}
写入
package com.example.test;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.util.*;public class ExcelWriteTest {public void createExcel(){//1、创建workbookHSSFWorkbook wb = new HSSFWorkbook();//2、创建sheetHSSFSheet sheet = wb.createSheet("经营预算科目");sheet.setDefaultColumnWidth(20);///3、创建第一行标题HSSFRow row = sheet.createRow(0);HSSFCellStyle titleStyle = wb.createCellStyle();/*** 设置边框*///下边框titleStyle.setBorderBottom(BorderStyle.THIN);//左边框titleStyle.setBorderLeft(BorderStyle.THIN);//上边框titleStyle.setBorderTop(BorderStyle.THIN);//右边框titleStyle.setBorderRight(BorderStyle.THIN);/*** 水平垂直居中*/titleStyle.setAlignment(HorizontalAlignment.CENTER);titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//单元格背景色titleStyle.setFillForegroundColor(IndexedColors.RED1.getIndex());//单元格填充效果titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);/*** 单元格内容的字体*/Font font = wb.createFont();//设置字体颜色font.setColor((short)1);//设置字体加粗font.setBold(true);titleStyle.setFont(font);Cell cell = row.createCell(0);cell.setCellValue("经营期预算科目-费用类填报");cell.setCellStyle(titleStyle);//第一行标题直接跨所有列sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));/*** 第二行，设置单元格内容的值，没有值也要设置为空。防止没有值导致不能设置单元格框线的问题*/row = sheet.createRow(1);cell = row.createCell(0);cell.setCellValue("预算科目代码");cell.setCellStyle(titleStyle);cell = row.createCell(1);cell.setCellValue("原科目");cell.setCellStyle(titleStyle);cell = row.createCell(2);cell.setCellValue("商业计划");cell.setCellStyle(titleStyle);cell.setCellStyle(titleStyle);cell = row.createCell(3);cell.setCellValue("新科目");cell.setCellStyle(titleStyle);cell = row.createCell(4);cell.setCellValue("");cell.setCellStyle(titleStyle);cell = row.createCell(5);cell.setCellValue("");cell.setCellStyle(titleStyle);cell = row.createCell(6);cell.setCellValue("");cell.setCellStyle(titleStyle);/*** 跨行跨列的合并，这里注意跨行跨列时，要保证起始行的起始列要有值*/sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 6));sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));sheet.addMergedRegion(new CellRangeAddress(1, 2, 2, 2));/*** 插入第三行的值*/row = sheet.createRow(2);List<String> title = new ArrayList<>();title.add("预算科目代码");title.add("原科目一级科目");title.add("商业计划");title.add("新科目大类");title.add("一级新科目");title.add("二级新科目");title.add("三级新科目");for (int i =0; i < title.size(); i++){cell = row.createCell(i);cell.setCellValue(title.get(i));cell.setCellStyle(titleStyle);}/*** 6、将生成的文件保存至指定文件夹下*/try {File file = new File("G:\\IDEAWorkspeace\\test\\excle\\src\\main\\resources\\excel\\write.xls");if (file.exists()){file.delete();}FileOutputStream fout = new FileOutputStream(file);wb.write(fout);fout.close();} catch (IOException e) {e.printStackTrace();}System.out.println("文件已创建");}public static void main(String[] args) {ExcelWriteTest writeTest = new ExcelWriteTest();writeTest.createExcel();}}
文件效果
单元格样式设置
跨行跨列
只需要一行代码即可完成。
//合并行列，参数一：开始行；参数二：结束行；参数三：开始列；参数四：结束列sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
设置字体
Font font = wb.createFont();font.setColor((short)1);font.setBold(true);
short值和颜色对应效果如图，来自POI4颜色名称，颜色汉语名称，颜色对应关系_lenovo96166的博客-CSDN博客
设置单元格样式
HSSFCellStyle style = wb.createCellStyle();//居中style.setAlignment(HorizontalAlignment.CENTER);//背景色为鲜红色style.setFillForegroundColor(IndexedColors.RED1.getIndex());//背景色填充效果充满整个单元格style.setFillPattern(FillPatternType.SOLID_FOREGROUND);style.setFont(font);
单元格背景色编码与颜色对应效果如图，来自POI设置Excel单元格背景色（setFillForegroundColor与setFillPattern的使用） - 大墨垂杨 - 博客园
单元格填充格式编码与实际效果对应关系如图，来自附录1——单元格设置背景色，FillPattern枚举效果_王事成的博客-CSDN博客_fillpatterntype颜色
总结
此篇博文构建了对Excel文件进行读取、写入的帮助类，对单元格的字体颜色、背景色、跨行跨列方法进行了说明。尤其是背景色，字体颜色、填充格式与实际效果的对应关系，方便快速对单元格进行格式设置，如有其它遗漏的操作，恳请交流。
如果觉得《JAVA对excle创建 读取 设置单元格颜色 背景色 跨行跨列》对你有帮助，请点赞、收藏，并留下你的观点哦！
