工具类
package com.example.demo.util; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; public class ReadExcel { public static List<List<String>> readExcel(String path) throws IOException { if (path.endsWith(".xlsx")) { return readXlsx(path); } else if (path.endsWith(".xls")) { return readXlsx(path); } else { return null; } } /** * Read the Excel 2010 */ public static List<List<String>> readXlsx(String path) throws IOException { InputStream is = new FileInputStream(path); XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); List<List<String>> list = new ArrayList<List<String>>(); // Read the Sheet int numSheets = xssfWorkbook.getNumberOfSheets();// 获取sheet页数 for (int numSheet = 0; numSheet < 1; numSheet++) { XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet); if (xssfSheet == null) { continue; } // Read the Row int rowNums = xssfSheet.getLastRowNum(); for (int rowNum = 0; rowNum <= rowNums; rowNum++) { List<String> l = new ArrayList<String>(); // 获取对应的行数据 XSSFRow xssfRow = xssfSheet.getRow(rowNum); if (xssfRow != null) { // 获取列数 int columnNum = xssfRow.getLastCellNum(); for (int cloNum = 0; cloNum < columnNum; cloNum++) { l.add(getValue(xssfRow.getCell(cloNum))); } list.add(l); } } } return list; } /** * Read the Excel 2003 */ public void readXls(String path) throws IOException { InputStream is = new FileInputStream(path); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); List<List<String>> list = new ArrayList<List<String>>(); // Read the Sheet int numSheets = hssfWorkbook.getNumberOfSheets();// 获取sheet页数 for (int numSheet = 0; numSheet < 1; numSheet++) { HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // Read the Row int rowNums = hssfSheet.getLastRowNum(); for (int rowNum = 0; rowNum <= rowNums; rowNum++) { // 获取对应的行数据 HSSFRow hssfRow = hssfSheet.getRow(rowNum); List<String> l = new ArrayList<String>(); if (hssfRow != null) { // 获取列数 int columnNum = hssfRow.getLastCellNum(); for (int cloNum = 0; cloNum < columnNum; cloNum++) { l.add(getValue(hssfRow.getCell(cloNum))); } list.add(l); } } } } /** * 取值2010excel * * @param cell * @return */ private static String getValue(XSSFCell cell) { if (cell == null) { return ""; } String value = ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { value = new SimpleDateFormat("yyyy-MM-dd").format(date); } else { value = ""; } } else { value = new DecimalFormat("0").format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_FORMULA: // 导入时如果为公式生成的数据则无值 if (!cell.getStringCellValue().equals("")) { value = cell.getStringCellValue(); } else { value = cell.getNumericCellValue() + ""; } break; case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_ERROR: break; case HSSFCell.CELL_TYPE_BOOLEAN: value = (cell.getBooleanCellValue() == true ? "Y" : "N"); break; default: value = ""; } return value.trim(); } /** * 取值2003excel * * @param cell * @return */ private String getValue(HSSFCell cell) { if (cell == null) { return ""; } String value = ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { value = new SimpleDateFormat("yyyy-MM-dd").format(date); } else { value = ""; } } else { value = new DecimalFormat("0").format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_FORMULA: // 导入时如果为公式生成的数据则无值 if (!cell.getStringCellValue().equals("")) { value = cell.getStringCellValue(); } else { value = cell.getNumericCellValue() + ""; } break; case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_ERROR: break; case HSSFCell.CELL_TYPE_BOOLEAN: value = (cell.getBooleanCellValue() == true ? "Y" : "N"); break; default: value = ""; } return value.trim(); } }
package com.example.demo.util; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStreamReader; import java.io.PrintWriter; import java.net.URL; import java.net.URLConnection; import java.util.List; import java.util.Map; public class HttpRequest { /** * 向指定URL发送GET方法的请求 * * @param url 发送请求的URL * @param param 请求参数,请求参数应该是 name1=value1&name2=value2 的形式。 * @return URL 所代表远程资源的响应结果 */ public static String sendGet(String url, String param) { String result = ""; BufferedReader in = null; try { String urlNameString = url + "?" + param; URL realUrl = new URL(urlNameString); // 打开和URL之间的连接 URLConnection connection = realUrl.openConnection(); // 设置通用的请求属性 connection.setRequestProperty("accept", "*/*"); connection.setRequestProperty("connection", "Keep-Alive"); connection.setRequestProperty("Charsert", "GBK"); //设置请求编码 connection.setRequestProperty("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;SV1)"); // 建立实际的连接 connection.connect(); // 获取所有响应头字段 Map<String, List<String>> map = connection.getHeaderFields(); // 遍历所有的响应头字段 for (String key : map.keySet()) { System.out.println(key + "--->" + map.get(key)); } // 定义 BufferedReader输入流来读取URL的响应 in = new BufferedReader(new InputStreamReader( connection.getInputStream(), "GBK")); String line; while ((line = in.readLine()) != null) { result += line; } } catch (Exception e) { System.out.println("get请求异常, 网站[" + url + "]"); // System.out.println("发送GET请求出现异常!" + e); // e.printStackTrace(); } // 使用finally块来关闭输入流 finally { try { if (in != null) { in.close(); } } catch (Exception e2) { e2.printStackTrace(); } } return result; } /** * 向指定 URL 发送POST方法的请求 * * @param url 发送请求的 URL * @param param 请求参数,请求参数应该是 name1=value1&name2=value2 的形式。 * @return 所代表远程资源的响应结果 */ public static String sendPost(String url, String param) { PrintWriter out = null; BufferedReader in = null; String result = ""; try { URL realUrl = new URL(url); // 打开和URL之间的连接 URLConnection conn = realUrl.openConnection(); // 设置通用的请求属性 conn.setRequestProperty("accept", "*/*"); conn.setRequestProperty("connection", "Keep-Alive"); conn.setRequestProperty("Charsert", "GBK"); //设置请求编码 conn.setRequestProperty("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;SV1)"); // 发送POST请求必须设置如下两行 conn.setDoOutput(true); conn.setDoInput(true); // 获取URLConnection对象对应的输出流 out = new PrintWriter(conn.getOutputStream()); // 发送请求参数 out.print(param); // flush输出流的缓冲 out.flush(); // 定义BufferedReader输入流来读取URL的响应 in = new BufferedReader( new InputStreamReader(conn.getInputStream(), "GBK")); String line; while ((line = in.readLine()) != null) { result += line; } } catch (Exception e) { System.out.println("发送 POST 请求出现异常!" + e); e.printStackTrace(); } //使用finally块来关闭输出流、输入流 finally { try { if (out != null) { out.close(); } if (in != null) { in.close(); } } catch (IOException ex) { ex.printStackTrace(); } } return result; } public static void main(String[] args) { //发送 GET 请求 String s = HttpRequest.sendGet("http://tianqi.2345.com/t/wea_history/js/201701/70188_201701.js", ""); s = s.substring("var weather_str=".length(), s.length() - 1); System.out.println(s); JSONObject object = JSON.parseObject(s); String city = object.get("city").toString(); System.out.println(object.get("city")); JSONArray array = JSONArray.parseArray(object.get("tqInfo").toString()); for (int i = 0; i < array.size(); i++) { String ymd = array.getJSONObject(i).get("ymd").toString(); String bWendu = array.getJSONObject(i).get("bWendu").toString(); String yWendu = array.getJSONObject(i).get("yWendu").toString(); String tianqi = array.getJSONObject(i).get("tianqi").toString(); String fengxiang = array.getJSONObject(i).get("fengxiang").toString(); String fengli = array.getJSONObject(i).get("fengli").toString(); String aqi = array.getJSONObject(i).get("aqi").toString(); String aqiInfo = array.getJSONObject(i).get("aqiInfo").toString(); System.out.println(fengli); } /* //发送 POST 请求 String sr=HttpRequest.sendPost("http://localhost:6144/Home/RequestPostString", "key=123&v=456"); System.out.println(sr);*/ } }
结果类 TqBean
package com.example.demo.tianqi; public class TqBean { private String shen; private String city; private String url; private String code; private String ymd; private String bWendu; private String yWendu; private String tianqi; private String fengxiang; private String fengli; private String aqi; private String aqiInfo; public TqBean(String shen, String city, String ymd, String bWendu, String yWendu, String tianqi, String fengxiang, String fengli, String aqi, String aqiInfo) { this.shen = shen; this.city = city; this.ymd = ymd; this.bWendu = bWendu; this.yWendu = yWendu; this.tianqi = tianqi; this.fengxiang = fengxiang; this.fengli = fengli; this.aqi = aqi; this.aqiInfo = aqiInfo; } public TqBean(String shen, String city, String url, String code) { this.shen = shen; this.city = city; this.url = url; this.code = code; } public TqBean() { } public String getShen() { return shen; } public void setShen(String shen) { this.shen = shen; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getUrl() { return url; } public void setUrl(String url) { this.url = url; } public String getYmd() { return ymd; } public void setYmd(String ymd) { this.ymd = ymd; } public String getbWendu() { return bWendu; } public void setbWendu(String bWendu) { this.bWendu = bWendu; } public String getyWendu() { return yWendu; } public void setyWendu(String yWendu) { this.yWendu = yWendu; } public String getTianqi() { return tianqi; } public void setTianqi(String tianqi) { this.tianqi = tianqi; } public String getFengxiang() { return fengxiang; } public void setFengxiang(String fengxiang) { this.fengxiang = fengxiang; } public String getFengli() { return fengli; } public void setFengli(String fengli) { this.fengli = fengli; } public String getAqi() { return aqi; } public void setAqi(String aqi) { this.aqi = aqi; } public String getAqiInfo() { return aqiInfo; } public void setAqiInfo(String aqiInfo) { this.aqiInfo = aqiInfo; } public String getCode() { return code; } public void setCode(String code) { this.code = code; } }
线程类 TianQiThread
package com.example.demo.tianqi; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.example.demo.util.HttpRequest; import java.util.Set; public class TianQiThread implements Runnable { private String shen; private String url; private Set<TqBean> resList; private Set<String> errirUrls; public TianQiThread(String shen, String url, Set<TqBean> resList, Set<String> errirUrls) { this.shen = shen; this.url = url; this.resList = resList; this.errirUrls = errirUrls; } @Override public void run() { String res = HttpRequest.sendGet(url, ""); if (res.equals("")) { errirUrls.add(url); } else { res = res.substring("var weather_str=".length(), res.length() - 1); JSONObject object = JSON.parseObject(res); String city = object.get("city").toString(); JSONArray array = JSONArray.parseArray(object.get("tqInfo").toString()); for (int i = 0; i < array.size(); i++) { String ymd = array.getJSONObject(i).get("ymd").toString(); String bWendu = array.getJSONObject(i).get("bWendu").toString(); String yWendu = array.getJSONObject(i).get("yWendu").toString(); String tianqi = array.getJSONObject(i).get("tianqi").toString(); String fengxiang = array.getJSONObject(i).get("fengxiang").toString(); String fengli = array.getJSONObject(i).get("fengli").toString(); String aqi = array.getJSONObject(i).get("aqi").toString(); String aqiInfo = array.getJSONObject(i).get("aqiInfo").toString(); TqBean tq = new TqBean(shen, city, ymd, bWendu, yWendu, tianqi, fengxiang, fengli, aqi, aqiInfo); resList.add(tq); } } } }
处理类 TianQiByYear
package com.example.demo.tianqi; import com.example.demo.util.ReadExcel; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.File; import java.io.FileOutputStream; import java.util.HashSet; import java.util.List; import java.util.Set; import java.util.concurrent.LinkedBlockingQueue; import java.util.concurrent.ThreadPoolExecutor; import java.util.concurrent.TimeUnit; public class TianQiByYear { private static int corePoolSize = 100; private static int maximumPoolSize = 1000; private static long keepAliveTime = 200; private static Set<TqBean> resList = new HashSet<>(); private static Set<String> errirUrls = new HashSet<>(); public static void main(String[] args) { try { Set<TqBean> list = new HashSet<>(); List<List<String>> citylist = ReadExcel.readExcel("d:\2345.xlsx"); // 以年为周期生成excel for (int year = 2017; year <= 2017; year++) { resList = new HashSet<>(); errirUrls = new HashSet<>(); for (List<String> row : citylist) { String shen = row.get(0); String city = row.get(1); String code = row.get(2); if (!"河南".equals(shen)) { continue; } for (int month = 1; month <= 12; month++) { String url = "http://tianqi.2345.com/t/wea_history/js/"; String m = month > 9 ? ("" + month) : "0" + month; url += year + m + "/" + code + "_" + year + m + ".js"; TqBean tq = new TqBean(shen, city, url, code); list.add(tq); } } ThreadPoolExecutor executor = new ThreadPoolExecutor(corePoolSize, maximumPoolSize, keepAliveTime, TimeUnit.MILLISECONDS, new LinkedBlockingQueue<Runnable>()); for (TqBean tq : list) { executor.execute(new TianQiThread(tq.getShen(), tq.getUrl(), resList, errirUrls)); } executor.shutdown(); while (true) { //等待线程处理完毕 if (executor.isTerminated()) { for (String s : errirUrls) { System.out.println("失败url: [" + s + "]"); } break; } Thread.sleep(300); } // 导出excel //创建新工作簿 XSSFWorkbook workbook = new XSSFWorkbook(); //新建工作表 XSSFSheet sheet = workbook.createSheet("sheet"); XSSFRow row0 = sheet.createRow(0); XSSFCell cell = row0.createCell(0); XSSFCell cell1 = row0.createCell(1); XSSFCell cell2 = row0.createCell(2); XSSFCell cell3 = row0.createCell(3); XSSFCell cell4 = row0.createCell(4); XSSFCell cell5 = row0.createCell(5); XSSFCell cell6 = row0.createCell(6); XSSFCell cell7 = row0.createCell(7); XSSFCell cell8 = row0.createCell(8); XSSFCell cell9 = row0.createCell(9); cell.setCellValue("省份"); cell1.setCellValue("城市"); cell2.setCellValue("日期"); cell3.setCellValue("最高气温"); cell4.setCellValue("最低气温"); cell5.setCellValue("天气"); cell6.setCellValue("风向"); cell7.setCellValue("风力"); cell8.setCellValue("空气质量"); cell9.setCellValue("质量指数"); int rownum = 1; for (TqBean t : resList) { //创建行,行号作为参数传递给createRow()方法,第一行从0开始计算 XSSFRow row = sheet.createRow(rownum++); //创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算 XSSFCell mcell = row.createCell(0); XSSFCell mcell1 = row.createCell(1); XSSFCell mcell2 = row.createCell(2); XSSFCell mcell3 = row.createCell(3); XSSFCell mcell4 = row.createCell(4); XSSFCell mcell5 = row.createCell(5); XSSFCell mcell6 = row.createCell(6); XSSFCell mcell7 = row.createCell(7); XSSFCell mcell8 = row.createCell(8); XSSFCell mcell9 = row.createCell(9); mcell.setCellValue(t.getShen()); mcell1.setCellValue(t.getCity()); mcell2.setCellValue(t.getYmd()); mcell3.setCellValue(t.getbWendu()); mcell4.setCellValue(t.getyWendu()); mcell5.setCellValue(t.getTianqi()); mcell6.setCellValue(t.getFengxiang()); mcell7.setCellValue(t.getFengli()); mcell8.setCellValue(t.getAqi()); mcell9.setCellValue(t.getAqiInfo()); } //输出到磁盘中 FileOutputStream fos = new FileOutputStream(new File("F:\tianqi\" + year + "年天气.xlsx")); workbook.write(fos); fos.close(); System.out.println("===========ok========================"); } } catch (Exception e) { e.printStackTrace(); } } }