Excel表导入导出接口写法

EricFRQ

已于 2023-11-27 12:32:39 修改

阅读量3k

点赞数 4

分类专栏： springboot项目相关文章标签： Excel表导入导出接口

于 2019-11-27 10:49:20 首次发布

本文链接：https://blog.csdn.net/weixin_42707397/article/details/103271046

版权

springboot项目相关专栏收录该内容

43 篇文章 3 订阅

订阅专栏

2022年7月18日补充：现在有个更方便的API操作excel，下面的博客可以被淘汰了。参考：（1）、easyExcel类（Excel解析工具）

或者使用方式（2）easypoi，实现一对多效果如下：

在这里插入图片描述
具体使用步骤如下：
（1）、添加依赖

<!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-base -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.2.0</version>
</dependency>

（2）实体类添加@Excel注解
一对多的一实体类

//import cn.afterturn.easypoi.excel.annotation.Excel;

@Excel(name = "用户名", width = 30, needMerge = true)//needMerge 是否合并单元格
private String username;

@Excel(name = "状态", replace = {"在线_0", "离线_1"}, needMerge = true)//replace 状态值为0替换为在线 状态值为1替换成离线
private Integer status;

@Excel(name = "创建时间", format = "yyyy-MM-dd", width = 25, needMerge = true)//format 格式化时间yyyy-MM-dd
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") // 前端时间字符串转java时间戳
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") // 后台时间戳转前端时间字符串(json对象)
private Date createTime;

@ExcelCollection(name = "用户权限")//一对多中的多个属性
private List<SysRole> roles;

一对多的多实体类

@Excel(name = "权限名称", width = 30)
private String roleName;

@Excel(name = "权限描述", width = 30)
private String roleDesc;

(3)导出业务代码

//模拟获取导出数据方法
private List<SysUser> getDatas() {
	List<SysRole> rList1 = new ArrayList<>();
	rList1.add(new SysRole(1,"update","修改"));
	rList1.add(new SysRole(2,"select","查询"));
	rList1.add(new SysRole(3,"add","添加"));
	SysUser sysUser1 = new SysUser();
	sysUser1.setUsername("admin");
	sysUser1.setStatus(0);
	sysUser1.setCreateTime(new Date());
	sysUser1.setRoles(rList1);
	
	List<SysRole> rList2 = new ArrayList<>();
	rList2.add(new SysRole(4,"delete","删除"));
	rList2.add(new SysRole(2,"select","查询"));
	SysUser sysUser2 = new SysUser();
	sysUser2.setUsername("EricFRQ");
	sysUser2.setStatus(0);
	sysUser2.setCreateTime(new Date());
	sysUser2.setRoles(rList2);
	
	List<SysUser> data = new ArrayList<>();
	data.add(sysUser1);
	data.add(sysUser2);
	return data;
}

/**
* @return
 * @Description 测试导出excel
 * @Time 2023年11月27日
 * @Author Eric
 */
