C # Exceladdin Development notes

Haven't written a blog for a long time, recently really is too busy, take over the company to do a small new project, from contact to recognize a new thing, and then to their own research, eventually combined with the company's business to complete the whole Excel plug-in project, or a little sense of accomplishment. Here is a summary of all the points in the project:

1.ADO. NET database operation, of course, the efficiency is not very high, the primary level of data is not very large, first use this.

2.ThisAddin contained object, current activity sheet:Excel.Worksheet Worksheet = (excel.worksheet) Globals.ThisAddIn.Application.ActiveSheet ;

3.excel Range object's various properties and methods, such as assignment, hide, filter, clear, add formula and so on.

------------------------------------------------------------------------------------------

There's a lot of work going on in there. studied the morning:"Protection for Excel worksheets", first to unlock, and then to protect the range to be protected after the lock.

That is, when editing Excel, the user is not allowed to edit the formula column. We can refer to the Excelhome forum to learn more, although many of them are VBA content, translated into C # believe it is not difficult.

--------------------------------------------------------------------------------------------------------------- --------------------------

4.WINFORM form callback value, use delegate, not limited to form sub-parent form pass value, very useful.

The 5.backgroundworker1 component is used to download in the background or to upload to the database operation, the interface operation is not allowed.

Designer and business logic for the Ribbon:

Click the login button to determine whether the current sheet is available for this plug-in, select the appropriate landing conditions (data source from the data from the DataTable), and get the current AD domain user values, determine the permissions OK, back to the current ribbon interface. For future business applications. Automatically empties the specific contents of the current sheet after each login.

Click the download button (not available) Formula column automatically initialize Excel, from Db->excel, Excel column fields and the DB table field mapping, using a two-dimensional array to assign a value, high efficiency.

< download and upload an update insert that includes two tables, the master table is used to save some notes and table first class information for the template >

The user edits Excel under the appropriate landing conditions.

Click the upload button (not logged in is not available) Excel->db, to determine whether the corresponding field in Excel,no-> prompt Nocell The number of rows, modified after uploading.

The following is an Excel template:

The above is the approximate business logic.

Next to the basic approximate logic of the part of the code, delete some of the business, to share with you.

Db->excel

protected voidFill (System.Data.DataTable DT,intRowstart,intColstart,BOOLIsformat,intDigit) {            intp =50000;  for(intBeginindex =0; Beginindex < dt. Rows.Count; Beginindex + =p) {intEndIndex = dt. Rows.Count-1; if(Beginindex + P <dt. Rows.Count) {EndIndex= Beginindex + P-1; }                Object[,] arr =New Object[Endindex-beginindex +1, dt.                Columns.count];  for(inti = Beginindex; I <= EndIndex; i++)                {                     for(intj =0; J < dt. Columns.count; J + +) {Arr[i-Beginindex, j] = dt. ROWS[I][J]. ToString (). Replace ("=",""); }} Range Range= Sheet.get_range (sheet. Cells[rowstart + Beginindex +1, Colstart +1], sheet. Cells[rowstart + EndIndex +1, Colstart +dt.                Columns.count]); Range. Value2=arr; Range. Borders.LineStyle=xllinestyle.xlcontinuous; if(Isformat) {stringStrdigit =string.                    Empty;  for(intK =0; K < Digit; k++) {Strdigit+="0"; }                    if(string. IsNullOrEmpty (Strdigit)) {range. NumberFormat="#,# #0. XX"; }                    Else{range. NumberFormat="#,# #0."+Strdigit; }                }                Else{range. NumberFormat="#,# #0"; }            }        }

The various basic properties of range:

Range. Entirecolumn.hidden =true;//Hide Columns        /// <summary>        ///Set header row style, red bottom/// </summary>        /// <param name= "DT" ></param>        /// <param name= "sheet" ></param>        /// <param name= "RowCount" >number of rows set</param>         Public voidSettitlestyle (System.Data.DataTable DT, Excel.Worksheet sheet,intRowCount) {            if(dt. Rows.Count <=0|| Dt. Columns.count <=0)            {                return; } rngtemp= Sheet.get_range (sheet. Cells[startrowindex +1, Startcolindex +1], sheet. Cells[startrowindex +1, Startcolindex +dt.            Columns.count]); RngTemp.Interior.ColorIndex= -; RngTemp.Font.ColorIndex=2; RngTemp.Font.Bold=1; }//=====================================            if(Worksheet. protectcontents)//de-protect if protected{worksheet. Unprotect ("MyPassword");//previously downloaded, before deleting to set not protected} excel.range Range=(Excel.Range) Worksheet.get_range ((excel.range) worksheet. Cells[startrow, StartColumn], (excel.range) worksheet.            Cells[endrow, EndColumn]); Range. Select ();//Select the range you want to clear            if(isdeleteentirerow) {//whether the entire row is deletedrange.            Entirerow.delete (EXCEL.XLDELETESHIFTDIRECTION.XLSHIFTUP); }            Else            {                //range. Delete (Excel.XlDeleteShiftDirection.xlShiftUp);Range2.                Cells.clearcontents (); Range. Cells.clearcontents ();//Clear only Values//Navigate to first cellExcel.Range range3 = worksheet.get_range (worksheet. cells[ One,1], worksheet. cells[ One,1]); Range3.            Activate (); }

Get the current domain and user name:

        Private string domainName = System.Environment.UserDomainName; // gets the current ad domain        Private string domainusername = System.Environment.UserName; // Gets the current domain user name

Set Formula and protection:

Excel.Range range2 = worksheet.get_range (worksheet. Cells[i,5], worksheet. Cells[i,5]); if(Worksheet. protectcontents)//de-protect if protected{worksheet. Unprotect ("MyPassword");//previously downloaded, before deleting to set not protected} range2. Formula="=b"+ i +"+c"+ i +"+d"+i; Range2. Locked=false; if(ifprotected) {range2. Locked=true; } ... worksheet. Protect ("MyPassword", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.miss ING, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.missi Ng, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.missin g);

In general, this is the first, the work has come,,, refueling!

fighting~~~~~~~~~~~~

C # Exceladdin Development notes