欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

WPF-将DataGrid控件中的数据导出到Excel

程序员文章站 2022-06-07 22:52:16
导出至Excel是非常常见,我们可以用很多类库,例如Aspose、NOPI、Interop,在这里我们使用微软自家的工具。我的WPF绑定的ObservableCollection集合。 该方法还不止这么多,还涉及到了1-27 = A-Z 的适配,如以下方法。 最后我们获取数据给方法,下面是设计 ......

  导出至excel是非常常见,我们可以用很多类库,例如aspose、nopi、interop,在这里我们使用微软自家的工具。我的wpf绑定的observablecollection<t>集合。

        public string excelexport(system.data.datatable dt, string title)
        {
            try
            {
                //创建excel
                microsoft.office.interop.excel.application excelapp = new microsoft.office.interop.excel.application();
                microsoft.office.interop.excel.workbook excelbook = excelapp.workbooks.add(system.type.missing);
                //创建工作表(即excel里的子表sheet) 1表示在子表sheet1里进行数据导出
                microsoft.office.interop.excel.worksheet excelsheet = (microsoft.office.interop.excel.worksheet)excelbook.worksheets[1];
                //如果数据中存在数字类型 可以让它变文本格式显示
                excelsheet.cells.numberformat = "@";
                //设置工作表名
                excelsheet.name = title;
                //设置sheet标题
                string start = "a1";
                string end = changeasc(dt.columns.count) + "1";
                microsoft.office.interop.excel.range _range = (microsoft.office.interop.excel.range)excelsheet.get_range(start, end);
                _range.merge(0);                     //单元格合并动作(要配合上面的get_range()进行设计)
                _range = (microsoft.office.interop.excel.range)excelsheet.get_range(start, end);
                _range.horizontalalignment = microsoft.office.interop.excel.xlhalign.xlhaligncenter;
                _range.font.size = 22; //设置字体大小
                _range.font.name = "宋体"; //设置字体的种类 
                excelsheet.cells[1, 1] = title;    //excel单元格赋值
                _range.entirecolumn.autofit(); //自动调整列宽
                //写表头
                for (int m = 1; m <= dt.columns.count; m++)
                {
                    excelsheet.cells[2, m] = dt.columns[m - 1].columnname.tostring();
                    start = "a2";
                    end = changeasc(dt.columns.count) + "2";
                    _range = (microsoft.office.interop.excel.range)excelsheet.get_range(start, end);
                    _range.font.size = 14; //设置字体大小
                    _range.font.name = "宋体"; //设置字体的种类  
                    _range.entirecolumn.autofit(); //自动调整列宽 
                    _range.horizontalalignment = microsoft.office.interop.excel.xlhalign.xlhaligncenter;
                }
                //写数据
                for (int i = 0; i < dt.rows.count; i++)
                {
                    for (int j = 1; j <= dt.columns.count; j++)
                    {
                        //excel单元格第一个从索引1开始
                        // if (j == 0) j = 1;
                        excelsheet.cells[i + 3, j] = dt.rows[i][j - 1].tostring();
                    }
                }
                //表格属性设置
                for (int n = 0; n < dt.rows.count + 1; n++)
                {
                    start = "a" + (n + 3).tostring();
                    end = changeasc(dt.columns.count) + (n + 3).tostring();
                    //获取excel多个单元格区域
                    _range = (microsoft.office.interop.excel.range)excelsheet.get_range(start, end);
                    _range.font.size = 12; //设置字体大小
                    _range.font.name = "宋体"; //设置字体的种类
                    _range.entirecolumn.autofit(); //自动调整列宽
                    _range.horizontalalignment = microsoft.office.interop.excel.xlhalign.xlhaligncenter; //设置字体在单元格内的对其方式 _range.entirecolumn.autofit(); //自动调整列宽 
                }
                excelapp.displayalerts = false; //保存excel的时候,不弹出是否保存的窗口直接进行保存 
                ////弹出保存对话框,并保存文件
                microsoft.win32.savefiledialog sfd = new microsoft.win32.savefiledialog();
                sfd.defaultext = ".xlsx";
                sfd.filter = "office 2007 file|*.xlsx|office 2000-2003 file|*.xls|所有文件|*.*";
                if (sfd.showdialog() == true)
                {
                    if (sfd.filename != "")
                    {
                        excelbook.saveas(sfd.filename);  //将其进行保存到指定的路径
                        system.windows.messagebox.show("导出文件已存储为: " + sfd.filename, "温馨提示");
                    }
                }
                //释放可能还没释放的进程
                excelbook.close();
                excelapp.quit();
                return sfd.filename;
            }
            catch
            {
                //system.windows.messagebox.show("导出文件保存失败,可能原因该文件已打开!", "警告!");
                return null;
            }
        }

该方法还不止这么多,还涉及到了1-27 = a-z 的适配,如以下方法。

        /// <summary>
        /// 获取当前列列名,并得到excel中对应的列
        /// </summary>
        /// <param name="count"></param>
        /// <returns></returns>
        private string changeasc(int count)
        {
            string ascstr = "";
            switch (count)
            {
                case 1:
                    ascstr = "a";
                    break;
                case 2:
                    ascstr = "b";
                    break;
                case 3:
                    ascstr = "c";
                    break;
                case 4:
                    ascstr = "d";
                    break;
                case 5:
                    ascstr = "e";
                    break;
                case 6:
                    ascstr = "f";
                    break;
                case 7:
                    ascstr = "g";
                    break;
                case 8:
                    ascstr = "h";
                    break;
                case 9:
                    ascstr = "i";
                    break;
                case 10:
                    ascstr = "j";
                    break;
                case 11:
                    ascstr = "k";
                    break;
                case 12:
                    ascstr = "l";
                    break;
                case 13:
                    ascstr = "m";
                    break;
                case 14:
                    ascstr = "n";
                    break;
                case 15:
                    ascstr = "o";
                    break;
                case 16:
                    ascstr = "p";
                    break;
                case 17:
                    ascstr = "q";
                    break;
                case 18:
                    ascstr = "r";
                    break;
                case 19:
                    ascstr = "s";
                    break;
                case 20:
                    ascstr = "t";
                    break;
                default:
                    ascstr = "u";
                    break;
            }
            return ascstr;
        }

最后我们获取数据给方法,下面是设计了数据转换到datatable的一个过程。

private void export(object sender, routedeventargs e)
        {
            datatable newtb = new datatable();
            observablecollection<xmodel.store_in> instore = this.datagrid.itemssource as observablecollection<xmodel.store_in>;
            list<xmodel.store_in> list = new list<xmodel.store_in>(instore.tolist());
              newtb = xtools.xhelper.datavalidation.copytodatatable<xmodel.store_in>(list);
             excelexport(newtb, "入库记录");
        }

 

 public static class datavalidation
    {
        public static datatable copytodatatable<t>(this ienumerable<t> array)
        {
            var ret = new datatable();
            foreach (propertydescriptor dp in typedescriptor.getproperties(typeof(t)))
                ret.columns.add(dp.name);
            foreach (t item in array)
            {
                var row = ret.newrow();
                foreach (propertydescriptor dp in typedescriptor.getproperties(typeof(t)))
                    row[dp.name] = dp.getvalue(item);
                ret.rows.add(row);
            }
            return ret;
        }
}