Say you have an
Excel.Range
variable called, e.g.,
MergeRange
that represents the range of cells you would've otherwise highlighted had you been working directly with an open instance of Excel.
Now suppose we want to Merge and Center the text that is in the range. How do we do this? By the below:
public void MergeAndCenter(Excel.Range MergeRange) {
MergeRange.Select();
MergeRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
MergeRange.VerticalAlignment = XlVAlign.xlVAlignBottom;
MergeRange.WrapText = false;
MergeRange.Orientation = 0;
MergeRange.AddIndent = false;
MergeRange.IndentLevel = 0;
MergeRange.ShrinkToFit = false;
MergeRange.ReadingOrder = (int)(Constants.xlContext);
MergeRange.MergeCells = false;
MergeRange.Merge(System.Type.Missing);
}
Ta-da! Works with the Microsoft Excel v12.0 and above object libraries.
You know how I did this? I opened up an instance of Excel on my own, typed in some text and performed a merge and center -- all while recording a Macro.
Then I just did a 'View Code' (on the Developer tab -- you can show it using the Ribbon Orb > Excel Options and then somewhere there's a check box that says "Show the Developer toolbar" -- and there's also Record Macro/Stop Macro buttons there) and then opened up the VBA module corresponding to the Macro.
I then translated the VBA code to C# equivalents.
Bonus tip: Now you don't have to Google search for how to do something with Excel Interop -- just record a macro, do it by hand, stop the macro, view its code, translate to your interop language and voila!
Dr. Brian Hart obtained his Ph.D. in Astrophysics from the University of California, Irvine, in 2008. Under Professor David Buote, Dr. Hart researched the structure and evolution of the universe. Dr. Hart is an Astrodynamicist / Space Data Scientist with Point Solutions Group in Colorado Springs, CO, supporting Space Operations Command, United States Space Force. Dr. Hart is a Veteran of the U.S. Army and the U.S. Navy, having most recently served at Fort George G. Meade, MD, as a Naval Officer with a Cyber Warfare Engineer designator. Dr. Hart has previously held positions at Jacobs Engineering supporting Cheyenne Mountain/Space Force supporting tests, with USSPACECOM/J58 supporting operators using predictive AI/ML with Rhombus Power, and with SAIC supporting the Horizon 2 program at STARCOM. Dr. Hart is well known to the community for his over 150 technical publications and public speaking events. Originally from Minneapolis/Saint Paul, Minnesota, Dr. Hart lives in Colorado Springs with his Black Lab, Bruce, and likes bowling, winter sports, exploring, and swimming. Dr. Hart has a new movie coming out soon, a documentary called "Galaxy Clusters: Giants of the Universe," about his outer space research. The movie showcases the Chandra X-ray Observatory, one of NASA’s four great observatories and the world’s most powerful telescopes for detecting X-rays. The movie has been accepted for screening at the U.S. Air Force Academy ("USAFA" for short) Planetarium and will highlight how scientists use clusters of galaxies, the largest bound objects in the Universe, to learn more about the formation and evolution of the cosmos --- as well as the space telescopes used for this purpose, and the stories of the astronauts who launched them and the scientists who went before Dr. Hart in learning more about the nature of the Universe.