SSM导出导入Excel表

程序员文章站 2022-06-25 12:08:45

...

项目中经常要用到Excel表格的导出导入。故此，写了一个测试。现在奉上源码。
项目的目录结构:
SSM导出导入Excel表

目录结构截图
SSM导出导入Excel表

页面展示截图：
SSM导出导入Excel表
导出截图

导入截图成功插入数据库
SSM导出导入Excel表

现在给出全部源码：
一：
PoiService.java

package com.bank.service;

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

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.whf.dao.PoiDao;
import com.whf.entity.Computer;
import com.whf.util.FillComputerManager;
import com.whf.util.Layouter;
import com.whf.util.Writer;

@Service("poiService")
@Transactional
public class PoiService {

    @Resource(name = "poiDao")
    private PoiDao dao;

    public void exportXLS(HttpServletResponse response) {

        // 1.创建一个 workbook
        HSSFWorkbook workbook = new HSSFWorkbook();

        // 2.创建一个 worksheet
        HSSFSheet worksheet = workbook.createSheet("Computer");

        // 3.定义起始行和列
        int startRowIndex = 0;
        int startColIndex = 0;

        // 4.创建title,data,headers
        Layouter.buildReport(worksheet, startRowIndex, startColIndex);

        // 5.填充数据
        FillComputerManager.fillReport(worksheet, startRowIndex, startColIndex, getDatasource());

        // 6.设置reponse参数
        String fileName = "ComputersReport.xls";
        response.setHeader("Content-Disposition", "inline; filename=" + fileName);
        // 确保发送的当前文本格式
        response.setContentType("application/vnd.ms-excel");

        // 7. 输出流
        Writer.write(response, worksheet);

    }

    /**
     * 读取报表
     */
    public List<Computer> readReport(InputStream inp) {

        List<Computer> computerList = new ArrayList<Computer>();

        try {
            String cellStr = null;

            Workbook wb = WorkbookFactory.create(inp);

            Sheet sheet = wb.getSheetAt(0);// 取得第一个sheets

            // 从第四行开始读取数据
            for (int i = 3; i <= sheet.getLastRowNum(); i++) {

                Computer computer = new Computer();
                Computer addComputer = new Computer();

                Row row = sheet.getRow(i); // 获取行(row)对象
                System.out.println(row);
                if (row == null) {
                    // row为空的话,不处理
                    continue;
                }

                for (int j = 0; j < row.getLastCellNum(); j++) {

                    Cell cell = row.getCell(j); // 获得单元格(cell)对象

                    // 转换接收的单元格
                    cellStr = ConvertCellStr(cell, cellStr);

                    // 将单元格的数据添加至一个对象
                    addComputer = addingComputer(j, computer, cellStr);

                }
                // 将添加数据后的对象填充至list中
                computerList.add(addComputer);
            }

        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (inp != null) {
                try {
                    inp.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            } else {

            }
        }
        return computerList;

    }

    /**
     * 从数据库获得所有的Computer信息.
     */
    private List<Computer> getDatasource() {
        return dao.getComputer();
    }

    /**
     * 读取报表的数据后批量插入
     */
    public int[] insertComputer(List<Computer> list) {
        return dao.insertComputer(list);

    }

    /**
     * 获得单元格的数据添加至computer
     * 
     * @param j 列数
     * @param computer 添加对象
     * @param cellStr 单元格数据
     * @return
     */
    private Computer addingComputer(int j, Computer computer, String cellStr) {
        switch (j) {
        case 0:
            // computer.setId(0);
            break;
        case 1:
            computer.setBrand(cellStr);
            break;
        case 2:
            computer.setCpu(cellStr);
            break;
        case 3:
            computer.setGpu(cellStr);
            break;
        case 4:
            computer.setMemory(cellStr);
            break;
        case 5:
            computer.setPrice(new Double(cellStr).doubleValue());
            break;
        }

        return computer;
    }

    /**
     * 把单元格内的类型转换至String类型
     */
    private String ConvertCellStr(Cell cell, String cellStr) {

        switch (cell.getCellType()) {

        case Cell.CELL_TYPE_STRING:
            // 读取String
            cellStr = cell.getStringCellValue().toString();
            break;

        case Cell.CELL_TYPE_BOOLEAN:
            // 得到Boolean对象的方法
            cellStr = String.valueOf(cell.getBooleanCellValue());
            break;

        case Cell.CELL_TYPE_NUMERIC:

            // 先看是否是日期格式
            if (DateUtil.isCellDateFormatted(cell)) {

                // 读取日期格式
                cellStr = cell.getDateCellValue().toString();

            } else {

                // 读取数字
                cellStr = String.valueOf(cell.getNumericCellValue());
            }
            break;

        case Cell.CELL_TYPE_FORMULA:
            // 读取公式
            cellStr = cell.getCellFormula().toString();
            break;
        }
        return cellStr;
    }
}

二：
PoiController.java

package com.whf.controller;

import java.io.IOException;
import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import com.bank.service.PoiService;
import com.whf.entity.Computer;

@Controller
@RequestMapping("/report")
public class PoiController {

