controller层实现导出Excel表格

 

 

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";
    }
}