java file only last data getting inserted in file

Question

package Reader;
        public class Write_Excel  {
            private WritableCellFormat timesBoldUnderline;
            private WritableCellFormat times;
            private String inputFile;
            Thread thread = new Thread();
            static String box, plc, hmi;
            int k=1,cnt=1;

    public void witeExcel(ArrayList<String> B,ArrayList<String> P, ArrayList<String> H)
                    throws WriteException, IOException {
                Write_Excel test = new Write_Excel();
                test.setOutputFile("C://Users//Tanmay//Desktop//Export.xls");
                for (int index = 0; index < B.size(); index++) {
                box = B.get(index);
                plc = P.get(index);
                hmi = H.get(index);

                test.write();
            }System.out.println("Please check the result file under C://Users//Tanmay//Desktop//Export.xls ");
            }

            public void setOutputFile(String inputFile) {
                this.inputFile = inputFile;
            }

            public void write() throws IOException, WriteException {
                File file = new File(inputFile);
                WorkbookSettings wbSettings = new WorkbookSettings();
                wbSettings.setLocale(new Locale("en", "EN"));
                WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
                workbook.createSheet("Serial Numbers", 0);
                WritableSheet excelSheet = workbook.getSheet(0);
                excelSheet.setColumnView(1, 15);
                excelSheet.setColumnView(2, 15);
                excelSheet.setColumnView(3, 15);
                createLabel(excelSheet);
                createContent(excelSheet);
                workbook.write();
                System.out.println("Value of k in close method is "+k);
                if(k==4) {
                    System.out.println("Condition Satisfied where value is "+k);
                    workbook.close();   
                }       
            }

            private void createLabel(WritableSheet sheet) throws WriteException {
                //System.out.println("In create label method");
                // Lets create a times font
                WritableFont times10pt = new WritableFont(WritableFont.ARIAL, 12);
                // Define the cell format
                times = new WritableCellFormat(times10pt);
                // Lets automatically wrap the cells
                times.setWrap(true);

                // create create a bold font with underlines
                WritableFont times10ptBoldUnderline = new WritableFont(
                        WritableFont.TIMES, 12, WritableFont.BOLD, false);
                timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
                // Lets automatically wrap the cells
                timesBoldUnderline.setWrap(false);

                CellView cv = new CellView();
                cv.setFormat(times);
                cv.setSize(20);
                cv.setFormat(timesBoldUnderline);

                // Write a few headers
                addCaption(sheet, 0, 0, "BOX");
                addCaption(sheet, 1, 0, "CPU");
                addCaption(sheet, 2, 0, "HMI");
                //System.out.println("Out create label method");
            }

            private void addCaption(WritableSheet sheet, int column, int row, String s)
                    throws RowsExceededException, WriteException {
                //System.out.println("In addCaption Method");
                Label label;
                label = new Label(column, row, s, timesBoldUnderline);
                sheet.addCell(label);
                //System.out.println("Out addCaption Method");
            }

            private void createContent(WritableSheet sheet) throws WriteException,
                    RowsExceededException {
                System.out.println("In createContent Method");
                    addLabel(sheet, 0, k, box);
                    addLabel(sheet, 1, k, plc);
                    addLabel(sheet, 2, k, hmi);
                    //System.out.println("Value of K is "+k);
                    System.out.println("Out createContent Method");
                    k++;
            }

            private void addLabel(WritableSheet sheet, int column, int row, String s)
                    throws WriteException, RowsExceededException {
                System.out.println("In Addlabel Method");
                Label label;
                label = new Label(column, row, s, times);
                System.out.println("Value of row is "+row+" Value of column is "+column+" Value string is "+s );
                sheet.addCell(label);
                System.out.println("Out addlabel Method");
            }
        }

I am building an application for barcode reading now the problem with code below is that when i get excel file in o/p only last data which are taken in table format gets in the excel file in the o/p. I needed some advice where am i going wrong and what should be done. I have already check all things with system.out.printlln(); But did not find any solution.

 Thanks in advance and double to one who helps to get right solution.

you are creating a new file each time you are adding a row. you need to create your file lets say in constructor. then in your write method don't create a file — nafas
– nafas, Commented Jul 4, 2014 at 9:41
I have tried with file to create in constructor but workbook settings is creating the problem. Can you help me on that. — user3793580
– user3793580, Commented Jul 4, 2014 at 9:48

vinayknl · Accepted Answer · 2014-07-04 09:44:16Z

2

       public void witeExcel(ArrayList<String> B,ArrayList<String> P, ArrayList<String> H)
                throws WriteException, IOException {
             WorkbookSettings wbSettings = new WorkbookSettings();
            wbSettings.setLocale(new Locale("en", "EN"));
            WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
            workbook.createSheet("Serial Numbers", 0);

            Write_Excel test = new Write_Excel();
            test.setOutputFile("C://Users//Tanmay//Desktop//Export.xls");
            for (int index = 0; index < B.size(); index++) {
            box = B.get(index);
            plc = P.get(index);
            hmi = H.get(index);

            test.write(workbook);
        }System.out.println("Please check the result file under C://Users//Tanmay//Desktop//Export.xls ");
        }

Move the logic to create Workbook before for loop

Change write method to pass workbook

 public void write(WritableWorkbook workbook) throws IOException, WriteException {
            File file = new File(inputFile);


            WritableSheet excelSheet = workbook.getSheet(0);
        ................

answered Jul 4, 2014 at 9:44

vinayknl

1,2528 silver badges18 bronze badges

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

3 Comments

nafas Over a year ago

@TAsk it has been passed as a parameter, I don't see why it shouldn't work

user3793580 Over a year ago

Anyways still there is some issue with declaration of file. But will eventually get through it. THANK YOU ALL FOR THE HELP AND SUPPORT.

user3793580 Over a year ago

As per advice i have tried creating file object in constructor and workbook in constructor but still it is showing error. May i know what would be exact solution for the same. Sorry for posting same thing again.

Akash Thakare · Accepted Answer · 2014-07-04 11:37:22Z

0

Reason is you are creating workbook in method every time you call so old data gets cleaned and new data will be added.

Remove this from method and move to some other place to execute once.

  File file = new File(inputFile);//From File
  WorkbookSettings wbSettings = new WorkbookSettings();
  wbSettings.setLocale(new Locale("en", "EN"));
  WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
  workbook.createSheet("Serial Numbers", 0);/To configuration

I suggest you to move your initializations to constructor.

Move all initialization to constructor OR
Initialize elements in writeExcel BUT Declare those variables at class level means where you have declared your variables.So that you can initialize those in wordExcel.

edited Jul 4, 2014 at 11:37

answered Jul 4, 2014 at 9:42

Akash Thakare

23k11 gold badges64 silver badges89 bronze badges

2 Comments

user3793580 Over a year ago

But what to do with File file. It is creating error where to declare it.

user3793580 Over a year ago

File file = new File(inputFile); and WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings); are giving error of declaration

Collectives™ on Stack Overflow

java file only last data getting inserted in file

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related