java范例

          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 王波 閱讀(1194) 評論(0)  編輯  收藏


          只有注冊用戶登錄后才能發表評論。


          網站導航:
           
          主站蜘蛛池模板: 丹棱县| 中宁县| 建湖县| 喜德县| 天津市| 屏山县| 临湘市| 仪陇县| 肃宁县| 滨州市| 呈贡县| 尼勒克县| 太原市| 韶关市| 冀州市| 巴彦淖尔市| 钟祥市| 上犹县| 分宜县| 怀柔区| 博乐市| 波密县| 平远县| 庐江县| 南昌市| 蒲城县| 应城市| 阿勒泰市| 嘉峪关市| 东源县| 泊头市| 江西省| 镇巴县| 天镇县| 宁国市| 休宁县| 正安县| 中宁县| 玉田县| 连南| 旬阳县|