Ejemplo n.º 1
0
        public static void ExportToExcel(DataSet ds, string fileName, bool overWrite, bool exportHeaders, bool open)
        {
            if (ds == null)
            {
                throw new ArgumentNullException("ds");
            }

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

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

            foreach (Interop.Excel.Worksheet item in book.Sheets)
            {
                item.Delete();
            }

            foreach (DataTable dt in ds.Tables)
            {
                Interop.Excel.Worksheet sheet = (Interop.Excel.Worksheet)book.Sheets.Add(miss, miss, miss, miss);

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

                Recordset           rs    = ConvertDataTableToRecordset(dt);
                Interop.Excel.Range range = (Interop.Excel.Range)sheet.Cells[1, 1];

                if (exportHeaders) // 标题行
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        Interop.Excel.Range rangeHeader = sheet.get_Range((object)(GetColumnLabel(i + 1) + "1"), miss);
                        rangeHeader.Value2 = dt.Columns[i].Caption;
                    }

                    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();
        }
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();
        }