Using OleDB to read excel file in c#?
I am building a program to read excel file into dataGridView.
using System;
using System.Collections.Generic;
using System.ComponentModel;
usin开发者_开发问答g System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
namespace pro1._0
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
string sConnecStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:\\Copy_of_Acute_HCV_2008.xls" + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conObj = new OleDbConnection(sConnecStr);
conObj.Open();
OleDbCommand sqlCommand = new OleDbCommand("SELECT * FROM [Sheet1$]",conObj);
OleDbDataAdapter adaObj = new OleDbDataAdapter();
adaObj.SelectCommand = sqlCommand;
DataSet setObj = new DataSet();
adaObj.Fill(setObj);
conObj.Close();
dataGridView1.DataSource = setObj.Tables[0];
dataGridView1.Refresh();
}
}
}
The program runs fine when i use a small excel file but when i use a big excel file it gives me this error
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
Additional information: 'Sheet1$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
thanks
edit: i always use .xls files not .xlsx
protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
if ((txtFilePath.HasFile))
{
OleDbConnection conn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
string query = null;
string connString = "";
string strFileName = DateTime.Now.ToString("ddMMyyyy_HHmmss");
string strFileType = System.IO.Path.GetExtension(txtFilePath.FileName).ToString().ToLower();
if (strFileType == ".xls" || strFileType == ".xlsx")
{
txtFilePath.SaveAs(Server.MapPath("~/UploadedExcel/" + strFileName + strFileType));
}
string strNewPath = Server.MapPath("~/UploadedExcel/" + strFileName + strFileType);
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strNewPath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
conn = new OleDbConnection(connString);
if (conn.State == ConnectionState.Closed) conn.Open();
string SpreadSheetName = "";
DataTable ExcelSheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
SpreadSheetName = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
query = "SELECT * FROM [" + SpreadSheetName + "]";
cmd = new OleDbCommand(query, conn);
da = new OleDbDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds, "tab1");
}
}
}
精彩评论