C#做了一个Access数据库的查询系统，但想加一个导入导出excel表的菜单功能，大家帮帮忙
我用C#做了一个Access数据库的查询系统，想加上一个导入导出excel表到access数据库的功能，网上搜索了不少代码，但好多都用不成。
我是一个新手，大家帮帮忙，谢谢了
[解决办法]
/// <summary>
/// 获得指定Excel文件中的表名列表（返回一个ArrayList类型的表名集合）
/// </summary>
/// <param name="xlsPath"></param>
/// <returns></returns>
public ArrayList onGetSheets(string xlsPath)
{
ArrayList sheetsList=new ArrayList();
Excel.Application excel=new Excel.ApplicationClass();
Excel._Workbook xBk=null;
try
{
xBk=excel.Workbooks.Open(xlsPath,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
foreach (Excel._Worksheet xWk in xBk.Sheets)
{
sheetsList.Add(xWk.Name);
}
}
catch (Exception exc)
{
Yj_CommonVar.onShowMessagebox(exc);
}
finally
{
if (xBk!=null)
xBk.Close(false,xlsPath,Missing.Value);
excel.Workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
xBk=null;
excel=null;
}
return sheetsList;
}
/// <summary>
/// 从一个Excel中读取数据到一个DataTable中
/// </summary>
/// <param name="strFile">目标Excel路径</param>
/// <param name="sheetIndex">要读取数据的表的索引（第几个表）</param>
/// <param name="distinctFlage">是否读取惟一行（使用Distinct参数进行读取）True：使用Distinct参数进行读取</param>
/// <param name="conditionStr">过滤数据的字符串，不能包含Where，如果为null则不进行条件查询</param>
/// <param name="groupbyStr">分组字符串，不能包含Group by,如果为null则不进行分组</param>
/// <param name="orderbyStr">排序字符串，不能包含Order by，如果为null则不进行排序</param>
/// <returns></returns>
public DataTable onReadExcel(string strFile,int sheetIndex,bool distinctFlage,string conditionStr,string groupbyStr,string orderbyStr)
{
DataTable dt=null;
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ strFile +";"+"Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
OleDbConnection conn=null;
OleDbDataAdapter myCommand=null;
string strExcel="";
try
{
conn = new OleDbConnection(strConn);
conn.Open();
ArrayList sheetNames=this.onGetSheets(strFile);
if (distinctFlage)
strExcel="Select distinct * from ["+sheetNames[sheetIndex].ToString()+"$]"+(conditionStr==null?"":(" Where "+conditionStr));
else
strExcel="Select * from ["+sheetNames[sheetIndex].ToString()+"$]"+(conditionStr==null?"":(" Where "+conditionStr));
strExcel+=(groupbyStr==null?"":(" group by "+groupbyStr));//添加分组语句
strExcel+=(orderbyStr==null?"":(" order by "+orderbyStr));//添加排序语句
myCommand = new OleDbDataAdapter(strExcel, strConn);
dt=new DataTable(sheetNames[sheetIndex].ToString());
myCommand.Fill(dt);
}
catch (Exception exc)
{
Yj_CommonVar.onShowMessagebox(exc);
}
finally
{
if (conn!=null)
{
conn.Close();
conn.Dispose();
}
}
return dt;
}
才10分，就给个读的吧，^_^
[解决办法]
public class DataToExcel
{
private DataSet ds;
private System.Data.DataTable table;
private System.Data.DataTable table1;
Excel.Application excel;
ArrayList alist1;
ArrayList alist2;
int rowIndex = 1;
int colIndex = 0;
int index = 0;
//不带参数构造函数
public DataToExcel() { }
//带参数构造函数
public DataToExcel(DataSet ds)
{
this.ds = ds;
excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
table = ds.Tables[0];
table1 = new System.Data.DataTable();
alist1 = new ArrayList();
alist2 = new ArrayList();
}
//添加字段方法
public void addColumn(String columnName, String column, int width, HorizontalAlignment horizontalAlignment)
//columnName表示新字段名，column表示dataset字段名，width表示字段宽度
{
table1.Columns.Add(columnName);
alist1.Add(column);
alist2.Add(columnName);
//获得字段索引
// int index = table.Columns.IndexOf(column);
index++;
//设置字段宽度
((Excel.Range)excel.Columns[index, Type.Missing]).ColumnWidth = width;
//设置字段对齐方式
switch (horizontalAlignment)
{
case HorizontalAlignment.Left:
horizontalAlignment = (HorizontalAlignment)(Excel.XlHAlign.xlHAlignLeft);
break;
case HorizontalAlignment.Right:
horizontalAlignment = (HorizontalAlignment)(Excel.XlHAlign.xlHAlignRight);
break;
default:
horizontalAlignment = (HorizontalAlignment)(Excel.XlHAlign.xlHAlignCenter);
break;
}
((Excel.Range)excel.Columns[index, Type.Missing]).HorizontalAlignment = horizontalAlignment;
}
//添加数据方法
public void addData()
{
//从table中copy数据到table1中
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table1.NewRow();
for (int n = 0; n < alist1.Count; n++)
{
String str1 = (String)alist1[n];
String str2 = (String)alist2[n];
row[str2] = table.Rows[i][str1];
}
table1.Rows.Add(row);
}
//将所得到的表的列名,赋值给单元格
foreach (DataColumn col in table1.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
//同样方法处理数据
foreach (DataRow row in table1.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table1.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString().Trim();
}
}
//不可见,即后台处理
excel.Visible = true;
// excel.Quit();
excel = null;
GC.Collect();//垃圾回收
}
public void AddAllData()
{
//将所得到的表的列名,赋值给单元格
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
//同样方法处理数据
foreach (DataRow row in table.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
//不可见,即后台处理
excel.Visible = true;
// excel.Quit();
excel = null;
GC.Collect();//垃圾回收
}
}
