Loading
How do you read an Excel sheet in C#?
3 Answers
if (FlUpload1.HasFile == true)
{
string str = "";
try
{
lblMsg.Text = "";
lblCount.Text = "";
lblError.Text = "";
grdShow.DataSource = null;
grdShow.DataBind();
DataTable ds = new DataTable();
if (FlUpload1.HasFile == true && new clsExtChk().CheckExt(FlUpload1.PostedFile.FileName, FlUpload1.PostedFile.ContentLength))
{
str = Server.MapPath(Path.GetFileName(FlUpload1.PostedFile.FileName));
FlUpload1.PostedFile.SaveAs(str);
string path = System.IO.Path.GetFullPath(str);
}
int flag = 0;
if (Path.GetExtension(str) == ".xls")
{
oledbConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + str + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"");
flag = 1;
}
else if (Path.GetExtension(str) == ".xlsx")
{
oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + str + ";Extended Properties='Excel 8.0;HDR=YES';");
flag = 1;
}
if (flag == 1)
{
oledbConn.Open();
string SpreadSheetName = "";
string query = null;
DataTable ExcelSheets = oledbConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
query = "SELECT UID, Title, First_Name, Last_Name,Organisation,Designation,Address1,Address2,Address3,City,PIN,District,State,Country,WorkPhone,HomePhone,Fax,Email,Website,OrgType,NatureofBusiness,DomainSpecialization,Offerings,StartofSubscription,MagazineName,SubsID,SubscriptionType,dead_alive,ConferenceName,ConferenceYear,Delegate_Type,delegateNumber,RefID,RegistrationDate,Remark FROM [" + SpreadSheetName + "]"; //,StartofSubscription,Magazine,SubsID,SubscriptionType,dead_alive,ConferenceName,ConferenceYear,Delegate_Type,delegateNumber,RefID,RegistrationDate,
OleDbDataAdapter oleda = new OleDbDataAdapter(query, oledbConn);
oleda.Fill(ds);
oledbConn.Close();
lblMsg.Text = ds.Rows.Count.ToString();
if (ds.Rows.Count > 0)
{
grdShow.DataSource = ds;
grdShow.DataBind();
for (int i = 0; i <= grdShow.Rows.Count - 1; i++)
{
if (grdShow.Rows[i].Cells[4].Text.Trim().Replace(" ", "") == "" || grdShow.Rows[i].Cells[13].Text.Trim().Replace(" ", "") == "")
{
grdShow.Rows[i].ForeColor = System.Drawing.Color.Red;
}
else if (grdShow.Rows[i].Cells[2].Text.Trim().Replace(" ", "") == "" && grdShow.Rows[i].Cells[3].Text.Trim().Replace(" ", "") == "")
{
grdShow.Rows[i].ForeColor = System.Drawing.Color.Red;
}
else if (grdShow.Rows[i].Cells[0].Text.Trim().Replace(" ", "") == "" && grdShow.Rows[i].Cells[17].Text.Trim().Replace(" ", "") == "")
{
grdShow.Rows[i].ForeColor = System.Drawing.Color.Red;
}
else if (grdShow.Rows[i].Cells[0].Text.Trim().Replace(" ", "") == "" && grdShow.Rows[i].Cells[17].Text.Trim().Replace(" ", "") != "")
{
grdShow.Rows[i].Cells[0].Text = grdShow.Rows[i].Cells[17].Text;
}
}
}
else
{
lblMsg.Text = "No Record Found";
}
File.Delete(str);
oleda.Dispose();
ds.Dispose();
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Please select Excel file');", true);
}
}
catch (Exception ex)
{
oledbConn.Close();
File.Delete(str);
lblMsg.Text = ex.Message;
}
finally
{
}
}
else
{
ScriptManager.RegisterStartupScript(this, this.GetType(), "Message", "alert('Please select file');", true);
}
Anil YadavPosted Apr 28, 2015, 7:56 AM
Write to Excel file from your application:
using System;
using System.Collections.Generic;
using System.Text;
using Bytescout.Spreadsheet;
using System.Diagnostics;
using System.IO;
namespace HelloWorld
{
class Program
{
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
// add new worksheet
Worksheet Sheet = document.Workbook.Worksheets.Add("FormulaDemo");
// headers to indicate purpose of the column
Sheet.Cell("A1").Value = "Formula (as text)";
// set A column width
Sheet.Columns[0].Width = 250;
Sheet.Cell("B1").Value = "Formula (calculated)";
// set B column width
Sheet.Columns[1].Width = 250;
// write formula as text
Sheet.Cell("A2").Value = "7*3+2";
// write formula as formula
Sheet.Cell("B2").Value = "=7*3+2";
// delete output file if exists already
if (File.Exists("Output.xls")){
File.Delete("Output.xls");
}
// Save document
document.SaveAs("Output.xls");
// Close Spreadsheet
document.Close();
// open generated XLS document in default program
Process.Start("Output.xls");
}
}
}
-----------------------------------------------------------------------------------------------------------------------------------
Read from Excel from your application:
using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using Bytescout.Spreadsheet;
using System.IO;
namespace HelloWorld
{
class Program
{
static void Main(string[] args)
{
// Create new Spreadsheet
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("Sample.xls");
// Get worksheet by name
Worksheet worksheet = document.Workbook.Worksheets.ByName("Sheet1");
// Check dates
for (int i = 0; i < 4; i++)
{
// Set current cell
Cell currentCell = worksheet.Cell(i, 0);
DateTime date = currentCell.ValueAsDateTime;
// Write Date
Console.WriteLine("{0}", date.ToShortDateString());
}
// Close document
document.Close();
// Write message
Console.Write("Press any key to continue...");
// Wait user input
Console.ReadKey();
}
}
}
Kishan TanpurePosted Jul 29, 2014, 7:10 AM
With the release of .Net 4, Microsoft has made Excel Interop incredibly simple and more intuitive. While this makes using Excel files incredibly easy, it does require that the computer that is running this code has Excel installed (which may be a problem for some people). Here is what you will need to get started:
Visual Studio 2010 (Express Edition works great, that is what I use)
Excel installed on your computer
.Net 4.0 installed
To get started, create a new project in Visual Studio (this tutorial assumes you are using WinForm project). After you do that you will need to add a reference to the Excel Object Library in the COM tab (it should be called Microsoft Excel 12.0 Object Library if you have Excel 2007 installed, if you don't then the 12 may be lower). Once you have that reference set we are set to go.
We want this to be easy so we will need to add the following to the top of your file
using Excel = Microsoft.Office.Interop.Excel;
We add this because it now allows us to access the Excel libraries easily and it makes it clear what the code is for. Once we have that code, we want to create the OnLoad event and that is what we will use to handle the Excel data. Here is the code to create an Excel App in C#:
Excel.Application xlApp = new Excel.Application();
Now remember the line we appended to the top of our file let us access the Interop library by using Excel.xxx, this is where it comes in handy! Well we created an Excel App so what? What can we do with it? Nothing right now, but we with a few lines of code we will be able to start reading data!
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:/C.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
We have now created an Excel application, a workbook, a worksheet and then selected the range that was used and also created two integers that contain the count of Columns and Rows. Using a for loop we can loop over the entire data set and get each value as follows:
for(int i=1; i <= rowCount; i++)
{
for(int j=1; j<=colCount; j++)
{
MessageBox.Show(xlRange.Cells[i,j].Value2.ToString());
}
}
It is important to make sure you use Value2 or else you will get a string that just says "Com Object" which is not what you want!!! Also, it is extremely important to make sure that the i and j start equal to 1 as there is no row/col 0 so your program will not run. Now, with the excel spreadsheet I included with this tutorial (and was taken from Yahoo Finance, thanks to them!) running this will take a very, very long time (there are about 8,000 rows in the file) and because we do not have it on any other thread, the UI will become non-responsive. The next tutorial in this series will show us how we can fix that to load large Excel files into our program while maintaining a responsive UI.
Here is the full code:
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 Excel = Microsoft.Office.Interop.Excel;
namespace ExcelTut
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"D:/C.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
for (int i = 1; i <= rowCount; i++)
{
for (int j = 1; j <= colCount; j++)
{
MessageBox.Show(xlRange.Cells[i, j].Value2.ToString());
}
}
}
}
}
Kishan TanpurePosted Jul 29, 2014, 7:09 AM