上面已經講了用jsp如何上傳文件，下面我們來講解如何用poi讀取excel中的內容
封裝了一個ExcelReader底層類。主要讀取excel內容的方法都在這里
package com.wellsoon.jyhd.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
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.poifs.filesystem.POIFSFileSystem;
/**
?* @author Jimmy Liu
?*/
public class ExcelReader {
??? private POIFSFileSystem excelFile = null;//file
??? private HSSFWorkbook wb = null;//book [includes sheet]
??? private HSSFSheet sheet = null;
??? private HSSFRow row = null;
??? private HSSFCell cell = null; //cell,the content which contains
??? private int sheetNum = 0;?//第sheetnum個工作表
??? private int rowNum = 0;
??? private FileInputStream fis = null;
??? private String fileName = "";
??? public ExcelReader() {
??? }
??? public ExcelReader(String fileName) {
??????? openFile(fileName);
??? }
??? public void setRowNum(int rowNum) {
??????? this.rowNum = rowNum;
??? }
??? public void setSheetNum(int sheetNum) {
??????? this.sheetNum = sheetNum;
??? }
??? /**
???? * 讀取excel文件獲得HSSFWorkbook對象
???? * @param fileName
???? */
??? public void openFile(String fileName) {
??????? this.fileName = fileName;
??????? File f = new File(fileName);
??????? if (f.exists()) {
??????????? try {
??????????????? fis = new FileInputStream(f);
??????????????? excelFile = new POIFSFileSystem(fis);
??????????????? wb = new HSSFWorkbook(excelFile);
??????????????? fis.close();
??????????????? if(null == wb){
??????????????? }else{
??????????????????? System.out.println("WorkBook不為空");
??????????????? }
??????????? } catch (Exception e) {
??????????????? e.printStackTrace();
??????????? }
??????? } else {
??????????? System.out.println("===========================>>>>>>>>>>>>>>>>>>>>>>>>>excel File? not exist");
??????? }
??? }
??? /**
???? * 返回sheet表數目
???? * @return int
???? */
??? public int getSheetCount() {
??????? int sheetCount = -1;
??????? sheetCount = wb.getNumberOfSheets();
??????? return sheetCount;
??? }
??? /**
???? * sheetNum下的記錄行數
???? * @return int
???? */
??? public int getRowCount() {
??????? if(null == wb) System.out.println("=============>WorkBook為空");
??????? HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
??????? int rowCount = -1;
??????? rowCount = sheet.getLastRowNum();
??????? return rowCount;
??? }
??? /**
???? * 讀取指定sheetNum的rowCount
???? * @param sheetNum
???? * @return int
???? */
??? public int getRowCount(int sheetNum) {
??????? HSSFSheet sheet = wb.getSheetAt(sheetNum);
??????? int rowCount = -1;
??????? rowCount = sheet.getLastRowNum();
??????? return rowCount;
??? }
??? /**
???? * 得到指定行的內容
???? * @param lineNum
???? * @return String[]
???? */
??? public String[] readExcelLine(int lineNum) {
??????? return readExcelLine(this.sheetNum, lineNum);
??? }
??? /**
???? * 指定工作表和行數的內容
???? * @param sheetNum
???? * @param lineNum
???? * @return String[]
???? */
??? public String [] readExcelLine(int sheetNum, int lineNum) {
??????? if (sheetNum < 0 || lineNum < 0) return null;
??????? String[] strExcelLine = null;
??????? try {
??????????? sheet = wb.getSheetAt(sheetNum);
??????????? row = sheet.getRow(lineNum);
??????????? int cellCount = row.getLastCellNum();
??????????? strExcelLine = new String[cellCount];
??????????? for (int i = 0; i < cellCount; i++) {
??????????????? strExcelLine[i] = "";
??????????????? if (null != row.getCell((short) i)) {
??????????????????? switch (row.getCell((short) i).getCellType()) {
??????????????????????? case HSSFCell.CELL_TYPE_FORMULA :
??????????????????????????? strExcelLine[i] = "FORMULA ";
??????????????????????????? break;
??????????????????????? case HSSFCell.CELL_TYPE_NUMERIC :
??????????????????????????? strExcelLine[i] = String.valueOf(row.getCell((short) i).getNumericCellValue());
??????????????????????????? break;
??????????????????????? case HSSFCell.CELL_TYPE_STRING :
??????????????????????????? strExcelLine[i] = row.getCell((short) i).getStringCellValue();
??????????????????????????? break;
??????????????????????? case HSSFCell.CELL_TYPE_BLANK :
??????????????????????????? strExcelLine[i] = "";
??????????????????????????? break;
??????????????????????? default :
??????????????????????????? strExcelLine[i] = "";
??????????????????????????? break;
??????????????????? }
??????????????????? //如果讀取的是科學計數法的格式，則轉換為普通格式
??????????????????? //added by wangtongjun at 20060626
??????????????????? if (null != strExcelLine[i] &&
??????????????????????????? strExcelLine[i].indexOf(".") != -1 &&
??????????????????????????? strExcelLine[i].indexOf("E") != -1) {
??????????????????????? DecimalFormat df = new DecimalFormat();
??????????????????????? strExcelLine[i] = df.parse(strExcelLine[i]).toString();
??????????????????? }
??????????????????? //如果讀取的是數字格式，并且以".0"結尾格式，則轉換為普通格式
??????????????????? //added by wangtongjun at 20060713
??????????????????? if (null != strExcelLine &&
??????????????????????????? strExcelLine[i].endsWith(".0")) {
??????????????????????? int size = strExcelLine[i].length();
??????????????????????? strExcelLine[i] = strExcelLine[i].substring(0, size - 2);
??????????????????? }
??????????????? }
??????????? }
??????? } catch (Exception e) {
??????????? e.printStackTrace();
??????? }
??????? return strExcelLine;
??? }
??? /**
???? * 讀取指定列的內容
???? * @param cellNum
???? * @return String
???? */
??? public String readStringExcelCell(int cellNum) {
??????? return readStringExcelCell(this.rowNum, cellNum);
??? }
???
??? /**
???? * 指定行和列編號的內容
???? * @param rowNum
???? * @param cellNum
???? * @return String
???? */
??? public String readStringExcelCell(int rowNum, int cellNum) {
??????? return readStringExcelCell(this.sheetNum, rowNum, cellNum);
??? }
??? /**
???? * 指定工作表、行、列下的內容
???? * @param sheetNum
???? * @param rowNum
???? * @param cellNum
???? * @return String
???? */
??? public String readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
??????? String strExcelCell = "";
??????? if (sheetNum < 0 || rowNum < 0) return null;
??????? try {
??????????? sheet = wb.getSheetAt(sheetNum);
??????????? row = sheet.getRow(rowNum);
??????????? /**
???????????? * modified by wangtongjun
???????????? * bug description:
???????????? * if the cell is empty ,the behind data can't get
???????????? * for null point exception deal
???????????? */
??????????? if (null != row.getCell((short) cellNum)) { //add this condition judge
??????????????? switch (row.getCell((short) cellNum).getCellType()) {
??????????????????? case HSSFCell.CELL_TYPE_FORMULA :
??????????????????????? strExcelCell = "FORMULA ";
??????????????????????? break;
??????????????????? case HSSFCell.CELL_TYPE_NUMERIC : {
??????????????????????? strExcelCell = String.valueOf(row.getCell((short) cellNum).getNumericCellValue());
??????????????????? }
??????????????????? break;
??????????????????? case HSSFCell.CELL_TYPE_STRING :
??????????????????????? strExcelCell = row.getCell((short) cellNum).getStringCellValue();
??????????????????????? break;
??????????????????? default :
??????????????? }
??????????????? //如果讀取的是科學計數法的格式，則轉換為普通格式
??????????????? //added by wangtongjun at 20060626
??????????????? if (null != strExcelCell &&
??????????????????????? strExcelCell.indexOf(".") != -1 &&
??????????????????????? strExcelCell.indexOf("E") != -1) {
??????????????????? DecimalFormat df = new DecimalFormat();
??????????????????? strExcelCell = df.parse(strExcelCell).toString();
??????????????? }
??????????????? //如果讀取的是數字格式，并且以".0"結尾格式，則轉換為普通格式
??????????????? //added by wangtongjun at 20060713
??????????????? if (null != strExcelCell &&
??????????????????????? strExcelCell.endsWith(".0")) {
??????????????????? int size = strExcelCell.length();
??????????????????? strExcelCell = strExcelCell.substring(0, size - 2);
??????????????? }
??????????? }
??????? } catch (Exception e) {
??????????? e.printStackTrace();
??????? }
??????? return strExcelCell;
??? }
??? public void writeExcelLine(String fileName, String[] strLine) {
??????? try {
??????????? File f = new File(fileName + ".xls");
??????????? if (f.isFile()) {
??????????????? FileOutputStream fileOut = new FileOutputStream(f);
??????????????? sheet = wb.createSheet("Sheet1");
??????????????? row = sheet.createRow(0);
??????????????? int cellNum = strLine.length;
??????????????? for (int i = 0; i < cellNum; i++) {
??????????????????? row.createCell((short) i).setCellValue(strLine[i]);
??????????????? }
??????????????? wb.write(fileOut);
??????????????? fileOut.close();
??????????? }
??????? }
??????? catch (FileNotFoundException e) {
??????? }
??????? catch (IOException e) {
??????? }
??? }
??? public void writeExcelLine(String fileName, String[] strLine, int iRownum) {
??????? try {
??????????? File f = new File(fileName + ".xls");
??????????? if (f.isFile()) {
??????????????? FileOutputStream fileOut = new FileOutputStream(f);
??????????????? sheet = wb.getSheet("Sheet1");
??????????????? if (null == sheet) {
??????????????????? sheet = wb.createSheet("Sheet1");
??????????????? }
??????????????? row = sheet.createRow(iRownum);
??????????????? int cellNum = strLine.length;
??????????????? for (int i = 0; i < cellNum; i++) {
??????????????????? HSSFCell cell = row.createCell((short)i);
??????????????????? cell.setEncoding(HSSFCell.ENCODING_UTF_16);
??????????????????? cell.setCellValue(strLine[i]);
??????????????? }
??????????????? wb.write(fileOut);
??????????????? fileOut.close();
??????????? }
??????? }
??????? catch (FileNotFoundException e) {
??????? }
??????? catch (IOException e) {
??????? }
??? }
??? public void writeExcelCell(int sheetNum, int rowNum, int cellNum, String strCell) {
??????? sheet = wb.getSheetAt(sheetNum);
??????? row = sheet.getRow(rowNum);
??????? cell = row.getCell((short) cellNum);
??????? cell.setCellValue(strCell);
??????? try {
??????????? File f = new File(fileName);
??????????? if (f.isFile()) {
??????????????? FileOutputStream fileOut = new FileOutputStream(f);
??????????????? sheet = wb.createSheet("Sheet1");
??????????????? row = sheet.createRow(1);
??????????????? //int cellNum=strLine.length;
??????????????? for (int i = 0; i < 10; i++) {
??????????????????? //row.createCell((short)i).setCellValue(strLine[i]);
??????????????? }
??????????????? wb.write(fileOut);
??????????????? fileOut.close();
??????????? }
??????? }
??????? catch (FileNotFoundException e) {
??????? }
??????? catch (IOException e) {
??????? }
??? }
??? public static void main(String args[]) throws Exception {
??????? ExcelReader readExcel = new ExcelReader();
??????? readExcel.openFile("C:\\all.xls");
??????? readExcel.setSheetNum(0);?//設置讀取索引為0的工作表
??????? //總行數
??????? int count = readExcel.getRowCount();
??????? System.out.println(readExcel.readStringExcelCell(1, 5));
??????? for (int i = 0; i <= count; i++) {
??????????? String[] rows = readExcel.readExcelLine(i);
??????????? for (int j = 0; j < rows.length; j++) {
??????????????? System.out.print(j+" "+rows[j]+"? ");
??????????? }
??????????? System.out.print("\n");
??????? }
??? }
}
業務邏輯類：OnlineTestServiceImpl.java
package com.wellsoon.jyhd.service.impl;
/**
?* 在線測試
?* User: dai_yingbo
?* Date: 2007-1-29
?* Time: 17:55:34
?*/
import java.util.ArrayList;
import java.util.List;
import com.wellsoon.jyhd.db.DBConnectionManager;
import com.wellsoon.jyhd.util.ExcelReader;
public class OnlineTestServiceImpl{
?//得到excel表中的數據，以集合返回出來
?public List readElevenPlusExcel(String filename,ExcelReader readExcel) {
??????? //服務器上文件的路徑
??????? String path =? "D:/test1/"+filename;
??????? readExcel.openFile(path);
??????? readExcel.setSheetNum(0);
??????? List list = new ArrayList();
??????? int count = -1;
??????? try{
??????? count = readExcel.getRowCount();
??????? }catch(Exception ex){
??????????? ex.printStackTrace();
??????? }
??????? for (int a = 0; a <= count; a++) {
??????????? String rows[] = readExcel.readExcelLine(a);
??????????? list.add(rows);
??????? }
??????? return list;
?}
?
?//將excel中的數據讀入到數據庫表中
?public int importExcelToElevenPlusTest(List list,ExcelReader readExcel) throws Exception{
??? ?String[] rowsTop = (String[])list.get(0);?//表頭
??if((rowsTop[0].trim()==null || rowsTop[0].indexOf("題目")==-1) ||
????(rowsTop[1].trim()==null || rowsTop[1].indexOf("類型")==-1) ||
????(rowsTop[2].trim()==null || rowsTop[2].indexOf("分值")==-1) ||
????(rowsTop[3].trim()==null || rowsTop[3].indexOf("答案")==-1) ||
????(rowsTop[4].trim()==null || !rowsTop[4].matches("[a-zA-Z]")) || rowsTop==null)
???throw new Exception("excel表不符合規范");
??for(int i=1;i<list.size();i++){
???String[] rows =(String[])list.get(i);
???if((rows[0]==null || rows[0].equals("")) ||
?????(rows[1]==null || rows[1].equals("")) ||
?????(rows[2]==null || rows[2].equals("")) ||
?????(rows[3]==null || rows[3].equals("")) ||
?????(rows[4]==null || rows[4].equals(""))){
????throw new Exception("excel表不符合規范");
???}
??}
??DBConnectionManager db = null;
??int results = 0;
??????? try {
??????? ?db = new DBConnectionManager();?//事務添加題目的備選答案
???????????
??????? ?/**String examId=onlineTest.getExamId();
??? ? ?String examName=onlineTest.getExamName();
??? ? ?String subjectId=onlineTest.getSubjectId();
??? ? ?String examType=onlineTest.getExamType();
??? ? ?String author=onlineTest.getAuthor();
??? ? ?String siteId=onlineTest.getSiteId();
??? ? ?String gradeId=onlineTest.getGradeId();
??? ? ?String classId=onlineTest.getClassId();
??? ? ?String intorduce=onlineTest.getIntroduce();
??? ? ?//添加測試
??????????? String sql ="insert into tbl_online_test(exam_id,exam_name,subject_id,"+
??????????????? " exam_type,exam_time,author,site_id,grade_id,class_id,introduction) values ('"+examId+"'," +
??????????????? "'"+examName+"','"+subjectId+"','"+examType+"',sysdate,'"+author+"','"+siteId+"'," +
??????????????? "'"+gradeId+"','"+classId+"','"+intorduce+"')";
??????????? ?db.addBatch(sql);
??????????? ?
???for(int i=1;i<list.size();i++){
????String[] rows =(String[])list.get(i);
????String QuestionID=WellsoonUtil.getSequence("tbl_online_questions_seq");
????String ExamQuestion = rows[0];?//考試題目
????String QuestionType = rows[1];?//題目類型(單選,多選)
????String Score = rows[2];???//題目分值
????String standard = rows[3];??//標準答案
????//插入測試的題目表
????String questionsql="insert into TBL_ONLINE_QUESTIONS(question_id,exam_id,exam_question," +
????"question_type,score,standard_answer) values('"+QuestionID+"','"+
????examId+"','"+ExamQuestion+"','"+getRealExamType(QuestionType)+"','"+
????Score+"','"+WellsoonUtil.getUpperCase(standard)+"')";
????db.addBatch(questionsql);
????
????for(int j=4;j<rows.length;j++){????????? ?
?????if(rows[j].trim().equals(""))
??????break;
?????else{
??????//插入考試題目的備選答案
??????String spareanswersql="insert into tbl_online_spare_answers(id,question_id,answer_id,answer_content)" +
???????" values(tbl_online_spare_answers_seq.nextval,'"+QuestionID+"','"+rowsTop[j].trim()+
???????"','"+rows[j].trim()+"')";
??????db.addBatch(spareanswersql);
?????}
????}
????results += db.executeBatch().length;
????db.endTransaction();
???}*/
??} catch (Exception e) {
???e.printStackTrace();
??}
??finally {
??????????? try {
??????????????? db.close();
??????????? } catch (Exception ee) {
??????????????? ee.printStackTrace();
??????????? }
??????? }
??return results;
?}
?
?//修改在線測試表并讀取excel
?public int updateExcelToElevenPlusTest(List list,String examId,ExcelReader readExcel) throws Exception {
??if (null == list || list.size() == 0) throw new Exception("=====>數據表中沒有記錄");
??DBConnectionManager db = null;
??int results = 0;
??????? try {
??????? ?db = new DBConnectionManager();
??????????? /**db.beginTransaction();
??????????? //刪除原有記錄
??????????? String sql="delete from tbl_online_questions where exam_id="+examId;
??????????? db.addBatch(sql);
???????????
??????? ?String[] rowsTop = (String[])list.get(0);?//表頭
???for(int i=1;i<list.size();i++){
????String[] rows =(String[])list.get(i);
????String QuestionID=WellsoonUtil.getSequence("tbl_online_questions_seq");
????String ExamQuestion = rows[0];?//考試題目
????String QuestionType = rows[1];?//題目類型(單選,多選)
????String Score = rows[2];???//題目分值
????String standard = rows[3];??//標準答案????
????//插入測試的題目表
????String questionsql="insert into TBL_ONLINE_QUESTIONS(question_id,exam_id,exam_question," +
????"question_type,score,standard_answer) values('"+QuestionID+"','"+
????examId+"','"+ExamQuestion+"','"+getRealExamType(QuestionType)+"','"+
????Score+"','"+WellsoonUtil.getUpperCase(standard)+"')";
????db.addBatch(questionsql);
????
????for(int j=4;j<rows.length;j++){????????? ?
?????if(rows[j].trim().equals(""))
??????break;
?????else{
??????//插入考試題目的備選答案
??????String spareanswersql="insert into tbl_online_spare_answers(id,question_id,answer_id,answer_content)" +
???????" values(tbl_online_spare_answers_seq.nextval,'"+QuestionID+"','"+rowsTop[j].trim()+
???????"','"+rows[j].trim()+"')";
??????db.addBatch(spareanswersql);
?????}
????}
????results += db.executeBatch().length;
????db.endTransaction();
???}*/
??} catch (Exception e) {
???e.printStackTrace();
??}
??finally {
??????????? try {
??????????????? db.close();
??????????? } catch (Exception ee) {
??????????????? ee.printStackTrace();
?????????????? // db.rollback();
??????????? }
??????? }
??return results;
?}
?
?//考試類型=(0,單選,單,單選題)單,多=(1,多,多選,多選題)
?public String getRealExamType(String examType){
??String type="0";
??if(examType==null || examType.trim().length()==0){
???type="0";
??}else if(examType.indexOf("單")!=-1 || examType.equals("0")){
???type="0";
??}else if(examType.indexOf("多")!=-1 || examType.equals("1")){
???type="1";
??}
??return type;
?}
}
在upload.jsp中添加
List content=epsi.readElevenPlusExcel(sServerFileName,readExcel);?//得到excel表中的數據
??for(int i=0;i<content.size();i++){
???String rows[]=(String[])content.get(i);
???for(int j=0;j<rows.length;j++){
????out.println(i+": row["+j+"]"+rows[j]);//列出excel表中的所有
???}
??}
點擊下載
Lyyb2001