/// <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..."; }