好多方法啊; 你可以下载一个ComponentOne组件,里面含有一个excel的操作组件,不受Excel版本的限制,但对于日期的处理有点问题. 也可以弄成一个文件文件,然后用Excel打开。 也可以使用反射机制对Excel使用后期绑定进行操作,这样就不受Excel的版本限制了. 也可以根据Excel.exe生成一个互操作程序集,然后进行调用,不过这个会受Excel版本的限制.using System; using System.Collections.Generic; using System.Text; using System.Reflection; namespace ExcelOpt { /// <summary> /// Excel后期绑定的操作类 /// </summary> public class Application { object m_ExcelApp; public Application() { Type objExcelType = Type.GetTypeFromProgID("Excel.Application"); if (objExcelType == null) { throw new Exception("未发现Excel程序"); } m_ExcelApp = Activator.CreateInstance(objExcelType); if (m_ExcelApp == null) { throw new Exception("启用Excel程序失败!"); } } public bool Visible { get { object objVisible = m_ExcelApp.GetType().InvokeMember("Visible", BindingFlags.GetProperty, null, m_ExcelApp, null); if (objVisible is Boolean) return (bool)objVisible; else throw new Exception("调用方法失败!"); } set { object[] Parameters = new object[1] { value }; m_ExcelApp.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, m_ExcelApp, Parameters); } } public Workbooks Workbooks { get { object workbooks = m_ExcelApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, m_ExcelApp, null); if (workbooks == null) throw new Exception("查询工作簿时失败!"); else return new Workbooks(workbooks); } } } } namespace ExcelOpt { public class Workbooks { private object m_Workbooks; private string m_ExcelFileName; public Workbooks(object workbooks) { m_Workbooks = workbooks; } public void Open(string ExcelFileName) { try { m_ExcelFileName = ExcelFileName; object[] Parameters = new object[1] { ExcelFileName }; m_Workbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, m_Workbooks, Parameters); } catch (Exception err) { throw err; } } public Workbook this[int index] { get { object[] Parameters = new object[1]{index }; object workbook = m_Workbooks.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, m_Workbooks, Parameters); if (workbook == null) throw new Exception("获取工作薄时出现错误!"); else return new Workbook(workbook); } } } } namespace ExcelOpt { public class Workbook { private object m_Workbook; public Workbook(object workbook) { m_Workbook = workbook; } public Worksheets Worksheets { get { object worksheets = m_Workbook.GetType().InvokeMember("Worksheets", System.Reflection.BindingFlags.GetProperty, null, m_Workbook, null); if (worksheets == null) throw new Exception("获取工作表集合时失败!"); else return new Worksheets(worksheets); } } public void SaveAs(string FileName) { object[] Parameters = new object[1] { FileName }; m_Workbook.GetType().InvokeMember("SaveAs", System.Reflection.BindingFlags.InvokeMethod, null, m_Workbook, Parameters); } } } namespace ExcelOpt { public class Worksheets { private object m_Worksheets; public Worksheets ( object worksheets ) { m_Worksheets = worksheets; } public Worksheet this[int index] { get { object[] Parameters = new object[1] { index }; object worksheet = m_Worksheets.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, m_Worksheets, Parameters); if (worksheet == null) throw new Exception("获取工作表时出现错误!"); else return new Worksheet(worksheet); } } } } namespace ExcelOpt { public class Worksheet { private object m_Worksheet; public Worksheet ( object worksheet ) { m_Worksheet = worksheet ; } public Range this[ int row , int col ] { get { object[] Parameters = new Object[2] { row , col }; object cells = m_Worksheet.GetType().InvokeMember("Cells", System.Reflection.BindingFlags.GetProperty, null, m_Worksheet, Parameters); if (cells == null) throw new Exception("获取单元格失败!"); else return new Range(cells); } } public Range this[ int row , string col ] { get { object[] Parameters = new Object[2] { row, col }; object cells = m_Worksheet.GetType().InvokeMember("Cells", System.Reflection.BindingFlags.GetProperty, null, m_Worksheet, Parameters); if (cells == null) throw new Exception("获取单元格失败!"); else return new Range(cells); } } } }
namespace ExcelOpt { public class Range { private object m_Range; public Range(object Range) { m_Range = Range; } public object Value { get { object result = m_Range.GetType().InvokeMember("Value", System.Reflection.BindingFlags.GetProperty, null, m_Range, null); return result; } set { object[] Parameters = new Object[1] { value }; m_Range.GetType().InvokeMember("Value", System.Reflection.BindingFlags.SetProperty, null, m_Range, Parameters); } } } }
OleDbConnection con = new OleDbConnection(strcon); string strcmd = "select * from dd"; con.Open(); OleDbCommand cmd = new OleDbCommand(strcmd, con); OleDbDataAdapter da = new OleDbDataAdapter(strcmd, strcon); DataSet ds = new DataSet(); da.Fill(ds); DataTable mydatatable = ds.Tables[0]; int row=2; a.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Workbooks.Add(true); for (int i = 0; i < mydatatable.Columns.Count; i++) { excel.Cells[1, i+1] = mydatatable.Columns[i].ColumnName;
} for (int i = 0; i < mydatatable.Rows.Count; i++) { for (int j = 0; j <mydatatable .Columns .Count; j++) { excel.Cells[row , j+1] = mydatatable.Rows[i][j];
你可以下载一个ComponentOne组件,里面含有一个excel的操作组件,不受Excel版本的限制,但对于日期的处理有点问题.
也可以弄成一个文件文件,然后用Excel打开。
也可以使用反射机制对Excel使用后期绑定进行操作,这样就不受Excel的版本限制了.
也可以根据Excel.exe生成一个互操作程序集,然后进行调用,不过这个会受Excel版本的限制.using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection; namespace ExcelOpt
{
/// <summary>
/// Excel后期绑定的操作类
/// </summary>
public class Application
{
object m_ExcelApp;
public Application()
{
Type objExcelType = Type.GetTypeFromProgID("Excel.Application");
if (objExcelType == null)
{
throw new Exception("未发现Excel程序");
}
m_ExcelApp = Activator.CreateInstance(objExcelType);
if (m_ExcelApp == null)
{
throw new Exception("启用Excel程序失败!");
}
}
public bool Visible
{
get
{
object objVisible =
m_ExcelApp.GetType().InvokeMember("Visible", BindingFlags.GetProperty, null, m_ExcelApp, null);
if (objVisible is Boolean)
return (bool)objVisible;
else
throw new Exception("调用方法失败!");
}
set
{
object[] Parameters = new object[1] { value };
m_ExcelApp.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, m_ExcelApp, Parameters);
}
} public Workbooks Workbooks
{
get
{
object workbooks = m_ExcelApp.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, m_ExcelApp, null);
if (workbooks == null)
throw new Exception("查询工作簿时失败!");
else
return new Workbooks(workbooks);
}
}
}
}
namespace ExcelOpt
{
public class Workbooks
{
private object m_Workbooks;
private string m_ExcelFileName; public Workbooks(object workbooks)
{
m_Workbooks = workbooks;
} public void Open(string ExcelFileName)
{
try
{
m_ExcelFileName = ExcelFileName;
object[] Parameters = new object[1] { ExcelFileName };
m_Workbooks.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, m_Workbooks, Parameters); }
catch (Exception err)
{
throw err;
}
} public Workbook this[int index]
{
get
{
object[] Parameters = new object[1]{index };
object workbook = m_Workbooks.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, m_Workbooks, Parameters);
if (workbook == null)
throw new Exception("获取工作薄时出现错误!");
else
return new Workbook(workbook);
}
}
}
} namespace ExcelOpt
{
public class Workbook
{
private object m_Workbook;
public Workbook(object workbook)
{
m_Workbook = workbook;
} public Worksheets Worksheets
{
get
{
object worksheets = m_Workbook.GetType().InvokeMember("Worksheets", System.Reflection.BindingFlags.GetProperty, null, m_Workbook, null);
if (worksheets == null)
throw new Exception("获取工作表集合时失败!");
else
return new Worksheets(worksheets);
}
}
public void SaveAs(string FileName)
{
object[] Parameters = new object[1] { FileName };
m_Workbook.GetType().InvokeMember("SaveAs", System.Reflection.BindingFlags.InvokeMethod, null, m_Workbook, Parameters); }
}
} namespace ExcelOpt
{
public class Worksheets
{
private object m_Worksheets; public Worksheets ( object worksheets )
{
m_Worksheets = worksheets;
} public Worksheet this[int index]
{
get
{
object[] Parameters = new object[1] { index };
object worksheet = m_Worksheets.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, m_Worksheets, Parameters);
if (worksheet == null)
throw new Exception("获取工作表时出现错误!");
else
return new Worksheet(worksheet);
}
} }
} namespace ExcelOpt
{
public class Worksheet
{
private object m_Worksheet;
public Worksheet ( object worksheet )
{
m_Worksheet = worksheet ;
} public Range this[ int row , int col ]
{
get
{
object[] Parameters = new Object[2] { row , col };
object cells = m_Worksheet.GetType().InvokeMember("Cells", System.Reflection.BindingFlags.GetProperty, null, m_Worksheet, Parameters);
if (cells == null)
throw new Exception("获取单元格失败!");
else
return new Range(cells);
}
}
public Range this[ int row , string col ]
{
get
{
object[] Parameters = new Object[2] { row, col };
object cells = m_Worksheet.GetType().InvokeMember("Cells", System.Reflection.BindingFlags.GetProperty, null, m_Worksheet, Parameters);
if (cells == null)
throw new Exception("获取单元格失败!");
else
return new Range(cells);
}
}
}
}
namespace ExcelOpt
{
public class Range
{
private object m_Range;
public Range(object Range)
{
m_Range = Range;
} public object Value
{
get
{
object result = m_Range.GetType().InvokeMember("Value", System.Reflection.BindingFlags.GetProperty, null, m_Range, null);
return result;
}
set
{
object[] Parameters = new Object[1] { value };
m_Range.GetType().InvokeMember("Value", System.Reflection.BindingFlags.SetProperty, null, m_Range, Parameters);
}
}
}
}
俺写的类只封装了一部分属性,如果你需要其它的属性,就新建一个工程,引用Excel.exe,在对象浏览器里查看一下,然后参照一下上面的代码就可以弄出来了.
俺也才学会这方法.
public void gettoexcel()
{
string strcon = "Provider =Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\xsq.mdb";
OleDbConnection con = new OleDbConnection(strcon);
string strcmd = "select * from dd";
con.Open();
OleDbCommand cmd = new OleDbCommand(strcmd, con);
OleDbDataAdapter da = new OleDbDataAdapter(strcmd, strcon);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable mydatatable = ds.Tables[0];
int row=2;
a.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Add(true); for (int i = 0; i < mydatatable.Columns.Count; i++)
{
excel.Cells[1, i+1] = mydatatable.Columns[i].ColumnName;
} for (int i = 0; i < mydatatable.Rows.Count; i++) {
for (int j = 0; j <mydatatable .Columns .Count; j++)
{
excel.Cells[row , j+1] = mydatatable.Rows[i][j];
}
row++;
} excel.Visible = true;
}首先要添加COM组件 mircrosoft.excel 在添加引用的COM里面找 在改下连接字符串和数据库就可以用了。
public void Export(string ExcelFileName)
{
NameValueControl nv = new NameValueControl(m_FilePath);
string templatefile = nv.GetText("TempletePath");
if (templatefile == "")
{
throw new Exception("没有配置模板文件!");
}
if (!System.IO.File.Exists(templatefile))
throw new System.IO.FileNotFoundException("模板文件" + templatefile + "没有发现,请重新指定!");
//启动Excel
ExcelOpt.Application excelapp = new ExcelOpt.Application();
excelapp.Workbooks.Open(templatefile);
ExcelOpt.Workbook book = excelapp.Workbooks[1];
ExcelOpt.Worksheet sheet = book.Worksheets[1];
//输出表头
string cell;
ExcelOpt.Range range;
foreach (string key in m_HeadValuePair.Keys)
{
cell = nv.GetText(key);
if (cell != "")
{
range = sheet[GetExcelRowIndex(cell), GetExcelColumn(cell)];
range.Value = m_HeadValuePair[key];
}
} //输出表体
int startline = (int)(decimal)nv.GetDouble("m_StartLine");
int LineCountOfPage = (int)(decimal)nv.GetDouble("m_LineCountOfPage");
foreach (DataColumn dc in m_dt.Columns)
{
cell = nv.GetText(dc.Caption);
if (cell != "")
{
int i = startline;
foreach (DataRow r in m_dt.Rows)
{
range = sheet[i, cell];
range.Value = r[dc] == null ? "" : r[dc];
i++;
}
}
}
book.SaveAs(ExcelFileName);
excelapp.Visible = true; }