/// <summary>
/// Format the selection according to the given Column Data Type
/// </summary>
/// <param name="format">the format to set </param>
/// <param name="clmnType">the Excel Column Data Type</param>
public static void FrmtTXT(string format, XlColumnDataType clmnType)
{
Application oExcel = ThisAddIn.GetActiveApplication();
Commands.Updates(oExcel, false);
Range range = oExcel.Selection;
long r = range.Row + range.Rows.Count - 1;
for (int c = range.Column; c < range.Column + range.Columns.Count; c++)
{
Range range0 = (Range)oExcel.Range[oExcel.Cells[range.Row, c], oExcel.Cells[r, c]];
if (oExcel.WorksheetFunction.CountA(range0, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing) > 0)
{
int[,] myArray = new int[, ] {
{ 1, (int)clmnType }
};
oExcel.Cells[1, 1].Select();
range0.TextToColumns(range0, FieldInfo: myArray);
range0.Select();
range0.NumberFormat = format;
Marshal.ReleaseComObject(range0);
}
}
range.Select();
Marshal.ReleaseComObject(range);
Commands.Updates(oExcel);
}
public static void ExportDataTableToExcel(System.Data.DataTable excelData, Worksheet excelWorkSheet)
{
Application oExcel = ThisAddIn.GetActiveApplication();
excelWorkSheet.Cells.ClearContents();
excelWorkSheet.Cells.ClearFormats();
Commands.Updates(oExcel, false);
string[,] dump = new string[excelData.Rows.Count + 1, excelData.Columns.Count];
for (int i = 0; i < excelData.Columns.Count; i++)
{
dump[0, i] = excelData.Columns[i].ColumnName;
}
for (int j = 1; j < excelData.Rows.Count + 1; j++)
{
for (int k = 0; k < excelData.Columns.Count; k++)
{
dump[j, k] = excelData.Rows[j - 1].ItemArray[k].ToString();
}
}
Excel.Range excelCellrange;
excelCellrange = excelWorkSheet.Range[excelWorkSheet.Cells[1, 1], excelWorkSheet.Cells[excelData.Rows.Count + 1, excelData.Columns.Count]];
excelCellrange.Value = dump;
Commands.Updates(oExcel);
excelData.Clear();
return;
}
/// <summary>
/// trim the selected cells (removing leading and trailing spaces)
/// </summary>
public static void TrimSelection()
{
Application oExcel = ThisAddIn.GetActiveApplication();
Commands.Updates(oExcel, false);
foreach (var cell in oExcel.Selection)
{
cell.value = oExcel.WorksheetFunction.Trim(cell.value);
}
Commands.Updates(oExcel);
}
public static void ClearFRMT()
{
Application oExcel = ThisAddIn.GetActiveApplication();
Commands.Updates(oExcel, false);
Range range = oExcel.Selection;
range.ClearFormats();
Commands.Updates(oExcel);
}
public static void ClearMessage()
{
Application oExcel = ThisAddIn.GetActiveApplication();
oExcel.Application.StatusBar = false;
}
public static void UpdatingMessage(string ThingRunning)
{
Application oExcel = ThisAddIn.GetActiveApplication();
oExcel.Application.StatusBar = ThingRunning + " is processing. Please Wait...";
}