Reading a string column in xlsx with date and time values
r,datetime,xlsx,posixct,posixlt
You can read into string with Hadley's readxl package. library(readxl) df <- read_excel('~/Desktop/test.xlsx') There is some support for col_type but it's not ready yet. Instead, you can use as.POSIXct to fix those after being read into a data.frame e.g. as.POSIXct(df$Date, format="%d/%m/%Y %H:%M:%S", tz="CET") ...
R: easy way to export multiple data.frame to multiple excel worksheets?
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") write.xlsx(dataframe2, file="filename.xlsx", sheetName="sheet2", append=TRUE) In case you might find it useful, here are some interesting helper functions that...
Apache POI XSSFFont getBold() give null pointer exception
java,apache,apache-poi,xlsx,xssf
From the Apache POI JavaDocs on XSSFRichTextString.getFontAtIndex(int): Returns: A copy of the font that's currently being applied at that index or null if no font is being applied or the index is out of range. With a Rich Text String, a section of the string can either have a specific...
UIWebview - could not open the password protected .xlsx,.docx and .ppt
Through UIWebView , we cannot open any password protected files except PDF. If you want to open the password protected files, use QLPreviewcontroller. It will work perfectly
NoClassDefFoundError: UnsupportedFileFormatException while working with excel sheet using java
java,excel,apache-poi,xlsx,xssf
This is covered in the Apache POI FAQ page: Can I mix POI jars from different versions? No. This is not supported. All POI jars in use must come from the same version. A combination such as poi-3.11.jar and poi-ooxml-3.9.jar is not supported, and will fail to work in unpredictable...
How to convert .xlsx file to .xml file using php?
php,xml,file,xlsx,file-conversion
Check the PHPExcel spreadsheet engine It will alow you to read different file formats into your spreadsheet object Excel 2007 (spreadsheetML) BIFF5 (Excel 5.0 / Excel 95), BIFF8 (Excel 97 and higher) PHPExcel Serialized Spreadsheet Excel 2003 XML format Open Office Calc (.ods) Gnumeric Symbolic Link (SYLK) CSV (Comma Separated...
read corrupt excell-file with NPOI
I figured it out! Since the .xls file is really just a html-table, I opened it with notepad and saw that it was html-source for a table. So All I had to do was to make a parser to read from the html-file into a DataTable and proceed from there....
Openpyxl - trouble naming workbook
Perhaps the problem is you have a space in the filename? Replace str(datetime.datetime.now()).split(".")[0] with this: str(datetime.datetime.now()).split(".")[0].replace(' ', '_'). Glad I could help Will!...
Excel & EPPlus .NET library: Advanced DropDown list validation
I did it with the following code: //ExcelWorksheet ws var validation = ws.DataValidations.AddListValidation(cell.Address); validation.ShowErrorMessage = true; validation.ErrorStyle = ExcelDataValidationWarningStyle.stop; validation.ErrorTitle = "Error"; validation.Error = "Error Text"; // sheet with a name : DropDownLists // from DropDownLists sheet, get values from cells: !$A$1:$A$10 var formula = "=DropDownLists!$A$1:$A$10" validation.Formula.ExcelFormula = formula; ...
Hssf Bold property not working in partial file in excel 2007-10
Upgrading comment to an answer; Seems you are using HSSF instead of XSSF for xlsx. Hence, though HSSFRichTextString is valid for an HSSFCell, you need XSSFCell instead and then set its value with XSSFRichTextString....
Cannot read data from an xlsx file in RStudio
Thanks to @Stibu I realised I had to set my work directory. This is the command you use to run in Rstudio; setwd("C/Documents..."). The file path is where the excel file is located.
how to upload csv/xlsx file to mongodb?
node.js,mongodb,csv,mongoose,xlsx
For csv file you can use node module 'csv-parse'. read the csv file using node 'fs' module pass the data to csv parser and you will get arry of array, where inner array represents each row. Take a look at the following code. var csvParser = require('csv-parse'); fs.readFile(filePath, { encoding:...
Running script for every string result in range
Try to use the below Code Note : It is assumed that there is no blank cell in your search criteria column i.e. Column "G". Sub SearchForString() Dim LSearchRow As Integer Dim LCopyToRow As Integer Dim LSearchValue As String Dim shName As String On Error GoTo Err_Execute Application.ScreenUpdating = False...
“read_excel” in a Shiny app
This is an open issue with the readxl package. The current workaround provided there is to copy the file data path and append .xlsx. Here is a working example on my machine limited to .xlsx files edited to use file.rename instead of file.copy. library(shiny) library(readxl) runApp( list( ui = fluidPage(...
Can't modify the xml node value by golang?
Here's a working example (using identation for XML): Playground Explanation: You're copying your structs in your for-loop. When writing for _, r := range v.Row { r is a copy of the value(s) in v.Row. If you then try to change the value, you will just change the copy, but...
Is there a library to edit .xlsx files while preserving formatting and graphs on mac?
If you're willing to write a bit of code, and ok to use coffeescript, I maintain a docx templating system called docxtemplater. It doesn't modify the document except for certain tags. They is not much docx-specific code, but rather for all of the xlsx, pptx documents. However, I don't yet...
write.xlsx function gives error when defining path with the file name but read.xlsx is fine
Replacing "~/" with "Users//" works for Mac (probably for Linux as well). Though, it still eludes me how read.xlsx and write.xlsx could differ in such a fundamental way.
opentbs doesn't print last row on xlsx
The tag [base_sub2_sub1;...] is not merged because it is out of the block "base_sub2" So it cannot be a sub-block of "base_sub2". The block "base_sub2" is defined over a cell, while block "base_sub2_sub1" is defined over a row and below "base_sub2". You have to change the bound of "base_sub2". I...
how to avoid large numbers from converting to Exponential in nodejs excel file read
The number 919971692474 is normally displayed as 9.19972E+11 in Excel. To force it to display the full number you have to set the number format to 0 (right click, format cell, choose custom type '0'). And when you do that, the full number is displayed. If you don't set a...
Delphi System.Zip xlsx LibreOffice
excel,delphi,zip,xlsx,libreoffice
i used XE2 System.Zip to create both ODS and XLSX and OpenOffice.org opens them fine can you ask your xlsx-generating component to use another zip engine ? Now that the topic starter reported he made zip repack and uploaded both files to compare - i removed most part of my...
importdata to large matrix
performance,matlab,import,xlsx
The fastest way would be: using xlsread function for reading the data; having also MS Excel installed (is not mandatory, but it helps with the speed and the data loading options). So, try this: [file_name, path_name] = uigetfile('*.xlsx','XLSX Files'); [num, txt, ~] = xlsread(fullfile(path_name, file_name)); After this, you will have...
SQL Developer won't attempt to import an .xlsx file because it's too large
import,heap,oracle-sqldeveloper,xlsx
I tried looking into SQL*Loader. Apparently you should be able to right click a table > Import Data > next and there should be an option to generate SQL*Loader files. Unfortunately, not only did the import wizard not open with my large .xlsx files, the SQL*Loader option was not even...
Open xls and xlsx with Apache POI
java,excel,apache-poi,xls,xlsx
This will do the work: Workbook wb = WorkbookFactory.create(myExcelFile); Then you can check the exact type created by the factory: if (wb instanceof HSSFWorkbook) { // do whatever } else if (wb instanceof SXSSFWorkbook) { // do whatever } else if (wb instanceof XSSFWorkbook) { // do whatever } Anyway...
How do I include empty columns when reading an xlsx spreadsheet into R?
If you're willing to use the XLConnect package rather than xlsx, the function XLConnect::readWorksheet allows you to specify the startCol, endCol, and autofitCol arguments, which would solve this issue for you: library(XLConnect) ## # wb1 <- loadWorkbook("~/tmp/tmp1.xlsx", create = FALSE) # wb2 <- loadWorkbook("~/tmp/tmp2.xlsx", create = FALSE) df1 <- readWorksheet(...
read in from read only xlsm file using java apache poi
I copied your code and gave the file a proper path and it worked. My version: FileInputStream file = new FileInputStream(new File("C:\\Users\\user\\Desktop\\filet.xlsm")); System.out.println("found file"); XSSFWorkbook workbook = new XSSFWorkbook(file); System.out.println("in workbook"); XSSFSheet sheet = workbook.getSheet("Shipments"); System.out.println("got sheet"); I think it has something to do with your pathing, or the file...
How can I import data from excel (xlsx, csv) to a HTML page using JavaScript? [closed]
javascript,html,excel,csv,xlsx
You can use Alasql JavaScript SQL library, which has special functionality to read XLSX files and put in HTML page. It uses js-xlsx library to read XLSX files. Disclaimer: I am the author of Alasql. In this example: Alasql takes Excel file from cdn.rawgit.com (you can replace it with your...
While using JRXlsxExporter, the file is not getting generated with proper extension
I found the answer for my question. This issue was occurring because in the web.xml file the mime mapping for "xlsx" format was missing. xlsx application/vnd.openxmlformats-officedocument.spreadsheetml.sheet I did this and it worked....
How to get header and data from Models in Python?
python,django,model,xlsx,openpyxl
Ok, I am going to try it one more time. If you cannot explain your question clearly, I cannot help you. To modify the model's column name in database, do this: from django.db import models class MyModel(models.Model): gender = models.CharField(db_column=u'column_name', max_length=10) If you don't want to change the database's column...
List of data.frame's to individual excel worksheets - R
Specify sheet name for each list element. library(xlsx) file <- paste("usarrests.xlsx", sep = "") write.xlsx(USArrests, file, sheetName = "Sheet1") write.xlsx(USArrests, file, sheetName = "Sheet2", append = TRUE) Second approach as suggested by @flodel, would be to use addDataFrame. This is more or less an example from the help page of...
How to set custom header names with ALASQL and XLSX
I managed to customize the headers by using plain SQL: alasql('SELECT firstName AS FirstName INTO XLSX("test.xlsx", ?) FROM ?', [options, $scope.users]); That worked, the header for the firstName would be FirstName....
import time stamps matlab
What you get is a representation of the time as a single value. Luckily, for times there is no difference between excel and matlab, both use fractions of a full day. (while for dates there is a different "zero") To get back minutes:seconds use: datestr(a,'MM:SS') Please note that MM is...
Problems opening .xlsx file created with EPPlus and zipped in a folder with ICSharpCode.SharpZipLib
c#,asp.net,xlsx,epplus,sharpziplib
You have to rewind memory stream before you can read something (after write operation file pointer is that its end): ms.Seek(0, SeekOrigin.Begin) ms.Read(buffer, 0, buffer.Length); That said a MemoryStream is nothing more than a byte array so you don't even need to allocate and read a new one then this...
CSVs to multi-sheet XLS without Excel installed. Powershell
if you work with the openxml format *.xlsx you can manipulate excel documents without having to have office installed. There are a few powershell modules(powertools,epplus etc) you can use to accomplish what you want to do. Here is a solution using a powershell wrapper that I wrote for Spreadsheetlight: Create...
Axlsx_rails. Set height of multiple rows at once
The best I know of is to set the height on each row: sheet.rows.values_at(1,2,5,6).each {|row| row.height = 40} If necessary you can gain access to the sheets through the workbook: sheet = workbook.worksheets.first # or sheet = workbook.sheet_by_name("Sheet Name") Whichever statement comes later has priority (meaning it isn't like CSS...
Write a data frame to a .xls file with a title
You could convert your source data.frame to a text matrix, prefix whatever title you want, and write that to file. For example, given this data.frame: dat <- data.frame(Fruit=c('Apple', 'Banana'), Quantity=1:2, Notes=c('Hello', 'Some Text')) You could use a function like this: text_matrix <- function(dat, table_title) { rbind(c(table_title, rep('', ncol(dat)-1)), # title...
Sorting XLSX data and writing to CSV
I solved it. Inside the sorting code, I added print ('-'*20 + '', 'Top Twenty Values', '' + '-'*20 ) print ('Value [count]') for val, cnt in counts.most_common(20): print ('%s [%s]' % (val, cnt)) with open('test.csv', 'a') as f: writer = csv.writer(f, delimiter=',', lineterminator='\n') writer.writerow([val]) ...
Using Open Xml to create xlsx file for Excel 2013
c#,excel,.net-3.5,openxml,xlsx
Dates are stored as numbers with type number, but with a style applied to them. Booleans are also stored as numbers, but with type boolean. private Cell CreateCellRow(object value) { Cell cell = new Cell(); cell.CellReference = GetCellLocation(); IncreaseCellColumn(); if (value == null) { cell.DataType = CellValues.InlineString; cell.InlineString = new...
How to disable the scientific notation when working with Perl and Excel
As alluded to by @toolic, you can do something like this: my $format = $workbook->add_format( ); $format->set_num_format( '#' ); $worksheet->write_number( 'A1', 1234567890, $format ); That will specify "no decimal places" as your format. See How to control and understand settings in the Format Cells dialog box in Excel for formatting...
Perl 5.10 with Spreadsheet-ParseXLSX-0.17?
In general you can get along fine by copying the perl modules from a distribution into a directory of your choice on the server, adding said directory to the PERL5LIB environment variable. Observe the local directory structure the distribution defines for its files. In the case of Spreadsheet::Parse, that would...
Stuck on import wizard SQL Server 2008 R2 (from Excel)
sql,excel,import,sql-server-2008-r2,xlsx
Are you copying data from a workbook instead of a single spreadsheet? It may be that the import simply does not know what to do with the blank spreadsheets also contained in the workbook. Thanks Jeff...
Code to read xlsx sheet into a table in a SQL Server database
This should do... select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 8.0;HDR=NO;Database=T:\temp\Test.xlsx','select * from [sheet1$]') But we aware, sometimes this just wont work. I had this working for local admins only. There is a way to do this using SSIS as well....
setBoldweight is not working
As per the valuable comment of Gagravarr, I have changed the code: Row rowhead= sheet.createRow((short)0); Font f = wb.createFont(); f.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle cs = wb.createCellStyle(); cs.setFont(f); Cell cell; cell = rowhead.createCell((short) 0); cell.setCellValue("SRNum"); cell.setCellStyle(cs); cell = rowhead.createCell((short) 1); cell.setCellValue("Name"); cell.setCellStyle(cs); ...
csv to xlsx - when import its always blank?
python,csv,xlsx,python-3.4,xlsxwriter
You have to close workbook to make it work: import csv from xlsxwriter.workbook import Workbook workbook = Workbook("test.xlsx") worksheet = workbook.add_worksheet("Raw_Data") with open("C:\Console2\\csv.test",'r') 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() See here for an example....
Import .xlsx file with PHP
You can use PHPExcel to read XLSX. <?php $objPHPExcel = PHPExcel_IOFactory::load('InOut.xlsx'); $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true); var_dump($sheetData); ...
Pandas: Always selecting the first sheet/tab in an Excel Sheet
python,excel,python-3.x,pandas,xlsx
The first sheet is automatically selected when the Excel table is read into a dataframe. To be explicit however, the command is : import pandas as pd fd = 'file path' data = pd.read_excel( fd, sheetname=0 ) ...
Android - docx4j build issue
android,build,docx,xlsx,docx4j
Finally I found the solution. I did add all the jars what they given in the github. So that I got the above exception. Now I removed the "serializer-2.7.1.jar" from the workspace and then build the app. I can convert the docx to html without build issue. It is working...