最近遇到这样一个需求:需要读取Excel内容(里面含有许多文章),查询指定关键字在各个文章中出现的次数,将结果返回到excel中。因为我不知道怎么用java操作excel,于是上网四处寻找解决办法,结果要么是只能对excel2003或excel2007版的才有用,要么是copy过来的代码压根运行不了!不过最后总算找到了方法~
所以我在这里出一篇文章,为大家避避雷,不用特意去花积分下载资源,到头来反而运行不了,而且该文方法适合07版本以后的excel。
长话短说,从这篇文章你可以学习到以下内容:
1、读取Excel文件内容
2、向Excel写入内容(包括设置样式)
3、从Excel读取内容写入数据库中
4、从数据库读取内容写入Excel中
Apache POI是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写,意为“简洁版的模糊实现”。
开门见山,java处理Excel就需要用到POI这个java API!
点击快速查看POI使用手册:POI中文API使用文档
这里包含了本文中全部实例需要用到的依赖,如果只是进行对Excel的读写,只需引入poi依赖即可
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<parent>
<artifactId>readExcel</artifactId>
<groupId>org.example</groupId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>POI_EXCEL</artifactId>
<packaging>jar</packaging>
<dependencies>
<!-- 引入poi,解析workbook视图 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.16</version>
<scope>compile</scope>
</dependency>
<!--durid连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.9</version>
</dependency>
<!--jdbcTemplate-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.2.RELEASE</version>
</dependency>
</dependencies>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
</project>
引入依赖的时候爆红是正常的,刷新一下maven工程,然后等待下载好就行了
需要用到的类及方法:
类 | 方法 |
---|---|
XSSFWorkbook:获取工作簿需要的类(工作簿即excel文档) | getSheetAt(index):获取工作簿中的工作表,参数即选择第几个工作表 |
XSSFSheet:获取工作表需要的类(excel左下角) | getLastRowNum:获得工作表中有效行数 |
XSSFRow:获取行需要的类 | getRow(int rownum):获取工作表中第rownum行 |
XSSFCell:获取列需要的类 | getLastCellNum():获取每行中有效列数 |
getCell(int cellnum):获取当前行的第cellnum列 | |
setCellType(int cellType):设置读取内容格式(一般为String) | |
getStringCellValue():读取当前行当前列的值 |
代码如下
public class ReadExcel {
public static void main(String[] args) throws Exception {
//1、获取工作簿
XSSFWorkbook workbook = new XSSFWorkbook("E:\\hello.xlsx");
//2、获取工作表
XSSFSheet sheet = workbook.getSheetAt(0);
//3、获取行
int lastRowNum = sheet.getLastRowNum(); //得到有效行
for (int i = 0; i <= lastRowNum; i++) {
XSSFRow row = sheet.getRow(i);
if (row != null) {
short cellNum = row.getLastCellNum(); //获取有效列
for (int j = 0; j <= cellNum; j++) {
XSSFCell cell = row.getCell(j);
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING); //设置格式为string
String stringCellValue = cell.getStringCellValue();
System.out.println(stringCellValue);
}
}
}
//释放资源
workbook.close();
}
}
}
Excel表格内容内容如下
读取excel内容 运行结果:
需要用到的方法:
方法 |
---|
createSheet(String sheetname):创建名为sheetnane的工作表 |
createRow(int rownum):创建索引为rownum行 |
createCell(int columnIndex):创建索引为columnIndex列 |
setCellValue(@Nullable String str):设置当前单元格值,允许为空 |
createCellStyle():创建单元格样式 |
setFillForegroundColor(short fg):设置单元格背景色 |
setFillPattern(short fp):自定义颜色填充规格 |
createFont():创建字体样式 |
setFontName(String name):设置字体样式名称 |
setColor(short color):设置字体颜色 |
setFont(Font font):将字体样式放进单元格样式中 |
setCellStyle(CellStyle style):将自定义单元格样式设置到当前单元格中 |
代码如下
public class WriteExcel {
public static void main(String[] args) throws Exception{
//1、创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
//2、创建工作表
XSSFSheet sheet = workbook.createSheet("工作表一");
//单元格样式
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.PINK.getIndex()); //粉色背景
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); //颜色填充规格,设置实心的一种颜色
//字体样式
XSSFFont font = workbook.createFont();
font.setFontName("黑体");
font.setColor(IndexedColors.BLUE.getIndex()); //字体蓝色
cellStyle.setFont(font); //把字体样式放入到单元格样式里去
//3、创建行
XSSFRow row = sheet.createRow(0);
//创建初始化列,设置其格式
XSSFCell cell = row.createCell(0);
cell.setCellValue("商品编号");
cell.setCellStyle(cellStyle);
XSSFCell cell1 = row.createCell(1);
cell1.setCellValue("商品名称");
cell1.setCellStyle(cellStyle);
XSSFCell cell2 = row.createCell(2);
cell2.setCellValue("商品价格(单位:元/斤)");
cell2.setCellStyle(cellStyle);
XSSFCell cell3 = row.createCell(3);
cell3.setCellValue("商品库存(单位:吨)");
cell3.setCellStyle(cellStyle);
//输出流
FileOutputStream out = new FileOutputStream("E:\\result.xlsx");
workbook.write(out);
out.flush();
//释放资源
out.close();
workbook.close();
System.out.println("写入成功!");
}
}
查看写入的excel结果:
读取excel内容写入数据库核心代码
public static List<Product> read(String path) throws Exception {
List<Product> productList = new ArrayList<>(); //这个集合是用来存放多个产品的
//1、获取个作簿
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(path);
//2、获取工作表
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
for (int i = 1; i <= lastRowNum; i++) {
XSSFRow row = sheet.getRow(i);
if (row != null) {
List<String> list = new ArrayList<>(); //将读取的内容弄放到list集合中
for (Cell cell : row) {
if (cell != null) {
cell.setCellType(Cell.CELL_TYPE_STRING); //设置格式为string
String value = cell.getStringCellValue(); //读取数据
if (value!=null &&!"".equals(value)) { //内容不为空才放进list里
list.add(value);
}
}
}
if (list.size() > 0) {
Product product = new Product(Integer.parseInt(list.get(0)), list.get(1), Double.parseDouble(list.get(2)), Integer.parseInt(list.get(3))); //放进实体类中
productList.add(product); //将实体类放进专门存储多个产品的表
}
}
}
return productList;
}
读取excel表中的数据
//1.1读取excel表中的数据
System.out.println("请输入您要读取的文件位置(不包含空格)");
String path = sc.next();
List<Product> productList = read(path); //获得excel表中的内容
System.out.println(productList);
将数据写入数据库
//1.2将数据写入到数据库中
productService.save(productList);
System.out.println("数据已存入数据库中!");
其中save方法是通过三层模式其中的Service和dao来实现的
Service层
public void save(List<Product> productList) {
for (Product product : productList) {
productDao.save(product);
}
}
Dao层
public void save(Product product) {
String sql = "insert into product values(?,?,?,?)";
jdbcTemplate.update(sql,product.getPid(),product.getPname(),product.getPrice(),product.getPstock());
}
运行测试:
插入成功!
读取数据库内容写入Excel核心代码
public static void write(List<Product> productList,String path) throws Exception{
//1、创建一个工作簿
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
//2、创建工作表
XSSFSheet sheet = xssfWorkbook.createSheet("商品");
//单元格样式
XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.PINK.getIndex()); //粉色背景
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); //颜色填充规格,设置实心的一种颜色
//字体样式
XSSFFont font = xssfWorkbook.createFont();
font.setFontName("黑体");
font.setColor(IndexedColors.BLUE.getIndex()); //字体蓝色
cellStyle.setFont(font); //把字体样式放入到单元格样式里去
//3、创建行
XSSFRow row = sheet.createRow(0);
//创建初始化列,设置其格式
XSSFCell cell = row.createCell(0);
cell.setCellValue("商品编号");
cell.setCellStyle(cellStyle);
XSSFCell cell1 = row.createCell(1);
cell1.setCellValue("商品名称");
cell1.setCellStyle(cellStyle);
XSSFCell cell2 = row.createCell(2);
cell2.setCellValue("商品价格(单位:元/斤)");
cell2.setCellStyle(cellStyle);
XSSFCell cell3 = row.createCell(3);
cell3.setCellValue("商品库存(单位:吨)");
cell3.setCellStyle(cellStyle);
for (int i = 0; i <productList.size(); i++) {
XSSFRow row1 = sheet.createRow(i + 1);
row1.createCell(0).setCellValue(productList.get(i).getPid());
row1.createCell(1).setCellValue(productList.get(i).getPname());
row1.createCell(2).setCellValue(productList.get(i).getPrice());
row1.createCell(3).setCellValue(productList.get(i).getPstock());
}
FileOutputStream fileOutputStream = new FileOutputStream(path);
xssfWorkbook.write(fileOutputStream);
fileOutputStream.flush();
fileOutputStream.close();
xssfWorkbook.close();
}
读取数据库中的数据
//2.1读取数据库中的数据
List<Product> productList = productService.findAll();
System.out.println(productList);
将数据写入到excel表格中
//2.2将数据写入到excel表格中
System.out.println("请输入要写入的文件位置");
String path = sc.next();
write(productList,path);
System.out.println("写入成功");
读取数据库信息findAll方法
Service层
@Override
public List<Product> findAll() {
return productDao.findAll();
}
Dao层
@Override
public List<Product> findAll() {
String sql="select * from product";
return jdbcTemplate.query(sql,new BeanPropertyRowMapper<Product>(Product.class));
}
运行结果: