关于Excel的两个问题？

Question

问题一：
mycommand01.CommandText = "select * from [Sheet1$]";
访问excel表中的sheet1表，但是我不想那么写，我想访问第一张表，请问怎么写呢？问题二：
网上我查了半天，只发现了怎么访问Excel数据库，但是不知道怎么用allData来修改、删除、插入数据，请高手指教
da = new OleDbDataAdapter(mycommand01);
da.Fill(allData);
tmp1 = allData.Tables[0].Rows[i][0].ToString();

Answer 1

你是将Excel文档作为数据源读取的。还有一种更灵活的办法是使用 Excel.Application 对象，也就是 VBA 方式来操纵 Excel 文档。具体怎么做，Google下，文章已经太多了。

Answer 2

给你提供个思路啊：
第一个问题你可以获取所有的sheets，然后你要取第一个，直接用Sheets[0]，即可。
代码示例大概如下：     //获得当前你选择的Excel Sheet的所有名字
     //filePath是Excel表格的全路径
     public static string[] GetExcelSheetNames(string filePath)
        {            Excel.ApplicationClass excelApp = new Excel.ApplicationClass();
            Excel.Workbooks wbs = excelApp.Workbooks;
            Excel.Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            int count=wb.Worksheets.Count;
            string[]sheetNames=new string[count];
            for (int i = 1; i <= count; i++)
            {
                sheetNames[i-1]=((Excel.Worksheet)wb.Worksheets[i]).Name;
            }
            return sheetNames;
        }第二个问题，你可以考虑先把数据存到Access中，然后执行数据库操作。

Answer 3

倒入数据到Excel
方法1.通过OLEDBprotected void ExportToExcel(string strConn, DataTable dtSQL)
        {
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                // Create a new sheet in the Excel spreadsheet.
                OleDbCommand cmd = new OleDbCommand("create table Person(LastName varchar(50), FirstName varchar(50),PersonCategory varchar(50))", conn);                // Open the connection.
                conn.Open();                // Execute the OleDbCommand.
                cmd.ExecuteNonQuery();                cmd.CommandText = "INSERT INTO Person (LastName, FirstName,PersonCategory) values (?,?,?)";                // Add the parameters.
                cmd.Parameters.Add("LastName", OleDbType.VarChar, 50, "LastName");
                cmd.Parameters.Add("FirstName", OleDbType.VarChar, 50, "FirstName");
                cmd.Parameters.Add("PersonCategory", OleDbType.VarChar, 50, "PersonCategory");                // Initialize an OleDBDataAdapter object.
                OleDbDataAdapter da = new OleDbDataAdapter("select * from Person", conn);                // Set the InsertCommand of OleDbDataAdapter, 
                // which is used to insert data.
                da.InsertCommand = cmd;                // Changes the Rowstate()of each DataRow to Added,
                // so that OleDbDataAdapter will insert the rows.
                foreach (DataRow dr in dtSQL.Rows)
                {
                    dr.SetAdded();
                }                // Insert the data into the Excel spreadsheet.
                da.Update(dtSQL);            }
        }
方法2.通过VBApublic void FullData(string sheetName, string workBookName, DataTable table)
        {
            Excel.Worksheet sheet = null;
            Excel.Range cell = null;
            int rowIndex;
            int columnIndex;            try
            {
                sheet = _excel.Workbooks[workBookName].Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
                sheet.Name = sheetName;                // head row
                for (columnIndex = 1; columnIndex <= table.Columns.Count; columnIndex++)
                {
                    cell = sheet.Cells[1, columnIndex] as Excel.Range;                    cell.Value2 = table.Columns[columnIndex - 1].ColumnName;
                    FormatCell(cell);
                }                rowIndex = 2;                //body rows
                foreach (DataRow row in table.Rows)
                {
                    if (rowIndex <= 65535)
                    {
                        for (columnIndex = 1; columnIndex <= table.Columns.Count; columnIndex++)
                        {
                            sheet.Cells[rowIndex, columnIndex] = row[columnIndex - 1].ToString();
                        }
                        rowIndex++;
                    }
                    else
                    {
                        break;
                    }
                }
            }
            catch
            {
                throw;
            }
        }

Answer 4

   // 创建一个Application对象并使其可见 
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
            app.Visible = true;
            // 打开模板文件，得到WorkBook对象 
            Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks._Open(templetFile, missing, missing, missing, missing, missing, missing, missing,
                               missing, missing, missing, missing, missing);
            //Microsoft.Office.Interop.Excel.Workbook workBook = app.Workbooks.Add(missing);
            // 得到WorkSheet对象 
            Microsoft.Office.Interop.Excel.Worksheet workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Sheets.get_Item(1);