最近项目中需要导出Excel数据表单,试了好几种方法,都感觉不怎么顺手,然后老大喊我去看看OLEDB,我接着花世间去学习了一下,感觉还挺不错的。
开始在网上找了一些代码,
然后需要配置连接字符串
我电脑上安装的office 2007:
所以连接字符串为:"Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filepath + ";Extended properties=\"Excel 12.0; HDR=Yes;\"";
HDR=Yes 表示第一行包含列名,在计算行数时就不包含第一行
IMEX 0:导入模式,1:导出模式:2混合模式
在VS上一跑,发现会报错
网上去找了一下原因,发现很多人都遇到过这种问题,所以答案很快就找到了,原来是没有安装“AccessDatabaseEngine.exe”这个插件。
于是去网上下载安装之后,先前的代码就能够跑了。
下面是一些简单的操作语句:
1.从Excel里读取数据
string str1="select * from [Sheet1$]";
2.更新Excel里的数据
string str2="update [Sheet1$] set FieldName1='aaa' where FiledName2='30'";
3.向Excel里写入数据
string str3="insert into [Sheet1$](FieldName1,FieldName2,...) values('a','b',...)";
对于非标准结构的Excel表格,可以采用以下方法:
4.读取数据
string str4="select * from [Sheet1$A3:F20]";
5.更新数据
string str5="update [Sheet1$A9:F15] set FieldName='bbb' where AnotherFieldName='b3'";
6.插入数据
string str6="insert into [Sheet1$A9:F15](FildName1,FieldName2,..) values('a','b',...)";
下面是我的一些测试代码:
向Excel写入数据
string strFilePath = "E:\\excel1.xls";
string str1 = "insert into [Sheet1$](商户ID,商家名称)values('DJ001','点击科技')";//excel1.xls必须已经存在,而且已有列名
DoOleSql(str1,strFilePath);
新建Excel表并写入数据
string filepaths = "E:\\excel2.xls";
string stra= "CREATE TABLE CustomerInfo ([CustomerID] VarChar,[Customer] VarChar)";
string strb = "insert into CustomerInfo(CustomerID,Customer)values('DJ001','点击科技')";
DoOleSql(stra,filepaths);
DoOleSql(strb,filepaths);
以上两个代码中用到的DoOleSql函数如下:
protected void DoOleSql(string sql, string filepath)
{
OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filepath + ";Extended properties=\"Excel 12.0; HDR=Yes;\"";
try
{//打开连接
conn.Open();
}
catch (Exception e)
{
//Response.Write(e.ToString());
}
OleDbCommand olecommand = new OleDbCommand(sql, conn);
try
{//执行语句
olecommand.ExecuteNonQuery();
}
catch (Exception eee)
{
//Response.Write(eee.ToString());
conn.Close();
}
finally
{
conn.Close();//关闭数据库
}
conn.Close();
}
将DataTable里的数据导出到Excel里
protected void Button3_Click(object sender, EventArgs e)
{
SqlHelp sqla = new SqlHelp();
string strfaca = "select * from tOAPower order by LoginID asc";
DataTable dta = sqla.GetDataTable(strfaca);
sqla.SqlClose();
//string modelpath = "E:\\staff.xlsx";
string realpath = "E:\\staff.xls";
DataTable2Excel(dta,realpath,500);
}
/// <summary>
/// 根据DataTable生成Excel
/// </summary>
/// <param name="dataTable">数据源</param>
/// <param name="fileName">要保存的路径</param>
/// <param name=" rowsCount ">当一个工作表最多的行数rowsCount,当超过时,则新建工作表。</param>
/// <returns>生成成功则返回True,否则返回False</returns>
public static bool DataTable2Excel(DataTable dataTable, string fileName, int rowsCount)
{
bool rt = false;//用于返回值
if (dataTable == null && rowsCount < 1)
{
return false;
}
int rowNum = dataTable.Rows.Count;//获取行数
int colNum = dataTable.Columns.Count;//获取列数
int SheetNum = (rowNum - 1) / rowsCount + 1; //获取工作表数
string sqlText = "";//带类型的列名
string sqlValues = "";//值
string colCaption = "";//列名
for (int i = 0; i < colNum; i++)
{
if (i != 0)
{
sqlText += " , ";
colCaption += " , ";
}
sqlText += "[" + dataTable.Columns[i].Caption.ToString() + "] VarChar";//生成带VarChar列的标题
colCaption += "[" + dataTable.Columns[i].Caption.ToString() + "]";//生成列的标题
}
String sConnectionString = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + fileName + ";Extended properties=\"Excel 12.0; HDR=Yes;\"";
OleDbConnection cn = new OleDbConnection(sConnectionString);
try
{
//判断文件是否存在,存在则先删除
if (File.Exists(fileName))
{
File.Delete(fileName);
}
int sheet = 1;//表数
int dbRow = 0;//数据的行数
//打开连接
cn.Open();
while (sheet <= SheetNum)
{
string sqlCreate = "CREATE TABLE [Sheet" + sheet.ToString() + "] (" + sqlText + ")";
OleDbCommand cmd = new OleDbCommand(sqlCreate, cn);
//创建Excel文件
cmd.ExecuteNonQuery();
for (int srow = 0; srow < rowsCount; srow++)
{
sqlValues = "";
for (int col = 0; col < colNum; col++)
{
if (col != 0)
{
sqlValues += " , ";
}
sqlValues += "'" + dataTable.Rows[dbRow][col].ToString() + "'";//拼接Value语句
}
String queryString = "INSERT INTO [Sheet" + sheet.ToString() + "] (" + colCaption + ") VALUES (" + sqlValues + ")";
cmd.CommandText = queryString;
cmd.ExecuteNonQuery();//插入数据
dbRow++;//目前数据的行数自增
if (dbRow >= rowNum)
{
//目前数据的行数等于rowNum时退出循环
break;
}
}
sheet++;
}
rt = true;
}
catch
{
}
finally
{
cn.Close();
}
return rt;
}
大体情况就是这个样子,我现在还有一些小问题,比如导出的Excel文档,不是严格意义上的Excel文档,打开的时候会出现这种情况
如果各位有解决方法的,请指教哦。