Java 杂记

1. Joda-Time 和 Calendar

• 获取 上周开始时间 和 结束时间 (Joda-Time)。
• 设置 上周开始时间为0点0分0秒，结束时间为23点59分59秒(Calendar)。
• 上周 开始时间往前推 2 天(Calendar)，结束时间往后推 2 天(Calendar)。
• 获取上周结束时间对应 年月日(Calendar)。

import org.joda.time.DateTime;
import org.joda.time.LocalDate;

/**
  * 获取每周的开始日期和结束日期
  *
  * @param week 周期(0:本周,-1:上周,-2:上上周,1:下周,2:下下周, 依次类推)
  * @return 返回开始日期(date[0]), 结束日期(date[1])
  */
private Date[] getBeginAndEndOfTheWeek(int week) {
	DateTime dateTime = new DateTime();
	LocalDate date = new LocalDate(dateTime.plusWeeks(week));
	date = date.dayOfWeek().withMinimumValue();
	Date beginDate = date.toDate();
	Date endDate = date.plusDays(6).toDate();
	return new Date[]{beginDate, endDate};
}

// 获取上周的开始时间和结束时间
Date[] lastWeek = getBeginAndEndOfTheWeek(-1);
Date startTime = lastWeek[0], endTime = lastWeek[1];

Calendar calendar = Calendar.getInstance();
// 上周开始时间往前推 2 天(正常是星期一, 往前推两天就是星期六0点0分0秒, 默认就是0点0分0秒)
calendar.setTime(startTime);
calendar.add(Calendar.DAY_OF_MONTH, -2);    // 设置为前 2 天
startTime = calendar.getTime();

// 上周结束时间往后推 2 天(正常是星期日, 往后推两天就是星期二23点59分59秒)
calendar.setTime(endTime);
calendar.add(Calendar.DAY_OF_MONTH, 2);    // 设置为后 2 天
// 设置结束时间为 23点59分59秒
calendar.set(Calendar.HOUR_OF_DAY, 23);
calendar.set(Calendar.MINUTE, 59);
calendar.set(Calendar.SECOND, 59);
endTime = calendar.getTime();

// 获取上周结束时间对应年月日
calendar.setTime(endTime);
int year = calendar.get(Calendar.YEAR);
int month = calendar.get(Calendar.MONTH) + 1;	// 月份范围 0-11, 需要加 1
int day = calendar.get(Calendar.DAY_OF_MONTH);

2. 字符串相除保留两位小数

通过 NumberFormat (数字格式化类) 实现。

import java.text.NumberFormat;

String a = "18", b = "143";
NumberFormat numberFormat = NumberFormat.getInstance();
// 精确到小数点后2位
numberFormat.setMaximumFractionDigits(2);
String result = numberFormat.format((float) Integer.valueOf(a) / (float) Integer.valueOf(b) * 100) + "%";
System.out.println(result);

3. Map 集合两种高效遍历方法

3.1 通过 Entry 遍历(数据量较大时效率也高)

Map<String, Object> map = new HashMap<>();

Set<Map.Entry<String, Object>> entrySet = map.entrySet();
for(Map.Entry<String,Object> entry : entrySet) {
	System.out.println("key=" + entry.getKey() + ", value=" + entry.getValue());
}

3.2 通过 Iterator 迭代器 和 Entry 遍历

通过 Iterator 迭代器遍历 Map 集合，可以在遍历过程中调用 remove() 方法删除元素

Map<String, Object> map = new HashMap<>();

Iterator<Map.Entry<String,Object>> iterator = map.entrySet().iterator();
while(iterator.hasNext()){
	Map.Entry<String,Object> mapEntry = iterator.next();
	// 删除元素
	if ("del".equals(mapEntry.getKey())) iterator.remove();
	System.out.println("key=" + mapEntry.getKey() + ", value=" + mapEntry.getValue());
}

结：Map 集合遍历过程中需要删除元素，使用 4.2，否则使用 4.1。

4. List 集合操作

4.1 多个 List 集合结果合并

需求见下图

思路：

• 找出 3 个 List 集合中长度最大的集合，即 List1。
• 根据最大长度，循环遍历 3 个 List 集合，并把值写到新集合 List4 中。

// 假设 list1,list2,list3 均有数据
List<Test1> list1 = new ArrayList<>();	// Test1 类只有一个属性 A
List<Test2> list2 = new ArrayList<>();	// Test2 类有两个属性 B,C
List<Test3> list3 = new ArrayList<>();	// Test3 类有三个属性 D,E,F

// 新集合 list4
List<Test4> list4 = new ArrayList<>();	// Test4 类有四个属性 A,C,D,F

// 找出 3 个 List 集合中长度最大的集合
int a = list1.size(), b = list2.size(), c = list3.size();
int max = (a > b) ? a : b;
max = (max > c) ? max : c;
for(int i = 0; i < max; i++) {
	Test4 test = new Test4();
	if(i < a) {
		if(list1.get(i) != null && list1.get(i).getA() != null && list1.get(i).getA() != "")
			test.setA(list1.get(i).getA());
	}
	
	if(i < b) {
		if(list2.get(i) != null && list2.get(i).getC() != null && list2.get(i).getC() != "")
			test.setC(list2.get(i).getC());
	}
	
	if(i < c) {
		if(list3.get(i) != null && list3.get(i).getD() != null && list3.get(i).getD() != "")
			test.setD(list3.get(i).getD());
		if(list3.get(i) != null && list3.get(i).getF() != null && list3.get(i).getF() != "")
			test.setF(list3.get(i).getF());
	}
	
	list4.add(test);
}

4.2 判断 List 集合是否包含某个元素

通过 contains() 方法实现，List 集合包含某元素，返回 true，否则返回 false。

List<String> list = new ArrayList<>();	// 假设 List 集合有元素
String str = "new";
if(list.contains(str)) System.out.println("List 集合包含元素 " + str);

4.3 两个 List 集合合并

public static <T> List<T> mergeList(List<T> var1, List<T> var2) {
    List<T> result = var1.stream().collect(Collectors.toList());
    result.addAll(var2);
    return result;
}

4.4 比较两个 List 集合，找出不同的元素

public static <T> List<T> getDifferentElements(List<T> var1, List<T> var2) {
    List<T> result = new ArrayList<>();
    Map<T, Integer> map = new HashMap<>();

    for (T t : var1) {
        map.put(t, 1);
    }
    for (T t : var2) {
        if (map.get(t) != null) {
            map.put(t, 2);
            continue;
        } else {
            map.put(t, 1);
        }
    }
    for (Map.Entry<T, Integer> entry : map.entrySet()) {
        if (entry.getValue() == 1) {
            result.add(entry.getKey());
        }
    }

    return result;
}

4.5 找出单个 List 集合中的重复元素

public static <T> List<T> getDuplicateElements(List<T> var) {
    return var.stream()    // list 对应的 Stream
            .collect(Collectors.toMap(e -> e, e -> 1, (a, b) -> a + b)) // 获得元素出现频率的 Map，键为元素，值为元素出现的次数
            .entrySet().stream()    // 所有 entry 对应的 Stream
            .filter(entry -> entry.getValue() > 1)  // 过滤出元素出现次数大于 1 的 entry
            .map(entry -> entry.getKey())   // 获得 entry 的键（重复元素）对应的 Stream
            .collect(Collectors.toList());  // 转化为 List
}

4.6 List 集合双重排序

需求：List 集合中保存了小明、小张和小红当月每人的工资以及三人的工资和，要求按工资升序排列，并且三人工资和在最前。

