C# 添加Excel表单控件(Form Controls)

在Excel中,添加的控件可以和单元格关联,我们可以操作控件来修改单元格的内容,在下面的文章中,将介绍在Excel中添加几种不同的表单控件的方法,包括:

  •  添加文本框(Textbox)
  •  单选按钮(Radio button)
  •  复选框(Checkbox)
  •  组合框(combo Box)

使用工具

PS:下载安装该组件后,注意在项目程序中添加引用Spire.Xls.dll(dll文件可在安装路径下的Bin文件夹中获取),如下图所示

代码示例

【示例1】插入Excel表单控件

步骤1:创建工作表

//实例化一个Workbook类实例,并获取第1个工作表
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

//设置表格行高、列宽
sheet.Range["A1:F1"].ColumnWidth = 15F;
sheet.Range["A1:B12"].RowHeight = 20F;

步骤 2:插入文本框

//插入文本框控件,指定文本框位置、大小以及文本对齐方式
sheet.Range["A1"].Text = "姓名:";
ITextBoxShape textBox = sheet.TextBoxes.AddTextBox(1, 2, 25, 110); 
textBox.Text = "John";
textBox.HAlignment = CommentHAlignType.Center;
textBox.VAlignment = CommentVAlignType.Center;

步骤 3:插入单选按钮

//插入单选按钮,指定单元格位置
sheet.Range["A3"].Text = "性别:";
IRadioButton radioButton = sheet.RadioButtons.Add(3, 2, 20, 80);
radioButton.CheckState = CheckState.Checked;
radioButton.Text = "";
radioButton = sheet.RadioButtons.Add(3, 3, 20, 80);
radioButton.Text = "";

步骤 4:插入复选框

//插入复选框并指定单元格位置
sheet.Range["A5"].Text = "所在行业:";
ICheckBox checkBox = sheet.CheckBoxes.AddCheckBox(5, 2, 18, 65);
checkBox.CheckState = CheckState.Checked;
checkBox.Text = "教育";
checkBox = sheet.CheckBoxes.AddCheckBox(5, 3, 18, 65);
checkBox.Text = "医疗";
checkBox = sheet.CheckBoxes.AddCheckBox(5, 4, 18, 65);
checkBox.Text = "IT";
checkBox = sheet.CheckBoxes.AddCheckBox(5, 5, 18, 65);
checkBox.Text = "零售";
checkBox = sheet.CheckBoxes.AddCheckBox(5, 6, 18, 65);
checkBox.Text = "其他";            

步骤 5:插入组合框

//插入组合框,并指定单元格位置、大小
sheet["A7"].Text = "年龄(段):";
sheet["A8"].Text = "<18";
sheet["A9"].Text = "18<Y<30";
sheet["A10"].Text = "30<Y<50";
IComboBoxShape comboBox = sheet.ComboBoxes.AddComboBox(7, 2, 23, 100);
comboBox.ListFillRange = sheet["A8:A10"];

步骤 6:指定Combox的关联单元格

sheet["A12"].Text = "代表人群类别:";
comboBox.LinkedCell = sheet.Range["B12"];
comboBox.SelectedIndex = 1;

步骤 7:保存文档

workbook.SaveToFile("AddFormControls.xlsx", ExcelVersion.Version2010);

运行该项目程序,生成文件(可在项目文件夹bin>Debug下查看文档)

全部代码:

using Spire.Xls;
using Spire.Xls.Core;
using System.Drawing;

namespace FormControls_XLS
{
    class Program
    {
        static void Main(string[] args)
        {
            //实例化一个Workbook类实例,并获取第1个工作表
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];

            //设置表格行高、列宽
            sheet.Range["A1:F1"].ColumnWidth = 15F;
            sheet.Range["A1:B12"].RowHeight = 20F;
            
            //插入文本框控件,指定文本框位置、大小以及文本对齐方式
            sheet.Range["A1"].Text = "姓名:";
            ITextBoxShape textBox = sheet.TextBoxes.AddTextBox(1, 2, 25, 110);          
            textBox.Text = "John";
            textBox.HAlignment = CommentHAlignType.Center;
            textBox.VAlignment = CommentVAlignType.Center;

            //插入单选按钮,指定单元格位置
            sheet.Range["A3"].Text = "性别:";
            IRadioButton radioButton = sheet.RadioButtons.Add(3, 2, 20, 80);
            radioButton.CheckState = CheckState.Checked;
            radioButton.Text = "";
            radioButton = sheet.RadioButtons.Add(3, 3, 20, 80);
            radioButton.Text = "";

            //插入复选框并指定单元格位置
            sheet.Range["A5"].Text = "所在行业:";
            ICheckBox checkBox = sheet.CheckBoxes.AddCheckBox(5, 2, 18, 65);
            checkBox.CheckState = CheckState.Checked;
            checkBox.Text = "教育";
            checkBox = sheet.CheckBoxes.AddCheckBox(5, 3, 18, 65);
            checkBox.Text = "医疗";
            checkBox = sheet.CheckBoxes.AddCheckBox(5, 4, 18, 65);
            checkBox.Text = "IT";
            checkBox = sheet.CheckBoxes.AddCheckBox(5, 5, 18, 65);
            checkBox.Text = "零售";
            checkBox = sheet.CheckBoxes.AddCheckBox(5, 6, 18, 65);
            checkBox.Text = "其他";            
            
            //插入组合框,并指定单元格位置、大小
            sheet["A7"].Text = "年龄(段):";
            sheet["A8"].Text = "<18";
            sheet["A9"].Text = "18<Y<30";
            sheet["A10"].Text = "30<Y<50";
            IComboBoxShape comboBox = sheet.ComboBoxes.AddComboBox(7, 2, 23, 100);
            comboBox.ListFillRange = sheet["A8:A10"];

            //指定组合框的关联单元格
            sheet["A12"].Text = "代表人群类别:";
            comboBox.LinkedCell = sheet.Range["B12"];
            comboBox.SelectedIndex = 1;

            //保存文档
            workbook.SaveToFile("AddFormControls.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("AddFormControls.xlsx");
        }
    }
}
View Code

 

【示例 2】 删除Excel表单控件

 步骤 1:加载文档,并获取指定单元格

Workbook workbook = new Workbook();
workbook.LoadFromFile("test.xlsx");
Worksheet sheet = workbook.Worksheets[0];

步骤 2:删除组合框

for (int i = 0; i < sheet.ComboBoxes.Count; i++)
{
    sheet.ComboBoxes[i].Remove();
}

步骤 3:保存文档

workbook.SaveToFile("RemoveComboBoxes.xlsx", ExcelVersion.Version2010);

 

全部代码:

using Spire.Xls;


namespace RemoveFormControl_XLS
{
    class Program
    {
        static void Main(string[] args)
        {
            //创建Workbook实例,加载Excel文档
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("test.xlsx");

            //获取第一个工作表
            Worksheet sheet = workbook.Worksheets[0];

            //删除工作表中所有的组合框
            for (int i = 0; i < sheet.ComboBoxes.Count; i++)
            {
                sheet.ComboBoxes[i].Remove();
            }

            //保存并打开文档
            workbook.SaveToFile("RemoveComboBoxes.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("RemoveComboBoxes.xlsx");
        }
    }
}
View Code

运行程序后,表格中相应的控件将被删除。

以上是本次关于C#操作Excel表单控件的全部内容,本文完。

(如需转载,请注明出处)

posted @ 2018-12-05 15:36  E-iceblue  阅读(4186)  评论(2编辑  收藏