POI study

      工作需要研究POI技术导出Excel格式，目前实验版本支持Excel 2003，其他版本有待验证确认。记录工作需要，其实整体来说了解POI开源技术，学习apache(http://poi.apache.org/)提供的源码和官方文档之后，感觉难度不大，主要内容包括常规的导出(比如：单元格合并、数据迭代、单元格图片操作、字体颜色、单元格背景、单元格格式、单元格条件格式、单元格函数控制等)，权当工作记录，以资后用。参考源码如下:

 

package com.poi;

/**
 * @author zhaoxiaobo
 * @email  [e-mail]
 * @createdTime：2011-9-11
 * JDK: 1.6.0_19
 * POI: 3.8 (20120326)
 * JavaBean basic data object class
 */
public class MeterReadings {
    private String carNo;
    private Long departureKm;
    private Long currentReadings;
    private Long arrivalKm;
    private Long actualKm;
    private byte[] picture;

    public MeterReadings(String carNo, Long departureKm, Long currentReadings, Long rrivalKm,
        Long actualKm, byte[] picture) {
        this.carNo = carNo;
        this.departureKm = departureKm;
        this.currentReadings = currentReadings;
        this.arrivalKm = actualKm;
        this.actualKm = actualKm;
        this.picture = picture;
    }

    public String getCarNo() {
        return carNo;
    }

    public void setCarNo(String carNo) {
        this.carNo = carNo;
    }

    public Long getDepartureKm() {
        return departureKm;
    }

    public void setDepartureKm(Long departureKm) {
        this.departureKm = departureKm;
    }

    public Long getCurrentReadings() {
        return currentReadings;
    }

    public void setCurrentReadings(Long currentReadings) {
        this.currentReadings = currentReadings;
    }

    public void setArrivalKm(Long arrivalKm) {
        this.arrivalKm = arrivalKm;
    }

    public Long getArrivalKm() {
        return arrivalKm;
    }

    public void setActualKm(Long actualKm) {
        this.actualKm = actualKm;
    }

    public Long getActualKm() {
        return actualKm;
    }

    public void setPicture(byte[] picture) {
        this.picture = picture;
    }

    public byte[] getPicture() {
        return picture;
    }
}

 

 

 

package com.poi;

/**
 * @author zhaoxiaobo
 * @email  [e-mail]
 * @createdTime：2011-9-11
 * JDK: 1.6.0_19
 * POI: 3.8 (20120326)
 *
 * Generate excel data class
 */
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.swing.JOptionPane;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPatternFormatting;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFSheetConditionalFormatting;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FontFormatting;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExportExcelTest<T> {

    /**
     * This is generic method, using Java reflection (invoke) mechanism ,can be placed some Java Collections data
     * and certain conditions of the data symbols in the form of Excel output to the specified IO device.
     * refer to the official website
     * @param sheetName    sheet name
     * @param headers      output table header name array
     * @param dataset      Javabean data collections (get set method)
     * @param out          output object stream(specify output path)
     * @param dateFormat   datetime format dd/MM/yyyy (d/MM/yyyy HH:mm:ss)
     *
     */

    public void exportExcel(String sheetName, String[] headers, Collection<T> dataset, OutputStream out, String dateFormat) {
        // declare a workbook
        HSSFWorkbook workbook = new HSSFWorkbook();
        // create one sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //Fixed cell width : 15 character
        sheet.setDefaultColumnWidth(15);
        //specify width
        sheet.setColumnWidth(2, 5400);

        sheet.createFreezePane(0, 3);

        // create one style
        HSSFCellStyle style = workbook.createCellStyle();
        // setup style
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // create one font
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // the font is applied to the current style
        style.setFont(font);
        // create another style
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
        style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        //define cell width
        HSSFCellStyle styleWidth = workbook.createCellStyle();
        //styleWidth.set
        styleWidth.setFillForegroundColor(HSSFColor.BLUE_GREY.index);

        // create another font
        HSSFFont font2 = workbook.createFont();
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        font2.setFontHeight((short) 250);
//        System.out.println(font2.getFontHeight());
//        System.out.println(font2.getFontHeightInPoints());

        // the font is applied to the current style
        style2.setFont(font2);
        // declare one top manager for painting
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // define one comment content
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
        // setup comment content author , invalidate
        comment.setAuthor("Zxb Dennis");
        // setup comment content
        comment.setString(new HSSFRichTextString("Insert into comment for pic！"));

        //comment.setAuthor(author)
        comment.setVisible(false);
        comment.setFillColor(255, 155, 255);
        comment.setLineStyle(1);


        HSSFCellStyle styleDate = workbook.createCellStyle();
        styleDate.setAlignment(CellStyle.ALIGN_RIGHT);

        HSSFFont fontDate = workbook.createFont();
        fontDate.setColor(HSSFColor.BLACK.index);
        fontDate.setFontHeightInPoints((short) 12);
        fontDate.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        styleDate.setFont(fontDate);

        HSSFRow row0 = sheet.createRow(0);
        row0.setHeightInPoints(20);
        HSSFCell cell0 = row0.createCell(0);
        HSSFCell cell1 = row0.createCell(1);
        cell0.setCellValue("Date:");
        cell0.setCellStyle(styleDate);
        //cell0.setCellStyle(styleMerge);

        //Date date = (Date) value;
        SimpleDateFormat sdf0 = new SimpleDateFormat(dateFormat);
        String dateValue = sdf0.format(new Date());
        cell1.setCellValue(dateValue);

        //merge column
        HSSFRow row1 = sheet.createRow(1);
        row1.setHeightInPoints(12);
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$I$2"));

        //create new row
        HSSFRow row = sheet.createRow(2);
        row.setHeightInPoints(15);

        //table header column
        for (int i = 0, len = headers.length; i < len; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            cell.setCellComment(comment);
        }

        //iterator data , generate data
        Iterator<T> it = dataset.iterator();
        HSSFSheetConditionalFormatting formating = sheet.getSheetConditionalFormatting();
        int index = 2;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = (T) it.next();
            //Java reflection technology Javabean
            Field[] fields = t.getClass().getDeclaredFields();
            for (int i = 0, len = fields.length; i < len; i++) {
                HSSFCell cell = row.createCell(i);

                cell.setCellStyle(style2);
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get"
                + fieldName.substring(0, 1).toUpperCase()
                + fieldName.substring(1);
                try {
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName, new Class[] { });
                    Object value = getMethod.invoke(t, new Object[] { });
                    //cast data type
                    String textValue = null;
                    if (value instanceof Integer) {
                        int intValue = (Integer) value;
                        cell.setCellValue(intValue);
                    } else if (value instanceof Float) {
                        float fValue = (Float) value;
                        textValue = new HSSFRichTextString(String.valueOf(fValue)).toString();
                        cell.setCellValue(textValue);
                    } else if (value instanceof Double) {
                        double dValue = (Double) value;
                        textValue = new HSSFRichTextString(String.valueOf(dValue)).toString();
                        cell.setCellValue(textValue);
                    } else if (value instanceof Long) {
                        long longValue = (Long) value;
                        cell.setCellValue(longValue);
                    }

                    if (value instanceof Boolean) {
                        boolean bValue = (Boolean) value;
                        textValue = "是";
                        if (!bValue) {
                            textValue = "否";
                        }
                    } else if (value instanceof Date) {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
                        textValue = sdf.format(date);
                    } else if (value instanceof byte[]) {
                        // setup row height
                        row.setHeightInPoints(25);
                        //
                        sheet.setColumnWidth(i, (short) (35.7 * 80));
                        // sheet.autoSizeColumn(i);
                        byte[] bsValue = (byte[]) value;
                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
                        1023, 255, (short) 5, index, (short) 5, index);
                        anchor.setAnchorType(2);
                        patriarch.createPicture(anchor, workbook.addPicture(
                        bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
                    } else {
                        //toString
                        textValue = value.toString();
                    }
                    //validate data type
                    if (textValue != null) {
                        //Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                        Pattern p = Pattern.compile("^\\d+$");
                        Matcher matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            //cell.getCellStyle();
                            if (!"carNo".equals(fieldName)) {
                                setCellStyle(workbook, cell);
                            }
                            if ("arrivalKm".equals(fieldName)) {
                                cell.setCellFormula("(LEFTB(B" + (index + 1)+",3)) * 10000 + C" + (index + 1));

                                //digit data type
                                cell.setCellValue(Double.parseDouble(textValue));
                            } else if ("actualKm".equals(fieldName)) {
                                String conditionFormat = "D" + (index + 1) + "-" + "B" + (index + 1);
                                cell.setCellFormula(conditionFormat);
                                //cell.setCellValue(richString);
                                //HSSFConditionalFormattingRule rule1 = sheet.createConditionalFormattingRule("$A$1<96");
                                conditionFormat = "OR(E" + (index + 1) + " < 0,E" + (index + 1) + " > 500)";
                                //System.out.println("=== " + conditionFormat);
                                CellRangeAddress[] range = {
                                    new CellRangeAddress(index, index ,4 ,4)
                                };
                                //cell.setCellComment(comment);
                                //cell.setCellStyle(styleWidth);
                                addConditionFormat(formating, range, conditionFormat, cell, workbook);

                            } else {
                                //digit
                                cell.setCellValue(Double.parseDouble(textValue));
                            }
                        } else {
                            HSSFRichTextString richString = new HSSFRichTextString(textValue);
                            HSSFFont font3 = workbook.createFont();
                            font3.setColor(HSSFColor.BLUE.index);
                            richString.applyFont(font3);
                            if ("arrivalKm".equals(fieldName)) {
                                cell.setCellFormula("(LEFTB(B" + (index + 0)+",3)) * 10000 + C" + (index + 0));
                                cell.setCellValue(richString);
                                //HSSFConditionalFormattingRule rule1 = formating.createConditionalFormattingRule("$A$1<96");
                            } else {
                                cell.setCellValue(richString);
                            }
                        }
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }  finally {

                }
            }
        }
        try {
            writeSummary(sheet, workbook);
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void addConditionFormat(HSSFSheetConditionalFormatting formating, CellRangeAddress[] range,
        String formatRule, HSSFCell cell, HSSFWorkbook workbook) {
        try{
            HSSFConditionalFormattingRule rule1 = formating.createConditionalFormattingRule(formatRule);
            HSSFConditionalFormattingRule[] rule = {rule1};
            //cellstyle background color setup
            HSSFPatternFormatting patternFmt1 = rule1.createPatternFormatting();
            patternFmt1.setFillBackgroundColor(HSSFColor.ROYAL_BLUE.index);
            //cellstyle font color setup
            FontFormatting font = rule1.createFontFormatting();
            font.setFontStyle(false, true);
            font.setFontColorIndex(IndexedColors.RED.index);
            //patternFmt1.setFillPattern(fp);
            formating.addConditionalFormatting(range, rule);
        }catch(Exception ex) {
            ex.printStackTrace();
        }
    }

    private void writeSummary(final HSSFSheet sheet, final HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);

        HSSFFont font = wb.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);


        //another
        HSSFCellStyle style2 = wb.createCellStyle();
        style2.setAlignment(CellStyle.ALIGN_CENTER);

        HSSFFont font2 = wb.createFont();
        font2.setColor(HSSFColor.GREEN.index);
        font2.setFontHeightInPoints((short) 12);
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style2.setFont(font2);


        HSSFRow row1 = sheet.getRow(3);
        HSSFCell cell = row1.createCell(7);
        HSSFCell cell_ = row1.createCell(8);
        cell.setCellValue("Max:");
        cell_.setCellFormula("MAX(E4:E200)");
        cell.setCellStyle(style);
        cell_.setCellStyle(style2);

        HSSFRow row2 = sheet.getRow(4);
        HSSFCell cel2 = row2.createCell(7);
        HSSFCell cel2_ = row2.createCell(8);
        cel2.setCellValue("Mix:");
        cel2_.setCellFormula("MIN(E4:E200)");
        cel2.setCellStyle(style);
        cel2_.setCellStyle(style2);

        HSSFRow row3 = sheet.getRow(5);
        HSSFCell cel3 = row3.createCell(7);
        HSSFCell cel3_ = row3.createCell(8);
        cel3.setCellValue("Average:");
        cel3_.setCellFormula("AVERAGE(E4:E200)");
        cel3.setCellStyle(style);
        cel3_.setCellStyle(style2);

        //specify width
        sheet.setColumnWidth(6, 1500);
        sheet.setColumnWidth(7, 3000);
        sheet.setColumnWidth(8, 2500);
    }

    private void setCellStyle(final HSSFWorkbook wb, final HSSFCell cell) {
        HSSFCellStyle styleThousands = wb.createCellStyle();
        styleThousands.cloneStyleFrom(cell.getCellStyle());
        styleThousands.setAlignment(CellStyle.ALIGN_CENTER);
        //styleThousands.setDataFormat(workbook.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
        //styleThousands.setDataFormat(wb.createDataFormat().getFormat("#,##0.00"));
        styleThousands.setDataFormat(wb.createDataFormat().getFormat("#,##0"));
        cell.setCellStyle(styleThousands);
    }

    public static void main(String[] args) {
        // Meter readings
        ExportExcelTest<MeterReadings> readings = new ExportExcelTest<MeterReadings>();
        String[] headers = {"Car Number", "Depart Km", "Current Readings", "Arrival Km", "Actual Km" ,"Picture"};
        List<MeterReadings> dataset = new ArrayList<MeterReadings>();
        //basic data
        try {
            BufferedInputStream bis = new BufferedInputStream(
                new FileInputStream("C:\\TEMP\\default\\xz.jpg"));
            byte[] buf = new byte[bis.available()];
            while ((bis.read(buf)) != -1) {
            }
            dataset.add(new MeterReadings("1001", 2115827l, 6063l, 2116063l, 236l,buf));
            dataset.add(new MeterReadings("1002", 2040982l, 1301l, 2041301l, 319l,buf));
            dataset.add(new MeterReadings("1003", 2072589l, 2782l, 2072782l, 193l,buf));
            dataset.add(new MeterReadings("1004", 1910111l, 0303l, 1910303l, 192l,buf));
            dataset.add(new MeterReadings("1005", 2005604l, 5875l, 2005875l, 271l,buf));
            dataset.add(new MeterReadings("1006", 2086034l, 6270l, 2086270l, 236l,buf));
            dataset.add(new MeterReadings("1007", 1986280l, 6600l, 1986600l, 320l,buf));
            dataset.add(new MeterReadings("1008", 2037798l, 8131l, 2038131l, 333l,buf));
            dataset.add(new MeterReadings("1009", 2122344l, 2565l, 2122565l, 221l,buf));
            dataset.add(new MeterReadings("1010", 2081331l, 1400l, 2081400l, 69l,buf));
            OutputStream out = new FileOutputStream("D:\\tmp\\b\\MeterReadings.xls");
            //readings.exportExcel("Car km data", headers, dataset, out, "dd/MM/yyyy HH:mm:ss");
            readings.exportExcel("Car km data", headers, dataset, out, "dd/MM/yyyy");
            out.close();
            JOptionPane.showMessageDialog(null, "Export successfully!!!");
        } catch (Exception e) {
            System.out.println("Exception：you should close file(MeterReadings.xls)!!!\n");
            e.printStackTrace();
        }

    }

}

 

 

 

 

   最后效果图：