从Excel读取数据到数据集 [英] Reading Data from Excel to dataset
问题描述
01/04/2013
SCRIPCODE SCRIP NAME CAPITAL（存款中的股票数量）
500002 ABB Ltd. 2119.08375
500003 Aegis Logistics Ltd. 334.29687
我想从上面读取数据excel
日期单独在上面下面列出的是col列名，下面是列的值。
如何从excel中读取数据，其中只有val为Date列？
在代码下方使用阅读
public void impPennyStockBSEBulkFile（string FilePath，int eximID）
{
try
{
DataSet ds = o_Cls_Utility.ReadExcelToDataSet（FilePath，SHEET1，F3，true）;
DateTime [] dDate =（来自ds中的行） .Tables [0] .AsEnumerable（）
选择Convert.ToDateTime（row [0]））。ToArray（）;
string [] strSCRIP_CODE = ds.Tables [0]
.AsEnumerable（）
。选择（row => row.Field< string>（SCRIPCODE）。修剪（））
.ToArray（）;
string [] strSCRIP_NAME = ds.Tables [0]
.AsEnumerable（）
.Select（row => row.Field< string>（SCRIP NAME）。Trim（））
.ToArray（）;
double [] dCAPITAL =（来自ds.Tables [0]中的行.AsEnumerable（）
选择Convert.ToDouble（CAPITAL ））ToArray的（）;
int [] neximID =（来自ds.Tables [0]中的行.AsEnumerable（）
选择Convert.ToInt32（eximID））。ToArray（）;
OracleConnect o_Cls_OracleConnect = new OracleConnect（）;
o_Cls_OracleConnect.OracleCommand_BulkInsert_PreInit（ ）; b / b
o_Cls_OracleConnect.OracleCommand_Date_AddParameters（0，sBulkDate，dDate）;
o_Cls_OracleConnect.OracleCommand_Varchar2_AddParameters（1，sScripCode，strSCRIP_CODE ）;
o_Cls_OracleConnect.OracleCommand_Varchar2_AddParameters（2，sScripName，strSCRIP_NAME）;
o_Cls_OracleConnect.OracleCommand_Double_AddParameters（3，sCapital，dCAPITAL）;
o_Cls_OracleConnect.OracleCommand_Number_AddParameters（4，nEXIM_ID，neximID）;
int recordsInserted = o_Cls_OracleConnect。 OracleCommand_BulkInsert（strSCRIP_CODE.Length，SP_PENNYSTOCK_BSE_BULKFILE_IMP）;
o_Cls_OracleConnect.Dispose（）;
}
并且仅显示在val下方的数据集
SCRIPCODE SCRIP NAME CAPITAL（存款中的股票数量）
500002 ABB Ltd. 2119.08375
500003 Aegis Logistics Ltd. 334.29687
此代码适用于c＃
if（FileUpload1.FileName.ToString（）==）
{
lblMsg.Text =请选择文件（.xls）;
返回;
}
string filExt =
FileUplo ad1.PostedFile.FileName.Substring（FileUpload1.PostedFile.FileName.LastIndexOf（。）+ 1）;
if（filExt.ToUpper（）！=XLS）
{
lblMsg.Text =仅支持Excel文件。;
FileUpload1.Focus（）;
return;
}
尝试
{
string strSQL =;
string bank =，CR =，DR =，Bal =;
DateTime EDOE;
int i = 0;
string FPath = Server.MapPath（〜/ PostReceive / BankFiles /）;
FPath = FPath + System.DateTime.Now.Date.ToString（dd-MMM-yyyy）+ .xls;
FileUpload1.PostedFile.SaveAs（FPath）;
lblMsg.Text =;
string connstr =Provider = Microsoft.Jet.Oledb.4.0;数据源=+ FPath +;
扩展属性= Excel 8.0 ;
OleDbConnection conn = new OleDbConnection（connstr）;
OleDbCommand cmd;
DataSet ds = new DataSet（）;
OleDbDataAdapter da;
OleDbCommand cmd1 = new OleDbCommand（）;
DataSet ds1 = new DataSet（）;
OleDbDataAdapter da1 = new OleDbDataAdapter（）;
lblTotal.Text =0.0;
strSQL =SELECT Name，Description，ChequeNo，DR，CR， BAL，格式（TRDate，'dd-MMM-yyyy'）
作为TDate FROM [NARAYAN
; //这里NARAYAN是Excel表格姓名
cmd =新OleDbCommand（strSQL，conn）; //表格中的栏目
//姓名，描述，ChequeNo，DR，CR ，BAL
da = new OleDbDataAdapter（cmd）;
da.Fill（ds，NTable）;
嗨......
看到这个链接，可能对你有用。
http://www.dotnetgallery.com/kb/resource19-How -to-export-data-to-excel-from-gridview-and-DataTable-using-aspnet.aspx [ ^ ]
谢谢你。
01/04/2013
SCRIPCODE SCRIP NAME CAPITAL(No. of shares in lacs)
500002 ABB Ltd. 2119.08375
500003 Aegis Logistics Ltd. 334.29687
I want to read data from above excel
Date is seperately in above excel below that are col names and below that are values for column.
How can i read data from excel where only val for Date column is there?
used below code to read
public void impPennyStockBSEBulkFile(string FilePath, int eximID)
{
try
{
DataSet ds = o_Cls_Utility.ReadExcelToDataSet(FilePath, "SHEET1", "F3", true);
DateTime[] dDate = (from row in ds.Tables[0].AsEnumerable()
select Convert.ToDateTime(row["0"])).ToArray();
string[] strSCRIP_CODE = ds.Tables[0]
.AsEnumerable()
.Select(row => row.Field<string>("SCRIPCODE").Trim())
.ToArray();
string[] strSCRIP_NAME = ds.Tables[0]
.AsEnumerable()
.Select(row => row.Field<string>("SCRIP NAME").Trim())
.ToArray();
double[] dCAPITAL = (from row in ds.Tables[0].AsEnumerable()
select Convert.ToDouble("CAPITAL")).ToArray();
int[] neximID = (from row in ds.Tables[0].AsEnumerable()
select Convert.ToInt32(eximID)).ToArray();
OracleConnect o_Cls_OracleConnect = new OracleConnect();
o_Cls_OracleConnect.OracleCommand_BulkInsert_PreInit();
o_Cls_OracleConnect.OracleCommand_Date_AddParameters(0, "sBulkDate", dDate);
o_Cls_OracleConnect.OracleCommand_Varchar2_AddParameters(1, "sScripCode", strSCRIP_CODE);
o_Cls_OracleConnect.OracleCommand_Varchar2_AddParameters(2, "sScripName", strSCRIP_NAME);
o_Cls_OracleConnect.OracleCommand_Double_AddParameters(3, "sCapital", dCAPITAL);
o_Cls_OracleConnect.OracleCommand_Number_AddParameters(4, "nEXIM_ID", neximID);
int recordsInserted = o_Cls_OracleConnect.OracleCommand_BulkInsert(strSCRIP_CODE.Length, "SP_PENNYSTOCK_BSE_BULKFILE_IMP");
o_Cls_OracleConnect.Dispose();
}
and in dataset only below val are shown
SCRIPCODE SCRIP NAME CAPITAL(No. of shares in lacs)
500002 ABB Ltd. 2119.08375
500003 Aegis Logistics Ltd. 334.29687解决方案This Code Works In c#
if (FileUpload1.FileName.ToString() == "")
{
lblMsg.Text = "Please Select File (.xls)";
return;
}
string filExt =
FileUpload1.PostedFile.FileName.Substring(FileUpload1.PostedFile.FileName.LastIndexOf(".") + 1);
if (filExt.ToUpper() != "XLS")
{
lblMsg.Text = "Only Excel files are supported.";
FileUpload1.Focus();
return;
}
try
{
string strSQL = "";
string bank="",CR="",DR="",Bal="";
DateTime EDOE;
int i = 0;
string FPath = Server.MapPath("~/PostReceive/BankFiles/");
FPath = FPath + System.DateTime.Now.Date.ToString("dd-MMM-yyyy") + ".xls";
FileUpload1.PostedFile.SaveAs(FPath);
lblMsg.Text = "";
string connstr = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + FPath + ";
Extended Properties=Excel 8.0";
OleDbConnection conn = new OleDbConnection(connstr);
OleDbCommand cmd;
DataSet ds = new DataSet();
OleDbDataAdapter da;
OleDbCommand cmd1 = new OleDbCommand();
DataSet ds1 = new DataSet();
OleDbDataAdapter da1 = new OleDbDataAdapter();
lblTotal.Text = "0.0";
strSQL = " SELECT Name,Description,ChequeNo,DR,CR,BAL,format(TRDate,'dd-MMM-yyyy')
as TDate FROM [NARAYAN
"; // Here NARAYAN is Excel Sheet Name
cmd = new OleDbCommand(strSQL, conn);// Column in sheet
// Name,Description,ChequeNo,DR,CR,BAL
da = new OleDbDataAdapter(cmd);
da.Fill(ds, "NTable");
Hi...
See this link, may its useful to u.
http://www.dotnetgallery.com/kb/resource19-How-to-export-data-to-excel-from-gridview-and-DataTable-using-aspnet.aspx[^]
Thank u.
这篇关于从Excel读取数据到数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
