基于Spring MVC的Excel导出

最新推荐文章于 2024-04-29 23:47:34 发布

djce57284

最新推荐文章于 2024-04-29 23:47:34 发布

阅读量117

点赞数

文章标签：测试 java

原文链接：http://www.cnblogs.com/Rage-Leila/p/8946230.html

版权

List<String[]> maps = new ArrayList<String[]>();
        String[] columnName = { "ID", "用户名","企业名称","服务类型", "消费金额", "渠道","消费类型", "消费时间" };
        for (int i = 0; i < lists.size(); i++) {
            ConsuDetl record = lists.get(i);
            String[] strlist = new String[columnName.length];
            strlist[0] = record.getSerialNo();
            String mobileStr=record.getMobile();
            if (StringUtils.isNotBlank(record.getParentUserId()) && !"0".equals(record.getParentUserId())) {
                mobileStr=record.getParentMobile();
            }
            strlist[1] = mobileStr;
            strlist[2] = record.getCompanyName();
            strlist[3] = record.getServiceName();
            strlist[4] = record.getTradeAmt()==null?"0.00":record.getTradeAmt().toString();
            strlist[5] = record.getChannel();
            strlist[6] = "H5".equals(record.getChannel()) && StringUtils.isNotBlank(record.getPackageRemark())?record.getPackageRemark():UserAccountDetl.AmtType.getAmtTypeStrByKey(record.getAmtType());
            strlist[7] = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(record.getCreateDate());
            maps.add(strlist);
        }
        SXSSFWorkbook wb = ExcelUtil.exportSxssfExcel("消费记录", columnName, maps, null);
        String fileName= new String(("消费记录" + new SimpleDateFormat("yyyy年MM月dd日HH：mm：ss").format(new Date())+".xlsx").getBytes("gb2312"),"iso-8859-1");
        ExcelResponse.sxssfExcelResponse(wb, response, fileName);