List<SalaryInfo> list = new ArrayList<SalaryInfo>();
SalaryInfo xm = new SalaryInfo();
xm.setName("小明"); xm.setSalary("5000.65");
SalaryInfo xz = new SalaryInfo();
xz.setName("小张"); xz.setSalary("3000.84");
SalaryInfo xh = new SalaryInfo();
xh.setName("小红"); xh.setSalary("1000.33");
SalaryInfo hj = new SalaryInfo();
hj.setName("合计"); hj.setSalary("9001.82");
list.add(xm); list.add(xz); list.add(xh); list.add(hj);

Collections.sort(list, new Comparator<SalaryInfo>() {
    @Override
    public int compare(SalaryInfo record1, SalaryInfo record2) {
        if ("合计".equals(record1.getName()) || "合计".equals(record2.getName())) {
            return -2;
        } else {
            double num = Double.valueOf(record1.getSalary()) - Double.valueOf(record2.getSalary());
            if (num > 0) {
                return 1;
            } else if (num < 0) {
                return -1;
            } else {
                return 0;
            }
        }
    }
});

System.out.println(list.toString());

4.7 把 List 集合保存到 Redis 中，并定期更新

@Component
public class SaveListToRedisUtils {
    @Autowired
    private RedisTemplate redisTemplate;

    private static SaveListToRedisUtils saveListToRedisUtils;

    @PostConstruct
    public void init() {
        saveListToRedisUtils = this;
        saveListToRedisUtils.redisTemplate = this.redisTemplate;
    }

	public static void saveListToRedis(List list, String key) {
		// 移除旧数据
        saveListToRedisUtils.redisTemplate.delete(key);;
        // 保存新数据
        saveListToRedisUtils.redisTemplate.opsForList().rightPush(key, list);
	}
}

@Configuration
@EnableScheduling
public class UpdateListToRedis {
    @Autowired
    private StudentMapper studentMapper;

	// 每隔 30 分钟更新一次数据
    @Scheduled(cron = "0 0/30 * * * ?")
    public void SchedulerForQuantityTable() throws Exception {
        List<Student> studentList = studentMapper.listAllNoPage();
        SaveListToRedisUtils.saveListToRedis(studentList, "studentList");
    }
}

@Service
public class StudentServiceImpl implements StudentService {
    @Autowired
    private StudentMapper studentMapper;
    @Autowired
    private RedisTemplate redisTemplate;

	@Override
    public List<Student> getAllNoPage() throws Exception {
		List<Student> studentList = null;
		// 取出 redis 中保存的数据
		List<Object> studentTempList = redisTemplate.opsForList().range("studentList", 0, -1);
	    if (studentTempList != null && studentTempList.size() > 0) {
	        studentList = (List<Student>) studentTempList.get(0);
	    } else {	// redis 中没有数据，则从数据库中获取
	        studentList = studentMapper.listAllNoPage();
	    }
	    return studentList;
	}
}

注：List 集合中的对象(这里即 Student )，需要实现序列化接口，否则无法保存到 Redis 中。

5. MyBatis + PageHelper + Vue + ElementUI 实现数据分页展示

需要用到两个主要 jar 包：

• 分页插件：pagehelper-5.1.2.jar
• SQL 语法解析器：jsqlparser-1.0.jar

5.1 MyBatis 配置 PageHelper

<!-- 在 MyBatis 配置文件中加入下面配置 -->
<plugins>
	<plugin interceptor="com.github.pagehelper.PageInterceptor">
		<!--<property name="dialect" value="mysql"/>-->
		<property name="offsetAsPageNum" value="true"/>
		<property name="rowBoundsWithCount" value="true"/>
	</plugin>
</plugins>

5.2 自定义分页参数类 PageParam

import com.github.pagehelper.Page;

public class PageParam<E> {
    private String DEFAULT_ORDER = "ASC";	// 默认排序方式
    private int pageNum = 1;	// 当前页号, 从 1 开始
    private int pageSize = 10;	// 页面大小
    private String sort;	// 排序字段, 如根据 createTime 字段排序
    private String order;	// 排序方式, 如降序desc、升序asc
    private E conditionObj;	// 查询条件
    
    public Page convertPage() {
        int pageNum = this.pageNum;
        int rowsInt = this.pageSize;
        String sort = this.sort;
        String order = this.order;
        Page page = new Page(pageNum, rowsInt);
        if (sort != null && sort.trim().length() > 0) {
            page.setOrderBy(sort.trim() + " " + (order != null && order.trim().length() > 0 ? order.trim() : DEFAULT_ORDER));
        }
        return page;
    }

    public int getOffset() {
        return (this.pageNum - 1) * this.pageSize;
    }
    
    // 此处省略构造方法、Getter 方法和 Setter 方法
}

5.3 实体 Student(继承自 PageParam)

import com.fasterxml.jackson.annotation.JsonFormat;
import org.springframework.format.annotation.DateTimeFormat;
import java.util.Date

public class Student extends PageParam {
    private String studentNo;

    private String studentName;

   /**
	* 入学时间格式化为 年月日
	*/
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date admissionTime;
    
     // 此处省略构造方法、Getter 方法和 Setter 方法
}

5.4 StudentServiceImpl(此处省略 StudentService)

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;

@Service
public class StudentServiceImpl implements StudentService {
    @Autowired
    private StudentMapper studentMapper;

    @Override
    public PageInfo<Student> listByPage(Student record) throws Exception {
        PageHelper.startPage(record.getPageNum(), record.getPageSize());
        List<Student> list = studentMapper.listByPage(record);

        PageInfo<Student> studentPageInfo = new PageInfo<>(list);
        studentPageInfo.setPageNum(studentPageInfo.getPageNum());
        studentPageInfo.setTotal(studentPageInfo.getTotal());
        studentPageInfo.setPageSize(studentPageInfo.getPageSize());
        return studentPageInfo;
    }
}

5.5 StudentMapper.xml

<select id="listByPage" resultMap="BaseResultMap" parameterType="com.demo.domain.Student">
     SELECT student_no, student_name, admission_time
     FROM student
     ORDER BY admission_time desc
 </select>

5.6 前端 HTML

<el-table style="width: 100%" border stripe ref="selectList" :data="tableData" :empty-text="tableDataTips"
          @selection-change="handleSelectionChange">
    <el-table-column type="selection"></el-table-column>
    <el-table-column prop="serialNum" width="50px" label="序号" align="center"></el-table-column>
    <el-table-column prop="studentNo" label="学号" align="center"></el-table-column>
    <el-table-column prop="studentName" label="姓名" align="center"></el-table-column>
    <el-table-column prop="admissionTime" label="入学时间" align="center">
        <template slot-scope="scope">
            <span>{{formatDate(scope.row.admissionTime, 'yyyy-MM')}}</span>
        </template>
    </el-table-column>
</el-table>
<div style="margin-top: 20px;">
	<el-pagination background align="right" @size-change="handleSizeChange"
	               @current-change="handleCurrentChange" :current-page="pageIndex"
	               :page-sizes="[10, 20, 30, 50]" :page-size="pageSize"
	               layout="total,  prev, pager, next, jumper,sizes" :total=total>
	</el-pagination>
</div>

5.7 前端 JS

