作者:乔山办公网日期:
返回目录:excel表格制作
方法一:公式
假设内容在A2—A4单元格,则在B2单元格输入:
下拉,完成
方法二:VBA
按Alt+F11,进入VBA编辑e79fa5e98193e4b893e5b19e330界面,左上角【工程】栏空白处右键,插入-模块,复制粘贴以下代码并相应修改,按F5键运行即可实现一次替换。
Sub replaceThem()
colIdxBefore = 1 '需替换内容所在列
colIdxAfter = 1 '替换后内容所在列
Dim oldWords As String '待替换字符,用|分隔
Dim newWords As String '替换后字符,用|分隔
oldWords = "他跑了|米"
newWords = "He ran | meters"
With Worksheets(2) '这里指定要操作的工作表
For i = 2 To .UsedRange.Rows.Count
.Cells(i, colIdxAfter).Value = repStr(.Cells(i, colIdxBefore).Value, oldWords, newWords)
Next
End With
End Sub
Function repStr(myStr As String, oldstr As String, newstr As String)
spl = "|" '分隔符
os = Split(oldstr, spl) '需替换内容,用|分隔
ns = Split(newstr, spl) '替换后内容,用|分隔
uos = UBound(os)
uns = UBound(ns)
If uos = uns Then '判断 替换内容 与 替换后内容 数量是否相同
For i = 0 To UBound(os)
myStr = replace(myStr, os(i), ns(i))
Next
repStr = myStr
Else
MsgBox "被替换字符有 " & uos & " 组,而替换字符有 " & uns & " 组。" & vbCrLf & "请检查!"
Exit Function
End If
End Function
java将多段内容分别写入不同的txt文件中
public class FileStreamDemo {
public static void main(String[] args) {
// 声明多行内容
String multiText = "行1\n" +
"行2\n" +
"行3\n" +
"行4\n" +
"行5\n" +
"\n" +
"\n" +
"行6";
BufferedReader bReader = new BufferedReader(new StringReader(multiText));
String lineStr = null;
try {
int index = 0;
while ((lineStr = bReader.readLine()) != null) {
if (index == 3) {
index = 0;
}
writeFile(new File("file", "content_" + index + ".txt"), lineStr);
index++;
}
System.out.println("写入成功");
} catch (Exception e) {
e.printStackTrace();
System.out.println("写入失败:" + e.getMessage());
}
}
/**
* 写入文本内容到文件中
*
* @param file 写入的文件路径和文件名e69da5e887aae799bee5baa6333
* @param content 写入的内容
*/
private static void writeFile(File file, String content) throws IOException {
// 本地如果不存在,创建一个
if (!file.exists()) {
File parentPath = file.getParentFile();
parentPath.mkdirs(); // 创建目录
file.createNewFile(); // 创建文件
}
System.out.println("准备写入文件:" + file.getAbsolutePath());
FileOutputStream fos = new FileOutputStream(file, true);
BufferedWriter bWriter = new BufferedWriter(new OutputStreamWriter(fos));
bWriter.write(content);
bWriter.write("\n");
bWriter.flush();
bWriter.close();
}
}
public static boolean readExcel(String filePath){
boolean success=false;
Workbook rwb = null;
try{
InputStream is = new FileInputStream(filePath);
rwb = Workbook.getWorkbook(is);
// 获取第一张Sheet表7a64e4b893e5b19e363
Sheet rs = rwb.getSheet(0);
for (int k = 1; k < rs.getRows(); k++){
String major=rs.getCell(0, k).getContents();
String year=rs.getCell(1, k).getContents();
String class1=rs.getCell(2, k).getContents();
String number1=rs.getCell(3, k).getContents();
String name=rs.getCell(4, k).getContents();
String sex=rs.getCell(5, k).getContents();
String people=rs.getCell(6, k).getContents();
String area=rs.getCell(7, k).getContents();
String birth=rs.getCell(8, k).getContents();
String bodyid=rs.getCell(9, k).getContents();
String phone=rs.getCell(10, k).getContents();
String hostel=rs.getCell(11, k).getContents();
String father=rs.getCell(12, k).getContents();
String mather=rs.getCell(13, k).getContents();
String tel=rs.getCell(14, k).getContents();
String studentstate=rs.getCell(15, k).getContents();
String level1=rs.getCell(16, k).getContents();
String party=rs.getCell(17, k).getContents();
String duties=rs.getCell(18, k).getContents();
String english=rs.getCell(19, k).getContents();
String computer=rs.getCell(20, k).getContents();
String address=rs.getCell(21, k).getContents();
String personstation=rs.getCell(22, k).getContents();
String fundingstation=rs.getCell(23, k).getContents();
String awardstation=rs.getCell(24, k).getContents();
String punishmentstation=rs.getCell(25, k).getContents();
String other1=rs.getCell(26, k).getContents();
String picturepath="";
if(major.equals("")||class1.equals("")||name.equals("")||number1.equals("")){
success=false;
break;
}else{
String str1="insert into information(major,year,class1,number1,name,sex,people,area,birth,bodyid,phone,hostel,father,mather,tel,studentstate,level1,party,duties,english,computer,address,personstation,fundingstation,awardstation,punishmentstation,other1,picturepath)values('"+major+"','"+year+"','"+class1+"','"+number1+"','"+name+"','"+sex+"','"+people+"','"+area+"','"+birth+"','"+bodyid+"','"+phone+"','"+hostel+"','"+father+"','"+mather+"','"+tel+"','"+studentstate+"','"+level1+"','"+party+"','"+duties+"','"+english+"','"+computer+"','"+address+"','"+personstation+"','"+fundingstation+"','"+awardstation+"','"+punishmentstation+"','"+other1+"','"+picturepath+"')";
CommonCode.addInfo(str1);
CommonCode.closeAll();
success=true;
}
}
rwb.close();
}catch (Exception e){
e.printStackTrace();
}
return success;
}
用VBA代码解决(前提是你把所有的TXT放到同一个文件夹内,且无其他文件),时间关系,代码我就不写了!仅给你个思路供参考:来一、用浏览文件夹控件选择指定目录二、遍历指定目录下的所有文件,每遍历一个TXT文件——1、提取不带后缀的文件名2、利用循环语句找到首行从B列开始的第一个空单元格,退源出循环3、把不带后缀的文件名写入找到的首行第一个空单元格内4、打开当前的TXT文件并利用循环语句逐行读入,然后执行:1)查找当前行从右向知左第一个“,”(逗号)的位置。注道意逗号的中英文状态。2)截取当前行最后一个逗号后面的内容,并转换为数值格式。3)把数值填写到对应列的对应行内读取下一个TXT文件。