以前刚学编程的时候早博客园写的poi上传下载Excel博客,现在整理回顾下以前写的
供大家分析,转载需注明出处
一、实现下载功能
下载的Excel是从数据库读取出数据,然后将数据写入Excel中,导出的是一个书籍信息表,经过浏览器下载,后台采用SpringMvc
bean类(Book):
public class Book {
private Long uuid;
private String bookName;
*/
private Category category;
public Long getUuid() {
return uuid;
}
public void setUuid(Long uuid) {
this.uuid = uuid;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public Category getCategory() {
return category;
}
public void setCategory(Category category) {
this.category = category;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
}
还有一个是类别表,用来联系这个书籍表的,就不列出来啦。
接下来在jsp页面设置一个按钮进行下载
jsp代码:
<input type ="button" value="导出数据" name="getExcel" οnclick="location.href='book/getExcel.html'">
在controller中的代码如下:
@RequestMapping("book/getExcel")
public ModelAndView getExcel(HttpServletRequest request,
HttpServletResponse response) throws IOException {
//FileOutputStream bookInfo = new FileOutputStream("E:\\书本信息.xls");这个是原本用来默认输出的地址及名称,现在没有使用
HSSFWorkbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("书籍信息");//sheet的名称
Row row = sheet.createRow(0);//取出行
Cell cell = row.createCell(0);//取出行中的单元
cell.setCellValue("书名");设置单元格头
cell = row.createCell(1);//第一行第二个单元
cell.setCellValue("书籍类别");
cell = row.createCell(2);//第三个单元格
cell.setCellValue("作者");
List<Book> books = bookService.searchAllBook();//传到service层的方法,最后会传到DAO层的DAO.XML进行sql查询,细的就不说了,相信没有比我更渣的了
for (int i = 0, index = 1; i < books.size(); i++, index++) {//进行循环取出数据
//for(Book book:books){现在想想,之前不知道为啥不用forech循环,继续用以前的,懒得改
row = sheet.createRow(index);
Book book = books.get(i);
cell = row.createCell(0);
cell.setCellValue(book.getBookName());
cell = row.createCell(1);
cell.setCellValue(book.getCategory().getName());
cell = row.createCell(2);
cell.setCellValue(book.getAuthor());
}
OutputStream bookInfo=response.getOutputStream();//
response.setHeader("Content-disposition","attachment;filename="+URLEncoder.encode("书本信息.xls","UTF-8"));
wb.write(bookInfo);//写
bookInfo.flush();
bookInfo.close();
request.setAttribute("books", books);
return new ModelAndView(BASEDIR + "/list");到处后回到当前页面
}
下载功能完成。
二、上传功能,使用Fileupload
首先要部署commons-fileupload,在pom.xml中部署下
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>${commons.fileupload.version}</version>
</dependency>
版本不同可以部署不一样的,看自己了
接下来在web-commen.xml里面配置//这个不是固定的,根据我自己的配置位置
<!-- 用于上传文件,设置上传最大文件限制 -->
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="maxUploadSize">
<value>1048576</value>
</property>
</bean>
jsp代码如下:
<form method="post" action="book/readExcel.html" enctype="multipart/form-data">
<input type="file" name="file" accept=".xls,.xlsx"/>
<input type="submit" value="提交"/>
</form>
现在就可以进行主要的Controller层进行操作了代码如下:
@RequestMapping("book/readExcel")
public ModelAndView readExcel(HttpServletRequest request,
HttpServletResponse response) throws IOException {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile mFile = multipartRequest.getFile("file");
InputStream in = mFile.getInputStream();
XSSFWorkbook wb = new XSSFWorkbook(in);
XSSFSheet sheet = wb.getSheetAt(0);
int rows = sheet.getPhysicalNumberOfRows();
for (int i = 0; i < rows; i++) {
XSSFRow row = sheet.getRow(i);
if (row != null) {
Book book = new Book();
Category category = new Category();
XSSFCell bookName = row.getCell(0);
XSSFCell author = row.getCell(1);
XSSFCell categoryName = row.getCell(2);
book.setBookName(bookName.getStringCellValue());
book.setAuthor(author.getStringCellValue());
category.setName(categoryName.getStringCellValue());
Map<String, Object> params = new HashMap<String, Object>();
params.put("categoryName", categoryName.getStringCellValue());
// TODO 存在找不到分类时,会出现保存异常
List<Category> categoryList = categoryService.searchCategoryByParams(params);
if (!categoryList.isEmpty()) {
category.setUuid(categoryList.get(0).getUuid());
}
else{
int Id=i+1;
response.sendRedirect("readExcelError.html?Id="+String.valueOf(Id));
return new ModelAndView(BASEDIR+"/readExcelError");
}
book.setCategory(category);
bookService.saveBook(book);
}
}
List<Book> Books = bookService.searchAllBook();
request.setAttribute("books", Books);
return new ModelAndView(BASEDIR + "/list");
}
到此,上传下载就可以完成了,希望对大家有帮助