poi导出excel

package org.easyinlab.overall.util;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;

import javax.imageio.ImageIO;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFPatternFormatting;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.easyinlab.biz.ZoomImage;

import com.sun.image.codec.jpeg.JPEGCodec;
import com.sun.image.codec.jpeg.JPEGEncodeParam;
import com.sun.image.codec.jpeg.JPEGImageEncoder;

public class ReadImg {

    //    public static void main(String[] args) {
    //
    //        math.
    //    }
    public static void main(String[] args) {
        FileOutputStream fos = null;
        BufferedImage bufferImg = null;
        XSSFWorkbook wb = null;
        XSSFSheet sheet = null;
        String[] audit = new String[] { "L", "Y", "N1", "N2", "N3" };
        int rowaccess = 5;//内存中缓存记录行数
        ZoomImage zi = new ZoomImage();
        for (int i = 0; i < 1; i++) {
            //创建工作簿
            wb = new XSSFWorkbook();
            //创建一个工作表sheet
            sheet = wb.createSheet();
            sheet.createFreezePane(1, 2, 2, 3);//冻结行列
            sheet.protectSheet("123");//设置保护密码
            XSSFCellStyle unlocked = wb.createCellStyle();
            unlocked.setLocked(false);//设置不锁定
            sheet.setPrintGridlines(true);
            sheet.setColumnHidden(18, true);
            sheet.setSelected(true);
            ((XSSFSheet) sheet).lockSelectLockedCells(true);//选中锁定单元格
            //              设置下拉框
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(audit);
            CellRangeAddressList addressList = new CellRangeAddressList(0, 65535, 17, 17);
            DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
            validation.setShowErrorBox(true);
            sheet.addValidationData(validation);
            // 设置列宽   
            sheet.setColumnWidth(0, 13 * 256);
            sheet.setColumnWidth(1, 9 * 256);
            sheet.setColumnWidth(2, 21 * 256);
            sheet.setColumnWidth(3, 9 * 256);
            sheet.setColumnWidth(4, 3500);
            sheet.setColumnWidth(5, 3500);
            sheet.setColumnWidth(6, 3500);
            sheet.setColumnWidth(7, 10000);
            sheet.setColumnWidth(8, 9 * 256);
            sheet.setColumnWidth(9, 5 * 256);
            sheet.setColumnWidth(10, 11 * 256);
            sheet.setColumnWidth(11, 3500);
            sheet.setColumnWidth(12, 21 * 256);
            sheet.setColumnWidth(13, 11 * 256);
            sheet.setColumnWidth(14, 3500);
            sheet.setColumnWidth(15, 70 * 256);
            sheet.setColumnWidth(16, 70 * 256);
            sheet.setColumnWidth(17, 9 * 256);
            sheet.setColumnWidth(18, 1 * 256);
            XSSFCellStyle sheetStyle = wb.createCellStyle();
            for (int x = 0; x <= 20; x++) {
                sheet.setDefaultColumnStyle((short) x, sheetStyle);
            }
            // 设置字体   
            XSSFFont headfont = wb.createFont();
            headfont.setFontName("黑体");
            headfont.setFontHeightInPoints((short) 22);// 字体大小   
            headfont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 加粗
            //            头行格式
            XSSFCellStyle headstyle = wb.createCellStyle();
            headstyle.setFont(headfont);
            headstyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 左右居中   
            headstyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中   
            headstyle.setLocked(false);
            headstyle.setWrapText(true);// 自动换行   
            //              头行内容
            Row row0 = sheet.createRow(0);
            row0.setHeight((short) (0));
            Cell cell0 = row0.createCell(0);
            cell0.setCellValue(new XSSFRichTextString("光明培儿贝瑞H5活动"));
            cell0.setCellStyle(headstyle);
            CellRangeAddress range = new CellRangeAddress(0, 0, 0, 20);
            sheet.addMergedRegion(range);
            Row row1 = sheet.createRow(1);
            row1.setHeight((short) (750));
            Cell cell = row1.createCell(0);
            cell.setCellValue(new XSSFRichTextString("电话"));
            cell = row1.createCell(1);
            cell.setCellValue(new XSSFRichTextString("宝宝姓名"));
            cell = row1.createCell(2);
            cell.setCellValue(new XSSFRichTextString("报名时间"));
            cell = row1.createCell(3);
            cell.setCellValue(new XSSFRichTextString("省/直辖市"));
            cell = row1.createCell(4);
            cell.setCellValue(new XSSFRichTextString("二级市"));
            cell = row1.createCell(5);
            cell.setCellValue(new XSSFRichTextString("镇/区"));
            cell = row1.createCell(6);
            cell.setCellValue(new XSSFRichTextString("县/街道"));
            cell = row1.createCell(7);
            cell.setCellValue(new XSSFRichTextString("地址"));
            cell = row1.createCell(8);
            cell.setCellValue(new XSSFRichTextString("妈妈状态"));
            cell = row1.createCell(9);
            cell.setCellValue(new XSSFRichTextString("性别"));
            cell = row1.createCell(10);
            cell.setCellValue(new XSSFRichTextString("出生年月"));
            cell = row1.createCell(11);
            cell.setCellValue(new XSSFRichTextString("出生证编号"));
            cell = row1.createCell(12);
            cell.setCellValue(new XSSFRichTextString("发送时间"));
            cell = row1.createCell(13);
            cell.setCellValue(new XSSFRichTextString("预产期"));
            cell = row1.createCell(14);
            cell.setCellValue(new XSSFRichTextString("大卡编号"));
            cell = row1.createCell(15);
            cell.setCellValue(new XSSFRichTextString("pic1"));
            cell = row1.createCell(16);
            cell.setCellValue(new XSSFRichTextString("pic2"));
            cell = row1.createCell(17);
            cell.setCellValue(new XSSFRichTextString("审核状态"));
            cell = row1.createCell(18);
            cell.setCellValue(new XSSFRichTextString("22222222222222222222222"));

            //追加数据

            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
            //            背景色
            XSSFCellStyle style = wb.createCellStyle();
            style.setFillPattern(XSSFPatternFormatting.FINE_DOTS);
            style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            for (int j = 0; j < 500; j++) {
                Row row = sheet.createRow(j + 2);
                row.setHeight((short) (6000));
                //System.out.println(batch.get(j).getCreatTime());
                cell = row.createCell(0);
                cell.setCellValue("12345678901");
                cell = row.createCell(1);
                cell.setCellValue("张三");
                cell = row.createCell(2);
                cell.setCellValue("");
                cell = row.createCell(3);
                cell.setCellValue("江苏省");
                cell = row.createCell(4);
                cell.setCellValue("宿迁市");
                cell = row.createCell(5);
                cell.setCellValue("泗阳县");
                cell = row.createCell(6);
                cell.setCellValue("王集镇");
                cell = row.createCell(7);
                cell.setCellValue("115号103");
                cell = row.createCell(8);
                cell.setCellValue("宝妈");

                cell = row.createCell(9);
                cell.setCellValue("男");
                cell = row.createCell(10);
                cell.setCellValue("2000-01-01");
                cell = row.createCell(11);
                cell.setCellValue("utf-87654");
                cell = row.createCell(12);

                cell.setCellValue("2017-06-07");

                cell = row.createCell(13);
                cell.setCellValue("");
                cell.setCellStyle(style);
                cell = row.createCell(14);
                cell.setCellValue("");
                cell.setCellStyle(style);
                File file = new File("E:/222.jpg");
                if (!file.exists()) {
                }
                double f = Math.random()*0.4+0.1;
               /* BigDecimal b = new BigDecimal(f);
                double f1 = b.setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue();*/
                BufferedImage pic = zi.zoomImage(file, (float)f);
                FileOutputStream newimage = null;
                try {
                    /*输出到文件流*/
                    newimage = new FileOutputStream("E:/123.jpg");
                    JPEGImageEncoder encoder = JPEGCodec.createJPEGEncoder(newimage);
                    JPEGEncodeParam jep = JPEGCodec.getDefaultJPEGEncodeParam(pic);
                    /* 压缩质量 */
                    jep.setQuality((float) f, true);
                    encoder.encode(pic, jep);
                    /*近JPEG编码*/

               
                cell = row.createCell(15);
                File file2 = new File("E:/123.jpg");
                    ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();
                    bufferImg = ImageIO.read(file2);
                    bufferImg.getHeight();
                   
                    ImageIO.write(bufferImg, "jpg", byteArrayOut1);
                    //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）  
                    Drawing patriarch1 = sheet.createDrawingPatriarch();
                    //anchor主要用
                    XSSFClientAnchor anchor1 = null;
             /*       if (bufferImg.getWidth() > bufferImg.getHeight()) {

                        anchor1 = new XSSFClientAnchor(5, 5, 0, 0, (short) 15, (j + 2), (short) 16,
                            (j + 3));
                    } else {
                        anchor1 = new XSSFClientAnchor(250, 5, 0, 0, (short) 15, (j + 2),
                            (short) 16, (j + 3));
                    }*/
                    anchor1 = new XSSFClientAnchor(bufferImg.getWidth(), bufferImg.getHeight(), 0, 0, (short) 15, (j + 2),
                            (short) 16, (j + 3));
                    //插入图片
                    patriarch1.createPicture(anchor1,
                        wb.addPicture(byteArrayOut1.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));

                    cell = row.createCell(16);

                    ByteArrayOutputStream byteArrayOut2 = new ByteArrayOutputStream();
                    bufferImg = pic;
                    ImageIO.write(bufferImg, "jpg", byteArrayOut2);
                    Drawing patriarch2 = sheet.createDrawingPatriarch();
                    XSSFClientAnchor anchor2 = null;
                   /* if (bufferImg.getWidth() > bufferImg.getHeight()) {
                        anchor2 = new XSSFClientAnchor(5, 5, 0, 0, (short) 16, (j + 2), (short) 17,
                            (j + 3));
                    } else {
                        anchor2 = new XSSFClientAnchor(250, 5, 0, 0, (short) 16, (j + 2),
                            (short) 17, (j + 3));
                    }*/
                    anchor2 = new XSSFClientAnchor(bufferImg.getWidth(), bufferImg.getHeight(), bufferImg.getWidth(), bufferImg.getHeight(), (short) 16, (j + 2),
                            (short) 17, (j + 3));
                    patriarch2.createPicture(anchor2,
                        wb.addPicture(byteArrayOut2.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));

                    cell = row.createCell(17);

                    cell.setCellStyle(unlocked);
                    //每当行数达到设置的值就刷新数据到硬盘,以清理内存
                    if (j != 0) {
                        if (j % rowaccess == 0) {
                            SXSSFWorkbook sw = new SXSSFWorkbook();
                            SXSSFSheet sheet1 = new SXSSFSheet(sw, sheet);
                            sheet1.flushRows();
                        }
                    }
                    
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }finally {
                    try {
                        newimage.close();
                    } catch (IOException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }

            }
            // 写入excel文件
            try {
                fos = new FileOutputStream("E:/2.xlsx");
                wb.write(fos);
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }
}