可以把操作EXCEL当成操作ACCESS,改一下数据库连接对象。
解决方案 »
- 网络传输问题
- 求:"5"的整数倍的正则表达式
- 在asp.net中,用getElementById改变HREF的值,这么简单的程序为什么会出错,找不出原因啊,帮忙,谢谢
- 求一个Repeater分页。。。效果见。。。
- 高手帮忙,我不会js,谁来看一下啊
- redirect为什么没有target属性。有什么办法只在Server端中实现这种功能?
- 简单问题大家帮帮忙!!!
- 怎么制作客户端的activex control,,就像windows更新程序时候的进度条
- 手写ajax在。net中创建工具提示
- 求教,用vs.net2005建立了1个ASP窗口后,却无法把控件拖到窗口上来,可我的书说可以拖
- 关于IsPostback的一个问题
- 关于DirectoryEntry的Invoke方法的问题(只有10分了,大家帮帮忙)
1 导出CSV,应为CSV默认用Excel打开,所以这也是导出到Excel的一种方法,不过它对编码的支持不是很好
2 导出到XML电子表格,是Excel自己的XML格式的电子表格,Excel支持很好,缺点是XML文件不一定是用Excel打开,所以客户也许不知道用Excel打开XML文件
3 用automation导出xls文件,缺点极大,需要服务器端安装Excel
4 最高级的,直接写XLS的二进制文件,开发成本较大,需要研究Excel文件的格式,但是对用户来说最完美。可以参考OpenOffice网站上的Excel文件格式来学习。
long totalCount=dt.Rows.Count;
long rowRead=0;
float percent=0;
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +strFullName +";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
OleDbCommand objCmd = new OleDbCommand();
objCmd.Connection = objConn;
objConn.Open();
try
{
objCmd.CommandText = "drop table Sheet1";
objCmd.ExecuteNonQuery();
}
catch
{
} objCmd.CommandText = @"CREATE TABLE Sheet1(产品名称 varchar,产品类型 varchar,用户ID varchar)";
objCmd.ExecuteNonQuery(); for(int i=0;i<dt.Rows.Count;i++)
{
string prodname = dt.Rows[i][0].ToString();
string prodtype = dt.Rows[i][1].ToString();
string userid = dt.Rows[i][2].ToString();
objCmd.CommandText = "insert into Sheet1 values ('"+prodname+"','"+prodtype+"','"+userid+"')";
objCmd.ExecuteNonQuery();
rowRead++;
percent=((float)(100*rowRead))/totalCount;
}
objConn.Close(); Response.ContentType="application/ms-excel";
Response.AddHeader("Content-Disposition","attachment; filename="+xls+"");
Response.WriteFile(strFullName);
Response.End();
'--------------------------------------
'sample of creating excel that used com
'--------------------------------------
Private Sub getexcel()
Dim filename As String
filename = ""
Dim i As Integer
Dim j As Integer = 1
Dim oExcel As Excel.ApplicationClass
oExcel = New Excel.ApplicationClass
oExcel.UserControl = False
Dim osheets As Excel.Sheets
Dim oSheet As Excel.Worksheet
Dim wb As Excel.WorkbookClass
wb = oExcel.Workbooks.Add(System.Reflection.Missing.Value)
osheets = wb.Sheets
oSheet = osheets.Item(1)
oSheet.Name = "sheet名"
Dim dt As New DataTable
Try
'write excel file
oSheet.Cells(1, 1) = "aa"
oSheet.Cells(1, 2) = "bb"
oSheet.Cells(1, 3) = "cc"
For i = 0 To 5
oSheet.Cells(i, 1) = 1
oSheet.Cells(i, 2) = 1
next
wb.Saved = True
filename = Request.PhysicalApplicationPath + "temp/test.xls"
oExcel.ActiveWorkbook.SaveCopyAs(filename)
oExcel.Quit()
System.GC.Collect()
ReleaseComObject(oSheet)
ReleaseComObject(osheets)
osheets = Nothing : oSheet = Nothing
Response.Redirect(Request.ApplicationPath + "/temp/test.xls", False)
Catch ex As Exception
oExcel.Quit()
System.GC.Collect()
ReleaseComObject(oSheet)
ReleaseComObject(osheets)
osheets = Nothing : oSheet = Nothing
Throw ex
End Try
End Sub
如下 验证过的东东
//创建一个数据链接
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = c:\\sample.xls;Extended Properties=Excel 8.0" ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
myConn.Open ( ) ;
string strCom1 = " SELECT * FROM [Sheet1$] " ; //选择出数据
//string strCom1 = "update [Sheet1$] set name='ggg' " ;//更新数据
OleDbCommand myconmm=new OleDbCommand (strCom1,myConn);
myconmm.ExecuteNonQuery ();
//打开数据链接,得到一个数据集
OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;
//创建一个 DataSet对象
DataSet myDataSet = new DataSet ( ) ;
//得到自己的DataSet对象
myCommand.Fill ( myDataSet , "[Sheet1$]" ) ;
//关闭此数据链接
myConn.Close ( ) ;
/// <summary>
///导出成EXCEL文件。
/// </summary>
/// <param name="ds">要导出的数据集`</param>
/// <param name="typeid">typeid=="1"时导出为EXCEL格式文件;typeid=="2"时导出为XML格式文件</param>
/// <param name="FileName">墨认文件名</param>
public void CreateExcel(DataSet ds,string typeid,string FileName)
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders= "", ls_item="";
int i=0; //定义表对象与行对像,同时用DataSet对其值进行初始化
DataTable dt=ds.Tables[0];
DataRow[] myRow=dt.Select("");
// typeid=="1"时导出为EXCEL格式文件;typeid=="2"时导出为XML格式文件
if(typeid=="1")
{
resp.ContentType = "application/ms-excel";
//取得数据表各列标题,各标题之间以\t分割,最后一个列标题后加回车符
for(i=0;i<dt.Columns.Count-1;i++)
{
colHeaders+=dt.Columns[i].Caption.ToString()+"\t";
}
colHeaders +=dt.Columns[i].Caption.ToString() +"\n";
//向HTTP输出流中写入取得的数据信息
resp.Write(colHeaders);
//逐行处理数据
foreach(DataRow row in myRow)
{
//在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n
for(i=0;i<dt.Columns.Count-1;i++)
{
if(row[i].ToString().Equals(null))
{
ls_item += ""+"\t";
}
else
{
// if(row[i].ToString().Length > 2)
// {
// if(row[i].ToString().Substring(row[i].ToString().Length-2)=="\r\n")
// {
// ls_item += row[i].ToString().Substring(0,row[i].ToString().Length-2) +"\t";
// }
// else
// {
// ls_item +=row[i].ToString() + "\t";
// }
// }
// else
// {
// ls_item +=row[i].ToString() + "\t";
// }
///替换掉字段中的换行与回车符号
ls_item += row[i].ToString().Replace("\r\n","") + "\t";
}
}
ls_item += row[i].ToString() +"\n";
//当前行数据写入HTTP输出流,并且置空ls_item以便下行数据
resp.Write(ls_item);
ls_item="";
}
}
else
{
if(typeid=="2")
{
//从DataSet中直接导出XML数据并且写到HTTP输出流中
resp.Write(ds.GetXml());
}
}
//写缓冲区中的数据到HTTP头文件中
resp.End();
}
private void ReadExcel()
{
DataTable rs=new DataTable();
rs=null;
DataSet rDs=null;
string strConn="";
int count=0;
DateTime createtime=DateTime.Now ;
string files=this.LoadfileDataGrid.SelectedItem.Cells[2].Text ;//获得文件名称
string userid=this.LoadfileDataGrid.SelectedItem.Cells[6].Text ;
string [] aa=files.Split(':');
string fileName=aa[1].ToString();
string path = Server.MapPath("../upparts/"+fileName); string filepath="..\\upparts\\"+fileName;
if (File.Exists (Server.MapPath(filepath)))//判断文件是否存在
{
strConn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+path+";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; //IMEX=1 用来转换文本
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",strConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet,"[Sheet1$]");
string sqlstr;
foreach(DataRow row in myDataSet.Tables[0].Rows)
{
count++;
string classid="";
string name="";
string model="";
string price="";
string num="";
string mfg="";
string package="";
string re="";
classid=row["产品类型"].ToString();
name=row["产品名称"].ToString();
model=row["产品型号"].ToString();
price=row["产品价格"].ToString();
num=row["数 量"].ToString();
mfg=row["生产厂商"].ToString();
package=row["封 装"].ToString();
re=row["交易说明"].ToString();
if (name!="" && model!="")
{
sqlstr="insert into tab_part values('"+name+"','"+model+"','"+num+"','"+package+"','"+mfg+"','"+price+"','"+re+"','"+classid+"','"+createtime+"','"+userid+"','0')";
data1.moddata(sqlstr);
}
}
data1.Alert2 ("上传记录成功!","BatchAddParts.aspx",Page);
}
else
{
data1.Alert2("没有找到相应的模板文件","BatchAddParts.aspx",Page);
}
//return rs=rDs.Tables[0];}
#endregion
要价200大洋
string FileName="d:\\abc.xls";long totalCount=dt.Rows.Count;
long rowRead=0;
float percent=0;
Excel.Application xlApp=null;
xlApp=new Excel.Application();
Excel.Workbooks workbooks=xlApp.Workbooks;
Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Range range;//写入字段
for(int i=0;i<dt.Columns.Count;i++)
{
worksheet.Cells[1,i+1]=dt.Columns[i].ColumnName;
range=(Excel.Range)worksheet.Cells[1,i+1];
}
for(int r=0;r<dt.Rows.Count;r++)
{
worksheet.Cells[r+2,1]=r+1;
for(int i=0;i<dt.Columns.Count;i++)
{
//worksheet.Cells[r+2,i+1]=dt.Rows[r][i];
if(i+1!=dt.Columns.Count)
worksheet.Cells[r+2,i+2]=dt.Rows[r][i+1];
}
rowRead++;
percent=((float)(100*rowRead))/totalCount;
//this.FM.CaptionText.Text = "正在导出数据,已导出[" + percent.ToString("0.00") + "%]...";
System.Windows.Forms .Application.DoEvents();
}
range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[dt.Rows.Count+2,dt.Columns.Count]);
workbook.Saved =true;
workbook.SaveCopyAs(FileName);
object missing = Missing.Value;
ApplicationClass excel;
_Workbook xBk;
_Worksheet xSt; excel = new ApplicationClass();
xBk = excel.Workbooks.Open(Server.MapPath(FileName), missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, false);
xSt = (_Worksheet)xBk.ActiveSheet;//方法二:创建一个excel GC.Collect();
ApplicationClass excel;
//行
int rowIndex = 0;
_Workbook xBk;
_Worksheet xSt;
excel = new ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (_Worksheet)xBk.ActiveSheet;
//读取excel里的第一行第一列值
string test=xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]).Value2.ToString();
//写入excel里的第一行第一列值
excel.Cells[1, 1]=“test”;保存excelstring sTimes = DateTime.Now.ToString("yyyyMMddHHmmss");
string strFileName = "";
System.IO.FileInfo file;
strFileName = Server.MapPath("Uploads/qafiles/") + sTimes + ".xls";
xBk.SaveCopyAs(strFileName);
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
file = new System.IO.FileInfo(strFileName);
Response.Clear();
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
Response.AddHeader("Content-Length", file.Length.ToString());
Response.ContentType = "application/ms-excel";
Response.WriteFile(file.FullName);
Response.End();
string sql = "select * from rdstudent";
DataTable dt = SQLHelper.GetTable(sql);
//把数据excel发送给客户端
Response.Clear();
Response.AddHeader("Content-Disposition",
"attchment;fileName=" + Server.UrlEncode("学生信息.xls"));
//Response.AddHeader("content-type", "charset=utf-8");
//Response.Charset = "gb2312";
Response.ContentEncoding = Encoding.Default;//服务器的编码格式
Response.ContentType = "application/vnd.ms-excel";
//这个附件中的内容
StringBuilder sb = new StringBuilder();
for (int i= 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
sb.Append(dr["stuid"].ToString() + "\t");
sb.Append(dr["studname"].ToString() + "\t");
sb.Append(dr["sex"].ToString()).Append ("\t");
sb.Append(dr["phone"].ToString());
sb.Append("\r\n");
//sb这样的一个字符串放入excel时,\t对应的是一个单元格
//\r\n对应的是下一行
}
string content= Server.HtmlEncode(sb.ToString());
// sb.ToString().ToCharArray();
Response.Write(sb.ToString ());
//把内容发送出去
Response.End();//后面的内容不要了