POI操作EXCEL应用实例代码

public class TestHSSF{
    Connection conn=null;
    Statement stmt = null;
    ResultSet rs = null;
    String sql="";   
    String[] headerNames={"编号","姓名","年龄","性别"};   //表头
    Vector values=new Vector();  //表格内容

    public TestHSSF(){
      checkXLS();
    }
    /**
     * 测试电子表格
     * 下载POI，解开JAR文件后将包拷贝到当前目录
     */
    public void checkXLS(){
      Vector vTmp=new Vector();
      vTmp.addElement(new Long(1));
      vTmp.addElement("张三");
      vTmp.addElement(new Integer(30));
      vTmp.addElement(new Boolean(true));
      values.addElement(vTmp);

      Vector vTmp1=new Vector();
      vTmp1.addElement(new Long(2));
      vTmp1.addElement("李四");
      vTmp1.addElement(new Integer(31));
      vTmp1.addElement(new Boolean(false));
      values.addElement(vTmp1);

      Vector vTmp2=new Vector();
      vTmp2.removeAllElements();
      vTmp2.addElement(new Long(3));
      vTmp2.addElement("王五");
      vTmp2.addElement(new Integer(5));
      vTmp2.addElement(new Boolean(true));
      values.addElement(vTmp2);

      try{
         //生成电子表格文件
         HSSFWorkbook wb = new HSSFWorkbook();
         //通过先建立表格，后命名可以定义使用的编码，从而保证汉字的正常显示
         HSSFSheet sheet1 = wb.createSheet();
         wb.setSheetName(0,"党组织情况统计表",HSSFWorkbook.ENCODING_UTF_16);
         HSSFSheet sheet2 = wb.createSheet();
         wb.setSheetName(1,"党员情况统计表",HSSFWorkbook.ENCODING_UTF_16);

         // 在表单中新建一个行，并建立4个列，分别写入一些数据
         // 需要注意的是：行的编号以0开始
         HSSFRow row0 = sheet1.createRow((short)0);
         HSSFRow row;

         //先定义一个字体对象
         HSSFFont f = wb.createFont();
         f.setFontName("黑体");
         f.setFontHeightInPoints((short) 10);  //字体大小
         f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  //加粗

         // 定义表头单元格格式
         HSSFCellStyle cellStyle = wb.createCellStyle();
         cellStyle.setFont(f);   //单元格字体
         cellStyle.setBorderBottom(cellStyle.BORDER_THIN);   //单元格边框
         cellStyle.setBorderTop(cellStyle.BORDER_THIN); 
         cellStyle.setBorderRight(cellStyle.BORDER_THIN);
         cellStyle.setBorderLeft(cellStyle.BORDER_THIN);
         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平对齐方式

         //定义新的调色板以便定义自己的颜色
         HSSFPalette palette = wb.getCustomPalette();
         //替换一个默认调色板中已经定义的颜色
         palette.setColorAtIndex(HSSFColor.BLUE_GREY.index, (byte) 230, (byte) 230, (byte) 255);
         //使用刚定义的颜色为填充色
         cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
         cellStyle.setFillPattern((short) HSSFCellStyle.SOLID_FOREGROUND);


         // 写入表头
         HSSFCell c;
         for(int i=0;i{
           c=row0.createCell((short)i);
           c.setCellStyle(cellStyle);
           c.setEncoding( HSSFCell.ENCODING_UTF_16 );
           c.setCellValue(headerNames[i]);
         }
         // 定义单元格格式
         HSSFCellStyle cellStyleContents = wb.createCellStyle();
         cellStyleContents.setBorderBottom(cellStyle.BORDER_THIN);   //表格细边框
         cellStyleContents.setBorderTop(cellStyle.BORDER_THIN); 
         cellStyleContents.setBorderRight(cellStyle.BORDER_THIN);
         cellStyleContents.setBorderLeft(cellStyle.BORDER_THIN);

         //定义数据格式
         HSSFDataFormat df = wb.createDataFormat();
         cellStyleContents.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
                          //setDataFormat(df.getFormat("#,##0.0"));
                          //setDataFormat(df.getFormat("0.0")
                          //


         // 写入单元格内容
         for(int i=0;i<values.size();i++){
           row=sheet1.createRow((short)i+1);        //建立新的行
           Vector vRecord=(Vector)values.elementAt(i);
           for(int j=0;j{
             c=row.createCell((short)j);            //建立新的列
             c.setCellStyle(cellStyleContents);
             c.setEncoding( HSSFCell.ENCODING_UTF_16 );
             c.setCellValue(vRecord.elementAt(j).toString());
           }
         }


         // 设置页眉
         HSSFHeader header = sheet1.getHeader();
         header.setCenter(toUnicode("党支部建设基本情况汇总"));
         header.setLeft(new String(">>>>>党委组织部".getBytes("GBK"),"8859_1"));
         header.setRight(HSSFHeader.date());
         // 设置页脚
         HSSFFooter footer = sheet1.getFooter();
         footer.setCenter( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );


         FileOutputStream fileOut = new FileOutputStream("d:/workbook.xls");
         wb.write(fileOut);
         fileOut.close();

      }catch(Exception ex){System.out.println(ex.toString());}

    }
    private String toUnicode(String s){
      try{
        //byte[] data=new byte[3+s.length()*2];
        //LittleEndian.putShort(data, 0, (short) s.length());

        String unicodeString = new String(s.getBytes("Unicode"),"Unicode");
        //StringUtil.putUncompressedUnicode(unicodeString, data,
        //                                           0x3 + 0);
        return unicodeString;
      }catch(Exception ex){
        System.out.println(ex.toString());
        return "aa";
      }
    }
    /**
     * 测试与MySQL数据库的连接及相关操作
     * 部署MySQL4.1.7,解开jar文件后将类目录拷贝到当前路径中
     */
    public void checkSQL(){
      MDBClass mdb=new MDBClass();
      mdb.connect();
      try {

        sql="drop table if exists test";
        int ret=mdb.getStatement().executeUpdate(sql);

        //建立表格
        sql="create table test("+
            "  ID MEDIUMINT,"+
            "  name varchar(20),"+
            "  sex  varchar(2),"+
            "  age int,"+
            "  telephone MEDIUMTEXT)";
        ret=mdb.executeUpdate(sql);

        // 删除一条记录
        sql="delete from test where ID=6";
        ret=mdb.executeUpdate(sql);

        // 插入一条记录
        sql="insert into test values(5,"张三","M",30,"13156中122699")";
        ret=mdb.executeUpdate(sql);

        // 显示数据库内容
        sql="SELECT * FROM test";
        rs = mdb.executeQuery(sql);
        while(rs.next()){
           long id=rs.getLong("ID");
           String name=rs.getString("name");
           String sex=rs.getString("sex");
           int age=rs.getInt("age");
           String telephone=rs.getString("telephone");       
           System.out.println(""+id+"  "+name+"   "+sex+"   "+
                 age+"    "+telephone);
        }
        rs.close();
        mdb.closeAll();
      } catch (SQLException ex) {
           System.out.println("SQLException: " + ex.getMessage());
           System.out.println("SQLState: " + ex.getSQLState());
           System.out.println("VendorError: " + ex.getErrorCode());
      } finally {
           if(mdb.getConnection()!=null){
             mdb.closeAll();
           }
           if (rs != null) {
             try {
                rs.close();
             } catch (SQLException sqlEx) {}

             rs = null;
           }

      }

    }
    public static void main(String[] args) {
        TestHSSF tms=new TestHSSF();
    }
}