package poiTest;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;


public class FormulaRemoveSheet {

	private static final String FILE = "C:/poiTest/poi-formula-removeSheet_before.xls";
	private static final String AFTER_FILE = "C:/poiTest/poi-formula-removeSheet_after.xls";
	public static void main(String... args) throws Exception {
		write();
		read();
	}

	static void write() throws IOException {
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet1 = workbook.createSheet("sheet1");
		Sheet sheet2 = workbook.createSheet("sheet2");
		Sheet sheet3 = workbook.createSheet("sheet3");

		sheet1.createRow(0).createCell((short) 0).setCellValue("val1");
		sheet2.createRow(0).createCell((short) 0).setCellValue("val2");
		sheet3.createRow(0).createCell((short) 0).setCellValue("val3");
		
		Name namedCell1 = workbook.createName();
		namedCell1.setNameName("name1");
		String reference1 = "sheet1!$A$1";
		namedCell1.setRefersToFormula(reference1);
		
		Name namedCell2= workbook.createName();
		namedCell2.setNameName("name2");
		String reference2 = "sheet2!$A$1";
		namedCell2.setRefersToFormula(reference2);

		Name namedCell3 = workbook.createName();
		namedCell3.setNameName("name3");
		String reference3 = "sheet3!$A$1";
		namedCell3.setRefersToFormula(reference3);
		
		
		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream(FILE); 		
			workbook.write(fos);
		} finally {
			fos.close();
		}
	}

	static void read() throws Exception {
		Workbook workbook = new HSSFWorkbook(new FileInputStream(FILE));;
		
        System.out.println("Before removing sheet1...");
		
		Name nameCell = workbook.getName("name1");
		System.out.println("name1: " + nameCell.getRefersToFormula());
	 
		nameCell = workbook.getName("name2");
		System.out.println("name2: " + nameCell.getRefersToFormula());
		
		nameCell = workbook.getName("name3");
		System.out.println("name3: " + nameCell.getRefersToFormula());
		
		workbook.removeSheetAt(workbook.getSheetIndex("sheet1"));
		
		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream(AFTER_FILE);
			workbook.write(fos);
		} finally {
			fos.close();
		}
		
		System.out.println("\nAfter removing sheet1...");
		
		nameCell = workbook.getName("name1");
		System.out.println("name1: " + nameCell.getRefersToFormula());
	 
		nameCell = workbook.getName("name2");
		System.out.println("name2: " + nameCell.getRefersToFormula());
		
		nameCell = workbook.getName("name3");
		System.out.println("name3: " + nameCell.getRefersToFormula());
		
	}
}