c# 导出DataSet到excel

public static bool ExportToExcel_dataSet(string queryNo, string conditions)

        {

            bool _bl = false;

            try

            {

                Docmd.cnnOpen();

 

                SqlCommand cmd = new SqlCommand();

                cmd.CommandText = "dbo.sx_pro_QueryTest";

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Connection = Docmd.cnn;

                cmd.CommandTimeout = 180;

                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.Parameters.AddWithValue("@QueryNo",queryNo);

                cmd.Parameters.AddWithValue("@Whr", conditions);

               

                DataSet ds = new DataSet();

                da.Fill(ds,"t1");

                DataTable dt = ds.Tables["t1"];

 

                int qtyRows = dt.Rows.Count;

 

                if(qtyRows==0)

                {

                    Docmd.msgInfo("no records found. \r\n 没有找到记录");

                    goto line1;

                }

 

 

 

                int qtyCols = dt.Columns.Count;

                int rn = 0;

                int cn = 0;

 

                object[,] objTitle = new object[1, qtyCols];

                foreach (DataColumn dc in dt.Columns)

                {

                    objTitle[0, cn] = dc.ColumnName;

                    cn++;

                }

 

                object[,] objData = new object[qtyRows, qtyCols];

                foreach (DataRow dr in dt.Rows)

                {

                    cn = 0;

                    foreach (DataColumn dc in dt.Columns)

                    {

                        objData[rn, cn] = dr[dc].ToString();

                        cn++;

                    }

                    rn++;

                }

                excel.Application xlApp = new excel.Application();

                excel.Workbook wk = xlApp.Workbooks.Add();

                excel.Worksheet sht = wk.Worksheets[1];

                xlApp.Visible = true;

                excel.Range myRange_Title = sht.Range[sht.Cells[1, 1], sht.Cells[1, qtyCols]];

                myRange_Title.Value2 = objTitle;

 

                excel.Range myRange = sht.Range[sht.Cells[2, 1], sht.Cells[qtyRows + 1, qtyCols]];

                myRange.Value2 = objData;

 

                sht.UsedRange.WrapText = false;

 

 

               line1:

                ds.Dispose();

                cmd.Dispose();

            }

           catch(Exception ex)

            {

                Docmd.msgInfo(ex.Message);

            }

            finally

            {

                Docmd.cnnClose();

            }

 

            return _bl;

        }

转载于:https://www.cnblogs.com/3xin/p/8027927.html