上面已经讲了用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