package?com.hibro.util;

import?org.apache.poi.hssf.usermodel.*;
import?org.apache.poi.hssf.util.HSSFColor;
import?org.apache.poi.ss.util.CellRangeAddress;

import?javax.imageio.ImageIO;
import?javax.servlet.http.HttpServletResponse;
import?java.awt.image.BufferedImage;
import?java.io.ByteArrayOutputStream;
import?java.io.FileOutputStream;
import?java.io.IOException;
import?java.io.OutputStream;
import?java.lang.reflect.Field;
import?java.lang.reflect.InvocationTargetException;
import?java.lang.reflect.Method;
import?java.net.URL;
import?java.text.SimpleDateFormat;
import?java.util.ArrayList;
import?java.util.Date;
import?java.util.Iterator;
import?java.util.List;
import?java.util.regex.Matcher;
import?java.util.regex.Pattern;

/**
?*?Created?by?admin?on?2018/8/24.
?*/
public?class?CommonExcel?{

????//顯示的導(dǎo)出表的標(biāo)題
????private?String?title;
????//導(dǎo)出表的列名
????private?String[]?rowName;
????//導(dǎo)出的文件名
????private?String?fileName;

????private?Listject[]>?dataList?=?new?ArrayListject[]>（）;

????private?HttpServletResponse?response;
????/**?時(shí)間格式：默認(rèn)為yyyy-MM-dd?*/
????private?String?DATE_PATTERN?=?“yyyy-MM-dd“;

????/**?圖片寬度，默認(rèn)為：100?*/
????private?int?IMAGE_WIDTH?=?30;

????/**?圖片高度，默認(rèn)為：50?*/
????private?int?IMAGE_HEIGHT?=?5;

????/**?單元格的最大寬度?*/
????private?int[]?maxWidth;

????/**
?????*?單頁支持最多數(shù)據(jù)列：超過65534會出錯(cuò)
?????*?若數(shù)據(jù)列多余65534則需要通過MORE_EXCEL_FLAG、MORE_SHEET_FLAG來區(qū)別生成多個(gè)Excel、還是sheet
?????*/
????private?int?maxRowCount?=?2500;

????/**?大量數(shù)據(jù)，多個(gè)Excel標(biāo)識---0001?*/
????private?String??MORE_EXCEL_FLAG?=?“0001“;

????/**?大量數(shù)據(jù)，多個(gè)sheet標(biāo)識---0001?*/
????private?String?MORE_SHEET_FLAG?=?“0002“;
????//構(gòu)造方法，傳入要導(dǎo)出的數(shù)據(jù)
????public?CommonExcel（String?title?String[]?rowName?Listject[]>?dataList?HttpServletResponse?response?String?fileName）?{
????????this.dataList?=?dataList;
????????this.rowName?=?rowName;
????????this.title?=?title;
????????this.response?=?response;
????????this.fileName?=?fileName;
????}

????/*
?????*?導(dǎo)出數(shù)據(jù)
?????*/
????public?void?downloadExcel（）?throws?Exception?{
????????try?{
????????????HSSFWorkbook?workbook?=?new?HSSFWorkbook（）;?????????????????????//?創(chuàng)建工作簿對象
????????????HSSFSheet?sheet?=?workbook.createSheet（title）;??????????????????//?創(chuàng)建工作表

????????????//?產(chǎn)生表格標(biāo)題行
????????????HSSFRow?rowm?=?sheet.createRow（0）;
????????????HSSFCell?cellTiltle?=?rowm.createCell（0）;

????????????//sheet樣式定義【getColumnTopstyle（）/getstyle（）均為自定義方法?-?在下面??-?可擴(kuò)展】
????????????HSSFCellstyle?columnTopstyle?=?this.getColumnTopstyle（workbook）;//獲取列頭樣式對象
????????????HSSFCellstyle?style?=?this.getstyle（workbook）;??????????????????//單元格樣式對象

????????????sheet.addMergedRegion（new?CellRangeAddress（0?1?0?（rowName.length?-?1）））;
????????????cellTiltle.setCellstyle（columnTopstyle）;
????????????cellTiltle.setCellValue（title）;

????????????//?定義所需列數(shù)
????????????int?columnNum?=?rowName.length;
????????????HSSFRow?rowRowName?=?sheet.createRow（2）;????????????????//?在索引2的位置創(chuàng)建行（最頂端的行開始的第二行