Java excel and requestor demo

作者: e43ef8a56980 | 来源:发表于2019-08-07 17:29 被阅读0次

Java excel and requestor demo
java实现Excel导入（迭代一）
Excel Upload Demo
反射机制--高淇Java视频笔记
Spring Boot 菜鸟教程 12 EasyPoi导出Exc
html使用exceljs 导出excel
Scriptella working demo
华为NB-IoT平台之应用接入
springboot jpa hibernate 属性列表
3.Python合并多个Excel数据

Working demo of reading URLs from Excel file and request them with URL
JExcelReaderWriter.java

package excel;


import http.HttpRequester;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.Number;
import org.junit.Test;

import java.io.File;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.Stream;


/**
 * Created by IntelliJ IDEA.<br/>
 * User: yz<br/>
 * Date: 7/9/2019<br/>
 * Time: 9:13 AM<br/>
 * To change this template use File | Settings | File Templates.
 */
public class JExcelReadWriter {
    private static final String EXCEL_FILE_LOCATION = "D:\\outputData.xls";
    private static final String URL_FILE_LOCATION = "C:\\programs\\scriptella-1.1\\bin\\tjzy.xls";
    private static final String stg1Prefix = "http://ecs-stg1.example.com.cn";
    private static final String prdPrefix = "http://ecs.example.com.cn";

    public static void main(String[] args) throws IOException, BiffException {
//        writeDemo();
//        readColumn(EXCEL_FILE_LOCATION,0, 1);
        List<String> urls = getUrls();
        urls.stream().forEach(System.err::println);
//        writeColumn(EXCEL_FILE_LOCATION, 9, urls);
    }

    @Test
    public void testValidateUrl() throws IOException, BiffException {
        List<String> urls = JExcelReadWriter.getUrls();
//        urls.add(failedUrl);
        urls.stream().forEach(v -> HttpRequester.requestUrl(v));
    }

    public static List<String> getUrls() throws IOException, BiffException {
        List<String> urls = readColumn(URL_FILE_LOCATION, 0, 2);
        urls.remove("URL");
        Stream<String> distinct = urls.stream().distinct();
        Stream<String> finalUrls = distinct.map(v -> stg1Prefix + v);
        List<String> collect = finalUrls.collect(Collectors.toList());
        return collect;
    }

    public static List<String> readColumn(String fileLocation, int sheetNumber, int columnNumber) throws IOException, BiffException {
        Workbook workbook = Workbook.getWorkbook(new File(fileLocation));
        Sheet sheet = workbook.getSheet(sheetNumber);
        Cell[] column = sheet.getColumn(columnNumber);
//        Arrays.stream(column).map(Cell::getContents).forEach(System.out::println);
        return Arrays.stream(column).map(Cell::getContents).collect(Collectors.toList());
    }

    private static void readDemo() {
        Workbook workbook = null;
        try {

            workbook = Workbook.getWorkbook(new File(EXCEL_FILE_LOCATION));

            Sheet sheet = workbook.getSheet(0);
            Cell cell1 = sheet.getCell(0, 0);
            System.out.print(cell1.getContents() + ":");    // Test Count + :
            Cell cell2 = sheet.getCell(0, 1);
            System.out.println(cell2.getContents());        // 1

            Cell cell3 = sheet.getCell(1, 0);
            System.out.print(cell3.getContents() + ":");    // Result + :
            Cell cell4 = sheet.getCell(1, 1);
            System.out.println(cell4.getContents());        // Passed

            System.out.print(cell1.getContents() + ":");    // Test Count + :
            cell2 = sheet.getCell(0, 2);
            System.out.println(cell2.getContents());        // 2

            System.out.print(cell3.getContents() + ":");    // Result + :
            cell4 = sheet.getCell(1, 2);
            System.out.println(cell4.getContents());        // Passed 2

        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } finally {

            if (workbook != null) {
                workbook.close();
            }

        }
    }

    public static void writeColumn(String fileLocation, int col, String cellContent, String cellContent2, int rowIndex, WritableSheet excelSheet,
                                   WritableWorkbook myFirstWbook) {

        try {

            Label label = new Label(col, rowIndex, cellContent);
            excelSheet.addCell(label);
            Label label2 = new Label(col + 1, rowIndex, cellContent2);
            excelSheet.addCell(label2);


        } catch (WriteException e) {
            e.printStackTrace();
        } finally {

//            if (myFirstWbook != null) {
//                try {
//                    myFirstWbook.close();
//                } catch (IOException e) {
//                    e.printStackTrace();
//                } catch (WriteException e) {
//                    e.printStackTrace();
//                }
//            }
        }
    }

