C# DataGridView数据导出Excel表格 要根据现有数据一个单位字段来分成不同的Sheet

Question

  麻烦高手了,关键问题在于怎样给第一个Sheet赋值和给其它不同的Sheet的表头赋列名
(Sheet名字是从DataGridView 中的一列得出的)    SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.Filter = "Execl files (*.xls)|*.xls";
            saveFileDialog1.FilterIndex = 0;
            saveFileDialog1.RestoreDirectory = true;
            saveFileDialog1.CreatePrompt = true;
            saveFileDialog1.Title = "导出Excel到";
           DialogResult result=saveFileDialog1.ShowDialog();
           if (result == DialogResult.OK)
           {
               string strName = saveFileDialog1.FileName;
               System.Reflection.Missing miss = System.Reflection.Missing.Value;               Excel.Application excel = new Excel.ApplicationClass();               Excel.Workbooks books = (Excel.Workbooks)excel.Workbooks;               Excel.Workbook book = (Excel.Workbook)(books.Add(miss));               Excel.Worksheet sheet = null;
               ArrayList list=new ArrayList();
              //下面的list就是我要存放的Sheet的名字 
              for (int c = 0; c < dataGridView1.ColumnCount; c++)
               {
                   list.Add(dataGridView1.Rows[0].Cells[1].Value.ToString());
                   int a = dataGridView1.Rows[c].Cells[1].Value.ToString().CompareTo(dataGridView1.Rows[c + 1].Cells[1].Value.ToString());
                  if (a > 0)
                   {
                       list.Add(dataGridView1.Rows[c + 1].Cells[1].Value.ToString());
                   }
               }
           //下面的代码有误    
           foreach (Object p in list)
               {
                    sheet =new Excel.Worksheet();                   sheet.Name="自动化系统@sheet";
                   int colIndex=0;
                   for(int k=0;k<dataGridView1.Columns;k++)
                   {
                       colIndex++;
                       excel.Cells[2,colIndex]=this.dataGridView1.Columns[k+2].HeaderText;
                   }
                   for (int i = 0; i < dataGridView1.Rows.Count; i++)
                   {
                       for (int j = 2; j < dataGridView1.Columns.Count; j++)
                       {
                           excel.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                                                   
                       }
                   }
               }
           }
               sheet.SaveAs(strName, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
               book.Close(false, miss, miss);
               books.Close();
               excel.Quit();
               System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
               System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
               System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
               System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
               GC.Collect();

Answer 1

Excel.Application    myExcel=new  Excel  .ApplicationClass  ();  
  myExcel.Visible  =true;  
  myExcel.Workbooks  .Add  (true);  
  for(int  i=0;i <5;i++)  
  {  
  myExcel.Sheets.Add  (Missing.Value,Missing.Value,Missing.Value,Missing.Value  );  
  }  
    

Answer 2

 myExcel.Visible =true;   
  myExcel.Workbooks .Add (true);    myExcel.Sheets.Add (Missing.Value,Missing.Value,Missing.Value,Missing.Value );   这3行代码是什么意思？知道大概意思,但是还是有点不明白

Answer 3

 SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.Filter = "Execl files (*.xls)|*.xls";
            saveFileDialog1.FilterIndex = 0;
            saveFileDialog1.RestoreDirectory = true;
            saveFileDialog1.CreatePrompt = true;
            saveFileDialog1.Title = "导出Excel到";
            DateTime now = DateTime.Now;
            string[] name = Logined.User.UserName.Split('.');
            if (name.Length == 1)
            {
                saveFileDialog1.FileName = "自动化系统" + Logined.User.UserName + now.Year.ToString().PadLeft(2)
                + now.Month.ToString().PadLeft(2, '0')
                 + now.Day.ToString().PadLeft(2, '0');
            }
            else
            {
                saveFileDialog1.FileName = "自动化系统" + name[0].ToString() + name[1].ToString() + now.Year.ToString().PadLeft(2)
                + now.Month.ToString().PadLeft(2, '0')
                + now.Day.ToString().PadLeft(2, '0');
            }
            DialogResult result = saveFileDialog1.ShowDialog();
            if (result == DialogResult.OK)
            {
                string strName = saveFileDialog1.FileName;
                System.Reflection.Missing miss = System.Reflection.Missing.Value;                Excel.Application excel = new Excel.ApplicationClass();                Excel.Workbooks books = (Excel.Workbooks)excel.Workbooks;                Excel.Workbook book = (Excel.Workbook)(books.Add(miss));
               
                Excel.Worksheet sheet = null;
                string word1 = "";
                ArrayList list = new ArrayList();
                for (int c = 0; c < dataGridView1.ColumnCount; c++)
                {
                    word1 = dataGridView1.Rows[0].Cells[1].Value.ToString();
                    list.Add(dataGridView1.Rows[0].Cells[1].Value.ToString());
                    if (dataGridView1.Rows[c + 1].Cells[1].Value == null)
                    {
                        break;
                    }
                    else
                    {
                        int a = dataGridView1.Rows[c].Cells[1].Value.ToString().CompareTo(dataGridView1.Rows[c + 1].Cells[1].Value.ToString());
                        if (a > 0)
                        {                            list.Add(dataGridView1.Rows[c + 1].Cells[1].Value.ToString());
                        }
                    }
                }                sheet = (Excel.Worksheet)book.Worksheets[1];                sheet.Name = "自动化系统记录@" + word1;
                int colIndex = 0;
                for (int k = 2; k < dataGridView1.Columns.Count; k++)
                {
                    colIndex++;
                    excel.Cells[1, colIndex] = this.dataGridView1.Columns[k].HeaderText;
                }
                int b = 0;
                int l = 1;
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    if(i!=0){
                    if (dataGridView1.Rows[i].Cells[1].Value != null)
                    {
                        if (dataGridView1.Rows[i].Cells[1].Value.ToString() != dataGridView1.Rows[i-1].Cells[1].Value.ToString()&&b==23)
                        {
                            l++;
                            sheet = (Excel.Worksheet)book.Worksheets[l];
                            sheet.Name ="自动化系统@"+dataGridView1.Rows[i].Cells[1].Value.ToString();
                            int index = 0;
                            for (int k = 2;k<dataGridView1.Columns.Count; k++)
                            {
                                index++;
                                excel.Cells[1,index] = this.dataGridView1.Columns[k].HeaderText;
                            }
                            b = 0;
                        }
                    }
                    }
                    for (int j = 2; j < dataGridView1.Columns.Count; j++)
                    {
                        b++;
                        excel.Cells[i + 2, j - 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                       
                    }
                }
                sheet.SaveAs(strName, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                book.Close(false, miss, miss);
                books.Close();
                excel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                GC.Collect();
            }            string name1 = "EXCEL.exe";
            guanbi(name1);
        }
我现在做到这种程度了,能倒出去 但是数据全部显示在第一张工作薄里,其它工作薄虽然有名字但是没数据.