Mysql和Excle的交互
一、研究背景
在Web应用系统中，用户们经常要求将数据库数据进行导入导出处理。Microsoft Excel是用户非常熟悉的办公软件，因此，Excel作为数据库数据导入导出的中间介质成为最佳选择。为了达到此目的，采用Java Excel API操作Excel文件的方法，实现了仅通过在客户端的操作，将数据库中的数据导出到Excel表格中，并将Excel表格中的数据导入数据库的功能。满足了用户进行数据库数据导入导出的要求。
我们做的系统“毕设管理系统”需要导入学生信息、教师信息等。需要实现将数据库进行导入和导出，转换为Excle表等。
二、OPI的选择及介绍
java在操作excel中提供了我常见的api第一个就是POI、JXI。而JXL是由韩国人写出的，功能相对POI没有强大，因此我们选择使用POI。
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程式对Microsoft Office格式档案读/写功能。该项目分为几个组件，其中包括一个HSSF的组件，它是一个非常正规和严谨的API。利用HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。
HSSF提供给用户使用的对象在org.apache.poi.hssf.usermodel包中,主要部分包括Excell对象，样式和格式，还有辅助操作。有以下几种对象：
HSSFWorkbook excell的文档对象
HSSFSheet excell的表单
HSSFRow excell的行
HSSFCell excell的格子单元
HSSFFont excell字体
HSSFName 名称
HSSFDataFormat 日期格式
在poi1.7中才有以下2项：
HSSFHeader sheet头
HSSFFooter sheet尾
和这个样式
HSSFCellStyle cell样式
辅助操作包括
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表
仔细看org.apache.poi.hssf包的结构，不难发现HSSF的内部实现遵循的是MVC模型。
HSSFWorkbook即是一个Excell对象。这幅类图体现的是HSSFWorkbook和基本对象的相互关系。可见，许多对象中也建立了Workbook的引用。还需要注意的是在HSSFWorkbook和HSSFSheet中建立了log机制POILogger，而且POILogger也是使用apache Log4J实现的。
三、具体实现过程及代码
<!--[if !supportLists]-->1、 <!--[endif]-->拷贝jar包
添加POI jar包(18个)到项目的lib目录下
jxl.jar
poi-2.5.1-final-20040804.jar
poi-3.0-rc4-20070503.jar
poi-3.0.1-FINAL-20070705.jar
poi-3.9-20121203.jar
poi-contrib-2.5.1-final-20040804.jar
poi-contrib-3.0-rc4-20070503.jar
poi-contrib-3.0.1-FINAL-20070705.jar
poi-examples-3.9-20121203.jar
poi-excelant-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar
poi-scratchpad-2.5.1-final-20040804.jar
poi-scratchpad-3.0-rc4-20070503.jar
poi-scratchpad-3.0.1-FINAL-20070705.jar
poi-scratchpad-3.9-20121203.jar
xmlbeans-2.3.0.jar
<!--[if !supportLists]-->2、 <!--[endif]-->创建model对象，利用Hibernate创建相应的表结构。
Event.java
Event.hbm.xml
在Hibernate.cfg.xml中配置，
<mapping resource="sjzc/edu/model/Event.hbm.xml"/>
3、代码实现
①将Mysql中的数据写入Excle表格
public boolean readCheckCodeToExcelFile(List<Event> list, String url) {
int i1 = 0;
int i2 = 0;
int i3 = 0;
int i4 = 0;
HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel工作簿对象
HSSFSheet sheet = workbook.createSheet();// 在工作簿中创建工作表对象
//sheet.setColumnWidth((short) 5, (short) (256 * 12));
//sheet.setColumnWidth((short) 4, (short) (256 * 14));
sheet.setDefaultColumnWidth((short) 20);
workbook.setSheetName(0, "毕设计划");// 设置工作表的名称
HSSFRow row1 = sheet.createRow(0);// 在工作表中创建行对象
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 4));// 合并第1行的第1个到第5个之间的单元格
HSSFFont font = workbook.createFont();// 创建字体对象
font.setColor(HSSFColor.SKY_BLUE.index);// 设置字体颜色
font.setFontHeightInPoints((short) 14);// 设置字号
font.setFontName("楷体");// 设置字体样式
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFont(font);
// 设置水平居中
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
// 设置垂直居中
titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 设置水平居中
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
// 设置垂直居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 创建字体对象
HSSFFont font2 = workbook.createFont();
// 设置字体颜色
font2.setColor(HSSFColor.BLACK.index);
// 设置字号
font2.setFontHeightInPoints((short) 14);
// 设置字体样式
font2.setFontName("楷体");
cellStyle.setFont(font2);
HSSFCellStyle cellStyle2 = workbook.createCellStyle();
// 设置水平居中
cellStyle2.setAlignment(CellStyle.ALIGN_CENTER);
// 设置垂直居中
cellStyle2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
HSSFCell titleCell = row1.createCell((short) 0);
titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
titleCell.setCellValue("毕设计划表");
titleCell.setCellStyle(titleStyle);
HSSFRow row2 = sheet.createRow(1);
// 在第1行中创建单元格对象
HSSFCell stageCell = row2.createCell((short) 0); stageCell.setEncoding(HSSFCell.ENCODING_UTF_16);
stageCell.setCellValue("阶段");
stageCell.setCellStyle(cellStyle);
// 在行中创建单元格对象
HSSFCell textCell = row2.createCell((short) 1); textCell.setEncoding(HSSFCell.ENCODING_UTF_16);
textCell.setCellValue("工作内容");
textCell.setCellStyle(cellStyle);
// 在行中创建单元格对象
HSSFCell startDateCell = row2.createCell((short) 2);tartDateCell.setEncoding(HSSFCell.ENCODING_UTF_16);
startDateCell.setCellValue("开始时间");
startDateCell.setCellStyle(cellStyle);
// 在行中创建单元格对象
HSSFCell endDateCell = row2.createCell((short) 3); endDateCell.setEncoding(HSSFCell.ENCODING_UTF_16);
endDateCell.setCellValue("结束时间");
endDateCell.setCellStyle(cellStyle);
// 在行中创建单元格对象
HSSFCell directorCell = row2.createCell((short) 4); directorCell.setEncoding(HSSFCell.ENCODING_UTF_16);
directorCell.setCellValue("负责人");
directorCell.setCellStyle(cellStyle);
//遍历Event将属于“毕设准备阶段”的记录数赋值给i1
for (int i = 0; i < list.size(); i++) {
Event event= (Event) list.get(i);
String stagei = event.getStage();
if("1".equals(stagei)){
i1 = i1+1;
}
}
//遍历Event将属于“毕设准备阶段”、“本科毕设阶段”的记录数赋值给i2
i2 = i1;
for (int i = 0; i < list.size(); i++) {
Event event= (Event) list.get(i);
String stagei = event.getStage();
if("2".equals(stagei)){
i2 = i2+1;
}
}
//遍历Event将属于“毕设准备阶段”、“本科毕设阶段”、“毕设评阅答辩阶段”的记录数赋值给i3
i3 = i2;
for (int i = 0; i < list.size(); i++) {
Event event= (Event) list.get(i);
String stagei = event.getStage();
if("3".equals(stagei)){
i3 = i3+1;
}
}
// 遍历保存数据对象的集合
for (int i = 0; i < list.size(); i++) {
// 获取封装数据的对象
Event event= (Event) list.get(i);
// 创建行
HSSFRow dataRow = sheet.createRow(i + 2);
// 创建单元格
HSSFCell stageName = dataRow.createCell((short) 0);stageName.setEncoding(HSSFCell.ENCODING_UTF_16);
String stagei = event.getStage();
// 将数据添加到单元格中
if("1".equals(stagei)){
stageName.setCellValue("毕设准备阶段");
}else if("2".equals(stagei)){
stageName.setCellValue("本科毕设阶段");
}else if("3".equals(stagei)){
stageName.setCellValue("毕设评阅答辩阶段");
}else{
stageName.setCellValue("毕设总结阶段");
}
//合并单元格
sheet.addMergedRegion(new Region(2, (short) 0, i1+1, (short) 0));
sheet.addMergedRegion(new Region(i1+2, (short) 0, i2+1, (short) 0));
sheet.addMergedRegion(new Region(i2+2, (short) 0, i3+1, (short) 0));
//将总记录数赋值给i4
i4 = list.size()+1;
sheet.addMergedRegion(new Region(i3+2, (short) 0, i4, (short) 0));
stageName.setCellStyle(cellStyle2);
HSSFCell textName = dataRow.createCell((short) 1);
textName.setEncoding(HSSFCell.ENCODING_UTF_16);
textName.setCellValue(event.getText());
textName.setCellStyle(cellStyle2);
HSSFCell startDate = dataRow.createCell((short) 2);
startDate.setEncoding(HSSFCell.ENCODING_UTF_16);
String fStartDate = new SimpleDateFormat("yyyy-MM-dd HH:mm").format(event.getStartDate());
startDate.setCellValue(fStartDate);
startDate.setCellStyle(cellStyle2);
HSSFCell endDate = dataRow.createCell((short) 3);
endDate.setEncoding(HSSFCell.ENCODING_UTF_16);
//将从数据库中取出来的日期类型的数据格式化
String fEndDate = new SimpleDateFormat("yyyy-MM-dd HH:mm").format(event.getEndDate());
endDate.setCellValue(fEndDate);
endDate.setCellStyle(cellStyle2);
HSSFCell director = dataRow.createCell((short) 4);
director.setEncoding(HSSFCell.ENCODING_UTF_16);
director.setCellValue(event.getDirector());
director.setCellStyle(cellStyle2);
}
//新建File
File xlsFile = new File(ServletActionContext.getServletContext().getRealPath(url + "/毕设计划表.xls"));
try {
FileOutputStream fos = new FileOutputStream(xlsFile);
//写入工作簿
workbook.write(fos);
fos.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}// 将文档对象写入文件输出流
return true;
}
②将Excle中的数据导入Mysql数据库（相应的表结构已通过Hibernate创建）
public void ExcelToMysqlOfData(String url) {
File file = new File(url);
String[][] result;
try {
result = getData(file, 1, 0);
int rowLength = result.length;
for (int i = 0; i < rowLength; i++) {
//new 一个Student对象
Student student = new Student();
//将从表中的字段值设置Student的属性
student.setSno(result[i][0]);
student.setSname(result[i][1]);
//将Student对象保存到数据库t_student表中
studentDao.saveOrUpdate(student);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public static String[][] getData(File file, int ignoreRows, int ignoreCols)
throws FileNotFoundException, IOException {
List<String[]> result = new ArrayList<String[]>();
int rowSize = 0;
BufferedInputStream in = new BufferedInputStream(new FileInputStream(
file));
// 打开HSSFWorkbook
POIFSFileSystem fs = new POIFSFileSystem(in);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFCell cell = null;
for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
HSSFSheet st = wb.getSheetAt(sheetIndex);
// 第一行为标题，不取
for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
HSSFRow row = st.getRow(rowIndex);
if (row == null) {
continue;
}
int tempRowSize = row.getLastCellNum() + 1;
if (tempRowSize > rowSize) {
rowSize = tempRowSize;
}
String[] values = new String[rowSize];
Arrays.fill(values, "");
boolean hasValue = false;
for (short columnIndex = (short) ignoreCols; columnIndex <= row.getLastCellNum(); columnIndex++) {
String value = "";
cell = row.getCell(columnIndex);
if (cell != null) {
// 注意：一定要设成这个，否则可能会出现乱码
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd")
.format(date);
} else {
value = "";
}
} else {
value = new DecimalFormat("0").format(cell
.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_FORMULA:
// 导入时如果为公式生成的数据则无值
if (!cell.getStringCellValue().equals("")) {
value = cell.getStringCellValue();
} else {
value = cell.getNumericCellValue() + "";
}
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y"
: "N");
break;
default:
value = "";
}
}
if (columnIndex == 0 && value.trim().equals("")) {
break;
}
values[columnIndex] = rightTrim(value);
hasValue = true;
}
if (hasValue) {
result.add(values);
}
}
}
in.close();
String[][] returnArray = new String[result.size()][rowSize];
for (int i = 0; i < returnArray.length; i++) {
returnArray[i] = (String[]) result.get(i);
}
return returnArray;
}
public static String rightTrim(String str) {
if (str == null) {
return "";
}
int length = str.length();
for (int i = length - 1; i >= 0; i--) {
if (str.charAt(i) != 0x20) {
break;
}
length--;
}
return str.substring(0, length);
}
<!--[if !supportLists]-->3、 <!--[endif]-->Mysql中的表结构t_event,如图1
图1
生成的相应的Excle表如图2
图2
<!--[if !supportLists]-->4、 <!--[endif]-->Excle的表结构如图3
图3
生成的Mysql数据库中对应的表结构如图4
图4
***（详细文档请看附件）
<!--EndFragment-->
相关推荐
在毕业设计管理系统中，Excel被用来设计底层数据库，负责存储和管理大量的文档数据，如教师评价、学生记录、设计任务等。 系统设计总体上分为六个主要模块：登录模块、设计任务发布模块、指导记录模块、论文评阅...
本毕设旨在开发一款基于C语言的产品管理系统，该系统可以帮助企业对其产品进行有效的管理和监控。本系统将包括以下核心功能： ## 1. 产品信息管理 该功能将允许企业管理员添加、查看、更新和删除产品信息。管理员...
本项目是“基于Java POI的Excel模拟数据库系统”与“基于Python WxPython的Excel模拟数据库”的结合，适用于计算机科学与技术专业学生的毕业设计。标题中的"Java POI"指的是Java的一个库，用于读写Microsoft Office...
"毕业设计选题管理系统"是一个专为高校设计的...总结，"毕业设计选题管理系统"是教育信息化的重要组成部分，通过合理的设计和实现，能够有效提高毕业设计管理的效率，减轻教务工作负担，同时为学生提供便捷的选题平台。
JSP 毕业设计选题管理系统是一套完善的web设计系统，对理解JSP java编程开发语言有帮助，系统具有完整的源代码和数据库,开发环境为TOMCAT7.0,Myeclipse8.5开发，数据库为 Mysql5.0，使用java语言开发，系统主要...
基于python的毕业设计教学质量评价系统源码+数据库.zip基于python的毕业设计教学质量评价系统源码+数据库.zip角色功能说明管理员管理学生管理教师管理课程管理选课管理评价题查看成绩excel批量上传修改密码学生评价...
基于springboot+vue的智慧小区管理系统源码+数据库（毕业设计）.zip基于springboot+vue的智慧小区管理系统源码+数据库（毕业设计）.zip基于springboot+vue的智慧小区管理系统源码+数据库（毕业设计）.zip基于...
基于springboot+vue的智慧小区管理系统源码+数据库（毕业设计）.zip基于springboot+vue的智慧小区管理系统源码+数据库（毕业设计）.zip基于springboot+vue的智慧小区管理系统源码+数据库（毕业设计）.zip基于...
毕业设计，基于VB+ACCESS开发的采购管理系统，内含完整源代码，数据库，论文答辩，毕业论文 采购管理是管理部门日常工作中一项十分重要的基础管理工作。利用数据库对物资计划及合同进行管理是十分有效且广泛应用的...
毕业设计，基于VB+ACCESS开发的电大图书管理系统，内含完整源代码，数据库，毕业论文 图书管理系统是典型的信息管理系统,其开发主要包括后台数据库的建立和维护以及前端应用程序的开发两个方面。对于前者要求建立起...
毕业设计基于SSM的健康管理系统源码+数据库（高分项目）.zip该项目是个人高分毕业设计项目源码，已获导师指导认可通过，都经过严格调试，确保可以运行！放心下载使用。项目介绍 本项目包含健康管理公司后台管理系统...
毕业设计，基于VB+ACCESS开发的仓库库存管理系统，内含完整源代码，数据库，毕业论文 仓库库存管理是管理部门日常工作中一项十分重要的基础管理工作。利用数据库对物资计划及合同进行管理是十分有效且广泛应用的一...
基于springboot+vue前后端分离的智慧酒店管理系统源码+数据库+使用说明（毕业设计）.zip酒店管理系统，基于Spring Boot框架。该系统有门户（Vue实现）后台管理端（Vue实现）和app（uniapp实现）。拥有酒店详情页、...
《毕业设计之人力资源管理系统》是针对企业日常运营中不可或缺的人力资源管理环节而开发的一款软件系统。该系统旨在提升人力资源管理的效率，优化工作流程，为企业的决策提供数据支持。在这一毕业设计中，学生将深入...
毕业设计基于Springboot+Vue的小区物业管理系统源码+数据库 实现物业管理公司的业务工作流程，包括：小区管理、楼盘管理、物业管理、用户管理、收费管理、系统管理。物业管理人员通过该系统实现在线物业管理工作，...
5. "数据库-大学生实习实训管理系统.xls"：可能是数据库的原型设计，使用Excel来预览和规划表结构、字段和数据类型。 6. "Stutrain"：这可能是一个包含源代码和资源文件的项目目录，其中的代码实现了系统的各种功能...
这个毕业设计选择了SQL Server 2000作为数据库管理系统，VB（Visual Basic）作为前端开发工具，这在20世纪末至21世纪初是一个常见的技术组合。 SQL Server 2000是微软推出的关系型数据库管理系统，具有稳定性和可...
毕业设计，基于Java+MySQL开发的教师工资管理系统，内含完整源码，数据库脚本，毕业论文，论文答辩 随着科技的迅速发展，各种管理系统已应用到社会的各个领域。高等院校作为科技发展的前沿阵地，实现对教师工资的...