Excel sheets

Lili R

I am stuck with excel sheets.

My question is: how can i extract data from excel sheets.. when there are many sheets in a excel file (.xls). And how can we use data from single rows or columns..

Please help..
Thank u

17 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vivek Bansod10y ago
Accepted answer
Hi,

When you create object of WorkSheet by name or index - you can loop through rows & columns in that sheet or if you need only specific row/columns from that sheet then you can pass hard coded value to like below -
excelWorksheet.Cells[row number, column number].Value = "value you want to set";
Before assigning it to cell - you need to manipulate it as your requirement. Then call Save method on workbook object.
If you can refer my blog link given above - will help you. I have read/write excel code in there.
Lili R10y ago
Hi Vivek

This was really help, previously i had no idea about excel sheet extraction and i was very confused. Now, i am using OLEDB connection string to get the excel file and then open and read the required sheet.

However, i am using this codes ideas
excelWorksheet.Cells[row number, column number].Value = "value you want to set";
for writing data in excel sheets.
Thanks
Sandy Surwase10y ago
check this link for custom formula...and some new things
http://www.spreadsheetgear.com/support/samples/calcengine.aspx
Lili R10y ago
Hi Munesh

I am able to import data from excel sheets, but what i am not able to do is apply formula on them and save the results in particular cells. I want to do this through programming,i.e, i want set some formulas in the program, so that on a button click all the sheets data in an excel file can be calculated at once. If you have any suggestions or idea on how to do that please suggest.

thanks
Munesh Sharma10y ago
http://dotnet-munesh.blogspot.in/2014/05/how-to-import-excel-file-to.html
Lili R10y ago
Hi Vivek

what i want to do is:

1. Read data form required excel sheets.
2. choose the particular rows and columns through programming and apply some formulas on it to get some values then put the values on the wanted cells
3. Then save all those values on the same excel sheets.

I hope it is possible to do.

I am able to read data from required excel sheets. But after that i am just stuck. Please Help!!
Vivek Bansod10y ago
Hi,

Could you please elaborate on your issues?

1. Are there compile time error or runtime error?
2. What errors you are getting?
3. Do you have issues while reading data from excel or saving data to excel?
Lili R10y ago
please anyone.. i have stuck with this whole night but could find a proper solution. Ireally need help.
Lili R10y ago
Hi Lisa and everyone. The previous ques i asked was to prepare this program.. but its showing problems, its showing values at some places and at some places its just doing nothing please if anyone can find me my mistake and a proper solution for it. Also, how can i save the values to required excelsheet.

Adding to this, i have prepared this code to take the choosen no of row and cell data at once and then do some required task on it. After that i want to save the evaluated values the same excel sheet, from where it was taken.

