随笔 - 394 文章 - 0 评论 - 946 阅读 - 143万

VSTO学习笔记一

1、加载文本文档到Excel中

View Code

 private void Sheet1_Startup(object sender, System.EventArgs e)
{
            #region the first method
            Application.ScreenUpdating = false;
            Application.Workbooks.Open(@"C:\regist.log",System.Type.Missing,false,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,System.Type.Missing,",",true,System.Type.Missing,System.Type.Missing,true,System.Type.Missing,System.Type.Missing);
            Application.ScreenUpdating = true;
            #endregion
｝

2、加载网络文本到Excel中

View Code

private void Sheet1_Startup(object sender, System.EventArgs e)
{
            #region the second method
            this.Application.Workbooks.OpenText("http://www.baidu.com", Excel.XlPlatform.xlMacintosh, 3, Excel.XlTextParsingType.xlDelimited, Excel.XlTextQualifier.xlTextQualifierNone, missing, missing, missing, false, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            #endregion
}

3、加载DataSet数据到Excel中

View Code

private void Sheet1_Startup(object sender, System.EventArgs e)
{

            #region the third method:load the dataset into the worksheet

            DataSet ds = GetDataSet();
            int count = 1;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                Excel.Range rng = this.Application.get_Range("A"+count++,missing);
                rng.Value2 = dr.ItemArray;
            }
            #endregion
｝

4、删除Excel的sheet

View Code

private void Sheet1_Startup(object sender, System.EventArgs e)
{
          
            #region the forth method:delete the worksheet

            Application.DisplayAlerts = false;
            ((Excel.Worksheet)this.Application.ActiveWorkbook.Sheets[2]).Delete();
            Application.DisplayAlerts = true;
            Application.Quit();
            #endregion
}

5、操作Excel的单元格

View Code

private void Sheet1_Startup(object sender, System.EventArgs e)
{ 
           #region the fifth method:the excel named ranges

            Microsoft.Office.Tools.Excel.NamedRange namedRange1 = Controls.AddNamedRange(this.Range["A1","A10"],"namedRange1");
            Microsoft.Office.Tools.Excel.NamedRange namedRange2 = Controls.AddNamedRange(this.Range["A1",missing],"namedRange2");

            namedRange1.Merge(false);
            namedRange2.Merge(false);

            namedRange1.BorderAround(missing, Excel.XlBorderWeight.xlHairline, Excel.XlColorIndex.xlColorIndexAutomatic, missing);
            namedRange1.AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormat3DEffects1,true,false,true,false,true,true);
            #endregion
}

6、单元格式限定

View Code

private void Sheet1_Startup(object sender, System.EventArgs e)
{
            #region this sixth method:cell manipulation

            Excel.Range rng = Globals.Sheet1.Range["B3", "B3"] as Excel.Range;
            rng.Value2 = "123456";
            rng.NumberFormat = "$#,###.0";
            rng = rng.Cells[3, 3] as Excel.Range;
            rng.Value2 = "new";
            #endregion 
}

7、单元格合并

View Code

private void Sheet1_Startup(object sender, System.EventArgs e)
{
            #region the seventh method:working with unions

            Excel.Range rngA = Globals.Sheet1.Range["a2", "B3"] as Excel.Range;
            rngA.Value2 = "Mortgage";
            Excel.Range rngB = Globals.Sheet1.Range["a5", "B6"] as Excel.Range;
            rngB.Value2 = "Interest";

            Excel.Range unionRange = Application.Union(rngA,rngB,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing) as Excel.Range;
            unionRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);

            #endregion
}

8、密码保护

View Code

private void Sheet1_Startup(object sender, System.EventArgs e)
{
            #region the eighth method:workbook password protection

            string password = this.Password;
            this.Protect(password, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            this.Unprotect(password);
            #endregion
}

9、单元格事件触发

View Code

private void Sheet1_Startup(object sender, System.EventArgs e)
{
            #region the ninth method:responding to events

            Microsoft.Office.Tools.Excel.NamedRange namedRange1 = Globals.Sheet1.Controls.AddNamedRange(Globals.Sheet1.Range["A1",missing],"NamedRange");
            namedRange1.Change+=new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(namedRange1_Change);

            #endregion
}

 private void namedRange1_Change(Microsoft.Office.Interop.Excel.Range target)
        {
            if (!this.Application.CheckSpelling((string)target.Value2, null, true))
            {
                MessageBox.Show("拼写有误！");
            }
        }