博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

Excel表复制、取消工作表保护、解除冻结操作

Posted on 2011-02-10 09:56  moss_tan_jun  阅读(2892)  评论(1编辑  收藏  举报

最近处理Farpoint保存下来的Excel时,发现表格式有各种问题,下面是修改Excel格式操作到内容,做下笔记 :)

 

/// <summary>
/// Excel表相关操作
/// </summary>
/// <param name="firstpath">第一个Excel表路径</param>
/// <param name="secondpath">第二个Excel表路径</param>
private void ExcelOperation(string firstpath, string secondpath)
{
    try
    {
        Microsoft.Office.Interop.Excel.Application App = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook fWorkbook = App.Workbooks.Open(firstpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        Microsoft.Office.Interop.Excel.Workbook sWorkbook = App.Workbooks.Open(secondpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        App.DisplayAlerts = false;  //屏蔽操作提示
        try
        {
            ((Microsoft.Office.Interop.Excel.Worksheet)sWorkbook.Sheets[1]).Delete();  //删除第二个表第一个Sheet
            sWorkbook.Save();

            Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)fWorkbook.Sheets[1];  //获取第一个表第一个Sheet

            sheet.Unprotect(Type.Missing);   //取消工作表保护

            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[1, Type.Missing];  //获取第一行
            sheet.Activate();
            App.ActiveWindow.FreezePanes = false;  //解除冻结
            App.ActiveWindow.SplitColumn = 0;
            App.ActiveWindow.SplitRow = 0;

            range.Interior.ColorIndex = 0;    //设置第一行为白色
            range.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlDown);     //删除第一行

            sheet.Copy(sWorkbook.Sheets[1], Type.Missing);  //复制到第二个表中
            sWorkbook.Save();

        }
        catch (Exception ex)
        {
        }
        finally
        {
            fWorkbook.Close(false, firstpath, null);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(fWorkbook);
            fWorkbook = null;

            sWorkbook.Close(false, secondpath, null);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(sWorkbook);
            sWorkbook = null;

            App.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(App);
        }
    }
    catch (Exception ex)
    {

    }
}