问题描述
数据导入Execl问题谁解决过,有代码的请帖上来。。。。
解决方案
解决方案二:
在VB.NET同样可以将报表导出到Excel和Word进行输出,制作出专业水平的报表。具体操作如下:(注:首先需添加引用,选择COM-->选择MicrosoftWordObjectLibrary和MicrosoftExcelObjectLibrary组件)PrivateFunctionCreaTable()AsDataTableDimdtAsNewDataTable()dt.Columns.Add("列1",GetType(String))dt.Columns.Add("列2",GetType(Integer))dt.Columns.Add("列3",GetType(String))dt.Columns.Add("列4",GetType(String))Dimrow,row1AsDataRowrow=dt.NewRow()row!列1="行1"row!列2=1row!列3="d"row!列4="a"dt.Rows.Add(row)row1=dt.NewRow()row1!列1="行2"row1!列2=12row1!列3="b"row1!列4="c"dt.Rows.Add(row1)ReturndtEndFunction'2.将表中的内容导出到ExcelDimxlAppAsNewExcel.Application()DimxlBookAsExcel.WorkbookDimxlSheetAsExcel.WorksheetDimrowIndexAsInteger=1DimcolIndexAsInteger=0xlBook=xlApp.Workbooks().AddxlSheet=xlBook.Worksheets("sheet1")DimTableAsNewDataTable()Table=CreaTable()'将所得到的表的列名,赋值给单元格DimColAsDataColumnDimRowAsDataRowForEachColInTable.ColumnscolIndex=colIndex+1xlApp.Cells(1,colIndex)=Col.ColumnNameNext'得到的表所有行,赋值给单元格ForEachRowInTable.RowsrowIndex=rowIndex+1colIndex=0ForEachColInTable.ColumnscolIndex=colIndex+1xlApp.Cells(rowIndex,colIndex)=Row(Col.ColumnName)NextNextWithxlSheet.Range(.Cells(1,1),.Cells(1,colIndex)).Font.Name="黑体"'设标题为黑体字.Range(.Cells(1,1),.Cells(1,colIndex)).Font.Bold=True'标题字体加粗.Range(.Cells(1,1),.Cells(rowIndex,colIndex)).Borders.LineStyle=1'设表格边框样式EndWithWithxlSheet.PageSetup.LeftHeader=""&Chr(10)&"&""楷体_GB2312,常规""&10公司名称:"'&Gsmc.CenterHeader="&""楷体_GB2312,常规""公司人员情况表&""宋体,常规"""&Chr(10)&_"&""楷体_GB2312,常规""&10日期:".RightHeader=""&Chr(10)&"&""楷体_GB2312,常规""&10单位:".LeftFooter="&""楷体_GB2312,常规""&10制表人:".CenterFooter="&""楷体_GB2312,常规""&10制表日期:".RightFooter="&""楷体_GB2312,常规""&10第&P页共&N页"EndWithxlApp.Visible=True'3.将表中的内容导出到WORDDimwordAppAsNewWord.Application()DimmyDocAsWord.DocumentDimoTableAsWord.TableDimrowIndex,colIndexAsIntegerrowIndex=1colIndex=0wordApp.Documents.Add()myDoc=wordApp.ActiveDocumentDimTableAsNewDataTable()Table=CreaTable()oTable=myDoc.Tables.Add(Range:=myDoc.Range(Start:=0,End:=0),_NumRows:=Table.Rows.Count+1,NumColumns:=Table.Columns.Count)'将所得到的表的列名,赋值给单元格DimColAsDataColumnDimRowAsDataRowForEachColInTable.ColumnscolIndex=colIndex+1oTable.Cell(1,colIndex).Range.InsertAfter(Col.ColumnName)Next'得到的表所有行,赋值给单元格ForEachRowInTable.RowsrowIndex=rowIndex+1colIndex=0ForEachColInTable.ColumnscolIndex=colIndex+1oTable.Cell(rowIndex,colIndex).Range.InsertAfter(Row(Col.ColumnName))NextNextoTable.Borders.InsideLineStyle=1oTable.Borders.OutsideLineStyle=1wordApp.Visible=True
解决方案三:
privatevoidbtnReadData_Click(objectsender,System.EventArgse){if(File1.Value.Length>1){stringfileName=System.IO.Path.GetFileName(File1.PostedFile.FileName);stringfilePath="";if(this.File1.Value==""){//Comm.Jscript.Alert("请先选择您要导入的文件!");}else{intindex=fileName.LastIndexOf(".");if(index>0){if(fileName.Substring(index)==".xls"){DateTimenow=DateTime.Now;fileName=now.ToShortDateString()+now.ToLongTimeString();fileName=fileName.Replace("-","").Replace(":","").Replace("","");filePath=@"../uploads/"+fileName+".xls";this.File1.PostedFile.SaveAs(Server.MapPath(filePath));}else{//Comm.Jscript.Alert("读入的文件不是XLS");}}}if(filePath!=""){fileName=Request.MapPath(filePath);//stringstrConn="Provider=Microsoft.Jet.Oledb.4.0;DataSource="+Dir+"\"+fileName+";ExtendedProperties="Excel8.0;HDR=Yes;IMEX=1;"stringstrConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+fileName+";ExtendedProperties='Excel8.0;HDR=Yes;IMEX=1;'";stringquery="SELECT*FROM[Sheet1$]";OleDbCommandoleCommand=newOleDbCommand(query,newOleDbConnection(strConn));OleDbDataAdapteroleAdapter=newOleDbDataAdapter(oleCommand);DataSetmyDataSet=newDataSet();//将Excel的[Sheet1]表内容填充到DataSet对象try{oleAdapter.Fill(myDataSet,"[Sheet1$]");//数据绑定this.dgExportProject.DataSource=myDataSet;this.dgExportProject.DataMember="[Sheet1$]";this.dgExportProject.DataBind();this.dgExportProject.Visible=true;this.btnSave.Visible=true;}catch(Exceptionexx){Response.Write(exx.Message);//Comm.Jscript.Alert("注意:请用默认的Sheet1$页名称!");}finally{if(File.Exists(filePath)){File.Delete(filePath);}}}}else{Response.Write("<script>alert('请选择正确的路径!')</script>");}}privatevoidbtnSave_Click(objectsender,System.EventArgse){stringconn=ConfigurationSettings.AppSettings["datasource"];SqlConnectionsqlConn=newSqlConnection(conn);{try{if(sqlConn.State==ConnectionState.Closed){sqlConn.Open();}foreach(DataGridItemiteminthis.dgExportProject.Items){if(item.Cells.Count==2){stringwldm=item.Cells[0].Text.Trim().Replace(" ","");stringcgjg=item.Cells[1].Text.Trim().Replace(" ","");stringsSQL="InsertIntowljg(wldm,cgjg)Values('"+wldm+"','"+cgjg+"')";SqlCommandcmd=newSqlCommand(sSQL,sqlConn);cmd.CommandType=CommandType.Text;try{cmd.ExecuteNonQuery();Response.Write("<script>alert('导入数据成功!')</script>");}catch{Response.Write("<script>alert('导入数据出错,请联系管理员!')</script>");}cmd.Dispose();}else{//Comm.Jscript.Alert("注意:请按照模板格式导入项目信息!");return;}}}catch(Exceptionexx){Response.Write(exx.Message);}finally{if(sqlConn.State==ConnectionState.Open){sqlConn.Close();}}}this.dgExportProject.Visible=false;this.btnSave.Visible=false;}}
解决方案四:
你要是操作Excel的API就比较麻烦了用OLEDB连上比较容易的
解决方案五:
StringWritersw=newStringWriter();sw.WriteLine("编号,状态,操作量");for(inti=0;i<数据List.Count;i++){sw.WriteLine(数据List[i].编号+"','"+数据List[i].状态+"','"+数据List[i].操作量);}sw.Close();Response.AddHeader("Content-Disposition","attachment;filename="+文件名+".xls");Response.ContentType="application/ms-excel";Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");Response.Write(sw);Response.End();其中的“数据List”为泛型数据源。
解决方案六:
该回复于2008-04-17 12:09:19被版主删除
解决方案七:
参考:
解决方案八:
本论坛就有,