c#数据导入excel多sheet问题，请各位帮忙，谢谢！

把你的代码贴出来，把你的导出execl的函数封装，创作工作区间sheet的地方改一下

objSheet = (Excel._Worksheet)objSheets.get_Item(Sheets);你想寫在哪頁就寫在哪個頁面上啊??

看看：
轻松实现SQL Server与Access、Excel数据表间的导入导出

  for (int i = 0; i < dtData.Rows.Count; i++)
            {                for (int j = 0; j < dtData.Columns.Count - 1; j++)
                {                    objSheet.Cells[i + 2, j + 1] = dtData.Rows[i][j];
                }
            }

private void button4_Click(object sender, EventArgs e)
        {
            //try
            //{
                //DataSet objSet = new DataSet();
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                SaveFileDialog saveFileDialog2 = new SaveFileDialog();                saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
                saveFileDialog.FilterIndex = 0;
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.CreatePrompt = true;
                saveFileDialog.Title = "导出文件保存路径";
                saveFileDialog.ShowDialog();
                string strName = saveFileDialog.FileName;
                System.Reflection.Missing miss = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.ApplicationClass excel;                Microsoft.Office.Interop.Excel.Workbooks books;
                Microsoft.Office.Interop.Excel.Workbook book;
                //Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
                Microsoft.Office.Interop.Excel.Worksheet sheet;

                //try
                //{
                //Type type = config[0].GetType();                for (int j = 0; j < 2; j++)
                {
                    Type type = obj1[j].GetType();
                    PropertyInfo[] propertys = type.GetProperties();                    string[] str = new string[type.GetProperties().Length];
                    string[] strpro = new string[type.GetProperties().Length];
                    for (int i = 0; i < type.GetProperties().Length; i++)
                    {
                        //propertys[i].SetValue(config[0], i.ToString(), null);
                        if (propertys[i].GetValue(obj1[j], null) != null)
                        {
                            strpro[i] = propertys[i].Name;
                            str[i] = propertys[i].GetValue(obj1[j], null).ToString();
                        }
                        //str[i] = propertys[i].ToString();
                    }
                    string shee = "Sheet" + (j + 1).ToString();                    if (strName.Length != 0)
                    {

                        excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                        excel.Application.Workbooks.Add(true); ;
                        excel.Visible = false;//若是true，则在导出的时候会显示EXcel界面。
                        if (excel == null)
                        {
                            MessageBox.Show("EXCEL无法启动！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                        books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
                        book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
                        //Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
                        sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets[j + 1] as Worksheet;
                        //sheet.Name = "Sheet1";//输出的sheet1名字
                        sheet.Name = shee;                                                //填充数据
                        for (int i = 0; i < type.GetProperties().Length; i++)
                        {                            excel.Cells[1, i + 1] = strpro[i];
                            excel.Cells[2, i + 1] = str[i];
                         }


                        sheet.SaveAs(strName, miss, miss, miss, miss, miss, true, miss, miss, miss);
                        //sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, miss, miss, miss);                        book.Close(false, miss, miss);
                        books.Close();
                        excel.Quit();

                        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                        GC.Collect();
                    }
                }
                        MessageBox.Show("数据已经成功导出到：" + saveFileDialog.FileName.ToString(), "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        //toolStripProgressBar1.Value = 0;
                        //toolStripProgressBar1.Visible = false;                    //}

            //}
            //catch
            //{ MessageBox.Show("请先执行反序列化和动态获取属性操作！"); }
        }

遍历数据，添加多个sheet
Excel.Application app = new Excel.ApplicationClass();
app.Visible = true;
Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing);
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
for(int i=1;i <sheetCount;i++)
{
((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);

}

看看：
轻松实现SQL Server与Access、Excel数据表间的导入导出

打开EXCEL文件，添加多sheet

遍历dataset中的数据表，然后依次添加。大致代码如下：Excel.Application  myExcel=new Excel.Application();
myExcel.Visible=false;
myExcel.Workbooks.Add();//第一行输出列名,dt是数据表
int col=0;
foreach(DataColumn dc in dt.Columns)
{
    myExcel.Worksheets("sheet1").activate();//让sheet1成为当前工作表
    myExcel.Cells(1,col).value=dc.ColumnName;
    col+=1;
}//第二行开始输出内容
for(col=0;col<dt.Columns.Count-1;col++)
{
    for(int row=0;row<dt.Rows.Count-1;row++)
    {
        //Excel是从1开始编号的
        meExcel.Cells(row+2,col+1).value=dt.Rows[row][col];
    }
}
//...让sheet2称为当前工作表，继续添加数据，最后保存文件

楼主，看看这里吧，这里有完整的代码
http://www.cnblogs.com/liaoyunjxn/archive/2010/04/28/1723375.html

看看我的空间吧 http://blog.csdn.net/loveheye/archive/2010/05/20/5611149.aspx 或许你能找到你想要的

http://www.cnblogs.com/denylau/archive/2010/04/30/1725172.html
这里提供了一个方法，导出两个的。你自己参考下做下调整就OK的！

      public void SaveTableListToExcel(List<System.Data.DataTable> table, string savepath)
      {
        try
        {
          Application xlApp = new Application();
          xlApp.Visible = false;
          xlApp.DisplayAlerts = false;
          Microsoft.Office.Interop.Excel.Workbooks wbs = xlApp.Workbooks;
          Microsoft.Office.Interop.Excel.Workbook wb = wbs.Add(Missing.Value);
          List<Microsoft.Office.Interop.Excel.Worksheet> ws = this.ToWorkSheetList(wb, table);
          xlApp.DisplayAlerts = false;
          xlApp.ActiveWorkbook.SaveCopyAs(savepath);
          xlApp.Quit();
          xlApp = null;
          GC.Collect();
          GC.WaitForPendingFinalizers();
        }
        catch (Exception ex)
        {
          throw ex;
        }
      }      //多个DataTable导入Excel
      public List<Microsoft.Office.Interop.Excel.Worksheet> ToWorkSheetList(Microsoft.Office.Interop.Excel.Workbook wb, List<System.Data.DataTable> TabList)
      {
        int k = 0;
        List<Microsoft.Office.Interop.Excel.Worksheet> WorksheetList = new List<Microsoft.Office.Interop.Excel.Worksheet>();
        foreach (System.Data.DataTable table in TabList)
        {
          k++;
          Microsoft.Office.Interop.Excel.Worksheet ret = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[k];
          //ret.Name = table.TableName;
          ret.Name = (table.TableName != String.Empty) ? table.TableName : "Sheets" + k + "";   //Name要特别注意不能为Empty 和特殊符号,不能重复
          Microsoft.Office.Interop.Excel.Range r = ret.get_Range(ret.Cells[1, 1], ret.Cells[1, table.Columns.Count]);
          object[] header = new object[table.Columns.Count];
          for (int i = 0; i < table.Columns.Count; i++)
          {
            header[i] = table.Columns[i].ToString();
          }
          r.Value2 = header;
          if (table.Rows.Count > 0)
          {
            r = ret.get_Range("A2", Missing.Value);
            object[,] objData = new Object[table.Rows.Count, table.Columns.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
              for (int j = 0; j < table.Columns.Count; j++)
              {
                objData[i, j] = table.Rows[i][j].ToString();
              }
            }
            r = r.get_Resize(table.Rows.Count, table.Columns.Count);
            r.Value2 = objData;
            r.EntireColumn.AutoFit();
          }
          WorksheetList.Add(ret);
        }
        return WorksheetList;
      }

private DataTable GetExcelData(string path, string sql)
    {
        OleDbConnection myConnection;
        OleDbCommand myCommand;
        string sqlString;
        string connectionString;
        DataSet dataSet = new DataSet();
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0";
        sqlString = sql;
        try
        {
            myConnection = new OleDbConnection(connectionString);
            myCommand = new OleDbCommand(sqlString, myConnection);
            myCommand.CommandType = CommandType.Text;
            OleDbDataAdapter myAdapter = new OleDbDataAdapter(myCommand);
            myConnection.Open();
            myAdapter.Fill(dataSet, "Table");
            myAdapter.Dispose();
            myCommand.Dispose();
            myConnection.Close();
            myConnection.Dispose();
            return dataSet.Tables[0];
        }
        catch (Exception E)
        {
            throw (E);
        }
        finally
        {        }
    }
    private void ExcuteSql(string path, string sql)
    {
        OleDbConnection myConnection;
        OleDbCommand myCommand;
        string sqlString;
        string connectionString;
        DataSet dataSet = new DataSet();
        connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0";
        sqlString = sql;
        try
        {
            myConnection = new OleDbConnection(connectionString);
            myCommand = new OleDbCommand(sqlString, myConnection);
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myConnection.Close();
        }
        catch (Exception E)
        {
            throw (E);
        }
        finally
        {
        }
    }
    private String[] GetExcelSheetNames(string path)
    {
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;
        try
        {
            String connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
            objConn = new OleDbConnection(connString);
            objConn.Open();
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return null;
            }
            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                i++;
            }
            return excelSheets;
        }
        catch (Exception ex)
        {
            return null;
        }
        finally
        {
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
        }
    }

wxm3630478
------------请问你有没有测试过，多于3个sheet页是否正常导出呢？？

貌似都有限制吧。excel的表行列都是有限制的工作表也有限制应该。。
具体介绍看 http://wenku.baidu.com/view/b76841d9ad51f01dc281f10a.html

以下代码将html写入指定工作表但是怎么控制写进那个工作表？
string html = GetHtml();
Response.ClearHeaders();
Response.ClearContent();
Response.AppendHeader("Content-Disposition", "attachment;filename=demo.xls");
Response.Charset = "gb2312";
ContentEncoding = Encoding.Default;
ContentType = "application/ms-excel";
Response.Write(html);
Response.End();

调试易

c#数据导入excel多sheet问题，请各位帮忙，谢谢！

解决方案 »