Question

C# 。。100分求高手弄一个简单的小程序。。。

要求：
环境：VS2005.
功能：在一个窗体上放两个按钮，btn_out,btn_in,前者用于将SQL数据表中的数据导出到EXCEL,后者用于将EXCEL中的数据插入到数据表。

附加：只要WinForm的。

最好是明天8点之前能发给我，辛苦了，谢谢了！
我要的是程序，不是代码呀。。。做好了加我发给我啊。。。谢谢了。

Answer 1

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Reflection;

namespace DataOutAndIn
{
/// <summary>
/// OutPutExcel 导出为EXCEL
/// </summary>
public class OutPutExcel
{
private Excel.Application oXL;
private Excel._Workbook oWB;
private Excel._Worksheet oSheet;
private Excel.Range oRng;
private System.Data.DataTable dt;

private System.Windows.Forms.SaveFileDialog SFD = new SaveFileDialog();

public OutPutExcel()
{
this.dt = new System.Data.DataTable();
}
/// <summary>
/// 快速导出Excel，直接在保存对话框中设好你要保存的路径和名字即可
/// </summary>
/// <param name="dtDataTable">用户指定的数据表</param>
public void FastOutPutExcel(System.Data.DataTable dtDataTable)
{
this.dt = dtDataTable;
try
{

this.SFD.Filter =" Excel文件 (*.xls)|*.xls";
this.SFD.FilterIndex = 1;
this.SFD.FileName = this.dt.TableName.ToString()+".xls";
this.SFD.RestoreDirectory = false;
if(this.SFD.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
#region///////////////////////////////////2007.05.23从条件外移入/////////////////////////////////////
//启动Excel,并获取应用程序实例
try
{
oXL = new Excel.Application();
}
catch(Exception exp)
{
MessageBox.Show(exp.Message);
}

oXL.Visible = false;

//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));

oSheet = (Excel._Worksheet)oWB.ActiveSheet;

//把表的列名加载进excel的第一行
for(int i = 0;i<this.dt.Columns.Count;i++)
{

oSheet.Cells[1,i+1] = this.dt.Columns[i].ColumnName.ToString();

}

//设置第一行标题的字体格式为黑体居中
// oSheet.get_Range("A1", "D1").Font.Bold = true;
// oSheet.get_Range("A1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
//

//创建一个二维数组把表中的数据读进来等待进行向excel加载数据的操作
string[,] strCellText = new string[this.dt.Rows.Count,this.dt.Columns.Count];
try
{
for(int i = 0;i < this.dt.Rows.Count;i++)
{
for(int j = 0;j < this.dt.Columns.Count;j++)
{
strCellText[i,j] = this.dt.Rows[i][j].ToString();
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
string ExcelColumLable = this.GetCellNameLable((int)(this.dt.Columns.Count/26))+this.GetCellNameLable((int)(this.dt.Columns.Count%26));
string max = ExcelColumLable+"1";
int row = this.dt.Rows.Count +1;
ExcelColumLable += row.ToString();
#endregion///////////////////////////////////////////////////////////////////////////////////////////////
oSheet.get_Range("A2", ExcelColumLable).Value2 = strCellText;
oRng = oSheet.get_Range("A1", max);
oRng.EntireColumn.AutoFit();

oXL.Visible = false;
oXL.UserControl = true;
oSheet.SaveAs(this.SFD.FileName,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);

try
{
oXL.Workbooks.Close();
oXL.Quit();
oXL = null;
}
catch(Exception exp)
{
MessageBox.Show(exp.Message);
}
}
// else
// {
// try
// {
// oXL.Workbooks.Close();
// oXL.Quit();
// oXL = null;
// }
// catch(Exception exp)
// {
// MessageBox.Show(exp.Message+"测试");
// }
// }

}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source );

MessageBox.Show( errorMessage, "Error" );
}
}
/// <summary>
/// 快速导出Excel，直接在保存对话框中设好你要保存的路径和名字即可，最多只导出二十六列以内的表
/// </summary>
/// <param name="dtDataTable">用户指定的数据表</param>
public void FastOutPutExcelIn26Colunms(System.Data.DataTable dtDataTable)
{
this.dt = dtDataTable;
try
{
this.SFD.Filter =" Excel文件 (*.xls)|*.xls";
this.SFD.FilterIndex = 1;
this.SFD.FileName = this.dt.TableName.ToString()+".xls";
this.SFD.RestoreDirectory = false;
if(this.SFD.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
#region/////////////////////////////////////2007.05.23从条件语句外移入/////////////////////////////////////////////////
//启动Excel并获取应用程序实例
try
{
oXL = new Excel.Application();
}
catch(Exception exp)
{
MessageBox.Show(exp.Message);
}

oXL.Visible = false;

//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));

oSheet = (Excel._Worksheet)oWB.ActiveSheet;

//把表的列名加载进excel的第一行
for(int i = 0;i<this.dt.Columns.Count;i++)
{

oSheet.Cells[1,i+1] = this.dt.Columns[i].ColumnName.ToString();

}

//创建一个二维数组把表中的数据读进来等待进行向excel加载数据的操作
string[,] strCellText = new string[this.dt.Rows.Count,this.dt.Columns.Count];
try
{
for(int i = 0;i < this.dt.Rows.Count;i++)
{
for(int j = 0;j < this.dt.Columns.Count;j++)
{
strCellText[i,j] = this.dt.Rows[i][j].ToString();
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
string ExcelColumLable = this.GetCellNameLable((int)(this.dt.Columns.Count%26));
string max = ExcelColumLable+"1";
int row = this.dt.Rows.Count +1;
ExcelColumLable += row.ToString();
#endregion/////////////////////////////////////////////////////////////////////////////////////////
oSheet.get_Range("A2", ExcelColumLable).Value2 = strCellText;
oRng = oSheet.get_Range("A1", max);
oRng.EntireColumn.AutoFit();

oXL.Visible = false;
oXL.UserControl = true;
oSheet.SaveAs(this.SFD.FileName,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);

try
{
oXL.Workbooks.Close();
oXL.Quit();
oXL = null;
}
catch(Exception exp)
{
MessageBox.Show(exp.Message);
}
}
// else
// {
// try
// {
// oXL.Workbooks.Close();
// oXL.Quit();
// oXL = null;
// }
// catch(Exception exp)
// {
// MessageBox.Show(exp.Message);
// }
// }
}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source );

MessageBox.Show( errorMessage, "Error" );
}
}
/// <summary>
/// 逐条导出Excel，它将打开一个Excel表格并把指定的表内数据导出到这个Excel进程中来
/// </summary>
/// <param name="dtDataTable">用户指定的数据表</param>
public void SlowOutPutExcel(System.Data.DataTable dtDataTable)
{
this.dt = dtDataTable;
try
{
//启动EXCEL，并获取应用程序实例
oXL = new Excel.Application();
oXL.Visible = true;

//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;

//把表的列名加载进excel的第一行
for(int i = 0;i<this.dt.Columns.Count;i++)
{

oSheet.Cells[1,i+1] = this.dt.Columns[i].ColumnName.ToString();

}

//设置第一行标题的字体格式为黑体居中
// oSheet.get_Range("A1", "D1").Font.Bold = true;
// oSheet.get_Range("A1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

try
{
for(int i = 0;i < this.dt.Rows.Count;i++)
{
for(int j = 0;j < this.dt.Columns.Count;j++)
{
oSheet.Cells[i+2,j+1] = this.dt.Rows[i][j].ToString();
}
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
string ExcelColumLable = this.GetCellNameLable((int)(this.dt.Columns.Count/26))+this.GetCellNameLable((int)(this.dt.Columns.Count%26));
string max = ExcelColumLable+"1";

oRng = oSheet.get_Range("A1", max);
oRng.EntireColumn.AutoFit();

oXL.Visible = true;
oXL.UserControl = true;
}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source );

MessageBox.Show( errorMessage, "Error" );
}
}
private string GetCellNameLable(int index)
{
string lable = "";
switch(index)
{
case 1:
lable = "A";
break;
case 2:
lable = "B";
break;
case 3:
lable = "C";
break;
case 4:
lable = "D";
break;
case 5:
lable = "E";
break;
case 6:
lable = "F";
break;
case 7:
lable = "G";
break;
case 8:
lable = "H";
break;
case 9:
lable = "I";
break;
case 10:
lable = "J";
break;
case 11:
lable = "K";
break;
case 12:
lable = "L";
break;
case 13:
lable = "M";
break;
case 14:
lable = "N";
break;
case 15:
lable = "O";
break;
case 16:
lable = "P";
break;
case 17:
lable = "Q";
break;
case 18:
lable = "R";
break;
case 19:
lable = "S";
break;
case 20:
lable = "T";
break;
case 21:
lable = "U";
break;
case 22:
lable = "V";
break;
case 23:
lable = "W";
break;
case 24:
lable = "X";
break;
case 25:
lable = "Y";
break;
case 0:
lable = "Z";
break;
default :
break;
}
return lable;

}
}
}
-------------------------------------------------------------
本程序在进行10到26进制转换时,做得不好.你可以进行修改使用.
不过,现在这个,在上百列之内是不会有问题的.

你可能在使用时需要引用excel和office.

如果想要导入到数据库中去.一般的思路是先把excel当成一个数据库进行查询,再把查询到的数据写入目标库.这个过程比导出简单多了.

Answer 2

1 public class ImportExportToExcel
2 {
3 private string strConn ;
4
5 private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
6 private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();
7
8 public ImportExportToExcel()
9 {
10 //
11 // TODO: 在此处添加构造函数逻辑
12 //
13 this.openFileDlg.DefaultExt = "xls";
14 this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";
15
16 this.saveFileDlg.DefaultExt="xls";
17 this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";
18
19 }
20
21 从Excel文件导入到DataSet#region 从Excel文件导入到DataSet
22 // /// <summary>
23 // /// 从Excel导入文件
24 // /// </summary>
25 // /// <param name="strExcelFileName">Excel文件名</param>
26 // /// <returns>返回DataSet</returns>
27 // public DataSet ImportFromExcel(string strExcelFileName)
28 // {
29 // return doImport(strExcelFileName);
30 // }
31 /**//// <summary>
32 /// 从选择的Excel文件导入
33 /// </summary>
34 /// <returns>DataSet</returns>
35 public DataSet ImportFromExcel()
36 {
37 DataSet ds=new DataSet();
38 if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
39 ds=doImport(openFileDlg.FileName);
40 return ds;
41 }
42 /**//// <summary>
43 /// 从指定的Excel文件导入
44 /// </summary>
45 /// <param name="strFileName">Excel文件名</param>
46 /// <returns></returns>
47 public DataSet ImportFromExcel(string strFileName)
48 {
49 DataSet ds=new DataSet();
50 ds=doImport(strFileName);
51 return ds;
52 }
53 /**//// <summary>
54 /// 执行导入
55 /// </summary>
56 /// <param name="strFileName">文件名</param>
57 /// <returns>DataSet</returns>
58 private DataSet doImport(string strFileName)
59 {
60 if (strFileName=="") return null;
61
62 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
63 "Data Source=" + strFileName + ";" +
64 "Extended Properties=Excel 8.0;";
65 OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
66
67 DataSet ExcelDs = new DataSet();
68 try
69 {
70 ExcelDA.Fill(ExcelDs, "ExcelInfo");
71
72 }
73 catch(Exception err)
74 {
75 System.Console.WriteLine( err.ToString() );
76 }
77 return ExcelDs;
78
79
80
81 }
82 #endregion
83
84 从DataSet到出到Excel#region 从DataSet到出到Excel
85 /**//// <summary>
86 /// 导出指定的Excel文件
87 /// </summary>
88 /// <param name="ds">要导出的DataSet</param>
89 /// <param name="strExcelFileName">要导出的Excel文件名</param>
90 public void ExportToExcel(DataSet ds,string strExcelFileName)
91 {
92 if (ds.Tables.Count==0 || strExcelFileName=="") return;
93 doExport(ds,strExcelFileName);
94
95
96 }
97 /**//// <summary>
98 /// 导出用户选择的Excel文件
99 /// </summary>
100 /// <param name="ds">DataSet</param>
101 public void ExportToExcel(DataSet ds)
102 {
103 if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
104 doExport(ds,saveFileDlg.FileName);
105
106 }
107 /**//// <summary>
108 /// 执行导出
109 /// </summary>
110 /// <param name="ds">要导出的DataSet</param>
111 /// <param name="strExcelFileName">要导出的文件名</param>
112 private void doExport(DataSet ds,string strExcelFileName)
113 {
114
115 Excel.Application excel= new Excel.Application();
116
117 // Excel.Workbook obj=new Excel.WorkbookClass();
118 // obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
119
120 int rowIndex=1;
121 int colIndex=0;
122
123 excel.Application.Workbooks.Add(true);
124
125
126 System.Data.DataTable table=ds.Tables[0] ;
127 foreach(DataColumn col in table.Columns)
128 {
129 colIndex++;
130 excel.Cells[1,colIndex]=col.ColumnName;
131 }
132
133 foreach(DataRow row in table.Rows)
134 {
135 rowIndex++;
136 colIndex=0;
137 foreach(DataColumn col in table.Columns)
138 {
139 colIndex++;
140 excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
141 }
142 }
143 excel.Visible=false;
144 excel.Sheets[0] = "sss";
145 excel.ActiveWorkbook.SaveAs(strExcelFileName+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
146
147
148 //wkbNew.SaveAs strBookName
149
150
151 //excel.Save(strExcelFileName);
152 excel.Quit();
153 excel=null;
154
155 GC.Collect();//垃圾回收
156 }
157 #endregion

Answer 3

**********************Excl导入到GridView*********************
创建一个GridView，将Server.MapPath路径下的东西导入

/// <summary>
/// 读取Excel数据的代码
/// Server.MapPath存储的是路径
/// </summary>
/// <returns></returns>
private DataSet CreateDataSource()
{
string strCon;
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("DB/Employee.xls") + "; Extended Properties=Excel 8.0;";
OleDbConnection olecon = new OleDbConnection(strCon);
OleDbDataAdapter myda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strCon);
DataSet myds = new DataSet();
try
{
myda.Fill(myds);
}
catch (OleDbException ex)
{
string strError = ex.ToString();
Response.Write(strError);
}
return myds;
}

***********************GridView 数据导出到Excl**********************
先得建立一个GridView
请在页面中加上 EnableEventValidation="false"
如：<%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation="false" CodeFile="Default.aspx.cs" Inherits="_Default" %>

private void Export(string FileType, string FileName)
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
Response.ContentType = FileType;
this.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
grdView.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}

public override void VerifyRenderingInServerForm(Control control)
{
}

protected void BtnExport_Click(object sender, EventArgs e)
{
Export("application/ms-Excel", "Employee.xls");
}

Answer 4

按钮一按就弹起来（比如电脑） 那种按钮叫什么开关
按钮一按不弹起来（比如小灯） 又叫什么开关
问下 谢谢