我有很多单元格需要转换,使每个单元格中的第一个字母大写。例如:cook, chef, fireman
变成 Cook, Chef, Fireman
。
- 我使用OpenOffice.org创建了电子表格,但是似乎只有“全部大写”或“全部小写”的选项。
- 如果OpenOffice.org无法实现,我可以在OpenOffice.org中编辑或将其导出为CSV,并使用BASH脚本编辑CSV。
如何将电子表格中每个单元格的第一个字母更改为大写?
我有很多单元格需要转换,使每个单元格中的第一个字母大写。例如:cook, chef, fireman
变成 Cook, Chef, Fireman
。
如何将电子表格中每个单元格的第一个字母更改为大写?
我刚好可以做这个任务。您需要安装Spreadsheet::ParseExcel和Spreadsheet::WriteExcel模块。
use strict;
use warnings;
use Spreadsheet::ParseExcel::SaveParser;
my $parser = Spreadsheet::ParseExcel::SaveParser->new();
my $workbook = $parser->Parse('Book1.xls');
if ( !defined $workbook ) {
die $parser->error(), ".\n";
}
for my $worksheet ( $workbook->worksheets() ) {
my ( $row_min, $row_max ) = $worksheet->row_range();
my ( $col_min, $col_max ) = $worksheet->col_range();
for my $row ( $row_min .. $row_max ) {
for my $col ( $col_min .. $col_max ) {
my $cell = $worksheet->get_cell( $row, $col );
next unless $cell;
# "ucfirst lc" make sure that only the first letter is capitalized
# if you dont like that just remove lc
$worksheet->AddCell( $row, $col, ucfirst lc $cell->value() );
}
}
}
# ofcouse save your work
$workbook->SaveAs('Book2.xls');
格式 -> 更改大小写 -> 句子首字母大写
,这正是你所需要的。我有一份awk脚本可以完成你想做的事情(我认为是这样)。
这是我的测试输入(test.input):
cook, chef, fireman
cook, chef, fireman
cook, chef, fireman
cook, chef, fireman
house, farm, road
我的awk脚本(up.awk):
# from: http://www.gnu.org/software/gawk/manual/html_node/Join-Function.html
function join(array, start, end, sep, result, i){
if (sep == "")
sep = " "
else if (sep == SUBSEP) # magic value
sep = ""
result = array[start]
for (i = start + 1; i <= end; i++)
result = result sep array[i]
return result
}
BEGIN {
FS="\n";
}
{
# split input on newline
for(i=1;i<=NF;i++) {
# split line on the commas
size = split($i, s, ",")
for(ii=1;ii<=size;ii++) {
# trim whitespace
gsub(/[[:space:]]*/,"",s[ii])
# uppercase first char and glue it back together
s[ii] = toupper(substr(s[ii], 0, 1)) substr(s[ii], 2)
}
# join array back and print it out
print join(s, 1, size, ", ")
}
}
awk -f up.awk test.input >test.output
我的test.output输出结果为:Cook, Chef, Fireman
Cook, Chef, Fireman
Cook, Chef, Fireman
Cook, Chef, Fireman
House, Farm, Road
Perl一行命令:
perl -pwe 's/(?:^|,)\s*\K([a-z])/\U$1/g;' file.csv > fileout.csv
您也可以使用带有-i
开关的原地编辑。例如:perl -pi.bak -we ...
更长的版本,使用Text::CSV_XS。该模块将更加小心地处理您的csv文件。
use strict;
use warnings;
use Text::CSV_XS;
my $csv = Text::CSV_XS->new({
binary => 1,
eol => $/,
});
my $file = shift;
open my $fh, '<', $file or die $!;
while (my $row = $csv->getline($fh)) {
s/([a-z])/\U$1/ for @$row;
$csv->print (*STDOUT, $row);
}
perl 脚本名.pl 文件名.csv > 输出文件名.csv
格式 -> 更改大小写 -> 句子大小写
,完成!看看是否解决了您的问题?你可以使用Python或任何其他编程语言进行一些相当危险的操作。
#!/usr/bin/env python
lastchar = ""
orig_csv = open("yourfile.csv", "r")
new_csv = open("newfile.csv", "w")
for line in orig_csv:
for char in line:
if lastchar == ",":
new_csv.write(char.upper())
else:
new_csv.write(char)
orig_csv.close()
new_csv.close()
将其导出为csv文件。然后执行以下操作。
#psuedo python
out=""
nums = [col numbers to capitalize]
for line in file.read_csv('input.csv'):
for n in nums:
line[n] = line[n].capitalize()
out += ",".join(line) + "\n"
你可以编写一个小的 gawk
和 sed
组合来获取标题大小写 -
gawk -v RS="[,\n]" -v ORS="" '
{a=substr($1,1,1); b=substr($1,2); print toupper(a)b" "; if (NR%3==0) print "\n"}' file |
sed -e 's/ /, /g' -e 's/, *$//'
测试:
[jaypal:~/Temp] cat file
cook, chef, fireman
cook, chef, fireman
cook, chef, fireman
cook, chef, fireman
house, farm, road
[jaypal:~/Temp] gawk -v RS="[,\n]" -v ORS="" '
{a=substr($1,1,1); b=substr($1,2); print toupper(a)b" "; if (NR%3==0) print "\n"}' file |
sed -e 's/ /, /g' -e 's/, *$//'
Cook, Chef, Fireman
Cook, Chef, Fireman
Cook, Chef, Fireman
Cook, Chef, Fireman
House, Farm, Road
显然这不是完美的解决方案,你可能需要根据你有多少行数据或想要的数据进行一些微调。对NR%3
的更改应该足够好。
这个可能适合你:
cat <<! | sed 's/\<[^,]*/\u&/g'
> cook, chef, fireman
> head cook, head chef, chief fireman
> house, farm, road
> !
Cook, Chef, Fireman
Head cook, Head chef, Chief fireman
House, Farm, Road
可能是特定于 GNU sed。