Microsoft Office 中级开发 .NET Visual Basic C#

完整的 Excel 编程示例

Lyrix

4.33/5 (11投票s)

2007 年 12 月 19 日

CPOL

3分钟阅读

361342

15489

一篇关于 Excel 编程的文章，包括 C# 和 VBA 中的 Excel 操作

下载示例 - 278 KB

引言

这是一个创建用于特定目的的 Excel 模板的示例。最终用户可以下载模板并填写，然后将其上传到服务器，服务器将读取模板并更新数据库。以下是此方案的步骤

明确需求并创建 Excel 模板
生成一个 Excel 模板以填充源数据
从网站下载模板
填写模板并通过 VBA 验证数据
上传模板（Web）
将 Excel 模板中的数据导入到数据库

背景

AdventureWorks 希望其每个地区的销售人员都能够填写他们想在其地区执行的特别优惠计划。但是大多数销售人员经常出差，他们无法按时访问该站点。那么需要一个离线解决方案，使用 Excel 模板是最好的解决方案。首先，它更便宜，因为不需要额外的软件/硬件。其次，所有销售人员都是 Excel 专家，培训将更容易。

可以从 Microsoft 下载中心下载 AdventureWorks 数据库。

Using the Code

步骤 1：根据需求创建 Excel 模板

明确需求
创建一个隐藏的模板工作表

明确每个单元格中的数据类型，填写类型（用户输入/列表验证或弹出表单）和锁定属性

创建一个隐藏的数据源工作表

明确每列中的源数据
为列表验证创建一个命名范围（如果数据已经知道）

创建摘要、填写工作表

设置布局，添加验证按钮和添加新按钮

用于添加列表验证或自动填充的 VBA 编程如下

rng.Validation.Add xlValidateList, xlValidAlertStop, xlBetween, _
    "=SpecialOfferType"
rng.Validation.IgnoreBlank = True
rng.Validation.InCellDropdown = True

用于在弹出表单中选择数据的 VBA 编程如下

With frmChoose
    .CCodeColumn = "AB"
    .CNameColumn = "B"
    .CRow = Target.Row
    .CCodeSourceColumn = "B"
    .CNameSourceColumn = "C"
    .CKeyWords = Me.Cells(iRow, iColumn).Text
    .Caption = Me.Cells(iRow, iColumn - 1)
    Set .CWorksheet = Application.ActiveSheet
    Set .CSourceWorksheet = sourceSheet
    .Show
End With

在 frmChoose 中，单击“确定”按钮时

CWorksheet.Unprotect Password
n = 2
If (CSourceWorksheet Is Nothing) Then
    name = wsDataSource.Range(CNameSourceColumn & n).Text
Else
    name = CSourceWorksheet.Range(CNameSourceColumn & n).Text
End If
While (name <>  "")
    If name = lstSelected.List(lstSelected.ListIndex) Then
        If (CSourceWorsheet Is Nothing) Then
            code = wsDataSource.Range(CCodeSourceColumn & n).Text
        Else
            code = CSourceWorksheet.Range(CCodeSourceColumn & n).Text
        End If
    End If

    n = n + 1
    If (CSourceWorksheet Is Nothing) Then
        name = wsDataSource.Range(CNameSourceColumn & n).Text
    Else
        name = CSourceWorksheet.Range(CNameSourceColumn & n).Text
    End If            Wend

If (CNameColumn <>  "") Then
    CWorksheet.Range(CNameColumn & CRow).Value2 =
    lstSelected.List(lstSelected.ListIndex)
End If

If (CCodeColumn <>  "") Then
    CWorksheet.Range(CCodeColumn & CRow).Value2 = code
End If
CWorksheet.Protect Password

用于验证每个工作表中填写的数据的 VBA 编程如下

