/* ExcelReportPoi - Decompiled by JODE
 * Visit http://jode.sourceforge.net/
 */
package com.report;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Properties;
import java.util.Vector;

import javax.imageio.ImageIO;

import com.coolservlets.beans.method.genMethod;
//import com.pluck.miniui.common.File;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

//import psdi.webclient.beans.tools.DateTool;//2016 年去掉  Max75AppGDHZ.jar ,修改的这句！

public class ExcelReportPoi extends ExcelReport
{
	private Object Workbook;
	private String extension;
	private Properties clusters;
	private HashMap xcellstyle = new HashMap();
	private boolean isMergedRegion = false;
	private ArrayList al = new ArrayList();
	private String dataStyle = "";
    private double FontHeight = 10.5;
    public double 	getFontHeight() { return this.FontHeight; }
	public void	setFontHeight(double m_FontHeight) { this.FontHeight = m_FontHeight; }
	public static void main1(String[] args) {
		/**
		ExcelReportPoi erp = new ExcelReportPoi();
		erp.setBytesExcelModule("ImpTaskMonth.xlsx");
		String sYear = "2013";
		String sMonth = "05";
		String titleDate
		= new StringBuilder(sYear).append(" \u5e74 ").append(sMonth).append
		(" \u6708").toString();
		titleDate = DateTool.getMonthFirstDay(Integer.parseInt(sYear),Integer.parseInt(sMonth));
		Properties clusters1 = new Properties();
		Vector vDept = new Vector();
		vDept.addElement(new String("\u8ba1\u5212\u7ecf\u8425\u90e8"));
		clusters1.put("$DEPT", vDept);
		Vector vDate = new Vector();
		vDate.addElement(new String(titleDate));
		clusters1.put("$DATE", vDate);
		erp.setClusters(clusters1);
		try {
			erp.setBytesExcelModuleTitle();
		} catch (IOException e) {
			e.printStackTrace();
		}
		 **/

	}
	/**
	 * 这是一个完整的通过一个复杂的 excel 模板（必须是  xlsx ），通过该模板完全克隆新的打印报表，适合复杂的报表功能！
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			ExcelReportPoi excelReport = new ExcelReportPoi();
			excelReport.setBytesExcelModule("样品跟踪" + ".xlsx");//模板在 F:\MaxProject\Maxdbcom\Max7x\Max7xdbcom\src\resource 下，模板格式 必须是 2007 格式，2003 格式不行！
			String reportdata[][] = new String[10][33];
			int nloop = reportdata.length;
			for(int n=0;n<nloop;n++){
				int mloop = reportdata[n].length;
				for(int m=0;m<mloop;m++){
					reportdata[n][m] = Integer.toString(m+1);
				}
			}
			HashMap hm = new HashMap();
			hm.put("$TOTAL",Integer.toString(1));

			Properties clusters = new Properties();
			Vector vector = null;
			Object[] tmp = hm.keySet().toArray();
			for(int i=0;i<tmp.length;i++)
			{
				vector = new Vector();
				vector.addElement(hm.get(tmp[i]));
				clusters.put(tmp[i],vector);
			}
			excelReport.setClusters(clusters);
			excelReport.setBytesExcelModuleTitle();
			//excelReport.setCovertag("***");
			//excelReport.set
			String aligt[][] = {{"0","CENTER"}};
			excelReport.setFontHeight(9.0);
			byte[] ExcelDataReport = excelReport.getCollectBytes(reportdata,"3,0;",aligt);//5,0; 代表数据记录从 第 1 行的第 0 列开始！
			SaveStreamToFile(ExcelDataReport,"D:\\报表导出\\模板.xls");
		}catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	/**
	 * 这是一个完整的通过一个复杂的 excel 模板（xls 或 xlsx 多可以），通过该模板完全克隆新的打印报表，适合复杂的报表功能！
	 * @param args
	 */
	public static void main3(String[] args) {
		try {
			ExcelReportPoi excelReport = new ExcelReportPoi();
			excelReport.setBytesExcelModule("纵向-中间页" + ".xlsx");//模板在 F:\MaxProject\Maxdbcom\Max7x\Max7xdbcom\src\resource 下
			String reportdata[][] = new String[10][8];
			int nloop = reportdata.length;
			for(int n=0;n<nloop;n++){
				int mloop = reportdata[n].length;
				for(int m=0;m<mloop;m++){
					reportdata[n][m] = Integer.toString(m+1);
				}
			}
			HashMap hm = new HashMap();
			hm.put("$TOTAL",Integer.toString(1));

			Properties clusters = new Properties();
			Vector vector = null;
			Object[] tmp = hm.keySet().toArray();
			for(int i=0;i<tmp.length;i++)
			{
				vector = new Vector();
				vector.addElement(hm.get(tmp[i]));
				clusters.put(tmp[i],vector);
			}
			excelReport.setClusters(clusters);
			excelReport.setBytesExcelModuleTitle();
			excelReport.setCovertag("***");
			//excelReport.set
			String aligt[][] = {{"0","CENTER"}};
			byte[] ExcelDataReport = excelReport.getCollectBytes(reportdata,"5,0;",aligt);//5,0; 代表数据记录从 第 5 行的第 0 列开始！
			SaveStreamToFile(ExcelDataReport,"D:\\报表导出\\模板.xls");
		}catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	/**
	 * 一个完整的插入图片的例子
	 * @param args
	 */
	public static void main2(String[] args) {
		BufferedImage bufferImg = null;
		try {
			String modflm = "F:\\sqlrun\\bfny\\template\\" + "自校验报告模板.xlsx";
			FileInputStream filepath = new FileInputStream(modflm);
			XSSFWorkbook wb = new XSSFWorkbook(filepath);
			filepath.close();
			Object Workbook = wb;
			//String sReportpath = loadConfigValue("com/config/report.cfg", "reportpath");
			String Imageflm = "F:\\sqlrun\\bfny\\template\\" + "logo.jpg";
			ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
			FileInputStream fileIn = new FileInputStream(Imageflm);
			bufferImg = ImageIO.read(fileIn);     
			ImageIO.write(bufferImg, "jpg", byteArrayOut);
			//画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
			XSSFSheet xssfsheet = ((XSSFWorkbook) Workbook).getSheetAt(0);
			XSSFDrawing patriarch = xssfsheet.createDrawingPatriarch();
			//anchor主要用于设置图片的属性  
			//HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 255, 255,(short) 31, 33, (short) 31, 33);
			XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 255, 255,(short) 31, 33, (short) 37, 34);
			anchor.setAnchorType(3);
			//插入图片    
			patriarch.createPicture(anchor, ((XSSFWorkbook) Workbook).addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));  
			//xssfsheet.createDrawingPatriarch()
			FileOutputStream  fileOut = new FileOutputStream("F:\\sqlrun\\bfny\\template\\测试Excel.xlsx");
			//写入excel文件     
			((XSSFWorkbook) Workbook).write(fileOut);
			// 写入excel文件     
			//wb.write(fileOut);
			if(fileOut != null){  
				try {  
					fileOut.close();  
				} catch (IOException e) {  
					e.printStackTrace();  
				}  
			} 
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 添加图片
	 * 
	 * @param ImageFile
	 * @param dx1：the x coordinate within the first cell。
	 * @param dy1：the y coordinate within the first cell。
	 * @param dx2：the x coordinate within the second cell。
	 * @param dy2：the y coordinate within the second cell。
	 * @param col1：the column (0 based) of the first cell。
	 * @param row1：the row (0 based) of the first cell。
	 * @param col2：the column (0 based) of the second cell。
	 * @param row2：the row (0 based) of the second cell。
	 * @throws IOException
	 */
	public void setBytesExcelModuleImage(String ImageFile,int dx1,int dy1,int dx2,int dy2,short col1,int row1,short col2, int row2) throws IOException {
		BufferedImage bufferImg = null;
		try {
			String sReportpath = loadConfigValue("com/config/report.cfg", "reportpath");
			String Imageflm = new StringBuilder(sReportpath).append(System.getProperties().getProperty("file.separator")).append(ImageFile).toString();
			ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
			FileInputStream fileIn = new FileInputStream(Imageflm);
			bufferImg = ImageIO.read(fileIn);     
			ImageIO.write(bufferImg, "jpg", byteArrayOut);
			//画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
			XSSFSheet xssfsheet = ((XSSFWorkbook) Workbook).getSheetAt(0);
			XSSFDrawing patriarch = xssfsheet.createDrawingPatriarch();
			//XSSFDrawing patriarch = xssfsheet.createDrawingPatriarch();
			//anchor主要用于设置图片的属性  
			XSSFClientAnchor anchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2,(short) col1, row1, (short) col2, row2);
			//XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 255, 255,(short) 31, 33, (short) 37, 34);
			anchor.setAnchorType(3);
			//插入图片    
			patriarch.createPicture(anchor, ((XSSFWorkbook) Workbook).addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
			//xssfsheet.createDrawingPatriarch()
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public byte[] getCollectBytes(String[][] aValues, String startLoc,
			String[][] aSetAlignment) {
		return getCollectBytes(aValues, startLoc, aSetAlignment, 0, false);
	}

	public byte[] getCollectBytes(String[][] aValues, String startLoc,
			String[][] aSetAlignment, int footrowcount) {
		return getCollectBytes(aValues, startLoc, aSetAlignment, footrowcount,
				false);
	}

	public byte[] getCollectBytes(String[][] aValues, String startLoc,
			String[][] aSetAlignment, boolean half) {
		return getCollectBytes(aValues, startLoc, aSetAlignment, 0, false);
	}

	public byte[] getCardExcelBytes() throws Exception {
		byte[] is;
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		((XSSFWorkbook) Workbook).write(baos);
		is = baos.toByteArray();
		return is;
	}
	public byte[] getCollectBytes(String[][] aValues, String startLoc,String[][] aSetAlignment, int footRows,boolean wrapline) {
		do {
			byte[] is;
			try {
				if (!extension.equalsIgnoreCase("XLSX"))
					break;
				long startTime = System.currentTimeMillis();
				XSSFSheet xssfsheet = ((XSSFWorkbook) Workbook).getSheetAt(0);
				long endTime = System.currentTimeMillis();
				System.out.println(new StringBuilder("***1****:").append(endTime - startTime).toString());
				startTime = System.currentTimeMillis();
				String[] aStart = genMethod.split(startLoc, ";");
				String[] fStart = genMethod.split(aStart[0], ",");
				int iStartRow = Integer.parseInt(fStart[0]);
				int iStartCol = Integer.parseInt(fStart[1]);
				int loop = aValues.length;
				if (footRows > 0)
					xssfsheet.shiftRows(iStartRow, iStartRow + footRows, loop);
				resetXcellstyle();
				for (int i = 0; i < loop; i++) {
					XSSFRow row = xssfsheet.getRow(iStartRow + i);
					if (row == null)
						row = xssfsheet.createRow(iStartRow + i);
					int nloop = aValues[i].length;
					for (int j = 0; j < nloop; j++) {
						XSSFCell cell = row.getCell(iStartCol + j);
						if (cell == null)
							cell = row.createCell(iStartCol + j);
						if (isNumber(aValues[i][j]))
							cell.setCellValue(Double
									.parseDouble(aValues[i][j]));
						else
							cell.setCellValue(aValues[i][j]);
						cell.setCellStyle((XSSFCellStyle)
								xcellstyle.get("CENTER"));
						for (int s = 0; s < aSetAlignment.length; s++) {
							if (String.valueOf(j).equals(aSetAlignment[s][0]))
								cell.setCellStyle((XSSFCellStyle)
										xcellstyle.get(aSetAlignment
												[s][1]));
						}
					}
					if (!wrapline)
						row.setHeightInPoints(row.getHeightInPoints() + 12.0F);
				}
				endTime = System.currentTimeMillis();
				System.out.println(new StringBuilder("***2****:").append
						(endTime - startTime).toString());
				startTime = System.currentTimeMillis();
				if (isMergedRegion) {
					for (int c = 0; c < al.size(); c++) {
						int[] tmp = new int[4];
						tmp = (int[]) al.get(c);
						int startR = tmp[0];
						int endR = tmp[1];
						int startC = tmp[2];
						int endC = tmp[3];
						System.out.println(new StringBuilder("startR:").append
								(startR).append
								("\t").append
								("endR:").append
								(endR).append
								("\t").append
								("startC:").append
								(startC).append
								("\t").append
								("endC:").append
								(endC).toString());
						System.out.println(new StringBuilder("_______").append
								(startR + iStartRow).append
								("-").append
								(endR + iStartRow).append
								("-").append
								(startC).append
								("-").append
								(endC).toString());
						xssfsheet.addMergedRegion
						(new CellRangeAddress(startR + iStartRow,
								endR + iStartRow, startC,
								endC));
						XSSFRow xxxfr = xssfsheet.getRow(startR + iStartRow);
						if (xxxfr == null)
							System.out.println(" xxxfr is null ... ");
						else {
							XSSFCell xssfc = xxxfr.getCell(0);
							String[] Bold = getDataStyle().split(",");
							for (int i = 0; i < Bold.length; i++)
								xxxfr.getCell(Integer.parseInt(Bold[i]))
								.setCellStyle
								((XSSFCellStyle) xcellstyle.get("BOLD"));
							xssfc.setCellStyle((XSSFCellStyle)
									xcellstyle.get("BOLD"));
							if (xssfc == null)
								System.out.println(" xssfc is null ... ");
							else
								xssfc.setCellValue(" \u5c0f   \u8ba1  ");
						}
					}
				}
				ByteArrayOutputStream baos = new ByteArrayOutputStream();
				((XSSFWorkbook) Workbook).write(baos);
				is = baos.toByteArray();
			} catch (Exception e) {
				e.printStackTrace();
				break;
			}
			return is;
		} while (false);
		return null;
	}

	public XSSFFont getXSSFfont() {
		XSSFFont xssffont = ((XSSFWorkbook) Workbook).createFont();
		xssffont.setFontHeight(FontHeight);
		xssffont.setFontName("宋体");
		return xssffont;
	}

	public XSSFCellStyle createXSSFCellStyle() {
		XSSFCellStyle xscs = ((XSSFWorkbook) Workbook).createCellStyle();
		return xscs;
	}

	public void setBytesExcelModuleTitle() throws IOException {
		if (extension.equalsIgnoreCase("XLSX")) {
			XSSFSheet xssfsheet = ((XSSFWorkbook) Workbook).getSheetAt(0);
			Iterator rit = xssfsheet.rowIterator();
			while (rit.hasNext()) {
				XSSFRow row = (XSSFRow) rit.next();
				Vector datas = new Vector();
				Iterator cit = row.cellIterator();
				while (cit.hasNext()) {
					XSSFCell cell = (XSSFCell) cit.next();
					String ls = getValue2007(cell);
					String st = "";
					if (ls.indexOf("$") >= 0) {
						Enumeration ee = clusters.propertyNames();
						while (ee.hasMoreElements()) {
							String keyList = (String) ee.nextElement();
							if (ls.indexOf(keyList, 0) > -1) {
								Vector cluList
								= (Vector) clusters.get(keyList);
								int plp = cluList.size();
								String[] rplValue = new String[plp];
								for (int j1 = 0; j1 < plp; j1++) {
									String s = (String) cluList.elementAt(j1);
									rplValue[j1] = s;
									st = genMethod.replaceValueStr(ls, keyList,
											(rplValue
													[j1]));
								}
								cell.setCellType(1);
								cell.setCellValue(st);
							}
						}
					}
				}
			}
		} else if (extension.equalsIgnoreCase("XLS")) {
			org.apache.poi.hssf.usermodel.HSSFSheet hssfsheet= ((HSSFWorkbook) Workbook).getSheetAt(0);
		}
	}

	public String getValue2007(XSSFCell cell) {
		String value = "";
		switch (cell.getCellType()) {
		case 1:
			value = cell.getRichStringCellValue().getString();
			break;
		case 0:
			if (DateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
				value = format.format(date);
			} else
				value = String.valueOf(cell.getNumericCellValue());
			break;
		case 4:
			value = new StringBuilder(" ").append
			(cell.getBooleanCellValue()).toString();
			break;
		case 2:
			value = cell.getCellFormula();
			break;
		}
		return value;
	}
	public void setBytesExcelModule(String ExcelModule) {
		try {
			String sReportpath = loadConfigValue("com/config/report.cfg", "reportpath");
			String modflm
			= new StringBuilder(sReportpath).append
			(System.getProperties().getProperty("file.separator"))
			.append
			(ExcelModule).toString();
			String dot = ".";
			int position = modflm.lastIndexOf(dot);
			extension = modflm.substring(position + 1);
			if (extension.equalsIgnoreCase("xlsx")) {
				FileInputStream fileIn = new FileInputStream(modflm);
				XSSFWorkbook wb = new XSSFWorkbook(fileIn);
				fileIn.close();
				Workbook = wb;
			} else if (extension.equalsIgnoreCase("xls")) {
				FileInputStream fileIn = new FileInputStream(modflm);
				POIFSFileSystem fs = new POIFSFileSystem(fileIn);
				fileIn.close();
				HSSFWorkbook wb = new HSSFWorkbook(fs);
				Workbook = wb;
			} else
				System.out.println("不支持的模板文件 ,仅支持excel文件");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static boolean isNumber(String str) {
		if (str == null || str.equals(""))
			return false;
		str = str.replace('.', '1').replace('-', '1');
		int i = str.length();
		while (--i >= 0) {
			if (!Character.isDigit(str.charAt(i)))
				return false;
		}
		return true;
	}

	public Object getWorkbook() {
		return Workbook;
	}

	public void setWorkbook(Object workbook) {
		Workbook = workbook;
	}

	public String getExtension() {
		return extension;
	}

	public void setExtension(String extension) {
		this.extension = extension;
	}

	public Properties getClusters() {
		return clusters;
	}

	public void setClusters(Properties clusters) {
		this.clusters = clusters;
	}

	public String getDataStyle() {
		return dataStyle;
	}

	public void setDataStyle(String dataStyle) {
		this.dataStyle = dataStyle;
	}

	public ArrayList getAl() {
		return al;
	}

	public void setAl(ArrayList al) {
		this.al = al;
	}

	public boolean isMergedRegion() {
		return isMergedRegion;
	}

	public void setMergedRegion(boolean isMergedRegion) {
		this.isMergedRegion = isMergedRegion;
	}

	public void resetXcellstyle() {
		XSSFCellStyle xssfstyle = createXSSFCellStyle();
		XSSFFont xsxf = getXSSFfont();
		xssfstyle.setFont(xsxf);
		xssfstyle.setWrapText(true);
		xssfstyle.setVerticalAlignment((short) 1);
		xssfstyle.setAlignment((short) 1);
		xssfstyle.setBorderBottom((short) 1);
		xssfstyle.setBorderTop((short) 1);
		xssfstyle.setBorderLeft((short) 1);
		xssfstyle.setBorderRight((short) 1);
		xcellstyle.put("LEFT", xssfstyle);
		XSSFCellStyle xssfstyle1 = createXSSFCellStyle();
		xssfstyle1.setFont(xsxf);
		xssfstyle1.setWrapText(true);
		xssfstyle1.setVerticalAlignment((short) 1);
		xssfstyle1.setAlignment((short) 2);
		xssfstyle1.setBorderBottom((short) 1);
		xssfstyle1.setBorderTop((short) 1);
		xssfstyle1.setBorderLeft((short) 1);
		xssfstyle1.setBorderRight((short) 1);
		xcellstyle.put("CENTER", xssfstyle1);
		XSSFCellStyle xssfstyle2 = createXSSFCellStyle();
		xssfstyle2.setFont(xsxf);
		xssfstyle2.setWrapText(true);
		xssfstyle2.setVerticalAlignment((short) 1);
		xssfstyle2.setAlignment((short) 3);
		xssfstyle2.setBorderBottom((short) 1);
		xssfstyle2.setBorderTop((short) 1);
		xssfstyle2.setBorderLeft((short) 1);
		xssfstyle2.setBorderRight((short) 1);
		xcellstyle.put("RIGHT", xssfstyle2);
		XSSFCellStyle xssfstyle3 = createXSSFCellStyle();
		XSSFFont xsxf1 = getXSSFfont();
		xsxf1.setBold(true);
		xssfstyle3.setFont(xsxf1);
		xssfstyle3.setWrapText(true);
		xssfstyle3.setVerticalAlignment((short) 1);
		xssfstyle3.setAlignment((short) 2);
		xssfstyle3.setBorderBottom((short) 1);
		xssfstyle3.setBorderTop((short) 1);
		xssfstyle3.setBorderLeft((short) 1);
		xssfstyle3.setBorderRight((short) 1);
		xcellstyle.put("BOLD", xssfstyle3);
	}
	///   <summary> 
	///   将流保存为文件 
	///   </summary> 
	///   <returns> </returns> 
	private static boolean SaveStreamToFile(byte[] excelModuleBytes,String path) 
	{ 
		try {
			FileOutputStream fos = new FileOutputStream(path);
			fos.write(excelModuleBytes);
			fos.close();
		}catch (Exception ex) {
			ex.printStackTrace();
		}
		return true; 
	}
}