java 直接調用micorosoft office （2003-2007和2010版本）中excel中計算函數

標籤：

   主要是講述java中poi讀取excel，而excel的版本包括：2003-2007和2010兩個版本， 即excel的尾碼名為：xls和xlsx

 

            讀取excel和MySQL相關： java的poi技術讀取Excel資料到MySQL

             你也可以在 ： java的poi技術讀取和匯入Excel 瞭解到寫入Excel的方法資訊

             使用JXL技術 ： java的jxl技術匯入Excel

 

          

                           下面是本文的項目結構：

 

                             

========================================

                                項目中所需要的jar檔案：

                             

==================================

                    

                                       所用的Excel資料（2003-2007,2010都是一樣的資料）

                                             

===========================================================================

                                                       運行效果：

                                              

=================================================

                                                               源碼部分：

=================================================

                     /Excel2010/src/com/b510/common/Common.java

        

                     

                                  /** * */package com.b510.common;/** * @author Hongten * @created 2014-5-21 */public class Common { public static final String OFFICE_EXCEL_2003_POSTFIX = "xls"; public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx"; public static final String EMPTY = ""; public static final String POINT = "."; public static final String LIB_PATH = "lib"; public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2003_POSTFIX; public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2010_POSTFIX; public static final String NOT_EXCEL_FILE = " : Not the Excel file!"; public static final String PROCESSING = "Processing...";}

==============================================

=================================================

   /Excel2010/src/com/b510/excel/ReadExcel.java

                                   

 

                                    /** * */package com.b510.excel;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.b510.common.Common;import com.b510.excel.util.Util;import com.b510.excel.vo.Student;/** * @author Hongten * @created 2014-5-20 */public class ReadExcel { /** * read the Excel file * @param path the path of the Excel file * @return * @throws IOException */ public List<Student> readExcel(String path) throws IOException { if (path == null || Common.EMPTY.equals(path)) { return null; } else { String postfix = Util.getPostfix(path); if (!Common.EMPTY.equals(postfix)) { if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) { return readXls(path); } else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) { return readXlsx(path); } } else { System.out.println(path + Common.NOT_EXCEL_FILE); } } return null; } /** * Read the Excel 2010 * @param path the path of the excel file * @return * @throws IOException */ public List<Student> readXlsx(String path) throws IOException { System.out.println(Common.PROCESSING + path); InputStream is = new FileInputStream(path); XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); Student student = null; List<Student> list = new ArrayList<Student>(); // Read the Sheet for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) { XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet); if (xssfSheet == null) { continue; } // Read the Row for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { XSSFRow xssfRow = xssfSheet.getRow(rowNum); if (xssfRow != null) { student = new Student(); XSSFCell no = xssfRow.getCell(0); XSSFCell name = xssfRow.getCell(1); XSSFCell age = xssfRow.getCell(2); XSSFCell score = xssfRow.getCell(3); student.setNo(getValue(no)); student.setName(getValue(name)); student.setAge(getValue(age)); student.setScore(Float.valueOf(getValue(score))); list.add(student); } } } return list; } /** * Read the Excel 2003-2007 * @param path the path of the Excel * @return * @throws IOException */ public List<Student> readXls(String path) throws IOException { System.out.println(Common.PROCESSING + path); InputStream is = new FileInputStream(path); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); Student student = null; List<Student> list = new ArrayList<Student>(); // Read the Sheet for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // Read the Row for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { HSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow != null) { student = new Student(); HSSFCell no = hssfRow.getCell(0); HSSFCell name = hssfRow.getCell(1); HSSFCell age = hssfRow.getCell(2); HSSFCell score = hssfRow.getCell(3); student.setNo(getValue(no)); student.setName(getValue(name)); student.setAge(getValue(age)); student.setScore(Float.valueOf(getValue(score))); list.add(student); } } } return list; } @SuppressWarnings("static-access") private String getValue(XSSFCell xssfRow) { if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) { return String.valueOf(xssfRow.getBooleanCellValue()); } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) { return String.valueOf(xssfRow.getNumericCellValue()); } else { return String.valueOf(xssfRow.getStringCellValue()); } } @SuppressWarnings("static-access") private String getValue(HSSFCell hssfCell) { if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) { return String.valueOf(hssfCell.getBooleanCellValue()); } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) { return String.valueOf(hssfCell.getNumericCellValue()); } else { return String.valueOf(hssfCell.getStringCellValue()); } }}

=========================

=============================================================================

                                                                    /Excel2010/src/com/b510/excel/client/Client.java

                                        

 

                                     /** * */package com.b510.excel.client;import java.io.IOException;import java.util.List;import com.b510.common.Common;import com.b510.excel.ReadExcel;import com.b510.excel.vo.Student;/** * @author Hongten * @created 2014-5-21 */public class Client { public static void main(String[] args) throws IOException { String excel2003_2007 = Common.STUDENT_INFO_XLS_PATH; String excel2010 = Common.STUDENT_INFO_XLSX_PATH; // read the 2003-2007 excel List<Student> list = new ReadExcel().readExcel(excel2003_2007); if (list != null) { for (Student student : list) { System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore()); } } System.out.println("======================================"); // read the 2010 excel List<Student> list1 = new ReadExcel().readExcel(excel2010); if (list1 != null) { for (Student student : list1) { System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore()); } } }}

 

===============================================-----------========================

====================

                               /Excel2010/src/com/b510/excel/util/Util.java

 

 

                                     /** * */package com.b510.excel.util;import com.b510.common.Common;/** * @author Hongten * @created 2014-5-21 */public class Util { /** * get postfix of the path * @param path * @return */ public static String getPostfix(String path) { if (path == null || Common.EMPTY.equals(path.trim())) { return Common.EMPTY; } if (path.contains(Common.POINT)) { return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length()); } return Common.EMPTY; }}

===============================

===============================================================================

                           /Excel2010/src/com/b510/excel/vo/Student.java

 

 

                                           /** * */package com.b510.excel.vo;/** * Student * * @author Hongten * @created 2014-5-18 */public class Student { /** * id */ private Integer id; /** * 學號 */ private String no; /** * 姓名 */ private String name; /** * 學院 */ private String age; /** * 成績 */ private float score; public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getNo() { return no; } public void setNo(String no) { this.no = no; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAge() { return age; } public void setAge(String age) { this.age = age; } public float getScore() { return score; } public void setScore(float score) { this.score = score; }}

 

 

 

==============================================================================

++++++++++++++++++++++++++++++++++++++++++++++_____________________+++++++++++++++++

 

java 直接調用micorosoft office （2003-2007和2010版本）中excel中計算函數