我现在想在C#应用程序中打开一个excel表并且显示其中的数据,而且可以对每一条记录进行编辑,编辑完成后可以将数据导出重新保存为excel表,请问winform控件用什么来显示excel中的数据,还有读取excel表需要用到哪些类?
解决方案 »
- 正则表达式
- 怎么用C#实现WORD文档的剥离?
- 我有一个程序出错了,有图片,帮忙看看怎么回事?
- SOS c#中如何把ListView中值转化问题? 急急!!!谢谢大哥们支持一下,帮助下小弟,谢谢了!~~~~~~~~~
- 分太多,散之!
- 请大家帮个忙,急!!!
- 我想在ListView中插入很多的数据,会让用户等待,如何实现弹出对话框显示进度?
- C#做一个类似流量监控的软件,绘制波形图的时候用到了这个函数,请问其中我标注问好的是什么意思?
- 急!!关于托管C++与C#动态库通讯的若干问题
- 请问C#(VC#.NET)中有没有代码补充,如delphi中按Ctrl+J?
- 大家好!我想用线程获取数据库数据,并添加在checkedListBox1中,但是运行后显示不了结果,谁知道什么原应吗
- VS2008的界面设计页面频繁刷新,并且每次刷新时间很长
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using Excel;namespace JGExam.BLL
{
public class ImportExportToExcel
{
private string strConn; private System.Windows.Forms.OpenFileDialog openFileDlg = new System.Windows.Forms.OpenFileDialog();
private System.Windows.Forms.SaveFileDialog saveFileDlg = new System.Windows.Forms.SaveFileDialog(); public ImportExportToExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
this.openFileDlg.DefaultExt = "xls";
this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls"; this.saveFileDlg.DefaultExt = "xls";
this.saveFileDlg.Filter = "Excel文件 (*.xls)|*.xls"; }
#region 从Excel文件导入到DataSet
// /// <summary>
// /// 从Excel导入文件
// /// </summary>
// /// <param name="strExcelFileName">Excel文件名</param>
// /// <returns>返回DataSet</returns>
// public DataSet ImportFromExcel(string strExcelFileName)
// {
// return doImport(strExcelFileName);
// }
/**/
/// <summary>
/// 从选择的Excel文件导入
/// </summary>
/// <returns>DataSet</returns>
public DataSet ImportFromExcel()
{
DataSet ds = new DataSet();
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
ds = doImport(openFileDlg.FileName);
return ds;
}
/**/
/// <summary>
/// 从指定的Excel文件导入
/// </summary>
/// <param name="strFileName">Excel文件名</param>
/// <returns></returns>
public DataSet ImportFromExcel(string strFileName)
{
DataSet ds = new DataSet();
ds = doImport(strFileName);
return ds;
}
/**/
/// <summary>
/// 执行导入
/// </summary>
/// <param name="strFileName">文件名</param>
/// <returns>DataSet</returns>
public DataSet doImport(string strFileName)
{
if (strFileName == "") return null; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strFileName + ";" +
"Extended Properties=Excel 8.0;";
OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn); DataSet ExcelDs = new DataSet();
try
{
ExcelDA.Fill(ExcelDs, "ExcelInfo"); }
catch (Exception err)
{
System.Console.WriteLine(err.ToString());
}
return ExcelDs; }
#endregion #region 从DataSet到出到Excel
/**/
/// <summary>
/// 导出指定的Excel文件
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的Excel文件名</param>
public void ExportToExcel(DataSet ds, string strExcelFileName)
{
if (ds.Tables.Count == 0 || strExcelFileName == "") return;
doExport(ds, strExcelFileName);
}
/**/
/// <summary>
/// 导出用户选择的Excel文件
/// </summary>
/// <param name="ds">DataSet</param>
public void ExportToExcel(DataSet ds)
{
if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
doExport(ds, saveFileDlg.FileName); }
/**/
/// <summary>
/// 执行导出
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的文件名</param>
private void doExport(DataSet ds, string strExcelFileName)
{ Excel.Application excel = new Excel.Application(); // Excel.Workbook obj=new Excel.WorkbookClass();
// obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null); int rowIndex = 1;
int colIndex = 0; excel.Application.Workbooks.Add(true);
System.Data.DataTable table = ds.Tables[0];
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 = false; excel.ActiveWorkbook.SaveAs(strExcelFileName + ".XLS",
Excel.XlFileFormat.xlExcel9795, null, null, false,
false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//wkbNew.SaveAs strBookName
//excel.Save(strExcelFileName);
excel.Quit();
excel = null; GC.Collect();//垃圾回收
}
#endregion #region 从XML导入到Dataset /**/
/// <summary>
/// 从选择的XML文件导入
/// </summary>
/// <returns>DataSet</returns>
public DataSet ImportFromXML()
{
DataSet ds = new DataSet();
System.Windows.Forms.OpenFileDialog openFileDlg = new System.Windows.Forms.OpenFileDialog();
openFileDlg.DefaultExt = "xml";
openFileDlg.Filter = "xml文件 (*.xml)|*.xml";
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
try { ds.ReadXml(openFileDlg.FileName, System.Data.XmlReadMode.ReadSchema); }
catch { }
return ds;
}
/**/
/// <summary>
/// 从指定的XML文件导入
/// </summary>
/// <param name="strFileName">XML文件名</param>
/// <returns></returns>
public DataSet ImportFromXML(string strFileName)
{
if (strFileName == "")
return null;
DataSet ds = new DataSet();
try { ds.ReadXml(strFileName, System.Data.XmlReadMode.ReadSchema); }
catch { }
return ds;
} #endregion #region 从DataSet导出到XML
/**/
/// <summary>
/// 导出指定的XML文件
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strXMLFileName">要导出的XML文件名</param>
public void ExportToXML(DataSet ds, string strXMLFileName)
{
if (ds.Tables.Count == 0 || strXMLFileName == "") return;
doExportXML(ds, strXMLFileName);
}
/**/
/// <summary>
/// 导出用户选择的XML文件
/// </summary>
/// <param name="ds">DataSet</param>
public void ExportToXML(DataSet ds)
{
System.Windows.Forms.SaveFileDialog saveFileDlg = new System.Windows.Forms.SaveFileDialog();
saveFileDlg.DefaultExt = "xml";
saveFileDlg.Filter = "xml文件 (*.xml)|*.xml";
if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
doExportXML(ds, saveFileDlg.FileName);
} /**/
/// <summary>
/// 执行导出
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的XML文件名</param>
private void doExportXML(DataSet ds, string strXMLFileName)
{
try
{ ds.WriteXml(strXMLFileName, System.Data.XmlWriteMode.WriteSchema); }
catch (Exception ex)
{ System.Windows.Forms.MessageBox.Show(ex.Message, "Errol"); }
} #endregion
}
}
当编辑之后就可以先更新DataSet,然后根据上面代码中的将数据保存到Excel的方法完成保存就可以了。