BufferedWriter导出数据excel文件

最新推荐文章于 2024-08-17 08:37:57 发布

置顶 zuo一个认真点的程序员

最新推荐文章于 2024-08-17 08:37:57 发布

阅读量2k

点赞数

分类专栏： Excel 文章标签： BufferedWriter java导出数据 BufferedWriter导出数据excel

本文链接：https://blog.csdn.net/tt336798/article/details/81355339

版权

Excel 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

BufferedWriter导出数据

BufferedWriter 将文本写入字符输出流，缓冲各个字符，从而提供单个字符、数组和字符串的高效写入。可以指定缓冲区的大小，或者接受默认的大小。在大多数情况下，默认值就足够大了

js 页面

//导出数据
 function exportData(){
                var data = {};
                if($("#memberName").val()) data.memberName = $("#memberName").val();
                if($("#merberTel").val()) data.merbertel = $("#merberTel").val();

                //创建标签元素
                  var form = document.createElement('form')
                    form.style.display = 'none'
                    document.body.appendChild(form)
                    form.method = 'POST'
                    form.action = "${webroot}/admin/exportDB.do";   //url
                    form.enctype = 'application/x-www-form-urlencoded'
                    var dataEl = document.createElement('input');
                    dataEl.type = 'hidden';
                    dataEl.name = 'data';
                    dataEl.value = JSON.stringify(data); //转JSON
                    form.appendChild(dataEl)
                    form.submit()
                    form.target = '_blank'
 }

以上无非就是创建下面的一个标签(看懂的可以略过)，作这一步的目的是可以只导出自己查询出来的列表：


<form method="post" action="" enctype="application/x-www-form-urlencoded" style="display:none;" target="_blank">
<input type="hidden" name="data" value="">

导出其实和页面查询有类似，input标签中的value存放数据data

需要了解form表单中target属性和enctype属性，前往另一篇博客

博客地址：https://blog.csdn.net/tt336798/article/details/81538649

后台代码

1.第一类

导包

import java.io.BufferedWriter;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.io.output.FileWriterWithEncoding;

声明变量

//导出地址
private String rootPath="E:/excelFiles/";
//产生的文件名
private String tempName="member";

导出方法(查询导出表所有的数据)

