<!-- 內建easypoi元件 http://easypoi.mydoc.io/ .start --> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.2.0</version> </dependency> <!-- 內建easypoi元件http://easypoi.mydoc.io/ .end --> <!-- lombok可使代碼更簡潔 --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <scope>provided</scope> </dependency>
@Data //注解在類上, 為類提供讀寫屬性, 此外還提供了 equals()、hashCode()、toString() 方法
@NoArgsConstructor //生成一個無參的構造函數
@AllArgsConstructor //生成一個有參構造函數 注意實體類中一定要有空構造函數
public class ExportPtRate {
private static final long serialVersionUID = 1L;
@Excel(name = "性别", orderNum = "0")
private String gender;
@Excel(name = "年齡", orderNum = "1")
private String age;
@Excel(name = "交費期", orderNum = "2")
private String payendyearFactor;
@Excel(name = "保障期", orderNum = "3")
private String insuyearFactor;
@Excel(name = "職業", orderNum = "4")
private String occupation;
@Excel(name = "有無社保", orderNum = "5")
private String socialSec;
@Excel(name = "保費", orderNum = "6")
private String premium;
}
package com.ssish.saas.utils;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
/**
* @author
* @Description TODO
* @date 2019/8/30 15:34
*/
public class EasyPoiUtils {
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response) throws Exception {
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response) throws Exception {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws Exception {
defaultExport(list, fileName, response);
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws Exception {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws Exception {
try {
//response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
/*throw new NormalException(e.getMessage());*/
/*e.printStackTrace();*/
throw new Exception(e.getMessage());
}
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws Exception {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
if (workbook != null);
downLoadExcel(fileName, response, workbook);
}
public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass) throws Exception {
if (StringUtils.isBlank(filePath)){
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
}catch (NoSuchElementException e){
throw new Exception("模闆不能為空");
}catch (Exception e) {
/*throw new NormalException(e.getMessage());*/
/*e.printStackTrace();*/
throw new Exception(e.getMessage());
}
return list;
}
/**
*
* @param file
* @param titleRows 标題行數
* @param headerRows 表頭行數
* @param pojoClass
* @param <T>
* @return
* @throws Exception
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
}catch (NoSuchElementException e){
throw new Exception("excel檔案不能為空");
} catch (Exception e) {
/*throw new NormalException(e.getMessage());*/
/*e.printStackTrace();*/
throw new Exception(e.getMessage());
}
return list;
}
public static void main(String[] args) {
//String filePath = "d:\\flb.xls";
//解析excel,
/*List<Person> personList = importExcel(filePath,1,1,Person.class);
//也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)導入
System.out.println("導入資料一共【"+personList.size()+"】行");*/
//導出excel
/*List<ExportPtRate> userList = getUserList();
EasyPoiUtils.exportExcel(userList, "使用者清單", "使用者報表", ExportPtRate.class, "使用者明細報表.xls", response);*/
}
}
@RequestMapping(value = "/importExcel")
@ResponseBody
public BaseResult<String> importExcel(@RequestParam("file") MultipartFile file) {
BaseResult<String> baseResult = new BaseResult<String>();
try {
List<ExportPtRate> list= EasyPoiUtils.importExcel(file,0,1, ExportPtRate.class);
/*for (ExportPtRate rate : list) {
// System.out.println(User);
logger.info("從Excel導入資料到資料庫的詳細為 :{}", JSONObject.toJSONString(rate));
//TODO 将導入的資料做儲存資料庫操作
}*/
if(list==null || list.size()==0){
BaseResult<String> result = new BaseResult<>();
result.setReturnCode(ReturnMsg.UNKONOW_ERROR.getCode());
result.setReturnMsg("導入的資料不能為空");
return result;
}
logger.info("從Excel導入資料一共 {} 行 ", list.size());
baseResult = riskService.exportFeilvExcel(list);
}catch (Exception e1) {
logger.error("導入失敗:{}", e1.getMessage());
baseResult.setReturnCode(ReturnMsg.UNKONOW_ERROR.getCode());
baseResult.setReturnMsg(e1.getMessage());
}
return baseResult;
}
//導出excel:
@RequestMapping("/risk/exportExcel") public void exportExcel(DataTableParam dataTableParam, HttpServletResponse response) throws Exception { BaseResult<List<ExportPtRate>> baseResult = new BaseResult<List<ExportPtRate>>(); String coreUserId = String.valueOf(securityHelper.getCoreUserId()); String userType = securityHelper.getUserType(); Map<String, Object> map = getPtParams(dataTableParam); map.put("coreUserId", coreUserId); map.put("userType",userType); try { baseResult = riskService.getriskList(map); if(baseResult.getContent()!=null && baseResult.getContent().size()>0){ List<ExportPtRate> list = baseResult.getContent();
//導出excel EasyPoiUtils.exportExcel(list, null, null, ExportPtRate.class, "險種費率報表.xls", response); } } catch (Exception e) { e.printStackTrace(); } }
.編寫html頁面和異步函數
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript" src="../js/jquery-1.11.3.min.js"></script>
<script type="text/javascript">
function importExp() {
var formData = new FormData();
var name = $("#upfile").val();
formData.append("file",$("#upfile")[0].files[0]);
formData.append("name",name);
$.ajax({
url : '/lms/excelImportLock',
type : 'POST',
async : false,
data : formData,
// 告訴jQuery不要去處理發送的資料
processData : false,
// 告訴jQuery不要去設定Content-Type請求頭
contentType : false,
beforeSend:function(){
console.log("正在進行,請稍候");
},
success : function(data) {
alert(data.returnMsg);
}
});
}
</script>
</head>
<body>
<ul>
<li>
<span>上 傳:</span>
<span class="input">
<input type="file" id="upfile" name="upfile" placeholder=""/>
</span>
<button onclick="importExp();">導入</button>
<span>格式:.xls</span>
</li>
</ul>
</body>
</html>