Inserting an array into Excel

Question

I have an array that I want to retrieve the values from and insert them into excel using Jxl, one value into one row eg:

1| blah blah blah

2| blah blah blah

3| blah blah blah

I can retrieve the values from the array as is shown in the code below, but inserting them into rows is proving difficult for me....the best I have managed through various attempts is inserting the right number of rows but all of the same value (the last value stored in the array).

private static void InsertStrings() throws RowsExceededException, WriteException {
    try {
        System.out.println("Starting Write to Excel");
        WritableWorkbook workbook = Workbook.createWorkbook(new File("C:\\Users\\Jason\\Documents\\Development\\Seaport Crawler\\CrawlerResults.xls"));

        WritableSheet sheet = workbook.createSheet("Results", 0);
        Label label = new Label(0, 0, "Notices to Mariners"); 
        sheet.addCell(label);
        for ( int indx = 0; indx < arr_Heading.size(); indx++)
        {
            /********HEADING and TITLE ARRAY*********/
            String heading;
            int rowValue = indx;            
            heading = arr_Heading.get(rowValue);    

            //Editing the Descriptions
            if (heading.contains("-")) {
                // Split it
                String string = heading;
                String[] parts = string.split("-",2);
                String part1 = parts[0]; // Heading
                String part2 = parts[1]; // Title
                //String part1s = part1.replaceAll("\\s+","");//Replace Spaces with none
                // String part2s = part2.replaceAll("\\s+","");

                System.out.println("row Value........" + rowValue + " Heading... " + part1 + " Title... " + part2);

                //Insert Strings to Excel Workbook
                Label ntmHeading = new Label(2, indx, part1);
                sheet.addCell(ntmHeading);

            }else{
                //something
            }
            // All sheets and cells added. Now write out the workbook 
            workbook.write(); 
            workbook.close();
        }
    } catch (IOException e) {
        System.out.println("WRITE TO EXCEL FAILED");
        e.printStackTrace();
    }

Thanks

Do not reinvent the wheels. Just use Apache's POI library to manipulate spread sheets. http://poi.apache.org/spreadsheet/quick-guide.html — Mohammad Najar, May 23 '14 at 16:45
@Mohammad Using JExcelApi is not reinventing the wheel. It is another library for writing Excel like POI. Which is _better_ is another discussion, but JExcelApi is definitely an available option. — Nivas, May 23 '14 at 16:49
I think jxl is certainly good enough for what I want to do, I think my problem is more to do with my lack of knowledge than the library.... — J4C3N-14, May 23 '14 at 16:52

score 2 · Accepted Answer · edited May 25 '14 at 16:51

2

Write the following statements outside the for loop:

workbook.write();
workbook.close();

Do some adjustments for try catch block/throws and it works.

edited May 25 '14 at 16:51

gung - Reinstate Monica

11,583
7
60
79

answered May 25 '14 at 16:29

Preeti Jain

46
3

Hi, Thanks for the answer, I had figured this out about a day after I had asked the question. What was happening was that because the workbook.write(); was inside of the loop it was generating a new excel sheet on each loop. this was why I was just getting the last result from the array. Once I placed it outside of the loop it worked. Anyway thanks for your time to answer and you are correct so I will accept. – J4C3N-14 May 26 '14 at 18:04
Thanks for accepting. This is my first contribution to stackoverflow!. – Preeti Jain May 26 '14 at 18:15

Inserting an array into Excel

1 Answers1