博客

项目中用到Apache POI来处理Execl,模板文件中有数据有效性的验证，处理时需要添加行，导致原有需要数据有效验证的单元格向下移动。经过测试发现POI对于数据有效性验证只能添加，不能删除。

在网上看到一篇文章作者的作法是可以通过直接修改execl的XML节点的方式，达到修改，删除数据有效性的目的。（

原文链接

）

本人项目需求不需要修改只需要删除在重新添加即可，以下是具体代码实现。

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.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class RemoveDataValidation {

	public static void main(String[] args) {

		String url = "D:\\test.xlsx";
		InputStream inputStream = null;
		OutputStream outputStream1 = null;
		String newFileName = "D:\\new_test.xlsx";
		XSSFWorkbook wb = null;
		try {
			inputStream = new FileInputStream(url);
			wb = new XSSFWorkbook(inputStream);
			XSSFSheet ws = wb.getSheet("Sheet2");

			delValidations(ws);

			outputStream1 = new FileOutputStream(newFileName);
			wb.write(outputStream1);
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
		} catch (IOException e) {
			// TODO
		} finally {

			try {
				inputStream.close();
				wb.close();
				outputStream1.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

	}

	public static void delValidations(XSSFSheet ws) {

		try {

			CTWorksheet worksheet = (CTWorksheet) ReflectHelper.getValueByFieldName(ws, "worksheet");
			Node domNode = worksheet.getDomNode();
			NodeList childNodes = domNode.getChildNodes();
			int size = childNodes.getLength();

			for (int i = 0; i < size; i++) {
				Node childNode = childNodes.item(i);
				// if node name is dataValidations
				if ("dataValidations".equals(childNode.getNodeName())) {
					// remove childnode from worksheet
					domNode.removeChild(childNode);
					break;
				}
			}

		} catch (Exception e) {
			// TODO
		}

	}