Read all the text

[shivinder singh]

Hi,

Is there a method like gettext() in word where i can get all the text in all the worksheers string format?

shiv

[Prasanth M]

Hi Shivinder,

Thank you for using Syncfusion products.

XlsIO does not support to get all the text in worksheet as a string. Whereas you can achieve this through a workaround. We have created a sample that illustrate how to get all the text in worksheet as string. It can be downloaded from the following link.

Sample Link: GetText.zip

Kindly refer to this and let us know if you need any clarification.

Regards,
Prasanth

[shivinder singh]

Hi Prasanth,

I am very very confused with this. i downloaded the zip file and ran it. it opens a web site and then says download excel. whcih then downloads a file. how is this getting the text of an excel file???

This does not help me at all. I need code in my c# program that gets text that then i can manipulate

[Prasanth M]

Hi Shivinder,

In the given sample, we have accessed the GetAllText method to return all texts in worksheet as string. We have shared the code snippets for your reference. Kindly try this and let us know if this helps.

Code snippets:

//Step 1 : Instantiate the spreadsheet creation engine.

            ExcelEngine excelEngine = new ExcelEngine();

            //Step 2 : Instantiate the excel application object.

            IApplication application = excelEngine.Excel;

 

            string fileName = Server.MapPath("App_data/BussinessObject.xlsx");

            IWorkbook workbook = application.Workbooks.Open(fileName,     ExcelOpenType.Automatic);

            //IWorkbook workbook = application.Workbooks.Create(1);

            IWorksheet sheet = workbook.Worksheets[0];

 

            string text = sheet.GetAllText();

 

            workbook.Version = ExcelVersion.Excel2010;

            workbook.SaveAs("Output.xlsx", ExcelSaveType.SaveAsXLS, Response, ExcelDownloadType.PromptDialog);

 

            workbook.Close();

            excelEngine.Dispose();   

//**************************Work Around**************************

    public static class Extension

    {

        public static string GetAllText(this IWorksheet sheet)

        {

            int lastRow = sheet.UsedRange.LastRow;

            int lastColumn = sheet.UsedRange.LastColumn;

            StringBuilder builder = new StringBuilder();

            IMigrantRange range = sheet.MigrantRange;

            for (int iRow = 1; iRow <= lastRow; iRow++)

            {

                for (int iColumn = 1; iColumn <= lastColumn; iColumn++)

                {

                    range.ResetRowColumn(iRow, iColumn);

                    builder.Append(range.DisplayText);

                    builder.Append(" ");

                }

            }

            return builder.ToString();

        }

    }   

//**************************Work Around**************************

Please let us know if you need any clarification.

Regards,
Prasanth