Let's Go!

java poi 写入excel，一开始利用java分析数据并写入

上一篇 / 下一篇 2010-12-24 17:00:25 / 个人分类：JAVA学习&编程相关

#这个是最初参考的文件，很简练，利于学习：

package test.function.excel;

import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hwpf.extractor.WordExtractor;
import org.apache.poi.poifs.filesystem.DirectoryEntry;
import org.apache.poi.poifs.filesystem.DocumentEntry;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

// code run against the jakarta-poi-1.5.0-FINAL-20020506.jar.

public class MyExcel {

static public void main(String[] args) throws Exception {

//－－－－－－－－－－－－在xls中写入数据
FileOutputStream fos = new FileOutputStream("e:\\text.xls");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
wb.setSheetName(0, "first sheet");
HSSFRow row = s.createRow(0);
HSSFCell cell = row.createCell((int)0,0);
HSSFRichTextString hts=new HSSFRichTextString("nihao");
cell.setCellValue(hts);
wb.write(fos);
fos.flush();
fos.close();

//-------------------------
// System.out.println("1111");
// HSSFCell cell02 = row.createCell((int)1,0);
// cell02.setCellValue(22);    // 当整型写入
// System.out.println("222");
//-------------------------

//－－－－－－－－－－－－从xls读出数据
wb = new HSSFWorkbook(new FileInputStream("e:\\text.xls"));
s = wb.getSheetAt(0);
HSSFRow r = s.getRow(0);
cell=r.getCell((short)0);
if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
    System.out.println(cell.getRichStringCellValue());
}

//－－－－－－－－－－－－从doc读出数据
    FileInputStream in = new FileInputStream("e:\\text.doc");
    WordExtractor extractor = new WordExtractor(in);
    String text =extractor.getText();
       // 对DOC文件进行提取
    System.out.println(text);
    in.close();

//------------------在doc中写入

    byte[] a=new String("看见了！").getBytes();
    ByteArrayInputStream bs = new ByteArrayInputStream(a);
    POIFSFileSystem fs = new POIFSFileSystem();
    ///////////////////////////////////
    DirectoryEntry directory = fs.getRoot();
    DocumentEntry de = directory.createDocument("WordDocument", bs);
    //以上两句代码不能省略，否则输出的是乱码
    fos = new FileOutputStream("e:\\text.doc");
    fs.writeFilesystem(fos);
    bs.close();
    fos.flush();
    fos.close();


    //Java中关于空格的正则表达式
    String str2 = "GET             /index.html HTTP/1.1";//字符串s由“GET”、“/index.html”和“HTTP/1.1”组成，中间有一个或多个空格
    String tt[] = str2.split("\\s{1,}");//按照空格分割字符串，多个空格作为一个空格对字符串进行分割
    for(String str: tt)//增强的for循环
    System.out.println(str);//输出：GET
                                       // /index.html
                                      // HTTP/1.1
    String qq = str2.replaceAll(" {2,}", " ");//把字符串s中的多个空格替换为一个空格
    System.out.println(qq);//输出：GET /index.html HTTP/1.1
    System.out.println(str2);//输出：GET             /index.html HTTP/1.1

}
}

# 后来进行日志分析，然后获取数据，写入到excel中，代码如下：

package test.function.excel;

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class InformixAnalysisTwo {

static public void main(String[] args) throws Exception {

  InformixAnalysisTwo myPro = new InformixAnalysisTwo() ;
  ArrayList<ArrayList> content = myPro.readDataFile() ;
  myPro.writeExcelFile(content) ;


} // main

//读取监控数据：   // 可以改成传入参数，获取路径，循环路径下的文件
public ArrayList readDataFile() throws Exception{

  //读取监控数据：
  //BufferedReader in = new BufferedReader(new FileReader("E:\\on_20101217_p_1_5-p","UTF-8"));
  BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream("E:\\on_20101217_p_1_5-p"), "gbk"));

  String inStr = "" ;

  //String sheetName = in.readLine() ;

  //String[][] content = {} ;

  ArrayList<ArrayList> content = new ArrayList<ArrayList>() ;

