fails to create excel sheet diminically by using NamedRange.Inner

[chan]

topic: Excel Object Model Reference (Visual Studio Tools for Office)
NamedRange.InnerObject Property
Gets a Microsoft.Office.Interop.Excel.Range that represents the underlying
native object for the Microsoft.Office.Tools.Excel.NamedRange.

Namespace: Microsoft.Office.Tools.Excel
Assembly: Microsoft.Office.Tools.Excel (in microsoft.office.tools.excel.dll)

I have current issue migartion of old office 2003 template XLT project to
office 2007 excle enviroment. the project working fine in Office 2003 sp1,
net 2003, PIA 1.1, vstor for office 2005.
when this project link to using office 2007 , enviroment:
NET 2005, PIA for office 2007, office 200 system, vstor se.
for user use in office 2007 sys , we change the dll . replace the
underlying native object for the Microsoft.Office.Tools.Excel.NamedRange to
Microsoft.Office.Interop.Excel. sheet and namedRange controls. that
issue :

it fails to create sheet incremental in runtime ( dynimically)after to
change to call the Microsoft.Office.Interop.Excel. sheet and namedRange
controls .

please advice of this issue that related to Compatibilty of office VSTO
2003-2007 tools. dll. thanks
pinky, chan


(e-mail address removed)

 

[Cindy M.]

Hi chan,

I have current issue migartion of old office 2003 template XLT project to
office 2007 excle enviroment. the project working fine in Office 2003 sp1,
net 2003, PIA 1.1, vstor for office 2005.
when this project link to using office 2007 , enviroment:
NET 2005, PIA for office 2007, office 200 system, vstor se.
for user use in office 2007 sys

Here, again, something is not clear.

1. There was never an Office version 2005. Which version of Office do you
mean?

2. When you write .NET 2005 do you mean Visual Studio 2005? Do you mean
VSTO 2005?

Did you upgrade a VSTO 2003 solution to work with VSTO 2005 + Office 2003?
And now you're trying to migrate it to Office 2007?

it fails to create sheet incremental in runtime ( dynimically)after to
change to call the Microsoft.Office.Interop.Excel. sheet and namedRange
controls .

This is not clear. It would probably help if you provided some commented
code (comments to indicate what the code is expected to do) for what used
to work and what you're currently trying.

Once again, please note that the best place to get support for VSTO is the
forum: http://forums.microsoft.com/msdn/showforum.aspx?
forumid=16&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[chan]

hello Cindy:

1-it was some of typo in last post i am sorry for that.
let us to clearify some of the quetions i use pinkyonline

-- cindy id you upgrade a VSTO 2003 solution to work with VSTO 2005 +
Office 2003? And now you're trying to migrate it to Office 2007?

pinkyonline: yes exactely ,
By running a legacy Excel template let application that is using VSTO
sheet object. Which is required to run solutions built using VSTO 2005 this
runtime supports solutions built for Microsoft Office 2007 system. The
creating sheets in run time dynamically are failing to completion properly.

Scenario

GeoDSStemplate projects are creating sheets by using
the“Microsoft.Office.Tools.excel dynamically which is VSTO sheet object so
that we can access the named range controls we created in the sheets
programmatically. The project absolutory runs fine in Microsoft Office 2003
system by using VSTO.
After replace VSTO sheet objects with excel Interop sheet objects and
Interop range objects using “Microsoft.Office.Interop.excel†in office 2007
system it fails the creating sheet to create sheets dynamically at runtime .

Note pleae kindly find the common code in this Blog below setion.

pinkyonline: we are subspecing :

The issue occrued in office 2007 migration now.the creating a sheet in
runtime falis to instance a NamedRange control by using Excel =
Microsoft.Office.Interop.Excel;
This template project is working fine with is task when office 2003 is by
using ExcelVSTO = Microsoft.Office.Tools.Excel;

pinkonline:
when we replace the Microsoft.Office.Tools.Excel to
Microsoft.Office.Interop.Excel to for office 2007 imgration.
it is fail. one of error example :
In method calls between 2 libraries ExcelTemplateLibrary and
GeoDSSTemplateLibrary in runtime , the processing sometimes ( it ) have a
few errors for method overload errors with invalid augments. refer to
getRange, or AddNamed Range method related to NameRange control;
Error 21 The best overloaded method match for
'ExcelTemplateLibrary.clsGlobalTemplateEngine.AddNamedRange(string,
Microsoft.Office.Interop.Excel.Range, string)' has some invalid arguments
C:\cd2005\ExcelTemplate2007issue\offline report \

