excel2003和excel2007文件的创建和读取在项目中用的很多，首先我们要了解excel的常用组件和基本操作步骤。
常用组件如下所示：
HSSFWorkbook excel的文档对象 HSSFSheet excel的表单 HSSFRow excel的行 HSSFCell excel的格子单元 HSSFFont excel字体 HSSFDataFormat 日期格式 HSSFHeader sheet头 HSSFFooter sheet尾（只有打印的时候才能看到效果） 样式： HSSFCellStyle cell样式 辅助操作包括： HSSFDateUtil 日期 HSSFPrintSetup 打印 HSSFErrorConstants 错误信息表
常用组件
基本操作步骤如下：
首先，理解一下一个Excel的文件的组织形式，一个Excel文件对应于一个workbook(HSSFWorkbook)，一个workbook可以有多个 sheet（HSSFSheet）组成，一个sheet是由多个row（HSSFRow）组成，一个row是由多个cell（HSSFCell）组成。
1、用HSSFWorkbook打开或者创建“Excel文件对象”
2、用HSSFWorkbook对象返回或者创建Sheet对象
3、用Sheet对象返回行对象，用行对象得到Cell对象
4、对Cell对象读写。
生成excel的例子如下：
复制代码
//创建HSSFWorkbook对象
HSSFWorkbook wb = new HSSFWorkbook();
//创建HSSFSheet对象
HSSFSheet sheet = wb.createSheet("sheet0");
//创建HSSFRow对象
HSSFRow row = sheet.createRow(0);
//创建HSSFCell对象
HSSFCell cell=row.createCell(0);
//设置单元格的值
cell.setCellValue("单元格中的中文");
//输出Excel文件
FileOutputStream output=new FileOutputStream("d:\\workbook.xls");
wkb.write(output);
output.flush();
生成excel的例子
今天专门在此通过项目做一个总结，项目结构如图所示：
思路如下：
1、从数据库（mysql）读取数据，获取数据集合；
2、判断文件的后缀是.xls 还是.xlsx ？如果后缀是.xls 则是excel2003,否则为excel2007；
3、excel2003的读取和创建；
4、excel2007的读取和创建；
代码如下：
1、数据库工具类：DBhepler
package com.test.excel.poi.dbutil; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException; public class DBhepler {
/*String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://127.0.0.1;DatabaseName=Mobile";*/ String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/excel"; Connection con = null;
ResultSet res = null; public void DataBase() {
try {
Class.forName(driver);
con = DriverManager.getConnection(url, "root", "ROOT");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
System.err.println("装载 JDBC/ODBC 驱动程序失败。" );
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
System.err.println("无法连接数据库" );
e.printStackTrace();
}
} // 查询
public ResultSet Search(String sql, String str[]) {
DataBase();
try {
PreparedStatement pst =con.prepareStatement(sql);
if (str != null) {
for (int i = 0; i < str.length; i++) {
pst.setString(i + 1, str[i]);
}
}
res = pst.executeQuery(); } catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return res;
} // 增删修改
public int AddU(String sql, String str[]) {
int a = 0;
DataBase();
try {
PreparedStatement pst = con.prepareStatement(sql);
if (str != null) {
for (int i = 0; i < str.length; i++) {
pst.setString(i + 1, str[i]);
}
}
a = pst.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return a;
} }
DBhepler
2、测试类：TestExcel
package com.test.test;
import com.test.excel.poi.ExcelUtils;
public class TestExcel {
public static void main(String[] args) throws Exception {
ExcelUtils eu = new ExcelUtils();
eu.parseFile();
}
}
TestExcel
3、excel工具类：ExcelUtils
package com.test.excel.poi; import java.util.ArrayList;
import java.util.List; import com.test.excel.poi.entity.Student;
import com.test.excel.poi.service.StuService; public class ExcelUtils { String newFilePath = "f:\\test\\students.xlsx";
String fileCurName = "f:\\test\\ouyy.xlsx";
public void parseFile() throws Exception{
// 通过文件名截取到文件类型
String fileType = fileCurName.substring(fileCurName.lastIndexOf(".")).toLowerCase();
List<Student> list = new ArrayList<Student>();
//1.从数据库读取数据，获取数据集合
list = StuService.getAllByDb(); // 解析2003及WPS格式的的excel文件
if(fileType.equals(".xls") || fileType.equals(".et"))
{
//1.将excel2003文件读取出来
JExcelTool.readExcel2003(fileCurName); //2.创建excel2003
JExcelTool.createExcel2003(list, newFilePath); } // 解析excel2007文件
else if(fileType.equals(".xlsx"))
{
//1.将excel2003文件读取出来
JExcelTool.readExcel2007(fileCurName); //2.创建excel2003
JExcelTool.createExcel2007(list, newFilePath);
}
} public static void main(String[] args) throws Exception {
ExcelUtils eu = new ExcelUtils();
eu.parseFile();
}
}
ExcelUtils
4、excel的解析类：JExcelTool
package com.test.excel.poi; import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; import com.test.excel.poi.entity.Student; public class JExcelTool { /**
*
*
* @param filePath D:/students.xls
* @return
*
* @Description:读取文件excel2003
*/
public static List<Student> readExcel2003(String filePath) {
List<Student> list = new ArrayList<Student>();
HSSFWorkbook workbook = null; try {
// 读取Excel文件
InputStream inputStream = new FileInputStream(filePath);
workbook = new HSSFWorkbook(inputStream);
inputStream.close();
} catch (Exception e) {
e.printStackTrace();
} // 循环工作表
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = workbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
} // 将单元格中的内容存入集合
Student student = new Student(); HSSFCell cell = hssfRow.getCell(0);
if (cell == null) {
continue;
}
student.setId((int) cell.getNumericCellValue());
cell = hssfRow.getCell(1);
if (cell == null) {
continue;
}
student.setName(cell.getStringCellValue()); cell = hssfRow.getCell(2);
if (cell == null) {
continue;
}
student.setSex(cell.getStringCellValue()); cell = hssfRow.getCell(3);
if (cell == null) {
continue;
}
student.setNum((int) cell.getNumericCellValue()); list.add(student);
}
}
return list;
} /**
*
*getCellFormatValue(row.getCell(0));
* @param list
* @param newFilePath 新的文件 f:/students.xls
*
* @Description: 创造文件excel2003
*/
public static void createExcel2003(List<Student> list,String newFilePath){
// 创建一个Excel文件
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建一个工作表
HSSFSheet sheet = workbook.createSheet("学生表一");
// 添加表头行
HSSFRow hssfRow = sheet.createRow(0);
// 设置单元格格式居中
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 添加表头内容
HSSFCell headCell = hssfRow.createCell(0);
headCell.setCellValue("id");
headCell.setCellStyle(cellStyle); headCell = hssfRow.createCell(1);
headCell.setCellValue("姓名");
headCell.setCellStyle(cellStyle); headCell = hssfRow.createCell(2);
headCell.setCellValue("性别");
headCell.setCellStyle(cellStyle); headCell = hssfRow.createCell(3);
headCell.setCellValue("编号");
headCell.setCellStyle(cellStyle); // 添加数据内容
for (int i = 0; i < list.size(); i++) {
hssfRow = sheet.createRow((int) i + 1);
Student student = list.get(i); // 创建单元格，并设置值
HSSFCell cell = hssfRow.createCell(0);
cell.setCellValue(student.getId());
cell.setCellStyle(cellStyle); cell = hssfRow.createCell(1);
cell.setCellValue(student.getName());
cell.setCellStyle(cellStyle); cell = hssfRow.createCell(2);
cell.setCellValue(student.getSex());
cell.setCellStyle(cellStyle); cell = hssfRow.createCell(3);
cell.setCellValue(student.getNum());
cell.setCellStyle(cellStyle);
} // 保存Excel文件
try {
OutputStream outputStream = new FileOutputStream(newFilePath);
workbook.write(outputStream);
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
} /**
*
*
* @param filePath
* @return
*
* @Description: 读取excel2007
*/
public static List<Student> readExcel2007(String filePath){
List<Student> list = new ArrayList<Student>();
XSSFWorkbook workbook = null; try {
// 读取Excel文件
InputStream inputStream = new FileInputStream(filePath);
workbook = new XSSFWorkbook(inputStream);
inputStream.close();
} catch (Exception e) {
e.printStackTrace();
} // 循环工作表
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
XSSFSheet hssfSheet = workbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// 循环行
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
XSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow == null) {
continue;
} // 将单元格中的内容存入集合
Student student = new Student(); XSSFCell cell = hssfRow.getCell(0);
if (cell == null) {
continue;
}
student.setId((int) cell.getNumericCellValue());
cell = hssfRow.getCell(1);
if (cell == null) {
continue;
}
student.setName(cell.getStringCellValue()); cell = hssfRow.getCell(2);
if (cell == null) {
continue;
}
student.setSex(cell.getStringCellValue()); cell = hssfRow.getCell(3);
if (cell == null) {
continue;
}
student.setNum((int) cell.getNumericCellValue()); list.add(student);
}
}
return list;
} public static void createExcel2007(List<Student> list,String newFilePath){
// 创建一个Excel文件
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建一个工作表
XSSFSheet sheet = workbook.createSheet("学生表一");
// 添加表头行
XSSFRow xssfRow = sheet.createRow(0);
// 设置单元格格式居中
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 添加表头内容
XSSFCell headCell = xssfRow.createCell(0);
headCell.setCellValue("id");
headCell.setCellStyle(cellStyle); headCell = xssfRow.createCell(1);
headCell.setCellValue("姓名");
headCell.setCellStyle(cellStyle); headCell = xssfRow.createCell(2);
headCell.setCellValue("性别");
headCell.setCellStyle(cellStyle); headCell = xssfRow.createCell(3);
headCell.setCellValue("编号");
headCell.setCellStyle(cellStyle); // 添加数据内容
for (int i = 0; i < list.size(); i++) {
xssfRow = sheet.createRow((int) i + 1);
Student student = list.get(i); // 创建单元格，并设置值
XSSFCell cell = xssfRow.createCell(0);
cell.setCellValue(student.getId());
cell.setCellStyle(cellStyle); cell = xssfRow.createCell(1);
cell.setCellValue(student.getName());
cell.setCellStyle(cellStyle); cell = xssfRow.createCell(2);
cell.setCellValue(student.getSex());
cell.setCellStyle(cellStyle); cell = xssfRow.createCell(3);
cell.setCellValue(student.getNum());
cell.setCellStyle(cellStyle);
} // 保存Excel文件
try {
OutputStream outputStream = new FileOutputStream(newFilePath);
workbook.write(outputStream);
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
} /***
* 获得excel的单元格
* @Description: TODO
* @param @param cell
* @param @return
* @return String
*/
private static String getCellFormatValue(XSSFCell cell)
{
String cellvalue = "";
if (cell != null)
{
// 判断当前Cell的Type
switch (cell.getCellType())
{
// 如果当前Cell的Type为NUMERIC
case XSSFCell.CELL_TYPE_NUMERIC:
case XSSFCell.CELL_TYPE_FORMULA:
{
// 判断当前的cell是否为Date
if (DateUtil.isCellDateFormatted(cell))
{
// 如果是Date类型则，取得该Cell的Date值
Date date = cell.getDateCellValue();
// 把Date转换成本地格式的字符串
Calendar c = Calendar.getInstance();
c.setTime(date);
if(c.get(Calendar.HOUR)==0 && c.get(Calendar.MINUTE)==0 && c.get(Calendar.SECOND) ==0){
cellvalue = new SimpleDateFormat("yyyy-MM-dd").format(date);
}else {
cellvalue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
}
}
// 如果是纯数字
else
{
// 取得当前Cell的数值
// 是否有小数部分（分开处理）
if(Math.floor(cell.getNumericCellValue())==cell.getNumericCellValue())
{
cellvalue=String.valueOf((long)cell.getNumericCellValue());
}else {
cellvalue = cell.getRawValue();
} }
break;
}
// 如果当前Cell的Type为STRIN
case XSSFCell.CELL_TYPE_STRING:
// 取得当前的Cell字符串
cellvalue = cell.getStringCellValue();
break;
// 默认的Cell值
default:
cellvalue = " ";
}
}
else
{
cellvalue = "";
}
return cellvalue;
} /**
* 判断单元格格式，返回字符串Excel2003
* @param cell
* @return
*/
private static String getCellFormatValue(HSSFCell cell)
{
String cellvalue = "";
if (cell != null)
{
// 判断当前Cell的Type
switch (cell.getCellType())
{
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
// 判断当前的cell是否为Date
if (DateUtil.isCellDateFormatted(cell))
{
// 如果是Date类型则，取得该Cell的Date值
Date date = cell.getDateCellValue();
// 把Date转换成本地格式的字符串
Calendar c = Calendar.getInstance();
c.setTime(date);
if(c.get(Calendar.HOUR)==0 && c.get(Calendar.MINUTE)==0 && c.get(Calendar.SECOND) ==0){
cellvalue = new SimpleDateFormat("yyyy-MM-dd").format(date);
}else {
cellvalue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
}
}
// 如果是纯数字
else
{
// 是否有小数部分（分开处理）
if(Math.floor(cell.getNumericCellValue())==cell.getNumericCellValue())
{
cellvalue=String.valueOf((long)cell.getNumericCellValue());
}else {
cellvalue = String.valueOf(cell.getNumericCellValue());
}
//System.out.println(cellvalue);
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellvalue = cell.getStringCellValue() ;
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellvalue = cell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
cellvalue = " ";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
cellvalue = " ";
break;
default:
cellvalue = " ";
break;
}
}
else
{
cellvalue = "";
}
return cellvalue;
} }
JExcelTool
5、查询数据库中stu表中所有的数据
package com.test.excel.poi.service; import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List; import com.test.excel.poi.dbutil.DBhepler;
import com.test.excel.poi.entity.Student; public class StuService { /**
* 查询stu表中所有的数据
* @return
*/
public static List<Student> getAllByDb(){
List<Student> list=new ArrayList<Student>();
try {
DBhepler db=new DBhepler();
String sql="select * from stu";
ResultSet rs= db.Search(sql, null);
while (rs.next()) {
int id=rs.getInt("id");
String name=rs.getString("name");
String sex=rs.getString("sex");
int num=rs.getInt("num"); //System.out.println(id+" "+name+" "+sex+ " "+num);
list.add(new Student(id, name, sex, num));
} } catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}
StuService
6、Java实体类：Student
package com.test.excel.poi.entity; /**
* @author Javen
* @Email zyw205@gmail.com
*
*/
public class Student{
private int id;
private String name;
private String sex;
private int num; public Student() {
}
public Student(int id, String name, String sex, int num) {
this.id = id;
this.name = name;
this.sex = sex;
this.num = num;
} @Override
public String toString() {
return "StuEntity [id=" + id + ", name=" + name + ", sex=" + sex
+ ", num=" + num + "]";
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
} }
Student