Java操作Excel表(二)学生表的增删查改

Java操作Excel表(二)学生信息管理系统

上一期

前言

       上一期查看了源码并且实现了读和写的操作以后，这一期就直接拿来做项目了。其中最经典的项目就是学生信息管理系统，有IO实现的，也有SQL实现的，今天这里就是要用Excel来替代前两种方式来实现。

建表

       这里给出四个字段，然后代码里面建立相应的实体类

字符串模糊查询辅助方法

这里提到过

    public boolean matchStringByIndexOf(String str,String part) {
        int count = 0;
        int index = 0;
        while( ( index = str.indexOf(part, index) ) != -1 )
        {
            index = index+part.length();
            count++;
        }
        if(count < 1){
            return false;
        }
        return true;
    }

全代码

package com.alvin.service.impl;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import com.alvin.domain.ZStudent;
import com.alvin.service.StringUtilService;
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;
import com.alvin.service.ExcelService;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WriteException;
import jxl.Sheet;
public class ExcelServiceImpl implements ExcelService{
    private String fileName = "E:/ExcelProject/student.xls";
    private StringUtilService stringUtilService = new StringUtilServiceImpl();
    /**
     * 添加数据
     * @throws IOException
     * @throws WriteException
     */
    public void writeExcel(ZStudent zStudent) throws Exception {
        FileInputStream fileInputStream;
        try {
            fileInputStream = new FileInputStream(fileName);
            POIFSFileSystem poifsFileSystem=new POIFSFileSystem(fileInputStream);  //使用POI提供的方法得到excel的信息
            HSSFWorkbook workbook=new HSSFWorkbook(poifsFileSystem);
            HSSFSheet sheet=workbook.getSheetAt(0);  //获取到工作表
            HSSFRow row=sheet.getRow(0);        //获得第一行
            int index=0;
            if("".equals(row)||row==null){  //判断第一行是不是空的
                index=0;
            }else{
                index=sheet.getLastRowNum();
                index=index+1;
            }
            FileOutputStream outputStream=new FileOutputStream(fileName);
            row=sheet.createRow((short)(index)); //在现有行号后追加数据
            //按顺序依次添加
            row.createCell(0).setCellValue(zStudent.getUserName());
            row.createCell(1).setCellValue(zStudent.getAge());
            row.createCell(2).setCellValue(zStudent.getSex());
            row.createCell(3).setCellValue(zStudent.getScore());
            outputStream.flush();
            workbook.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询返显
     * @param userName  根据学生姓名
     * @return
     * @throws IOException
     * @throws BiffException
     */
    public ZStudent selectExcel(String userName) throws IOException, BiffException {
        List<ZStudent> students = getExcelList();       //获得全部列表
        int index = 0;  //寻找坐标
        for(ZStudent student : students){   //循环比较
            if(userName.equals(student.getUserName())){     //查询
                break;  //查询到了则找到坐标
            }
            index++;
        }
        return students.get(index); //根据坐标
    }

    /**
     * 模糊查询
     * @param userName
     * @return
     */
    public List<ZStudent> selectListByUserName(String userName) throws IOException, BiffException {
        List<ZStudent> students = getExcelList();       //获得全部列表
        List<ZStudent> result = new ArrayList<ZStudent>();  //用来存储
        for(ZStudent student : students){   //循环比较
            if(stringUtilService.matchStringByIndexOf(student.getUserName(),userName)){     //查询
                result.add(student);
            }
        }
        return result;
    }

    /**
     * 删除行
     * @param userName
     */
    public void del(String userName) throws IOException, BiffException {
        List<ZStudent> students = getExcelList();       //获得全部列表
        int index = 0;  //寻找坐标
        for(ZStudent student : students){   //循环比较
            if(userName.equals(student.getUserName())){     //查询
                break;  //查询到了则找到坐标
            }
            index++;
        }
        FileInputStream fileInputStream;
        try {
            fileInputStream = new FileInputStream(fileName);
            HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
            HSSFSheet sheet=workbook.getSheetAt(0);
            HSSFRow row = sheet.getRow(index + 1);      //找到坐标
            sheet.removeRow(row);
            FileOutputStream os = new FileOutputStream(fileName);
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 修改
     * @param userName
     * @param student
     * @throws IOException
     * @throws BiffException
     */
    public void updateExcel(String userName,ZStudent student) throws IOException, BiffException {
        List<ZStudent> studentList = getExcelList();       //获得全部列表
        int index = 0;  //寻找坐标
        for(ZStudent stu : studentList){   //循环比较
            if(userName.equals(stu.getUserName())){     //查询
                break;  //查询到了则找到坐标
            }
            index++;
        }
        FileInputStream fileInputStream;
        try {
            fileInputStream = new FileInputStream(fileName);
            POIFSFileSystem poifsFileSystem=new POIFSFileSystem(fileInputStream);  //使用POI提供的方法得到excel的信息
            HSSFWorkbook workbook=new HSSFWorkbook(poifsFileSystem);
            HSSFSheet sheet=workbook.getSheetAt(0);  //获取到工作表
            FileOutputStream outputStream=new FileOutputStream(fileName);
            HSSFRow row=sheet.createRow((short)(index + 1)); //此时这里即是行号
            //按顺序依次添加
            row.createCell(0).setCellValue(student.getUserName());
            row.createCell(1).setCellValue(student.getAge());
            row.createCell(2).setCellValue(student.getSex());
            row.createCell(3).setCellValue(student.getScore());
            outputStream.flush();
            workbook.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取操作
     * @throws IOException
     * @throws BiffException
     */
    public List<ZStudent> getExcelList() throws IOException, BiffException {
        List<ZStudent> ZStudentList = new ArrayList<ZStudent>();                                //创建列表
        File xlsFile = new File(fileName);                       //文件目录
        Workbook workbook = Workbook.getWorkbook(xlsFile);                                   //获得工作簿对象
        Sheet sheet = workbook.getSheet(0);                                            //获得工作表
        int rows = sheet.getRows();                                                          //获得行
        int cols = sheet.getColumns();                                                       //获得列
        int index = 0;
        for(int i = 1; i < rows; i++){                                                       //读取数据
            index = 0;
            ZStudent ZStudent = new ZStudent();
            ZStudent.setUserName(sheet.getCell(index,i).getContents());
            index = getIndex(index,cols);                                                   //每次添加完都调用一次
            ZStudent.setAge(sheet.getCell(index,i).getContents());
            index = getIndex(index,cols);
            ZStudent.setSex(sheet.getCell(index,i).getContents());
            index = getIndex(index,cols);
            ZStudent.setScore(sheet.getCell(index,i).getContents());
            ZStudentList.add(ZStudent);       //保存到列表中最后用来返回
        }
        workbook.close();
        return ZStudentList;
    }

    /**
     * 循环时定义坐标，防止坐标越界 用于 辅助 getExcelList()
     * @param index
     * @param col
     * @return
     */
    private Integer getIndex(int index, int col){
        index++;
        if(index > col){
            index = col;
        }
        return index;
    }

    public static void main(String[] args) throws Exception {
        ExcelService excelService = new ExcelServiceImpl();
        //读取测试
        List<ZStudent> zStudentList = excelService.getExcelList();
        for(ZStudent student : zStudentList){
            System.out.println(student.toString());
        }
        //新增测试
        ZStudent zStudent = new ZStudent("刘备","22","男","500");
        excelService.writeExcel(zStudent);
        //查询测试
        ZStudent A = excelService.selectExcel("李四");
        System.out.println("查询测试 : " + A);
        //模糊查询测试
        List<ZStudent> B = excelService.selectListByUserName("四");
        System.out.println(B.toString());
        //修改测试
        ZStudent C = new ZStudent("李四","1","女","2");
        excelService.updateExcel("李四",C);
        //删除测试
        excelService.del("李四");
    }
}