    @Resource(name = "poiService")
    private PoiService service;

    /**
     * 跳转到主页.
     */
    @RequestMapping(value = "", method = RequestMethod.GET)
    public String getIndex() {
        return "report";
    }

    /**
     * 导出excel报表
     */
    @RequestMapping(value = "/export", method = RequestMethod.GET)
    public void getXLS(HttpServletResponse response) {
        service.exportXLS(response);
    }

    /**
     * 读取excel报表
     */
    @RequestMapping(value = "/read", method = RequestMethod.POST)
    public String getReadReport(@RequestParam
    MultipartFile file) throws IOException {
        List<Computer> list = service.readReport(file.getInputStream());
        service.insertComputer(list);
        return "addedReport";

    }
}

三：
BaseDao.java

package com.whf.dao;

import java.io.Serializable;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.SessionFactoryUtils;
import org.springframework.util.Assert;

/**
 * DAO基类，其它DAO可以直接继承这个DAO，不但可以复用共用的方法，还可以获得泛型的好处。
 */
public class BaseDao<T> {

    private Class<T> entityClass;

    @Autowired
    private HibernateTemplate hibernateTemplate;

    /**
     * 通过反射获取子类确定的泛型类
     */
    public BaseDao() {
        Type genType = getClass().getGenericSuperclass();
        Type[] params = ((ParameterizedType) genType).getActualTypeArguments();
        entityClass = (Class) params[0];
    }

    /**
     * 根据ID加载PO实例
     * 
     * @param id
     * @return 返回相应的持久化PO实例
     */
    public T load(Serializable id) {
        return (T) getHibernateTemplate().load(entityClass, id);
    }

    /**
     * 根据ID获取PO实例
     * 
     * @param id
     * @return 返回相应的持久化PO实例
     */
    public T get(Serializable id) {
        return (T) getHibernateTemplate().get(entityClass, id);
    }

    /**
     * 获取PO的所有对象
     * 
     * @return
     */
    public List<T> loadAll() {
        return getHibernateTemplate().loadAll(entityClass);
    }

    /**
     * 保存PO
     * 
     * @param entity
     */
    public void save(T entity) {
        getHibernateTemplate().save(entity);
        getHibernateTemplate().flush();
        // SessionFactory sf = hibernateTemplate.getSessionFactory();
        // sf.getCurrentSession().beginTransaction();
        // sf.getCurrentSession().save(entity);
    }

    /**
     * 删除PO
     * 
     * @param entity
     */
    public void remove(T entity) {
        getHibernateTemplate().delete(entity);
    }

    /**
     * 更改PO
     * 
     * @param entity
     */
    public void update(T entity) {
        getHibernateTemplate().update(entity);
    }

    /**
     * 执行HQL查询
     * 
     * @param sql
     * @return 查询结果
     */
    public List find(String hql) {
        return this.getHibernateTemplate().find(hql);
    }

    /**
     * 执行带参的HQL查询
     * 
     * @param sql
     * @param params
     * @return 查询结果
     */
    public List find(String hql, Object... params) {
        return this.getHibernateTemplate().find(hql, params);
    }