See the codes i prepared..

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Diagnostics;
using Microsoft.Office.Interop;
using Excel = Microsoft.Office.Interop;
using System.Data.OleDb;
namespace Check1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
try
{
OpenFileDialog opfd = new OpenFileDialog();
opfd.InitialDirectory = @"";
opfd.Title = "Select file";
opfd.CheckPathExists = false;
opfd.CheckFileExists = false;
opfd.Filter = "xls files (*.xls)|*.xls|All files (*.*)|*.*";
opfd.FilterIndex = 2;
if (opfd.ShowDialog() == DialogResult.OK)
{
textBox1.Text = opfd.FileName;
//string sSheetName, sConnection, dtTablesList, oleExcelCommand, oleExcelReader, oleExcelConnection;
OleDbConnection sConnection = new OleDbConnection();
sConnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox1.Text + @";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";//@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1""";
OleDbCommand command = new OleDbCommand
(
"Select *From [" + textBox2.Text + "$]", sConnection
);
//////////DataSet ds = new DataSet();
//////////OleDbDataAdapter adapter = new OleDbDataAdapter(command);
//////////adapter.Fill(ds);
//.......................................................
DataTable dt = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(dt);
int rowCount = dt.Rows.Count;
//textBox1.Text = rowCount.ToString();
//.........................................................
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xla.ActiveSheet;

for (int i = 5; i <= rowCount; i++)
{
dataGridView1.DataSource = dt;

string HzData4 = dt.Rows[i]["F2"].ToString();
double Hz4 = double.Parse(HzData4);
double Rc = "does task";
dt.Rows[i].SetField("F4", Rc);

//..................................
string sRi = dt.Rows[6]["F2"].ToString();//initial value
double Ri = double.Parse(sRi);
double RiValue = Rc - Ri;
dt.Rows[i].SetField("F5", RiValue);
double EApparant = GF * RiValue;
dt.Rows[i].SetField("F6", EApparant);

string sTi = dt.Rows[6]["F3"].ToString();
double Ti = double.Parse(sTi);
string sTc = dt.Rows[i]["F3"].ToString();
double Tc = double.Parse(sTc);
double TValue = Tc - Ti;
dt.Rows[i].SetField("Co-ordinates", TValue);
double KValue =12.0;
double xELoad = (EApparant + TValue);
double ELoad = xELoad * KValue;
dt.Rows[i].SetField("F8", ELoad);
double sKValue = 2.2;//Thermal Coefficient of Steel
double EActual = xELoad * sKValue;
dt.Rows[i].SetField("F9", EActual);
ws.Cells[i + 2, 4] = Rc.ToString();
ws.Cells[i + 2, 5] = RiValue.ToString();
ws.Cells[i + 2, 6] = EApparant.ToString();
ws.Cells[i + 2, 7] = TValue.ToString();
ws.Cells[i + 2, 8] = ELoad.ToString();
ws.Cells[i + 2, 9] = EActual.ToString();
//xla.Visible = true;
}
dataGridView1.DataSource = dt;
xla.Visible = true;
sConnection.Close();

}
}
catch (FormatException ef)
{
}
catch (IndexOutOfRangeException ie)
{
}
}
}
}
Lisa White10y ago
Hi Lili,

here is a sample for retrieving data from excel worksheets and extracting to a new excel file in C#. I'm not sure if this could help you, if not, please just let me know.

static void Main(string[] args)
{
Workbook newBook = new Workbook();
Worksheet newSheet = newBook.Worksheets[0];
Workbook workbook = new Workbook();
workbook.LoadFromFile(filename);
Worksheet sheet = workbook.Worksheets[0];
int i = 1;
int columnCount = sheet.Columns.Count();
foreach (CellRange range in sheet.Columns[0])
{
if (range.Text == "text")
{
CellRange sourceRange = sheet.Range[range.Row, 1, range.Row, columnCount]; CellRange destRange = newSheet.Range[i, 1, i, columnCount];
sheet.Copy(sourceRange, destRange, true);
i++;
}
}
newBook.SaveToFile("NewForm.xlsx", ExcelVersion.Version2010);
}
besides, in this sample, I used a free excel control, if you do not mind, check here.

https://visualstudiogallery.msdn.microsoft.com/81c33294-eee9-4683-9cee-a5714cfebf34
Rajesh Singh10y ago
Thanks. Sure
Lili R10y ago
Thank u.. Rajesh and Vivek.. these are great suggestions.. i am working on it.. i will post my solution as soon as i get success with my queries.. meanwhile if u guys get any other solution for me.. please suggest.

Thanks
Vivek Bansod10y ago
Hey,

if I understand your correctly, your issue is only with multiple sheets in excel then you need create initialize WorkSheet object by providing sheet name or sheet index whiel initialization which will give that particular excel. Then you get used range from that work sheet and loop through each row/columns.

You can see sample code in my blog post -
http://vivekbansod.blogspot.in/2013/07/how-to-bulk-upload-test-case-results-to.html

Let us know if it didn't help
Rajesh Singh10y ago
Please refer this link for code.

http://csharp.net-informations.com/excel/csharp-read-excel.htm
http://stackoverflow.com/questions/657131/how-to-read-data-of-an-excel-file-using-c
http://codereview.stackexchange.com/questions/47227/reading-data-from-excel-sheet-with-exceldatareader
Lili R10y ago
I need more info.. this is not helping..

If anybody know how to work on excel sheets in c#
Lili R10y ago
Hi
Yes i am using C#.
Rajesh Singh10y ago
Hi
Which programming language you are using for extract data from excel?

If you are using C# then refer this link.

http://stackoverflow.com/questions/18993735/how-to-read-single-excel-cell-value
http://csharphelper.com/blog/2014/11/read-data-from-an-excel-workbook-in-c