If Not CheckDateType(ws.Cells(i, 2).Value) Then
    bCheck = False
    MsgBox ws.Cells(i, 1).Value & "ÄÚÊäÈëµÄ²»ÊÇÓÐÐ§Ê±¼ä"
    ws.Activate
    ws.Cells(i, 2).Select
    Exit Sub
End If            .....
Function CheckNumberic(sourceString As String) As Boolean
    Dim bOk As Boolean
    bOk = True
    If sourceString <>  "" And Not IsNumeric(sourceString) Then
        bOk = False
    End If
    CheckNumberic = bOk
End Function

Function CheckDateType(sourceString As String) As Boolean
    Dim bOk As Boolean
    bOk = False
    If sourceString <>  "" And IsDate(sourceString) Then
        bOk = True
    End If
    CheckDateType = bOk
End Function

步骤 2：在 C# 中生成一个 Excel 模板以填充源数据

创建一个 Excel 应用程序对象

Microsoft.Office.Interop.Excel.Application xlsApp = null;
Workbook wb=null;

打开模板并使用 SaveCopyAs 保存一个临时的模板文件名

fileName= templatePath + @"\template.xls";
excelFileName = tempFileName + "_template.xls";
tempFileName= tempFileName + "_template_Temp.xls";
xlsApp = new ApplicationClass();
wb = xlsApp.Workbooks.Open(fileName, 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);
wb.Unprotect(TemplatePassword);
wb.SaveCopyAs(tempFileName);

关闭模板并打开新的临时文件

wb.Close(false, Type.Missing, Type.Missing);
xlsApp.Quit();
wb = xlsApp.Workbooks.Open(tempFileName, 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);

从数据库获取主数据，并根据需求填充每个单元格，并正确设置验证锁定属性

....
ws = (Worksheet)wb.Worksheets[DataSourceSheet];
LoadDataSource(ws, wb, beginDate, endDate);
....

private void LoadDataSource(Worksheet ws, Workbook wb, DateTime beginDate,
DateTime endDate)
{
    DataSet ds = SqlHelper.ExecuteDataset(connnectionString, CommandType.Text, "");
    //Generate the Base Info
    ws.get_Range("A1",System.Type.Missing).Value2 = 0; //Check flag
    ws.get_Range("A4",System.Type.Missing).Value2 = beginDate.ToString("yyyy-MM-dd");
    ws.get_Range("A5",System.Type.Missing).Value2 = endDate.ToString("yyyy-MM-dd");
    ws.get_Range("A6",System.Type.Missing).Value2 = 
        beginDate.ToString(PlanDateFormat);
    ws.get_Range("A7",System.Type.Missing).Value2 = _templatetype;

    LoadActionStatus(wb, ws, ds.Tables[0]);
    LoadPromotionType(wb, ws, ds.Tables[0]);

    LoadUserDataSource(wb, ws, beginDate.ToString(PlanDateFormat));

    LoadCategoryDataSource(ws);

    //ws.Visible = XlSheetVisibility.xlSheetVisible;
}

private void LoadCategoryDataSource(Worksheet ws)
{
    string sql = "SELECT ProductCategoryID, Name FROM ProductCategory";
    DataSet ds = SqlHelper.ExecuteDataset(connnectionString, CommandType.Text, sql);
    int currentRow = 2;
    foreach (DataRow dr in ds.Tables[0].Rows)
    {
        ws.get_Range("F" + currentRow, System.Type.Missing).Value2 = 
            dr["ProductCategoryID"].ToString();
        ws.get_Range("G" + currentRow, System.Type.Missing).Value2 = 
            dr["Name"].ToString();
        currentRow++;
    }
}

根据需求保护工作表和工作簿

ws = (Worksheet) wb.Worksheets[SummarySheet];
ws.Protect(TemplatePassword,System.Type.Missing,System.Type.Missing,
    System.Type.Missing,System.Type.Missing,System.Type.Missing,
    System.Type.Missing,System.Type.Missing,System.Type.Missing,
    System.Type.Missing,System.Type.Missing,System.Type.Missing,
    System.Type.Missing,System.Type.Missing,System.Type.Missing,
    System.Type.Missing);
