poi开发Excel的共通库

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.poifs.filesystem.POIFSFileSystem;

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

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

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

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

bOpened = true;
}
}

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

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

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

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

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

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

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

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

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

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

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

row++;
str = str1 + str2 + row;

return str;
}

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

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

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

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

/**
*
* @param row (i/ ):
* @param col (i/ ):
* @param value (i/ ):
*/
public void setCellNumberValue(int row, int col, String value)
throws Exception {
if (value == null || "".equals(value)) {
return;
} else {
try {
double tmpValue = new BigDecimal(value).doubleValue();
setCellValue(row, col, tmpValue);
} catch (Exception ex) {
setCellValue(row, col, value);
}
}
}

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

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

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

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

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

private HSSFCellStyle cloneCellStyle(HSSFCellStyle objStyle1) {
HSSFCellStyle objStyle2 = objWorkbook.createCellStyle();
objStyle2.setAlignment(objStyle1.getAlignment());
objStyle2.setBorderBottom(objStyle1.getBorderBottom());
objStyle2.setBorderLeft(objStyle1.getBorderLeft());
objStyle2.setBorderRight(objStyle1.getBorderRight());
objStyle2.setBorderTop(objStyle1.getBorderTop());
objStyle2.setBottomBorderColor(objStyle1.getBottomBorderColor());
objStyle2.setDataFormat(objStyle1.getDataFormat());
objStyle2.setFillBackgroundColor(objStyle1.getFillBackgroundColor());
objStyle2.setFillForegroundColor(objStyle1.getFillForegroundColor());
objStyle2.setFillPattern(objStyle1.getFillPattern());
objStyle2.setFont(objWorkbook.getFontAt(objStyle1.getFontIndex()));
objStyle2.setHidden(objStyle1.getHidden());
objStyle2.setIndention(objStyle1.getIndention());
objStyle2.setLeftBorderColor(objStyle1.getLeftBorderColor());
objStyle2.setLocked(objStyle1.getLocked());
objStyle2.setRightBorderColor(objStyle1.getRightBorderColor());
objStyle2.setRotation(objStyle1.getRotation());
objStyle2.setTopBorderColor(objStyle1.getTopBorderColor());
objStyle2.setVerticalAlignment(objStyle1.getVerticalAlignment());
objStyle2.setWrapText(objStyle1.getWrapText());

return objStyle2;
}

/**
*
* @param row (i/ ):
* @param col (i/ ):
* @param clrIndex (i/ ):
*/
public void setCellColor(int row, int col, int clrIndex)
throws Exception {
if (bOpened == true) {
HSSFRow objRow = objSheet.getRow(row);
HSSFCell objCell = objRow.getCell((short) col);
HSSFCellStyle objStyle = objCell.getCellStyle();//cloneCellStyle(objCell.getCellStyle());
objStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
objStyle.setFillForegroundColor((short) clrIndex);
objCell.setCellStyle(objStyle);
} else {
throw new Exception("Excel is not managed!");
}
}

/**
*
* @param row (i/ ):
* @param col (i/ ):
* @param clrIndex (i/ ):
*/
public void setFontColor(int row, int col, int clrIndex,int fontSize)
throws Exception {
if (bOpened == true) {
HSSFRow objRow = objSheet.getRow(row);
HSSFCell objCell = objRow.getCell((short) col);
HSSFCellStyle objStyle = objCell.getCellStyle();//cloneCellStyle(objCell.getCellStyle());
HSSFFont data_font = objWorkbook.createFont();
data_font.setColor((short) clrIndex);
data_font.setFontHeightInPoints((short) fontSize);
data_font.setFontName("MS PGothic");
objStyle.setFont(data_font);
objCell.setCellStyle(objStyle);
} else {
throw new Exception("Excel is not managed!");
}
}

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

/**
*
* @param cellName (i/ ):
* @param clrIndex (i/ ):
*/
public void setWidthColumn(int col, int width)
throws Exception {
if (bOpened == true) {
objSheet.setColumnWidth((short)col,(short)width);
} else {
throw new Exception("Excel is not managed!");
}
}

/**
*
* @param cellName (i/ ):
* @param clrIndex (i/ ):
*/
public void setAutoWidthColumn(int col)
throws Exception {
if (bOpened == true) {
objSheet.autoSizeColumn((short)col);
} else {
throw new Exception("Excel is not managed!");
}
}

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

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

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

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

/**
*
* @param cellName (i/ ):
* @param link (i/ ):
*/
public void addCellLink(String cellName, String link)
throws Exception {
if (bOpened == true) {
this.getCellRowCol(cellName, rc);
this.addCellLink(rc[0], rc[1], link);
} else {
throw new 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;
int pStartRow = 0;
int pEndRow = 0;
int pPosition = 0;

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

if ((pStartRow == -1) || (pEndRow == -1)) {
return;
}

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

for (int i = 0; i <= pEndRow; i++) {
sourceRow = sourceSheet.getRow(i);
if (sourceRow == null) {
continue;
}
targetRow = targetSheet.createRow(i - pStartRow + pPosition);
targetRow.setHeight(sourceRow.getHeight());
for (short j = sourceRow.getFirstCellNum(); j < sourceRow.getLastCellNum(); j++) { //sourceRow.getPhysicalNumberOfCells()
sourceCell = sourceRow.getCell(j);
if (sourceCell == null) {
continue;
}
targetCell = targetRow.createCell(j);
targetCell.setEncoding(sourceCell.getEncoding());
targetCell.setCellStyle(sourceCell.getCellStyle());
int cType = sourceCell.getCellType();
targetCell.setCellType(cType);
switch (cType) {
case HSSFCell.CELL_TYPE_BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
targetCell.setCellFormula(sourceCell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
targetCell.setCellValue(sourceCell.getStringCellValue());
break;
}
}
}
}

/**
*
* @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(int originSheetIndex, int originLeftTopRow,
int originLeftTopCol, int originRightBottomRow,
int originRightBottomCol, int targetSheetIndex, int targetLeftTopRow,
int targetLeftTopCol) throws Exception {
if (bOpened == true) {
HSSFSheet sheet1 = objWorkbook.getSheetAt(originSheetIndex);
HSSFSheet sheet2 = objWorkbook.getSheetAt(targetSheetIndex);

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

if (row1 == null) {
continue;
}

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

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

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

if (cell1 == null) {
continue;
}

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

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

cell2.setEncoding(cell1.getEncoding());
cell2.setCellStyle(cell1.getCellStyle());

int cellType = cell1.getCellType();

if (cellType == HSSFCell.CELL_TYPE_STRING) {
cell2.setCellValue(cell1.getStringCellValue());
} else if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
cell2.setCellValue(cell1.getNumericCellValue());
}
}
}

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

if ((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(new Region((short) LeftTopRow,
(short) LeftTopCol, (short) RightBottomRow,
(short) RightBottomCol));
}
}
} else {
throw new 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 {
if (bOpened == true) {
int originSheetIndex;
int originLeftTopRow;
int originLeftTopCol;
int originRightBottomRow;
int originRightBottomCol;
int targetSheetIndex;
int targetLeftTopRow;
int 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);
} else {
throw new Exception("Excel is not managed!");
}
}

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

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

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


}