1. Set the cell's name as same as the ColumnName of the DataSet column：
Insert - Name - Define
Notice: The name you defiend to Excel's cells must be same as the ColumnName of the DataSet columns, otherwise, the special data will not be filled!
2.Code following:
1
2 protected void Button2_Click(object sender, EventArgs e)
3
{
4
int StartIndex = 3; //the first row to be filled & the start index is 1 not 0 in COM programming
5 string path = @"D:/test1.xls";
6 Excel.ApplicationClass app = new Microsoft.Office.Interop.Excel.ApplicationClass();
7 Excel.Workbooks oWorkBooks = app.Workbooks;
8 Excel.Sheets sheets = null;
9
10 try
11
{
12 Excel.Workbook oWorkBook = oWorkBooks.Open(path, Type.Missing, (object)false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
13 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
14 sheets= oWorkBook.Worksheets;
15 Excel.Worksheet oWorkSheet = (Excel.Worksheet)sheets.get_Item(1);
16
17 DataSet dataSet = GetDataSet(); //Get the DataSource which will be filled into Excel sheet
18
19 int RowCount = dataSet.Tables[0].Rows.Count;
20 int ColumnCount = dataSet.Tables[0].Columns.Count;
21 Excel.Range NameRange = null;
22 Excel.Range FillRange = null;
23 for (int i = 0; i < RowCount; i++)
24 {
25 for (int j = 0; j < ColumnCount; j++)
26 {
27 string ColumnName = dataSet.Tables[0].Columns[j].ColumnName;
28 try
29 {
30 NameRange = oWorkSheet.get_Range(ColumnName, Type.Missing);
31 FillRange = oWorkSheet.get_Range((alphabet)(NameRange.Column-1) + StartIndex.ToString(), Type.Missing);
32 FillRange.Value2 = dataSet.Tables[0].Rows[i][j];
33
34
}
35 catch (Exception)
36 {
37 //if can't find the Column in the Excel sheet, igorne the error and go on
38 }
39
40 }
41 StartIndex++;
42 }
43 oWorkBook.Save();
44 ClientScript.RegisterStartupScript(GetType(),Guid.NewGuid().ToString(), "<script>alert('Successful!');</script>");
45
46 }
47 catch (Exception error)
48 {
49 throw error;
50 }
51 finally
52 {
53 NAR(sheets);
54 NAR(oWorkBooks);
55 app.Quit();
56 NAR(app);
57 GC.Collect();
58 }
59
}
60
61 /**//// <summary>
62 /// Get The Data Source
63 /// </summary>
64 /// <returns>DataSet</returns> 65 private DataSet GetDataSet()
66 {
67 DataSet dataSet = new DataSet();
68 dataSet.Tables.Add();
69 DataTable dataTable = dataSet.Tables[0];
70
71 dataTable.Columns.Add("name");
72 dataTable.Columns.Add("age");
73 dataTable.Columns.Add("address");
74
75 DataRow dataRow = dataSet.Tables[0].NewRow();
76 dataRow[0] = "Jack";
77 dataRow[1] = 20;
78 dataRow[2] = "浙江杭州";
79 dataSet.Tables[0].Rows.Add(dataRow);
80
81 DataRow dataRow1 = dataSet.Tables[0].NewRow();
82 dataRow1[0] = "Smith";
83 dataRow1[1] = 30;
84 dataRow1[2] = "河北沧州";
85 dataSet.Tables[0].Rows.Add(dataRow1);
86
87 DataRow dataRow2 = dataSet.Tables[0].NewRow();
88 dataRow2[0] = "Hard";
89 dataRow2[1] = 40;
90 dataRow2[2] = "北京";
91 dataSet.Tables[0].Rows.Add(dataRow2);
92
93 return dataSet;
94 }
95
96 /**//// <summary>
97 /// enum for Excel column alphabet
98 /// </summary> 99 private enum alphabet
100 {
101 //and so on
102 A, B, C, D, E, F, G, H, I, J, K, L, M, N,O,P,Q,R,S,T,U,V,W,X,Y,Z
103 }
The reasult:
Good Luck!