c# 制作Excel AddIn（插件）中的关键知识点

ThisAddIn类是加载项的入口，通过InternalStartup()方法初始化。

InternalStartup()方法内添加了加载项的启动和关闭事件

        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }

在启动事件处理函数中做一些初始化处理，如生成用户控制面板等

创建自定义面板，并设置显示位置

        public CustomTaskPane assetSearchTaskPane = null;
private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            UserControl1 assetSearchUC = new UserControl1();
            assetSearchTaskPane = this.CustomTaskPanes.Add(assetSearchUC, " ");
            assetSearchTaskPane.Width = 600;
            assetSearchTaskPane.Visible = false;
            assetSearchTaskPane.DockPosition = Office.MsoCTPDockPosition.msoCTPDockPositionLeft;
        }

设置面板显示位置

customTaskPane.DockPosition = Office.MsoCTPDockPosition.msoCTPDockPositionLeft;

 

 

给comboBox添加选项

        //業種のcomboboxの初期化
        public void initComboboxIndustryType1()
        {
            initIndustryData2();

            Dictionary<string, string> dict = new Dictionary<string, string>();
            dict.Add("A", "A-エネルギー・素材・産業機械");
            dict.Add("B", "B-食品");
            dict.Add("C", "C-嗜好品(タバコ)");
            dict.Add("D", "D-ノンアルコール飲料");

　　　　　　　//给comboBox指定数据源
            this.industryType1.DataSource = getDataTableFromDictionary(dict);
            this.industryType1.DisplayMember = "name";     //指定显示值
            this.industryType1.ValueMember = "value";     //指定value值

        }

        public DataTable getDataTableFromDictionary(Dictionary<String, String> dict) {
　　　　　　　//定义DataTable
            DataTable dt = new DataTable();
            dt.Columns.Add("name");
            dt.Columns.Add("value");

　　　　　　　//给DataTable添加数据
            foreach (var item in dict)
            {
                DataRow dr = dt.NewRow();
                dr[0] = item.Value;
                dr[1] = item.Key;
                dt.Rows.Add(dr);
            }
            return dt;
        }

 

Dictionary创建方式

        public static Dictionary<String, String> headerDict = new Dictionary<string, string> {
            {"projectName", "案件名"},
            {"jobNo", "JobNo"},
            {"clientName", "クライアント名"},
            {"researchTypeName", "調査分類"},
            {"industryTypeName", "業種"}
        }；

            Dictionary<string, string> dict = new Dictionary<string, string>();
            dict.Add("A", "A-エネルギー・素材・産業機械");
            dict.Add("B", "B-食品");
            dict.Add("C", "C-嗜好品(タバコ)");
            dict.Add("D", "D-ノンアルコール飲料");

 

json字符串解析

使用以下方法需要先添加引用

using Newtonsoft.Json;

//JSON文字列を解析

JsonResult jsonResult = JsonConvert.DeserializeObject<JsonResult>(jsonStr);

 

获取当前工作簿

Excel.WorkBook workbook = Globals.ThisAddIn.Application.ActiveWorkbook

　　

获取当前sheet

Excel.Worksheet sheet = Globals.ThisAddIn.Application.ActiveSheet;

 

防止Excel在填写数据时闪动

sheet.Application.ScreenUpdating = false;

sheet.Application.ScreenUpdating = true;

 

清空Excle

sheet.UsedRange.Value = null;

设置行高和列宽

sheet.UsedRange.Rows.RowHeight = 15
sheet.Rows[3].RowHeight = 15;

sheet.Columns.ColumnWidth = 4;
sheet.Columns[1].ColumnWidth = 15;

 

设置背景色

//无色
sheet.UsedRange.Interior.ColorIndex = 0;
//指定色
sheet.UsedRange.Interior.Color = Color.FromArgb(220, 198, 224);

 

设置边框

using Spire.Xls;

//无边框
sheet.UsedRange.Borders.LineStyle = LineStyleType.None;
//细边框
sheet.UsedRange.Borders.LineStyle = LineStyleType.Hair;

 

设置文字方向

//文字垂直显示
range.Orientation = Excel.XlOrientation.xlVertical;
//文字上对齐
range.VerticalAlignment = Excel.XlVAlign.xlVAlignTop;

 

发送get请求

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
            request.Method = "GET";
            request.ContentType = "Content-Type:application/json";


            HttpWebResponse response = (HttpWebResponse)request.GetResponse();
            Stream myResponseStream = response.GetResponseStream();
            StreamReader myStreamReader = new StreamReader(myResponseStream, Encoding.GetEncoding("utf-8"));
            string retString = myStreamReader.ReadToEnd();
            myStreamReader.Close();
            myResponseStream.Close();