poi上传下载

本教程只实现poi简单的上传下载功能，如需高级操作请绕行!

		<!--poi start-->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.15</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.15</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.15</version>
		</dependency>

		<!--poi end-->

　　resources目录下准备一个提供下载的文件：题目批量上传Excel.xls

poi工具类：

package com.rm.framework.util;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
/**
 * excel读写工具类
 */
public class POIUtil {

        private static Logger logger  = Logger.getLogger(POIUtil.class);
        private final static String xls = "xls";
        private final static String xlsx = "xlsx";

        /**
         * 读入excel文件，解析后返回
         * @param file
         * @throws IOException
         */
        public static List<String[]> readExcel(MultipartFile file) throws IOException{
            //检查文件
            checkFile(file);
            //获得Workbook工作薄对象
            Workbook workbook = getWorkBook(file);
            //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
            List<String[]> list = new ArrayList<String[]>();
            if(workbook != null){
                for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
                    //获得当前sheet工作表
                    Sheet sheet = workbook.getSheetAt(sheetNum);
                    if(sheet == null){
                        continue;
                    }
                    //获得当前sheet的开始行
                    int firstRowNum  = sheet.getFirstRowNum();
                    //获得当前sheet的结束行
                    int lastRowNum = sheet.getLastRowNum();
                    //循环除了第一行的所有行
                    for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
                        //获得当前行
                        Row row = sheet.getRow(rowNum);
                        if(row == null){
                            continue;
                        }
                        //获得当前行的开始列
                        int firstCellNum = row.getFirstCellNum();
                        //获得当前行的列数
                        int lastCellNum = row.getPhysicalNumberOfCells();
                        String[] cells = new String[row.getPhysicalNumberOfCells()];
                        //循环当前行
                        StringBuffer sb = new StringBuffer();
                        for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
                            Cell cell = row.getCell(cellNum);
                            cells[cellNum] = getCellValue(cell);
                            sb.append(cells[cellNum]);
                        }
                        int length = sb.toString().length();
                        if(length>5){
                            list.add(cells);
                        }else{
                                  sb.delete(0, sb.length());

                        }
                    }
                }
               // workbook.close();
            }
            return list;
        }
        public static void checkFile(MultipartFile file) throws IOException{
            //判断文件是否存在
            if(null == file){
                logger.error("文件不存在！");
                throw new FileNotFoundException("文件不存在！");
            }
            //获得文件名
            String fileName = file.getOriginalFilename();
            //判断文件是否是excel文件
            if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
                logger.error(fileName + "不是excel文件");
                throw new IOException(fileName + "不是excel文件");
            }
        }
        public static Workbook getWorkBook(MultipartFile file) {
            //获得文件名
            String fileName = file.getOriginalFilename();
            //创建Workbook工作薄对象，表示整个excel
            Workbook workbook = null;
            try {
                //获取excel文件的io流
                InputStream is = file.getInputStream();
                //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
                if(fileName.endsWith(xls)){
                    //2003
                    workbook = new HSSFWorkbook(is);
                }else if(fileName.endsWith(xlsx)){
                    //2007 及2007以上
                    workbook = new XSSFWorkbook(is);
                }
            } catch (IOException e) {
                logger.info(e.getMessage());
            }
            return workbook;
        }
        public static String getCellValue(Cell cell){
            String cellValue = "";
            if(cell == null){
                return cellValue;
            }
            //把数字当成String来读，避免出现1读成1.0的情况
            if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
                cell.setCellType(Cell.CELL_TYPE_STRING);
            }
            //判断数据的类型
            switch (cell.getCellType()){
                case Cell.CELL_TYPE_NUMERIC: //数字
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_STRING: //字符串
                    cellValue = String.valueOf(cell.getStringCellValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN: //Boolean
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA: //公式
                    cellValue = String.valueOf(cell.getCellFormula());
                    break;
                case Cell.CELL_TYPE_BLANK: //空值
                    cellValue = "";
                    break;
                case Cell.CELL_TYPE_ERROR: //故障
                    cellValue = "非法字符";
                    break;
                default:
                    cellValue = "未知类型";
                    break;
            }
            return cellValue;
        }
    }

　　controller：

package com.rm.eval.controller;
import java.util.Date;

import com.rm.eval.entity.EvalQuestion;
import com.rm.eval.service.EvalQnQuestionService;
import com.rm.framework.controller.BaseController;
import com.rm.framework.util.CUID;
import com.rm.framework.util.POIUtil;
import com.rm.sys.dao.SysUserDao;
import com.rm.sys.entity.SysUser;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;

@RestController
@RequestMapping(value = "/excel")
public class ExcelController  extends BaseController {
    @Autowired
private  SysUserDao sysUserDao;

