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