Excel文件的导入与导出

       最近一个项目中用到了Excel文件的导出，经过一段时间的学习，了解到Excel文件的导入与导出大致有两种方式:poi与jxl。自己结合网上的材料略微整理了一下。下面例子中所用到的jar包

       poi   poi-3.0.1.jar

       jxl    jxl.jar

 

 

package com.lhy.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelInAndOut {
 
 public static void main(String[] args) {
  //jxlout();
     // jxlin();
  //poiin();
  poiout();
 }
   
  //Excel数据以poi方式导入
 public static void poiin(){
  
         String filePath="D://lhy.xls";
     
      try {
             // 创建对Excel工作簿文件的引用
             HSSFWorkbook wookbook = new HSSFWorkbook(new FileInputStream(filePath));
             // 在Excel文档中，第一张工作表的缺省索引是0,其语句为
            
             //  HSSFSheet sheet = wookbook.getSheetAt(0);
             String name =wookbook.getSheetName(0);     //得到第一个工作表的名字
             HSSFSheet sheet = wookbook.getSheet(name);   //得到第一个工作表
          
            //  int t= wookbook.getSheetIndex("sheet1");
             System.out.println(name);
             //获取到Excel文件中的所有行数
             int rows = sheet.getPhysicalNumberOfRows();
             //遍历行
                 for (int i = 0; i < rows; i++) {
                   // 读取左上端单元格
                   HSSFRow row = sheet.getRow(i);
                   // 行不为空
                    if (row != null) {
                         //获取到Excel文件中的所有的列
                         int cells = row.getPhysicalNumberOfCells();
                         String value = "";    
                         //遍历列
                           for (int j = 0; j < cells; j++) {
                               //获取到列的值
                               HSSFCell cell = row.getCell((short) j);
                                  if (cell != null) {
                                         switch (cell.getCellType()) {
                                           case HSSFCell.CELL_TYPE_FORMULA:
                                           break;
                                           case HSSFCell.CELL_TYPE_NUMERIC:
                                               //  value += cell.getNumericCellValue() + ",";  
                                            System.out.println(cell.getNumericCellValue());
                                           break;  
                                           case HSSFCell.CELL_TYPE_STRING:
                                               //  value += cell.getStringCellValue() + ",";
                                            System.out.println(cell.getStringCellValue());
                                           break;
                                           default:
                                               //  value += "0";
                                           break;
                                         }
                                  } 
                          }
                    }
               }
         }catch(Exception e){
          e.printStackTrace();
        }
 }
 
 //poi方式导出到外部Excel文件
    public static void poiout(){
     
     //生成的Excel文件路径
     String filePath="D:/lhy.xls";
     try{
          //准备输出工作流文件
      FileOutputStream fout = new FileOutputStream(new File(filePath)); 
                  
      //创建excel文件
      HSSFWorkbook wb = new HSSFWorkbook();
   
         //创建第一个工作表first
      HSSFSheet sheet=wb.createSheet("first");
      //创建第一行
         HSSFRow row=sheet.createRow(0);
        
      //创建两个单元格
      HSSFCell cell[]=new HSSFCell[2];
      
      cell[0] = row.createCell((short) 0);
      cell[0].setCellValue("lhy");
      
      
      cell[1] = row.createCell((short) 1);
      cell[1].setCellValue("123.456");
        
       //将工作流数据写入到工作表中
       wb.write(fout);  
          fout.flush();  
             fout.close();
      }
     catch(Exception e){
      e.printStackTrace();
     }
  
 }
   
    //Excel数据以jxl方式导入
    public static void jxlin(){
     
     try {  
    //导入的Excel名字和路径
        Workbook book = Workbook.getWorkbook(new File("d:/lhy.xls"));  
        //得到第一个工作薄 
        Sheet sheet = book.getSheet(0);  
        //得到工作薄的行数
        int rows=sheet.getRows();
        //得到工作薄的列数
        int cols=sheet.getColumns();
       
        for(int r=0;r<rows;r++){
         for(int c=0;c<cols;c++){
          //  System.out.println("r="+r+"  c="+c);
          //得到单元格数据
          Cell cell=sheet.getCell(c, r);
          String result=cell.getContents();
          System.out.println(result);
         }
        }
       
         book.close();  
       } catch (Exception e) {  
        e.printStackTrace();  
      }
      
 }
   
    //jxl方式导出到外部Excel文件
    public static void jxlout(){
  
       try {  
       //新建工作表路径和Excel表名字
       WritableWorkbook book = Workbook.createWorkbook(new File("d:/lhy.xls"));  
             //创建第一个工作薄,以first命名
       WritableSheet sheet = book.createSheet("first", 0);  
          //在第1行第1列，创建标签写入字符串数据  
          Label label = new Label(0, 0, "lhy");  
          //加入到工作薄的相应单元格中
             sheet.addCell(label);  
             //在第1行第2列写入数字
             jxl.write.Number number = new jxl.write.Number(1, 0, 123.456);  
      
       sheet.addCell(number);  
         
       book.write();  
       //关闭工作表      
       book.close();  
     } catch (Exception e) {  
         e.printStackTrace();  
   }   
 }
}