    //创建表头
    private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet){
        HSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(1,12*256);
        sheet.setColumnWidth(3,17*256);

        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setFont(font);

        HSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("题目内容");
        cell.setCellStyle(style);
//题目内容，类型，难度，维度，状态，备注

        cell = row.createCell(1);
        cell.setCellValue("类型");
        cell.setCellStyle(style);

        cell = row.createCell(2);
        cell.setCellValue("难度");
        cell.setCellStyle(style);

        cell = row.createCell(3);
        cell.setCellValue("维度");
        cell.setCellStyle(style);

        cell = row.createCell(4);
        cell.setCellValue("状态");
        cell.setCellStyle(style);

        cell = row.createCell(5);
        cell.setCellValue("备注");
        cell.setCellStyle(style);
    }

    //生成user表excel
    @GetMapping(value = "/getUser")
    public String getUser(HttpServletResponse response) throws Exception{
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("题目批量上传");
        createTitle(workbook,sheet);
       // List<SysUser> rows = sysUserService.getAll();
        List<SysUser> rows = sysUserDao.findAll();
        //设置日期格式
        HSSFCellStyle style = workbook.createCellStyle();
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        //新增数据行，并且设置单元格数据


        String fileName = "题目批量上传Excel.xls";

        //生成excel文件
        buildExcelFile(fileName, workbook);

        //浏览器下载excel
        buildExcelDocument(fileName,workbook,response);

        return "download excel";
    }

    //生成excel文件
    protected void buildExcelFile(String filename,HSSFWorkbook workbook) throws Exception{
        FileOutputStream fos = new FileOutputStream(filename);
        workbook.write(fos);
        fos.flush();
        fos.close();
    }

    //浏览器下载excel
    protected void buildExcelDocument(String filename,HSSFWorkbook workbook,HttpServletResponse response) throws Exception{
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename, "utf-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }
    /**
     * 文件下载
     */
    @ResponseBody
    @GetMapping(value = "/download")
    public ResponseEntity<byte[]> download(HttpSession session) {
        System.out.println("das");
        ResponseEntity<byte[]> response = null;
        try {
            byte[] body = null;
            InputStream in=ExcelController.class.getClassLoader().getResourceAsStream("题目批量上传Excel.xls");

            body = new byte[in.available()];
            in.read(body);
            HttpHeaders headers = new HttpHeaders();
            String fileName11 = new String("题目批量上传Excel".getBytes("GB2312"), "ISO_8859_1");
            headers.add("Content-Disposition", "attachment;filename=" + fileName11 + ".xls");
            HttpStatus statusCode = HttpStatus.OK;
            response = new ResponseEntity<byte[]>(body, headers, statusCode);

            return response;
        } catch (IOException e) {
            e.printStackTrace();
        }
return response;
    }
    @Autowired
  private com.rm.eval.service.EvalQuestionService evalQuestionService;
    /**
     * 文件上传
     * @param file
     * @return
     */
    @ResponseBody
    @RequestMapping(value = "/UploadFile")
    public String UploadFile(@RequestParam(required=false) MultipartFile file ){
        String flag ="0";
        try{
            List<String[]> list = POIUtil.readExcel(file); //这里得到的是一个集合，里面的每一个元素是String[]数组
           //TODO service实现方法
            System.out.println(list);
            for (int i=0;i<list.size();i++){
                EvalQuestion eval = new EvalQuestion();

                eval.setTitle(list.get(i)[0]);//题目
          String type= list.get(i)[1];
                eval.setType(GetAndSetType(type));//类型
             String   diffcult=   list.get(i)[2];
                eval.setDiffcult(GetAndSetDiffcult(diffcult));//难度
                eval.setWay(list.get(i)[3]);
                eval.setStatus("1");
                eval.setSort(0);
                eval.setRemark(list.get(i)[4]);
                eval.setValidateFlag("Y");
                eval.setCreateManId(getCurrentUser().getId());
                eval.setCreateMan(getCurrentUser().getName());
                eval.setCreateDate(new Date());
                eval.setModify_date(new Date());
                eval.setCom(getUserCom());
                eval.setId(CUID.createUUID("EvalQuestion"));
                evalQuestionService.save(eval);
            }

        } catch(Exception e){
            flag = "1";
        }
        return flag;
    }

    private String GetAndSetDiffcult(String type) {
        switch (type){
            case "简单":
                type="A";
                break;
            case "容易":
                type="B";
                break;
            case "困难":
                type="C";
                break;
            case "艰难":
                type="D";
                break;
        }
        return  type;
    }

    private String  GetAndSetType(String type){
    switch (type){
        case "单选题":
            type="A";
            break;
        case "多选题":
            type="B";
            break;
        case "排序题":
            type="C";
            break;
        case "程度题":
            type="D";
            break;
        case "问答题":
            type="E";
            break;
        case "矩阵程度题":
            type="F";
            break;
        case "拽选择题":
            type="G";
            break;
    }
    return  type;
}
}