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

bruno_323 2010-06-04 11:04:39

想把多个dataset中数据保存到一个excel中多个sheet，不能保存成功，只能保存第一个。

...全文

2117 24 打赏收藏转发到动态举报

写回复

用AI写文章

24 条回复

切换为时间正序

请发表友善的回复…

发表回复

IT业余者 2012-03-01

打赏
举报

以下代码将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();

wy811007 2011-01-11

打赏
举报

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

mingmr 2010-06-10

打赏
举报

学习中

hpx021131 2010-06-09

打赏
举报

wxm3630478
------------

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

ilqtj 2010-06-09

打赏
举报

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();

            }

        }

    }

ilqtj 2010-06-09

打赏
举报

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();
}
}
}

alanhuqi 2010-06-09

打赏
举报

学习。。。

wxm3630478 2010-06-09

打赏
举报



      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;

      }

denyliufly 2010-06-09

打赏
举报

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

Masic_send 2010-06-08

打赏
举报

sheet需要多次实例化。

a736589809 2010-06-08

打赏
举报

看不懂哟/

loveheye 2010-06-07

打赏
举报

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

零零妖 2010-06-06

打赏
举报

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

捷哥1999 2010-06-05

打赏
举报

[Quote=引用楼主 bruno_323 的回复:]
想把多个dataset中数据保存到一个excel中多个sheet，不能保存成功，只能保存第一个。
[/Quote]

遍历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称为当前工作表，继续添加数据，最后保存文件

wuyq11 2010-06-05

打赏
举报

打开EXCEL文件，添加多sheet

ChinaITOldMan 2010-06-05

打赏
举报

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

mingyicz 2010-06-05

打赏
举报

馬一下吧。。。。

banana_97 2010-06-04

打赏
举报

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];
}
}

捷哥1999 2010-06-04