Json转为Excel文件导出下载

JSON转Excel导出

1. 先写界面，一个输入框，一个打印按钮，一个清空输入框按钮

我用的是layui框架，随便用啥都行

<div class="mb_l">
    <div class="pager-form">
        <form class="layui-form form_across" action="">
            <div class="layui-form-item">
                <div class="layui-inline">
                    <label class="layui-form-label">输入JSON：</label>
                    <div class="layui-input-inline" style="width: 400px; height: 300px">
                        <textarea id="inputJSON" lay-verify="required" style="width: 400px; height: 300px"
                                  placeholder="请以标准数组格式json字符串进行转换导出excel&#13;把JSON字符串用 [] 括起来&#13;如：[{&quot;name&quot;:&quot;熊大&quot;,&quot;年龄&quot;:&quot;22&quot;},{&quot;name&quot;:&quot;熊二&quot;,&quot;年龄&quot;:&quot;21&quot;}]"
                                  autocomplete="off" class="layui-textarea"></textarea>
                    </div>
                </div>

                <div class="layui-inline ml-xl">
                    <button type="button" class="layui-btn" id="createExcelByJSON">生成Excel</button>
                    <button type="button" class="layui-btn" id="clearJSON">清空输入框</button>
                </div>
            </div>
        </form>
    </div>
</div>

//JSON 转 Excel 导出
$('#createExcelByJSON').click(function (){
    exportExcelByJSON();
})
//清空输入的JSON
$('#clearJSON').click(function () {
    clearJSON();
})

function exportExcelByJSON(){
    let input = valueReplace($('#inputJSON').val());
    let inputJson = {
        "data": input
    }
    console.info(inputJson);
    if (isJsonString($('#inputJSON').val())){
        postDownLoadFile({
            url: "你的地址/exportExcelByJSON",
            data: inputJson
        })
    } else {
        layer.msg('请输入正确的JSON格式数据',{icon: 5});
    }
}

//判断输入的字符串是否为JSON格式
function isJsonString(str) {
    try {
        if (typeof JSON.parse(str) == "object") {
            return true;
        }
    } catch(e) {
    }
    return false;
}

//将输入的双引号进行转义，避免字符串被误读
function valueReplace(v){
    v=v.toString().replace(new RegExp('(["\"])', 'g'),""");
    return v;
}

//清空输入框
function clearJSON() {
    $('#inputJSON').val("")
}

2. Controller层

@Autowired
private IService service;

@PostMapping("/exportExcelByJSON")
@ResponseBody
public ResultDTO exportExcelByJSON(HttpServletRequest request, HttpServletResponse response, @RequestParam String data){
    try{
        String fileName = "Json转Excel导出_" + DateUtils.getCurrentTime("yyyyMMddHHmmss");
        SXSSFWorkbook workbook = service.exportJsonToExcel(fileName,data);
        if (workbook != null) {
            fileName = fileName + ".xlsx";
            FileDownloadUtils.downloadExcel(request, response, fileName, workbook);
        }
    } catch (Exception e) {
        e.printStackTrace();
        try {
            if (!response.isCommitted()) {
                response.setContentType("application/json;utf-8");
                response.setHeader("Content-Disposition", "");
                String html = FileDownloadUtils.getErrorHtml("下载失败");
                response.getOutputStream().write(html.getBytes("UTF-8"));
            }
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }
    return ResultDTO.success();
}

3. Service层

	@Override
    public SXSSFWorkbook exportJsonToExcel(String fileName, String jsonData){
        List<Object> data = new ArrayList<>();
        Map<String, Object> stringObjectMap = new LinkedHashMap<>();

        JSONArray jsonArray = JSONArray.fromObject(jsonData);
        if (jsonArray.size() > 0) {
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                Iterator<String> it = jsonObject.keys();
                while (it.hasNext()) {
                    String key = it.next();
                    stringObjectMap.put(key, jsonObject.get(key).toString());
                    data.add(jsonObject.get(key).toString());
                }
            }
        }
        Set<String> title = stringObjectMap.keySet();
        String[] titleRows = stringObjectMap.keySet().toArray(new String[title.size()]);

        SXSSFWorkbook wb = ExcelSXSSFUtil.getSxssfWorkBookByJson(titleRows, data,null);
        return wb;
    }

4.工具类

public static <T> SXSSFWorkbook getSxssfWorkBookByJson(String[] title, List<Object> dataByJson, SXSSFWorkbook wb) {

        //1.如果SXSSFWorkbook不存在，则创建一个
        if (wb == null) {
            wb = new SXSSFWorkbook();
        }
        //2.创建指定sheetName的sheet
        SXSSFSheet sheet = wb.createSheet("sheet1");
        //单元格样式-居中
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);
        //表头样式
        CellStyle styleTitle = wb.createCellStyle();
        styleTitle.setAlignment(HorizontalAlignment.CENTER);
        styleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        styleTitle.setFillForegroundColor(IndexedColors.TAN.getIndex());
        //3.设置表头
        SXSSFRow row = sheet.createRow(0);
        //声明列对象
        SXSSFCell cell = null;
        // 自动调整列宽
        sheet.trackAllColumnsForAutoSizing();


        List<String> titles = new ArrayList<>(title.length);
        for (int i = 0; i < title.length; i++) {
            titles.add(title[i]);
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(styleTitle);
        }

        DataFormat format = wb.createDataFormat();
        //创建内容
        Iterator<Object> iterator = dataByJson.iterator();
        int index = 0;
        while (iterator.hasNext()) {
            index++;
            row = sheet.createRow(index);

            /*如果需要匹配*/
            for (int i = 0; i < title.length; i++) {
                setCellValueNew(iterator.next(), row.createCell(i), style, format);
            }
        }

        //修改列宽
        for (int i = 0; i < titles.size(); i++) {
            sheet.autoSizeColumn(i);
            // 解决自动设置列宽时，内容含中文时，列宽依然不足，所以，要再加宽一点。
            int width = sheet.getColumnWidth(i) * 17 / 10;
            // java.lang.IllegalArgumentException: The maximum column width for an individual cell is 255 characters.
            int maxExcelRowWidth = 60;
            if (width > maxExcelRowWidth * 256) {
                width = maxExcelRowWidth * 256;
            }
            sheet.setColumnWidth(i, width);
        }

        return wb;
    }

 public static void downloadExcel(HttpServletRequest request, HttpServletResponse response, String fileName, SXSSFWorkbook workbook) {
        //一个流两个头
        //设置下载excel的头信息
        FileDownloadUtils.setExcelHeadInfo(response, request, fileName);

        // 写出文件
        ServletOutputStream os = null;
        try {
            os = response.getOutputStream();
            workbook.write(os);
        } catch (IOException e) {
            logger.error(Thread.currentThread().getStackTrace()[1].getMethodName() + "发生的异常是: ", e);
            throw new RuntimeException(e);
        } finally {
            try {
                if (os != null) {
                    os.flush();
                    os.close();
                }
                if (workbook != null) {
                    workbook.close();
                }
            } catch (Exception e1) {
                throw new RuntimeException(e1);
            }
        }
    }

下面是碎碎念：
好像前端有现成的接口可以直接调用，完全不用搞得像我这么麻烦，但是我前端还没入门呢……我再去研究研究前端怎么实现