/**
 * Copyright (c) 2013-Now http://jeesite.com All rights reserved.
 */
package com.ruoyi.common.core.excel;

import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.Calendar;
import java.util.Enumeration;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;

/**
 * Excel超大数据写入，抽象excel2007读入器，先构建.xlsx一张模板，改写模板中的sheet.xml,
 * 使用这种方法 写入.xlsx文件，不需要太大的内存
 * @version 2014-9-2
 */
public abstract class ExcelWriter {

	private SpreadsheetWriter sw;

	/**
	 * 写入电子表格的主要流程
	 * 
	 * @param fileName
	 * @throws Exception
	 */
	@SuppressWarnings("resource")
	public void process(String fileName) throws Exception {
		
		// 建立工作簿和电子表格对象
		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sheet = wb.createSheet("sheet1");
		
		// 持有电子表格数据的xml文件名 例如 /xl/worksheets/sheet1.xml
		String sheetRef = sheet.getPackagePart().getPartName().getName();

		// 保存模板
		FileOutputStream os = new FileOutputStream("template.xlsx");
		wb.write(os);
		os.close();

		// 生成xml文件
		File tmp = File.createTempFile("sheet", ".xml");
		Writer fw = new FileWriter(tmp);
		sw = new SpreadsheetWriter(fw);
		generate();
		fw.close();

		// 使用产生的数据替换模板
		File templateFile = new File("template.xlsx");
		FileOutputStream out = new FileOutputStream(fileName);
		substitute(templateFile, tmp, sheetRef.substring(1), out);
		out.close();
		// 删除文件之前调用一下垃圾回收器，否则无法删除模板文件
		System.gc();
		// 删除临时模板文件
		if (templateFile.isFile() && templateFile.exists()) {
			templateFile.delete();
		}
	}

	/**
	 * 类使用者应该使用此方法进行写操作
	 * 
	 * @throws Exception
	 */
	public abstract void generate() throws Exception;

	public void beginSheet() throws IOException {
		sw.beginSheet();
	}

	public void insertRow(int rowNum) throws IOException {
		sw.insertRow(rowNum);
	}

	public void createCell(int columnIndex, String value) throws IOException {
		sw.createCell(columnIndex, value, -1);
	}

	public void createCell(int columnIndex, double value) throws IOException {
		sw.createCell(columnIndex, value, -1);
	}

	public void endRow() throws IOException {
		sw.endRow();
	}

	public void endSheet() throws IOException {
		sw.endSheet();
	}

