如何将数据库中的数据导入Excel表格？

Question

我想从数据库生成一个Excel表（SQL 2008）。我写下面提到的代码，但它不工作。请帮助我改进我的代码。低于我的示例代码

protected void generate_Click(object sender, EventArgs e) 
    { 
     DataTable dt = new DataTable(); //My Function which generates DataTable 
     DataSet ds = new DataSet(); 
     using (ExcelPackage p = new ExcelPackage()) 
     { 
      //Here setting some document properties 
      p.Workbook.Properties.Author = "Zeeshan Umar"; 
      p.Workbook.Properties.Title = "Office Open XML Sample"; 

      //Create a sheet 
      p.Workbook.Worksheets.Add("Sample WorkSheet"); 
      ExcelWorksheet ws = p.Workbook.Worksheets[1]; 
      ws.Name = "Sample Worksheet"; //Setting Sheet's name 
      ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet 
      ws.Cells.Style.Font.Name = "Calibri"; //Default Font name for whole sheet 

      //Merging cells and create a center heading for out table 
      ws.Cells[1, 1].Value = "msd"; 
      ws.Cells[1, 1, 1, ws.Dimension.End.Column].Merge = true; 
      // ws.Cells[1, 1, 1, dt.Columns.Count].Merge = true; 
      ws.Cells[1, 1, 1, ws.Dimension.End.Column].Style.Font.Bold = true; 
      ws.Cells[1, 1, 1, ws.Dimension.End.Column].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; 
       int colIndex = 1; 
       int rowIndex = 2; 
       SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString); 

       SqlCommand cmd = new SqlCommand("SELECT Fname,Mname FROM EmpMaster where EmpCode='" + ddcode.SelectedItem.Text + "'", conn); 
       // dr = conn.query(str); 
       SqlDataAdapter adr = new SqlDataAdapter(); 

       adr.SelectCommand = cmd; 
       adr.Fill(dt); 
       //Add the table to the data set 
       ds.Tables.Add(dt); 
       //cell.Value = "Heading " + ds.ColumnName; 
       var rows = ds.Tables[0].Rows; 
       foreach (DataRow row in rows) 
        { 
          string name = Convert.ToString(row["Fname"]); 
         string code = Convert.ToString(row["Mname"]); 
         string lname = Convert.ToString(row["Lname"]); 
         //ws.Cells[colIndex +1 , rowIndex +0].Value = name; 
         //ws.Cells[colIndex +1, rowIndex +1].Value = code; 
         //ws.Cells[colIndex +1, rowIndex +2].Value = lname; 
         ws.Cells[rowIndex , colIndex ].Value = name; 
         ws.Cells[rowIndex , colIndex +1 ].Value = code; 
         ws.Cells[rowIndex , colIndex +2].Value = lname; 
         // Move to the next row in the sheet. 
         rowIndex++; 
         colIndex++; 

     } 
       //Generate A File with Random name 
       Byte[] bin = p.GetAsByteArray(); 
       string file = "F:\\ excelsample.xlsx"; 
       File.WriteAllBytes(file, bin); 
       System.Diagnostics.Process.Start("F:\\ excelsample.xlsx"); 
     } 
} 

这生成excel工作表，但它只显示一行（中间名）。我有两个值名和中间名。我怎么能实现它？

Answer 1

foreach (DataRow row in rows) 
{ 
    string name = Convert.ToString(row["Fname"]); 
    string code = Convert.ToString(row["Middle Name"]); 

    // Setting Value in cell 
    ws.Cells[colIndex, rowIndex].Value = name; 
    ws.Cells[colIndex + 1, rowIndex].Value = code;  

    // Move to the next row in the sheet. 
    rowIndex++; 
}