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 {
	
	@Autowired
	private StatisticsRepository statisticsRepository;
	
	@Autowired
	private 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++;
		}
		
		//拼装blobName
		String fileName = "测试数据统计表.xlsx";
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
		String dateTime = dateFormat.format(new Date());
		String blobName =  dateTime + "/" + UUID.randomUUID().toString().replaceAll("-", "") + "/" + fileName;
		
		//获取或创建container
		CloudBlobContainer blobContainer = BlobHelper.getBlobContainer("temp", storageConfig);
		//设置文件类型，并且上传到azure blob
		try {
			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、