    /**
     * 对延迟加载的实体PO执行初始化
     * 
     * @param entity
     */
    public void initialize(Object entity) {
        this.getHibernateTemplate().initialize(entity);
    }

    /**
     * 分页查询函数，使用hql.
     * 
     * @param pageNo 页号,从1开始.
     */
    public Page pagedQuery(String hql, int pageNo, int pageSize, Object... values) {
        Assert.hasText(hql);
        Assert.isTrue(pageNo >= 1, "pageNo should start from 1");
        // Count查询
        String countQueryString = " select count (*) " + removeSelect(removeOrders(hql));
        List countlist = getHibernateTemplate().find(countQueryString, values);
        long totalCount = (Long) countlist.get(0);

        if (totalCount < 1)
            return new Page();
        // 实际查询返回分页对象
        int startIndex = Page.getStartOfPage(pageNo, pageSize);
        Query query = createQuery(hql, values);
        List list = query.setFirstResult(startIndex).setMaxResults(pageSize).list();

        return new Page(startIndex, totalCount, pageSize, list);
    }

    /**
     * 创建Query对象. 对于需要first,max,fetchsize,cache,cacheRegion等诸多设置的函数,可以在返回Query后自行设置. 留意可以连续设置,如下：
     * 
     * <pre>
     * dao.getQuery(hql).setMaxResult(100).setCacheable(true).list();
     * </pre>
     * 
     * 调用方式如下：
     * 
     * <pre>
     *        dao.createQuery(hql)
     *        dao.createQuery(hql,arg0);
     *        dao.createQuery(hql,arg0,arg1);
     *        dao.createQuery(hql,new Object[arg0,arg1,arg2])
     * </pre>
     * 
     * @param values 可变参数.
     */
    public Query createQuery(String hql, Object... values) {
        Assert.hasText(hql);
        Query query = getSession().createQuery(hql);
        for (int i = 0; i < values.length; i++) {
            query.setParameter(i, values[i]);
        }
        return query;
    }

    /**
     * 去除hql的select 子句，未考虑union的情况,用于pagedQuery.
     * 
     * @see #pagedQuery(String,int,int,Object[])
     */
    private static String removeSelect(String hql) {
        Assert.hasText(hql);
        int beginPos = hql.toLowerCase().indexOf("from");
        Assert.isTrue(beginPos != -1, " hql : " + hql + " must has a keyword 'from'");
        return hql.substring(beginPos);
    }

    /**
     * 去除hql的orderby 子句，用于pagedQuery.
     * 
     * @see #pagedQuery(String,int,int,Object[])
     */
    private static String removeOrders(String hql) {
        Assert.hasText(hql);
        Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
        Matcher m = p.matcher(hql);
        StringBuffer sb = new StringBuffer();
        while (m.find()) {
            m.appendReplacement(sb, "");
        }
        m.appendTail(sb);
        return sb.toString();
    }

    public HibernateTemplate getHibernateTemplate() {
        return hibernateTemplate;
    }

    public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
        this.hibernateTemplate = hibernateTemplate;
    }

    public Session getSession() {
        return SessionFactoryUtils.getSession(hibernateTemplate.getSessionFactory(), true);
    }

}

四
Page.java

package com.whf.dao;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

/**
 * 分页对象. 包含当前页数据及分页信息如总记录数.
 * 
 */
public class Page implements Serializable {

    private static int DEFAULT_PAGE_SIZE = 20;

    private int pageSize = DEFAULT_PAGE_SIZE; // 每页的记录数

    private long start; // 当前页第一条数据在List中的位置,从0开始

    private List data; // 当前页中存放的记录,类型一般为List

    private long totalCount; // 总记录数

    /**
     * 构造方法，只构造空页.
     */
    public Page() {
        this(0, 0, DEFAULT_PAGE_SIZE, new ArrayList());
    }

    /**
     * 默认构造方法.
     * 
     * @param start 本页数据在数据库中的起始位置
     * @param totalSize 数据库中总记录条数
     * @param pageSize 本页容量
     * @param data 本页包含的数据
     */
    public Page(long start, long totalSize, int pageSize, List data) {
        this.pageSize = pageSize;
        this.start = start;
        this.totalCount = totalSize;
        this.data = data;
    }

