POI Excel相關常用功能紀錄

參考來源：[POI]寫入Excel小記
                 POI - 瘋人院院長的筆記本

POI是一個用Java處理Windows office相關處理的專案
目前工作上會用到Excel的功能
以下紀錄常用功能


File tempFile = new File(filePath,filename);                        //建立儲存檔案
HSSFWorkbook workbook = new HSSFWorkbook();   //建立Excel物件
String safeName = WorkbookUtil.createSafeSheetName(SHEETNAME);
Sheet sheet = workbook.createSheet(safeName);             //建立工作表
workbook.setSheetName(index, name);                            //更改工作表名稱
Row row1 = sheet.createRow((short)0);                           //建立工作列

/*
讀取Excel Template的方式
String filePath = "...";     //Excel template路徑
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream( filePath ));
HSSFWorkbook hwb = new HSSFWorkbook(fs);
*/

//CellReference 用來轉換 A1 <-> 0,0   B2 <-> 1,1
CellReference cr1 = new CellReference(1, 1);
cr1.formatAsString() ===>  "B2"

//字型設定
Font font = workbook.createFont();

font.setFontName("新細明體");                                     //設定字體
font.setFontHeightInPoints((short) 14);                            //設定字體大小

font.setColor(HSSFColor.WHITE.index);                       //顏色
font.setBoldweight(Font.BOLDWEIGHT_BOLD);         //粗體

//設定儲存格格式，包含字體大小等
CellStyle styleRow1 = workbook.createCellStyle();

styleRow1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  //設置可填充儲存格底色
styleRow1.setFillForegroundColor(HSSFColor.YELLOW.index);      //指定底色
styleRow1.setFont(font);                                                                    //設定字體
styleRow1.setAlignment(HSSFCellStyle.ALIGN_CENTER);                   //水平置中
styleRow1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直置中
http://stackoverflow.com/questions/5335285/write-number-in-excel-cell-with-poi

//設定框線
styleRow1.setBorderBottom((short)1);
styleRow1.setBorderTop((short)1);
styleRow1.setBorderLeft((short)1);
styleRow1.setBorderRight((short)1);
styleRow1.setWrapText(true);                                     //自動換行
Cell cell = row1.createCell(0);                                    //建立儲存格
cell.setCellStyle(styleRow1);                                       //套用格式
cell.setCellValue("demo");                                          //設定內容
sheet.setColumnWidth((short)column,(short)width);     //設定欄位寬度   
sheet.autoSizeColumn( (short)column );                       //自動調整欄位寬度
row.setHeightInPoints((short)height);                           //設定row高

int rowcount = sheet.getLastRowNum();                     //取得sheet的總行數(最後一行的數字)
row.getLastCellNum();                                               //取得目前所在row的最後一個cell編號

cell.setCellType(HSSFCell.CELL_TYPE_STRING); //設置單元格為STRING類型  
cell.getNumericCellValue();                                        //讀取為數值類型的單元格內容  

//設定欄位格式需要注意 Excel 需要值跟格式的型別一致才會正確設定
//意思是設定數值格式也需要值用Integer.parseInt(string)轉換成數字才會生效
//Excel裡的資料型別分成String Integer Decimal
//所以用Java 的 Double、Float 格式去轉換也會是常見作法

//數值的格式設定法
HSSFCellStyle cellStyle = workbook .createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00")); // 使用Excel內建格式 0.00
cellStyle.setDataFormat(numericFormat.getFormat("0.##"));                // 使用自訂格式

 // 合併儲存格
sheet.addMergedRegion(new CellRangeAddress(firstRow, endRow, firstColumn, endColumn));

 //凍結Excel顯示行,左上角為行參和列參的起始處，從0開始
sheet.createFreezePane(2, 1);


//寫入 excel 公式，不用加 [ = ]
//注意公式結果不會立刻被算出來，可以參考POI自訂方法那篇文章
row1.createCell(cellIndex).setCellFormula("SUM(A1:A12)");
http://poi.apache.org/spreadsheet/formula.html

//Using newlines in cells
http://poi.apache.org/spreadsheet/quick-guide.html

//調整sheet顯示比例
HSSFSheet sheet1 =  workbook .createSheet("new sheet");    
sheet1.setZoom(1,2);       // 50 percent magnification 


//加入圖片的方法
//先把讀進來的圖片放到一個ByteArrayOutputStream中，以便產生ByteArray     
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();      
BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));      
ImageIO.write(bufferImg,"jpg",byteArrayOut);      
         
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();      
HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);          
patriarch.createPicture(anchor , workbook.addPicture(byteArrayOut.toByteArray()
                                   , HSSFWorkbook.PICTURE_TYPE_JPEG));  

//儲存檔案
FileOutputStream fileOut = new FileOutputStream(tempFile);
workbook.write(fileOut);
fileOut.close();