.net 读取Excel文件


public void DSToExcel(string Path,DataSet oldds)   
{   
//先得到汇总Excel的DataSet 主要目的是获得Excel在DataSet中的结构   
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+path1+";Extended Properties=Excel 8.0" ;   
OleDbConnection myConn = new OleDbConnection(strCon) ;   
string strCom="select * from [Sheet1$]";   
myConn.Open ( ) ;   
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom, myConn ) ;   
system.Data.OleDb.OleDbCommandBuilder builder=new OleDbCommandBuilder(myCommand);   
//QuotePrefix和QuoteSuffix主要是对builder生成InsertComment命令时使用。   
builder.QuotePrefix="["; //获取insert语句中保留字符(起始位置)   
builder.QuoteSuffix="]"; //获取insert语句中保留字符(结束位置)   
DataSet newds=new DataSet();   
myCommand.Fill(newds ,"Table1") ;   
for(int i=0;i<oldds.Tables[0].Rows.Count;i++)   
{   
//在这里不能使用ImportRow方法将一行导入到news中,   
//因为ImportRow将保留原来DataRow的所有设置(DataRowState状态不变)。   
//在使用ImportRow后newds内有值,但不能更新到Excel中因为所有导入行的DataRowState!=Added   
DataRow nrow=aDataSet.Tables["Table1"].NewRow();   
for(int j=0;j<newds.Tables[0].Columns.Count;j++)   
{   
nrow[j]=oldds.Tables[0].Rows[i][j];   
}   
newds.Tables["Table1"].Rows.Add(nrow);   
}   
myCommand.Update(newds,"Table1");   
myConn.Close();   
} 
展开阅读全文

读取EXCEL文件

07-15

