Extracting excel worksheet into a string in C#

Question

How can i extract excel worksheet into a string using C#? i already have the sheet and can save as txt file but instead of that I want to extract it into a string directly and do some processing.

score 3 · Answer 1 · answered Feb 11 '11 at 20:11

Try using a brilliant EPPlus library which allows you to load the spreadsheet and access all cells programatically.

Here's a few examples:

//Select all cells in column d between 9990 and 10000
var query1= (from cell in sheet.Cells["d:d"] where cell.Value is double && (double)cell.Value >= 9990 && (double)cell.Value <= 10000 select cell);
//In combination with the Range.Offset method you can also check values of other columns...


//Here we use more than one column in the where clause. 
//We start by searching column D, then use the Offset method to check the value of column C.
var query3 = (from cell in sheet.Cells["d:d"]
                     where cell.Value is double && 
                              (double)cell.Value >= 9500 && (double)cell.Value <= 10000 && 
                              cell.Offset(0, -1).Value is double &&      //Column C is a double since its not a default date format.
                              DateTime.FromOADate((double)cell.Offset(0, -1).Value).Year == DateTime.Today.Year+1 
                     select cell);

Console.WriteLine();
Console.WriteLine("Print all cells with a value between 9500 and 10000 in column D and the year of Column C is {0} ...", DateTime.Today.Year + 1);
Console.WriteLine();    

 count = 0;
//The cells returned here will all be in column D, since that is the address in the indexer. 
//Use the Offset method to print any other cells from the same row.
foreach (var cell in query3)    
{
   Console.WriteLine("Cell {0} has value {1:N0} Date is {2:d}", cell.Address, cell.Value, DateTime.FromOADate((double)cell.Offset(0, -1).Value));
   count++;
}

score 1 · Accepted Answer · answered Feb 11 '11 at 20:15

1

Something like the following (untested) code should work:

Excel.Application oXL= new Excel.Application();
oXL.Visible = true;
Excel._Workbook oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
Excel._Worksheet oSheet = (Excel._Worksheet)oWB.ActiveSheet;
string s = (string)oSheet.Cells[1, 1].Value;

Though look at this sample to see how to clean up everything properly etc.

answered Feb 11 '11 at 20:15

Hans Olsson

54,199
15
94
116

Looks good, any idea how to get cell number or row number of very last cell? – ns12345 Feb 11 '11 at 20:19
1

@1100: Look at the [UsedRange](http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.usedrange(v=vs.80).aspx) Property, which gives you a [Range](http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range_members.aspx), from there you can find out what cells/columns are in the range. – Hans Olsson Feb 11 '11 at 20:38

Extracting excel worksheet into a string in C#

2 Answers2