R wrapper function to aid reading in .xlsx worksheets and writing to original position in the workbook

read.csv, part of the base package in R, provides native support to read in .csv and .txt documents, with the column in a dataframe assigned with high accuracy to the "true" class. The XLConnect package allows the user to read in sheets from .xls and .xlsx workbooks, but the column classes are typically all assigned as factor, or must be manually re-assigned by the user. The below is a wrapper function to allow the user to quickly read in a .xlsx file to R, while leveraging the strengths of read.csv.

xlsx.out allows the user to take data (the intention is data read in from xlsx.in) and overwrite a worksheet in a workbook with that data.



## this is a wrapper function to aid reading in xlsx sheets with a better approximation of the correct column class than is natively provided by XLConnect. Allows the user to do this from a workbook without manual export to csv.

####with XLConnect


library(XLConnect)

file.names<-list.files(path='./', full.names=TRUE)
file.names
#load a file by the number in file.names
wb<-loadWorkbook(file.names[filenumber])
data.frame(getSheets(yoursheetnumber))

xlsx.in<-function(file, sheet = 1){
temp<-loadWorkbook(file, create=FALSE)
temp.test <-readWorksheet(temp, sheet)
write.csv(temp.test, "temp.csv")
out<-read.csv("temp.csv")
file.remove("temp.csv")
return(out)
}


#make sure your file is versioned first- this will overwrite the original
xlsx.out<-function(file, sheet = 1, datas){
temp<-loadWorkbook(file, create=FALSE)
clearSheet(temp, sheet)
datas<data.frame(datas)
writeWorksheet(temp,datas,sheet)
saveWorkbook(temp)
}

Data loss when exporting from ArcMap to Excel

When exporting records from an attribute table in ArcMap to Excel, be conscious of field types. For example, if a Double field is longer than 6 digits, it may get rounded to 6 significant figures in Excel. Check the output Excel file to ensure that your data has been fully retained, and if necessary transfer the data into a new text attribute.

High Resolution Imagery for the Brazilian Amazon

As part of its DETER program to detect illegal deforestation, INPE (National Institute for Space Research) of Brazil publicly provides imagery from the CBERS satellite program. You must first register before downloading imagery at this link. Then select imagery:

Satellite: CBERS 2B or CBERS 2

Instrument: CCD for multispectral 20-m resolution imagery, HRC for 2.7 m panchromatic band (CBERS 2B only)

You may select scenes with the (clunky) navigation pane, or based on coordinates, a city name, or path/row (recommended). To determine path/row, click on "Files and Documents" to the right (Item 5) and download "CBERS Reference Grid - WRS (Shape File)" (also stored in S:\Resources\CBERS_shp). Set the coordinate system to WGS 1984.

HRC imagery can be used to sharpen CCD. There are 25 HRC scenes per  CCD: subpaths A-E from west to east, and subrows 1-5 from north to south (though a CCD scene's corresponding 25 HRC scenes don't occupy quite the same footprint). Note that scenes may require georeferencing.

FBRI FPS Grow Model Accelerator

We have developed a parallel implementation of the Forest Biometrics Research Institute's growth model in the Forest Projection and Planning System.   It reduces the time to grow stand records by a factor approximately equal to the number of CPU cores.

Please contact ecoPartners if you are interested in acquiring a copy of the software or if you are interested in supporting further parallel implementation of FPS routines.

Connect to ESRI Shapefile DBF

You need to use OLE DB Provider for Visual FoxPro:

http://www.microsoft.com/en-us/download/details.aspx?id=14839

"Provider=VFPOLEDB;Data Source=C:\Temp;Exclusive=Yes;Collating Sequence=machine"

You must restart your computer after installing the provider.

Extracting a subset of records in ArcMap 10

If you are looking to subsample your data by extracting a set number of records (i.e., every sixth record), use the script below in the field calculator of ArcMap 10.

Pre-Logic Script Code:

P=[ID]

i=Int(p/6)-Int((p-1)/6)

[name of field]=

i

The example above will extract every sixth record.

In String for Excel

For some reason, Excel doesn't have a built-in function to query whether a string contains a specified second string. Luckily, the joys of VB allow us to do so.

In Excel, under the Developer tab, click "Visual Basic." In the left-hand pane, right-click "VBAProject ()" > Insert > Module. Paste the below snippet into the main pane and save. Now you can call this function into Excel just as you would any other.

~~~

Function InstrE(start As Integer, examine As String, lookFor As String) As Integer

InstrE = InStr(start, examine, lookFor)

End Function

~~~