var vm = new Vue({
    el: '#index',
    data() {
        return {
            pageSize: 10,
            pageIndex: 1,
            total: 0,
            tableDataTips: '暂无数据',
            tableData: [],	// 表格数据
            selectList: [],
        }
    },

    mounted() {
        this.getTableData();
    },

    methods: {
        // 获取表格数据
        getTableData: function (options) {
            var that = this;
            that.tableDataTips = '数据加载中...';
            $.ajax({
                url: ctx + 'student/listByPage',
                type: "get",
                async: false,
                data: {pageNum: that.pageIndex, pageSize: that.pageSize},
                success(data) {
                    that.tableData = data.list;
                    that.tableData.forEach(function (item, index) {
                        !item && (item = {});
                        item['serialNum'] = (that.pageIndex - 1) * that.pageSize + (index + 1);
                    });
                    that.total = data.total;
                    if (!that.tableData.length) that.tableDataTips = '暂无数据'
                }, error() {
                    that.$message({
                        message: '服务器走丢了',
                        type: 'error'
                    })
                }
            })
        },

        // 改变页码容量
        handleSizeChange(sizeVal) {
            this.pageSize = sizeVal;
            this.getTableData()
        },
        // 改变页码
        handleCurrentChange(indexVal) {
            this.pageIndex = indexVal;
            this.getTableData()
        },

        // 选中或取消列表复选框
        handleSelectionChange(val) {
            this.selectList = val;
        },

        // 格式化时间
        formatDate(time, fmt) {
            time = new Date(time);
            var splitTime = {
                "y+": time.getFullYear().toString(), // 年
                "M+": time.getMonth() + 1, //月份
                "d+": time.getDate(), //日
                "h+": time.getHours(), //小时
                "m+": time.getMinutes(), //分
                "s+": time.getSeconds(), //秒
                "q+": Math.floor((time.getMonth() + 3) / 3), //季度
                "S": time.getMilliseconds() //毫秒
            };
            if (/(y+)/.test(fmt))
                fmt = fmt.replace(RegExp.$1, (time.getFullYear() + "").substr(4 - RegExp.$1.length));
            for (var key in splitTime) {
                if (new RegExp("(" + key + ")").test(fmt))
                    fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (splitTime[key]) : (("00" + splitTime[key]).substr(("" + splitTime[key]).length)));
            }
            return fmt;
        },
    },
});

6. 编写工具类，在 static 方法中引用 @Autowired 依赖注入报错

@Component
public class StudentUtils {
    @Autowired
    private StudentMapper studentMapper;
    
    private static StudentUtils studentUtils;

    @PostConstruct
    public void init() {
        studentUtils = this;
        studentUtils.studentMapper = this.studentMapper;
    }
    
    // static 方法中引用 studentMapper
    public static Student getStudentInfo(String sno) {
        return studentUtils.studentMapper.listStudentInfo(sno);
    }
}

7. 字符串实现加 1 操作(字符串前面可能有数字 0 )

String res = null;
String code = "010088";
res = String.format("%0" + code.length() + "d", Long.parseLong(code) + 1);
System.out.println(res);	// 输出结果为 010089

8. 生成唯一不重复的 12 位数字

这里通过 synchronized 关键字实现，前 8 位是 年月日，后 4 位 唯一不重复。

@Component
public class OrderNoUtils {
    @Autowired
    private OrderMapper orderMapper;
    
    private static OrderNoUtils orderNoUtils;
    
    @PostConstruct
    public void init() {
        orderNoUtils = this;
        orderNoUtils.orderMapper = this.orderMapper;
    }
    
    public static synchronized String genOrderNo() {
        String orderNO = null;

        // 获取当前年月日
        String date = new SimpleDateFormat("yyyyMMdd").format(new Date());
        // 在数据库中查询一次最大的订单号
        String maxOrderNO = orderNoUtils.orderMapper.getMaxOrderNO(date).get("max_no").toString();
        if (!"0".equals(maxOrderNO)) {
            // 订单号加1
            orderNO = String.format("%0" + maxOrderNO.length() + "d", Long.parseLong(maxOrderNO) + 1);
        } else {
            orderNO = date + "0000";
        }

        return orderNO;
    }
}

