poi操作Excel

package com.demo.poi;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
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 org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
*  poi读取Excel表数据
* @author
* 2008-11-11
*/
public class ExcelRead {

private HSSFWorkbook wb = null;

private HSSFSheet sheet = null;

private HSSFRow row = null;

private HSSFCell cell = null;

private FileInputStream fis = null;
   //属性
private int sheetNum = 0;

private int rowNum = 0;

private File file = null;


// 有参数的构造函数
public ExcelRead(File file) {
this.file = file;
}

public void setFile(File file) {
this.file = file;
}

public void setRowNum(int rowNum) {
this.rowNum = rowNum;
}

public void setSheetNum(int sheetNum) {
this.sheetNum = sheetNum;
}

/**
* 读取excel文件获得HSSFWorkbook对象
*/
public void open() {
try {
fis = new FileInputStream(file);
try {
wb = new HSSFWorkbook(new POIFSFileSystem(fis));
fis.close();
} catch (IOException e) {
// TODO Auto-generated catch block
System.out.println("IO异常！！");
e.printStackTrace();
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
System.out.println("没有找到相应的文件!!");
e.printStackTrace();
}
}

/**
* 返回sheet表个数
* @return
*/
public int getSheetCount() {
int sheetCount = -1;
sheetCount = wb.getNumberOfSheets();
System.out.println("sheet表的个数"+sheetCount);
return sheetCount;
}
/**
* 返回sheet表下的记录
*
* @return
*/
public int getRowNumber() {
int rowNum = -1;
if (wb == null)
System.out.println("WB为NULL");
HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
rowNum = sheet.getLastRowNum();
    return rowNum;
}
/**
* 返回指定sheet表的记录[重构]
*
* @param sheetNum
* @return
*/
public int getRowNumber(int sheetNum) {
HSSFSheet sheet = wb.getSheetAt(sheetNum);
  int rowCount = -1;
rowCount = sheet.getLastRowNum();
System.out.println(sheetNum+"表的行数为:"+rowCount);
return rowCount;
}
/**
* 得到指定行的所有的内容
* @param lineNum
* @return
*/
  public String[] readExcelLine(int lineNum) {
  return readExcelLine(this.sheetNum, lineNum);
}
/**
* 读取指定列的值[]
*
* @param cellNum
* @return
*/
  public String readStringExcelCell(int cellNum) {

return readStringExcel(this.rowNum, cellNum);
}
/**
* 读取指定行和列编号的内容
*
* @param rowNum
* @param cellNum
* @return
*/
public String readStringExcel(int rowNum, int cellNum) {

return readStringExcelCell(this.sheetNum, rowNum, cellNum);
}
/**
* 得到指定工作表和行的内容
*
* @param sheetNum
* @param lineLine
* @return
*/
public String[] readExcelLine(int sheetNum, int lineNum) {
if (sheetNum < 0 || lineNum < 0)
return null;
String[] strExcel = null;
sheet = wb.getSheetAt(sheetNum);
row = sheet.getRow(lineNum);
int cellCount = row.getLastCellNum();//获得列数
strExcel = new String[cellCount-1];
for (int i = 0; i < cellCount-1; i++) {
strExcel[i] = readStringExcel(lineNum,i);
}
return strExcel;
}

/**[重构]
* 得到指定工作表、行、列下的内容
*
* @param sheetNum
* @param rowNum
* @param cellNum
* @return
*/
public String readStringExcelCell(int sheetNum, int rowNum, int cellNum)
{
if (sheetNum < 0 || rowNum < 0) {
return "";
}
String strExcel = "";
try {
sheet = wb.getSheetAt(sheetNum);
row = sheet.getRow(rowNum);
if (row.getCell((short) cellNum) != null) {
switch (row.getCell((short) cellNum).getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
strExcel = "FORMULA";
break;
case HSSFCell.CELL_TYPE_NUMERIC:
strExcel = String.valueOf(row.getCell((short) cellNum)
.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
strExcel = row.getCell((short) cellNum).getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
strExcel = "";
break;
default:
strExcel = "";
break;
}
}

} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
return strExcel;
}
/**
* 测试
* @param args
*/
public static void main(String[] args) {

File file = new File("f://lpc1.xls");
ExcelRead read = new ExcelRead(file);
try {
read.open();
} catch (Exception e) {
e.printStackTrace();// TODO: handle exception
}
read.setSheetNum(0);
int count = read.getRowNumber();
System.out.println("行数"+count);
for(int i=0;i<count;i++)
{
String[] rows=read.readExcelLine(i);
for(int j=0;j<rows.length;j++)
{
System.out.print(rows[j]+"\t");
    }
             System.out.println();
   }
//System.out.println(read.readStringExcel(4, 2));
}
}

//*************************poi WriteExcel******************

package com.demo.poi;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
* 生成导出Excel文件对象
* @author
* 2008-11-10
*/
public class ExcelWriter {
  // 设置cell编码解决中文高位字节截断
private static short XLS_ENCODING = HSSFCell.ENCODING_UTF_16;
     //定制浮点数格式
private static String NUMBER_FORMAT = "#,##0.00";
// 定制日期格式
private static String DATE_FORMAT = "m/d/yy"; // "m/d/yy h:mm"  
    private OutputStream out=null;
    private HSSFWorkbook workbook=null;
    private HSSFSheet sheet=null;
    private HSSFRow row=null;
    //初始化ExcelWriter 
    public ExcelWriter(OutputStream out)
    {
    this.out=out;
    this.workbook=new HSSFWorkbook();
    this.sheet=workbook.createSheet(); 
    }
    /**
     * 导出Excel 文件
     */
     public void export()
     {
         try {
workbook.write(out);
out.flush();
out.close();
  } catch (IOException e) {
// TODO Auto-generated catch block
  System.out.println("写入Excel文件异常");
e.printStackTrace();
}  
     }
     /**
      * 增加一行
      * @param index行号
      */
    public void createRow(int index)
    {
    this.row=this.sheet.createRow(index);
   
    }
    /**
     * 获取单元格的值
     * @param index 列号
     * @return
     */
    public String getCell(int index)
    {
    HSSFCell cell=this.row.createCell((short)index);
    String strExcelCell = "";
    if(cell!=null)
    {
    switch(cell.getCellType())
    {
       case HSSFCell.CELL_TYPE_NUMERIC: strExcelCell=String.valueOf(cell.getNumericCellValue());
           break;
       case HSSFCell.CELL_TYPE_FORMULA : strExcelCell="FORMULA ";
           break;
       case HSSFCell.CELL_TYPE_STRING :strExcelCell=cell.getStringCellValue();
         break;
       case HSSFCell.CELL_TYPE_BOOLEAN : strExcelCell=String.valueOf(cell.getBooleanCellValue());
         break;
         default:strExcelCell="";
          break;
    }
    }
    return strExcelCell; 
    }
    /**
     * 设置单元格[int类型]
     * HSSFCell.CELL_TYPE_NUMERIC
     * @param index 行号
     * @param value 值
     */
    public void setCell(int index,int value)
    {
    HSSFCell cell=this.row.createCell((short)index);
         cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
         cell.setCellValue(value);
    }
    /**重构
     * 设置单元格
     * @param index
     * @param value
     */
    public void setCell(int index,double value)
    {
    HSSFCell cell=this.row.createCell((short)index);
    cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
    cell.setCellValue(value);
    HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
        HSSFDataFormat format=workbook.createDataFormat();
        cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));// 设置cell样式为定制的浮点数格式
        cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式        
    }
    /**
     *
     * 设置单元格[String类型]
     * @param index
     * @param value
     */
    public void setCell(int index,String value)
    {
    HSSFCell cell=this.row.createCell((short)index);
    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
    //cell.setEncoding(XLS_ENCODING);
    cell.setCellValue(value);
    }
   /**
    * 设置单元格[Calendar]
    * @param index
    * @param value
    */
      public void setCell(int index,Calendar  value)
      {
      HSSFCell cell=row.createCell((short)index);
      cell.setEncoding(XLS_ENCODING);
      cell.setCellValue(value.getTime());
      HSSFCellStyle cellStyle=workbook.createCellStyle();// 建立新的cell样式
          cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT));
      cell.setCellStyle(cellStyle);
      }  
      /**
       * 测试
       */
      public static void main(String[] args)
      {
      ExcelWriter e=null;
    System.out.println("开始导出Excel");
    File file=new File("f:\\qt.xls");   
    try {
      e=new ExcelWriter(new FileOutputStream(file));
      e.createRow(0);
      e.setCell(0, "试题编码 ");
      e.setCell(1, "题型");
      e.setCell(2, "分值");
      e.setCell(3, "难度");
      e.setCell(4, "级别");
      e.setCell(5, "知识点");
      e.createRow(1);
      e.setCell(0, "t1");
      e.setCell(1, 1);
      e.setCell(2, 3.0);
      e.setCell(3, 1);
      e.setCell(4, "重要");
      e.setCell(5, "专业"); 
} catch (Exception exs) {
// TODO: handle exception
exs.printStackTrace();
}
try{
  e.export();
System.out.println("导出成功");
    } catch (Exception ex) {
// TODO: handle exception
ex.printStackTrace();
}   
      }
}