poi导出excel

Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。 

此例子用的是poi3.9的版本，请在apache官网自行下载。

一、相关api介绍

//生成Workbook  (.xls)
HSSFWorkbook wb = new HSSFWorkbook();  
//生成Workbook  (.xlsx)
XSSFWorkbook wb = new XSSFWorkbook();  

//添加Worksheet（不添加sheet时生成的xls文件打开时会报错）  
@SuppressWarnings("unused")  
Sheet sheet1 = wb.createSheet();
  
//保存为Excel文件  
FileOutputStream out = null;  
try {  
    out = new FileOutputStream("c:\text.xls");  
    wb.write(out);        
} catch (IOException e) {  
    System.out.println(e.toString());  
} finally {  
    try {  
        out.close();  
    } catch (IOException e) {  
        System.out.println(e.toString());  
    }  
} 

//读取Workbook
FileInputStream in = null;  
Workbook wb = null;  
try {  
    in = new FileInputStream(TEST_WORKBOOK_NAME);  
    wb = WorkbookFactory.create(in);  
} catch (IOException e) {  
    System.out.println(e.toString());  
} catch (InvalidFormatException e) {  
    System.out.println(e.toString());  
} finally {  
    try {  
        in.close();  
    } catch (IOException e) {  
        System.out.println(e.toString());  
    }  
}
   
//行操作
//创建
Row row1 = wb.getSheet("sheet1").createRow(1); 
//删除
wb.getSheet("sheet1").removeRow(row1);  
//移动行（把第2行和第3行移到第6行之后）  
wb.getSheet("sheetname5").shiftRows(1, 2, 6);  
//设置默认行高  
wb.getSheet("sheetname6").setDefaultRowHeight((short)100);  
//设置行高  
wb.getSheet("sheetname6").getRow(2).setHeight((short)(100 * 20));  
//设置默认列宽  
wb.getSheet("sheetname7").setDefaultColumnWidth(12);  
//设置列宽  
wb.getSheet("sheetname7").setColumnWidth(0, 5 * 256);  

//单元格操作
//追加行  
for (int i = 0; i < 10; i++) {  
    Row row = wb.getSheet("sheetname10").createRow(i);  
    for (int j = 0; j < 10; j++) {  
        //添加单元格  
        Cell cell = row.createCell(j);  
        cell.setCellValue(i + 1);  
    }  
      
    //删除单元格  
    row.removeCell(row.getCell(5));  
}          
//合并单元格  
//CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)  
wb.getSheet("sheetname10").addMergedRegion(new CellRangeAddress(1, 4, 2, 3));  

//设置单元格值
for (List<Object> dataRow : objects) {
    row = sheet.createRow(lastRowIndex);
    lastRowIndex++;
    for (int j = 0; j < dataRow.size(); j++) {
        Cell contentCell = row.createCell(j);
        Object dataObject = dataRow.get(j);
        if (dataObject != null) {
            if (dataObject instanceof Integer) {
                contentCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
                contentCell.setCellStyle(contentIntegerStyle);
                contentCell.setCellValue(Integer.parseInt(dataObject.toString()));
            } else if (dataObject instanceof Double) {
                contentCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
                contentCell.setCellStyle(contentDoubleStyle);
                contentCell.setCellValue(Double.parseDouble(dataObject.toString()));
            } else if (dataObject instanceof Long && dataObject.toString().length() == 13) {
                contentCell.setCellType(XSSFCell.CELL_TYPE_STRING);
                contentCell.setCellStyle(contentStyle);
                contentCell.setCellValue(getCnDate(new Date(Long.parseLong(dataObject.toString()))));
            } else if (dataObject instanceof Date) {
                contentCell.setCellType(XSSFCell.CELL_TYPE_STRING);
                contentCell.setCellStyle(contentStyle);
                contentCell.setCellValue(getCnDate((Date) dataObject));
            } else {
                contentCell.setCellType(XSSFCell.CELL_TYPE_STRING);
                contentCell.setCellStyle(contentStyle);
                contentCell.setCellValue(dataObject.toString());
            }
        } else {
            contentCell.setCellStyle(contentStyle);
            // 设置单元格内容为字符型
            contentCell.setCellValue("");
        }
    }
}

