/*
* 2006.06.22 Alex SUO
* Add another constructor from file input stream.
*
* 2006.05.19 Alex SUO
* Add font related procedures.
*
* 2006.01.06 Alex SUO
* Change the methods to support data type definition.
* Add the data type convertion function.
* Fix the bug for the append data function.
*
* 2005.11.21 Alex SUO
* Add the get data from sheet methods
*
* Created on Nov 15, 2005
*
*/
package xxx.xxx.xxx.xxx;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.Iterator;
import java.util.Vector;

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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.bnpparibas.utils.IOUtil;

/**
* This class is the representation of a workbook in excel format.
* The implementation adds several methods to the exising functionalities
* in the HSSFWorkbook.
*
* @author Alex SUO
*/
public class Workbook extends HSSFWorkbook {

/**
* The demilinator for the data so that when
* using a vector for the data and each row will be
* an element, the cell strings are separated by this
* deliminator.
*/
public static final String DELI = ",";

/** The demilinator for a line. */
public static final String ENTER =
System.getProperty("line.separator");

/** The name of the default sheet. */
public static final String DEFAULT_SHEET = "Sheet1";

/** The allowed extension for the work book file. */
public static final String[] EXTS = {".xls"};

/** The file name of the workbook. */
public String fileName = "";

/**
* The default constructor.
*/
public Workbook() {}

/**
* Constructor inherited from super class.
* @param fs The file stream.
* @throws IOException If the super class throws it.
*/
public Workbook(POIFSFileSystem fs) throws IOException {
super(fs);
}

/**
* Constructor inherited from super class.
* @param is The input stream.
* @throws IOException If the super class throws it.
*/
public Workbook(InputStream is) throws IOException {
super(is);
}

/**
* The constructor with the given excel workbook.
*
* The given excel workbook will be loaded into the
* workbook.
*
* @param fileName The path of the excel workbook. Must
* be a .xls file.
* @return The instance of the workbook.
* @throws ExcelException if the file is not a valid
* excel workbook.
* @throws IOException if error reading the file.
*/
public static Workbook getWorkbook(String fileName)
throws ExcelException, IOException {
if (!isExcel(fileName)) {
throw new ExcelException("Not a valid excel file: " + fileName);
}
POIFSFileSystem fs = new POIFSFileSystem(
new FileInputStream(fileName));

Workbook wb = new Workbook(fs);
wb.fileName = fileName;
return wb;
}

/**
* Construct the workbook by creating file input stream.
*
* The given excel workbook will be loaded. POI package is not
* used due to a bug inside.
*
* @param fileName The path of the excel workbook. Must be a .xls file.
* @return The instance of the workbook.
* @throws ExcelException If the file is not a valid excel workbook.
* @throws IOException If error reading the file.
*/
public static Workbook getWorkbookByFile(String fileName)
throws ExcelException, IOException {
if (!isExcel(fileName)) {
throw new ExcelException("Not a valid excel file: " + fileName);
}
Workbook wb = new Workbook(new FileInputStream(fileName));
wb.fileName = fileName;
return wb;
}

/**
* Get the column types defined in the ref class by the string
* references.
* @param refClass The reference class.
* @param types The types in string.
* @return The array with column types in integere for use.
*/
public static int[] getColumnTypes(String refClass, String[] types)
throws ExcelException {
try {
Class c = Class.forName(refClass);
int[] result = new int[types.length];

for (int i = 0; i < result.length; i++) {
result[i] = c.getField(types[i]).getInt(c);
}
return result;
} catch (ClassNotFoundException e) {
throw new ExcelException("Reference class not found: " + refClass);
} catch (IllegalAccessException e) {
throw new ExcelException("Cannot access field in " + refClass);
} catch (NoSuchFieldException e) {
throw new ExcelException("Field not exist.");
}
}

/**
* Tranform a csv file to excel workbook.
*
* @param csvPath The path of the csv file.
* @param deli The deliminator between data fields.
* @param types The data types of columns. If this
* array is null, all the data types will be string.
* @param hasHeader Flag if the csv file has header row.
* @throws ExcelException if the given file is not valid.
* @throws IOException if failed to read the file.
*/
public static Workbook csvToWorkbook(
String csvPath, String deli, int[] types, boolean hasHeader)
throws ExcelException, IOException {
String content = IOUtil.readFile(csvPath);
String[] data = content.split(ENTER);
Vector v = new Vector(data.length);

for (int i = 0; i < data.length; i++) {
v.add(data[i]);
}

Iterator i = Arrays.asList(data).iterator();
Workbook wb = new Workbook();
wb.fileName = csvPath;
if (types == null) {
wb.dataToNewSheet(i, deli, DEFAULT_SHEET);
} else {
wb.dataToNewSheet(i, deli, DEFAULT_SHEET, types);
}
return wb;
}


/**
* Check if a given file is an excel file or not.
* @param path The path of the file.
* @return true if the path indicates an excel file, false otherwise.
*/
public static boolean isExcel(String path) {
boolean matched = false;
for (int i = 0; i < EXTS.length; i++) {
if (path.endsWith(EXTS[i])) {
matched = true;
break;
}
}
return matched;
}

/**
* Get the file name of the workbook.
* @return The file name. Empty string if the workbook
* is not created from any file.
*/
public String getFileName() {
return fileName;
}

/**
* Get the data from excel sheet with specified range.
*
* The data will be returned so that each row is an
* element in the vector. Data is presented as String for
* each cell and demilinated by ","
*
* @param index The index of the sheet.
* @param row The starting row number.
* @param col The starting column number.
* @param lastRow The last row number.
* @param lastCol The last column number.
* @return The vector contains the data, each row as an
* element, each cell data separated by ","
* @throws ExcelException if the sheet cannot be found.
*/
public Vector getSheetData(int index, int row, int col,
int lastRow, int lastCol) throws ExcelException {
HSSFSheet sheet = this.getSheetAt(index);
if (null == sheet) {
throw new ExcelException("Error: sheet " +
String.valueOf(index) +
" cannot be found in the workbook.");
}
return getSheetData(sheet, row, col, lastRow, lastCol);
}

/**
* Get the data from excel sheet with specified range.
*
* The data will be returned so that each row is an
* element in the vector. Data is presented as String for
* each cell and demilinated by ","
*
* @param name The sheet name.
* @param row The starting row number.
* @param col The starting column number.
* @param lastRow The last row number.
* @param lastCol The last column number.
* @return The vector contains the data, each row as an
* element, each cell data separated by ","
* @throws ExcelException if the sheet cannot be found.
*/
public Vector getSheetData(String name, int row, int col,
int lastRow, int lastCol) throws ExcelException {
HSSFSheet sheet = this.getSheet(name);
if (null == sheet) {
throw new ExcelException("Error: sheet " + name +
" cannot be found in the workbook.");
}
return getSheetData(sheet, row, col, lastRow, lastCol);
}

/**
* Get the data from the given sheet as Strings.
*
* Get the data from the given square in the given sheet
* as vector. Each row will be an element in the vector
* and each cell data in the row will be presented as string
* and separated by ","
*
* @param sheet The sheet.
* @param row The starting row number.
* @param col The starting column numner.
* @param lastRow The last row number.
* @param lastCol The last column number.
* @return The vector containing the data as specified.
*/
protected Vector getSheetData(HSSFSheet sheet, int row, int col,
int lastRow, int lastCol) {
Vector v = new Vector(lastRow - row);
StringBuffer sb = new StringBuffer();
HSSFRow tempRow;
HSSFCell tempCell;

for (int i = row; i < lastRow; i++) {
tempRow = sheet.getRow(i);
sb.setLength(0);
for (short j = (short)col; j < lastCol; j++) {
sb.append(getCellString(tempRow.getCell(j)));
sb.append(DELI);
}
sb.setLength(sb.length() - 1);
v.add(sb.toString());
}
return v;
}

/**
* Add title to the specified sheet.
*
* This method will insert a row into the sheet and
* set the titles. The existing data will not be affected.
*
* @param titles The array with all the titles.
* @param sheetName The name of the sheet.
*/
public void addTitle(String[] titles, String sheetName) {
HSSFSheet sheet = getSheet(sheetName);
sheet.shiftRows(sheet.getFirstRowNum(), sheet.getLastRowNum(), 1);
HSSFRow row = sheet.createRow(0);
HSSFCell cell;

for (short i = 0; i < titles.length; i++) {
cell = row.createCell(i);
cell.setCellValue(titles[i]);
}
}

/**
* Transform the data into a new sheet in the workbook.
*
* Each item in the iterator must represent a row by calling the
* <code>toString()</code> method of it. Then the item will be splitted
* to array using the deliminator and then filled to the cells.
*
* @param i The iterator for the data.
* @param deli The deliminator.
* @param sheetName The name of the sheet.
* @return The HSSFWorkbook instance with the data.
*/
public void dataToNewSheet(Iterator i, String deli, String sheetName) {
HSSFSheet sheet = createSheet();
setSheetName(getNumberOfSheets() - 1, sheetName);

HSSFRow row = null;
HSSFCell cell = null;
String[] buffer;
int index = 0;

while (i.hasNext()) {
buffer = i.next().toString().split(deli);
row = sheet.createRow(index++);
for (short j = 0; j < buffer.length; j++) {
cell = row.createCell(j);
cell.setCellValue(buffer[j]);
}
}
}

/**
* Transform the data into a new sheet in the workbook with
* specified types.
*
* Each item in the iterator must represent a row by calling the
* <code>toString()</code> method of it. Then the item will be splitted
* to array using the deliminator and then filled to the cells.
*
* @param i The iterator for the data.
* @param deli The deliminator.
* @param sheetName The name of the sheet.
* @param types The array holding the types of the data. Must be
* the same length as the deliminated string.
* @return The HSSFWorkbook instance with the data.
* @throws ExcelException if failed to conver the data to the sheet.
*/
public void dataToNewSheet(Iterator i, String deli, String sheetName,
int[] types) throws ExcelException {
HSSFSheet sheet = createSheet();
setSheetName(getNumberOfSheets() - 1, sheetName);

HSSFRow row = null;
HSSFCell cell = null;
String[] buffer;
int index = 0;

while (i.hasNext()) {
buffer = i.next().toString().split(deli);
row = sheet.createRow(index++);
for (short j = 0; j < buffer.length; j++) {
cell = row.createCell(j);
try {
setCellValue(cell, buffer[j], types[j]);
} catch (ArrayIndexOutOfBoundsException e) {
throw new ExcelException(
"Error: different sizes of data and types.", e);
}
}
}
}

/**
* Convert the string to the specified value and set to the cell.
* @param cell The cell.
* @param s The value of the cell to set in string format.
* @param type The type of data for the cell.
* @throws ExcelException If the data conversion failed.
*/
protected void setCellValue(HSSFCell cell, String s, int type)
throws ExcelException {
try {
//for empty data, deal it specially
if (s.trim().length() == 0) {
cell.setCellValue("");
cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
} else {
switch (type) {
case HSSFCell.CELL_TYPE_BLANK :
cell.setCellValue("");
break;
case HSSFCell.CELL_TYPE_BOOLEAN :
cell.setCellValue(
Boolean.valueOf(s).booleanValue());
break;
case HSSFCell.CELL_TYPE_FORMULA :
cell.setCellFormula(s);
break;
case HSSFCell.CELL_TYPE_NUMERIC :
cell.setCellValue(Double.parseDouble(s));
break;
case HSSFCell.CELL_TYPE_STRING :
cell.setCellValue(s);
break;
}
}
} catch (NumberFormatException e) {
throw new ExcelException("Cell value not a number", e);
}
}

/**
* Get the cell value as string.
* @param cell The cell.
* @param s The value of the cell to set in string format.
* @param type The type of data for the cell.
* @throws ExcelException If the data conversion failed.
*/
protected String getCellString(HSSFCell cell) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_BLANK :
return "";
case HSSFCell.CELL_TYPE_BOOLEAN :
return String.valueOf(cell.getBooleanCellValue());
case HSSFCell.CELL_TYPE_FORMULA :
return cell.getCellFormula();
case HSSFCell.CELL_TYPE_NUMERIC :
return String.valueOf(cell.getNumericCellValue());
case HSSFCell.CELL_TYPE_STRING :
return cell.getStringCellValue();
default:
return null;
}
}