    /**
     * 取总记录数.
     */
    public long getTotalCount() {
        return this.totalCount;
    }

    /**
     * 取总页数.
     */
    public long getTotalPageCount() {
        if (totalCount % pageSize == 0)
            return totalCount / pageSize;
        else
            return totalCount / pageSize + 1;
    }

    /**
     * 取每页数据容量.
     */
    public int getPageSize() {
        return pageSize;
    }

    /**
     * 取当前页中的记录.
     */
    public List getResult() {
        return data;
    }

    /**
     * 取该页当前页码,页码从1开始.
     */
    public long getCurrentPageNo() {
        return start / pageSize + 1;
    }

    /**
     * 该页是否有下一页.
     */
    public boolean isHasNextPage() {
        return this.getCurrentPageNo() < this.getTotalPageCount();
    }

    /**
     * 该页是否有上一页.
     */
    public boolean isHasPreviousPage() {
        return this.getCurrentPageNo() > 1;
    }

    /**
     * 获取任一页第一条数据在数据集的位置，每页条数使用默认值.
     * 
     * @see #getStartOfPage(int,int)
     */
    protected static int getStartOfPage(int pageNo) {
        return getStartOfPage(pageNo, DEFAULT_PAGE_SIZE);
    }

    /**
     * 获取任一页第一条数据在数据集的位置.
     * 
     * @param pageNo 从1开始的页号
     * @param pageSize 每页记录条数
     * @return 该页第一条数据
     */
    public static int getStartOfPage(int pageNo, int pageSize) {
        return (pageNo - 1) * pageSize;
    }
}

五：
PoiDao.java

package com.whf.dao;

import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.whf.entity.Computer;

@Repository("poiDao")
public class PoiDao {

    private SimpleJdbcTemplate jdbcTemplate;

    @Resource(name = "dataSource")
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new SimpleJdbcTemplate(dataSource);
    }

    public List<Computer> getComputer() {
        String sql = "SELECT * FROM computer";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Computer>(Computer.class));

    }

    public int[] insertComputer(List<Computer> list) {
        String sql = "INSERT INTO computer (BRAND,CPU,GPU,MEMORY,PRICE)VALUES(:brand,:cpu,:gpu,:memory,:price)";
        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(list.toArray());
        return jdbcTemplate.batchUpdate(sql, batch);

    }
}

六：
Computer.java