<select id="getMaxOrderNO" parameterType="java.lang.String" resultType="map">
    SELECT CASE WHEN MAX(A.order_no) IS NULL THEN '0' ELSE MAX (A.order_no) END AS max_no
    FROM order_detail A
    WHERE A.order_no LIKE concat(#{eightBitNum},'%') AND LENGTH(A.order_no) = 12
</select>

9. 限制每秒并发次数不超过 5 次

转载自 一次任务的实践，解决每秒最大并发次数的问题 – 生产者消费者模式。

import java.util.LinkedList;
import java.util.List;

public class tencentLbsUtils{
    private static Consume consume = null;
	static{
		// 创建仓库对象
		Warehouse warehouse = new Warehouse();
		// 创建消费者对象
		consume = new Consume(warehouse);
		// 创建生产者线程
		new Thread(new Produce(warehouse)).start();
	}
	
	/**
	 * 根据经纬度返回详细地址
	 * @param Latitude
	 * @param longitude
	 */
	public static String getDetailAddress(String Latitude, String longitude) throws InterruptedException{
		// 调用消费者进行消费
		return consume.getDetailAddress(Latitude, longitude);
	}
}



/**
 * 消费者
 */
class Consume{
	public Warehouse producer;
	Consume(Warehouse producer){
		this.producer = producer;
	}
	public void consume() throws InterruptedException{
		System.out.println("开始消费");
		producer.consume();
	}
	
	public String getDetailAddress(String Latitude, String longitude) throws InterruptedException{
		// 访问仓库是否可以进行消费
		consume();
		// 调用接口
		System.out.println("调用地图接口");
		// 返回详细地址
		return "详细地址。。。";
	}
}



/**
 * 生产者
 */
class Produce implements Runnable{
	private Warehouse producer;
	
	Produce(Warehouse producer){
		this.producer = producer;
	}
	@Override
	public void run() {
		// 让生产者循环生产
		while(true){
			try {
				System.out.println("开始生产");
				producer.produce();
			} catch (InterruptedException e) {
				e.printStackTrace();
			}
		}
	}

}

/**
 * 仓库
 */
class Warehouse{
	/**
	 * 仓库当前容量
	 */
	private int index = 5; 
	/**
	 * 最大容量 
	 */
    private final int MAX = 5;
    /**
     * 理论上最大只会存放5个
     */
    private List<Long> timeList = new LinkedList<>(); 
    
	public synchronized void produce() throws InterruptedException{
		while(index >= MAX){
			System.out.println("等待消費。。。");
			this.wait();
		}
		// 拿到前面第五个的消费时间的后一秒
		long upTime = timeList.remove(0) + 1000;  
		long currentTime = System.currentTimeMillis(); 
		// 如果前面第五个消费时间的后一秒大于当前时间
		while(upTime > currentTime){		
			// 等待时间差，保证每六个之间前后的时间差为一秒
			this.wait(upTime - currentTime);
			// 刷新当前时间，需要重新判断，排除是notify 或 notifyAll方法激活的该线程
			currentTime = System.currentTimeMillis();	
		}
		// 生产一个
		this.index++;
		//生产之后可通知消费者线程消费
		notifyAll();
	}
	
	public synchronized void consume() throws InterruptedException{
		// 判断仓库空了，则等待。
		while(index <= 0) {
			System.out.println("等待生產。。。");
			this.wait();
        }
        // 消费一个
		index--;
		// 记录当前消费时间
		timeList.add(System.currentTimeMillis());
		// 消费之后可通知生产者线程进行生产
		notifyAll();		
	}
}

10. 请求 URL 返回 JSON 对象、JSON 对象操作

这里以 腾讯地图逆向地址解析(通过地址获取经纬度) 为例，对后端请求 URL 获取数据进行简单说明。
注：逆向地址解析返回的数据并不规范，不能直接转换为 JSON 对象，需要进行简单的处理，如下：

QQmap&&QQmap({
    "status": 0,
    "message": "query ok",
    "result": {
        "title": "阅江西路222号",
        "location": {
            "lng": 113.32389,
            "lat": 23.10594
        },
        "ad_info": {
            "adcode": "440105"
        },
        "address_components": {
            "province": "广东省",
            "city": "广州市",
            "district": "海珠区",
            "street": "阅江西路",
            "street_number": "222"
        },
        "similarity": 0.8,
        "deviation": 1000,
        "reliability": 7,
        "level": 9
    }
})

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.springframework.web.client.RestTemplate;

public class TestUtils {
    public static JSONObject getLngLat(String address) throws InterruptedException {
        RestTemplate restTemplate = new RestTemplate();
        // 请求参数
        Map<String, String> requestParam = new HashMap<>();
        requestParam.put("output", "jsonp");    			// 设置请求返回数据格式
        requestParam.put("key", "");  						// 腾讯地图key(需要自己申请)
        requestParam.put("address", "广东省" + address);   //  需要解析的地址

        // 封装请求地址和请求参数
        String requestUrl = "https://apis.map.qq.com/ws/geocoder/v1?output={output}&address={address}&key={key}";
        // 调用接口
        String responseStr = restTemplate.getForObject(requestUrl, String.class, requestParam);

        // 处理不规范的返回数据
        List<String> list = extractMessageByRegular(responseStr);
        // 获取处理后的数据，并转化为 JSON 对象
        JSONObject jsonObject = JSON.parseObject(list.get(0));
        return jsonObject;
    }
    
    /**
     * 正则提取小括号中的内容
     */
     private static List<String> extractMessageByRegular(String msg) {
        List<String> list = new ArrayList<String>();
        Pattern pattern = Pattern.compile("(\\([^\\]]*\\))");
        Matcher matcher = pattern.matcher(msg);
        while (matcher.find()) {
            list.add(matcher.group().substring(1, matcher.group().length() - 1));
        }
        return list;
    }
}

// 获取 JSON 对象数据
JSONObject jsonObject = TestUtils.getLngLat("广州市海珠区阅江西路222号");
if (jsonObject.getString("status").equals("0")) {	// 返回 0 说明逆向地址解析成功
    // 获取 jsonObject 中的 result 对象
    JSONObject resultObject = jsonObject.getJSONObject("result");
    // 获取 result 对象中的 location 对象
    JSONObject locationObject = resultObject.getJSONObject("location");
    lng = locationObject.getString("lng"); // 获取经度
    lat = locationObject.getString("lat"); // 获取纬度
}

11. 补全当前年 12 个月的数据

假设现在是 2022 年 1 月，要求从数据库中查询 2022 年 12 个月 的支出情况，2 月 之后的数据用 “-” 表示。

List<Map<String, Object>> result = new ArrayList<>();

Calendar calendar = Calendar.getInstance();
calendar.setTime(new Date());
String year = calendar.get(Calendar.YEAR) + "";
// 月份补全标志位
Boolean flag = false;
// 一年中的 12 个月
List<String> monthsList = Arrays.asList("1,2,3,4,5,6,7,8,9,10,11,12".split(","));
List<Map<String, Object>> list = testMapper.getYearExpendInfo(year);
if (list != null && list.size() > 0) {
	for (String months : monthsList) {
		for (Map<String, Object> mapInfo : list) {
			Date date = (Date) mapInfo.get("expend_date");
			calendar.setTime(date);
			String month = (calendar.get(Calendar.MONTH) + 1) + "";
			
			if (month.equals(months)) {
				flag = true;	// 当前月份不需要补全
				Map<String, Object> map = new HashMap<>();
                map.put("month", months + "月");
                map.put("expend", mapInfo.get("expend"));
                result.add(map);
                break;
            }
        }
        
        if (!flag) {    // 需要补全的月份
	        Map<String, Object> map = new HashMap<>();
	        map.put("month", months + "月");
            map.put("expend", "-");
	        result.add(map);
        }
        
        flag = false;   // 补全标志位置为 false, 继续下一次循环
    }
} else {
	for (String months : monthsList) {
	    Map<String, Object> map = new HashMap<>();
	    map.put("month", months + "月");
        map.put("expend", "-");
	    result.add(map);
    }
}

System.out.println(result.toString());	// result 就是我们需要的结果

12. POI 中 Excel 导入导出

通过 POI 实现相对复杂表头的导入导出，包括增加 筛选 和 冻结表头，实现效果如下图：

12.1 学生实体 StudentInfo

public class StudentInfo {
    private String sid;

    private String sno;

    private String sname;

    private String gender;

    private String address;

    private String telephone;

    private Date createTime;

    private Date modifyTime;
    
    /* 此处省略 Getter 和 Setter 方法 */
}

12.2 导入导出全局变量定义 ExcelConstants

public class ExcelConstants {
    public static List<String> ROW_TITLE_LIST_MERGE = Arrays.asList("基本信息", "通讯信息");
    public static List<String> ROW_TITLE_LIST = Arrays.asList("*学号", "*姓名", "性别（男、女）", "|", "通讯地址", "*联系电话");
    public static List<String> ROW_FIELD_LIST = Arrays.asList("sno", "sname", "gender", "address", "telephone");
    public static List<Integer> COLUMN_WIDTH_LIST = Arrays.asList(7000, 7000, 5000, 6000, 6000, 6000, 8000);
    public static String EXPORT_FILE_NAME = "学生信息导出";
    public static String TEMPLATE_FILE_NAME = "学生信息导入模板";
    public static String SHEET_NAME = "学生信息";
    public static String TITLE_NAME = "学生信息";
}

12.3 StudentInfoMapper.xml

<select id="selectBySno" parameterType="java.lang.String" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from student_info
    where sno = #{sno}
</select>

<select id="listByNoPage" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from student_info
</select>

<insert id="insertItem" parameterType="com.demo.domain.StudentInfo">
    insert into student_info
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="sid != null">
            sid,
        </if>
        <if test="sno != null">
            sno,
        </if>
        <if test="sname != null">
            sname,
        </if>
        <if test="gender != null">
            gender,
        </if>
        <if test="address != null">
            address,
        </if>
        <if test="telephone != null">
            telephone,
        </if>
        <if test="createTime != null">
            create_time,
        </if>
        <if test="modifyTime != null">
            modify_time,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="sid != null">
            #{sid,jdbcType=INTEGER},
        </if>
        <if test="sno != null">
            #{sno,jdbcType=INTEGER},
        </if>
        <if test="sname != null">
            #{sname,jdbcType=VARCHAR},
        </if>
        <if test="gender != null">
            #{gender,jdbcType=VARCHAR},
        </if>
        <if test="address != null">
            #{address,jdbcType=VARCHAR},
        </if>
        <if test="telephone != null">
            #{telephone,jdbcType=VARCHAR},
        </if>
        <if test="createTime != null">
            #{createTime,jdbcType=TIMESTAMP},
        </if>
        <if test="modifyTime != null">
            #{modifyTime,jdbcType=TIMESTAMP},
        </if>
    </trim>
</insert>

<update id="updateBySno" parameterType="com.demo.domain.StudentInfo">
    update student_info
    <set>
        <if test="sno != null">
            sno = #{sno,jdbcType=INTEGER},
        </if>
        <if test="sname != null">
            sname = #{sname,jdbcType=VARCHAR},
        </if>
        <if test="gender != null">
            gender = #{gender,jdbcType=VARCHAR},
        </if>
        <if test="address != null">
            address = #{address,jdbcType=VARCHAR},
        </if>
        <if test="telephone != null">
            telephone = #{telephone,jdbcType=VARCHAR},
        </if>
        <if test="createTime != null">
            create_time = #{createTime,jdbcType=TIMESTAMP},
        </if>
        <if test="modifyTime != null">
            modify_time = #{modifyTime,jdbcType=TIMESTAMP},
        </if>
    </set>
    where sno = #{sno,jdbcType=INTEGER}
</update>

12.4 导出工具类 ExcelExportUtils

public class ExcelExportUtils {
    private static int HEAD_ROW_POSITION = 0;               // 一级标题所在行
    private static int TITLE_ROW_POSITION[] = {1, 2};       // 二级标题所在行
    private static int CONTENT_ROW_POSITON = 3;             // 内容开始行

    public static void buildExportData(List rowTitleMergeList, List rowTitleList, List fieldList, List columnWidthList, List dataList, String fileName, String sheetName, String titleName, ExcelExportUtils.ExcelParam excelParam) {
        long l = System.currentTimeMillis();
        Date date = new Date(l);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String dateStr = dateFormat.format(date);
        fileName = fileName + dateStr + ".xls";
        excelParam.setColumnWidthList(columnWidthList);
        excelParam.setRowTitleList(rowTitleList);
        excelParam.setRowTitleMergeList(rowTitleMergeList);
        excelParam.setFieldList(fieldList);
        excelParam.setDataList(dataList);
        excelParam.setFileName(fileName);
        excelParam.setSheetName(sheetName);
        excelParam.setTitleName(titleName);
    }

    public static void exportExcel(ExcelExportUtils.ExcelParam excelParam, HttpServletRequest req, HttpServletResponse response) throws Exception {
        if (excelParam == null) {
            throw new RuntimeException("excel请求数据为空！");
        } else {
            List<String> rowTitle = excelParam.getRowTitleList();   // 二级标题
            List<String> rowTitleMerge = excelParam.getRowTitleMergeList(); // 二级标题合并
            if (rowTitle != null && rowTitle.size() != 0) {
                String fileName = excelParam.getFileName();
                if (StringUtils.isBlank(fileName)) {
                    fileName = UUID.randomUUID().toString() + ".xls";
                }

                String sheetName = excelParam.getSheetName();
                if (StringUtils.isBlank(sheetName)) {
                    sheetName = "查询结果";
                }

                HSSFWorkbook workbook = new HSSFWorkbook();
                HSSFSheet sheet = initSheet(workbook, sheetName);
                if (excelParam.getColumnWidthList() != null && excelParam.getColumnWidthList().size() > 0) {
                    sheet.setColumnWidth(0, 1500);

                    for (int i = 0; i < excelParam.getColumnWidthList().size(); ++i) {
                        sheet.setColumnWidth(i + 1, (Integer) excelParam.getColumnWidthList().get(i));
                    }
                }

                String titleName = excelParam.getTitleName();
                if (StringUtils.isBlank(titleName)) {
                    titleName = "查询结果";
                }

                createTitle(workbook, sheet, titleName, excelParam.getFieldList().size() + 1);
                createRowTitle(workbook, sheet, rowTitle, rowTitleMerge);
                createContent(workbook, sheet, excelParam.getFieldList(), excelParam.getDataList());
                response.reset();
                response.setContentType("application/octet-stream");
                response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
                workbook.write(response.getOutputStream());
                response.getOutputStream().flush();
            } else {
                throw new RuntimeException("没有表头数据！");
            }
        }
    }

    private static HSSFSheet initSheet(HSSFWorkbook workbook, String sheetName) {
        return workbook.createSheet(sheetName);
    }

    private static void createTitle(HSSFWorkbook workbook, HSSFSheet sheet, String titleName, int dateSize) {
        HSSFRow row = sheet.createRow(HEAD_ROW_POSITION);
        HSSFCell cell = row.createCell(HEAD_ROW_POSITION);
        cell.setCellValue(titleName);
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setCharSet((byte) 1);
        font.setFontName("宋体");
        font.setFontHeight((short) 18);
        font.setBoldweight((short) 700);
        font.setFontHeightInPoints((short) 18);
        cellStyle.setFont(font);
        setVertical(cellStyle);
        setBorder(cellStyle);
        cell.setCellStyle(cellStyle);

        for (int i = 1; i < dateSize; ++i) {
            cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
        }

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, dateSize - 1));
    }

    private static void createRowTitle(HSSFWorkbook workbook, HSSFSheet sheet, List<String> tempTitleList, List<String> titleMergeList) {
        for (int position = TITLE_ROW_POSITION.length - 1; position >= 0; position--) {
            HSSFRow row = sheet.createRow(TITLE_ROW_POSITION[position]);
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBoldweight((short) 700);
            font.setFontName("等线");
            font.setFontHeightInPoints((short) 14);
            font.setFontHeight((short) 14);
            setBackYellowColor(cellStyle);
            setVertical(cellStyle);
            cellStyle.setWrapText(true);
            setBorder(cellStyle);

            List<String> titleList = new ArrayList<>();
            List<Short> splitList = new ArrayList<>();
            splitList.add((short) 0);

            short split = 0;
            for (int i = 0; i < tempTitleList.size(); i++) {
                if ("|".equals(tempTitleList.get(i))) {
                    splitList.add(split);
                    split--;
                } else {
                    titleList.add(tempTitleList.get(i));
                }

                split++;
            }
            splitList.add((short) titleList.size());

            HSSFCell cell = null;
            CellRangeAddress cellRangeAddress = null;

            cell = row.createCell(0);
            cellRangeAddress = new CellRangeAddress(TITLE_ROW_POSITION[0], TITLE_ROW_POSITION[TITLE_ROW_POSITION.length - 1], 0, 0);
            sheet.addMergedRegion(cellRangeAddress);
            setMergedBorder(workbook, sheet, cellRangeAddress); // 设置合并单元格边框
            cell.setCellValue("序号");
            cell.setCellStyle(cellStyle);

            if (position == 0) {
                for (int i = 0; i < titleMergeList.size(); i++) {
                    cell = row.createCell(splitList.get(i) + 1);
                    cellRangeAddress = new CellRangeAddress(TITLE_ROW_POSITION[position], TITLE_ROW_POSITION[position], splitList.get(i) + 1, splitList.get(i + 1));
                    sheet.addMergedRegion(cellRangeAddress);
                    setMergedBorder(workbook, sheet, cellRangeAddress); // 设置合并单元格边框
                    cell.setCellValue((String) titleMergeList.get(i));
                    cell.setCellStyle(cellStyle);
                }
            } else {
                for (int i = 0; i < titleList.size(); ++i) {
                    cell = row.createCell(i + 1);
                    cell.setCellValue((String) titleList.get(i));
                    cell.setCellStyle(cellStyle);
                }
            }

            // 冻结前3行
            setFreezePane(sheet, new short[]{0, 3, 0, 4});
            // 筛选第3行B列到第3行F列
            setFilter(sheet, "B3:" + numberToSheetHeader((short) titleList.size()) + "3");

            HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
            hssfCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
            hssfCellStyle.setFillPattern((short) 1);
            setVertical(hssfCellStyle);
            hssfCellStyle.setWrapText(true);
        }

    }

    private static void setVertical(HSSFCellStyle cellStyle) {
        cellStyle.setAlignment((short) 2);
        cellStyle.setVerticalAlignment((short) 1);
    }

    private static void setBorder(HSSFCellStyle cellStyle) {
        cellStyle.setBorderBottom((short) 1);
        cellStyle.setBorderLeft((short) 1);
        cellStyle.setBorderRight((short) 1);
        cellStyle.setBorderTop((short) 1);
    }

    private static void setMergedBorder(HSSFWorkbook workbook, HSSFSheet sheet, CellRangeAddress cellRangeAddress) {
        RegionUtil.setBorderBottom((short) 1, cellRangeAddress, sheet, workbook);
        RegionUtil.setBorderLeft((short) 1, cellRangeAddress, sheet, workbook);
        RegionUtil.setBorderRight((short) 1, cellRangeAddress, sheet, workbook);
        RegionUtil.setBorderTop((short) 1, cellRangeAddress, sheet, workbook);
    }

    /**
     * 获取数字对应的 Excel 表头，如 A-Z;AA-ZZ;AAA-ZZZ 支持无限
     *
     * @param index 数字
     * @return 返回数字对应的英文表头
     */
    public static String numberToSheetHeader(short index) {
        String colCode = "";
        char key = 'A';
        int loop = index / 26;

        if (loop > 0) colCode += numberToSheetHeader((short) (loop - 1));
        key = (char) (key + index % 26);
        colCode += key;
        return colCode;
    }

    /**
     * 设置 Excel 筛选
     *
     * @param sheet       sheet表格
     * @param filterRange 筛选范围
     */
    private static void setFilter(HSSFSheet sheet, String filterRange) {
        sheet.setAutoFilter(CellRangeAddress.valueOf(filterRange));

        /**
         * sheet.setAutoFilter(CellRangeAddress.valueOf("B3:F3");  // 筛选范围从第3行B列到第3行F列
         */
    }

    /**
     * 冻结窗格
     *
     * @param sheet      sheet表格
     * @param freezePane 冻结范围
     */
    private static void setFreezePane(HSSFSheet sheet, short freezePane[]) {
        sheet.createFreezePane(freezePane[0], freezePane[1], freezePane[2], freezePane[3]);

        /**
         * sheet.createFreezePane(0,4,0,5);    // 冻结前4行（只冻结行）
         * sheet.createFreezePane(4,0,5,0);    // 冻结前4列（只冻结列）
         */
    }

    private static void setBackYellowColor(HSSFCellStyle cellStyle) {
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        cellStyle.setFillPattern((short) 1);
    }

    private static void createContent(HSSFWorkbook workbook, HSSFSheet sheet, List<String> fieldList, List dataList) throws Exception {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) 11);
        cellStyle.setFont(font);
        setVertical(cellStyle);
        cellStyle.setWrapText(true);
        setBorder(cellStyle);
        if (dataList != null) {
            for (int i = 0; i < dataList.size(); ++i) {
                Object object = dataList.get(i);
                HSSFRow row = sheet.createRow(CONTENT_ROW_POSITON + i);
                HSSFCell cell0 = row.createCell(0);
                cell0.setCellValue((double) (i + 1));
                cell0.setCellStyle(cellStyle);
                Class objCalss = object.getClass();

                for (int j = 0; j < fieldList.size(); ++j) {
                    HSSFCell cell = row.createCell(j + 1);
                    String fieldName = (String) fieldList.get(j);
                    String cKey = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Object fValue = null;

                    try {
                        Method fMethod = objCalss.getMethod("get" + cKey);
                        fValue = fMethod.invoke(object);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                    if (fValue != null && fValue instanceof Date) {
                        Date date = (Date) fValue;
                        fValue = (new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).format(date);
                    }

                    String value = fValue == null ? null : fValue.toString();
                    cell.setCellValue(value);
                    cell.setCellStyle(cellStyle);
                }
            }
        }
    }

    public static HSSFWorkbook getExportWorkBook(HSSFWorkbook workbook, ExcelExportUtils.ExcelParam excelParam, HttpServletRequest req, HttpServletResponse response) throws Exception {
        if (excelParam == null) {
            throw new RuntimeException("excel请求数据为空！");
        } else {
            List<String> rowTitle = excelParam.getRowTitleList();
            List<String> rowTitleMerge = excelParam.getRowTitleMergeList(); // 二级标题合并
            if (rowTitle != null && rowTitle.size() != 0) {
                String fileName = excelParam.getFileName();
                if (com.augurit.agcloud.framework.util.StringUtils.isBlank(fileName)) {
                    fileName = UUID.randomUUID().toString() + ".xls";
                }

                String sheetName = excelParam.getSheetName();
                if (com.augurit.agcloud.framework.util.StringUtils.isBlank(sheetName)) {
                    sheetName = "查询结果";
                }

                if (workbook == null) {
                    workbook = new HSSFWorkbook();
                }

                HSSFSheet sheet = initSheet(workbook, sheetName);
                if (excelParam.getColumnWidthList() != null && excelParam.getColumnWidthList().size() > 0) {
                    sheet.setColumnWidth(0, 1500);

                    for (int i = 0; i < excelParam.getColumnWidthList().size(); ++i) {
                        sheet.setColumnWidth(i + 1, (Integer) excelParam.getColumnWidthList().get(i));
                    }
                }

                String titleName = excelParam.getTitleName();
                if (com.augurit.agcloud.framework.util.StringUtils.isBlank(titleName)) {
                    titleName = "查询结果";
                }

                createTitle(workbook, sheet, titleName, excelParam.getFieldList().size() + 1);
                createRowTitle(workbook, sheet, rowTitle, rowTitleMerge);
                createContent(workbook, sheet, excelParam.getFieldList(), excelParam.getDataList());
                return workbook;
            } else {
                throw new RuntimeException("没有表头数据！");
            }
        }
    }

    public static void exportExcelData(HSSFWorkbook workbook, String fileName, HttpServletRequest req, HttpServletResponse response) throws Exception {
        long l = System.currentTimeMillis();
        Date date = new Date(l);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String dateStr = dateFormat.format(date);
        fileName = fileName + dateStr + ".xls";
        response.reset();
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
    }

    public static class ExcelParam {
        private String FileName;                    // 导出文件名称
        private List<? extends Object> dataList;    // 内容数据
        private int sheetNum;                       // 表数量
        private String sheetName;                   // 表名称
        private String titleName;                   // 一级标题
        private List<Integer> columnWidthList;      // 列宽
        private List<String> rowTitleList;          // 二级标题
        private List<String> rowTitleMergeList;     // 二级标题合并
        private List<String> fieldList;             // 二级标题对应的字段名

        public ExcelParam() {
        }

        public String getFileName() {
            return this.FileName;
        }

        public void setFileName(String fileName) {
            this.FileName = fileName;
        }

        public List<? extends Object> getDataList() {
            return this.dataList;
        }

        public void setDataList(List<? extends Object> dataList) {
            this.dataList = dataList;
        }

        public int getSheetNum() {
            return this.sheetNum;
        }

        public void setSheetNum(int sheetNum) {
            this.sheetNum = sheetNum;
        }

        public String getSheetName() {
            return this.sheetName;
        }

        public void setSheetName(String sheetName) {
            this.sheetName = sheetName;
        }

        public String getTitleName() {
            return this.titleName;
        }

        public void setTitleName(String titleName) {
            this.titleName = titleName;
        }

        public List<Integer> getColumnWidthList() {
            return this.columnWidthList;
        }

        public void setColumnWidthList(List<Integer> columnWidthList) {
            this.columnWidthList = columnWidthList;
        }

        public List<String> getRowTitleList() {
            return this.rowTitleList;
        }

        public void setRowTitleList(List<String> rowTitleList) {
            this.rowTitleList = rowTitleList;
        }

        public List<String> getRowTitleMergeList() {
            return this.rowTitleMergeList;
        }

        public void setRowTitleMergeList(List<String> rowTitleMergeList) {
            this.rowTitleMergeList = rowTitleMergeList;
        }

        public List<String> getFieldList() {
            return this.fieldList;
        }

        public void setFieldList(List<String> fieldList) {
            this.fieldList = fieldList;
        }
    }
}

