java poi读写excel表格

Poi读取excel文件
package com.tp.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.LinkedList;
import java.util.List;

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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TestRdExcel {

/**
* 读取 office 2003 excel
*
* @throws IOException
* @throws FileNotFoundException
*/
private static List<List<Object>> read2003Excel(File file)
throws IOException {
List<List<Object>> list = new LinkedList<List<Object>>();
HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
HSSFSheet sheet = hwb.getSheetAt(0);//只是读取第一页
Object value = null;
HSSFRow row = null;
HSSFCell cell = null;

//循环行
for (int i = sheet.getFirstRowNum(); i <= sheet
.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null) {//判断是否为空
continue;
}
List<Object> linked = new LinkedList<Object>();
//循环列
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
cell = row.getCell(j);//获得单元格
if (cell == null) {
continue;
}
value=cell.toString();//单元格内容  由于是模板所以这里不用考虑类型
if (value == null || "".equals(value)) {
continue;
}
linked.add(value);

}
list.add(linked);
}

return list;
}

/**
* 读取Office 2007 excel
*/

private static List<List<Object>> read2007Excel(File file)
throws IOException {

List<List<Object>> list = new LinkedList<List<Object>>();
// 构造 XSSFWorkbook 对象，strPath 传入文件路径
XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
// 读取第一章表格内容
XSSFSheet sheet = xwb.getSheetAt(0);
Object value = null;
XSSFRow row = null;
XSSFCell cell = null;
for (int i = sheet.getFirstRowNum(); i <= sheet
.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null) {
continue;
}
List<Object> linked = new LinkedList<Object>();
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
cell = row.getCell(j);
if (cell == null) {
continue;
}
value = cell.toString();
if (value == null || "".equals(value)) {
continue;
}
linked.add(value);
}
list.add(linked);
}
return list;
}

/**
* 对外提供读取excel 的方法
*/
public static List<List<Object>> readExcel(File file) throws IOException {
String fileName = file.getName();
String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
.substring(fileName.lastIndexOf(".") + 1);

//判断文件类型
if ("xls".equals(extension)) {
return read2003Excel(file);
} else if ("xlsx".equals(extension)) {
return read2007Excel(file);
} else {
throw new IOException("不支持的文件类型");
}
}

/**
* 测试类
* @param args
*/
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
List<List<Object>> list = readExcel(new File("D:/test.xls"));

for(List<Object> l : list){
for(Object o: l){
System.out.println("结果为: "+o);
}
}

}
}

Poi写入excel文件
package com.tp.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.LinkedList;
import java.util.List;

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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TestWtExcel {

/**
* 写入07excel
* @param filePath
*/
public static void write2007Excel(String filePath,List<String> list) {

try {
//创建excel文件对象  
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
//创建一个张表  
XSSFSheet sheet = wb.getSheetAt(0);

//创建行对象
XSSFRow row = null;
//创建表格对象
XSSFCell cell = null;

//循环行
for (int i = sheet.getFirstRowNum(); i <= sheet
.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null) {//判断是否为空
continue;
}
//循环列
for (int j=0; j<list.size(); j++) {
cell = row.createCell(j);//创建单元格
String m = list.get(j);
cell.setCellValue(m);//赋值
}

}
FileOutputStream out = new FileOutputStream(filePath);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}

/**
* 寫入03excel
* @param filePath
*/
public static void write2003Excel(String filePath,List<String> list) {
try {
//创建excel文件对象  
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(filePath));
//创建一个张表  
HSSFSheet sheet = wb.getSheetAt(0);

//创建行对象
HSSFRow row = null;
//创建表格对象
HSSFCell cell = null;

//循环行
for (int i = sheet.getFirstRowNum(); i <= sheet
.getPhysicalNumberOfRows(); i++) {
row = sheet.getRow(i);
if (row == null) {//判断是否为空
continue;
}
//循环列
for (int j=0; j<list.size(); j++) {
cell = row.createCell(j);//创建单元格
String m = list.get(j);
cell.setCellValue(m);//赋值
}

}
FileOutputStream out = new FileOutputStream(filePath);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}


/**
* 对外提供读取excel 的方法
*/
public static void writeExcel(File file,List<String> mList) throws IOException {
String fileName = file.getName();
String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
.substring(fileName.lastIndexOf(".") + 1);

//判断文件类型
if ("xls".equals(extension)) {
write2003Excel(file.getPath(),mList);
} else if ("xlsx".equals(extension)) {
write2007Excel(file.getPath(),mList);
} else {
throw new IOException("不支持的文件类型");
}
}

public static void main(String[] args) throws IOException{
List<String> mList = new LinkedList<String>();
mList.add("aa");
mList.add("bb");
mList.add("cc");
mList.add("dd");
mList.add("ee");
writeExcel(new File("D:/test.xls"),mList);
}

}