Java Mysql 导入导出 Excel

java 的 Excel 导入导出  （参照：慕课网  关于多版本问题）

（一）普通 Java 程序 (无多版本控制)

1-1 Excel 写入

package com.ssm.test;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.commons.io.FileUtils;
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;

public class PoiEcpExcel {

    public static void main(String[] args) {
　　　　// 设置第一行名
        String[] title = { "id", "name", "sex" };
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = null;
·　　　　// 写入第一行
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }
　　　　// 循环写入5 条
        for (int i = 1; i < 5; i++) {
            HSSFRow nextrow = sheet.createRow(i);
            HSSFCell cell2 = nextrow.createCell(0);
            cell2.setCellValue("a" + i);
            cell2 = nextrow.createCell(1);
            cell2.setCellValue("user" + i);
            cell2 = nextrow.createCell(2);
            cell2.setCellValue("男");
        }

        File file = new File("f:/poi_test.xls");
        try {
            file.createNewFile();
            FileOutputStream stream = FileUtils.openOutputStream(file);
            workbook.write(stream);
            stream.close(); 
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

1-2 Excel 读取

public static void main(String[] args) throws IOException {

        // 需要解析的 Excel 文件
        // File file = new File("f:/test.xlsx");
        File file = new File("f:/test.xlsx");

        Workbook workbook = null;
        String fileName = file.getName();
        if (fileName.endsWith("xls")) {
            workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
        } else if (fileName.endsWith("xlsx")) {
            workbook = new XSSFWorkbook(FileUtils.openInputStream(file));
        }

        if (workbook != null) {
            Sheet sheet = workbook.getSheetAt(0);
            // 获得当前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()];
                // 循环当前行
                for (int cellNum = firstCellNum; cellNum < lastCellNum + firstCellNum; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    String value = (String) getCellValue(cell);
                    System.out.print(value + "\t");
                }
                System.out.println();
            }

        }

    }

（二） JavaWeb 程序 (多版本控制)

2.0 pom 文件依赖

        <!-- excel导入导出 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.7</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.7</version>
        </dependency>

 

2-1.文件上传

<form class="modifyall" id="excelForm">
    <span class="bigtext">批量导入学生名单</span><br>
　　 <input type="file" name="file" id="file" style="display: inline-block" class="po">
    <input id="excelButton" type="button" class="layui-btn layui-btn-sm  po" value="导入">
</form>

/*
 * 导入名单
 */
function excelUpload() {

    var data = new FormData($("#excelForm")[0]);
    var URL = root + "/teacher/excelUpload";
    $.ajax({
        url : URL,
        type : "post",
        data : data,
        async : false,
        cache : false,
        processData : false,
        contentType : false,
        success : function(data) {
            if (data == "true") {
                location.reload();
            }
            if (data == "false") {
                alert("请检查文件格式");
            }

        },
        error : function(data) {
            alert("操作失败");
        }
    });
}

2-2 后台逻辑处理

