C# 读取Excel数据到datagridview中

[px880817]

要做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;
}

[聖傑]

参考这个

[px880817]

引用 1 楼 ysj1163620987 的回复:

参考这个

你引用的这个链接里面什么都没有,你到底是想要我参考什么?

[聖傑]

引用 2 楼 px880817 的回复:

引用 1 楼 ysj1163620987 的回复:参考这个 你引用的这个链接里面什么都没有,你到底是想要我参考什么?

怎么会什么也没有

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("失败！");  
                    }  
  
                }  
            }  

[vssvss]

ws.UsedRange.Cells.get_Range(cellStr, miss) 这里获取对象有没有成功呀

[px880817]

引用 4 楼 vssvss 的回复:

ws.UsedRange.Cells.get_Range(cellStr, miss) 这里获取对象有没有成功呀

那这里是需要怎么修改?

[px880817]

引用 3 楼 ysj1163620987 的回复://插入数据 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; } }

想问一下为什么要将datatable插入到数据库中?这样做能起到什么作用?能否直接传递datatable数据?

[realdja]

建议 百度搜索NPOI ，这个DLL比微软的JB东西好用，微软那东西兼容、IIS发布都有些问题，不符合他口味就要报错

[px880817]

引用 4 楼 vssvss 的回复:

ws.UsedRange.Cells.get_Range(cellStr, miss) 这里获取对象有没有成功呀

按照你的提示改了一个小地方就解决了,修改如下: for (int i = 0; i < 93; i++) { for (int j = 0; j < 4; j++) { if (ch <= 'D') { cellStr = ch.ToString() + (i + 1).ToString(); str[i, j] = ws.UsedRange.Cells.get_Range(cellStr, miss).Text.ToString(); } } }

[px880817]

结贴给分了

[熙风]

http://blog.csdn.net/happy09li/article/details/7431967