最近做一个excel导出,需要在导出的excel里加数据验证;自己觉得在程序里面加数据验证太麻烦;自己想了一个办法就是先在一个excel模板里手动加上数据验证;然后将模板读入程序里面,修改修改,添点数据;然后再导出来;写的过程中才发现poi的数据验证居然只能添加不能修改和删除,那我也就只能自己写修改和删除了;这里把我用到的方法整理了一下。
突然发现代码有些多,这里说一下思路,修改数据验证就是用反射读出poi中的一个对象,修改了一番然后再塞进去;shiftRow合并乱的问题,是每次shiftRow前 先读出合并对象,复制一份 执行完shiftRow之后 清除原有合并 然后自己做合并变换,再添加进去。
/**
* xlsx处理
*/
public class XObjectExcelView {
/**
* @Title fullData
* @Description (填充数据)
* @return XSSFSheet
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午3:32:13
*/
public void fullData(XSSFSheet sheet,List<Map<String,Object>> data) {
XSSFRow row=null;
XSSFCell cell=null;
/*开始填入数据*/
if(data!=null){
Object dataTemp=null;
int colIndex,rowIndex;
float rowHeight;
for(Map<String,Object> dd:data){
colIndex=(Integer)dd.get("colIndex");
rowIndex=(Integer)dd.get("rowIndex");
row=sheet.getRow(rowIndex);
if(row==null)row=sheet.createRow(rowIndex);
if(dd.get("height")!=null){
rowHeight=(Float)dd.get("height");
row.setHeightInPoints(rowHeight);
}
cell=getCell(sheet,rowIndex,colIndex);
if(cell==null) {
cell=row.createCell(colIndex);
}
dataTemp=dd.get("value");
if(dataTemp.getClass().getName().equals("java.lang.String")){
if(dataTemp.toString().indexOf("`")==0){
cell.setCellFormula(dataTemp.toString().substring(1));
}else{
cell.setCellValue(String.valueOf(dataTemp));
}
}else{
cell.setCellValue(Double.parseDouble(String.valueOf(dataTemp)));
}
}
}
}
/**
* @Title getCell
* @Description (得到单元格)
* @return XSSFCell
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午5:23:20
*/
protected XSSFCell getCell(XSSFSheet sheet, int row, int col) {
XSSFRow sheetRow = sheet.getRow(row);
if (sheetRow == null) {
sheetRow = sheet.createRow(row);
}
XSSFCell cell = sheetRow.getCell(col);
if (cell == null) {
cell = sheetRow.createCell(col);
}
return cell;
}
/**
* @Title removeRow
* @Description (Remove a row by its rowIndex )
* @param sheet a Excel sheet
* @param rowIndex a 0 based index of removing row
* @return void
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午3:26:27
*/
public void removeRow(XSSFSheet sheet, int rowIndex) {
int lastRowNum=sheet.getLastRowNum();
if(rowIndex>=0&&rowIndex<lastRowNum) {
sheet.shiftRows(rowIndex+1,lastRowNum,-1,true,false);//将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行,以便删除rowIndex行
//sheet.shiftRows(startRow, endRow, n, copyRowHeight, resetOriginalRowHeight);
}else if(rowIndex==lastRowNum){
XSSFRow removingRow=sheet.getRow(rowIndex);
if(removingRow!=null)
sheet.removeRow(removingRow);
}
}
/**
* @Title insertRow
* @Description (insert a row by its rowIndex )
* @param sheet a Excel sheet
* @param rowIndex a 0 based index of adding row
* @return void
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午3:24:45
*/
public void insertRow(XSSFSheet sheet, int rowIndex) {
int lastRowNum=sheet.getLastRowNum();
XSSFRow row = sheet.getRow(rowIndex);
if(rowIndex>=0&&rowIndex<=lastRowNum)
sheet.shiftRows(rowIndex,lastRowNum,1,true,false);//将行号为rowIndex一直到行号为lastRowNum的单元格全部下移一行,以便添加rowIndex行
sheet.createRow(rowIndex);
if(rowIndex==lastRowNum+1){
sheet.createRow(rowIndex);
}
XSSFRow insertRow= sheet.getRow(rowIndex);
insertRow.setRowStyle(row.getRowStyle());
insertRow.setHeight(row.getHeight());
int lastCellNum = row.getLastCellNum();
for(int i=0;i<lastCellNum;i++) {
insertRow.createCell(i);
insertRow.getCell(i).setCellStyle(row.getCell(i).getCellStyle());
}
}
/**
* @Title removeMergedRegion
* @Description (删除指定区域)
* @return void
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午3:23:05
*/
@Deprecated
public void removeMergedRegion(XSSFSheet sheet,CellRangeAddress mr) {
int numMergedRegions = sheet.getNumMergedRegions();
List <Integer> removemrList=new ArrayList<Integer>();
for(int i=0;i<numMergedRegions;i++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
if(mr.getFirstRow()==mergedRegion.getFirstRow()&&mr.getLastRow()==mergedRegion.getLastRow()&&mr.getFirstColumn()==mergedRegion.getFirstColumn()&&mr.getLastColumn()==mergedRegion.getLastColumn()) {
removemrList.add(i);
}
}
for (int i=removemrList.size()-1;i>=0;i--) {
sheet.removeMergedRegion(removemrList.get(i));
}
}
/**
* @Title removeMergedRegionIn
* @Description (删除该区域中的所有合并)
* @return void
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午3:21:34
*/
@Deprecated
public void removeMergedRegionIn(XSSFSheet sheet,CellRangeAddress mr) {
int numMergedRegions = sheet.getNumMergedRegions();
List <Integer> removemrList=new ArrayList<Integer>();
for(int i=0;i<numMergedRegions;i++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
if(mr.getFirstRow()<=mergedRegion.getFirstRow()&&mr.getLastRow()>=mergedRegion.getLastRow()&&mr.getFirstColumn()<=mergedRegion.getFirstColumn()&&mr.getLastColumn()>=mergedRegion.getLastColumn()) {
removemrList.add(i);
}
}
for (int i=removemrList.size()-1;i>=0;i--) {
sheet.removeMergedRegion(removemrList.get(i));
}
}
/**
* @Title removeMergedRegion
* @Description (在删除一行时,删除并修改合并)
* @return void
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午3:15:04
*/
public void removeMergedRegion(XSSFSheet sheet,int rowIndex) {
List<CellRangeAddress> mergedRegionList=getSheetMergedRegionList(sheet);
this.sheetClearMergedRegion(sheet);
List<CellRangeAddress> addmrList=new ArrayList<CellRangeAddress>();
for(int i=0;i<mergedRegionList.size();i++) {
CellRangeAddress mergedRegion = mergedRegionList.get(i);
if(mergedRegion.getFirstRow()==rowIndex&&mergedRegion.getLastRow()==rowIndex) {
}else if(mergedRegion.getFirstRow()<=rowIndex&&mergedRegion.getLastRow()>=rowIndex) {
mergedRegion.setLastRow(mergedRegion.getLastRow()-1);
addmrList.add(mergedRegion);
}else if(mergedRegion.getFirstRow()>rowIndex&&mergedRegion.getLastRow()>rowIndex) {
mergedRegion.setFirstRow(mergedRegion.getFirstRow()-1);
mergedRegion.setLastRow(mergedRegion.getLastRow()-1);
addmrList.add(mergedRegion);
}else {
addmrList.add(mergedRegion);
}
}
//重新添加合并
for (CellRangeAddress cellRangeAddress : addmrList) {
sheet.addMergedRegion(cellRangeAddress);
}
}
/**
* @Title insertMergedRegion
* @Description (插入一行时,插入并修改合并,在当前rowIndex上添加,原有行自动被移到下一行)
* @return void
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午3:08:52
*/
public void insertMergedRegion(XSSFSheet sheet,int rowIndex) {
List<CellRangeAddress> mergedRegionList=getSheetMergedRegionList(sheet);
this.sheetClearMergedRegion(sheet);
List<CellRangeAddress> addmrList=new ArrayList<CellRangeAddress>();
for(int i=0;i<mergedRegionList.size();i++) {
CellRangeAddress mergedRegion = mergedRegionList.get(i);
if(mergedRegion.getFirstRow()==rowIndex&&mergedRegion.getLastRow()==rowIndex) {
addmrList.add(mergedRegion);
addmrList.add(new CellRangeAddress(mergedRegion.getFirstRow()+1,mergedRegion.getLastRow()+1,mergedRegion.getFirstColumn(),mergedRegion.getLastColumn()));
}else if(mergedRegion.getFirstRow()<=rowIndex&&mergedRegion.getLastRow()>=rowIndex) {
mergedRegion.setLastRow(mergedRegion.getLastRow()+1);
addmrList.add(mergedRegion);
}else if(mergedRegion.getFirstRow()>rowIndex&&mergedRegion.getLastRow()>rowIndex) {
mergedRegion.setFirstRow(mergedRegion.getFirstRow()+1);
mergedRegion.setLastRow(mergedRegion.getLastRow()+1);
addmrList.add(mergedRegion);
}else {
addmrList.add(mergedRegion);
}
}
//重新添加合并
for (CellRangeAddress cellRangeAddress : addmrList) {
sheet.addMergedRegion(cellRangeAddress);
}
}
/**
* @Title sheetClearMergedRegion
* @Description (删除sheet中的所有合并区域)
* @return void
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午3:08:01
*/
public void sheetClearMergedRegion(XSSFSheet sheet) {
int numMergedRegions=sheet.getNumMergedRegions();
for(int i=0;i<numMergedRegions;i++) {
sheet.removeMergedRegion(0);
}
}
/**
* @Title sheetAddMergedRegionList
* @Description (向sheet中添加合并集合)
* @return void
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午3:06:45
*/
public void sheetAddMergedRegionList(XSSFSheet sheet,List<CellRangeAddress> mergedRegionList) {
for(int i=0;i<mergedRegionList.size();i++) {
CellRangeAddress cellRangeAddress = mergedRegionList.get(i);
sheet.addMergedRegion(cellRangeAddress);
}
}
/**
* @Title getSheetMergedRegionList
* @Description (得到该sheet的合并信息)
* @return List<CellRangeAddress>
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午3:05:28
*/
public List<CellRangeAddress> getSheetMergedRegionList(XSSFSheet sheet) {
List<CellRangeAddress> mergedRegionList=new ArrayList<CellRangeAddress>();
int numMergedRegions = sheet.getNumMergedRegions();
for(int i=0;i<numMergedRegions;i++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
mergedRegionList.add(mergedRegion.copy());
}
return mergedRegionList;
}
/**
* @Title removeDataValidation
* @Description (删除一个行时,删除并修改验证)
* @return void
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午3:04:13
*/
public void removeDataValidation(XSSFSheet sheet,int rowIndex) throws Exception {
ReflectHelper reflect=new ReflectHelper();
CTWorksheet worksheet=(CTWorksheet) reflect.getValueByFieldName(sheet, "worksheet");
CTDataValidations dataValidations = worksheet.getDataValidations();
// List <Integer> removemrList=new ArrayList<Integer>();
Node domNode = dataValidations.getDomNode();
NodeList childNodes = domNode.getChildNodes();
int length = childNodes.getLength();
for (int i = 0; i < length; i++) {
Node item = childNodes.item(i);
NamedNodeMap attrs = item.getAttributes();
boolean flag=false;
String linkedRegion="";
for(int j=0;j<attrs.getLength();j++) {
Node item2 = attrs.item(j);
if("type".equals(item2.getNodeName())&&"list".equals(item2.getNodeValue())) {
String str = item.getFirstChild().getFirstChild().getNodeValue();
if(str.indexOf("INDIRECT")>-1) {
flag=true;
linkedRegion=str.substring(str.indexOf("(")+1, str.indexOf(")"));
}
}
if("sqref".equals(item2.getNodeName())) {
String nodeValue = item2.getNodeValue();
List<int[]> regions = this.stringToRegions(nodeValue);
int k=regions.size();
for (int m=0;m<k;m++) {
int[] region = regions.get(m);
if(region[0]==rowIndex&®ion[1]==rowIndex) {
regions.remove(m);
m--;
k--;
if(flag&&m==0) {
linkedRegion="";
}
}else if(region[0]<=rowIndex&®ion[1]>=rowIndex) {
region[1]=region[1]-1;
if(flag&&m==0) {
linkedRegion=linkedRegion.replaceAll("[0-9]*$", "")+(region[0]+1);
}
}else if(region[0]>rowIndex&®ion[1]>rowIndex) {
region[0]=region[0]-1;
region[1]=region[1]-1;
if(flag&&m==0) {
linkedRegion=linkedRegion.replaceAll("[0-9]*$", "")+(region[0]+1);
}
}
}
if(regions.isEmpty()) {
//domNode.removeChild(item);
}else {
String regionsStr = this.regionsToString(regions);
item2.setNodeValue(regionsStr);
}
if(flag) {
item.getFirstChild().getFirstChild().setNodeValue("INDIRECT("+linkedRegion+")");;
}
}
}
}
dataValidations = worksheet.getDataValidations();
reflect.setValueByFieldName(sheet, "worksheet", worksheet);
}
/**
* @Title insertDataValidation
* @Description (插入一个行时,添加并修改验证,在当前rowIndex上添加,原有行自动被移到下一行)
* @return void
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午3:00:53
*/
public void insertDataValidation(XSSFSheet sheet,int rowIndex) throws Exception {
ReflectHelper reflect=new ReflectHelper();
CTWorksheet worksheet=(CTWorksheet) reflect.getValueByFieldName(sheet, "worksheet");
CTDataValidations dataValidations = worksheet.getDataValidations();
// List <Integer> removemrList=new ArrayList<Integer>();
Node domNode = dataValidations.getDomNode();
NodeList childNodes = domNode.getChildNodes();
int length = childNodes.getLength();
for (int i = 0; i < length; i++) {
Node item = childNodes.item(i);
NamedNodeMap attrs = item.getAttributes();
boolean flag=false;
String linkedRegion="";
for(int j=0;j<attrs.getLength();j++) {
Node item2 = attrs.item(j);
if("type".equals(item2.getNodeName())&&"list".equals(item2.getNodeValue())) {
String str = item.getFirstChild().getFirstChild().getNodeValue();
if(str.indexOf("INDIRECT")>-1) {
flag=true;
linkedRegion=str.substring(str.indexOf("(")+1, str.indexOf(")"));
}
}
if("sqref".equals(item2.getNodeName())) {
String nodeValue = item2.getNodeValue();
List<int[]> regions = this.stringToRegions(nodeValue);
int k=regions.size();
for (int m=0;m<k;m++) {
int[] region = regions.get(m);
if(region[0]==rowIndex&®ion[1]==rowIndex) {
regions.add(new int[] {region[0]+1,region[1]+1,region[2],region[3]});
if(flag&&m==0) {
//linkedRegion=linkedRegion;
}
}else if(region[0]<=rowIndex&®ion[1]>=rowIndex) {
region[1]=region[1]+1;
if(flag&&m==0) {
//linkedRegion=linkedRegion;
}
}else if(region[0]>rowIndex&®ion[1]>rowIndex) {
region[0]=region[0]+1;
region[1]=region[1]+1;
if(flag&&m==0) {
linkedRegion=linkedRegion.replaceAll("[0-9]*$", "")+(region[0]+1);
}
}
}
if(regions.isEmpty()) {
//domNode.removeChild(item);
}else {
String regionsStr = this.regionsToString(regions);
item2.setNodeValue(regionsStr);
}
if(flag) {
item.getFirstChild().getFirstChild().setNodeValue("INDIRECT("+linkedRegion+")");;
}
}
}
}
dataValidations = worksheet.getDataValidations();
reflect.setValueByFieldName(sheet, "worksheet", worksheet);
}
public int charToNum(char a) {
return a-65;
}
public char numToChar(int a) {
return (char) (a+65);
}
/**
* @Title getColNum
* @Description (字母列号转数字)
* @return int
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午3:00:03
*/
public int getColNum(String col) {
int num=0;
char[] charArray = col.toCharArray();
for (int i = 0; i < charArray.length; i++) {
num=num*26+this.charToNum(charArray[i])+1;
}
return num-1;
}
/**
* @Title getColString
* @Description (数字转字母列号)
* @return String
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午2:59:09
*/
public String getColString(int num) {
String col="";
int count = 1;
num=num+1;
while((int)(num/Math.pow(26, count-1))>0){
int temp = ((int)(num % Math.pow(26,count)))/((int)Math.pow(26, count-1));
col = this.numToChar(temp-1) + col;
count++;
}
return col;
}
/**
* @Title stringToRegions
* @Description (字符串转区域,返回一个区域集合,int[] 为 int firstRow, int lastRow, int firstCol, int lastCol)
* @return List<int[]>
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午2:58:02
*/
public List<int []> stringToRegions(String str){
List<int []> regions=new ArrayList<int[]>();
String[] split = str.split(" ");
int firstRow=0;
int lastRow=0;
int firstCol=0;
int lastCol=0;
String tempStr="";
for (int i = 0; i < split.length; i++) {
int[] arr=new int[4];
if(split[i].contains(":")) {
String[] split2 = split[i].split(":");
tempStr=split2[0].replaceAll("^[A-Z]*", "");
firstRow=Integer.parseInt(tempStr)-1;
tempStr=split2[0].replaceAll("[0-9]*$", "");
firstCol=this.getColNum(tempStr);
tempStr=split2[1].replaceAll("^[A-Z]*", "");
lastRow=Integer.parseInt(tempStr)-1;
tempStr=split2[1].replaceAll("[0-9]*$", "");
lastCol=this.getColNum(tempStr);
}else {
tempStr=split[i].replaceAll("^[A-Z]*", "");
firstRow=Integer.parseInt(tempStr)-1;
tempStr=split[i].replaceAll("[0-9]*$", "");
firstCol=this.getColNum(tempStr);
lastRow=firstRow;
lastCol=firstCol;
}
arr[0]=firstRow;
arr[1]=lastRow;
arr[2]=firstCol;
arr[3]=lastCol;
regions.add(arr);
}
return regions;
}
/**
* @Title regionsToString
* @Description (区域转字符串)
* @return String
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午2:55:54
*/
public String regionsToString(List<int[]> regions) {
String str="";
for(int i=0;i<regions.size();i++) {
if(i>0) {
str+=" ";
}
int[] region = regions.get(i);
if(region[0]==region[1]&®ion[2]==region[3]) {
str+=this.getColString(region[2])+(region[0]+1);
}else {
str+=this.getColString(region[2])+(region[0]+1)+":"+this.getColString(region[3])+(region[1]+1);
}
}
return str;
}
/**
* @Title getCopyMergedRegionList
* @Description (复制合并区域)
* @return List<CellRangeAddress>
* @author Dream (孟孝龙)
* @date 2019年9月23日 下午2:55:28
*/
public List<CellRangeAddress> getCopyMergedRegionList(XSSFSheet sheet) {
List<CellRangeAddress> mergedRegionList=new ArrayList<CellRangeAddress>();
int numMergedRegions = sheet.getNumMergedRegions();
for(int i=0;i<numMergedRegions;i++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
mergedRegionList.add(mergedRegion.copy());
}
return mergedRegionList;
}
}