@Log("测试导出excel")
@ApiOperation(value = "测试导出excel", httpMethod = "GET")
@GetMapping("/excel")
public void excel(HttpServletResponse response) {
    List<SysUser> data = getDatas();
    try {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = null;
        fileName = URLEncoder.encode("用户信息", "utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
        ExcelExportUtil.exportExcel(new ExportParams(), SysUser.class, data).write(response.getOutputStream());
    } catch (IOException e) {
        e.printStackTrace();
    }
}

效果如下：
在这里插入图片描述

--------------------------无特殊需要下面方式可以淘汰了-------------------------------------------

所需依赖：

	<!-- XSSF -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml</artifactId>
		    <version>3.17</version>
		</dependency>
		
		 <!--文件上传 -->
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>2.2</version>
		</dependency>
		<dependency>
			<groupId>commons-fileupload</groupId>
			<artifactId>commons-fileupload</artifactId>
			<version>1.3.3</version>
		</dependency>

所需工具类（自己新建类）：

package com.ws.ssms.business.util;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.NumberToTextConverter;

public class ExcelUtil {
	
	 /** 
     * 根据excel单元格类型获取excel单元格值 
     * @param cell 
     * @return 
     */  
	public static String getCellValue(Cell cell) {  
        String cellvalue = "";  
        if (cell != null) {  
            // 判断当前Cell的Type  
            switch (cell.getCellTypeEnum()) {  
	            // 如果当前Cell的Type为NUMERIC  
	            case NUMERIC: {  
	                short format = cell.getCellStyle().getDataFormat();  
	                if(format == 14 || format == 31 || format == 57 || format == 58){   //excel中的时间格式  
	                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");    
	                    double value = cell.getNumericCellValue();    
	                    Date date = DateUtil.getJavaDate(value);    
	                    cellvalue = sdf.format(date);    
	                }  
	                // 判断当前的cell是否为Date  
	                else if (HSSFDateUtil.isCellDateFormatted(cell)) {  //先注释日期类型的转换，在实际测试中发现HSSFDateUtil.isCellDateFormatted(cell)只识别2014/02/02这种格式。  
	                    // 如果是Date类型则，取得该Cell的Date值           // 对2014-02-02格式识别不出是日期格式  
	                    Date date = cell.getDateCellValue();  
	                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");  
	                    cellvalue= formater.format(date);  
	                } else { // 如果是纯数字  
	                    // 取得当前Cell的数值  
	                    cellvalue = NumberToTextConverter.toText(cell.getNumericCellValue());   
	                }  
	                break;  
	            }  
	            // 如果当前Cell的Type为STRIN  
	            case STRING:  
	                // 取得当前的Cell字符串  
	                cellvalue = cell.getStringCellValue().replaceAll("'", "''");  
	                break;  
	            case BLANK:  
	                cellvalue = null;  
	                break;  
	            // 默认的Cell值  
	            default:{  
	                cellvalue = " ";  
	            }  
            }  
        } else {  
            cellvalue = "";  
        }  
        return cellvalue;  
    }
}

一、导出

1.controller层：

@RestController
@RequestMapping("/pay****Log")
@Api(tags = "缴费记录管理类")
public class ****Controller {
/**
	 * @Description 导出集中器信息
	 * @return
	 * @Time 2019年11月26日
	 * @Author Eric
	 */
	@ApiOperation(value = "导出Excle", httpMethod = "GET")
	@ApiImplicitParams({ @ApiImplicitParam(name = "name", value = "检索条件-集中器名称", dataType = "String"),
			@ApiImplicitParam(name = "address", value = "检索条件-集中器地址", dataType = "String"),
			@ApiImplicitParam(name = "status", value = "检索条件-集中器状态", dataType = "Integer") })
	@GetMapping("/export")
	@RequiredPermission("concentrator:view")
	public void exportConcentratorToExcel(HttpServletRequest request, HttpServletResponse response, String name,
			String address, Integer status) throws Exception {
		// 从session获取用户
		HttpSession session = request.getSession();
		String json = (String) session.getAttribute("ws_admin_user");
		User user = JSON.parseObject(json, User.class);
		SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss");
		String nameDate = URLEncoder.encode("集中器列表" + formatter.format(new Date()), "UTF-8");// excel名称
		if (nameDate != "") {
			response.reset(); // 清除buffer缓存
			// 指定下载的文件名
			response.setHeader("Content-Disposition", "attachment;filename=" + nameDate + ".xlsx");
			response.setContentType("application/vnd.ms-excel;charset=UTF-8"); // 下载文件类型
			// 不要缓存
			response.setHeader("Pragma", "no-cache"); // Pragma(HTTP1.0)
			response.setHeader("Cache-Control", "no-cache"); // Cache-Control(HTTP1.1)
			response.setDateHeader("Expires", 0); // Expires:过时期限值

			XSSFWorkbook workbook = null;
			// 导出Excel对象
			List<Organization> organizationList = organizationService.getOrganizationList(user.getOrganizationid());
			workbook = concentratorService.export(organizationList, name, address, status);
			OutputStream output = null;
			BufferedOutputStream bufferedOutPut = null;
			try {
				output = response.getOutputStream();
				bufferedOutPut = new BufferedOutputStream(output);
				workbook.write(bufferedOutPut);
				bufferedOutPut.flush();
				output.flush();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				if (output != null) {
					output.close();
					output = null;
				}
				if (bufferedOutPut != null) {
					bufferedOutPut.close();
					bufferedOutPut = null;
				}
			}
		}
		return;
	}
}

2、service的实现层（service层接口自己补充）：

    @SuppressWarnings("deprecation")
	@Override
	public XSSFWorkbook export(List<Organization> organizationList, String name, String address, Integer status) {
		List<Concentrator> list = concentratorMapper.getConcentratorList(name, address, status, organizationList);
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet();

		// 设置列宽
		sheet.setColumnWidth(0, 6000);
		sheet.setColumnWidth(1, 6000);
		sheet.setColumnWidth(2, 6000);

		// 设置字体
		XSSFFont headfont = workbook.createFont();
		headfont.setFontName("黑体");
		headfont.setFontHeight((short) 400);

		// 另一个样式
		XSSFCellStyle headstyle = workbook.createCellStyle();
		headstyle.setFont(headfont);
		headstyle.setAlignment(HorizontalAlignment.CENTER);
		headstyle.setVerticalAlignment(VerticalAlignment.JUSTIFY);
		headstyle.setLocked(true);
		headstyle.setWrapText(true);// 自动换行

		// 另一个字体样式
		XSSFFont columnHeadFont = workbook.createFont();
		columnHeadFont.setFontName("宋体");
		columnHeadFont.setFontHeight((short) 220);

		// 列标题的样式
		XSSFCellStyle columnHeadStyle = workbook.createCellStyle();
		columnHeadStyle.setFont(columnHeadFont);
		columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
		columnHeadStyle.setVerticalAlignment(VerticalAlignment.JUSTIFY);// 上下居中
		columnHeadStyle.setLocked(true);
		columnHeadStyle.setWrapText(true);
		columnHeadStyle.setLeftBorderColor(IndexedColors.BLACK.index);
		columnHeadStyle.setBorderLeft(BorderStyle.THIN);
		columnHeadStyle.setRightBorderColor(IndexedColors.BLACK.index);
		columnHeadStyle.setBorderRight(BorderStyle.THIN);
		columnHeadStyle.setTopBorderColor(IndexedColors.BLACK.index);
		columnHeadStyle.setBorderTop(BorderStyle.THIN);
		columnHeadStyle.setBottomBorderColor(IndexedColors.BLACK.index);
		columnHeadStyle.setBorderBottom(BorderStyle.THIN);

		XSSFFont font = workbook.createFont();
		font.setFontName("宋体");
		font.setFontHeight((short) 220);

		// 另一个样式
		XSSFCellStyle centerstyle = workbook.createCellStyle();
		centerstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		centerstyle.setFillForegroundColor(IndexedColors.WHITE.index);
		centerstyle.setFont(font);
		centerstyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
		centerstyle.setVerticalAlignment(VerticalAlignment.JUSTIFY);// 上下居中
		centerstyle.setWrapText(true);
		centerstyle.setLeftBorderColor(IndexedColors.BLACK.index);
		centerstyle.setBorderLeft(BorderStyle.THIN);
		centerstyle.setRightBorderColor(IndexedColors.BLACK.index);
		centerstyle.setBorderRight(BorderStyle.THIN);
		centerstyle.setTopBorderColor(IndexedColors.BLACK.index);
		centerstyle.setBorderTop(BorderStyle.THIN);
		centerstyle.setBottomBorderColor(IndexedColors.BLACK.index);
		centerstyle.setBorderBottom(BorderStyle.THIN);

		// handling header.
		XSSFRow row0 = sheet.createRow(0);
		row0.setHeight((short) 300);
		XSSFCell cell = row0.createCell(0);
		cell.setCellValue("集中器ID");
		cell.setCellStyle(columnHeadStyle);

		cell = row0.createCell(1);
		cell.setCellValue("集中器地址");
		cell.setCellStyle(columnHeadStyle);

		cell = row0.createCell(2);
		cell.setCellValue("集中器类型");
		cell.setCellStyle(columnHeadStyle);

		int rowIndex = 1;
		for (int i = 0; i < list.size(); i++) {
			Concentrator item = list.get(i);
			XSSFRow dataRow = sheet.createRow(rowIndex);
			dataRow.setHeight((short) 300);

			cell = dataRow.createCell(0);
			cell.setCellValue(item.getConcentratorId());
			cell.setCellStyle(centerstyle);

			cell = dataRow.createCell(1);
			cell.setCellValue(item.getAddress());
			cell.setCellStyle(centerstyle);

			cell = dataRow.createCell(2);
			cell.setCellValue(item.getType());
			cell.setCellStyle(centerstyle);
			
			/**时间类型
			/*cell = dataRow.createCell(6);
			/*cell.setCellValue(item.getIntime().toLocaleString());
			/*cell.setCellStyle(centerstyle);
			*/
			rowIndex++;
		}

		return workbook;
	}
}

