首先,我先说一下我要实现的功能:在一个datagridview中实现三个功能。
1)读取Excel中的表单,将其中的数据加载到datagridview中;
2)同样是加载数据到datagridview中,但是是通过手动添加;(例如:我通过textbox控件输入学生姓名"abc"然后点击button按钮,将"abc"添加到datagridview的 “姓名” 列中)
3)把datagridview中的数据导入Execl中
现在我贴一下我的代码:DataTable dt = new DataTable();
private void student_Load(object sender, EventArgs e)
{
dt.Columns.Add(new DataColumn("编号", typeof(string)));
dt.Columns.Add(new DataColumn("姓名", typeof(string)));
}
private void btnReadExcel_Click(object sender, EventArgs e)//读取Excel
{
OpenFileDialog fd = new OpenFileDialog();
fd.Filter = "表格|*.xls";
if (fd.ShowDialog() == DialogResult.OK)
{
string strPath = fd.FileName;
dt = Common.ExcelXmlforBS.ReadExcel(strPath);//调用读取Excel的方法
dgvWorkReport.DataSource = dt;
}
}
private void btnAdd_Click(object sender, EventArgs e)//手动添加
{
string stuNO = txtstuNO.Text.Trim();
string stuName = txtstuName.Text.Trim();
dt.Rows.Add(stuNO,stuName);
dgvWorkReport.DataSource = dt;
}
private void btnWriteExcel_Click(object sender, EventArgs e)//写Excel
{
Common.ExcelXmlforBS.WriteExcel(dgvWorkReport);//调用方法
}读写Excel代码public static DataTable ReadExcel(string strPath)//读Excel
{
string strCon = "provider=microsoft.jet.oledb.4.0;data source=" + strPath + ";extended properties=excel 8.0";
OleDbConnection Con = new OleDbConnection(strCon);
Con.Open();
string strSql = "select * from[Sheet1$]";
OleDbCommand Cmd = new OleDbCommand(strSql, Con);
OleDbDataAdapter da = new OleDbDataAdapter(Cmd);
DataSet ds = new DataSet();
da.Fill(ds);
Con.Close();
return ds.Tables[0];
}写Excel的太长了,参考http://chenxu4277.blog.163.com/blog/static/79946502008018112028280/里的第一种方法现在提问:
1)写Excel时如何设置列文本属性,例如stuNO列为学号,假定为"001"现在生成Excel后对应显示为"1",使用什么方法才能显示为"001"?
2)当前程序在点击btnAdd按钮后,可以将txtstuNO和txtstuName中的文本显示在datagridview中,但在写Excel时应使用什么方法才能将列名一同写入?
int currentcolumnindex = 1;
Excel.ApplicationClass Mylxls= new Microsoft.Office.Interop.Excel.ApplicationClass();
Mylxls.Cells[1, currentcolumnindex] = dgv.Columns[i].HeaderText;
这样吗?可是不行。
3)读取Excel时无法产生添加的列名?该怎样实现?
例如:Excel表如下:
001 abc
002 aaa
003 bbb
加载到datagridview后显示同上,但我想要的格式应为:
stuNO stuName
001 abc
002 aaa
003 bbb
注:若使用btnAdd按钮可以实现上述格式
小弟新手,还望各位大大多多指点,不胜感激~~~~~~~拜托,救急,哪位知道快点告诉我呀!!!
1)读取Excel中的表单,将其中的数据加载到datagridview中;
2)同样是加载数据到datagridview中,但是是通过手动添加;(例如:我通过textbox控件输入学生姓名"abc"然后点击button按钮,将"abc"添加到datagridview的 “姓名” 列中)
3)把datagridview中的数据导入Execl中
现在我贴一下我的代码:DataTable dt = new DataTable();
private void student_Load(object sender, EventArgs e)
{
dt.Columns.Add(new DataColumn("编号", typeof(string)));
dt.Columns.Add(new DataColumn("姓名", typeof(string)));
}
private void btnReadExcel_Click(object sender, EventArgs e)//读取Excel
{
OpenFileDialog fd = new OpenFileDialog();
fd.Filter = "表格|*.xls";
if (fd.ShowDialog() == DialogResult.OK)
{
string strPath = fd.FileName;
dt = Common.ExcelXmlforBS.ReadExcel(strPath);//调用读取Excel的方法
dgvWorkReport.DataSource = dt;
}
}
private void btnAdd_Click(object sender, EventArgs e)//手动添加
{
string stuNO = txtstuNO.Text.Trim();
string stuName = txtstuName.Text.Trim();
dt.Rows.Add(stuNO,stuName);
dgvWorkReport.DataSource = dt;
}
private void btnWriteExcel_Click(object sender, EventArgs e)//写Excel
{
Common.ExcelXmlforBS.WriteExcel(dgvWorkReport);//调用方法
}读写Excel代码public static DataTable ReadExcel(string strPath)//读Excel
{
string strCon = "provider=microsoft.jet.oledb.4.0;data source=" + strPath + ";extended properties=excel 8.0";
OleDbConnection Con = new OleDbConnection(strCon);
Con.Open();
string strSql = "select * from[Sheet1$]";
OleDbCommand Cmd = new OleDbCommand(strSql, Con);
OleDbDataAdapter da = new OleDbDataAdapter(Cmd);
DataSet ds = new DataSet();
da.Fill(ds);
Con.Close();
return ds.Tables[0];
}写Excel的太长了,参考http://chenxu4277.blog.163.com/blog/static/79946502008018112028280/里的第一种方法现在提问:
1)写Excel时如何设置列文本属性,例如stuNO列为学号,假定为"001"现在生成Excel后对应显示为"1",使用什么方法才能显示为"001"?
2)当前程序在点击btnAdd按钮后,可以将txtstuNO和txtstuName中的文本显示在datagridview中,但在写Excel时应使用什么方法才能将列名一同写入?
int currentcolumnindex = 1;
Excel.ApplicationClass Mylxls= new Microsoft.Office.Interop.Excel.ApplicationClass();
Mylxls.Cells[1, currentcolumnindex] = dgv.Columns[i].HeaderText;
这样吗?可是不行。
3)读取Excel时无法产生添加的列名?该怎样实现?
例如:Excel表如下:
001 abc
002 aaa
003 bbb
加载到datagridview后显示同上,但我想要的格式应为:
stuNO stuName
001 abc
002 aaa
003 bbb
注:若使用btnAdd按钮可以实现上述格式
小弟新手,还望各位大大多多指点,不胜感激~~~~~~~拜托,救急,哪位知道快点告诉我呀!!!
加载到datagridview后显示同上,但我想要的格式应为:
stuNO stuName
001 abc
002 aaa
003 bbb
注:若使用btnAdd按钮可以实现上述格式
应为:
加载到datagridview后显示同上,但我想要的格式应为:
编号 姓名
001 abc
002 aaa
003 bbb
注:若使用btnAdd按钮可以实现上述格式
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using Excel;namespace ImportExportToExcel
{
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>
private 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
}}