how can i export database table value in excel thorugh c#

Question

Hi All, Please help me about how can i export database value in excel thorugh c#

Karthik Elumalai · Answer

Hi Feroz Khan,Below is the real time code which worked in my project: //button click event protected void btnexport_grid_Click(object sender, EventArgs e) { DataSet ds = ObjOSM.getboqitems(); DataTable dt = ds.Tables[0]; WriteExcelWithNPOI("xlsx", dt); } public void WriteExcelWithNPOI(String extension, DataTable dt) { // dll refered NPOI.dll and NPOI.OOXML IWorkbook workbook; if (extension == "xlsx") { workbook = new XSSFWorkbook(); } else if (extension == "xls") { workbook = new HSSFWorkbook(); } else { throw new Exception("This format is not supported"); } ISheet sheet1 = workbook.CreateSheet("Sheet 1"); //make a header row IRow row1 = sheet1.CreateRow(0); for (int j = 0; j { ICell cell = row1.CreateCell(j); String columnName = dt.Columns[j].ToString(); cell.SetCellValue(columnName); } //loops through data for (int i = 0; i { IRow row = sheet1.CreateRow(i + 1); for (int j = 0; j { ICell cell = row.CreateCell(j); String columnName = dt.Columns[j].ToString(); cell.SetCellValue(dt.Rows[i][columnName].ToString()); } } using (var exportData = new MemoryStream()) { Response.Clear(); workbook.Write(exportData); if (extension == "xlsx") //xlsx file format { Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "tpms_Dict.xlsx")); Response.BinaryWrite(exportData.ToArray()); } else if (extension == "xls") //xls file format { Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "tpms_dict.xls")); Response.BinaryWrite(exportData.GetBuffer()); } Response.End(); } } Hope this will work for you too,Kindly let me your suggestions or feedback Thanks

Vinay Singh · Answer

Hi have a look of this link http://www.aspsnippets.com/Articles/Export-ASPNet-Web-Page-with-images-to-PDF-using-ITextsharp.aspx

Rajeesh Menoth · Answer

Hi, You can choose “Spire.Xls” Or Other third party libraries. Check the following reference : http://www.c-sharpcorner.com/UploadFile/83fe73/solutions-to-export-data-from-database-to-excel-in-C-Sharp/

Balaji Palani · Answer

Retrieve the Database values in data table : public DataTable Getdatafromexternalfile() { string strSQL = ""; DataTable myrec = new DataTable(); try { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ToString()); SqlCommand cmd = new SqlCommand("SELECT * FROM tblMedicaid", con); cmd.CommandType = CommandType.Text; SqlDataAdapter da = new SqlDataAdapter(); da.Fill(myrec); } catch (Exception e) { } return myrec; } After Get the datatable we can export the data table to excel sheet DataTable dataTable = Getdatafromexternalfile(); Response.ClearContent(); Response.Buffer = true; Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Document Name-Doc.xls")); Response.ContentType = "application/ms-excel"; string str = string.Empty; foreach (DataColumn dtcol in dataTable.Columns) { Response.Write(str + dtcol.ColumnName); str = "	"; } Response.Write("
"); foreach (DataRow dr in dt.Rows) { str = ""; for (int j = 0; j { Response.Write(str + Convert.ToString(dr[j])); str = "	"; } Response.Write("
"); } Response.End();

Leon D · Answer

Hi, You can use spire.xls. Reference: http://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html