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 KhanBelow 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 tooKindly 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