本文由 资源共享网 – zgwxw 发布,转载请注明出处,如有问题请联系我们!批量导出json数据到Excel的Java类
资源语言: 中文
java资源(批量导出json数据到Excel的Java类)网址:https://www.08i8.com/ttkfzy/detail83550.html;转载请注明!
实现了json数据导入到Excel的方法
实现了将标准的json字符串转换成map,并将数据解析到Excel的方法,本方法简单实用,可以批量导出数据到Excel文档中
package com.hxb.xn.utils; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.Date; import java.util.Map; import org.apache.commons.collections.MapUtils; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; /** * @author luze25962 * @version 创建时间:2019年7月5日 下午2:18:25 * 说明: 账户汇总excel报表 * 来源:www.08i8.com */ public class AcctExcelReportFile { /** * 柜面交易明细统计报表 excel * @param map * @param dir * @throws IOException */ public String makeGuiMianDetailDataExcel(Map<String, String> map,String dir) throws IOException{ //生成的Excel文件前缀: String surfixTile = "counterDetail_"; String fileName = getFileName(surfixTile)+".xlsx"; File excel = new File(dir+fileName); if(excel.exists()){ excel.delete(); excel.createNewFile(); } else{excel.createNewFile();} String[] titles = "供热用户编号 ,客户名称,交易金额,缴费账户,交易日期,交易流水,交易类型,交易状态,缴费方式,操作员".split(","); String[] cols = "userNo,userName,transAmt,acct,pltDate,pltSerial,transCode,hostStatus,cashFlag,teller".split(","); String body = map.get("body"); //获取流水明细循环json //调用生成Excel方法 makeExcel(excel,titles,cols,body,"柜面交易明细","柜面交易明细汇总报表"); return fileName; } /** * 日缴费统计报表 excel * @param map * @param dir * @throws IOException */ public String makeDayPayExcel(Map<String, String> map,String dir) throws IOException{ //生成的Excel文件前缀: String surfixTile = "dayPay_"; String fileName = getFileName(surfixTile)+".xlsx"; File excel = new File(dir+fileName); if(excel.exists()){ excel.delete(); excel.createNewFile(); } else{excel.createNewFile();} String[] titles = "缴费日期,缴费笔数,缴费总额".split(","); String[] cols = "busiDate,serialCnt,serialAmt".split(","); String body = map.get("body"); //获取流水明细循环json //调用生成Excel方法 makeExcel(excel,titles,cols,body,"日缴费汇总","日缴费汇总报表"); return fileName; } /** * 月缴费统计报表 excel * @param map * @param dir * @throws IOException */ public String makeMonthPayExcel(Map<String, String> map,String dir) throws IOException{ //生成的Excel文件前缀: String surfixTile = "monthPay_"; String fileName = getFileName(surfixTile)+".xlsx"; File excel = new File(dir+fileName); if(excel.exists()){ excel.delete(); excel.createNewFile(); } else{excel.createNewFile();} String[] titles = "缴费月份,缴费笔数,缴费总额,冲正笔数,冲正金额".split(","); String[] cols = "month,serialCnt,serialAmt,correctCnt,correctAmt".split(","); String body = map.get("body"); //获取流水明细循环json //调用生成Excel方法 makeExcel(excel,titles,cols,body,"月缴费汇总","月缴费汇总报表"); return fileName; } //生成Excel的公用代码: public void makeExcel(File excel,String[] titles,String[] cols, String body,String sheetName,String headerName) throws IOException{ JSONObject jsonBody = JSONObject.parseObject(body); JSONArray jsonArray = jsonBody.getJSONArray("data"); SXSSFWorkbook wb = new SXSSFWorkbook(); Sheet sheet = wb.createSheet(sheetName); Font font = wb.createFont(); font.setFontName("黑体"); font.setFontHeightInPoints((short) 16);//设置字体大小 font.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体显示 Font font2 = wb.createFont(); font2.setFontName("黑体"); font2.setFontHeightInPoints((short) 12);//设置字体大小 font2.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体显示 CellStyle cs = wb.createCellStyle(); CellStyle cs1 = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 cs2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 cs2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 cs2.setFont(font2);//要用到的字体格式 cs1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框 cs1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框 cs1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 cs1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 cs.setAlignment(CellStyle.ALIGN_CENTER); cs.setBorderBottom(CellStyle.BORDER_THIN); //下边框 cs.setBorderLeft(CellStyle.BORDER_THIN);//左边框 cs.setBorderTop(CellStyle.BORDER_THIN);//上边框 cs.setBorderRight(CellStyle.BORDER_THIN);//右边框 cs.setAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中 cs.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 // cs.setAlignment(CellStyle.VERTICAL_CENTER); cs.setFont(font);//要用到的字体格式 //设置填充方案 cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //设置预定义填充颜色 cs.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); sheet.addMergedRegion(new CellRangeAddress(0,1,0,titles.length-1)); Row rowHeader = sheet.createRow(0); Cell cellHeader = rowHeader.createCell(0); cellHeader.setCellValue(headerName); cellHeader.setCellStyle(cs); int r = 2; Row title = sheet.createRow(r++);// 表头 for (int h = 0; h < titles.length; h++) { Cell cell = title.createCell(h); cell.setCellValue(titles[h]); cell.setCellStyle(cs2); } for (int i = 0; i < jsonArray.size(); i++) { // 填写数据 Row bodyRow = sheet.createRow(r++); Map<String, Object> row = jsonArray.getJSONObject(i); for (int c = 0; c < cols.length; c++) { // Cell cell = body.createCell(c); Cell createCell = bodyRow.createCell(c); createCell.setCellType(Cell.CELL_TYPE_STRING); createCell.setCellValue(MapUtils.getString(row, cols[c])); createCell.setCellStyle(cs1); } } FileOutputStream fos = new FileOutputStream(excel); wb.write(fos); fos.flush(); fos.close(); } /** * 获取文件名 * @return */ public String getFileName(String surfixTile){ SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");//17位时间戳 Date date = new Date(); String prefix = sdf.format(date); return surfixTile + prefix; } public String dateTransFer(String date){ if(date!=null && !date.equals("")){ return date.replaceAll("-", ""); }else{ return date; } } public static void main(String[] args) { SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");//17位时间戳 Date date = new Date(); String prefix = sdf.format(date); System.out.println("acct_"+prefix); } }
java资源(批量导出json数据到Excel的Java类)网址:https://www.08i8.com/ttkfzy/detail83550.html;转载请注明!