JAVA读写xls格式的Excel表格实例
工程目录
文件
pom.xml
<?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>helloworld</artifactId>
<groupId>com.ydfind</groupId>
<version>1.0-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>excel-demo</artifactId>
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<filtering>true</filtering>
</resource>
</resources>
</build>
<dependencies>
<!-- 日志 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.20</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-simple</artifactId>
<version>1.7.25</version>
</dependency>
<!-- xml依赖 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<!-- test -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
ExcelReadWrite.java
package com.ydfind.excel;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 java.io.*;
import java.util.Objects;
@Slf4j
public class ExcelReadWrite {
/**
* 通过复制格子的形式,作为读写的例子
* 将excel文件sourceFilename的内容,复制到targetFilename
* @param sourceFilename
* @param targetFilename
*/
public static void copyExcel(String sourceFilename, String targetFilename) throws IOException {
FileInputStream inputStream = null;
FileOutputStream outputStream = null;
try{
log.info("start to copy excel from {} to {}", sourceFilename, targetFilename);
// 源文件
File inFile = new File(sourceFilename);
inputStream = new FileInputStream(inFile);
Workbook inWork = new HSSFWorkbook(inputStream);
// 目标
File outFile = new File(targetFilename);
outputStream = new FileOutputStream(outFile);
Workbook outWork = new HSSFWorkbook();
// 复制
copyExcel(inWork, outWork);
// 保存到目标文件
outWork.write(outputStream);
log.info("copy success");
}finally {
if(outputStream != null){
try {
outputStream.close();
}catch (Exception e){
log.warn("outputStream close error!");
}
}
if(inputStream != null) {
try {
inputStream.close();
} catch (Exception e) {
log.warn("inputStream close error!");
}
}
}
}
private static void copyExcel(Workbook source, Workbook target){
// 遍历复制
int sheetCount = source.getNumberOfSheets();
for(int i = 0; i < sheetCount; i++){
Sheet inSheet = source.getSheetAt(i);
Sheet outSheet = target.createSheet(inSheet.getSheetName());
int rowCount = inSheet.getLastRowNum() + 1;
for(int j = 0; j < rowCount; j++){
Row inRow = inSheet.getRow(j);
Row outRow = outSheet.createRow(j);
if(Objects.isNull(inRow)){
continue;
}
int colNum = inRow.getLastCellNum() + 1;
for(int coli = 0; coli < colNum; coli++){
Cell inCell = inRow.getCell(coli);
Cell outCell = outRow.createCell(coli);
if(Objects.isNull(inCell)){
continue;
}
String text = inCell.toString();
if(Objects.isNull(text)){
continue;
}
outCell.setCellValue(text.trim());
}
}
}
}
}
FileUtil.java
package com.ydfind.util;
import lombok.extern.slf4j.Slf4j;
import java.io.*;
@Slf4j
public class FileUtil {
public static void writeFile(String path, String str) throws IOException {
try {
File file = new File(path);
if(!file.exists()) {
file.createNewFile();
}
FileOutputStream out = new FileOutputStream(file);
StringBuffer sb = new StringBuffer();
sb.append(str + "\r\n");
out.write(sb.toString().getBytes("utf-8"));
out.close();
} catch(IOException ex) {
throw new IOException("writeFile " + path + " error", ex);
}
}
public static String readFile(String path) throws IOException {
return readFile(path, "utf-8");
}
public static String readFile(String path, String encode) throws IOException {
StringBuffer sb=new StringBuffer();
String tempstr=null;
try {
File file=new File(path);
if(!file.exists()) {
throw new FileNotFoundException();
}
FileInputStream fis=new FileInputStream(file);
BufferedReader br=new BufferedReader(new InputStreamReader(fis, encode));
while((tempstr=br.readLine())!=null) {
sb.append(tempstr + "\r\n");
}
} catch(IOException ex) {
throw new IOException("readFile " + path + " error", ex);
}
return sb.toString();
}
}
ExcelReadWriteTest.java
import com.ydfind.excel.ExcelReadWrite;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import java.io.IOException;
@Slf4j
public class ExcelReadWriteTest {
@Test
public void testCopyExcel() throws IOException {
String source = ExcelReadWriteTest.class.getClassLoader().getResource("source.xls").getFile();
String target = ExcelReadWriteTest.class.getClassLoader().getResource("target.xls").getFile();
ExcelReadWrite.copyExcel(source, target);
log.info("打开 \".\\excel-demo\\target\\test-classes\\target.xls\", 查看是否复制成功!");
}
}
测试
新建两个excel文件,放到test的resource文件夹里(也可以放到别的地方,用绝对路径),运行test,就可以把source.xls的内容复制到target.xls里