项目中的需求
1.通过解析用户上传的excel向数据库中批量添加数据
2.用户下载excel模板
上传Excel模板
在controller中使用MultipartFile接收文件
/**
* @Description: 通过Excel批量添加微信号
* @return: String
* @Author: xpWang
* @Date: 2019-12-11
*/
@PostMapping("/addWeixinByExcel")
@ControllerLog(info = "add weixin list by Excel.",level = LogManager.LogLevel.INFO)
public String addWeixinByExcel(@RequestParam("file") MultipartFile file,@RequestParam("token") String token){
try {
if (null==file){
logger.error("the MultipartFile is null.");
return getErrorStringResponse("the file is null.");
}
return this.wxaccountConfigService.addWeixinListByExcel(file,token);
} catch (Exception e) {
String info="WxAccountConfigController add weixin list exception : "+e;
logger.error(info);
return getErrorStringResponse(info+e.getMessage());
}
}
解析Excel工厂
/**
* @Description 解析Excel工厂
* 传入MultipartFile，生成具体对象集合
* @author xpWang
* @date 2019/12/11 14:31
*/
public class ExcelAnalyzeFactory {
private static final String SUFFIX_2003 = ".xls";
private static final String SUFFIX_2007 = ".xlsx";
private static final DecimalFormat df = new DecimalFormat("0");// 格式化 number
private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
private static final DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
/**
* @Description 根据Excel内容批量添加微信
* @param file MultipartFile
* @return java.util.List<cn.com.taiji.system.vo.collectconfig.WeixinCrawlerRequest>
* @author xpWang
* @date 2019/12/11 14:28
*/
public static List<WeixinCrawlerRequest> analyzeWeixinExcel(MultipartFile file) throws Exception {
return (List<WeixinCrawlerRequest>)analyzeExcel(file,ExcelType.WEIXIN);
}
/**
* @Description 根据Excel内容批量添加微博
* @param file MultipartFile
* @return java.util.List<cn.com.taiji.system.vo.collectconfig.WeiboCrawlerRequest>
* @author xpWang
* @date 2019/12/11 14:30
*/
public static List<WeiboCrawlerRequest> analyzeWeiboExcel(MultipartFile file) throws Exception {
return (List<WeiboCrawlerRequest>)analyzeExcel(file,ExcelType.WEIBO);
}
/**
* @Description 根据Excel内容批量添加网站
* @param file MultipartFile
* @return java.util.List<cn.com.taiji.system.vo.collectconfig.WebsiteCrawlerRequest>
* @author xpWang
* @date 2019/12/11 14:30
*/
public static List<WebsiteCrawlerRequest> analyzeWebExcel(MultipartFile file) throws Exception {
return (List<WebsiteCrawlerRequest>)analyzeExcel(file,ExcelType.WEB);
}
/**
* @Description 根据Excel内容批量添加频道
* @param file MultipartFile
* @return java.util.List<cn.com.taiji.system.vo.collectconfig.ChannelCrawlerRequest>
* @author xpWang
* @date 2019/12/11 14:30
*/
public static List<ChannelCrawlerRequest> analyzeChannelExcel(MultipartFile file) throws Exception {
return (List<ChannelCrawlerRequest>)analyzeExcel(file,ExcelType.CHANNEL);
}
/**
* @Description 验证文件有效性，同时生成对应POI操作类
* @param file MultipartFile
* @return org.apache.poi.ss.usermodel.Workbook
* @author xpWang
* @date 2019/12/11 14:32
*/
private static Workbook generalWorkbook(MultipartFile file) throws Exception {
if (file == null) {
throw new Exception("the multipartfile is null.");
}
//获取文件的名字
String originalFilename = file.getOriginalFilename();
try {
if (originalFilename.endsWith(SUFFIX_2003)) {
return new HSSFWorkbook(file.getInputStream());
} else if (originalFilename.endsWith(SUFFIX_2007)) {
return new XSSFWorkbook(file.getInputStream());
}else{
throw new Exception("the multipartfile "+originalFilename+" is invalid.");
}
} catch (Exception e) {
e.printStackTrace();
throw e;
}
}
/**
* @Description 解析excel
* @param file MultipartFile
* @param type excel类型
* @return java.util.List
* @author xpWang
* @date 2019/12/11 14:33
*/
private static List analyzeExcel(MultipartFile file,ExcelType type) throws Exception {
List result=new ArrayList();
Workbook wb=generalWorkbook(file);
int numOfSheets=wb.getNumberOfSheets();
for(int i=0;i<numOfSheets;i++){
Sheet sheet=wb.getSheetAt(i);
int lastRow=sheet.getLastRowNum();
for (int j=1;j<=lastRow;j++){
Row row=sheet.getRow(j);
Object obj=null;
switch (type){
case WEIXIN:
obj=buildWeixinVo(wb,row);
break;
case WEIBO:
obj=buildWeiboVo(wb,row);
break;
case WEB:
obj=buildWebsite(wb,row);
break;
case CHANNEL:
obj=buildChannel(wb,row);
break;
}
result.add(obj);
}
}
wb.close();
return result;
}
private static WeixinCrawlerRequest buildWeixinVo(Workbook wb, Row row){
WeixinCrawlerRequest wx=new WeixinCrawlerRequest();
wx.setWx_no((String) getCellValue(wb,row.getCell(1)));
wx.setWx_name(getCellValue(wb,row.getCell(0))+EnumConstant.CONNECT_FIELD+ getCellValue(wb,row.getCell(2)));
wx.setStatusString((String) getCellValue(wb,row.getCell(3)));
return wx;
}
private static WeiboCrawlerRequest buildWeiboVo(Workbook wb, Row row){
WeiboCrawlerRequest weibo=new WeiboCrawlerRequest();
weibo.setName( getCellValue(wb,row.getCell(0))+EnumConstant.CONNECT_FIELD+getCellValue(wb,row.getCell(1)));
weibo.setReal_name((String) getCellValue(wb,row.getCell(2)));
weibo.setUrl((String) getCellValue(wb,row.getCell(3)));
weibo.setPic((String) getCellValue(wb,row.getCell(4)));
weibo.setStatusString((String) getCellValue(wb,row.getCell(5)));
return weibo;
}
private static WebsiteCrawlerRequest buildWebsite(Workbook wb, Row row){
WebsiteCrawlerRequest web=new WebsiteCrawlerRequest();
//update 2020/1/4 网站取消分类字段
//web.setWname(getCellValue(wb,row.getCell(0))+EnumConstant.CONNECT_FIELD+getCellValue(wb,row.getCell(1)));
web.setWname((String) getCellValue(wb,row.getCell(0)));
web.setUrl((String) getCellValue(wb,row.getCell(1)));
web.setProvince((String) getCellValue(wb,row.getCell(2)));
web.setStatusString((String) getCellValue(wb,row.getCell(3)));
return web;
}
private static ChannelCrawlerRequest buildChannel(Workbook wb, Row row){
ChannelCrawlerRequest c=new ChannelCrawlerRequest();
c.setCname((String) getCellValue(wb,row.getCell(0))+EnumConstant.CONNECT_FIELD+getCellValue(wb,row.getCell(1)));
c.setUrl((String) getCellValue(wb,row.getCell(2)));
c.setWebTypeString((String) getCellValue(wb,row.getCell(3)));
c.setSpiderTypeString((String) getCellValue(wb,row.getCell(4)));
c.setMediaTypeString((String) getCellValue(wb,row.getCell(5)));
c.setCharset((String) getCellValue(wb,row.getCell(6)));
c.setWebsite_id(paseInt( getCellValue(wb,row.getCell(7))));
c.setInterval(paseInt( getCellValue(wb,row.getCell(8))));
c.setModel_id(paseInt( getCellValue(wb,row.getCell(9))));
c.setStatusString((String) getCellValue(wb,row.getCell(10)));
c.setPc((String) getCellValue(wb,row.getCell(11)));
c.setUrl_reg((String) getCellValue(wb,row.getCell(12)));
c.setIfReportString((String) getCellValue(wb,row.getCell(13)));
c.setSeed_charset((String) getCellValue(wb,row.getCell(14)));
return c;
}
private static Integer paseInt(Object obj){
try {
return Double.valueOf(obj.toString()).intValue();
} catch (NumberFormatException e) {
e.printStackTrace();
return 0;
}
}
/**
* 待修改方法，目前此方法效率不高 xpWang
* @Description 获取表格内容公共方法，便于修改
* @return java.lang.Object
* @author xpWang
* @date 2019/12/11 16:32
*/
private static Object getCellValue(Workbook wb, Cell cell) {
if (cell != null) {
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue();
case NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
return df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
//目前不存在小数情况，未作小数的处理
return nf.format(cell.getNumericCellValue());
} else {
return sdf.format( HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
case BOOLEAN:
return cell.getBooleanCellValue();
case BLANK:
return "";
case FORMULA:
// 格式单元格
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateFormulaCell(cell);
CellValue cellValue = evaluator.evaluate(cell);
return cellValue.getNumberValue();
default:
return cell.toString();
}
}
return null;
}
}
Excel类型enum
/**
* excel文件内容类型，如：微信，微博，网站，频道等
*/
public enum ExcelType {
WEIXIN("WEIXIN","weixin_module.xlsx"), WEIBO("WEIBO","weibo_module.xlsx"), WEB("WEB","website_module.xlsx"), CHANNEL("CHANNEL","channel_module.xlsx");
@Getter
private String typeName;
@Getter
private String fileName;
ExcelType(String name,String fileName){
this.typeName=name;
this.fileName=fileName;
}
public String getSystemFileName(){
return SystemConstant.dirPath+fileName;
}
}
下载Excel模板
在application.yml中配置下载路径
module:
path: classpath:document/
工具类中读取路径
public class SystemConstant {
public static final String COLLECT_TOKEN = "fcf0a6ae1ac94796baf152fd6a0b54a1";
public static String dirPath;
@Value("${module.path}")
public void setDirPath(String path){
dirPath=path;
}
}
下载模板controller
@RestController
@RequestMapping("/api/v1/module")
@Slf4j
public class ModuleDownloadController extends BaseCrawlerController {
@GetMapping( value = "/download/{type}")
public String excelDownload( HttpServletResponse res,@PathVariable("type") String type) {
ExcelType excelType= null;
try {
excelType = ExcelType.valueOf(type.toUpperCase());
} catch (IllegalArgumentException e) {
log.error("invalid parameter type\t"+type);
return getErrorStringResponse("invalid parameter type:\t"+type);
}
// 配置文件下载
res.setHeader("content-type", "application/octet-stream");
res.setContentType("application/octet-stream");
// 下载文件能正常显示中文
res.setHeader("Content-Disposition", "attachment; filename=" + excelType.getFileName());
byte[] buff = new byte[1024];
BufferedInputStream bis = null;
OutputStream os = null;
try {
os = res.getOutputStream();
File file= ResourceUtils.getFile(excelType.getSystemFileName());
System.out.println(file.getAbsolutePath());
bis = new BufferedInputStream(new FileInputStream(
file));
int i = bis.read(buff);
while (i != -1) {
os.write(buff, 0, buff.length);
os.flush();
i = bis.read(buff);
}
} catch ( IOException e ) {
log.error("download exception\t"+e);
return getErrorStringResponse("download exception\t"+e);
} finally {
if (bis != null) {
try {
bis.close();
} catch (IOException e) {
log.error("inputsteam close exception\t"+e);
}
}
}
log.info("Download completed successfully");
return "Download completed successfully";
}
}
4619
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
