excel2003和excel2007文件的创建和读取

　　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(); 

今天专门在此通过项目做一个总结，项目结构如图所示：

　　　　　　

　　思路如下：

　　　　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;
    }

}

　　　　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();

    }

}

　　　　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(); 
    }
}

　　　　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;
    }
    
    
}

　　　　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;
    }
}

　　　　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;
    }
    
    
    
    
}