C#导出EXCEL winform&&WPF


前言

作为一个刚学C#几天的菜鸡,由于实习工作需要,学习并成功实现了一下几种DataGrid导出EXCEL的方法,仅供像我一样的新手参考,大佬请点击右上角


提示:以下是本篇文章正文内容,下面案例可供参考

一、用微软自带的API导出(大概)

1.创建一个导出类

代码如下:

 #region 第一种导出Excel的方法
    public class ExportToExcel<T, U>
      where T : class
      where U : List<T>
    {
        public List<T> dataToPrint;
        // Excel object references.
        private Microsoft.Office.Interop.Excel.Application _excelApp = null;
        private Microsoft.Office.Interop.Excel.Workbooks _books = null;
        private Microsoft.Office.Interop.Excel._Workbook _book = null;
        private Microsoft.Office.Interop.Excel.Sheets _sheets = null;
        private Microsoft.Office.Interop.Excel._Worksheet _sheet = null;
        private Microsoft.Office.Interop.Excel.Range _range = null;
        private Microsoft.Office.Interop.Excel.Font _font = null;
        // Optional argument variable
        private object _optionalValue = Missing.Value;

        /// <summary>
        /// 生成报表
        /// </summary>
        public void GenerateReport()
        {

            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel (*.XLSX)|*.xlsx"; ;
            if ((saveFileDialog.ShowDialog()) == DialogResult.OK)
            {
                try
                {
                    if (dataToPrint != null)
                    {
                        if (dataToPrint.Count != 0)
                        {
                            Mouse.SetCursor(System.Windows.Input.Cursors.Wait);
                            CreateExcelRef();
                            FillSheet();
                            OpenReport(saveFileDialog.FileName);
                            Mouse.SetCursor(System.Windows.Input.Cursors.Arrow);
                        }
                    }
                }
                catch (Exception e)
                {
                    System.Windows.Forms.MessageBox.Show("保存失败");
                }
                finally
                {
                    ReleaseObject(_sheet);
                    ReleaseObject(_sheets);
                    ReleaseObject(_book);
                    ReleaseObject(_books);
                    ReleaseObject(_excelApp);
                }
            }

        }
   
        private void OpenReport(string excelName)
        {
            _excelApp.Visible = false;

            //保存为.xls格式
            //_book.SaveAs(excelName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            //保存为.xlsx格式
            _book.SaveAs(excelName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            _excelApp.Quit();
            System.Windows.Forms.MessageBox.Show("保存成功");
        }
        /// <summary>
        /// 渲染表
        /// </summary>
        private void FillSheet()
        {
            object[] header = CreateHeader();
            WriteData(header);
        }
        /// <summary>
        /// 向工作簿中写数据
        /// </summary>
        /// <param name="header"></param>
        private void WriteData(object[] header)
        {
            object[,] objData = new object[dataToPrint.Count, header.Length];

            for (int j = 0; j < dataToPrint.Count; j++)
            {
                var item = dataToPrint[j];
                for (int i = 0; i < header.Length; i++)
                {
                    var y = typeof(T).InvokeMember(header[i].ToString(), BindingFlags.GetProperty, null, item, null);
                    objData[j, i] = (y == null) ? "" : y.ToString();
                }
            }
            AddExcelRows("A2", dataToPrint.Count, header.Length, objData);
            AutoFitColumns("A1", dataToPrint.Count + 1, header.Length);
        }
        /// <summary>
        /// 列自适应宽
        /// </summary>
        /// <param name="startRange"></param>
        /// <param name="rowCount"></param>
        /// <param name="colCount"></param>
        private void AutoFitColumns(string startRange, int rowCount, int colCount)
        {
            _range = _sheet.get_Range(startRange, _optionalValue);
            _range = _range.get_Resize(rowCount, colCount);
            _range.Columns.AutoFit();
        }
        /// <summary>
        /// 创建列头
        /// </summary>
        /// <returns></returns>
        private object[] CreateHeader()
        {
            PropertyInfo[] headerInfo = typeof(T).GetProperties();

           
            // 为列头创建一个数组
            // 从单元A1开始
            List<object> objHeaders = new List<object>();
            for (int n = 0; n < headerInfo.Length; n++)
            {
                objHeaders.Add(headerInfo[n].Name);
            }
            

            var headerToAdd = objHeaders.ToArray();
            AddExcelRows("A1", 1, headerToAdd.Length, headerToAdd);
            SetHeaderStyle();

            return headerToAdd;
        }
        /// <summary>
        /// 设置列头加粗
        /// </summary>
        private void SetHeaderStyle()
        {
            _font = _range.Font;
            _font.Bold = true;
        }
        /// <summary>
        /// 添加行
        /// </summary>
        /// <param name="startRange"></param>
        /// <param name="rowCount"></param>
        /// <param name="colCount"></param>
        /// <param name="values"></param>
        private void AddExcelRows(string startRange, int rowCount, int colCount, object values)
        {
            _range = _sheet.get_Range(startRange, _optionalValue);
            _range = _range.get_Resize(rowCount, colCount);
            _range.set_Value(_optionalValue, values);
        }
        /// <summary>
        /// 创建Excel实例
        /// </summary>
        private void CreateExcelRef()
        {
            _excelApp = new Microsoft.Office.Interop.Excel.Application();
            _books = _excelApp.Workbooks;
            //xlsx格式设置book
            _book = _books.Add(XlWBATemplate.xlWBATWorksheet);
            //xls格式设置book
           // _book = _books.Add(_optionalValue);
            _sheets = _book.Worksheets;
            _sheet = (Microsoft.Office.Interop.Excel._Worksheet)(_sheets.get_Item(1));
        }
        /// <summary>
        /// 释放资源
        /// </summary>
        /// <param name="obj"></param>
        private void ReleaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                System.Windows.Forms.MessageBox.Show(ex.Message.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
    #endregion

2.给按钮绑定事件

            ExportToExcel<MaterialModel, List<MaterialModel>> exportToExcel = new ExportToExcel<MaterialModel, List<MaterialModel>>();
            ICollectionView view = CollectionViewSource.GetDefaultView(materialInfo.ItemsSource);

            exportToExcel.dataToPrint = (List<MaterialModel>)view.SourceCollection;
            exportToExcel.GenerateReport();

二、使用ASPOSE.DLL导出

1先将DataGrid的数据转为DataTable

代码如下:

      public System.Data.DataTable ToDataTable(List<MaterialModel> materialModels)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Columns.Add(new DataColumn("材料名称", typeof(string)));
            dt.Columns.Add(new DataColumn("材料数量", typeof(string)));
            dt.Columns.Add(new DataColumn("材料长度", typeof(string)));
            dt.Columns.Add(new DataColumn("管段", typeof(string)));
            dt.Columns.Add(new DataColumn("注释", typeof(string)));
            foreach (var m in materialModels)
            {
                DataRow dataRow = dt.NewRow();
                dataRow["材料名称"] = m.MaterailName;
                dataRow["材料数量"] = m.MaterailCount;
                dataRow["材料长度"] = m.MaterailLength;
                dataRow["管段"] = m.PipeSection;
                dataRow["注释"] = m.Notes;
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

2.导出Excel的方法

 public  bool ExportExcelWithAspose(System.Data.DataTable data)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel (*.XLSX)|*.xlsx";
            if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) {
                try
                {
                    if (data == null)
                    {
                        System.Windows.Forms.MessageBox.Show("数据为空");
                        return false;
                    }


                    Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(); //创建工作簿
                    Aspose.Cells.Worksheet sheet = book.Worksheets[0]; //创建工作表
                    Cells cells = sheet.Cells; //单元格
                                               //创建样式
                    Aspose.Cells.Style style = book.Styles[book.Styles.Add()];
                    style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 左边界线  
                    style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 右边界线  
                    style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 上边界线  
                    style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 下边界线   
                    style.HorizontalAlignment = TextAlignmentType.Center; //单元格内容的水平对齐方式文字居中
                    style.Font.Name = "宋体"; //字体
                                            //style1.Font.IsBold = true; //设置粗体
                    style.Font.Size = 11; //设置字体大小
                                          //style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); //背景色
                                          //style.Pattern = Aspose.Cells.BackgroundType.Solid;  

                    int Colnum = data.Columns.Count;//表格列数 
                    int Rownum = data.Rows.Count;//表格行数 
                                                 //生成行 列名行 
                    for (int i = 0; i < Colnum; i++)
                    {
                        cells[0, i].PutValue(data.Columns[i].ColumnName); //添加表头
                        cells[0, i].SetStyle(style); //添加样式
                    }
                    //生成数据行 
                    for (int i = 0; i < Rownum; i++)
                    {
                        for (int k = 0; k < Colnum; k++)
                        {
                            cells[1 + i, k].PutValue(data.Rows[i][k].ToString()); //添加数据
                            cells[1 + i, k].SetStyle(style); //添加样式
                        }
                    }
                    sheet.AutoFitColumns(); //自适应宽
                    book.Save(saveFileDialog.FileName); //保存
                    System.Windows.Forms.MessageBox.Show("Excel成功保存到"+saveFileDialog.FileName);
                    GC.Collect();
                }
                catch (Exception e)
                {
                    return false;
                }
               
            }
            return true;

        }

三、Winform上DataGridView导出Excel

这里有一个封装好的类 拿来用就行了
下载点击这里 👍

总结

总结个锤子,就是网上东拼西凑加重写方法,以上仅供新手们参考学习,以及个人记录用。

  • 0
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页
评论

打赏作者

黄皮耗子在充电

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值