poi导出excel

最近一直想写一个导出，去网上看了一下，大部分是采用HSSF的方式来导出的，所以自己想写一个基于XSSF来做的导出，去官网上看了些例子，做出来了一个小demo，用于记录自己的成果。

项目采用的maven来管理的，当然首先得引入关于POI的jar包。

<poi.version>3.14</poi.version>
<poi-ooxml.version>3.14</poi-ooxml.version>

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi-ooxml.version}</version>
        </dependency>

下面开始我们正式的编码了。

package cn.sampson.utils;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.PrintSetup;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
import java.io.FileOutputStream;
import java.io.FileNotFoundException;
import java.util.Map;
import java.util.HashMap;
import java.util.List;

/**
 * Created with IntelliJ IDEA
 * ProjectName: util
 * CreateTime : 2016/5/17 15:14
 * Class Description:
 * To change this template use File | Settings | File Template
 */
public class ExcelUtils {
    private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    public static final String xls = ".xlsx";

    /**
     * 采用XSSF方式导出excel（2007以上的版本采用此种方式）
     *
     * @param sheetName
     * @param titles
     * @param dataSet
     */
    public void exportExcel(String sheetName, String[] titles, List<Object[]> dataSet){
        //声明工作薄
        Workbook workbook = new XSSFWorkbook();

        Map<String,CellStyle> styles = createStyles(workbook);
        Sheet sheet = workbook.createSheet(sheetName);
        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        sheet.setDefaultColumnWidth(18);

        Row headerRow = sheet.createRow(0);
        //设置行高
        headerRow.setHeightInPoints(25.75f);
        Cell headerCell;
        for (int i = 0; i < titles.length; i++) {
            headerCell = headerRow.createCell(i);
            headerCell.setCellValue(titles[i]);
            headerCell.setCellStyle(styles.get("header"));
        }

        //写入excel
        writeExcel(sheet, styles, dataSet);

        //开始执行导出操作
        String xlsFile = sheetName+"_"+System.currentTimeMillis()+xls;
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(xlsFile);
            workbook.write(out);
            out.close();
            workbook.close();
        } catch (FileNotFoundException e) {
            logger.error("Not Found" + xlsFile+" exception " + e.getMessage() + " at " + ExcelUtils.class);
        } catch (IOException e) {
            logger.error("IO Exception" + e.getMessage() + " at " + ExcelUtils.class);
        }

    }

    /**
     * create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook workbook){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        //表头样式
        Font headerFont = workbook.createFont();
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        headerFont.setFontName("微软雅黑");
        headerFont.setFontHeightInPoints((short)10);
        headerFont.setColor(IndexedColors.BLACK.getIndex());

        style = createBorderedStyle(workbook);
        //单元格居中
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //单元格背景色
        style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(headerFont);
        style.setWrapText(false);//不需要自动换行，如果需要自动换行，设置为true
        styles.put("header", style);

        //普通单元格样式
        Font normalFont = workbook.createFont();
        normalFont.setFontHeight((short)5);
        style = createBorderedStyle(workbook);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setWrapText(false);
        createBorderedStyle(workbook);
        styles.put("cell", style);

        return styles;
    }

    //创建带黑边框样式的单元格
    private static CellStyle createBorderedStyle(Workbook wb){
        CellStyle style = wb.createCellStyle();
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        return style;
    }

    //写入excel
    private void writeExcel(Sheet sheet,Map<String, CellStyle> styles,List<Object[]> dataSet){
        Row row;
        Cell cell;
        int rowNum = 1;
        for (int i = 0; i < dataSet.size(); i++,rowNum++) {
            row = sheet.createRow(rowNum);
            if (null == dataSet.get(i)) {
                continue;
            }
            //开始写入数据
            for (int j = 0; j < dataSet.get(i).length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(dataSet.get(i)[j].toString());
                //设置样式
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }
}

下面开始写测试代码：

public static void main(String[] args) {
        String[] titles = new String[]{"日期","邮箱","应用名称","OS"};
        List<Object[]> dataSet = new LinkedList<Object[]>();
        for (int i = 0; i <= 1000; i++) {
            Object[] obj = new Object[titles.length];
            obj[0]= DateUtils.format(new Date(),DateUtils.FORMAT_DATE_ONLY);
            obj[1] = "owen" + i + "@163.com";
            obj[2] = "开心消消乐" + i + "";
            obj[3] = "iOS";

            dataSet.add(obj);
        }
        ExcelUtils excelUtils = new ExcelUtils();
        excelUtils.exportExcel("应用信息",titles,dataSet);

    }

导出的效果图如下：