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

Feroz Khan

10y1.1kVisual Basic .NET

Hi All,

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Karthik Elumalai10y ago
Accepted 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 < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
String columnName = dt.Columns[j].ToString();
cell.SetCellValue(columnName);
}
//loops through data
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet1.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; 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 Singh10y ago
Hi have a look of this link
http://www.aspsnippets.com/Articles/Export-ASPNet-Web-Page-with-images-to-PDF-using-ITextsharp.aspx
Rajeesh Menoth10y ago
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 Palani10y ago
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 = "\t";
}
Response.Write("\n");
foreach (DataRow dr in dt.Rows)
{
str = "";
for (int j = 0; j < dataTable.Columns.Count; j++)
{
Response.Write(str + Convert.ToString(dr[j]));
str = "\t";
}
Response.Write("\n");
}
Response.End();
Leon D10y ago
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