pinkyonline: nature of the design in Environment:

it is built in excel office 2003, vsto for office 2003, visaul studio 2005
team suite , project type: template , migrate to office 2007 , with upgrade
to VSTO 2005 se



pinkyonline: i need to know does any limitation or not-support by VSTO 2005
se for office 2007 to using:
is a corrct appoaching write code : to creating a sheet in runtime to
instance a NamedRange control by using Excel =
Microsoft.Office.Interop.Excel;
in office 2007 migration now with C#?


thank you advance:
Pink
i will post next 3 post for add the code. it is limited to 30000

Excel.Range rngRange;
Excel.Range rngDivRange;
DataRow[] arr_dRSheet;
DataRow[] arr_dRRange;
string[,] arr_strRRRange;
string strWSName = "";
string strFilter = "";
string strSuperRangeName = "";
string strSoldTo = "";
string strDivRangeName = "";
int iRRColsCount = 0;
int iRRRowsCount = 0;
int iRowIndx = 0;
int iStartIndx = 1;
string strUpdDim = "";
string[] arr_strUpdDim ;
DataRow[] arr_dRSubRange;
string strSheetCaption = "";
string strDivRangeFilter = "";
DataTable dTRangeData = null;
DataTable dTDivRangeData = null;
int iDivColCnt = 0;

i will post the code in next 3 post for each method.
thanks
pinkychan

 

[chan]

public static void AddNamedRange(string strSheetName, Excel.Range
rngRange, string strRangeName)
{
string strAddress = "";

try
{
strAddress = "=" + strSheetName + "!" + GetAddress(rngRange);
AddNames(strRangeName, strAddress);
}
catch (Exception ex)
{
if (clsGlobal.PublishError == true)
{
clsGlobal.PublishException(ex);
}
throw new Exception(ex.Message);
}
}

public static void AddNamedRange(Excel.Worksheet wsInterop,
Excel.Range rngRange, string strRangeName)
{
try
{

clsGlobal.TemplateApplication.ActiveWorkbook.Names.Add(strRangeName,
rngRange, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//wsInterop.Names.Add(strRangeName, rngRange, true,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
if (clsGlobal.PublishError == true)
{
clsGlobal.PublishException(ex);
}
throw new Exception(ex.Message);
}
}

public static void AddNames(string strName, string strValue,
Excel.Worksheet wsInterop)
{
try
{
wsInterop.Names.Add(strName, strValue, true, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
}
catch (Exception ex)
{
if (clsGlobal.PublishError == true)
{
clsGlobal.PublishException(ex);
}
throw new Exception(ex.Message);
}
finally
{
}
}

public static Excel.Range GetRange(string strRangeName,
Excel.Worksheet wsInterop)
{
string strRefersTo = "";
Excel.Range rngRange = null;

try
{
try
{
strRefersTo = wsInterop.Names.Item(strRangeName,
Type.Missing, Type.Missing).Value;
strRefersTo = strRefersTo.Replace("=", "");
strRefersTo = strRefersTo.Replace("\"", "");
if (strRefersTo.Trim() != "")
{
rngRange = GetRangeFromName(strRefersTo);
}
}
catch
{
rngRange = GetRange(strRangeName);
}
}
catch //(Exception ex)
{
rngRange = null;
//if (clsGlobal.PublishError == true)
//{
// clsGlobal.PublishException(ex);
//}
//throw new Exception(ex.Message);
}
return rngRange;
}
public static void DeleteNames(string strName)
{
Excel.Name nmRange = null;

try
{
nmRange =
clsGlobal.TemplateApplication.ActiveWorkbook.Names.Item(strName,
Type.Missing, Type.Missing);
if (nmRange != null)
{
nmRange.Delete();
}
}
catch
{
}
}
#endregion "Dynamic SubTotal/GrandTotal design"
//SubTotal/GrandTotal design support_spattana_END
}
}

 

[chan]

