将Excel文件内容写入到数据库

java 代码

package com.zhupan.spring;

import java.io.File;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Sheet;
import jxl.Workbook;

import org.springframework.validation.BindException;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.SimpleFormController;

import com.ctgusec.model.Student_info;
import com.ctgusec.service.IStudent_infoManage;

/** */
/**
 * @author zhupan
 *
 */
public class EStudentInsertExcelController extends SimpleFormController {

    private IStudent_infoManage studentManage;

    @Override
    protected ModelAndView onSubmit(HttpServletRequest request,
            HttpServletResponse response, Object command, BindException errors)
            throws Exception {
        Student_info student_info = (Student_info) command;
        try {
            MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
            MultipartFile file = multipartRequest.getFile(" Excelfile "); // 获得文件：
            File toFile = new File(" c:\\学生信息临时文件.xls "); // 产生文件名和空文件
            file.transferTo(toFile); // 文件上传
            Workbook book = Workbook.getWorkbook(toFile); // 得到工作薄
            Sheet sheet = book.getSheet(0); // 获得第一个工作表对象
            int row = sheet.getRows(); // /得到该sheet的行数
            int column = sheet.getColumns(); // 得到该sheet的列数
            System.out.println(" 数据行数= " + row);
            System.out.println(" 数据列数= " + column);
            for (int i = 1; i < row; i++) {
                for (int j = 0; j < column; j++) {
                    System.out.println(" j= " + j);
                    sheet.getCell(j, i).getContents(); // 得到第j列第i行的单元格的类容
                    student_info
                            .setStudentID(sheet.getCell(j, i).getContents());
                    student_info.setName(sheet.getCell(++j, i).getContents());
                    student_info.setSex(sheet.getCell(++j, i).getContents());
                    student_info.setUnit(sheet.getCell(++j, i).getContents());
                    student_info.setClass_(sheet.getCell(++j, i).getContents());
                    student_info.setSpecialty(sheet.getCell(++j, i)
                            .getContents());
                    student_info.setRemark(sheet.getCell(++j, i).getContents());
                }
                if (this.studentManage.getStudentByStudentID(
                        student_info.getStudentID()).size() != 0)
                    return new ModelAndView(" education/e-studentInfoAddError ");
                this.studentManage.insertStudent_info(student_info);
            }
            book.close();
            return new ModelAndView(" education/e-studentInfoAddExcelSuccess ",
                    " row ", new Integer(row - 1));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new ModelAndView(" education/e-studentInfoAddExcelError ");
    }

    public void setStudentManage(IStudent_infoManage studentManage) {
        this.studentManage = studentManage;
    }
}

评论

1 楼 ssuupv 2006-10-09  

收藏一下先