The OpenXML SDK is the right tool for this job but you need to be careful to use the SAX (Simple API for XML) approach rather than the DOM approach. From the linked wikipedia article for SAX: Where the DOM operates on the document as a whole, SAX parsers operate on each piece of the … 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
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 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
Try pd.ExcelFile: xls = pd.ExcelFile(‘path_to_file.xls’) df1 = pd.read_excel(xls, ‘Sheet1’) df2 = pd.read_excel(xls, ‘Sheet2′) As noted by @HaPsantran, the entire Excel file is read in during the ExcelFile() call (there doesn’t appear to be a way around this). This merely saves you from having to read the same file in each time you want to access … Read more
“External table is not in the expected format.” typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0 Using the following connection string seems to fix most problems. public static string path = @”C:\src\RedirectApplication\RedirectApplication\301s.xlsx”; public static string connStr = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + path + … Read more
To colour the rows: $objPHPExcel->getActiveSheet()->getStyle(‘A1000:IV2000’) ->getFill() ->setFillType(PHPExcel_Style_Fill::FILL_SOLID); $objPHPExcel->getActiveSheet()->getStyle(‘A1000:IV2000′) ->getFill() ->getStartColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); To format dates: Make sure that you’re setting your date values as an Excel serialized timestamp $myDate = “21/12/2014 15:08:23”; $myDateValue = DateTime::createFromFormat(‘d/m/Y H:i:s’, $myDate); $excelTimeStamp = PHPExcel_Shared_Date::PHPToExcel($myDateValue); $objPHPExcel->getActiveSheet() ->setCellValue(‘A1’, $excelTimeStamp); $objPHPExcel->getActiveSheet() ->getStyle(‘C9’) ->getNumberFormat() ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY); All of this is covered in the documentation and in … Read more