This PowerPoint deck, entitled “SpreadsheetML Basics”, from Microsoft, provides a good overview. It is a 30-page presentation including diagrams, and it is not practical to include its contents here. Four different stackoverflow reviewers have jumped on this post to delete it because the stackoverflow policy objects to simple links. I do not think that is … Read more
In answer to your questions: XLSX is just a collection of XML files in a zip container. There is no other magic. If you decompress/unzip a valid XLSX files and then recompress/zip it and you can’t read the resulting output then the problem is generally with the files being rezipped or, less likely, the zipping … Read more
I found a snippet the the write test here https://github.com/SheetJS/js-xlsx/blob/master/tests/write.js#L14-L19 For quick reference, where ws is your worksheet. var wscols = [ {wch:6}, {wch:7}, {wch:10}, {wch:20} ]; ws[‘!cols’] = wscols;
Here’s an example using xlsxwriter: import os import glob import csv from xlsxwriter.workbook import Workbook for csvfile in glob.glob(os.path.join(‘.’, ‘*.csv’)): workbook = Workbook(csvfile[:-4] + ‘.xlsx’) worksheet = workbook.add_worksheet() with open(csvfile, ‘rt’, encoding=’utf8′) as f: reader = csv.reader(f) for r, row in enumerate(reader): for c, col in enumerate(row): worksheet.write(r, c, col) workbook.close() FYI, there is also … Read more
You can also use var XLSX = require(‘xlsx’); var workbook = XLSX.readFile(‘Master.xlsx’); var sheet_name_list = workbook.SheetNames; console.log(XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]]))
Try in2csv! Usage: in2csv file.xlsx > file.csv
I stumbled on this question when someone sent me (yet another) Excel file to analyze. This one isn’t even that big but for whatever reason I was running into a similar error: java.lang.OutOfMemoryError: GC overhead limit exceeded Based on comment by @DirkEddelbuettel in a previous answer I installed the openxlsx package (http://cran.r-project.org/web/packages/openxlsx/). and then ran: … Read more
This is a known issue: http://code.google.com/p/rexcel/issues/detail?id=33 While unresolved, the issue page links to a solution by Gabor Grothendieck suggesting that the heap size should be increased by setting the java.parameters option before the rJava package is loaded. (rJava is a dependency of xlsx.) options(java.parameters = “-Xmx1000m”) The value 1000 is the number of megabytes of … Read more
For a solution that is free of fiddly external dependencies*, there is now readxl: The readxl package makes it easy to get data out of Excel and into R. Compared to many of the existing packages (e.g. gdata, xlsx, xlsReadWrite) readxl has no external dependencies so it’s easy to install and use on all operating … Read more
You can write to multiple sheets with the xlsx package. You just need to use a different sheetName for each data frame and you need to add append=TRUE: library(xlsx) write.xlsx(dataframe1, file=”filename.xlsx”, sheetName=”sheet1″, row.names=FALSE) write.xlsx(dataframe2, file=”filename.xlsx”, sheetName=”sheet2″, append=TRUE, row.names=FALSE) Another option, one that gives you more control over formatting and where the data frame is placed, … Read more