Excel sheets

Question

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

Vivek Bansod · 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.

Rajesh Singh · Answer

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

Sandy Surwase · Answer

check this link for custom formula...and some new things http://www.spreadsheetgear.com/support/samples/calcengine.aspx

Munesh Sharma · Answer

http://dotnet-munesh.blogspot.in/2014/05/how-to-import-excel-file-to.html

Lisa White · Answer

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

Lili R · Answer

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

Lili R · Answer

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 programmingi.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

Lili R · Answer

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 Bansod · Answer

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 R · Answer

please anyone.. i have stuck with this whole night but could find a proper solution. Ireally need help.

Lili R · Answer

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 { 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) { } } } }

Rajesh Singh · Answer

Thanks. Sure

Lili R · Answer

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 Bansod · Answer

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

Lili R · Answer

I need more info.. this is not helping.. If anybody know how to work on excel sheets in c#

Lili R · Answer

Hi Yes i am using C#.

Rajesh Singh · Answer

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