Spring Boot实战之导出excel，并上传存入Azure Storage

Spring Boot实战之导出excel

本文使用Apache POI实现excel文档的导出。 实现从数据库读取数据——生成excel——上传到AzureStorage的流程

数据库操作，及文件上传AzureStorage的流程可以参考之前的文章

http://blog.csdn.net/sun_t89/article/details/51912905

http://blog.csdn.net/sun_t89/article/details/51956392

Apache POI详细操作，可以参考

http://www.cnblogs.com/LiZhiW/p/4313789.html?utm_source=tuicool&utm_medium=referral

1、修改pom.xml，添加Apache POI库

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.14</version></dependency>

2、添加导出数据表的数据模型

package com.xiaofangtech.sunt.bean;import java.math.BigDecimal;import java.util.Date;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.GenerationType;import javax.persistence.Id;import javax.persistence.Table;@Entity@Table(name="t_statistics")public class StatisticsInfo {@Id@GeneratedValue(strategy = GenerationType.AUTO)private long id;private BigDecimal money;private String description;private Date currentdate;public long getId() {return id;}public void setId(long id) {this.id = id;}public BigDecimal getMoney() {return money;}public void setMoney(BigDecimal money) {this.money = money;}public String getDescription() {return description;}public void setDescription(String description) {this.description = description;}public Date getCurrentdate() {return currentdate;}public void setCurrentdate(Date currentdate) {this.currentdate = currentdate;}}

3、添加数据访问接口类StatisticsRepository，本文查询所有数据，不另外添加方法，直接使用findAll方法

package com.xiaofangtech.sunt.repository;import org.springframework.data.repository.CrudRepository;import com.xiaofangtech.sunt.bean.StatisticsInfo;public interface StatisticsRepository extends CrudRepository<StatisticsInfo, Long>{}

4、创建ExcelController，读取数据库，生成excel文件，并上传到AzureStorage

package com.xiaofangtech.sunt.controller;import java.io.ByteArrayInputStream;import java.io.ByteArrayOutputStream;import java.io.IOException;import java.text.SimpleDateFormat;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.UUID;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFDataFormat;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.usermodel.HSSFWorkbook;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import com.microsoft.azure.storage.blob.CloudBlobContainer;import com.microsoft.azure.storage.blob.CloudBlockBlob;import com.xiaofangtech.sunt.bean.StatisticsInfo;import com.xiaofangtech.sunt.repository.StatisticsRepository;import com.xiaofangtech.sunt.storage.BlobHelper;import com.xiaofangtech.sunt.storage.StorageConfig;import com.xiaofangtech.sunt.utils.ResultMsg;import com.xiaofangtech.sunt.utils.ResultStatusCode;@RestController@RequestMapping("excel")public class ExcelController {@Autowiredprivate StatisticsRepository statisticsRepository;@Autowiredprivate StorageConfig storageConfig;/*** * 创建表头 * @param workbook * @param sheet */private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet){HSSFRow row = sheet.createRow(0);//设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度sheet.setColumnWidth(2, 12*256);sheet.setColumnWidth(3, 17*256);//设置为居中加粗HSSFCellStyle style = workbook.createCellStyle();HSSFFont font = workbook.createFont();font.setBold(true);style.setAlignment(HSSFCellStyle.ALIGN_CENTER);style.setFont(font);HSSFCell cell;cell = row.createCell(0);cell.setCellValue("序号");cell.setCellStyle(style);cell = row.createCell(1);cell.setCellValue("金额");cell.setCellStyle(style);cell = row.createCell(2);cell.setCellValue("描述");cell.setCellStyle(style);cell = row.createCell(3);cell.setCellValue("日期");cell.setCellStyle(style);}/*** * 获取excel数据 * @return 返回文件名称及excel文件的URL * @throws IOException */@SuppressWarnings({ "unchecked", "rawtypes" })@RequestMapping("getExcel")public Object getExcel() throws IOException{HSSFWorkbook workbook = new HSSFWorkbook();HSSFSheet sheet = workbook.createSheet("统计表");createTitle(workbook, sheet);List<StatisticsInfo> entities = (List<StatisticsInfo>) statisticsRepository.findAll();//设置日期格式HSSFCellStyle style=workbook.createCellStyle();style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));//新增数据行，并且设置单元格数据int rowNum = 1;for (StatisticsInfo statisticsInfo:entities) {HSSFRow row = sheet.createRow(rowNum);row.createCell(0).setCellValue(statisticsInfo.getId());row.createCell(1).setCellValue(statisticsInfo.getMoney().toString());row.createCell(2).setCellValue(statisticsInfo.getDescription());HSSFCell cell = row.createCell(3);cell.setCellValue(statisticsInfo.getCurrentdate());cell.setCellStyle(style);rowNum++;}//拼装blobNameString fileName = "测试数据统计表.xlsx";SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");String dateTime = dateFormat.format(new Date());String blobName =  dateTime + "/" + UUID.randomUUID().toString().replaceAll("-", "") + "/" + fileName;//获取或创建containerCloudBlobContainer blobContainer = BlobHelper.getBlobContainer("temp", storageConfig);//设置文件类型，并且上传到azure blobtry {CloudBlockBlob blob = blobContainer.getBlockBlobReference(blobName);ByteArrayOutputStream out = new ByteArrayOutputStream();workbook.write(out);ByteArrayInputStream in = new ByteArrayInputStream(out.toByteArray());blob.upload(in, out.toByteArray().length);Map map = new HashMap();map.put("fileName", fileName);map.put("excelUrl", blob.getUri().toString());ResultMsg resultMsg = new ResultMsg(ResultStatusCode.OK.getErrcode(),ResultStatusCode.OK.getErrmsg(), map);return resultMsg;} catch (Exception e){ResultMsg resultMsg = new ResultMsg(ResultStatusCode.SYSTEM_ERR.getErrcode(),ResultStatusCode.SYSTEM_ERR.getErrmsg(), null);return resultMsg;}}}

5、运行测试

调用接口，获取生成excel后的url

直接使用excelURL下载excel文件，打开文件后如下所示

6、