java spring boot 网络请求下载一个excel 后台自动生成 excle 实例

目录

介绍

样式

实例

依赖

控制层代码

---

介绍

• 1.引入jar
• 2.写theadarr
• 3.写rows数据
• 4.遍历数据
• 5.生成文档
• 6.response 返回数据
• ok

 

样式

 

实例

• 如下

依赖

<!--数据导出依赖 excel -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.17</version>
		</dependency>

控制层代码

package com.superman.global.service.excel;

import org.springframework.web.bind.annotation.*;

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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * 导出excel 网路直接下载
 * 用get请求
 * 
 * @author yushen
 *
 */
@RestController
public class ExcelController {

	/**
	 * 下载excel 
	 * 
	 * @param response
	 * @return
	 * @throws Exception
	 */
    @GetMapping(value = "/getUser")
    public String getUser(HttpServletResponse response) throws Exception{
    	//表头
    	String[] theadarr = "天,地,水".split(",");
    	
    	//创作数据
    	List<HashMap<String,Object>> rows = new ArrayList<HashMap<String,Object>>();
        HashMap<String,Object> map99 = new HashMap<String,Object>();
        map99.put("a","a1");
        map99.put("b","b1");
        map99.put("c","c1");
        rows.add(map99);
        rows.add(map99);
        rows.add(map99);
        
        String fileName = "导出excel例子.xls"; //文件名
        String workbookname ="统计表"; //工作页名
        
        dcsj(theadarr,rows,response,fileName,workbookname);
        return "download excel";
    }
    
    /**
     * 导出excel
     * 
     * @param theadarr
     * @param rows
     * @param response
     * @param fileName
     * @param workbookname
     * @throws Exception
     */
    private void dcsj(String[] theadarr,List<HashMap<String,Object>> rows,HttpServletResponse response,
    		String fileName,String workbookname) throws Exception {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(workbookname);
        createTitle(workbook,sheet,theadarr);//创建表头
        
        //设置日期格式
//        HSSFCellStyle style = workbook.createCellStyle();
//        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

        //新增数据行，并且设置单元格数据
        int rowNum=1;
        for(HashMap<String,Object> obj:rows){
            HSSFRow row = sheet.createRow(rowNum);
            int numl = 0;
            for(Object str: obj.keySet()) {
            	row.createCell(numl++).setCellValue(obj.get(str).toString());
            }
            rowNum++;
        }
        

        //生成excel文件
        buildExcelFile(fileName, workbook);

        //浏览器下载excel
        buildExcelDocument(fileName,workbook,response);

    }
    
    //创建表头
    private void createTitle(HSSFWorkbook workbook,HSSFSheet sheet,String[] theadarr){
        HSSFRow row = sheet.createRow(0);
        //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
        sheet.setColumnWidth(1,12*256);
        sheet.setColumnWidth(3,17*256);
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setFont(font);
        HSSFCell cell;
        for (int i = 0; i < theadarr.length; i++) {
        	cell = row.createCell(i);
            cell.setCellValue(theadarr[i]);
            cell.setCellStyle(style);
		}
    }

    //生成excel文件
    protected void buildExcelFile(String filename,HSSFWorkbook workbook) throws Exception{
        FileOutputStream fos = new FileOutputStream(filename);
        workbook.write(fos);
        fos.flush();
        fos.close();
    }

    //浏览器下载excel
    protected void buildExcelDocument(String filename,HSSFWorkbook workbook,HttpServletResponse response) throws Exception{
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(filename, "utf-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

}

 

ok

 

查询更新