Steps to format an Excel table suitable to paste in Word

-

  • Create a table style

#region CREATE_TABLE_STYLE
                    //Creating table Sytle. Unprotect all the sheets. If anyone sheet is protected it will return a COM Exception hresult 0x800a03ec
                    foreach (Excel.Worksheet ws in activeWorksheets)
                    {
                        if (ws.ProtectionMode || ws.ProtectContents)
                        {
                            ws.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden;
                           SalesforceWrapper.sheet_UnProtect(ws);
                            protectedSheets.Add(ws);
                        }
                   }
                    
                    try
                    {
                        Globals.ThisAddIn.Application.ActiveWorkbook.TableStyles["MyProposalStyleName"].Delete();
                    }
                    catch (Exception excep)
                    {
                    //DISP_E_BADINDEX exception occurs if there is no TableStyle named “MyProposalStyleName”                       
if (!excep.Message.Contains("DISP_E_BADINDEX"))
                        {
                            throw excep;
                        }
                    }
             
                    createTableStyle("MyProposalStyleName");
                    //Protect sheets

                    foreach (Excel.Worksheet ws in protectedSheets)
                    {
                        ws.Visible = Excel.XlSheetVisibility.xlSheetVisible;
                        SalesforceWrapper.sheet_Protect(ws);
                    }

                    #endregion

       //We had no luck using the Color property. Thus, using color index (http://msdn.microsoft.com/en-us/library/office/cc296089(v=office.12).aspx)

       void createTableStyle(string styleName)
        {
            Globals.ThisAddIn.Application.ActiveWorkbook.TableStyles.Add(styleName);
            Excel.TableStyle ptStyle = Globals.ThisAddIn.Application.ActiveWorkbook.TableStyles[styleName];
            ptStyle.ShowAsAvailableTableStyle = true;

            // Table style Header Row               
            Excel.TableStyleElement HeaderRow = ptStyle.TableStyleElements[Excel.XlTableStyleElementType.xlHeaderRow];
            HeaderRow.Interior.ThemeColor = Excel.XlThemeColor.xlThemeColorDark1;
            HeaderRow.Interior.ColorIndex = 23;
            HeaderRow.Interior.TintAndShade = -0.249946592608417;
            HeaderRow.Font.ColorIndex = 2;
            HeaderRow.Font.Bold = true;
            HeaderRow.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = 23;
            HeaderRow.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
            HeaderRow.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            HeaderRow.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = 23;
            HeaderRow.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
            HeaderRow.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = 23;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = 23;           
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex = 23;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = 23;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
            HeaderRow.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;

            // Table style Row Stripe 1
            Excel.TableStyleElement totalTable = ptStyle.TableStyleElements[Excel.XlTableStyleElementType.xlWholeTable];
            totalTable.Font.ColorIndex = 1;

            totalTable.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = 23;
            totalTable.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
            totalTable.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
            totalTable.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = 23;
            totalTable.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
            totalTable.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = 23;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = 23;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex = 23;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = 23;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
            totalTable.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;

        }

  •       Applying table style
// Reference : http://www.codeproject.com/Tips/137183/Tip-Format-an-Excel-Range-as-a-Table-Programatical
        public void FormatAsTable(Excel.Range SourceRange, string TableName, string TableStyleName)
        {
            SourceRange.Worksheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange,
            SourceRange, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing).Name =
                TableName;
            SourceRange.Select();

            SourceRange.Worksheet.ListObjects[TableName].TableStyle = TableStyleName;
            Excel.Range e = SourceRange.Cells[Type.Missing, 1];
            // applying conversion. 1 inch = 72 pt. Ex: 2.49 inches in Word = 2.49 * 72 pts in Excel
             
            e.ColumnWidth = 2.49 * 72;

            e = SourceRange.Cells[Type.Missing, 2];
            e.ColumnWidth = 1.19 * 72;

            e = SourceRange.Cells[Type.Missing, 3];
            e.ColumnWidth = 0.74 * 72;

            e = SourceRange.Cells[Type.Missing, 4];
            e.ColumnWidth = 1.02 * 72;

            e = SourceRange.Cells[Type.Missing, 5];
            e.ColumnWidth = 1.03 * 72;
        }

Now, create a table out of the selected range and format

FormatAsTable(rangeToCopy, "MyTableName", "MyProposalStyleName");



  •        Copy the table to clipboard

copiedRange.Copy();

Comments

Post a Comment

Feedback - positive or negative is welcome.

Popular posts from this blog

Updating SourceData/ Data Source of the Pivot Table

-
Image
Errors:  1. Run time error '1004' Cannot Open PivotTable source file https://....[servlet.FileDownload?]... 2. Data source reference is not valid 3. Can not use web data source as pivot data 4.  Run-time error '-2147024809 (80070057)' Item with specified name wasn't found 5.  Error: Cannot open PivotTable source file ‘[filename[x].xls]SourceData’ 6.  Exception from HRESULT: 0x800A03EC   at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String message)    at CallSite.Target(Closure , CallSite , ComObject , String )    at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)    at CallSite.Target(Closure , CallSite , Object , String )    at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1) Solution: We have spend a day searching around for the solution. We ...
Read more

Salesforce.com migration tool - Deploying Weblink and migrating files with special characters

-
Image
Recently, I noticed the followed issues in SFDC Ant deployment. Issue 1: Typo in XML <name> metadata data for Web Link Just to reiterate, all the buttons and links created under "Buttons and Links" section of the Object are treated as Web Links . Screenshot from Force.com Migration Tool Guide It should be WebLink, not Weblink. Otherwise, the following error occurs while extracting the package :  package.xml - Entity type: 'Weblink' is unknown. Correct XML is:  <types> <members>Opportunity.Cancel_CheckOut</members> <members>Opportunity.Generate_Deal_Sheet</members> <name>WebLink</name> </types> Issue 2: Unable to deploy entities with special characters Example: <types> <members> User w/Export  </members> <name>Profile</name> </types> I tried to deploy Profile entities with special characters like '-' and '/...
Read more

Information Architecture - Setup your term store to scale

-
In this post I am going to put together a list of best practices to consider when setting up a term store in Sharepoint Online. As you may already know term store is collection of term groups. And each term group consists of number term sets. Each term set contains a list/hierarchy of terms. Team group gives the flexibility in terms of access. You can assign permissions to users on their term group, rather than giving them access to the entire store.  Always maintain a corporate/general term group which contains common term sets across organization.  Every group/business unit should have a term group. Pin the terms from corporate term group as required into Business unit term groups.  For example, you plan to maintain list of document types in common term group. Thus, there is a term set created. Marketing department want to use this term set. But, they want to add a few more terms. In this case, you should create another term set in Marketing department term g...
Read more