Пример #1
0
        public static void FHDataGrid_btn_导入Excel(string pageTittle, DataGrid dataGrid, List <FHFormLb> listFormResult, ref Microsoft.Office.Interop.Excel.Application application)
        {
            try
            {
                int rowDistance = 1;

                if (_excelother == null)
                {
                    //创建excel应用程序
                    _excelother = new Microsoft.Office.Interop.Excel.Application();
                }

                application = _excelother;

                //创建一个excel工作簿
                Microsoft.Office.Interop.Excel._Worksheet ws = new Microsoft.Office.Interop.Excel.WorksheetClass();

                _excelother.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(excelother_WorkbookBeforeClose);
                //启用
                _excelother.Application.Workbooks.Add(true);

                #region 生成数据

                int count = 0;

                List <string> tittleList = new List <string>();

                bool isWait = true;

                dataGrid.Dispatcher.BeginInvoke(new System.Action(() =>
                {
                    count = dataGrid.Columns.Count;

                    foreach (var item in dataGrid.Columns)
                    {
                        tittleList.Add(item.Header.ToString());
                    }
                    isWait = false;
                }));
                while (isWait)
                {
                    //生成小标题
                    for (int i = 1; i < count; i++)
                    {
                        _excelother.Cells[1 + rowDistance, i] = tittleList[i];
                    }
                }

                ////生成数据
                PropertyInfo[] propertyInes = typeof(FHFormLb).GetProperties();

                for (int x = 1; x < count; x++)
                {
                    for (int y = 1; y < listFormResult.Count; y++)
                    {
                        string columnName = tittleList[x];

                        string columnValue = string.Empty;

                        foreach (var property in propertyInes)
                        {
                            if (property.Name == columnName)
                            {
                                if (property.Name == "附件")
                                {
                                    columnName = listFormResult[y].是否有附件;
                                    break;
                                }
                                else
                                {
                                    columnValue = property.GetValue(listFormResult[y - 1], null).ToString();
                                    break;
                                }
                            }
                        }
                        _excelother.Cells[y + 1 + rowDistance, x] = columnValue;
                    }
                }

                #endregion

                #region 设置页面标题

                ws = (Microsoft.Office.Interop.Excel._Worksheet)_excelother.ActiveSheet;
                Microsoft.Office.Interop.Excel.Range rangTittle = null;
                rangTittle                     = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, count - 1]);
                rangTittle.MergeCells          = true;
                _excelother.Cells[1, 1]        = pageTittle;
                rangTittle.ColumnWidth         = 26;
                rangTittle.Font.Size           = 17;
                rangTittle.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

                rangTittle.Borders.LineStyle = XlLineStyle.xlContinuous;
                #endregion

                #region 单元格属性设置

                //设置标题属性
                for (int i = 1; i < count; i++)
                {
                    //设置标题单元格属性
                    Microsoft.Office.Interop.Excel.Range r = ws.get_Range(ws.Cells[1 + rowDistance, i], ws.Cells[1 + rowDistance, i]);
                    r.Borders.LineStyle   = XlLineStyle.xlContinuous;
                    r.Font.Size           = 13;
                    r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

                    string columnHeader = tittleList[i];
                    if (columnHeader.Contains("日期时间") || columnHeader.Contains("影响范围") || columnHeader.Contains("单位") || columnHeader.Contains("障碍设备") || columnHeader.Contains("线别"))
                    {
                        r.ColumnWidth = 20;
                    }
                }

                //数据属性
                for (int x = 1; x < count; x++)
                {
                    for (int y = 1; y < listFormResult.Count; y++)
                    {
                        //设置数据单元格属性
                        Microsoft.Office.Interop.Excel.Range r = ws.get_Range(ws.Cells[y + 1 + rowDistance, x], ws.Cells[y + 1 + rowDistance, x]);
                        r.Borders.LineStyle   = XlLineStyle.xlContinuous;
                        r.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                        r.RowHeight           = 26;
                    }
                }
                _excelother.Visible = true;

                #endregion
            }
            catch (Exception ex)
            {
                MethodLb.CreateLog(typeof(CommonMethod).FullName, "FHDataGrid_btn_导入Excel", ex.ToString());
            }
        }
Пример #2
0
        /// <summary>
        /// 将Datagrid导出到Excel
        /// </summary>
        /// <param name="tittle">工作簿的名称</param>
        /// <param name="datagrid">要导出的列表</param>
        public static void Excel_LeadingOut(DataGrid datagrid, Type type)
        {
            try
            {
                int rowDistance = 1;

                int columnDistance = 1;

                //创建excel应用程序
                Microsoft.Office.Interop.Excel.Application excelother = new Microsoft.Office.Interop.Excel.Application();
                //创建一个excel工作簿
                Microsoft.Office.Interop.Excel._Worksheet ws = new Microsoft.Office.Interop.Excel.WorksheetClass();
                //启用
                excelother.Application.Workbooks.Add(true);

                #region 设置页面标题

                ws = (Microsoft.Office.Interop.Excel.Worksheet)excelother.ActiveSheet;
                Microsoft.Office.Interop.Excel.Range rangTittle = null;


                #endregion

                #region 生成数据

                //生成数据
                PropertyInfo[] propertyInes = type.GetProperties();
                for (int x = 0; x < datagrid.Columns.Count; x++)
                {
                    for (int y = 0; y < datagrid.Items.Count; y++)
                    {
                        var strText = propertyInes[x].GetValue(datagrid.Items[y], null);
                        if (strText == null)
                        {
                            continue;
                        }
                        excelother.Cells[y + rowDistance + 1, x + columnDistance] = strText.ToString();
                    }
                }

                #endregion

                //生成大标题
                for (int i = 0; i < datagrid.Columns.Count; i++)
                {
                    rangTittle = ws.get_Range(ws.Cells[rowDistance, i + columnDistance], ws.Cells[rowDistance, i + columnDistance]);
                    excelother.Cells[rowDistance, i + columnDistance] = datagrid.Columns[i].Header.ToString();
                    rangTittle.RowHeight           = 25;
                    rangTittle.ColumnWidth         = 13;
                    rangTittle.Font.Size           = 17;
                    rangTittle.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                    rangTittle.Borders.LineStyle   = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                }

                excelother.Visible = true;
            }
            catch (Exception ex)
            {
                MethodLb.CreateLog(typeof(FrameworkElement).ToString(), "Excel_LeadingOut", ex.ToString(), datagrid, type);
            }
            finally
            {
            }
        }