poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MSB <markbrd...@tiscali.co.uk>
Subject Re: Populating EXCEL Templates using configuration files
Date Mon, 01 Mar 2010 16:05:10 GMT

Not too sure I would advise this as I do not know enough about the format and
contents of the various files, but you can work on the xml directly. The
.xslx files are simply zipped archives that you can unzip and rezip (if that
is the term) with the toolset provided by the core java API. Just recently,
I wanted to play around with a particular setting for one of the style
objects, did not know how to gain access to that setting using Excel and so
bogded togehther a calss that would allow me to modify the xml directly.
Only a few lines of code are required to either open or resave the archive;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Enumeration;
import java.util.zip.ZipFile;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 *
 * @author win user
 */
public class HyperlinkImages {
    
    private String sourceFilename = null;
    private String destinationFolder = null;
    private BufferedOutputStream destination = null;
    private BufferedInputStream source = null;
    private ZipEntry zipEntry = null;
    private ZipFile zipFile = null;
    private Enumeration<? extends ZipEntry> zipEntries = null;
    private byte[] data = null;
    private int count = 0;
    
    private static final int BUF_SIZE = 2048;
    
    public HyperlinkImages(String sourceFilename, String destinationFolder)
{
        this.sourceFilename = sourceFilename.replaceAll("\\\\", "/");
        this.destinationFolder = destinationFolder.replaceAll("\\\\", "/");
    }
    
       
    public void unzipFile() throws IOException {
        
        File file = null;
        String fileName = null;
        int to = 0;
        boolean result = false;
        
        try {
            zipFile = new ZipFile(this.sourceFilename);
            zipEntries = zipFile.entries();
            while(zipEntries.hasMoreElements()) {
                zipEntry = (ZipEntry)zipEntries.nextElement();
                source = new
BufferedInputStream(zipFile.getInputStream(zipEntry));
                fileName = this.destinationFolder + "/" +
zipEntry.getName();
                // If the file should be unzipped into a folder then.....
                if(fileName.contains("/")) {
                    to = fileName.lastIndexOf("/");
                    // Get the name of the folder
                    file = new File(fileName.substring(0, to));
                    // If the folder DOES NOT already exit then.....
                    if(!file.exists()) {
                        // Try to create the folder.
                        result = file.mkdirs();
                        if(!result) {
                            // Throw an exception if the folder cannot be
created
                            throw new IOException("It was not possible to
create the following folder to unzip the archive into: " +
                                    fileName.substring(0, to));
                        }
                    }
                }
                file = new File(fileName);
                destination = new BufferedOutputStream(new
FileOutputStream(file), BUF_SIZE);
                data = new byte[BUF_SIZE];
                while((count = source.read(data, 0, BUF_SIZE)) != -1) {
                    destination.write(data, 0, count);
                }
                destination.flush();
                destination.close();
                destination = null;
                source.close();
                source = null;
            }
        }
        finally {
            try {
                if(destination != null) {
                    destination.flush();
                    destination.close();
                    destination = null;
                }
            }
            catch(Exception ex) {
                // I G N O R E //
            }
            try {
                if(source != null) {
                    source.close();
                    source = null;
                }
            }
            catch(Exception ex) {
                // I G N O R E //
            }
        }
    }
    
    public void zipFile(String zipFilename, String sourceFilename) throws
FileNotFoundException, IOException {
        File outputFile = null;
        File inputFile = null;
        FileOutputStream fileOutputStream = null;
        ZipOutputStream zipOutputStream = null;
        try {
            outputFile = new File(zipFilename);
            inputFile = new File(sourceFilename);
            fileOutputStream = new FileOutputStream(outputFile);
            zipOutputStream = new ZipOutputStream(new
BufferedOutputStream(fileOutputStream, BUF_SIZE));
            this.saveToFile(zipOutputStream, inputFile, sourceFilename);
        }
        finally {
            if(zipOutputStream != null) {
                try {
                    zipOutputStream.flush();
                    zipOutputStream.close();
                }
                catch(Exception ex) {
                    // I G N O R E //
                }
            }
            if(outputFile != null) {
                outputFile = null;
            }
            if(inputFile != null) {
                inputFile = null;
            }
        }
    }