/**
* Append the given data at the end of the sheet.
*
* The data will be appended at the end of the sheet. Each
* item in the iterator is assumed to compose a row, with each
* cell data separated by the <code>deli</code> paramter.
*
* @param i The iterator for traversing the data.
* @param deli The deliminator if the data in a row.
* @param sheetName The name of the sheet.
* @throws ExcelException If no sheet with the given name is
* found in the workbook.
*/
public void appendRowToSheet(Iterator i, String deli, String sheetName)
throws ExcelException {
HSSFSheet sheet = getSheet(sheetName);
if (null == sheet) {
throw new ExcelException("No sheet with name " +
sheetName + " found.");
}

HSSFRow row = null;
HSSFCell cell = null;
String[] buffer;
int index =
sheet.getPhysicalNumberOfRows() == 0 ? 0 :
sheet.getLastRowNum() + 1;

while (i.hasNext()) {
buffer = i.next().toString().split(deli);
row = sheet.createRow(index++);
for (short j = 0; j < buffer.length; j++) {
cell = row.createCell(j);
cell.setCellValue(buffer[j]);
}
}
}

/**
* Append the given data at the end of the sheet.
*
* The data will be appended at the end of the sheet. Each
* item in the iterator is assumed to compose a row, with each
* cell data separated by the <code>deli</code> paramter.
*
* @param i The iterator for traversing the data.
* @param deli The deliminator if the data in a row.
* @param sheetName The name of the sheet.
* @param types The types of the columns.
* @throws ExcelException If no sheet with the given name is
* found in the workbook.
*/
public void appendRowToSheet(Iterator i, String deli, String sheetName,
int[] types) throws ExcelException {
HSSFSheet sheet = getSheet(sheetName);
if (null == sheet) {
throw new ExcelException("No sheet with name " +
sheetName + " found.");
}

HSSFRow row = null;
HSSFCell cell = null;
String[] buffer;
int index =
sheet.getPhysicalNumberOfRows() == 0 ? 0 :
sheet.getLastRowNum() + 1;

while (i.hasNext()) {
buffer = i.next().toString().split(deli);
row = sheet.createRow(index++);
for (short j = 0; j < buffer.length; j++) {
cell = row.createCell(j);
try {
setCellValue(cell, buffer[j], types[j]);
} catch (ArrayIndexOutOfBoundsException e) {
throw new ExcelException(
"Error: different sizes of data and types.", e);
}
}
}
}

