Click here to Skip to main content
13,351,050 members (55,563 online)
Click here to Skip to main content
Add your own
alternative version


83 bookmarked
Posted 19 Dec 2007

A Complete Excel Programming Sample

, 19 Dec 2007
Rate this:
Please Sign up or sign in to vote.
An article on Excel programming, including Excel operations in C# and VBA
Screenshot - Template Image


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:

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


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

  1. Clarify the requirement
  2. 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
  3. 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)
  4. Create Summary, Filling Sheet
    • Set the layout, add validation button and add new button
  5. VBA programming to add list validation or auto-fill is as follows:
    rng.Validation.Add xlValidateList, xlValidAlertStop, xlBetween, _
    rng.Validation.IgnoreBlank = True
    rng.Validation.InCellDropdown = True 
  6. VBA programming to select data in pop form is as follows:

    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
    End With 

    In frmChoose, when the Ok button is clicked:

    CWorksheet.Unprotect Password
    n = 2
    If (CSourceWorksheet Is Nothing) Then
        name = wsDataSource.Range(CNameSourceColumn & n).Text
        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
                code = CSourceWorksheet.Range(CCodeSourceColumn & n).Text
            End If
        End If
        n = n + 1
        If (CSourceWorksheet Is Nothing) Then
            name = wsDataSource.Range(CNameSourceColumn & n).Text
            name = CSourceWorksheet.Range(CNameSourceColumn & n).Text
        End If            Wend
    If (CNameColumn <>  "") Then
        CWorksheet.Range(CNameColumn & CRow).Value2 =
    End If
    If (CCodeColumn <>  "") Then
        CWorksheet.Range(CCodeColumn & CRow).Value2 = code
    End If
    CWorksheet.Protect Password
  7. VBA programming to validate the data filled in every sheet is as follows:
    If Not CheckDateType(ws.Cells(i, 2).Value) Then
        bCheck = False
        MsgBox ws.Cells(i, 1).Value & "ÄÚÊäÈëµÄ²»ÊÇÓÐЧʱ¼ä"
        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#

  1. Create an Excel application object:
    Microsoft.Office.Interop.Excel.Application xlsApp = null;
    Workbook wb=null;
  2. 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);
  3. Close the template and open the new temporary file:
    wb.Close(false, Type.Missing, Type.Missing);
    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);
  4. 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 = 
        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));
        //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 = 
            ws.get_Range("G" + currentRow, System.Type.Missing).Value2 = 
  5. Protected worksheets and workbook based on requirement:
    ws = (Worksheet) wb.Worksheets[SummarySheet];
    wb.Protect(TemplatePassword,System.Type.Missing, System.Type.Missing);
  6. Save the temporary file as the final template name:
  7. Close the template and release the Excel resource:
    if (wb!=null)
        wb.Close(false, Type.Missing, Type.Missing);
    if(xlsApp != null)
    //Remove the temporary file

Step 3: Download the Template from the Web Site

Step 4: Fill in the Template and Verify the Data

  1. User fills in the data according to the description: list validation, free typing in, popup form etc.
  2. 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

  1. 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,
  2. 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");
  3. Verify the data in the template again in the web application:
        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);
            iChecked = Convert.ToInt16(check);
            iChecked = 0;
  4. Read the data in the worksheet:
    string territoryList = ws.get_Range("B"+ i.ToString(), 
    string territoryIDList = ws.get_Range("AB" + i.ToString(), 
    string category = ws.get_Range("E" + i.ToString(), 
    string categoryID = ws.get_Range("AE" + i.ToString(), 
  5. Change the database based on data in the Excel sheet:
    SqlHelper.ExecuteNonQuery(connnectionString, CommandType.Text, "insert into … ");

Points of Interest

  1. Excel.Application
    • Application.Workbooks.Open
    • Quit
  2. Excel.Workbook
    • SaveCopyAs
    • Unprotect
    • Worksheets
    • Protect
    • Names.Add
    • Close
  3. Excel.WorkSheet
    • Unprotect
    • Protect
    • Hyperlinks.Add
  4. Range
    • Value2
    • Text
  5. Cell, Cells


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


About the Author

China China
No Biography provided

You may also be interested in...

Comments and Discussions

QuestionPassword Pin
Member 117528579-Jul-17 17:42
memberMember 117528579-Jul-17 17:42 
AnswerRe: Password Pin
Thomas Edmundson (DS1nt2210)27-Sep-17 19:48
memberThomas Edmundson (DS1nt2210)27-Sep-17 19:48 
QuestionPassword please Pin
Member 107224289-Feb-17 20:37
memberMember 107224289-Feb-17 20:37 
QuestionPassword please Pin
Member 127238006-Sep-16 12:14
memberMember 127238006-Sep-16 12:14 
SuggestionWhy extend with C#.NET? Pin
ExcelVBAMaster26-Jan-16 12:02
memberExcelVBAMaster26-Jan-16 12:02 
Questionpassword Pin
Member 1179565426-Jun-15 16:46
memberMember 1179565426-Jun-15 16:46 
QuestionPass Pin
Georgian Pirvu23-Feb-15 23:42
memberGeorgian Pirvu23-Feb-15 23:42 
QuestionHopes dashed Pin
mzsarko2-Sep-14 17:49
membermzsarko2-Sep-14 17:49 
QuestionAsking for help Pin
Member 1063850320-May-14 0:01
memberMember 1063850320-May-14 0:01 
QuestionPassword needed Pin
Member 108002486-May-14 20:38
memberMember 108002486-May-14 20:38 
QuestionDear sir, Pin
lin21c30-Sep-13 20:53
memberlin21c30-Sep-13 20:53 
QuestionNice Pin
PraveenKumarReddyChinta29-Nov-12 0:00
memberPraveenKumarReddyChinta29-Nov-12 0:00 
Great post ! Smile | :)
QuestionSpecial_Offer_Central_200712_Template Pin
ady izwan shah13-Sep-12 6:46
memberady izwan shah13-Sep-12 6:46 
Question能不能直接告诉我 能改字体颜色和列宽吗 还有效率怎么样 Pin
batsword18-Aug-11 0:26
memberbatsword18-Aug-11 0:26 
GeneralCan't open the macros of the template Pin
pwarborg19-Oct-09 2:54
memberpwarborg19-Oct-09 2:54 
QuestionDatabase Pin
bethalam30-Apr-08 21:54
memberbethalam30-Apr-08 21:54 
GeneralUnable to load the project samples Pin
Serguei_Ko3-Jan-08 3:28
memberSerguei_Ko3-Jan-08 3:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180111.1 | Last Updated 19 Dec 2007
Article Copyright 2007 by Lyrix
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid