/// <summary>
/// 保存excel文件
/// </summary>
public void SaveToExcel(DataTable dt,string projectID,string strFileName)
{
OleDbConnection conn = null;
try
{
#region 填充模板EXCEL
string path = string.Empty;
if (SysContext.CurrentLanguage.ToLower() == "cn")
{
path = Server.MapPath(@"..\UpLoad") + @"\CostCodeTemplate\ProjectBudgetMaster_cn.xls";//2003格式
}
else
{
path = Server.MapPath(@"..\UpLoad") + @"\CostCodeTemplate\ProjectBudgetMaster_cn.xls";//2003格式
}
string sfileName = Server.MapPath(@"..\UpLoad") + @"\CostCode\" + strFileName;
//
//File.SetAttributes(path,FileAttributes.Normal);
File.Copy(path, sfileName, true);
string strConn = "Provider=Microsoft.ACE.OleDb.12.0;Data Source=" + sfileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=0'";
conn = new OleDbConnection(strConn);
conn.Open();
DataTable oSheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string sTableName = "";
if (sTableName == "")
{
sTableName = oSheets.Rows[0]["TABLE_NAME"].ToString();
}
System.Data.DataSet dsCostCode = new DataSet();
if (sTableName != "")
{
System.Data.OleDb.OleDbDataAdapter daCostCost = new System.Data.OleDb.OleDbDataAdapter();
//System.Data.DataSet dsCostCode = new DataSet();
string tableName = "AccountCode";
// 使用数据适配器读取EXCEL中的数据并导入至DATASET数据集中；
daCostCost.SelectCommand = new System.Data.OleDb.OleDbCommand("Select * From [" + sTableName + "]");
daCostCost.SelectCommand.Connection = conn;
daCostCost.Fill(dsCostCode, tableName);
}
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
for (int i = 0; i < dt.Rows.Count; i++)
{
cmd.CommandText = "INSERT INTO [" + sTableName + "] (";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[0].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[1].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[2].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[3].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[4].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[5].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[6].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[7].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[8].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[9].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[10].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[11].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[12].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[13].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[14].ColumnName + "],";
cmd.CommandText += "[" + dsCostCode.Tables[0].Columns[15].ColumnName + "]";
cmd.CommandText += ")VALUES('" + GetProjectName(projectID) + "',";
cmd.CommandText += "'" + dt.Rows[i]["AccountCodeName"] + "',";
cmd.CommandText += "'" + GetTransaction(dt.Rows[i]["AccountTypeStringCode"].ToString()) + "',";
cmd.CommandText += "'" + decimal.Round(Convert.ToDecimal(dt.Rows[i]["EstimatedAmt"]),2) + "',";
cmd.CommandText += "'" + decimal.Round(Convert.ToDecimal(dt.Rows[i]["AboveDirectAmt"]),2) + "',";
cmd.CommandText += "'" + dt.Rows[i]["FormulaDesc"] + "',";
cmd.CommandText += "'" + decimal.Round(Convert.ToDecimal(dt.Rows[i]["DirectBudgetAmt"]),2) + "',";
cmd.CommandText += "'" + dt.Rows[i]["AttributeName"] + "',";
cmd.CommandText += "'" + decimal.Round(Convert.ToDecimal(dt.Rows[i]["ApportionBudgetAmt"]),2) + "',";
cmd.CommandText += "'" + decimal.Round(Convert.ToDecimal(dt.Rows[i]["SummaryBudgetAmt"]),2) + "',";
cmd.CommandText += "'" + decimal.Round(Convert.ToDecimal(dt.Rows[i]["BudgetAmt"]), 2) + "',";
cmd.CommandText += "'" + dt.Rows[i]["AccountCode"] + "',";
cmd.CommandText += "'" + dt.Rows[i]["IsLastNode"] + "',";
cmd.CommandText += "'" + dt.Rows[i]["AccountType"] + "',";
cmd.CommandText += "'" + dt.Rows[i]["AllocMethod"] + "',";
cmd.CommandText += "'" + dt.Rows[i]["Level"] + "')";
cmd.ExecuteNonQuery();
}
conn.Close();
#endregion
#region 保存文件到客户端
FileInfo file = new FileInfo(sfileName);
Response.Clear();
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename =" + Server.UrlEncode(file.Name));
Response.WriteFile(file.FullName);
Response.End();
#endregion
}
catch (System.Data.OleDb.OleDbException ex)
{
conn.Close();
System.Diagnostics.Debug.WriteLine("写入Excel发生错误：" + ex.Message);
}
finally
{
//string sfileName = Server.MapPath(@"..\UpLoad") + @"\AccountCodeTemplate\" + strFileName;
////删除文件
//if (File.Exists(sfileName))
//{
// File.Delete(sfileName);
//}
}
}
