Spring Boot实战之导出excel
来源:互联网 发布:程序员基础知识 编辑:程序博客网 时间:2024/06/03 17:46
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;
-
-
-
-
-
-
- private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet)
- {
- HSSFRow row = sheet.createRow(0);
-
- 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);
- }
-
-
-
-
-
-
- @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++;
- }
-
-
- String fileName = "测试数据统计表.xlsx";
- SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
- String dateTime = dateFormat.format(new Date());
- String blobName = dateTime + "/" + UUID.randomUUID().toString().replaceAll("-", "") + "/" + fileName;
-
-
- CloudBlobContainer blobContainer = BlobHelper.getBlobContainer("temp", storageConfig);
-
- 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文件,打开文件后如下所示