Suppose I have range from E2 to E16. How do I read values from cells E2 to E16?
Add a comment
|
2 Answers
You can try somethinfg like this. it should work
U can specify ur range as u wish inside.
this.openFileDialog1.FileName = "*.xls";
if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
{
Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(
openFileDialog1.FileName, 0, true, 5,
"", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,
0, true);
Excel.Sheets sheets = theWorkbook.Worksheets;
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
for (int i = 1; i <= 10; i++)
{
Excel.Range range = worksheet.get_Range("A"+i.ToString(), "J" + i.ToString());
System.Array myvalues = (System.Array)range.Cells.Value;
string[] strArray = ConvertToStringArray(myvalues);
}
}
string[] ConvertToStringArray(System.Array values)
{
// create a new string array
string[] theArray = new string[values.Length];
// loop through the 2-D System.Array and populate the 1-D String Array
for (int i = 1; i <= values.Length; i++)
{
if (values.GetValue(1, i) == null)
theArray[i-1] = "";
else
theArray[i-1] = (string)values.GetValue(1, i).ToString();
}
return theArray;
}
4 Comments
Sathish
but if i have a fixed range already i am passing from cell and two cell to a function say fromcell = A3 and Tocell=A8 and i need to have the values from A3 to A8
Phani Kumar PV
then u need to remove the for loop and give the range directly in the code somthing as follows Excel.Range range = worksheet.get_Range("E2", "E16");
Sathish
what is the namespace i should use to get ConvertToStringArray
user3439632
ConvertToStringArray is a local method and is not a library method. So, you will have to include it in your code and then reference it using whatever namespace you place it in (presumably you will have it be a private static method in whatever class you are reading your excel file in)
Another Alternative approach. Positng as a spearate answer because it wil be give less space for confusion.
Excel.Application app = new Excel.Application();
Excel.Workbook wbook = null;
Excel.Worksheet wsheet = null;
Excel.Range range = null;
app.Visible = false;
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
string filepath = inputFile1.Value.ToString();
if (filepath != "")
{
wbook = app.Workbooks.Open(filepath, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
string currentSheet = "Sheet1";
wsheet = (Excel.Worksheet)wbook.Worksheets.get_Item(currentSheet);
range = wsheet.get_Range("B6", "H20");
System.Array myvalues = (System.Array)range.Cells.Value2;
valueArray = ConvertToStringArray(myvalues);
if (app != null)
{
app.Workbooks.Close();
app.Quit();
}
app = null;
wsheet = null;
range = null;
string[] ConvertToStringArray(System.Array values)
{
// create a new string array
string[] theArray = new string[values.Length];
// loop through the 2-D System.Array and populate the 1-D String Array
for (int i = 1; i <= values.Length; i++)
{
if (values.GetValue(1, i) == null)
theArray[i-1] = "";
else
theArray[i-1] = (string)values.GetValue(1, i).ToString();
}
return theArray;
}