Ejemplo n.º 1
0
        /// <summary>
        /// 导出为Excel方法
        /// </summary>
        /// <param name="savePath">保存EXCEL的路径,如"C:\\ ab.xls"</param>
        /// <param name="dt1">数据表-可以改为范型集合</param>

        public void ToExcel(string savePath, System.Windows.Forms.DataGridView dgv)
        {
            Interop.Excel.ApplicationClass xlApp     = new Interop.Excel.ApplicationClass();
            Interop.Excel.Workbooks        workbooks = xlApp.Workbooks;
            Interop.Excel.Workbook         workbook  = workbooks.Add(Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Interop.Excel.Worksheet        worksheet = (Interop.Excel.Worksheet)workbook.Worksheets[1];

            Interop.Excel.Range range = (Interop.Excel.Range)worksheet.Columns;
            range.EntireColumn.AutoFit();   //自动调整列宽
            range.NumberFormatLocal = "@";

            worksheet.Name = tabFiled[0];//命名工作表的名称


            try
            {
                //加列头
                for (int i = 1; i < tabFiled.Count; i++)
                {
                    worksheet.Cells[1, i] = tabFiled[i];
                }

                //加列值
                for (int p = 0; p < dgv.Rows.Count; p++)
                {
                    for (int j = 0; j < dgv.Columns.Count; j++)
                    {
                        worksheet.Cells[p + 2, j + 1] = dgv.Rows[p].Cells[j].Value.ToString();
                    }
                }
                workbook.Saved = true;
                workbook.SaveCopyAs(savePath);
            }
            catch (Exception ed)
            {
                throw new Exception(ed.Message);
            }
            finally
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                worksheet = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;
                workbooks.Close();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                workbooks = null;
                xlApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp = null;
                GC.Collect();//使用此方法强制对所有代码进行垃圾回收。
            }
            //xlApp.Visible = true;//如果要显示EXCEL,那么finally语句的内容就不应该执行
        }
Ejemplo n.º 2
0
        public static void ExportToExcel(DataGridView dgv, string fileName, bool overWrite, string sheetName, bool exportHeaders, bool open)
        {
            if (dgv == null)
            {
                throw new ArgumentNullException("dgv");
            }

            if (dgv.Columns.Count == 0)
            {
                throw new Exception("所提供的 DataGridView 中不包含任何列.");
            }

            if (File.Exists(fileName))
            {
                if (overWrite)
                {
                    File.Delete(fileName);
                }
                else
                {
                    throw new Exception("文件已经存在!");
                }
            }

            Interop.Excel.Application app = new Interop.Excel.ApplicationClass {
                Visible = false
            };
            Interop.Excel.Workbooks books = app.Workbooks;
            Interop.Excel.Workbook  book  = books.Add(miss);
            Interop.Excel.Worksheet sheet = (Interop.Excel.Worksheet)book.ActiveSheet;

            foreach (Interop.Excel.Worksheet s in book.Sheets)
            {
                if (s != sheet)
                {
                    s.Delete();
                }
            }

            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet.Name = sheetName;
            }

            Recordset rs = ConvertDataGridViewToRecordSet(dgv);

            Interop.Excel.Range range = (Interop.Excel.Range)sheet.Cells[1, 1];

            if (exportHeaders) // 标题行
            {
                int i = 0;
                foreach (DataGridViewColumn col in dgv.Columns)
                {
                    if (col.Visible)
                    {
                        Interop.Excel.Range rangeHeader = sheet.get_Range((object)(GetColumnLabel(i + 1) + "1"), miss);
                        rangeHeader.Value2 = col.HeaderText;
                        i++;
                    }
                }

                range = (Interop.Excel.Range)sheet.Cells[2, 1];
            }

            range.CopyFromRecordset(rs, miss, miss);

            book.SaveAs(fileName, Interop.Excel.XlFileFormat.xlExcel7, miss, miss, miss, miss,
                        Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);

            if (open)
            {
                app.Visible = true;
            }
            else
            {
                book.Close(false, miss, miss);
                books.Close();
                app.Quit();
            }

            System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            GC.Collect();
        }