public static void AddNamedRange
public static void AddNamedRange(string strSheetName, Excel.Range
rngRange, string strRangeName)
{
string strAddress = "";

try
{
strAddress = "=" + strSheetName + "!" + GetAddress(rngRange);
AddNames(strRangeName, strAddress);
}
catch (Exception ex)
{
if (clsGlobal.PublishError == true)
{
clsGlobal.PublishException(ex);
}
throw new Exception(ex.Message);
}
}

public static void AddNamedRange(Excel.Worksheet wsInterop,
Excel.Range rngRange, string strRangeName)
{
try
{

clsGlobal.TemplateApplication.ActiveWorkbook.Names.Add(strRangeName,
rngRange, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//wsInterop.Names.Add(strRangeName, rngRange, true,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
if (clsGlobal.PublishError == true)
{
clsGlobal.PublishException(ex);
}
throw new Exception(ex.Message);
}
}

public static void AddNames(string strName, string strValue,
Excel.Worksheet wsInterop)
{
try
{
wsInterop.Names.Add(strName, strValue, true, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
}
catch (Exception ex)
{
if (clsGlobal.PublishError == true)
{
clsGlobal.PublishException(ex);
}
throw new Exception(ex.Message);
}
finally
{
}
}

public static Excel.Range GetRange(string strRangeName,
Excel.Worksheet wsInterop)
{
string strRefersTo = "";
Excel.Range rngRange = null;

try
{
try
{
strRefersTo = wsInterop.Names.Item(strRangeName,
Type.Missing, Type.Missing).Value;
strRefersTo = strRefersTo.Replace("=", "");
strRefersTo = strRefersTo.Replace("\"", "");
if (strRefersTo.Trim() != "")
{
rngRange = GetRangeFromName(strRefersTo);
}
}
catch
{
rngRange = GetRange(strRangeName);
}
}
catch //(Exception ex)
{
rngRange = null;
//if (clsGlobal.PublishError == true)
//{
// clsGlobal.PublishException(ex);
//}
//throw new Exception(ex.Message);
}
return rngRange;
}
public static void DeleteNames(string strName)
{
Excel.Name nmRange = null;

try
{
nmRange =
clsGlobal.TemplateApplication.ActiveWorkbook.Names.Item(strName,
Type.Missing, Type.Missing);
if (nmRange != null)
{
nmRange.Delete();
}
}
catch
{
}
}
#endregion "Dynamic SubTotal/GrandTotal design"
//SubTotal/GrandTotal design support_spattana_END
}
}

 

[chan]

