0

I'm working on some task where I need to open the excel file and write some data in that file. I'm using apache POI for this task. I'm able to write on some other file basically by creating the different files. But with this file, I'm not able to write data in it.

Could you help me out. What is the issue with this file? Is it locked or frozen? Here is my code:

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetProtection;

public class AAA2 {

    public static void main(String[] args) {
        AAA2 aaa2 = new AAA2();
        aaa2.xlsOps();
    }

    private void xlsOps() {
        String filePath="C:\\dev\\excel_file.xls";
        try (InputStream inp = new FileInputStream(filePath)) {
            
                Workbook wb = WorkbookFactory.create(inp);
                HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);
               // XSSFSheet sheet = (XSSFSheet) wb.getSheetAt(0);
                
                CellStyle unlockStyle=wb.createCellStyle();
                unlockStyle.setLocked(false);   
                
                //Row part
                Row row = sheet.getRow(6);
                row.setRowStyle(unlockStyle);
                System.out.println("Row:"+row);

                //Row row = null;
//              if(row == null) {
//                  row =sheet.createRow(6);
//                  System.out.println("Row Lock: "+row.getRowStyle());
//              }
                // Cell part.
                Cell cell = row.getCell(5);
                System.out.println(cell);
                if(cell == null) {
                    cell = row.createCell(5);
                    System.out.println("After creation lockVal: "+cell.getCellStyle().getLocked()); 
                    cell.setCellStyle(unlockStyle);
                }
                
                System.out.println("Update the lock as false: "+cell.getCellStyle().getLocked());
                cell.setCellValue("TEST 1223435");
                System.out.println(cell);
                
                // Write the output to a file
                try (OutputStream fileOut = new FileOutputStream("C:\\dev\\x-new22.xls")) {
                    wb.write(fileOut);
                }
                System.out.println("sheet created.");
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            }
    }
}

What I'm doing wrong? Is the row or cell is fixed in the given excel? Here is file enter link description here

Anshu Kumar
  • 633
  • 7
  • 12
  • 1
    Your code works well but you are fiddling with the wrong sheet. Your workbook contains 4 sheets. Only one is visible. But the visible sheet named "Header" is the second sheet, not the first one. So you code sets cell value of `F7` in sheet "Validation Lists" which is the first sheet but not visible. – Axel Richter Nov 01 '20 at 11:59
  • @AxelRichter Thank you so much. I spend lots of time on this while searching over the internet. Thanks Axel. – Anshu Kumar Nov 03 '20 at 09:22

0 Answers0