- Controller.cs
- @CrossOrigin(allowCredentials="true", allowedHeaders="*", methods={RequestMethod.GET,
RequestMethod.POST, RequestMethod.DELETE, RequestMethod.OPTIONS,
RequestMethod.HEAD, RequestMethod.PUT, RequestMethod.PATCH}, origins="*")
- @RequestMapping("/ot")
- @ResponseBody
- public void ot(String key, HttpServletResponse response) {
- try {
- String storeName = key+".xlsx";
- XSSFWorkbook workBook = excelService.outputExcel(key);
- ByteArrayOutputStream bt = new ByteArrayOutputStream();
- workBook.write(bt);
- workBook.close();
- bt.flush();
- ByteArrayInputStream bis = new ByteArrayInputStream(bt.toByteArray());
- response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
- response.setHeader("Content-disposition", "attachment; filename="
- + new String(storeName.getBytes("utf-8"), "ISO8859-1"));
- // 设置输出长度
- response.setHeader("Content-Length", String.valueOf(bt.size()));
- BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());
- byte[] buff = new byte[2048];
- int bytesRead;
- while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
- bos.write(buff, 0, bytesRead);
- }
- // 关闭流
- bis.close();
- bos.close();
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- Service.cs
- public XSSFWorkbook outputExcel(String key){
- String json = "{status:'OK', msg:'导出完毕!'}";
- String schame = key.split("\\.")[0];
- String tablename = key.split("\\.")[1];
- XSSFWorkbook workBook = new XSSFWorkbook();
- XSSFSheet sheet = workBook.createSheet();
- workBook.setSheetName(0,"Sheet1");
- XSSFRow titleRow = sheet.createRow(0);
- titleRow.createCell(0).setCellValue(tablename);
- Jedis jedis = new Jedis("10.8.4.94");
- List<String> tables = new ArrayList<String>();
- jedis.select(2);
- try {
- tables = jedis.lrange("db_tables", 0, -1);
- } catch (Exception e) {
- // TODO: handle exception
- }finally{
- jedis.close();
- }
- for(String s:tables){
- JSONObject obj = JSON.parseObject(s);
- if(obj.getString("owner").equals(schame) && obj.getString("table_name").equals(tablename)){
- titleRow.createCell(1).setCellValue(obj.getString("comment"));
- }
- }
- XSSFRow secondRow = sheet.createRow(1);
- secondRow.createCell(0).setCellValue("字段英文名");
- secondRow.createCell(1).setCellValue("字段类型");
- secondRow.createCell(2).setCellValue("注释");
- secondRow.createCell(3).setCellValue("是否主键");
- secondRow.createCell(4).setCellValue("是否非空");
- secondRow.createCell(5).setCellValue("默认值");
- jedis = new Jedis("10.8.4.94");
- List<String> columns = new ArrayList<String>();
- jedis.select(2);
- try {
- columns = jedis.lrange(key, 0, -1);
- } catch (Exception e) {
- // TODO: handle exception
- }finally{
- jedis.close();
- }
- int k = 2;
- for(String s: columns){
- JSONObject obj = JSON.parseObject(s);
- XSSFRow row = sheet.createRow(k);
- row.createCell(0).setCellValue(obj.getString("column_name"));
- row.createCell(1).setCellValue(obj.getString("data_type").equals("VARCHAR2")?(obj.getString("data_type")+"("+obj.getString("data_length")+")"):obj.getString("data_type"));
- row.createCell(2).setCellValue(obj.getString("comment"));
- row.createCell(3).setCellValue(obj.getString("pk"));
- row.createCell(4).setCellValue(obj.getString("nullable"));
- row.createCell(5).setCellValue(obj.getString("data_default"));
- k++;
- }
- return workBook;
- }
来源: http://www.bubuko.com/infodetail-2637184.html