通過模板將資料匯出word格式和excel格式下載：
例子程式碼如下：
using Newtonsoft.Json;
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
#region [全域性變數]
HiddenField literal_guest_list, literal_GuestJsonList;
private System.Data.DataTable dataTable = new System.Data.DataTable("table");
private DataRow dataRow = null;
private string strAddress = @"C:/Program Files/Common Files/Microsoft Shared/web server extensions/12/TEMPLATE/LAYOUTS/ERPResources";//存放路徑（word模版以及生成的word）--伺服器
#endregion
#region [控制元件載入]
protected void Page_Load(object sender, EventArgs e)
{
literal_guest_list = (HiddenField)FindControl("literal_guest_list");
literal_GuestJsonList = (HiddenField)FindControl("literal_GuestJsonList");
if (!Page.IsPostBack)
{
Page.DataBind();
}
}
#endregion
#region [基本屬性]
/// <summary>
/// 團隊編號
/// </summary>
private string _TeamID = string.Empty;
public string TeamID
{
get
{
if (string.IsNullOrEmpty(Request.QueryString["id"]))
{
_TeamID = string.Empty;
}
else
{
_TeamID = Request.QueryString["id"].ToString().Trim();
}
return _TeamID;
}
}
/// <summary>
/// 引數 用於哪個組的撤銷頁面
/// </summary>
private string _Type = string.Empty;
public string Type
{
get
{
_Type = string.IsNullOrEmpty(Request.QueryString["type"]) ? string.Empty : Request.QueryString["type"].ToString();
return _Type;
}
}
//團隊資訊
private TeamManagementData.VSTeamInfoDataTable mTeamInfo = null;
public TeamManagementData.VSTeamInfoDataTable TeamInfoTable
{
get
{
if (!string.IsNullOrEmpty(TeamID))
{
mTeamInfo = BFTeamManagement.GetAllTeamInfoByPK(TeamID);
mTeamInfo = mTeamInfo.Rows.Count > 0 ? mTeamInfo : null;
}
return mTeamInfo;
}
}
#endregion
#region [操作方法]
/// <summary>
/// 匯出Word檔案
/// </summary>
private void OutputPrint(string type)
{
GC.Collect();
DateTime dateBegin = DateTime.Now;
string strWordSourcepath = string.Empty, strWordPath = string.Empty, strFileName = string.Empty, strSaveFileName = string.Empty;
if (type == "word")
{
strWordSourcepath = strAddress + @"/WordTemplate/";//word模版存放位置
strWordPath = strAddress + @"/erpWord/";//存放路徑
strFileName = "簽證名單表.doc"; //模版名稱
strSaveFileName = string.Format("簽證名單表{0:yyyyMMddhhmmss}.doc", DateTime.Now); //存放名稱
}
else
{
strWordSourcepath = strAddress + @"/ExcelTemplate/";//excl模版存放位置
strWordPath = strAddress + @"/erpExcel/";//存放路徑
strFileName = "簽證名單表.xls"; //模版名稱
strSaveFileName = string.Format("簽證名單表{0:yyyyMMddhhmmss}.xls", DateTime.Now); //存放名稱
}
if (!Directory.Exists(strWordPath))
{
try
{
Directory.CreateDirectory(strWordPath); //word存放路徑
}
catch { }
}
if (Directory.Exists(strWordSourcepath) && File.Exists(strWordSourcepath + strFileName))//模版路徑
{
string strDesFilePath = strWordPath + strSaveFileName;
try
{
if (!strDesFilePath.Equals(""))
{
FileInfo fi = new FileInfo(strDesFilePath);
//判斷檔案屬性是否只讀?是則修改為一般屬性再刪除
if (fi.Attributes.ToString().IndexOf("ReadOnly") != -1)
{
fi.Attributes = FileAttributes.Normal;
}
}
}
catch (Exception err)
{
throw err;
}
try
{
File.Copy(strWordSourcepath + strFileName, strDesFilePath, true);
if (type == "word")
//建立Word
CreateWord(strDesFilePath);
else
CareateExcel(strDesFilePath);
//下載檔案
//FileDownload(strWordPath + strSaveFileName);
Byecity2009.Erp.Web.CommonMethod.FileDownload(Response, strWordPath + strSaveFileName);
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// 匯出Word檔案
/// </summary>
/// <param name="path">路徑</param>
private void CreateWord(string path)
{
//定義一個缺少的引數
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Word.Application _App = null;
try
{
//word save path
object FullFileName = path;
//declare document variable
Word._Document MyDoc = null;
try
{
_App = new Microsoft.Office.Interop.Word.Application();
MyDoc = new Word.Document();
object Visible = false;
//open document
MyDoc = _App.Documents.Open(ref FullFileName, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref Visible,
ref missing, ref missing, ref missing,
ref missing);
//啟用MyDoc
MyDoc.Activate();
//動態新增行
if (dataTable.Rows.Count > 0)
{
for (int j = 0; j < dataTable.Rows.Count; j++)
{
object beforeRows = MyDoc.Tables[1].Cell(4, 1).Tables[1].Rows[j + 2];
MyDoc.Tables[1].Cell(4, 1).Tables[1].Rows.Add(ref beforeRows);
}
}
MyDoc.Tables[1].Cell(2, 2).Range.Text = TeamInfoTable != null ? TeamInfoTable[0]["ProductName"].ToString() : ""; //線路名稱
MyDoc.Tables[1].Cell(3, 2).Range.Text = TeamInfoTable != null ? TeamInfoTable[0]["TeamNum"].ToString() : ""; //團隊編號
if (dataTable != null && dataTable.Rows.Count > 0)
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
MyDoc.Tables[1].Cell(4, 1).Tables[1].Cell(i + 2, 1).Range.Text = (i + 1).ToString();
MyDoc.Tables[1].Cell(4, 1).Tables[1].Cell(i + 2, 2).Range.Text = dataTable.Rows[i]["Name"].ToString();
MyDoc.Tables[1].Cell(4, 1).Tables[1].Cell(i + 2, 3).Range.Text = dataTable.Rows[i]["UserNameEn"].ToString();
MyDoc.Tables[1].Cell(4, 1).Tables[1].Cell(i + 2, 4).Range.Text = dataTable.Rows[i]["Gender"].ToString();
MyDoc.Tables[1].Cell(4, 1).Tables[1].Cell(i + 2, 5).Range.Text = Convert.ToDateTime(dataTable.Rows[i]["Birthday"].ToString()).ToString("yyyy-MM-dd");
MyDoc.Tables[1].Cell(4, 1).Tables[1].Cell(i + 2, 6).Range.Text = dataTable.Rows[i]["Passport"].ToString();
MyDoc.Tables[1].Cell(4, 1).Tables[1].Cell(i + 2, 7).Range.Text = dataTable.Rows[i]["PassportAddress"].ToString();
MyDoc.Tables[1].Cell(4, 1).Tables[1].Cell(i + 2, 8).Range.Text = dataTable.Rows[i]["SaleName"].ToString();
MyDoc.Tables[1].Cell(4, 1).Tables[1].Cell(i + 2, 9).Range.Text = dataTable.Rows[i]["ClientCompanyName"].ToString();
}
}
//刪除多餘行
MyDoc.Tables[1].Cell(4, 1).Tables[1].Rows.Last.Delete();
MyDoc.SaveAs(ref FullFileName, ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing);
}
catch (Exception ex)
{
throw ex;
}
finally
{
// 關閉，釋放
if (MyDoc != null)
{
MyDoc.Close(ref missing, ref missing, ref missing);
MyDoc = null;
}
}
}
finally
{
// 關閉，釋放
if (_App != null)
{
ErpCommon.KillProcess("winword");
_App = null;
}
}
}
/// <summary>
/// 客人詳細
/// </summary>
/// <returns></returns>
private string getVisaList()
{
dataTable.Columns.Add("ID", System.Type.GetType("System.String"));
dataTable.Columns.Add("Name", System.Type.GetType("System.String"));
dataTable.Columns.Add("UserNameEn", System.Type.GetType("System.String"));
dataTable.Columns.Add("Gender", System.Type.GetType("System.String"));
dataTable.Columns.Add("Birthday", System.Type.GetType("System.DateTime"));
dataTable.Columns.Add("Passport", System.Type.GetType("System.String"));
dataTable.Columns.Add("PassportAddress", System.Type.GetType("System.String"));
dataTable.Columns.Add("SaleName", System.Type.GetType("System.String"));
dataTable.Columns.Add("ClientCompanyName", System.Type.GetType("System.String"));
StringBuilder buffer = new StringBuilder();
string[] GuestList = literal_GuestJsonList.Value.Split('|');
int GuestID = 0, ClientID = 0, SellID = 0;
Dictionary<string, string>[] arrGuestInfo = new Dictionary<string, string>[GuestList.Length];
for (int i = 0; i < arrGuestInfo.Length; i++)
{
arrGuestInfo[i] = (Dictionary<string, string>)JavaScriptConvert.DeserializeObject(GuestList[i].ToString(), typeof(Dictionary<string, string>));
if (!string.IsNullOrEmpty(arrGuestInfo[i]["GuestID"]) && !string.IsNullOrEmpty(arrGuestInfo[i]["ClientID"]) && !string.IsNullOrEmpty(arrGuestInfo[i]["SellID"]))//獲取資料資訊
{
GuestID = Convert.ToInt32(arrGuestInfo[i]["GuestID"]);
ClientID = Convert.ToInt32(arrGuestInfo[i]["ClientID"]);
SellID = Convert.ToInt32(arrGuestInfo[i]["SellID"]);
TeamManagementData.V_GuestListDataTable vvdt = BFTeamManagement.GetTeamOperateGuestListDataByWhereCondition(" and TeamID=" + TeamID + " and GuestID=" + GuestID + " and sellID=" + SellID + " and ClientID=" + ClientID);
if (vvdt.Rows.Count > 0)
{
buffer.Append("<tr><td>" + (i + 1) + "</td>");
buffer.Append("<td>" + vvdt[0]["Name"].ToString() + "</td>");
buffer.Append("<td>" + vvdt[0]["UserNameEn"].ToString() + "</td>");
buffer.Append("<td>" + vvdt[0]["Gender"].ToString() + "</td>");
buffer.Append("<td>" + (string.IsNullOrEmpty(vvdt[0]["Birthday"].ToString()) ? "" : DateTime.Parse(vvdt[0]["Birthday"].ToString()).ToString("yyyy-MM-dd")) + "</td>");
buffer.Append("<td>" + vvdt[0]["Passport"].ToString() + "</td>");
buffer.Append("<td>" + vvdt[0]["PassportAddress"].ToString() + "</td>");
buffer.Append("<td>" + vvdt[0]["SaleName"].ToString() + "</td>");
buffer.Append("<td>" + vvdt[0]["ClientCompanyName"].ToString() + "</td>");
buffer.Append("</tr>");
dataRow = dataTable.NewRow();
dataRow["ID"] = i + 1;
dataRow["Name"] = vvdt[0]["Name"].ToString();
dataRow["UserNameEn"] = vvdt[0]["UserNameEn"].ToString();
dataRow["Gender"] = vvdt[0]["Gender"].ToString();
dataRow["Birthday"] = (string.IsNullOrEmpty(vvdt[0]["Birthday"].ToString()) ? "" : DateTime.Parse(vvdt[0]["Birthday"].ToString()).ToString("yyyy-MM-dd"));
dataRow["Passport"] = vvdt[0]["Passport"].ToString();
dataRow["PassportAddress"] = vvdt[0]["PassportAddress"].ToString();
dataRow["SaleName"] = vvdt[0]["SaleName"].ToString();
dataRow["ClientCompanyName"] = vvdt[0]["ClientCompanyName"].ToString();
dataTable.Rows.Add(dataRow);
}
}
}
return buffer.ToString();
}
/// <summary>
/// 匯出Excel檔案
/// </summary>
/// <param name="table">資料</param>
/// <param name="context"></param>
private void CareateExcel(string path)
{
//請求一個Excel的類
Excel.ApplicationClass excel = null;
Excel._Workbook workbook = null;
Excel._Worksheet worksheet = null;
try
{
excel = new Excel.ApplicationClass();
//要儲存的檔名
string FullFileName = path;
object missing = System.Reflection.Missing.Value;
try
{
//開啟模板檔案，得到WorkBook物件
workbook = excel.Workbooks.Open(FullFileName, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
worksheet = (Excel.Worksheet)workbook.Sheets[1];//指定操作第一個表
//啟用MyDoc
workbook.Activate();
//給主表新增資料
excel.Cells[2, 3] = TeamInfoTable[0]["ProductName"].ToString();
excel.Cells[3, 3] = TeamInfoTable[0]["TeamNum"].ToString();
if (dataTable != null && dataTable.Rows.Count > 0)
{
for (int i = 0; i < dataTable.Rows.Count; i++)
{
excel.Cells[i + 5, 1] = (i + 1).ToString();
excel.Cells[i + 5, 2] = dataTable.Rows[i]["Name"].ToString();
excel.Cells[i + 5, 3] = dataTable.Rows[i]["UserNameEn"].ToString();
excel.Cells[i + 5, 4] = dataTable.Rows[i]["Gender"].ToString();
excel.Cells[i + 5, 5] = Convert.ToDateTime(dataTable.Rows[i]["Birthday"].ToString()).ToString("yyyy-MM-dd");
excel.Cells[i + 5, 6] = dataTable.Rows[i]["Passport"].ToString();
excel.Cells[i + 5, 7] = dataTable.Rows[i]["PassportAddress"].ToString();
excel.Cells[i + 5, 8] = dataTable.Rows[i]["SaleName"].ToString();
excel.Cells[i + 5, 9] = dataTable.Rows[i]["ClientCompanyName"].ToString();
}
}
excel.Application.DisplayAlerts = false; //不顯示提示資訊
workbook.SaveAs(FullFileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
}
finally
{
// 關閉，釋放
if (workbook != null)
{
workbook.Close(false, null, null);
workbook = null;
worksheet = null;
}
}
}
finally
{
// 關閉，釋放
if (excel != null)
{
excel.Quit();
ErpCommon.KillExcel(excel);
excel = null;
}
}
}
#endregion
#region [操作事件]
protected void btnExportWord_Click(object sender, CommandEventArgs e)
{
switch (e.CommandName)
{
case "ExportWord":
getVisaList();
OutputPrint("word");
break;
case "ExportExcel":
getVisaList();
OutputPrint("excel");
break;
}
}
#endregion