Bug 51280

Summary: when we insert a new image to the existing excel file that corrupts the previous images
Product: POI Reporter: shafiexp
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: major    
Priority: P2    
Version: unspecified   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: The Corrupted Excel File

Description shafiexp 2011-05-28 08:52:50 UTC
Created attachment 27079 [details]
The Corrupted Excel File

Hi,

I'm opening the existing excel file(Which contains String data and images(.jpg) and do the following operations.

1.adding new data to the existing file---its adding 
2.adding new image to the existing file -the previous image is corrupted


Give me the solutionnnnnn

The code I have Wriiten


package tfib.excel;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Picture;

public class WriteExcel2 {
	
	private HSSFWorkbook workBook;
	private HSSFSheet sheet;
	private HSSFRow excelRow;
	private HSSFCell cell;
	
	private String excelFileName;
	private String sheetName;
	public WriteExcel2(String excelFileName,String sheetName) throws IOException
	{
		this.excelFileName=excelFileName;
		this.sheetName=sheetName;
		System.out.println("CHecking Whether the File exists");
		
		File fileIn=new File(excelFileName);
		System.out.println("The Input File Name:"+fileIn);
		FileInputStream fInputStream;
		if(fileIn.exists())
		{
			System.out.println("WorkBook is Exist / Getting the Existing Workbook");
			
			
				fInputStream = new FileInputStream(fileIn);
				POIFSFileSystem poiStream=new POIFSFileSystem(fInputStream);
				workBook=new HSSFWorkbook(poiStream);
				System.out.println("Getting The Existing Sheet From the Existing Workbook");
				sheet=workBook.getSheet(sheetName);
				System.out.println("THe WorkBook: "+workBook);
				System.out.println("THe Sheet: "+sheet);
//				System.out.println("THe Sheet: "+sheet.getSheetName());
//				sheet.getDrawingPatriarch();
				if(sheet==null)
				{
					System.out.println("Creating New Sheet in the Existing Workbook");
					sheet=workBook.createSheet(sheetName);
				}
				
		 
		}
		else
		{
			System.out.println("Creating New WorkBook and New Sheet");
			workBook=new HSSFWorkbook();
			sheet=workBook.createSheet(sheetName);
			System.out.println("THe WorkBook: "+workBook);
			System.out.println("THe Sheet: "+sheet);
			System.out.println("THe Sheet: "+sheet.getSheetName());
		}
	}
	public void insertData(int row,int col,String data)
	{
		System.out.println("Creating Row,Cell and inserting Data into WorkSheet");
		
			if(sheet!=null)
			{
				if(sheet.getRow(row)!=null)
				{
					excelRow=sheet.getRow(row);
				}
				else
				{
					excelRow=sheet.createRow(row);
				}

				cell=excelRow.createCell(col);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(data));
				sheet.autoSizeColumn(col);
				System.out.println("The Data is inserted successfully in WorkSheet");
			}
	}
	public void insertImage(int startRow,int startCol,int endRow,int endCol,String imgPath) throws IOException
	{
		System.out.println("Inserting Picture into WorkSheet");
		ClientAnchor anchor;
		HSSFPatriarch patriarch;
		Picture picture;
		FileInputStream fileInput=new FileInputStream(imgPath);
		ByteArrayOutputStream img_bytes=new ByteArrayOutputStream();
		int b;
		while((b=fileInput.read())!=-1)
		img_bytes.write(b);
		fileInput.close();
		anchor=workBook.getCreationHelper().createClientAnchor();
		int picIndex=workBook.addPicture(img_bytes.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG);
		anchor.setCol1(startCol);
		anchor.setRow1(startRow);
		anchor.setCol2(endCol);
		anchor.setRow2(endRow);
		anchor.setAnchorType(1);
		
		System.out.println("The Worbook  : "+workBook);
		System.out.println("The Sheet  : "+sheet);
		
		System.out.println("THe Existing Sheet Patriarch: "+sheet.getDrawingPatriarch());
		
		patriarch=sheet.createDrawingPatriarch();
		
		System.out.println("THe PAtrich Val: "+patriarch);
		
		picture=patriarch.createPicture(anchor, picIndex);
//		pic.resize();
		System.out.println("The Image is inserted Successfully in workSheet");
	}

	public void writeDataToExcel() throws IOException 
	{	
		FileOutputStream outputExcelFile;
		System.out.println("Writing the workbook(cintains data) to Output Excel File");
			System.out.println(excelFileName);
			System.out.println("The length of excel file : "+excelFileName.length());
			outputExcelFile=new FileOutputStream(new File(this.excelFileName));
			workBook.write(outputExcelFile);
			System.out.println("THe Workbook is succesfully written to Output Excel File");	
				
			if(outputExcelFile!=null)
			{
				outputExcelFile.flush();
				outputExcelFile.close();
			} 
		}
	}
Comment 1 Nick Burch 2011-05-28 10:18:10 UTC
It's not currently possible with HSSF, sorry. See the warnings on the two key methods:

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#createDrawingPatriarch%28%29
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#getDrawingPatriarch%28%29

You should either place all your images in in one go, or switch to XSSF (where the file format makes it easier to support)
Comment 2 Nick Burch 2011-05-28 10:18:38 UTC
*** Bug 51281 has been marked as a duplicate of this bug. ***
Comment 3 Nick Burch 2011-05-29 18:26:18 UTC
*** Bug 51290 has been marked as a duplicate of this bug. ***
Comment 4 Evgeniy Berlog 2012-08-12 12:44:14 UTC
This problem should be fixed in trunk.

Please try with a nightly build - see download links on http://poi.apache.org/
or build yourself from SVN trunk, see http://poi.apache.org/subversion.html