今天有一个小任务,就是读取excel的内容,处理一下以后重新写入到excel中,中间踩了一些坑,这里记录一下方便自己以后查,只做最简单的读写操作,而且是一个单独的测试类,性能也没做优化。有空再改造成工具类吧,大概明天就有空,嗯,大概…
首先需要添加maven
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
操作Excel的代码
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.FileOutputStream;
public class ExcelTest {
private XSSFSheet sheet;
private XSSFWorkbook xxsWorkBook;
//导入excel文件
public void importExcel(String filePath, String sheetName) {
FileInputStream fileInputStream;
try {
fileInputStream = new FileInputStream(filePath);
xxsWorkBook = new XSSFWorkbook(fileInputStream);
//获取sheet
sheet = xxsWorkBook.getSheet(sheetName);
} catch (Exception e) {
e.printStackTrace();
}
}
//根据表格的位置获取字符串
public String getExcelDataByIndex(int mRow, int mColumn) {
XSSFRow row = sheet.getRow(mRow);
XSSFCell cell = row.getCell(mColumn);
if (cell != null) {
return cell.toString();
}
return null;
}
//根据表格的位置设置字符串
public void setExcelDataByIndex(int mRow, int mColumn, String value) {
XSSFRow row = sheet.getRow(mRow);
XSSFCell cell = row.createCell(mColumn);
cell.setCellValue(value);
}
//写出excel文件
public void writeExcel(String filePath) {
try {
FileOutputStream out = new FileOutputStream(filePath);
System.out.println("excel写入中");
xxsWorkBook.write(out);
out.close();
System.out.println("excel写入成功");
} catch (Exception e) {
e.printStackTrace();
}
}
//测试读取里面单元格的内容
@Test
public void testReadExcel() {
importExcel("C:\\Users\\DELL\\Desktop\\excel.xlsx", "Sheet1");
for (int i = 0; i < 2000; i++) {
String excelDateByIndex = getExcelDataByIndex(i, 1);
System.out.println(excelDateByIndex);
}
}
//测试写入
@Test
public void testWriteExcel() {
String filePath = "C:\\Users\\DELL\\Desktop\\excel.xlsx";
importExcel(filePath, "Sheet1");
for (int i = 0; i < 2000; i++) {
setExcelDataByIndex(i, 1, "test");
}
writeExcel(filePath);
}
}