将DataSet导入到Excel的多个Sheet中

参考MSDN文章：

如何：使用 COM Interop 创建 Excel 电子表格（C# 编程指南）

如何： 使用 Visual C#.net Excel 的句柄的事件

 

代码：

 

  private bool WriteToExcel(string filePath, DataSet ds)
        {
            Excel.Application oXL;
            Excel._Workbook oWB;
            Excel._Worksheet oSheet;
            Excel.Range oRng;

            object oMissing = System.Reflection.Missing.Value;
            //Start Excel and get Application object.
            oXL = new Excel.Application();
            oXL.Visible = false;

            try
            {

                //Get a new workbook.
                oWB = (Excel._Workbook)(oXL.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing,
                    oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing));


                oXL.DisplayAlerts = false;

                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    oSheet = (Excel.Worksheet)oWB.Worksheets.Add(oMissing, oMissing, 1, oMissing);
                    oSheet.Name = ds[i].TableName.Trim();

                    int rowIndex = 1;
                    int colIndex = 0;

                    for (int j = 0; j < ds.Tables[i].Columns.Count; j++)
                    {
                        //Add table headers going cell by cell.
                        oSheet.Cells[1, j + 1] = ds.Tables[i].Columns[j].ColumnName.Trim();
                        colIndex++;

                    }
                    string maxHeaderCell = NumberToChar(colIndex) + "1";
                    //Format A1:D1 as bold, vertical alignment = center.
                    oSheet.get_Range("A1", maxHeaderCell).Font.Bold = true;
                    oSheet.get_Range("A1", maxHeaderCell).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                    // Create an array to multiple values at once.
                    string[,] saNames = new string[ds.Tables[i].Rows.Count, ds.Tables[i].Columns.Count];

                    for (int n = 0; n < ds.Tables[i].Rows.Count; n++)
                    {
                        for (int m = 0; m < ds.Tables[i].Columns.Count; m++)
                        {
                            saNames[n, m] = ds.Tables[i].Rows[n][ds.Tables[i].Columns[m].ColumnName.Trim()].ToString();
                        }
                        rowIndex++;
                    }

                    oSheet.get_Range("A2", NumberToChar(colIndex) + rowIndex.ToString()).Value2 = saNames;
                    oRng = oSheet.get_Range("A1", maxHeaderCell);
                    oRng.EntireColumn.AutoFit();

                }

                // 保存并关闭这个工作簿
                oWB.Close(Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);

                //Make sure Excel is visible and give the user control
                //of Microsoft Excel's lifetime.
                //oXL.Visible = true;
                //oXL.UserControl = true;

                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;

            }
            finally
            {
                oXL.Quit();             //釋放Excel資源 
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
                oWB = null;
                oSheet = null;
                oRng = null;
                oXL = null;
                GC.Collect();
            }
        }
        private string NumberToChar(int number)
        {
            if (1 <= number && 26 >= number)
            {
                int num = number + 64;
                System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding();
                byte[] btNumber = new byte[] { (byte)num };
                return asciiEncoding.GetString(btNumber);
            }
            else if (number > 26)
            {
                int NewNum = number % 26;
                int count = number / 26;
                string ss = NumberToChar(count);
                int num = NewNum + 64;
                System.Text.ASCIIEncoding asciiEncoding = new System.Text.ASCIIEncoding();
                byte[] btNumber = new byte[] { (byte)num };
                return ss + asciiEncoding.GetString(btNumber);
            }

            return "数字不在转换范围内";
        }