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