二、导入

1、controller层：

/**
	 * @Description 导入集中器
	 * @return
	 * @Time 2019年11月26日
	 * @Author Eric
	 */
	@ApiOperation(value = "导入集中器", httpMethod = "POST")
	@ApiImplicitParams({ @ApiImplicitParam(name = "excelfile", value = "excel文件", dataType = "CommonsMultipartFile") })
	@ApiResponses({ @ApiResponse(code = 1, message = "导入成功"), @ApiResponse(code = 0, message = "导入失败") })
	@PostMapping(value = "/importConcentrator", headers = ("content-type=multipart/*"))
	@RequiredPermission("concentrator:view")
	public ResponseData importConcentrator(@ApiParam(value = "file", required = true) MultipartFile file,
			HttpServletRequest request) throws Exception {
		return concentratorService.importConcentrator(file);
	}

2、service实现层：

@Override
	public ResponseData importConcentrator(MultipartFile file) throws Exception {
		Workbook workbook = null;
		InputStream is = file.getInputStream();
		String name = file.getOriginalFilename();
		if (name.endsWith("xlsx")) {
			workbook = new XSSFWorkbook(is);
		} else if (name.endsWith("xls")) {
			workbook = new HSSFWorkbook(is);
		} else {
			return new ResponseData(ResponseEnum.ERROR.getCode(), "文件格式不正确", null);
		}
		Sheet sheet = workbook.getSheetAt(0);
		int maxIndex = sheet.getLastRowNum();
		// 表头校验
		String[] headArr = { "集中器地址", "集中器类型", "集中器型号"};
		Row headRow = sheet.getRow(0);
		for (int i = 0; i < headArr.length; i++) {
			Cell cell = headRow.getCell(i);
			if (!headArr[i].equals(cell.getStringCellValue())) {
				if (null != workbook) {
					workbook.close();
				}
				return new ResponseData(ResponseEnum.ERROR.getCode(), "表头格式不正确", null);
			}
		}
		String returnMsg = "";
		List<Concentrator> list = new ArrayList<Concentrator>();
		for (int index = 1; index <= maxIndex; index++) {
			Concentrator concentrator = new Concentrator();
			Row row = sheet.getRow(index);
			String address = ExcelUtil.getCellValue(row.getCell(0));
			String type = ExcelUtil.getCellValue(row.getCell(1));
			String model = ExcelUtil.getCellValue(row.getCell(2));
			if (address == null || "".equals(address)) {
				returnMsg += "第" + (index + 1) + "行：【集中器地址】列不能为空;";
			} else if (type == null || "".equals(type)) {
				returnMsg += "第" + (index + 1) + "行：【集中器类型】列不能为空;";
			} else if (model == null || "".equals(model)) {
				returnMsg += "第" + (index + 1) + "行：【集中器型号】列不能为空;";
			} else {
				concentrator.setAddress(address);
				concentrator.setType(Integer.parseInt(type));
				concentrator.setModel(Integer.parseInt(model));
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				equipmentFile.setInstallationTime(sdf.parse(installationTime));
				list.add(concentrator);
			}
		}

		if (null != workbook) {
			workbook.close();
		}
		if (list.size() == 0) {
			return new ResponseData(ResponseEnum.ERROR.getCode(), "导入失败", returnMsg);
		}
		int result = 0;
		for (Concentrator con : list) {
			result = concentratorMapper.insertOneConcentrator(con);
		}
		if (result > 0) {
			return new ResponseData(ResponseEnum.SUCCESS.getCode(), "导入成功", returnMsg);
		} else {
			return new ResponseData(ResponseEnum.ERROR.getCode(), "导入失败", returnMsg);
		}
	}
	 /*
        关于表头校验的另外一种方式，此方式可以不规定表头顺序，包含即可
        // 1、表头校验，先放入MAP，再判断
        String[] headArr = {"ADDNAME", "LON", "LAT", "ADDCODE", "COLDATE", "CLASID"};
        for (String head : headArr) {
            if (!hashMap.containsKey(head)) {
                if (null != workbook) {
                    workbook.close();
                }
                return new HttpResponse(false, "表头格式不正确,必须有ADDNAME、LON、LAT、ADDCODE、COLDATE、CLASID");
            }
        }
        2、取值
        String colDate = ExcelUtil.getCellValue(row.getCell(hashMap.get("COLDATE")));
        * */

