3 controller层实现导出Excel表格
@RequestMapping("/importExc")
@ResponseBody
public String importExc(HttpSession session){
//标记是否验证成功
boolean flag=false;
//创建一个核心对象
HSSFWorkbook hwb=new HSSFWorkbook();
//新建一个Excel表格,括号内是表格名称
HSSFSheet sheet = hwb.createSheet("第一张表");
//设置每一列的宽度
/* sheet.setColumnWidth("0",2000);*/
//创建第一行
HSSFRow hrow = sheet.createRow(0);
//创建他的列
HSSFCell [] hssfCells=new HSSFCell[8];
//遍历列
for (int i=0;i<hssfCells.length;i++){
hssfCells [i]=hrow.createCell(i);
}
//给第一行的列进行赋值
hssfCells[0].setCellValue("序号");
hssfCells[1].setCellValue("功能名称");
hssfCells[2].setCellValue("模块名称");
hssfCells[3].setCellValue("需求名称");
hssfCells[4].setCellValue("项目名称");
hssfCells[5].setCellValue("优先级");
hssfCells[6].setCellValue("添加时间");
hssfCells[7].setCellValue("修改时间");
//得到域对象中的集合
List<Function> functionList = (List<Function>) session.getAttribute("functionList");
for (int i=0; i<functionList.size() ;i++){
Function function=functionList.get(i);
HSSFRow datarow=sheet.createRow(i+1);
HSSFCell [] datacells =new HSSFCell[8];
//对列遍历
for(int j=0;j<datacells.length;j++){
datacells[j]=datarow.createCell(j);
//进行赋值
}
datacells[0].setCellValue(function.getId());
datacells[1].setCellValue(function.getFunctionname());
datacells[2].setCellValue(function.getModname());
datacells[3].setCellValue(function.getAnalysisname());
datacells[4].setCellValue(function.getProname());
datacells[5].setCellValue(function.getLevel());
datacells[6].setCellValue(function.getStarttime());
datacells[7].setCellValue(function.getUpdatetime());
HSSFCellStyle cellStyle = hwb.createCellStyle();
HSSFDataFormat daraformat= hwb.createDataFormat();
cellStyle.setDataFormat(daraformat.getFormat("yyyy年m月d日"));
datacells[6].setCellStyle(cellStyle);
datacells[7].setCellStyle(cellStyle);
}
try {
File file =new File("d:\\hos.xls");
FileOutputStream fileOutputStream=new FileOutputStream(file);
hwb.write(fileOutputStream);
flag=true;
} catch (IOException e) {
e.printStackTrace();
}
if (flag){
return "1";
}
return "2";
}
4 将xml文件导入数据库
package com.offcn.controller;
import com.offcn.entity.Forum;
import com.offcn.service.ForumService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import javax.annotation.Resource;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
@Controller
public class ForumController {
@Resource
private ForumService forumService;
@RequestMapping("doForm")
@ResponseBody
public String doForm(){
boolean flag=true;
List<Forum> forumList=new ArrayList<>();
try {
FileInputStream fid=new FileInputStream("D:\\up\\data.xls");
HSSFWorkbook hwb=new HSSFWorkbook(fid);
for (int i=0;i<hwb.getNumberOfSheets();i++){
//拿到工作表
HSSFSheet hssfSheet=hwb.getSheetAt(i);
if (hssfSheet==null){
continue;
}
for (int j=1;j<=hssfSheet.getLastRowNum();j++){
//获取除第一行以外的所有数据
HSSFRow row = hssfSheet.getRow(j);
/* HSSFCell cell = row.getCell(j);*/
if (row!=null){
HSSFCell hsstitle = row.getCell(0);
HSSFCell celldate = row.getCell(1);
HSSFCell cellurl= row.getCell(2);
Forum forum=new Forum();
forum.setFdate(celldate.getDateCellValue());
forum.setFtitle(hsstitle.getStringCellValue());
forum.setFurl(cellurl.getStringCellValue());
forumList.add(forum);
}
}
}
} catch (IOException e) {
flag=false;
e.printStackTrace();
}
if (flag){
int num = forumService.addForum(forumList);
System.out.println(forumList.size());
if (num>0) {
return "1";
}
}
return "2";
}
}