excel 操作工具类
时间：2024-6-7 16:41 作者：紫琪软件工作室 分类： POI
package com.qinghao.common.utils.poi;
import com.qinghao.common.exception.UtilException;
import com.qinghao.common.utils.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.util.Date;
/**
* excel 操作工具类
* @author ZhangJi
*/
public class PoiExcel {
private Workbook workBook;
private String filePath;
public PoiExcel() {
this.workBook = new XSSFWorkbook();
this.workBook.createSheet("sheet1");
}
public PoiExcel(String filePath) throws IOException {
this(new File(filePath));
}
public PoiExcel(File file) throws IOException {
this.filePath = file.getAbsolutePath();
this.verifyXls(file);
InputStream inputStream = Files.newInputStream(file.toPath());
// 根据不同的后缀，创建不同的对象
if(RegExpLib.matcher(file.getName(), ".*\\.xlsx$")) {
this.workBook = new XSSFWorkbook(inputStream);
} else{
this.workBook = new HSSFWorkbook(inputStream);
}
}
public PoiExcel(InputStream inputStream, String fileName) throws IOException {
if(!RegExpLib.matcher(fileName, ".*\\.xls$|.*\\.xlsx$")) {
throw new UtilException("导入文件类型错误！");
}
// 根据不同的后缀，创建不同的对象
if(RegExpLib.matcher(fileName, ".*\\.xlsx$")) {
this.workBook = new XSSFWorkbook(inputStream);
} else {
this.workBook = new HSSFWorkbook(inputStream);
}
}
/**
* 获取单元格值
* @param sheetIndex 工作簿索引
* @param rowIndex 行索引
* @param cellIndex 列索引
* @return String
*/
public Object getObjVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {
Cell cell = this.getSheet(sheetIndex).getRow(rowIndex).getCell(cellIndex);
if(null == cell) {
return null;
}
switch (cell.getCellType()) {
case STRING: return cell.getStringCellValue();
case NUMERIC:
case FORMULA:
if(DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
}
return cell.getNumericCellValue();
case BOOLEAN: return cell.getBooleanCellValue();
default: return null;
}
}
/**
* 获取单元格String值
* @param sheetIndex 工作簿索引
* @param rowIndex 行索引
* @param cellIndex 列索引
* @return String
*/
public String getStringVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {
Object cellVal = this.getObjVal(sheetIndex, rowIndex, cellIndex);
return null != cellVal?cellVal.toString(): "";
}
/**
* 获取单元格double值
* @param sheetIndex 工作簿索引
* @param rowIndex 行索引
* @param cellIndex 列索引
* @return String
*/
public Double getDoubleVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {
Object cellVal = this.getObjVal(sheetIndex, rowIndex, cellIndex);
return null == cellVal?.0: RegExpLib.matcher(cellVal.toString(), RegExpLib.REG_FLOAT)?Double.parseDouble(cellVal.toString()): .0;
}
/**
* 获取单元格Integer值
* @param sheetIndex 工作簿索引
* @param rowIndex 行索引
* @param cellIndex 列索引
* @return Integer
*/
public Integer getIntegerVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {
Object cellVal = this.getObjVal(sheetIndex, rowIndex, cellIndex);
if(null == cellVal) {
return 0;
}
String valStr = cellVal.toString();
if(!RegExpLib.matcher(valStr, RegExpLib.REG_FLOAT)) {
return 0;
}
if(valStr.contains(".")) {
valStr = valStr.substring(0, valStr.indexOf("."));
}
return Integer.parseInt(valStr);
}
/**
* 获取boolean值
* @param sheetIndex 工作簿索引
* @param rowIndex 行索引
* @param cellIndex 列索引
* @return boolean
*/
public boolean getBooleanVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {
Object cellVal = this.getObjVal(sheetIndex, rowIndex, cellIndex);
return null != cellVal && (boolean) cellVal;
}
/**
* 获取单元格Date值
* @param sheetIndex 工作簿索引
* @param rowIndex 行索引
* @param cellIndex 列索引
* @return Date
*/
public Date getDateVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {
Object cellVal = this.getObjVal(sheetIndex, rowIndex, cellIndex);
if(null == cellVal) {
return null;
}
if(cellVal instanceof Date) {
return (Date) cellVal;
}
return DateUtils.parseDate(cellVal.toString());
}
/**
* 设置字符串值
* @param cellIndex 列索引
* @param value 值
*/
public void setStringValue(Row row, Integer cellIndex, String value, CellStyle style) {
Cell cell = row.getCell(cellIndex);
if(null == cell) {
cell = row.createCell(cellIndex, CellType.STRING);
}
cell.setCellStyle(style);
cell.setCellValue(value);
}
/**
* 设置double值
* @param cellIndex 列索引
* @param value 值
*/
public void setDoubleValue(Row row, Integer cellIndex, double value, CellStyle style) {
Cell cell = row.getCell(cellIndex);
if(null == cell) {
cell = row.createCell(cellIndex, CellType.NUMERIC);
}
cell.setCellStyle(style);
cell.setCellValue(value);
}
public void setIntegerValue(Row row, Integer cellIndex, int value, CellStyle style) {
Cell cell = row.createCell(cellIndex, CellType.NUMERIC);
cell.setCellStyle(style);
cell.setCellValue(value);
}
/**
* 获取边框样式
* @return CellStyle
*/
public CellStyle getBorderStyle() {
CellStyle style = this.workBook.createCellStyle();
style.setBorderTop(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
return style;
}
/**
* 获取默认样式
* @return CellStyle
*/
public CellStyle createNormalStyle() {
return this.workBook.createCellStyle();
}
/**
* 创建字体
* @return Font
*/
public Font createFont() {
return this.workBook.createFont();
}
public void save() throws IOException {
FileOutputStream fileOutputStream = new FileOutputStream(this.filePath);
this.workBook.write(fileOutputStream);
this.workBook.close();
fileOutputStream.close();
this.workBook = null;
}
public void setFilePath(String filePath) {
this.filePath = filePath;
}
public void close() throws IOException {
if(null != this.workBook) {
this.workBook.close();
}
}
public Sheet getSheet(Integer sheetIndex) {
return this.workBook.getSheetAt(sheetIndex);
}
public Sheet getSheetBySheetName(String sheetName) {
return this.getSheet(this.getSheetIndexBySheetName(sheetName));
}
/**
* 获取sheet索引<br />
* 优先完全匹配，如完全匹配未找到，再包含匹配
* @param sheetName sheet名称
* @return Integer
*/
public Integer getSheetIndexBySheetName(String sheetName) {
int numbers = this.workBook.getNumberOfSheets();
// 完全匹配
for (int i=0; i<numbers; i++) {
Sheet sheet = this.workBook.getSheetAt(i);
if(sheet.getSheetName().equals(sheetName)) {
return i;
}
}
// 包含匹配
for (int i=0; i<numbers; i++) {
Sheet sheet = this.workBook.getSheetAt(i);
if(sheet.getSheetName().contains(sheetName)) {
return i;
}
}
throw new UtilException("sheetName ["+sheetName+"] not found");
}
public void setMergedRegionValue(Integer sheetIndex, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol, String value, CellStyle style) {
for (int beginRowIndex=firstRow; beginRowIndex <= lastRow; beginRowIndex++) {
Row row = this.getSheet(sheetIndex).getRow(beginRowIndex);
for (int beginCellIndex=firstCol; beginCellIndex <= lastCol; beginCellIndex++) {
this.setStringValue(row, beginCellIndex, "", style);
}
}
this.getSheet(sheetIndex).addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
this.setStringValue(this.getSheet(sheetIndex).getRow(firstRow), firstCol, value, style);
}
public void setMergedRegionImage(Integer sheetIndex, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol,
InputStream inputStream, int format, CellStyle style) throws IOException {
for (int beginRowIndex=firstRow; beginRowIndex <= lastRow; beginRowIndex++) {
Row row = this.getSheet(sheetIndex).getRow(beginRowIndex);
for (int beginCellIndex=firstCol; beginCellIndex <= lastCol; beginCellIndex++) {
this.setStringValue(row, beginCellIndex, "", style);
}
}
byte[] imageBytes = IOUtils.toByteArray(inputStream);
int pictureIdx = this.workBook.addPicture(imageBytes, format);
inputStream.close();
this.getSheet(sheetIndex).addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, firstCol, firstRow, lastCol, lastRow + 1); // 图片位置和大小
// 创建绘图对象
Drawing<?> drawing = this.getSheet(sheetIndex).createDrawingPatriarch();
Picture picture = drawing.createPicture(anchor, pictureIdx);
picture.resize(2,1);
}
private void verifyXls(File file){
if(!file.exists() || file.isDirectory()) {
throw new UtilException("文件不存在！");
}
if(!RegExpLib.matcher(file.getName(), ".*\\.xls$|.*\\.xlsx$")) {
throw new UtilException("导入文件类型错误！");
}
}
public Workbook getWorkBook() {
return workBook;
}
}