excel.java

通过java语音实现execl文件的存取、读写

                 irow ++;
                }




      }
      catch(Exception e)
      {
        System.out.println("--loginAction.checkUser系统错误!");
        //e.printStackTrace();
      }
      FileOutputStream fileOut = new FileOutputStream(excelFileName);

      wb.write(fileOut);

      fileOut.close();


}








    /**
   *
   * 读EXCEL文件
   * @param fileToBeRead String
   */
  /** Excel文件的存放位置。注意是正斜线*/
  public static void excelRead(String fileToBeRead){
      try
      {
      // 创建对Excel工作簿文件的引用
      HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
      // 创建对工作表的引用。
      // 本例是按名引用（让我们假定那张表有着缺省名"Sheet1"）
      HSSFSheet sheet = workbook.getSheet("new sheet");
      // HSSFSheet sheet = workbook.getSheetAt(0);
      // 也可用getSheetAt(int index)按索引引用，
      // 在Excel文档中，第一张工作表的缺省索引是0，
      // 其语句为：HSSFSheet sheet = workbook.getSheetAt(0);

      // 读取左上端单元
      HSSFRow row = sheet.getRow(0);
      HSSFCell cell = row.getCell((short)0);

      // 输出单元内容，cell.getStringCellValue()就是取所在单元的值
      System.out.println("左上端单元是： " + cell.getNumericCellValue());
    }catch(Exception e)
    {
        System.out.println("已运行xlRead() : " + e );
    }
  }

  /**
    *
    * 通过查询数据库，把查询到的值保存到EXCEL表中
    * @param excelFileName String  EXCEL文件名
    * @param sql String   执行的SQL语句
    * @throws IOException
    */
   static public void excelWriteHdCompare(String excelFileName, String StrQ,String code, String tmppath) throws IOException
     {
       int count=0;
       dbConnect dbconn = new dbConnect(code);
       DBResultset rs = null;
       DBResultset rs1 = null;
       String sql="";
       String strSql0="";
       String strSql1="";

       int charge=0;
       float fei=0;

     //HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook对象
     //HSSFSheet sheet = wb.createSheet("sheet0");//建立新的sheet对象
     String readfile = tmppath+"\\zwCompare_tmp.xls";
     POIFSFileSystem fs      =
                 new POIFSFileSystem(new FileInputStream(readfile));
     HSSFWorkbook wb = new HSSFWorkbook(fs);
     HSSFSheet sheet = wb.getSheetAt(1);


       try{
            rs = dbconn.runQuery(StrQ,1000);
            count = rs.getRowSize();
            System.out.println("COUNT IS: "+count);

            short irow=0;
             String str="";

             HSSFRow row0 = sheet.createRow(irow);

             str = "帐期标识";
             HSSFCell csCell00 =row0.createCell((short)0);
             csCell00.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell00.setCellValue(str);

             str = "营业区标识";
             HSSFCell acsCell10 =row0.createCell((short)1);
             acsCell10.setEncoding(HSSFCell.ENCODING_UTF_16);
             acsCell10.setCellValue(str);

             str = "账目类型标识";
             HSSFCell csCell20 =row0.createCell((short)2);
             csCell20.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell20.setCellValue(str);

            str = "计费模块标识";
             HSSFCell csCell30 =row0.createCell((short)3);
             csCell30.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell30.setCellValue(str);

             str = "本帐期费用";
             HSSFCell csCell40 =row0.createCell((short)4);
             csCell40.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell40.setCellValue(str);

             str = "上个帐期费用";
             HSSFCell csCell50 =row0.createCell((short)5);
             csCell50.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell50.setCellValue(str);

             str = "上上个帐期费用";
             HSSFCell csCell60 =row0.createCell((short)6);
             csCell60.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell60.setCellValue(str);

             str = "上上上个帐期费用";
             HSSFCell csCell70 =row0.createCell((short)7);
             csCell70.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell70.setCellValue(str);

             str = "同比的费用";
             HSSFCell csCell80 =row0.createCell((short)8);
             csCell80.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell80.setCellValue(str);

             str = "本帐期与上个帐期的差额";
             HSSFCell csCell90 =row0.createCell((short)9);
             csCell90.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell90.setCellValue(str);

             str = "本帐期与上上个帐期的差额";
             HSSFCell csCell100 =row0.createCell((short)10);
             csCell100.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell100.setCellValue(str);

             str = "本帐期与上上上个帐期的差额";
             HSSFCell csCell110 =row0.createCell((short)11);
             csCell110.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell110.setCellValue(str);

             str = "同比的费用差额";
             HSSFCell csCell120 =row0.createCell((short)12);
             csCell120.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell120.setCellValue(str);

             str = "上帐期百分比";
             HSSFCell csCell130 =row0.createCell((short)13);
             csCell130.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell130.setCellValue(str);

             str = "上上帐期百分比";
             HSSFCell csCell140 =row0.createCell((short)14);
             csCell140.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell140.setCellValue(str);

             str = "上上上帐期百分比";
             HSSFCell csCell150 =row0.createCell((short)15);
             csCell150.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell150.setCellValue(str);

             str = "同比的百分比";
             HSSFCell csCell160 =row0.createCell((short)16);
             csCell160.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell160.setCellValue(str);

             str = "是否确认";
             HSSFCell csCell170 =row0.createCell((short)17);
             csCell170.setEncoding(HSSFCell.ENCODING_UTF_16);
             csCell170.setCellValue(str);

             irow ++;


                 while (rs.moveNext()) {
                  HSSFRow row = sheet.createRow(irow);

                  str = rs.fieldByIndex(0);
                  HSSFCell csCell0 =row.createCell((short)0);
                  csCell0.setEncoding(HSSFCell.ENCODING_UTF_16);
                  csCell0.setCellValue(str);

                 // str = rs.fieldByIndex(1);
                  strSql0="select * from AREA where AREA_ID_CODE ='"+rs.fieldByName("AREA_ID")+"'";
                  rs1=dbconn.runQuery(strSql0,1000);
//                  rs1.moveFirst();
                  HSSFCell csCell1 =row.createCell((short)1);
                  csCell1.setEncoding(HSSFCell.ENCODING_UTF_16);
                  while(rs1.moveNext())
                    csCell1.setCellValue(rs1.fieldByName("NAME"));

                  //str = rs.fieldByIndex(2);
                  strSql0="select * from ACCT_ITEM_TYPE WHERE ACCT_ITEM_TYPE_ID="+rs.fieldByName("ACCT_ITEM_TYPE_ID");
                  rs1=dbconn.runQuery(strSql0,1000);
                  rs1.moveFirst();


                  HSSFCell csCell2 =row.createCell((short)2);
                  csCell2.setEncoding(HSSFCell.ENCODING_UTF_16);
                  csCell2.setCellValue(rs1.fieldByName("NAME"));

                 // str = rs.fieldByIndex(3);
                  strSql0="select BELONG_NAME from PROCID_BELONG_RELATION where BELONG ="+rs.fieldByName("BELONG");
                  rs1=dbconn.runQuery(strSql0,1000);
 //                 rs1.moveFirst();

                  HSSFCell csCell3 =row.createCell((short)3);
                  csCell3.setEncoding(HSSFCell.ENCODING_UTF_16);
                  while(rs1.moveNext())
                     csCell3.setCellValue(rs1.fieldByName("BELONG_NAME"));

                  str = rs.fieldByIndex(4).trim();
                  HSSFCell csCell4 =row.createCell((short)4);
                  if(str.equalsIgnoreCase(""))
                  {
                     charge=0;
                  }
                  else
                     charge=Integer.parseInt(str);
                  csCell4.setCellValue(charge);

                  str = rs.fieldByIndex(5).trim();
                  HSSFCell csCell5 =row.createCell((short)5);
                  if(str.equalsIgnoreCase(""))
                  {
                     charge=0;
                  }
                  else
                     charge=Integer.parseInt(str);
                  csCell5.setCellValue(charge);

                  str = rs.fieldByIndex(6).trim();
                  HSSFCell csCell6 =row.createCell((short)6);
                  if(str.equalsIgnoreCase(""))
                  {
                     charge=0;
                  }
                  else
                     charge=Integer.parseInt(str);
                  csCell6.setCellValue(charge);

                  str = rs.fieldByIndex(7).trim();
                  HSSFCell csCell7 =row.createCell((short)7);
                  if(str.equalsIgnoreCase(""))
                  {
                     charge=0;
                  }
                  else
                     charge=Integer.parseInt(str);
                  csCell7.setCellValue(charge);

                  str = rs.fieldByIndex(8).trim();
                  HSSFCell csCell8 =row.createCell((short)8);
                  if(str.equalsIgnoreCase(""))
                  {
                     charge=0;
                  }
                  else
                     charge=Integer.parseInt(str);
                  csCell8.setCellValue(charge);

                  str = rs.fieldByIndex(9).trim();
                  HSSFCell csCell9 =row.createCell((short)9);
                  if(str.equalsIgnoreCase(""))
                  {
                     charge=0;
                  }
                  else
                     charge=Integer.parseInt(str);
                  csCell9.setCellValue(charge);

                  str = rs.fieldByIndex(10).trim();
                  HSSFCell csCell10 =row.createCell((short)10);
                  if(str.equalsIgnoreCase(""))
                  {
                     charge=0;
                  }
                  else
                     charge=Integer.parseInt(str);
                  csCell10.setCellValue(charge);

                  str = rs.fieldByIndex(11);
                  HSSFCell csCell11 =row.createCell((short)11);
                  if(str.equalsIgnoreCase(""))
                  {
                     charge=0;
                  }
                  else
                     charge=Integer.parseInt(str);
                  csCell11.setCellValue(charge);

                  str = rs.fieldByIndex(12).trim();
                  HSSFCell csCell12 =row.createCell((short)12);
                  if(str.equalsIgnoreCase(""))
                  {
                     charge=0;
                  }
                  else
                     charge=Integer.parseInt(str);
                  csCell12.setCellValue(charge);

                  str = rs.fieldByIndex(13).trim();
                  HSSFCell csCell13 =row.createCell((short)13);
                  if(str.equalsIgnoreCase(""))
                  {
                     fei=0;
                  }
                  else
                     fei=Float.parseFloat(str);
                  csCell13.setCellValue(fei);
                  str = rs.fieldByIndex(14).trim();
                  HSSFCell csCell14 =row.createCell((short)14);
                  if(str.equalsIgnoreCase(""))
                  {
                     fei=0;
                  }
                  else
                     fei=Float.parseFloat(str);
                  csCell14.setCellValue(fei);
                  str = rs.fieldByIndex(15).trim();
                  HSSFCell csCell15 =row.createCell((short)15);
                  if(str.equalsIgnoreCase(""))
                  {
                     fei=0;
                  }
                  else
                     fei=Float.parseFloat(str);
                  csCell15.setCellValue(fei);
                  str = rs.fieldByIndex(16).trim();
                  HSSFCell csCell16 =row.createCell((short)16);
                  if(str.equalsIgnoreCase(""))
                  {
                     fei=0;
                  }
                  else
                     fei=Float.parseFloat(str);
                  csCell16.setCellValue(fei);

                  str = rs.fieldByIndex(17);
                  if(str.equalsIgnoreCase("0"))
                    str="未确认";
                   else
                     str="确认";
                  HSSFCell csCell17 =row.createCell((short)17);
                  csCell17.setEncoding(HSSFCell.ENCODING_UTF_16);
                  csCell17.setCellValue(str);

                  irow ++;
                 }

                FileOutputStream fileOut = new FileOutputStream(excelFileName);
                wb.write(fileOut);
                fileOut.close();


       }
       catch(Exception e)
       {
         System.out.println("--loginAction.checkUser系统错误!");
         //e.printStackTrace();
       }


 }


public static void main(String[] args)
{
  String filename="e:\\Mydomain\\05311\\test2.xls";
  try {
    excelWrite(filename);
  }
  catch (IOException ex) {
  }

}

    }