C#怎么读取Excel的数据 有哪位大哥用C#读取Excel的数据呢?有没有相关的实例指导一下?论坛里好少相关的资料! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 其实就跟读取数据库数据一样http://www.csharpwin.com/csharpspace/6809r1243.shtmlhttp://www.cnblogs.com/tuyile006/archive/2006/10/25/395586.html DataTable dt = null; string connetionStr = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + url + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"; string strSql = "select xx from [Sheet1$]"; OleDbConnection oleConn = new OleDbConnection(connetionStr); OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strSql, connetionStr); try { dt = new DataTable(); oleAdapter.Fill(dt); return dt; }参考,根据版本修改红色某些具体值 有几种方法方法:1. OLEDBhttp://blog.csdn.net/weiqian000/article/details/42048262. Office Com组件参考http://www.codeproject.com/Articles/5123/Opening-and-Navigating-Excel-with-C3第三方控件。找crackdung,他对这方面比较熟悉。 有几种方法方法:1. OLEDBhttp://blog.csdn.net/weiqian000/article/details/42048262. Office Com组件参考http://www.codeproject.com/Articles/5123/Opening-and-Navigating-Excel-with-C3第三方控件。找crackdung,他对这方面比较熟悉。看到我也说两句,我用的是把ddatatgridview里面的数据存到excle中,我用的是office com这种方法,导出到是能导出,但是速度确实很慢,数据不多(也就不到一屏)就可以看到明显的停留,我看别人的程序做的都挺好的,不知道是我写的不好,还是可以优化,还是有好方法,谢谢(我主要是导出的数据希望能快) 请搜索ReadExcel,会有你的答案的,有一个是忽略Excel环境的 应该是我问的不准确,我想实现在窗体键入关键字的时候,可以读取并筛选Excel,并列出该关键字同行的一些数据。 用OLEDB不怎么好..有个开源的ExcelLibrary.dll处理不错的,而且服务器不用装excel都可以. 看看这个例子可不可以首先先添加项目引用:1. .NET->System.Data.OracleClient.dll2. COM->Microsoft Excel 11.0 Object Library载编写代码如下:using System;using System.IO;using System.Data;using System.Reflection;using System.Diagnostics;using System.Configuration;using System.Collections;using Excel;namespace ProtoType{ /// /// 套用模板输出Excel,生成xls文件和html文件 /// Author: Liu Wen /// Date Created: 2006-8 /// public class ExportExcel { #region variable member protected string templateFile = null; protected string excelFile = null; protected string htmlFile = null; protected object missing = Missing.Value; Excel.ApplicationClass app; Excel.Workbook book; Excel.Worksheet sheet; Excel.Range range; private DateTime beforeTime; //Excel启动之前时间 private DateTime afterTime; //Excel启动之后时间 #endregion /// /// 构造函数,将一个已有Excel工作簿作为模板,并指定输出路径 /// /// Excel模板文件路径 /// Excel输出文件路径 /// Html输出文件路径 public ExportExcel(string templateFile, string excelFile, string htmlFile) { if(templateFile == null) throw new Exception("Excel模板文件路径不能为空!"); if(excelFile == null) throw new Exception("Excel输出文件路径不能为空!"); if(htmlFile == null) throw new Exception("Html输出文件路径不能为空!"); if(!File.Exists(templateFile)) throw new Exception("指定路径的Excel模板文件不存在!"); this.templateFile = templateFile; this.excelFile = excelFile; this.htmlFile = htmlFile; //创建一个Application对象 beforeTime = DateTime.Now; app = new ApplicationClass(); //app.Visible = true; afterTime = DateTime.Now; //打开模板文件,得到WorkBook对象 try { book = app.Workbooks.Open(templateFile,missing,missing,missing,missing,missing, missing,missing,missing,missing,missing,missing,missing,missing,missing); } catch (Exception e) { throw e; } //得到WorkSheet对象 sheet = (Excel.Worksheet)book.Sheets.get_Item(1); } /// /// 将DataTable数据导出到Excel(可动态插入行) /// /// DataTable /// 插入行的索引 /// 插入列的索引 public void DataTableToExcel(System.Data.DataTable dt, int rowIndex, int colIndex) { //range = sheet.get_Range("A7", missing); //range.Value2 = "raogerrr"; //string str = range.Text.ToString(); int rowCount = dt.Rows.Count; //DataTable行数 int colCount = dt.Columns.Count; //DataTable列数 int iRow; int iCol; //将数据导出到相应的单元格 for (iRow = 0; iRow < rowCount; iRow++) { //插入新行 this.InsertRows(sheet, iRow+rowIndex); //填充当前行 for (iCol = 0; iCol < colCount; iCol++) { sheet.Cells[iRow+rowIndex, iCol+colIndex] = dt.Rows[iRow][iCol].ToString(); } } this.DeleteRows(sheet, rowCount+rowIndex); //Excel.QueryTables qts = sheet.QueryTables; //Excel.QueryTable qt = qts.Add(,,); //qt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows; //qt.Refresh(); } /// /// 将DataTable数据导出到Excel(可动态插入行) /// /// DataTable /// 插入数据的起始单元格 public void DataTableToExcel(System.Data.DataTable dt, string cellID) { int rowIndex = sheet.get_Range(cellID, missing).Row; int colIndex = sheet.get_Range(cellID, missing).Column; int rowCount = dt.Rows.Count; //DataTable行数 int colCount = dt.Columns.Count; //DataTable列数 int iRow; int iCol; //利用二维数组批量写入 string[,] array = new string[rowCount,colCount]; for (iRow = 0; iRow < rowCount; iRow++) { for (iCol = 0; iCol < colCount; iCol++) { array[iRow,iCol] = dt.Rows[iRow][iCol].ToString(); } } for (iRow = 0; iRow < rowCount; iRow++) { this.InsertRows(sheet, iRow+rowIndex); } this.DeleteRows(sheet, rowCount+rowIndex); range = sheet.get_Range(cellID, missing); range = range.get_Resize(rowCount, colCount); range.Value2 = array; } /// /// 将DataTable数据导出到Excel(固定) /// /// DataTable /// 插入数据的起始单元格 public void DataTableToExcel2(System.Data.DataTable dt, string cellID) { int rowCount = dt.Rows.Count; //DataTable行数 int colCount = dt.Columns.Count; //DataTable列数 int iRow; int iCol; //利用二维数组批量写入 string[,] array = new string[rowCount,colCount]; for (iRow = 0; iRow < rowCount; iRow++) { for (iCol = 0; iCol < colCount; iCol++) { array[iRow,iCol] = dt.Rows[iRow][iCol].ToString(); } } range = sheet.get_Range(cellID, missing); range = range.get_Resize(rowCount, colCount); range.Value2 = array; } /// /// 输出生成的Excel, Html文件 /// public void OutputFile() { if (this.excelFile == null) throw new Exception("没有指定Excel输出文件路径!"); if (this.htmlFile == null) throw new Exception("没有指定Htmll输出文件路径!"); try { book.SaveAs(excelFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing,missing); book.SaveAs(htmlFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); } catch (Exception e) { throw e; } finally { this.Dispose(); } } /// /// 在工作表中插入行,并调整其他行以留出空间 /// /// 当前工作表 /// 欲插入的行索引 private void InsertRows(Excel.Worksheet sheet, int rowIndex) { Range r = (Excel.Range)sheet.Rows[rowIndex, missing]; //object Range.Insert(object shift, object copyorigin); //shift: Variant类型,可选。指定单元格的调整方式。可以为下列 XlInsertShiftDirection 常量之一: //xlShiftToRight 或 xlShiftDown。如果省略该参数,Microsoft Excel 将根据区域形状确定调整方式。 r.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing); } /// /// 在工作表中删除行 /// /// 当前工作表 /// 欲删除的行索引 private void DeleteRows(Excel.Worksheet sheet, int rowIndex) { Range r = (Range)sheet.Rows[rowIndex, missing]; r.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); } /// /// 退出Excel,并且释放调用的COM资源 /// private void Dispose() { book.Close(missing, missing, missing); app.Workbooks.Close(); app.Quit(); if(range != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range); range = null; } if(sheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); sheet = null; } if(book != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(book); book = null; } if(app != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; } GC.Collect(); this.KillExcelProcess(); } /// /// 结束Excel进程 /// private void KillExcelProcess() { DateTime startTime; Process[] processes = Process.GetProcessesByName("Excel"); //得不到Excel进程ID,暂时只能判断进程启动时间 foreach (Process process in processes) { startTime = process.StartTime; if(startTime > beforeTime && startTime < afterTime) process.Kill(); } } }} 如果你使用office庫,注意使用的版本就好,不然,,呵呵 因为我是用WPF做的程序,不知道后台读取并筛选Excel的数据后显示到窗体的难度大不大? 我想在Excel查找定位到关键字,并复制跟他同行的某几个数据(不是整行)。我试过OleDB、ExcelLibrary,但是网上关于它们的查找这一功能的资料很少,讲的比较多的还是怎么读取全部数据或者将数据导出到Excel里面。而且我的是数据源很大,一个簿里面有很多表,并都有做了筛选的,我用Library读的时候就报错大概说“不能打开特殊表”,换个小一点的表却没事. 前两天刚好用到这个,读Excel文件到DataTable。 参考: http://www.dotneter.com/tag/read-excel-file-in-c C#窗体程序,谁能帮忙一下给一个com组件操作读写Excel文件,显示在gridview控件中的demo 急,求助:Excel导入SQL SERVER 2005我这代码错在哪? 如何选择使用.net 1.1/2.0? 【菜鸟问题大曝光】DataList中Items中有四列!怎么实现啊? ado.net远程调用 socket编程问题 windows workflow foundation 菜鸟提问!关于文件转换问题? 关于程序里面的路径问题...请高手近来... 怎么使用C#来调用外部程序? 请问对注册表进行操作,要using什么东东啊? 水晶报表+折线图+标准线 学生档案管理系统(WinForm应用程序)
http://www.csharpwin.com/csharpspace/6809r1243.shtml
http://www.cnblogs.com/tuyile006/archive/2006/10/25/395586.html
string connetionStr = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + url + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';";
string strSql = "select xx from [Sheet1$]";
OleDbConnection oleConn = new OleDbConnection(connetionStr);
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strSql, connetionStr);
try
{
dt = new DataTable();
oleAdapter.Fill(dt); return dt;
}
参考,根据版本修改红色某些具体值
1. OLEDB
http://blog.csdn.net/weiqian000/article/details/42048262. Office Com组件
参考
http://www.codeproject.com/Articles/5123/Opening-and-Navigating-Excel-with-C3第三方控件。
找crackdung,他对这方面比较熟悉。
1. OLEDB
http://blog.csdn.net/weiqian000/article/details/42048262. Office Com组件
参考
http://www.codeproject.com/Articles/5123/Opening-and-Navigating-Excel-with-C3第三方控件。
找crackdung,他对这方面比较熟悉。看到我也说两句,我用的是把ddatatgridview里面的数据存到excle中,我用的是office com这种方法,导出到是能导出,但是速度确实很慢,数据不多(也就不到一屏)就可以看到明显的停留,我看别人的程序做的都挺好的,不知道是我写的不好,还是可以优化,还是有好方法,谢谢(我主要是导出的数据希望能快)
应该是我问的不准确,我想实现在窗体键入关键字的时候,可以读取并筛选Excel,并列出该关键字同行的一些数据。
2. COM->Microsoft Excel 11.0 Object Library载编写代码如下:using System;
using System.IO;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using System.Configuration;
using System.Collections;
using Excel;namespace ProtoType
{
///
/// 套用模板输出Excel,生成xls文件和html文件
/// Author: Liu Wen
/// Date Created: 2006-8
///
public class ExportExcel
{
#region variable member
protected string templateFile = null;
protected string excelFile = null;
protected string htmlFile = null;
protected object missing = Missing.Value;
Excel.ApplicationClass app;
Excel.Workbook book;
Excel.Worksheet sheet;
Excel.Range range;
private DateTime beforeTime; //Excel启动之前时间
private DateTime afterTime; //Excel启动之后时间
#endregion ///
/// 构造函数,将一个已有Excel工作簿作为模板,并指定输出路径
///
/// Excel模板文件路径
/// Excel输出文件路径
/// Html输出文件路径
public ExportExcel(string templateFile, string excelFile, string htmlFile)
{
if(templateFile == null)
throw new Exception("Excel模板文件路径不能为空!"); if(excelFile == null)
throw new Exception("Excel输出文件路径不能为空!"); if(htmlFile == null)
throw new Exception("Html输出文件路径不能为空!"); if(!File.Exists(templateFile))
throw new Exception("指定路径的Excel模板文件不存在!"); this.templateFile = templateFile;
this.excelFile = excelFile;
this.htmlFile = htmlFile; //创建一个Application对象
beforeTime = DateTime.Now;
app = new ApplicationClass();
//app.Visible = true;
afterTime = DateTime.Now; //打开模板文件,得到WorkBook对象
try
{
book = app.Workbooks.Open(templateFile,missing,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing,missing,missing,missing);
}
catch (Exception e)
{
throw e;
}
//得到WorkSheet对象
sheet = (Excel.Worksheet)book.Sheets.get_Item(1);
} ///
/// 将DataTable数据导出到Excel(可动态插入行)
///
/// DataTable
/// 插入行的索引
/// 插入列的索引
public void DataTableToExcel(System.Data.DataTable dt, int rowIndex, int colIndex)
{
//range = sheet.get_Range("A7", missing);
//range.Value2 = "raogerrr";
//string str = range.Text.ToString(); int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
int iRow;
int iCol; //将数据导出到相应的单元格
for (iRow = 0; iRow < rowCount; iRow++)
{
//插入新行
this.InsertRows(sheet, iRow+rowIndex);
//填充当前行
for (iCol = 0; iCol < colCount; iCol++)
{
sheet.Cells[iRow+rowIndex, iCol+colIndex] = dt.Rows[iRow][iCol].ToString();
}
}
this.DeleteRows(sheet, rowCount+rowIndex); //Excel.QueryTables qts = sheet.QueryTables;
//Excel.QueryTable qt = qts.Add(,,);
//qt.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
//qt.Refresh();
} ///
/// 将DataTable数据导出到Excel(可动态插入行)
///
/// DataTable
/// 插入数据的起始单元格
public void DataTableToExcel(System.Data.DataTable dt, string cellID)
{
int rowIndex = sheet.get_Range(cellID, missing).Row;
int colIndex = sheet.get_Range(cellID, missing).Column;
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
int iRow;
int iCol; //利用二维数组批量写入
string[,] array = new string[rowCount,colCount];
for (iRow = 0; iRow < rowCount; iRow++)
{
for (iCol = 0; iCol < colCount; iCol++)
{
array[iRow,iCol] = dt.Rows[iRow][iCol].ToString();
}
} for (iRow = 0; iRow < rowCount; iRow++)
{
this.InsertRows(sheet, iRow+rowIndex);
}
this.DeleteRows(sheet, rowCount+rowIndex); range = sheet.get_Range(cellID, missing);
range = range.get_Resize(rowCount, colCount);
range.Value2 = array;
} ///
/// 将DataTable数据导出到Excel(固定)
///
/// DataTable
/// 插入数据的起始单元格
public void DataTableToExcel2(System.Data.DataTable dt, string cellID)
{
int rowCount = dt.Rows.Count; //DataTable行数
int colCount = dt.Columns.Count; //DataTable列数
int iRow;
int iCol; //利用二维数组批量写入
string[,] array = new string[rowCount,colCount];
for (iRow = 0; iRow < rowCount; iRow++)
{
for (iCol = 0; iCol < colCount; iCol++)
{
array[iRow,iCol] = dt.Rows[iRow][iCol].ToString();
}
} range = sheet.get_Range(cellID, missing);
range = range.get_Resize(rowCount, colCount);
range.Value2 = array;
} ///
/// 输出生成的Excel, Html文件
///
public void OutputFile()
{
if (this.excelFile == null)
throw new Exception("没有指定Excel输出文件路径!");
if (this.htmlFile == null)
throw new Exception("没有指定Htmll输出文件路径!");
try
{
book.SaveAs(excelFile, missing, missing, missing, missing, missing,
Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing,missing); book.SaveAs(htmlFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing,
Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
}
catch (Exception e)
{
throw e;
}
finally
{
this.Dispose();
}
} ///
/// 在工作表中插入行,并调整其他行以留出空间
///
/// 当前工作表
/// 欲插入的行索引
private void InsertRows(Excel.Worksheet sheet, int rowIndex)
{
Range r = (Excel.Range)sheet.Rows[rowIndex, missing]; //object Range.Insert(object shift, object copyorigin);
//shift: Variant类型,可选。指定单元格的调整方式。可以为下列 XlInsertShiftDirection 常量之一:
//xlShiftToRight 或 xlShiftDown。如果省略该参数,Microsoft Excel 将根据区域形状确定调整方式。
r.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);
} ///
/// 在工作表中删除行
///
/// 当前工作表
/// 欲删除的行索引
private void DeleteRows(Excel.Worksheet sheet, int rowIndex)
{
Range r = (Range)sheet.Rows[rowIndex, missing]; r.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
} ///
/// 退出Excel,并且释放调用的COM资源
///
private void Dispose()
{
book.Close(missing, missing, missing);
app.Workbooks.Close();
app.Quit(); if(range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if(sheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
sheet = null;
}
if(book != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
book = null;
}
if(app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
} GC.Collect();
this.KillExcelProcess();
} ///
/// 结束Excel进程
///
private void KillExcelProcess()
{
DateTime startTime;
Process[] processes = Process.GetProcessesByName("Excel"); //得不到Excel进程ID,暂时只能判断进程启动时间
foreach (Process process in processes)
{
startTime = process.StartTime;
if(startTime > beforeTime && startTime < afterTime)
process.Kill();
}
}
}
}
因为我是用WPF做的程序,不知道后台读取并筛选Excel的数据后显示到窗体的难度大不大?
前两天刚好用到这个,读Excel文件到DataTable。 参考:
http://www.dotneter.com/tag/read-excel-file-in-c