wb.Protect(TemplatePassword,System.Type.Missing, System.Type.Missing);

将临时文件保存为最终模板名称
```
wb.SaveCopyAs(excelFileName);
```

关闭模板并释放 Excel 资源

if (wb!=null)
{
    wb.Close(false, Type.Missing, Type.Missing);
}
if(xlsApp != null)
{
    xlsApp.Quit();
}
//Remove the temporary file
System.IO.File.Delete(tempFileName);

步骤 3：从网站下载模板

步骤 4：填写模板并验证数据

用户根据描述填写数据：列表验证、自由输入、弹出表单等。
单击工作表中的验证按钮并验证数据

步骤 5：将模板上传到网站

请参考示例将文件上传到网站。

步骤 6：将 Excel 中的数据导入到数据库

创建一个 Excel 应用程序对象并打开上传的文件

Microsoft.Office.Interop.Excel.Application xlsApp = new ApplicationClass();

Workbook wb = xlsApp.Workbooks.Open(_filePath,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);

wb.Unprotect(TemplatePassword);

将上传的文件保存为临时文件。然后关闭上传的文件，并按如下方式打开临时文件

string tempFileName = _filePath.ToLower().Replace(".xls","_Temp.xls");
wb.Unprotect(TemplatePassword);
wb.SaveCopyAs(tempFileName);

在 Web 应用程序中再次验证模板中的数据

ReadDataSource((Worksheet)wb.Worksheets[DataSourceSheet], 
    out branchID, out planDate, out startDate, out endDate, 
    out iChecked, out templatetype);

if(!(iChecked == 1))
{
    returnVal = "Please verify the data before upload to the server!";
    throw new Exception(returnVal);
}
if(templatetype.ToUpper() != _templatetype.ToUpper())
{
    returnVal = "The version is not corrected, 
    please verify the document and uploaded again";
    throw new Exception(returnVal);
}
……
private void ReadDataSource(Worksheet ws, out string branchID, 
    out string planDate, out DateTime startDate,out DateTime endDate, 
    out int iChecked, out string templatetype)
{
    string check = ws.get_Range("A1", System.Type.Missing).Text.ToString();
    branchID = ws.get_Range("A2", System.Type.Missing).Text.ToString();
    string sDate = ws.get_Range("A4", System.Type.Missing).Text.ToString();
    string eDate = ws.get_Range("A5", System.Type.Missing).Text.ToString();
    planDate = ws.get_Range("A6", System.Type.Missing).Text.ToString();
    templatetype = ws.get_Range("A7", System.Type.Missing).Text.ToString();
    startDate = DateTime.Parse(sDate);
    endDate = DateTime.Parse(eDate);
    try
    {
        iChecked = Convert.ToInt16(check);
    }
    catch
    {   
        iChecked = 0;
    }
 }

读取工作表中的数据

string territoryList = ws.get_Range("B"+ i.ToString(), 
    System.Type.Missing).Text.ToString();
string territoryIDList = ws.get_Range("AB" + i.ToString(), 
    System.Type.Missing).Text.ToString();
string category = ws.get_Range("E" + i.ToString(), 
    System.Type.Missing).Text.ToString();
string categoryID = ws.get_Range("AE" + i.ToString(), 
    System.Type.Missing).Text.ToString();

根据 Excel 工作表中的数据更改数据库

SqlHelper.ExecuteNonQuery(connnectionString, CommandType.Text, "insert into … ");

关注点

Excel.Application

Application.Workbooks.Open
退出

Excel.Workbook

SaveCopyAs
Unprotect
Worksheets
Protect
Names.Add
Close

Excel.WorkSheet

Unprotect
Protect
Hyperlinks.Add

Range

Value2
文本

Cell, Cells