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