    /*
     * excel 导入名单
     */
    @ResponseBody
    @RequestMapping("/excelUpload")
    public Object excelUpload(@RequestParam MultipartFile file, HttpServletRequest request, HttpServletRequest reponse)
            throws IOException {
        PageData pd = this.getPageData();
        //System.out.println(pd);
        String check = (String) this.teacherFacade.readExcel(file, pd);
        return check;
    }

 

/*
     * excel 导入名单
     */
    public Object readExcel(MultipartFile file, PageData pd) throws IOException {
        // TODO Auto-generated method stub
        String fileName = file.getOriginalFilename();
        Workbook workbook = null;
        String check = "";
        InputStream in = file.getInputStream();
        System.out.println(fileName);
　　　　// 判断后缀名，确定版本
        if (fileName.endsWith("xls")) {
            workbook = new HSSFWorkbook(in);
        } else if (fileName.endsWith("xlsx")) {
            workbook = new XSSFWorkbook(in);
        }
        String[] title = { "学号", "姓名", "班级" };
        if (workbook != null) {
            Sheet sheet = workbook.getSheetAt(0);
            // 获得当前sheet的开始行
            int firstRowNum = sheet.getFirstRowNum();
            // 获得当前sheet的结束行
            int lastRowNum = sheet.getLastRowNum();
            Row row = null;
            int firstCellNum = 0;
            int lastCellNum = 0;
            Cell cell = null;
            String value = "";
            row = sheet.getRow(firstRowNum);
            // 获得当前行的开始列
            firstCellNum = row.getFirstCellNum();
            // 获得当前行的列数
            lastCellNum = row.getPhysicalNumberOfCells();

            // 循环当前行
            for (int cellNum = firstCellNum; cellNum < lastCellNum + firstCellNum; cellNum++) {
                cell = row.getCell(cellNum);
                value = (String) GetCellValues.getCellValue(cell);
                if (title[cellNum].equals(value)) {
                    check = "true";
                } else {
                    check = "false";
                    break;
                }
            }
            System.out.println(check);
            if (check.equals("true")) {
                // 循环除了第一行的所有行
                for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
                    // 获得当前行
                    row = sheet.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    // 获得当前行的开始列
                    firstCellNum = row.getFirstCellNum();
                    // 获得当前行的列数
                    lastCellNum = row.getPhysicalNumberOfCells();
                    // 循环当前行
                    for (int cellNum = firstCellNum; cellNum < lastCellNum + firstCellNum; cellNum++) {
                        cell = row.getCell(cellNum);
                        value = (String) GetCellValues.getCellValue(cell);
                        if (pd != null) {
                            switch (cellNum) {
                            case 0:
                                pd.put("stuid", value);
                                break;
                            case 1:
                                pd.put("stuname", value);
                                break;
                            case 2:
                                pd.put("stuclass", value);
                                break;
                            default:
                                break;
                            }
                        }

                    }
                    boolean b = this.teacherService.insertStudent(pd);
                }
            }

        }
        return check;

        // TODO: handle exception
    }

2-3 获取 Excel Cell 值的工具类

package com.ssm.sample.util;

import org.apache.poi.ss.usermodel.Cell;

public class GetCellValues {
    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;
    }
}

 

2-4 从数据库导出 Excel

<a class="test_after_op  btn btn-default" id="exportrecords_info" href="excelExport?testid=${test.get('testid')}" title="导出提交记录"><img
   src="<%=path%>/assets/imgs/export_record.png"  class="test_afterlogos" alt=""></a>

2-5 后台　　/*

     * excel 导出提交信息
     */
    @ResponseBody
    @RequestMapping("/excelExport")
    public void excelExport(HttpServletRequest request, HttpServletRequest reponse) throws IOException {
        PageData pd = this.getPageData();
        System.out.println(pd);
        String testid = pd.getString("testid");
        List<PageData> test = this.teacherFacade.selectTestById(testid);
        List<PageData> list = this.teacherFacade.selectUploadById(testid);
        System.out.println(list);
        String[] title = { "学号", "提交时间", "文件名", "文件大小" };
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = null;
　　　　 // 设置 Excel 第一行内容
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }
　　　　// 从数据库获取数据加入 Excel 文件
        for (int i = 1; i < list.size(); i++) {
            HSSFRow nextrow = sheet.createRow(i);
            HSSFCell cell2 = nextrow.createCell(0);
            cell2.setCellValue(list.get(i).getString("stuid"));
            cell2 = nextrow.createCell(1);
            cell2.setCellValue(list.get(i).getString("fileuptime"));
            cell2 = nextrow.createCell(2);
            cell2.setCellValue(list.get(i).getString("filename"));
            cell2 = nextrow.createCell(3);
            cell2.setCellValue(list.get(i).getString("filesize"));
        }
        String fileName = test.get(0).getString("testname") + ".xls";
        System.out.println(fileName);
        String path = request.getSession().getServletContext().getRealPath("/") + "ExamSystem/" + fileName;
        File file = new File(path);
        try {
            file.createNewFile();
            FileOutputStream stream = FileUtils.openOutputStream(file);
            workbook.write(stream);
            stream.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
　　　　// 导出
        response.addHeader("content-disposition", "attachment;filename=" + fileName);
        FileUtils.copyFile(file, response.getOutputStream());

    }