Přeskočit na hlavní obsah

OpenOffice.org - spojení obsahu buněk

Tak se po prosincové dovolené vracím k počítači a první záznam v novém roce se nebude zabývat Javou, ale OpenOffice.org. V konferenci users@cs.openoffice.org se řešilo, jak poskládat dohromady hodnoty buněk ze sloupce Calcu do jednoho stringu s oddělovačem mezi hodnotami.

První nápad, který se objevil bylo využití klasické funkce CONCATENATE: 

=CONCATENATE(A1;"oddelovac";A2;"oddelovac";A3)

Už toto může být dost náročné při více hodnotách ve sloupci. Ale v konferenci přibyl požadavek na ošetření prázdných buněk, které nemají být ve výsledném seznamu. Nový kód byl: 

=CONCATENATE(IF(NOT(A1="");CONCATENATE(A1;",");"");IF(NOT(A2="");CONCATENATE(A2;",");"");IF(NOT(A3="");CONCATENATE(A3;",");""))

Tady už se v tom začíná člověk ztrácet navíc nám za poslední hodnotou zůstává oddělovač. Tak vylepšíme: 

=LEFT(CONCATENATE(IF(NOT(A1="");CONCATENATE(A1;",");"");IF(NOT(A2="");CONCATENATE(A2;",");"");IF(NOT(A3="");CONCATENATE(A3;",");"")); LEN(CONCATENATE(IF(NOT(A1="");CONCATENATE(A1;",");"");IF(NOT(A2="");CONCATENATE(A2;",");"");IF(NOT(A3="");CONCATENATE(A3;",");""))) - 1 )

Tohle už je nepoužitelné. Takže začneme úplně znovu. Do dokumentu si vložíme makro (Nástroje > Makra > Správce maker > OpenOffice.org Basic...) 

Public Function MyConcat(aRange, aSplitter as String)
 Dim tmpResult As String
 Dim i, j as Integer

 tmpResult = ""

 For i = LBound(aRange(),2) To UBound(aRange(),2)
   For j = LBound(aRange()) To UBound(aRange())
     if aRange(j,i) <> "" And aRange(j,i) <> 0 then
       if tmpResult <> "" then
         tmpResult = tmpResult + aSplitter
       end if
       tmpResult = tmpResult + aRange(j, i)
     end if
   Next
 Next

 MyConcat = tmpResult
End Function

A nyní už můžeme vesele použít konstrukci: 

=MyConcat(A1:A3;",")

Více informací o práci s makry můžete najít v OpenOffice.org How-To dokumentech. Doporučuji podívat se hlavně na:

Štítky:

Komentáře

Anonymní píše…
Paráda, moc pomohlo!!!
28. října 2013 v 18:43
Okomentovat

Populární příspěvky z tohoto blogu

Three ways to redirect HTTP requests to HTTPs in WildFly and JBoss EAP

WildFly application server (and JBoss EAP) supports several simple ways how to redirect the communication from plain HTTP to TLS protected HTTPs. This article presents 3 ways. Two are on the application level and the last one is on the server level valid for requests to all deployments. 1. Request confidentiality in the deployment descriptor The first way is based on the Servlet specification. You need to specify which URLs should be protected in the web.xml deployment descriptor. It's the same approach as the one used for specifying which URLs require authentication/authorization. Just instead of requesting an assigned role, you request a transport-guarantee . Sample content of the WEB-INF/web.xml <web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" version="3.1...
Okomentovat
Další informace

Simple TLS certificates in WildFly 18

It's just 2 weeks when WildFly 18 was released. It includes nice improvements in TLS certificates handling through ACME protocol (Automatic Certificate Management Environment), it greatly simplifies obtaining valid HTTPS certificates. There was already a support for the Let's Encrypt CA in WildFly 14 as Farah Juma described in her blog post last year. New WildFly version allows using other CA-s with ACME protocol support. It also adds new switch --lets-encrypt to interactive mode of security enable-ssl-http-server JBoss CLI commands. Let's try it. Before we jump on WildFly configuration, let's just mention the HTTPs can be used even in the default configuration and a self-signed certificate is generated on the fly. Nevertheless, it's not secure and you should not use it for any other purpose than testing. Use Let's Encrypt signed certificate for HTTPs application interface Start WildFly on a machine with the public IP address. Run it on the defaul...
Okomentovat
Další informace

Enable Elytron in WildFly

Steps to enable Elytron in WildFly nightly builds. There is an ongoing effort to bring a new security subsystem Elytron to WildFly and JBoss EAP. For some time a custom server profile named standalone-elytron.xml  existed beside other profiles in standalone/configuration directory. It was possible to use it for playing with Elytron. The custom Elytron profile was removed now.  The Elytron subsystem is newly introduced to all standard server profiles. The thing is, the Elytron is not used by default and users have to enable it in the subsystems themselves. Let's look into how you can enable it. Get WildFly nightly build # Download WildFly nightly build wget --user=guest --password=guest https://ci.wildfly.org/httpAuth/repository/downloadAll/WF_Nightly/.lastSuccessful/artifacts.zip # unzip build artifacts zip. It contains WildFly distribution ZIP unzip artifacts.zip # get the WildFly distribution ZIP name as property WILDFLY_DIST_ZIP=$(ls wildfly-*-SNAPSHOT.zip)...
Okomentovat
Další informace