package co.B2B.jouhou.common;

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.hssf.util.Region;
import org.apache.poi.pos.filesystem.POIFSFile;

import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.math.BigDecimal;


public CommonExcel {
private boolean bOpened;
private HSSFWorkbook objWorkbook;
private HSSFSheet objSheet;
private rc = [2];

public CommonExcel {
bOpened = false;
objWorkbook = null;
objSheet = null;
}

public HSSFWorkbook getWorkBook {
objWorkbook;
}
/**
* Excel Open
* @param tempFileName (i/ ):
*/
public void open(String tempFileName) throws Exception {
(bOpened true) {
throw Exception("Excel is opening!");
} {
FileInputStream fs = FileInputStream(tempFileName);
objWorkbook = HSSFWorkbook(fs);
objSheet = objWorkbook.getSheetAt(0);

bOpened = true;
}
}

/**
* save file
* @param fileName (i/ ):
*/
public void save(String fileName) throws Exception {
(bOpened true) {
FileOutputStream fs = FileOutputStream(fileName);
objWorkbook.write(fs);
fs.flush;
fs.close;
} {
throw Exception("Excel is not managed!");
}
}

/**
* OutputStream
* @param outputStream
*/
public void save(OutputStream outputStream) throws Exception {
(bOpened true) {
BufferedOutputStream out = BufferedOutputStream(outputStream);
objWorkbook.write(out);
out.flush;
out.close;
} {
throw Exception("Excel is not managed!");
}
}

/**
* closed
*
*/
public void close throws Exception {
(bOpened true) {
objSheet = null;
objWorkbook = null;
bOpened = false;
} {
throw Exception("Excel is not managed!");
}
}

/**
* Edit sheet
* @param index (i/ ): sheet no
*/
public void EditSheet( index) throws Exception {
(bOpened true) {
objSheet = objWorkbook.getSheetAt(index);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param sheetName (i/ ):
*/
public void EditSheet(String sheetName) throws Exception {
(bOpened true) {
objSheet = objWorkbook.getSheet(sheetName);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param index (i/ ):
*/
public void deleteSheet( index) throws Exception {
(bOpened true) {
objWorkbook.removeSheetAt(index);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param index (i/ ):
*/
public void deleteSheet(String sheetName) throws Exception {
(bOpened true) {
this.deleteSheet(objWorkbook.getSheetIndex(sheetName));
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param row (i/ ): row
* @param col (i/ ): column
* @

*/
public String getCellName( row, col) {
String str1 = "";
String str2 = "";
String str = "";
String list = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

((col + 1) > 26) {
con1 = (col) / 26;
str1 = list.sub(con1 - 1, con1);

con2 = (col + 1) - (26 * con1);
str2 = list.sub(con2 - 1, con2);
} {
str2 = list.sub(col, col + 1);
}

row;
str = str1 + str2 + row;

str;
}

/**
*
* @param cellName (i/ ):
* @param row (/o):
* @param col (/o):
*/
public void getCellRowCol(String cellName, rowcol) {
String list = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

(list.indexOf(cellName.sub(1, 2).toUpperCase) < 0) {
rowcol[1] = list.indexOf(cellName.sub(0, 1).toUpperCase);
rowcol[0] = Integer.parseInt(cellName.sub(1)) - 1;
} {
con1 = list.indexOf(cellName.sub(0, 1).toUpperCase);
con2 = list.indexOf(cellName.sub(1, 2).toUpperCase);
rowcol[1] = ((con1 + 1) * 26) + con2;
rowcol[0] = Integer.parseInt(cellName.sub(2)) - 1;


}
}

/**
*
* @param row (i/ ):
* @param col (i/ ):
* @param value (i/ ):
*/
public void CellValue( row, col, String value)
throws Exception {
(bOpened true) {
HSSFRow objRow = objSheet.getRow(row);
HSSFCell objCell = objRow.getCell(() col);
objCell.Encoding(HSSFWorkbook.ENCODING_UTF_16);
objCell.CellValue(value);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param row (i/ ):
* @param col (i/ ):
* @param value (i/ ):
*/
public void CellValue( row, col, double value)
throws Exception {
(bOpened true) {
HSSFRow objRow = objSheet.getRow(row);
HSSFCell objCell = objRow.getCell(() col);
objCell.CellValue(value);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param row (i/ ):
* @param col (i/ ):
* @param value (i/ ):
*/
public void CellNumberValue( row, col, String value)
throws Exception {
(value null || "".equals(value)) {
;
} {
try {
double tmpValue = BigDecimal(value).doubleValue;
CellValue(row, col, tmpValue);
} catch (Exception ex) {
CellValue(row, col, value);
}
}
}

/**
*
* @param row (i/ ):
* @param col (i/ ):
* @param value (i/ ):
*/
public void CellNumberValue(String cellName, String value)
throws Exception {
(bOpened true) {
this.getCellRowCol(cellName, rc);
} {
throw Exception("Excel is not managed!");
}

(value null || "".equals(value)) {
;
} {
try {
double tmpValue = BigDecimal(value).doubleValue;
CellValue(rc[0], rc[1], tmpValue);
} catch (Exception ex) {
CellValue(rc[0], rc[1], value);
}
}
}

/**
*
* @param sheetName (i/ ):
*/
public void SheetSelected(String sheetName)
throws Exception {
(bOpened true) {
objSheet = objWorkbook.getSheet(sheetName);
objSheet.Selected(true);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param cellName (i/ ):
* @param value (i/ ):
*/
public void CellValue(String cellName, String value)
throws Exception {
(bOpened true) {
this.getCellRowCol(cellName, rc);
this.CellValue(rc[0], rc[1], value);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param cellName (i/ ):
* @param value (i/ ):
*/
public void CellValue(String cellName, double value)
throws Exception {
(bOpened true) {
this.getCellRowCol(cellName, rc);
this.CellValue(rc[0], rc[1], value);
} {
throw Exception("Excel is not managed!");
}
}

private HSSFCellStyle cloneCellStyle(HSSFCellStyle objStyle1) {
HSSFCellStyle objStyle2 = objWorkbook.createCellStyle;
objStyle2.Alignment(objStyle1.getAlignment);
objStyle2.BorderBottom(objStyle1.getBorderBottom);
objStyle2.BorderLeft(objStyle1.getBorderLeft);
objStyle2.BorderRight(objStyle1.getBorderRight);
objStyle2.BorderTop(objStyle1.getBorderTop);
objStyle2.BottomBorderColor(objStyle1.getBottomBorderColor);
objStyle2.DataFormat(objStyle1.getDataFormat);
objStyle2.FillBackgroundColor(objStyle1.getFillBackgroundColor);
objStyle2.FillForegroundColor(objStyle1.getFillForegroundColor);
objStyle2.FillPattern(objStyle1.getFillPattern);
objStyle2.Font(objWorkbook.getFontAt(objStyle1.getFontIndex));
objStyle2.Hidden(objStyle1.getHidden);
objStyle2.Indention(objStyle1.getIndention);
objStyle2.LeftBorderColor(objStyle1.getLeftBorderColor);
objStyle2.Locked(objStyle1.getLocked);
objStyle2.RightBorderColor(objStyle1.getRightBorderColor);
objStyle2.Rotation(objStyle1.getRotation);
objStyle2.TopBorderColor(objStyle1.getTopBorderColor);
objStyle2.VerticalAlignment(objStyle1.getVerticalAlignment);
objStyle2.WrapText(objStyle1.getWrapText);

objStyle2;
}

/**
*
* @param row (i/ ):
* @param col (i/ ):
* @param clrIndex (i/ ):
*/
public void CellColor( row, col, clrIndex)
throws Exception {
(bOpened true) {
HSSFRow objRow = objSheet.getRow(row);
HSSFCell objCell = objRow.getCell(() col);
HSSFCellStyle objStyle = objCell.getCellStyle;//cloneCellStyle(objCell.getCellStyle);


objStyle.FillPattern(HSSFCellStyle.SOLID_FOREGROUND);
objStyle.FillForegroundColor(() clrIndex);
objCell.CellStyle(objStyle);
} {
throw Exception("Excel is not managed!");
}
}


/**
*
* @param row (i/ ):
* @param col (i/ ):
* @param clrIndex (i/ ):
*/
public void FontColor( row, col, clrIndex, fontSize)
throws Exception {
(bOpened true) {
HSSFRow objRow = objSheet.getRow(row);
HSSFCell objCell = objRow.getCell(() col);
HSSFCellStyle objStyle = objCell.getCellStyle;//cloneCellStyle(objCell.getCellStyle);
HSSFFont data_font = objWorkbook.createFont;
data_font.Color(() clrIndex);
data_font.FontHeightInPos(() fontSize);
data_font.FontName("MS PGothic");
objStyle.Font(data_font);
objCell.CellStyle(objStyle);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param cellName (i/ ):
* @param clrIndex (i/ ):
*/
public void CellColor(String cellName, clrIndex)
throws Exception {
(bOpened true) {
this.getCellRowCol(cellName, rc);
this.CellColor(rc[0], rc[1], clrIndex);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param cellName (i/ ):
* @param clrIndex (i/ ):
*/
public void WidthColumn( col, width)
throws Exception {
(bOpened true) {
objSheet.ColumnWidth(()col,()width);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param cellName (i/ ):
* @param clrIndex (i/ ):
*/
public void AutoWidthColumn( col)
throws Exception {
(bOpened true) {
objSheet.autoSizeColumn(()col);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param cellName (i/ ):
* @param clrIndex (i/ ):
*/
public void FontColor(String cellName, clrIndex, fontSize)
throws Exception {
(bOpened true) {
this.getCellRowCol(cellName, rc);
this.FontColor(rc[0], rc[1], clrIndex, fontSize);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param row (i/ ):
* @param col (i/ ):
* @param formula (i/ ):
*/
public void CellFormula( row, col, String formula)
throws Exception {
(bOpened true) {
HSSFRow objRow = objSheet.getRow(row);
HSSFCell objCell = objRow.getCell(() col);
objCell.CellFormula(formula);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param cellName (i/ ):
* @param formula (i/ ):
*/
public void CellFormula(String cellName, String formula)
throws Exception {
(bOpened true) {
this.getCellRowCol(cellName, rc);
this.CellFormula(rc[0], rc[1], formula);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param row (i/ ):
* @param col (i/ ):
* @param link (i/ ):
*/
public void addCellLink( row, col, String link)
throws Exception {
(bOpened true) {
HSSFRow objRow = objSheet.getRow(row);
HSSFCell objCell = objRow.getCell(() col);
objCell.CellFormula("HYPERLINK(\"" + link + "\",\"" +
objCell.getStringCellValue + "\")");
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param cellName (i/ ):
* @param link (i/ ):
*/
public void addCellLink(String cellName, String link)
throws Exception {
(bOpened true) {
this.getCellRowCol(cellName, rc);
this.addCellLink(rc[0], rc[1], link);
} {
throw Exception("Excel is not managed!");
}
}

public void copySheet(HSSFWorkbook originWorkBook, String pSourceSheetName,HSSFWorkbook targetWorkBook, String pTargetSheetName) {
HSSFRow sourceRow = null;
HSSFRow targetRow = null;
HSSFCell sourceCell = null;
HSSFCell targetCell = null;
HSSFSheet sourceSheet = null;
HSSFSheet targetSheet = null;
Region region = null;
pStartRow = 0;
pEndRow = 0;
pPosition = 0;

sourceSheet = originWorkBook.getSheet(pSourceSheetName);
targetSheet = targetWorkBook.getSheet(pTargetSheetName);
pStartRow = sourceSheet.getFirstRowNum;
pEndRow = sourceSheet.getLastRowNum;

((pStartRow -1) || (pEndRow -1)) {
;


}

for ( i = 0; i < sourceSheet.getNumMergedRegions; i) {
region = sourceSheet.getMergedRegionAt(i);
((region.getRowFrom >= pStartRow) && (region.getRowTo <= pEndRow)) {
targetRowFrom = region.getRowFrom - pStartRow + pPosition;
targetRowTo = region.getRowTo - pStartRow + pPosition;
region.RowFrom(targetRowFrom);
region.RowTo(targetRowTo);
targetSheet.addMergedRegion(region);
}
}

for ( i = 0; i <= pEndRow; i) {
sourceRow = sourceSheet.getRow(i);
(sourceRow null) {
continue;
}
targetRow = targetSheet.createRow(i - pStartRow + pPosition);
targetRow.Height(sourceRow.getHeight);
for ( j = sourceRow.getFirstCellNum; j < sourceRow.getLastCellNum; j) { //sourceRow.getPhysicalNumberOfCells
sourceCell = sourceRow.getCell(j);
(sourceCell null) {
continue;
}
targetCell = targetRow.createCell(j);
targetCell.Encoding(sourceCell.getEncoding);
targetCell.CellStyle(sourceCell.getCellStyle);
cType = sourceCell.getCellType;
targetCell.CellType(cType);
switch (cType) {
HSSFCell.CELL_TYPE_BOOLEAN:
targetCell.CellValue(sourceCell.getBooleanCellValue);
;
HSSFCell.CELL_TYPE_ERROR:
targetCell.CellErrorValue(sourceCell.getErrorCellValue);
;
HSSFCell.CELL_TYPE_FORMULA:
targetCell.CellFormula(sourceCell.getCellFormula);
;
HSSFCell.CELL_TYPE_NUMERIC:
targetCell.CellValue(sourceCell.getNumericCellValue);
;
HSSFCell.CELL_TYPE_STRING:
targetCell.CellValue(sourceCell.getStringCellValue);
;
}
}
}
}


/**
*
* @param originSheetIndex (i/ ):
* @param originLeftTopRow (i/ ):
* @param originLeftTopCol (i/ ):
* @param originRightBottomRow (i/ ):
* @param originRightBottomCol (i/ ):
* @param targetSheetIndex (i/ ):
* @param targetLeftTopRow (i/ ):
* @param targetLeftTopCol (i/ ):
*/
public void copyRange( originSheetIndex, originLeftTopRow,
originLeftTopCol, originRightBottomRow,
originRightBottomCol, targetSheetIndex, targetLeftTopRow,
targetLeftTopCol) throws Exception {
(bOpened true) {
HSSFSheet sheet1 = objWorkbook.getSheetAt(originSheetIndex);
HSSFSheet sheet2 = objWorkbook.getSheetAt(targetSheetIndex);

for ( i = 0; i <= (originRightBottomRow - originLeftTopRow);
i) {
HSSFRow row1 = sheet1.getRow(originLeftTopRow + i);

(row1 null) {
continue;
}

HSSFRow row2 = sheet2.getRow(targetLeftTopRow + i);

(row2 null) {
row2 = sheet2.createRow(targetLeftTopRow + i);
row2.Height(row1.getHeight);
}

for ( j = 0; j <= (originRightBottomCol - originLeftTopCol);
j) {
HSSFCell cell1 = row1.getCell(() (originLeftTopCol +
j));

(cell1 null) {
continue;
}

HSSFCell cell2 = row2.getCell(() (targetLeftTopCol +
j));

(cell2 null) {
cell2 = row2.createCell(() (targetLeftTopCol + j));
//sheet2.ColumnWidth(() (targetLeftTopCol + j),sheet1.getColumnWidth(() (targetLeftTopCol + j)));
}

cell2.Encoding(cell1.getEncoding);
cell2.CellStyle(cell1.getCellStyle);

cellType = cell1.getCellType;

(cellType HSSFCell.CELL_TYPE_STRING) {
cell2.CellValue(cell1.getStringCellValue);
} (cellType HSSFCell.CELL_TYPE_NUMERIC) {
cell2.CellValue(cell1.getNumericCellValue);
}
}
}

for ( i = 0; i < sheet1.getNumMergedRegions; i) {
Region region1 = sheet1.getMergedRegionAt(i);
LeftTopRow = region1.getRowFrom;
LeftTopCol = region1.getColumnFrom;
RightBottomRow = region1.getRowTo;
RightBottomCol = region1.getColumnTo;

((LeftTopRow >= originLeftTopRow) &&
(LeftTopCol >= originLeftTopCol) &&
(RightBottomRow <= originRightBottomRow) &&
(RightBottomCol <= originRightBottomCol)) {
LeftTopRow = targetLeftTopRow +
(LeftTopRow - originLeftTopRow);
LeftTopCol = targetLeftTopCol +
(LeftTopCol - originLeftTopCol);
RightBottomRow = targetLeftTopRow +
(RightBottomRow - originLeftTopRow);
RightBottomCol = targetLeftTopCol +
(RightBottomCol - originLeftTopCol);
sheet2.addMergedRegion( Region(() LeftTopRow,
() LeftTopCol, () RightBottomRow,
() RightBottomCol));
}
}
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param originSheetName (i/ ):


* @param originLeftTopCellName (i/ ):
* @param originRightBottomCellName (i/ ):
* @param targetSheetName (i/ ):
* @param targetLeftTopCellName (i/ ):
*/
public void copyRange(String originSheetName, String originLeftTopCellName,
String originRightBottomCellName, String targetSheetName,
String targetLeftTopCellName) throws Exception {
(bOpened true) {
originSheetIndex;
originLeftTopRow;
originLeftTopCol;
originRightBottomRow;
originRightBottomCol;
targetSheetIndex;
targetLeftTopRow;
targetLeftTopCol;
originSheetIndex = objWorkbook.getSheetIndex(originSheetName);
this.getCellRowCol(originLeftTopCellName, rc);
originLeftTopRow = rc[0];
originLeftTopCol = rc[1];
this.getCellRowCol(originRightBottomCellName, rc);
originRightBottomRow = rc[0];
originRightBottomCol = rc[1];
targetSheetIndex = objWorkbook.getSheetIndex(targetSheetName);
this.getCellRowCol(targetLeftTopCellName, rc);
targetLeftTopRow = rc[0];
targetLeftTopCol = rc[1];
this.copyRange(originSheetIndex, originLeftTopRow,
originLeftTopCol, originRightBottomRow, originRightBottomCol,
targetSheetIndex, targetLeftTopRow, targetLeftTopCol);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param SheetName (i/ ):
*/
public void addSheet(String SheetName) throws Exception {
(bOpened true) {
objWorkbook.createSheet(SheetName);
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param leftTopRow (i/ ):
* @param leftTopCol (i/ ):
* @param rightBottomRow (i/ ):
* @param rightBottomCol (i/ ):
*/
public void MergeRegion( leftTopRow, leftTopCol,
rightBottomRow, rightBottomCol) throws Exception {
(bOpened true) {
objSheet.addMergedRegion( Region(() leftTopRow,
() leftTopCol, () rightBottomRow,
() rightBottomCol));
} {
throw Exception("Excel is not managed!");
}
}

/**
*
* @param leftTopCellName (i/ ):
* @param rightBottomCellName (i/ ):
*/
public void MergeRegion(String leftTopCellName,
String rightBottomCellName) throws Exception {
(bOpened true) {
lr = 0;
lc = 0;
this.getCellRowCol(leftTopCellName, rc);
lr = rc[0];
lc = rc[1];
this.getCellRowCol(rightBottomCellName, rc);
this.MergeRegion(lr, lc, rc[0], rc[1]);
} {
throw Exception("Excel is not managed!");
}
}

}