EXCEL批量处理批注

前言：某项目需要通过取excel的批注对数据进行处理，但是excel数据量大，没法通过简单的复制粘贴给每一条数据增加批注，唯一的突破点就是每一列的批注都是有规律的。

思路：1、批量增加批注；2、批量修改批注。

解决方式：

        1）编辑好一行的批注，ctrl+c，选中后面要编辑的所有行，ctrl+v（因为批注没有办法通过拖动进行复制）

        2）原文件通过修改后缀直接修改为.zip文件

        3）双击zip文件，进入xl目录下，找到comments1.xml文件，双击打开，会发现好多“1001”，你编辑的所有批注都在这里，那么接下来就简单多了，替换字符串就好了

        4）本人替换字符串的方式是通过java代码处理的：

             4.1）取出comments1.xml文件的头尾

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<comments xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><authors><author>Administrator</author></authors><commentList><comment ref="B2" authorId="0">

</comment></commentList></comments>

             4.2）comments1.xml中的1001全部替换为某特定字符串（本人替换为9999），双引号替换为反斜杠加双引号

             4.3）excel中的“序号”列中的数据取出来，编辑为字符串数组（作为代码中的目标字符串数组）

             4.4）comments1.xml中的文本内容取出来（作为代码中的文本内容）

             4.5）代码处理：

package dealString;

public class replaceString {
	
	public static void main(String argsp[]){
		 //目标字符串数组
		 String arr [] = {"1001","1002","1003","1004","1005","1006","1007",
				 "1008","1009","1010","1011","1012","1013","1014","1015",
				 "1016","1017","1018","1019","1020","1021","1022","1023",
				 "1024","1025","1026","1027","1028","1029","1030","1031",
				 "1032","1033","1034","1035","1036","1037","1038","1039",
				 "1040","1041","1042","1043","1044","1045","1046","1047",
				 "1048","1049","1050","1051","1052","1053","1054","1055",
				 "1056","1057","1058","1059","1060","1061","1062","1063",
				 "1064","1065","1066","1067","1068","1069","1070","1071"};
		 //文本内容(文本内容太长，此处省略)
		 String pl = "<text><r><rPr><sz val=\"9\"/><rFont val=\"宋体\"/>........</text>";
		 for(int i = 0 ; i < arr.length ; i ++){
			 String a = arr[i];
			 int k = 0;
			 do{
				 //字符串替换
				 pl = pl.replaceFirst("9999",a);
				 k++;
			 }while(k<5);//5列
		 }
		 System.out.println(pl);
	}
}

             4.4）代码执行完之后，复制console打印出来的内容，替换进comments1.xml中（原头尾替换进去，原文件中的反斜杠有可能在替换字符串时丢失），保存文件

        5）修改文件后缀，由.zip改为.xlsx，打开文件你会发现大功告成