EzDevInfo.com

Setting styles in Openpyxl

I need advice on setting styles in Openpyxl.

I see that the NumberFormat of a cell can be set, but I also require setting of font colors and attributes (bold etc). There is a style.py class but it seems I can't set the style attribute of a cell, and I don't really want to start tinkering with the openpyxl source code.

Has anyone found a solution to this?

Convert xlsx file to csv using batch

How do you convert multiple xlsx files to csv files with a batch script?

Reading xlsx files using Python [closed]

I read last year that xlrd was being updated to be able to read xlsx files (Excel 2007, 2010). Is there any news on this development, or the use of other Python utilities?

Excel "External table is not in the expected format."

I'm trying to read an Excel (xlsx) file using the code shown below. I get an "External table is not in the expected format." error unless I have the file already open in Excel. In other words, I have to open the file in Excel first before I can read if from my C# program. The xlsx file is on a share on our network. How can I read the file without having to open it first? Thanks

string sql = "SELECT * FROM [Sheet1$]";
string excelConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathname + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";

using (OleDbDataAdapter adaptor = new OleDbDataAdapter(sql, excelConnection)) {
    DataSet ds = new DataSet();
    adaptor.Fill(ds);
}

Read Data from XLSX in c#

I am new to c# and am trying to read an XLSX file in c# with the following code:

string Connection = "Provider=Microsoft.ACE.OLEDB.12.0;DataSource=c:\\Temp\\source.xlsx;Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";

//code to read the content of format file 
OleDbConnection con = new OleDbConnection(Connection);
OleDbCommand command = new OleDbCommand();

DataTable dt = new DataTable();
OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Tabelle1$]", con);

myCommand.Fill(dt);
Console.Write(dt.Rows.Count);

I get t a correct count from the output, but I have 2 more questions:

1.How do make a select where statement (how to access the rows)?

 select * from [Tabelle1$] where A = '123' (A being an existing Excel row)

will throw an error mentioning wrong parameters...

2.can anyone supply me with a tutorial link or short sample how to access the data?

Parsing an Excel file in C#, the cells seem to get cut off at 255 characters... how do I stop that?

I am parsing through an uploaded excel files (xlsx) in asp.net with c#. I am using the following code (simplified):

string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connString);
DataSet ds = new DataSet();
adapter.Fill(ds);
adapter.Dispose();
DataTable dt = ds.Tables[0];
var rows = from p in dt.AsEnumerable() select new { desc = p[2] };

This works perfectly, but if there is anything longer than 255 characters in the cell, it will get cut off. Any idea what I am doing wrong? Thank you.

EDIT: When viewing the excel sheet, it shows much more than 255 characters, so I don't believe the sheet itself is limited.

Missing worksheets and page size issue when excel (.xlsx) convert to pdf (.pdf) using open office

I have created an application using JodConverter and Open-Office for converting an excel(.xlsx) to PDF, The application works fine but i am facing two problems

1. The pages of output PDF is in the form of A4 size, since because of that certain worksheet content have been sliced off. since i want each worksheet of the excel as complete as in one page what ever the size.

2. The no of worksheets were missing, say if my excel has 8 worksheet i am getting only two or three within the PDF output

Even if we tried to convert to pdf directly from open-office, its giving the above similar issues

Excel File - ss1.xlsx

Output PDF - work.pdf

can anyone please tell me some solution for this

My code is as given below

public class MyConverter {

