xlquery

README

xlquery

xlquery is an experimental utility designed to update workbook files (.xlsx) taking a column as search query strings and putting the results in another column of the same spreadsheet. This proof of concept works with EPrints repository software relying on advanced search and results returned in RSS2 format. By default the search if performanced against CaltechAUTHORS repository. You can point at a different EPrints repository by setting the environment variable EPRINTS_SEARCH_URL.

USAGE

    xlquery [OPTIONS] WORKBOOK_NAME QUERY_COLUMN RESULT_COLUMN

The command line program xlquery takes the name of a xlsx file along with the column name for the query string and one for the result. By default it searches on "Sheet1". This can be changed with the "-s" command line option.

The simple form where column A holds the query string and results will be put in column B

    xlquery titlelist.xlsx A B

xlquery will display console message describing the processing on stdout. If there are errors they will be sent to stderr with catastrophic errors exiting with a value 1. If the program is successful it will exit with the value 0.

• Sheet name should correspond to the sheet you want to run through (e.g. "Sheet1")
• Column names are in Excel's letter format (e.g. "A", "FX", "BBC").
• data path is the part of the result you want to use (e.g. ".item[].link" is the RSS item link field)

OPTIONS

-d, -datapath	set the data path to return for results, e.g. ".item[].link"
-h, -help	show help information
-l, -license	show license information
-o, -overwrite	overwrite the results column
-s, -sheet	set the sheet name, e.g. "Sheet1"
-v, -version	show version information

Example

    xlquery -sheet "Title List" -overwrite ./testdata/demo2.xlsx Sheet1 A C 

This opens demo2.xlsx and uses the sheet named "Title List". It populates fresh results in column C based on the query string in column A. The results are taken from the data path of ".item[].link" from the RSS2 response in the search request.

Documentation

Overview

xlquery - a package for quering Caltech library API (and others) and integrating results into an Excel Workbook.

@author R. S. Doiel, <rsdoiel@caltech.edu>

Copyright (c) 2016, Caltech All rights not granted herein are expressly reserved by Caltech.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

3. Neither the name of the copyright holder nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Index

• Constants
• func ColumnNameToIndex(colName string) (int, error)
• func GetCell(sheet *xlsx.Sheet, row int, col int) string
• func Request(api *url.URL, headers map[string]string) ([]byte, error)
• func UpdateCell(sheet *xlsx.Sheet, row int, col int, value string, overwrite bool) error
• func UpdateParameters(api *url.URL, queryTerms map[string]string) *url.URL

Constants

const (
	// Version of this package
	Version = "v0.0.1"
)

Functions

func ColumnNameToIndex

func ColumnNameToIndex(colName string) (int, error)

ColumnNameToIndex turns a column reference e.g. 'A', 'BF' into a zero-based array position

func GetCell

func GetCell(sheet *xlsx.Sheet, row int, col int) string

GetCell given a Spreadsheet, row and col, return the query string or error

func Request

func Request(api *url.URL, headers map[string]string) ([]byte, error)

Request executes an HTTP request to the service returning a Query structure and error value.

func UpdateCell

func UpdateCell(sheet *xlsx.Sheet, row int, col int, value string, overwrite bool) error

UpdateCell given a Spreadsheeet, row and col, save the value respecting the overWrite flag or return an error

func UpdateParameters

func UpdateParameters(api *url.URL, queryTerms map[string]string) *url.URL

UpdateParameters adds/overwrites any mapped values to the URL object passed in.

URL attribute for EPrints advanced search (output is Atom):

 Scheme: http
 Host: eprint-repository.example.org
 Path: /cgi/search/advanced
 Query parameters:
		title: Molecules in solutoin
		output: Atom

Example usage: api, _ := url.Parse("http://eprint-repository.example.org/cgi/search/advanced") xlquery.UpdateQuery(api, map[string]string{"title": title, "output":"Atom"}) data, err := http.Get(api.String()) ...