excel添加列下拉框票价_用C#创建Excel，添加下拉框列，并动态给下拉框赋值

public void simpleButton1_Click(object sender, EventArgs e)

{

KillProcess("Excel");//首先杀死进程

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

Microsoft.Office.Interop.Excel.Range range = worksheet.Cells;

//Excel.Range ran = range.get_Range("A5", "A5");

//ran.AddComment("批注");

Excel.Range ran1 = worksheet.Columns.get_Range("H:H", Type.Missing);//或者.get_Range("D1", "D1").get_Resize(50,1);//获取一整列D，行数为50

ran1.Validation.Delete();//有无都可以

ran1.Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, "1,2,3", Type.Missing);

//填充值

worksheet.Cells[1, 1] = "用户代码";

worksheet.Cells[1, 2] = "用户名称";

worksheet.Cells[1, 3] = "绩效积分项";

worksheet.Cells[1, 4] = "绩效积分时间";

worksheet.Cells[1, 5] = "描述";

//先把下拉框值赋值在Z列上

//杀死进程

private void KillProcess(string processName)

{

//获得进程对象，以用来操作

System.Diagnostics.Process myproc = new System.Diagnostics.Process();

//得到所有打开的进程

try

{

//获得需要杀死的进程名

foreach (Process thisproc in Process.GetProcessesByName(processName))

{

//立即杀死进程

thisproc.Kill();

}

}

catch (Exception Exc)

{

throw new Exception("", Exc);

}

}

worksheet.Cells[1, 26] = "a";

worksheet.Cells[2, 26] = "b";

worksheet.Cells[3, 26] = "c";

//在把Z列值赋值到下拉框列上

worksheet.get_Range(worksheet.Cells[1, 3], worksheet.Cells[10000, 3]).Validation.Add(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, "=$Z$1:$Z$5", Type.Missing);

//最后隐藏Z列

Excel.Range ranger = (Excel.Range)worksheet.Columns["Z:Z", System.Type.Missing];

ranger.Hidden = true;

//将文件保存到D:\\tt.xls

workbook.SaveCopyAs("D:\\111.xlsx");

workbook.Saved = true;

xlApp.Visible = true;//打开Excel文件

// ran1.Validation.Modify(Excel.XlDVType.xlValidateList, Excel.XlDVAlertStyle.xlValidAlertStop, Type.Missing, "4,3,2", Type.Missing);//用此法方(Modify)可以对前面的下拉框进行修改，

}

//当使用模板时，用下面的代码替换上面的几行

object missing = Missing.Value;

KillProcess("Excel");//首先杀死进程

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

//Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

//Microsoft.Office.Interop.Excel.Range range = worksheet.Cells;

//打开模板文件，得到WorkBook对象

Microsoft.Office.Interop.Excel.Workbook workbook = xlApp.Workbooks.Open(HttpContext.Request.PhysicalApplicationPath + "Templates\\"+"IPalTempFile\\PDU资产模板1.xls", missing, missing, missing, missing, missing,

missing,missing,missing,missing,missing,missing,missing);

//得到WorkSheet对象

Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.get_Item(1);

Microsoft.Office.Interop.Excel.Range range = worksheet.Cells;

//worksheet.Name = "过程资产平台导入建议模板";