涉及到asp.net操作word的问题
今年陆续又碰到操作excel、word等office文档
使用的都是Interop相关dll,打开office相关进程进行操作
为免忘记,将相关tip整理如下:
1. 引用文件, 几个dll打包了
2. 操作word代码示例:
using
ImportExportToOffice;
using Microsoft.ApplicationBlocks.Data;
protected void btnJianJie_Click( object sender, EventArgs e)
{
string sYear = DropDownList2.SelectedValue;
string sMonth = DropDownList1.SelectedValue;
string sDate1 = String.Format( " {0}-{1}-{2} " , sYear, sMonth, " 1 " );
string sDate2 = String.Format( " {0}-{1}-{2} " , sYear, sMonth, System.DateTime.DaysInMonth( int .Parse(sYear), int .Parse(sMonth)).ToString());
word = new ImportExportToWord();
string sTmpUrl = " ../Report/ " + Guid.NewGuid().ToString() + " .Doc " ;
string sTmpFile = Server.MapPath(sTmpUrl);
string sModelDoc = Server.MapPath( " ../Report/国际漫游简报模板.doc " );
System.IO.File.Copy(sModelDoc, sTmpFile, true );
try
{
word.Open(sTmpFile);
object sBookMark = "" ;
// GSM 来访前十 分公司
sBookMark = " GSM_1 " ;
if (word.Document.Bookmarks.Exists(sBookMark.ToString()))
{
word.Document.Bookmarks.get_Item( ref sBookMark).Select();
string sSql = " SELECT TOP 10 t1.ToCarrier, COUNT(t1.ToCarrier) AS Num, t2.ShortName AS Name " +
" FROM BiteGSM t1 INNER JOIN " +
" BranchInfo t2 ON t1.ToCarrier = t2.ID " +
" INNER JOIN DispatchInfo t3 ON t1.DispatchID = t3.ID " +
" Where t3.SendTime >=' " + sDate1 + " ' " +
" And t3.SendTime <=' " + sDate2 + " ' " +
" And t1.SimType=1 " +
" GROUP BY t1.ToCarrier, t2.ShortName " +
" ORDER BY COUNT(t1.ToCarrier) DESC " ;
SqlDataReader dr = SqlHelper.ExecuteReader(Connection.ConnectionString, CommandType.Text, sSql);
while (dr.Read())
{
word.Application.Selection.TypeText(dr[ " Name " ].ToString());
word.GoToRightCell();
word.Application.Selection.TypeText(dr[ " Num " ].ToString());
word.GoToLeftCell();
word.GoToDownCell();
}
dr.Close();
}
// CDMA 来访前十 分公司
sBookMark = " CDMA_1 " ;
if (word.Document.Bookmarks.Exists(sBookMark.ToString()))
{
word.Document.Bookmarks.get_Item( ref sBookMark).Select();
string sSql = " SELECT TOP 10 t1.ToCarrier, COUNT(t1.ToCarrier) AS Num, t2.ShortName AS Name " +
" FROM BiteGSM t1 INNER JOIN " +
" BranchInfo t2 ON t1.ToCarrier = t2.ID " +
" INNER JOIN DispatchInfo t3 ON t1.DispatchID = t3.ID " +
" Where t3.SendTime >=' " + sDate1 + " ' " +
" And t3.SendTime <=' " + sDate2 + " ' " +
" And t1.SimType=2 " +
" GROUP BY t1.ToCarrier, t2.ShortName " +
" ORDER BY COUNT(t1.ToCarrier) DESC " ;
SqlDataReader dr = SqlHelper.ExecuteReader(Connection.ConnectionString, CommandType.Text, sSql);
while (dr.Read())
{
word.Application.Selection.TypeText(dr[ " Name " ].ToString());
word.GoToRightCell();
word.Application.Selection.TypeText(dr[ " Num " ].ToString());
word.GoToLeftCell();
word.GoToDownCell();
}
dr.Close();
}
// GSM 去访前十 分公司
sBookMark = " GSM1 " ;
if (word.Document.Bookmarks.Exists(sBookMark.ToString()))
{
word.Document.Bookmarks.get_Item( ref sBookMark).Select();
string sSql = " SELECT TOP 10 t1.ToCarrier, COUNT(t1.ToCarrier) AS Num, t2.CarrierName AS Name " +
" FROM Unicom2TSTT t1 INNER JOIN " +
" CarrierInfo t2 ON t1.ToCarrier = t2.ID " +
" INNER JOIN DispatchInfo t3 ON t1.DispatchID = t3.ID " +
" Where t3.SendTime >=' " + sDate1 + " ' " +
" And t3.SendTime <=' " + sDate2 + " ' " +
" And t1.SimType=1 " +
" GROUP BY t1.ToCarrier, t2.CarrierName " +
" ORDER BY COUNT(t1.ToCarrier) DESC " ;
SqlDataReader dr = SqlHelper.ExecuteReader(Connection.ConnectionString, CommandType.Text, sSql);
while (dr.Read())
{
word.Application.Selection.TypeText(dr[ " Name " ].ToString());
word.GoToRightCell();
word.Application.Selection.TypeText(dr[ " Num " ].ToString());
word.GoToLeftCell();
word.GoToDownCell();
}
dr.Close();
}
// CDMA 去访前十 分公司
sBookMark = " CDMA1 " ;
if (word.Document.Bookmarks.Exists(sBookMark.ToString()))
{
word.Document.Bookmarks.get_Item( ref sBookMark).Select();
string sSql = " SELECT TOP 10 t1.ToCarrier, COUNT(t1.ToCarrier) AS Num, t2.CarrierName AS Name " +
" FROM Unicom2TSTT t1 INNER JOIN " +
" CarrierInfo t2 ON t1.ToCarrier = t2.ID " +
" INNER JOIN DispatchInfo t3 ON t1.DispatchID = t3.ID " +
" Where t3.SendTime >=' " + sDate1 + " ' " +
" And t3.SendTime <=' " + sDate2 + " ' " +
" And t1.SimType=2 " +
" GROUP BY t1.ToCarrier, t2.CarrierName " +
" ORDER BY COUNT(t1.ToCarrier) DESC " ;
SqlDataReader dr = SqlHelper.ExecuteReader(Connection.ConnectionString, CommandType.Text, sSql);
while (dr.Read())
{
word.Application.Selection.TypeText(dr[ " Name " ].ToString());
word.GoToRightCell();
word.Application.Selection.TypeText(dr[ " Num " ].ToString());
word.GoToLeftCell();
word.GoToDownCell();
}
dr.Close();
}
word.SaveAs(sTmpFile);
word.Quit();
SysUtil.Log(UserContext.GetUserContext(), FunctionContext.GetFunctionContext(),OperType.其它, LogLevel.信息, " 生成国际漫游简报成功 " );
Response.Write( " <script>window.open(\ "" + sTmpUrl + " \ " )</script> " );
}
catch (Exception err)
{
try
{
word.Quit();
}
catch
{
}
SysUtil.Log(UserContext.GetUserContext(), FunctionContext.GetFunctionContext(),OperType.其它, LogLevel.信息, " 生成国际漫游简报失败,错误信息: " + err.Message);
}
}
using Microsoft.ApplicationBlocks.Data;
protected void btnJianJie_Click( object sender, EventArgs e)
{
string sYear = DropDownList2.SelectedValue;
string sMonth = DropDownList1.SelectedValue;
string sDate1 = String.Format( " {0}-{1}-{2} " , sYear, sMonth, " 1 " );
string sDate2 = String.Format( " {0}-{1}-{2} " , sYear, sMonth, System.DateTime.DaysInMonth( int .Parse(sYear), int .Parse(sMonth)).ToString());
word = new ImportExportToWord();
string sTmpUrl = " ../Report/ " + Guid.NewGuid().ToString() + " .Doc " ;
string sTmpFile = Server.MapPath(sTmpUrl);
string sModelDoc = Server.MapPath( " ../Report/国际漫游简报模板.doc " );
System.IO.File.Copy(sModelDoc, sTmpFile, true );
try
{
word.Open(sTmpFile);
object sBookMark = "" ;
// GSM 来访前十 分公司
sBookMark = " GSM_1 " ;
if (word.Document.Bookmarks.Exists(sBookMark.ToString()))
{
word.Document.Bookmarks.get_Item( ref sBookMark).Select();
string sSql = " SELECT TOP 10 t1.ToCarrier, COUNT(t1.ToCarrier) AS Num, t2.ShortName AS Name " +
" FROM BiteGSM t1 INNER JOIN " +
" BranchInfo t2 ON t1.ToCarrier = t2.ID " +
" INNER JOIN DispatchInfo t3 ON t1.DispatchID = t3.ID " +
" Where t3.SendTime >=' " + sDate1 + " ' " +
" And t3.SendTime <=' " + sDate2 + " ' " +
" And t1.SimType=1 " +
" GROUP BY t1.ToCarrier, t2.ShortName " +
" ORDER BY COUNT(t1.ToCarrier) DESC " ;
SqlDataReader dr = SqlHelper.ExecuteReader(Connection.ConnectionString, CommandType.Text, sSql);
while (dr.Read())
{
word.Application.Selection.TypeText(dr[ " Name " ].ToString());
word.GoToRightCell();
word.Application.Selection.TypeText(dr[ " Num " ].ToString());
word.GoToLeftCell();
word.GoToDownCell();
}
dr.Close();
}
// CDMA 来访前十 分公司
sBookMark = " CDMA_1 " ;
if (word.Document.Bookmarks.Exists(sBookMark.ToString()))
{
word.Document.Bookmarks.get_Item( ref sBookMark).Select();
string sSql = " SELECT TOP 10 t1.ToCarrier, COUNT(t1.ToCarrier) AS Num, t2.ShortName AS Name " +
" FROM BiteGSM t1 INNER JOIN " +
" BranchInfo t2 ON t1.ToCarrier = t2.ID " +
" INNER JOIN DispatchInfo t3 ON t1.DispatchID = t3.ID " +
" Where t3.SendTime >=' " + sDate1 + " ' " +
" And t3.SendTime <=' " + sDate2 + " ' " +
" And t1.SimType=2 " +
" GROUP BY t1.ToCarrier, t2.ShortName " +
" ORDER BY COUNT(t1.ToCarrier) DESC " ;
SqlDataReader dr = SqlHelper.ExecuteReader(Connection.ConnectionString, CommandType.Text, sSql);
while (dr.Read())
{
word.Application.Selection.TypeText(dr[ " Name " ].ToString());
word.GoToRightCell();
word.Application.Selection.TypeText(dr[ " Num " ].ToString());
word.GoToLeftCell();
word.GoToDownCell();
}
dr.Close();
}
// GSM 去访前十 分公司
sBookMark = " GSM1 " ;
if (word.Document.Bookmarks.Exists(sBookMark.ToString()))
{
word.Document.Bookmarks.get_Item( ref sBookMark).Select();
string sSql = " SELECT TOP 10 t1.ToCarrier, COUNT(t1.ToCarrier) AS Num, t2.CarrierName AS Name " +
" FROM Unicom2TSTT t1 INNER JOIN " +
" CarrierInfo t2 ON t1.ToCarrier = t2.ID " +
" INNER JOIN DispatchInfo t3 ON t1.DispatchID = t3.ID " +
" Where t3.SendTime >=' " + sDate1 + " ' " +
" And t3.SendTime <=' " + sDate2 + " ' " +
" And t1.SimType=1 " +
" GROUP BY t1.ToCarrier, t2.CarrierName " +
" ORDER BY COUNT(t1.ToCarrier) DESC " ;
SqlDataReader dr = SqlHelper.ExecuteReader(Connection.ConnectionString, CommandType.Text, sSql);
while (dr.Read())
{
word.Application.Selection.TypeText(dr[ " Name " ].ToString());
word.GoToRightCell();
word.Application.Selection.TypeText(dr[ " Num " ].ToString());
word.GoToLeftCell();
word.GoToDownCell();
}
dr.Close();
}
// CDMA 去访前十 分公司
sBookMark = " CDMA1 " ;
if (word.Document.Bookmarks.Exists(sBookMark.ToString()))
{
word.Document.Bookmarks.get_Item( ref sBookMark).Select();
string sSql = " SELECT TOP 10 t1.ToCarrier, COUNT(t1.ToCarrier) AS Num, t2.CarrierName AS Name " +
" FROM Unicom2TSTT t1 INNER JOIN " +
" CarrierInfo t2 ON t1.ToCarrier = t2.ID " +
" INNER JOIN DispatchInfo t3 ON t1.DispatchID = t3.ID " +
" Where t3.SendTime >=' " + sDate1 + " ' " +
" And t3.SendTime <=' " + sDate2 + " ' " +
" And t1.SimType=2 " +
" GROUP BY t1.ToCarrier, t2.CarrierName " +
" ORDER BY COUNT(t1.ToCarrier) DESC " ;
SqlDataReader dr = SqlHelper.ExecuteReader(Connection.ConnectionString, CommandType.Text, sSql);
while (dr.Read())
{
word.Application.Selection.TypeText(dr[ " Name " ].ToString());
word.GoToRightCell();
word.Application.Selection.TypeText(dr[ " Num " ].ToString());
word.GoToLeftCell();
word.GoToDownCell();
}
dr.Close();
}
word.SaveAs(sTmpFile);
word.Quit();
SysUtil.Log(UserContext.GetUserContext(), FunctionContext.GetFunctionContext(),OperType.其它, LogLevel.信息, " 生成国际漫游简报成功 " );
Response.Write( " <script>window.open(\ "" + sTmpUrl + " \ " )</script> " );
}
catch (Exception err)
{
try
{
word.Quit();
}
catch
{
}
SysUtil.Log(UserContext.GetUserContext(), FunctionContext.GetFunctionContext(),OperType.其它, LogLevel.信息, " 生成国际漫游简报失败,错误信息: " + err.Message);
}
}
3.部署相关: 确保服务器正确安装office 2k3
运行后若office进程起不来,如下检查:
1.运行Dcomcnfg.exe
2.组件服务――计算机――我的电脑――DCOM配置――找到microsoft word 文档
3.点击属性
4.选择“安全性”
5.选定“使用自定义访问权限”和“使用自定义启动权限”
6.分别编辑权限,添加Everyone(ASPNET,VS Developers,Debugger User)
7.选择“身份标识”,在选定“交互式用户” 即可
8.在Web.config里加 <identity impersonate="true"/>