目录
一、Java操作Excel
使用Java操作excel可以使用两种方式：
关于表格导入导出，市面上比较知名的开源就是 Apache 的POI 和 阿里巴巴的 EasyExcel了。EasyExcel 也是对 POI 的改进和封装， 更加好用。
1：POI是Apache 的开源项目，由Java编写的跨平台 Java API，可操作Microsoft Office。
借助POI，可以方便的生成数据报表，数据批量上传，数据备份等工作。但是将文件数据一
次性全部加载到内存可能导致OOM。
2：EasyExcel 能大大减少内存占用的主要原因是在解析 Excel 时没有将文件数据一次性全部
加载到内存中，而是从磁盘上一行行读取数据，逐个解析。
1、Apache POI操作Excel
pom
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>读写数据
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import java.io.FileInputStream;
import java.io.FileOutputStream;
public class TestExcelWrite {
public static void main(String[] args) throws Exception {
// Workbook workbook = new HSSFWorkbook();//xls
Workbook workbook = new XSSFWorkbook();//xlsx
write(workbook,"C:\\Excel\\test.xlsx");
read();
}
public static void write(Workbook workbook,String path) throws Exception {
/*创建一个表*/
Sheet sheet = workbook.createSheet("test1");
/*创建一行*/
Row row1 = sheet.createRow(0);
/*创建一个单元格*/
Cell cell = row1.createCell(0);
/*设置单元格的内容*/
cell.setCellValue("学习的内容");
cell = row1.createCell(1);
cell.setCellValue("java操作ecxel");
Row row2 = sheet.createRow(1);
cell = row2.createCell(0);
cell.setCellValue("学习的时间");
cell = row2.createCell(1);
cell.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
FileOutputStream fileOutputStream = new FileOutputStream(path);
workbook.write(fileOutputStream);
fileOutputStream.flush();
fileOutputStream.close();
}
public static void read() throws Exception{
FileInputStream stream = new FileInputStream("C:\\Excel\\test.xlsx");
Workbook workbook = new XSSFWorkbook(stream);
Sheet sheet = workbook.getSheet("test1");
Row row = sheet.getRow(0);
Cell cell = row.getCell(0);
String stringCellValue = cell.getStringCellValue();
System.out.println(stringCellValue);
}
}读数据
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
public class TestExcelRead {
public static void main(String[] args) throws Exception {
FileInputStream inputStream = new FileInputStream("C:\\Excel\\test.xlsx");
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
for (int i = 0; i < colNum; i++) {
Cell cell = row.getCell(i);
String stringCellValue = cell.getStringCellValue();
System.out.print(stringCellValue+"| ");
}
System.out.println("一共"+colNum+"列。");
int rowNum = sheet.getPhysicalNumberOfRows();
for (int i = 4; i < rowNum; i++) {
Row row1 = sheet.getRow(i);
int physicalNumberOfCells1 = row1.getPhysicalNumberOfCells();
for (int i1 = 0; i1 < physicalNumberOfCells1; i1++) {
Cell cell = row1.getCell(i1);
if (cell!=null){
int cellType = cell.getCellType();
switch (cellType){
case XSSFCell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue()+"| ");
break;
case XSSFCell.CELL_TYPE_BLANK:
System.out.print("空值| ");break;
case XSSFCell.CELL_TYPE_BOOLEAN:
System.out.print(cell.getBooleanCellValue()+"| ");
break;
case XSSFCell.CELL_TYPE_FORMULA:
FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
//获得计算公式
String cellFormula = cell.getCellFormula();
CellValue evaluate = formulaEvaluator.evaluate(cell);
String s = evaluate.formatAsString();
System.out.print(s+"| ");
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)){
System.out.print(cell.getDateCellValue()+"| ");
}else {
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
System.out.print(cell.getStringCellValue()+"| ");
}
break;
case XSSFCell.CELL_TYPE_ERROR:
System.out.print("数据类型错误| ");
break;
}
}
}
System.out.println();
}
}
}修改数据
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelUtils {
public static void main(String[] args) {
// task1: 新建excel并设置 excel的C3的值为test1
String path = "src/main/resources/data/test.xlsx";
openExcel(path);
writeData(2,2,"test1");
generateExcel(path);
// task2: 编辑已存在的excel,设置A2的值为demo
openExcel(path);
writeData(1,0,"demo");
generateExcel(path);
// task3: 编辑已存在的excel，设置A列值为demo的这行第3列的值为demo3
openExcel(path);
writeData(0,"demo",2,"demo3");
generateExcel(path);
}
public static Workbook workbook;
public static void openExcel(String filepath) {
ExcelUtils.workbook = getWorkbook(filepath);
}
public static Workbook getWorkbook(String filepath) {
Workbook Workbook = null;
try (FileInputStream inputStream =new FileInputStream(filepath);){
if (filepath.endsWith(".xlsx")) {
Workbook = new XSSFWorkbook(inputStream);
} else {
Workbook = new HSSFWorkbook(inputStream);
}
} catch (IOException e) {
e.printStackTrace();
}
return Workbook;
}
public static void newWorkbook() {
workbook = new HSSFWorkbook();
}
/**
* row and column start from 0
* @param row
* @param column
* @param value
*/
public static void writeData(int row, int column,String value) {
Sheet sheet;
try {
sheet = workbook.getSheetAt(0);
}catch (IllegalArgumentException e){
sheet=workbook.createSheet();
}
Row row1 = sheet.createRow(row);
Cell cell = row1.createCell(column);
cell.setCellValue(value);
}
/**
* 循环找column1 列，当值为targetValue时，设置column2对应行的值为newData
* @param column1
* @param targetValue
* @param newData
* @param column2
*/
public static void writeData(int column1,String targetValue, int column2,String newData) {
Sheet sheet;
try {
sheet = workbook.getSheetAt(0);
}catch (IllegalArgumentException e){
sheet=workbook.createSheet();
}
int rows1 = sheet.getPhysicalNumberOfRows();
for (int i = 0; i < rows1; i++) {
try {
String value = cellValue(sheet.getRow(i).getCell(column1));
System.out.println(value);
if (targetValue.equalsIgnoreCase(value)) {
sheet.getRow(i).createCell(column2).setCellValue(newData);
break;
}
} catch (NullPointerException e) {
System.out.println("no this cell");
}
}
}
public static void generateExcel(String pathName) {
try (FileOutputStream fileOutputStream = new FileOutputStream(pathName);){
workbook.write(fileOutputStream);
workbook=null;
} catch (IOException e) {
e.printStackTrace();
}
}
private static String cellValue(Cell cell) {
String s = "";
if (cell != null) {
int cellType = cell.getCellType();
switch (cellType) {
case 0:
long numericCellValue = (long) cell.getNumericCellValue();
s = String.valueOf(numericCellValue);
break;
case 1:
s = cell.getStringCellValue();
break;
}
}
return s;
}
public static void setCellBackgroundAsRed(Cell cell) {
CellStyle cellStyle = cell.getCellStyle();
short color = cellStyle.getFillForegroundColor();
short fillPattern = cellStyle.getFillPattern();
if (color == IndexedColors.RED.getIndex() && fillPattern == CellStyle.SOLID_FOREGROUND) {
System.out.println("无需再设置");
} else {
CellStyle redCell = workbook.createCellStyle();
redCell.setFillForegroundColor(IndexedColors.RED.getIndex());
redCell.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell.setCellStyle(redCell);
}
}
}2、EasyExcel操作Excel
Easyexcel官网中有使用案例等说明
pom
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>最简单示例
@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
}制作数据
private List<DemoData> data() {
List<DemoData> list = ListUtils.newArrayList();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}写数据
/**
* 最简单的写
* <p>
* 1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>
* 2. 直接写即可
*/
@Test
public void simpleWrite() {
// 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入
// 写法1 JDK8+
// since: 3.0.0-beta1
String fileName = "C:\\Excel\\easyTest.xlsx";
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
// 如果这里想使用03 则 传入excelType参数即可
EasyExcel.write(fileName, DemoData.class)
.sheet("模板")
.doWrite(() -> {
// 分页查询数据
return data();
});
}读数据
/**
* 最简单的读
* <p>
* 1. 创建excel对应的实体对象 参照{@link DemoData}
* <p>
* 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
* <p>
* 3. 直接读即可
*/
@Test
public void simpleRead() {
// 写法1：JDK8+ ,不用额外写一个DemoDataListener
// since: 3.0.0-beta1
String fileName = "C:\\Excel\\easyTest.xlsx";
// 这里默认每次会读取100条数据 然后返回过来 直接调用使用数据就行
// 具体需要返回多少行可以在`PageReadListener`的构造函数设置
EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
for (DemoData demoData : dataList) {
log.info("读取到一条数据{}", JSON.toJSONString(demoData));
}
})).sheet().doRead();
}原文链接：https://blog.csdn.net/qq_45401910/article/details/123164846
二、Excel操作
1、Excel根据单元格状态自动变更行背景颜色
用excel记录和跟进工作的时候，设置背景颜色，以此让记录更加突出，方便查看，但是手动修改背景颜色一来麻烦容易漏，也可能颜色不统一，我们可以试试使用excel的条件格式，根据状态来自动变更整行背景颜色。
1、打开Excel，按alt+O，然后按D，点击“新建规则”，选择使用公式设置单元格。
在公式栏里输入公式：=$C2="完成"，公式表示C列对应第2行开始等于完成时，触发条件。
格式选择填充一个背景颜色
下图：应用于：代表此格式作用的范围
最终的效果
可以，重复点击“新建规则”，追加多条不同的规则。
2、Excel汇总统计管理
2.1、COUNTIFS函数
COUNTIF函数是Excel中一个非常有用的统计函数，它用于统计满足特定条件的单元格数量。函数的语法为COUNTIF(range, criteria)。具体说明如下：
range是必需的参数，代表要统计的单元格范围。
criteria是必需的参数，代表用于匹配的条件，可以是数字、表达式或文本形式。
COUNTIF函数的一些常见用法包括：
- 统计特定文本出现的次数。例如，=COUNTIF(A1:A10,"苹果")会统计区域A1到A10中包含“苹果”的单元格数量。
- 统计满足特定条件的数字单元格数量。例如，=COUNTIF(B1:B10,">50")会统计区域B1到B10中大于50的单元格数量。
- 统计非空单元格数量。例如，=COUNTIF(C1:C10,"<>")会统计区域C1到C10中非空的单元格数量。
- 使用通配符进行模糊匹配。例如，=COUNTIF(D1:D10,"*")会统计区域D1到D10中包含任何文本的单元格数量。
- 统计满足多个条件的单元格数量。例如，使用&符号连接条件，如=COUNTIF(E1:E10,">"&F1)会统计区域E1到E10中大于F1单元格值的单元格数量。
- 这个函数在处理大量数据时特别有用，因为它可以帮助快速汇总和分析数据。
2.2、IF函数
IF函数是Excel中最常用的函数之一
公式：IF(条件,真值,假值)：判断一个条件是否满足，如果满足指定的条件返回一个“真值”，如果不满足指定的条件则返回一个“假值”
一个程序员最重要的能力是：写出高质量的代码！！
有道无术，术尚可求也，有术无道，止于术。
无论你是年轻还是年长，所有程序员都需要记住：时刻努力学习新技术，否则就会被时代抛弃！
378
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
