如何在一个已知的文件夹中创建一个名为aaa.xls的EXCEL文件,并将查询结果放入文件中?
查询语句是
select * from table1 where name="aaa"
查询语句是
select * from table1 where name="aaa"
解决方案 »
- 连接局域网中的另外一台SQLSERVER2008
- 请高手进来指点membership成员管理问题~~~在线等...............................
- 怎么在后台CS文件中引用前面HTML控件或javascript中的变量值???
- webconfig文件 乱码了
- txt文件导入access出错
- 请教一个关于存储过程返回值的问题
- ASP.NET是否可以调用PB写的数据库接口程序?
- 请问谁有验证是否数字的C#函数啊?多谢了!
- 怎样判断两个字符串“内容”相等
- 用IE浏览aspx文件显示的全是源码,请问这是怎么回事?
- Oracle 导入出错
- 导出excel数据只有几行时正常,十几行时就中文乱码了,为什么
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace TestAccess
{
class Program
{
static void Main(string[] args)
{
string strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";
strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;";
strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
OleDbConnection objConnection = new OleDbConnection(strConnection);
objConnection.Open();
OleDbDataAdapter myCommandd = new OleDbDataAdapter("select * from [Sheet1$]", objConnection);
DataSet ds = new DataSet();
myCommandd.Fill(ds, "[Sheet1$]");
System.Data.DataTable dt = ds.Tables["[Sheet1$]"];
Console.WriteLine(dt.Columns[0].ToString());
Console.WriteLine(dt.Columns[1].ToString());
DataRow drDisplay = dt.Rows[0];
int[] num = new int[dt.Columns.Count];
for (int j = 0; ; )
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (drDisplay[i] is DBNull) ;
else
num[i] += Convert.ToInt32(drDisplay[i]);
}
if (++j >= dt.Rows.Count) break;
drDisplay = dt.Rows[j];
}
objConnection.Close();
object MissingValue = Type.Missing;
Microsoft.Office.Interop.Excel.Application app = new Application();
Microsoft.Office.Interop.Excel.Workbook wbook = app.Workbooks.Open(@"C:\Documents and Settings\v-changl\My Documents\couse.xlsx", MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue);
Microsoft.Office.Interop.Excel.Worksheet wsheet = wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
for (int i = 0; i < dt.Columns.Count; i++)
{
//注意下面是i+1,,excel小标默认从1开始
wsheet.Cells[dt.Rows.Count + 2, i + 1] = num[i].ToString();
}
wbook.Save();
wbook.Close(true, null, null);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wsheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
wsheet = null;
wbook = null;
app = null;
GC.Collect();
}
}
}本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/LCL_data/archive/2009/05/06/4154784.aspx
strConnection += @"Data Source=C:\Documents and Settings\v-changl\My Documents\couse.xlsx;";
strConnection += "Extended Properties=\"Excel 12.0 Xml;HDR=YES\";"; 这段是什么意思
,'Excel 5.0;HDR=YES;DATABASE=excel文件路径',sheet1$)
select * from 表名
存储过程调用情况如下
SqlCommand cmd = new SqlCommand("gongzi_excel", Conn);
cmd.CommandType = CommandType.StoredProcedure;//指定命令类型为存储过程 SqlParameter Para1 = new SqlParameter("@date",SqlDbType.DateTime);
Para1.Value =rq;
cmd.Parameters.Add(Para1); SqlParameter Para2 = new SqlParameter("@QiShu",SqlDbType.Int);
Para2.Value =qs;
cmd.Parameters.Add(Para2); SqlParameter Para3 = new SqlParameter("@serveid",SqlDbType.VarChar);
Para3.Value =serverid;
cmd.Parameters.Add(Para3); cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr=cmd.ExecuteNonQuery();存储过程中的查询语句如下:select * from table1 where date=@date and qishu=@QiShu and serverid=@serveid