private void SetRangeData(ExcelVSTO.NamedRange nrDivRange, string
strDivRangeName, Excel.Range rngDivRange)
{
DataTable dTData = null;
DataTable dTGroup = null;
DataRow[] arr_dRGroup = null;
DataRow[] arr_dRData = null;
object[,] arr_objDivRangeCalCol = null;
string[,] arr_strDivRangeCols = null;
string strFilter = "";
DataTable dTUColumns;
DataTable dTUserData = null;
System.Array arr_RangeData = null;
System.Array arr_GroupData = null;
int iSubGroupColIndx = 0;
int[] arr_iSubTotalRowIndex = null;
int iGroupIndx = 0;
Excel.Range rngIns = null;
Excel.Range rngData = null;
Excel.Range rngCell = null;
int iDivRowCnt = 0;
int iDivColCnt = 0;

try
{
dTGroup = m_dSTemplateData.Tables[clsGlobal.STR_TBL_GROUP];
if (dTGroup != null)
{
dTUColumns = new DataTable();
dTUColumns.Columns.Add("DataRow");
dTUColumns.Columns.Add("Index");
dTUColumns.Columns.Add("Type");

try
{
rngIns = (Excel.Range)nrDivRange.Cells[2, 1];
}
catch
{
rngIns = (Excel.Range)rngDivRange.Cells[2, 1];
}
arr_objDivRangeCalCol =
GetCalcNamedRanges(strDivRangeName);
//Record Range column details
arr_strDivRangeCols = GetRangeColumns(nrDivRange, ref
dTUColumns, rngDivRange);
if (clsGlobal.HasUserData == true)
{
if (clsGlobal.RangeIndentifierColumns.Trim() == "")
{
iSubGroupColIndx =
GetSubGroupColumn(m_nrCurrent, m_rngCurrent);
dTUserData = GetUserData(nrDivRange, dTUColumns,
iSubGroupColIndx, strDivRangeName, rngDivRange);
}
else
{
dTUserData = GetUserData(nrDivRange, dTUColumns,
strDivRangeName, rngDivRange);
}
}
//Set Data to the range


if (m_dTSuperRangeData != null)
{
dTData = m_dTSuperRangeData;
}
else
{
dTData = m_dSTemplateData.Tables[m_strTableName];
}
arr_dRGroup = dTGroup.Select("", clsGlobal.STR_COL_ORDER);
arr_iSubTotalRowIndex = new int[arr_dRGroup.Length];
foreach (DataRow dRGroup in arr_dRGroup)
{
strFilter = clsGlobal.STR_COL_GROUP + " = '" +
dRGroup[clsGlobal.STR_COL_GROUP].ToString() + "'";
if (dTData.Columns[clsGlobal.STR_COL_ORDER + "New"]
!= null)
{
arr_dRData = dTData.Select(strFilter,
clsGlobal.STR_COL_ORDER + "New");
}
else
{
arr_dRData = dTData.Select(strFilter);
}

arr_GroupData = ConvertDataRowToArray(arr_dRData,
arr_strDivRangeCols, dTUserData);
AddSubTotalPlaceholderRow(ref arr_RangeData,
arr_GroupData, ref arr_iSubTotalRowIndex, ref iGroupIndx, dRGroup);
iGroupIndx = iGroupIndx + 1;
}
if (clsGlobal.GrandTotal == true)
{
AddSubTotalPlaceholderRow(ref arr_RangeData, null,
ref arr_iSubTotalRowIndex, ref iGroupIndx, null);
}

try
{
iDivRowCnt = nrDivRange.Rows.Count;
iDivColCnt = nrDivRange.Columns.Count;
}
catch
{
iDivRowCnt = rngDivRange.Rows.Count;
iDivColCnt = rngDivRange.Columns.Count;
}
if (iDivRowCnt < arr_RangeData.GetUpperBound(0) + 1)
{
for (int i = iDivRowCnt; i <
arr_RangeData.GetUpperBound(0) + 1; i++)
{
if (m_bInsertRow == false)
{
rngIns = rngIns.get_Resize(Type.Missing,
iDivColCnt);

rngIns.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing);
}
else
{

rngIns.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown,
Type.Missing);
}
}
}

try
{
iDivRowCnt = nrDivRange.Rows.Count;
}
catch
{
iDivRowCnt = rngDivRange.Rows.Count;
}
if (arr_strDivRangeCols.GetUpperBound(0) >= 1)//multi
dimensional array
{
if (arr_RangeData.GetUpperBound(0) + 1 < iDivRowCnt)
{
try
{
rngData = (Excel.Range)nrDivRange.Cells[1, 1];
}
catch
{
rngData = (Excel.Range)rngDivRange.Cells[1,
1];
}
rngData =
rngData.get_Resize(arr_RangeData.GetUpperBound(0) + 1, iDivColCnt);
try
{
rngData.ClearContents();
}
catch { }
rngData.Value2 = arr_RangeData;
}
else
{
try
{
try
{
nrDivRange.ClearContents();
}
catch { }
nrDivRange.Value2 = arr_RangeData;
}
catch
{
try
{
rngDivRange.ClearContents();
}
catch { }
rngDivRange.Value2 = arr_RangeData;
}
}
}
else//one dimensional array
{
try
{
rngData = (Excel.Range)nrDivRange.Cells[1, 1];
}
catch
{
rngData = (Excel.Range)rngDivRange.Cells[1, 1];
}
string[] arr_strData = (string[])arr_RangeData;
if (arr_RangeData.GetUpperBound(0) + 1 <= iDivRowCnt)
{
rngData =
rngData.get_Resize(arr_RangeData.GetUpperBound(0) + 1, iDivColCnt);
}
else
{
rngData = rngData.get_Resize(iDivRowCnt,
iDivColCnt);
}
for (int iRIndx = 1; iRIndx <= rngData.Rows.Count;
iRIndx++)
{
rngCell = (Excel.Range)rngData.Cells[iRIndx, 1];
try
{
rngCell.ClearContents();
}
catch { }
rngCell.Value2 = arr_strData[iRIndx - 1];
}
}

//Reset Calc range formulas
SetCalRangeFormula(arr_objDivRangeCalCol);
AddSubTotalAndGrndTotalRow(arr_dRGroup,
arr_iSubTotalRowIndex, strDivRangeName);
}
else
{
throw new Exception("SubTotal groups table not found.");
}
}
catch (Exception ex)
{
if (clsGlobal.PublishError == true)
{
clsGlobal.PublishException(ex);
}
throw new Exception(ex.Message);
}
finally
{
dTData = null;
dTGroup = null;
arr_dRGroup = null;
arr_dRData = null;
arr_objDivRangeCalCol = null;
arr_strDivRangeCols = null;
dTUColumns = null;
dTUserData = null;
arr_RangeData = null;
arr_GroupData = null;
rngIns = null;
rngData = null;
rngCell = null;
}
}