package com.whf.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class Computer {

    @Id
    @GeneratedValue
    private int id;

    private String brand;

    private String cpu;

    private String gpu;

    private String memory;

    private Double price;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public String getCpu() {
        return cpu;
    }

    public void setCpu(String cpu) {
        this.cpu = cpu;
    }

    public String getGpu() {
        return gpu;
    }

    public void setGpu(String gpu) {
        this.gpu = gpu;
    }

    public String getMemory() {
        return memory;
    }

    public void setMemory(String memory) {
        this.memory = memory;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

}

七：
DateUtils.java

package com.whf.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class Computer {

    @Id
    @GeneratedValue
    private int id;

    private String brand;

    private String cpu;

    private String gpu;

    private String memory;

    private Double price;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getBrand() {
        return brand;
    }

    public void setBrand(String brand) {
        this.brand = brand;
    }

    public String getCpu() {
        return cpu;
    }

    public void setCpu(String cpu) {
        this.cpu = cpu;
    }

    public String getGpu() {
        return gpu;
    }

    public void setGpu(String gpu) {
        this.gpu = gpu;
    }

    public String getMemory() {
        return memory;
    }

    public void setMemory(String memory) {
        this.memory = memory;
    }

    public Double getPrice() {
        return price;
    }

    public void setPrice(Double price) {
        this.price = price;
    }

}

八：
FillComputerManager.java

package com.whf.util;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.CellStyle;

import com.whf.entity.Computer;

public class FillComputerManager {

    public static void fillReport(HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Computer> datasource) {

        // Row offset
        startRowIndex += 2;

        // Create cell style for the body
        HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle();
        bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        bodyCellStyle.setWrapText(false); // 是否自动换行.

        // Create body
        for (int i = startRowIndex; i + startRowIndex - 2 < datasource.size() + 2; i++) {
            // Create a new row
            HSSFRow row = worksheet.createRow((short) i + 1);

            // Retrieve the id value
            HSSFCell cell1 = row.createCell(startColIndex + 0);
            cell1.setCellValue(datasource.get(i - 2).getId());
            cell1.setCellStyle(bodyCellStyle);

            // Retrieve the brand value
            HSSFCell cell2 = row.createCell(startColIndex + 1);
            cell2.setCellValue(datasource.get(i - 2).getBrand());
            cell2.setCellStyle(bodyCellStyle);

            // Retrieve the model value
            HSSFCell cell3 = row.createCell(startColIndex + 2);
            cell3.setCellValue(datasource.get(i - 2).getCpu());
            cell3.setCellStyle(bodyCellStyle);

            // Retrieve the maximum power value
            HSSFCell cell4 = row.createCell(startColIndex + 3);
            cell4.setCellValue(datasource.get(i - 2).getGpu());
            cell4.setCellStyle(bodyCellStyle);

            // Retrieve the price value
            HSSFCell cell5 = row.createCell(startColIndex + 4);
            cell5.setCellValue(datasource.get(i - 2).getMemory());
            cell5.setCellStyle(bodyCellStyle);

            // Retrieve the efficiency value
            HSSFCell cell6 = row.createCell(startColIndex + 5);
            cell6.setCellValue(datasource.get(i - 2).getPrice());
            cell6.setCellStyle(bodyCellStyle);
        }
    }
}

九：

package com.whf.util;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.CellStyle;

import com.whf.entity.Computer;

public class FillComputerManager {

    public static void fillReport(HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Computer> datasource) {

        // Row offset
        startRowIndex += 2;

        // Create cell style for the body
        HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle();
        bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        bodyCellStyle.setWrapText(false); // 是否自动换行.

        // Create body
        for (int i = startRowIndex; i + startRowIndex - 2 < datasource.size() + 2; i++) {
            // Create a new row
            HSSFRow row = worksheet.createRow((short) i + 1);

            // Retrieve the id value
            HSSFCell cell1 = row.createCell(startColIndex + 0);
            cell1.setCellValue(datasource.get(i - 2).getId());
            cell1.setCellStyle(bodyCellStyle);

            // Retrieve the brand value
            HSSFCell cell2 = row.createCell(startColIndex + 1);
            cell2.setCellValue(datasource.get(i - 2).getBrand());
            cell2.setCellStyle(bodyCellStyle);

            // Retrieve the model value
            HSSFCell cell3 = row.createCell(startColIndex + 2);
            cell3.setCellValue(datasource.get(i - 2).getCpu());
            cell3.setCellStyle(bodyCellStyle);

            // Retrieve the maximum power value
            HSSFCell cell4 = row.createCell(startColIndex + 3);
            cell4.setCellValue(datasource.get(i - 2).getGpu());
            cell4.setCellStyle(bodyCellStyle);

            // Retrieve the price value
            HSSFCell cell5 = row.createCell(startColIndex + 4);
            cell5.setCellValue(datasource.get(i - 2).getMemory());
            cell5.setCellStyle(bodyCellStyle);

            // Retrieve the efficiency value
            HSSFCell cell6 = row.createCell(startColIndex + 5);
            cell6.setCellValue(datasource.get(i - 2).getPrice());
            cell6.setCellStyle(bodyCellStyle);
        }
    }
}

十：
Layouter.java

package com.whf.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;

public class Layouter {

    /**
     * 创建报表
     */
    public static void buildReport(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
        // 设置列的宽度
        worksheet.setColumnWidth(0, 5000);
        worksheet.setColumnWidth(1, 5000);
        worksheet.setColumnWidth(2, 5000);
        worksheet.setColumnWidth(3, 5000);
        worksheet.setColumnWidth(4, 5000);
        worksheet.setColumnWidth(5, 5000);

        buildTitle(worksheet, startRowIndex, startColIndex);

        buildHeaders(worksheet, startRowIndex, startColIndex);

    }

    /**
     * 创建报表标题和日期
     */
    private static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
        // 设置报表标题字体
        Font fontTitle = worksheet.getWorkbook().createFont();
        fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fontTitle.setFontHeight((short) 280);

        // 标题单元格样式
        HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();
        cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyleTitle.setWrapText(true);
        cellStyleTitle.setFont((HSSFFont) fontTitle);

        // 报表标题
        HSSFRow rowTitle = worksheet.createRow((short) startRowIndex);
        rowTitle.setHeight((short) 500);
        HSSFCell cellTitle = rowTitle.createCell(startColIndex);
        cellTitle.setCellValue("Computer Report!");
        cellTitle.setCellStyle(cellStyleTitle);

        // 合并区域内的报告标题
        worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));

        // date header
        HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1);
        HSSFCell cellDate = dateTitle.createCell(startColIndex);
        cellDate.setCellValue("这个报表创建于: " + DateUtils.getNowTime());
    }

    /**
     * 创建表头
     */
    private static void buildHeaders(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
        // Header字体
        Font font = worksheet.getWorkbook().createFont();

        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // 单元格样式
        HSSFCellStyle headerCellStyle = worksheet.getWorkbook().createCellStyle();
        headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
        headerCellStyle.setFillPattern(CellStyle.FINE_DOTS);
        headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        headerCellStyle.setWrapText(true);
        headerCellStyle.setFont((HSSFFont) font);
        headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN);

        // 创建字段标题
        HSSFRow rowHeader = worksheet.createRow((short) startRowIndex + 2);
        rowHeader.setHeight((short) 500);

        HSSFCell cell1 = rowHeader.createCell(startColIndex + 0);
        cell1.setCellValue("Id");
        cell1.setCellStyle(headerCellStyle);

        HSSFCell cell2 = rowHeader.createCell(startColIndex + 1);
        cell2.setCellValue("Brand");
        cell2.setCellStyle(headerCellStyle);

        HSSFCell cell3 = rowHeader.createCell(startColIndex + 2);
        cell3.setCellValue("CPU");
        cell3.setCellStyle(headerCellStyle);

        HSSFCell cell4 = rowHeader.createCell(startColIndex + 3);
        cell4.setCellValue("GPU");
        cell4.setCellStyle(headerCellStyle);

        HSSFCell cell5 = rowHeader.createCell(startColIndex + 4);
        cell5.setCellValue("Memory");
        cell5.setCellStyle(headerCellStyle);

        HSSFCell cell6 = rowHeader.createCell(startColIndex + 5);
        cell6.setCellValue("Price");
        cell6.setCellStyle(headerCellStyle);

    }
}

