JAVA导出excel工具类

JAVA导出excel工具类

package com.kjxh.sectionrepresentative.util;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.xssf.usermodel.*;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;

import java.util.*;

/**
 * @author 尚先生
 * @date 2020/2/5 16:26
 */

public class ExcelUtils {
    private static XSSFCellStyle xssfCellStyle;


    public static void out(HttpServletResponse response, String fileName, XSSFWorkbook wb) throws IOException {

        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
//        String agent = request.getHeader("USER-AGENT").toLowerCase();
//        if (agent.contains("firefox")) {
//            response.setCharacterEncoding("utf-8");
//            response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xls");
//        } else {
//            response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
        String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8");
         response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName+".xlsx");
//        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        ServletOutputStream sout = response.getOutputStream();
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;

        try {
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(sout);
            byte[] buff = new byte[2048];
            int bytesRead;
            // Simple read/write loop.
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (Exception e) {

            System.out.println("导出excel出现异常:" + e);
        } finally {
            if (bis != null) {
                bis.close();
            }
            if (bos != null) {
                bos.close();
            }
        }
    }

    public static XSSFSheet excle(XSSFWorkbook wb, int index) {

        // 读取excel模板
        excelStyle(wb);
        // 读取了模板内所有sheet内容
        XSSFSheet sheet = wb.getSheetAt(index);
        return sheet;
    }

    public static void excelStyle(XSSFWorkbook wb) {
        xssfCellStyle = wb.createCellStyle();
        xssfCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        xssfCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        xssfCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        xssfCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        xssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
    }

    public static void ExcelFillIn(int rowIndex, List<LinkedHashMap<String, Object>> projectLaunchList, XSSFSheet sheet) {
        XSSFRow row;
        XSSFCell cell;

        //从数据库取数据填充到Excel
        for (int i = rowIndex; i < projectLaunchList.size() + rowIndex; i++) {//i从2开始计数
            Map<String, Object> obj;
            obj = projectLaunchList.get(i - rowIndex);
            row = sheet.createRow(i);//创建行
            int count = 0;
            Iterator iterator = obj.entrySet().iterator();
            while (iterator.hasNext()) {
                //创建列的单元格
                cell = row.createCell(count);
                Map.Entry entry = (Map.Entry) iterator.next();
                //序号列赋值
                if ("rownum".equals(entry.getKey().toString())) {
                    cell.setCellValue(i - rowIndex + 1);
                } else {
                    if (entry.getValue()==null){ cell.setCellValue("");}else {cell.setCellValue(entry.getValue().toString());}
                }
                cell.getCellStyle().cloneStyleFrom(xssfCellStyle);

                count++;
            }
        }
    }


}

使用方法

        XSSFWorkbook wb = null;
        List<LinkedHashMap<String, Object>> ExportCleaningTableLevelList =meetingPostMapper.getElectionMeetingPostList(people_name,people_type_classification,people_type,post_id);
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("statics/RosterOfBureaux.xlsx");
        int rowIndex = 1;
        try {
            wb = new XSSFWorkbook(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        XSSFSheet sheet = ExcelUtils.excle(wb, 0);
        ExcelUtils.ExcelFillIn(rowIndex, ExportCleaningTableLevelList, sheet);
        try {
            Date currentTime = new Date();
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
            ExcelUtils.out(response, formatter.format(currentTime) + "主席团名册" , wb);
        } catch (IOException e) {
            e.printStackTrace();
        }