using Microsoft.Office.Interop.Word;
using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;
namespace addin_Excel
{
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 >
//< leeres Delegate erstellen >
private delegate void DoEvents_EmptyDelegate();
//</ leeres Delegate erstellen >
#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_Shown(object sender, EventArgs e)
{
DateTime dtStart = DateTime.Now;
_worksheet = open_Excel_File();
if (_worksheet == null) return;
read_Excel_File_into_DataGridView();
TimeSpan tsDauer = DateTime.Now.Subtract(dtStart);
string sDauer = tsDauer.Milliseconds + " msek.";
lblStatus.Text = sDauer;
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 >
DateTime dtStart = DateTime.Now;
lblStatus.Text = "opening excel file..";
_excel_App = new Microsoft.Office.Interop.Excel.Application();
lblStatus.Text = "file open. ";
log_with_Date(lblStatus.Text, dtStart);
lblLog.Text += lblStatus.Text;
try
{
//----< open_Excel >----
dtStart = DateTime.Now;
_workbook = _excel_App.Workbooks.Open(sFilename, UpdateLinks: false, ReadOnly: true, AddToMru: false);
lblStatus.Text = "open sheet ";
log_with_Date(lblStatus.Text, dtStart);
//----</ 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 >
DateTime dtStart = DateTime.Now;
Excel.Range usedRange = _worksheet.UsedRange;
log_with_Date("get UsedRange", dtStart);
//*fast Excel-Read:
object[,] values = usedRange.Value2;
int nColumnsMax = 0;
if (usedRange.Rows.Count > 0)
{
//----< Read_Header >----
dtStart = DateTime.Now;
for (int iColumn = 1; iColumn <= usedRange.Columns.Count; iColumn++)
{
//*slow Excel:
//Excel.Range cell = usedRange.Cells[1, iColumn] as Excel.Range;
//String sValue = Convert.ToString(cell.Value2);
//*fast Excel:
string sValue = Convert.ToString(values[1, iColumn]);
if (sValue == "" || sValue == null) break;
dataGridView.Columns.Add("column_" + iColumn, sValue);
nColumnsMax = iColumn;
}
log_with_Date("HeadLines= " , dtStart);
//----</ Read_Header >----
//----< Read_DataRows >----
dtStart = DateTime.Now;
for (int iRow = 2; iRow <= usedRange.Rows.Count; iRow++)
{
lblStatus.Text = "row " + iRow + "/" + usedRange.Rows.Count;
//< add_Row >
int iNewRow = dataGridView.Rows.Add(new DataGridViewRow());
DataGridViewRow newRow = dataGridView.Rows[iNewRow];
//</ add_Row >
if (iRow > 20) break;
for (int iColumn = 1; iColumn <= nColumnsMax; iColumn++)
{
//--< Transfer Cells >--
//< read >
//*slow Excel: Excel.Range cell = usedRange.Cells[iRow, iColumn] as Excel.Range;
//*slow Excel: String sValue = Convert.ToString(cell.Value2);
//*fast Excel:
string sValue = Convert.ToString(values[iRow, iColumn]);
if (iColumn == 1)
{
if (sValue == "" || sValue == null)
{
log_with_Date("rows= ", dtStart);
return; // break;
}
}
//</ read >
//< write >
newRow.Cells[iColumn - 1].Value = sValue;
//</ write >
//--</ Transfer Cells > --
}
}
log_with_Date("rows= ", dtStart);
//----</ Read_DataRows >----
}
//----------------</ read_Excel_File_into_DataGridView() >------------
}
private void log_with_Date(string sText, DateTime dtStart)
{
TimeSpan tsDauer = DateTime.Now.Subtract(dtStart);
lblLog.Text += Environment.NewLine + sText + " " + tsDauer.Milliseconds + " msek.";
System.Windows.Forms.Application.DoEvents();
}
//===================</ 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
//DataGridViewColumn column = new DataGridViewColumn();
//column.HeaderText = sValue;
//column.CellTemplate = new DataGridViewTextBoxCell();
//dataGridView.Columns.Add(column);
}
}
|