DataSet导入到Excel里，多个DataTable分成多个Sheet,Sheet以TableName命名

方法体代码如下：

        #region  DataSet导入到Excel里，多个DataTable分成多个Sheet,Sheet以TableName命名(引用Microsoft.Office.Interop.Excel 11.0)
        /// <summary>
        /// DataSet导入到Excel里，多个DataTable分成多个Sheet,Sheet名以TableName命名
        /// </summary>
        /// <param name="DS">要导入的Excel</param>
        /// <param name="FilePathAndName">要保存的路径和文件名</param>
        public static void DataSetToExcel(DataSet DS, string FilePathAndName)
        {
            string strName = FilePathAndName.Replace(@"\\", @"\").Replace(@"\\", @"\").Replace(@"\\", @"\");
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                excel.Visible = false;
                //设置禁止弹出保存和覆盖的询问提示框
                excel.DisplayAlerts = false;
                excel.AlertBeforeOverwriting = false;
                //增加一个工作簿
                Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Add(true);
                System.Reflection.Missing miss = System.Reflection.Missing.Value;
                //添加工作表
                Microsoft.Office.Interop.Excel.Worksheet sheets = (Microsoft.Office.Interop.Excel.Worksheet)
                    book.Worksheets.Add(miss, miss, 19, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
                for (int i = 0; i < DS.Tables.Count; i++)
                {
                    System.Data.DataTable table = DS.Tables[i];
                    //获取一个工作表
                    //Microsoft.Office.Interop.Excel.Worksheet sheet = book.Worksheets[i + 1] as Microsoft.Office.Interop.Excel.Worksheet;

                    Microsoft.Office.Interop.Excel.Worksheet sheet = book.Worksheets.Add(book.Worksheets[i + 1], Type.Missing, Type.Missing, Type.Missing) as Microsoft.Office.Interop.Excel.Worksheet;
                    int rowIndex = 1;
                    int colIndex = 0;
                    foreach (DataColumn col in table.Columns)
                    {
                        colIndex++;
                        sheet.Cells[1, colIndex] = col.ColumnName;
                    }
                    foreach (DataRow row in table.Rows)
                    {
                        rowIndex++;
                        colIndex = 0;
                        foreach (DataColumn col in table.Columns)
                        {
                            colIndex++;
                            sheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                    }
                    //sheet.Name = tableNames[i];
                    sheet.Name = Common.GetLegalFileName(DS.Tables[i].TableName.ToString());
                }
                //删除多余Sheet
                for (int g = 1; g <= book.Worksheets.Count; g++)
                {
                    Microsoft.Office.Interop.Excel.Worksheet sheet = book.Worksheets[g] as Microsoft.Office.Interop.Excel.Worksheet;

                    if (sheet.Name.Length > 5 && sheet.Name.Substring(0, 5) == "Sheet")
                    {
                        sheet.Delete();
                        g--;
                    }
                }
                //book.Save();
                book.SaveAs(strName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                book.Close(false, miss, miss);
                book = null;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                KillExcelProcess(excel);//结束Excel进程                
                GC.WaitForPendingFinalizers();
                GC.Collect();
            }
        }

        //using System.Runtime.InteropServices;
        [DllImport("user32.dll", SetLastError = true)]
        static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);

        /// <summary>
        /// kill the excel.exe Process
        /// </summary>
        private static void KillExcelProcess(Microsoft.Office.Interop.Excel.Application _ApplicationClass)
        {
            try
            {
                if (_ApplicationClass != null)
                {
                    int lpdwProcessId;
                    GetWindowThreadProcessId(new IntPtr(_ApplicationClass.Hwnd), out lpdwProcessId);
                    System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                }
            }
            catch (Exception ex)
            {
                //ErrorLogManager.Log("Kill Excel Process", ex.Source, ex.StackTrace);

            }
        }

        

        #endregion

 

调用代码:

ataSet ds = new DataSet();

            string sql = "select top 5  * from Table_Table1";
            SqlDbHelper db = new SqlDbHelper();
            ds.Tables.Add(db.GetDataTable(sql));

            string sqlB = "select top 5  * from Table_Table2";
            ds.Tables.Add(db.GetDataTable(sqlB));

            string FilePathAndName = Server.MapPath("Temp.xls");
            DataSetToExcel(ds, FilePathAndName);

 

 

转载于:https://www.cnblogs.com/q149072205/archive/2013/04/09/3009424.html