最近项目用到了导入导出excel表,简单整理了一个,以后再遇到就可以参考了,呵呵~`
创建一个新项目:
1.需要的jar包
2.testExcel类:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class testExcel {
/**
* 1--- 导入excel2007 :xlsx格式-----------------
* @throws IOException
* @throws FileNotFoundException
*/
public static void main1(String[] arg) throws FileNotFoundException, IOException{
try {
String strP = "f://ex.xlsx";
//验证文件
boolean flag = validateExcel(strP);
if(flag){
//创建一个工作簿
Workbook wb=null;
try {
wb = new XSSFWorkbook(strP);
} catch (IOException e) {
e.printStackTrace();
}
//工作表的序号
Sheet sheet = wb.getSheetAt(0);
//System.out.println("有行数" + sheet.getLastRowNum());
//System.out.println(sheet.getPhysicalNumberOfRows());
//开始遍历整个工作表
for(int i =0;i<sheet.getLastRowNum()+1;i++){
Row row = sheet.getRow(i);
System.out.println("有行数" + row.getLastCellNum()+"----------------------");
for(int j=row.getFirstCellNum();j<row.getLastCellNum();j++){
//System.out.println("有列数" + row.getLastCellNum());
Cell cell = row.getCell(j);
System.out.println(cell);
}
}
}else{
System.out.println("没有此文件或此文件不是excel文件!");
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("文件导入失败!");
}
}//方法end;
/**
* 2--- 导入excel2003 :xls格式-----------------
* @throws IOException
* @throws FileNotFoundException
*/
public static void main2(String[] arg) throws FileNotFoundException, IOException{
try {
String strP = "f://123.xls"; //f://123.xls f://ex.xlsx
//验证文件
boolean flag = validateExcel(strP);
if(flag){
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(strP));
HSSFSheet sheet = wb.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
HSSFRow row = (HSSFRow) rows.next();
System.out.println("行:" + row.getRowNum() + "--------------------- ");
Iterator cells = row.cellIterator();
while (cells.hasNext()) {
HSSFCell cell = (HSSFCell) cells.next();
//System.out.println("列:" + cell.getCellNum());
System.out.println(cell);
}
}
}else{
System.out.println("没有此文件或此文件不是excel文件!");
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("文件导入失败!");
}
}//方法end;
/**
* @描述:验证excel文件(公共方法)
* @作者:wangjiaoe
* @时间:2012-9-20 上午
* @参数:@param fileName
* @参数:@return
* @返回值:boolean
*/
public static boolean validateExcel(String fileName){
//检查文件是否存在
File file = new File(fileName);
if(file==null || !file.exists()){//文件不存在
return false;
}else{//检查是否是excel文件
String fname = file.getName().substring(file.getName().lastIndexOf("."));
//System.out.println(fname+"=========文件后缀");
if(fname.equals(".xlsx")|| fname.equals(".xls")){
return true;
}else{
return false;
}
}
}
/**
* 3- 综合读取excel:导入xlsx(2007)、xls(2003)-------------------------------
*/
public static void main(String[] arg) throws FileNotFoundException, IOException{
try {
//创建一个工作簿
Workbook wb=null;
String strP = "f://123.xls"; //f://123.xls f://ex.xlsx
//验证文件
boolean flag = validateExcel(strP);
if(flag){
try {
wb = new XSSFWorkbook(strP);//为xlsx格式
} catch (Exception e) {
System.out.println("进入catch---------");
wb = new HSSFWorkbook(new FileInputStream(strP));//为xls格式
}
//工作表的序号
Sheet sheet = wb.getSheetAt(0);
//开始遍历整个工作表
for(int i =0;i<=sheet.getLastRowNum();i++){
Row row = sheet.getRow(i);
System.out.println(sheet.getLastRowNum()+"==============================");
if(row!=null){
for(int j=0;j<row.getLastCellNum();j++){
Cell cell = row.getCell(j);
System.out.println(cell);
}
}
}
}else{
System.out.println("没有此文件或此文件不是excel文件!");
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("文件导入失败!");
}
}//方法end;
}