Excel Programming (C# + VBA) Part II

 

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

 

转载于:https://www.cnblogs.com/lyrix/archive/2007/12/04/976009.html