How to extract data from excel sheet & by using same data create c# project. This all should happen programmatically.

Answer 1

There a 2 methods
via a sqlconnection
http://www.c-sharpcorner.com/article/accessing-excel-spreadsheet-in-C-Sharp/
Using the following connectionstring
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MySpreadsheet.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""

or via the excel object model

http://csharp.net-informations.com/excel/csharp-read-excel.htm

xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(@"d:\csharp-Excel.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

I prefer the sql connection because it seems to be less dependent on the version of excel that has been used

Answer 2

No thanks. I prefer to stay in the open anonymous world. We need well formed questions like these in the sololearn area.

Answer 3

Please use some more debug information.
I had to change the code a bit, because I was working in winforms.
I added rowtext to be able to view the rowtext
I added cell3text to be able to view the text of the cell which was evaluated.

int rowCount = myRange.Rows.Count;
int colCount = myRange.Columns.Count;
int n = 1; //n is equals to 1st row's value
int k = 7, l = 3;
string rowText = ""; //used a temporary variable that holds the text of the row
string cell3Text = "";
for (int i = 1; i <= rowCount; i++) //Started reading all rows
{
rowText = ""; //initialize rowText every time a new row is read
if (Convert.ToString(myWorksheet.Cells[i,1].Value2) == Convert.ToString(n))
//When a value is equals to "1", then it means first test case found & needs to start reading this row
{//row = i, col=j
for (int j = 3; j <= colCount; j++)//Started reading from 3rd Column, and row is where "1" found in first column
{
rowText = rowText + " " + myWorksheet.Cells[i, j].Value2;
cell3Text = Convert.ToString(myWorksheet.Cells[i, j].Value2);
MessageBox.Show("Cell text : " + cell3Text);
if (Convert.ToString(myWorksheet.Cells[i, j].Value2) == Convert.ToString("x")) //In the same row if "x" found, then we need to display the value of inputs in corresponding cells
{
MessageBox.Show("Column 7 text " + Convert.ToString(myWorksheet.Cells[i, 7].Value2));
}
}
n++;
k++;
l++;
}
MessageBox.Show(rowText);
//Console.ReadLine();
}

Answer 4

Please do not ask me again.

I prefer to stay in the open anonymous world. We need well formed questions like these in the sololearn area.

Answer 5

I don't want SQL connection. And want to read first column. Suppose, there are numbers 1...n in that column. After reaching at 1, I want to read that row. Then again 2 at first column, and that row. Can you write a method or algorithm for this?

Answer 6

Microsoft.Office.Interop.Excel.Application myApplication = new Microsoft.Office.Interop.Excel.Application();
myApplication.Visible = false;
Microsoft.Office.Interop.Excel.Workbook myWorkbook = myApplication.Workbooks.Open("C:\\Temp\\Example_Rows_Colums.xlsx");
Microsoft.Office.Interop.Excel.Worksheet myWorksheet = myWorkbook.Sheets["Blad1"] as Microsoft.Office.Interop.Excel.Worksheet;
Microsoft.Office.Interop.Excel.Range myRange;

for (int row = 1; row < myWorksheet.UsedRange.Rows.Count+1; row++)
{
for (int col = 1; col < myWorksheet.UsedRange.Columns.Count+1; col++)
{
myRange = (Range)myWorksheet.Cells[row, col];
if (myRange.Value2.ToString() != String.Empty)
{
MessageBox.Show(String.Format(myRange.Value2.ToString() + " "));
}
}

}

https://stackoverflow.com/questions/25833425/read-all-rows-and-columns-using-microsoft-office-interop-excel

Answer 7

Suppose if I want to read only first column then what modify in this code?

Answer 8

Actually I want to search 1 in first column then want to start reading first row?

Answer 9

Yes you can. Comment out the second for loop and replace "col" with 1

Answer 10

Hi, Pl see my below code. I am able to extract first columns values. Now while "1" is coming in the first column, then I want to start reading first row. But I am not able to compare first columns value's by the == operator.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
using _Excel = Microsoft.Office.Interop.Excel;

namespace ExcelRead
{

class Excel
{

static void Main(string[] args)
{
string pathExcel;
int sheetExcel;
Console.WriteLine("Enter the location of file:");
pathExcel = Console.ReadLine();
Console.WriteLine("Enter the sheet no:");
sheetExcel = Convert.ToInt32(Console.ReadLine());
_Application excel = new _Excel.Application();
Workbook wb;
Worksheet ws;
wb = excel.Workbooks.Open(pathExcel);
ws = excel.Worksheets[sheetExcel];
_Excel.Range xlRange = ws.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 1; i <= rowCount; i++)
{

if (ws.Cells[i, 1].Value2 != null)
{
if (ws.Cells[i, 1].Value2 == 1)
Console.WriteLine("Yes");
}
}
Console.ReadLine();
}

}
}

Answer 11

You are doing good. value2 is a object so it cannot be compared with 1. You need to cast value2 to integer with (int), convertto.int, or parse. Make sure you are reading the correct values using tostring () to see the text if the cell. (currently not near a coding environment so i cannot make example code)

Answer 12

namespace ExcelRead
{

class Excel
{

static void Main(string[] args)
{
string pathExcel;
int sheetExcel;
Console.WriteLine("Enter the location of file:");
pathExcel = Console.ReadLine();
Console.WriteLine("Enter the sheet no:");
sheetExcel = Convert.ToInt32(Console.ReadLine());
_Application excel = new _Excel.Application();
Workbook wb;
Worksheet ws;
wb = excel.Workbooks.Open(pathExcel);
ws = excel.Worksheets[sheetExcel];
_Excel.Range xlRange = ws.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
int n = 1; //n is equals to 1st row's value
int k = 7, l = 3;
for (int i = 1; i <= rowCount; i++) //Started reading all rows
{
if (Convert.ToString(ws.Cells[i, 1].Value2) == Convert.ToString(n)) //When a value is equals to "1", then it means first test case found & needs to start reading this row
{
for (int j = 3; j <= colCount; j++)//Started reading from 3rd Column, and row is where "1" found in first column
{
if (Convert.ToString(ws.Cells[i, j].Value2) == Convert.ToString("x")) //In the same row if "x" found, then we need to display the value of inputs in corresponding cells
{
//Console.WriteLine(Convert.ToString(ws.Cells[7,j].Value2));
}
}
n++;
k++;
l++;
}
}
Console.ReadLine();
}

}
}

Please see my code. When I am getting 'X' then I have to take values of 7th row, & column no is same as X's column. If you can provide your email id , I'll send the excel sheet.

Answer 13

You did a really good job.

Console.WriteLine(Convert.ToString(ws.Cells[7,j].Value2));
should be
Console.WriteLine(Convert.ToString(myWorksheet.Cells[i, 7].Value2));

i stands for row index
j stands for column index which in this case is fixed to 7

cellvalues index have the following strucure
cells[rowindex, columnindex]

please use more descriptive variable names, i,j,k,l are confusing.
(I do it myself, every time I do it, i get confused)

Answer 14

Hey Sneeze! Can i get your personal email id. Want to discuss something on this topic. my is [e-mail]

Answer 15

How to transfer console output of excel to text file?

Answer 16

I do not understand you question. Do you want to create a excel file ?

Answer 17

Hi...I finally did this. But getting once more problem now. How to identify ranges of merged cells in excel using c#