How to bring data from database to excel sheet?

Question

I am trying to generate a excel sheet from database(sql 2008). I have wrote below mentioned code but it not working. Please help me to improve my code. below my sample code

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

This generates excel sheet but it shows only one row (middle name). I have two values Firstname and Middle name. how can I achieve it?

It is smarter to have VB macro in sheet that accesses the DB and gets the info. It was possible in Excel 2000-2003 but not sure for the latest versions. — i486, Dec 04 '14 at 10:52
@varchar, compnay name not merging it shows in first column first row — Semil Sebastian, Dec 04 '14 at 11:22
Comments are not for extended discussion; this conversation has been [moved to chat](http://chat.stackoverflow.com/rooms/66198/discussion-on-question-by-sara-john-how-to-bring-data-from-database-to-excel-she). — Taryn, Dec 04 '14 at 11:29

mainvoid · Accepted Answer · 2014-12-04T10:44:17.427

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

edited Dec 04 '14 at 10:44

answered Dec 04 '14 at 09:37

mainvoid

347
1
4
17

(row[..]); wht you mean?? – Semil Sebastian Dec 04 '14 at 09:42
@SaraJohn for example if you are trying to get the value from the first column you would use: 'row[0]'. – mainvoid Dec 04 '14 at 09:49
ws.Cells[1, 1, 1, dt.Columns.Count].Merge = true; field shows column out of range error message.Why it is?? – Semil Sebastian Dec 04 '14 at 09:52
@SaraJohn instead of 'dt.Columns.Count' use: 'ws.Cells[1, 1, 1, ws.Dimension.End.Column].Merge = true;' – mainvoid Dec 04 '14 at 10:05
I applied chnges like your answer 4th line " cell.Value = Convert.ToInt32(rows[0]);" then it shows error like "Unable to cast object of type 'System.Data.DataRow' to type 'System.IConvertible'." – Semil Sebastian Dec 04 '14 at 10:17
@SaraJohn Not rows, **row**; you have to use the row you declared in the foreach loop. – mainvoid Dec 04 '14 at 10:19
but it shows only one value. I have morethan one value in table. – Semil Sebastian Dec 04 '14 at 10:23
@SaraJohn check my edited answer. Also you might wanna try and take a look at the function LoadFromDataTable of the class ExcelWorksheet. – mainvoid Dec 04 '14 at 10:45
It works but the issue is first colum fisrt row shows company name(msd) as our code and first column second row is empty.Actually First name should come here. Now second column first row is First name and second row is middle name. I hope u got wat I said :) – Semil Sebastian Dec 04 '14 at 10:53

How to bring data from database to excel sheet?

1 Answers1