Excel导出（一）

    本文是列表导出规则Excel文档（直接读取DataGridView中的数据）的示例。

 

    首先在项目中添加“Microsoft.Office.Interop.Excel”。

 

    创建一个工作表，如下所示：

            int v = 0;
            SaveFileDialog sfd = new SaveFileDialog();
            string currentPatch = Directory.GetCurrentDirectory();
            sfd.Title = "请选择将导出的EXCEL文件存放路径";
            sfd.FileName = Convert.ToDateTime(tempdate).ToString("yyyy年MM月") + "-保險代扣實際明細表";
            sfd.Filter = "Excel文档(*.xls)|*.xls";
            if (sfd.ShowDialog() == DialogResult.OK)
            {

                if (sfd.FileName != "")
                {

                    Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
                    if (excelApp == null)
                    {
                        MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
                    }
                    else
                    {
                        try
                        {
                            Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
                            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
                           
                            for (int i = 0; i < dt.Tables[0].Rows.Count; i++)
                            {
                                
                                for (int j = 1; j < dt.Tables[0].Columns.Count; j++)
                                {
                                    if (i == 0)
                                    {
                                        if (j == 1)
                                        {
                                            worksheet.Cells[i + 1, j] = this.dtEmp.Columns[j-1].HeaderText;
                                        }
                                        else
                                        {
                                            worksheet.Cells[i + 1, j] = this.dtEmp.Columns[j].HeaderText;
                                        }
                                    }

                                      worksheet.Cells[v + 2, j] = dt.Tables[0].Rows[i][j].ToString();

                                   
                                }
                                 v++;
                               

                            }
                            //保存方式一：保存WorkBook
                            //workbook.SaveAs(@"F:CData.xls",
                            // Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
                            // Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,
                            // Missing.Value,Missing.Value);

                            //保存方式二：保存WorkSheet
                            // worksheet.SaveAs(@"F:CData2.xls",
                            // Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                            // Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                            //保存方式三
                            workbook.Saved = true;
                            workbook.SaveCopyAs(sfd.FileName);

                            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;
                            excelApp.Quit();
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                            excelApp = null;
                            MessageBox.Show("导出Excel完成!");
                        }
                        catch
                        {
                            MessageBox.Show("保存出错，请检查文件是否被正使用！", "系统信息");
                        }
                        finally
                        {
                            GC.Collect();
                        }

                    }
                }
           

 

 

   注意事项：

   1、有Excel的操作，把本页面中的DataTable全部要改为DataSet。

   2、进行Excel的操作后，系统默认的路径发生了变化，需从默认路径读去数据的操作就会报错。在操作前写上string currentPatch = Directory.GetCurrentDirectory();语句保存默认路径，操作后用Directory.SetCurrentDirectory(currentPatch);恢复默认值。而且必须要在项目中添加“Microsoft.IO”。