本文转自：http://hi.baidu.com/plj_/blog/item/6efe90a37053d2a7cbefd099.html
#region 导出Execl
private void sBtnExportToXLS_Click(object sender, EventArgs e)
{
string fileName = ShowSaveFileDialog("Microsoft Excel Document", "Microsoft Excel|*.xls");
if (fileName != "")
{
progressBarControl1.Visible = true;
ExportTo(new ExportXlsProvider(fileName));
OpenFile(fileName);
progressBarControl1.Visible = false;
}
}
private string ShowSaveFileDialog(string title, string filter)
{
SaveFileDialog dlg = new SaveFileDialog();
string name = Application.ProductName;
int n = name.LastIndexOf(".") + 1;
if (n > 0) name = name.Substring(n, name.Length - n);
dlg.Title = "导出为" + title;
dlg.FileName = name;
dlg.Filter = filter;
if (dlg.ShowDialog() == DialogResult.OK) return dlg.FileName;
return "";
}
private void ExportTo(IExportProvider provider)
{
Cursor currentCursor = Cursor.Current;
Cursor.Current = Cursors.WaitCursor;
this.FindForm().Refresh();
BaseExportLink link = gv1.CreateExportLink(provider);
(link as GridViewExportLink).ExpandAll = false;
link.Progress += new DevExpress.XtraGrid.Export.ProgressEventHandler(Export_Progress);
link.ExportTo(true);
provider.Dispose();
link.Progress -= new DevExpress.XtraGrid.Export.ProgressEventHandler(Export_Progress);
Cursor.Current = currentCursor;
}
private void Export_Progress(object sender, DevExpress.XtraGrid.Export.ProgressEventArgs e)
{
if (e.Phase == DevExpress.XtraGrid.Export.ExportPhase.Link)
{
progressBarControl1.Position = e.Position;
this.Update();
}
}
private void OpenFile(string fileName)
{
if (XtraMessageBox.Show("是否要打开该文件?", "导出为", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
try
{
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo.FileName = fileName;
process.StartInfo.Verb = "Open";
process.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal;
process.Start();
}
catch
{
DevExpress.XtraEditors.XtraMessageBox.Show(this, "Cannot find an application on your system suitable for openning the file with exported data.", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
progressBarControl1.Position = 0;
}
#endregion
#region 导入Execl
private void sBtn_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
// ofd.ShowDialog();
ofd.Title = "Excel文件";
ofd.FileName = "";
ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
ofd.Filter = "Excel文件(*.xls)|*.xls";
//文件有效性验证ValidateNames，验证用户输入是否是一个有效的Windows文件名
ofd.ValidateNames = true;
//验证路径有效性
ofd.CheckFileExists = true;
//验证文件有效性
ofd.CheckPathExists = true;
string strName = string.Empty;
if (ofd.ShowDialog() == DialogResult.OK)
{
strName = ofd.FileName;
}
if (strName == "")
{
MessageBox.Show("没有选择Excel文件，无法导入！");
return;
}
DataSet ds = GetDataFromExcelWithAppointSheetName(strName);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string monthnames= ds.Tables[0].Rows[i][0].ToString();
DateTime sdates = DateTime.Parse(ds.Tables[0].Rows[i][1].ToString());
DateTime edates = DateTime.Parse(ds.Tables[0].Rows[i][2].ToString());
int states;
switch (ds.Tables[0].Rows[i][3].ToString())
{
case "新建":
states = 0;
break;
}
int p= SqlData.ExecuteSql("insert into op_month(monthname,sdate,edate,state) values('"+monthnames+"','"+sdates+"','"+edates+"','"+states+"')");
if (p > 0)
{
}
}
InitDate();//重新加载数据库
}
private DataSet GetDataFromExcelWithAppointSheetName(string Path)
{
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Path + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
//返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
DataTable dtSheetName=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"Table"});
//包含excel中表名的字符串数组
string[]strTableNames=new string[dtSheetName.Rows.Count];
for(int k=0;k<dtSheetName.Rows.Count;k++)
{
strTableNames[k]=dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
OleDbDataAdapter da=null;
DataSet ds=new DataSet();
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel="select * from["+strTableNames[0]+"]";
da = new OleDbDataAdapter(strExcel, conn);
da.Fill(ds);
return ds;
}
#endregion
