SpringBoot_报表_JXL

依赖pom.xml

	<dependency>
		<groupId>net.sourceforge.jexcelapi</groupId>
		<artifactId>jxl</artifactId>
		<version>2.6.12</version>
	</dependency>

导入

InputStream inputStream = file.getInputStream(); 
Workbook rwb = null;
rwb = Workbook.getWorkbook(inputStream);
Sheet sheet = rwb.getSheet(index);
int rows = sheet.getRows();
int columns = sheet.getColumns();
StringBuffer sb = new StringBuffer();
for (int i = 1; i < rows; i++) {
	sb.append("insert into ").append(tableName).append(" values");
	sb.append("(uuid(),"); // Mysql：uuid()  Oracle：sys_guid()
	for (int j = 0; j < columns; j++) {
		// 表示获取第i行第j列
		Cell cell = sheet.getCell(j, i);
		String celll = cell.getContents();
		if(celll.startsWith("'")) {
			celll = celll.substring(1);
		}
		if(celll.endsWith("'")) {
			celll = celll.substring(0, celll.length()-1);
		}
		sb.append("'").append(celll).append("'").append(",");
	}
	sb.deleteCharAt(sb.length() - 1).append(")");
	dataHandlerDao.exeSql(sb.toString());
	sb.setLength(0);
}

导出

// 通过token获取查询语句
String sql = CacheUtils.get(token);
if (StringUtils.isBlank(sql)) {
	throw new RuntimeException("无法获取到查询sql");
}
OutputStream os = null;
WritableWorkbook workbook = null;
try {
	List<Map<String, Object>> list = dataHandlerService.out(sql);
	if (list == null || list.size() < 1) {
		throw new RuntimeException("无法查询到数据");
	}
	os = response.getOutputStream();// 取得输出流
	response.reset();// 清空输出流
	// 下面是对中文文件名的处理
	response.setCharacterEncoding("UTF-8");// 设置相应内容的编码格式
	response.setHeader("Content-Disposition", "attachment;filename=" + UUID.randomUUID() + ".xls");
	response.setContentType("application/msexcel");// 定义输出类型
	// 创建工作薄
	workbook = Workbook.createWorkbook(os);
	// 创建新的一页
	WritableSheet sheet = workbook.createSheet("sheet", 0);
	// 通过查询语句获取数据
	int size = list.size();
	for (int j = 0; j < size; j++) {
		Map<String, Object> map = list.get(j);
		Set<String> keySet = map.keySet();
		int i = 0;
		for (String str : keySet) {
			if(j==0) {
				//添加标题
				Label title = new Label(i, 0, str);
				sheet.addCell(title);
			}
			//填充数据
			Label content = new Label(i, j + 1, list.get(j).get(str).toString());
			sheet.addCell(content);
			i = i + 1;
		}
	}
	// 把创建的内容写入到输出流中，并关闭输出流
	workbook.write();
} catch (Exception e) {
	e.printStackTrace();
} finally {
	try {
		if (workbook != null) {
			workbook.close();
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
	try {
		if (os != null) {
			os.close();
		}
	} catch (IOException e) {
		e.printStackTrace();
	}
}