当我们利用poi时,创建下拉框或者其他数据有效性的时候,删除或者覆盖数据有效性的方法在网上看了一些,但是尝试无果。以下方法我能成功删除有效性,下面是关键代码部分
//创建表和数据验证
//fis是excel输入流,假设已经被你创建好
XSSFWorkbook book = new XSSFWorkbook(fis);
XSSFSheet sheet = book.getSheet("yourSheet");
/**核心部分 sheet.getCTWorkSheet.getDataValidations().removeDataValidation(0) */
ArrayList<XSSFDataValidation> dataValidations = sheet.getDataValidations();
if(dataValidations.size()!=0){
//重置validations
tmpSheet.getCTWorksheet().unsetDataValidations();
}
以下是完整代码例子
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationConstraint.OperatorType;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelExample {
public static void main(String[] args) throws Exception {
// 创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
workbook.setForceFormulaRecalculation(true);
XSSFSheet sheet = workbook.createSheet("TestSheet");
// 创建数据验证 ---在表TestSheet中的A1单元格
DataValidationHelper validationHelper = new XSSFDataValidationHelper(sheet);
DataValidationConstraint validationConstraint = validationHelper.createExplicitListConstraint(new String[]{"数据有效性1", "数据有效性2", "数据有效性3"});
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DataValidation validation = validationHelper.createValidation(validationConstraint, addressList);
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(false);
sheet.addValidationData(validation);
// 将工作簿保存到文件
try {
FileOutputStream fileOut = new FileOutputStream("example.xlsx");
workbook.write(fileOut);
fileOut.close();
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
//删除有效性
FileInputStream fis = new FileInputStream("example.xlsx");
XSSFWorkbook tmpBook = new XSSFWorkbook(fis);
tmpBook.setForceFormulaRecalculation(true);
XSSFSheet tmpSheet = tmpBook.getSheet("TestSheet");
/**核心部分 tmpSheet.getCTWorksheet().unsetDataValidations(); */
List<XSSFDataValidation> dataValidationsDeleted = tmpSheet.getDataValidations();
if(dataValidationsDeleted.size()!=0){
//方法一 推荐使用
tmpSheet.getCTWorksheet().unsetDataValidations();
//方法二 缺点:打开execl会报错,但是点击自动修复,也能正常打开
// while(tmpSheet.getCTWorksheet().getDataValidations().sizeOfDataValidationArray()!=0){
// // 可以调试时候看validations的数据结构,里面有你之前积累的数据有效性
// //在这里全部删除
// tmpSheet.getCTWorksheet().getDataValidations().removeDataValidation(0);
//
// }
}
fis.close();
FileOutputStream fos = new FileOutputStream("exampleDeletedValidation.xlsx");
tmpBook.write(fos);
tmpBook.close();
fos.close();
}
}