View Code

  1 package com.sanying.trust.common.utils.excel;
  2 
  3 import java.util.List;
  4 import java.util.Map;
  5 
  6 import org.apache.poi.hssf.usermodel.HSSFCell;
  7 import org.apache.poi.hssf.usermodel.HSSFCellStyle;
  8 import org.apache.poi.hssf.usermodel.HSSFFont;
  9 import org.apache.poi.hssf.usermodel.HSSFRow;
 10 import org.apache.poi.hssf.usermodel.HSSFSheet;
 11 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 12 import org.apache.poi.ss.usermodel.Cell;
 13 import org.apache.poi.ss.usermodel.CellStyle;
 14 import org.apache.poi.ss.usermodel.Font;
 15 import org.apache.poi.ss.usermodel.Row;
 16 import org.apache.poi.ss.usermodel.Sheet;
 17 import org.apache.poi.xssf.streaming.SXSSFWorkbook;
 18 
 19 
 20 /**
 21  * Excel表格
 22  * 
 23  */
 24 public class ExcelUtil {
 25 
 26 
 27     @SuppressWarnings("deprecation")
 28     public static HSSFWorkbook exportExcel(String title,String[] columnName, List<String[]> texts,Map<Integer, Integer> columnmap) {
 29         // 第一步，创建一个webbook，对应一个Excel文件  
 30         HSSFWorkbook wb = new HSSFWorkbook();  
 31         // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
 32         HSSFSheet sheet = wb.createSheet(title);  
 33         // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
 34         HSSFRow row = sheet.createRow((int) 0);  
 35         // 第四步，创建单元格，并设置值表头 设置表头居中  
 36         HSSFCellStyle style = wb.createCellStyle();  
 37         style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
 38         //设置样式
 39         setExcel(wb,sheet,columnName.length,columnmap);
 40         HSSFCell cell =  null;
 41         for (int i = 0; i < columnName.length; i++) {
 42             cell = row.createCell((short)i);
 43             cell.setCellValue(columnName[i]);  
 44             cell.setCellStyle(style);  
 45         }
 46 
 47         for (int i = 0; i < texts.size(); i++) {  
 48             row = sheet.createRow((int) i + 1);  
 49             String[] map = texts.get(i);
 50             int columnid = 0;
 51             for(String val : map) {
 52                 if(val == null) {
 53                     row.createCell((short) columnid).setCellValue("");
 54                 }else {
 55                     row.createCell((short) columnid).setCellValue(val);
 56                 }
 57                 columnid++;
 58             }
 59             // 第四步，创建单元格，并设置值  
 60         }
 61         return wb;  
 62     }
 63     public static void setExcel(HSSFWorkbook workbook,HSSFSheet sheet,int size,Map<Integer, Integer> map) {
 64         // 设置列宽   
 65         for (int i = 0; i < size; i++) {
 66             sheet.setColumnWidth(i, 5000);
 67         }
 68         if(map != null) {
 69             for(Integer key:map.keySet()) {
 70                 sheet.setColumnWidth(key, map.get(key));
 71             }
 72         }
 73         // Sheet样式   
 74         HSSFCellStyle sheetStyle = workbook.createCellStyle();   
 75         
 76         sheetStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
 77         sheetStyle.setAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
 78         HSSFFont font = workbook.createFont(); 
 79         font.setFontName("宋体");//设置字体
 80         font.setFontHeightInPoints((short) 20);//设置字体大小
 81         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
 82         //设置自动换行:
 83         sheetStyle.setWrapText(true);//设置自动换行 
 84         
 85     
 86     }
 87     
 88     public static SXSSFWorkbook exportSxssfExcel(String title,String[] columnName, List<String[]> texts,Map<Integer, Integer> columnmap) {
 89         // 第一步，创建一个webbook，对应一个Excel文件  
 90         SXSSFWorkbook wb = new SXSSFWorkbook();  
 91         // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
 92         Sheet sheet = wb.createSheet(title);  
 93         // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
 94         Row row = sheet.createRow((int) 0);  
 95         // 第四步，创建单元格，并设置值表头 设置表头居中  
 96         CellStyle style = wb.createCellStyle();  
 97         style.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式  
 98         //设置样式
 99         setSxssfExcel(wb,sheet,columnName.length,columnmap);
100         Cell cell =  null;
101         for (int i = 0; i < columnName.length; i++) {
102             cell = row.createCell((short)i);
103             cell.setCellValue(columnName[i]);  
104             cell.setCellStyle(style);  
105         }
106 
107         for (int i = 0; i < texts.size(); i++) {  
108             row = sheet.createRow((int) i + 1);  
109             String[] map = texts.get(i);
110             int columnid = 0;
111             for(String val : map) {
112                 if(val == null) {
113                     row.createCell((short) columnid).setCellValue("");
114                 }else {
115                     row.createCell((short) columnid).setCellValue(val);
116                 }
117                 columnid++;
118             }
119             // 第四步，创建单元格，并设置值  
120         }
121         return wb;  
122     }
123     
124     public static void setSxssfExcel(SXSSFWorkbook workbook,Sheet sheet,int size,Map<Integer, Integer> map) {
125         // 设置列宽   
126         for (int i = 0; i < size; i++) {
127             sheet.setColumnWidth(i, 5000);
128         }
129         if(map != null) {
130             for(Integer key:map.keySet()) {
131                 sheet.setColumnWidth(key, map.get(key));
132             }
133         }
134         // Sheet样式   
135         CellStyle sheetStyle = workbook.createCellStyle();   
136         
137         sheetStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平居中
138         sheetStyle.setAlignment(CellStyle.VERTICAL_CENTER); // 垂直居中
139         Font font = workbook.createFont(); 
140         font.setFontName("宋体");//设置字体
141         font.setFontHeightInPoints((short) 20);//设置字体大小
142         font.setBoldweight(Font.BOLDWEIGHT_BOLD);//粗体显示
143         //设置自动换行:
144         sheetStyle.setWrapText(true);//设置自动换行 
145     }
146 }

View Code

package com.sanying.trust.console.utils;

import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import com.sanying.trust.common.constants.Constants;

public class ExcelResponse {
    
    public static void sxssfExcelResponse(SXSSFWorkbook wb,HttpServletResponse response,String fileName) throws Exception{
        response.reset();
        response.setCharacterEncoding("utf-8");  
        response.setContentType("multipart/form-data");
        response.setHeader("Content-Disposition","attachment;filename=" +fileName);
        ServletOutputStream out = response.getOutputStream();
        wb.write(out);  
        out.close();
    }
    
    /**
     * 判断是否超出限定的导出行数
     * @param list
     * @param response
     * @return
     * @throws Exception
     */
    public static boolean retExcelErr(List<?> list,HttpServletResponse response)throws Exception{
        if (list.size()>Constants.EXPORT_EXCEL_SIZE) {
            response.sendRedirect("/errMsg/excelErr");
            return false;
        }
        return true;
    }

}

View Code

转载于:https://www.cnblogs.com/Rage-Leila/p/8946230.html

djce57284

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
基于Spring MVC的Excel导出

List<String[]> maps = new ArrayList<String[]>(); String[] columnName = { "ID", "用户名","企业名称","服务类型", "消费金额", "渠道","消费类型", "消费时间" }; for (int i = 0; i < lists.s...
复制链接

扫一扫