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.