利用springmvc 导入导出excel

导如excel文件

工具类

import java.io.File;import java.util.ArrayList;import java.util.List;import net.sf.ehcache.hibernate.management.impl.BeanUtils;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;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.ss.usermodel.WorkbookFactory;import org.apache.poi.ss.util.CellRangeAddress;public class ExcelUtil {    private ExcelUtil() {    }    /**     * 主标题     * @param title     * @param cellRangeAddressLength     * @return     */    public static HSSFWorkbook makeExcelHead(String title, int cellRangeAddressLength){        HSSFWorkbook workbook = new HSSFWorkbook();        HSSFCellStyle styleTitle = createStyle(workbook, (short)16);        HSSFSheet sheet = workbook.createSheet(title);        sheet.setDefaultColumnWidth(25);        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, cellRangeAddressLength);        sheet.addMergedRegion(cellRangeAddress);        HSSFRow rowTitle = sheet.createRow(0);        HSSFCell cellTitle = rowTitle.createCell(0);        // 为标题设置背景颜色        styleTitle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);        styleTitle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);        cellTitle.setCellValue(title);        cellTitle.setCellStyle(styleTitle);        return workbook;    }    /**     * 二级标题     * @param workbook     * @param secondTitles     * @return     */    public static HSSFWorkbook makeSecondHead(HSSFWorkbook workbook, String[] secondTitles){        // 创建用户属性栏        HSSFSheet sheet = workbook.getSheetAt(0);        HSSFRow rowField = sheet.createRow(1);        HSSFCellStyle styleField = createStyle(workbook, (short)13);                for (int i = 0; i < secondTitles.length; i++) {            HSSFCell cell = rowField.createCell(i);            cell.setCellValue(secondTitles[i]);            cell.setCellStyle(styleField);                    }        return workbook;    }    /**     * 插入数据     * @param workbook     * @param dataList     * @param beanPropertys     * @return     */    public static <T> HSSFWorkbook exportExcelData(HSSFWorkbook workbook, List<T> dataList, String[] beanPropertys) {        HSSFSheet sheet = workbook.getSheetAt(0);        // 填充数据        HSSFCellStyle styleData = workbook.createCellStyle();        styleData.setAlignment(HSSFCellStyle.ALIGN_CENTER);        styleData.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        for (int j = 0; j < dataList.size(); j++) {            HSSFRow rowData = sheet.createRow(j + 2);            T t = dataList.get(j);            for(int k=0; k<beanPropertys.length; k++){                Object value = BeanUtils.getBeanProperty(t, beanPropertys[k]);                HSSFCell cellData = rowData.createCell(k);                cellData.setCellValue(value.toString());                cellData.setCellStyle(styleData);            }                    }        return workbook;    }    /**     * 使用批量导入方法时，请注意需要导入的Bean的字段和excel的列一一对应     * @param clazz     * @param file     * @param beanPropertys     * @return     */    public static <T> List<T> parserExcel(Class<T> clazz, File file, String[] beanPropertys) {        // 得到workbook        List<T> list = new ArrayList<T>();        try {            Workbook workbook = WorkbookFactory.create(file);            Sheet sheet = workbook.getSheetAt(0);            // 直接从第三行开始获取数据            int rowSize = sheet.getPhysicalNumberOfRows();            if(rowSize > 2){                                for (int i = 2; i < rowSize; i++) {                    T t = clazz.newInstance();                    Row row = sheet.getRow(i);                    int cellSize = row.getPhysicalNumberOfCells();                    for(int j=0; j<cellSize; j++){                        Object cellValue = getCellValue(row.getCell(j));                        org.apache.commons.beanutils.BeanUtils.copyProperty(t, beanPropertys[j], cellValue);                    }                                     list.add(t);                }            }                    } catch (Exception e) {            e.printStackTrace();        }        return list;    }    /**     * 通用的读取excel单元格的处理方法     * @param cell     * @return     */    private static Object getCellValue(Cell cell) {        Object result = null;        if (cell != null) {            switch (cell.getCellType()) {            case Cell.CELL_TYPE_STRING:                result = cell.getStringCellValue();                break;            case Cell.CELL_TYPE_NUMERIC:                //对日期进行判断和解析                if(HSSFDateUtil.isCellDateFormatted(cell)){                    double cellValue = cell.getNumericCellValue();                    result = HSSFDateUtil.getJavaDate(cellValue);                }                break;            case Cell.CELL_TYPE_BOOLEAN:                result = cell.getBooleanCellValue();                break;            case Cell.CELL_TYPE_FORMULA:                result = cell.getCellFormula();                break;            case Cell.CELL_TYPE_ERROR:                result = cell.getErrorCellValue();                break;            case Cell.CELL_TYPE_BLANK:                break;            default:                break;            }        }        return result;    }    /**     * 提取公共的样式     * @param workbook     * @param fontSize     * @return     */    private static HSSFCellStyle createStyle(HSSFWorkbook workbook, short fontSize){        HSSFCellStyle style = workbook.createCellStyle();                style.setAlignment(HSSFCellStyle.ALIGN_CENTER);        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);        // 创建一个字体样式        HSSFFont font = workbook.createFont();        font.setFontHeightInPoints(fontSize);        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        style.setFont(font);        return style;    }}

实体类

public class User {    public String name;    public String account;    public String dept;    public String gender;    public String email;    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getAccount() {        return account;    }    public void setAccount(String account) {        this.account = account;    }    public String getDept() {        return dept;    }    public void setDept(String dept) {        this.dept = dept;    }    public String getGender() {        return gender;    }    public void setGender(String gender) {        this.gender = gender;    }    public String getEmail() {        return email;    }    public void setEmail(String email) {        this.email = email;    }    public User(String name, String account, String dept, String gender, String email) {        super();        this.name = name;        this.account = account;        this.dept = dept;        this.gender = gender;        this.email = email;    }    public User() {        super();    }}

controller类

import java.io.BufferedInputStream;import java.io.BufferedOutputStream;import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.Date;import java.util.List;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import javax.servlet.http.HttpSession;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 org.springframework.web.multipart.MultipartFile;import cn.itcast.core.model.User;@Controller@RequestMapping("/excel")public class ExcelController {    @RequestMapping("/export.do")    @ResponseBody    public void getExcel(HttpServletRequest req, HttpServletResponse response) throws IOException {        String fileName = new Date().getTime() + ".xls";        List<User> userList = new ArrayList<User>();        User u1 = new User("张三", "zhangsan", "10", "男", "[e-mail]");        User u2 = new User("张三1", "zhangsan1", "10", "男", "[e-mail]");        User u3 = new User("张三2", "zhangsan2", "10", "男", "[e-mail]");        userList.add(u1);        userList.add(u2);        userList.add(u3);        ByteArrayOutputStream os = new ByteArrayOutputStream();        try {            HSSFWorkbook workbook1 = ExcelUtil.makeExcelHead("用户列表", 4);            String[] secondTitles = { "用户名", "账号", "所属部门", "性别", "电子邮箱" };            HSSFWorkbook workbook2 = ExcelUtil.makeSecondHead(workbook1, secondTitles);            String[] beanProperty = { "name", "account", "dept", "gender", "email" };            HSSFWorkbook workbook = ExcelUtil.exportExcelData(workbook2, userList, beanProperty);            workbook.write(os);        } catch (IOException e) {            e.printStackTrace();        }        byte[] content = os.toByteArray();        InputStream is = new ByteArrayInputStream(content);        // 设置response参数，可以打开下载页面        response.reset();        response.setContentType("application/vnd.ms-excel;charset=utf-8");        response.setHeader("Content-Disposition",                "attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1"));        ServletOutputStream out = response.getOutputStream();        BufferedInputStream bis = null;        BufferedOutputStream bos = null;        try {            bis = new BufferedInputStream(is);            bos = new BufferedOutputStream(out);            byte[] buff = new byte[2048];            int bytesRead;            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {                bos.write(buff, 0, bytesRead);            }        } catch (final IOException e) {            throw e;        } finally {            if (bis != null)                bis.close();            if (bos != null)                bos.close();        }    }    @RequestMapping("/import.do")    public void upload(MultipartFile uploadFile, HttpSession session) throws IllegalStateException, IOException {        String filename = uploadFile.getOriginalFilename();        String path = session.getServletContext().getRealPath("/");        File file = new File(path, filename);        uploadFile.transferTo(file);        String[] beanProperty = { "name", "account", "dept", "gender", "email" };        List<User> list = ExcelUtil.parserExcel(User.class, file, beanProperty);    }}

springmvc 新增视图解析器

    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">         <property name="maxUploadSize" value="104857600" />         <property name="maxInMemorySize" value="4096" />         <property name="defaultEncoding" value="UTF-8"></property>    </bean>

maven依赖

        <!-- poi 开始 -->        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi</artifactId>            <version>3.14</version>        </dependency>        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-scratchpad</artifactId>            <version>3.14</version>        </dependency>        <dependency>            <groupId>org.apache.poi</groupId>            <artifactId>poi-ooxml</artifactId>            <version>3.14</version>        </dependency>        <dependency>            <groupId>net.sf.ehcache</groupId>            <artifactId>ehcache</artifactId>            <version>2.10.4</version>        </dependency>        <dependency>            <groupId>commons-beanutils</groupId>            <artifactId>commons-beanutils</artifactId>            <version>1.9.3</version>        </dependency>        <!--poi 结束 -->