ExcelUtil—（包括公式，合并单元格）

public class ExcelUtilStat {

 /**
  * 合併單元格
  *
  * @param rowFrom
  * @param cellFrom
  * @param rowTo
  * @param cellTo
  * @return
  */
 public static Region getRegion(int rowFrom, short cellFrom, int rowTo, short cellTo) {
  Region region = new Region(rowFrom, cellFrom, rowTo, cellTo);
  return region;
 }

 /**
  * 根據列所在索引位置取得字母
  *
  * @param cell
  * @return
  */
 public static String getCellWorld(int cell) {
  String str = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
  int m = ((cell) / 26) - 1;
  char a = str.charAt(cell % 26);
  return "" + (m == -1 ? "" : str.charAt(m)) + a;
 }

 /**
  * 取得單元格函數 (如MAX,MIN,AVERAGE,STDEVP)等等
  *
  * @param cellFrom
  *            計算的開始列
  * @param cellTo
  *            計算的結束列
  * @param rowFrom
  *            第幾行開始
  * @param rowTo
  *            第幾行結束
  * @param function
  *            Excel函數
  * @return
  */
 public static String getXToY(int cellFrom, int cellTo, int rowFrom, int rowTo, String function) {
  String resultX = getCellWorld(cellFrom) + rowFrom;
  String resultY = getCellWorld(cellTo) + rowTo;
  String XToY = "";
  String x1 = resultX + ":" + resultY;
  String x2 = function + "(" + resultX + ":" + resultY + ")";
  XToY = function == null ? x1 : x2;
  return XToY;
 }

 /**
  * 取得rank公式
  *
  * @param x
  *            計算的列
  * @param y
  *            列所在的行
  * @param yFrom
  *            第幾行開始
  * @param yTo
  *            第幾行結束
  * @param function
  *            Excel函數
  * @return
  */
 public static String getRank(int x, int y, int yFrom, int yTo, String function) {
  String a = getCellWorld(x) + y;
  String b = getXToY(x, x, yFrom, yTo, null);
  return function + "(" + a + "," + b + "," + 0 + ")";
 }
 
 public static void main(String[] args) {
  System.out.println(getRank(6, 3, 3, 5, "RANK"));
 }

 /**
  * 取得Normsinv公式， 操作如(M5+NORMSINV(NORMDIST(E3,E6,E7,TRUE))*N5)
  *
  * @param xm
  *            全體平均值的列
  * @param xn
  *            全體標準差的列
  * @param xo
  *            委員列
  * @param yFrom
  *            第幾行
  * @param rowTotal
  *            數據總行數
  * @return
  */
 public static String getNormsinv(int xm, int xn, int xo, int yFrom, int rowTotal) {
  String m5 = getCellWorld(xm) + yFrom;
  String n5 = getCellWorld(xn) + yFrom;
  String e3 = getCellWorld(xo) + yFrom;
  String e6 = getCellWorld(xo) + (rowTotal + 3);
  String e7 = getCellWorld(xo) + (rowTotal + 4);
  return m5 + "+NORMSINV(NORMDIST(" + e3 + "," + e6 + "," + e7 + ",TRUE))*" + n5;
 }

 /**
  * 取得標準後名次差異(如:T3-J3)
  *
  * @param xFrom
  * @param xTo
  * @param y
  * @return
  */
 public static String getVariance(int xFrom, int xTo, int y) {
  String startStr = getCellWorld(xFrom) + y;
  String endStr = getCellWorld(xTo) + y;
  return endStr + "-" + startStr;
 }

 /**
  * 設置工作表各列寬度
  * @param sheet
  * @param width
  */
 @SuppressWarnings("deprecation")
 public static void setColumnWidth(HSSFSheet sheet, int[] width) {
  for (int i = 0; i < width.length; i++) {
   sheet.setColumnWidth((short) i, (short) (width[i] * 256));
  }
 }

 /**
  * 設置邊框
  *
  * @param wb
  * @return
  */
 public static HSSFCellStyle createBorder(HSSFWorkbook wb) {
  HSSFCellStyle hcs = wb.createCellStyle();
  hcs.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  hcs.setBorderRight(HSSFCellStyle.BORDER_THIN);
  hcs.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  hcs.setBorderTop(HSSFCellStyle.BORDER_THIN);
  hcs.setBottomBorderColor(HSSFColor.BLACK.index);
  hcs.setTopBorderColor(HSSFColor.BLACK.index);
  hcs.setLeftBorderColor(HSSFColor.BLACK.index);
  hcs.setRightBorderColor(HSSFColor.BLACK.index);
  hcs.setFont(getFont(wb, (short) 12, "新細明體"));
  return hcs;
 }

 /**
  * 設置基本單元格格式
  *
  * @param wb
  * @return
  */
 public static HSSFCellStyle createBasicHcs(HSSFWorkbook wb) {
  HSSFCellStyle normalStyle = createBorder(wb);
  normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  normalStyle.setWrapText(true);
  return normalStyle;
 }
 
 /**
  * 設置邊框
  *
  * @param wb
  * @return
  */
 public static HSSFCellStyle createBorder(HSSFCellStyle cellStyle) {
  cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
  cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
  cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
  cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
  return cellStyle;
 }
 
 
 
 

 /**
  * 設置單元格對齊方式及顏色
  *
  * @param wb
  * @param x
  *            水平
  * @param y
  *            垂直
  * @return
  */
 public static HSSFCellStyle createBasicHcs(HSSFWorkbook wb, short x, short y, short color) {
  HSSFCellStyle normalStyle = createBasicHcs(wb);
  normalStyle.setAlignment(x);
  normalStyle.setVerticalAlignment(y);
  normalStyle.setWrapText(true);
  normalStyle.setFillForegroundColor(color);
  normalStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  return normalStyle;
 }

 /**
  * 設置單元格顏色
  *
  * @param wb
  * @return
  */
 public static HSSFCellStyle createHcs(HSSFWorkbook wb, short color) {
  HSSFCellStyle normalStyle = createBasicHcs(wb);
  normalStyle.setFillForegroundColor(color);
  normalStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  return normalStyle;
 }

 /**
  * 设置字体
  *
  * @param wb
  * @param height
  * @param fontName
  * @return
  */
 public static HSSFFont getFont(HSSFWorkbook wb, short height, String fontName) {
  HSSFFont font = wb.createFont();
  font.setFontHeightInPoints(height);
  font.setFontName(fontName);
  return font;
 }

 /**
  * 設置單元格為小數點為兩位
  *
  * @param wb
  * @return
  */
 public static HSSFCellStyle createHcsForDataFormat(HSSFWorkbook wb) {
  DataFormat format = wb.createDataFormat();
  HSSFCellStyle cellStyle = createBorder(wb);
  cellStyle.setDataFormat(format.getFormat("#,##0.00"));
  return cellStyle;
 }

 /**
  * 設置單元格為小數點為兩位及設置顏色
  *
  * @param wb
  * @param color
  * @return
  */
 public static HSSFCellStyle createHcsForDataFormat(HSSFWorkbook wb, short color) {
  HSSFCellStyle cellStyle = createHcsForDataFormat(wb);
  cellStyle.setFillForegroundColor(color);
  cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  return cellStyle;
 }

 

}