程序目的:EXCEL文件存放数据,根据提供的模板,生成相应的word文件,每条数据生成一页数据。
Excel操作:取数据
using
System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace WorkBookForHKAccountDoc
{
public class ExcelOperation
{
public delegate void ErrorEventHandler(object sender, OnErrorEventArgs e);
public event ErrorEventHandler OnError = null;
public delegate void InfoEventHandler(object sender, OnPrintInfoEventArgs e);
public event InfoEventHandler OnPrintInfo = null;
获取EXCEL中用户信息列表#region 获取EXCEL中用户信息列表
/**//// <summary>
/// 获取EXCEL中用户信息列表
/// </summary>
/// <returns></returns>
public System.Collections.Generic.List<AccountInfo> getAccountList(string strExcelFilePathName)
{
Excel.Application appXSL = new Microsoft.Office.Interop.Excel.Application();
System.Collections.Generic.List<AccountInfo> accountList = new System.Collections.Generic.List<AccountInfo>();
Excel.Workbook workbook;
Excel.Worksheet sheet;
OnPrintInfo(this, new OnPrintInfoEventArgs("解析EXCEL文件"));
workbook = appXSL.Workbooks.Open(strExcelFilePathName, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
sheet = appXSL.Sheets[1] as Excel.Worksheet;
for (int i = 2; i < sheet.Rows.Count; i++)
//for (int i = 2; i < 95; i++)
{
AccountInfo accInfo = new AccountInfo();
try
{
accInfo.txtClient_Main_Account = ((Excel.Range)sheet.Cells[i, 1]).Text.ToString();
if (accInfo.txtClient_Main_Account.Length == 0)
{
break;
}
accInfo.txtClient_Properity = ((Excel.Range)sheet.Cells[i, 2]).Text.ToString();
accInfo.txtClient_Sub_Account = ((Excel.Range)sheet.Cells[i, 3]).Text.ToString();
accInfo.txtShort_Name = ((Excel.Range)sheet.Cells[i, 5]).Text.ToString();
accInfo.txtChinese_Name = ((Excel.Range)sheet.Cells[i, 6]).Text.ToString();
accInfo.txtOpen_Date = ((Excel.Range)sheet.Cells[i, 7]).Text.ToString();
accountList.Add(accInfo);
}
catch (Exception ex)
{
if (OnError != null)
{
OnErrorEventArgs e = new OnErrorEventArgs(accInfo.txtClient_Main_Account, ex);
OnError(this, e);
}
else
{
throw ex;
}
}
}
OnPrintInfo(this, new OnPrintInfoEventArgs("EXCEL文件解析完毕,共"+accountList.Count+"条记录"));
workbook.Close(System.Type.Missing, System.Type.Missing, System.Type.Missing);
appXSL.Quit();
return accountList;
}
#endregion
}
}
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace WorkBookForHKAccountDoc
{
public class ExcelOperation
{
public delegate void ErrorEventHandler(object sender, OnErrorEventArgs e);
public event ErrorEventHandler OnError = null;
public delegate void InfoEventHandler(object sender, OnPrintInfoEventArgs e);
public event InfoEventHandler OnPrintInfo = null;
获取EXCEL中用户信息列表#region 获取EXCEL中用户信息列表
/**//// <summary>
/// 获取EXCEL中用户信息列表
/// </summary>
/// <returns></returns>
public System.Collections.Generic.List<AccountInfo> getAccountList(string strExcelFilePathName)
{
Excel.Application appXSL = new Microsoft.Office.Interop.Excel.Application();
System.Collections.Generic.List<AccountInfo> accountList = new System.Collections.Generic.List<AccountInfo>();
Excel.Workbook workbook;
Excel.Worksheet sheet;
OnPrintInfo(this, new OnPrintInfoEventArgs("解析EXCEL文件"));
workbook = appXSL.Workbooks.Open(strExcelFilePathName, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
sheet = appXSL.Sheets[1] as Excel.Worksheet;
for (int i = 2; i < sheet.Rows.Count; i++)
//for (int i = 2; i < 95; i++)
{
AccountInfo accInfo = new AccountInfo();
try
{
accInfo.txtClient_Main_Account = ((Excel.Range)sheet.Cells[i, 1]).Text.ToString();
if (accInfo.txtClient_Main_Account.Length == 0)
{
break;
}
accInfo.txtClient_Properity = ((Excel.Range)sheet.Cells[i, 2]).Text.ToString();
accInfo.txtClient_Sub_Account = ((Excel.Range)sheet.Cells[i, 3]).Text.ToString();
accInfo.txtShort_Name = ((Excel.Range)sheet.Cells[i, 5]).Text.ToString();
accInfo.txtChinese_Name = ((Excel.Range)sheet.Cells[i, 6]).Text.ToString();
accInfo.txtOpen_Date = ((Excel.Range)sheet.Cells[i, 7]).Text.ToString();
accountList.Add(accInfo);
}
catch (Exception ex)
{
if (OnError != null)
{
OnErrorEventArgs e = new OnErrorEventArgs(accInfo.txtClient_Main_Account, ex);
OnError(this, e);
}
else
{
throw ex;
}
}
}
OnPrintInfo(this, new OnPrintInfoEventArgs("EXCEL文件解析完毕,共"+accountList.Count+"条记录"));
workbook.Close(System.Type.Missing, System.Type.Missing, System.Type.Missing);
appXSL.Quit();
return accountList;
}
#endregion
}
}
数据结构:
using
System;
using System.Collections.Generic;
using System.Text;
namespace WorkBookForHKAccountDoc
{
public class AccountInfo
{
public string txtClient_Main_Account;
public string txtClient_Properity;
public string txtClient_Sub_Account;
public string txtShort_Name;
public string txtChinese_Name;
public string txtOpen_Date;
public string txtStatus;
}
}
using System.Collections.Generic;
using System.Text;
namespace WorkBookForHKAccountDoc
{
public class AccountInfo
{
public string txtClient_Main_Account;
public string txtClient_Properity;
public string txtClient_Sub_Account;
public string txtShort_Name;
public string txtChinese_Name;
public string txtOpen_Date;
public string txtStatus;
}
}
Word文件操作:
using
System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Word;
namespace WorkBookForHKAccountDoc
{
public class WordOperation
{
public delegate void ErrorEventHandler(object sender, OnErrorEventArgs e);
public event ErrorEventHandler OnError = null;
public delegate void InfoEventHandler(object sender, OnPrintInfoEventArgs e);
public event InfoEventHandler OnPrintInfo = null;
private int intCopyNum = 1;
生成用户信息文档#region 生成用户信息文档
public void generateDocumentDoc(List<AccountInfo> accList, string retFilePath, object mouldFilePathName, int copyNum)
{
//每个Doc文件的记录数
int intPageSize = 1000;
this.intCopyNum = copyNum;
for (int i = 0; i < accList.Count; i += intPageSize)
{
int tmp = i / intPageSize;
string savePathName = retFilePath + tmp.ToString() + ".doc";
generateDocumentDocTmp(accList, savePathName, mouldFilePathName, i, (i + intPageSize) > accList.Count ? accList.Count : (i + intPageSize));
}
}
/**//// <summary>
/// 生成用户信息Doc文档
/// </summary>
/// <param name="accList"></param>
/// <param name="retFilePathName">生成文件存放位置</param>
/// <param name="mouldFilePathName">模板文件位置</param>
void generateDocumentDocTmp(List<AccountInfo> accList, string retFilePathName, object mouldFilePathName,int intListStart,int intListEnd)
{
Microsoft.Office.Interop.Word.Application wordApp = new Microsoft.Office.Interop.Word.Application();
object Nothing = System.Reflection.Missing.Value;
object objRetFileName = retFilePathName;
try
{
OnPrintInfo(this, new OnPrintInfoEventArgs("创建用户信息文档(" + GetFileName(retFilePathName) + ")"));
if (System.IO.File.Exists(retFilePathName))
{
System.IO.File.Delete(retFilePathName);
}
Document docTemp = wordApp.Documents.Open(ref mouldFilePathName, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
docTemp.SaveAs(ref objRetFileName, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
docTemp.Close(ref Nothing, ref Nothing, ref Nothing);
}
catch (Exception ex)
{
if (OnPrintInfo != null)
{
OnPrintInfo(this, new OnPrintInfoEventArgs("创建用户信息文档(" + GetFileName(retFilePathName) + ")失败!"));
}
else
{
throw ex;
}
}
finally
{
OnPrintInfo(this, new OnPrintInfoEventArgs("创建用户信息文档(" + GetFileName(retFilePathName) + ")完成。"));
}
OnPrintInfo(this, new OnPrintInfoEventArgs("生成用户信息文档(" + GetFileName(retFilePathName) + ")"));
Document wordDoc = wordApp.Documents.Open(ref objRetFileName, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
int tblIndex = 1;
for (int listIndex = intListStart; listIndex < intListEnd; listIndex++)
{
try
{
for (int i = 0; i < intCopyNum; i++)
{
wordDoc.Tables[tblIndex].Cell(1, 2).Range.Text = "AA-" + accList[listIndex].txtClient_Main_Account;
wordDoc.Tables[tblIndex].Cell(2, 2).Range.Text = accList[listIndex].txtClient_Main_Account;
wordDoc.Tables[tblIndex].Cell(3, 2).Range.Text = accList[listIndex].txtShort_Name;
wordDoc.Tables[tblIndex].Cell(4, 2).Range.Text = accList[listIndex].txtOpen_Date;
if (!(tblIndex == (intListEnd-intListStart)*intCopyNum))
{
object bookMarkIndex = 1;
wordDoc.Bookmarks.get_Item(ref bookMarkIndex).Select();
wordApp.Selection.Copy();
object objEndKey = WdUnits.wdStory;
wordApp.Selection.EndKey(ref objEndKey, ref Nothing);
object objInsertBreakType = WdBreakType.wdPageBreak;
wordApp.Selection.InsertBreak(ref objInsertBreakType);
wordApp.Selection.PasteAndFormat(WdRecoveryType.wdPasteDefault);
}
tblIndex++;
}
}
catch (Exception ex)
{
if (OnError != null)
{
OnErrorEventArgs e = new OnErrorEventArgs(accList[listIndex].txtClient_Main_Account, ex);
OnError(this, e);
}
else
{
throw ex;
}
}
}
try
{
wordDoc.Save();
}
catch (Exception)
{
OnPrintInfo(this, new OnPrintInfoEventArgs("用户信息文档(" + GetFileName(retFilePathName) + ")生成失败!"));
}
object objSaveFlag = WdSaveOptions.wdSaveChanges;
wordDoc.Close(ref objSaveFlag, ref Nothing, ref Nothing);
wordApp.Quit(ref objSaveFlag, ref Nothing, ref Nothing);
OnPrintInfo(this, new OnPrintInfoEventArgs("用户信息文档(" + GetFileName(retFilePathName) + ")生成结束。"));
}
#endregion
生成用户ID表格文档#region 生成用户ID表格文档
public void generateTableDoc(List<AccountInfo> accList, string retFilePath, object mouldFilePathName)
{
//每个Doc文件的记录数
int intPageSize = 1000;
for (int i = 0; i < accList.Count; i += intPageSize)
{
int tmp = i / intPageSize;
string savePathName = retFilePath + tmp.ToString() + ".doc";
generateTableDocTmp(accList, savePathName, mouldFilePathName, i, (i + intPageSize) > accList.Count ? accList.Count : (i + intPageSize));
}
}
/**//// <summary>
/// 生成用户名列表Doc文档
/// </summary>
/// <param name="accList"></param>
/// <param name="retFilePathName">生成文件存放位置</param>
/// <param name="mouldFilePathName">模板文件位置</param>
void generateTableDocTmp(List<AccountInfo> accList, string retFilePathName, object mouldFilePathName, int intListStart, int intListEnd)
{
Microsoft.Office.Interop.Word.Application wordApp = new Microsoft.Office.Interop.Word.Application();
object Nothing = System.Reflection.Missing.Value;
创建文档#region 创建文档
object objRetFileName = retFilePathName;
try
{
OnPrintInfo(this, new OnPrintInfoEventArgs("创建用户帐号表格文档(" + GetFileName(retFilePathName) + ")"));
if (System.IO.File.Exists(retFilePathName))
{
System.IO.File.Delete(retFilePathName);
}
Document docTemp = wordApp.Documents.Open(ref mouldFilePathName, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
docTemp.SaveAs(ref objRetFileName, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
docTemp.Close(ref Nothing, ref Nothing, ref Nothing);
}
catch (Exception ex)
{
if (OnPrintInfo != null)
{
OnPrintInfo(this, new OnPrintInfoEventArgs("创建用户帐号表格文档(" + GetFileName(retFilePathName) + ")失败!"));
}
else
{
throw ex;
}
}
finally
{
OnPrintInfo(this, new OnPrintInfoEventArgs("创建用户帐号表格文档(" + GetFileName(retFilePathName) + ")完成。"));
}
#endregion
OnPrintInfo(this, new OnPrintInfoEventArgs("生成用户帐号表格文档(" + GetFileName(retFilePathName) + ")"));
填充数据#region 填充数据
Document wordDoc = wordApp.Documents.Open(ref objRetFileName, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
int rowIndex = 1;
//插入空表格
object row = intListEnd - intListStart - 1;
wordDoc.Tables[1].Cell(rowIndex, 1).Select();
wordApp.Selection.InsertRowsBelow(ref row);
for (int listIndex = intListStart; listIndex < intListEnd; listIndex++)
{
try
{
string strAccNO = GetAccID(accList[listIndex]);
for (int i = 1; i < 5; i++)
{
wordDoc.Tables[1].Cell(rowIndex, i).Range.Text = strAccNO;
}
rowIndex++;
}
catch (Exception ex)
{
if (OnError != null)
{
OnErrorEventArgs e = new OnErrorEventArgs(accList[listIndex].txtClient_Main_Account, ex);
OnError(this, e);
}
else
{
throw ex;
}
}
}
#endregion
try
{
wordDoc.Save();
}
catch (Exception)
{
OnPrintInfo(this, new OnPrintInfoEventArgs("用户帐号表格文档(" + GetFileName(retFilePathName) + ")生成失败!"));
}
object objSaveFlag = WdSaveOptions.wdSaveChanges;
wordDoc.Close(ref objSaveFlag, ref Nothing, ref Nothing);
wordApp.Quit(ref objSaveFlag, ref Nothing, ref Nothing);
OnPrintInfo(this, new OnPrintInfoEventArgs("用户帐号表格文档(" + GetFileName(retFilePathName) + ")生成结束。"));
}
#endregion
CommonFunctions#region CommonFunctions
string GetAccID(AccountInfo Acc)
{
return Acc.txtClient_Main_Account + "-01-" + Acc.txtClient_Properity + "-" + Acc.txtClient_Sub_Account;
}
string GetFileName(string strFilePathName)
{
string strRet = "";
strRet = strFilePathName.Substring(strFilePathName.LastIndexOf(@"\") + 1);
if (strRet.Length == 0)
{
strRet = strFilePathName.Substring(strFilePathName.LastIndexOf(@"/") + 1);
}
return strRet;
}
#endregion
}
}
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Word;
namespace WorkBookForHKAccountDoc
{
public class WordOperation
{
public delegate void ErrorEventHandler(object sender, OnErrorEventArgs e);
public event ErrorEventHandler OnError = null;
public delegate void InfoEventHandler(object sender, OnPrintInfoEventArgs e);
public event InfoEventHandler OnPrintInfo = null;
private int intCopyNum = 1;
生成用户信息文档#region 生成用户信息文档
public void generateDocumentDoc(List<AccountInfo> accList, string retFilePath, object mouldFilePathName, int copyNum)
{
//每个Doc文件的记录数
int intPageSize = 1000;
this.intCopyNum = copyNum;
for (int i = 0; i < accList.Count; i += intPageSize)
{
int tmp = i / intPageSize;
string savePathName = retFilePath + tmp.ToString() + ".doc";
generateDocumentDocTmp(accList, savePathName, mouldFilePathName, i, (i + intPageSize) > accList.Count ? accList.Count : (i + intPageSize));
}
}
/**//// <summary>
/// 生成用户信息Doc文档
/// </summary>
/// <param name="accList"></param>
/// <param name="retFilePathName">生成文件存放位置</param>
/// <param name="mouldFilePathName">模板文件位置</param>
void generateDocumentDocTmp(List<AccountInfo> accList, string retFilePathName, object mouldFilePathName,int intListStart,int intListEnd)
{
Microsoft.Office.Interop.Word.Application wordApp = new Microsoft.Office.Interop.Word.Application();
object Nothing = System.Reflection.Missing.Value;
object objRetFileName = retFilePathName;
try
{
OnPrintInfo(this, new OnPrintInfoEventArgs("创建用户信息文档(" + GetFileName(retFilePathName) + ")"));
if (System.IO.File.Exists(retFilePathName))
{
System.IO.File.Delete(retFilePathName);
}
Document docTemp = wordApp.Documents.Open(ref mouldFilePathName, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
docTemp.SaveAs(ref objRetFileName, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
docTemp.Close(ref Nothing, ref Nothing, ref Nothing);
}
catch (Exception ex)
{
if (OnPrintInfo != null)
{
OnPrintInfo(this, new OnPrintInfoEventArgs("创建用户信息文档(" + GetFileName(retFilePathName) + ")失败!"));
}
else
{
throw ex;
}
}
finally
{
OnPrintInfo(this, new OnPrintInfoEventArgs("创建用户信息文档(" + GetFileName(retFilePathName) + ")完成。"));
}
OnPrintInfo(this, new OnPrintInfoEventArgs("生成用户信息文档(" + GetFileName(retFilePathName) + ")"));
Document wordDoc = wordApp.Documents.Open(ref objRetFileName, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
int tblIndex = 1;
for (int listIndex = intListStart; listIndex < intListEnd; listIndex++)
{
try
{
for (int i = 0; i < intCopyNum; i++)
{
wordDoc.Tables[tblIndex].Cell(1, 2).Range.Text = "AA-" + accList[listIndex].txtClient_Main_Account;
wordDoc.Tables[tblIndex].Cell(2, 2).Range.Text = accList[listIndex].txtClient_Main_Account;
wordDoc.Tables[tblIndex].Cell(3, 2).Range.Text = accList[listIndex].txtShort_Name;
wordDoc.Tables[tblIndex].Cell(4, 2).Range.Text = accList[listIndex].txtOpen_Date;
if (!(tblIndex == (intListEnd-intListStart)*intCopyNum))
{
object bookMarkIndex = 1;
wordDoc.Bookmarks.get_Item(ref bookMarkIndex).Select();
wordApp.Selection.Copy();
object objEndKey = WdUnits.wdStory;
wordApp.Selection.EndKey(ref objEndKey, ref Nothing);
object objInsertBreakType = WdBreakType.wdPageBreak;
wordApp.Selection.InsertBreak(ref objInsertBreakType);
wordApp.Selection.PasteAndFormat(WdRecoveryType.wdPasteDefault);
}
tblIndex++;
}
}
catch (Exception ex)
{
if (OnError != null)
{
OnErrorEventArgs e = new OnErrorEventArgs(accList[listIndex].txtClient_Main_Account, ex);
OnError(this, e);
}
else
{
throw ex;
}
}
}
try
{
wordDoc.Save();
}
catch (Exception)
{
OnPrintInfo(this, new OnPrintInfoEventArgs("用户信息文档(" + GetFileName(retFilePathName) + ")生成失败!"));
}
object objSaveFlag = WdSaveOptions.wdSaveChanges;
wordDoc.Close(ref objSaveFlag, ref Nothing, ref Nothing);
wordApp.Quit(ref objSaveFlag, ref Nothing, ref Nothing);
OnPrintInfo(this, new OnPrintInfoEventArgs("用户信息文档(" + GetFileName(retFilePathName) + ")生成结束。"));
}
#endregion
生成用户ID表格文档#region 生成用户ID表格文档
public void generateTableDoc(List<AccountInfo> accList, string retFilePath, object mouldFilePathName)
{
//每个Doc文件的记录数
int intPageSize = 1000;
for (int i = 0; i < accList.Count; i += intPageSize)
{
int tmp = i / intPageSize;
string savePathName = retFilePath + tmp.ToString() + ".doc";
generateTableDocTmp(accList, savePathName, mouldFilePathName, i, (i + intPageSize) > accList.Count ? accList.Count : (i + intPageSize));
}
}
/**//// <summary>
/// 生成用户名列表Doc文档
/// </summary>
/// <param name="accList"></param>
/// <param name="retFilePathName">生成文件存放位置</param>
/// <param name="mouldFilePathName">模板文件位置</param>
void generateTableDocTmp(List<AccountInfo> accList, string retFilePathName, object mouldFilePathName, int intListStart, int intListEnd)
{
Microsoft.Office.Interop.Word.Application wordApp = new Microsoft.Office.Interop.Word.Application();
object Nothing = System.Reflection.Missing.Value;
创建文档#region 创建文档
object objRetFileName = retFilePathName;
try
{
OnPrintInfo(this, new OnPrintInfoEventArgs("创建用户帐号表格文档(" + GetFileName(retFilePathName) + ")"));
if (System.IO.File.Exists(retFilePathName))
{
System.IO.File.Delete(retFilePathName);
}
Document docTemp = wordApp.Documents.Open(ref mouldFilePathName, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
docTemp.SaveAs(ref objRetFileName, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
docTemp.Close(ref Nothing, ref Nothing, ref Nothing);
}
catch (Exception ex)
{
if (OnPrintInfo != null)
{
OnPrintInfo(this, new OnPrintInfoEventArgs("创建用户帐号表格文档(" + GetFileName(retFilePathName) + ")失败!"));
}
else
{
throw ex;
}
}
finally
{
OnPrintInfo(this, new OnPrintInfoEventArgs("创建用户帐号表格文档(" + GetFileName(retFilePathName) + ")完成。"));
}
#endregion
OnPrintInfo(this, new OnPrintInfoEventArgs("生成用户帐号表格文档(" + GetFileName(retFilePathName) + ")"));
填充数据#region 填充数据
Document wordDoc = wordApp.Documents.Open(ref objRetFileName, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing, ref Nothing);
int rowIndex = 1;
//插入空表格
object row = intListEnd - intListStart - 1;
wordDoc.Tables[1].Cell(rowIndex, 1).Select();
wordApp.Selection.InsertRowsBelow(ref row);
for (int listIndex = intListStart; listIndex < intListEnd; listIndex++)
{
try
{
string strAccNO = GetAccID(accList[listIndex]);
for (int i = 1; i < 5; i++)
{
wordDoc.Tables[1].Cell(rowIndex, i).Range.Text = strAccNO;
}
rowIndex++;
}
catch (Exception ex)
{
if (OnError != null)
{
OnErrorEventArgs e = new OnErrorEventArgs(accList[listIndex].txtClient_Main_Account, ex);
OnError(this, e);
}
else
{
throw ex;
}
}
}
#endregion
try
{
wordDoc.Save();
}
catch (Exception)
{
OnPrintInfo(this, new OnPrintInfoEventArgs("用户帐号表格文档(" + GetFileName(retFilePathName) + ")生成失败!"));
}
object objSaveFlag = WdSaveOptions.wdSaveChanges;
wordDoc.Close(ref objSaveFlag, ref Nothing, ref Nothing);
wordApp.Quit(ref objSaveFlag, ref Nothing, ref Nothing);
OnPrintInfo(this, new OnPrintInfoEventArgs("用户帐号表格文档(" + GetFileName(retFilePathName) + ")生成结束。"));
}
#endregion
CommonFunctions#region CommonFunctions
string GetAccID(AccountInfo Acc)
{
return Acc.txtClient_Main_Account + "-01-" + Acc.txtClient_Properity + "-" + Acc.txtClient_Sub_Account;
}
string GetFileName(string strFilePathName)
{
string strRet = "";
strRet = strFilePathName.Substring(strFilePathName.LastIndexOf(@"\") + 1);
if (strRet.Length == 0)
{
strRet = strFilePathName.Substring(strFilePathName.LastIndexOf(@"/") + 1);
}
return strRet;
}
#endregion
}
}
MAINFORM:
using
System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace WorkBookForHKAccountDoc
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
}
private void MainForm_Load(object sender, EventArgs e)
{
initializeComBox();
}
private void btnXlsFileUp_Click(object sender, EventArgs e)
{
OpenFileDialog file = new OpenFileDialog();
file.Title = "选择Excel资源";
file.Filter = "xls files(*.xls)|*.xls" +
"|All files(*.*)|*.*";
if (file.ShowDialog() == DialogResult.OK)
{
txtXlsFilePathName.Text = file.FileName;
}
file.Dispose();
}
private void btnXlsFileUp2_Click(object sender, EventArgs e)
{
OpenFileDialog file = new OpenFileDialog();
file.Title = "选择Excel资源";
file.Filter = "xls files(*.xls)|*.xls" +
"|All files(*.*)|*.*";
if (file.ShowDialog() == DialogResult.OK)
{
txtXlsFilePathName2.Text = file.FileName;
}
file.Dispose();
}
private void btnDocFileUp_Click(object sender, EventArgs e)
{
OpenFileDialog file = new OpenFileDialog();
file.Title = "选择Doc文档";
file.Filter = "doc files(*.doc)|*.doc" +
"|All files(*.*)|*.*";
if (file.ShowDialog() == DialogResult.OK)
{
txtDocFilePathName.Text = file.FileName;
}
file.Dispose();
}
private void btnDocAccountFileUp_Click(object sender, EventArgs e)
{
OpenFileDialog file = new OpenFileDialog();
file.Title = "选择Doc文档";
file.Filter = "doc files(*.doc)|*.doc" +
"|All files(*.*)|*.*";
if (file.ShowDialog() == DialogResult.OK)
{
this.txtDocAccountFilePathName.Text = file.FileName;
}
file.Dispose();
}
private void btnGenerateAccInfoDocs_Click(object sender, EventArgs e)
{
if (!validateTag1())
{
return;
}
try
{
ExcelOperation xlsOp = new ExcelOperation();
xlsOp.OnPrintInfo += new ExcelOperation.InfoEventHandler(OnPrintInfo);
xlsOp.OnError += new ExcelOperation.ErrorEventHandler(OnError);
System.Collections.Generic.List<AccountInfo> list = xlsOp.getAccountList(this.txtXlsFilePathName.Text);
int intTmp = 1;
if (comboBox1.SelectedIndex > 0)
{
intTmp = Convert.ToInt32(this.comboBox1.SelectedItem);
}
WordOperation docOp = new WordOperation();
docOp.OnError += new WordOperation.ErrorEventHandler(OnError);
docOp.OnPrintInfo += new WordOperation.InfoEventHandler(OnPrintInfo);
object objDocMouldFileAccInfo = txtDocFilePathName.Text;
docOp.generateDocumentDoc(list, GetConfigValue("strSaveAccountInfoDocPath"), objDocMouldFileAccInfo,intTmp);
MessageBox.Show("执行完毕!");
}
catch (Exception ex)
{
OnError(this, new OnErrorEventArgs("", ex));
}
}
private void btnGenerateAccIDDocs_Click(object sender, EventArgs e)
{
if (!validateTag2())
{
return;
}
try
{
ExcelOperation xlsOp = new ExcelOperation();
xlsOp.OnPrintInfo += new ExcelOperation.InfoEventHandler(OnPrintInfo);
xlsOp.OnError += new ExcelOperation.ErrorEventHandler(OnError);
System.Collections.Generic.List<AccountInfo> list = xlsOp.getAccountList(this.txtXlsFilePathName2.Text);
WordOperation docOp = new WordOperation();
docOp.OnError += new WordOperation.ErrorEventHandler(OnError);
docOp.OnPrintInfo += new WordOperation.InfoEventHandler(OnPrintInfo);
object objDocMouldFileAccID = txtDocAccountFilePathName.Text;
docOp.generateTableDoc(list, GetConfigValue("strSaveAccountIDDocPath"), objDocMouldFileAccID);
MessageBox.Show("执行完毕!");
}
catch (Exception ex)
{
OnError(this, new OnErrorEventArgs("", ex));
}
}
void OnError(object sender, OnErrorEventArgs e)
{
listBox1.Items.Add(e.AccountID + " | " + e.InnerException.Message);
this.Refresh();
}
void OnPrintInfo(object sender, OnPrintInfoEventArgs e)
{
listBox1.Items.Add(e.StrEventInfo);
listBox1.SelectedIndex = listBox1.Items.Count - 1;
this.Refresh();
}
void initializeComBox()
{
object objComBoxItem = "--用户信息拷贝数--";
this.comboBox1.Items.Add(objComBoxItem);
for (int i = 1; i <= 10; i++)
{
objComBoxItem = i;
this.comboBox1.Items.Add(objComBoxItem);
}
comboBox1.SelectedIndex = 0;
}
bool validateTag1()
{
if (this.txtDocAccountFilePathName.Text == "" || txtXlsFilePathName.Text == "")
{
MessageBox.Show("请选择需要的文件!");
return false;
}
else
{
return true;
}
}
bool validateTag2()
{
if ( this.txtDocAccountFilePathName.Text == "" || txtXlsFilePathName2.Text == "")
{
MessageBox.Show("请选择需要的文件!");
return false;
}
else
{
return true;
}
}
string GetConfigValue(string strCfgName)
{
string strRet = System.Configuration.ConfigurationSettings.AppSettings[strCfgName];
if (!strRet.EndsWith(@"\"))
{
strRet = strRet + @"\";
}
return strRet;
}
}
}
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace WorkBookForHKAccountDoc
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
}
private void MainForm_Load(object sender, EventArgs e)
{
initializeComBox();
}
private void btnXlsFileUp_Click(object sender, EventArgs e)
{
OpenFileDialog file = new OpenFileDialog();
file.Title = "选择Excel资源";
file.Filter = "xls files(*.xls)|*.xls" +
"|All files(*.*)|*.*";
if (file.ShowDialog() == DialogResult.OK)
{
txtXlsFilePathName.Text = file.FileName;
}
file.Dispose();
}
private void btnXlsFileUp2_Click(object sender, EventArgs e)
{
OpenFileDialog file = new OpenFileDialog();
file.Title = "选择Excel资源";
file.Filter = "xls files(*.xls)|*.xls" +
"|All files(*.*)|*.*";
if (file.ShowDialog() == DialogResult.OK)
{
txtXlsFilePathName2.Text = file.FileName;
}
file.Dispose();
}
private void btnDocFileUp_Click(object sender, EventArgs e)
{
OpenFileDialog file = new OpenFileDialog();
file.Title = "选择Doc文档";
file.Filter = "doc files(*.doc)|*.doc" +
"|All files(*.*)|*.*";
if (file.ShowDialog() == DialogResult.OK)
{
txtDocFilePathName.Text = file.FileName;
}
file.Dispose();
}
private void btnDocAccountFileUp_Click(object sender, EventArgs e)
{
OpenFileDialog file = new OpenFileDialog();
file.Title = "选择Doc文档";
file.Filter = "doc files(*.doc)|*.doc" +
"|All files(*.*)|*.*";
if (file.ShowDialog() == DialogResult.OK)
{
this.txtDocAccountFilePathName.Text = file.FileName;
}
file.Dispose();
}
private void btnGenerateAccInfoDocs_Click(object sender, EventArgs e)
{
if (!validateTag1())
{
return;
}
try
{
ExcelOperation xlsOp = new ExcelOperation();
xlsOp.OnPrintInfo += new ExcelOperation.InfoEventHandler(OnPrintInfo);
xlsOp.OnError += new ExcelOperation.ErrorEventHandler(OnError);
System.Collections.Generic.List<AccountInfo> list = xlsOp.getAccountList(this.txtXlsFilePathName.Text);
int intTmp = 1;
if (comboBox1.SelectedIndex > 0)
{
intTmp = Convert.ToInt32(this.comboBox1.SelectedItem);
}
WordOperation docOp = new WordOperation();
docOp.OnError += new WordOperation.ErrorEventHandler(OnError);
docOp.OnPrintInfo += new WordOperation.InfoEventHandler(OnPrintInfo);
object objDocMouldFileAccInfo = txtDocFilePathName.Text;
docOp.generateDocumentDoc(list, GetConfigValue("strSaveAccountInfoDocPath"), objDocMouldFileAccInfo,intTmp);
MessageBox.Show("执行完毕!");
}
catch (Exception ex)
{
OnError(this, new OnErrorEventArgs("", ex));
}
}
private void btnGenerateAccIDDocs_Click(object sender, EventArgs e)
{
if (!validateTag2())
{
return;
}
try
{
ExcelOperation xlsOp = new ExcelOperation();
xlsOp.OnPrintInfo += new ExcelOperation.InfoEventHandler(OnPrintInfo);
xlsOp.OnError += new ExcelOperation.ErrorEventHandler(OnError);
System.Collections.Generic.List<AccountInfo> list = xlsOp.getAccountList(this.txtXlsFilePathName2.Text);
WordOperation docOp = new WordOperation();
docOp.OnError += new WordOperation.ErrorEventHandler(OnError);
docOp.OnPrintInfo += new WordOperation.InfoEventHandler(OnPrintInfo);
object objDocMouldFileAccID = txtDocAccountFilePathName.Text;
docOp.generateTableDoc(list, GetConfigValue("strSaveAccountIDDocPath"), objDocMouldFileAccID);
MessageBox.Show("执行完毕!");
}
catch (Exception ex)
{
OnError(this, new OnErrorEventArgs("", ex));
}
}
void OnError(object sender, OnErrorEventArgs e)
{
listBox1.Items.Add(e.AccountID + " | " + e.InnerException.Message);
this.Refresh();
}
void OnPrintInfo(object sender, OnPrintInfoEventArgs e)
{
listBox1.Items.Add(e.StrEventInfo);
listBox1.SelectedIndex = listBox1.Items.Count - 1;
this.Refresh();
}
void initializeComBox()
{
object objComBoxItem = "--用户信息拷贝数--";
this.comboBox1.Items.Add(objComBoxItem);
for (int i = 1; i <= 10; i++)
{
objComBoxItem = i;
this.comboBox1.Items.Add(objComBoxItem);
}
comboBox1.SelectedIndex = 0;
}
bool validateTag1()
{
if (this.txtDocAccountFilePathName.Text == "" || txtXlsFilePathName.Text == "")
{
MessageBox.Show("请选择需要的文件!");
return false;
}
else
{
return true;
}
}
bool validateTag2()
{
if ( this.txtDocAccountFilePathName.Text == "" || txtXlsFilePathName2.Text == "")
{
MessageBox.Show("请选择需要的文件!");
return false;
}
else
{
return true;
}
}
string GetConfigValue(string strCfgName)
{
string strRet = System.Configuration.ConfigurationSettings.AppSettings[strCfgName];
if (!strRet.EndsWith(@"\"))
{
strRet = strRet + @"\";
}
return strRet;
}
}
}