Excel导入DataGridView 字符串不能导入求解决办法
using System;using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data;
namespace statisticalTools
{
class EcxelToDataGridView
{
/// <summary>
/// Excel数据导入方法
/// 作者:zxl
/// </summary>
/// <param name="filePath"></param>
/// <param name="dgv"></param>
public void ecxelToDataGridView(string filePath, DataGridView dgv)
{
try{
//根据路径打开一个Excel文件并将数据填充到DataSet中
// string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据,并且将数字和字符混合的单元格视为文本进行导入
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=YES;IMEX=0'";//导入时包含Excel中的第一行数据,并且将数字和字符混合的单元格视为文本进行导入
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
//根据DataGridView的列构造一个新的DataTable
DataTable tb = new DataTable();
//add top table name
DataColumn column;
//DataRow row;
//日期
column = new DataColumn();
column.ColumnName = "车牌号";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//省内/省外
column = new DataColumn();
column.ColumnName = "省内/外";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//地区
column = new DataColumn();
column.ColumnName = "地区";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//县
column = new DataColumn();
column.ColumnName = "县";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//矿名
column = new DataColumn();
column.ColumnName = "矿名";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//名品
column = new DataColumn();
column.ColumnName = "名品";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//浮沉
column = new DataColumn();
column.ColumnName = "浮沉";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//浮后结果
column = new DataColumn();
column.ColumnName = "浮后结果";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//灰分
column = new DataColumn();
column.ColumnName = "灰分";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//挥发分
column = new DataColumn();
column.ColumnName = "挥发分";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//硫
column = new DataColumn();
column.ColumnName = "硫";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//水份
column = new DataColumn();
column.ColumnName = "水份";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//特征
column = new DataColumn();
column.ColumnName = "特征";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//G
column = new DataColumn();
column.ColumnName = "G";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//Y
column = new DataColumn();
column.ColumnName = "Y";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//X
column = new DataColumn();
column.ColumnName = "X";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
//备注
column = new DataColumn();
column.ColumnName = "备注";
column.AllowDBNull = true;
column.DefaultValue = " ";
column.MaxLength = 100;
tb.Columns.Add(column);
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
//if ( dgvc.CellType != typeof(DataGridViewCheckBoxCell))
if (dgvc.Visible && dgvc.CellType != typeof(DataGridViewCheckBoxCell))
{
DataColumn dc = new DataColumn();
dc.ColumnName = dgvc.DataPropertyName;
//dc.ColumnName = "ccccccccc";
//dc.DataType = dgvc.ValueType;//若需要限制导入时的数据类型则取消注释,前提是DataGridView必须先绑定一个数据源那怕是空的DataTable
try
{
tb.Columns.Add(dc);
}catch(Exception ex){
Console.WriteLine(ex);
}
}
}
//根据Excel的行逐一对上面构造的DataTable的列进行赋值
foreach (DataRow excelRow in ds.Tables[0].Rows)
{
int i = 0;
DataRow dr = tb.NewRow();
foreach (DataColumn dc in tb.Columns)
{
dc.DefaultValue = false;
dr[dc] = excelRow[i];
i++;
}
tb.Rows.Add(dr);
}
//在DataGridView中显示导入的数据
dgv.DataSource = tb;
}catch(Exception ex){
Console.WriteLine(ex);
}
}
}
}
求各位技术大神指导,开发的一个下东西上用到,百度上查也没见到解决办法,倒是好像有这样的说法
1.DataGridView 不能导入字符串,如果导入字符串需要封包?还是怎么地
2.查了好几天没见到这方面的解决办,如果有解决办法希望能和大家分享 excel c# DataGridView --------------------编程问答-------------------- 自己给自己顶个,是用来统计用的!所以是个小程序!
--------------------编程问答-------------------- 检查下绑定表的字段类型和数量是否匹配 --------------------编程问答--------------------
难道就这原因!貌似没人出现过这样的问题?
求解 --------------------编程问答-------------------- 求代码,求解决!持续关注 --------------------编程问答-------------------- 最终解决代码!希望能帮助大家!
//Excel导入datagridview中文能显示
///
this.OpenFileDlg.Title = "打开Excel表格";
this.OpenFileDlg.Filter = "Excel文件 (*.xls)|*.xls";
this.OpenFileDlg.ShowDialog();
string filename;
filename = this.OpenFileDlg.FileName;
///
object missingValue = System.Reflection.Missing.Value;
////
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();///
Microsoft.Office.Interop.Excel.Workbook myWorkBook;
//
excelApp.Workbooks.Open(filename, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
myWorkBook = excelApp.Workbooks.get_Item(1);
Microsoft.Office.Interop.Excel.Worksheet newSheet; newSheet = (Microsoft.Office.Interop.Excel.Worksheet)myWorkBook.Sheets[1];
dataGridView1.Columns.Clear();
int m = 1, n = 1;
Microsoft.Office.Interop.Excel.Range rng1;
while ((rng1 = (Microsoft.Office.Interop.Excel.Range)newSheet.Cells[1, m]).Value2 != null)
{
dataGridView1.Columns.Add("", m.ToString());
m++;
}
m--;
while ((rng1 = (Microsoft.Office.Interop.Excel.Range)newSheet.Cells[n, 1]).Value2 != null)
{
dataGridView1.Rows.Add();
n++;
}
n--;
for (int i = 0; i < n; i++)
{ for (int j = 0; j < m; j++)
{
rng1 = (Microsoft.Office.Interop.Excel.Range)newSheet.Cells[i + 1, j + 1];
dataGridView1.Rows[i].Cells[j].Value = rng1.Value2;
}
}
excelApp.Quit();
补充:.NET技术 , C#