A Complete Excel Programming Sample

Lyrix

4.33/5 (13 votes)

19 Dec 2007CPOL3 min read

15.5K

An article on Excel programming, including Excel operations in C# and VBA

Download sample - 278 KB

Introduction

This is a sample to create an Excel template for a specified purpose. The end user could download the template and fill it in and then upload it to the server, and the server will read the template and update the database. Here are the steps of this scenario:

Clarify the requirement and create the Excel template
Generate an Excel template to fill with the source data
Download the template from the web site
Fill in the template and verify the data via VBA
Upload the template (Web)
Import data in Excel Template to the database

Background

AdventureWorks wanted its salesmen in each territory to be able to fill in the special offer plan which they want to execute in their territory. But most of the salesmen travel a lot and they cannot access the site on-time. Then an offline solution is needed and using Excel template is the best solution. First of all, it’s cheaper because no additional software/hardware is needed. Secondly, all the salesmen are experts in Excel and the training will be much easier.

The AdventureWorks database could be downloaded from the Microsoft download center.

Using the Code

Step 1: Create the Excel Template Based on Requirement

Clarify the requirement
Create a hidden template sheet

Clarify the data type in each cell, fill in type (user input/list validation or a pop form) and the lock property

Create a hidden data source sheet

Clarify the source data in each column
Create a named range for list validation (if the data is already known)

Create Summary, Filling Sheet

Set the layout, add validation button and add new button

VBA programming to add list validation or auto-fill is as follows:

VB.NET

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

VBA programming to select data in pop form is as follows:

VB.NET

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

In frmChoose, when the Ok button is clicked:

VB.NET

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 programming to validate the data filled in every sheet is as follows:

VB.NET

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

Step 2: Generate an Excel Template to Fill with Source Data in C#

Create an Excel application object:

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

Open the template and SaveCopyAs a new temporary template file name:

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);

Close the template and open the new temporary file:

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);

Get master data from the database and fill in each cell and set validation lock property properly based on requirement:

....
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++;
    }
}

Protected worksheets and workbook based on requirement:

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);

Save the temporary file as the final template name:

C#
```
wb.SaveCopyAs(excelFileName);
```

Close the template and release the Excel resource:

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

Step 3: Download the Template from the Web Site

Step 4: Fill in the Template and Verify the Data

User fills in the data according to the description: list validation, free typing in, popup form etc.
Click the validation button in the sheet and validate the data

Step 5: Upload the Template to the Web Site

Please refer to the sample to upload a file to the website.

Step 6: Import the Data in Excel to the Database

Create an Excel app object and open the uploaded file:

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);

Save the upload file as a temporary file. Then close the uploaded file and open the temporary file as follows:

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

Verify the data in the template again in the web application:

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;
    }
 }

Read the data in the worksheet:

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();

Change the database based on data in the Excel sheet:

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

Points of Interest

Excel.Application

Application.Workbooks.Open
Quit

Excel.Workbook

SaveCopyAs
Unprotect
Worksheets
Protect
Names.Add
Close

Excel.WorkSheet

Unprotect
Protect
Hyperlinks.Add

Range

Value2
Text

Cell, Cells

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)