Angularjs结合springMVC的Excel导入导出功能。
前些时间,因为项目需要用到angularjs和springmvc进行Excel表的导入导出功能,在网上找了好久,各种各样的答案都有,但都多少有些问题,但最后还是解决了,这篇文章就分享下我在项目中实现这个功能,供大家参考。
前期的angularjs的引入和springmvc的搭建就不在这里细说了,我后台是使用springboot就行便捷开发,没什么配置文件,推荐大家去学习下。
导出功能
直接上代码:
- html
<div class="detail-btn">
<input type="button" value="导出" class="btn btn-primary" ng-click="ExportPostList()">
</div>
就一个导出按钮,ng-click=”ExportPostList()”直接调用js的ExportPostList方法。
- js(angular)
/**
* 导出Excel模板
*/
$scope.ExportPostSensitiveWordsList = function () {
var url = "http://localhost:8080/post/export/Export";
$http({
url: url,
method: "POST",
data: $scope.queryByRequire, //需要带的参数
headers: {
'Content-type': 'application/json'//发送内容的类型,这是使用'application/json'
},
responseType: 'arraybuffer'//返回结果的类型,字节流
}).success(function (data, status, headers, config) {
var blob = new Blob([data], {type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"});//以二进制形式存储,并转化为Excel
var objectUrl = URL.createObjectURL(blob);
var now=new Date();
var fileName=now.toLocaleDateString() +' '+now.getHours()+'/'+now.getMinutes()+'/'+now.getSeconds()+"xxx"+ ".xls";//自定义导出excel表名字,这里使用日期
saveAs(blob, fileName);//这里使用了文件导出插件FileSaver.js
}).error(function (data, status, headers, config) {
Alert.error("导出失败!");
});
};
文件导出插件FileSaver.js可以直接在百度上下载。
3.Controller层
ExcelController.java
/**
* 导出帖子列表数据
*/
@RequestMapping(value = "/Export" , method = RequestMethod.POST)
public ModelAndView ExportPostList(ModelMap model,@RequestBody Book,book) {
List<Book> bookList = bookService.queryForDownload(book);//到数据库查询出需要导出的数据
model.addAttribute("bookList ",bookList );//把查询结果放到ModelMap中,在导出工具类ExcelViewList.java中使用
ExcelViewList excelViewList =new ExcelViewList();
return new ModelAndView(excelViewPostList,model);//调用了ExcelViewList并返回视图
}
ModelAndView的详细功能可以自行百度。
4.ExcelViewList .java(用于导出Excel表格属性的设定)
import com.einwin.post.cm.model.Book;
import com.einwin.post.cm.utils.EncodeExcelUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.servlet.view.document.AbstractXlsView;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* Created by Tony on 2017/8/9.
*/
public class ExcelViewPostList extends AbstractXlsView {
@Override
protected void buildExcelDocument(Map<String, Object> model,
Workbook workbook,
HttpServletRequest request,
HttpServletResponse response) throws Exception {
// change the file name
response.setContentType("application/x-msdownload");//返回的格式
response.setHeader("Content-Disposition", "attachment; filename=\"PostManageList.xls\"");//返回头属性
List<Book> bookManageList = (List<Book>) model.get("bookList ");//获取返回的数据
Sheet sheet = workbook.createSheet("PostList Detail");// 创建sheet并命名
sheet.setDefaultColumnWidth(30);//默认列的宽度
// 下面是设置表头的样式
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontName("Arial");
style.setFillForegroundColor(HSSFColor.BLUE.index);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
font.setBold(true);
font.setColor(HSSFColor.WHITE.index);
style.setFont(font);
// 设置表头每个字段名
Row header = sheet.createRow(0);
header.createCell(0).setCellValue("编号");
header.getCell(0).setCellStyle(style);
header.createCell(1).setCellValue("书名");
header.getCell(1).setCellStyle(style);
header.createCell(2).setCellValue("作者");
header.getCell(2).setCellStyle(style);
header.createCell(3).setCellValue("出版时间");
header.getCell(3).setCellStyle(style);
int rowCount = 1;
//进行赋值
for (int i = 0; i < bookManageList .size(); i++) {
Book bookList1 =postManageList.get(i);
Row userRow = sheet.createRow(rowCount++);
userRow.createCell(0).setCellValue(bookList1 .getCommunityName());
userRow.createCell(1).setCellValue(bookList1 .getTagText());
userRow.createCell(2).setCellValue(bookList1 .getContent());
userRow.createCell(3).setCellValue(bookList1 .getCreationDate().toString());
}
}
}
导入功能
- html
<div>
<form class="form-horizontal" id="form1" role="form" ng-submit="uploadFile()"
enctype="multipart/form-data">
<div>
<table>
<tr>
<td><input type="file" name="file" ng-model="document.fileInput" id="file"></td>
</tr>
</table>
</div>
<button type="submit" class="btn btn-primary">导入</button>
</form>
</div>
2.js
/**
* Excel批量导入
*/
$scope.uploadFile = function () {
var url = "localhost:8080/post/export/importListExcel";
var formData = new FormData();//使用FormData进行文件上传
formData.append("file", file.files[0]);//拿到当前文件
$http.post(url, formData, {
transformRequest: angular.identity,
headers: {'Content-Type': undefined}
}).success(function (data, status) {
....
}).error(function (data, status) {
....
});
};
3.Controller
/**
* Excel导入敏感词
*/
@RequestMapping(value = "/importListExcel" , method = RequestMethod.POST)
public int importPostSensitiveWordsListExcel(@RequestParam(value = "file") MultipartFile file) throws Exception {
InputStream in = null;
int data = 0;
int count = 0;
List<List<Object>> listob = null;
if (file.isEmpty()) {
throw new Exception("文件为空");
}
in = file.getInputStream();
listob = new ImportExcelUtil().getBankListByExcel(in, file.getOriginalFilename());//调用导入工具类ImportExcelUtil,把excel中的数据拿出来
in.close();
//将Excel的数据set进数据库
for (int i = 0; i < listob.size(); i++) {
List<Object> lo = listob.get(i);
PostList postList = new PostList();
postList .setWord(String.valueOf(lo.get(0)));
postList .setRemark(String.valueOf(lo.get(1)));
postList .init();
data = postSensitiveWordsListMapper.insert(postList );//导入数据库
count = data + count;
}
return count;
}
service层和连接数据库的操作这里就不多说了
4.导入工具类ImportExcelUtil.java
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
public class ImportExcelUtil {
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
/**
* 描述:获取IO流中的数据,组装成List<List<Object>>对象
* @param in,fileName
* @return
* @throws IOException
*/
public List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{
List<List<Object>> list = null;
//创建Excel工作薄
Workbook work = this.getWorkbook(in,fileName);
if(null == work){
throw new Exception("创建Excel工作薄为空!");
}
Sheet sheet = null;
Row row = null;
Cell cell = null;
list = new ArrayList<List<Object>>();
//遍历Excel中所有的sheet
for (int i = 0; i < work.getNumberOfSheets(); i++) {
sheet = work.getSheetAt(i);
if(sheet==null){continue;}
//遍历当前sheet中的所有行
for (int j = sheet.getFirstRowNum(); j <=sheet.getLastRowNum(); j++) {
row = sheet.getRow(j);
if(row==null||row.getFirstCellNum()==j){continue;}
//遍历所有的列
List<Object> li = new ArrayList<Object>();
for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
cell = row.getCell(y);
li.add(this.getCellValue(cell));
}
list.add(li);
}
}
work.close();
return list;
}
/**
* 描述:根据文件后缀,自适应上传文件的版本
* @param inStr,fileName
* @return
* @throws Exception
*/
public Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{
Workbook wb = null;
String fileType = fileName.substring(fileName.lastIndexOf("."));
System.out.print(fileType);
if(excel2003L.equals(fileType)){
wb = new HSSFWorkbook(inStr); //2003-
}else if(excel2007U.equals(fileType)){
wb = WorkbookFactory.create(inStr); /*new XSSFWorkbook(inStr); */ //2007+
}else{
throw new Exception("解析的文件格式有误!");
}
return wb;
}
/**
* 描述:对表格中数值进行格式化
* @param cell
* @return
*/
public Object getCellValue(Cell cell){
Object value = null;
DecimalFormat df = new DecimalFormat("0"); //格式化number String字符
SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化
DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if("General".equals(cell.getCellStyle().getDataFormatString())){
value = df.format(cell.getNumericCellValue());
}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
value = sdf.format(cell.getDateCellValue());
}else{
value = df2.format(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
value = "";
break;
default:
break;
}
return value;
}
}
导入功能上面已经给出,还有一种情况是带参数的文件上传,那怎么做呢?下面我简单说下:
带参数文件上传
1.html和上面一样
2.js
$scope.save = {//参数
prizeType: '',
name: '',
totalCount: '',
orderNo: '',
prizeIntegral:''
};
$scope.saveAndUpload = function () {
var formData = new FormData();
formData.append("file", file.files[0]);
formData.append("type", "img");//其他参数可以这样append到formData
angular.forEach($scope.save, function (val, key) {
formData.append(key, val);//这个是把$scope.save的参数append到formData
});
$http.post(url, formData, {
transformRequest: angular.identity,
headers: {'Content-Type': undefined,"token": $.cookie("accessToken")}
}).success(function (result) {
...
}).error(function (result) {
$uibModalInstance.close(result);
});
}
$scope.save = {//参数
no: '',
name: '',
total: '',
order: ''
};
3.Controller
/**
* 新增奖品
*/
@PostMapping("/savePrize")
public int savePrize(PriceInfo priceInfo, MultipartHttpServletRequest multipartHttpServletRequest) throws Exception {
result = getMapper().insert(priceInfo);//priceInfo可以拿到js传进来的 $scope.save参数
List uploadFiles = multipartHttpServletRequest.getFiles("file");//获取文件(多文件也可)
String[] types = multipartHttpServletRequest.getParameterValues("type");
String[] names = multipartHttpServletRequest.getParameterValues("name");//也可以通过getParameterValues获取参数
String[] remarks = multipartHttpServletRequest.getParameterValues("remark");
}
这里就说到带参数文件上传在后台怎么获取参数和文件,其他的请参考前面的内容。