文件里的Excel表的表示方法 哪位高手过客用VC,C#语言开发,想把文件里的Excel表给表示出来,怎莫做? 有好的网站介绍一下也好。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 加载Excel(读取excel内容)返回值是一个DataSet //加载Excel public static DataSet LoadDataFromExcel(string filePath) { try { string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"; OleDbConnection OleConn = new OleDbConnection(strConn); OleConn.Open(); String sql = "SELECT * FROM [Sheet1$]";//可是更改Sheet名称,比如sheet2,等等 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn); DataSet OleDsExcle = new DataSet(); OleDaExcel.Fill(OleDsExcle, "Sheet1"); OleConn.Close(); return OleDsExcle; } catch (Exception err) { MessageBox.Show("数据绑定Excel失败!失败原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return null; } }2.写入Excel内容,参数:excelTable是要导入excel的一个table表 public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass(); try { app.Visible = false; Workbook wBook = app.Workbooks.Add(true); Worksheet wSheet = wBook.Worksheets[1] as Worksheet; if (excelTable.Rows.Count > 0) { int row = 0; row = excelTable.Rows.Count; int col = excelTable.Columns.Count; for (int i = 0; i < row; i++) { for (int j = 0; j < col; j++) { string str = excelTable.Rows[i][j].ToString(); wSheet.Cells[i + 2, j + 1] = str; } } } int size = excelTable.Columns.Count; for (int i = 0; i < size; i++) { wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName; } //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.Save(); //保存excel文件 app.Save(filePath); app.SaveWorkspace(filePath); app.Quit(); app = null; return true; } catch (Exception err) { MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return false; } finally { } } 〉将其导入数据库中,然后读取。谢谢。现在使用变更了、变成在编程内部也可,但要打开复数Excel,读取其中项目,然后相同的几项合集到新的Excel表中。 Mr.colin_liying 你好!谢谢你的帖子。我是个新手,OleDbConnection OleConn = new OleDbConnection(strConn); 中的OleDbConnection 是在哪里設定呢?如果不用OleDbConnection还有别的方法吗? 导入一个excel.exe 从reference里, 然后就可以操作excel了。任意取值和添加 using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using Microsoft.Office.Interop.Excel;using System.Data.SqlClient;using System.Data.OleDb;using System.Reflection;namespace ExcelPrj{ /// <summary> /// Excel 系统中的主文件Excel.exe 本身就是 COM 组件,通过在.NET 项目中引用Exel.exe 文件可以实现对Excel 的功能控制 /// 与COM 组件相互操作是通过使用"包装类"(Wrapper Class) 和"代理"(Proxy) 的机制实现的.包装类使.NET 程序可以识别COM 组件提供的接口,而代理类则是提供对 COM 接口的访问 /// </summary> public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button3_Click(object sender, EventArgs e) { ExportTasks(Bind(), dataGridView1); } //如果 Excel 安装在计算机上,侧导出表格内容到 Excel public void ExportTasks(DataSet TasksData, DataGridView TasksGridView) { // 定义要使用的Excel 组件接口 // 定义Application 对象,此对象表示整个Excel 程序 Microsoft.Office.Interop.Excel.Application excelApp = null ; // 定义Workbook对象,此对象代表工作薄 Microsoft.Office.Interop.Excel.Workbook workBook; // 定义Worksheet 对象,此对象表示Execel 中的一张工作表 Microsoft.Office.Interop.Excel.Worksheet ws=null; //定义Range对象,此对象代表单元格区域 Microsoft.Office.Interop.Excel.Range r; int row = 1; int cell = 1; try { //初始化 Application 对象 excelApp excelApp = new Microsoft.Office.Interop.Excel.Application(); //在工作薄的第一个工作表上创建任务列表 workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); ws =(Worksheet)workBook.Worksheets[1]; // 命名工作表的名称为 "Task Management" ws.Name = "Task Management"; #region 创建表格的列头 // 遍历数据表中的所有列 foreach (DataGridViewColumn cs in TasksGridView.Columns) { // 假如并不想把主键也显示出来 if (cs.HeaderText != "编号") { ws.Cells[row, cell] = cs.HeaderText; r = (Range)ws.Cells[row, cell]; ws.get_Range(r, r).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //此处用来设置列的样式 cell++; } } // 创建行,把数据视图记录输出到对应的Excel 单元格 for (int i = 2; i < TasksData.Tables[0].Rows.Count; i++) { for (int j = 1; j < TasksData.Tables[0].Columns.Count; j++) { ws.Cells[i, j] = TasksData.Tables[0].Rows[i][j].ToString(); // r = (Range)ws.Cells[i,j]; Range rg = (Range)ws.get_Range(ws.Cells[i, j], ws.Cells[i, j]); rg.EntireColumn.ColumnWidth = 20; // rg.Columns.AutoFit(); rg.NumberFormatLocal = "@"; } } #endregion } catch (Exception ex) { MessageBox.Show(ex.ToString()); } //显示 Excel excelApp.Visible = true; } private void button5_Click(object sender, EventArgs e) { DataSet ds = Bind(); dataGridView1.DataSource = ds.Tables[0]; } private DataSet Bind() { SqlConnection conn = new SqlConnection("Server=.;Database=testManage;Integrated Security=SSPI"); SqlDataAdapter da = new SqlDataAdapter("select FNumber,FExamNum,FName,FSex,FJobAdd,FCardID,FBirDate from stuInfo", conn); DataSet ds = new DataSet(); da.Fill(ds); return ds; } private void button2_Click(object sender, EventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Title = "请选择将导出的EXCEL文件存放路径"; sfd.FileName = System.DateTime.Now.ToShortDateString() + "-学生信息"; sfd.Filter = "Excel文档(*.xls)|*.xls"; sfd.ShowDialog(); if (sfd.FileName != "") { Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); if (excelApp == null) { MessageBox.Show("无法创建Excel对象,可能您的机器未安装Excel"); } else { Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet =(Worksheet) workbook.Worksheets[1]; DataSet ds=Bind(); for (int i = 1; i < ds.Tables[0].Rows.Count; i++) { for (int j = 1; j < ds.Tables[0].Columns.Count;j++ ) { if (i == 1) { worksheet.Cells[i, j] = dataGridView1.Columns[j].HeaderText; } worksheet.Cells[i+1, j] = ds.Tables[0].Rows[i][j].ToString(); } } //保存方式一:保存WorkBook //workbook.SaveAs(@"F:\CData.xls", // Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value, // Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value, // Missing.Value,Missing.Value); //保存方式二:保存WorkSheet // worksheet.SaveAs(@"F:\CData2.xls", // Missing.Value, Missing.Value, Missing.Value, Missing.Value, // Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); ////保存方式三 //workbook.Saved = true; //workbook.SaveCopyAs(sfd.FileName); System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet); worksheet = null; System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; workbooks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); workbooks = null; excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); excelApp = null; MessageBox.Show("导出Excel完成!"); } } } private void button4_Click(object sender, EventArgs e) { string strExcelFileName = @"F:\\2007-07-16-学生信息.xls"; string strSheetName = "sheet1"; #region Aspnet 操作Excel 正确 ////源的定义 //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strExcelFileName + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'"; ////Sql语句 //string strExcel = "select * from [" + strSheetName + "$]"; ////定义存放的数据表 //DataSet ds = new DataSet(); ////连接数据源 //OleDbConnection conn = new OleDbConnection(strConn); //conn.Open(); ////适配到数据源 //OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn); //adapter.Fill(ds,"res"); //conn.Close(); //// 一般的情况下. Excel 表格的第一行是列名 //dataGridView2.DataSource = ds.Tables["res"]; #endregion #region COM 组件读取复杂Excel Microsoft.Office.Interop.Excel.Application excelApp = null; Microsoft.Office.Interop.Excel.Workbook workBook; Microsoft.Office.Interop.Excel.Worksheet ws = null; try { excelApp = new Microsoft.Office.Interop.Excel.Application(); workBook = excelApp.Workbooks.Open(@"F:\\Book1.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); ws = (Worksheet)workBook.Worksheets[1]; //Excel 默认为 256 列.. MessageBox.Show(ws.Cells.Columns.Count.ToString()); excelApp.Quit(); } catch (Exception ex) { throw ex; } #endregion } }} using Microsoft.Office.Interop.Excel;的Office.怎莫不能读取呢?现在的Version是、 Microsoft Visual Studio 2005 你添加reference了吗? C:\Program Files\Microsoft Office\Office12\excel.exe 谢谢上述的C:\Program Files\Microsoft Office\Office12\excel.exe是指在直接在C:\Program Files\Microsoft Office\Office12\ 里加上excel.exe吗? 将上面路径下的excel.exe加到你的references里 我不知道我的references在哪里? Solution Explorer里, 中文名字叫 引用 就是引用! excel就是 一个单独文件版的 数据库 里面就是表结构的数据。 微软提供了完整的读取类库。 就像操作数据库一样。 在Microsoft Visual Studio 2005 的目录栏里找到了[参考追加],因为是Excel 2003 选择了「Microsoft Excel 11.0 Object Library」using Microsoft.Office.Interop.Excel; 也能读取了。 打印对话框中如何显示总页数 求一图片上传至数据库里的代码。。 数据库连接关闭的问题 (在线等待 + 满分)关于批量增加及更新的问题 学C#要先学C++和Java吗? 指针问题,在线等待 当numericUpDown内容用BackSpace键去除变为空白时,引发CausesValidation,让程序停顿数秒无反应?? C#中[]表示什么? 有没有C# 和 matlab 混合编程的书?推荐一本!亲们! 请问有独立出来的MSDN安装文件吗? 拖动进度条,播放器是怎么定位到相应位置播放的 C# 的FileStream 可否序列化
public static DataSet LoadDataFromExcel(string filePath)
{
try
{
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
String sql = "SELECT * FROM [Sheet1$]";//可是更改Sheet名称,比如sheet2,等等 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, "Sheet1");
OleConn.Close();
return OleDsExcle;
}
catch (Exception err)
{
MessageBox.Show("数据绑定Excel失败!失败原因:" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return null;
}
}
2.写入Excel内容,参数:excelTable是要导入excel的一个table表 public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath)
{
Microsoft.Office.Interop.Excel.Application app =
new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
app.Visible = false;
Workbook wBook = app.Workbooks.Add(true);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
if (excelTable.Rows.Count > 0)
{
int row = 0;
row = excelTable.Rows.Count;
int col = excelTable.Columns.Count;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
string str = excelTable.Rows[i][j].ToString();
wSheet.Cells[i + 2, j + 1] = str;
}
}
} int size = excelTable.Columns.Count;
for (int i = 0; i < size; i++)
{
wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
}
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
//保存工作簿
wBook.Save();
//保存excel文件
app.Save(filePath);
app.SaveWorkspace(filePath);
app.Quit();
app = null;
return true;
}
catch (Exception err)
{
MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return false;
}
finally
{
}
}
〉将其导入数据库中,然后读取。谢谢。现在使用变更了、
变成在编程内部也可,但要打开复数Excel,读取其中项目,然后相同的几项合集到新的Excel表中。
谢谢你的帖子。
我是个新手,
OleDbConnection OleConn = new OleDbConnection(strConn);
中
的OleDbConnection 是在哪里設定呢?如果不用OleDbConnection还有别的方法吗?
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Reflection;
namespace ExcelPrj
{
/// <summary>
/// Excel 系统中的主文件Excel.exe 本身就是 COM 组件,通过在.NET 项目中引用Exel.exe 文件可以实现对Excel 的功能控制
/// 与COM 组件相互操作是通过使用"包装类"(Wrapper Class) 和"代理"(Proxy) 的机制实现的.包装类使.NET 程序可以识别COM 组件提供的接口,而代理类则是提供对 COM 接口的访问
/// </summary>
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button3_Click(object sender, EventArgs e)
{
ExportTasks(Bind(), dataGridView1);
}
//如果 Excel 安装在计算机上,侧导出表格内容到 Excel
public void ExportTasks(DataSet TasksData, DataGridView TasksGridView)
{
// 定义要使用的Excel 组件接口
// 定义Application 对象,此对象表示整个Excel 程序
Microsoft.Office.Interop.Excel.Application excelApp = null ;
// 定义Workbook对象,此对象代表工作薄
Microsoft.Office.Interop.Excel.Workbook workBook;
// 定义Worksheet 对象,此对象表示Execel 中的一张工作表
Microsoft.Office.Interop.Excel.Worksheet ws=null;
//定义Range对象,此对象代表单元格区域
Microsoft.Office.Interop.Excel.Range r;
int row = 1; int cell = 1;
try
{
//初始化 Application 对象 excelApp
excelApp = new Microsoft.Office.Interop.Excel.Application();
//在工作薄的第一个工作表上创建任务列表
workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
ws =(Worksheet)workBook.Worksheets[1];
// 命名工作表的名称为 "Task Management"
ws.Name = "Task Management";
#region 创建表格的列头
// 遍历数据表中的所有列
foreach (DataGridViewColumn cs in TasksGridView.Columns)
{
// 假如并不想把主键也显示出来
if (cs.HeaderText != "编号")
{
ws.Cells[row, cell] = cs.HeaderText;
r = (Range)ws.Cells[row, cell];
ws.get_Range(r, r).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
//此处用来设置列的样式
cell++;
}
}
// 创建行,把数据视图记录输出到对应的Excel 单元格
for (int i = 2; i < TasksData.Tables[0].Rows.Count; i++)
{
for (int j = 1; j < TasksData.Tables[0].Columns.Count; j++)
{
ws.Cells[i, j] = TasksData.Tables[0].Rows[i][j].ToString();
// r = (Range)ws.Cells[i,j];
Range rg = (Range)ws.get_Range(ws.Cells[i, j], ws.Cells[i, j]);
rg.EntireColumn.ColumnWidth = 20;
// rg.Columns.AutoFit();
rg.NumberFormatLocal = "@";
}
}
#endregion
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
//显示 Excel
excelApp.Visible = true;
}
private void button5_Click(object sender, EventArgs e)
{
DataSet ds = Bind();
dataGridView1.DataSource = ds.Tables[0];
}
private DataSet Bind()
{
SqlConnection conn = new SqlConnection("Server=.;Database=testManage;Integrated Security=SSPI");
SqlDataAdapter da = new SqlDataAdapter("select FNumber,FExamNum,FName,FSex,FJobAdd,FCardID,FBirDate from stuInfo", conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
private void button2_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "请选择将导出的EXCEL文件存放路径";
sfd.FileName = System.DateTime.Now.ToShortDateString() + "-学生信息";
sfd.Filter = "Excel文档(*.xls)|*.xls";
sfd.ShowDialog();
if (sfd.FileName != "")
{
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
if (excelApp == null)
{
MessageBox.Show("无法创建Excel对象,可能您的机器未安装Excel");
}
else
{
Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet =(Worksheet) workbook.Worksheets[1];
DataSet ds=Bind();
for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
{
for (int j = 1; j < ds.Tables[0].Columns.Count;j++ )
{
if (i == 1)
{
worksheet.Cells[i, j] = dataGridView1.Columns[j].HeaderText;
}
worksheet.Cells[i+1, j] = ds.Tables[0].Rows[i][j].ToString();
}
}
//保存方式一:保存WorkBook
//workbook.SaveAs(@"F:\CData.xls",
// Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
// Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,
// Missing.Value,Missing.Value);
//保存方式二:保存WorkSheet
// worksheet.SaveAs(@"F:\CData2.xls",
// Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
////保存方式三
//workbook.Saved = true;
//workbook.SaveCopyAs(sfd.FileName);
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
MessageBox.Show("导出Excel完成!");
}
}
}
private void button4_Click(object sender, EventArgs e)
{
string strExcelFileName = @"F:\\2007-07-16-学生信息.xls";
string strSheetName = "sheet1";
#region Aspnet 操作Excel 正确
////源的定义
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strExcelFileName + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";
////Sql语句
//string strExcel = "select * from [" + strSheetName + "$]";
////定义存放的数据表
//DataSet ds = new DataSet();
////连接数据源
//OleDbConnection conn = new OleDbConnection(strConn);
//conn.Open();
////适配到数据源
//OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);
//adapter.Fill(ds,"res");
//conn.Close();
//// 一般的情况下. Excel 表格的第一行是列名
//dataGridView2.DataSource = ds.Tables["res"];
#endregion
#region COM 组件读取复杂Excel
Microsoft.Office.Interop.Excel.Application excelApp = null;
Microsoft.Office.Interop.Excel.Workbook workBook;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
try
{
excelApp = new Microsoft.Office.Interop.Excel.Application();
workBook = excelApp.Workbooks.Open(@"F:\\Book1.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
ws = (Worksheet)workBook.Worksheets[1];
//Excel 默认为 256 列..
MessageBox.Show(ws.Cells.Columns.Count.ToString());
excelApp.Quit();
}
catch (Exception ex)
{
throw ex;
}
#endregion
}
}
}
的
Office.
怎莫不能读取呢?现在的Version是、 Microsoft Visual Studio 2005
上述的C:\Program Files\Microsoft Office\Office12\excel.exe
是指在直接在
C:\Program Files\Microsoft Office\Office12\ 里
加上
excel.exe
吗?
excel就是 一个单独文件版的 数据库
里面就是表结构的数据。
微软提供了完整的读取类库。
就像操作数据库一样。
找到了[参考追加],
因为是Excel 2003 选择了「Microsoft Excel 11.0 Object Library」
using Microsoft.Office.Interop.Excel;
也能读取了。