package com.zhiwei.util;

import java.io.File;//引入类
import java.net.URL;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import com.zw.entity.NewsResult;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import weixin.popular.bean.menu.selfmenu.News;

/**
 * excel操作类
 * 
 * @author admin
 *
 */
@Component
public class ExcelUtil {
	private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

	/**
	 * 删除文件
	 * 
	 * @param url
	 *            文件的路径
	 */
	public static void delete(String url) {
		File file = new File(url);
		if (file.isFile()) {
			file.delete();
		}
		log.info("删除完成");
	}

	/**
	 * 日匹配所有新闻数据写入excel
	 * 
	 * @param list
	 * @param time
	 * @param fileName
	 * @throws Exception
	 */
	public void pushDataWithFinance(List<NewsResult> list, Date time, String fileName) throws Exception {
		File tempFile = new File(fileName);
		// 若是文件不存在就创建新文件
		if (!tempFile.exists()) {
			tempFile.createNewFile();
		}
		WritableWorkbook workbook = Workbook.createWorkbook(tempFile);
		WritableSheet sheet = workbook.createSheet("table1", 0);
		// 一些临时变量，用于写到excel中
		Label l = null;
		// 预定义的一些字体和格式，格式： 字形 大小 加粗 倾斜 下划线 颜色
		WritableFont font1 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false,
				UnderlineStyle.NO_UNDERLINE, Colour.WHITE);
		WritableFont font2 = new WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, false,
				UnderlineStyle.NO_UNDERLINE, Colour.WHITE);
		WritableCellFormat MainTitle = new WritableCellFormat(font2);
		MainTitle.setAlignment(Alignment.CENTRE);
		MainTitle.setBackground(Colour.BLUE_GREY);
		MainTitle.setVerticalAlignment(VerticalAlignment.CENTRE);
		// 财新数据标题格式
		WritableCellFormat mainTitle = new WritableCellFormat(font1);
		mainTitle.setAlignment(Alignment.CENTRE);
		mainTitle.setBackground(Colour.BLUE_GREY);
		// 标题格式
		WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false,
				UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
		WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
		titleFormat.setAlignment(Alignment.CENTRE);
		// 正文字体
		WritableFont detFont = new WritableFont(WritableFont.ARIAL, 13, WritableFont.NO_BOLD, false,
				UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
		WritableCellFormat detFormat = new WritableCellFormat(detFont);
		detFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		WritableFont urlf = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false,
				UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
		WritableCellFormat urlFormat = new WritableCellFormat(urlf);
		urlFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		int seq = 0;
		seq = 1;
		int row = seq++;
		l = new Label(0, row, "序号", titleFormat);
		sheet.addCell(l);
		l = new Label(1, row, "标题", titleFormat);
		sheet.addCell(l);
		l = new Label(2, row, "时间", titleFormat);
		sheet.addCell(l);
		l = new Label(3, row, "来源", titleFormat);
		sheet.addCell(l);
		int num = 1;

		for (NewsResult news : list) {
			row = seq++;
			l = new Label(0, row, String.valueOf(num++), detFormat);// 序号
			sheet.addCell(l);
			try {
				WritableHyperlink link = new WritableHyperlink(1, row, 1, row, new URL(news.getUrl()), news.getTitle());
				sheet.addHyperlink(link);
			} catch (Exception e) {
				System.out.println(news);
				log.error("获取url失败，错误信息{}", e);
			}
			l = new Label(1, row, news.getTitle(), urlFormat);// 标题
			sheet.addCell(l);
			l = new Label(2, row, news.getTime(), detFormat);// 时间
			sheet.addCell(l);
			l = new Label(3, row, news.getSource(), detFormat);// 时间
			sheet.addCell(l);
		}
		int column = 0;
		sheet.setColumnView(column++, 10);
		sheet.setColumnView(column++, 25);
		sheet.setColumnView(column++, 20);
		sheet.setColumnView(column++, 22);
		sheet.setColumnView(column++, 22);
		workbook.write();
		workbook.close();
		log.info("内容写入成功");
	}

	public void pushDataWithFinanceLong(List<NewsResult> list, List<NewsResult> wechat, List<NewsResult> weibo,
			Date time, String fileName) throws Exception {
		File tempFile = new File(fileName);
		// 若是文件不存在就创建新文件
		if (!tempFile.exists()) {
			tempFile.createNewFile();
		}
		WritableWorkbook workbook = Workbook.createWorkbook(tempFile);
		WritableSheet sheet = workbook.createSheet("table1", 0);
		// 一些临时变量，用于写到excel中
		Label l = null;
		// 预定义的一些字体和格式，格式： 字形 大小 加粗 倾斜 下划线 颜色
		WritableFont font1 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false,
				UnderlineStyle.NO_UNDERLINE, Colour.WHITE);
		WritableFont font2 = new WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, false,
				UnderlineStyle.NO_UNDERLINE, Colour.WHITE);
		WritableCellFormat MainTitle = new WritableCellFormat(font2);
		MainTitle.setAlignment(Alignment.CENTRE);
		MainTitle.setBackground(Colour.BLUE_GREY);
		MainTitle.setVerticalAlignment(VerticalAlignment.CENTRE);
		// 财新数据标题格式
		WritableCellFormat mainTitle = new WritableCellFormat(font1);
		mainTitle.setAlignment(Alignment.CENTRE);
		mainTitle.setBackground(Colour.BLUE_GREY);
		// 标题格式
		WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false,
				UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
		WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
		titleFormat.setAlignment(Alignment.CENTRE);
		// 正文字体
		WritableFont detFont = new WritableFont(WritableFont.ARIAL, 13, WritableFont.NO_BOLD, false,
				UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
		WritableCellFormat detFormat = new WritableCellFormat(detFont);
		detFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		WritableFont urlf = new WritableFont(WritableFont.ARIAL, 11, WritableFont.NO_BOLD, false,
				UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
		WritableCellFormat urlFormat = new WritableCellFormat(urlf);
		urlFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		int seq = 0;
		seq = 0;
		seq++;
		int row = seq++;
		l = new Label(0, row, "序号", titleFormat);
		sheet.addCell(l);
		l = new Label(1, row, "标题", titleFormat);
		sheet.addCell(l);
		l = new Label(2, row, "时间", titleFormat);
		sheet.addCell(l);
		l = new Label(3, row, "来源", titleFormat);
		sheet.addCell(l);
		int num = 1;
		for (NewsResult news : list) {
			row = seq++;
			l = new Label(0, row, String.valueOf(num++), detFormat);// 序号
			sheet.addCell(l);
			try {
				WritableHyperlink link = new WritableHyperlink(1, row, 1, row, new URL(news.getUrl()), news.getTitle());
				sheet.addHyperlink(link);
			} catch (Exception e) {
				log.error("获取url失败，错误信息{}", e);
			}
			l = new Label(1, row, news.getTitle(), urlFormat);// 标题
			sheet.addCell(l);
			l = new Label(2, row, news.getTime(), detFormat);// 时间
			sheet.addCell(l);
			l = new Label(3, row, news.getSource(), detFormat);// 时间
			sheet.addCell(l);
		}
		int column = 0;
		sheet.setColumnView(column++, 10);
		sheet.setColumnView(column++, 25);
		sheet.setColumnView(column++, 20);
		sheet.setColumnView(column++, 22);
		sheet.setColumnView(column++, 22);

		WritableSheet sheet2 = workbook.createSheet("wechat", 1);
		seq = 0;
		sheet2.mergeCells(0, seq, 7, seq + 1);
		l = new Label(0, seq, "", mainTitle);
		sheet2.addCell(l);
		seq += 2;
		row = seq++;
		l = new Label(0, row, "序号", titleFormat);
		sheet2.addCell(l);
		l = new Label(1, row, "标题", titleFormat);
		sheet2.addCell(l);
		l = new Label(2, row, "时间", titleFormat);
		sheet2.addCell(l);
		l = new Label(3, row, "来源", titleFormat);
		sheet2.addCell(l);
		num = 1;
		for (NewsResult news : wechat) {
			row = seq++;
			l = new Label(0, row, String.valueOf(num++), detFormat);// 序号
			sheet2.addCell(l);
			try {
				WritableHyperlink link = new WritableHyperlink(1, row, 1, row, new URL(news.getUrl()), news.getTitle());
				sheet2.addHyperlink(link);
			} catch (Exception e) {
				System.out.println(news);
				log.error("获取url失败，错误信息{}", e);
			}
			l = new Label(1, row, news.getTitle(), urlFormat);// 标题
			sheet2.addCell(l);
			l = new Label(2, row, news.getTime(), detFormat);// 时间
			sheet2.addCell(l);
			l = new Label(3, row, news.getSource(), detFormat);// 时间
			sheet2.addCell(l);
		}
		column = 0;
		sheet2.setColumnView(column++, 10);
		sheet2.setColumnView(column++, 25);
		sheet2.setColumnView(column++, 20);
		sheet2.setColumnView(column++, 22);
		sheet2.setColumnView(column++, 22);
		WritableSheet sheet3 = workbook.createSheet("weibo", 2);
		seq = 0;
		sheet3.mergeCells(0, seq, 7, seq + 1);
		l = new Label(0, seq, "", mainTitle);
		sheet3.addCell(l);
		seq += 2;
		row = seq++;
		l = new Label(0, row, "序号", titleFormat);
		sheet3.addCell(l);
		l = new Label(1, row, "标题", titleFormat);
		sheet3.addCell(l);
		l = new Label(2, row, "时间", titleFormat);
		sheet3.addCell(l);
		l = new Label(3, row, "来源", titleFormat);
		sheet3.addCell(l);
		num = 1;
		for (NewsResult news : weibo) {
			row = seq++;
			l = new Label(0, row, String.valueOf(num++), detFormat);// 序号
			sheet3.addCell(l);
			try {
				WritableHyperlink link = new WritableHyperlink(1, row, 1, row, new URL(news.getUrl()), news.getTitle());
				sheet3.addHyperlink(link);
			} catch (Exception e) {
				System.out.println(news);
				log.error("获取url失败，错误信息{}", e);
			}
			l = new Label(1, row, news.getTitle(), urlFormat);// 标题
			sheet3.addCell(l);
			l = new Label(2, row, news.getTime(), detFormat);// 时间
			sheet3.addCell(l);
			l = new Label(3, row, news.getSource(), detFormat);// 时间
			sheet3.addCell(l);
		}
		column = 0;
		sheet3.setColumnView(column++, 10);
		sheet3.setColumnView(column++, 25);
		sheet3.setColumnView(column++, 20);
		sheet3.setColumnView(column++, 22);
		sheet3.setColumnView(column++, 22);
		workbook.write();
		workbook.close();
		log.info("内容写入成功");
	}
}
