POI实现图书馆管理系统的数据存储
目的:通过这个系统POI操作Excel表,实现了对其数据的增、删、改、查功能
系统功能描述:
备注: ①各项功能的详细操作流程参见压缩文件中视频,项目效果演示。
1:增加图书
添加一个图数信息(图书编号、名称、价格、类别、简介)
2:删除图书
允许管理员输入要删除的图书编号,然后完成图书的删除
3:修改图书信息
允许管理员输入要修改的部门的编号,然后输入该图书修改后的新名称、新价格、新类别、新简介。最后完成修改
4:查看所有图书信息
展示该系统中所有图书信息。
5:根据价格区间查找图书
管理员输入,最小价格,和最大价格,然后展示所有图书价格处于这个之间的所有图书信息。
6:根据类别查找图书
管理员输入类别名称,然后展示该类别的所有图书。
1、主函数
public class Library_main {
public static void main(String[] args) {
// 将数据存入excle表格中
Library a = new Library();
Scanner sc = new Scanner(System.in);
while (true) {
show();
System.out.println("输入您的选项:");
int i = sc.nextInt();
switch (i) {
case 1:
System.out.println("请输入书的编号:");
Integer id = sc.nextInt();
System.out.println("请输入书的名称:");
String name = sc.next();
System.out.println("请输入书的价格:");
Double price = sc.nextDouble();
System.out.println("请输入书的类别:");
String cla = sc.next();
System.out.println("请输入书的简介:");
String produce = sc.next();
Book book = new Book();
book.setBook_id(id);
book.setBook_title(name);
book.setBook_price(price);
book.setBook_produce(produce);
book.setBook_class(cla);
a.addBook(book);
break;
case 2:
System.out.println("请输入书的编号:");
Integer id1 = sc.nextInt();
a.removeBook(id1);
break;
case 3:
System.out.println("请输入要更新的书的编号:");
Integer id2 = sc.nextInt();
System.out.println("请输入编号为" + id2 + "的书的新的名字:");
String name2 = sc.next();
System.out.println("请输入编号为" + id2 + "的书的新的价格:");
Double price2 = sc.nextDouble();
System.out.println("请输入编号为" + id2 + "的书的新的类别:");
String class2 = sc.next();
System.out.println("请输入编号为" + id2 + "的书的新的简介:");
String product2 = sc.next();
a.updateBook(id2, name2, price2, class2, product2);
break;
case 4:
List<Book> li = a.getAllBooks();
for (Book book2 : li) {
System.out.println(book2);
}
break;
case 5:
System.out.println("输入最低价格:");
Double minPrice = sc.nextDouble();
System.out.println("输入最高价格:");
Double maxPrice = sc.nextDouble();
List<Book> li1 = a.getBooksByPriceRange(minPrice, maxPrice);
for (Book book2 : li1) {
System.out.println(book2);
}
break;
case 6:
System.out.println("请输入类别查找图书:");
String category = sc.next();
List<Book> li2 = a.getBooksByCategory(category);
for (Book book2 : li2) {
System.out.println(book2);
}
break;
case 7:
System.exit(0);
}
}
}
public static void show() {
System.out.println("1、添加一个图书信息(图书编号、名称、价格、类别、简介):");
System.out.println("2、根据编号删除图书:");
System.out.println("3、修改图书信息:");
System.out.println("4、查看所有图书信息:");
System.out.println("5、根据价格区间查找图书:");
System.out.println("6、根据类别查找图书:");
System.out.println("7、退出系统");
}
}
2、接口
package com.baizhi.test.project;
import java.util.List;
/**
* 图书管理系统功能标准(接口) 作者:sqc 时间:2018-4-3 下午21:43:41 描述:TODO
*/
public interface LibrarySystem {
/**
* 描述:TODO 将参数book对象添加到数据仓库类中
*/
public void addBook(Book book);
/**
* 描述:TODO 根据参数bookId在数据仓库中找到对应的图书,然后删除。
* 提示:1.遍历数据仓库中的books属性中所有的图书,找到图书编号和bookId相同的图书下标
* 2.在遍历的循环外面,通过list的remove(index)方法删除图书
*/
public void removeBook(int bookId);
/**
* 描述:TODO 根据bookId找到对应的图书,将图书的其余属性分别修改为参数中传递过来的值
*/
public void updateBook(int bookId, String newName, double newPrice,
String newCategory, String newDesc);
/**
* 描述:TODO 获得所有图书
*/
public List<Book> getAllBooks();
/**
* 描述:TODO 根据价格区间找到 minPrice<=price<=maxPrice的所有图书
*/
public List<Book> getBooksByPriceRange(double minPrice, double maxPrice);
/**
* 描述:TODO 根据类名查找所有的图书
*/
public List<Book> getBooksByCategory(String category);
}
3、实现接口
class Library implements LibrarySystem {
@Override
public void addBook(Book book) {
try{
File file = new File("0.xls");
if(!file.exists()){
file=Excel.createExcel(file);
}
FileInputStream fis = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(fis);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(0);//获取第一行(excel中的行默认从0开始,所以这就是为什么,一个excel必须有字段列头),即,字段列头,便于赋值
int lastRowNum = sheet.getLastRowNum();//得到最后一行的信息
System.out.println(lastRowNum);
FileOutputStream fos = new FileOutputStream(file);
row = sheet.createRow((short)(lastRowNum+1)); //现有行之后追加数据
for(int i=0;i<5;i++){
switch(i){
case 0:row.createCell(0).setCellValue(book.getBook_id());break;
case 1:row.createCell(1).setCellValue(book.getBook_title());break;
case 2:row.createCell(2).setCellValue(book.getBook_price());break;
case 3:row.createCell(3).setCellValue(book.getBook_class());break;
case 4:row.createCell(4).setCellValue(book.getBook_produce());break;
}
}
wb.write(fos);
fos.close();
}catch(Exception e){
e.printStackTrace();
}
}
@Override
public void removeBook(int bookId) {
try{
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("0.xls"));
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row ;
int lastRowNum = sheet.getLastRowNum();
//得到第二行的数据
int i = sheet.getFirstRowNum()+1;
//从第二行开始遍历,直至最后,因为第一行是表头
while(i<=lastRowNum){
//得到第i行
row = sheet.getRow(i);
//得到第i行的第一个单元格
int removeRow = (int) row.getCell(0).getNumericCellValue();
if(bookId == removeRow){
sheet.shiftRows(i, sheet.getLastRowNum(), -1);
sheet.removeRow(sheet.getRow(lastRowNum));//删除最后一个空行,避免下次存入数据有空行出现
FileOutputStream fos = new FileOutputStream("0.xls");
wb.write(fos);
fos.close();
System.out.println("数据删除完成!");
return ;
}
i++;
}
System.out.println("不存在这条数据!");
}catch(Exception e){
e.printStackTrace();
}
}
public void updateBook(int bookId, String newName, double newPrice,
String newCategory, String newDesc) {
try{
File file = new File("0.xls");
FileInputStream fis = new FileInputStream(file);
HSSFWorkbook wb = new HSSFWorkbook(fis);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
//得到第二行的行号
int firstRowNum = sheet.getFirstRowNum()+1;
//得到最后一行的行号
int lastRowNum = sheet.getLastRowNum();
while(firstRowNum<=lastRowNum){
//得到一行数据
row = sheet.getRow(firstRowNum);
//进行第一个单元格和bookId作比较
if((int)row.getCell(0).getNumericCellValue() == bookId){
row.getCell(1).setCellValue(newName);//设置firstRowNum的第二个单元格
row.getCell(2).setCellValue(newPrice);//设置firstRowNum的第三个单元格
row.getCell(3).setCellValue(newCategory);//设置firstRowNum的第四个单元格
row.getCell(4).setCellValue(newDesc);//设置firstRowNum的第五个单元格
FileOutputStream fos = new FileOutputStream(file);
wb.write(fos);
fos.close();
return;
}
firstRowNum++;
}
System.out.println("此条数据不存在!");
}catch(Exception e){
e.printStackTrace();
}
}
@Override
public List<Book> getAllBooks() {
List<Book> list = new ArrayList<Book>();
try{
FileInputStream fis = new FileInputStream(new File("0.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fis);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
int firstRowNum = sheet.getFirstRowNum()+1;
int lastRowNum = sheet.getLastRowNum();
System.out.println(firstRowNum+" "+lastRowNum);
while(firstRowNum<=lastRowNum){
System.out.println(firstRowNum+"------------->");
row = sheet.getRow(firstRowNum);
Book book = new Book();
int id= (int) row.getCell(0).getNumericCellValue();
String name= row.getCell(1).getStringCellValue();
double price = row.getCell(2).getNumericCellValue();
String newCategory = row.getCell(3).getStringCellValue();
String newDesc = row.getCell(4).getStringCellValue();
book.setBook_id(id);
book.setBook_title(name);
book.setBook_price(price);
book.setBook_class(newCategory);
book.setBook_produce(newDesc);
list.add(book);
firstRowNum++;
}
}catch(Exception e){
e.printStackTrace();
}
return list;
}
@Override
public List<Book> getBooksByPriceRange(double minPrice, double maxPrice) {
List<Book> list = new ArrayList<Book>();
try{
FileInputStream fis = new FileInputStream(new File("0.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fis);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
int firstRowNum = sheet.getFirstRowNum()+1;
int lastRowNum = sheet.getLastRowNum();
while(firstRowNum<=lastRowNum){
double price = sheet.getRow(firstRowNum).getCell(2).getNumericCellValue();
if(price>minPrice && price<maxPrice){
row = sheet.getRow(firstRowNum);
Book book = new Book();
int id= (int) row.getCell(0).getNumericCellValue();
String name= row.getCell(1).getStringCellValue();
String newCategory = row.getCell(3).getStringCellValue();
String newDesc = row.getCell(4).getStringCellValue();
book.setBook_id(id);
book.setBook_title(name);
book.setBook_price(price);
book.setBook_class(newCategory);
book.setBook_produce(newDesc);
list.add(book);
}
firstRowNum++;
}
}catch(Exception e){
e.printStackTrace();
}
return list;
}
@Override
public List<Book> getBooksByCategory(String category) {
List<Book> list = new ArrayList<Book>();
try{
FileInputStream fis = new FileInputStream(new File("0.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fis);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
int firstRowNum = sheet.getFirstRowNum()+1;
int lastRowNum = sheet.getLastRowNum();
while(firstRowNum<=lastRowNum){
String cg = sheet.getRow(firstRowNum).getCell(3).getStringCellValue();
if(category.equals(cg)){
row = sheet.getRow(firstRowNum);
Book book = new Book();
int id= (int) row.getCell(0).getNumericCellValue();
String name= row.getCell(1).getStringCellValue();
Double price = row.getCell(2).getNumericCellValue();
String newCategory = row.getCell(3).getStringCellValue();
String newDesc = row.getCell(4).getStringCellValue();
book.setBook_id(id);
book.setBook_title(name);
book.setBook_price(price);
book.setBook_class(newCategory);
book.setBook_produce(newDesc);
list.add(book);
}
firstRowNum++;
}
}catch(Exception e){
e.printStackTrace();
}
return list;
}
}
4、Book类
package com.baizhi.test.project;
public class Book {
private Integer book_id;
private String book_title;
private Double book_price;
private String book_class;
private String book_produce;
public Book(){}
public Book(Integer book_id, String book_title, Double book_price,
String book_class, String book_produce) {
super();
this.book_id = book_id;
this.book_title = book_title;
this.book_price = book_price;
this.book_class = book_class;
this.book_produce = book_produce;
}
public Integer getBook_id() {
return book_id;
}
public void setBook_id(Integer book_id) {
this.book_id = book_id;
}
public String getBook_title() {
return book_title;
}
public void setBook_title(String book_title) {
this.book_title = book_title;
}
public Double getBook_price() {
return book_price;
}
public void setBook_price(Double book_price) {
this.book_price = book_price;
}
public String getBook_class() {
return book_class;
}
public void setBook_class(String book_class) {
this.book_class = book_class;
}
public String getBook_produce() {
return book_produce;
}
public void setBook_produce(String book_produce) {
this.book_produce = book_produce;
}
@Override
public String toString() {
return "Book [book_id=" + book_id + ", book_title=" + book_title
+ ", book_price=" + book_price + ", book_class=" + book_class
+ ", book_produce=" + book_produce + "]";
}
}
5、辅助操作Excel表的类,功能未写完
class Excel {
//创建一个文件
public static File createExcel(File file) {
try {
FileOutputStream os = new FileOutputStream(file);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("信息");
HSSFRow row ;
HSSFCell cell ;
// 创建表头
row = sheet.createRow(0);
for (int i = 0; i < 5; i++) {
cell = row.createCell(i);
switch (i) {
case 0:cell.setCellValue("图书编号");break;
case 1:cell.setCellValue("名称");break;
case 2:cell.setCellValue("价格");break;
case 3:cell.setCellValue("类别");break;
case 4:cell.setCellValue("简介");break;
}
}
workbook.write(os);
os.close();
System.out.println("创建成功!");
} catch (Exception e) {
}
return file;
}
}