VSTO-excel programming [To be continued] [updated on April 9, December 5, see below]

 

I am idle recently and the exam is coming soon. However, the programming technology still cannot be removed.

Now we have started the VSTO programming journey. This topic is related to blogs. Therefore, you may be interested in comments and exchanges from anonymous or non-Anonymous bloggers and technical experts.

By the way, the following code is self-written and accumulated. Every function is pasted in the form of Method. Generally, you can directly call it if you understand it, of course, it generally starts from the basics.

If you want to communicate with me, my homepage will contain my MSN ....

Returning to the question ----
VSTO [Excel]
// Create a Sheet
Private void CreditSheet ()
{
Excel. Workbook newWorkbook = this. Application. Workbooks. Add (missing );
}

// Open Sheet
Private void OpenSheet ()
{
This. application. workbooks. open (this. imputFolderName. text. toString (), missing, and missing );
}

// Display text in multiple cells consecutively and adjust the width of each cell
Private void AimatA1RangeUsingNameRange ()
{
// Set a NamedRange
NamedRange textInCell;
TextInCell = this. Controls. AddNamedRange (this. get_Range ("A8", missing), "EvaluateDemo ");

// Represcribe NameRange [A8-D8]
TextInCell. RefersTo = "= Sheet1! $ A $8: $ D $8 ";

// Evaluate
TextInCell. Value2 = "Hello world! ";

// Automatically adjust Cells
TextInCell. Columns. AutoFit ();
}

** The unique difference between the Value2 attribute and the Value attribute is that Value2 is not a parameterized attribute.

Private void FindMarthaInTheRange ()
{
This. Range ["H1", missing]. Value2 = "Martha jone ";

NamedRange auto_HR =
This. Controls. AddNamedRange (this. Range ["H2", missing],
"Auto_HR ");

// Automatically search for and copy the same value from the beginning based on the string in H1
Auto_HR.Value2 = auto_HR.AutoComplete ("Ma ");

// Automatically adjust Cells
Auto_HR.Columns.AutoFit ();
}

* Here, Martha jone cannot be searched at will, which means AutoComplete (parameter value). It can only be matched from the beginning and cannot be any match.

// Merge Cells
Private void MergeRange ()
{
NamedRange rangeOne = this. Controls. AddNamedRange (this. Range ["A12", "D27"], "rangeOne ");
RangeOne. Select ();
RangeOne. Merge (false );
}

// Set the cell border
Private void setBordersAndInterior ()
{
// Method 1
NamedRange bordersRange = this. Controls. AddNamedRange (this. Range ["E12", "F12"], "borderRange ");
BordersRange. Borders. LineStyle = Excel. XlLineStyle. xlSlantDashDot;

// Set Color
BordersRange. Interior. Color = 0xFF00;

// Method 2
For (int I = 1; I <= 7; I ++)
{
Base. Range [base. Cells [I, 7], base. Cells [I, 8]. Borders. LineStyle = I;
}
}


AutoCalculate # region AutoCalculate
// Automatic Calculation
Private void policychanges ()
{
NamedRange changesRange = this. Controls. AddNamedRange (this. Range ["G2", "G8"], "compositeRange ");
ChangesRange. Change + = new DocEvents_ChangeEventHandler (changesRange_Change );
}

Private void changesRange_Change (Range Target)
{
NamedRange namedRange2 =
This. Controls. AddNamedRange (this. Range ["G9", missing],
"NamedRange5 ");
// Set the formula
NamedRange2.Formula = "= SUM (G2: G8 )";
NamedRange2.FormulaHidden = true;
// Computing
NamedRange2.Calculate ();
}

That's it today... To be continued

# Endregion

Updated on April 9, December 5 (coming soon)

Question 1: Why do I enter a 19-digit or more-digit number in a cell? The number is not displayed in hexadecimal format, for example, 1111E + 9. Want to "display numbers in text "?
A: Just set NumberFormatLocal = "@" for this cell.
Ex:
This. Range ["A35", missing]. NumberFormatLocal = "@";
This. Range ["A35", missing]. value2. "111111111111111111111111111111111111111111 ";

Question 2: Do you want to remove the gridlines of the current active window?
A: Set DisplayGridlines to False. This. Application. ActiveWindow. DisplayGridlines = false;