上传excel和csv文件 [英] Upload excel and csv file

问题描述

如何上传csv和excel文件并将上传文件的数据存储到数据表

How to upload a csv and excel file and store tha data of uploaded file to a datatable

推荐答案

private DataTable GetExcelSheetdata()
{
   DataTable dt = new DataTable();
   try
   {
      // Url of file to be imported.
      var url = @"c:\test.xls";

      // Create OleDB connection string.
      string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;""", url);

       var query = String.Format("Select * from [{0}

中选择*， Sheet1）;

// 创建并填充Oledb数据适配器。
OleDbDataAdapter dataAdapter = new OleDbDataAdapter（query，connectionString）;

// 从适配器创建和填充数据集
DataSet dataSet = new DataSet（）;

dataAdapter.Fill（dataSet）;
dt = dataSet.Tables [ 0 ];
}
catch
{
}

返回 dt;
}


和如果我们要导入.csv文件，那么 导入文件的代码和 return data-table：
namespace required是：System.Data和System.Data.Odbc;

private DataTable GetCsvSpreadSheetdata（）
{
DataTable dt = new DataTable（）;

尝试
{
// 要导入的目录和文件。
var serverDirPath = @ c：\;
var fileName = test.csv ;
var filePath = string .Concat（serverDirPath， \\，fileName）;

var strConnString = Driver = {Microsoft Text Driver（* .txt; * .csv）}; Dbq =
+ serverDirPath + ; Extensions = asc，csv，tab，txt; Persist Security Info = False;

// 创建并打开ODBC连接
OdbcConnection conn = new OdbcConnection（strConnString.Trim（））;
conn.Open（）;

var query = string .Format（ 从[{0}]选择* ，fileName）;

// 创建数据适配器
OdbcDataAdapter obj_da = < span class =code-keyword> new OdbcDataAdapter（query，conn）;

DataSet ds = new DataSet（）;

// 使用CSV文件中的记录填充数据集
obj_da.Fill（ds， members）;

// 关闭连接
conn.Close（） ;

dt = ds.Tables [ 0 ];
}
catch
{
}

返回 dt;
}

", "Sheet1"); //Creates and fill Oledb data adapter. OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString); //create and fill dataset from adapter DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet); dt = dataSet.Tables[0]; } catch { } return dt; } and if we want to import .csv file, then here is the code to import file and return data-table : namespace required are : System.Data and System.Data.Odbc; private DataTable GetCsvSpreadSheetdata() { DataTable dt = new DataTable(); try { // Directory and file to be imported. var serverDirPath = @"c:\"; var fileName = "test.csv"; var filePath = string.Concat(serverDirPath, "\\", fileName); var strConnString = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + serverDirPath + ";Extensions=asc,csv,tab,txt;Persist Security Info=False"; // Creates and opens an ODBC connection OdbcConnection conn = new OdbcConnection(strConnString.Trim()); conn.Open(); var query = string.Format("Select * from [{0}]", fileName); //Creates the data adapter OdbcDataAdapter obj_da = new OdbcDataAdapter(query, conn); DataSet ds = new DataSet(); //Fill dataset with the records from CSV file obj_da.Fill(ds, "members"); //closes the connection conn.Close(); dt = ds.Tables[0]; } catch { } return dt; }

这是将excel或CSV文件保存到数据表的基本原理，现在您可以轻松地迭代返回data-table将记录保存在DB中。

有关详细信息，请访问： http://sandeep-tada.blogspot.com/2012/02/import-excel-and-csv-file-using-c.html [ ^ ]

public void GetDataTable（string strSheetName）

{

try {

string pathname = Server.MapPath（〜\\Admin \\CountScore.xls;）;

OleDbConnection ExcelConnection = default（OleDbConnection）;

ExcelConnection = new OleDbConnection （Provider = Microsoft.Jet.Oledb.4.0; Data Source =+ pathname +Extended Properties = Excel 8.0;）;

ExcelConnection.Open（）;

string strComand =select * from [+ strSheetName +

public void GetDataTable(string strSheetName)
{
try {
string pathname = Server.MapPath("~\\Admin\\CountScore.xls;");
OleDbConnection ExcelConnection = default(OleDbConnection);
ExcelConnection = new OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + pathname + "Extended Properties=Excel 8.0;");
ExcelConnection.Open();
string strComand = "select * from [" + strSheetName + "

这篇关于上传excel和csv文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！