Questions tagged [xlsx]
536 questions
0
votes
0
answer
5
Views
How to read 2 columns from excel file (xlsx) and write it to resource properties as key=value pair
I am trying to generate messages_[en/da/bg].properties file from the given set for excel (xlsx) files. Each excel sheet is provided with two columns as key-value pair. I have to read each file and write it to generate messages_en.properties file for i18n.
If I have excel sheets with filenames like...
1
votes
1
answer
40
Views
Is there platform-agnostic code in Java to invoke the default app to view document types like PDF, DOCX, JPEG etc.?
I would like to write a Java class (or method) whose responsibility is to invoke the standard/default application on any platform for opening and handling files of specific formats such as PDF, DOCX, XLSX, JPG etc. and also to know if no suitable application for that file format is available.
Is the...
1
votes
2
answer
1.9k
Views
How can I add blank top rows to an xlsx file using the xlsx package?
I would like to add two blank rows to the top of an xlsx file that I create.
so far I have tried:
library("xlsx")
fn1
1
votes
0
answer
6
Views
XLSX client save from backend api response: binary string, application/octet-stream
I need to save XLSX file on frontend that im getting from XHR to api.
In api response i got this headers:
Content-Type: application/octet-stream
content-disposition: attachment; filename = OrdersList-253CREATED0.xlsx
And part of response body:
PKõzMdocProps/core.xmlMKÄ0ïý!÷vVd-mQÅ++ÞB:¶Å...
1
votes
1
answer
438
Views
Store text as a number in Excel
I'm trying to make a function that exports an XLSX file based on a template. I currently unzip the template file, go through all the XML files looking for the worksheets, and then do a global find-and-replace on the appropriate strings - placeholder_1, placeholder_2 and so on.
This does work but whe...
1
votes
1
answer
1.1k
Views
Axlsx_rails. Set height of multiple rows at once
I'm generating *.xlsx files using axlsx_rails based on xlsx gem.
I'm setting height for single row like this:
sheet.add_row [1,2,3,4,5,6], :style => predefined_style, :height =>
14.3
How could I set height for a batch of rows, and if it possible, which declaration has higher priority?
1
votes
2
answer
1.3k
Views
Read Excel's internal calculated values with PHPExcel
I'm using PHPExcel library to read Microsoft Office Excel xlsx files. My xlsx has only one row:
As I tried xlsx is simply a zip file which contains xml files. I unzipped my xlsx file
:~> unzip w2.xlsx -d w2
Archive: w2.xlsx
inflating: w2/_rels/.rels
inflating: w2/docProps/app.xml
inflating: w2/docP...
1
votes
1
answer
315
Views
Pandas: Exporting multiple DataFrames to multiple xlsx files
I have multiple DataFrames (DF), such as
df1
df2
...
df24
and I would like to export each DF to a separate xlsx file, such as
'output_T1.xlsx' for df1,
'output_T2.xlsx' for df2
...
'output_T24.xlsx' for df24.
Although I know how to do it with to_csv:
df[i].to_csv('output_T%s.csv' %(1+i), index...
1
votes
2
answer
1.1k
Views
Performance : Writing oracle ResultSet into XLSX using Java, Apache-POI
I need to write 600-700k records into xlsx file using Apache POI.
the code I am presently using is :
public void writeRecords(ResultSet rs) {
try{
SXSSFWorkbook wb = new SXSSFWorkbook();
wb.setCompressTempFiles(true);
SXSSFSheet sh = (SXSSFSheet)wb.createSheet("Sheet 1");
Row row = null;
int numC...
1
votes
2
answer
750
Views
Write .xlsx in R with double header
Is it possible to write a data.frame/data.table to a .xlsx with a double header? My data contains numeric, date and string columns. Please find a example below.
example
1
votes
2
answer
877
Views
Receiving message of issue with content in Excel File
I am currently building an Excel file by hand using OpenXml. I'm in the process of adding the sheets, however, I have come across an issue. I have a loop that adds the names of each sheet in but once it runs and I try to open the file, I get the following message:
"We found a problem with some con...
1
votes
2
answer
492
Views
Reading horizontal (row-based) data from xlsx files into R data frames
This is a "let's try another way" post that is related to this one:
Is it possible to define/modify a reading function that
can handle the fact that data stored in an xlsx file is row-based (i.e. each row represents a variable)
and transforms it accordingly so it can be stored in a column-based da...
1
votes
2
answer
322
Views
How OpenXmlReader.Skip supposed to work?
From documentation:
Skips the child elements of the current node.
Here is code:
var reader = DocumentFormat.OpenXml.OpenXmlReader.Create(worksheetPart);
// Header
while (reader.Read())
{
if (reader.ElementType == typeof (Row)) // reader is at row r="1"
{
headers = GetHeaders((Row)reader.LoadCurrentE...
1
votes
1
answer
2.8k
Views
xlsxwriter - how to create from csv file
I'm going to poll controllers in our data centers and output all of them to a csv file. The python tool, xlsxwriter, looks to be the best for it. However, I don't see any mention of how to simply take a csv file and convert it to xlsx.
Xlsxwriter seems to be great for making an xlsx file based on th...
2
votes
1
answer
1.8k
Views
ImportError: Install xlrd >= 0.9.0 for Excel support when using pd.readexcel to read .xlsx file : never happened before
Something strange is going on. Just today when trying to read in a dataframe from an xlsx file:
import pandas as pd
df = pd.read_excel('vlnew.xlsx',sheet_name='Sheet1')
I am getting the following error:
ImportError: Install xlrd >= 0.9.0 for Excel support
I am fully aware that plain and simple the...
1
votes
1
answer
4.3k
Views
setBoldweight is not working
I was trying to add some styles to the excel sheet, I have generated using apache poi. Here is the code, I tried to make the first row in BOLD.
SXSSFWorkbook wb=new SXSSFWorkbook();
Sheet sheet = wb.createSheet("Excelx");
Row row= sheet.createRow((short)0);
row.createCell((short) 0).setCellValue("SR...
3
votes
1
answer
8.1k
Views
read.xlsx 'Error in loadWorkbook(file): Cannot find file' R
I am trying to read in a number of Excel files into R using read.xlsx using the xlsx package but when I do so I am getting the following error:
Error in loadWorkbook(file) : Cannot find id100.xlsx
First I list the files in the directory:
> files files
[1] "id100.xlsx" "id101.xlsx" etc...
> files[1]...
3
votes
1
answer
1.4k
Views
R - Cannot install xlsxjars, 64-bit Windows but trying to load i386
I have been at this problem for far too long now, any help welcomed. I finally got rJava installed and loaded, and as part of it I installed the 64-bit JRE and JDK. R version 3.2.2, 64-bit, running on 64-bit Windows 7.Trying to install xlsxjars (for xlsx package) gives a long error:
> install.packa...
3
votes
1
answer
2k
Views
How to read an .xls file that is encrypted with R?
Typically I use the readxl package (which is quite good) for reading excel files:
read_excel(path = 'ihateexcel.xls', sheet = 1)
However, the session will essentially freeze if I try and read an encrypted .xls file. Obviously don't want to save the file as .csv as I'll lose the encryption or have t...
1
votes
3
answer
16.6k
Views
java.lang.ClassNotFoundException: org.apache.poi.xssf.usermodel.XSSFWorkbook
when i tried to create a workbook in normal java class it is creating but when i tried to run the same on servlet it is throwing exceptions
Normal java class
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.poi.s...
12
votes
1
answer
26k
Views
How to fix OutOfMemoryError (Java): GC overhead limit exceeded in r? [duplicate]
This question already has an answer here:
“Out of Memory Error (Java)” when using R and XLConnect package
7 answers
I have to read a file in a list of folders and save data in R.
I use following code for my test data and it works. When I use the code for the actual data then
I get this error
Er...
3
votes
3
answer
3.4k
Views
Converting XLSX to XSL-FO to PDF
Apaches POI has a ExcelToFoConverter class that allows to convert xls files to XSL-FO. Since I use XLSX files, I am worried that I will have data loss when I use this converter. POI does not offer an explicit converter for xlsx files. However, I saw that someone used the ExcelToFoConverter for xlsx...
0
votes
1
answer
20
Views
Set table style. C# - closed xml
I am looking for a way to apply a table style to the inserted data in an excel file.
I use the library closed xml
How can I do it?
Sample table I want to get
using (XLWorkbook wb = new XLWorkbook(excel))
{ IXLWorksheet ws = wb.Worksheets.Last();
string Qry;
using (SqlCommand cmd = new SqlCommand(Q...
2
votes
1
answer
952
Views
Convert data to string after reading from a xls or xlsx file in python
I am writing a function which is used to read data from an xls/xlsx file and insert into db. But the problem is after reading the data from the cells of the xls file the value becomes float type.
Below is my sample data from xls file :
Zip Zone
35096
31723 1
35963
36037 D 21
36...
17
votes
1
answer
14.7k
Views
How to set cell width when export .xlsx files with js-xlsx
I am trying to set a fixed column/cell width to my exported excel files with js-xlsx.
EDIT:
Here is the source of js-xlsx: https://github.com/SheetJS/js-xlsx
18
votes
3
answer
6.7k
Views
Django: openpyxl saving workbook as attachment
Hi I have a quick question. I didn't find answer in internet maybe someone of you can help me.
So i want to save workbook as attachment but I don't know how lets see an example :
from openpyxl import Workbook
from openpyxl.cell import get_column_letter
wb = Workbook(encoding='utf-8')
dest_filename =...
18
votes
8
answer
26.5k
Views
Which gem support Import/Export to xlsx file in ruby
I need to read and write to Excelx file with ruby code,
Tried with spreadsheet it does not support xlsx format,
Roo is also to read the file and not write to xlsx.
Is there any gem/plugin which will write to Excelx?
1
votes
2
answer
493
Views
new R user has regression trouble
I am new to R and am trying to run a regression analysis. I have constructed arbitrary vectors with the c() function to learn the plot, lm, fit, abline, and summary functions. That has worked properly, but when trying to regress imported data, I receive the following error message. I don't know what...
2
votes
1
answer
72
Views
Java get the excel cell background color from Apache poi
I am trying to get cell color info in .xlsx file using Apache POI.
Method cellStyle.getFillBackgroundColor() is returning short. How can I convert short to java.awt.Color or any other format(XSSFColor).
Ultimately I want to store the value of cell based on its background color.
Workbook workbook = W...
3
votes
0
answer
567
Views
Cannot open the file after writing xlsx file using R openxlsx package
At first, I tried to read and write xlsx files in R (while comparing the output between the xlsx and openxlsx packages).
I work on mac os.
It worked well to read xlsx files using the read.xlsx() from both packages.
However, when it comes to writing a new file, only the xlsx::write.xlsx() worked....
2
votes
0
answer
626
Views
Node.js writing into an existing excel file
I’m doing some testing with some node modules to try to insert data into an existing .xlsx template file. I can’t seem to find a module that lets me just insert data into an existing template file. All of the modules essentially let you read a xlsx file into the app, than modify it, and than wri...
4
votes
1
answer
5k
Views
Creating Transform stream using ExcelJS for writing xlsx
I'm using ExcelJS module and writing wrapper for my purposes, which implements Transform Stream API (yes, node version is 0.10.40).
ExcelJS has a stream API and according to example in ExcelJS module we can use one like this (execute node index.js > test.xlsx):
var stream = require('stream'),
Excel...
3
votes
1
answer
1.3k
Views
XLSX get last column?
How can i get the the last column when reading a xlsx file using the Apache POI API? There's a getLastRowNum method but nothing related with number of columns.
Thx for help.
19
votes
3
answer
16.2k
Views
Creating Excel (.xlsx) files in Cocoa
I'm writing a Cocoa application and I'm trying to export to the Excel XML format (ISO/IEC 29500-1) which is basically a zip file with a bunch of XML files in it named with a .xlsx extension.
I've tried generating AppleScript and using NSAppleScript to create the Excel file, which works, but is slow...
2
votes
0
answer
836
Views
Apache POI vs JS XLS - What's the best for an Excel Sheet driven application?
I'm developing an Excel Sheet driven application.
All the data must be retrieved from an specific and default Excel Sheet that will contain new rows and alterations in existing rows. I need to merge specific columns with the existing data, and preserving the other columns.
The basic functions are:
I...
4
votes
1
answer
1.1k
Views
Failure when using java.util.zip.* to unzip an xlsx blob created with Apache POI
The following code is a test program that creates an xlsx blob
using Apache POI and unzips the blob (xlsx is in zip format)
to check some values. The code is based on Stack Overflow
questions but I have not been able to get unzipping to work
properly. A requirement for this is that the filesystem mu...
1
votes
1
answer
4.8k
Views
convert .csv to a .xlsx from CLI
I found this for the opposite conversion Convert xlsx to text CSV via command line only on Linux
But I have absolutely no understanding of that line of code. Can anyone tell me how to convert from a csv to a xlsx from the CLI?
Thanks in advance for any help.
6
votes
1
answer
13.3k
Views
read.xlsx and colClasses
Does anyone know why argument colClasses does not seem to work in read.xlsx?
I create a sample *.xlsx file:
> library(xlsx)
> d1 = data.frame(A=LETTERS[1:3], B=letters[1:3], C=1:3, D=c(1.1, NA, NA))
> str(d1)
'data.frame': 3 obs. of 4 variables:
$ A: Factor w/ 3 levels "A","B","C": 1 2 3
$ B: Fac...
5
votes
1
answer
577
Views
How do I set a workbook-wide default format with Excel::Writer::XLSX?
Is it possible to change or declare a default font with Excel::Writer::XLSX?
I can set it for charts and cells every time I create or add them. But I think there should be a more simple way.
2
votes
3
answer
1.1k
Views
openpyxl readonly use_iterators
I have some huge Excel files, but I'm stymied with even a "modest" one (50 Mbytes). I do need to skip over the first two rows, but I don't think that's the slowdown. Can you think of something else?
wb = load_workbook(MyFile,read_only=True)
ws = wb.active
NDepth = ws.max_row-2
NTime = ws.max_column...