private void AddSubTotalPlaceholderRow(ref System.Array
arr_RangeData,
System.Array arr_GroupData,
ref int[]
arr_iSubTotalRowIndex,
ref int iGroupIndx,
DataRow dRGroup)
{
int iRowCnt = 0;
int iLastRowIndx = 0;

try
{
if (arr_RangeData != null)
{
iRowCnt = arr_RangeData.GetUpperBound(0) + 1;
iLastRowIndx = arr_RangeData.GetUpperBound(0);
}
else
{
iLastRowIndx = -1;
}
if (dRGroup != null)
{
if
(dRGroup[clsGlobal.STR_COL_GROUPCAPTION].ToString().Trim() != "")
{
iRowCnt = iRowCnt + 1;//Sub Total Row
}
}

if (arr_GroupData != null)
{
ResizeArray(ref arr_RangeData, iRowCnt +
(arr_GroupData.GetUpperBound(0) + 1),arr_GroupData.GetUpperBound(1)+1);
for (int iRow = 0; iRow <=
arr_GroupData.GetUpperBound(0); iRow++)
{
iLastRowIndx = iLastRowIndx + 1;
for (int iCol = 0; iCol <=
arr_RangeData.GetUpperBound(1); iCol++)
{

arr_RangeData.SetValue(arr_GroupData.GetValue(iRow, iCol), iLastRowIndx,
iCol);
}
}
iLastRowIndx = iLastRowIndx + 1;
arr_iSubTotalRowIndex[iGroupIndx] = iLastRowIndx;
}
else
{
ResizeArray(ref arr_RangeData,
arr_RangeData.GetUpperBound(0) + 2, arr_RangeData.GetUpperBound(1) + 1);
}
}
catch (Exception ex)
{
if (clsGlobal.PublishError == true)
{
clsGlobal.PublishException(ex);
}
throw new Exception(ex.Message);
}
}

private void AddSubTotalAndGrndTotalRow(DataRow[] arr_dRGroup,
int[] arr_iSubTotalRowIndex,
string strDivRangeName)
{
int iColIndx = 0;
string[] arr_strRangeIndentifierColumns = null;
int iSubTotalRowIndex = 0;
int iIndx = 0;
string strRefersTo = "";
Excel.Range rngDivRange = null;
Excel.Range rngCell = null;
Excel.Range rngSum = null;
string strTotalGroupCaption = "";
int iSumStartRow = 1;
int iBufferRow = 0;
string[] arr_strGrantTotalFormula = null;
int iFormulaCol = 0;

try
{
if (!(strDivRangeName.StartsWith("nr")))
{
strDivRangeName = clsGlobal.STR_NAMED_RANGE_PREFIX +
strDivRangeName;
}
strRefersTo = GetNameValue(strDivRangeName);
if (strRefersTo.Trim() != "")
{
rngDivRange = GetRangeFromName(strRefersTo);
}

arr_strRangeIndentifierColumns =
clsGlobal.RangeIndentifierColumns.Split(',');
iColIndx = GetColumnIndex(m_nrCurrent,
arr_strRangeIndentifierColumns[0], m_rngCurrent);
foreach (DataRow dRGroup in arr_dRGroup)
{
iSubTotalRowIndex = arr_iSubTotalRowIndex[iIndx];
if (iIndx > 0)
{
iSumStartRow = arr_iSubTotalRowIndex[iIndx - 1] + 1;
}
else
{
iSumStartRow = 0;
}
iSumStartRow = iSumStartRow + 1;
strTotalGroupCaption =
dRGroup[clsGlobal.STR_COL_GROUPCAPTION].ToString();
if (strTotalGroupCaption.Trim() == "")
{
iBufferRow = 1;
}
if (strTotalGroupCaption.Trim() != "")
{
rngCell =
(Excel.Range)rngDivRange.Cells[iSubTotalRowIndex + 1, iColIndx];
try
{
rngCell.ClearContents();
}
catch { }
rngCell.Value2 =
dRGroup[clsGlobal.STR_COL_GROUPCAPTION].ToString();
try
{
rngCell.Font.Bold = true;
}
catch { }
SetSubTotalRowProperty(rngCell);
}
if (arr_strGrantTotalFormula == null)
{
arr_strGrantTotalFormula = new
string[rngDivRange.Columns.Count - iColIndx];
}
iFormulaCol = 0;
for (int iCol = iColIndx + 1; iCol <=
rngDivRange.Columns.Count; iCol++)
{
if (strTotalGroupCaption.Trim() != "")
{
rngCell =
(Excel.Range)rngDivRange.Cells[iSubTotalRowIndex + 1, iCol];
}
if (strTotalGroupCaption.Trim() != "")
{
rngSum =
(Excel.Range)rngDivRange.Cells[iSumStartRow - iBufferRow, iCol];
rngSum = rngSum.get_Resize((iSubTotalRowIndex +
1 - iSumStartRow) + iBufferRow, Type.Missing);
try
{
rngCell.ClearContents();
}
catch { }
rngCell.Value2 = "=SUM(" + GetAddress(rngSum) +
")";
SetSubTotalRowProperty(rngCell);
//rngTotInd =
(Excel.Range)rngDivRange.Cells[(rngCell.Row - rngDivRange.Row) + 1, 1];
//rngTotInd = rngTotInd.get_Offset(Type.Missing,
-1);
//rngTotInd.Value2 = "TOT";
}
else
{
rngSum =
(Excel.Range)rngDivRange.Cells[iSumStartRow, iCol];
rngSum = rngSum.get_Resize(iSubTotalRowIndex + 1
- iSumStartRow, Type.Missing);
}
if (clsGlobal.GrandTotal == true)
{
if (arr_strGrantTotalFormula[iFormulaCol] != null)
{
if
(arr_strGrantTotalFormula[iFormulaCol].Trim() != "")
{
arr_strGrantTotalFormula[iFormulaCol] =
arr_strGrantTotalFormula[iFormulaCol] + " + ";
}
}
else
{
arr_strGrantTotalFormula[iFormulaCol] = "";
}
if (strTotalGroupCaption.Trim() != "")
{
arr_strGrantTotalFormula[iFormulaCol] =
arr_strGrantTotalFormula[iFormulaCol] + GetAddress(rngCell);
}
else
{
arr_strGrantTotalFormula[iFormulaCol] =
arr_strGrantTotalFormula[iFormulaCol] + "SUM(" + GetAddress(rngSum) + ")";
}
}
if (strTotalGroupCaption.Trim() != "")
{
try
{
rngCell.Font.Bold = true;
}
catch { }
}
iFormulaCol = iFormulaCol + 1;
}
iIndx = iIndx + 1;
iBufferRow = 0;
}
if (clsGlobal.GrandTotal == true)
{
rngCell =
(Excel.Range)rngDivRange.Cells[rngDivRange.Rows.Count, iColIndx];
try
{
rngCell.ClearContents();
}
catch { }
rngCell.Value2 = "TOTAL";
try
{
rngCell.Font.Bold = true;
}
catch { }
SetSubTotalRowProperty(rngCell);
iFormulaCol = 0;
for (int iCol = iColIndx + 1; iCol <=
rngDivRange.Columns.Count; iCol++)
{
rngCell =
(Excel.Range)rngDivRange.Cells[rngDivRange.Rows.Count, iCol];
try
{
rngCell.ClearContents();
}
catch { }
rngCell.Value2 = "=" +
arr_strGrantTotalFormula[iFormulaCol];
try
{
rngCell.Font.Bold = true;
}
catch { }
SetSubTotalRowProperty(rngCell);
//rngTotInd =
(Excel.Range)rngDivRange.Cells[(rngCell.Row - rngDivRange.Row) + 1, 1];
//rngTotInd = rngTotInd.get_Offset(Type.Missing, -1);
//rngTotInd.Value2 = "TOT";
iFormulaCol = iFormulaCol + 1;
}
}
}
catch (Exception ex)
{
if (clsGlobal.PublishError == true)
{
clsGlobal.PublishException(ex);
}
throw new Exception(ex.Message);
}
finally
{
rngDivRange = null;
rngCell = null;
rngSum = null;
}
}