12.5 导入工具类 ExcelImportUtils

public class ExcelImportUtils {
    private static int MAX_IMPORT_SUM = 1000;
    private static int TITLE_ROW_INDEX = 2;
    private static int TITLE_LINE_INDEX = 0;

    public static Workbook getWorkbook(MultipartFile file) throws Exception {
        Workbook readbook = null;
        InputStream in = file.getInputStream();
        String fileName = file.getOriginalFilename();
        fileName.substring(fileName.lastIndexOf(".") + 1);
        readbook = WorkbookFactory.create(in);
        return readbook;
    }

    public static String getValue(Cell cell) {
        String value = null;
        if (cell == null) {
            return value;
        } else {
            switch (cell.getCellType()) {
                case 0:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                        value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                    } else {
                        value = (new DecimalFormat("0")).format(cell.getNumericCellValue());
                    }
                    break;
                case 1:
                    value = cell.getStringCellValue();
                    break;
                case 2:
                    value = cell.getCellFormula() + "";
                    break;
                case 3:
                    value = "";
                    break;
                case 4:
                    value = cell.getBooleanCellValue() + "";
                    break;
                case 5:
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
            }

            return value;
        }
    }

    public static String checkTemplateIsValid(Workbook dataBook, List<String> templateSheetNameList, List<List<String>> templateRowTitleListOld) {
        List<List<String>> templateRowTitleListNew = new ArrayList<>();
        for (List<String> listOld : templateRowTitleListOld) {
            LinkedList<String> listNew = new LinkedList<>(listOld);
            listNew.add(0, "序号");
            templateRowTitleListNew.add(listNew);
        }
        StringBuilder sb = new StringBuilder();
        if (dataBook != null) {
            int templateSheetNum = templateSheetNameList.size();
            int dataSheetNum = dataBook.getNumberOfSheets();
            if (templateSheetNum == dataSheetNum) {
                Sheet dataSheet = null;
                String templateSheetName = null;
                String dataSheetName = null;
                int dataSheetLastRowNum = 0;
                Row dataRow = null;
                short templateColumnNum = 0;
                short dataColumnNum = 0;
                String templateCellValue = null;
                String dataCellValue = null;
                for (int sheetIndex = 0; sheetIndex < templateSheetNameList.size(); sheetIndex++) {
                    dataSheet = dataBook.getSheetAt(sheetIndex);
                    if (dataSheet != null) {
                        templateSheetName = templateSheetNameList.get(sheetIndex);
                        dataSheetName = dataSheet.getSheetName();
                        if (StringUtils.isNotBlank(templateSheetName) && StringUtils.isNotBlank(dataSheetName) && templateSheetName.equals(dataSheetName)) {
                            dataSheetLastRowNum = dataSheet.getLastRowNum();
                            if (dataSheetLastRowNum > MAX_IMPORT_SUM)
                                buildHtmlStr(sb, "导入的文件sheet名称为‘" + dataSheetName + "中的数据不能超过" + MAX_IMPORT_SUM + "行数据，请分次导入！");
                            dataRow = dataSheet.getRow(TITLE_ROW_INDEX);
                            if (dataRow != null) {
                                templateColumnNum = (short) ((List) templateRowTitleListNew.get(sheetIndex)).size();
                                dataColumnNum = dataRow.getLastCellNum();
                                if (templateColumnNum == dataColumnNum) {
                                    for (int lineIndex = TITLE_LINE_INDEX; lineIndex < templateColumnNum; lineIndex++) {
                                        templateCellValue = ((List<String>) templateRowTitleListNew.get(sheetIndex)).get(lineIndex);
                                        dataCellValue = getValue(dataRow.getCell(lineIndex));
                                        if (!StringUtils.isNotBlank(templateCellValue) || !StringUtils.isNotBlank(dataCellValue) || !templateCellValue.equals(dataCellValue))
                                            buildHtmlStr(sb, "导入的文件sheet名称为‘" + dataSheetName + "’中的第‘" + (TITLE_ROW_INDEX + 1) + "’行的第‘" + (lineIndex + 1) + "’列名称为‘" + dataCellValue + "’与模板中的列名称为‘" + templateCellValue + "’不一致，请重新确认！");
                                    }
                                } else {
                                    buildHtmlStr(sb, "导入的文件sheet名称为‘" + dataSheetName + "’中的第‘" + (TITLE_ROW_INDEX + 1) + "’行的列数为‘" + dataColumnNum + "’与模板中的列数为‘" + templateColumnNum + "’不一致，请重新确认！");
                                }
                            }
                        } else {
                            buildHtmlStr(sb, "导入的文件第" + (sheetIndex + 1) + "个sheet名称为‘" + dataSheetName + "’与模板中的sheet名称为‘" + templateSheetName + "’不一致，请重新确认！");
                        }
                    }
                }
            } else {
                buildHtmlStr(sb, "导入的文件sheet个数为‘" + dataSheetNum + "’与模板中sheet个数为‘" + templateSheetNum + "’不一致，请重新确认！");
            }
        }
        return sb.toString();
    }

    public static void buildHtmlStr(StringBuilder sb, String content) {
        sb.append(content);
        sb.append(";");
    }

}

