HSSF对Excel的操作主要是通过下面几个对象实现:
HSSFWorkbook 工作簿对象对应于Excel文件
HSSFSheet Sheet对象对应于Excel中的Sheet
HSSFRow 行对象表示Sheet中的一行(这个对象在JXL中并没有提供)
HSSFCell 单元格对象
基本步骤:
通过Poi操作Excel十分方便,将一个Excel抽象为一个Workbook,一个表单页抽象为Sheet,表单中的一行抽象为Row,一行中的一个单元格可以抽象为Cell。HSSF对应的是97-03格式(.xls),XSSF对应的是07格式的(.xlsx)。
Workbook的获取有以下几种方式:
1.可以通过WorkbookFactory,工厂方法
Workbook wb=WorkbookFactory.create(new FileInputStream(file));//可以读取xls格式或xlsx格式。
2.直接通过HSSFWorkbook或XSSFWorkbook的构造方法
Workbook wb=new HSSFWorkbook();//生成一个空的Excel文件
Workbook wb=new HSSFWorkbook(new FileInputStream(file));//读取一个已经存在的Excel文件
Sheet可由Workbook创建和得到
Sheet s=wb.createSheet(sheet);//创建一个名为sheet的表单
HSSFSheet sheet = wb.getSheetAt(0);//得到第一个seet表单,也可以通过表单名字得到
Row由Sheet创建和得到
Row r=s.createRow(row);//新创建一行,行号为row+1
HSSFRow row = sheet.getRow(n);//得到一行,从0开始
Cell有Row创建和得到
Cell c=r.createCell(col);//创建一个单元格,列号为col+1
row.getCell(n)// 得到一个单元格,从0开始
最后就可以通过c.setCellValue(value)向单元格填充内容即可
private void excel2003(String fileName) throws IOException {
FunctionDao functionDao = new FunctionDao();
functionDao.deleteAll();
InputStream is = new FileInputStream("E:\\upload\\" + fileName);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
Function function = null;
//只执行sheet1
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
function = new Function();
// 循环列
HSSFCell divide = hssfRow.getCell(0);
if (divide == null) {
continue;
}
function.setDivide(divide.toString());
HSSFCell num = hssfRow.getCell(1);
if (num == null) {
continue;
}
function.setNum(num.toString());
HSSFCell name = hssfRow.getCell(2);
if (name == null) {
continue;
}
function.setName(name.toString());
HSSFCell leader = hssfRow.getCell(3);
if (leader == null) {
continue;
}
function.setLeader(leader.toString());
HSSFCell description = hssfRow.getCell(4);
if (description == null) {
continue;
}
function.setDescription(description.toString());
HSSFCell uri = hssfRow.getCell(5);
if (uri == null) {
continue;
}
function.setUri(uri.toString());
functionDao.addFunction(function);
}
}
private void excel2007(String fileName) throws InvalidFormatException,
IOException {
FunctionDao functionDao = new FunctionDao();
functionDao.deleteAll();
Function function=new Function();
InputStream is = new FileInputStream("E:\\upload\\" + fileName);
Workbook wb = WorkbookFactory.create(is);
Sheet sheet = wb.getSheetAt(0);
// 循环行Row
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
function = new Function();
// 循环列
Cell divide = row.getCell(0);
if (divide == null) {
continue;
}
function.setDivide(divide.toString());
Cell num = row.getCell(1);
if (num == null) {
continue;
}
function.setNum(num.toString());
Cell name = row.getCell(2);
if (name == null) {
continue;
}
function.setName(name.toString());
Cell leader = row.getCell(3);
if (leader == null) {
continue;
}
function.setLeader(leader.toString());
Cell description = row.getCell(4);
if (description == null) {
continue;
}
function.setDescription(description.toString());
Cell uri = row.getCell(5);
if (uri == null) {
continue;
}
function.setUri(uri.toString());
functionDao.addFunction(function);
}
}