JAVA对excle创建、读取、设置单元格颜色、背景色、跨行跨列
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) {
//遍历sheet
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
//得到单个sheet
Sheet 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、创建workbook
HSSFWorkbook wb = new HSSFWorkbook();
//2、创建sheet
HSSFSheet 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文件进行读取、写入的帮助类，对单元格的字体颜色、背景色、跨行跨列方法进行了说明。尤其是背景色，字体颜色、填充格式与实际效果的对应关系，方便快速对单元格进行格式设置，如有其它遗漏的操作，恳请交流。