C# 操作Excel2003

2021-11-15 02:33:59

1.(新建工程等步骤在这里都省略了)添加操作excel的COM组件:
C# 操作Excel2003
2.主要的方法代码:

    /// <summary>
    /// 修改Excel
    /// </summary>
    /// <param name="Path">读取路径</param>
        /// <param name="newPath">另存路径</param>
    /// <param name="shop_name">店名</param>
    /// <param name="tablename">表名</param>
    public void AlterExcel(string Path,string newPath,string shop_name,string tablename)
    {
        try
        {
            Object miss = System.Reflection.Missing.Value;
            Excel.Application ExcelApp = new Excel.ApplicationClass();
            Excel.Workbooks wbooks = (Excel.Workbooks)ExcelApp.Workbooks;
            Excel.Workbook wbook = wbooks.Open(Path, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
            //获得第一个工作薄
            Excel.Worksheet wsheet = (Excel.Worksheet)wbook.Worksheets.get_Item(1);

            ExcelApp.Visible = false;
            ExcelApp.DisplayAlerts = false;

            //取得编号
            ArrayList arr = new ArrayList();
            for (int i = 3, j = wsheet.UsedRange.Rows.Count; i < j; i++)
            {
                Excel.Range rang = (Excel.Range)wsheet.Cells[i, 2];
                if (null != rang.Text && rang.Text.ToString().Length > 0)
                    arr.Add(rang.Text);
                else
                    break;
            }

            //添加新行 Cells[行,列]
            ((Excel.Range)wsheet.Columns.get_Item(3, miss)).Insert(XlInsertShiftDirection.xlShiftToRight, miss);
            //添加区域单元格
            //wsheet.get_Range(wsheet.Cells[2, 3], wsheet.Cells[wsheet.UsedRange.Rows.Count - 3, 3]).Insert(XlInsertShiftDirection.xlShiftToRight, miss);

            //设置标头
            wsheet.Cells[2, 3] = "地址";
            ((Excel.Range)wsheet.Columns.get_Item(3, miss)).ColumnWidth = 25;
            //此处为获得该店的所有地址,这里替换成自己的方法就行
            Hashtable hash = GetShopAddr(shop_name, tablename);

            for (int i = 3, j = i + arr.Count; i < j; i++)
            {
                try
                {
                        //根据编号设置地址
                    wsheet.Cells[i, 3] = hash[Convert.ToInt32(arr[i - 3])];
                }
                catch(Exception)
                {
                    continue;
                }
            }
            //另存为
            wbook.SaveAs(newPath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
            wbook.Close(false, miss, miss);
            ExcelApp.Quit();

            ReleaseObject(wsheet);
            ReleaseObject(wbook);
            ReleaseObject(wbooks);
            ReleaseObject(ExcelApp);

            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
        catch
        {
            //throw;
        }
        finally
        {
            //结束进程
            //KillExcelProcess();
        }
    }

    /// <summary>
    /// 释放对象
    /// </summary>
    /// <param name="obj"></param>
    private void ReleaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        }
        catch {}
        finally { obj = null; }
    }

    /// <summary>
    /// 结束Excel进程
    /// </summary>
    public void KillExcelProcess()
    {
        Process[] myProcesses = Process.GetProcessesByName("Excel");
        foreach (Process myProcess in myProcesses)
            myProcess.Kill();
    }

注意:
a).需要添加命名空间using Microsoft.Office.Interop.Excel;
b).添加新行处代码两个都行,一个是添加一列一个是添加一个范围的单元格,达到效果是一样的

最后推荐一个网站和一个论坛:
Excel终极伴侣 http://www.chinaobs.com/

MSDN http://msdn.microsoft.com/library/CHS/dv_wrcore/html/wrgrfexcelobjects.asp

本文转自博客园农民伯伯的博客，原文链接：C# 操作Excel2003，如需转载请自行联系原博主。

码农公寓

相关文章