使用C#和Excel进行报表开发（八）－用程序绑定数据源

本文演示一个简单的办法，并使用程序将一个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如下图：