EzDevInfo.com

How to quote a quote with the CONCATENATE function in OOCalc

In OOCalc I want to use the CONCATENATE function to add quotes to each string in column A. So in cell B1 I want to do:

=CONCATENATE("\"",A1,"\"")

OOCalc does not like this, or without the escaping backslash. Does anyone know how to do this, or what an alternative method might be?

Thanks

Android - Google Spreadsheet Api

I can't get which libs i should use for developing an Android app that menages Google spreadsheet. I need connecting, copying, editing, reading from a user spreadsheet but i can't understand today which is the way.

Google Drive Api : https://developers.google.com/drive/
Google Spreadsheet Api: https://developers.google.com/google-apps/spreadsheets/
Google APi java client: http://code.google.com/p/google-api-java-client/

Which is the correct one?

Excel: Set background colour of cell to rgb value of data in cell

I have a column containing rgb values, eg

127,187,199
67,22,94

In Excel, is there any way I can use this to set the background colour of the cell?

Spreadsheets that use a functional-programming language

I'd like to know if there exists a spreadsheet application which uses an existing functional-programming language to define functions.

I've already heard about Resolver One which uses python, but I'm more interested in anything which uses a purely functional language like Haskell.

Thanks

Counting number of occurrences in column?

What would be a good approach to calculate the number of occurrences in a spreadsheet column? Can this be done with a single array formula?

Example (column A is input, columns B and C are to be auto-generated):

|   A   |   B   |   C   |
+-------+-------+-------+
| Name  | Name  | Count |
+-------+-------+-------+
| Joe   | Joe   |     2 |
| Lisa  | Lisa  |     3 |
| Jenny | Jenny |     2 |
| Lisa  |       |       |
| Lisa  |       |       |
| Joe   |       |       |
| Jenny |       |       |

How do I avoid the "#DIV/0!" error in Google docs spreadsheet?

I have a column with average(K23:M23) that starts out with #DIV/0! when the K23 through M23 cells are empty. Preferably I'd like to only do the average of cells that contain non-zero, non-blank values. I think it's possible using the query command:

https://docs.google.com/support/bin/answer.py?hl=en&answer=159999

But their example doesn't help me.

paste excel data into html table

Using Javascript, how do I create an HTML table that can "accept" numeric matrix data from excel (or google spreadsheet), via "copy" in the spreadsheet and then "paste" into the table in the browser.

Thanks.

Selecting the last value of a column

I have a spreadsheet with some values in column G. Some cells are empty in between, and I need to get the last value from that column into another cell.

Something like:

=LAST(G2:G9999)

except that LAST isn't a function :-)

jQuery Spreadsheet/Grid plugin with copy/paste from/to Excel [closed]

I am looking for a jQuery plugin for something like spreadsheet or grid. I have found a lot of jQuery Grid plugins and one spreadsheet plugin. But I would like functionality with copy/paste cells from/to Excel. Something like here Any suggestions?

Python - Write to Excel Spreadsheet

I am new to Python. I need to write some data from my program to a spreadsheet. I've searched online and there seems to be many packages available (xlwt, XlsXcessive, openpyxl). Others suggest to write to a csv file (never used csv & don't really understand what it is).

The program is very simple. I have two lists (float) and three variables (strings). I don't know the lengths of the two lists and they probably won't be the same length.

I want the layout to be as in the picture below:

The pink column will have the values of the first list and the green column will have the values of the second list.

So what's the best way to do this? Thanks.

P.S. I am running Windows 7 but I won't necessarily have Office installed on the computers running this program.

EDIT

import xlwt

x=1
y=2
z=3

list1=[2.34,4.346,4.234]

book = xlwt.Workbook(encoding="utf-8")

sheet1 = book.add_sheet("Sheet 1")

sheet1.write(0, 0, "Display")
sheet1.write(1, 0, "Dominance")
sheet1.write(2, 0, "Test")

sheet1.write(0, 1, x)
sheet1.write(1, 1, y)
sheet1.write(2, 1, z)

sheet1.write(4, 0, "Stimulus Time")
sheet1.write(4, 1, "Reaction Time")

i=4

for n in list1:
    i = i+1
    sheet1.write(i, 0, n)



book.save("trial.xls")

I wrote this using all your suggestions. It gets the job done but it can be slightly improved. How do I format the cells created in the for loop (list1 values) as scientific or number? I do not want to truncate the values. The actual values used in the program would have around 10 digits after the decimal.

Are there any spreadsheet widgets for Android?

I have an Excel file that I want to use in my application. Using jexcel I read and write an Excel file in the application, but I don't know how to set up the view for that file in my application. Are there any spreadsheet widgets available for Android to show an Excel file?

Google Docs Spreadsheet Automatic Coloring [closed]

I am looking for a way to automatically change the color of the minimum cell in a row in google docs spreadsheet.

So, for a table like

1 | 2 | 3
4 | 2 | 1
2 | 1 | 6

It would color all the cells with '1' in them.

Recommend a Python library to read Excel XLS files [closed]

What Python library can be recommended to read Excel XLS files?

Cell styles in OpenXML spreadsheet (SpreadsheetML)

I've generated a .xlsx spreadsheet in C# using the OpenXML SDK, but can't figure out how to get cell styles working. I've been studying files produced by Excel, and can't quite figure out how it's done.

Right now, I'm creating a fill, creating a CellStyleFormat that points at the fill, creating a CellFormat that points at the index of the CellStyleFormat, then creating a CellStyle that points to the CellFormat.

Here's the code I'm using to generate the document:

