在网上找到的一个很好的poi DEMO ，本地测试已通过，感谢原作者！需要的Jar包附件已上传，
要注意的是，除了最基本的poi-3.9.jar，操作2007版本的excel时还要用到poi-ooxml-3.9、poi-ooxml-schemas-3.9、还需要一些包的支持：xmlbeans-2.3.0、dom4j-1.6.1、junit-4.1。
package com.weil.poi.excel;
import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Arrays;
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;
import com.weil.poi.bean.Student;
public class GenerateExcel {
private static String xls2003 = "D:\\Book1.xls";
private static String xlsx2007 = "D:\\Book1.xlsx";
private static List<Student> studentList = null;
private static Student[] students = new Student[4];
/**
* 静态块初始化数据
*/
static {
studentList = new ArrayList<Student>();
students[0] = new Student("张三", "男", 23, "一班", 94);
students[1] = new Student("李四", "女", 20, "一班", 92);
students[2] = new Student("王五", "男", 21, "一班", 87);
students[3] = new Student("赵六", "女", 22, "一班", 83);
studentList.addAll(Arrays.asList(students));
}
/**
* 创建2003文件的方法
*
* @param filePath
*/
public static void generateExcel2003(String filePath) {
// 先创建工作簿对象
HSSFWorkbook workbook2003 = new HSSFWorkbook();
// 创建工作表对象并命名
HSSFSheet sheet = workbook2003.createSheet("学生信息统计表");
// 遍历集合对象创建行和单元格
for (int i = 0; i < studentList.size(); i++) {
// 取出Student对象
Student student = studentList.get(i);
// 创建行
HSSFRow row = sheet.createRow(i);
// 开始创建单元格并赋值
HSSFCell nameCell = row.createCell(0);
nameCell.setCellValue(student.getName());
HSSFCell genderCell = row.createCell(1);
genderCell.setCellValue(student.getGender());
HSSFCell ageCell = row.createCell(2);
ageCell.setCellValue(student.getAge());
HSSFCell sclassCell = row.createCell(3);
sclassCell.setCellValue(student.getSclass());
HSSFCell scoreCell = row.createCell(4);
scoreCell.setCellValue(student.getScore());
}
// 生成文件
File file = new File(filePath);
FileOutputStream fos = null;
try {
fos = new FileOutputStream(file);
workbook2003.write(fos);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fos != null) {
try {
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 创建2007文件的方法
*
* @param filePath
*/
public static void generateExcel2007(String filePath) {
// 先创建工作簿对象
XSSFWorkbook workbook2003 = new XSSFWorkbook();
// 创建工作表对象并命名
XSSFSheet sheet = workbook2003.createSheet("学生信息统计表");
// 遍历集合对象创建行和单元格
for (int i = 0; i < studentList.size(); i++) {
// 取出Student对象
Student student = studentList.get(i);
// 创建行
XSSFRow row = sheet.createRow(i);
// 开始创建单元格并赋值
XSSFCell nameCell = row.createCell(0);
nameCell.setCellValue(student.getName());
XSSFCell genderCell = row.createCell(1);
genderCell.setCellValue(student.getGender());
XSSFCell ageCell = row.createCell(2);
ageCell.setCellValue(student.getAge());
XSSFCell sclassCell = row.createCell(3);
sclassCell.setCellValue(student.getSclass());
XSSFCell scoreCell = row.createCell(4);
scoreCell.setCellValue(student.getScore());
}
// 生成文件
File file = new File(filePath);
FileOutputStream fos = null;
try {
fos = new FileOutputStream(file);
workbook2003.write(fos);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fos != null) {
try {
fos.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 主函数
*
* @param args
*/
public static void main(String[] args) {
long start = System.currentTimeMillis();
generateExcel2007(xlsx2007);
long end = System.currentTimeMillis();
System.out.println((end - start) + " ms done!");
}
}
package com.weil.poi.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
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;
import com.weil.poi.bean.Student;
public class ReadExcel {
private static String xls2003 = "D:\\Book1.xls";
private static String xls2007 = "D:\\Book1.xlsx";
/**
* 读取Excel2003的示例方法
*
* @param filePath
* @return
*/
private static List<Student> readFromXLS2003(String filePath) {
File excelFile = null;// Excel文件对象
InputStream is = null;// 输入流对象
String cellStr = null;// 单元格，最终按字符串处理
List<Student> studentList = new ArrayList<Student>();// 返回封装数据的List
Student student = null;// 每一个学生信息对象
try {
excelFile = new File(filePath);
is = new FileInputStream(excelFile);// 获取文件输入流
HSSFWorkbook workbook2003 = new HSSFWorkbook(is);// 创建Excel2003文件对象
HSSFSheet sheet = workbook2003.getSheetAt(0);// 取出第一个工作表，索引是0
// 开始循环遍历行，表头不处理，从1开始
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
student = new Student();// 实例化Student对象
HSSFRow row = sheet.getRow(i);// 获取行对象
if (row == null) {// 如果为空，不处理
continue;
}
// 循环遍历单元格
for (int j = 0; j < row.getLastCellNum(); j++) {
HSSFCell cell = row.getCell(j);// 获取单元格对象
if (cell == null) {// 单元格为空设置cellStr为空串
cellStr = "";
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {// 对布尔值的处理
cellStr = String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {// 对数字值的处理
cellStr = cell.getNumericCellValue() + "";
} else {// 其余按照字符串处理
cellStr = cell.getStringCellValue();
}
// 下面按照数据出现位置封装到bean中
if (j == 0) {
student.setName(cellStr);
} else if (j == 1) {
student.setGender(cellStr);
} else if (j == 2) {
student.setAge(new Double(cellStr).intValue());
} else if (j == 3) {
student.setSclass(cellStr);
} else {
student.setScore(new Double(cellStr).intValue());
}
}
studentList.add(student);// 数据装入List
}
} catch (IOException e) {
e.printStackTrace();
} finally {// 关闭文件流
if (is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return studentList;
}
/**
* 读取Excel2007的示例方法
*
* @param filePath
* @return
*/
public static List<Student> readFromXLSX2007(String filePath) {
File excelFile = null;// Excel文件对象
InputStream is = null;// 输入流对象
String cellStr = null;// 单元格，最终按字符串处理
List<Student> studentList = new ArrayList<Student>();// 返回封装数据的List
Student student = null;// 每一个学生信息对象
try {
excelFile = new File(filePath);
is = new FileInputStream(excelFile);// 获取文件输入流
XSSFWorkbook workbook2007 = new XSSFWorkbook(is);// 创建Excel2003文件对象
XSSFSheet sheet = workbook2007.getSheetAt(0);// 取出第一个工作表，索引是0
// 开始循环遍历行，表头不处理，从1开始
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
student = new Student();// 实例化Student对象
XSSFRow row = sheet.getRow(i);// 获取行对象
if (row == null) {// 如果为空，不处理
continue;
}
// 循环遍历单元格
for (int j = 0; j < row.getLastCellNum(); j++) {
XSSFCell cell = row.getCell(j);// 获取单元格对象
if (cell == null) {// 单元格为空设置cellStr为空串
cellStr = "";
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {// 对布尔值的处理
cellStr = String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {// 对数字值的处理
cellStr = cell.getNumericCellValue() + "";
} else {// 其余按照字符串处理
cellStr = cell.getStringCellValue();
}
// 下面按照数据出现位置封装到bean中
if (j == 0) {
student.setName(cellStr);
} else if (j == 1) {
student.setGender(cellStr);
} else if (j == 2) {
student.setAge(new Double(cellStr).intValue());
} else if (j == 3) {
student.setSclass(cellStr);
} else {
student.setScore(new Double(cellStr).intValue());
}
}
studentList.add(student);// 数据装入List
}
} catch (IOException e) {
e.printStackTrace();
} finally {// 关闭文件流
if (is != null) {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return studentList;
}
/**
* 主函数
*
* @param args
*/
public static void main(String[] args) {
long start = System.currentTimeMillis();
List<Student> list = readFromXLSX2007(xls2007);
for (Student student : list) {
System.out.println(student);
}
long end = System.currentTimeMillis();
System.out.println((end - start) + " ms done!");
}
}
885
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
