参考一下 using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb; using System.Data.SqlClient; using System.IO; using Microsoft.Office.Interop.Excel; namespace TestAccess { class Program { static void Main(string[] args) {
string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;"; strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;"; strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";"; OleDbConnection objConnection = new OleDbConnection(strConnection); objConnection.Open(); OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection); DataSet ds = new DataSet(); myCommandd.Fill(ds, "[Sheet1$]"); System.Data.DataTable dt = ds.Tables["[Sheet1$]"]; Console.WriteLine(dt.Columns[0].ToString()); Console.WriteLine(dt.Columns[1].ToString()); DataRow drDisplay = dt.Rows[0]; int[] num = new int[dt.Columns.Count]; for (int j = 0; ; ) { for (int i = 0; i < dt.Columns.Count; i++) {
if (drDisplay[i] is DBNull) ; else num[i] += Convert.ToInt32(drDisplay[i]);
} if (++j >= dt.Rows.Count) break; drDisplay = dt.Rows[j]; } objConnection.Close(); object MissingValue = Type.Missing; Microsoft.Office.Interop.Excel.Application app = new Application(); Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(@"C:\Documents and Settings\v-changl\My Documents\couse.xlsx", MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue); Microsoft.Office.Interop.Excel.Worksheet wsheet = wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; for (int i = 0; i < dt.Columns.Count; i++) { //注意下面是i+1,,excel小标默认从1开始 wsheet.Cells[dt.Rows.Count + 2, i + 1] = num[i].ToString(); }
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace TestAccess
{
class Program
{
static void Main(string[] args)
{
string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";
strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;";
strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
OleDbConnection objConnection = new OleDbConnection(strConnection);
objConnection.Open();
OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection);
DataSet ds = new DataSet();
myCommandd.Fill(ds, "[Sheet1$]");
System.Data.DataTable dt = ds.Tables["[Sheet1$]"];
Console.WriteLine(dt.Columns[0].ToString());
Console.WriteLine(dt.Columns[1].ToString());
DataRow drDisplay = dt.Rows[0];
int[] num = new int[dt.Columns.Count];
for (int j = 0; ; )
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (drDisplay[i] is DBNull) ;
else
num[i] += Convert.ToInt32(drDisplay[i]);
}
if (++j >= dt.Rows.Count) break;
drDisplay = dt.Rows[j];
}
objConnection.Close();
object MissingValue = Type.Missing;
Microsoft.Office.Interop.Excel.Application app = new Application();
Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(@"C:\Documents and Settings\v-changl\My Documents\couse.xlsx", MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue);
Microsoft.Office.Interop.Excel.Worksheet wsheet = wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
for (int i = 0; i < dt.Columns.Count; i++)
{
//注意下面是i+1,,excel小标默认从1开始
wsheet.Cells[dt.Rows.Count + 2, i + 1] = num[i].ToString();
}
wbook.Save();
wbook.Close(true, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wsheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
wsheet = null;
wbook = null;
app = null;
GC.Collect();
}
}
}
excel里直接设成文本格式经常无效
你遇到的这个问题目前还没有办法解决,这是OLEDB的缺陷问题。
不过有几个方法可以解决这个问题:
方法一:(不一定行,因为这是一个治标不治本的方法):
在任意一个空的单元格中输入数字1,然后复制它,再选择你要转换格式的列。然后在“编辑”菜单下面选择“选择性粘贴”选项;会出来一个小框,然后选择“乘”,确定。
方法二:
放弃这种方式访问Excel文件,使用.net组件中的 workbook,worksheet他们来访问;使用这种方式目前还没发现任何解决不了得问题!!!
方法三:
使用owc11 来访问,这种方式,目前我发现解决不了得就是,不能设置自动换行。呵呵!!
兄弟我能帮得就这么多,希望对你有用!!!!
并將這一列設置為文字類型
你取单元格的时候如果是全取的话,那是很慢的~~~
用这个属性 UsedRange
我在循环读取Excel的时候是这样的
dr[columnID - 1] = ((Excel.Range)excelWorkSheet.UsedRange.Cells[rowID,columnID]).Text.ToString();可是还是很慢的啊 ?
{
List<DealerModel.YeJi> deList = new List<DealerModel.YeJi>();
for (int r = 2; r <= sheet.UsedRange.Cells.Rows.Count; r++)
{
if (r == (sumCount/100))
{
add += 1;
System.Web.HttpContext.Current.Session["add"] = add;
}
DealerModel.YeJi de = new DealerModel.YeJi();
//for (int c = 1; c < sheet.UsedRange.Cells.Columns.Count; c++)
//{
de.YNumber = ((Excel.Range)sheet.Cells[r, 1]).Text.ToString().Trim();
de.Name = ((Excel.Range)sheet.Cells[r, 2]).Text.ToString().Trim();
if (de.YNumber.Trim().Length <= 0 && de.Name.Trim().Length <= 0)
{
error++;
if (error > 3)
{
break;
}
continue;
}
else
{
if (((Excel.Range)sheet.Cells[r, 3]).Text.ToString().Length > 0)
{
de.Pv = uint.Parse(((Excel.Range)sheet.Cells[r, 3]).Text.ToString());
}
de.Zmdm = ((Excel.Range)sheet.Cells[r, 4]).Text.ToString().Trim();
de.Msg = "";
deList.Add(de);
}
}
return deList;
}
return null;
看看这个可不可以解决你的问题
如果操作的话,用下
OleDbDataReader reader = excelCommand.ExecuteReader();
应该能取全数据
cn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + filename + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
对的 用oledb 取 解决不了的
int columnCount = getTotalColumnCount();
int rowCount = getTotalRowCount();
System.Data.DataTable dt = new System.Data.DataTable();
//设置datatable列的名称
for (int columnID = 1; columnID <= columnCount; columnID++)
{
dt.Columns.Add(((Excel.Range)excelWorkSheet.UsedRange.Cells[1, columnID]).Text.ToString());
}
for (int rowID = 2; rowID <= rowCount; rowID++)
{
DataRow dr = dt.NewRow();
for (int columnID = 1; columnID <= columnCount; columnID++)
{
dr[columnID - 1] = ((Excel.Range)excelWorkSheet.UsedRange.Cells[rowID, columnID]).Text.ToString();
}
dt.Rows.Add(dr);
}
return (dt); 好像和你的没什么大区别的啊,你的只是读取几个cell 我是读取每个cell 可效率也太低了 速度太慢了啊
读取Excel文件,影响速度的有两个东西需要控制一下!1. 只读取有数据的列。用UsedRange来限制一下!!!
2. 行,Excel有个问题,就是有很多行明明没有数据,但是行还是很多。比如:你打开一个表格,数据只有200行左右。但是旁边的下拉条却可以拉到2000多行。这个问题,UsedRange属性是没办法控制的,需要你手动写代码控制!不然UsedRange也认为这些空的行是有数据的,数据等于" "。