/**
     * 导出数据
     * @param request
     * @param response
     * @return
     */
    @SuppressWarnings("unchecked")
    @RequestMapping("/exportDB")
    @ResponseBody
    public void exportMember(HttpServletRequest request, HttpServletResponse response){
            RequestModel  requestModel=BaseUtil.getModel(request, response);
            Map<String,Object> map=memberServiceImpl.exportData(requestModel.getData(),null);
            List<MemberEntity> list = new ArrayList<MemberEntity>();

            if (map.get("data") != null) {
                    list = (List<MemberEntity>) map.get("data");  //map 转list 
            }
            try {
                downLoad(list, "member", response);  //方法如下 
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }

这里的map,也就是从数据库中获取的所有数据，我后面转成了list。每个项目的框架不同，代码编写上也会也所不同。转回话题，在这一步中是获取你需要导出的数据表格，是导出所有数据，这里不用分页。

接下来是代码的重点

private void downLoad(List<MemberEntity> list, String fileName, HttpServletResponse response) throws IOException {
        long time = new Date().getTime();
        File file = new File(rootPath+ time + ".csv");
        BufferedWriter bw = new BufferedWriter(new FileWriterWithEncoding(file, "gb2312"));
        bw.write("昵称,姓名,手机号码,性别,年龄,邮箱");
        bw.write("\n");
        for (MemberEntity member : list) {
            bw.write((StringUtils.isBlank(member.getNickName()) ? "-" : member.getNickName()) + ",");
            bw.write((StringUtils.isBlank(member.getMemberName()) ? "-" : member.getMemberName()) + ",");
            bw.write((StringUtils.isBlank(member.getMerberTel()) ? "-" : member.getMerberTel()) + ","); 
            if(member.getSex()==1){  //男
                bw.write((StringUtils.isBlank(member.getSex.toString()) ? "-" : "男") + ",");
            }else{
                bw.write((StringUtils.isBlank(member.getSex().toString()) ? "-" : "女") + ",");
            }
            if(member.getSex()==0){ //女
                bw.write((StringUtils.isBlank(member.getSex().toString()) ? "-" : "女") + ",");
            }else{
                bw.write((StringUtils.isBlank(member.getSex().toString()) ? "-" : "男") + ",");
            }

            bw.write((StringUtils.isBlank(member.getAge().toString()) ? "-" : member.getAge().toString()) + ",");
            bw.write((StringUtils.isBlank(member.getMail().toString()) ? "-" : member.getMail().toString()) + ",");

            bw.write("\n");
        }
        bw.close();
        ExcelUtil.downloadCsv(file, response, fileName);
    }

ExcelUtil类中方法

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;

import javax.servlet.http.HttpServletResponse;

public static void downloadCsv(File file, HttpServletResponse response,String filename) {
        try {
            // path是指欲下载的文件的路径。
            // File file = new File(path);
            // 取得文件名。
            // String filename = file.getName();
            // 以流的形式下载文件。
            InputStream fis = new BufferedInputStream(new FileInputStream(file));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            // 清空response
            response.reset();
            // 设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename, "UTF-8") + ".csv");  //.csv 或则 .xls
            response.addHeader("Content-Length", "" + file.length());
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
            // file.delete();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

2.第二类

后台还可以这样简便的写

public String downData(HttpServletRequest request,HttpServletResponse response) throws IOException {
        try {
            RequestModel requestModel = BaseUtil.getModel(request, response); //自己的一个工具类方法
            List<Map<String, Object>>  listMap = memberServiceImpl.queryMember(requestModel.getData(),null);//获得所有数据,不分页

            long time = new Date().getTime();
            File file = new File(tempFile + "/" + time + ".xls");  //tempFile 文件产生路径
            if (!file.exists()) {
                file.createNewFile();
            }
            List<String> titleList = new ArrayList<>();
            titleList.add("nickName_" + "昵称");   //nickName :实体类属性
            titleList.add("memberName_" + "姓名");
            titleList.add("merberTel_电话号码");
            titleList.add("age_年龄");
            titleList.add("sex_性别");
            titleList.add("merberBirth_出生日期");
            titleList.add("memberEmail_邮箱");


            OutputStream out = new FileOutputStream(file);
            ExcelUtil.exportExcel2(listMap, out, titleList);
            ExcelUtil.download(file, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

附上service层

// 返回类型 List<Map<String, Object>>
public List<Map<String, Object>> queryMember(Map<String, Object> params, Page page) {
        StringBuffer sqlBuf = new StringBuffer("SQL语句写在下面");
        if (params == null) {
            params = new HashMap<String, Object>();
        }
        if (params.containsKey("memberTel")) {
            sqlBuf.append(" and ts.member_tel=:memberTel");
        }
        if (params.containsKey("memberName")) {
            sqlBuf.append(" and tm.member_name like CONCAT('%',:memberName,'%')");
        }   
        return this.customerJPARepository.queryListEntity(sqlBuf.toString(), params); //这个是Dao层自定义查询方法，我定义的是JPA框架中的方法

    }

以上sex和birthday直接在写SQL时进行性别判断

//SQL中后面的别名 对应实体类中的属性
SELECT IFNULL(tm.nick_name,'--') nickName,IFNULL(tm.member_name,'--') memberName,IFNULL(tm.merber_tel,'--') merberTel,IFNULL(tm.age,'--') age,
CASE tm.sex WHEN 1 THEN '男' WHEN 0 THEN '女' ELSE '--' END sex, 
IFNULL(DATE_FORMAT(tm.birthday,'%Y-%m-%d %T'),'--') merberBirth,IFNULL(tm.member_email,'--') memberEmail 
FROM t_member tm

ExcelUtil工具类

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletResponse;

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 com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

public class ExcelUtil{


    /**
     * List<Map<String, Object>> list
     * @param list
     * @param out
     */
    public static void exportExcel1(List<Map<String, Object>> list,OutputStream out) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet("sheet1");
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);

        // 声明一个画图的顶级管理器
        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("excels");

        // 产生表格标题行

        if (list != null && list.size() > 0) {
            HSSFRow row = sheet.createRow(0);
            // 生成头
            Set<String> set = list.get(0).keySet();
            String[] tempArr = new String[set.size()];
            String[] colArr = new String[set.size()];
            set.toArray(tempArr);
            for (int i = 0; i < tempArr.length; i++) {
                String[] temp = tempArr[i].split("_");
                colArr[Integer.parseInt(temp[0])] = tempArr[i];
            }

            for (int i = 0; i < colArr.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style);
                String[] temp = colArr[i].split("_");
                cell.setCellValue(temp[1]);

            }

            int index = 1;
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> map = list.get(i);

                row = sheet.createRow(index);
                index++;

                for (int j = 0; j < colArr.length; j++) {
                    HSSFCell cell = row.createCell(j);
                    cell.setCellStyle(style2);

                    if (j == colArr.length - 1) {
                        try {
                            String content = String.valueOf(map.get(colArr[j]));
                            JSONObject jsonObject = JSON.parseObject(content); //需要一个 fastjson.jar包
                            if (jsonObject.containsKey("type")) {
                                switch (String.valueOf(jsonObject.get("type"))) {
                                case "text":
                                    cell.setCellValue(String.valueOf(jsonObject.get("content")));
                                    break;
                                case "image":
                                    cell.setCellValue("[图片]");
                                    break;
                                case "car":
                                    cell.setCellValue(String.valueOf(jsonObject.getJSONObject("content").get("carModelName")));
                                    break;
                                default:
                                    cell.setCellValue("");
                                    break;
                                }
                            }
                        } catch (Exception ex) {
                            ex.printStackTrace();
                            cell.setCellValue("");
                        }
                    } else {
                        cell.setCellValue(String.valueOf(map.get(colArr[j])));
                    }
                }
            }
        }

        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * List<Map<String, Object>> list
     * 
     * @param list
     * @param out
     * @param titleList
     */

    public static void exportExcel2(List<Map<String, Object>> list,OutputStream out, List<String> titleList) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // '生成一个表格'
        HSSFSheet sheet = workbook.createSheet("sheet1");
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 设置这些样式
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        // 生成并设置另一个样式
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成另一个字体
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style2.setFont(font2);

        // 声明一个画图的顶级管理器
        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("excels");

        // 产生表格标题行

        if (titleList != null && titleList.size() > 0) {
            String[] colArr = new String[titleList.size()];

            HSSFRow row = sheet.createRow(0);
            // 生成头
            for (int i = 0; i < titleList.size(); i++) {
                String title = titleList.get(i);
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style);
                String[] temp = title.split("_");
                colArr[i] = temp[0];
                cell.setCellValue(temp[1]);
            }

            int index = 1;
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> map = list.get(i);

                row = sheet.createRow(index);
                index++;
                for (int j = 0; j < colArr.length; j++) {
                    HSSFCell cell = row.createCell(j);
                    cell.setCellStyle(style2);
                    cell.setCellValue(String.valueOf(map.get(colArr[j])));
                }
            }
        }

        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //下载
    public static void download(File file, HttpServletResponse response) {
        try {
            // path是指欲下载的文件的路径。
            // File file = new File(path);
            // 取得文件名。
            String filename = file.getName();
            // 以流的形式下载文件。
            InputStream fis = new BufferedInputStream(new FileInputStream(file));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            // 清空response
            response.reset();
            // 设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes()));
            response.addHeader("Content-Length", "" + file.length());
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
            file.delete();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

}

maven 中需要加入jar包

    <dependency>
         <groupId>junit</groupId>
         <artifactId>junit</artifactId>
         <version>4.11</version>
         <scope>test</scope>
    </dependency>

    <dependency>
       <groupId>org.apache.poi</groupId>
       <artifactId>poi</artifactId>
       <version>3.6</version>
   </dependency>

    <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
    <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>fastjson</artifactId>
       <version>1.2.47</version>
    </dependency>