SpringBoot+AngularJS导出数据库数据到excel

由于本项目是使用Spring Boot+AngularJs的开发模式,以Excel的形式导出数据

首先是angularJs的代码：

	/**
	 * 导出excel模板
	 */
	$scope.exportExcelModule = function(){
		if($scope.systemId != null && $scope.systemId != undefined && $scope.systemId != ""){
			var param1 = {};
			param1.id = $scope.systemId;
			commonService.search("dataAndExcel/exportSystemModuleById.do", param1, function(returnValue) {
				if(returnValue.data.returnCode == 1) {
					location.href=returnValue.data.data;
				}
			});
		}else{
			$scope.showFlag = false;
			$('#myModal').show();
		}
	}

commonService.js

var commonServices = angular.module("CommonService",[]);

commonServices.factory('commonService', ['$http','$state',function($http,$state) {
	return {
		/*
		 * 根据查询条件检索操作
		 */
		search:function(url,param,callBack){
			$http({
				url: baseUrl+url,
				method: 'get',
				params: param
			}).then(function(data) {
				if(data.data.returnCode == 2){
					$state.go("login");
				}else{
					callBack(data);
				}
			},function(data){
				$state.go("error"); 
			});
		}
	}
}]);

后台代码：

package com.bjb.controller;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.bjb.common.ReturnJsonObject;
import com.bjb.dao.BdmsApplicationDao;
import com.bjb.dao.BdmsMetadataClassDao;
import com.bjb.entity.BdmsApplication;
import com.bjb.entity.BdmsMetadataClass;
import com.bjb.entity.BdmsUser;
import com.bjb.services.DataAndExcelService;
import com.bjb.services.OperationLogService;
import com.bjb.upload.FTPConfigure;
import com.bjb.upload.FTPUtil;
import com.bjb.util.LoginUtil;
import com.bjb.util.OperationLogUtil;

@RestController
@RequestMapping("dataAndExcel")
public class DataAndExcelController {
	
	@Autowired
	private FTPConfigure FTPConfigure;
	//读取配置文件
	@Value("${fileUrl}")
    private String fileUrl;
	@Resource
	private BdmsApplicationDao bdmsApplicationDao;
	@Resource
	private BdmsMetadataClassDao bdmsMetadataClassDao;
	@Resource
	private DataAndExcelService dataAndExcelService;
	@Resource
	private OperationLogService operationLogService;
	
	/**
	 * 根据系统ID导出该系统的模板
	 * @param id
	 * @return
	 * @throws Exception
	 * @author majm
	 * @date 2017年12月4日 下午3:50:16
	 */
	@RequestMapping("/exportSystemModuleById")
	public ReturnJsonObject exportSystemModuleById(Integer id) throws Exception {
		BdmsApplication application = bdmsApplicationDao.findById(id);
		String fileName = application.getApplicationName();
		
		if(application.getIsMakeModule()==1){
			return new ReturnJsonObject((Object)application.getMakeModuleUrl());
		}else{
			BdmsMetadataClass metadataClass = new BdmsMetadataClass();
			metadataClass.setSystemId(id);
			List<BdmsMetadataClass> metadataClassList = bdmsMetadataClassDao.findByDtoPage(metadataClass);
			
			ByteArrayOutputStream output = new ByteArrayOutputStream(); 
			
			String[] headers1 = { "资源地区ID", "资源类型ID", "资源名称", "资源路径", "资源封面路径", "资源编目状态" };
			String[] headers2 = new String[metadataClassList.size()];
			for(int i=0;i<headers2.length;i++){
				headers2[i] = metadataClassList.get(i).getMetadataClassInfo();
			}
			DataAndExcelService eeu = new DataAndExcelService();
			HSSFWorkbook workbook = new HSSFWorkbook();
			eeu.exportExcel(workbook, 0, fileName+"资源列表", headers1, null, output);
			eeu.exportExcel(workbook, 1, "元数据列表", headers2, null, output);
			//原理就是将所有的数据一起写入，然后再关闭输入流。
			workbook.write(output);
			byte[] b = output.toByteArray();
			
			InputStream sbs = new ByteArrayInputStream(b); 
			//拼装blobName 
			fileName=fileName+"数据导入模板.xls";
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");  
			String dateTime = dateFormat.format(new Date());  
			String blobName =  dateTime + "-" + fileName;
			FTPUtil.uploadFile(FTPConfigure, blobName, sbs);
			
			application.setIsMakeModule(1);
			application.setMakeModuleUrl(fileUrl+FTPConfigure.getDir()+"/"+blobName);
			bdmsApplicationDao.updatePartial(application);
			
			return new ReturnJsonObject((Object)(fileUrl+FTPConfigure.getDir()+"/"+blobName));
		}
	}
	
	@RequestMapping("/excelToData")
	public ReturnJsonObject addFile(@RequestParam(value = "file", required = true) MultipartFile file)
			throws Exception {
		MultipartFile fileM = file;
        InputStream inputStream = fileM.getInputStream();
        if(inputStream!=null) {
        	dataAndExcelService.excelDataToDb(inputStream);
        }
        
        BdmsUser operationUser = LoginUtil.getLoginUserFromSession();
        
		/**writeOperationLog(模块类型，操作日志标题，操作日志内容)******start*/
        String operationInfo = file.getOriginalFilename();
//		String operationInfo = "<p>"+OperationLogUtil.OPERATION_TYPE_CREATE+"数据源:</p>";
//		operationInfo += "<p><span>新增数据源:"+operationUser.getUserCode()+"</span></p>";
		operationLogService.writeOperationLog(14, "用户【"+operationUser.getUserCode()+"】在【数据管理系统】新增数据源", operationInfo,null,null);
		/**writeOperationLog(模块类型，操作日志标题，操作日志内容)******end*/
        
		return new ReturnJsonObject();
	}
}