a.       Create 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);

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

           string tempFileName = _filePath.ToLower().Replace(".xls","_Temp.xls");      

            wb.Unprotect(TemplatePassword);

            wb.SaveCopyAs(tempFileName);

c.       Verify the data in template again in 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;

            }

      }    

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

e.      Change the database based on data in excel sheet

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

Summary: Objects used in this sample

a.       Excel.Application

                                                               i.      Application.Workbooks.Open

                                                             ii.      Quit

b.      Excel.Workbook

                                                               i.      SaveCopyAs

                                                             ii.      Unprotect

                                                            iii.      Worksheets

                                                           iv.      Protect

                                                             v.      .Names.Add

                                                           vi.      Close

c.       Excel.WorkSheet

                                                               i.      Unprotect

                                                             ii.      Protect

                                                            iii.      .Hyperlinks.Add

d.      Range

                                                               i.      Value2

                                                             ii.      Text

e.      Cell, Cells

2.     Step 2 Generate an Excel template to fill with source data in C#

a.       Create excel application object

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

            Workbook wb=null;
           

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

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

d.      Get master data from 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 LoadMaterialDataSource(Worksheet ws)
        {
            string sql = "select MaterialCode, EnglighShortName as MaterialName from Material";
            string conn = ConfigurationSettings.AppSettings["ConnString"];
            Range rng = ws.get_Range("Q1",System.Type.Missing);
            QueryTable qt = ws.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + conn, rng, sql);
            qt.Refresh(System.Type.Missing);
            qt.Name = "Material";
        }

        private void LoadCategoryDataSource(Worksheet ws)
        {
            string sql = "SELECT CategoryID, CategoryNameEn FROM Category WHERE Status = 3";
            string conn = ConfigurationSettings.AppSettings["ConnString"];
            Range rng = ws.get_Range("W1",System.Type.Missing);
            QueryTable qt = ws.QueryTables.Add("OLEDB;Provider=SQLOLEDB.1;" + conn, rng, sql);
            qt.Refresh(System.Type.Missing);
            qt.Name = "Category";
        }

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

 

f.      Save the temporary file as the final template name

            wb.SaveCopyAs(excelFileName);                  

g.       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 web site

4.       Step 4 Fill in the template and verify the data

a.       User fills in the data according to the description: list validation, free typing in, popup form etc.

b.      Click the validation button in the sheet and validate the dat

5.       Step 5 Upload the template to the web site

•    Clarify the requirement and create the Excel template (VBA)
• Generate an Excel template to fill with source data (C#)
• Download the template from web site (C# + ASP.NET)
• Fill in the template and verified the data via VBA (VBA)
• Upload the template (Web) (ASP.NET)
• Import data in Excel Template to database (C#)

1.      Setp 1 Create the Excel Template based on requirement

a.       Clarify the requirement

b.      Create hidden template sheet

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

c.       Create hidden data source sheet

                                                               i.      Clarify the source data in each column

                                                             ii.      Create named range for list validation (If the data is already known)

d.      Create Summary, Filling Sheet

                                                               i.      Set the layout, add validation button and Add new button

e.      VBA programming to add list validation or auto-filling 

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

f.        VBA programming to select data in pop form

            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:

        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 (CSourceWorksheet 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
        

g.       VBA programming to validate the data filled in every sheets

          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.