Change to Use Spread Sheet Gear from Microsoft Office Interop OWC11

Question

I wish to change my code to Spread Sheet Gear from Microsoft Office Interop OWC11. However, I face some problems in my code. The code show here is using the Microsoft.Office.Interop.Owc11, I do try with Spread Sheet Gear with some IWorkBook, IWorkSheet, using SpreadsheetGear; etc. But I still fail to change and get error that make my file messy. May you help to assist me on this issue? Changing the following code to SpreadsheetGear

TemplateManagerBLTest.cs

using Microsoft.Office.Interop.Owc11;

public void FindRangeTest()
        {
            TemplateManagerBL target = new TemplateManagerBL();
            TestBusinessLogic.BusinessLogic_TemplateManagerBLAccessor accessor = new TestBusinessLogic.BusinessLogic_TemplateManagerBLAccessor(target);

            string rangeKeyword = "END";
            AxSpreadsheet currentSpreadSheet = GetTestSheet("..\\..\\..\\TestBusinessLogic\\TemplateManagerTestData\\TEST_TemplateForGetRangeTest.xml");

            int expectedCol, expectedRow;
            expectedCol = 1;
            expectedRow = 306;
            TemplateManagerBL.Coordinate actual;

            actual = accessor.FindRange(rangeKeyword, currentSpreadSheet);

            Assert.AreEqual(expectedCol, actual.Column, "FindRange did not return the expected column value.");
            Assert.AreEqual(expectedRow, actual.Row, "FindRange did not return the expected column value.");

        }

Inside GetTestSheet

#region "Test Data Methods"
        private AxMicrosoft.Office.Interop.Owc11.AxSpreadsheet GetTestSheet(string filePath)
        {
            AxMicrosoft.Office.Interop.Owc11.AxSpreadsheet currentSpreadSheet;
            string tXML = "";
            TextReader textReader = new StreamReader(@filePath);

            while (textReader.ReadLine() != null)
            {
                tXML = textReader.ReadToEnd();
            }
            TemplateControl tc = new TemplateControl();

            currentSpreadSheet = new AxMicrosoft.Office.Interop.Owc11.AxSpreadsheet();
            currentSpreadSheet = tc.SpreadSheet;
            currentSpreadSheet.XMLData = tXML;
            return currentSpreadSheet;
        }

Inside FindRange

internal global::BusinessLogic.TemplateManagerBL.Coordinate FindRange(string rangeKeyword, global::AxMicrosoft.Office.Interop.Owc11.AxSpreadsheet currentSpreadSheet)
        {
            object[] args = new object[] {
                rangeKeyword,
                currentSpreadSheet};
            global::BusinessLogic.TemplateManagerBL.Coordinate ret = ((global::BusinessLogic.TemplateManagerBL.Coordinate)(m_privateObject.Invoke("FindRange", new System.Type[] {
                    typeof(string),
                    typeof(global::AxMicrosoft.Office.Interop.Owc11.AxSpreadsheet)}, args)));
            return ret;
        }

score 1 · Answer 1 · answered Jun 30 '15 at 15:29

I'm not familiar with OWC, so I cannot rewrite your code below exactly, but below is some SpreadsheetGear code that will open an Excel file and attempt to find some keyword inside a given worksheet:

using SpreadsheetGear;

...

// Open a workbook.
// Note SpreadsheetGear opens XLS/XLSX/XLSM as well as comma- and tab-delimited
// files.  SpreadsheetGear does not support plain XML files, which you appear
// to use in your example code.
IWorkbook workbook = Factory.GetWorkbook(@"c:\path\to\workbook.xlsx");

// For convenience, make a local variable to the worksheet you are interested in.
IWorksheet worksheet = workbook.Worksheets["Sheet1"];

// Use IRange.Find(...) to look for a keyword within the "used range" of the sheet.
// Please see the documentation for IRange.Find(...) for more detailed information
// on each paramater option.
IRange foundCell = worksheet.UsedRange.Find("END", null, FindLookIn.Values, LookAt.Whole, 
    SearchOrder.ByColumns, SearchDirection.Next, false);

if(foundCell != null)
{
    // Note that SpreadsheetGear Row/Column indexes start at 0 and not 1
    int foundRow = foundCell.Row;
    int foundCol = foundCell.Column;

    // Compare to what you expected...
}

Change to Use Spread Sheet Gear from Microsoft Office Interop OWC11

1 Answers1