C# WinForm 实现增删改查等功能(Access版) 系列之八-导出数据到Excel

C# WinForm 实现增删改查等功能(Access版) 系列之八

导出数据到Excel模块设计与实现

/// <summary>

        /// 导出数据到excel中

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void button5_Click(object sender, EventArgs e)

        {

            SaveDataTableToExcel(dtInfo, @"c:");

        }

        //保存到Excel

        public void SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath)

        {

            Microsoft.Office.Interop.Excel.Application app =

                new Microsoft.Office.Interop.Excel.ApplicationClass();

            try

            {

                //让后台执行设置为不可见

                app.Visible = false;

                //新增加一个工作簿

                Microsoft.Office.Interop.Excel.Workbook wBook = app.Workbooks.Add(true);

                //如果要打开已有的工作簿，则使用下面的注释语句

                // Workbook wBook = app.Workbooks.Open(@"C:/YourPath/YourWorkbook.xls",

                //    missing, missing, missing, missing, missing, missing, missing,

                //    missing, missing, missing, missing, missing,missing, missing);

 

                //取得一个工作表

                //如果打开了已有的工作簿,也可以这样获取工作表Worksheet wSheet = wBook.ActiveSheet as Worksheet

                Microsoft.Office.Interop.Excel.Worksheet wSheet = wBook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

                if (excelTable.Rows.Count > 0)

                {

                    int row = 0;

                    row = excelTable.Rows.Count;

                    int col = excelTable.Columns.Count;

                    //allsize = row;

 

                    for (int i = 0; i < row; i++)

                    {

                        //succeesize++;

                        //prograsssize++;

                        for (int j = 0; j < col; j++)

                        {

                            string str = excelTable.Rows[i][j].ToString();

                            wSheet.Cells[i + 1, j + 1] = str;

                        }

                        //SetMessageText();

                    }

                }

 

                int size = excelTable.Columns.Count;

                for (int i = 0; i < size; i++)

                {

                    wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;

                }

 

                //设置禁止弹出保存和覆盖的询问提示框

                app.DisplayAlerts = false;

                app.AlertBeforeOverwriting = false;

                //保存工作簿

                wBook.Save();

                //保存excel文件

                app.Save(filePath + "//导出一览表.xls");

            }

            catch (Exception err)

            {

                MessageBox.Show("导入Excel出错！错误原因：" + err.Message, "NaviDMS 2.2",

                    MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            finally

            {

                //确保Excel进程关闭

                app.Quit();

                app = null;

            }

            //SetCancelUse();

        }