美文网首页
Java实现大数据导出

Java实现大数据导出

作者: 山巅自相见 | 来源:发表于2021-08-25 09:29 被阅读0次

pom依赖

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>3.9</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>3.9</version>
</dependency>

注意：这两个依赖的版本必须相同

excel导出工具类

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
 
public class ExportExcel {
  /**
  * 导出excel
  * @param response
  *            HttpServletResponse
  * @param list
  *            导出数据集合
  * @param lables           
  *            表头数组
  * @param fields
  *            key数组
  * @param title            
  *            文件名
  */
  public static void export(HttpServletResponse response,List<Map<String, Object>> list,String[] lables,String[] fields,String title) {            
     response.setContentType("application/octet-stream");//告诉浏览器输出内容为流
    String filename = "";
    try {
      filename = new String(title.getBytes("UTF-8"), "ISO_8859_1");
    } catch (UnsupportedEncodingException e1) {
      e1.printStackTrace();
    }
    response.setHeader("Content-Disposition","attachment;filename=" + filename);
    OutputStream os = null;
    SXSSFWorkbook sxssfWorkbook = null;
    try {        
      // 获取SXSSFWorkbook
      sxssfWorkbook = new SXSSFWorkbook();
      Sheet sheet = sxssfWorkbook.createSheet("Sheet1");
      // 冻结第一行
      sheet.createFreezePane(0, 1); 
      // 创建第一行,作为header表头
      Row header = sheet.createRow(0);
      // 循环创建header单元格
      for (int cellnum = 0; cellnum < lables.length; cellnum++) {
        Cell cell = header.createCell(cellnum);
        //cell.setCellStyle(getAndSetXSSFCellStyleHeader(sxssfWorkbook));//设置表头单元格样式,根据需要设置
        cell.setCellValue(lables[cellnum]);
        //设置每列固定宽度
        sheet.setColumnWidth(cellnum, 20 * 256);
      }
       // 遍历创建行,导出数据
      for (int rownum = 1; rownum <= list.size(); rownum++) {
        Row row = sheet.createRow(rownum);
        Map<String, Object> map = list.get(rownum-1);
        // 循环创建单元格
        for (int cellnum = 0; cellnum < fields.length; cellnum++) {
          Cell cell = row.createCell(cellnum);
          //cell.setCellStyle(getAndSetXSSFCellStyleOne(sxssfWorkbook));//设置数据行单元格样式,根据需要设置
          cell.setCellValue(map.get(fields[cellnum]) == null ? "" : map.get(fields[cellnum]).toString());
        }
      }
      //自定义各列宽度
      //setSheet(sheet);
      os = response.getOutputStream(); 
      sxssfWorkbook.write(os);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if(sxssfWorkbook != null) {
          //处理SXSSFWorkbook导出excel时，产生的临时文件
          sxssfWorkbook.dispose();
        }
        if(os != null) {
          os.close();
        }
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
  }
 
  /**
  * 导出excel带标题
  * @param response
  *            HttpServletResponse
  * @param list
  *            导出数据集合
  * @param lables           
  *            表头数组
  * @param fields
  *            key数组
  * @param title            
  *            文件名
  * @param headTitle            
  *            文件标题
  */
  public static void titleExport(HttpServletResponse response,List<Map<String, Object>> list,String[] lables,String[] fields,String title,String headTitle) {              
       response.setContentType("application/octet-stream");//告诉浏览器输出内容为流
       String filename = "";
       try {
           filename = new String(title.getBytes("UTF-8"), "ISO_8859_1");
       } catch (UnsupportedEncodingException e1) {
           e1.printStackTrace();
       }
       response.setHeader("Content-Disposition","attachment;filename=" + filename);
       OutputStream os = null;
       SXSSFWorkbook sxssfWorkbook = null;
       try {         
            // 获取SXSSFWorkbook
            sxssfWorkbook = new SXSSFWorkbook();
            Sheet sheet = sxssfWorkbook.createSheet("Sheet1");
            // 创建第一行,作为标题
            Row headline = sheet.createRow(0);
            Cell c = headline.createCell(0);
            //设置居中
            XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
            xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
            c.setCellStyle(xssfCellStyle);
            c.setCellValue(headTitle);
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,lables.length-1));//合并单元格(起始行号,终止行号,起始列号,终止列号)
            // 冻结第二行
            sheet.createFreezePane(0, 2);   
            // 创建第二行,作为header表头
            Row header = sheet.createRow(1);
            // 循环创建header单元格
            for (int cellnum = 0; cellnum < lables.length; cellnum++) {
                Cell cell = header.createCell(cellnum);
                //cell.setCellStyle(getAndSetXSSFCellStyleHeader(sxssfWorkbook));//设置表头单元格样式,根据需要设置
                cell.setCellValue(lables[cellnum]);
                //设置每列固定宽度
                sheet.setColumnWidth(cellnum, 10 * 256);
            }
            // 遍历创建行,导出数据
            for (int rownum = 1; rownum <= list.size(); rownum++) {
                Row row = sheet.createRow(rownum+1);
                Map<String, Object> map = list.get(rownum-1);
                // 循环创建单元格
                for (int cellnum = 0; cellnum < fields.length; cellnum++) {
                    Cell cell = row.createCell(cellnum);
                    //cell.setCellStyle(getAndSetXSSFCellStyleOne(sxssfWorkbook));//设置数据行单元格样式,根据需要设置
                    cell.setCellValue(map.get(fields[cellnum]) == null ? "" : map.get(fields[cellnum]).toString());
                }
            }
            //自定义各列宽度
            //setSheet(sheet);
            os = response.getOutputStream(); 
            sxssfWorkbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(sxssfWorkbook != null) {
                    //处理SXSSFWorkbook导出excel时，产生的临时文件
                    sxssfWorkbook.dispose();
                }
                if(os != null) {
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
 
   /**
    * 导出excel(多个工作薄)
    * 
    * @param response
    *            HttpServletResponse
    * @param datas
    *            导出数据集合
    * @param title            
    *            文件名
    */
    public static void exportSheets(HttpServletResponse response,List<Map<String, Object>> datas,String title) {               
       response.setContentType("application/octet-stream");//告诉浏览器输出内容为流
       String filename = "";
       try {
           filename = new String(title.getBytes("UTF-8"), "ISO_8859_1");
       } catch (UnsupportedEncodingException e1) {
           e1.printStackTrace();
       }
       response.setHeader("Content-Disposition","attachment;filename=" + filename);
       OutputStream os = null;
       SXSSFWorkbook sxssfWorkbook = null;
       try {         
            // 获取SXSSFWorkbook
            sxssfWorkbook = new SXSSFWorkbook();
            // 根据集合的数量创建sheet
            for(int i=0; i<datas.size(); i++){
                Sheet sheet = sxssfWorkbook.createSheet("Sheet"+(i+1));
            // 冻结第一行
            sheet.createFreezePane(0, 1);   
            // 创建第一行,作为header表头
            Row header = sheet.createRow(0);
            // 循环创建header单元格
            String[] lables = (String[]) datas.get(i).get("lables");
            String[] fields = (String[]) datas.get(i).get("fields");
            for (int cellnum = 0; cellnum < lables.length; cellnum++) {
                Cell cell = header.createCell(cellnum);
                //cell.setCellStyle(getAndSetXSSFCellStyleHeader(sxssfWorkbook));//设置表头单元格样式,根据需要设置
                cell.setCellValue(lables[cellnum]);
                //设置每列固定宽度
                sheet.setColumnWidth(cellnum, 20 * 256);
            }
            // 遍历创建行,导出数据
            List<Map<String, Object>> list = (List<Map<String, Object>>) datas.get(i).get("list");
            for (int rownum = 1; rownum <= list.size(); rownum++) {
                Row row = sheet.createRow(rownum);
                Map<String, Object> map = list.get(rownum-1);
                // 循环创建单元格
                for (int cellnum = 0; cellnum < fields.length; cellnum++) {
                    Cell cell = row.createCell(cellnum);
                    //cell.setCellStyle(getAndSetXSSFCellStyleOne(sxssfWorkbook));//设置数据行单元格样式,根据需要设置
                    cell.setCellValue(map.get(fields[cellnum]) == null ? "" : map.get(fields[cellnum]).toString());
                }
            }
            }
            os = response.getOutputStream(); 
            sxssfWorkbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(sxssfWorkbook != null) {
                    //处理SXSSFWorkbook导出excel时，产生的临时文件
                    sxssfWorkbook.dispose();
                }
                if(os != null) {
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
       
   /**
    * 自定义各列宽度(单位为:字符宽度的1/256)
    */
    private static void setSheet(Sheet sheet) {       
    sheet.setColumnWidth(0, 32 * 256);
        sheet.setColumnWidth(1, 32 * 256);
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(3, 20 * 256);
        sheet.setColumnWidth(4, 20 * 256);
        sheet.setColumnWidth(5, 20 * 256);
        sheet.setColumnWidth(6, 20 * 256);
        sheet.setColumnWidth(7, 20 * 256);
        sheet.setColumnWidth(8, 20 * 256);
        sheet.setColumnWidth(9, 20 * 256);
        sheet.setColumnWidth(10, 32 * 256);
    }
 
   /**
    * 获取并设置header样式
    */
    private static XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        Font font = sxssfWorkbook.createFont();
        // 字体大小
        font.setFontHeightInPoints((short) 14);
        // 字体粗细
        font.setBoldweight((short) 20);
        // 将字体应用到样式上面
        xssfCellStyle.setFont(font);
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return xssfCellStyle;
    }
 
   /**
    * 获取并设置样式
    */
    private static XSSFCellStyle getAndSetXSSFCellStyleOne(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        XSSFDataFormat format = (XSSFDataFormat)sxssfWorkbook.createDataFormat();
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 前景颜色
        xssfCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        xssfCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
        // 边框
        xssfCellStyle.setBorderBottom(BorderStyle.THIN);
        xssfCellStyle.setBorderRight(BorderStyle.THIN);
        xssfCellStyle.setBorderTop(BorderStyle.THIN);
        xssfCellStyle.setBorderLeft(BorderStyle.THIN);
        xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
        xssfCellStyle.setDataFormat(format.getFormat("0"));
        return xssfCellStyle;
    }
}

业务层调用

//导出
@RequestMapping("/export")
public String export(Model model,HttpServletRequest request,HttpServletResponse response) {
    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    System.out.println("导出开始时间："+format.format(new Date()));
    List<Map<String, Object>> list = jkconfigDao.exportByRksj();//查询业务数据
    String[] lables = new String[]{"号码","入库时间"};//表头数组
    String[] fields = new String[]{"HM","RKSJ"};//查询数据对应的属性数组
    String title = "测试.xlsx";
    ExportExcel.export(response, list, lables, fields, title);  
    System.out.println("导出结束时间："+format.format(new Date()));
    return null;
}

相关文章

网友评论

      本文标题：Java实现大数据导出

      本文链接：https://www.haomeiwen.com/subject/unbdiltx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|Java实现大数据导出|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！