//设置标题头与标题列名
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(lastRowIndex, lastRowIndex, 0, columnNames.size() - 1));
// 产生表格标题行
Row rowMerged = sheet.createRow(lastRowIndex);
lastRowIndex++;
Cell mergedCell = rowMerged.createCell(0);
mergedCell.setCellStyle(headStyle);
mergedCell.setCellValue(new XSSFRichTextString(sheetTitle));
// 产生表格表头列标题行
Row row = sheet.createRow(lastRowIndex);
lastRowIndex++;
for (int i = 0; i < columnNames.size(); i++) {
    Cell cell = row.createCell(i);
    cell.setCellStyle(headStyle);
    RichTextString text = new XSSFRichTextString(columnNames.get(i));
    cell.setCellValue(text);
}

二、实例代码ExcelUtils

package com.skin.webcat.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExportExcelUtil2 {
    
    private static final int DEFAULT_COLUMN_SIZE = 30;

    /**
     * 导出字符串数据
     *
     * @param file        文件名
     * @param columnNames 表头
     * @param sheetTitle  sheet页Title
     * @param append      是否追加写文件
     * @return file
     * @throws ReportInternalException
     */
    public static void exportExcelTitle(File file,String sheetName, List<String> columnNames,
                                         String sheetTitle, boolean append,List<List<Object>> objects) throws  IOException {
        // 声明一个工作薄
        Workbook workBook = new XSSFWorkbook();
       if (file.exists() && append) {
            workBook = new XSSFWorkbook(new FileInputStream(file));
        } else {
            workBook = new XSSFWorkbook();
        }
        Map<String, CellStyle> cellStyleMap = styleMap(workBook);
        // 表头样式
        CellStyle headStyle = cellStyleMap.get("head");
        // 生成一个表格
        Sheet sheet = workBook.getSheet(sheetName);
        if (sheet == null) {
            sheet = workBook.createSheet(sheetName);
        }
        //最新Excel列索引,从0开始
        int lastRowIndex = sheet.getLastRowNum();
        if (lastRowIndex > 0) {
            lastRowIndex++;
        }
        // 设置表格默认列宽度
        sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(lastRowIndex, lastRowIndex, 0, columnNames.size() - 1));
        // 产生表格标题行
         Row rowMerged = sheet.createRow(lastRowIndex);
        lastRowIndex++;
        Cell mergedCell = rowMerged.createCell(0);
        mergedCell.setCellStyle(headStyle);
        mergedCell.setCellValue(new XSSFRichTextString(sheetTitle));
        // 产生表格表头列标题行
        Row row1 = sheet.createRow(lastRowIndex);
        for (int i = 0; i < columnNames.size(); i++) {
            Cell cell = row1.createCell(i);
            cell.setCellStyle(headStyle);
            RichTextString text = new XSSFRichTextString(columnNames.get(i));
            cell.setCellValue(text);
        }
        
        
        CellStyle contentStyle = cellStyleMap.get("content");
        //正文整数样式
        CellStyle contentIntegerStyle = cellStyleMap.get("integer");
        //正文带小数整数样式
        CellStyle contentDoubleStyle = cellStyleMap.get("double");
        // 生成一个表格
        if (sheet == null) {
            sheet = workBook.createSheet(sheetName);
        }
        //最新Excel列索引,从0开始
       if (lastRowIndex > 0) {
            lastRowIndex++;
        }
        // 设置表格默认列宽度
        sheet.setDefaultColumnWidth(DEFAULT_COLUMN_SIZE);
        // 遍历集合数据,产生数据行,前两行为标题行与表头行
        for (List<Object> dataRow : objects) {
            Row row = sheet.createRow(lastRowIndex);
            lastRowIndex++;
            for (int j = 0; j < dataRow.size(); j++) {
                Cell contentCell = row.createCell(j);
                Object dataObject = dataRow.get(j);
                if (dataObject != null) {
                    if (dataObject instanceof Integer) {
                        contentCell.setCellStyle(contentIntegerStyle);
                        contentCell.setCellValue(Integer.parseInt(dataObject.toString()));
                    } else if (dataObject instanceof Double) {
                        contentCell.setCellStyle(contentDoubleStyle);
                        contentCell.setCellValue(Double.parseDouble(dataObject.toString()));
                    } else if (dataObject instanceof Long && dataObject.toString().length() == 13) {
                        contentCell.setCellStyle(contentStyle);
                        contentCell.setCellValue(getCnDate(new Date(Long.parseLong(dataObject.toString()))));
                    } else if (dataObject instanceof Date) {
                        contentCell.setCellStyle(contentStyle);
                        contentCell.setCellValue(getCnDate((Date) dataObject));
                    } else {
                        contentCell.setCellStyle(contentStyle);
                        contentCell.setCellValue(dataObject.toString());
                    }
                } else {
                    contentCell.setCellStyle(contentStyle);
                    // 设置单元格内容为字符型
                    contentCell.setCellValue("");
                }
            }
        }
        
        try {
            OutputStream ops = new FileOutputStream(file);
            workBook.write(ops);
            ops.flush();
            ops.close();
        } catch (IOException e) {
            throw new IOException(e);
        }
//        return file;
    }
    /**
     * 日期转化为字符串,格式为yyyy-MM-dd HH:mm:ss
     */
    private static String getCnDate(Date date) {
        String format = "yyyy-MM-dd HH:mm:ss";
        SimpleDateFormat sdf = new SimpleDateFormat(format);
        return sdf.format(date);
    }
    
    /**
     * 创建单元格表头样式
     *
     * @param workbook 工作薄
     */
    private static CellStyle createCellHeadStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        //设置对齐样式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 生成字体
        Font font = workbook.createFont();
        // 表头样式
        style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        style.setFont(font);
        return style;
    }

    /**
     * 创建单元格正文样式
     *
     * @param workbook 工作薄
     */
    private static CellStyle createCellContentStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        //设置对齐样式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 生成字体
        Font font = workbook.createFont();
        // 正文样式
        style.setFillPattern(XSSFCellStyle.NO_FILL);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style.setFont(font);
        return style;
    }

    /**
     * 单元格样式(Integer)列表
     */
    private static CellStyle createCellContent4IntegerStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        //设置对齐样式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 生成字体
        Font font = workbook.createFont();
        // 正文样式
        style.setFillPattern(XSSFCellStyle.NO_FILL);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style.setFont(font);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));//数据格式只显示整数
        return style;
    }

    /**
     * 单元格样式(Double)列表
     */
    private static CellStyle createCellContent4DoubleStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        // 设置边框样式
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);
        style.setBorderTop(XSSFCellStyle.BORDER_THIN);
        //设置对齐样式
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 生成字体
        Font font = workbook.createFont();
        // 正文样式
        style.setFillPattern(XSSFCellStyle.NO_FILL);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式
        style.setFont(font);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));//保留两位小数点
        return style;
    }

    
    /**
     * 单元格样式列表
     */
    private static Map<String, CellStyle> styleMap(Workbook workbook) {
        Map<String, CellStyle> styleMap = new LinkedHashMap<>();
        styleMap.put("head", createCellHeadStyle(workbook));
        styleMap.put("content", createCellContentStyle(workbook));
        styleMap.put("integer", createCellContent4IntegerStyle(workbook));
        styleMap.put("double", createCellContent4DoubleStyle(workbook));
        return styleMap;
    }
    
    public static void main(String[] args) {
        File file = new File("d:/a.xlsx");
        List<List<Object>> objects = new ArrayList<List<Object>>();
        List<String> listn = new ArrayList<String>();
        listn.add("a");
        listn.add("b");
        listn.add("c");
        listn.add("d");
        for (int i = 0; i < 1000; i++) {
            List<Object> os = new ArrayList<Object>();
            os.add("a");
            os.add(new Date());
            os.add(1);
            os.add(1.33);
            objects.add(os);
        }
        try {
            exportExcelTitle(file, "sdf", listn, "213", false, objects);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}