原文地址:http://www.cnblogs.com/dahuzizyd/archive/2007/04/11/CSharp_Excel_Report_Chart_All.html
整个系列均为转载,转载请注明出处
本文演示一个简单的办法,并使用程序将一个dataset中的内容填充到指定的格子中,目的是尽可能的通用,从而避免C#代码必须知道Excel文件中字段和内容的位置的情况。
先制作一个简单的Excel文件作为模板,为了防止要填充的Cell中的内容和标题的内容一样,所以要填充内容的Cell中的内容是“$” + 字段名(要和DataTable中的列名一致),效果如图:
![](https://i-blog.csdnimg.cn/blog_migrate/e7bdc8d00568742ca704368093eb6b5a.gif)
创建一个Winform程序,给窗体上添加两个按钮,代码分别为:
创建Xml:
提取xml并且加载到Excel模板上,再另存:
private
void button2_Click(
object sender, EventArgs e)
{
DataSet ds = new DataSet();
ds.ReadXml(Application.StartupPath + "\\ExcelBindingXml.xml");
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
Excel.Application m_objExcel = null;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
Excel._Workbook m_objBook = null;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
Excel.Sheets m_objSheets = null;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
Excel._Worksheet m_objSheet = null;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
Excel.Range m_objRange = null;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
object m_objOpt = System.Reflection.Missing.Value;
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
try
{
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
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));
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
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);
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
if ( m_objRange.Text.ToString().Replace("$","") == ds.Tables[0].Columns[col].ColumnName )
{
m_objRange.Value2 = dr[col].ToString();
}
}
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
}
}
}
m_objExcel.DisplayAlerts = false;
m_objBook.SaveAs(Application.StartupPath + "\\ExcelBindingXml.xls", m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
![](https://i-blog.csdnimg.cn/blog_migrate/587e34b10dcf5efbc0859b53470a2db3.gif)
}
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。
运行完成后,生成的Excel如下图:
![](https://i-blog.csdnimg.cn/blog_migrate/3df5bf26c4767a816f58d6432e009958.gif)