小常识-笔记-JAVA导出多sheet的EXCEL工具类（代码较为简单）

最新推荐文章于 2024-03-21 09:25:18 发布

乾九幽

最新推荐文章于 2024-03-21 09:25:18 发布

阅读量533

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/ilifei/article/details/107197675

版权

java 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

工具类代码

package com.utils;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Hyperlink;

public class RegexExcelUtilTMP {
	
	/**
	 * 
	 * @param indexPageName		目录页sheet名称
	 * @param sheetColumn		取sheet列名称的字段名
	 * @param dataColumn		取sheet列数据的字段名
	 * @param title				所有sheet数据标题、这里中写的是统一列名若需要不同列名的需要自己扩展一下
	 * @param sheetMapList		每个sheet的数据｛sheet名称，content数据｝
	 * @return
	 * @throws IOException
	 */
	public static HSSFWorkbook createMutiSheet(String indexPageName, String sheetColumn, String dataColumn, String[] title,
			@SuppressWarnings("rawtypes") List<Map> sheetMapList) 
			throws IOException {
		/* 建立HSSFWorkbook对象 */
		HSSFWorkbook wb = new HSSFWorkbook();

		/* 建立索引页sheet */
		HSSFSheet sheet = wb.createSheet(indexPageName);
		
		int index = 0;
		for (Map<?, ?> sheetMap : sheetMapList) {
			//取出每个sheet的名称
			String sheetName = sheetMap.get(sheetColumn)+"";
			
			//取出每个sheet的数据 
			Object object = sheetMap.get(dataColumn);
			String[][] cotent = (String[][])object;
			
			HSSFRow row = sheet.createRow((short) index);
			HSSFCell likeCell = row.createCell(0);
			Hyperlink hyperlink = new HSSFHyperlink(Hyperlink.LINK_DOCUMENT);
		
			// "#"表示本文档 "sheetName"表示sheet页名称 "A10"表示第几列第几行
			hyperlink.setAddress("#"+sheetName+"!A1");
			likeCell.setHyperlink(hyperlink);
			// 点击进行跳转
			likeCell.setCellValue(sheetName);
			
			/* 连接跳转 */
			/* 设置为超链接的样式 */
			HSSFCellStyle linkStyle = wb.createCellStyle();
			HSSFFont cellFont = wb.createFont();
			cellFont.setUnderline((byte) 1);
			cellFont.setColor(HSSFColor.BLUE.index);
			linkStyle.setFont(cellFont);
			likeCell.setCellStyle(linkStyle);
			
			// 创建一个居中格式
			HSSFCellStyle style = wb.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			
			/* 建立第每个子sheet对象 */
			HSSFSheet sheetDetail = wb.createSheet(sheetName); // 建立新的sheet对象
			
			creatSheet(sheetDetail, style, title, cotent);
			
			index++;
		}
		return wb;
	}
	
	
	/**
	 * 填充每个sheet中的内容、样式、标题
	 * @param sheet
	 * @param style
	 * @param title 
	 * @param values
	 */
	public static void creatSheet(HSSFSheet sheet, HSSFCellStyle style, String[] title, String[][] values) {
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        //声明列对象
        HSSFCell cell = null;
        //存储最大列宽  
        Map<Integer,Integer> maxWidth = new HashMap<Integer,Integer>(); 

        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
            
            maxWidth.put(i,title[i].length());
        }
        
        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                HSSFCell createCell = row.createCell(j);
				createCell.setCellValue(values[i][j]);

                int length = createCell.getStringCellValue().getBytes().length  * 256 + 200;  
                //这里把宽度最大限制到15000  
                if (length>15000){  
                	length = 15000;  
                }  
                maxWidth.put(j,Math.max(length,maxWidth.get(j)));          
            }
        }
        
        // 列宽自适应  
        for (int i = 0; i < title.length; i++) {  
            sheet.setColumnWidth(i,maxWidth.get(i));  
        }  
	}
}

调用Controller触发浏览器下载

package com.unis.hlht.cjlksj.server.controller;

import com.unis.hlht.cjlksj.server.common.rest.LiangKuRest;
import com.unis.hlht.cjlksj.server.dto.instruction.InstructionParasDTO;
import com.unis.hlht.cjlksj.server.dto.upload.UploadDTO;
import com.unis.hlht.cjlksj.server.entity.LiangKuApiEntity;
import com.unis.hlht.cjlksj.server.entity.RestCountEntity;
import com.unis.hlht.cjlksj.server.enums.instruction.InstructionTypeEnum;
import com.unis.hlht.cjlksj.server.mapper.GrabResultMapper;
import com.unis.hlht.cjlksj.server.mapper.LiangKuApiMapper;
import com.unis.hlht.cjlksj.server.mapper.SysUploadErrorResultMapper;
import com.unis.hlht.cjlksj.server.server.DataUpLoadServer;
import com.unis.hlht.cjlksj.server.server.InstructionServer;
import com.unis.hlht.cjlksj.server.test.ExcelUtils;
import com.unis.hlht.cjlksj.server.utils.DateUtil;
import com.unis.hlht.cjlksj.server.utils.ExcelUtil;
import com.unis.hlht.cjlksj.server.utils.PingUtil;
import com.unis.hlht.cjlksj.server.utils.RegexExcelUtilTMP;
import com.unis.hlht.cjlksj.server.utils.ResponseWrapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import lombok.extern.log4j.Log4j2;

import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.HttpURLConnection;
import java.net.URL;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

@Log4j2
@SuppressWarnings("all")
@Controller
public class ExcelController {

	@GetMapping(value = "/dxp")
	public void dxp(HttpServletRequest request,HttpServletResponse response) {
		//查询数据库数据
		List<Map> mlist = getData();
        //excel文件名
        String fileName = DateUtil.getCurrentStringDate() + ".xls";
        //excel标题
        String[] title = {"tname", "kdmc", "info", "name", "val"};
        //sheet名
        String sheetName = "错误数据";
        
        //数据逻辑处理
		List<Map> sheetMapList = new ArrayList<>();
		for (Map sheet : sheetMapList) {
			String sName = sheet.get("sheetColumn")+"";
			Integer count = Integer.valueOf(sheet.get("dataCount")+"");
			String[][] content = new String[count][5];
			
			int j = 0;
	        for (int i = 0; i < mlist.size(); i++) {
	            Map obj = mlist.get(i);
	            String kdmc = obj.get("kdmc").toString();
				if (kdmc.contains(sName)) {
					content[j][0] = obj.get("tname").toString();
					content[j][1] = kdmc;
					content[j][2] = obj.get("info").toString();
					content[j][3] = obj.get("name").toString();
					content[j][4] = obj.get("val").toString();
					j++;
				}
	       
	        }
	        sheet.put("content", content);
			System.out.println(sName + "---"+count+"++++"+content.length);
		}
		
        //创建HSSFWorkbook
        HSSFWorkbook wb = RegexExcelUtilTMP.createMutiSheet
        		("汇总页面", "sheetColumn", "content", title, sheetMapList);
        //响应到客户端
        try {
            try {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            OutputStream outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
        	log.info("导出异常");
        }
	}
}