-
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
-
//在工作薄的第一个工作表上创建任务列表
-
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
-
}
-
}
-
}
-
VS2005[C#] 操作 Excel 全攻略
最新推荐文章于 2015-06-06 10:53:16 发布