一.导包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
二.读取excel
1.遍历行列
package com.miracle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
public class ExcelTest {
public static void main(String[] args) throws IOException {
File xlsFile = new File("/home/miracle/Desktop/workspace_java/testmaven/src/test/java/com/miracle/his.xlsx");
/**
* 这里根据不同的excel类型
* 可以选取不同的处理类:
* 1.XSSFWorkbook
* 2.HSSFWorkbook
*/
// 获得工作簿
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(xlsFile));
// 获得工作表
XSSFSheet sheet = workbook.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows();
for (int i = 0; i < rows; i++) {
// 获取第i行数据
XSSFRow sheetRow = sheet.getRow(i);
// 获取第0格数据
XSSFCell cell = sheetRow.getCell(0);
// 调用toString方法获取内容
System.out.println(cell);
}
}
}
2.文本提取
package com.miracle.poi;
import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
public class Demo3 {
public static void main(String[] args) throws IOException {
FileInputStream inputStream = new FileInputStream("/home/miracle/Downloads/a.xls");
HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
ExcelExtractor excelExtractor = new ExcelExtractor(workbook);
System.out.println(excelExtractor.getText());
}
}
三.写入excel
package com.miracle.poi;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
public class Demo2 {
public static void main(String[] args) throws IOException {
// 创建一个新的工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个sheet页
HSSFSheet sheet1 = workbook.createSheet("sheet1");
// 创建一行
HSSFRow row = sheet1.createRow(0);
// 设置行高
row.setHeightInPoints(30);
// 创建并设置样式
HSSFCreationHelper creationHelper = workbook.getCreationHelper();
// 创建字体
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("courier New");// 字体名称
font.setItalic(true);// 倾斜
font.setStrikeout(true);
// 创建样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 设置字体
cellStyle.setFont(font);
// 设置日期格式化样式
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd hh:mm:ss"));
// 设置水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置配景色及颜色的填充方式(如果不设置填充方式,背景色填充不上)
cellStyle.setFillForegroundColor(IndexedColors.BLUE.index);
cellStyle.setFillPattern(FillPatternType.ALT_BARS);
// 创建一个单元格
HSSFCell cell = row.createCell(0);
// 设置单元格内容类型
cell.setCellType(CellType.STRING);
// 给单元格设置值
cell.setCellValue(new Date());
// 给单元格设置样式
cell.setCellStyle(cellStyle);
// 设置合并单元格,参数:起始行,结束行,起始列,结束列
sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
HSSFRow row2 = sheet1.createRow(1);
HSSFCell cell2 = row2.createCell(0);
cell2.setCellType(CellType.STRING);
// 通过换行符,设置强制换行
// 设置自动换行
// HSSFCellStyle cellStyle=workbook.createCellStyle();
// cellStyle.setWrapText(true);
cell2.setCellValue("我要\n换行");
// 创建输出流
FileOutputStream fileOutputStream = new FileOutputStream("/home/miracle/Downloads/a.xls");
// 将workbook写入流中
workbook.write(fileOutputStream);
// 关流
fileOutputStream.close();
}
}