//  String dataParaName =
//  "dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached"
//  +" isamtot open     start    read     write    rewrite delete   commit   rollbk"
//  +" gp_read gp_write gp_rewrt gp_del   gp_alloc gp_free gp_curs"
//  +" ovlock   ovuserthread ovbuff   usercpu syscpu   numckpts flushes"
//  +" bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans"
//  +" ixda-RA idx-RA   da-RA    RA-pgsused lchwaits" ;
//
//  String[] dataParaNameArray = dataParaName.split("\\s{1,}") ;
//  for(int paraNum=0 ; paraNum < dataParaNameArray.length;paraNum++){
//   String listName = dataParaNameArray[paraNum] ;
//   //ArrayList<String> listName = new ArrayList<String>() ;
//  }

  //Const String s = "" ;

  ArrayList<String> timeP = new ArrayList<String>() ;
  timeP.add("Time") ;

  ArrayList<String> dskreads = new ArrayList<String>() ;
  dskreads.add("dskreads") ;

  ArrayList<String> pagreads = new ArrayList<String>() ;
  pagreads.add("pagreads") ;

  content.add(timeP) ;
  content.add(dskreads) ;
  content.add(pagreads) ;

  while( (inStr=in.readLine()) != null   ){

//   Pattern pattern = Pattern.compile("^Fri*");
//   Matcher matcher = pattern.matcher(inStr);
//      boolean b= matcher.matches();
//      //当条件满足时，将返回true，否则返回false
//     System.out.println(b);

   //System.out.println("内容："+inStr);
   //Fri Dec 17 11:50:34 EAT 2010
//   if(inStr.matches("*\\d{2}:\\d{2}:\\d{2}*")){
//    System.out.println("eat");
//   }
   //System.out.println("行读取： "+inStr);

   if(inStr.endsWith("onstat -p")){
    inStr = in.readLine() ;
    String timeNow =inStr.split("\\s{1,}")[3] ; // 空格是固定的
    timeP.add(timeNow) ;
    //System.out.println ("读取 time "+timeNow) ;
    //timeP[num] = timeNow ;
    //num++ ;
   }
   if(inStr.startsWith("dskreads")){
    inStr = in.readLine() ;
    String[] paraValues = inStr.split("\\s{1,}") ; // 空格的数量是不固定的
    dskreads.add(paraValues[0]) ;
    pagreads.add(paraValues[1]) ;
   }

  }

  System.out.println(content);
  System.out.println(timeP);
  System.out.println(dskreads);
  System.out.println(pagreads);

  return content ;
}

// 在xls中写入数据
public void writeExcelFile(ArrayList<ArrayList> content) throws Exception {
  //－－－－－－－－－－－－在xls中写入数据

  FileOutputStream fos = new FileOutputStream("e:\\lianxi.xls");

  HSSFWorkbook wb = new HSSFWorkbook();

  HSSFSheet s = wb.createSheet();
  wb.setSheetName(0, "first sheet");   // 建立多个sheet，每个sheet 取不同的名字

  //for (int i=0 ;i<content.size() ;i++){ //行

  int m = 0 ;
   for(ArrayList paraValues : content) { //foreach语句 java SE5新增
    System.out.println("读取到的arrayList："+paraValues);
    HSSFRow hssfRowFinal = s.createRow(m) ;
    m++ ;
    int n = 1 ; // 0 参数名，已被写入。
       //for(String value : paraValues){
    Iterator myIterator = paraValues.iterator() ;

        HSSFCell hssfCellFirstFinalHead = hssfRowFinal.createCell((int)0, 1) ; //HSSFCell.CELL_TYPE_STRING= 1
          String prarameterName = myIterator.next().toString() ;
        hssfCellFirstFinalHead.setCellValue(prarameterName );

    while(myIterator.hasNext()){

     String value = myIterator.next().toString() ;
       //System.out.println(value);

       if(value.matches("\\d{2}:\\d{2}:\\d{2}")){
         System.out.println("值符合日期格式：time："+value);
         HSSFCell hssfCellFirstFinalTime = hssfRowFinal.createCell((int)n, 1) ;
         // int org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING = 1 [0x1]
         hssfCellFirstFinalTime.setCellValue(value) ;
       }else{
         System.out.println("参数值：value："+value);
         HSSFCell hssfCellFirstFinal = hssfRowFinal.createCell((int)n, 0) ;
         hssfCellFirstFinal.setCellValue(Double.parseDouble(value) );

       }

     n++;
    }

   }

   wb.write(fos);
   fos.flush();
   fos.close();

  System.out.print("over") ;

}