JAVA WEB ------ 文件下载及导出数据到office Execl表格

时间：2025-01-02 15:33:56

文件下载需要五步：

1.设置文件ContentType类型

// 设置文件ContentType类型，这样设置，会自动判断下载文件类型

   response.setContentType("multipart/form-data");

2.设置文件头

// 设置文件头：最后一个参数是设置下载文件名

   response.addHeader("Content-Disposition", "attachment;filename=" + new String(title.getBytes(),"ISO8859-1") + ".xls");

3.获取输出流(out)

// 获取输出流

   out = res.getOutputStream();

4.写到输出流(out)中

5.关闭资源

--------华丽的分割线-------web项目导出Excel文档

　　POM ：

　　<dependency>

　　　　<groupId>org.apache.poi</groupId>

　　　　<artifactId>poi</artifactId>

　　　　<version>3.15-beta1</version>

　　</dependency>

　　Class ： RestController

package com.xindatai.ibs.device.act;

import java.io.IOException;

import java.io.OutputStream;

import java.util.List;

import javax.annotation.Resource;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

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

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

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

import com.xindatai.common.web.BaseAct;

import com.xindatai.common.web.resp.ModelMapWriter;

import com.xindatai.common.web.resp.PageRespWriter;

import com.xindatai.ibs.device.act.bean.PerfReqParam;

import com.xindatai.ibs.device.act.validate.PerfActValidate;

import com.xindatai.ibs.device.bean.PerfPM25;

import com.xindatai.ibs.device.service.PerfService;

import com.xindatai.ibs.util.ExportExcel;

@RestController

public class PerfAct extends BaseAct{

    @Resource

    private PerfService service;

    @Resource

    private PerfActValidate validate;

    @Resource

    private ExportExcel<PerfPM25> exportExcel;

    @RequestMapping(value = "/perf" , method = RequestMethod.GET)

    public String perf(HttpServletRequest req,HttpServletResponse res , PerfReqParam param){

        res.setHeader("Access-Control-Allow-Origin", "*");

        ModelMapWriter writer = new ModelMapWriter();

        validate.perf(param);

        if(param.hasErrors()){

            writer = ModelMapWriter.createErrWriter(param);

        }else{

            if(param.getExport()){

                OutputStream out = null;

                try {

                    String title = "来福士PM2.5浓度监测值统计报表";

                    List<PerfPM25> list = service.getPerfsExport(param);

                    String[] headers = {"监测点","统计时间","PM2.5浓度平均值","温度平均值","湿度平均值"};

                    String[] headersName = {"name","perfTime","pm25","temp","humi"};

//                    设置文件ContentType类型，这样设置，会自动判断下载文件类型

                    res.setContentType("multipart/form-data");

//                    设置文件头：最后一个参数是设置下载文件名

                    res.addHeader("Content-Disposition", "attachment;filename=" + new String(title.getBytes(),"ISO8859-1") + ".xls");

//                    获取输出流

                    out = res.getOutputStream();

                    exportExcel.exportExcel(title, headers, headersName, list, out);

                } catch (IOException e) {

                    e.printStackTrace();

                }finally{

                    try {

                        if(null != null){

                            out.close();

                        }

                    } catch (IOException e) {

                        e.printStackTrace();

                    }

                }

            }else{

                PageRespWriter<PerfPM25> query = service.getPerfs(param);

                return toJsonFormatDate(query);

            }

        }

        return toJsonFormatDate(writer);

    }

}

　　Class ： @Service

package com.xindatai.ibs.util;

import java.io.IOException;

import java.io.OutputStream;

import java.lang.reflect.InvocationTargetException;

import java.lang.reflect.Method;

import java.text.SimpleDateFormat;

import java.util.Collection;

import java.util.Date;

import java.util.Iterator;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;

import org.apache.poi.hssf.usermodel.HSSFComment;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFPatriarch;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;

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.springframework.stereotype.Service;

@Service

public class ExportExcel<T> {

    public void exportExcel(String title, String[] headers,String[] headersName,Collection<T> dataset, OutputStream out) {

        exportExcel(title, headers, headersName, dataset, out, "yyyy-MM-dd HH:mm:ss");

    }

    /*

     * 这是一个通用的方法，利用JAVA的反射机制，可以将放置在JAVA集合中并且符合一定条件的数据以EXCEL的形式输出到指定IO设备上

     * @param title 表格标题名

     * @param headers 表格属性列名数组

     * @Param headersName 字段数组，用来获取反射方法。

     * @param dataset 需要显示的数据集合，集合中一定要放置符合javabean风格的类的对象。此方法支持的javabean属性的数据类型有基本数据类型及String，Date，byte[]（图片数据)

     * @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中。

     * @param pattern 如果有时间数据，设定输出格式。默认为“yyyy-MM-dd”

     */