12.6 ExcelServiceImpl(此处省略 ExcelService)

@Service
public class ExcelServiceImpl implements ExcelService {
    @Autowired
    private StudentInfoMapper studentInfoMapper;

    @Override
    public void exportExcel(HttpServletRequest req, HttpServletResponse resp, String fileName, String sheetName, String titleName) throws Exception {
        List<StudentInfo> itemList = studentInfoMapper.listByNoPage();

        ExcelExportUtils.ExcelParam excelParam = new ExcelExportUtils.ExcelParam();
        ExcelExportUtils.buildExportData(ExcelConstants.ROW_TITLE_LIST_MERGE, ExcelConstants.ROW_TITLE_LIST, ExcelConstants.ROW_FIELD_LIST, ExcelConstants.COLUMN_WIDTH_LIST, itemList, fileName, sheetName, titleName, excelParam);
        ExcelExportUtils.exportExcel(excelParam, req, resp);
    }

    @Override
    public Map<String, Object> importExcel(MultipartFile file, int sheetIndex, String typeId) throws Exception {
        Map<String, Object> result = new HashMap<>();
        List list = new ArrayList();
        result.put("success", true);
        result.put("message", "导入成功!");

        Workbook readbook = ExcelImportUtils.getWorkbook(file);
        Sheet sheet = readbook.getSheetAt(sheetIndex);
        Row row = null;
        StringBuilder checkValidStr = new StringBuilder();

        for (int rowNum = 3; rowNum <= sheet.getLastRowNum(); ++rowNum) {
            row = sheet.getRow(rowNum);
            if (row != null) {
                StudentInfo studentInfo = new StudentInfo();
                String checkResult = "";

                studentInfo.setSno(ExcelImportUtils.getValue(row.getCell(1)));
                studentInfo.setSname(ExcelImportUtils.getValue(row.getCell(2)));
                studentInfo.setGender(ExcelImportUtils.getValue(row.getCell(3)));
                studentInfo.setAddress(ExcelImportUtils.getValue(row.getCell(4)));
                studentInfo.setTelephone(ExcelImportUtils.getValue(row.getCell(5)));
                checkResult = checkResult + this.checkDataIsValid(studentInfo, rowNum);

                if (StringUtils.isNotBlank(studentInfo.getSno())) {
                    if (validateNumber(studentInfo.getSno())) studentInfo.setSno(studentInfo.getSno());
                    else checkResult += "第" + (rowNum + 1) + "行数据中的学号不是数字，请重新确认!;";
                }
                if (StringUtils.isNotBlank(studentInfo.getTelephone())) {
                    if (validateTelephone(studentInfo.getTelephone()))
                        studentInfo.setTelephone(studentInfo.getTelephone());
                    else checkResult += "第" + (rowNum + 1) + "行数据中的联系电话不正确，请重新确认!;";
                }

                if (StringUtils.isEmpty(checkResult)) {
                    list.add(this.excelToInsertStudentInfo(studentInfo));
                } else {
                    checkValidStr.append(checkResult);
                }
            }
        }

        if (StringUtils.isNotBlank(checkValidStr.toString())) {
            if (CollectionUtils.isNotEmpty(list)) {
                ExcelImportUtils.buildHtmlStr(checkValidStr, "其余项已成功导入！");
            }

            result.put("success", false);
            result.put("message", checkValidStr.toString());
        }

        return result;
    }

