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();
}
}
}