import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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;

/*
 * Created on 22.06.2004
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */

/**
 * @author karlheinz
 *
 * To change the template for this generated type comment go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
public class EuroAdapt {
	HSSFWorkbook oiWB = null;
	String siFileRead = null;
	String siFileWrite = null;


	public EuroAdapt(String	psTemplate,
					String	psFileWrite)
		throws IOException
	{
		POIFSFileSystem fs = null;

		this.siFileRead = psTemplate;
		this.siFileWrite = psFileWrite;
		//
		try {
			fs = new POIFSFileSystem(new FileInputStream(this.siFileRead));
			this.oiWB = new HSSFWorkbook(fs);
		}
		catch(IOException e)
			{ throw(e); }
		//
		return;
	}


	public static void main(String[] args) {
		EuroAdapt oEA = null;
		HSSFCell oC = null;
		HSSFSheet oSh = null;
		int iCnt;
		long lRow;
		FileOutputStream fosOut = null;
		String sFileRead = null;
		String sFileWrite = null;

		if (args.length < 2) {
			System.out.println("Need name of the template file and of the write file");
			return;
		}
		sFileRead = args[0];
		sFileWrite = args[1];
		//
		try {
			oEA = new EuroAdapt(sFileRead, sFileWrite);
		}
		catch(IOException e) {
			System.out.println("Can't open " + oEA.siFileRead + ".");
			System.out.println("Possibly it's not an Excel file.");
			return;
		}
		oEA.createExampleData();
		// - write the file
		try {
			fosOut = new FileOutputStream(oEA.siFileWrite);
		}
		catch (FileNotFoundException e) {
			System.out.println("Can't open file " + oEA.siFileWrite + " for writing.");
			return;
		}
		try {
			oEA.oiWB.write(fosOut);
			fosOut.close();
		}
		catch (IOException e2) {
			System.out.println("Can't write to file " + oEA.siFileWrite + ".");
			return;
		}
		//
		return;
	}


// ************ THIS IS THE RELEVANT PIECE OF CODE **********
	public void createExampleData() {
		HSSFCell oC = null;
		HSSFCellStyle oCS = null;
		HSSFSheet oSh = null;
		int iCnt;
		long lRow;

		// - The first sheet always has to exist!
		oSh = this.oiWB.getSheetAt(0);
		// - fill in the values
		for (iCnt = 1, lRow = 2; iCnt < 10; iCnt++, lRow++) {
			oC = this.setExtNum(oSh,lRow,2,(long) iCnt);
// ->At this position it doesn't work and it should be the right position.
			oC.setEncoding(HSSFCell.ENCODING_UTF_16);
			oCS = this.oiWB.createCellStyle();
			HSSFDataFormat oFmt = this.oiWB.createDataFormat();
// ************ HERE COMES THE FORMATTING PROBLEM ************
// TODO How to see Euro?
//			oCS.setDataFormat(oFmt.getFormat("#,##0 [$€-1]"));
			oCS.setDataFormat(oFmt.getFormat("#,##0 [$\u20ac-1]"));
// ->At this position it also doesn't work.
//			oC.setEncoding(HSSFCell.ENCODING_UTF_16);
			// - write the cell style to the cell
			oC.setCellStyle(oCS);
// ->At this position it also doesn't work.
//			oC.setEncoding(HSSFCell.ENCODING_UTF_16);
		}
		//
		return;
	}


	public HSSFCell getCell(HSSFSheet poSh,
							long plRow,
							int piCol)
	{
		HSSFCell oCell = null;
		HSSFRow oRow = null;
		// - Indexing starts at 0.
		int	nRows = poSh.getLastRowNum() + 1;

		if (plRow >= nRows)			// row doesn't exist
			{ return(null); }
		oRow = poSh.getRow((int) plRow);
		if (oRow == null)			// row doesn't exist
			{ return(null); }
		// - Indexing starts at 0.
		int nCols = oRow.getLastCellNum() + 1;
		if (piCol >= nCols)			// column doesn't exist
			{ return(null); }
		oCell  = oRow.getCell((short) piCol);
		//
		return(oCell);
	}


	// - create cell if it doesn't exist yet
	public HSSFCell getCellExt(HSSFSheet poSh,
								long plRow,
								int piCol)
	{
		HSSFCell oCell = null;
		HSSFRow oRow = null;

		 oCell = this.getCell(poSh,plRow,piCol);
		 // - So even in case of errors it will react right.
		 if (oCell == null) {					// new cell
			 oRow = poSh.getRow((int) plRow);
			 if (oRow == null)
				 { oRow = poSh.createRow((int) plRow); }
			 // - The cell has to be created in any case.
			 oCell = oRow.createCell((short) piCol);
		 }
		 return(oCell);
	}


	// - write a numeric value to a cell.
	// - Create it if it doesn't exist yet.
	public HSSFCell setExtNum(HSSFSheet poSh,
								long plRow,
								int piCol,
								long pnNum)
	{
		HSSFCell oCell = null;

		oCell = this.getCellExt(poSh,plRow,piCol);
		oCell.setCellValue(pnNum);
		// - Dates are already stored left bound without any further message.
		return(oCell);
	}


}