//要到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);
bsp; 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; }
}
}
|