lyyb2001

只是为方便自己找记录而已
posts - 57, comments - 27, trackbacks - 0, articles - 5
  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理

jsp读取excel表中的内容

Posted on 2007-03-29 17:43 skycity 阅读(5270) 评论(4)  编辑  收藏 所属分类: J2EE技术

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

评论

# re: jsp读取excel表中的内容  回复  更多评论   

2008-08-18 10:12 by Yvon
在找这方面的内容,谢谢博主的分享

# re: jsp读取excel表中的内容  回复  更多评论   

2008-09-26 10:47 by 小张
请问楼主,你用的datasource连接的是什么数据库?数据库表怎么都没说明一下?

# re: jsp读取excel表中的内容[未登录]  回复  更多评论   

2010-12-31 21:37 by yy
哎,看不懂呀5555555

# re: jsp读取excel表中的内容  回复  更多评论   

2015-03-25 14:55 by pb
String examName=onlineTest.getExamName();
onlineTest是什么?哪里定义的?

只有注册用户登录后才能发表评论。


网站导航: