/// 
        /// Export DataSet into Excel        /// 
        ///         ///         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);        }        /// 
        /// This method takes DataSet as input paramenter and it exports the same to excel        /// 
        ///         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();        }

　　

/// 
        /// EXCEL表的所有工作表导入到DataSet        /// 涂聚文 Microsoft.ACE.OLEDB.12.0        /// Geovin Du        /// 
        ///         /// 
             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;        }        /// 
        /// 读取所有工作表名        /// 
        ///         /// 
             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;        }

　　

/// 
        /// 添加图片        /// 涂聚文        /// 
        ///         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();         }

　　

/// 
        /// GirdView转换成DataTable        /// 20150813         /// 涂聚文        /// 
        ///         /// 
             public static DataTable GetGirdViewToTableHeaderText(DataGridView dgv)        {            DataTable dt = new DataTable();            try            {                                for (int count = 0; count < dgv.Columns.Count; count++)                {                    if (dgv.Columns[count].Visible == true)                    {                        DataColumn dc = new DataColumn(dgv.Columns[count].HeaderText);                        dt.Columns.Add(dc);                    }                }                for (int count = 0; count < dgv.Rows.Count; count++)                {                    DataRow dr = dt.NewRow();                    for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)                    {                        if (dgv.Columns[count].Visible == true)                        {                            //if (dgv[countsub, count].ValueType == typeof(string))                            //{                            //    dr[countsub] = "'" + dgv.Rows[count].Cells[countsub].Value;                            //}                            //else                            //{                                dr[countsub] = dgv.Rows[count].Cells[countsub].Value;                            //}                                                    }                    }                    dt.Rows.Add(dr);                }            }            catch (Exception ex)            {                ex.Message.ToString();            }            return dt;        }        /// 
        /// List 转DataTable        /// 涂聚文        /// 
        ///         /// 
              public static DataTable ConvertListToDataTable(List
     
       list)        {            // New table.            DataTable table = new DataTable();            // Get max columns.            int columns = 0;            foreach (var array in list)            {                if (array.Length > columns)                {                    columns = array.Length;                }            }            // Add columns.            for (int i = 0; i < columns; i++)            {                table.Columns.Add();            }            // Add rows.            foreach (var array in list)            {                table.Rows.Add(array);            }            return table;        }
     

　　

/// 
        /// 涂聚文        /// 2015.08.18        /// 
        ///         /// 
             public static DataTable DataGridViewToDataTable(DataGridView dataGridView)        {            DataTable dt = new DataTable();            try            {                foreach (DataGridViewColumn col in dataGridView.Columns)                {                    if (col.Visible == true)                    {                        dt.Columns.Add(col.HeaderText, col.ValueType);                    }                }                foreach (DataGridViewRow gridRow in dataGridView.Rows)                {                    if (gridRow.IsNewRow)                        continue;                    int irow = 0;                    DataRow dtRow = dt.NewRow();                    for (int i1 = 0; i1 < dataGridView.Columns.Count; i1++)                    {                        if (dataGridView.Columns[i1].Visible == true)                        {                            dtRow[irow] = (gridRow.Cells[i1].Value == null ? DBNull.Value : gridRow.Cells[i1].Value);                            irow++;                        }                    }                    dt.Rows.Add(dtRow);                }                //ds.Tables.Add(dt);                //System.Diagnostics.Debugger.Break();            }            catch (Exception ex)            {               MessageBox.Show(ex.Message.ToString());            }            return dt;        }        /// 
        /// 涂聚文        /// 
        ///         /// 
             public static DataTable GetGirdViewToTableHeaderText(DataGridView dgv)        {            //DataGridViewColumnCollection            DataTable dt = new DataTable();            try            {                //标题                for (int count = 0; count < dgv.Columns.Count; count++)                {                    if (dgv.Columns[count].Visible == true)                    {                        DataColumn dc = new DataColumn(dgv.Columns[count].HeaderText, dgv.Columns[count].ValueType);                        dt.Columns.Add(dc);                    }                }                for (int count = 0; count < dgv.Rows.Count; count++)                {                    int irow = 0;                   DataRow dr = dt.NewRow();                     //DataRow dr = dt.Rows.Add();                    for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)                    {                        if (dgv.Columns[countsub].Visible == true)                        {                            //if (dgv[countsub, count].ValueType == typeof(string))                            //{                            //    dr[countsub] = "'" + dgv.Rows[count].Cells[countsub].Value;                            //}                            //elsedt.Rows[countsub][count]                            //{                            //dr[countsub] = dgv[countsub, count].Value;// dgv.Rows[count].Cells[countsub].Value;                            dr[irow] = dgv.Rows[count].Cells[countsub].Value; // dgv[countsub, count].Value;                            //}                           irow++;                        }                    }                    dt.Rows.Add(dr);                                    }            }            catch (Exception ex)            {                MessageBox.Show(ex.Message.ToString());            }            return dt;        }