/**
* Get a font with the specific name, height, and style.
* @param wb The workbook on which to create the font.
* @param height The height of the font in point.
* @param name The name of the font.
* @param boldWeight The bold weight of the font.
* @param italic Flag if the font is italic.
* @param strikeOut Flag if the font is strked out.
* @return The font with the attributes as specified.
*/
public HSSFFont getFont(int height, String name,
int boldWeight, boolean italic, boolean strikeOut) {
HSSFFont font = createFont();
font.setFontHeightInPoints((short)height);
font.setFontName(name);
font.setBoldweight((short)boldWeight);
font.setItalic(italic);
font.setStrikeout(strikeOut);

return font;
}

/**
* Set the font for the entire sheet.
* @param font The font to be set.
* @param sheet The sheet whose font to be changed.
*/
public void setFont(HSSFFont font, HSSFSheet sheet) {
HSSFRow row;
HSSFCell cell;
HSSFCellStyle style = createCellStyle();
style.setFont(font);

for (int i = sheet.getFirstRowNum();
i <= sheet.getLastRowNum(); i++) {
if ( (row = sheet.getRow(i)) == null) {
continue;
}

//skip the row(s) which has no cells
if (row.getPhysicalNumberOfCells() == 0) {
continue;
}
for (int j = row.getFirstCellNum();
j <= row.getLastCellNum(); j++) {
if ((cell = row.getCell( (short)j )) == null) {
continue;
}
cell.setCellStyle(style);
}
}
}

/**
* Write the given workbook to file.
* @param path The path of the file.
* @throws Exception If any problem during writting the file
*/
public void writeExcel(String path) throws IOException {
FileOutputStream fos = null;
try {
fos = new FileOutputStream(path);
write(fos);
} finally {
if (null != fos) {
fos.close();
}
}
}
}