mapper

package com.ws.wssp.elec.business.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.ws.wssp.elec.business.model.Customer;
import com.ws.wssp.elec.business.model.ElecChangeLog;
import com.ws.wssp.elec.business.model.ElecMeterParams;
import com.ws.wssp.elec.business.model.Electricmeter;
import com.ws.wssp.elec.business.model.TreeNode;

public interface ElectricmeterMapper {

	List<Electricmeter> getElectricmeterList(@Param("meterAddress") String meterAddress,
			@Param("status") Integer status, @Param("organizations") String organizations);

	int insertOneElectricmeter(Electricmeter electricmeter);

	int deleteOneElectricmeter(@Param("fileId") int fileId);

	int editOneElectricmeter(Electricmeter electricmeter);

	Integer getMaxMeasuredPoint();

	Electricmeter getElectricmeterById(@Param("fileId") int fileId);

	List<Electricmeter> getMeterSwitchList(@Param("meterAddress") String meterAddress,
			@Param("customerList") List<Customer> customerList, @Param("organizations") String organizations);

	List<Electricmeter> getElectricmeterByCustomerId(@Param("meterAddress") String meterAddress,
			@Param("status") Integer status, @Param("customerListByRegionId") List<Customer> customerListByRegionId,
			@Param("organizations") String organizations);

	List<Electricmeter> getMeterSwitchListByCustomerId(@Param("meterAddress") String meterAddress,
			@Param("customerList") List<Customer> customerList,
			@Param("customerListByRegionId") List<Customer> customerListByRegionId,
			@Param("organizations") String organizations);

	Integer getElectricmeterCountByCustomerId(@Param("customerListByRegionId") List<Customer> customerListByRegionId,
			@Param("organizations") String organizations);

	Integer getValveStatusCountByCustomerId(@Param("customerListByRegionId") List<Customer> customerListByRegionId,
			@Param("organizations") String organizations);

	Integer getElectricmeterCount(@Param("organizations") String organizations);

	Integer getValveStatusCount(@Param("organizations") String organizations);

	List<ElecMeterParams> selectMeterParams(@Param("organizationList") List<Integer> organizationList,
			@Param("meterAddrs") String meterAddrs);

	// 修改水表状态为下发
	int updateElectricmeter(@Param("fileId") int fileId);

	/**
	 * @Description 根据设备地址获取电表信息
	 * @param meterAddress
	 *            设备地址
	 * @return
	 * @Time 2020年06月8日
	 * @Author lmn
	 */
	Electricmeter selectMeterByMeterAddress(@Param("meterAddress") String meterAddress);

	/**
	 * 更新软件版本号
	 * 
	 * @param meterAddress
	 * @param swver
	 * @return
	 * @Author lmn
	 */
	int updateSoftVersion(@Param("meterAddress") String meterAddress, @Param("softversion") String softversion);

	/**
	 * 修改电表开关阀状态
	 * 
	 * @param electricmeter
	 * @return
	 */
	int editValveStatus(Electricmeter electricmeter);

	List<TreeNode> selectAmmeterTreeNode(@Param("organizations") String organizations);

	/**
	 * 通过客户id查询电表
	 * 
	 * @param electricmeter
	 * @return
	 */
	List<Electricmeter> selectMeterByCutomerIds(@Param("customerIds") List<Integer> customerIds);

	Integer getValveStatusByCustomerId(@Param("customerId") Integer customerId);

	Electricmeter getmeterByCustomerId(@Param("customerId") Integer customerId);

	int updateTwoAmmeterTable(Electricmeter elecmeter);

	int changeAmmeter(ElecChangeLog elecLog);

	int updateAmmeterStatus(@Param("fileId") Integer fileId);

	Electricmeter getElectricmeterByMeterAddress(@Param("oldmeteraddress") String oldmeteraddress);

	/*
	 * 根据客户ID获取电表档案和明细内容
	 */
	List<ElecMeterParams> selectMeterParamsBycustomerIds(@Param("customerIds") String customerIds);

	List<Electricmeter> getElectricmeterByIds(@Param("fileIds") String fileIds);

}

xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.ws.wssp.elec.business.mapper.ElectricmeterMapper">
	<resultMap
		id="BaseResultMap"
		type="com.ws.wssp.elec.business.model.Electricmeter">
		<result
			column="FILEID"
			property="fileId"
			jdbcType="INTEGER" />
		<result
			column="METERADDRESS"
			property="meterAddress"
			jdbcType="VARCHAR" />
		<result
			column="MEASUREDPOINT"
			property="measuredPoint"
			jdbcType="INTEGER" />
		<result
			column="METERTYPE"
			property="meterType"
			jdbcType="INTEGER" />
		<result
			column="COMMUNICATION"
			property="communication"
			jdbcType="INTEGER" />
		<result
			column="BAUDRATE"
			property="baudrate"
			jdbcType="INTEGER" />
		<result
			column="PROTOCOL"
			property="protocol"
			jdbcType="INTEGER" />
		<result
			column="SWVER"
			property="swver"
			jdbcType="VARCHAR" />
		<result
			column="HWVER"
			property="hwver"
			jdbcType="VARCHAR" />
		<result
			column="COLLECTORADDRESS"
			property="collectorAddress"
			jdbcType="VARCHAR" />
		<result
			column="UPGOINGID"
			property="upGoingId"
			jdbcType="INTEGER" />
		<result
			column="INSTALLADDRESS"
			property="installAddress"
			jdbcType="VARCHAR" />
		<result
			column="COMMPASSWORD"
			property="commPassword"
			jdbcType="VARCHAR" />
		<result
			column="WORKINGDATE"
			property="workingDate"
			jdbcType="TIMESTAMP" />
		<result
			column="STATUS"
			property="status"
			jdbcType="INTEGER" />
		<result
			column="CUSTOMERID"
			property="customerId"
			jdbcType="INTEGER" />
		<result
			column="ORGANIZATIONID"
			property="organizationId"
			jdbcType="INTEGER" />
		<result
			column="ValveStatus"
			property="valveStatus"
			jdbcType="INTEGER" />
		<result
			column="Upgoingtype"
			property="upgoingType"
			jdbcType="INTEGER" />
		<result
			column="Intime"
			property="intime"
			jdbcType="TIMESTAMP" />
		<result
			column="Creator"
			property="creator"
			jdbcType="INTEGER" />
		<result
			column="Coordinate"
			property="coordinate"
			jdbcType="VARCHAR" />
		<result
			property="imei"
			column="imei"
			jdbcType="VARCHAR" />
		<result
			property="imsi"
			column="imsi"
			jdbcType="VARCHAR" />
		<result
			property="platform"
			column="platform"
			jdbcType="INTEGER" />
		<result
			property="password"
			column="password"
			jdbcType="VARCHAR" />
		<result
			property="handlerCode"
			column="handlerCode"
			jdbcType="VARCHAR" />
	</resultMap>


	<select
		id="getElectricmeterList"
		resultMap="BaseResultMap">
		select c.*,cd.imei imei,cd.imsi imsi,cd.platform
		platform,cd.Onenetdeviceid onenetDeviceId,cd.version version from
		file_electricmeter c,file_electricmeter_detail cd where
		c.FILEID=cd.FileID and c.status!=0
		<if test="meterAddress != null and meterAddress != ''">
			and c.meterAddress like
			concat(concat('%',#{meterAddress,jdbcType=VARCHAR}),'%')
		</if>
		<if test="status != null">
			and c.status=#{status,jdbcType=DECIMAL}
		</if>
		<if test="organizations != null and organizations != ''">
			and FIND_IN_SET(c.organizationid, #{organizations,jdbcType=VARCHAR})
		</if>
		order by c.fileId
	</select>

	<insert
		id="insertOneElectricmeter"
		useGeneratedKeys="true"
		keyProperty="fileId"
		parameterType="com.ws.wssp.elec.business.model.Electricmeter">
		insert into file_electricmeter
		<trim
			prefix="("
			suffix=")"
			suffixOverrides=",">
			<if test="measuredPoint != null">
				MEASUREDPOINT,
			</if>
			<if test="meterType != null">
				METERTYPE,
			</if>
			<if test="communication != null">
				COMMUNICATION,
			</if>
			<if test="baudrate != null">
				BAUDRATE,
			</if>
			<if test="protocol != null">
				PROTOCOL,
			</if>
			<if test="swver != null">
				SWVER,
			</if>
			<if test="hwver != null">
				HWVER,
			</if>
			<if test="collectorAddress != null">
				COLLECTORADDRESS,
			</if>
			<if test="upGoingId != null">
				UPGOINGID,
			</if>
			<if test="installAddress != null">
				INSTALLADDRESS,
			</if>
			<if test="commPassword != null">
				COMMPASSWORD,
			</if>
			WORKINGDATE,
			<if test="status != null">
				STATUS,
			</if>
			<if test="customerId != null">
				CUSTOMERID,
			</if>
			ORGANIZATIONID,
			<if test="valveStatus != null">
				ValveStatus,
			</if>
			<if test="meterAddress != null">
				METERADDRESS,
			</if>
			<if test="upgoingType != null">
				Upgoingtype,
			</if>
			<if test="creator != null">
				Creator,
			</if>
			<if test="coordinate != null">
				Coordinate,
			</if>
			<if test="password != null">
				password,
			</if>
		</trim>
		<trim
			prefix="values ("
			suffix=")"
			suffixOverrides=",">
			<if test="measuredPoint != null">
				#{measuredPoint,jdbcType=DECIMAL},
			</if>
			<if test="meterType != null">
				#{meterType,jdbcType=DECIMAL},
			</if>
			<if test="communication != null">
				#{communication,jdbcType=DECIMAL},
			</if>
			<if test="baudrate != null">
				#{baudrate,jdbcType=DECIMAL},
			</if>
			<if test="protocol != null">
				#{protocol,jdbcType=DECIMAL},
			</if>
			<if test="swver != null">
				#{swver,jdbcType=VARCHAR},
			</if>
			<if test="hwver != null">
				#{hwver,jdbcType=VARCHAR},
			</if>
			<if test="collectorAddress != null">
				#{collectorAddress,jdbcType=VARCHAR},
			</if>
			<if test="upGoingId != null">
				#{upGoingId,jdbcType=DECIMAL},
			</if>
			<if test="installAddress != null">
				#{installAddress,jdbcType=VARCHAR},
			</if>
			<if test="commPassword != null">
				#{commPassword,jdbcType=VARCHAR},
			</if>
			now(),
			<if test="status != null">
				#{status,jdbcType=DECIMAL},
			</if>
			<if test="customerId != null">
				#{customerId,jdbcType=DECIMAL},
			</if>
			#{organizationId,jdbcType=DECIMAL},
			<if test="valveStatus != null">
				#{valveStatus,jdbcType=DECIMAL},
			</if>
			<if test="meterAddress != null">
				#{meterAddress,jdbcType=VARCHAR},
			</if>
			<if test="upgoingType != null">
				#{upgoingType,jdbcType=DECIMAL},
			</if>
			<if test="creator != null">
				#{creator,jdbcType=DECIMAL},
			</if>
			<if test="coordinate != null">
				#{coordinate,jdbcType=DECIMAL},
			</if>
			<if test="password != null">
				#{password,jdbcType=DECIMAL},
			</if>
		</trim>
	</insert>

	<delete
		id="deleteOneElectricmeter"
		parameterType="java.lang.Integer">
		delete from file_electricmeter
		where FILEID =
		#{fileId,jdbcType=INTEGER}
	</delete>

	<update
		id="editOneElectricmeter"
		parameterType="com.ws.wssp.elec.business.model.Electricmeter">
		update file_electricmeter
		set
		METERADDRESS=#{meterAddress,jdbcType=VARCHAR},
		MEASUREDPOINT=#{measuredPoint,jdbcType=DECIMAL},
		METERTYPE=#{meterType,jdbcType=DECIMAL},
		COMMUNICATION=
		#{communication,jdbcType=DECIMAL},
		BAUDRATE=#{baudrate,jdbcType=DECIMAL},
		PROTOCOL=#{protocol,jdbcType=DECIMAL},
		SWVER=#{swver,jdbcType=VARCHAR},
		HWVER=#{hwver,jdbcType=VARCHAR},
		COLLECTORADDRESS=#{collectorAddress,jdbcType=VARCHAR},
		UPGOINGID=#{upGoingId,jdbcType=DECIMAL},
		INSTALLADDRESS=#{installAddress,jdbcType=VARCHAR},
		COMMPASSWORD=#{commPassword,jdbcType=VARCHAR},
		STATUS=#{status,jdbcType=DECIMAL},
		CUSTOMERID=#{customerId,jdbcType=DECIMAL},
		Upgoingtype=#{upgoingType,jdbcType=DECIMAL},
		Creator=#{creator,jdbcType=DECIMAL},
		Coordinate=#{coordinate,jdbcType=VARCHAR},
		password=#{password,jdbcType=VARCHAR}
		where
		FILEID=#{fileId,jdbcType=DECIMAL}
	</update>

	<select
		id="getMaxMeasuredPoint"
		resultType="java.lang.Integer">
		select MAX(c.MEASUREDPOINT) from file_electricmeter as c
	</select>

	<select
		id="getElectricmeterById"
		resultMap="BaseResultMap"
		parameterType="java.lang.Integer">
		select * from file_electricmeter where
		FILEID=#{fileId,jdbcType=DECIMAL}
	</select>

	<select
		id="getMeterSwitchList"
		resultMap="BaseResultMap">
		select c.*,cd.imei imei,cd.Onenetdeviceid onenetDeviceId from
		file_electricmeter c ,file_electricmeter_detail cd where
		c.FILEID=cd.FileID
		<if test="meterAddress != null and meterAddress != ''">
			and c.meterAddress like
			concat(concat('%',#{meterAddress,jdbcType=VARCHAR}),'%')
		</if>
		<if test="customerList != null">
			and
			<foreach
				collection="customerList"
				index="index"
				item="item"
				open="("
				close=")"
				separator="or">
				c.CUSTOMERID=#{item.customerId,jdbcType=DECIMAL}
			</foreach>
		</if>
		<if test="organizations != null and organizations != ''">
			and FIND_IN_SET(c.organizationid, #{organizations,jdbcType=VARCHAR})
		</if>
		order by c.fileId
	</select>

	<select
		id="getElectricmeterByCustomerId"
		resultMap="BaseResultMap">
		select c.*,cd.imei imei,cd.imsi imsi,cd.platform
		platform,cd.Onenetdeviceid onenetDeviceId,cd.version version from
		file_electricmeter c,file_electricmeter_detail cd where
		c.FILEID=cd.FileID
		<if test="meterAddress != null and meterAddress != ''">
			and c.meterAddress like
			concat(concat('%',#{meterAddress,jdbcType=VARCHAR}),'%')
		</if>
		<if test="status != null">
			and c.status=#{status,jdbcType=DECIMAL}
		</if>
		<if test="customerListByRegionId != null">
			and
			<foreach
				collection="customerListByRegionId"
				index="index"
				item="item"
				open="("
				close=")"
				separator="or">
				c.CUSTOMERID=#{item.customerId,jdbcType=DECIMAL}
			</foreach>
		</if>
		<if test="organizations != null and organizations != ''">
			and FIND_IN_SET(c.organizationid, #{organizations,jdbcType=VARCHAR})
		</if>
		order by c.fileId
	</select>

	<select
		id="getMeterSwitchListByCustomerId"
		resultMap="BaseResultMap">
		select c.*,cd.imei imei,cd.Onenetdeviceid onenetDeviceId from
		file_electricmeter c ,file_electricmeter_detail cd where
		c.FILEID=cd.FileID
		<if test="meterAddress != null and meterAddress != ''">
			and c.meterAddress like
			concat(concat('%',#{meterAddress,jdbcType=VARCHAR}),'%')
		</if>
		<if test="customerList != null">
			and
			<foreach
				collection="customerList"
				index="index"
				item="item"
				open="("
				close=")"
				separator="or">
				c.CUSTOMERID=#{item.customerId,jdbcType=DECIMAL}
			</foreach>
		</if>
		<if test="customerListByRegionId != null">
			and
			<foreach
				collection="customerListByRegionId"
				index="index"
				item="item"
				open="("
				close=")"
				separator="or">
				c.CUSTOMERID=#{item.customerId,jdbcType=DECIMAL}
			</foreach>
		</if>
		<if test="organizations != null and organizations != ''">
			and FIND_IN_SET(c.organizationid, #{organizations,jdbcType=VARCHAR})
		</if>
		order by c.fileId
	</select>

	<select
		id="getElectricmeterCountByCustomerId"
		resultType="java.lang.Integer">
		select count(*) from file_electricmeter c where 1=1
		<if test="customerListByRegionId != null">
			and
			<foreach
				collection="customerListByRegionId"
				index="index"
				item="item"
				open="("
				close=")"
				separator="or">
				c.CUSTOMERID=#{item.customerId,jdbcType=DECIMAL}
			</foreach>
		</if>
		<if test="organizations != null and organizations != ''">
			and FIND_IN_SET(c.organizationid, #{organizations,jdbcType=VARCHAR})
		</if>
		order by c.fileId
	</select>

	<select
		id="getValveStatusCountByCustomerId"
		resultType="java.lang.Integer">
		select count(*) from file_electricmeter c where c.ValveStatus=0
		<if test="customerListByRegionId != null">
			and
			<foreach
				collection="customerListByRegionId"
				index="index"
				item="item"
				open="("
				close=")"
				separator="or">
				c.CUSTOMERID=#{item.customerId,jdbcType=DECIMAL}
			</foreach>
		</if>
		<if test="organizations != null and organizations != ''">
			and FIND_IN_SET(c.organizationid, #{organizations,jdbcType=VARCHAR})
		</if>
		order by c.fileId
	</select>

	<select
		id="getElectricmeterCount"
		resultType="java.lang.Integer">
		select count(*) from file_electricmeter c where 1=1
		<if test="organizations != null and organizations != ''">
			and FIND_IN_SET(c.organizationid, #{organizations,jdbcType=VARCHAR})
		</if>
		order by c.fileId
	</select>

	<select
		id="getValveStatusCount"
		resultType="java.lang.Integer">
		select count(*) from file_electricmeter c where c.ValveStatus=0
		<if test="organizations != null and organizations != ''">
			and FIND_IN_SET(c.organizationid, #{organizations,jdbcType=VARCHAR})
		</if>
		order by c.fileId
	</select>

	<select
		id="selectMeterParams"
		resultType="com.ws.wssp.elec.business.model.ElecMeterParams">
		select
		*
		from file_electricmeter fem
		left join file_electricmeter_detail fed on fem.FILEID=fed.FileID
		where 1=1
		<if test="organizationList != null">
			and fem.organizationid in
			<foreach
				collection="organizationList"
				index="index"
				item="item"
				open="("
				close=")"
				separator=",">
				#{item,jdbcType=DECIMAL}
			</foreach>
		</if>
		<if test="meterAddrs!=null and meterAddrs!=''">
			and FIND_IN_SET(fem.meteraddress, #{meterAddrs,jdbcType=VARCHAR})
		</if>
	</select>

	<!--修改水电表状态为下发 -->
	<update
		id="updateElectricmeter"
		parameterType="java.lang.Integer">
		update file_electricmeter set status = 2
		where fileId = #{fileId,jdbcType=DECIMAL}
	</update>

	<!-- 根据设备地址获取水表信息 -->
	<select
		id="selectMeterByMeterAddress"
		resultMap="BaseResultMap">
		select f.*
		from file_electricmeter f
		where f.status!=0
		and
		f.meterAddress = #{meterAddress,jdbcType=VARCHAR}
	</select>

	<!-- 根据权限获取电表树节点 -->
	<select
		id="selectAmmeterTreeNode"
		resultType="com.ws.wssp.elec.business.model.TreeNode">
		select concat('ammeter',r.fileid) as id, r.fileid as gid,
		r.meteraddress as name, r.meteraddress as text,
		concat('customer',r.customerid) as parentid, 3 as type, 2 as
		parenttype,
		'icon-ammeter' as iconcls from file_electricmeter r where
		r.STATUS > 0
		<if test="organizations != null and organizations != ''">
			and FIND_IN_SET(r.organizationid, #{organizations,jdbcType=VARCHAR})
		</if>
		order by r.fileid
	</select>

	<!--更新软件版本号 -->
	<update id="updateSoftVersion">
		update file_electricmeter set SWVER = #{softversion,jdbcType=VARCHAR}
		where METERADDRESS = #{meterAddress,jdbcType=VARCHAR} and status!=0
	</update>

	<!-- 通过客户id查询电表- -->
	<select
		id="selectMeterByCutomerIds"
		resultMap="BaseResultMap">
		select f.* from file_electricmeter f
		where f.status!=0 and f.customerid
		in
		<foreach
			collection="customerIds"
			index="index"
			item="item"
			open="("
			close=")"
			separator=",">
			#{item,jdbcType=DECIMAL}
		</foreach>
	</select>

	<!-- 通过客户id查询电表阀门状态- -->
	<select
		id="getValveStatusByCustomerId"
		resultType="java.lang.Integer">
		select ValveStatus from file_electricmeter
		where
		customerId=#{customerId,jdbcType=VARCHAR} and status!=0
	</select>


	<select
		id="getElectricmeterByMeterAddress"
		resultMap="BaseResultMap">
		select c.*,cd.* from file_electricmeter c ,file_electricmeter_detail cd
		where c.FILEID=cd.FileID
		and c.meterAddress=#{oldmeteraddress,jdbcType=VARCHAR}
	</select>

	<update
		id="updateTwoAmmeterTable"
		parameterType="com.ws.wssp.elec.business.model.Electricmeter">
		update file_electricmeter fem,file_electricmeter_detail fed
		set fem.METERADDRESS=#{meterAddress,jdbcType=VARCHAR},fem.`STATUS`=1,
		fed.Imei=#{imei,jdbcType=VARCHAR},fed.Imsi=#{imsi,jdbcType=VARCHAR},fed.Onenetdeviceid='',
		fed.Psk='',fed.version=null where fem.FILEID=fed.FileID and
		fem.FILEID=#{fileId,jdbcType=INTEGER}
	</update>

	<insert
		id="changeAmmeter"
		parameterType="com.ws.wssp.elec.business.model.ElecChangeLog">
		insert into dev_elecchangelog
		(Oldmeteraddress,Oldmetervalue,Newmeteraddress,Creater)
		values(#{oldmeteraddress,jdbcType=VARCHAR},#{oldmetervalue,jdbcType=DECIMAL},
		#{newmeteraddress,jdbcType=VARCHAR},#{creater,jdbcType=INTEGER})
	</insert>
	
	<update id="updateAmmeterStatus">
		UPDATE file_electricmeter set  STATUS=1 where fileid=#{fileId,jdbcType=INTEGER}
	</update>

	<!-- 通过客户id查询电表，一户一表控制- -->
	<select
		id="getmeterByCustomerId"
		resultMap="BaseResultMap">
		select * from file_electricmeter
		where
		customerId=#{customerId,jdbcType=VARCHAR} and status!=0
	</select>

	<select
		id="selectMeterParamsBycustomerIds"
		resultType="com.ws.wssp.elec.business.model.ElecMeterParams">
		select
		*
		from file_electricmeter fem
		left join file_electricmeter_detail fed on fem.FILEID=fed.FileID
		where 1=1
		<if test="customerIds!=null and customerIds!=''">
			and FIND_IN_SET(fem.customerid, #{customerIds,jdbcType=VARCHAR})
		</if>
	</select>

	<select
		id="getElectricmeterByIds"
		resultMap="BaseResultMap">
		select c.*,cd.imei imei,cd.imsi imsi,cd.platform
		platform,cd.Onenetdeviceid onenetDeviceId,cd.version version from
		file_electricmeter c,file_electricmeter_detail cd where
		c.FILEID=cd.FileID
		<if test="fileIds!=null and fileIds!=''">
			and FIND_IN_SET(c.fileId, #{fileIds,jdbcType=VARCHAR})
		</if>
	</select>
<update id="updateByPrimaryKeySelective" parameterType="com.ws.wssp.system.model.Organization">
		update SYS_ORGANIZATION
		<set>
			<if test="organizationcode != null">
				ORGANIZATIONCODE = #{organizationcode,jdbcType=VARCHAR},
			</if>
			<if test="organizationname != null">
				ORGANIZATIONNAME = #{organizationname,jdbcType=VARCHAR},
			</if>
			<if test="parentid != null">
				PARENTID = #{parentid,jdbcType=DECIMAL},
			</if>
			<if test="status != null">
				STATUS = #{status,jdbcType=DECIMAL},
			</if>
			<if test="compactor != null">
				COMPACTOR = #{compactor,jdbcType=DECIMAL},
			</if>
		</set>
		where ORGANIZATIONID = #{organizationid,jdbcType=DECIMAL}
	</update>
</mapper>

补充一个pgsql使用mybatis的xml，包含pgsql的迷糊查询、根据首字母排序和mysql的FIND_IN_SET等同用法

<select
		id="getDataResourcesByParams"
		resultMap="BaseResultMap">
		select * from tb_stock_data_resources 
		where f_id = ANY(STRING_TO_ARRAY(#{ids,jdbcType=VARCHAR}, ','))
		and is_del=0
		<if test="name != null and name != ''">
			and f_name like '%'||#{name,jdbcType=VARCHAR}||'%'
		</if>
		<if test="desc != null and desc != ''">
			and f_desc like '%'||#{desc,jdbcType=VARCHAR}||'%'
		</if>
		<if test="type == 1 or type == '1'">
			and (f_data_source_type =1 or f_data_source_type =2 or f_data_source_type =3)
		</if>
		<if test="type == 2 or type == '2'">
			and (f_data_source_type =4 or f_data_source_type =5)
		</if>
		<if test="orderType == 1 or orderType == '1'">
			order by f_create_time Desc
		</if>
		<if test="orderType == 2 or orderType == '2'">
			ORDER BY convert_to( f_name, 'gbk' );
		</if>
	</select>

	@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") // 前端时间字符串转java时间戳
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") // 后台时间戳转前端时间字符串(json对象)
	private Date workingDate;
	或者还有一种统一配置的方式：
	在application.yml配置文件中添加
spring:
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss

在这里插入图片描述

EricFRQ

关注

4
点赞
踩
12

收藏

觉得还不错? 一键收藏
1
评论
Excel表导入导出接口写法

一、导出1.controller层：/** * @Description 导出集中器信息 * @return * @Time 2019年11月26日 * @Author Eric */ @ApiOperation(value = "导出Excle", httpMethod = "GET") @ApiImplicitParams({ @ApiImplicitParam(n...
复制链接

扫一扫

专栏目录