java范例

apache poi 寫入 excle

package com.together.printbean;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.poifs.filesystem.POIFSFileSystem;

import com.together.dbbean.DBOptBean;

public class PrintBean {
 public void printInfo(String str1, String str2, String tbl,boolean bol1,boolean bol2)
   throws Exception {
  // 分割工作表
  // HSSFSheet.createSplitPane(int colSplit, int rowSplit, int
  // leftmostColumn, int topRow);
  // 固定某行不滾動
  // HSSFSheet.createFreezePane(int colSplit, int rowSplit, int
  // leftmostColumn, int topRow);
  FileInputStream is = new FileInputStream("F://sss.xls");
  //  POIFSFileSystem fs = new POIFSFileSystem(is);
  //  
  //  HSSFWorkbook wb = new HSSFWorkbook(fs);
  //  
  // // wb.cloneSheet(0); //復制工作表
  //  is.close();
  //  HSSFSheet sheet = wb.getSheetAt(0);
  //  int len = sheet.getPhysicalNumberOfRows();
  //  HSSFRow row = null;
  //  HSSFCell cell = null;
  //  int rlen = 0;
  //  for(int i=0;i<len;i++){
  //   row = sheet.getRow((short)i);
  //   rlen = row.getLastCellNum();
  //   for(int j=0;j<rlen;j++){
  //    cell = row.createCell((short)j);
  //    cell.setCellValue("");
  //   }
  //   
  //  }
  String str = str1;
  FileOutputStream fileout = new FileOutputStream("F://test.xls");

  // 創建xls對象
  HSSFWorkbook wb = new HSSFWorkbook();
  // wb.setSheetName(0,"",wb.ENCODING_UTF_16);  
  // 創建工作表
  HSSFSheet sheet = wb.createSheet();
  // 設置工作表為只讀不可修改
  sheet.setProtect(true);
  sheet.setPrintGridlines(true);
  String strv = str2;
  
  // 中文轉碼
  strv = new String(strv.getBytes("ISO-8859-1"), "UTF-8");
  String ssv = strv.substring(0, strv.length() - 1);
  String[] skv = ssv.split(",");
  int lenv = skv.length;
  int iv = 0;

  // 虛線HSSFCellStyle.BORDER_DOTTED
  // 實線HSSFCellStyle.BORDER_THIN
  HSSFCellStyle style = wb.createCellStyle();
  style.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下邊框
  style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左邊框
  style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右邊框
  style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上邊框
  style.setLocked(true);
  
  // 設置自動換行
  //style.setWrapText(true);
  // 創建對象
  HSSFRow rowv = sheet.createRow(0);
  // 樣式對象
  HSSFCellStyle cs = wb.createCellStyle();
  cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);//
  cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);//
  cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 
  cs.setBorderRight(HSSFCellStyle.BORDER_THIN);//
  cs.setBorderTop(HSSFCellStyle.BORDER_THIN);//
  // 創建字體
  HSSFFont ff = wb.createFont();
  // ff.setColor(HSSFFont.COLOR_RED);//  字體顏色
  ff.setFontHeightInPoints((short) 11);// 字體大小
  // ff.setFontName("");// 設置字體，沒用
  ff.setBoldweight((short) 700);
  ff.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
  cs.setFont(ff);//放入樣式中
  
  //循環標題
  for (int k = 0; k < lenv; k++) {
   HSSFCell cell0 = rowv.createCell((short) k);
   // cell0.setCellStyle(style);
   cell0.setCellStyle(cs);
   cell0.setEncoding(cell0.ENCODING_UTF_16);//設置編碼格式
   cell0.setCellValue(skv[k]);
  }
  String ss = str.substring(0, str.length() - 1);
  String tb = tbl;
  String[] sk = ss.split(",");
  int len = sk.length;
  int i = 1;
  // sql 語句
  String sql = "select " + ss.toUpperCase() + " from " + tb;
  // 數據庫對象
  DBOptBean dbb = new DBOptBean();
  Connection conn = dbb.GetConn();
  // 設置游標可恢復
  Statement stmt = conn.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
  ResultSet rs = stmt.executeQuery(sql);
  
  int o = 0;
  int k = 0;
  int[] cc = { 0, 0 };
  int[] dd = { 0, 0 };
  int xx = 0;
  int length = 1;
  // 得到列最長長度
  while (rs.next()) {

   for (int er = 1; er <= len; er++) {
    xx = er - 1;
    dd[xx] = rs.getObject(er).toString().getBytes().length;
    if (cc[xx] < dd[xx]) {
     cc[xx] = dd[xx];
    }
   }
  }
  rs.beforeFirst();//游標恢復
  while (rs.next()) {
   length++;
   HSSFRow row = sheet.createRow(i);
   row.setHeight((short) 250);

   for (int j = 1; j <= len; j++) {
    o = j - 1;
    sheet.setColumnWidth((short) o, (short) (280 * cc[o]));
    HSSFCell cell = row.createCell((short) o);
    //  加入數據
    cell.setCellStyle(style);
    cell.setEncoding(cell.ENCODING_UTF_16);
    cell.setCellValue(rs.getObject(j).toString());
    o++;
   }
   i++;
   k++;
  }
  if(bol1){
   ViewBean vb = new ViewBean();
   vb.viewInfo();
   if(bol2){
    BufferedImage bufferImg = null;
    try {
     ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
     bufferImg = ImageIO.read(new File("F:/hoho.png"));
     ImageIO.write(bufferImg, "png", byteArrayOut);
     HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
     //HSSFClientAnchor 控制圖片位置 參數詳解
     //(short)no.1 參數1為控制圖片離左墻的距離
     //(short)no.2 參數2為控制圖片離房頂的距離
     //(short)no.3 參數1為控制圖片的寬度width
     //(short)no.4 參數2為控制圖片的高度height
     HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,
       (short) 0, length, (short) 5, 30);
     patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
       .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

    } catch (IOException io) {
     io.printStackTrace();
     System.out.println("io erorr :  " + io.getMessage());
    }
   }
  }
  wb.write(fileout);
  // 關閉資源
  fileout.flush();
  fileout.close();
  stmt.close();
  rs.close();
 }
}

posted on 2007-10-23 16:46 王波 閱讀(1192) 評論(0)  編輯  收藏