建立XLS檔(依DS內DT 建立SHEET)－冠霖的部落格

     /// <summary>
        /// 依DS內DT 建XLS SHEET
        /// </summary>
        /// <param name="myds"></param>
        /// <returns></returns>
        private string MakeEmptyXLS(DataSet myds)
        {

           Microsoft.Office.Interop.Excel.Application xlApp = null;
           Microsoft.Office.Interop.Excel.Range aRange = null;
           Microsoft.Office.Interop.Excel.Range aRange2 = null;
            object mObj_opt = System.Reflection.Missing.Value;

xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)
            {
                MessageBox.Show("無法建立EXCEL應用程式!");
                return "";
            }

            Microsoft.Office.Interop.Excel.Workbook wb = null;
            wb = xlApp.Workbooks.Add(mObj_opt);

            //把DS內每個TABLE 建一個SHEET
            for (int i = 0; i < myds.Tables.Count; i++)
   {
                Microsoft.Office.Interop.Excel.Worksheet ws = null;
                ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets.Add(Type.Missing,wb.Worksheets[3], 1, Type.Missing);
                ws.Name = myds.Tables[i].TableName;
                ws.Cells[1, 1] = "欄位名稱";
                ws.Cells[1, 2] = "欄位型別";
                ws.Cells[1, 3] = "欄位長度";
                ws.Cells[1, 4] = "PK";
                ws.Cells[1, 5] = "欄位描述";
   }

            //把DEFAULT的SHEET1 2 3 DELETE
            Microsoft.Office.Interop.Excel.Worksheet oWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
            oWorksheet.Delete();
            Microsoft.Office.Interop.Excel.Worksheet oWorksheet2 = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
            oWorksheet2.Delete();
            Microsoft.Office.Interop.Excel.Worksheet oWorksheet3 = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets.get_Item(1);
            oWorksheet3.Delete();
            string execPath = @"C:\" + cbx_db.Text + ".xls";

//此SAVEAS方式,XLS檔內容是XML,用OLEDB方式要再插入資料會出現 [不是預期的格式]
//wb.SaveAs(@"C:\" + cbx_db.Text + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, mObj_opt, mObj_opt, mObj_opt, mObj_opt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, mObj_opt, mObj_opt, mObj_opt, mObj_opt, mObj_opt);

            wb.SaveAs(execPath, mObj_opt, mObj_opt, mObj_opt, mObj_opt, mObj_opt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, mObj_opt, mObj_opt, mObj_opt, mObj_opt, mObj_opt);

            wb.Close(false, mObj_opt, mObj_opt);
            xlApp.Workbooks.Close();
            xlApp.Quit();
            KillProcess("EXCEL");
            return   "C:\\" + cbx_db.Text + ".xls" ;

}