Question

c#.net完成数据到excel表的导入与导出

c#.net完成数据到excel表的导入与导出

要源代码~~~

一定要有注释

excel名字为 china
sql数据库表名 CNA

一定要有注释 谁的详细给谁分

Answer 1

下面代码从DataSet和excel的导入导出，具体从数据库到DataSet应该会把！
引用COM里面的Microsoft.Excel 11.0

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 2

导入和查询数据库的表差不多，很简单的。
给你写一下导出

绑定好的GridView。
string filename 就是导出是的文件名了。

给你二个我写的，你去比较一下这是用GridVeiw 直接导出到Excel。
===============第一个================
调用：
ExcelOut("application/ms-excel","事例.xls");
方法：
private void ExcelOut(string FillType, string FillName)
{
DataTable dt = new DataTable();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.AppendHeader("Content-Disposition","attachment;filename="+HttpUtility.UrlEncode(FillName,System.Text.Encoding.UTF8).ToString());
Response.ContentType = FillType;
this.EnableViewState = false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
GridView1.RenderControl(hw);
//GridView1.Rows[0].RenderControl
//GridView2.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
记得加上这个方法不然会报错
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}

=============第二个===============
这种直接用datatable 就可以了。
public void ExportExcel(DataTable dt)
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("ccc_mys.xls", System.Text.Encoding.UTF8).ToString());
Response.ContentType = "application / ms - excel";
string colHeaders = "", ls_item = "";

//定义表对象与行对象，同时用DataSet对其值进行初始化
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;

//取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列，加n
{
colHeaders += dt.Columns[i].Caption.ToString() + "\n";
}
else
{
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
}

}
// resp.Write(colHeaders);
Response.Write(colHeaders);
//向HTTP输出流中写入取得的数据信息

//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列，加n
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}

}
Response.Write(ls_item);
ls_item = "";

}
//resp.End();
Response.Flush();
Response.Close();
Response.End();
}

Answer 3

下面的函数作用，将DataTable导出到EXCEL文件：
private void DataTabletoExcel(System.Data.DataTable tmpDataTable,string strFileName)
{
if (tmpDataTable == null)
{
return;
}
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;

Excel.Application xlApp = new Excel.ApplicationClass();

xlApp.DefaultFilePath = "";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;

Excel.Workbook xlBook = xlApp.Workbooks.Add(true);

//将DataTable的列名导入Excel表第一行
foreach(DataColumn dc in tmpDataTable.Columns)
{
columnIndex ++;
xlApp.Cells[rowIndex,columnIndex] = dc.ColumnName;
}

//将DataTable中的数据导入Excel中
for(int i = 0;i<rowNum; i++)
{
rowIndex ++;
columnIndex = 0;
for (int j = 0;j<columnNum; j++)
{
columnIndex ++;
xlApp.Cells[rowIndex,columnIndex] = tmpDataTable.Rows[i][j].ToString();
}
}
xlBook.SaveCopyAs(strFileName + ".xls");
}

Answer 4

接助第3方控件几行代码就搞定了:

DevExpress.GridView

带有ExportToExcel(string fileName)方法， 而且导出的数据与Grid显示一个样。

楼上的代码也导出样式吗?

Answer 5

这个代码示例内容比较详细了，你参考一下C# Excel数据导入、导出