`

毕业设计管理系统之二Excel和数据库之间互存

阅读更多

MysqlExcle的交互

一、研究背景

在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-->

 

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics