C# 读取Excel数据到datagridview中
要做Excel文档读取到datagridview单元格中的程序,
1.开始的时候尝试将Excel文档数据直接读取到datagridview单元格中,发现最后datagridview中存在很多空白行,且没办法对空白行进行处理
2.后来我通过先将Excel数据读取到二维数组中,再由二维数组转换成datatable类型数据,最后将datatable赋值给datagridview空间的datasource来实现,但现在出现一个新的问题,老是出现报错提示"异常来自 HRESULT:0x800A03EC";另外说一句我装的是Office2010专业版_试用版
具体代码如下:
using Microsoft.Office.Core;
using MSExcel = Microsoft.Office.Interop.Excel;
using MSWord = Microsoft.Office.Interop.Word;
private string path;
private MSExcel.Application excelApp;
private MSExcel.Workbooks wbs = null;
private MSExcel.Sheets sheet = null;
private MSExcel.Worksheet ws = null;
private void 打开ToolStripMenuItem_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
DataRow dr;
string[,] str;
string[] strText=new string[4]{"宠物名称","天生技能1","天生技能2","天生技能3"};
OpenFileDialog open = new OpenFileDialog();
open.Title = "打开Excel文件";
open.Filter = "Excel文件(*.xlsx)|*.xlsx|所有文件(*.*)|*.*";
open.RestoreDirectory = true;
if (open.ShowDialog() == DialogResult.OK)
{
path = open.FileName;
str = readExcel(path);
for (int i = 0; i < strText.Length; i++)
{
dt.Columns.Add(strText[i].ToString());
}
for (int i = 0; i < str.GetLength(0); i++)
{
dr = dt.NewRow();
for (int j = 0; j < str.GetLength(1); j++)
{
dr[j] = str[i, j];
}
dt.Rows.Add(dr);
}
dataGridView1.DataSource = dt;
}
}
private string[,] readExcel(string path)
{
object miss = System.Reflection.Missing.Value;
excelApp.UserControl = true;
excelApp.DisplayAlerts = false;
excelApp.Application.Workbooks.Open(path, miss, miss, miss, miss,
miss, miss, miss, miss,
miss, miss, miss, miss,
miss, miss);
wbs = excelApp.Workbooks;
sheet = wbs[1].Worksheets;
ws = (MSExcel.Worksheet)sheet.get_Item(1);
int rowNum = ws.UsedRange.Cells.Rows.Count;
string[,] str = new string[rowNum, 4];
string cellStr = null;
char ch = 'A';
for (int i = 0; i < 93; i++)
{
for (int j = 0; j < 4; j++)
{
cellStr = ch.ToString() + (i + 1).ToString();
str[i,j] = ws.UsedRange.Cells.get_Range(cellStr, miss).Text.ToString();//这句出现报错"异常来自 HRESULT:0x800A03EC"
}
ch++;
}
return str;
}
[最优解释]
ws.UsedRange.Cells.get_Range(cellStr, miss) 这里获取对象有没有成功呀
[其他解释]
1 注意：上传控件上传的文件名，在IE浏览器中设置，必须是全路径 。设置方法（(IE浏览器)工具→Internet选项→安全→自定义级别→将文件上载到服务器时包含本地目录路径→启用即可。
）
//excel数据源 path为excel的路径
private DataTable GetDataFromExcelWithAppointSheetName(string path)
{
//连接串
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
//返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null,null, null, "Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
OleDbDataAdapter myCommand = null;
DataTable dt = new DataTable();
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = "select * from [" + strTableNames[0] + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt = new DataTable();
myCommand.Fill(dt);
conn.Close();
conn.Dispose();
//this.GridView1.DataSource = dt;
//this.GridView1.DataBind();
return dt;
}
2 //插入数据 dt为获取的excel，dataname为数据库名字
protected bool InsertSQLServer(DataTable dt, string dataname)
{
string strCon = @"Server=.;Integrated Security=true;";//无数据库名连接
string strTest = "Users";//表的名字
try
{
SqlConnection con = new SqlConnection(strCon);//创建数据库
con.Open();
string strSQL = "IF NOT EXISTS(SELECT name FROM sys.databases WHERE name=N'" + dataname + "') CREATE DATABASE [" + dataname + "]";
SqlCommand command = new SqlCommand(strSQL, con);
command.ExecuteNonQuery();
//创建数据库表
strSQL = "USE[" + dataname + "] IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'[DBO].[" + strTest + "]') AND type in (N'U')) CREATE TABLE " + strTest + " (";
string strColumn = string.Empty;
for (int i = 0; i < dt.Columns.Count; i++)
{
strSQL += "[" + dt.Columns[i].ColumnName + "] [nvarchar](256),";
strColumn += dt.Columns[i].ColumnName + ",";
}
strSQL += " )";
SqlCommand newcom = new SqlCommand(strSQL, con);
newcom.ExecuteNonQuery();
//插入数据
strColumn = strColumn.Substring(0, strColumn.Length - 1);
for (int i = 0; i < dt.Rows.Count; i++)//将i=0改为i=1可以再第二行加中文注释
{
strSQL = "USE[" + dataname + "] Insert into " + strTest + " (" + strColumn + ") values (";
for (int k = 0; k < dt.Columns.Count; k++)
{
strSQL += "'" + dt.Rows[i][k].ToString() + "',";
}
strSQL = strSQL.Substring(0, strSQL.Length - 1);
strSQL += ")";
SqlCommand insertCom = new SqlCommand(strSQL, con);
insertCom.ExecuteNonQuery();
//加一个session["no"]=dt.Rows.Count.toString();可以知道导入几条（注意要减1）
}
return true;
}
catch
{
return false;
}
}
3 插入按钮单击事件代码：
string dataname = "ClManage";//数据库名字
if (FuloadExcelFile.FileName == "")//FuloadExcelFile是上传控件FileUpload
{
return;
}
else
{
string fileExt = System.IO.Path.GetExtension(FuloadExcelFile.FileName);
if (fileExt != ".xls")//必须是EXCEL文件
{
return;
}
else
{
string filepath = FuloadExcelFile.PostedFile.FileName;//文件路径，FuloadExcelFile为上传文件的控件
DataTable dt = new DataTable();
dt = GetDataFromExcelWithAppointSheetName(filepath);
if (InsertSQLServer(dt, dataname))//导入数据库
{
Response.Write("成功");
}
else
{
Response.Write("失败！");
}
}
}
[其他解释]
参考这个
[其他解释]