Console.WriteLine("Creating document");
using (var spreadsheet = SpreadsheetDocument.Create("output.xlsx", SpreadsheetDocumentType.Workbook))
{
    Console.WriteLine("Creating workbook");
    spreadsheet.AddWorkbookPart();
    spreadsheet.WorkbookPart.Workbook = new Workbook();
    Console.WriteLine("Creating worksheet");
    var wsPart = spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
    wsPart.Worksheet = new Worksheet();

    var stylesPart = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
    stylesPart.Stylesheet = new Stylesheet();
    stylesPart.Stylesheet.Fills = new Fills();

    // create a solid red fill
    var solidRed = new PatternFill() { PatternType = PatternValues.Solid };
    solidRed.AppendChild(new BackgroundColor { Rgb = HexBinaryValue.FromString("FF00FF00") });

    stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill() { PatternType = PatternValues.None } });
    stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = solidRed });
    stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
    stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat { FillId = 0, ApplyFill = false });
    stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat { FillId = 1, ApplyFill = true });
    stylesPart.Stylesheet.CellFormats = new CellFormats();
    stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0 });
    stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 1 });
    stylesPart.Stylesheet.CellStyles = new CellStyles();
    stylesPart.Stylesheet.CellStyles.AppendChild(new CellStyle { Name = "None", FormatId = 0 });
    stylesPart.Stylesheet.CellStyles.AppendChild(new CellStyle { Name = "Solid Red", FormatId = 1 });

    stylesPart.Stylesheet.Save();

    Console.WriteLine("Creating sheet data");
    var sheetData = wsPart.Worksheet.AppendChild(new SheetData());

    Console.WriteLine("Adding rows / cells...");

    var row = sheetData.AppendChild(new Row());
    row.AppendChild(new Cell() { CellValue = new CellValue("This"),  DataType = CellValues.String });
    row.AppendChild(new Cell() { CellValue = new CellValue("is"),    DataType = CellValues.String });
    row.AppendChild(new Cell() { CellValue = new CellValue("a"),     DataType = CellValues.String });
    row.AppendChild(new Cell() { CellValue = new CellValue("test."), DataType = CellValues.String });

    sheetData.AppendChild(new Row());

    row = sheetData.AppendChild(new Row());
    row.AppendChild(new Cell() { CellValue = new CellValue("Value:"),   DataType = CellValues.String });
    row.AppendChild(new Cell() { CellValue = new CellValue("123"),      DataType = CellValues.Number });
    row.AppendChild(new Cell() { CellValue = new CellValue("Formula:"), DataType = CellValues.String });
    row.AppendChild(new Cell() { CellFormula = new CellFormula("B3"),   StyleIndex = 1 }); // 

    Console.WriteLine("Saving worksheet");
    wsPart.Worksheet.Save();

    Console.WriteLine("Creating sheet list");
    var sheets = spreadsheet.WorkbookPart.Workbook.AppendChild(new Sheets());
    sheets.AppendChild(new Sheet() { Id = spreadsheet.WorkbookPart.GetIdOfPart(wsPart), SheetId = 1, Name = "Test" });

    Console.WriteLine("Saving workbook");
    spreadsheet.WorkbookPart.Workbook.Save();

    Console.WriteLine("Done.");
}

Here's the generated XML:

workbook.xml

<?xml version="1.0" encoding="utf-8"?>
<x:workbook xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:sheets>
    <x:sheet name="Test" sheetId="1" r:id="Rbad86b8c80844a16" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" />
  </x:sheets>
</x:workbook>

styles.xml

<?xml version="1.0" encoding="utf-8"?>
<x:styleSheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:fills>
    <x:fill>
      <x:patternFill patternType="none" />
    </x:fill>
    <x:fill>
      <x:patternFill patternType="solid">
        <x:bgColor rgb="FF00FF00" />
      </x:patternFill>
    </x:fill>
  </x:fills>
  <x:cellStyleXfs>
    <x:xf fillId="0" applyFill="0" />
    <x:xf fillId="1" applyFill="1" />
  </x:cellStyleXfs>
  <x:cellXfs>
    <x:xf xfId="0" />
    <x:xf xfId="1" />
  </x:cellXfs>
  <x:cellStyles>
    <x:cellStyle name="None" xfId="0" />
    <x:cellStyle name="Solid Red" xfId="1" />
  </x:cellStyles>
</x:styleSheet>

worksheets/sheet.xml

<?xml version="1.0" encoding="utf-8"?>
<x:worksheet xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:sheetData>
    <x:row>
      <x:c t="str"><x:v>This</x:v></x:c>
      <x:c t="str"><x:v>is</x:v></x:c>
      <x:c t="str"><x:v>a</x:v></x:c>
      <x:c t="str"><x:v>test.</x:v></x:c>
    </x:row>
    <x:row />
    <x:row>
      <x:c t="str"><x:v>Value:</x:v></x:c>
      <x:c t="n"><x:v>123</x:v></x:c>
      <x:c t="str"><x:v>Formula:</x:v></x:c>
      <x:c s="1"><x:f>B3</x:f></x:c>
    </x:row>
  </x:sheetData>
</x:worksheet>

The last cell of the last row is where I'm trying to add the style.

This all validates properly when I run it through the OpenXML SDK Productivity Tool, but, when I attempt to open the file in Excel, I get the following error:

Repaired Records: Format from /xl/styles.xml part (Styles)

The spreadsheet then shows, but the fill isn't applied.

Any idea how to go about fixing this?

How to generate an Excel document with multiple worksheets from PHP?

I want to generate an MS Excel file from PHP. I know one can do something like this:

header ( "Content-type: application/vnd.ms-excel" );
header ( "Content-Disposition: attachment; filename=foo_bar.xls" );

But it will generate a file with just one Sheet. What I want is generating a file with multiple sheets. How can I do that? Maybe there's a third party library, but I haven't found too much.