Steps to format an Excel table suitable to paste in Word

February 05, 2013


  • 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
        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();

You Might Also Like

0 comments

Popular Posts

Twitter