存储过程导出为ex​​cel [英] Stored procedures export to excel

问题描述

Is there anyway to export multiple stored procedures to single excel sheet.
I want to export multiple stored procedures to excel sheet(single) using epplus library, but i am only able to export two stored procedure in single sheet.
i will be glad if i get answers.
Thanks.

我的尝试：

What I have tried:

 SqlCommand cmd = new SqlCommand("[usp_GetCampaignSchedule]", con1);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@CampaignID", SqlDbType.Int).Value = 1102;
            SqlDataAdapter da = new SqlDataAdapter(cmd);

            SqlCommand cmd1 = new SqlCommand("[usp_GetEmailStatitics]", con1);
            cmd1.CommandType = CommandType.StoredProcedure;
            cmd1.Parameters.Add("@CampaignID", SqlDbType.Int).Value = 1102;
            SqlDataAdapter da1 = new SqlDataAdapter(cmd1);

           

            DataTable dt = new DataTable();
            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();
            DataSet ds = new DataSet();
            da.Fill(dt1);
            da1.Fill(dt2);
           ds.Tables.Add(dt1);
            ds.Tables.Add(dt2);


            ExcelPackage package = new ExcelPackage();
            var worksheet = package.Workbook.Worksheets.Add("ExcelSheet");
         
            int z = 1;
            for (int x = 0; x < ds.Tables.Count; x++)
            {
                int r = z;
                int t = r + 1;
                for (int i = 1; i < ds.Tables[x].Columns.Count + 1; i++)
                {
                    worksheet.Cells[r, i].Value = ds.Tables[x].Columns
[i-1].ToString();
                }
                for (int i = 0; i < ds.Tables[x].Rows.Count; i++)
                {
                    for (int j = 0; j < ds.Tables[x].Columns.Count; j++)
                    {
                        worksheet.Cells[i + t, j + 1].Value = ds.Tables[x].Rows[i]
                        [j].ToString();
                    }
                }
                z = ds.Tables[x].Rows.Count + 4;
            }
            package.SaveAs(new FileInfo(@"C:\Users\Desktop\Work\CampaignID.xlsx"));

推荐答案

尝试

try

using (ExcelPackage package = new ExcelPackage())
           {
               ExcelWorksheet ws = package.Workbook.Worksheets.Add("Your Sheet Name");
               int rowNumber = 1;
               foreach (DataTable dt in ds.Tables)
               {
                   ws.Cells["A" + rowNumber].LoadFromDataTable(dt, true);
                   rowNumber += dt.Rows.Count + 2; // to create 2 empty rows
               }

               package.SaveAs(new FileInfo(@"D:\Projects\CPTemp\CPTemp\TextFile.xlsx"));
           }

这篇关于存储过程导出为ex​​cel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！