Java讀取資料庫寫excel文檔

//要到apache公司下載poi3.1jar包

package excel;
import org.apache.poi.hssf.util.*;
import java.sql.*;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import org.apache.poi.hssf.usermodel.*;
//插入圖片需要匯入的jar包
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.net.URL;
import javax.imageio.ImageIO;

public class WriteExcel {
 private Connection con=null;

 public Connection getConnection(){
  String ClssForName="com.microsoft.jdbc.sqlserver.SQLServerDriver";
  String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=pubs";
  if(con==null){
   
   try{
    Class.forName(ClssForName);
    con=DriverManager.getConnection(url,"sa","");
    
   } catch (ClassNotFoundException e) { 
    e.printStackTrace();
   }catch(SQLException cone){
    System.out.println(cone);
   }
  }
  
  return con;
 }
 
 public static void main(String[] args)throws Exception {
  Connection conn=null;
  Statement st=null;
  ResultSet rs=null;
  int i=0;
  String sql="select *from authors";
  WriteExcel wr=new WriteExcel();
  
  conn=wr.getConnection();
  st=conn.createStatement();
  rs=st.executeQuery(sql);
  
        //聲明一個工作薄
        HSSFWorkbook wb=new HSSFWorkbook();
        //產生一個表格
        HSSFSheet sheet=wb.createSheet("表格1");
        //產生一個列
      //  HSSFRow row=sheet.createRow(0);
        //產生一個樣式
        HSSFCellStyle style=wb.createCellStyle();
        //設定這些樣式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //產生一個字型
        HSSFFont font=wb.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short)10);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //把字型應用到當前的樣式
        style.setFont(font);
        //填充儲存格
        while(rs.next()){
         HSSFRow row=sheet.createRow(i);
            //聲明一個儲存格
            HSSFCell cell=row.createCell((short)0);
            //設定儲存格的字元值
            cell.setCellValue(new HSSFRichTextString(rs.getString("au_id").toString()));
            //設定儲存格的樣式
            cell.setCellStyle(style);
            //設定第二列數值
            HSSFCell au_lname=row.createCell((short)1);
            au_lname.setCellValue(new HSSFRichTextString(rs.getString("au_lname")));
            //設定第三列數值
            HSSFCell au_fname=row.createCell((short)2);
            au_fname.setCellValue(new HSSFRichTextString(rs.getString("au_fname")));
            HSSFCell phone=row.createCell((short)3);
            phone.setCellValue(new HSSFRichTextString(rs.getString("phone")));
            HSSFCell address=row.createCell((short)4);
            address.setCellValue(new HSSFRichTextString(rs.getString("address")));
            i++;
        }
        FileOutputStream fout=new FileOutputStream("d://我的第一個EXCEL.xls");
        //輸出到檔案
        wb.write(fout);
        fout.close();
    }
 /**
  * 插入圖片
  * @throws Exception
  *
  */
 public void insertIMG() throws Exception{
  
    //聲明一個工作薄
        HSSFWorkbook wb=new HSSFWorkbook();
        //產生一個表格
        HSSFSheet sheet=wb.createSheet("表格1");
        //產生一個列
        HSSFRow row=sheet.createRow(0);
        //產生一個樣式
        HSSFCellStyle style=wb.createCellStyle();
        //設定這些樣式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //產生一個字型
        HSSFFont font=wb.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short)16);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //把字型應用到當前的樣式
        style.setFont(font);
        //聲明一個畫圖的頂級管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        //填充儲存格
        for(short i=0;i<5;i++){
            //聲明一個儲存格
            HSSFCell cell=row.createCell(i);
            switch(i){
                case 0:
                    //設定普通文本
                    cell.setCellValue(new HSSFRichTextString("普通文本"));
                    break;
                case 1:
                    //設定為形狀
                    HSSFClientAnchor a1 = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 1, 0, (short) 1, 0 );
                    HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
                    //這裡可以設定形狀的樣式
                    shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);
                   
                    break;
                case 2:
                    //設定為布爾量
                    cell.setCellValue(true);
                    break;
                case 3:
                    //設定為double值
                    cell.setCellValue(12.5);
                    break;
                case 4:
                    //設定為圖片]
                  //  URL url=Test3.class.getResource("hello.jpg");
                   // insertImage(wb,patriarch,getImageData(ImageIO.read(url)),0,4,1);
                    break;
                   
            }
           
            //設定儲存格的樣式
            cell.setCellStyle(style);
        }
        FileOutputStream fout=new FileOutputStream("我的第一個EXCEL.xls");
        //輸出到檔案
        wb.write(fout);
        fout.close();
    }
    //自訂的方法,插入某個圖片到指定索引的位置
    private static void insertImage(HSSFWorkbook wb,HSSFPatriarch pa,byte[] data,int row,int column,int index){
        int x1=index*250;
        int y1=0;
        int x2=x1+255;
        int y2=255;
        HSSFClientAnchor anchor = new HSSFClientAnchor(x1,y1,x2,y2,(short)column,row,(short)column,row);
        anchor.setAnchorType(2);
        pa.createPicture(anchor , wb.addPicture(data,HSSFWorkbook.PICTURE_TYPE_JPEG));
    }
    //從圖片裡面得到位元組數組
    private static  byte[] getImageData(BufferedImage bi){
        try{
            ByteArrayOutputStream bout=new ByteArrayOutputStream();
            ImageIO.write(bi,"PNG",bout);
            return bout.toByteArray();
        }catch(Exception exe){
            exe.printStackTrace();
            return null;
        }

 }

}