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!");
        }
    }
   
 
}