第一部分:导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
注意:
尽量导入的位置靠前一些,不然会出现导入之后系统检测不到没反应的奇怪现象!(刷新maven不奏效的那种情况)
第二部分:验证是否导入成功
验证代码:
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class POITest {
public static void main(String[] args) {
Workbook workbook = new XSSFWorkbook();
System.out.println("Apache POI 安装成功!");
}
}
效果:
第三部分:功能使用
(1)获取文件流
FileInputStream fis = new FileInputStream("账号密码.xlsx");
(2)创建一个工作簿
一个工作簿中能够有多个表:
①从本地文件导入
Workbook workbook = new XSSFWorkbook(fis);
(3)表
0代表的是第一张表:
①得到表
Sheet sheet = workbook.getSheetAt(0);
②创建表
Sheet sheet = workbook.createSheet("Sheet1");
(4)行
0代表的是第一行:
①得到行
Row row1 = sheet.getRow(0); // 第一行
(5)列
0代表的是第一列:
①得到列
Cell cell1 = row1.getCell(0); // 第一行的第一列
②创建列
Row row_head = sheet.getRow(0);
row_head.getCell(0).setCellValue("机组");
row_head.getCell(1).setCellValue("参数");
row_head.getCell(2).setCellValue("value");
(6)数据
①得到数据
1.cell1.getStringCellValue()得到的是字符串
2.cell2.getNumericCellValue()得到的是数字(默认是浮点数)
所以如果想要得到的是整数,还需要强制类型转换(int)cell2.getNumericCellValue()
System.out.println("账号 = " + cell1.getStringCellValue());//得到的是字符串
System.out.println("密码 = " + (int)cell2.getNumericCellValue()); // 得到的是数字
②填充数据
Row row_content ;
int []arr1=new int[]{1,2,3,4,5,6,7,8,9,10};
int []arr2=new int[]{11,12,13,14,15,16,17,18,19,20};
int []arr3=new int[]{21,22,23,24,25,26,27,28,29,30};
for (int i = 0; i < 10; i++) {
row_content = sheet.getRow(i + 1);
row_content.getCell(0).setCellValue(arr1[i]);
row_content.getCell(1).setCellValue(arr2[i]);
row_content.getCell(2).setCellValue(arr3[i]);
}
(7)输出文件流
//输出
try (FileOutputStream fileOut = new FileOutputStream("test.xlsx")) {
workbook.write(fileOut);
} catch (IOException exception) {
exception.printStackTrace();
} finally {
try {
workbook.close();
} catch (IOException exception) {
exception.printStackTrace();
}
}
(8)实线段格式
①定义函数
private void createStyledCell(Workbook workbook,Cell cell) {
CellStyle style = workbook.createCellStyle();
style.setBorderTop(BorderStyle.MEDIUM); // 设置顶部边框
style.setBorderBottom(BorderStyle.MEDIUM); // 设置底部边框
style.setBorderLeft(BorderStyle.MEDIUM); // 设置左侧边框
style.setBorderRight(BorderStyle.MEDIUM); // 设置右侧边框
style.setAlignment(HorizontalAlignment.CENTER); // 水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
style.setWrapText(true); // 自动换行
cell.setCellStyle(style);
}
②调用函数批量处理
Row row_style;
for(int i=0;i<10+1;i++)
{
row_style=sheet.createRow(i);
for(int j=0;j<3;j++)
{
Cell cell = row_style.createCell(j);
createStyledCell(workbook, cell);
}
}
(9)合并单元格(n变1)
①num1代表行的开始索引
②num2代表行的结束索引
③num3代表列的开始索引
④num4代表列的结束索引
sheet.addMergedRegion(new CellRangeAddress(num1,num2 , num3, num4));
第四部分:案例总结
(1)读取表格xlsx内容
①xlsx文件的位置(在项目同一级就行)
②代码内容
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import java.io.FileInputStream;
public class POITest {
@Test
public void testReader() throws Exception {
// 获取文件流
FileInputStream fis = new FileInputStream("账号密码.xlsx");
// 1、创建一个工作簿
Workbook workbook = new XSSFWorkbook(fis);
// 2、得到表
Sheet sheet = workbook.getSheetAt(0);
// 3、得到行
Row row1 = sheet.getRow(0); // 第一行
Row row2 = sheet.getRow(1); // 第二行
// 4、得到列
Cell cell1 = row1.getCell(1); // 第一行的第二列
Cell cell2 = row2.getCell(1); // 第二行的第二列
// 5、读取值的时候,注意类型
System.out.println("账号 = " + cell1.getStringCellValue());//得到的是字符串
System.out.println("密码 = " + (int)cell2.getNumericCellValue()); // 得到的是数字
System.out.println("读取完成");
fis.close();
}
}
③运行结果
(2)往表格中填充数据并输出表格
①代码内容
package com.example;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import java.io.FileOutputStream;
import java.io.IOException;
public class POITest {
@Test
public void testReader() throws Exception {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Sheet1");
Row row_style;
for(int i=0;i<10+1;i++)
{
row_style=sheet.createRow(i);
for(int j=0;j<3;j++)
{
Cell cell = row_style.createCell(j);
CellStyle style = workbook.createCellStyle();
cell.setCellStyle(style);
}
}
Row row_head = sheet.getRow(0);
row_head.getCell(0).setCellValue("机组");
row_head.getCell(1).setCellValue("参数");
row_head.getCell(2).setCellValue("value");
Row row_content ;
int []arr1=new int[]{1,2,3,4,5,6,7,8,9,10};
int []arr2=new int[]{11,12,13,14,15,16,17,18,19,20};
int []arr3=new int[]{21,22,23,24,25,26,27,28,29,30};
for (int i = 0; i < 10; i++) {
row_content = sheet.getRow(i + 1);
row_content.getCell(0).setCellValue(arr1[i]);
row_content.getCell(1).setCellValue(arr2[i]);
row_content.getCell(2).setCellValue(arr3[i]);
}
//输出
try (FileOutputStream fileOut = new FileOutputStream("test.xlsx")) {
workbook.write(fileOut);
} catch (IOException exception) {
exception.printStackTrace();
} finally {
try {
workbook.close();
} catch (IOException exception) {
exception.printStackTrace();
}
}
}
}
②保存文件位置
③输出结果
(3)往表格中填充数据并输出表格(带加粗格式)
①代码内容
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import java.io.FileOutputStream;
import java.io.IOException;
public class POITest {
@Test
public void testReader() throws Exception {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Sheet1");
Row row_style;
for(int i=0;i<10+1;i++)
{
row_style=sheet.createRow(i);
for(int j=0;j<3;j++)
{
Cell cell = row_style.createCell(j);
createStyledCell(workbook, cell);
}
}
Row row_head = sheet.getRow(0);
row_head.getCell(0).setCellValue("机组");
row_head.getCell(1).setCellValue("参数");
row_head.getCell(2).setCellValue("value");
Row row_content ;
int []arr1=new int[]{1,2,3,4,5,6,7,8,9,10};
int []arr2=new int[]{11,12,13,14,15,16,17,18,19,20};
int []arr3=new int[]{21,22,23,24,25,26,27,28,29,30};
for (int i = 0; i < 10; i++) {
row_content = sheet.getRow(i + 1);
row_content.getCell(0).setCellValue(arr1[i]);
row_content.getCell(1).setCellValue(arr2[i]);
row_content.getCell(2).setCellValue(arr3[i]);
}
//输出
try (FileOutputStream fileOut = new FileOutputStream("test.xlsx")) {
workbook.write(fileOut);
} catch (IOException exception) {
exception.printStackTrace();
} finally {
try {
workbook.close();
} catch (IOException exception) {
exception.printStackTrace();
}
}
}
private void createStyledCell(Workbook workbook,Cell cell) {
CellStyle style = workbook.createCellStyle();
style.setBorderTop(BorderStyle.MEDIUM); // 设置顶部边框
style.setBorderBottom(BorderStyle.MEDIUM); // 设置底部边框
style.setBorderLeft(BorderStyle.MEDIUM); // 设置左侧边框
style.setBorderRight(BorderStyle.MEDIUM); // 设置右侧边框
style.setAlignment(HorizontalAlignment.CENTER); // 水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
style.setWrapText(true); // 自动换行
cell.setCellStyle(style);
}
}
②输出结果
(4)带合并格式的输出表格
①代码内容
package com.example;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import java.io.FileOutputStream;
import java.io.IOException;
public class POITest {
@Test
public void testReader() throws Exception {
int m=11;//行数(数据部分)
int n=4;//列数
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Sheet1");
Row row_style;
for(int i=0;i<m+1;i++)
{
row_style=sheet.createRow(i);
for(int j=0;j<n;j++)
{
Cell cell = row_style.createCell(j);
createStyledCell(workbook, cell);
}
}
Row row_head = sheet.getRow(0);
row_head.getCell(0).setCellValue("机组");
row_head.getCell(1).setCellValue("demo");
row_head.getCell(2).setCellValue("参数");
row_head.getCell(3).setCellValue("value");
Row row_content ;
//int []arr1=new int[]{1,2,3,4,5,6,7,8,9,10,11};
//int []arr2=new int[]{11,12,13,14,15,16,17,18,19,20};
String[] arr2_1 = {"A", "B", "C", "D", "E", "F或G", "", "", "", "", ""};
String[] arr2_2 = {"", "", "", "", "", "", "H", "I", "J", "K", "L"};
int []arr3=new int[]{21,22,23,24,25,26,27,28,29,30,31};
for (int i = 0; i < m; i++) {
row_content = sheet.getRow(i + 1);
row_content.getCell(3).setCellValue(arr3[i]);
}
for (int i = 0; i < 6; i++) {
row_content = sheet.getRow(i + 1);
row_content.getCell(1).setCellValue(arr2_1[i]);
}
for (int i = 6; i < 11; i++) {
row_content = sheet.getRow(i + 1);
row_content.getCell(1).setCellValue(arr2_2[i]);
}
//左侧合并并赋值(合并1)"1#机组"(开始部分)
sheet.addMergedRegion(new CellRangeAddress(1, 11, 0, 0));
Row Row_demo = sheet.getRow(1);
Row_demo.getCell(0).setCellValue("1#机组");
//左侧合并并赋值(合并1)"1#机组"(结束部分)
//内部合并(合并2)“掺烧生物质的机组”(开始部分)
sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(3, 3, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(4, 4, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(5, 5, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(6, 6, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(7, 11, 1, 1));
Row_demo = sheet.getRow(7);
Row_demo.getCell(1).setCellValue("掺烧生物质的机组");
//内部合并(合并2)“掺烧生物质的机组”(结束部分)
for (int i = 6; i < 11; i++) {
row_content = sheet.getRow(i + 1);
row_content.getCell(2).setCellValue(arr2_2[i]);
}
//输出
try (FileOutputStream fileOut = new FileOutputStream("test.xlsx")) {
workbook.write(fileOut);
} catch (IOException exception) {
exception.printStackTrace();
} finally {
try {
workbook.close();
} catch (IOException exception) {
exception.printStackTrace();
}
}
}
private void createStyledCell(Workbook workbook,Cell cell) {
CellStyle style = workbook.createCellStyle();
style.setBorderTop(BorderStyle.MEDIUM); // 设置顶部边框
style.setBorderBottom(BorderStyle.MEDIUM); // 设置底部边框
style.setBorderLeft(BorderStyle.MEDIUM); // 设置左侧边框
style.setBorderRight(BorderStyle.MEDIUM); // 设置右侧边框
style.setAlignment(HorizontalAlignment.CENTER); // 水平居中
style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
style.setWrapText(true); // 自动换行
cell.setCellStyle(style);
}
}