Generate a Unique Random id on a range of cells - Simple Random Sample

Generate a Unique Random id on a range of cells

Hi this is my method to assign a unique random number whithin the desired range of values to each of the cells in the selected Range.

The uniqueRandomLongMethods has been copied from http://www.cpearson.com/excel/randomnumbers.aspx

 Public Sub attributeRandomRange()  
 Dim myArray As Variant  
 Dim i As Integer  
 Dim requiredRange As Range   
 Set requiredRange = Selection  
   myArray = UniqueRandomLongs(1, requiredRange.Count, requiredRange.Count)  
   i = 1  
   For Each Cell In requiredRange.Cells  
      Cell.Value = myArray(i)  
      i = i + 1  
   Next   
 End Sub  
 Public Function UniqueRandomLongs(Minimum As Long, Maximum As Long, _  
       Number As Long, Optional ArrayBase As Long = 1, _  
       Optional Dummy As Variant) As Variant  
 Dim SourceArr() As Long  
 Dim ResultArr() As Long  
 Dim SourceNdx As Long  
 Dim ResultNdx As Long  
 Dim TopNdx As Long  
 Dim Temp As Long  
 If Minimum > Maximum Then  
   UniqueRandomLongs = Null  
   Exit Function  
 End If  
 If Number > (Maximum - Minimum + 1) Then  
   UniqueRandomLongs = Null  
   Exit Function  
 End If  
 If Number <= 0 Then  
   UniqueRandomLongs = Null  
   Exit Function  
 End If  
 Randomize  
 ''''''''''''''''''''''''''''''''''''''''''''''  
 ReDim SourceArr(Minimum To Maximum)  
 ReDim ResultArr(ArrayBase To (ArrayBase + Number - 1))  
 For SourceNdx = Minimum To Maximum  
   SourceArr(SourceNdx) = SourceNdx  
 Next SourceNdx  
 TopNdx = UBound(SourceArr)  
 For ResultNdx = LBound(ResultArr) To UBound(ResultArr)  
   SourceNdx = Int((TopNdx - Minimum + 1) * Rnd + Minimum)  
   ResultArr(ResultNdx) = SourceArr(SourceNdx)  
   Temp = SourceArr(SourceNdx)  
   SourceArr(SourceNdx) = SourceArr(TopNdx)  
   SourceArr(TopNdx) = Temp  
   TopNdx = TopNdx - 1  
 Next ResultNdx  
 UniqueRandomLongs = ResultArr  
 End Function  

Here I ran the code 3 times. First for A1:A17, then for B1:B17 then C1:C17.

Cheers

Java - Temporary Files in Your Java Application

I spent the 4 last weeks to develop an exportation module in Java. This module, depending of the user selection, generates Microsoft Word and Excel tables.

At first I had the brilliant idea to ask a file name and a preferred location to the users. The idea behind that choice consisted in forcing the user to be somehow tidy and organised :) . In fact it turned out to be really boring in a daily use basis without even talking of the user interface which was overloaded by all theses controls.

Okay that was for the little story, now let's see how I managed my temporary files.

Analyse

1. First of all, I chose a location for all the temporary files generated by the application:C:/Temp/  The folder may be a bad choice since many other application and sysadmin use it as a temporary folder.
2. In order to be sure that we won't delete the files generated by our favorite sysadmin, let's specify a file name format. tmp#########.rtf (Word tables) and tmp#########.xlsx (Excel Tables) This file format will help us to recognise our own files.(Of course if your sysadmin generates files finishing by rtf or xlsx, be more specific)
3. Now that we have some tiny specifications, let's see some code

Code

Generate the Temporary Folder

The first method checks if a directory C:/Temp/ exists. If it exists, the directory is returned if not, the directory is created. If the user who launched the application has no right to create a folder in C:/ a custom exception will be thrown. We catch the exception in our Graphic User Interface layer.

 public static File getTemporaryFileBaseDirectory() throws OpenFile_Error
    {
        String basedirectoryString="C:/Temp/";
        File baseDirectory=new File(basedirectoryString);
        boolean permissiontowrite=true;
        if (!baseDirectory.exists())
            permissiontowrite=baseDirectory.mkdir();
        if (!permissiontowrite)
            throw new OpenFile_Error(String.format("Impossible to create a Temp directory at %s", basedirectoryString),null);
        return baseDirectory;
    } 

Generate the Temporary Files 

The second method will create a temporar y file. This temporary file should have the extension given in the "extension" parameter. Like in the getTemporaryFileBaseDirectory() method, an OpenFile_Error exception can be thrown. This method will get the temporary folder as a file. Then by calling the java.io.File.createTempFile(String prefix, String suffixe, File baseDirectory), we will create a temporaryfile. At the end of the function if no exception has been thrown, the new Temporary file is returned.

    public static File getTemporaryFile(String extension) throws OpenFile_Error
    {
        File baseDirectory= null;
        File TemporaryFile=null;
        try {
            baseDirectory = Util.Files.File_UTIL.getTemporaryFileBaseDirectory();
            TemporaryFile = File.createTempFile("tmp", extension, baseDirectory);
        }catch(OpenFile_Error error1)
        {
            throw error1;
        } catch (IOException e) {
            throw new OpenFile_Error("Error while creating temporary File", e.getStackTrace());
        }
        return TemporaryFile;
    }

Below is a concrete example of how to save my Microsoft Word and Excel files in temporary files.
On the first lines I instantiate my exporters which are java classes managing Excel (static) and Word (entity) POI API.
After creating my exporter, I get my temporary file with the method studied above getTemporaryFile(String extension). Once the change in my temporary files are treated, I open my files with the Util.Files.Command_UTIL.OpenFile(File f) function.

 @Override
    public void actionPerformed(ActionEvent e)
    {
        try {
            if (e.getSource().equals(exportRTF_Button))
            {
                //Export in Word Tenporrary File
                WordExporter_UTIL exporter=new WordExporter_UTIL();
                File temp = File_UTIL.getTemporaryFile(".rtf");
                exporter.createDocument(temp,WordExporter_UTIL.PageOrientation.PORTRAIT);
                exporter.closeDocument();
                Util.Files.Command_UTIL.OpenFile(temp);
                System.out.println("RTF");
            }
            if (e.getSource().equals(exportXLS_Button))
            {
                Util.Exporter.EXCEL.ExcelExporter_UTIL.CreateWorkbook();
                File temp = File_UTIL.getTemporaryFile(".xlsx");
                ExcelExporter_UTIL.AddDatasheet_WithRandomData(this.Title,this.results);
                ExcelExporter_UTIL.saveWorkbook(temp);
                Util.Files.Command_UTIL.OpenFile(temp);
            }
        } catch (OpenFile_Error openFile_error) {
            Error_Message.show(openFile_error);
        }
    }

Opening a File in its own application

This is really important, the file name is not known anymore by the user. Therefore, we need to open the file and show directly where is the dataset | table. My method to open a file in its own application is detailed here. Because we are using some network disk in my office, I implemented two differents function. One for the local files and one for the network files.

package Util.Files;

import ERRORL.Files.OpenFile_Error;

import java.io.File;

public class Command_UTIL {

    public static void  OpenFile(File FileToOpen) throws OpenFile_Error
    {
        try {
            if (FileToOpen.getCanonicalPath().startsWith("\\\\"))
                OpenNetworkFile(FileToOpen.getCanonicalPath());
            else
                OpenLocalFile(FileToOpen.getCanonicalPath());
        }catch (Exception e)
        {
            throw new OpenFile_Error("Error while Opening the file", e.getStackTrace());
        }
    }

    private static void OpenLocalFile(String filename)throws Exception
    {
        Runtime.getRuntime().exec(String.format("cmd /c \"%s\"",filename.replaceAll("\\(","^(").replaceAll("\\)","^)").replaceAll(" ","^ ")));
    }
    private static void OpenNetworkFile(String filename)throws Exception
    {
        Runtime.getRuntime().exec(String.format("cmd /c \"%s\"",filename));
    }
}

Deleting the files 

After some tests, you will notice that the temporary folder is getting bigger and bigger. It needs to be cleaned.
For that I decided that the best moment would be whether at the start of the application process, or at its end. The start may sounds simpler because every java programmer knows where the entry point is. But it also means that the application needs to run a second time before the files are deleted. What if theses documents are confidential and what if the user never uses the application again?For all these reasons, I decided to implement a cleanTemporaryFolder() method when the application process ends.
To catch the process shutdown event, we need to add a hook when the application start. (add the hook after the creation of a file may be a good idea to save resources)

public static void main(String[] args)
    {
        //Add a Shutdown Hook cleaning all the temporary files,
        Runtime.getRuntime().addShutdownHook(new Thread(new TemporaryFilesCleane()));
[.....]
}

The code above, shows us how to add a hook witth the addShutDownHook(Thread T) method of the Runtime object. Our hook is a Runnable (it has to be a runnable in any case) called TemporaryFilesCleaner.

package Util.Files;

public class TemporaryFilesCleaner implements Runnable {

    @Override
    public void run()
    {
        File_UTIL.cleanTemporaryFolder();
    }
}

Voila, I kept it simple because we just need this cleaner as an interface between our File_UTIL static methods library and our application's entry point.
The method cleanTemporaryFolder is quite simple and although it could catch an exception, we will never display the exception or block the application since it is in a CLOSING system thread (if there is an exception, the process will never end and you will use resources uselessly without even knowing it).

public static synchronized void cleanTemporaryFolder()
    {
        File TemporaryFolder=null;
        try {
            TemporaryFolder = getTemporaryFileBaseDirectory();
        }catch(OpenFile_Error error)
        {
            error.printStackTrace();
        }
        List<File> filesInFolder= Arrays.asList(TemporaryFolder.listFiles());
        try {
            filesInFolder=filesInFolder
                    .stream()
                    .filter(File_Predicates.isTemporaryFile().and(File_Predicates.isRtfFile().or(File_Predicates.isExcelFile())))
                    .forEach(f->f.delete());
        }catch(Exception e)
        {e.printStackTrace();}

    }

This method lists all the files in our temporary folder then it filters all the files matching the criteria of my predicates (see the predicate below). The criteria expressed with Windows 8 lambda expression, filter all the files starting with "temp" and finishing by "rtf" or by "xlsx". After what each of the files matching the criteria will be deleted. You probably noticed that my method is synchronized. I added this keyword because we use this method in a Thread and we want our application to be thread safe, especially in this case in a hook.

package Util.Files;

import java.io.File;
import java.util.function.Predicate;

public class File_Predicates
{
    public static Predicate<File> isTemporaryFile()
    {
        return f -> f.getName().startsWith("tmp");
    }
    public static Predicate<File> isExcelFile()
    {
        return p -> p.getName().endsWith(".xlsx");
    }
    public static Predicate<File> isRtfFile()
    {
        return p -> p.getName().endsWith(".rtf");
    }


}

I think, I finished to document this part of the application. :D

Have fun and don't forget to work!

Java - SDK 8 New Features

Hi,

This code shows some of the coolest features of the SDK 8.

As you can see functional methods have been added and will facilitate our work.

Yeeeeeaaaaay

 package uk.ac.imperial.cup.main;  
 import java.util.Comparator;  
 import java.util.List;  
 import java.util.ArrayList;  
 import java.util.Optional;  
 public class Main {  

   public static void main(String[] args) {  
     List<People> peoples = populateList();  
     peoples.stream()  //USE OF STREAM, THE SAME AS PIPE IN LINUX|UNIX
         .filter((People p) -> p.getLastName().equals("Stevens")) //FILTER PEOPLE WITH STEVENS AS LAST NAME
         .sorted(Comparator.comparing(People::getFirstName).reversed()) //SORT FILTERED LIST BY FIRST NAME DESC  
         .forEach((People p) -> System.out.println(p.toString()));  //PRINT EACH ELEMENT OF THE SORTED AND FILTERED LIST
    }  

   private static java.util.List<People> populateList() {  
     java.util.List<People> peoples = new ArrayList<People>();  
     peoples.add(new People("Christophe", "Stevens", Optional.ofNullable(28))); //CREATE AN OPTIONAL IN CONSTRUCTOR 
     peoples.add(new People("Vincent", "Stevens", Optional.ofNullable(30)));  
     peoples.add(new People("Kilye", "Minogue", Optional.ofNullable(45)));  
     peoples.add(new People("William", "Adams", Optional.ofNullable(39)));  
     peoples.add(new People("Tom", "Jones", Optional.ofNullable(73)));  
     peoples.add(new People("Ricky", "Wilson", Optional.ofNullable(null)));  
     return peoples;  
   }  
 }  

 class People  
 {  
   private String FirstName, LastName;  
   private Optional<Integer> Age;  //DECLARING AN OPTIONAL
   public People(String firstName, String lastName, Optional<Integer> age) {  
     FirstName = firstName;  
     LastName = lastName;  
     Age = age;  
   }  
   public String getFirstName() {  
     return FirstName;  
   }  
   public void setFirstName(String firstName) {  
     FirstName = firstName;  
   }  
   public String getLastName() {  
     return LastName;  
   }  
   public void setLastName(String lastName) {  
     LastName = lastName;  
   }  
   public Optional<Integer> getAge() {  
     return Age;  
   }  
   public void setAge(Optional<Integer> age) {  
     Age = age;  
   }  

   //TESTING IF OPTIONAL IS PRESENT WITH TERNARY OPERATOR
   @Override  
   public String toString()  
   {  
     return String.format("%s, %s %s", getLastName(), getFirstName(), getAge().isPresent()?getAge().get() + " year":"");  
   }  
 }  

JAVA - Read A Huge Excel Files (xlsx-XSSF) with SAX parser (avoid gc limit exceeded)

I adapted a piece of code I found on internet in order to read a huge Excel file without facing a "gc limited exceeded" as I always got in POI.

My test file was 42,723 rows long. 

It would be a great idea for you, to browse a xlsx file first. The understanding of the sheets and xml elements and attributes names is important.

To do that:

1.  rename your .xlsx file in .zip file.
2. Extract the content of your zip file in a folder.
3. Open the xl\worksheets\sheets1.xml (Notepad++ is recommanded)
4. Open the xl\sharedStrings.xml (Notepadd++ is recommanded)

You just have to understand that the parser will throw the startElement and endElement while starting or finishing  to read an xml element in sheets1.xml. When ending the reading of a "v" element (index of the value of a cell) it will retrieve the string value in sharedStrings.xml with the code " new XSSFRichTextString(sst.getEntryAt(idx)).toString()"

After you can adapt the code to your own business objects depending of your organisation in cells.

Have fun! (But don't forget to work :D )

 package BL.Parser;  
 import BO.Files.ExcelPublicationFile;  
 import BO.Publication.RisPublication;  
 import org.apache.poi.openxml4j.opc.OPCPackage;  
 import org.apache.poi.xssf.eventusermodel.XSSFReader;  
 import org.apache.poi.xssf.model.SharedStringsTable;  
 import org.apache.poi.xssf.usermodel.*;  
 import org.xml.sax.*;  
 import org.xml.sax.helpers.DefaultHandler;  
 import org.xml.sax.helpers.XMLReaderFactory;  
 import java.io.*;  
 import java.util.ArrayList;  
 import java.util.Iterator;  
 import java.util.List;  
 /**  
  * Created by casteven on 19/03/14.  
  */  
 public class ExcelPublicationFileParser  
 {  
   public ExcelPublicationFile ParseFile(ExcelPublicationFile file) throws Exception {  
     OPCPackage pkg = OPCPackage.open(file);  
     XSSFReader r = new XSSFReader( pkg );  
     SharedStringsTable sst = r.getSharedStringsTable();     //XML file containing all the String values, referenced by index  
     XMLReader parser = fetchSheetParser(sst);  
     Iterator<InputStream> sheets = r.getSheetsData();  
     //Browsing sheets 1  and extracting data.  
     if (sheets.hasNext())  
     {  
       InputStream sheet = sheets.next();  
       InputSource sheetSource = new InputSource(sheet);  
       parser.parse(sheetSource);  
       sheet.close();  
     }  
     file.setPublications(SheetHandler.getPubs());  
     return file;  
   }  
   public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {  
     XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");  
     ContentHandler handler = new SheetHandler(sst);  
     parser.setContentHandler(handler);  
     return parser;  
   }  
   /**  
    * See org.xml.sax.helpers.DefaultHandler javadocs  
    */  
   private static class SheetHandler extends DefaultHandler {  
     private SharedStringsTable sst;  
     private String lastContents;  
     private RisPublication pub=new RisPublication();  
     private static List<RisPublication> pubs=new ArrayList<RisPublication>();  
     private int column=0;  
     private int row=0;  
     private SheetHandler(SharedStringsTable sst) {  
       this.sst = sst;  
       pubs=new ArrayList<RisPublication>();  
     }  
     /*  
       GETTER AND SETTER  
     */  
     public static List<RisPublication> getPubs() {  
       return pubs;  
     }  
     public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException  
     {  
       // Clear contents cache  
       lastContents = "";  
     }  
     public void endElement(String uri, String localName, String name) throws SAXException {  
       // v => index of the content of a cell.  
       if(name.equals("v")) {  
         try {  
           int idx = Integer.parseInt(lastContents); //Catch the ID in int  
           lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString(); // Get the value referenced by index ()  
         } catch (NumberFormatException e) {  
         }  
       }  
       //If we are reading a cell and columns is not the first  
       if(name.equals("c") && row>0) {  
         switch (column)  
         {  
           case 0:pub.setU3(lastContents);  
             break;  
           case 1:pub.setU4(lastContents);  
             break;  
           case 2:pub.setID(lastContents);  
             break;  
           case 3:pub.setT1(lastContents);  
             break;  
           case 4:String author=lastContents;  
             List<String> authors=Util.List_Util.convertCommaSeparatedStringToListOfString(author);  
             for (String s:authors)  
               pub.addA1(s);  
             break;  
           case 5:pub.setY1(lastContents);  
             break;  
           case 6:pub.setN2(lastContents);  
           case 7:String keyword=lastContents;  
             List<String> keywords=Util.List_Util.convertCommaSeparatedStringToListOfString(keyword);  
             for (String s:keywords)  
               pub.addKW(s);  
             break;  
           case 8:pub.setJF(lastContents);  
             break;  
           case 9:pub.setJA(lastContents);  
             break;  
           case 10:pub.setVL(lastContents);  
             break;  
           case 11:pub.setIS(lastContents);  
             break;  
           case 12:pub.setSP(lastContents);  
             break;  
           case 13:pub.setEP(lastContents);  
             break;  
           case 14:pub.setCY(lastContents);  
             break;  
           case 15:pub.setPB(lastContents);  
             break;  
           case 16:pub.setSN(lastContents);  
             break;  
           case 17:pub.setM1(lastContents);  
             break;  
           case 18:pub.setU1(lastContents);  
             break;  
           case 19:pub.setU2(lastContents);  
             break;  
           case 20:pub.setU5(lastContents);  
             break;  
           case 21:pub.setUR(lastContents);  
         }  
         column++;  
       }  
       //If it is the end of a row, save the current publication. An create a new one  
       if(name.equals("row")) {  
         if (row>0)  
           pubs.add(pub);  
         pub=new RisPublication();  
         row++;  
         column=0;  
       }  
     }  
     //Extracting the content of an element  
     public void characters(char[] ch, int start, int length) throws SAXException {  
       lastContents += new String(ch, start, length);  
     }  
   }  
 }  

JavaScript - Direction and Show Map on SmartPhone Native App

Hi,

Here is the code I used on the new Mobile version of www.belle-campagne.be

The first method is used to detect whether or not the accessing device is a Computer or a Smartphone (only the size of the screen is considered)

The 2 other method launch the native maps application in Windows Phone, IOS 6-7, Android.

Have fun and keep coding!

Christophe

 function redirect()  
 {  
      if ( (screen.width > 1024) && (screen.height > 768) )   
      {   
       window.location = 'http://www.belle-campagne.be/index.html';  
      }   
 }  
 function openInMap()  
 {  
   if( (navigator.platform.indexOf("iPhone") != -1)   
     || (navigator.platform.indexOf("iPod") != -1))  
     window.open("http://maps.apple.com/maps?q=50.3258755,5.593183&z=11");  
   else if (navigator.platform.indexOf("Win32") != -1)   
     window.open("explore-maps://v2.0/show/place/?latlon=50.3258755,5.593183&zoom=11");  
      else  
      {  
           var ua = navigator.userAgent.toLowerCase();  
           var isAndroid = ua.indexOf("android") > -1;  
           if(isAndroid)   
                window.open('http://maps.google.com/maps?q=50.3258755,5.593183&z=2');  
           else  
                window.open("comgooglemaps://?center=50.3258755,5.593183&zoom=11&views=traffic");  
      }  
 }  
 function showRouteInMap()  
 {  
   if( (navigator.platform.indexOf("iPhone") != -1)   
     || (navigator.platform.indexOf("iPod") != -1))  
     window.open("http://maps.apple.com/maps?daddr=50.3258755,5.593183");  
   else if (navigator.platform.indexOf("Win32") != -1)   
     window.open("directions://v2.0/route/destination/?latlon=50.3258755,5.593183");  
      else  
      {  
           var ua = navigator.userAgent.toLowerCase();  
           var isAndroid = ua.indexOf("android") > -1;  
           if(isAndroid)   
                window.open('http://maps.google.com/?daddr=50.3258755,5.593183&directionsmode=transit');  
           else  
                window.open("comgooglemaps://?daddr=50.3258755,5.593183&directionsmode=transit");  
      }  
 }