SpringBoot_报表_JXL

忘是亡心i 2021-10-06 04:16 429阅读 0赞

### SpringBoot\_报表\_JXL ###

*  依赖pom.xml
 *  导入
 *  导出

# 依赖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();
    	}
    }