如何读取excel文件,那位能不能给个例子:下面是我的方法,但读excel文件时,读到的行不是有数据的行,而是所有行,列也是这样,是所有列,而不只是有数据的列,读的过程中我在电脑的任务管理器下看到有好几个EXCEL.EXE;电脑就死在那不动了??rn Prb_status.Value = 0rn ''读取多sheet的EXCELrn Dim xlApp As Microsoft.Office.Interop.Excel.Applicationrn Dim xlBook As Microsoft.Office.Interop.Excel.Workbookrn Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheetrn Dim xlsName As Stringrn If Me.OpenFD_Excel.ShowDialog = System.Windows.Forms.DialogResult.OK Thenrn xlsName = Me.OpenFD_Excel.FileNamern Elsern Exit Subrn End Ifrnrn xlApp = New Microsoft.Office.Interop.Excel.Applicationrn xlBook = xlApp.Workbooks.Open(xlsName)rn Dim m, n As Integer ''i,rn Dim str_min, str_max, str_date, str_ph, str_cl, str_mess As String '''str_value, str_name,rn 'For i = 3 To 15rn xlSheet = xlBook.Worksheets("4541")rn Prb_status.Maximum = xlSheet.Rows.Countrn 'Nextrn 'For i = 3 To 15 'xlBook.Worksheets.Count - 1rn 'xlSheet = xlBook.Worksheets(1)rn 'str_name = xlSheet.Namern str_mess = ""rn str_date = xlSheet.Cells._Default(1, 1).Valuern str_ph = xlSheet.Cells._Default(1, 2).Valuern If Convert.ToDecimal(dtoperator.executescalar("select count(*) from SP_BreakDown where STARTDATE='" + str_date + "' and MB001='" + str_ph + "'")) > 0 Thenrn For m = 2 To xlSheet.Rows.Count - 1rnrn str_min = ""rn str_max = ""rn str_cl = xlSheet.Cells._Default(m + 1, 1).Valuern If Prb_status.Value < Prb_status.Maximum Thenrn Prb_status.Value += 1rn End Ifrn For n = 0 To 14 ''xlSheet.Columns.Count - 1rn System.Windows.Forms.Application.DoEvents()rn If xlSheet.Cells._Default(2, n + 1).Value = "Min Quantity" Thenrn str_min = xlSheet.Cells._Default(m + 1, n + 1).Valuern End Ifrn If xlSheet.Cells._Default(2, n + 1).Value = "Max Quantity" Thenrn str_max = xlSheet.Cells._Default(m + 1, n + 1).Valuern End Ifrn Next nrn If str_min <> "" And str_max <> "" Thenrn If Convert.ToDecimal(dtoperator.executescalar("select count(*) from SP_BreakDown where STARTDATE='" + str_date + "' and MB001='" + str_ph + "' and SEVECOPN='" + str_cl + "'")) > 0 Thenrn dtoperator.executenonquery("update SP_BreakDown set MinQuantiry='" + str_min + "',MaxQuantity='" + str_max + "' where STARTDATE='" + str_date + "' and MB001='" + str_ph + "' and SEVECOPN='" + str_cl + "'")rn End Ifrn End Ifrn Next mrn Elsern str_mess = "所选产品在Summary中没有对应的日期的产品记录," + Chr(10) + Chr(13) + "请检查所所导入的日期的产品是否存在入Summary中."rn End Ifrn 'xlBook.Save()rn xlBook.Close()rn '用 Quit 方法关闭 Microsoft Excelrn xlApp.Quit()rn '释放对象rn Dim pro As Processrn For Each pro In Process.GetProcessesrn If pro.ProcessName = "EXCEL" Thenrn pro.Kill()rn End Ifrn Nextrn 'Nextrn If Prb_status.Value < Prb_status.Maximum Thenrn Prb_status.Value = Prb_status.Maximumrn End Ifrn If str_mess = "" Thenrn MessageBox.Show("安全库存导入完成.", "提示")rn Elsern MessageBox.Show(str_mess, "提示")rn End If 论坛

.net读取Excel表的问题

08-22

由于要读取到Excel表,所以上网看了2种解决方案rnrn1。 ADO.NETrnrn string fileName=null;rn if(DialogResult.OK==openFile.ShowDialog())rn fileName=openFile.FileName;rnrn string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+fileName+";Extended Properties=Excel 8.0" ;rn string strCmd="SELECT * FROM [Sheet1$]";rn OleDbConnection con = new OleDbConnection(strCon);rn tryrn rn con.Open();rn OleDbCommand cmd=new OleDbCommand(strCmd,con);rn OleDbDataAdapter da=new OleDbDataAdapter(cmd);rn DataSet ds=new DataSet();rn da.Fill(ds,"[Sheet1$]");rnrn rnrn rn rn rnrnrn rn catch(Exception ex)rn rn textBox1.Text=ex.Message;rn rn finallyrn rn con.Close();rn rnrn2. COMrn private void button1_Click(object sender, System.EventArgs e)rn rn System.Threading.Thread t=new System.Threading.Thread(new System.Threading.ThreadStart(run));rn t.Start();rn runExcel();rn rn private void run()rn rn runExcel();rn GC.Collect();rn rn private void runExcel()rn rn Excel.Application excel=new Excel.Application();rn object va=Type.Missing;rn excel.Application.Workbooks.Open(@"C:\hello.xls",va,va,va,va,va,va,va,va,va,va,va,va,va,va);rn rn Excel.Worksheet sheet=new Excel.WorksheetClass();rn Excel.Worksheet sheet=(Excel.Worksheet)excel.Worksheets[1];rn Excel.Range r=(Excel.Range)sheet.UsedRange.Cells[1,1];rn string s=r.Text.ToString(); rn textBox1.Text=s;rn excel.Workbooks.Close();rn excel.Quit(); rn System.Runtime.InteropServices.Marshal.ReleaseComObject(r);rn System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); rn System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);rn GC.Collect();rnrn rn excel=null;rn sheet=null;rn rn rnrn上面是2种实现代码,但是有如下问题:rnrn如果是ADO.NET 如果cell里面是日期,比如1990-1-1的话会返回1,那么延伸一下,对于Excel表里面的数据如果通过ado的方式读出来怎么对应数据类型和转换问题rn使用ADO.NET还有一个问题就是会把Excel的第一行默认为 列名 这个问题怎么解决?rnrn如果是COM,关于内存释放的问题呢?rnrnrn恳请熟悉这个方面的专家给一点意见或者资料,谢谢 论坛

!!!读取Excel文件问题!!!

05-12

遇到一个比较奇怪的问题,就是在服务器上直接测试读取正常,但是远程客户端访问时,却抛出异常,显示返回的DataSet为空,几经调整,确未发现代码存在问题,所以上来咨询各位,是否遇到过类似问题,或者给个解决建议,谢谢,顺便也贴代码出来rn读取代码如下:rn[code=csharp]rnpublic static DataSet ToDataTable(string filePath) rn rn string connStr = ""; rn string fileType = System.IO.Path.GetExtension(fileName); rn if (string.IsNullOrEmpty(fileType)) return null; rn rn if (fileType == ".xls") rn connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath+ ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\""; rn else rn connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath+ ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\""; rn string sql_F = "Select * FROM [0]"; rn rn OleDbConnection conn = null; rn OleDbDataAdapter da = null; rn DataTable dtSheetName= null; rn rn DataSet ds = new DataSet(); rn try rn rn // 初始化连接,并打开 rn conn = new OleDbConnection(connStr); rn conn.Open(); rn rn // 获取数据源的表定义元数据 rn string SheetName = ""; rn dtSheetName= conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] null, null, null, "TABLE" ); rn rn // 初始化适配器 rn da = new OleDbDataAdapter(); rn for (int i = 0; i < dtSheetName.Rows.Count; i++) rn rn SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"]; rn rn if (SheetName .Contains("$") && !SheetName .Replace("'", "").EndsWith("$")) rn rn continue; rn rn rn da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName ), conn); rn DataSet dsItem = new DataSet(); rn da.Fill(dsItem, tblName); rn rn ds.Tables.Add(dsItem.Tables[0].Copy()); rn rn rn catch (Exception ex) rn rn rn finally rn rn // 关闭连接 rn if (conn.State == ConnectionState.Open) rn rn conn.Close(); rn da.Dispose(); rn conn.Dispose(); rn rn rn return ds; rn rn[/code]rn遇到一个比较奇怪的 论坛

没有更多推荐了,返回首页