前端页面：
一：
index.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<script>
  window.location.href=" <c:url value="/report"/>";
</script>

二：
addedReport.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">

    <title>Insert title here<</title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> 


  </head>

  <body>
    <h1>导入成功!</h1>  

<a href="/bank/report">返回</a>  
  </body>
</html>

三：
report.jsp

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>  
<%@ taglib uri="http://www.springframework.org/tags" prefix="spring" %>  
<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">

    <title>Report</title>


  </head>

  <body>
    <c:url var="exportUrl" value="/report/export" />  
    <c:url var="readUrl" value="/report/read" />  


    <h3><a href="${exportUrl }">Export Report</a></h3>  
    <br />  
    <form  id="readReportForm" action="${readUrl }" method="post" enctype="multipart/form-data"  >  
            <label for="file">File</label>  
            <input id="file" type="file" name="file" />  
            <p><button type="submit">Read</button></p>    
        </form>  
  </body>
</html>

《end》

SSM导出导入Excel表

EXCEL导入导出工具DataPieV3.7更新，大幅提升EXCEL导入ACCESS的

在命令行导出MySQL数据到excel表

ThinkPHP使用PHPExcel实现Excel数据导入导出完整实例

JAVA中导出、导入EXCEL用到的方法和一些语句

php mysql数据的导入导出,数据表结构的导入导出_PHP教程

asp.net实现数据从DataTable导入到Excel文件并创建表的方法

简单的excel导入导出示例分享

SpringMvc+POI处理excel表数据导入

C#导入导出EXCEL文件的代码实例

Java中excel表数据的批量导入方法