Month: July 2011

TransactionRequiredException: Executing an update/delete query

Simply adding @Transactional on the method will resolve this issue

July 29, 2011

Convert given excel column name to column Index, ex ‘A=0’, ‘AA=26’

Other day I needed to convert excel spreadsheet column name to column index when having 200+ columns is easier to express as ‘FB’ instead of 157

A = 0 
B = 1
AA = 26
AA = 27
FB = 157

This code will work for any number of column names.

	/**
	 * Convert given excel column name to column Index, ex 'A=0', 'AA=26'
	 * @param columnName
	 * @return 0 based index of the column
	 */
	private static short convert2ColumnIndex(String columnName) {
		columnName = columnName.toUpperCase();
		short value = 0;
		for (int i = 0, k = columnName.length() - 1; i < columnName.length(); i++, k--) {
			int alpabetIndex = ((short) columnName.charAt(i)) - 64;
			int delta = 0;
			// last column simply add it
			if (k == 0) {
				delta = alpabetIndex - 1;
			} else { // aggregate
				if (alpabetIndex == 0)
					delta = (26 * k);
				else
					delta = (alpabetIndex * 26 * k);					
			}
			value += delta;
		}
		return value;
	}

Converting from index to column name

This process is trivial we simply keep on taking mod 26 from index till we have nothing left, and converting that value to char.

July 21, 2011

StringOutputStream backed by ByteArrayOutputStream

The other day I needed to read output stream directly into a string, but JDK does not provides us with a stream directly for that. There have been numerous discussions on why and why not we should have this build into the JDK, but this was a case where I did need it and knew the encoding.

My implementation

StringOutputStream backed by ByteArrayOutputStream that simplifies reading of OutputStream directly into string.


import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.Charset;

/**
 * StringOutputStream backed by ByteArrayOutputStream that simplifies  reading of {@link OutputStream}  directly into string
 * @author Grzegorz Bugaj
 *
 */
public class StringOutputStream extends OutputStream{
	private ByteArrayOutputStream buffer = new ByteArrayOutputStream();
	private static final Charset DEFAULT_CHARACTER_SET = Charset.forName("UTF-8");
	private Charset characterSet;
	
	@Override
	public void write(int b) throws IOException {
		buffer.write(b);
	}
	
	@Override
	public void write(byte[] b) throws IOException {
		buffer.write(b);
	}

	@Override
	public void write(byte[] b, int off, int len) throws IOException {
		buffer.write(b, off, len);
	}
	
	/**
	 * Get currently set character set if none set default charset of UTF-8 will be used
	 * @return Charaset 
	 */
	public Charset getCharacterSet() {
		if(characterSet == null)
			return DEFAULT_CHARACTER_SET;
		
		return characterSet;
	}

	public void setCharacterSet(Charset characterSet) {
		this.characterSet = characterSet;
	}
	
	
	@Override
	public String toString() {
		return new String(buffer.toByteArray(), getCharacterSet());
	}
}

Usage Example

	/**
	 * Read in given stream into a string
	 * @param stream
	 * @return
	 */
	public static String readAsString(InputStream stream) throws IOException{
		OutputStream sos = new StringOutputStream(); 		
		// Set chunk size to 64K chunk
		byte[] buffer = new byte[0x10000];
		int readLen;
		while((readLen = stream.read(buffer, 0, buffer.length)) != -1){
			sos.write(buffer, 0, readLen);
		}
		return sos.toString();
	}

July 8, 2011