    public void editXML(String filename) throws Exception {
        File file = null;
        FileInputStream fis = null;
        FileOutputStream fos = null;
        StringBuffer buffer = null;
        String contents = null;
        int readFromFile = 0;
        byte[] byteBuf = new byte[1024];
        byte[] outputByteBuf = null;
        try {
            file = new File(filename);
            fis = new FileInputStream(file);
            buffer = new StringBuffer();
            readFromFile = fis.read(byteBuf, 0, byteBuf.length);
            while(readFromFile > -1) {
                buffer.append(new String(byteBuf, 0, readFromFile));
                readFromFile = fis.read(byteBuf, 0, byteBuf.length);
            }
            fis.close();
            fis = null;
            contents = buffer.toString().trim();
            if(contents.contains("quotePrefix=\"1\"")) {
                System.out.println("Found it.");
                contents = contents.replace("quotePrefix=\"1\"",
"quotePrefix=\"1\"");
            }
            outputByteBuf = contents.getBytes();
            fos = new FileOutputStream(file);
            fos.write(outputByteBuf, 0, outputByteBuf.length);
        }
        catch(IOException ioEx) {

        }
        finally {
            if(fos != null) {
                try {
                    fos.flush();
                    fos.close();
                    fos = null;
                }
                catch(IOException ioEx) {

                }
            }
        }

    }
    
    private void saveToFile(ZipOutputStream zipOutputStream, File
sourceFile, String sourceFilename) throws FileNotFoundException, IOException
{
        BufferedInputStream bufferedInputStream = null;
        FileInputStream fileInputStream = null;
        File[] files = null;
        String entryName = null;
        try {
            if(sourceFile.isDirectory()) {
                files = sourceFile.listFiles();
                for(File file : files) {
                    this.saveToFile(zipOutputStream, file, sourceFilename);
                }
            }
            else {
                this.data = new byte[BUF_SIZE];
                this.count = 0;
                entryName =
sourceFile.getAbsolutePath().substring(sourceFilename.length() + 1);
                fileInputStream = new FileInputStream(sourceFile);
                bufferedInputStream = new
BufferedInputStream(fileInputStream, BUF_SIZE);
                this.zipEntry = new ZipEntry(entryName);
                zipOutputStream.putNextEntry(zipEntry);
                while((count = bufferedInputStream.read(data, 0, BUF_SIZE))
!= -1) {
                   zipOutputStream.write(data, 0, count);
                }
            }
        }
        finally {
            if(bufferedInputStream != null) {
                try {
                    bufferedInputStream.close();
                }
                catch(Exception ex) {
                    // I G N O R E //
                }
            }
        }
    }
}

I would not say that this is the best approach to adopt - just as an
example, strings are shared and how you would deal with the situation where
you wanted to change the value of one of the cells that shared a particular
value, I do not know - but it is possible to get at the raw xml.

As to the other two options you presented, I am sorry to say that I cannot
offer an opinion.

Yours

Mark B

PS Please ignore the editXML() method, that was me simply substituting
different values for one of a cell styles attributes.


Richard Holmes-2 wrote:
> 
> Hi All,
> 
> A client of mine is looking to export a java object model to various EXCEL
> sheets.  These sheets are not of any particular format or layout and they
> would like to be able to use some form of configuration files to populate
> the sheets how they wish, the EXCEL version being used is 2003.
> 
> The options I am looking into are:
> 1) Use the XML features in EXCEL to populate the workbook - but in this
> case
> I can not find a way of updating the XML source in Java, in .net is it
> ImportXML.
> 2) Use some sort of configuration take the java or XML object and populate
> the sheets/cells in Excel, I have tried jxls but this does not have the
> flexibility other than this I have come up short.  (I am looking into
> using
> mono but I don't believe this is possible either).
> 3) Use EXCEL macros to re-format the data once in the sheet, but again
> this
> requires the users to perform actions, such as allowing macros to be run.
> Some of our users find this a complex proceedure :)
> 
> I have thought of writing my own engine but the deadlines are too short.
> 
> If anyone has any ideas I would be very greatful, the easiest for me would
> be to find a way of updating XML or find an engine that uses a
> configuration
> file to populate EXCEL.
> 
> 
> 
> Many thanks
> Richard
> 
> 

-- 
View this message in context: http://old.nabble.com/Populating-EXCEL-Templates-using-configuration-files-tp27741955p27745456.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Mime
View raw message