毕业设计管理系统之二Excel和数据库之间互存
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-->