csharp: Export DataSet into Excel and import all the Excel sheets to DataSet

/// <summary>
       /// Export DataSet into Excel
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
       private void Form3_Load(object sender, EventArgs e)
       {
           //Create an Emplyee DataTable
           DataTable employeeTable = new DataTable("Employee");
           employeeTable.Columns.Add("Employee ID");
           employeeTable.Columns.Add("Employee Name");
           employeeTable.Rows.Add("1", "涂聚文");
           employeeTable.Rows.Add("2", "geovindu");
           employeeTable.Rows.Add("3", "李蘢怡");
           employeeTable.Rows.Add("4", "ноппчц");
           employeeTable.Rows.Add("5", "ニヌネハヒフキカォноппчц");
           //Create a Department Table
           DataTable departmentTable = new DataTable("Department");
           departmentTable.Columns.Add("Department ID");
           departmentTable.Columns.Add("Department Name");
           departmentTable.Rows.Add("1", "IT");
           departmentTable.Rows.Add("2", "HR");
           departmentTable.Rows.Add("3", "Finance");

           //Create a DataSet with the existing DataTables
           DataSet ds = new DataSet("Organization");
           ds.Tables.Add(employeeTable);
           ds.Tables.Add(departmentTable);

           ExportDataSetToExcel(ds);
       }

       /// <summary>
       /// This method takes DataSet as input paramenter and it exports the same to excel
       /// </summary>
       /// <param name="ds"></param>
       private void ExportDataSetToExcel(DataSet ds)
       {
           //Creae an Excel application instance
           //EXCEL组件接口
           System.Reflection.Missing miss = System.Reflection.Missing.Value;
           Excel.Application excelApp = new Excel.Application();
           excelApp.Application.Workbooks.Add(true);
           string timeMark = DateTime.Now.ToString("yyyyMMddHHmmss");
           string FilePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "geovindu" + timeMark + ".xlsx");
           //Create an Excel workbook instance and open it from the predefined location
           //Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(FilePath);
           Excel.Workbooks books = (Excel.Workbooks)excelApp.Workbooks;
           Excel.Workbook excelWorkBook = (Excel.Workbook)books.Add(miss);
           foreach (DataTable table in ds.Tables)
           {
               //Add a new worksheet to workbook with the Datatable name
               Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
               excelWorkSheet.Name = table.TableName;

               for (int i = 1; i < table.Columns.Count + 1; i++)
               {
                   excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
               }

               for (int j = 0; j < table.Rows.Count; j++)
               {
                   for (int k = 0; k < table.Columns.Count; k++)
                   {
                       excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
                   }
               }
           }

           excelWorkBook.SaveAs(FilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, System.Text.Encoding.UTF8, miss, miss);
           excelWorkBook.Close(false, miss, miss);
           //excelWorkBook.Save();
           books.Close();
           excelApp.Quit();

       }

/// <summary>
       /// EXCEL表的所有工作表导入到DataSet
       /// 涂聚文 Microsoft.ACE.OLEDB.12.0
       /// Geovin Du
       /// </summary>
       /// <param name="fileName"></param>
       /// <returns></returns>
       static DataSet  ImportExcelParse(string fileName)
       {
           string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);

           DataSet data = new DataSet();

           foreach (var sheetName in GetExcelSheetNames(connectionString))
           {
               using (OleDbConnection con = new OleDbConnection(connectionString))
               {
                   var dataTable = new DataTable();
                   string query = string.Format("SELECT * FROM [{0}]", sheetName);
                   con.Open();
                   OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
                   adapter.Fill(dataTable);
                   data.Tables.Add(dataTable);
               }
           }

           return data;
       }
       /// <summary>
       /// 读取所有工作表名
       /// </summary>
       /// <param name="connectionString"></param>
       /// <returns></returns>
       static string[] GetExcelSheetNames(string connectionString)
       {
           OleDbConnection con = null;
           DataTable dt = null;
           con = new OleDbConnection(connectionString);
           con.Open();
           dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

           if (dt == null)
           {
               return null;
           }

           String[] excelSheetNames = new String[dt.Rows.Count];
           int i = 0;

           foreach (DataRow row in dt.Rows)
           {
               excelSheetNames[i] = row["TABLE_NAME"].ToString();
               i++;
           }

           return excelSheetNames;
       }

/// <summary>
       /// 添加图片
       /// 涂聚文
       /// </summary>
       /// <param name="dt"></param>
       protected void ExportExcelImg(System.Data.DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0) return;
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                return;
            }
            xlApp.Application.Workbooks.Add(true);
            string timeMark = DateTime.Now.ToString("yyyyMMddHHmmss");
            string FilePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "geovindu" + timeMark + ".xlsx");

            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            Microsoft.Office.Interop.Excel.Range range;
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            long totalCount = dt.Rows.Count;
            long rowRead = 0;
            float percent = 0;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 15;
            }
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    try
                    {
                        worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
                    }
                    catch
                    {
                        worksheet.Cells[r + 2, i + 1] =
               dt.Rows[r][i].ToString().Replace("=", "");
                    }
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
            }
            string strimg =Application.StartupPath+@"/IMG_6851.JPG";
            worksheet.Shapes.AddPicture(strimg, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 100, 200, 200, 300);
           //在添加的图片上加文字
            worksheet.Shapes.AddTextEffect(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1, "涂聚文写上", "Red", 15, Microsoft.Office.Core.MsoTriState.msoFalse,Microsoft.Office.Core.MsoTriState.msoTrue, 150, 200);
            xlApp.Visible = true;

            workbook.SaveAs(FilePath, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, System.Text.Encoding.UTF8, miss, miss);
            workbook.Close(false, miss, miss);
            //excelWorkBook.Save();
            workbooks.Close();
            xlApp.Quit();
        }