package com.jsh.erp.utils;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

import jxl.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.StringUtils;
import jxl.format.*;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import javax.servlet.http.HttpServletResponse;

@Slf4j
public class ExcelUtils {

	public static InputStream getPathByFileName(String template, String tmpFileName) {
		File tmpFile = new File(template, tmpFileName);
		InputStream path = null;
		//判断文件或文件夹是否存在
		if (tmpFile.exists()) {
			try {
				path = new FileInputStream(tmpFile);
			} catch (FileNotFoundException e) {
				log.error("", e);
			}
		}
		return path;
	}

	/**
	 * 导出excel，带多sheet
	 *
	 * @param wtwb
	 * @param tip
	 * @param names
	 * @param title
	 * @param index
	 * @param objects
	 * @return
	 * @throws Exception
	 */
	public static void exportObjectsManySheet(WritableWorkbook wtwb, String tip,
											  String[] names, String title, int index, List<String[]> objects) throws Exception {
		WritableSheet sheet = wtwb.createSheet(title, index);
		sheet.getSettings().setDefaultColumnWidth(12);

		// 标题的格式-红色
		WritableFont redWF = new WritableFont(WritableFont.ARIAL, 12,
				WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
				Colour.RED);
		WritableCellFormat redWFFC = new WritableCellFormat(redWF);
		redWFFC.setVerticalAlignment(VerticalAlignment.CENTRE);
		redWFFC.setBorder(Border.ALL, BorderLineStyle.THIN);

		// 标题的格式-黑色
		WritableFont blackWF = new WritableFont(WritableFont.ARIAL, 12,
				WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
				Colour.BLACK);
		WritableCellFormat blackWFFC = new WritableCellFormat(blackWF);
		blackWFFC.setVerticalAlignment(VerticalAlignment.CENTRE);
		blackWFFC.setBorder(Border.ALL, BorderLineStyle.THIN);

		// 设置字体以及单元格格式
		WritableFont wfont = new WritableFont(WritableFont.createFont("楷书"), 12);
		WritableCellFormat format = new WritableCellFormat(wfont);
		format.setAlignment(Alignment.LEFT);
		format.setVerticalAlignment(VerticalAlignment.TOP);
		format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

		// 第一行写入提示
		if(com.jsh.erp.utils.StringUtil.isNotEmpty(tip) && tip.contains("*")) {
			sheet.addCell(new Label(0, 0, tip, redWFFC));
		} else {
			sheet.addCell(new Label(0, 0, tip, blackWFFC));
		}

		// 第二行写入标题
		for (int i = 0; i < names.length; i++) {
			if(StringUtil.isNotEmpty(names[i]) && names[i].contains("*")) {
				sheet.addCell(new Label(i, 1, names[i], redWFFC));
			} else {
				sheet.addCell(new Label(i, 1, names[i], blackWFFC));
			}
		}

		// 其余行依次写入数据
		int rowNum = 2;
		for (int j = 0; j < objects.size(); j++) {
			String[] obj = objects.get(j);
			for (int h = 0; h < obj.length; h++) {
				sheet.addCell(new Label(h, rowNum, obj[h], format));
			}
			rowNum = rowNum + 1;
		}
	}

	/**
	 * 导出excel，带单个sheet
	 *
	 * @param fileName
	 * @param names
	 * @param title
	 * @param objects
	 * @return
	 * @throws Exception
	 */
	public static File exportObjectsOneSheet(String fileName, String tip,
											 String[] names, String title, List<String[]> objects) throws Exception {
		File excelFile = new File("/opt/"+ fileName);
		WritableWorkbook wtwb = Workbook.createWorkbook(excelFile);
		WritableSheet sheet = wtwb.createSheet(title, 0);
		sheet.getSettings().setDefaultColumnWidth(12);

		// 标题的格式-红色
		WritableFont redWF = new WritableFont(WritableFont.ARIAL, 12,
				WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
		WritableCellFormat redWFFC = new WritableCellFormat(redWF);
		redWFFC.setVerticalAlignment(VerticalAlignment.CENTRE);
		redWFFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

		// 标题的格式-黑色
		WritableFont blackWF = new WritableFont(WritableFont.ARIAL, 12,
				WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
		WritableCellFormat blackWFFC = new WritableCellFormat(blackWF);
		blackWFFC.setVerticalAlignment(VerticalAlignment.CENTRE);
		blackWFFC.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

		// 设置字体以及单元格格式
		WritableFont wfont = new WritableFont(WritableFont.createFont("楷书"), 12);
		WritableCellFormat format = new WritableCellFormat(wfont);
		format.setAlignment(Alignment.LEFT);
		format.setVerticalAlignment(VerticalAlignment.TOP);
		format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

		// 第一行写入提示
		if(StringUtil.isNotEmpty(tip) && tip.contains("*")) {
			sheet.addCell(new Label(0, 0, tip, redWFFC));
		} else {
			sheet.addCell(new Label(0, 0, tip, blackWFFC));
		}

		// 第二行写入标题
		for (int i = 0; i < names.length; i++) {
			if(StringUtil.isNotEmpty(names[i]) && names[i].contains("*")) {
				sheet.addCell(new Label(i, 1, names[i], redWFFC));
			} else {
				sheet.addCell(new Label(i, 1, names[i], blackWFFC));
			}
		}

		// 其余行依次写入数据
		int rowNum = 2;
		for (int j = 0; j < objects.size(); j++) {
			String[] obj = objects.get(j);
			for (int h = 0; h < obj.length; h++) {
				sheet.addCell(new Label(h, rowNum, obj[h], format));
			}
			rowNum = rowNum + 1;
		}
		wtwb.write();
		wtwb.close();
		return excelFile;
	}

	public static String getContent(Sheet src, int rowNum, int colNum) {
		if(colNum < src.getRow(rowNum).length) {
			return src.getRow(rowNum)[colNum].getContents().trim();
		} else {
			return null;
		}
	}

	/**
	 * 获取真实的行数，剔除掉空白行
	 * @param src
	 * @return
	 */
	public static int getRightRows(Sheet src) {
		int rsRows = src.getRows(); //行数
		int rsCols = src.getColumns(); //列数
		int nullCellNum;
		int rightRows = rsRows;
		for (int i = 1; i < rsRows; i++) { //统计行中为空的单元格数
			nullCellNum = 0;
			for (int j = 0; j < rsCols; j++) {
				String val = src.getCell(j, i).getContents().trim();
				if (StringUtils.isEmpty(val)) {
					nullCellNum++;
				}
			}
			if (nullCellNum >= rsCols) { //如果nullCellNum大于或等于总的列数
				rightRows--; //行数减一
			}
		}
		return rightRows;
	}

	public static void downloadExcel(File excelFile, String fileName, HttpServletResponse response) throws Exception{
		response.setContentType("application/octet-stream");
		fileName = new String(fileName.getBytes("gbk"),"ISO8859_1");
		response.setHeader("Content-Disposition", "attachment;filename=\"" + fileName + ".xls" + "\"");
		FileInputStream fis = new FileInputStream(excelFile);
		OutputStream out = response.getOutputStream();

		int SIZE = 1024 * 1024;
		byte[] bytes = new byte[SIZE];
		int LENGTH = -1;
		while((LENGTH = fis.read(bytes)) != -1){
			out.write(bytes,0,LENGTH);
		}
		out.flush();
		fis.close();
	}
}