|
using Microsoft.Office.Interop.Word;
using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
namespace word_Addin_MailMerger
{
public partial class frmExcel_Data : Form
{
//< variables >
private DataGridView _dataGridView;
private Excel.Application _excel_App;
private Excel.Worksheet _worksheet;
private Excel.Workbook _workbook;
Document _doc = null;
Word.Application _app = null;
//</ variables >
#region Region: Form
//===================< Form >===================
public frmExcel_Data()
{
InitializeComponent();
_dataGridView = dataGrid_Excel;
_dataGridView.AllowUserToAddRows = false;
_doc = Globals.ThisAddIn.Application.ActiveDocument;
_app = Globals.ThisAddIn.Application;
}
private void frmExcel_Data_Load(object sender, EventArgs e)
{
}
private void frmExcel_Data_Shown(object sender, EventArgs e)
{
_worksheet = open_Excel_File();
if (_worksheet == null) return;
read_Excel_File_into_DataGridView();
lblStatus.Text = "";
try
{
_workbook.Close();
_excel_App.Quit();
}
finally
{
}
}
//===================</ Form >===================
#endregion /Region: Form
#region --Buttons--
//===================< Buttons >===================
private void btnSelect_Click(object sender, EventArgs e)
{
replace_Serial_Fields();
Close();
}
//===================</ Buttons >===================
#endregion --/ Buttons--
#region --Methods Excel--
//===================< Methods Excel >===================
public Excel.Worksheet open_Excel_File()
{
//-----------------< open_Excel_File() >-----------------
//< init >
String sFilename = Settings1.Default.Excel_Filename;
if (sFilename.IndexOf(":") < 0)
{
string sWordPath = Globals.ThisAddIn.Application.ActiveDocument.Path;
sFilename = sWordPath + "\\" + sFilename;
}
//</ init >
lblStatus.Text = "opening excel file..";
_excel_App = new Microsoft.Office.Interop.Excel.Application();
try
{
//----< open_Excel >----
_workbook = _excel_App.Workbooks.Open(sFilename);//, UpdateLinks: false, ReadOnly: true, AddToMru: false);
//_worksheet = _workbook.Worksheets[Settings1.Default.Excel_Sheet];
//----</ open_Excel >----
return _workbook.Worksheets[Settings1.Default.Excel_Sheet]; ;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return null;
}
//-----------------</ open_Excel_File() >-----------------
}
public void read_Excel_File_into_DataGridView()
{
//----------------< read_Excel_File_into_DataGridView() >------------
//< init >
DataGridView dataGridView = dataGrid_Excel;
//</ init >
Excel.Range usedRange = _worksheet.UsedRange;
int nColumnsMax = 0;
if (usedRange.Rows.Count > 0)
{
//----< Read_Header >----
for (int iColumn = 1; iColumn <= usedRange.Columns.Count; iColumn++)
{
Excel.Range cell = usedRange.Cells[1, iColumn] as Excel.Range;
String sValue = Convert.ToString(cell.Value2);
if (sValue == "" || sValue==null) break;
dataGridView.Columns.Add("column_" + iColumn, sValue);
lblStatus.Text = "load header " + sValue ;
nColumnsMax = iColumn;
}
//----</ Read_Header >----
//----< Read_DataRows >----
for (int iRow = 2; iRow <= usedRange.Rows.Count; iRow++)
{
//< add_Row >
int iNewRow = dataGridView.Rows.Add(new DataGridViewRow());
DataGridViewRow newRow = dataGridView.Rows[iNewRow];
//</ add_Row >
lblStatus.Text = "load row" + iRow + "..";
for (int iColumn = 1; iColumn <= nColumnsMax; iColumn++)
{
Microsoft.Office.Interop.Excel.Range cell = usedRange.Cells[iRow, iColumn] as Excel.Range;
String sValue = Convert.ToString(cell.Value2);
if (iColumn == 1)
{
if (sValue == "" || sValue == null) return; // break;
}
newRow.Cells[iColumn-1].Value = sValue;
}
}
//----</ Read_DataRows >----
}
//----------------</ read_Excel_File_into_DataGridView() >------------
}
//===================</ Methods >===================
#endregion /Methods
#region --Methods Word --
//===================< Methods Word >===================
private void replace_Serial_Fields()
{
//-----------------< suche_Address_Felder() >-----------------
//*Serienbrieffelder=ActiveDocument.FormFields[]
//--< @Loop: SerialFields >--
foreach (object varField in _doc.MailMerge.Fields)
{
//«KDNR»
MailMergeField field = (MailMergeField) varField ;
string sField_Content = field.Code.Text;
int posField = sField_Content.IndexOf("MERGEFIELD ");
if ( posField > -1) // MERGEFIELD SANr
{
//---< IsMail_Field >---
//< get fieldname >
posField = posField + "MERGEFIELD ".Length;
string sField = sField_Content.Substring(posField);
sField = sField.Trim();
//</ get fieldname >
//< get grid >
int intColumn = get_Column_with_Header(sField);
//</ get grid >
if(intColumn>=0)
{
string sReplace = _dataGridView.Rows[1].Cells[intColumn].Value.ToString();
//< replace field >
field.Select();
_app.Selection.Text = sReplace;
//field.Delete();
//</ replace field >
}
//---</ IsMail_Field >---
}
}
//--</ @Loop: SerialFields >--
//-----------------</ suche_Address_Felder() >-----------------
}
private int get_Column_with_Header(string sHeader)
{
//-----------------< get_Column_with_Header() >-----------------
//< init >
DataGridView dataGridView = dataGrid_Excel;
//</ init >
//--< @Loop: SerialFields >--
foreach (DataGridViewColumn col in dataGridView.Columns)
{
if (col.HeaderText == sHeader) return col.Index;
}
//--</ @Loop: SerialFields >--
return -1;
//-----------------</ get_Column_with_Header() >-----------------
}
//===================</ Methods Word >===================
#endregion /Methods Word
}
}
|