I have a excel file.I want to read the file using c#.
Excel file contains following.
- Name subject Marks
- A maths 35
- B maths 50
- C maths 69
- ------------Empty row-----------------
- D maths 36
i want my o/p like below.
- Name subject Marks
- A maths 35
- B maths 50
- C maths 69
- D maths 36
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using Excel = Microsoft.Office.Interop.Excel;
- namespace excel
- {
- class Program
- {
- static void Main(string[] args)
- {
- Excel.Application xlApp = new Excel.Application();
- Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\dinesh\Sample.xlsx");
- Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1]; // assume it is the first sheet
- Excel.Range xlRange = xlWorksheet.UsedRange;
- xlWorkbook.Worksheets.Add(xlWorksheet);
- }
- }
- }
Rafnas T PPosted Jan 25, 2017, 6:40 AM
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"E:\Book1.xlsx");
Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1]; // assume it is the first sheet
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
//int colCount = 3;
DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("subject", typeof(string));
dt.Columns.Add("Marks", typeof(string));
for (int i = 2; i <= rowCount; i++)
{
cell1 = ""; cell2 = ""; cell3 = "";
//for (int j = 1; j <= colCount; j++)
//{
if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null)
cell1 = xlRange.Cells[i, 1].Value2.ToString();
if (xlRange.Cells[i, 2] != null && xlRange.Cells[i, 2].Value2 != null)
cell2 = xlRange.Cells[i, 2].Value2.ToString();
if (xlRange.Cells[i, 3] != null && xlRange.Cells[i, 3].Value2 != null)
cell3 = xlRange.Cells[i, 3].Value2.ToString();
//here you will get cell value per iteration , from here you can add to datatable
//}
if(cell1 != "" && cell2 != "" && cell3 != "")
dt.Rows.Add(cell1, cell2, cell3);
}
dt.AcceptChanges();
xlWorksheet.Cells.Clear();
for (var i = 0; i < dt.Columns.Count; i++)
{
xlWorksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}
// rows
for (var i = 0; i < dt.Rows.Count; i++)
{
for (var j = 0; j < dt.Columns.Count; j++)
{
xlWorksheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
}
}
xlWorksheet.SaveAs(@"E:\Book1.xlsx");
GC.Collect();
GC.WaitForPendingFinalizers();
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Chandan RajbharPosted Feb 11, 2017, 1:47 AM
Sandip G PatilPosted Feb 7, 2017, 2:35 AM
Toti BirdPosted Jan 25, 2017, 3:54 PM
Dinesh SanthalingamPosted Jan 25, 2017, 7:35 AM
Rafnas T P
Rafnas T PPosted Jan 25, 2017, 6:26 AM
Dinesh SanthalingamPosted Jan 25, 2017, 5:37 AM
Rafnas T PPosted Jan 25, 2017, 5:25 AM
Dinesh SanthalingamPosted Jan 25, 2017, 4:59 AM
Rafnas T PPosted Jan 25, 2017, 4:56 AM
Rafnas T PPosted Jan 25, 2017, 4:44 AM
Rafnas T PPosted Jan 25, 2017, 4:43 AM
Dinesh SanthalingamPosted Jan 25, 2017, 4:40 AM
Rafnas T P
Rafnas T PPosted Jan 25, 2017, 4:36 AM
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"E:\Book1.xlsx");
Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1]; // assume it is the first sheet
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
//int colCount = 3;
DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("subject", typeof(string));
dt.Columns.Add("Marks", typeof(string));
for (int i = 2; i <= rowCount; i++)
{
cell1 = ""; cell2 = ""; cell3 = "";
//for (int j = 1; j <= colCount; j++)
//{
if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null)
cell1 = xlRange.Cells[i, 1].Value2.ToString();
if (xlRange.Cells[i, 2] != null && xlRange.Cells[i, 2].Value2 != null)
cell2 = xlRange.Cells[i, 2].Value2.ToString();
if (xlRange.Cells[i, 3] != null && xlRange.Cells[i, 3].Value2 != null)
cell3 = xlRange.Cells[i, 3].Value2.ToString();
//here you will get cell value per iteration , from here you can add to datatable
//}
if(cell1 != "" && cell2 != "" && cell3 != "")
dt.Rows.Add(cell1, cell2, cell3);
}
dt.AcceptChanges();
GC.Collect();
GC.WaitForPendingFinalizers();
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);
//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
Dinesh SanthalingamPosted Jan 24, 2017, 7:36 AM
Rafnas T PPosted Jan 24, 2017, 7:26 AM
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\dinesh\Sample.xlsx");
Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1]; // assume it is the first sheet
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 0; i <= rowCount; i++)
{
for (int j = 0; j <= colCount; j++)
{
string cell= (string)(xlRange.Cells[i, j] as Excel.Range).Value2;
}
}
Dinesh SanthalingamPosted Jan 24, 2017, 7:13 AM
Rafnas T PPosted Jan 24, 2017, 7:07 AM
Dinesh SanthalingamPosted Jan 24, 2017, 6:15 AM
Can you explain it without using OlEDb? @
Rafnas T PPosted Jan 24, 2017, 4:59 AM
string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", "C:\dinesh\Sample.xlsx");
Econ = new OleDbConnection(constr);
string Query = string.Format("Select [Name ],[subject ],[Marks] FROM [{0}]", "Sheet1$");
OleDbCommand Ecom = new OleDbCommand(Query, Econ);
Econ.Open();
DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
Econ.Close();
oda.Fill(ds);
DataTable Exceldt = ds.Tables[0];
for (int i = Exceldt.Rows.Count - 1; i >= 0; i--)
{
if (Exceldt.Rows[i][" Name "] == DBNull.Value || Exceldt.Rows[i]["subject "] == DBNull.Value || Exceldt.Rows[i]["Marks"] == DBNull.Value)
{
Exceldt.Rows[i].Delete();
}
}
Exceldt.AcceptChanges();