TLv8 数据导出

查询SQL

使用标准的数据库查询SQL

select code,name from table where a=1

SQL SELECT 语法

SELECT 列名称 FROM 表名称

SELECT * FROM 表名称

更多帮助可以查看SQL教程|菜鸟教程

代码实现Java

DBUtils

List list = DBUtils.execQueryforList(String key, String sql);

参数：key数据库连接标识

参数：sql查询语句

返回：List Map

JDBC

      SqlSession session = DBUtils.getSession(key);
      Connection aConn = session.getConnection();
      Statement qry = aConn.createStatement();
      ResultSet rs = qry.executeQuery(sql);
      while (rs.next()) {
      //遍历数据
      }

调用实例

public InputStream getExcelInputStream(Map objOut) throws Exception {
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		try {
			ExportExcel.exportExcel(out, objOut);
		} catch (Exception e) {
			throw new Exception(e);
		}
		return new ByteArrayInputStream(out.toByteArray());
	}

objOut类型位HashMap,需要包含的内容为:

excelName:String[]导出Excel表头（列名）

excelKey:String[]导出的数据字段名

数据按行封装成数组，获取时调用：String[] c = objOut.get(excelKey[i]);

自己利用jxl写Excel

    	WritableWorkbook wwb = Workbook.createWorkbook(os); // 建立excel文件
    	int ii = wwb.getNumberOfSheets();
		WritableSheet ws = wwb.createSheet("Sheet" + Integer.toString(ii),ii); // 创建一个工作表
		// 设置单元格的文字格式
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
					WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
					Colour.BLUE);
			WritableCellFormat wcf = new WritableCellFormat(wf);
			wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
			wcf.setAlignment(Alignment.CENTRE);
			ws.setRowView(0, 500);
			
			// 设置列头名
			for (int j = 0; j < excelKeyArray.length; j++) {
				ws.addCell(new Label(j, 0, excelNameArray[j], wcf));
			}
			
			// 设置内容
			wcf = new WritableCellFormat();
			for (int i = 0; i < len; i++) {
				for (int j = 0; j < excelKeyArray.length; j++) {
					String dataValue = ((String[]) hmOut.get(excelKeyArray[j]))[i];
					// System.out.println(dataValue);
					ws.addCell(new Label(j, i + 1, dataValue, wcf));
				}
			}
			wwb.write();
			wwb.close();
    	

将写好的Excel文件传递给控制层为前端下载，需要返回：org.springframework.http.ResponseEntity<byte[]>

    	protected ResponseEntity<byte[]> getByteResponseEntity(InputStream inpbs, MediaType mediaType, String userAgent,
			String filename) {
		ByteArrayOutputStream outs = new ByteArrayOutputStream();
		try {
			int bytesRead;
			while ((bytesRead = inpbs.read()) != -1) {
				outs.write(bytesRead);
			}
			outs.flush();
			outs.close();
			inpbs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		ResponseEntity re = getByteResponseEntity(outs, mediaType, filename, filename);
		return re;
	}
    	

userAgent:@RequestHeader("User-Agent") String userAgent

mediaType:org.springframework.http.MediaType.APPLICATION_OCTET_STREAM

控制层实现

@RequestMapping(value = "/expportAction")
	public Object execute(@RequestHeader("User-Agent") String userAgent) throws Exception {
		response.setContentType("application/vnd.ms-excel");
		return getByteResponseEntity(getExcelStream(), MediaType.APPLICATION_OCTET_STREAM, userAgent, "导出的Excel文件.xls");
	}