    private static void writeDemo() {
        //1. Create an Excel file
        WritableWorkbook myFirstWbook = null;
        try {

            myFirstWbook = Workbook.createWorkbook(new File(EXCEL_FILE_LOCATION));

            // create an Excel sheet
            WritableSheet excelSheet = myFirstWbook.createSheet("Sheet 1", 0);

            // add something into the Excel sheet
            Label label = new Label(0, 0, "Test Count");
            excelSheet.addCell(label);

            Number number = new Number(0, 1, 1);
            excelSheet.addCell(number);

            label = new Label(1, 0, "Result");
            excelSheet.addCell(label);

            label = new Label(1, 1, "Passed");
            excelSheet.addCell(label);

            number = new Number(0, 2, 2);
            excelSheet.addCell(number);

            label = new Label(1, 2, "Passed 2");
            excelSheet.addCell(label);

            myFirstWbook.write();


        } catch (IOException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        } finally {

            if (myFirstWbook != null) {
                try {
                    myFirstWbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}

HttpRequester.java

package http;

/**
 * Created by IntelliJ IDEA.<br/>
 * User: yz<br/>
 * Date: 7/9/2019<br/>
 * Time: 9:13 AM<br/>
 * To change this template use File | Settings | File Templates.
 * 1. use Fiddler to extract useful information from login vs non-logon same url request status
 * 2. use Jsoup won't work I jump back to okhttp and it worked
 */

import excel.JExcelReadWriter;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import okhttp3.*;
import org.junit.Test;
import org.slf4j.Logger;

import java.io.File;
import java.io.IOException;
import java.util.List;

/**
 */
public class HttpRequester {
    private static Logger logger;
    String loginUrl = "http://ecs-stg1.example.com.cn/gateway-server/exam/login/esg/checkLogin.do";
    String failedUrl = "http://ecs-stg1.example.com.cn/gateway-server/";
    public static final String EXCEL_FILE_LOCATION = "D:\\outputDataNew.xls";
    private static final String URL_FILE_LOCATION = "C:\\programs\\scriptella-1.1\\bin\\tjzy.xls";

    @Test
    public void testValidateUrl() throws IOException, BiffException {
        List<String> urls = JExcelReadWriter.getUrls();
//        urls.add(failedUrl);
        urls.stream().forEach(v -> requestUrl(v));
    }

    public static void requestUrl(String url) {
        try {
            OkHttpClient client = new OkHttpClient();

//        loginStg1(client);

            MediaType mediaTypeQ = MediaType.parse("application/json;charset=UTF-8");
            RequestBody bodyQuery = RequestBody.create(mediaTypeQ, "{\"id\":17917,\"flag\":2}");
            Request requestQ = new Request.Builder()
                    .url(url)
                    .post(bodyQuery)
                    .addHeader("cache-control", "no-cache") // cookie header replace \n with ; it will work
                    .addHeader("Origin", "http://ecs-stg1.example.com.cn")
                    .addHeader("Accept-Encoding", "gzip, deflate")
                    .addHeader("Accept-Language", "zh-CN,zh;q=0.9,en;q=0.8")
                    .addHeader("User-Agent", "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/62.0.3202.94 Safari/537.36")
                    .addHeader("Content-Type", "application/json;charset=UTF-8")
                    .addHeader("Accept", "application/json, text/plain, */*")
                    .addHeader("Referer", "http://ecs-stg1.example.com.cn/exam/")
                    .addHeader("Cookie", "ecs-stg1.example.com.cn/gateway-server/")
                    .addHeader("Connection", "keep-alive")
                    .addHeader("cache-control", "no-cache")
                    .addHeader("Postman-Token", "5c55a52b-424a-4b56-bb6c-b59ceacfac18")
                    .addHeader("Cookie", "BIGipServerPOOL_PACLOUD_STGR2018040304093=2627994654.20480.0000; HIPPO-WDSESSION=S4f43d07020834e12bd959010e30944b40;" +
                            "HRX-WEB-SESSION=9e84d8e163680d78016371366dda0014-e650d851a42a468f99b636bac83f15d8;" +
                            "CAS_SSO_COOKIE=9e84d8e163680d78016371366dda0014-e650d851a42a468f99b636bac83f15d8;")
                    .build();

            Response responseQ = client.newCall(requestQ).execute();
            String result = responseQ.body().string();
            if (result.contains("权限认证失败") || result.contains("\"status\":404,")) {
                throw new RuntimeException("有失败的啊！");
            }
            System.err.println("responseOfUrl = " + result);
            int col = 1;
            int rowIndex = 1;
            //1. Create an Excel file
            WritableWorkbook myFirstWbook = null;
            String fileName = EXCEL_FILE_LOCATION;
            myFirstWbook = Workbook.createWorkbook(new File(fileName));
            // create an Excel sheet
            WritableSheet excelSheet = myFirstWbook.createSheet("Sheet 1", 0);
            excelSheet.insertColumn(col);
            JExcelReadWriter.writeColumn(EXCEL_FILE_LOCATION, col,
                    result,
                    url, rowIndex, excelSheet, myFirstWbook);
            rowIndex++;
//            if (myFirstWbook != null) {
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    @Test
    public void testLogin() throws IOException {
        OkHttpClient client = new OkHttpClient();
        loginStg1(client);
    }

    private void loginStg1(OkHttpClient client) throws IOException {
        MediaType mediaType = MediaType.parse("application/json;charset=UTF-8");
        RequestBody body = RequestBody.create(mediaType, "{\n    \"umNum\": \"rw5LLXDFhmZMNoWpuwpQeA==\",\n    \"umPassword\": \"PuNMiOlxnTlfIjdcIq93fg==\",\n    \"verificationCode\": \"\",\n    \"requestId\": \"\"\n}");
        Request request = new Request.Builder()
                .url(loginUrl)
                .post(body)
                .addHeader("Accept", "application/json, text/plain, */*")
                .addHeader("Referer", "http://ecs-stg1.example.com.cn/exam/")
                .addHeader("Origin", "http://ecs-stg1.example.com.cn")
                .addHeader("User-Agent", "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/62.0.3202.94 Safari/537.36")
                .addHeader("Content-Type", "application/json;charset=UTF-8")
                .addHeader("cache-control", "no-cache")
                .build();
        Response response = client.newCall(request).execute();
        Headers headers = response.headers();
        System.err.println("headers = " + headers);
    }

}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>yz</groupId>
    <artifactId>JExcelHttpRequester</artifactId>
    <version>1.0-SNAPSHOT</version>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

        <dependency>
            <!-- jsoup HTML parser library @ https://jsoup.org/ -->
            <groupId>org.jsoup</groupId>
            <artifactId>jsoup</artifactId>
            <version>1.11.3</version>
        </dependency>

        <!-- log4j -->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.2</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.21</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.9.8</version>
        </dependency>
        <dependency>
            <groupId>com.squareup.okhttp3</groupId>
            <artifactId>okhttp</artifactId>
            <version>3.8.1</version>
        </dependency>

        <dependency>
            <groupId>com.mashape.unirest</groupId>
            <artifactId>unirest-java</artifactId>
            <version>1.4.9</version>
        </dependency>

        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpclient</artifactId>
            <version>4.3.6</version>
        </dependency>
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpasyncclient</artifactId>
            <version>4.0.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpmime</artifactId>
            <version>4.3.6</version>
        </dependency>

    </dependencies>

</project>

Java excel and requestor demo
Working demo of reading URLs from Excel file and request ...
java实现Excel导入（迭代一）
java实现Excel导入（迭代一）目录 1.准备工作 2.Excel导入代码及demo 3.Excel导入的时...
Excel Upload Demo
upload Excel file to spring mvc Excel Java Excel.java Exc...
反射机制--高淇Java视频笔记
以注释形式讲解。 1.User.java 2.Demo1.java 3.Demo2.java 4.Demo3.java
Spring Boot 菜鸟教程 12 EasyPoi导出Exc
Java操作excel框架 Java Excel俗称jxl，可以读取Excel文件的内容、创建新的Excel文件、...
html使用exceljs 导出excel
记录一下前端html使用excel 导出excel 学习的demo
Scriptella working demo
Scriptella excel and javascript and database working demo...
华为NB-IoT平台之应用接入
JAVA SDK使用指南下载北向JAVA SDK Demo及北向JAVA SDK：北向JAVA SDK Demo...
springboot jpa hibernate 属性列表
更多demo请关注 springboot demo实战项目java 脑洞java 面试宝典 Hibernate属性...
3.Python合并多个Excel数据
写入数据到excel的操作这样我们得到了包含两个sheet的excel文件demo.xlsx 读取excel操作...