使用C#和Excel进行报表开发(八)-用程序绑定数据源

    技术2022-05-11  75

    本文演示一个简单的办法,并使用程序将一个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 0break;                    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;                    defaultbreak;                }            }            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;                    defaultbreak;                }            }                        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;                                defaultbreak;            }            return colText;        } 运行完成后,生成的Excel如下图:  

    最新回复(0)