怎么删除excel旧的已有sheet

seashellingolds

各位大大
我想把数据输出到xls文件的sheet中，自己定义名字
xlswrite可以将输出写到当前已有sheet，怎么有办法改名呢？
或是添加新sheet用我定义的名字，怎么删除旧的已有的sheet呢？

非常感谢

[ 本帖最后由 ChaChing 于 2009-10-24 10:41 编辑 ]

friendchj

xlswrite(filename, M, sheet) writes matrix M to the specified worksheet sheet in the file filename. The sheet argument can be either a positive, double scalar value representing the worksheet index, or a quoted string containing the sheet name. The sheet argument cannot contain a colon.

seashellingolds

帮助文件我看过了，主任引用的第一句话说写入到指定工作表中，意味着无法修改工作表名称？ 要想让工作表名称符合自己要求必须创建新表而保留着旧表无法从matlab里删去？

ChaChing

From help
If sheet does not exist, a new sheet is added at the end of the worksheet collection.

seashellingolds

主任们所言都是事实，我的问题还是没有解决
目前我认为
已存在sheet无法改名
只能靠添加sheet来进行命名，但默认生成sheet也无法删除
总而言之，matlab不能解决我提的问题？
手动进excel解决
就此封贴吧

ChaChing

"当前已有sheet改名, 删除旧的已有的sheet?"
个人好奇又不才, 可问为何有此需求?

seashellingolds

其实我就想1.把数据导入一个sheet，2. 让这个sheet有我起得名字， 3.多余的sheet不要
1. 主要目的
2. 就像书的每个章节都有个题目，不能只叫Chapter 1, Chapter 2.....
3. 同上，不想书Chapter 1,2,3都是空的，从Chapter 4 开始写，前面占用的资源浪费，且不方便阅读

ChaChing

数据导入一个sheet并依LZ意思进行命名, 进入Excel删除不必要的sheet, 之后新sheet亦可依LZ意思进行命名!
好像仅能这样了!:loveliness:

friendchj

example:
clc
clear
a=1:5;
xlswrite('my.xls',a,'myown');
DeleteEmptyExcelSheets('my.xls')

Reference：http://www.mathworks.com/matlabcentral/newsreader/view_thread/257376

1. 
   
2. % DeleteEmptyExcelSheets.m
   
3. % DeleteEmptyExcelSheets: deletes all empty sheets in an xls-file
   
4. %
   
5. %==========================================================================
   
6. % Version : 1.0
   
7. % Author : hnagel
   
8. % Date : 27/04/2007
   
9. % Tested : 02/05/2007 (DR)
   
10. %==========================================================================
    
11. %
    
12. % This function looped through all sheets and deletes those sheets
    
13. %that are
    
14. % empty. Can be used to clean a newly created xls-file after all
    
15. %results
    
16. % have been saved in it.
    
17. %
    
18. % References: Torsten Jacobsen, "delete standard excel sheet"
    
19. %---------------------------------------------------------------------
    
20. %
    
21. % Input:
    
22. %
    
23. % fileName: name of xls file
    
24. %
    
25. %---------------------------------------------------------------------
    
26. %
    
27. % Output:
    
28. %
    
29. % none
    
30. %
    
31. %---------------------------------------------------------------------
    
32. %
    
33. % See also XLSWRITE
    
34. %---------------------------------------------------------------------
    
35. % Changes
    
36. %---------------------------------------------------------------------
    
37. %
    
38. % Name :
    
39. % Date :
    
40. % Description:
    
41. % Indicated :
    
42. function DeleteEmptyExcelSheets(fileName)
    
43. % Check whether the file exists
    
44. if ~exist(fileName,'file')
    
45. error([fileName ' does not exist !']);
    
46. else
    
47. % Check whether it is an Excel file
    
48. typ = xlsfinfo(fileName);
    
49. if ~strcmp(typ,'Microsoft Excel Spreadsheet')
    
50. error([fileName ' not an Excel sheet !']);
    
51. end
    
52. end
    
53. % If fileName does not contain a "" the name of the current path is
    
54. % added to fileName. The reason for this is that the full path is required
    
55. % for the command "excelObj.workbooks.Open(fileName)" to work properly
    
56. if isempty(strfind(fileName,'\'))
    
57. fileName = [cd '\' fileName];
    
58. end
    
59. excelObj = actxserver('Excel.Application');
    
60. excelWorkbook = excelObj.workbooks.Open(fileName);
    
61. worksheets = excelObj.sheets;
    
62. sheetIdx = 1;
    
63. sheetIdx2 = 1;
    
64. numSheets = worksheets.Count;
    
65. % Prevent beeps from sounding if we try to delete a non-empty
    
66. % worksheet.
    
67. excelObj.EnableSound = false;
    
68. % Loop over all sheets
    
69. while sheetIdx2 <= numSheets
    
70. % Saves the current number of sheets in the workbook
    
71. temp = worksheets.count;
    
72. % Check whether the current worksheet is the last one. As there
    
73. % always
    
74. % need to be at least one worksheet in an xls-file the last sheet
    
75. % must
    
76. % not be deleted.
    
77. if or(sheetIdx>1,numSheets-sheetIdx2>0)
    
78. % worksheets.Item(sheetIdx).UsedRange.Count is the number of used
    
79. % cells.
    
80. % This will be 1 for an empty sheet. It may also be one for
    
81. %vcertain other
    
82. % cases but in those cases, it will beep and not actually delete
    
83. % the sheet.
    
84. if worksheets.Item(sheetIdx).UsedRange.Count == 1
    
85. worksheets.Item(sheetIdx).Delete;
    
86. end
    
87. end
    
88. % Check whether the number of sheets has changed. If this is not
    
89. % the case the counter "sheetIdx" is increased by one.
    
90. if temp == worksheets.count;
    
91. sheetIdx = sheetIdx + 1;
    
92. end
    
93. sheetIdx2 = sheetIdx2 + 1; % prevent endless loop...
    
94. end
    
95. excelObj.EnableSound = true;
    
96. excelWorkbook.Save;
    
97. excelWorkbook.Close(false);
    
98. excelObj.Quit;
    
99. delete(excelObj);
    
100. return;
     

复制代码

seashellingolds

这都让你给找到了，赞一个~~~ 多谢多谢，Matlab依旧还是强大滴
worksheets.Item(sheetIdx).Delete; 这句将空表删掉，如果改成worksheets.Item(sheetIdx).Move啥的或许会实现改名的效果
我顶一下，看有没牛闲人试试写一个改名的脚本，那真是感激不尽了呀~~

送给ls
小红花+1 理由：回复具有直接解决问题性

[ 本帖最后由 seashellingolds 于 2009-10-24 15:32 编辑 ]

ChaChing

原帖由 seashellingolds 于 2009-10-24 15:29 发表

                               
登录/注册后可看大图

...如果改成worksheets.Item(sheetIdx).Move啥的或许会实现改名的效果 ...

我想楼主也非等闲之辈, 或许楼主有兴趣的话, 不是就可以直接试试看!?
个人以为试试看是最好的方式

friendchj

一个笨的方法是读入你要修改名字sheet的数据并清空，存到你要重新命名的sheet中，最后删除那个空sheet。

oquicko

好帖，我顶

zhs_163com

friendchj 发表于 2009-10-24 05:07

                               
登录/注册后可看大图

example:
clc
clear

太犀利了，哥们，佩服啊

twm0226

绝对好贴啊！
学习学习！