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;
?}
?
}
