import java.io.ByteArrayOutputStream;
	import java.io.File;
	import java.io.FileOutputStream;
	import java.io.InputStream;
	import java.io.IOException;
	import java.io.FileInputStream;
	import java.net.URL;

	import org.apache.poi.hssf.usermodel.HSSFCell;
	import org.apache.poi.hssf.usermodel.HSSFCellStyle;
	import org.apache.poi.hssf.usermodel.HSSFFont;
	 import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
	 import org.apache.poi.hssf.usermodel.HSSFChildAnchor;
	 import org.apache.poi.hssf.usermodel.HSSFShapeGroup;
	import org.apache.poi.hssf.usermodel.HSSFFooter;
	import org.apache.poi.hssf.usermodel.HSSFHeader;
	import org.apache.poi.hssf.usermodel.HSSFHyperlink;
	import org.apache.poi.hssf.usermodel.HSSFPatriarch;
	import org.apache.poi.hssf.usermodel.HSSFPicture;
	import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
	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.hssf.util.HSSFColor;
	import org.apache.poi.hssf.util.Region;

	import junit.framework.TestCase;

	public class ImageTest extends TestCase{

		/**
		 * @param args
		 */
		public void testExcelReport() throws Exception{
			int nRows = 20;
			int nColumns = 10;
			float imageHeight =24f;
			float imageWidth = 96f;
			float[] columnWidths = new float[nColumns];
			for (int c=0;c<nColumns;c++){
				columnWidths[c] = 90f;
			}
			columnWidths[9]=1000f;//Comment this line to see the unreversed image!!
			
			HSSFWorkbook workbook = new HSSFWorkbook();
	 		HSSFSheet sheet = workbook.createSheet("test report");
	 		HSSFRow titleRow = sheet.createRow(0);
	 	    titleRow.setHeightInPoints((short)convertPixelsToPoints(imageHeight));
	 	    HSSFCell titleCell = titleRow.createCell((short)0);
	 	    titleCell.setCellValue(new HSSFRichTextString("Report Title"));
	 	    
	 		 		
	 		for (int r=0;r<nRows;r++){
				
				 HSSFRow hssfRow = sheet.createRow(r+2);

				
					int columnIndex = 0;
					for(int c=0;c<nColumns;c++){
						
						float cellWidth = convertPixelsToCharacters(columnWidths[c]*256f);
						
						sheet.setColumnWidth((short)(columnIndex),(short)cellWidth);
						HSSFCell hssfCell = hssfRow.createCell((short)(columnIndex));
						hssfCell.setCellValue(new HSSFRichTextString("data_"+r+"_"+c));
						columnIndex++;
						
					}
	 		}
		
	 		Region rg = new Region(0, (short)8,(short)0,(short)9); 
			sheet.addMergedRegion(rg); 
			HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); 
			HSSFClientAnchor anchor; 
			anchor = new HSSFClientAnchor(0,0,0,0,rg.getColumnFrom(),0,(short)(rg.getColumnTo()+1),1);
			HSSFShapeGroup group = patriarch.createGroup(anchor); 
			anchor.setAnchorType( 3 ); 
			int rngWidth = 0;

			for (short cellnum = rg.getColumnFrom(); cellnum <= rg.getColumnTo();
			cellnum++){
			    rngWidth += sheet.getColumnWidth(cellnum); 
			}

			int scaleWidth = (int)Math.round(((imageWidth*15*2)/rngWidth)*1024);// 1 pixel = 15 twips. 2 is the fudge factor. 1024 - max value of dx
			HSSFChildAnchor an1 = new HSSFChildAnchor(0,0,scaleWidth,254); //254 - max value of dy
			try{
			HSSFPicture pic = group.createPicture(an1, loadPicture( "src/main/resources/header_logo.JPG", workbook ));
			}catch(Exception e){
				throw new Exception ("Error adding logo image - "+e.getMessage());
			}
			ByteArrayOutputStream baosXLS = new ByteArrayOutputStream();
			try{
				workbook.write(baosXLS);
			
	        	File resultFile = new File("C:/MyReport123.xls");
	        	FileOutputStream fos = new FileOutputStream(resultFile.getAbsolutePath());
	        	fos.write(baosXLS.toByteArray());
	        	fos.close();
			}catch(Exception e){
				System.out.println("Error writing excel report to disk: " +e.getMessage());
	        	throw new RuntimeException(e);
			}
		}
		private int loadPicture( String path, HSSFWorkbook wb ) throws IOException{
			int pictureIndex;
			FileInputStream fis = null;
			ByteArrayOutputStream bos = null;
			try{
				fis = new FileInputStream( path);
				bos = new ByteArrayOutputStream( );
				int c;
				while ( (c = fis.read()) != -1)
					bos.write( c );
				pictureIndex = wb.addPicture( bos.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG );
			}
			finally{
				if (fis != null)fis.close();if (bos != null)bos.close();
			}
			return pictureIndex;
		}
		private  float convertPixelsToCharacters(float pixels){
			//96 pixels = 13 characters
			float characters = 13f/96f * pixels;
			return characters;
		}
		public  float convertPixelsToPoints(float pixels){
			//72 points = 96 pixels
			float points = 72f/96f * pixels;
			return points;
		}
	}