Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Posts Tagged ‘Import Excel file’

DataTable to CSV File and CSV file to Dataset in asp.net

Posted by Viral Sarvaiya on April 21, 2011


Hi,

here i give you 2 simple function for the export Datatable/Dataset to CSV file format and import CSV file to dataset/DataTable.

1. DataTable/DataSet to CSV file

private void DataTableToCSVFile(string filename)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();
foreach (DataColumn column in dtExcelUpdown.Columns)
{
context.Response.Write(column.ColumnName + ",");
}
context.Response.Write(Environment.NewLine);
foreach (DataRow row in dtExcelUpdown.Rows)
{
for (int i = 0; i < dtExcelUpdown.Columns.Count; i++)
{
context.Response.Write(row[i].ToString().Replace(",", string.Empty) + ",");
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + ".csv");
context.Response.End();
}

2. CSV file to DataSet/DataTable

from the upload control you have to save to directory and then this path and file name give as a parameter of the function.


private DataSet GetCVSFile(string pathName, string fileName)
{
OleDbConnection ExcelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties=Text;");
OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM " + fileName, ExcelConnection);

OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);

ExcelConnection.Open();

DataSet ExcelDataSet = new DataSet();
ExcelAdapter.Fill(ExcelDataSet);

ExcelConnection.Close();
return ExcelDataSet;
}

thanks…

enjoy coding…..

Advertisements

Posted in .Net, ASP.NET, asp.net feature | Tagged: , , , , , , , , | Leave a Comment »

GridView Export to Excel

Posted by Viral Sarvaiya on July 16, 2009


Exporting GridView to Excel is a very common task which is performed in most of the web applications. There are various techniques of exporting the GridView to excel and it highly depends on the application scenario. In this article I will demonstrate some techniques that you will find useful.

Exporting GridView to Excel is a very common task which is performed in most of the web applications. There are various techniques of exporting the GridView to excel and it highly depends on the application scenario. In this article I will demonstrate some techniques that you will find useful.

Response.Clear()

Response.Buffer = True

Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")

Response.Charset = ""

Response.ContentType = "application/vnd.ms-excel"

Dim sw As New StringWriter()

Dim hw As New HtmlTextWriter(sw)

GridView1.DataBind()

GridView1.RenderControl(hw)

Dim style As String = "<style>.textmode{mso-number-format:\@;}</style>"

Response.Write(style)

Response.Output.Write(sw.ToString())

Response.Flush()

Response.End()

this code gives you the open/save/cancel dialog box of the excel file

if you want to save directly to the server then write the below code.

Dim sw As New StringWriter()

Dim hw As New HtmlTextWriter(sw)

GridView1.DataBind()

GridView1.RenderControl(hw)

Dim style As String = "<style>.textmode{mso-number-format:\@;}</style>"

System.IO.File.WriteAllText("F:\\Test.xls", style & sw.ToString())

with this code the file of the excel is automatically stored in the particular folder which u provide.

Posted in ASP.NET | Tagged: , | 2 Comments »

 
%d bloggers like this: