/**
* excel 插入图片
* @param imgPath
* @param filePath
*/
public static boolean excelImage(String imageFilePath,String sourceFilePath, String destPath) {
FileOutputStream fileOut = null;
Workbook wb = null;
boolean result = true;
try {
String imghz = imageFilePath.substring(
imageFilePath.lastIndexOf(".")+1, imageFilePath.length());
String excelhz = sourceFilePath.substring(
sourceFilePath.lastIndexOf("."), sourceFilePath.length());
// 先把读进来的图片放到一个ByteArrayOutputStream中,以便产生ByteArray
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(new File(imageFilePath));
ImageIO.write(bufferImg, imghz.toUpperCase(), byteArrayOut);
if(excelhz.endsWith(".xls")){
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
sourceFilePath));
// 创建excel2003的文件文本抽取对象
wb = new HSSFWorkbook(fs);
HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);
// 创建一个工作薄
Drawing patriarch = sheet.createDrawingPatriarch();
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
int columnNum = row.getPhysicalNumberOfCells();
if (row != null) {
for (int i = 0; i < columnNum; i++) {
HSSFCell cell = row.getCell(i);
int CELLTYPE = 0;
if (cell != null) {
CELLTYPE = cell.getCellType();
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
}
if (cell == null
|| cell.getStringCellValue() == null) {
continue;
}
String value = cell.getStringCellValue().trim();
if (!"".equals(value)) {
if(value.contains("东调继") || value.contains("东调限") ){
int col1 = cell.getColumnIndex();
int row1 = row.getRowNum();
if(col1>=1){
col1 = col1-1;
}
if(row1>=1){
row1 = row1-1;
}
HSSFClientAnchor anchor = new HSSFClientAnchor();
anchor.setCol1(col1);
anchor.setRow1(row1);
int pictureIdx = wb.addPicture(
byteArrayOut.toByteArray(),
HSSFWorkbook.PICTURE_TYPE_JPEG);
org.apache.poi.ss.usermodel.Picture pic = patriarch.createPicture(anchor, pictureIdx);
pic.resize(1.0);
}
} else {
cell.setCellValue("");
}
if(CELLTYPE != XSSFCell.CELL_TYPE_NUMERIC){//判断原先数据属性是否为数字类型(如果是的话就不转为数字类型,因为转为数字会导致结果不一致)
cell.setCellType(CELLTYPE);
}
}
}
}
}else{
wb = new XSSFWorkbook(sourceFilePath);
XSSFSheet sheet = (XSSFSheet) wb.getSheetAt(0);
// // 创建一个工作薄
Drawing patriarch = sheet.createDrawingPatriarch();
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
XSSFRow row = (XSSFRow) rows.next();
if (row != null) {
int columnNum = row.getPhysicalNumberOfCells();
for (int i = 0; i < columnNum; i++) {
XSSFCell cell = row.getCell(i);
int CELLTYPE = 0;
if (cell != null) {
CELLTYPE = cell.getCellType();
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
}
if (cell == null || cell.getStringCellValue() == null) {
continue;
}
String value = cell.getStringCellValue().trim();
if (!"".equals(value)) {
if(value.contains("东调继") || value.contains("东调限") ){
int col1 = cell.getColumnIndex();
int row1 = row.getRowNum();
if(col1>=1){
col1 = col1-1;
}
if(row1>=1){
row1 = row1-1;
}
XSSFClientAnchor anchor = new XSSFClientAnchor();
anchor.setCol1(col1);
anchor.setRow1(row1);
int pictureIdx = wb.addPicture(
byteArrayOut.toByteArray(),
XSSFWorkbook.PICTURE_TYPE_JPEG);
org.apache.poi.ss.usermodel.Picture pic = patriarch.createPicture(anchor, pictureIdx);
pic.resize(1.0);
}
} else {
cell.setCellValue("");
}
if(CELLTYPE != XSSFCell.CELL_TYPE_NUMERIC){//判断原先数据属性是否为数字类型(如果是的话就不转为数字类型,因为转为数字会导致结果不一致)
cell.setCellType(CELLTYPE);
}
}
}
}
}
fileOut = new FileOutputStream(destPath);
// 写入excel文件
wb.write(fileOut);
System.out.println("----Excle文件已生成------");
} catch (Exception e) {
e.printStackTrace();
result = false;
return result;
} finally {
if (fileOut != null) {
try {
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return result;
}
poi对excel进行添加图片操作
最新推荐文章于 2023-12-18 16:30:30 发布