Dataset to Excel sheets using interop.excel generation in C#.net
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
getquery();
}
}
void getquery()
{
con = new SqlConnection(strcon);
con.Open();
string query = " Select top 100 * from getezee; ";
cmd = new SqlCommand(query,con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
con.Close();
}
protected void excel_generation_Click(object sender, EventArgs e)
{
try
{
Export_Excel(ds);
}
catch (Exception ex)
{
throw ex;
}
}
public void Export_Excel(DataSet ds)
{
Microsoft.Office.Interop.Excel.Application objXL = null;
Microsoft.Office.Interop.Excel.Workbook objWB = null;
try
{
objXL = new Microsoft.Office.Interop.Excel.Application();
objWB = objXL.Workbooks.Add(ds.Tables.Count);
int sheetcount = 1;
foreach (System.Data.DataTable dt in ds.Tables)
{
Microsoft.Office.Interop.Excel.Worksheet objSHT = (Microsoft.Office.Interop.Excel.Worksheet)objWB.Sheets.Add();
objSHT.Name = "SheetData" + sheetcount.ToString();
for (int j = 0; j < dt.Rows.Count; j++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (j == 0)
{
objSHT.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();
}
objSHT.Cells[j + 2, i + 1] = dt.Rows[j][i].ToString();
}
}
sheetcount++;
}
objWB.Saved = true;
objWB.SaveCopyAs("C:\\" + Guid.NewGuid().ToString() + ".xlsx");
objWB.Close();
objXL.Quit();
}
catch (Exception ex)
{
objWB.Saved = true;
objWB.Close();
objXL.Quit();
Response.Write("Illegal permission");
}
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
Samuel SummersPosted Jul 10, 2015, 3:41 AM
Alex SmithPosted Apr 20, 2015, 2:15 AM
OleDbConnection, then Export datatable to Excel.//connect database
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString @"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""demo.mdb"";User Id=;Password="
OleDbCommand command = new OleDbCommand();
command.CommandText = "select * from parts";
DataSet dataSet = new System.Data.DataSet();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command.CommandText,connection);
dataAdapter.Fill(dataSet);
DataTable t = dataSet.Tables[0];
//export datatable to excel
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
sheet.InsertDataTable(t, true, 1, 1);
book.SaveToFile("insertTableToExcel.xls");
System.Diagnostics.Process.Start("insertTableToExcel.xls");
Prakash JPosted Feb 17, 2015, 2:04 AM