前端导出

jsonExportExcel.js

JS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
/**
 * jsonExportExcel.js
 */
var JSONToExcelConvertor = window.JSONToExcelConvertor = function(arrData, FileName, ShowLabel) {
	var excel = '<table>';

	//设置表头  
	var row = "<tr>";
	var keys = [];
	for (var key in ShowLabel) {
		keys.push(key);
		row += "<td>" + ShowLabel[key] + '</td>';
	}

	//换行  
	excel += row + "</tr>";

	//设置数据  
	for (var i = 0; i < arrData.length; i++) {
		var row = "<tr>";

		var rowdata = arrData[i];
		for(var j=0; j<keys.length; j++){
			row += '<td>' + rowdata[keys[j]] + '</td>';
		}

		excel += row + "</tr>";
	}
	excel += "</table>";

	var excelFile = "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>";
	excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">';
	excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel';
	excelFile += '; charset=UTF-8">';
	excelFile += "<head>";
	excelFile += "<!--[if gte mso 9]>";
	excelFile += "<xml>";
	excelFile += "<x:ExcelWorkbook>";
	excelFile += "<x:ExcelWorksheets>";
	excelFile += "<x:ExcelWorksheet>";
	excelFile += "<x:Name>";
	excelFile += "{worksheet}";
	excelFile += "</x:Name>";
	excelFile += "<x:WorksheetOptions>";
	excelFile += "<x:DisplayGridlines/>";
	excelFile += "</x:WorksheetOptions>";
	excelFile += "</x:ExcelWorksheet>";
	excelFile += "</x:ExcelWorksheets>";
	excelFile += "</x:ExcelWorkbook>";
	excelFile += "</xml>";
	excelFile += "<![endif]-->";
	excelFile += "</head>";
	excelFile += "<body>";
	excelFile += excel;
	excelFile += "</body>";
	excelFile += "</html>";


	var uri = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(excelFile);

	var link = document.createElement("a");
	link.href = uri;

	link.style = "visibility:hidden";
	link.download = FileName + ".xls";

	document.body.appendChild(link);
	link.click();
	document.body.removeChild(link);
}

使用

1
2
3
4
5
6
7
8
9
10
11
12
// 标题信息
var title = {deptname:"登记机构",urgeflag:"是否督办 ",
					eventcode:"事件编号",sourcecodename:"事件来源",
					eventtypename:"事件类别",classcodename:"大类",
					subclasscodename:"小类",statename:"事件状态",
					intro:"事件描述",eventaddress:"地址描述"};
					
//导出数据
var data = [{"avgevaluate":"","classcode":"1474616314219830","classcodename":"市容环境","createtime":"2019-10-23 17:01:48","creator":"154639663","creatorname":"何建平","delaytime":"","deptcode":"410105003005001","deptname":"安定区-福台路街道-凤台社区(C3)-C3网格","efficiency":"","emergency":"030604","end":0,"endtime":"","evaluate":"","evaluatetime":"","eventaddress":"福台幼儿园门前","eventcode":"20191023410105003005001006","eventid":"1571763875394812","eventtype":"1","eventtypename":"城市事件类","filedtime":"","geoarcgis":"","geobaidu":"104.607222,35.596623","intro":"幼儿园门前流动摊贩","iscase":"1","istwodispatch":"0","isvisitback":"0","lastdeptname":"","levelcode":"","monitors":"154639663","openid":"","operatorname":"","opinion":"","overtimeflag":"","party":"何建平","partyphone":"18393273256","receiver":"1547769930766580","receivername":"李丽丽","receivetime":"2019-10-23 17:27:48","results":"","rows":0,"satisfaction":"","satisfy":"","selfflag":"0","sourcecode":"030101","sourcecodename":"监督员上报","stageperiod":"","stagestart":"","stagestart1":"","start":0,"starttime":"2019-10-23 17:27:48","state":"030408","statename":"已派遣待处理","subclasscode":"1550134405176118","subclasscodename":"占道经营","unionflag":"0","urgeflag":"未督办"}];

//导出
JSONToExcelConvertor(data, "文件名", title);

问题

  1. 数字导出到excel时，可能以科学计数法展示

后端导入导出

POI

https://juejin.im/post/5c09e559e51d451da152df9c

JXL

Maven

1
2
3
4
5
<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

JS

1
2
var url = rootPath +'/DetailsEvent/export.jspx?filename=事件列表';
document.location.href=url;

Java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
package com.bjtcrj.scm.check.controller;

import com.bjtcrj.scm.common.utils.StringEx;
import com.bjtcrj.scm.core.controller.BaseController;
import com.bjtcrj.scm.event.business.dto.EventDto;
import jxl.Workbook;
import jxl.write.*;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;

@Controller
@RequestMapping(value = "/DetailsEvent")
public class DetailsEventController extends BaseController {

	@RequestMapping(value = "/export")
	public void export(HttpServletRequest request, HttpServletResponse response) throws Exception{
		//导出的数据
		List<EventDto> l = (List<EventDto>)this.getSessionAttribute(request, "detaileventlist");
		
		//导出文件名
		String filename=request.getParameter("filename");

		//定义输出流，以便打开保存对话框______________________begin
		// 清空输出流       
		response.reset(); 
		request.setCharacterEncoding("utf-8");
		response.setHeader("Content-Disposition","attachment;filename="+
				URLEncoder.encode(filename+StringEx.getDateStringYMD(new Date())+".xls", "UTF-8"));
		response.setCharacterEncoding("utf-8");
		response.setHeader("Connection", "close");
		response.setHeader("Content-Type", "application/octet-stream");

		OutputStream out = response.getOutputStream();
		WritableWorkbook book = null;
		try {
			book=Workbook.createWorkbook(out);
			// 生成名为"学生"的工作表，参数0表示这是第一页
			WritableSheet sheet = book.createSheet("sheet_one", 0);
			
			//单元格格式
			WritableFont wf = new WritableFont(WritableFont.TIMES, 14, WritableFont.NO_BOLD, false);
			WritableCellFormat wcf  = new WritableCellFormat(wf);
			wcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
			
			if(l!=null && !l.isEmpty()){
				String title[]={"是否督办","事件编号","登记机构","登记人","事件来源","事件类别","大类","小类",
						"事件状态","事件描述", "地址描述"};
				for(int i=0;i<11;i++)  //title
				{
					sheet.addCell(new Label(i,0,title[i], wcf));
				}
				for(int i=0; i<l.size(); i++){
					sheet.addCell(new Label(0, i+1, l.get(i).getUrgeflag(), wcf));
					sheet.addCell(new Label(1, i+1, l.get(i).getEventcode(), wcf));
					sheet.addCell(new Label(2, i+1, l.get(i).getDeptname(), wcf));
					sheet.addCell(new Label(3, i+1, l.get(i).getCreatorname(), wcf));
					sheet.addCell(new Label(4, i+1, l.get(i).getSourcecodename(), wcf));
					sheet.addCell(new Label(5, i+1, l.get(i).getEventtypename(), wcf));
					sheet.addCell(new Label(6, i+1, l.get(i).getClasscodename(), wcf));
					sheet.addCell(new Label(7, i+1, l.get(i).getSubclasscodename(), wcf));
					sheet.addCell(new Label(8, i+1, l.get(i).getStatename(), wcf));
					sheet.addCell(new Label(9, i+1, l.get(i).getIntro(), wcf));
					sheet.addCell(new Label(10, i+1, l.get(i).getEventaddress(), wcf));
				}
			}
			// 写入数据并关闭文件
			book.write();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(book!=null){
				try {
					book.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
}

easyexcel

https://github.com/alibaba/easyexcel