    public void exportExcel(String title,String[] headers,String[] headersName, Collection<T> dataset,OutputStream out,String pattern){

//        声明一个工作簿

        HSSFWorkbook workbook = new HSSFWorkbook();

//        声明一个表格

        HSSFSheet sheet = workbook.createSheet(title);

//        设置表格默认列宽度为15个字节

        sheet.setDefaultColumnWidth((short)15);

//        生成一个单元格样式：表头单元格样式

        HSSFCellStyle styleCaptaion = workbook.createCellStyle();

//        设置表头单元格样式：设置单元格背景色

//        styleCaptaion.setFillBackgroundColor(HSSFColor.SKY_BLUE.index);

//        设置表头单元格样式：指定单元格的填充信息模式和纯色填充单元。

//        styleCaptaion.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

//        设置表头单元格样式：设置边框的类型为单元格的右边界

        styleCaptaion.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        styleCaptaion.setBorderTop(HSSFCellStyle.BORDER_THIN);

        styleCaptaion.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        styleCaptaion.setBorderRight(HSSFCellStyle.BORDER_THIN);

//        设置表头单元格样式：设置单元格为水平对齐的类型

        styleCaptaion.setAlignment(HSSFCellStyle.ALIGN_CENTER);

//        生成一个字体：表头字体

        HSSFFont fontCaptaion = workbook.createFont();

//        生成一个字体：设置字体颜色

        fontCaptaion.setColor(HSSFColor.VIOLET.index);

//        生成一个字体：设置字体大小

        fontCaptaion.setFontHeightInPoints((short)12);

//        生成一个字体：字体加粗

        fontCaptaion.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

//        把字体应用到当前单元格样式中

        styleCaptaion.setFont(fontCaptaion);

//        生成内容单元格样式

        HSSFCellStyle styleContent = workbook.createCellStyle();

        styleContent.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);

        styleContent.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN);

        styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN);

        styleContent.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        styleContent.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

//        生成内容字体

        HSSFFont fontContent = workbook.createFont();

        fontContent.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

//        把字体应用到内容单元格样式中

        styleContent.setFont(fontContent);

//        声明一个画图的*管理器

        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

//        定义注释的大小和位置

        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short)6, 5));

//        设置注释内容

        comment.setString(new HSSFRichTextString("可以在POI中添加注释"));

//        设置注释作者，当鼠标移动到单元格上时可以在状态栏中看到该内容

        comment.setAuthor("lime");

//        产生表格标题行

        HSSFRow row = sheet.createRow(0);

        for(short i = 0;i < headers.length;i++){

            HSSFCell cell = row.createCell(i);

            cell.setCellStyle(styleCaptaion);

            HSSFRichTextString text = new HSSFRichTextString(headers[i]);

            cell.setCellValue(text);

        }

//        遍历集合数据，产生数据行

        Iterator<T> it = dataset.iterator();

        int index = 0;

        while(it.hasNext()){

            row = sheet.createRow(++index);

            T t = (T)it.next();

            Class<? extends Object> tCls = t.getClass();

            for(short i = 0;i < headers.length;i++){

                HSSFCell cell = row.createCell(i);

                String getterName = "get" + headersName[i].substring(0, 1).toUpperCase() + headersName[i].substring(1);

                Method method = null;

                Object value = null;

                try {

                    method = tCls.getDeclaredMethod(getterName);

                    value = method.invoke(t);

                } catch (NoSuchMethodException e) {

                    e.printStackTrace();

                } catch (SecurityException e) {

                    e.printStackTrace();

                } catch (IllegalAccessException e) {

                    e.printStackTrace();

                } catch (IllegalArgumentException e) {

                    e.printStackTrace();

                } catch (InvocationTargetException e) {

                    e.printStackTrace();

                }

                verify(value, cell, row, pattern, sheet, index, patriarch, workbook, i);

            }

        }

        try {

            workbook.write(out);

        } catch (IOException e) {

            e.printStackTrace();

        }finally{

            if(null != out){

                try {

                    out.close();

                } catch (IOException e) {

                    e.printStackTrace();

                }

            }

        }

    }

    public void verify(Object value,HSSFCell cell,HSSFRow row, String pattern, HSSFSheet sheet, int index, HSSFPatriarch patriarch, HSSFWorkbook workbook, int i){

//        判断值的类型后进行强制类型转换

        String textValue = null;

        if(value instanceof Integer){

            int intValue = (Integer)value;

            cell.setCellValue(intValue);

        }else if(value instanceof Float){

            float fValue = (Float)value;

            cell.setCellValue(fValue);

        }else if(value instanceof Double){

            double dValue = (Double)value;

            cell.setCellValue(dValue);

        }else if(value instanceof Long){

            long longValue = (Long)value;

            cell.setCellValue(longValue);

        }else if(value instanceof Boolean){

            boolean bValue = (Boolean)value;

            textValue ="男";

            if(!bValue){

                textValue = "女";

            }

            cell.setCellValue(textValue);

        }else if(value instanceof Date){

            Date date = (Date)value;

            SimpleDateFormat sdf = new SimpleDateFormat(pattern);

            textValue = sdf.format(date);

            cell.setCellValue(textValue);

        }else if(value instanceof byte[]){

//            有图片时，设置行高为60px；

            row.setHeightInPoints(60);

//            设置图片所在列宽度为80px，注意这里单位的一个换算

            sheet.setColumnWidth(i,(short)35.7*80);

//            sheet.autoSizeColumn(i);

            byte[] bsValue = (byte[])value;

            HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short)6,index,(short)6,index);

//            anchor.setAnchorType(2);

            patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));

        }else{

//            其他数据类型都当作字符串简单处理

            if(null == value){

                value = "未知区域";

            }

            textValue = value.toString();

        }

//        如果不是图片数据，就利用正则表达式判断textValue是否全部有数字组成

        if(textValue != null){

            Pattern p = Pattern.compile("^//d+(//.//d+)?$");

            Matcher matcher = p.matcher(textValue);

            if(matcher.matches()){

//                是数字当double处理

                cell.setCellValue(Double.parseDouble(textValue));

            }else{

                HSSFRichTextString richString = new HSSFRichTextString(textValue);

                HSSFFont font3 = workbook.createFont();

                font3.setColor(HSSFColor.BLUE.index);

                richString.applyFont(font3);

                cell.setCellValue(richString);

            }

        }

    }

}

啦啦啦

啦啦啦

啦啦啦

啦啦啦

相关文章