CODEWITHSUNDEEP
c#excelgembox-spreadsheet
automationFormation
automationFormation

Reputation: 31

GemBox Spreadsheet

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!

Upvotes: 1

Views: 2408

Answers (3)

Mario Z
Mario Z

Reputation: 4381

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();

Upvotes: 1

Sondre
Sondre

Reputation: 364

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

Upvotes: 0

Aliasghar Bahrami
Aliasghar Bahrami

Reputation: 267

you can Use This Code

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

Upvotes: 0

Related Questions