示例#1
0
    // Export DataTable into an excel file with field names in the header line
    // - Save excel file without ever making it visible if filepath is given
    // - Don't save excel file, just make it visible if no filepath is given
    public static void ExportToExcel(this DataTable Tbl, string ExcelFilePath = null)
    {
        try
        {
            if (Tbl == null || Tbl.Columns.Count == 0)
            {
                throw new Exception("ExportToExcel: Null or empty input table!\n");
            }

            // load excel, and create a new workbook
            Excel.Application excelApp = new Excel.Application();
            excelApp.Workbooks.Add();

            // single worksheet
            Excel._Worksheet workSheet = excelApp.ActiveSheet;

            // column headings
            for (int i = 0; i < Tbl.Columns.Count; i++)
            {
                workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
            }

            // rows
            for (int i = 0; i < Tbl.Rows.Count; i++)
            {
                // to do: format datetime values before printing
                for (int j = 0; j < Tbl.Columns.Count; j++)
                {
                    workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
                }
            }

            // check fielpath
            if (ExcelFilePath != null && ExcelFilePath != "")
            {
                try
                {
                    workSheet.SaveAs(ExcelFilePath);
                    excelApp.Quit();
                    MessageBox.Show("Excel file saved!");
                }
                catch (Exception ex)
                {
                    throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
                                        + ex.Message);
                }
            }
            else        // no filepath is given
            {
                excelApp.Visible = true;
            }
        }
        catch (Exception ex)
        {
            throw new Exception("ExportToExcel: \n" + ex.Message);
        }
    }
        public static void Export_To_Excel(DataGridView dtGridView, string filename, string sheetName)
        {
            int    i, j;
            object missing = Type.Missing;

            Excel.ApplicationClass excellApp;
            excellApp = new Excel.ApplicationClass();
            excellApp.Application.Workbooks.Add(true);

            try
            {
                // Add columns name to excel file
                for (i = 0; i < dtGridView.Columns.Count; i++)
                {
                    excellApp.Cells[1, i + 1] = dtGridView.Columns[i].Name;
                }
                for (i = 0; i < dtGridView.Rows.Count; i++)
                {
                    for (j = 0; j < dtGridView.Columns.Count; j++)
                    {
                        excellApp.Cells[i + 2, j + 1] = dtGridView.Rows[i].Cells[j].Value;
                    }
                }
                //excellApp.Save(("Loinhuan.xls");
                Excel._Worksheet worksheet = (Excel._Worksheet)excellApp.ActiveSheet;
                worksheet.Activate();
                worksheet.Name = sheetName;
                worksheet.SaveAs(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                //excellApp.Workbooks[1].SaveCopyAs(@"D:\Project\SVN\Source\Quanlyloinhuan\Loinhuan.xls");
                excellApp.Quit();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
        }