怎样将从数据库中读出来的数据插入到Excel表中
并且指定在Excel中的第几行开始插入
Ado.net或com都行
并且指定在Excel中的第几行开始插入
Ado.net或com都行
解决方案 »
- 同步登录
- 重写textbox,为什么不能赋值
- App_Themes样式为何不会显示在设计视图中?
- 快来,看看我这个<a>加runat=server以后怎么绑定呢?
- 请教:使用asp.net开发网页,能否升级到IE9?
- datagrid数据绑定问题
- IIS身份验证问题
- asp.net2.0发邮件,尽管已设置了webconfig,为什么还是提示not authorized
- 我想问一个软件工程的问题。构建一个CRM管理系统需要经过的步骤。
- 为什么我的电脑老在报错:用户 'NT AUTHORITY\NETWORK SERVICE' 登录失败
- .net简单计算器的问题(有代码)
- 请问如何用这个动态改变gridview宽度的函数?
Excel.Application excelApp=new Excel.Application();
object filename = _filePath;
object MissingValue = Type.Missing;
//加载Excel文件.
Excel.Workbook ew;
Excel.Workbooks wbs;
wbs = excelApp.Workbooks;
ew = wbs.Open(filename.ToString(), MissingValue,
false, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue, MissingValue,
MissingValue, MissingValue); excelApp.DisplayAlerts = false; Excel.Range rg = (Excel.Range)ws.Cells[nRow, nCol];
//改变单元格颜色
rg.Interior.ColorIndex = 3;
ew.Saved = true;
ew.SaveAs(filename, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, Excel.XlSaveAsAccessMode.xlExclusive
, MissingValue, MissingValue, MissingValue, MissingValue);
excelApp.Quit();
我在insert的时候没法确定excel每列的字段名 就插入不了值
con.Open();
//创建Excel应用程序
Excel.Application xApp=new Excel.ApplicationClass();
if (xApp == null)
{
return ;
}
//打开指定路径的Excel文件
Excel._Workbook xBook=xApp.Workbooks.Open(fileName,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
Excel.Sheets xSheets=xBook.Worksheets;
Excel._Worksheet xSheet=(Excel._Worksheet) xSheets.get_Item(1);
if (xSheet == null)
{
return;
}
if(_StrIsModify=="b")
{
xApp.Visible=true;
}
//写数据集表头 xSheet.Cells[8,1] =dt.Rows[0]["Client_name"].ToString();
xSheet.Cells[10,6] =dt.Rows[0]["Client_Sign"].ToString();
xSheet.Cells[11,6] ="CA"+System.DateTime.Now.ToString("yyMMddhhmmss");
xSheet.Cells[12,6] =System.DateTime.Now.Date.ToShortDateString();
int intCount=dtt.Rows.Count;
Double intTotalMoney=0;
for(int k=0;k<intCount;k++)
{
intTotalMoney=intTotalMoney+Convert.ToDouble(dtt.Rows[k]["ChargeIn_Debt"].ToString());
for(int l=0;l<dtt.Columns.Count;l++)
{
xSheet.Cells[k+16,l+1] = dtt.Rows[k][l].ToString();
}
} Excel.Range rang= xSheet.get_Range(xSheet.Cells[16+intCount+1, 1], xSheet.Cells[16+intCount+1, 1]);
rang.Select();
rang.Value2= "总计:"+intTotalMoney+"RMB";
decimal atm=Convert.ToDecimal(intTotalMoney);
decimal amk;
//美元汇率//decimal amk=Convert.ToDecimal(myDbCon.sQuery(sqlMoneykind));
string sqlMoneykind="use "+Limit.SE+" select MoneyKind_ExRate,convert(varchar,MoneyKind_Start,111)MoneyKind_Start,convert(varchar,MoneyKind_End,111)MoneyKind_End from MoneyKind where MoneyKind_Name='美元' and MoneyKind_Start<='"+dt.Rows[0]["EndDate"].ToString()+"' and MoneyKind_End>='"+dt.Rows[0]["StartDate"].ToString()+"' ";
SqlDataAdapter ad=new SqlDataAdapter(sqlMoneykind,con);
DataSet ds=new DataSet();
ad.Fill(ds,"MoneyKind");
string sqlMoneyusa="use "+Limit.SE+" select 'cs'=count(*) from MoneyKind where MoneyKind_Name='美元' and MoneyKind_Start<='"+dt.Rows[0]["EndDate"].ToString()+"' and MoneyKind_End>='"+dt.Rows[0]["StartDate"].ToString()+"' ";
SqlDataAdapter adusa=new SqlDataAdapter(sqlMoneyusa,con);
DataSet dsa=new DataSet();
adusa.Fill(dsa,"MoneyKind");
if(Convert.ToInt32(dsa.Tables[0].Rows[0][0].ToString())>0)
{ amk=Convert.ToDecimal(ds.Tables["MoneyKind"].Rows[0][0].ToString());
}
else
{
amk=1;
Response.Write("<script>alert('汇率设置不完整!')</script>");
}
decimal my=atm/amk; Excel.Range rang2= xSheet.get_Range(xSheet.Cells[16+intCount+1, 4], xSheet.Cells[16+intCount+1, 4]);
rang2.Select();
rang2.Value2= "美元:"+my.ToString("0.00");
xSheet.Cells[24,8]=amk.ToString();
//美元汇率期限
string startUsa=ds.Tables[0].Rows[0][1].ToString();
xSheet.Cells[24,5]=startUsa.ToString()+"----";
string endUsa=ds.Tables[0].Rows[0][2].ToString();
xSheet.Cells[24,6]=endUsa.ToString(); //港币//decimal amk2=Convert.ToDecimal(myDbCon.sQuery(sqlMoneykind2));
decimal amk2=1;
string sqlMoneykind2="use "+Limit.SE+" select MoneyKind_ExRate,MoneyKind_Start,MoneyKind_End from MoneyKind where MoneyKind_Name='港币' and MoneyKind_Start<='"+dt.Rows[0]["EndDate"].ToString()+"' and MoneyKind_End>='"+dt.Rows[0]["StartDate"].ToString()+"' ";
SqlDataAdapter ad2=new SqlDataAdapter(sqlMoneykind2,con);
DataSet ds2=new DataSet();
ad2.Fill(ds2,"MoneyKind"); string sqlMoneyhong="use "+Limit.SE+" select 'cs'=count(*) from MoneyKind where MoneyKind_Name='港币' and MoneyKind_Start<='"+dt.Rows[0]["EndDate"].ToString()+"' and MoneyKind_End>='"+dt.Rows[0]["StartDate"].ToString()+"' ";
SqlDataAdapter adhong=new SqlDataAdapter(sqlMoneyhong,con);
DataSet dshong=new DataSet();
adhong.Fill(dshong,"MoneyKind");
if(Convert.ToInt32(dshong.Tables[0].Rows[0][0].ToString())>0)
{
amk2=Convert.ToDecimal(ds2.Tables[0].Rows[0][0].ToString());
}
else
{
Response.Write("<script>alert('汇率设置不完整!')</script>");
}
decimal my2=atm/amk2;
Excel.Range rang3= xSheet.get_Range(xSheet.Cells[16+intCount+1, 7], xSheet.Cells[16+intCount+1, 7]);
rang3.Select();
rang3.Value2= "港币:"+my2.ToString("0.00");
xSheet.Cells[24,10]=amk2.ToString();
xBook.Save();
if(_StrIsModify=="a")
{
xApp.Quit();
xApp=null;
GC.Collect();
}
con.Close();
}