C# Excel 儲存格編輯

標籤：檔案   os   for   io   工作   re   

public class ExcelReport
    {
  //Excel 檔案修改要引用COM組件Microsoft Excel 11.0 Object Library
  //using Microsoft.Office.Interop.Excel;
  /// <summary>
        /// 儲存格修改
        /// </summary>
        /// <param name="filePath">excel路徑</param>
  public void writeExcelEdit(string filePath)
  {
   Application myExcel = null;//引用Excel Application類別
            Workbook myBook = null;//引用活頁簿類別
            Worksheet mySheet = null;//引用工作表類別
            Range myRange = null;//引用Range類別
            try
            {
                myExcel = new Application();//執行個體化Excel Application
                myBook = myExcel.Workbooks._Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                myExcel.DisplayAlerts = false;//停用警告
                myExcel.Visible = false;      //Excel 不可見
                mySheet = myBook.Worksheets[1];//Excel檔案開啟活頁簿的第一個檔案
    
    int rowindex = 1;//要修改的儲存格行
                int columnIndex = 2;//儲存格列
    {//修改儲存格
     myRange = mySheet.Cells[rowindex, columnIndex];
                    myRange.Value = 12;//把座標是[1,2]的儲存格值修改成12
    }
    {//修改儲存格參照函數=Sum(A1:A3)
     myRange = mySheet.Cells[rowindex, columnIndex];
                    string strColName1 = getExcelColumnLabel(cindex + 1);
                    myRange.Formula = string.Format("=SUM({0}:{1})", strColName1 + (rindex-3), strColName1 + (rindex - 1));
    }
    
    myBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                myBook.Close(false, Type.Missing, Type.Missing);//關閉
                myExcel.Quit();//關閉Excel
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);//釋放Excel資源
   }
   catch(Exception ex)
   {
    throw new Exception(ex);
   }
   finally
   {
    myRange = null;
                mySheet = null;
                myBook = null;
                myExcel = null;
                GC.Collect();//強制記憶體回收
   }
  }
  
  //
  //儲存格格式修改都可以用Range類來實現
  //
  
  /// <summary>
        /// 根據列編號擷取列別名
        /// </summary>
        /// <param name="num">列編號（從0開始）</param>
        /// <returns></returns>
        private String getExcelColumnLabel(int num)
        {
            String temp = "";
            double i = Math.Floor(Math.Log(25.0 * (num) / 26.0 + 1) / Math.Log(26)) + 1;
            if (i > 1)
            {
                double sub = num - 26 * (Math.Pow(26, i - 1) - 1) / 25;
                for (double j = i; j > 0; j--)
                {
                    //temp = temp + (char)(sub / Math.Pow(26, j - 1) + 65);//列號從0開始
                    temp = temp + (char)(sub / Math.Pow(26, j - 1) + 64);  //列號從1開始
                    sub = sub % Math.Pow(26, j - 1);
                }
            }
            else
            {
                //temp = temp + (char)(num + 65);//列號從0開始
                temp = temp + (char)(num + 64);  //列號從1開始
            }
            return temp;
        }
    }