The common method for filling date into Excel cells from DataSet

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!