    @Override
    public void downloadTemplate(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        String templateFileName = null;
        List<List<String>> rowTitleMergeStrList = new ArrayList<>();
        List<List<String>> rowTitleStrList = new ArrayList();
        List<List<String>> rowFieldStrList = new ArrayList();
        List<List<Integer>> columnWidthList = new ArrayList();
        List<String> sheetNameList = new ArrayList();
        List<String> titleNameList = new ArrayList();

        templateFileName = ExcelConstants.TEMPLATE_FILE_NAME;
        rowTitleMergeStrList.add(ExcelConstants.ROW_TITLE_LIST_MERGE);
        rowTitleStrList.add(ExcelConstants.ROW_TITLE_LIST);
        rowFieldStrList.add(ExcelConstants.ROW_FIELD_LIST);
        columnWidthList.add(ExcelConstants.COLUMN_WIDTH_LIST);
        sheetNameList.add(ExcelConstants.SHEET_NAME);
        titleNameList.add(ExcelConstants.TITLE_NAME);
        HSSFWorkbook exportWorkBook = null;

        for (int i = 0; i < sheetNameList.size(); ++i) {
            ExcelExportUtils.ExcelParam excelParam = new ExcelExportUtils.ExcelParam();
            ExcelExportUtils.buildExportData((List) rowTitleMergeStrList.get(i), (List) rowTitleStrList.get(i), (List) rowFieldStrList.get(i), (List) columnWidthList.get(i), (List) null, templateFileName, (String) sheetNameList.get(i), (String) titleNameList.get(i), excelParam);
            exportWorkBook = ExcelExportUtils.getExportWorkBook(exportWorkBook, excelParam, req, resp);
        }

        ExcelExportUtils.exportExcelData(exportWorkBook, templateFileName, req, resp);
    }

