C# dataGridView导出EXCEL,保存前显示全部数据

Question

C# dataGridView导出EXCEL,保存前显示全部数据 ,因为导出的EXCEL不需要保存的只作临时文件
原来在网上找到的代码点击按钮后导出EXCEL之前要填写文件名及路径, 但现在想要的是导出EXCEL后暂时不保存而显示EXCEL里面的所有内容项.
  private void btnExcel_Click(object sender, EventArgs e)
        {
            string fileName = "Book1";
            string saveFileName = "";
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";
            saveDialog.FileName = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            //取消
            int r = 0;
            if (saveFileName.IndexOf(":") < 0)
            {
                return;
            }            //创建Excel对象
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的电脑未安装Excel");
                return;
            }            Excel.Workbooks workbooks = xlApp.Workbooks;
            //工作簿
            Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            //Sheet
            Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];            //写入Excel第一行
            Excel.Range Range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 3]);//Range对象
            Range.MergeCells = true;//合并单元格
            worksheet.Cells[1, 1] = "销售订单展开表";//单元格内文本
            //Range.Font.Name = "黑体";//字体
            Range.Font.Size = 12;//字体大小            //标题格式
            Microsoft.Office.Interop.Excel.Range Range2 = worksheet.get_Range("A2", "C2");
            //Range2.Font.Name = "黑体";
            Range2.Font.Size = 12;
            Range2.Interior.Pattern = Excel.XlSortType.xlSortValues;
            //上边框加粗            
            Range2.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
            Range2.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
            Range2.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
            Range2.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;            //写入标题
            for (int i = 0; i < dataGridView1.ColumnCount; i++)
            {
                worksheet.Cells[2, i + 1] = dataGridView1.Columns[i].HeaderText;
            }
            //写入数值
            for (r = 0; r < dataGridView1.Rows.Count; r++)
            {
                for (int i = 0; i < dataGridView1.ColumnCount; i++)
                {
                    worksheet.Cells[r + 3, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
            //保存
            workbook.Saved = true;
            //获得保存路径
            workbook.SaveCopyAs(saveFileName);
            object TM = System.Type.Missing;
            Excel.Application excel = new Excel.Application();
            //打开已经写入值的EXCEL进行操作
            //Excel.Workbook book = excel.Application.Workbooks.Add(saveFileName);
            //Excel.Worksheet sheet = (Excel.Worksheet)book.Sheets[1];
            //要汇总的列
            //int[] totile = new int[1] { 3 };
            //以第二列分组汇总
            //sheet.get_Range(sheet.Cells[2, 1], sheet.Cells[r + 3, 3]).Subtotal(2, Excel.XlConsolidationFunction.xlSum, totile, TM, TM, Excel.XlSummaryRow.xlSummaryBelow);
            //获得写入的总行
            //int count = sheet.UsedRange.Rows.Count;
            //获得A、B、C列
            //string A = "A" + count;
            //string B = "B" + count;
            //string C = "C" + count;
            //Excel.Range RangeA = sheet.get_Range("A2", A);
            //Excel.Range RangeB = sheet.get_Range("B2", B);
            //Excel.Range RangeC = sheet.get_Range("C2", C);            //字体
            //RangeA.Font.Name = "Arial";
            //字体大小
            //RangeA.Font.Size = 8;
            //RangeA.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            //RangeA.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
            //RangeC.NumberFormatLocal = "#,##0.00_);(#,##0.00)";//格式
            //RangeC.Font.ColorIndex = 5;//字体颜色
            //边框
            //RangeC.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
            //RangeC.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
            //sheet.get_Range(A, C).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;            //是否打开
            excel.Visible = true;
            //保存
            //book.Saved = true;
            //book.SaveCopyAs(saveFileName);
            excel.Quit();
            xlApp.Quit();
            if (excel != null)
            {
                excel.Quit();
            }
            if (xlApp != null)
            {
                xlApp.Quit();
            }
            GC.Collect();//强行销毁
            //MessageBox.Show("Excel导出成功!" + "共写入" + count + "行");
            MessageBox.Show("Excel导出成功!");        }

Answer 1

不保存打不开吧  System.Diagnostics.Process.Start（保存后的文件路径）;
打开文件

Answer 2

是要导出EXCEL后再在EXCEL文件菜单里面点击保存才真正保存,我在其它软件里面见过是可以的,但我在C#里面就是做不到.

Answer 3

那只能临时保存，真真的保存还是要把excel保存下来。

Answer 4

C# DataGridView控件中数据导出到Excel
方法一：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using Microsoft.Office.Interop.Excel;namespace TestEXCLE
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            _DataGridView = this.dataGridView1;
            ExportToExcel("www");
        }        private DataGridView _DataGridView;
        private string _FileName = "";        ///// <summary>
        ///// 构造函数
        ///// </summary>
        ///// <param name="ownerForm">拥用者窗体</param>
        ///// <param name="dataGridView">DataGridView对象</param>
            public Form1(Form ownerForm, DataGridView dataGridView)
            {
            
            _DataGridView = dataGridView;
            }        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="pfileName">文件名,不需要带扩展名</param>
        public void ExportToExcel(string pfileName)
        {
            _FileName = pfileName == null ? "未命名" : pfileName.Trim();            string fileName = ShowSaveFileDialog("Microsoft Excel Document", "Microsoft Excel|*.xls");
            if (fileName != "")
            {
                try
                {
                    ExportTo(fileName);
                    OpenFile(fileName);
                }
                catch (System.Exception err)
                {
                    MessageBox.Show(err.Message);
                }
            }
        }        /// <summary>
        /// 导出到
        /// </summary>
        /// <param name="fileName">文件名</param>
        private void ExportTo(string fileName)
        {            //
            //获取指定文件是否存在
            bool isExist = System.IO.File.Exists(fileName);
            //
            //定义一个缺少的object对象            
            object oMis = System.Reflection.Missing.Value;            //
            //定义一个Excel区域对象,用于保存选择的区域
            Microsoft.Office.Interop.Excel.Range selectRange;
            //
            //声明一Excel Application 对象
            Microsoft.Office.Interop.Excel.ApplicationClass App = null;
            //
            //声明一Excel Workbook 对象
            Microsoft.Office.Interop.Excel.Workbook wb = null;
            //
            //声明一Excel Worksheet 对象
            Microsoft.Office.Interop.Excel.Worksheet ws = null;            //
            //将当前水标状态保存到临时变量中后将光标置为忙状态
            Cursor currentCursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;            try
            {
                App = new Microsoft.Office.Interop.Excel.ApplicationClass();                //
                //判断指定的文件是否存在
                if (isExist)
                {
                    //
                    //打开已存在的工作薄 Workbook
                    wb = App.Workbooks.Open(fileName, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis, oMis);
                    //新建工作表
                    ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.Add(oMis, oMis, 1, oMis);
                    //指定工作表名
                    ws.Name = this.Text + wb.Sheets.Count; //加 wb.Sheets.Count 防止重名
                }
                else
                {
                    //
                    //增加一工作薄 Workbook
                    wb = App.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                    //新建工作薄后默认有一个工作表,取得第一个工作表
                    ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets[1];
                    //指定工作表名
                    ws.Name = this.Text;
                }
                //
                //增加一工作表 Worksheet                      //
                //声明两个变量记录Excle当前操作的行号与列号
                int rown = 0;
                int coln = 0;                //
                //声明一变量用于记录当前DatagridView的总行数
                int colCount = _DataGridView.Columns.Count;                _DataGridView.SuspendLayout();                //
                //将DataGirdView列头写入Excel中
                foreach (DataGridViewColumn dgvc in _DataGridView.Columns)
                {
                    selectRange = ws.get_Range(ws.Cells[rown + 1, ++coln], ws.Cells[rown + 1, coln]);
                    selectRange.Columns.ColumnWidth = dgvc.Width / 10;
                    selectRange.Interior.ColorIndex = 16;
                    selectRange.Interior.Pattern = Microsoft.Office.Interop.Excel.Constants.xlSolid;
                    selectRange.Font.ColorIndex = 2;
                    selectRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                    selectRange.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                    selectRange.set_Item(1, 1, dgvc.HeaderText);
                }                //
                //行号增加一
                rown++;                //
                //将DataGridView中所有的行数写入Excel中
                foreach (DataGridViewRow dgvr in _DataGridView.Rows)
                {
                    for (int i = 0; i < colCount; i++)
                    {
                        selectRange = ws.get_Range(ws.Cells[rown + 1, i + 1], ws.Cells[rown + 1, i + 1]);
                        selectRange.set_Item(1, 1, dgvr.Cells[i].Value);
                    }
                    rown++;
                }                //
                //写入完成后将有数据的范围内设置其边框与内部线条
                ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = 3;
                ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = 3;
                ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = 3;
                ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = 3;
                ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = 2;
                ws.UsedRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = 2;                //
                //设置Wookbook为已保存状态
                wb.Saved = true;
                if (isExist)
                {
                    wb.Save();
                }
                else
                {
                    //
                    //将当前工作薄保存为指定的文件名
                    wb.SaveCopyAs(fileName);
                }
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {                _DataGridView.ResumeLayout();                wb.Close(false, null, null);
                //
                //退出Excel程序
                App.Quit();                //
                //将当前光标更改回原来的状态
                Cursor.Current = currentCursor;
            }        }
        /// <summary>
        /// 打开文件
        /// </summary>
        /// <param name="fileName">文件名</param>
        private void OpenFile(string fileName)
        {
            if (MessageBox.Show("你想打开这个文件吗?", "导出到...", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                try
                {
                    System.Diagnostics.Process process = new System.Diagnostics.Process();
                    process.StartInfo.FileName = fileName;
                    process.StartInfo.Verb = "Open";
                    process.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal;
                    process.Start();
                }
                catch
                {
                    MessageBox.Show(this, "你的计算机中未安装Excel,不能打开该文档!" , this.ProductName
                        , MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }        /// <summary>
        /// 显示保存文件对话框,并返回选择的文件路径
        /// </summary>
        /// <param name="title">对话框的标题</param>
        /// <param name="filter">过滤器</param>
        /// <returns>选择的文件路径</returns>
        private string ShowSaveFileDialog(string title, string filter)
        {
            SaveFileDialog dlg = new SaveFileDialog();
            string name = _FileName;
            int n = name.LastIndexOf(".") + 1;
            if (n > 0) name = name.Substring(n, name.Length - n);
            dlg.Title = "导出到" + title;
            dlg.FileName = name;
            dlg.Filter = filter;
            if (dlg.ShowDialog() == DialogResult.OK) return dlg.FileName;
            return "";
        }        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: 这行代码将数据加载到表“testDataSet2.chengji”中。您可以根据需要移动或移除它。
            this.chengjiTableAdapter.Fill(this.testDataSet2.chengji);        }
    }
}

Answer 5

谢谢你的回复,因为刚开始自学C#,所以不知道如何在我的这个基础上改,麻烦帮忙帮我再改一下好吗?
 private void btnExcel_Click(object sender, EventArgs e) //我是点击按钮就导出的

Answer 6

怎么样一边导出，一边看呢？visible属性设置为true也不可以