	/**
	 * 
	 * @param zipfile the template file
	 * @param tmpfile the XML file with the sheet data
	 * @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
	 * @param out the stream to write the result to
	 */
	private static void substitute(File zipfile, File tmpfile, String entry,
			OutputStream out) throws IOException {
	    	try (
				ZipFile zip = new ZipFile(zipfile);
				ZipOutputStream zos = new ZipOutputStream(out);
				InputStream is = new FileInputStream(tmpfile);
			){
			@SuppressWarnings("unchecked")
			Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) zip.entries();
			while (en.hasMoreElements()) {
				ZipEntry ze = en.nextElement();
				if (!ze.getName().equals(entry)) {
					zos.putNextEntry(new ZipEntry(ze.getName()));
					try (InputStream is2=zip.getInputStream(ze)){
						copyStream(is2, zos);
					}
				}
			}
			zos.putNextEntry(new ZipEntry(entry));
			copyStream(is, zos);
		}
	}

	private static void copyStream(InputStream in, OutputStream out)
			throws IOException {
		byte[] chunk = new byte[1024];
		int count;
		while ((count = in.read(chunk)) >= 0) {
			out.write(chunk, 0, count);
		}
	}

	/**
	 * 在写入器中写入电子表格
	 * 
	 */
	public static class SpreadsheetWriter {
		private final Writer _out;
		private int _rownum;
		private static String LINE_SEPARATOR = System
				.getProperty("line.separator");

		public SpreadsheetWriter(Writer out) {
			_out = out;
		}

		public void beginSheet() throws IOException {
			_out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>"
					+ "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">");
			_out.write("<sheetData>" + LINE_SEPARATOR);
		}

		public void endSheet() throws IOException {
			_out.write("</sheetData>");
			_out.write("</worksheet>");
		}

		/**
		 * 插入新行
		 * 
		 * @param rownum
		 *            以0开始
		 */
		public void insertRow(int rownum) throws IOException {
			_out.write("<row r=\"" + (rownum + 1) + "\">" + LINE_SEPARATOR);
			this._rownum = rownum;
		}

		/**
		 * 插入行结束标志
		 */
		public void endRow() throws IOException {
			_out.write("</row>" + LINE_SEPARATOR);
		}

		/**
		 * 插入新列
		 * 
		 * @param columnIndex
		 * @param value
		 * @param styleIndex
		 * @throws IOException
		 */
		public void createCell(int columnIndex, String value, int styleIndex)
				throws IOException {
			String ref = new CellReference(_rownum, columnIndex)
					.formatAsString();
			_out.write("<c r=\"" + ref + "\" t=\"inlineStr\"");
			if (styleIndex != -1) {
				_out.write(" s=\"" + styleIndex + "\"");
			}
			_out.write(">");
			_out.write("<is><t>" + encoderXML(value) + "</t></is>");
			_out.write("</c>");
		}

		public void createCell(int columnIndex, String value)
				throws IOException {
			createCell(columnIndex, value, -1);
		}

		public void createCell(int columnIndex, double value, int styleIndex)
				throws IOException {
			String ref = new CellReference(_rownum, columnIndex)
					.formatAsString();
			_out.write("<c r=\"" + ref + "\" t=\"n\"");
			if (styleIndex != -1) {
				_out.write(" s=\"" + styleIndex + "\"");
			}
			_out.write(">");
			_out.write("<v>" + value + "</v>");
			_out.write("</c>");
		}

		public void createCell(int columnIndex, double value)
				throws IOException {
			createCell(columnIndex, value, -1);
		}

		public void createCell(int columnIndex, Calendar value, int styleIndex)
				throws IOException {
			createCell(columnIndex, DateUtil.getExcelDate(value, false),
					styleIndex);
		}
	}

	// XML Encode
	private static final String[] xmlCode = new String[256];

	static {
		// Special characters
		xmlCode['\''] = "'";
		xmlCode['\"'] = "\""; // double quote
		xmlCode['&'] = "&"; // ampersand
		xmlCode['<'] = "<"; // lower than
		xmlCode['>'] = ">"; // greater than
	}

	/**
	 * <p>
	 * Encode the given text into xml.
	 * </p>
	 * 
	 * @param string the text to encode
	 * @return the encoded string
	 */
	public static String encoderXML(String string) {
		if (string == null) {
			return "";
		}
		int n = string.length();
		char character;
		String xmlchar;
		StringBuffer buffer = new StringBuffer();
		// loop over all the characters of the String.
		for (int i = 0; i < n; i++) {
			character = string.charAt(i);
			// the xmlcode of these characters are added to a StringBuffer
			// one by one
			try {
				xmlchar = xmlCode[character];
				if (xmlchar == null) {
					buffer.append(character);
				} else {
					buffer.append(xmlCode[character]);
				}
			} catch (ArrayIndexOutOfBoundsException aioobe) {
				buffer.append(character);
			}
		}
		return buffer.toString();
	}

//	/**
//	 * 测试方法
//	 */
//	public static void main(String[] args) throws Exception {
//
//		String file = "E:/测试导出数据.xlsx";
//		
//		ExcelWriter writer = new ExcelWriter() {
//			@Override
//			public void generate() throws Exception {
//				
//				// 电子表格开始
//				this.beginSheet();
//				
//				for (int rownum = 0; rownum < 100; rownum++) {
//					// 插入新行
//					this.insertRow(rownum);
//					
//					// 建立新单元格,索引值从0开始,表示第一列
//					this.createCell(0, "第 " + rownum + " 行");
//					this.createCell(1, 34343.123456789);
//					this.createCell(2, "23.67%");
//					this.createCell(3, "12:12:23");
//					this.createCell(4, "2014-10-11 12:12:23");
//					this.createCell(5, "true");
//					this.createCell(6, "false");
//
//					// 结束行
//					this.endRow();
//				}
//				
//				// 电子表格结束
//				this.endSheet();
//			}
//		};
//		writer.process(file);
//	}
		
}