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

　　导出至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;
        }
}