Java poi基于模板形式导出Excel的使用（二）

基于模板的形式导出Excel

1、Excel处理类中使用JSONArray或者JSONObject定义需要输出数据的模板方法:

需要使用到的jar:

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.6</version>
</dependency>

public static JSONArray baseInfo() {
    //name为Excel中输出的表头,value与数据库输出的字段名一致
    JSONObject json = null;
    JSONArray array = new JSONArray();
    
    json = new JSONObject();
    json.put("name","姓名");
    json.put("value","studentName");
    array.add(json);
    
    json = new JSONObject();
    json.put("name","年龄");
    json.put("value","age");
    array.add(json);
    
    json = new JSONObject();
    json.put("name","性别");
    json.put("value","gender");
    array.add(json);
    
    json = new JSONObject();
    json.put("name","联系方式");
    json.put("value","studentPhone");
    array.add(json);

    return array;

}

2、Excel处理类中对数据进行处理

public void exportExcel(HSSFWorkbook book, int sheetNum, String sheetTitle, JSONArray columnsArr, List<Map<String, Object>> exportsList) throws Exception {
    /* 生成一个表格*/
    HSSFSheet sheet = book.createSheet(sheetTitle);
    /* sheetNum为sheet页,从0开始,sheetTitle为sheet的名称 */
    book.setSheetName(sheetNum, sheetTitle);

    /* 1 设置样式*/
    HSSFCellStyle cellStyle = book.createCellStyle();

    for (int i = 0; i <= columnsArr.size(); i++) {
        if (i == 0) {
            sheet.setColumnWidth(i, 3000);
        } else {
            sheet.setColumnWidth(i, 5800);
        }
    }
    /* 设置单元格边框*/
    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 设置下面
    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 设置左边
    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 设置右边
    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 设置顶部

    /* 2 设置样式居中*/
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平居中
    cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中

    HSSFRow titleRow = sheet.createRow(0);
    /*行高设置1：20*/
    titleRow.setHeight((short) 1200);

    /*克隆样式 生成数据内容*/

    /* 3 设置字体*/
    HSSFFont font = book.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
    font.setFontHeightInPoints((short)11);
    font.setFontName("黑体");

    //设置样式2，并克隆之前的样式
    HSSFCellStyle style2 = book.createCellStyle();
    style2.cloneStyleFrom(cellStyle);

    // 指定当单元格内容显示不下时自动换行
    cellStyle.setWrapText(true);
    // 4.设置首行单元格
    HSSFCell cell = null;
    for (int i = 0; i < columnsArr.size(); i++) {
        Object columns = columnsArr.get(i);
        JSONObject columnsJson = JSONObject.parseObject(columns.toString());
        cell = titleRow.createCell(0);
        cell.setCellValue("序号");
        cellStyle.setFont(font);// 将字体应用到样式中
        cell.setCellStyle(cellStyle);// 把样式应用到每个单元格中
        cell = titleRow.createCell(i + 1);

        cell.setCellValue(columnsJson.getString("name"));
        cell.setCellStyle(cellStyle);// 把样式应用到每个单元格中
    }
    
    // 5.设置首行之下的单元格
    int rowIndex=1;
    int i=0;
    for (Map<String, Object> map : exportsList) {
        //从第一行开始写数据内容
        HSSFRow row = sheet.createRow(rowIndex);
        for (int j = 0; j < columnsArr.size(); j++){
            Object colums = columnsArr.getString(j);
            JSONObject columnsJson = JSONObject.parseObject(colums.toString());
            String value = columnsJson.getString("value");
            String text = map.get(value).toString();
            //序号
            cell = row.createCell(0);
            cell.setCellValue(i+1);
            cell.setCellStyle(style2);// 把样式应用到每个单元格中
             //内容
            cell = row.createCell(j + 1);
            cell.setCellValue(text);
            cell.setCellStyle(style2);// 把样式应用到每个单元格中
        }
        i++;
        rowIndex++;
    }
}

3、dao接口:

List<Map<String, Object>> exportTatolExcel(Map<String, String> map);

//mapping.xml文件
    <select id="exportTatolExcel" parameterType="map" resultType="java.util.Map">
        SELECT
			IFNULL(s.name,'')AS studentName,
			IFNULL(s.age,'')AS age,
			IFNULL(s.gender,'') AS gender,
            IFNULL(s.phone,'') AS studentPhone
		from student s
    </select>

4、serviceImpl实现类:

public String exportMonthly(Map<String, String> map, HttpServletRequest request, HttpServletResponse response) throws Exception {
    // 创建工作簿
    HSSFWorkbook book = new HSSFWorkbook();
    String sheet = "学生基本信息";
    JSONArray columnsArr = ExcelUtil.monthlyExport();
    map.put("sys_org_code", ResourceUtil.getSessionUserName().getCurrentDepart().getOrgCode());
    List<Map<String, Object>> statistic = dao.exportTatolExcel(map);
    List<Map<String, Object>> Details = dao.detailsExcel(map);
    //将文件存在指定位置
    String fileName = UUID.randomUUID().toString().replace("-", "") + ".xls";
    String filePath = ResourceUtil.getConfigByName("uploadpath") + "/";
    File file = new File(filePath);
    if (!file.exists()) {
        file.mkdirs();
    }
    FileOutputStream fileOut = new FileOutputStream(filePath + fileName);
    ExcelUtil excelUtil = new ExcelUtil();
    excelUtil.exportExcel(book, 0, sheet, columnsArr, statistic);
    excelUtil.exportExcel(book, 1, sheet1, columnsArr1, Details);
    String url = "uploadController.do?readFile&fileName=" + filePath + fileName;
    saveFile(url, fileName);
    book.write(fileOut);
    fileOut.close();
    return url;
}

//保存方法按照上传至数据库之后在下载的方式:
private void saveFile(String url, String fileName) {
        TbAttachEntity t = new TbAttachEntity();
        t.setCreateTime(DateUtil.convertToShowTime1(new Date(), "yyyy-MM-dd hh:mm:ss"));
        t.setIsDelete("0");
        t.setPicUrl(url);
        t.setName(fileName);
        systemService.save(t);
    }

@Override
	public <T> Serializable save(T entity) {
		return commonDao.save(entity);
	}