    private String checkDataIsValid(StudentInfo studentInfo, int rowNum) {
        StringBuilder sb = new StringBuilder();
        if (StringUtils.isEmpty(studentInfo.getSno())) {
            ExcelImportUtils.buildHtmlStr(sb, "第" + (rowNum + 1) + "行数据中的学号不能为空，请重新确认！");
        }

        if (StringUtils.isEmpty(studentInfo.getSname())) {
            ExcelImportUtils.buildHtmlStr(sb, "第" + (rowNum + 1) + "行数据中的姓名不能为空，请重新确认！");
        }

        if (StringUtils.isEmpty(studentInfo.getTelephone())) {
            ExcelImportUtils.buildHtmlStr(sb, "第" + (rowNum + 1) + "行数据中的联系电话不能为空，请重新确认！");
        }

        if (StringUtils.isNotBlank(studentInfo.getGender())) {
            if ("男，女".indexOf(studentInfo.getGender()) > -1) {
                if ("男".equals(studentInfo.getGender())) {
                    studentInfo.setGender("1");
                } else if ("女".equals(studentInfo.getGender())) {
                    studentInfo.setGender("0");
                }
            } else {
                ExcelImportUtils.buildHtmlStr(sb, "第" + (rowNum + 1) + "行数据中的性别:'" + studentInfo.getGender() + "'不是“男，女”的其中一项，请重新确认！");
            }
        }

        // 学号已存在于系统，给出相应提示，不导入该学生的信息
        /*if (StringUtils.isNotBlank(studentInfo.getSno())) {
            StudentInfo record = this.studentInfoMapper.selectBySno(studentInfo.getSno());
            if (record != null)
                ExcelImportUtils.buildHtmlStr(sb, "第" + (rowNum + 1) + "行数据中的学号:'" + studentInfo.getSno() + "'已存在于系统，请重新确认！");
        }*/

        return sb.toString();
    }

    private StudentInfo excelToInsertStudentInfo(StudentInfo studentInfo) throws Exception {
        this.saveStudentInfo(studentInfo);
        return studentInfo;
    }

    private void saveStudentInfo(StudentInfo studentInfo) {
        if (studentInfo != null) {
            StudentInfo record = null;
            if (StringUtils.isNotBlank(studentInfo.getSno())) {
                record = this.studentInfoMapper.selectBySno(studentInfo.getSno());
                if (record != null) {   // 学号已存在于系统，则更新该学生的信息
                    studentInfo.setModifyTime(new Date());
                    this.studentInfoMapper.updateBySno(studentInfo);
                } else {
                    studentInfo.setSid(UUID.randomUUID().toString());
                    studentInfo.setCreateTime(new Date());
                    this.studentInfoMapper.insertItem(studentInfo);
                }
            }
        }
    }

    private boolean validateNumber(String number) {
        return Pattern.compile("^\\d+$").matcher(number).matches(); // 非负整数
    }

    private boolean validateTelephone(String telephone) {
        return Pattern.compile("(^(0[0-9]{2,3}\\\\-)?([2-9][0-9]{6,7})+(\\\\-[0-9]{1,4})?$)|(^0?[1][356789][0-9]{9}$)").matcher(telephone).matches();   // 电话号码和手机号码校验
    }
}

12.7 ExcelController

@RestController
@RequestMapping("/excel")
public class ExcelController {
    @Autowired
    private ExcelService excelService;

    /**
     * 导入
     *
     * @param req
     * @param resp
     * @throws Exception
     */
    @GetMapping({"/export"})
    public void exportExcel(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        try {
            this.excelService.exportExcel(req, resp, ExcelConstants.EXPORT_FILE_NAME, ExcelConstants.SHEET_NAME, ExcelConstants.TITLE_NAME);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * 导出
     *
     * @param file
     * @param typeId
     * @return
     * @throws Exception
     */
    @RequestMapping({"/import"})
    public Map<String, Object> importExcel(MultipartFile file, String typeId) throws Exception {
        Map<String, Object> result = new HashMap<>();
        try {
            if (file == null) {
                result.put("success", false);
                result.put("message", "导入文件不能为空!");
                return result;
            } else {
                Workbook databook = ExcelImportUtils.getWorkbook(file);
                List<String> templateSheetNameList = new ArrayList();
                List<List<String>> templateRowTitleList = new ArrayList();
                templateSheetNameList.add(ExcelConstants.SHEET_NAME);
                List<String> titleList = new ArrayList<>();
                for (int i = 0; i < ExcelConstants.ROW_TITLE_LIST.size(); i++) {
                    if (!"|".equals(ExcelConstants.ROW_TITLE_LIST.get(i)))
                        titleList.add(ExcelConstants.ROW_TITLE_LIST.get(i));
                }
                templateRowTitleList.add(titleList);
                String templateIsValidResult = ExcelImportUtils.checkTemplateIsValid(databook, templateSheetNameList, templateRowTitleList);

                result.put("success", false);
                result.put("message", templateIsValidResult);
                return (StringUtils.isNotBlank(templateIsValidResult) ? result : this.excelService.importExcel(file, 0, typeId));
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * 导入模板下载
     *
     * @param req
     * @param resp
     * @throws Exception
     */
    @GetMapping({"/template"})
    public void downloadTemplate(HttpServletRequest req, HttpServletResponse resp) throws Exception {
        this.excelService.downloadTemplate(req, resp);
    }
}

13. JSON 与 JavaBean 之间的相互转换

转载自 使用FastJson对JSON字符串、JSON对象及JavaBean之间的相互转换。