public void SubTotalRowProperty(int iFontSize, int iFontColorIndex,
int iBackColorIndex)
{
try
{
m_iFontSize = iFontSize;
m_iFontColor = iFontColorIndex;
m_iBackColor = iBackColorIndex;
}
catch (Exception ex)
{
if (clsGlobal.PublishError == true)
{
clsGlobal.PublishException(ex);
}
throw new Exception(ex.Message);
}
}

private void SetSubTotalRowProperty(Excel.Range rngCell)
{
try
{
if (m_iFontSize > 0)
{
rngCell.Font.Size = m_iFontSize;
}
if (m_iFontColor > 0)
{
rngCell.Font.ColorIndex = m_iFontColor;
}
if (m_iBackColor > 0)
{
rngCell.Interior.ColorIndex = m_iBackColor;
}
}
catch //(Exception ex)
{
//if (clsGlobal.PublishError == true)
//{
// clsGlobal.PublishException(ex);
//}
//throw new Exception(ex.Message);
}
}

public static Excel.Range GetRange(string strRangeName)
{
string strRefersTo = "";
Excel.Range rngRange = null;

try
{
strRefersTo = GetNameValue(strRangeName);
if (strRefersTo.Trim() != "")
{
rngRange = GetRangeFromName(strRefersTo);
}
}
catch //(Exception ex)
{
rngRange = null;
//if (clsGlobal.PublishError == true)
//{
// clsGlobal.PublishException(ex);
//}
//throw new Exception(ex.Message);
}
return rngRange;
}

public static void AddNamedRange(string strSheetName, Excel.Range
rngRange, string strRangeName)
{
string strAddress = "";

try
{
strAddress = "=" + strSheetName + "!" + GetAddress(rngRange);
AddNames(strRangeName, strAddress);
}
catch (Exception ex)
{
if (clsGlobal.PublishError == true)
{
clsGlobal.PublishException(ex);
}
throw new Exception(ex.Message);
}
}

public static void AddNamedRange(Excel.Worksheet wsInterop,
Excel.Range rngRange, string strRangeName)
{
try
{

clsGlobal.TemplateApplication.ActiveWorkbook.Names.Add(strRangeName,
rngRange, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//wsInterop.Names.Add(strRangeName, rngRange, true,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
}
catch (Exception ex)
{
if (clsGlobal.PublishError == true)
{
clsGlobal.PublishException(ex);
}
throw new Exception(ex.Message);
}
}

public static void AddNames(string strName, string strValue,
Excel.Worksheet wsInterop)
{
try
{
wsInterop.Names.Add(strName, strValue, true, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
}
catch (Exception ex)
{
if (clsGlobal.PublishError == true)
{
clsGlobal.PublishException(ex);
}
throw new Exception(ex.Message);
}
finally
{
}
}

public static Excel.Range GetRange(string strRangeName,
Excel.Worksheet wsInterop)
{
string strRefersTo = "";
Excel.Range rngRange = null;

try
{
try
{
strRefersTo = wsInterop.Names.Item(strRangeName,
Type.Missing, Type.Missing).Value;
strRefersTo = strRefersTo.Replace("=", "");
strRefersTo = strRefersTo.Replace("\"", "");
if (strRefersTo.Trim() != "")
{
rngRange = GetRangeFromName(strRefersTo);
}
}
catch
{
rngRange = GetRange(strRangeName);
}
}
catch //(Exception ex)
{
rngRange = null;
//if (clsGlobal.PublishError == true)
//{
// clsGlobal.PublishException(ex);
//}
//throw new Exception(ex.Message);
}
return rngRange;
}