java使用POI jar包读写xls文件

主要使用poi jar来操作excel文件。代码中用到的数据库表信息见ORACLE之表。使用public ArrayList<Person> getPersonAllRecords()获得所有的记录。

 

 1 public class PersonXLS {
 2 
 3     public static void main(String[] args) throws IOException {
 4         
 5         Workbook newExcel=new HSSFWorkbook();//创建一个excel 
 6         DBUtil dbUtil=new DBUtil();//数据库工具类
 7         Sheet newSheet0=newExcel.createSheet("Person Info");//创建一个sheet
 8         String[] title={"MAP_ID","PERSON_CODE","PERSON_NAME","SEX","INSERT_TIME","UPDATE_TIME","POSITION","SALARY","DEPT"};//表头
 9         SimpleDateFormat sdf;
10         Row newRow0=newSheet0.createRow(0);//创建表头
11           int i=0;
12           for(String s:title){//写入表头
13               Cell cell=newRow0.createCell(i++);
14               cell.setCellValue(s);
15           }
16           long sql_begin = System.currentTimeMillis();
17           dbUtil.getConn();//数据库连接
18           ArrayList<Person> persons=dbUtil.getPersonAllRecords();//获取记录
19           sdf=new SimpleDateFormat("MM/dd/yyyy");
20           for(i=0;i<persons.size();i++){//写入记录
21               Row newRow=newSheet0.createRow(i+1);
22               newRow.createCell(0).setCellValue(persons.get(i).getMap_id());
23               newRow.createCell(1).setCellValue(persons.get(i).getPerson_code());
24               newRow.createCell(2).setCellValue(persons.get(i).getPerson_name());
25               newRow.createCell(3).setCellValue(persons.get(i).getSex());
26               newRow.createCell(4).setCellValue(sdf.format(persons.get(i).getInsert_time()));
27               newRow.createCell(5).setCellValue(sdf.format(persons.get(i).getUpdate_time()));
28               newRow.createCell(6).setCellValue(persons.get(i).getPosition());
29               newRow.createCell(7).setCellValue(persons.get(i).getSalary());
30               newRow.createCell(8).setCellValue(persons.get(i).getDept());
31           }
32           sdf=new SimpleDateFormat("yyyyMMddHHmmss");
33           FileOutputStream fos=new FileOutputStream("PersonInfo"+sdf.format(new Date())+System.currentTimeMillis()+".xls");//创建
34           newExcel.write(fos);//向输入流写入数据
35           System.out.println("sql cost :"+(System.currentTimeMillis()-sql_begin)+"ms.");//数据操作花费时间
36     }
37 }

 

output:

 

posted @ 2016-03-29 12:13  权杖攻城狮  阅读(1682)  评论(0)  编辑  收藏  举报