GemBox Spreadsheet

Question

How can I retrieve data from a worksheet into a string variable? There seems to be no method for it.

var workbook = ExcelFile.Load("Workbook.xls");
var worksheet = workbook.Worksheets[0];

How do I get the data in a worksheet into a string?

Kindly help me out!

Mario Z · Accepted Answer · 2016-05-03T13:30:11.177

Do you want to get the whole worksheet's data as text, or just a specific cell range, or just a specific cell?
Nevertheless here is how you can get the whole worksheet's data to string by converting it to TAB format:

var workbook = ExcelFile.Load("Workbook.xls");
workbook.Worksheets.ActiveWorksheet = workbook.Worksheets[0];

var options = new CsvSaveOptions(CsvType.TabDelimited);
options.Encoding = new UTF8Encoding(false);

using (var stream = new MemoryStream())
{
    workbook.Save(stream, options);

    string worksheetAsText = options.Encoding.GetString(stream.ToArray());
    // Do something with "worksheetAsText" ...
}

Or instead of writing into a stream you could use StringWriter, like this:

using (var writer = new StringWriter())
{
    workbook.Save(writer, options);

    string worksheetAsText = writer.ToString();
    // Do something with "worksheetAsText" ...
}

UPDATE (for GemBox.Document, see comment below):
There are two approaches how you can do this, one is to do the similar as above and save the DocumentModel into a plain text format (TxtSaveOptions).

Another way would be to just use the following:

var document = DocumentModel.Load("Document.docx");
string documentAsText = document.Content.ToString();

Thank-you very much! It works perfectly. Can you provide me for Gembox Document for MS Word also? The same as reading the whole document into a string. It would be much appreciated. I don't have any idea about Gembox and neither can find any examples to help me out. — automationFormation, May 02 '16 at 18:45

score 0 · Answer 2 · answered May 02 '16 at 11:50

0

you can Use This Code

TextBox1.Text=worksheet.Cells[6, 0].Value;

answered May 02 '16 at 11:50

Aliasghar Bahrami

267
3
14

score 0 · Answer 3 · edited May 02 '16 at 14:12

You can achive this using "Microsoft.Office.Interop.Excel"

using Excel = Microsoft.Office.Interop.Excel;

var workbookPath = "your workbookpath";
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Sheet1"; //name of the sheet
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
var cellValue = (string)(excelWorksheet.Cells[10, 2] as Excel.Range).Value; //cell is casted to string

Now the variable cellvalue holds the sting witch is in the cell [10,2]. Hopes this awensers the question

GemBox Spreadsheet

3 Answers3