package com.zhiwei.chendong;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.zw.entity.Weixin;

public class ReaderExcel {
	private final static Log log = LogFactory.getLog(ReaderExcel.class);

	public static void main(String[] args) throws IOException {
		for (String w : getZhUrls("C:\\Users\\Administrator\\Desktop/新闻源栏目.xlsx")) {
			Matcher m=Pattern.compile("[\\S]+").matcher(w);
			if(m.find()) {
				System.out.println(m.group().replace("java.util.regex.Matcher[pattern=[\\S]+ region=0,3 lastmatch=", ""));
			}
		}
	}

	public static List<Weixin> getExcel(String url) throws IOException {
		// String url="C:/Users/Administrator/Desktop/jd.xls";
		List<Weixin> list = new ArrayList<>();
		Workbook book = null;
		book = getExcelWorkbook(url);
		Sheet sheet = getSheetByNum(book, 0);
		int lastRowNum = sheet.getLastRowNum();
		log.info("last number is " + lastRowNum);
		for (int i = 1; i <= lastRowNum; i++) {
			Row row = null;
			row = sheet.getRow(i);
			if (row != null) {
				int lastCellNum = row.getLastCellNum();
				Cell cell = null;
				Weixin w = new Weixin();
				for (int j = 0; j < lastCellNum; j++) {
					cell = row.getCell(j);
					if (cell != null) {
						cell.setCellType(Cell.CELL_TYPE_STRING);
						String str = cell.getStringCellValue();
						if (str != null) {
							if (sheet.getRow(0).getCell(j) == null) {
								continue;
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("来源")) {
								w.setSource(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("时间")) {
								w.setTime(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("标题")) {
								w.setTitle(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("地址")) {
								w.setUrl(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("内容")) {
								w.setConetent(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("关键词")) {
								w.setKeyValue(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("阅读数")) {
								w.setRead(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("点赞数")) {
								w.setZan(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("用户昵称")) {
								w.setNickname(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("简介")) {
								w.setJianjie(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("历史标注情况")) {
								w.setHistory(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("长地址")) {
								w.setLongUrl(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("mark")) {
								w.setMark(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("mark_time")) {
								w.setMark_time(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("markname")) {
								w.setMarkname(str == null ? "null" : str);
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("真实阅读数")) {
								w.setTrueRead(str == null ? "null" : str);
							}
						}
					}
				}
				if (w != null)
					list.add(w);
			}
		}
		return list;
	}

	public static List<String> getZhUrls(String url) throws IOException {
		List<String> list = new ArrayList<>();
		Workbook book = null;
		book = getExcelWorkbook(url);
		Sheet sheet = getSheetByNum(book, 0);
		int lastRowNum = sheet.getLastRowNum();
		log.info("last number is " + lastRowNum);
		for (int i = 1; i <= lastRowNum; i++) {
			Row row = null;
			row = sheet.getRow(i);
			if (row != null) {
				int lastCellNum = row.getLastCellNum();
				Cell cell = null;
				for (int j = 0; j < lastCellNum; j++) {
					cell = row.getCell(j);
					if (cell != null) {
						cell.setCellType(Cell.CELL_TYPE_STRING);
						String str = cell.getStringCellValue();
						if (str != null) {
							if (sheet.getRow(0).getCell(j) == null) {
								continue;
							}
							if (sheet.getRow(0).getCell(j).getStringCellValue().equals("链接")) {
								list.add(str);
							}
						}
					}
				}
			}
		}
		return list;
	}
	public static Sheet getSheetByNum(Workbook book, int number) {
		Sheet sheet = null;
		try {
			sheet = book.getSheetAt(number);
			// if(sheet == null){
			// sheet = book.createSheet("Sheet"+number);
			// }
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		}
		return sheet;
	}

	public static Workbook getExcelWorkbook(String filePath) throws IOException {
		Workbook book = null;
		File file = null;
		FileInputStream fis = null;
		try {
			file = new File(filePath);
			if (!file.exists()) {
				throw new RuntimeException("文件不存在");
			} else {
				fis = new FileInputStream(file);
				book = WorkbookFactory.create(fis);
			}
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage());
		} finally {
			if (fis != null) {
				fis.close();
			}
		}
		return book;
	}
}
