项目中经常要用到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》