[J2EE>>JAR>>excel] 利用POI导出Excel文件

 

 

一、带表头及合并的Excel导出

 

package com.cong.file.xls;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ss.util.CellRangeAddress;
/**
 * 
 * Excel导出工具类<br>  
 * @date 2016年9月1日
 * @author 任聪
 * @since 1.0
 * @version 1.0
 */
public final  class ExcelExpUtils {
    
    private ExcelExpUtils(){
        
    }



    /**
     * 
     * 导出数据<br>
     * @param title:excel标题
     * @param columns:excel表头列
     * @param fields:excel显示列
     * @param data:导出数据
     * @return
     * @author:珞珈搬砖工
     * @date:last updated time 2016年8月30日
     */
    public static <T> InputStream expData(String title,String[] columns,String fields[],List<Map<String,Object>> data){
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFCellStyle borderStyle = getBorderStyle(wb);
        HSSFCellStyle titleStyle = getTitleStyle(wb);
        HSSFSheet sheet = wb.createSheet("sheet1");
        createHeader(sheet,title,titleStyle,columns,borderStyle);
        for (int i = 0; i < data.size(); i++) {
            Map<String,Object> dataMap = data.get(i);
            HSSFRow dataRow = sheet.createRow(i + 2);// 创建行
            dataRow.setHeight((short) 300); // 设置行高
            for(int j=0;j<fields.length;j++){
                createCell(dataRow, j, dataMap.get(fields[j]),borderStyle);
            }
        }
        ByteArrayInputStream value = null;
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();  
            wb.write(os);
            value = new ByteArrayInputStream(os.toByteArray());
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }  
        return value;
    }
    
    
    
    /**
     * 创建Excel表头<br>
     * @param sheet:表
     * @param style:样式
     * @param columns:表头列数组
     */
    public static  void createHeader(HSSFSheet sheet,String title,HSSFCellStyle titleStyle,String[] columns ,HSSFCellStyle style){
        HSSFRow titleRow = sheet.createRow(0);
        createCell(titleRow,0,title,titleStyle);
        titleRow.setHeight((short) 500);
        /**
         * 合并表头格
         * I.method one:sheet.addMergedRegion(new Region());
         * II.method two:sheet.addMergedRegion(new CellRangeAddress());
         */
        //sheet.addMergedRegion(new Region(0,(short) 0,0,(short)(columns.length-1)));
        CellRangeAddress cra = new CellRangeAddress(0, 0, 0, columns.length-1);        
        sheet.addMergedRegion(cra);  
        HSSFRow row = sheet.createRow(1);
        for(int i=0;i<columns.length;i++){
            createCell(row,i,columns[i],style);
        }
    }

    /**
     * 创建Excel单元格Cell<br>
     * @param row
     * @param column
     * @param value
     * @param style
     */
    public static void createCell(HSSFRow row,int column ,Object value,HSSFCellStyle style){
        HSSFCell cell = row.createCell(column);
        cell.setCellValue(String.valueOf(value));
        cell.setCellStyle(style);
    }
    /**
     * 设置Excel抬头样式<br>
     * @param wb
     * @return
     */
    public static HSSFCellStyle getTitleStyle(HSSFWorkbook wb){
        HSSFCellStyle fontStyle = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        fontStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        fontStyle.setFont(font);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeight((short)250);
        return fontStyle;
    }
    /**
     * 设置Excel单元格样式<br>
     * @param wb
     * @return
     */
    public static HSSFCellStyle getBorderStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return cellStyle;
    }
}

 

 

二、不带表头和单元格合并的Excel导出

 

package com.cong.file.xls;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ss.util.CellRangeAddress;
/**
 * 
 * Excel导出工具类<br>  
 * @date 2016年9月1日
 * @author 任聪
 * @since 1.0
 * @version 1.0
 */
public final  class ExcelExpUtils {
    
    private ExcelExpUtils(){
        
    }



    /**
     * 
     * 导出数据<br>
     * @param title:excel标题
     * @param columns:excel表头列
     * @param fields:excel显示列
     * @param data:导出数据
     * @return
     * @author:珞珈搬砖工
     * @date:last updated time 2016年8月30日
     */
    public static <T> InputStream expData(String[] columns,String fields[],List<Map<String,Object>> data){
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFCellStyle borderStyle = getBorderStyle(wb);
        HSSFSheet sheet = wb.createSheet("sheet1");
        createHeader(sheet,columns,borderStyle);
        for (int i = 0; i < data.size(); i++) {
            Map<String,Object> dataMap = data.get(i);
            HSSFRow dataRow = sheet.createRow(i + 1);// 创建行
            dataRow.setHeight((short) 300); // 设置行高
            for(int j=0;j<fields.length;j++){
                createCell(dataRow, j, dataMap.get(fields[j]),borderStyle);
            }
        }
        ByteArrayInputStream value = null;
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();  
            wb.write(os);
            value = new ByteArrayInputStream(os.toByteArray());
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }  
        return value;
    }
    
    
    
    /**
     * 创建Excel表头<br>
     * @param sheet:表
     * @param style:样式
     * @param columns:表头列数组
     */
    public static  void createHeader(HSSFSheet sheet,String[] columns ,HSSFCellStyle style){      
        HSSFRow row = sheet.createRow(0);
        for(int i=0;i<columns.length;i++){
            createCell(row,i,columns[i],style);
        }
    }

    /**
     * 创建Excel单元格Cell<br>
     * @param row
     * @param column
     * @param value
     * @param style
     */
    public static void createCell(HSSFRow row,int column ,Object value,HSSFCellStyle style){
        HSSFCell cell = row.createCell(column);
        cell.setCellValue(String.valueOf(value));
        cell.setCellStyle(style);
    }
    /**
     * 设置Excel抬头样式<br>
     * @param wb
     * @return
     */
    public static HSSFCellStyle getTitleStyle(HSSFWorkbook wb){
        HSSFCellStyle fontStyle = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        fontStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        fontStyle.setFont(font);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeight((short)250);
        return fontStyle;
    }
    /**
     * 设置Excel单元格样式<br>
     * @param wb
     * @return
     */
    public static HSSFCellStyle getBorderStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return cellStyle;
    }
}