poi 导出工具类

时间：2023-02-10 06:19:14

工具类

package com.banxue.kmsservice.helper;

import net.sf.json.JSONArray;

import net.sf.json.JSONObject;

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

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

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import rx.internal.util.LinkedArrayList;

import javax.servlet.http.HttpServletResponse;

import java.util.Iterator;

import java.util.LinkedList;

import java.util.Map;

public class ExcelExportHelper {

    public static void writeExcelHeader(HttpServletResponse response,String fileName) throws Exception{

        if(!(fileName.endsWith(".xls") || fileName.endsWith("xlsx"))){

            fileName+=".xlsx";

        }

        response.setHeader("Content-disposition","attachment;filename="+new String(fileName.getBytes("gb2312"),"ISO8859-1"));  //设置文件头编码格式

        response.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");//设置类型

        response.setHeader("Cache-Control","no-cache");//设置头

        response.setDateHeader("Expires", 0);//设置日期头

    }

    public static void writeExcelData(HttpServletResponse response, JSONArray dataAraay, Map<String,String> map, String sheetName) throws Exception{

        XSSFWorkbook book=new XSSFWorkbook();

        XSSFSheet sheet=book.createSheet(sheetName);

        //创建表头

        Iterator<String> iterator = map.keySet().iterator();

        Row row = sheet.createRow(0);

        int i = 0;

        LinkedList list = new LinkedList();

        while (iterator.hasNext()){

            String key = iterator.next();

            Cell cell = row.createCell(i);

            cell.setCellValue(map.get(key));

            list.add(key);

            i++;

        }

        //写入数据

        for(int j = 0; j < dataAraay.size(); j++){

            JSONObject jsonObject = JSONObject.fromObject(dataAraay.get(j));

            Row dataRow = sheet.createRow(j+1);

            for(int k = 0; k < list.size(); k++){

                dataRow.createCell(k).setCellValue(String.valueOf(jsonObject.get(list.get(k))));

            }

        }

        book.write(response.getOutputStream());

        response.getOutputStream().flush();

        response.getOutputStream().close();

    }

}

　　引用：

　　　　 //获取活动下可以发货的数据

        List<Map> recordDaos = laXinActivityExchangeRecordMapperUser.selectWaitDeliverByActivityId(activityId);

        //生成excel

        ExcelExportHelper.writeExcelHeader(response,DateHelper.getDateTimeString(new Date(),"yyyy-MM-dd"));

        //设置字段对应的含义值

        Map map = new HashMap();

        map.put("id","id");

        map.put("wx_name","用户微信名");

        map.put("receive_user","收货人");

        map.put("receive_phone","收货电话");

        map.put("receive_address","收货地址");

        map.put("receive_memo","备注");

        map.put("quantity","申请数量");

        map.put("id","id");

        //写入数据并下载excel

        ExcelExportHelper.writeExcelData(response,JSONArray.fromObject(recordDaos),map,DateHelper.getDateTimeString(new Date(),"yyyy-MM-dd"));

相关文章