C#做了一个Access数据库的查询系统,但想加一个导入导出excel表的菜单功能,大家帮帮忙 我用C#做了一个Access数据库的查询系统,想加上一个导入导出excel表到access数据库的功能,网上搜索了不少代码,但好多都用不成。我是一个新手,大家帮帮忙,谢谢了 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 俺想用代码来实现,而不是在Access中用导入按钮,谢谢! 在菜单的Click中写了: OpenFileDialog OFD = new OpenFileDialog();//启动打开对话框 OFD.Filter = "Excel文档(*.xls)|*.xls";//只读取xls文件,如果后面再加,使用"Excel文档(*.xls)|*.xls|Word文档(*.doc)|*.doc|文本文档(*.txt)|*.txt" OFD.FilterIndex = 1; OFD.Title = "请选择要导入的文件"; OFD.Multiselect = false; string[] excel_paths = OFD.FileNames;---------------------------------下来怎么做??谢谢 /// <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分,就给个读的吧,^_^ 把DataTable中的数据写到数据库中就不用我说了吧 我想要WinForm编程的,不是ASP.Net啊,谢谢!! 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();//垃圾回收 } } 怎么定义传入DLL的SAFEARRAY*参数 用C#语音写一个带指针的时钟 treeview中修改修改的节点的数据怎么存到数据库 c# listbox焦点 用户自定义控件问题!急... c# 数据库未开启 将更新数据写入事务中 ,怎样在下次操作程序时看到这些未提交的数据 ,急急急!!,希望与我联系 修改GridControl控件中的值如何更新数据库中内容 文件下载操作的怪问题,请教大家,谢谢~~! 怎么用wmi的win32_printjob获取打印页数 用C#写一个简单的QQ聊天工具 多个panel的控制问题,大家帮忙看看,50分! 大哥们,救一下吧!请教一个闷了我好多时间的问题?
OpenFileDialog OFD = new OpenFileDialog();//启动打开对话框
OFD.Filter = "Excel文档(*.xls)|*.xls";//只读取xls文件,如果后面再加,使用"Excel文档(*.xls)|*.xls|Word文档(*.doc)|*.doc|文本文档(*.txt)|*.txt"
OFD.FilterIndex = 1;
OFD.Title = "请选择要导入的文件";
OFD.Multiselect = false;
string[] excel_paths = OFD.FileNames;---------------------------------
下来怎么做??谢谢
/// 获得指定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分,就给个读的吧,^_^
{
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();//垃圾回收 } }