Writing data to an excel sheet using Java

Question

I am writing a program which writes data to an excel sheet sort of a table format. My code gives me all the details as I want.

But in order to get the table format I have to follow these steps in excel sheet

(select a column -> data -> text to columns -> select the option 'delimited' -> click button 'next' -> select the option 'comma'-> click button 'next'-> click button 'finish').

I want my code to automatically generate the format I want without me doing the above shown steps in the excel sheet. Can anyone help me in this? Thanks in advance. Below shown is my code.

public class GenrateCSV {

    private static JFileChooser fileChooser;

    private ComparisonController comparisonController;
    private int referenceId;

    private void generateXlsFile(String sFileName, ComparisonController comparisonController) {
        try {
            this.referenceId = comparisonController.getReferenceId();
            FileWriter writer = new FileWriter(sFileName);

            //Main headings
            writer.append(",");
            writer.append(",");
            writer.append(",");
            writer.append('\n');
            writer.append('\n');
            writer.append(",");
            writer.append(",");
            writer.append("Case name");
            writer.append(",");
            writer.append(",");
            writer.append(',');
            writer.append("Folder 01");
            writer.append(",");
            writer.append(',');
            writer.append(',');
            writer.append(',');
            writer.append(',');
            writer.append("Folder 02");
            writer.append(",");
            writer.append(',');
            writer.append(',');
            writer.append(',');
            writer.append(",");
            writer.append("Compared results");
            writer.append('\n');

            //folder 01- sub headings
            writer.append(",");
            writer.append(",");
            writer.append(",");
            writer.append(",");
            writer.append("Min. Jacobian");
            writer.append(",");
            writer.append("Average Jacobian");
            writer.append(",");
            writer.append("Max. Jacobian");
            writer.append(',');
            writer.append("Range");
            writer.append(",");
            writer.append(',');

            //folder 02 - sub headings
            writer.append("Min. Jacobian");
            writer.append(",");
            writer.append("Average Jacobian");
            writer.append(",");
            writer.append("Max. Jacobian");
            writer.append(',');
            writer.append("Range");
            writer.append(",");
            writer.append(',');

            //comapred results - sub headings
            writer.append("Percentage change of min. values");
            writer.append(",");
            writer.append("Percentage change of Average");
            writer.append(",");
            writer.append("Percentage change of min. values");
            writer.append(",");
            writer.append("Percentage change of Ranges");
            writer.append('\n');

            //empty line as for the 2nd line in all the columns
            writer.append('\n');

            //Data for folder 1. Case: turb_rad_A70 1
            //case name
            writer.append(",");
            writer.append(",");
            String string = comparisonController.getQalFile1().getFileDetails().getParent();;
            string = string.replace("\\", ",");
            String[] splittedStr = string.split(",");
            writer.append(splittedStr[splittedStr.length - 1]);

            //Min. Jacobian
            writer.append(",");
            writer.append(",");
            if (referenceId == 0) {
                writer.append(String.valueOf(comparisonController.getQalFile1().getMinimumElement().getJacobianRatio()));
            }

            //Avg.Jacobian
            writer.append(",");
            if (referenceId == 0) {
                writer.append(String.valueOf(comparisonController.getQalFile1().getAvgElement().getJacobianRatio()));
            }

            //Max. Jacobian
            writer.append(",");
            if (referenceId == 0) {
                writer.append(String.valueOf(comparisonController.getQalFile1().getMaximumElement().getJacobianRatio()));
            }

            //Range
            writer.append(",");
            if (referenceId == 0) {
                writer.append(String.valueOf(comparisonController.getQalFile1().getMaximumElement().getJacobianRatio() - comparisonController.getQalFile1().getMinimumElement().getJacobianRatio()));
            }

            //Data for folder 2. Case: turb_rad_A70 1
            //Min. Jacobian
            writer.append(",");
            writer.append(",");
            if (referenceId == 0) {
                writer.append(String.valueOf(comparisonController.getQalFile2().getMinimumElement().getJacobianRatio()));
            }

            //Avg.Jacobian
            writer.append(",");
            if (referenceId == 0) {
                writer.append(String.valueOf(comparisonController.getQalFile2().getAvgElement().getJacobianRatio()));
            }

            //Max. Jacobian
            writer.append(",");
            if (referenceId == 0) {
                writer.append(String.valueOf(comparisonController.getQalFile2().getMaximumElement().getJacobianRatio()));
            }

            //Range
            writer.append(",");
            if (referenceId == 0) {
                writer.append(String.valueOf(comparisonController.getQalFile2().getMaximumElement().getJacobianRatio() - comparisonController.getQalFile2().getMinimumElement().getJacobianRatio()));
            }

            //Data for compared reults. Case: turb_rad_A70 1
            //Percentage change of min.values ((Folder 01 - Folder 02)/|Folder 01|*100)
            writer.append(",");
            writer.append(",");
            if (referenceId == 0) {
                writer.append(String.valueOf(((comparisonController.getQalFile1().getMinimumElement().getJacobianRatio() - comparisonController.getQalFile2().getMinimumElement().getJacobianRatio()) / comparisonController.getQalFile1().getMinimumElement().getJacobianRatio()) * 100));
            }

            //Percentage change of Average. ((Folder 01 - Folder 02)/|Folder 01|*100)
            writer.append(",");
            if (referenceId == 0) {
                writer.append(String.valueOf(((comparisonController.getQalFile1().getAvgElement().getJacobianRatio() - comparisonController.getQalFile2().getAvgElement().getJacobianRatio()) / comparisonController.getQalFile1().getAvgElement().getJacobianRatio()) * 100));
            }

            //Percentage change of max.values ((Folder 01 - Folder 02)/|Folder 01|*100)
            writer.append(",");
            if (referenceId == 0) {
                writer.append(String.valueOf(((comparisonController.getQalFile1().getMaximumElement().getJacobianRatio() - comparisonController.getQalFile2().getMaximumElement().getJacobianRatio()) / comparisonController.getQalFile1().getMaximumElement().getJacobianRatio()) * 100));
            }

            //Percentage change of Range. ((Folder 01 - Folder 02)/|Folder 01|*100)
            writer.append(",");
            if (referenceId == 0) {
                double file1range = comparisonController.getQalFile1().getMaximumElement().getJacobianRatio() - comparisonController.getQalFile1().getMinimumElement().getJacobianRatio();
                double file2range = comparisonController.getQalFile2().getMaximumElement().getJacobianRatio() - comparisonController.getQalFile2().getMinimumElement().getJacobianRatio();
                writer.append(String.valueOf(((file1range - file2range) / file1range) * 100));
//                writer.append(String.valueOf(((comparisonController.getQalFile1().getMaximumElement().getJacobianRatio() - comparisonController.getQalFile1().getMinimumElement().getJacobianRatio()) - ((comparisonController.getQalFile2().getMaximumElement().getJacobianRatio() - comparisonController.getQalFile2().getMinimumElement().getJacobianRatio()) - (comparisonController.getQalFile1().getMaximumElement().getJacobianRatio() - comparisonController.getQalFile1().getMinimumElement().getJacobianRatio()))) / (comparisonController.getQalFile1().getMaximumElement().getJacobianRatio() - comparisonController.getQalFile1().getMinimumElement().getJacobianRatio()) * 100));
            }

            System.out.println(writer.toString());
            System.out.println("1-max" + comparisonController.getQalFile1().getMaximumElement().getJacobianRatio());
            System.out.println("1-min" + comparisonController.getQalFile1().getMinimumElement().getJacobianRatio());
            System.out.println();
            System.out.println("2-max" + comparisonController.getQalFile1().getMaximumElement().getJacobianRatio());
            System.out.println("2-min" + comparisonController.getQalFile1().getMinimumElement().getJacobianRatio());
            System.out.println();
            System.out.println("1-range" + (comparisonController.getQalFile1().getMaximumElement().getJacobianRatio() - comparisonController.getQalFile1().getMinimumElement().getJacobianRatio()));
            System.out.println("2-range" + (comparisonController.getQalFile2().getMaximumElement().getJacobianRatio() - comparisonController.getQalFile2().getMinimumElement().getJacobianRatio()));
            System.out.println();
            double file1range = comparisonController.getQalFile1().getMaximumElement().getJacobianRatio() - comparisonController.getQalFile1().getMinimumElement().getJacobianRatio();
            double file2range = comparisonController.getQalFile2().getMaximumElement().getJacobianRatio() - comparisonController.getQalFile2().getMinimumElement().getJacobianRatio();
            System.out.println(((file1range - file2range) / file1range) * 100);

`

If you don't want excel to do that you'll need to generate an XLS file or XLSX file with the help of an eternal library — fdsa
– fdsa, Commented Apr 24, 2015 at 4:41
@fdsa do you know any library? can you please tell me. thanks in advance — AndyRJ
– AndyRJ, Commented Apr 24, 2015 at 5:08
Why not use tabs as delimiter? I believe excel user tab as the default delimiter? — Chetan Kinger
– Chetan Kinger, Commented Apr 24, 2015 at 5:31

Igor Kudryashov · Accepted Answer · 2015-04-24 11:10:24Z

2

See Apache POI - the Java API for Microsoft Documents. Apache POI Project's is pure Java implementation of the Excel file formats (xls and xlsx). You can directly write/read native Excel's files by POI. Please see examples.

edited Apr 24, 2015 at 11:10

answered Apr 24, 2015 at 5:55

Igor Kudryashov

3632 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LittlePanda Over a year ago

Explain a bit about Apache POI. Link only answers are discouraged.

Igor Kudryashov Over a year ago

Apache POI Project's is pure Java implementation of the Excel file formats (xls and xlsx). You can directly write/read native Excel's files by POI. Please see [examples] (poi.apache.org/spreadsheet/how-to.html#user_api)

LittlePanda Over a year ago

I meant add it to the question.

aurelius · Accepted Answer · 2015-04-24 07:18:39Z

0

Jasper Reports provides you an easy way to write excel files

answered Apr 24, 2015 at 7:18

aurelius

4,1149 gold badges43 silver badges78 bronze badges

Collectives™ on Stack Overflow

Writing data to an excel sheet using Java

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related