本文演示一个简单的办法，并使用程序将一个dataset中的内容填充到指定的格子中，目的是尽可能的通用，从而避免C#代码必须知道Excel文件中字段和内容的位置的情况。
先制作一个简单的Excel文件作为模板，为了防止要填充的Cell中的内容和标题的内容一样，所以要填充内容的Cell中的内容是“$” + 字段名(要和DataTable中的列名一致），效果如图：
创建一个Winform程序，给窗体上添加两个按钮，代码分别为：
创建Xml：
private void button1_Click(object sender, EventArgs e)
{
DataColumn dcName = new DataColumn("name", typeof(string)); DataColumn dcAge = new DataColumn("age", typeof(int)); DataColumn dcMemo = new DataColumn("memo", typeof(string)); DataTable dt = new DataTable(); dt.Columns.Add(dcName); dt.Columns.Add(dcAge); dt.Columns.Add(dcMemo); DataRow dr = dt.NewRow(); dr["name"] = "dahuzizyd"; dr["age"] = "20"; dr["memo"] = "dahuzizyd.cnblogs.com"; dt.Rows.Add(dr); dt.AcceptChanges(); DataSet ds = new DataSet(); ds.Tables.Add(dt); ds.WriteXml(Application.StartupPath +"\\ExcelBindingXml.xml");
}
提取xml并且加载到Excel模板上，再另存：
private void button2_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); ds.ReadXml(Application.StartupPath + "\\ExcelBindingXml.xml"); Excel.Application m_objExcel = null; Excel._Workbook m_objBook = null; Excel.Sheets m_objSheets = null; Excel._Worksheet m_objSheet = null; Excel.Range m_objRange = null; object m_objOpt = System.Reflection.Missing.Value; try
{
m_objExcel = new Excel.Application(); m_objBook = m_objExcel.Workbooks.Open(Application.StartupPath + "\\ExcelTemplate.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); m_objSheets = (Excel.Sheets)m_objBook.Worksheets; m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1)); foreach (DataRow dr in ds.Tables[0].Rows) { for (int col = 0; col < ds.Tables[0].Columns.Count; col++) { for (int excelcol = 1; excelcol < 8; excelcol++) { for (int excelrow = 1; excelrow < 5; excelrow++) { string excelColName = ExcelColNumberToColText(excelcol); m_objRange = m_objSheet.get_Range(excelColName + excelrow.ToString(), m_objOpt); if ( m_objRange.Text.ToString().Replace("$","") == ds.Tables[0].Columns[col].ColumnName ) { m_objRange.Value2 = dr[col].ToString();
} } } } } m_objExcel.DisplayAlerts = false; m_objBook.SaveAs(Application.StartupPath + "\\ExcelBindingXml.xls", m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { m_objBook.Close(m_objOpt, m_objOpt, m_objOpt); m_objExcel.Workbooks.Close(); m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); m_objBook = null; m_objExcel = null; GC.Collect(); } }
下面是一个辅助函数，主要是将整数的列序号转换到Excel用的以字母表示的列号，Excel最大列数为255。
private string ExcelColNumberToColText(int colNumber) { string colText = ""; int colTextLength = colNumber / 26; int colTextLast = colNumber % 26; if (colTextLast != 0) { switch (colTextLength) { case 0: break; case 1: colText = "A"; break; case 2: colText = "B"; break; case 3: colText = "C"; break; case 4: colText = "D"; break; case 5: colText = "E"; break; case 6: colText = "F"; break; case 7: colText = "G"; break; case 8: colText = "H"; break; case 9: colText = "I"; break; default: break; } } else { switch (colTextLength) { case 1: colText = ""; break; case 2: colText = "A"; break; case 3: colText = "B"; break; case 4: colText = "C"; break; case 5: colText = "D"; break; case 6: colText = "E"; break; case 7: colText = "F"; break; case 8: colText = "G"; break; case 9: colText = "H"; break; default: break; } } switch (colTextLast) { case 0:colText = colText + "Z"; break; case 1: colText = colText + "A"; break; case 2: colText = colText + "B"; break; case 3: colText = colText + "C"; break; case 4: colText = colText + "D"; break; case 5: colText = colText + "E"; break; case 6: colText = colText + "F"; break; case 7: colText = colText + "G"; break; case 8: colText = colText + "H"; break; case 9: colText = colText + "I"; break; case 10: colText = colText + "J"; break; case 11: colText = colText + "K"; break; case 12: colText = colText + "L"; break; case 13: colText = colText + "M"; break; case 14: colText = colText + "N"; break; case 15: colText = colText + "O"; break; case 16: colText = colText + "P"; break; case 17: colText = colText + "Q"; break; case 18: colText = colText + "R"; break; case 19: colText = colText + "S"; break; case 20: colText = colText + "T"; break; case 21: colText = colText + "U"; break; case 22: colText = colText + "V"; break; case 23: colText = colText + "W"; break; case 24: colText = colText + "X"; break; case 25: colText = colText + "Y"; break; default: break; } return colText; }
运行完成后，生成的Excel如下图：