    public static void main(String[] args) throws ConnectException {
        File inputFile = new File("C:/Users/Work/Desktop/ss1.xlsx");
        File outputFile = new File("C:/Users/Work/Desktop/work.pdf");

        // connect to an OpenOffice.org instance running on port 8100
        OpenOfficeConnection connection = new SocketOpenOfficeConnection(8100);
        connection.connect();

        // convert
        DocumentConverter converter = new OpenOfficeDocumentConverter(connection);
        converter.convert(inputFile, outputFile);

        // close the connection
        connection.disconnect();
    }

Import password-protected xlsx workbook into R

How can I import a worksheet from a password-protected xlsx workbook into R?

I would like to be able to convert an Excel worksheet into a csv file without having to go through Excel itself.

It is possible for xls workbooks using the perl-based function xls2csv from package gdata. I gather that the problem is Spreadsheet::XLSX doesn't support it.

There are a variety of functions and packages for importing non-encrypted xlsx workbooks, but none seems to address this issue.

At present it seems the only alternatives are to go through Excel or figure out how to write perl code that can do it.

openpyxl cell style not reporting correctly

Using the python library openpyxl I am reading an XLSX file created in excel 2007. it is empty apart from cell A1 which is coloured yellow and has the value "test" written in it. I can easily retrieve the value from that cell, however when I attempt to determine the fill colour I get the following results:

this_sheet.cell("A1").style.fill.start_color 

returns "FFFFFF"

this_sheet.cell("A1").style.fill.end_color 

returns "FF0000"

Testing this on other blank cells I get exactly the same results, and trying to retrieve the font style information keeps returning calibri size 11 (our system default).

Am I going about this all wrong? Is there an alternative method i should be using? Any help would be greatly appreciated.

Thanks!

How to convert Excel File-Format to G-Sheet by Drive API upload?

I can upload and convert CSV files by Drive API without any problem, but when sending XLS (rendered with PEAR XLS-Writer) or XLSX (rendered with PHPExcel) the Drive API complains:

"Error calling POST https://www.googleapis.com/upload/drive/v2/files?convert=true&uploadType=multipart&key=123: (500) Internal Error"

MIME type "application/vnd.ms-excel" apparently works better than "application/vnd-excel"

When I write out XLSX with content-type "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" it's quite the same ... it gets stuck when loading the preview, when convert=true.

When creating files with MS Excel and uploading them manually it works fine.

I can insert/update, download/open the files (all fine) - but I want to convert them.

With all the tests performed I guess the problem is related to the created file-format - or MIME type.

Any hints why the conversion might fail...

"Did ever someone managed to convert rendered XLS/XLSX?"

The question basically is: What is the expected file-format + MIME-type in order to convert to gSheet?

I'd accept any answer for the bounty, which provides a way to convert a multi-dimensional array to gSheet with several pages, in case it's not (yet) possible via the Drive API (for sure that would be the preferred way).

This is a Google Drive API question - there's just no individual tag for this available.

Read excel xlsx file using simplexlsx in php

I am using simplexlsx.class.php to read xlsx file type. It's giving problems when the file contain date field in the excel file.

Sample output:

In the file data:

Day Date Thursday 2/2/2012 Friday 2/3/2012

Program output:

Day Date

Thursday 40941
Friday 40942

It's not give the correct date

<?php

if (isset($_FILES['file'])) {

require_once "simplexlsx.class.php";

$xlsx = new SimpleXLSX( $_FILES['file']['tmp_name'] );

echo '<h1>Parsing Result</h1>';
echo '<table border="1" cellpadding="3" style="border-collapse: collapse">';

list($cols,) = $xlsx->dimension();

foreach( $xlsx->rows() as $k => $r) {
    if ($k == 0) continue; // skip first row
    echo '<tr>';
    for( $i = 0; $i < $cols; $i++)
    {

        echo '<td>'.( (isset($r[$i])) ? $r[$i] : '&nbsp;' ).'</td>';

    }
    echo '</tr>';
}
echo '</table>';
}

?>
<h1>Upload</h1>
<form method="post" enctype="multipart/form-data">
*.XLSX <input type="file" name="file"  />&nbsp;&nbsp;<input type="submit" value="Parse" />

Openpyxl setting number format

Could please someone show an example of applying the number format to the cell. For example, I need scientific format, form would be like '2.45E+05' but I couldn't figure a way how to do that in openpyxl.

I tried in several ways but they are all reporting errors when saving the workbook.

for example:

    import openpyxl as oxl

    wb = oxl.Workbook()
    ws = wb.create_sheet(title='testSheet')
    _cell = ws.cell('A1')
    _cell.style.number_format = '0.00E+00'

or this (here I'm trying to use some of the predefined number formats, I have also seen there is engineering format in builtins but don't know how to access it:

    nf = oxl.style.NumberFormat.FORMAT_NUMBER_00
    _cell.style.number_format = nf

I have seen this thread: Setting styles in Openpyxl but it doesn't help because I don't have to change any other formatting settings.

EDIT

Here is the error:

C:\Python27\openpyxl\cell.pyc in is_date(self)
    408         """
    409         return (self.has_style
--> 410                 and self.style.number_format.is_date_format()
    411                 and isinstance(self._value, NUMERIC_TYPES))

AttributeError: 'str' object has no attribute 'is_date_format'

In both cases I get the same error.

How to read XLSX file of size >40MB

I am using XSSF of apache-POI to read the XLSX file. I was getting an error java.lang.OutOfMemoryError: Java heap space. Later, increased the heap size using -Xmx1024m for the java class still the same error repeats.

Code:

String filename = "D:\\filename.xlsx";
FileInputStream fis = null;
try {
   fis = new FileInputStream(filename);
   XSSFWorkbook workbook = new XSSFWorkbook(fis);

In the above code segment, the execution stops at XSSFWorkbook and throws the specified error. Can someone suggest better approach to read large XLSX files.

EPPlus - How to use a template

I have recently discovered EPPlus (http://epplus.codeplex.com/). I have an excel .xlsx file in my project with all the styled column headers. I read on their site that you can use templates.

Does anyone know how or can provide code sample of how to use my template.xlsx file with EPPlus? I would like to be able to simply load my data into the rows without messing with the headings.

How to extract plain text from a DOCX file using the new OOXML support in Apache POI 3.5?

On September 28, 2009 the Apache POI project released version 3.5 which officially supports the OOXML formats introduced in Office 2007, like DOCX and XLSX.

Please provide a code sample for extracting a DOCX file's content in plain text, ignoring any styles or formatting.

I am asking this because I have been unable to find any Apache POI examples covering the new OOXML support.