最近工作遇到一個需求，需要下載excel模板，編輯后上傳解析存儲到數據庫。因此為了更好的理解公司框架，我就自己先用spring mvc實現了一個樣例。
基礎框架
之前曾經介紹過一個最簡單的spring mvc的項目如何搭建，傳送門在這里。
這次就基于這個工程，繼續實現上傳下載的小例子。需要做下面的事情：
1 增加index.html，添加form提交文件
2 引入commons-fileupload、commons-io、jxl等工具包
3 創建upload download接口
4 注入multipartResolver bean
5 在upload中使用HttpServletRequest獲取文件流，通過WorkBook進行解析
6 在download中通過HttpServerResponse返回文件流，實現下載
頁面
頁面很簡單，其實就是一個form標簽，需要注意的是：
- form中enctype="multipart/form-data"
- action指定訪問的url
- input中需要設置name屬性，這樣后端才能獲取到文件對象
|
1
2
3
4
5
6
7
|
<form role="form" action="/upload" method="POST" enctype="multipart/form-data"> <div class="form-group"> <label for="file">上傳文件</label> <input type="file" id="file" name="file"> </div> <button type="submit" class="btn btn-default">提交</button></form> |
引入commons-fileupload、jxl等工具包
涉及的jar包有：
- commons-fileupload 用于獲取上傳文件
- jxl 用于解析excel
|
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
|
<!-- springframework begins --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>4.2.4.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.2.4.RELEASE</version> </dependency> <dependency> <groupId>javax.servlet</groupId> <artifactId>javax.servlet-api</artifactId> <version>4.0.0-b01</version> </dependency> <!-- https://mvnrepository.com/artifact/commons-io/commons-io --> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.5</version> </dependency> <!-- https://mvnrepository.com/artifact/commons-fileupload/commons-fileupload --> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.2</version> </dependency> <!-- https://mvnrepository.com/artifact/jexcelapi/jxl --> <dependency> <groupId>jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6</version> </dependency> |
Xml的配置
在web.xml中需要配置默認的訪問頁面，因為之前已經設置過攔截的請求是/，因此如果不設置所有的靜態頁面都會被攔截下來。
|
1
2
3
|
<welcome-file-list> <welcome-file>index.html</welcome-file></welcome-file-list> |
在spring的配置文件中，加入CommonsMultipartResolver的bean。
|
1
2
3
4
5
6
7
8
9
|
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> <!-- set the max upload size100MB --> <property name="maxUploadSize"> <value>104857600</value> </property> <property name="maxInMemorySize"> <value>4096</value> </property> </bean> |
上傳代碼
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
@RequestMapping("upload") public void upload(HttpServletRequest request, HttpServletResponse response) throws IOException, BiffException, WriteException { MultipartHttpServletRequest mRequest = (MultipartHttpServletRequest) request; MultipartFile file = mRequest.getFile("file"); Workbook workbook = Workbook.getWorkbook(file.getInputStream()); //遍歷Sheet頁 Arrays.stream(workbook.getSheets()) .forEach(sheet -> { int size = sheet.getRows(); for(int i=0; i<size; i++){ //遍歷每一行，讀取每列信息 Arrays.stream(sheet.getRow(i)).forEach(cell -> System.out.println(cell.getContents().equals("")?'空':cell.getContents())); } }); response.setHeader("Content-Disposition", "attachment; filename=return.xls"); WritableWorkbook writableWorkbook = ExcelUtils.createTemplate(response.getOutputStream()); writableWorkbook.write(); writableWorkbook.close(); } |
下載代碼
|
1
2
3
4
5
6
7
|
@RequestMapping("download") public void download(HttpServletRequest request, HttpServletResponse response) throws IOException, BiffException, WriteException { response.setHeader("Content-Disposition", "attachment; filename=template.xls"); WritableWorkbook writableWorkbook = ExcelUtils.createTemplate(response.getOutputStream()); writableWorkbook.write(); writableWorkbook.close(); } |
模板類
|
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
|
static class ExcelUtils { public static WritableWorkbook createTemplate(OutputStream output) throws IOException, WriteException { WritableWorkbook writableWorkbook= Workbook.createWorkbook(output); WritableSheet wsheet = writableWorkbook.createSheet("測試title", 0); CellFormat cf = writableWorkbook.getSheet(0).getCell(1, 0).getCellFormat(); WritableCellFormat wc = new WritableCellFormat(); // 設置居中 wc.setAlignment(Alignment.CENTRE); // 設置邊框線// wc.setBorder(Border.ALL, BorderLineStyle.THIN); wc.setBackground(jxl.format.Colour.GREEN); Label nc0 = new Label(0, 0, "標題1",wc);//Label(x,y,z)其中x代表單元格的第x+1列，第y+1行, 單元格的內容是z Label nc1 = new Label(1, 0, "標題2",wc); Label nc2 = new Label(2, 0, "標題3",wc); Label nc3 = new Label(0, 1, "dddd"); Label nc4 = new Label(1, 1, "ffff"); wsheet.addCell(nc0); wsheet.addCell(nc1); wsheet.addCell(nc2); wsheet.addCell(nc3); wsheet.addCell(nc4); return writableWorkbook; } } |
最后貢獻下相關的代碼：SpringTest.rar
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持服務器之家。
原文鏈接：http://www.cnblogs.com/xing901022/p/6107048.html
