Excel 問題 我有一個Excel,裏面有7張工作表 咁我A1係打日期,例如:1/1/2017,咁我想工作表名稱自動顯示A1儲存格個名 因為我有好多個FILE要改,所以想問有咩快速方法可以將工作表名更改?THZ?

1 個解答

評分
  • John
    Lv 7
    3 年 前
    最佳解答

    唔可以,因為個名唔合法。但20170101就得。如果妳有好多files不如寫個程式做了算吧。

    file: ExcelWorkSheetNameReplacer.cs

    namespace MyTester.Ui.Con

    {

    using System;

    using System.IO;

    using Excel = Microsoft.Office.Interop.Excel;

    public class Program

    {

    public static void Main(string[] args)

    {

    string value = @"20170101";

    // single file update...

    string filename = @"c:\path\to\201702041731.xlsx";

    AppendExcelWorkSheetNameTo(filename, value);

    // multi-files update...

    string path = @"D:\path\to";

    if (Directory.Exists(path))

    {

    var files = Directory.GetFiles(path, @"*.xlsx");

    foreach (var file in files)

    {

    AppendExcelWorkSheetNameTo(file, value);

    }

    }

    }

    private static void AppendExcelWorkSheetNameTo(string filename, string value)

    {

    Excel.Application xls;

    Excel.Workbook wkb;

    object missing = System.Reflection.Missing.Value;

    xls = new Excel.Application();

    wkb = xls.Workbooks.Open(filename);

    foreach (Excel.Worksheet wks in wkb.Worksheets)

    {

    wks.Name = string.Format("{0}{1}", value, wks.Name);

    Console.WriteLine("Excel:{0} WorkSheet:{1} updated", wkb.Name, wks.Name);

    }

    wkb.Close(true, missing, missing);

    xls.Quit();

    cleanup(wkb);

    cleanup(xls);

    }

    private static void cleanup(object sender)

    {

    try

    {

    System.Runtime.InteropServices.Marshal.ReleaseComObject(sender);

    sender = null;

    }

    catch (Exception ex)

    {

    Console.Error.WriteLine(ex.Message);

    }

    finally

    {

    GC.Collect();

    }

    }

    }

    }

    compile:

    csc.exe ExcelWorkSheetNameReplacer.cs /r:Microsoft.Office.Interop.Excel.dll

    execute:

    ExcelWorkSheetNameReplacer

    • John
      Lv 7
      3 年 前舉報

      當然得可惜Excel會自動改為1-FEB-17而且日期轉為名字又一重難度。祝妳好運。

還有問題嗎?立即提問即可得到解答。