急急急!.net 导出EXCEL表格
页面上添加了一个LINKBUTTON,后台写如下代码。
错误提示:'还款详细$' 不是一个有效名称。请确认它不包含无效的字符或标点,且名称不太长。
private void ExportADONet()
{//导excel
int loaid = Convert.ToInt32(Request.QueryString["ID"]);
//获取Session中的数据
//DataTable dt = (DataTable)Session["dtData"];
OperateClass CData = new OperateClass();
DataTable dtDatabase = CData.ExecuteSqlRead("select repDate,repAcont,repMony,decMony,BjYe,addDate,remark from repInfo where LoaID=" + loaid);
DataTable dt = new DataTable();
DataColumn dc = new DataColumn("还款日期", typeof(System.DateTime));
dt.Columns.Add(dc);
dc = new DataColumn("扣款账号", typeof(System.String));
dt.Columns.Add(dc);
dc = new DataColumn("还款金额", typeof(System.Decimal));
dt.Columns.Add(dc);
dc = new DataColumn("扣息金额", typeof(System.Decimal ));
dt.Columns.Add(dc);
dc = new DataColumn("本金余额", typeof(System.Decimal ));
dt.Columns.Add(dc);
dc = new DataColumn("录入日期", typeof(System.DateTime ));
dt.Columns.Add(dc);
dc = new DataColumn("备注", typeof(System.String));
dt.Columns.Add(dc);
int allcount = dtDatabase.Rows.Count;
for (int i = 0; i < allcount; i++)
{
DataRow dr = dt.NewRow();
dr[0] =Convert.ToDateTime( dtDatabase.Rows[i]["repDate"].ToString()).ToShortDateString ();
//dr[1] = dtDatabase.Rows[i]["market_code"].ToString();
//dr[2] = dtDatabase.Rows[i]["position"].ToString();
dr[1] =dtDatabase.Rows[i]["repAcont"].ToString();
dr[2] = Convert.ToDecimal(dtDatabase.Rows[i]["repMony"].ToString());
dr[3] = Convert.ToDecimal(dtDatabase.Rows[i]["decMony"].ToString());
dr[4] = Convert.ToDecimal(dtDatabase.Rows[i]["BjYe"].ToString());
dr[5] = Convert.ToDateTime(dtDatabase.Rows[i]["addDate"].ToString()).ToShortDateString();
dr[6] = dtDatabase.Rows[i]["remark"].ToString();
dt.Rows.Add(dr);
}
string strTemplateFileName = Request.MapPath(".") + "\\xls\\模板_还款详细.xls";
//Response.Write(Request.MapPath("."));
//Response.End();
//string strTemplateFileName = "\\xls\\模板_员工信息.xls";
//生成临时文件
string strFileName = Request.MapPath(".") + "\\" + System.Guid.NewGuid() + ".xls";
File.Copy(strTemplateFileName, strFileName, true);
//打开链接
string strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
strFileName + ";Extended Properties=\"Excel 8.0;\"";
OleDbConnection cn = new OleDbConnection(strCn);
cn.Open();
ArrayList commandLists = BuildInsertCommand(dt, cn, null);//产生插入OleDbCommand
foreach (OleDbCommand cmdInsert in commandLists)
{
cmdInsert.ExecuteNonQuery();//执行OleDbCommand插入
}
OleDbCommand cmdUpdate = BuildUpdateCommand(dt, cn, null);//产生更新第一行数据
cmdUpdate.ExecuteNonQuery();//执行OleDbCommand更新,将Excel第一行数据更改
cn.Close(); //关闭连接
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.WriteFile(strFileName);
string namexls = "还款详细" + System.DateTime.Now.Year + "-" + System.DateTime.Now.Month + "-" + System.DateTime.Now.Day + ".xls";
string httpHeader = "attachment;filename=" + HttpUtility.UrlEncode(namexls);
response.AppendHeader("Content-Disposition", httpHeader);
response.Flush();
System.IO.File.Delete(strFileName);//删除临时文件
response.End();
}
private OleDbCommand BuildUpdateCommand(DataTable dt, System.Data.OleDb.OleDbConnection cn, System.Data.OleDb.OleDbTransaction trans)
{
string maskedColumnNames = "", columnNames = "";
string updateSql = "UPDATE [{0}$] SET {1} WHERE ({2})";
string entityKey = string.Format("[{0}] is null", "还款日期");
foreach (DataColumn dc in dt.Columns)
{
maskedColumnNames += "[" + dc.ColumnName + string.Format("]={0},", "@" + dc.ColumnName);
columnNames += dc.ColumnName + ",";
}
if (columnNames.Length > 0) columnNames = columnNames.Substring(0, columnNames.Length - 1); //去掉最后一个","号
if (maskedColumnNames.Length > 0) maskedColumnNames = maskedColumnNames.Substring(0, maskedColumnNames.Length - 1);
string[] columns = columnNames.Split(',');
OleDbCommand command = new OleDbCommand(string.Format(updateSql, "还款详细", maskedColumnNames, entityKey), cn, trans);
DataRow dr = dt.Rows[0];
foreach (string column in columns)
{
System.Type columnType = dt.Columns[column].DataType;
OleDbParameter param = new OleDbParameter("@" + column, MapOleDbDataType(columnType));
param.Value = dr[column];
command.Parameters.Add(param);
}
return command;
}
private ArrayList BuildInsertCommand(DataTable dt, System.Data.OleDb.OleDbConnection cn, System.Data.OleDb.OleDbTransaction trans)
{
string insertSql = "INSERT INTO [{0}$]({1}) VALUES({2})";
string columnNames = "";
string maskedColumnNames = "";
string parameterNames = "";
ArrayList commandArray = new ArrayList();
//构造字段列表
foreach (DataColumn dc in dt.Columns)
{
maskedColumnNames += "[" + dc.ColumnName + "],";
columnNames += dc.ColumnName + ",";
parameterNames += "@" + dc.ColumnName + ",";
}
if (columnNames.Length > 0) columnNames = columnNames.Substring(0, columnNames.Length - 1); //去掉最后一个","号
if (maskedColumnNames.Length > 0) maskedColumnNames = maskedColumnNames.Substring(0, maskedColumnNames.Length - 1);
if (parameterNames.Length > 0) parameterNames = parameterNames.Substring(0, parameterNames.Length - 1);//去掉最后一个","号
DataRow dr;
string[] columns = columnNames.Split(',');
for (int i = 1; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
OleDbCommand command = new OleDbCommand(string.Format(insertSql, "还款详细", maskedColumnNames, parameterNames), cn, trans);
foreach (string column in columns)
{
System.Type columnType = dt.Columns[column].DataType;
OleDbParameter param = new OleDbParameter("@" + column, MapOleDbDataType(columnType));
param.Value = dr[column];
command.Parameters.Add(param);
}
commandArray.Add(command);
}
return commandArray;
}
private System.Data.OleDb.OleDbType MapOleDbDataType(System.Type columnType)
{
switch (columnType.ToString())
{
case "System.Int16":
return OleDbType.Integer;
case "System.Int32":
return OleDbType.Integer;
case "System.Int64":
return OleDbType.BigInt;
case "System.String":
return OleDbType.VarChar;
case "System.DateTime":
return OleDbType.Date;
case "System.Decimal":
return OleDbType.Decimal;
default:
return OleDbType.VarChar;
}
}
protected void LinkButton1_Click(object sender, EventArgs e)
{
ExportADONet();
}