SSM导出导入Excel表
来源：互联网 发布：java必看书籍知乎 编辑：程序博客网 时间：2024/05/21 14:40
项目中经常要用到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")@Transactionalpublic 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; }}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
二：
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"; }}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
三：
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); }}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
四
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; }}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
五：
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); }}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
六：
Computer.java
package com.whf.entity;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.Id;@Entitypublic 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; }}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
七：
DateUtils.java
package com.whf.entity;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.Id;@Entitypublic 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; }}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
八：
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); } }}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
九：
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); } }}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
十：
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); }}- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
前端页面：
一：
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>- 1
- 2
- 3
- 4
- 5
- 6
二：
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>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
三：
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>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
《end》
阅读全文
0 0
- SSM导出导入Excel表
- SSM Excel表格导出导入
- 在SSM下使用POI实现Excel表的导入/导出
- 在SSM下使用POI实现Excel表的导入/导出
- Java SSM注解实现POI导入导出Excel
- SSM学习笔记（三）——excel导入导出
- SSM框架整合(基本CRUD+分页+Excel导入导出)
- ssm整合导入导出
- SSM框架导入导出
- (SSM)POI导出Excel
- excel数据导入ssm
- Excel表的导入，导出
- Thinkphp导入导出excel表
- GridView导入导出Excel表
- java导入/导出excel表
- POI导入导出excel表
- python导入导出Excel表
- ssm中用poi导出excel
- ASP.NET 判断微信浏览器
- mt6737 M0 如何写马达驱动和在驱动中注册mic设备
- 侠之大者，为国为民
- js 中的正则表达式
- 虚拟机中的Ubuntu安装步骤
- SSM导出导入Excel表
- 机器视觉之多坐标系标定与统一
- Java.lang.throwable源代码解析
- python爬虫起步
- 继承与构造函数
- HDU 5115 Dire Wolf (区间DP)
- springboot+mybatis+mysql(2)
- Mysql集群重启失败
- web.xml配置详解
