public void Save(string fileName = "")
{
if (fileName == string.Empty)
{
wb.Save();
}
else
{
wb.SaveAs(fileName);
}
}
private void Tool_Click(object sender, RoutedEventArgs e)
{
MSWord.Application EXCL1 = new MSWord.Application(); //新建一个应用程序EXC1
EXCL1.Visible = true; //设置EXC1打开后可见
MSWord.Workbooks wbs = EXCL1.Workbooks;
MSWord._Workbook wb = wbs.Add(GlobalData.gFileName); //打开EXCEL
MSWord._Worksheet mySheet;
mySheet = wb.Sheets[wantUseSheetIndex(GlobalData.gMySheetName, wb)]; //找到我要操作的sheet
mySheet.Activate();
//tiaoshi.Text = excelElementRead(mySheet, 1, 5).Interior.Color.ToString();
System.Data.DataTable dt = readNonredunOneColumn(mySheet, GlobalData.gWantUseColumn);
MSWord._Worksheet testPointSheet = wb.Sheets[wantUseSheetIndex(GlobalData.gTestPointSheetName, wb)];
testPointSheet.Activate();
excelElementWrite(testPointSheet, 1, 1, "测试点序号");
excelElementWrite(testPointSheet, 1, 2, "测试点");
for (int i = 0; i < dt.Rows.Count; i++)
{
excelElementWrite(testPointSheet, i + 2, 1, dt.Rows[i][0].ToString());
excelElementWrite(testPointSheet, i + 2, 2, dt.Rows[i][1].ToString());
}
writeTestPointCount(mySheet, GlobalData.gTestPointRange, GlobalData.gWantUseColumn, dt);
wb.Save();
}
private void UpdateExcel(int row, int col, string data)
{
// UpdateExcel() is the method used to replace cells inside the specified
// Excel spreadsheet, using the Interop.Excel library.
Excel.Application oXL = null;
Excel._Workbook oWB = null;
Excel._Worksheet oSheet = null;
try
{
// This try block opens up your specified document in the File Chooser,
// and attempts to write to cells with the specified data given (string data).
oXL = new Excel.Application();
oWB = oXL.Workbooks.Open(excelDocument);
oSheet = String.IsNullOrEmpty(dropDown.Text) ? (Excel._Worksheet)oWB.ActiveSheet : (Excel._Worksheet)oWB.Worksheets[dropDown.Text];
oSheet.Cells[row, col] = data;
oWB.Save();
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
finally
{
if (oWB != null)
{
oWB.Close(); // Close down document, to avoid deadlock or R/W issues.
}
}
}
/// <summary>
/// Değişiklikleri kaydedin.
/// </summary>
public void Save()
{
if (m_Workbook != null)
{
m_Workbook.Save();
}
}
public void PrintWskazniki(List <Wskazniki> listaWskaznikow, string nazwaSpolki)
{
oWB = (Excel._Workbook)(oXL.Workbooks.Open("E:\\raporty\\wyniki.xlsx"));
oSheet = (Excel._Worksheet)oWB.Sheets.Add();
oSheet.Name = nazwaSpolki;
//oRng = oSheet.UsedRange;
oSheet.Activate();
// worksheet.get_Range(topLeftLetter, bottomRightLetter).EntireColumn.AutoFit();
for (int j = 0; j < 5; j++)
{
int startRok = 2010;
oSheet.Cells[1, j + 4] = (startRok + j).ToString();
}
for (int i = 0; i < listaWskaznikow.Count(); i++)
{
oSheet.Cells[i + 2, 1] = listaWskaznikow[i].category;
oSheet.Cells[i + 2, 2] = listaWskaznikow[i].name;
oSheet.Cells[i + 2, 3] = listaWskaznikow[i].wzor;
for (int j = 0; j < 5; j++)
{
oSheet.Cells[i + 2, j + 4] = listaWskaznikow[i].value[j];
}
}
oWB.Save();
}
//public bool dB_Delete(string p_dB, string p_Col, string p_Cond)
//{
// bool ret_val = false;
// string sql = "DELETE FROM [Sheet1$] WHERE [" + p_Col + "] LIKE '" + p_Cond + "%';";
// ret_val = dB_Update(p_dB, sql);
// return ret_val;
//}
public bool dB_Delete(string p_dB, string p_Col, string p_Cond)
{
bool ret_val = false;
int col_idx = 0, row_idx = 0;
string filename = DataDir + p_dB + dbFileExt;
XL.Application xlApp = new XL.Application();
XL._Workbook xlWorkbook = xlApp.Workbooks.Open(filename);
XL._Worksheet xlWorksheet = xlWorkbook.Worksheets["Sheet1"];
XL.Range xlRange = xlWorksheet.UsedRange;
int row_cnt = xlRange.Rows.Count;
int col_cnt = xlRange.Columns.Count;
// First find the column number
for (int i = 1; i < col_cnt; i++)
{
string val = xlRange.Cells[1, i].Value2.ToString();
if (val == p_Col)
{
col_idx = i;
break;
}
}
if (col_idx > 0)
{
// Next find the row number to delete
for (int i = 2; i <= row_cnt; i++)
{
string val = xlRange.Cells[i, col_idx].Value2.ToString();
if (val == p_Cond)
{
row_idx = i;
break;
}
}
if (row_idx > 0)
{
(xlWorksheet.Rows[row_idx, System.Reflection.Missing.Value] as XL.Range).Delete(XL.XlDeleteShiftDirection.xlShiftUp);
ret_val = true;
}
}
GC.Collect();
GC.WaitForPendingFinalizers();
xlApp.DisplayAlerts = false;
xlWorkbook.Save();
xlWorkbook.Close();
xlApp.Quit();
// release COM objects
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlWorksheet);
Marshal.ReleaseComObject(xlApp);
return(ret_val);
}
//adds project to SEQR workbook
private static void AddToWorkbook(string parkName, string projectName, bool newProject)
{
string region = albPath.Substring(0, albPath.Length - 1);
region = region.Substring(region.LastIndexOf("\\") + 1);
string smRegion = region.Replace(" ", "");
string workbook = albPath + smRegion + "SEQRWorkbook.xlsx";
//workbook = "D:\\CentralSEQRWorkbook.xlsx";
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = false;
Excel._Workbook wBk = excelApp.Workbooks.Open(workbook, 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);
Excel._Worksheet wSht = (Excel.Worksheet)wBk.Worksheets[1];
int rowNum = 0;
string searchStr = newProject ? "" : projectName;
int searchCol = newProject ? 1 : 3;
foreach (Excel.Range row in wSht.Rows)
{
Excel.Range rng = (Excel.Range)wSht.Cells[row.Row, searchCol];
if (rng.Text.ToString().Equals(searchStr))
{
rowNum = row.Row;
break;
}
}
if (newProject)
{
PrincipalContext ctx = new PrincipalContext(ContextType.Domain);
UserPrincipal user = UserPrincipal.Current;
string fullName = user.GivenName + " " + user.Surname;
wSht.Cells[rowNum, 1] = region;
wSht.Cells[rowNum, 2] = parkName;
wSht.Cells[rowNum, 3] = projectName;
wSht.Cells[rowNum, 5] = DateTime.Today.Year;
wSht.Cells[rowNum, 7] = fullName;
}
else
{
wSht.Hyperlinks.Add(wSht.Cells[rowNum, 4], albPath + parkName + "\\" + projectName, Type.Missing, Type.Missing, parkName + "\\" + projectName);
wSht.Cells[rowNum, 13] = DateTime.Today;
}
wBk.Save();
wBk.Close();
excelApp.Quit();
excelApp = null;
GC.Collect();
}
/// <summary>
/// 执行Excel中的宏
/// </summary>
/// <param name="excelFilePath">Excel文件路径</param>
/// <param name="macroName">宏名称</param>
/// <param name="parameters">宏参数组</param>
/// <param name="rtnValue">宏返回值</param>
/// <param name="isShowExcel">执行时是否显示Excel</param>
//public void RunExcelMacro(string excelFilePath, string macroName, object[] parameters, out object rtnValue, bool isShowExcel)
public void RunExcelMacro(string excelFilePath, string macroName, object[] parameters, object rtnValue, bool isShowExcel)
{
try
{
#region 检查入参
//检查文件是否存在
if (!File.Exists(excelFilePath))
{
MessageBox.Show(excelFilePath + " 文件不存在");
//return;
}
// 检查是否输入宏名称
if (string.IsNullOrEmpty(macroName))
{
MessageBox.Show("请输入宏的名称");
//return;
}
#endregion
#region 调用宏处理
// 准备打开Excel文件时的缺省参数对象
object oMissing = System.Reflection.Missing.Value;
// 根据参数组是否为空，准备参数组对象
object[] paraObjects;
if (parameters == null)
{
paraObjects = new object[] { macroName };
}
else
{
// 宏参数组长度
int paraLength = parameters.Length;
paraObjects = new object[paraLength + 1];
paraObjects[0] = macroName;
for (int i = 0; i < paraLength; i++)
{
paraObjects[i + 1] = parameters[i];
}
}
// 创建Excel对象示例
//Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
// 判断是否要求执行时Excel可见
if (isShowExcel)
{
// 使创建的对象可见
oExcel.Visible = false;
}
rtnValue = this.RunMacro(oExcel, paraObjects);
// 保存更改
oBook.Save();
}
catch (Exception)
{
throw;
}
}
private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application xlexcel = null;
Excel._Workbook xlWorkbook = null;
Excel._Worksheet xlWorkSheet = null;
Excel.Range oRng;
try
{
//start excel and get application object
oXL = new Microsoft.Office.Interop.Excel.Application();
//open existing workbook
oWB = oXL.Workbooks.Open("C:\\vehicledet.xlsx");
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//Add table headers going cell by cell.
xlWorkSheet.Cells[1, 1] = "Plate Number";
xlWorkSheet.Cells[1, 2] = "Car Model";
xlWorkSheet.Cells[1, 3] = "Car Brand";
xlWorkSheet.Cells[1, 4] = "Mileage";
//Format A1:D1 as bold, vertical alignment = center.
xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
xlWorkSheet.get_Range("A1", "D1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
// insert text at every last row
int _lastRow = xlWorkSheet.Range["A" + xlWorkSheet.Rows.Count].End[Excel.XlDirection.xlUp].Row + 1;
xlWorkSheet.Cells[_lastRow, 1] = textBox1.Text;
xlWorkSheet.Cells[_lastRow, 2] = textBox2.Text;
xlWorkSheet.Cells[_lastRow, 3] = textBox3.Text;
xlWorkSheet.Cells[_lastRow, 4] = textBox4.Text;
//AutoFit columns A:D.
oRng = xlWorkSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
//Make sure Excel is not visible and give the user control of Microsoft Excel's lifetime.
xlexcel.Visible = false;
xlexcel.UserControl = true;
//can save update to same file but wont close unless user press x button
xlWorkbook.Save();
MessageBox.Show("Data saved successfully");
xlWorkbook.Close();
//allow excel to auto close by itself
oXL.Quit();
Marshal.ReleaseComObject(oXL);
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
private void Button1_Click(object sender, RoutedEventArgs e)
{
string cellName = this.DataContext.ToString();
string fileName = "C:\\Temp\\" + cellName + ".xlsx";
FileInfo fi = new FileInfo(@fileName);
if (!fi.Exists)
{
wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
ws = wb.Worksheets[1];
ws.Range["A1"].Value = cellName;
DateTime currentDate = DateTime.Now;
for (int i = 2; i <= 1000; i++)
{
if (ws.Range["A" + i].Value == null)
{
ws.Range["A" + i].Value = currentDate;
break;
}
}
app.DisplayAlerts = false;
wb.SaveAs(fileName);
wb.Close();
app.Quit();
app.DisplayAlerts = true;
}
else
{
string myPath = (@fileName);
app.Workbooks.Open(myPath);
wb = app.ActiveWorkbook;
ws = wb.ActiveSheet;
DateTime currentDate = DateTime.Now;
for (int i = 2; i <= 1000; i++)
{
if (ws.Range["A" + i].Value == null)
{
ws.Range["A" + i].Value = currentDate;
break;
}
}
app.DisplayAlerts = false;
wb.Save();
wb.Close();
app.Quit();
app.DisplayAlerts = true;
}
}
public int InsertEntry(ref ExcelDataUnit unitData)
{
CouplingRetData retData = unitData.couplingRetData;
double totalTime = unitData.timeCost;
if (index < 0)
{
return(index);
}
xls_exp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xls_book = xls_exp.Workbooks.Open(fName, Missing.Value,
false, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, true,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
xls_sheet = (Microsoft.Office.Interop.Excel._Worksheet)xls_book.Worksheets.get_Item(1);
xls_sheet.Cells[index, 1] = Convert.ToString(index - startIndex + 1);
xls_sheet.Cells[index, 2] = unitData.prdId;
xls_sheet.Cells[index, 3] = retData.vpp.ToString();
xls_sheet.Cells[index, 4] = unitData.Vbr.ToString("F2");
xls_sheet.Cells[index, 5] = retData.lx1.ToString();
xls_sheet.Cells[index, 6] = retData.ly1.ToString();
xls_sheet.Cells[index, 7] = retData.lz.ToString();
xls_sheet.Cells[index, 8] = retData.lx2.ToString();
xls_sheet.Cells[index, 9] = retData.ly2.ToString();
xls_sheet.Cells[index, 10] = Convert.ToString(((double)retData.duration) / 1000000);
xls_sheet.Cells[index, 11] = totalTime.ToString();
xls_sheet.Cells[index, 12] = DateTime.Now.ToString("yyyy-MM-dd HH:mm");
xls_sheet.Cells[index, 13] = unitData.couplingRetData.vpp1.ToString();
xls_sheet.Cells[index, 14] = unitData.VAPD_Vbr3V_Iop_Vpp.ToString();
object misValue = System.Reflection.Missing.Value;
// if (index == 3)
// {
xls_sheet.Columns.AutoFit();
// }
xls_book.Saved = true;
//xls_exp.ActiveWorkbook.SaveCopyAs(fName);
xls_book.Save();
//xls_book.SaveAs(fName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
Close();
return(index++);
}
/// <summary>
/// Save file
/// </summary>
public static void Save()
{
if (LogCalls)
{
DebugLog.Message("ExcelOp Save");
}
try
{
XlBook.Save();
return;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
private void OnApplicationExit(object sender, EventArgs e)
{
Gem();
Thread.Sleep(100);
excelApp.Run("Opdater");
Thread.Sleep(2000);
excelWorkbook.Save();
Thread.Sleep(1000);
excelWorkbook.Close();
excelApp.Quit();
Marshal.FinalReleaseComObject(excelWorkbook);
Marshal.FinalReleaseComObject(excelApp);
excelApp = null;
excelWorkbook = null;
GC.Collect();
}
public void RunExcelMacro(Excel::Application excelApp, string macroName, object[] parameters, out object rtnValue)
{
object oMissing = System.Reflection.Missing.Value;
object[] paraObjects;
if (parameters == null)
{
paraObjects = new object[] { macroName };
}
else
{
// 宏参数组长度
int paraLength = parameters.Length;
paraObjects = new object[paraLength + 1];
paraObjects[0] = macroName;
for (int i = 0; i < paraLength; i++)
{
paraObjects[i + 1] = parameters[i];
}
}
rtnValue = "";
try
{
rtnValue = excelApp.GetType().InvokeMember(
"Run",
System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod,
null,
excelApp,
paraObjects
);
}
catch (Exception e)
{
SharedObject.Instance.Output(SharedObject.enOutputType.Error, "宏执行异常,请检查宏名称与参数是否匹配", e.Message);
}
Excel._Workbook oBook = excelApp.ActiveWorkbook;
oBook.Save();
}
public static void Export(DataTable dt, string filepath)
{
ExcelApp.Application myExcel = new ExcelApp.Application();
//Create a New file
ExcelApp._Workbook mybook = myExcel.Workbooks.Add();
//Open the exist file
//ExcelApp._Workbook mybook = myExcel.Workbooks.Open(filepath,
// 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);
//ExcelApp._Workbook mybook = myExcel.Workbooks.Open(Filename: filepath);
myExcel.Visible = true;
try
{
mybook.Activate();
ExcelApp._Worksheet mysheet = mybook.Worksheets.Add();
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
ExcelApp.Range cell =
mysheet.get_Range(((char)(65 + j)).ToString() + (i + 1).ToString());
cell.Select();
cell.Cells.FormulaR1C1 = dt.Rows[i][j] ?? "";
}
}
//mybook.SaveAs(Filename: filepath);
mybook.Save();
}
catch (Exception ex)
{
}
finally
{
mybook.Close();
myExcel.Quit();
GC.Collect();
}
}
private void countTask(String fileName)
{
endTask += 1;
Console.Clear();
Console.Write("Started scanning {0}files. {1}/{0}", taskCount, endTask);
if (endTask == taskCount)
{
if (EXCEL_PATH.Exists)
{
workBook.Save();
}
else
{
workBook.SaveAs(EXCEL_PATH.FullName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
workBook.Close();
excelApp.Quit();
System.Diagnostics.Process.Start("explorer.exe", Application.StartupPath);
System.Windows.Forms.Application.Exit();
}
}
protected void SaveWorkBook(bool blnSave)
{
// save and close the workbook
if (this.xlWorkBook != null)
{
if (blnSave && this.IsWorkBookOpened)
{
xlWorkBook.Save();
}
xlWorkBook.Close(true, misValue, misValue);
this.IsWorkBookOpened = false;
this.IsWorkSheetOpened = false;
}
if (xlApp != null && this.IsAppOpened)
{
xlApp.Quit();
this.IsAppOpened = false;
}
// close the object
this.ClearObject();
}
public GenerarExcel(M_Eno m, string titulo)
{
this.m = m;
this.titulo = titulo;
excel = new Excel.Application();
libro = (Excel._Workbook)excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
hoja = (Excel._Worksheet)libro.Worksheets.Add();
hoja.Name = "Resultado Del ENO";
((Excel.Worksheet)excel.ActiveWorkbook.Sheets["Hoja1"]).Delete();
generarDocumento(ref hoja);
if (System.IO.File.Exists(Environment.CurrentDirectory + @"Result.xlsx"))
{
libro.Save();
}
else
{
libro.SaveAs(Environment.CurrentDirectory + @"\Result.xlsx");
}
libro.Close();
releaseObject(libro);
excel.UserControl = false;
excel.Quit();
releaseObject(excel);
}
public bool AddToSheet(DataTable dt)
{
Excel._Workbook workbook = excelApp.Workbooks.Open(filename);
excelApp.Visible = false;
excelApp.DisplayAlerts = false;
workbook.Worksheets.Add();
int count = workbook.Worksheets.Count;
Excel._Worksheet sheet = workbook.Worksheets.get_Item(1);
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.Cells[1, i + 1] = dt.Columns[i].Caption;
}
for (int i = 0; i < dt.Columns.Count; i++)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
sheet.Cells[j + 2, i + 1] = dt.Rows[j].Field <object>(i);
}
}
workbook.Save();
workbook.Close();
return(true);
}
static void Main(string[] args)
{
// set english culture (for english function names and . decimal)
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");
// list seperator is now , Trying to change it to ; doesnt work :(
// System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator = ";";
// show manual
bool showMan = false;
// check pipe
String pipedText = "";
bool isKeyAvailable;
bool piped = false;
try { isKeyAvailable = System.Console.KeyAvailable; }
catch { pipedText = System.Console.In.ReadToEnd(); piped = true; }
// if no args or pipe, show manual
if (piped == false && args.Length == 0) showMan = true;
// set default values
string infile = "";
string paste = "";
List<string> macro = new List<string>();
int[] cellA = new int[2] { 0, 1 };
int[] cellB = new int[2] { 0, 1 };
int[] outCellA = new int[2] { 0, 1 };
int[] outCellB = new int[2] { 0, 0 };
string sheet = "";
string active = "";
// warnings are off by default, since excel will warn about ANYTHING, which interupts the program and leads to errors.
// f.eks. log charts will make the program fail, when they are given blank data in the step before new data is provided.
bool warning = false;
// save on exit
bool save = true;
// convert error codes to text in output
bool outErr = true;
// paste as text
bool asText = false;
// blehh..
string errLine = "--------------------------------------------------------------------------------";
// tab as default output space character
string cellSpacer = "\t";
// all charts that should be saved
List<string> charts = new List<string>();
// check input arguments
int iarg = 0;
for (int i = 0; i < args.Length;i++ )
{
if (args[i].StartsWith("-")) {
// show manual
if (args[i] == "-help" || args[i] == "--help" || args[i] == "-?") showMan = true;
// specify macro to run
if (args[i] == "-m") {
try {
macro.Add(args[i + 1]);
i++;
}
catch {
Error("No macro name given for -m.",1);
}
}
// paste input as text?
if (args[i] == "-t") asText = true;
// dont save
if (args[i] == "-n") save = false;
// hide warnings
if (args[i] == "-w") warning = true;
// set space character
if (args[i] == "-b")
{
cellSpacer = " ";
if (args.Length > i+1)
if (args[i + 1].Length == 1)
{
cellSpacer = args[i + 1];
i++;
}
}
// set paste sheet
if (args[i] == "-p")
{
try
{
active = args[i + 1];
i++;
}
catch
{
Error("No paste name given for -p.", 1);
}
}
// set output sheet
if (args[i] == "-s")
{
try
{
sheet = args[i + 1];
i++;
}
catch
{
Error("No sheet name given for -s.", 1);
}
}
// blank errors
if (args[i] == "-#") outErr = false;
}
else {
// excel file
if (iarg == 0) infile = args[i];
// paste file
else if (iarg == 1 && !piped)
{
paste = args[i];
if (paste == "~" || paste == "")
{
paste = "";
iarg++; iarg++;
}
}
// input cell ref
else if (iarg == 2)
{
string[] cellArr = args[i].Split(':');
if (cellArr.Length == 1) {
cellA = ExcelCellRef(cellArr[0]);
}
else {
cellA = ExcelCellRef(cellArr[0]);
cellB = ExcelCellRef(cellArr[1]);
iarg++;
}
}
else if (iarg == 3)
{
cellB = ExcelCellRef(args[i]);
}
// output cell ref
else if (iarg == 4)
{
string[] cellArr = args[i].Split(':');
if (cellArr.Length == 1)
{
outCellA = ExcelCellRef(cellArr[0]);
}
else
{
outCellA = ExcelCellRef(cellArr[0]);
outCellB = ExcelCellRef(cellArr[1]);
iarg++;
}
}
else if (iarg == 5)
{
outCellB = ExcelCellRef(args[i]);
}
// output charts
else if (iarg > 5)
{
charts.Add(args[i]);
}
iarg++;
}
}
// Print header
if (showMan)
{
Print(@"Usage: excel [OPTIONS] ExcelFile PasteFile Cell1 Cell2 OutCell1 OutCell2 [Chart1 [Chart2 ..]]
or: excel =FORMULA
Opens ExcelFile and places the contents of PasteFile from position given.
Position is the range between Cell1 and Cell2. Unused cells are cleared.
Echos all filled rows in the range between OutCell1 and OutCell2.
Any charts named will be saved to <ExcelFile_ChartN>.png.
'Sheet.ChartN' can be used if multiple charts has the same name.
-p PasteSheet Select the sheet that should be pasted to.
-s OutSheet Select the sheet that should be outputted.
-m Macro Run macro after paste. If -m is used multiples
times, more than 1 macro can be executed.
-n Do not save workbook
-w display Excel dialogs. Default is to surpress.
-# Replace errors with blanks in output
-b [CHAR] Set cell-spacing character in output to
'space' or 'CHAR' (default is 'tab')
-t Insert PasteFile as text instead of numbers
If '~' is specified as PasteFile no file is loaded and Cell1 and Cell2
should not be specified.
Version 1.0. Report bugs to <*****@*****.**>");
Environment.Exit(0);
}
if (infile.StartsWith("="))
{
string result = "";
try
{
result = ExcelMath.Calc(infile);
}
catch (Exception ex) {
Console.Error.WriteLine("Error: " + ex.Message);
#if DEBUG
Console.ReadKey();
#endif
Environment.Exit(1);
}
Print(result);
#if DEBUG
Console.ReadKey();
#endif
Environment.Exit(0);
}
// open file
if (piped == false && infile == "") Error("No file given.", 1);
System.IO.TextReader stream = new StringReader(pipedText);
if (!piped && paste.Length > 0)
{
try { stream = new StreamReader(paste); }
catch (Exception e) { Error("Unable to open file: " + paste + "\n\n"+errLine+"\n\n"+e.ToString(), 1); }
}
// number of lines and columns
int N = 0;
int C = 0;
// input data, as numbers and text. only one will be used
double[,] cells = null;
string[,] sCells = null;
// if pasted text
if (paste.Length > 0)
{
String line;
List<string[]> strings = new List<string[]>();
// trim all lines and split between words
while ((line = stream.ReadLine()) != null)
{
line = line.Replace(",", " ");
line = line.Replace("\t", " ");
line = line.Trim();
line = System.Text.RegularExpressions.Regex.Replace(line, @"\s+", " ");
if (asText)
{
//if (line.Length > 0)
strings.Add(line.Split(' '));
}
else if (line.Length > 0 && !line.StartsWith("#"))
strings.Add(line.Split(' '));
}
// set number of rows and columns
N = strings.Count();
C = 0;
foreach (string[] str in strings) if (str.Length > C) C = str.Length;
// convert input data to a format the Excel-interop understands: var[,]
if (asText) sCells = new string[N, C];
else cells = new double[N, C];
// parse all cells and add to array
for (int i = 0; i < N; i++)
{
for (int j = 0; j < strings[i].Length; j++)
{
try
{
if (asText) sCells[i, j] = strings[i][j];
else cells[i, j] = double.Parse(strings[i][j]);
}
catch (Exception e) { Error("Unable to parse number in paste file, line " + (i + 1) + ", column " + (j + 1) + ":\n" + strings[i][j] + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
}
}
}
Excel.Range startCell;
Excel.Range endCell;
try
{
// open excel app
oXL = new Excel.Application();
if (!warning) oXL.DisplayAlerts = false;
try
{
// try to open the selected excel file
// we turn of errors, since excel prompts for macro-enabled files and other things
oXL.DisplayAlerts = false;
// we need the absolute file path, since excel defaults to the user home dir, not the current working dir :S
oWB = oXL.Workbooks.Open(Path.GetFullPath(infile));
// turn back on warnings if wanted
if (warning) oXL.DisplayAlerts = true;
}
catch (Exception e) { throw new System.Exception("Unable to open file: " + Path.GetFullPath(infile) + "\n\n" + errLine + "\n\n" + e.ToString()); }
// set the active sheet
if (active.Length > 0)
{
try
{
oSheet = (Excel._Worksheet)oWB.Sheets[active];
}
catch (Exception e) { throw new System.Exception("Unable to select worksheet: " + active + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
else
oSheet = (Excel._Worksheet)oWB.Worksheets[1];
// insert data
if (paste.Length > 0)
{
// if only columns are specified, find the amount of rows used
if (cellA[0] == 0 && cellB[0] == 0)
{
string cell = GetExcelColumnName(cellA[1]) + ":" + GetExcelColumnName(cellB[1]);
Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
cellA[0] = 1;
cellB[0] = r.Rows.Count;
}
// select and paste values
try
{
startCell = (Excel.Range)oSheet.Cells[cellA[0], cellA[1]];
endCell = (Excel.Range)oSheet.Cells[cellB[0], cellB[1]];
oSheet.get_Range(startCell, endCell).Value = null;
endCell = (Excel.Range)oSheet.Cells[cellA[0] + N - 1, cellA[1] + C - 1];
if (asText) oSheet.get_Range(startCell, endCell).Value2 = sCells;
else oSheet.get_Range(startCell, endCell).Value2 = cells;
}
catch (Exception e)
{
string inputCell = GetExcelColumnName(cellA[1]) + cellA[0] + ":" + GetExcelColumnName(cellB[1]) + cellB[0];
throw new System.Exception("Unable to select input cells:\n\n " + inputCell + "\n\n" + errLine + "\n\n" + e.ToString());
}
}
// run macro
for (int i = 0; i < macro.Count; i++)
{
try
{
oXL.Run(macro[i]);
}
catch (Exception e) { throw new System.Exception("Unable to run macro: " + macro[i] + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
// force workbook refresh
oXL.Calculate();
// go to result sheet
if (sheet.Length > 0)
{
try
{
oSheet = (Excel._Worksheet)oWB.Sheets[sheet];
}
catch (Exception e) { throw new System.Exception("Unable to select output sheet:" + sheet + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
// save charts
foreach (Excel.Worksheet cSheet in oWB.Worksheets)
{
// loop trough all charts
Excel.ChartObjects xlCharts = (Excel.ChartObjects)cSheet.ChartObjects(Type.Missing);
for (int i = 1; i <= xlCharts.Count; i++)
{
Excel.ChartObject oChart = (Excel.ChartObject)xlCharts.Item(i);
Excel.Chart chart = oChart.Chart;
string chartName = "";
if (charts.Contains(cSheet.Name + "." + oChart.Name)) chartName = cSheet.Name + "." + oChart.Name;
else if (charts.Contains(oChart.Name)) chartName = oChart.Name;
// if chart is specified for output, save it
if (chartName.Length > 0)
{
int id = charts.FindIndex(s => s == chartName);
charts.RemoveAt(id);
try
{
// we need full path name again.. excel defaults to user home dir...
string saveas = Path.GetFullPath(infile);
saveas = Path.GetDirectoryName(saveas) + "\\" + Path.GetFileNameWithoutExtension(saveas);
saveas = saveas + "_" + chartName + ".png";
chart.Export(saveas, "PNG");
}
catch (Exception e) { throw new System.Exception("Unable to save chart '" + chartName + "':\n\n" + errLine + "\n\n" + e.ToString()); }
}
}
}
// if any charts was not found; throw an error.
if (charts.Count > 0)
{
string list = "";
foreach (string s in charts) list += s + ", ";
throw new Exception("Unable to find chart(s): " + list);
}
// if only columns are specified, find amount of rows to use
if (outCellA[0] == 0 && outCellB[0] == 0)
{
if (outCellB[1] == 0) outCellB[1] = oSheet.UsedRange.Columns.Count;
string cell = GetExcelColumnName(outCellA[1]) + ":" + GetExcelColumnName(outCellB[1]);
Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
outCellA[0] = 1;
outCellB[0] = r.Rows.Count;
}
// select the output cell range
try
{
startCell = (Excel.Range)oSheet.Cells[outCellA[0], outCellA[1]];
endCell = (Excel.Range)oSheet.Cells[outCellB[0], outCellB[1]];
}
catch (Exception e)
{
string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
throw new System.Exception("Unable to select output cells:\n " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
}
// get output from selected cells
object[,] arr = null;
try
{
Excel.Range r = (Excel.Range)oSheet.get_Range(startCell, endCell);
// if only 1 cell is selected, excel will return an object instead of object array!
if (r.Cells.Count == 1)
{
arr = new object[2, 2];
arr[1, 1] = r.Cells.Value2;
}
else arr = r.Cells.Value2 as object[,];
}
catch (Exception e)
{
string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
throw new System.Exception("Invalid OutCells given. Unable to retrieve data:\n " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
}
List<string> results = new List<string>();
int last = 0;
N = outCellB[0] - outCellA[0] + 1;
C = outCellB[1] - outCellA[1] + 1;
// loop trough output rows
for (int i = 1; i <= N; i++)
{
// loop trough output columns
string s = "";
for (int j = 1; j <= C; j++)
{
// check if cell contains an error
if (arr[i, j] is Int32)
{
if (outErr)
{
int eCode = (int)arr[i, j];
string e = "";
if (eCode == -2146826281) e = "#DIV/0!";
else if (eCode == -2146826246) e = "#N/A";
else if (eCode == -2146826259) e = "#NAME?";
else if (eCode == -2146826288) e = "#NULL!";
else if (eCode == -2146826252) e = "#NUM!";
else if (eCode == -2146826265) e = "#REF!";
else if (eCode == -2146826273) e = "#VALUE!";
// no more error codes exists (?) as of 2013.. But to be sure / support future ones:
else e = "#ERR" + eCode.ToString();
s = s + e + " " + cellSpacer;
}
else s = s + " " + cellSpacer;
}
else if (arr[i, j] != null) s = s + arr[i, j].ToString() + cellSpacer;
else s = s + " " + cellSpacer;
}
// remove cellspacer from last column
if (C > 0) results.Add(s.Remove(s.Length - 1).TrimEnd());
// record last row column with content
if (s.Replace(cellSpacer, " ").TrimEnd().Length > 0) last = results.Count();
}
// write output to console
for (int i = 0; i < last; i++)
Console.WriteLine(results[i]);
// save file
if (save)
{
// if macros are enabled, excel would prompt about saving
oXL.DisplayAlerts = false;
oWB.Save();
}
}
// catch any exception
catch (Exception theException)
{
Error(errLine + "\n Error: " + theException.Message, 1);
}
finally
{
// clean up and exit
CleanUp();
}
#if DEBUG
Console.ReadKey();
#endif
}
static void Main(string[] args)
{
// set english culture (for english function names and . decimal)
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-GB");
// list seperator is now , Trying to change it to ; doesnt work :(
// System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator = ";";
// show manual
bool showMan = false;
// check pipe
String pipedText = "";
bool isKeyAvailable;
bool piped = false;
try { isKeyAvailable = System.Console.KeyAvailable; }
catch { pipedText = System.Console.In.ReadToEnd(); piped = true; }
// if no args or pipe, show manual
if (piped == false && args.Length == 0)
{
showMan = true;
}
// set default values
string infile = "";
string paste = "";
List <string> macro = new List <string>();
int[] cellA = new int[2] {
0, 1
};
int[] cellB = new int[2] {
0, 1
};
int[] outCellA = new int[2] {
0, 1
};
int[] outCellB = new int[2] {
0, 0
};
string sheet = "";
string active = "";
// warnings are off by default, since excel will warn about ANYTHING, which interupts the program and leads to errors.
// f.eks. log charts will make the program fail, when they are given blank data in the step before new data is provided.
bool warning = false;
// save on exit
bool save = true;
// convert error codes to text in output
bool outErr = true;
// paste as text
bool asText = false;
// blehh..
string errLine = "--------------------------------------------------------------------------------";
// tab as default output space character
string cellSpacer = "\t";
// all charts that should be saved
List <string> charts = new List <string>();
// check input arguments
int iarg = 0;
for (int i = 0; i < args.Length; i++)
{
if (args[i].StartsWith("-"))
{
// show manual
if (args[i] == "-help" || args[i] == "--help" || args[i] == "-?")
{
showMan = true;
}
// specify macro to run
if (args[i] == "-m")
{
try {
macro.Add(args[i + 1]);
i++;
}
catch {
Error("No macro name given for -m.", 1);
}
}
// paste input as text?
if (args[i] == "-t")
{
asText = true;
}
// dont save
if (args[i] == "-n")
{
save = false;
}
// hide warnings
if (args[i] == "-w")
{
warning = true;
}
// set space character
if (args[i] == "-b")
{
cellSpacer = " ";
if (args.Length > i + 1)
{
if (args[i + 1].Length == 1)
{
cellSpacer = args[i + 1];
i++;
}
}
}
// set paste sheet
if (args[i] == "-p")
{
try
{
active = args[i + 1];
i++;
}
catch
{
Error("No paste name given for -p.", 1);
}
}
// set output sheet
if (args[i] == "-s")
{
try
{
sheet = args[i + 1];
i++;
}
catch
{
Error("No sheet name given for -s.", 1);
}
}
// blank errors
if (args[i] == "-#")
{
outErr = false;
}
}
else
{
// excel file
if (iarg == 0)
{
infile = args[i];
}
// paste file
else if (iarg == 1 && !piped)
{
paste = args[i];
if (paste == "~" || paste == "")
{
paste = "";
iarg++; iarg++;
}
}
// input cell ref
else if (iarg == 2)
{
string[] cellArr = args[i].Split(':');
if (cellArr.Length == 1)
{
cellA = ExcelCellRef(cellArr[0]);
}
else
{
cellA = ExcelCellRef(cellArr[0]);
cellB = ExcelCellRef(cellArr[1]);
iarg++;
}
}
else if (iarg == 3)
{
cellB = ExcelCellRef(args[i]);
}
// output cell ref
else if (iarg == 4)
{
string[] cellArr = args[i].Split(':');
if (cellArr.Length == 1)
{
outCellA = ExcelCellRef(cellArr[0]);
}
else
{
outCellA = ExcelCellRef(cellArr[0]);
outCellB = ExcelCellRef(cellArr[1]);
iarg++;
}
}
else if (iarg == 5)
{
outCellB = ExcelCellRef(args[i]);
}
// output charts
else if (iarg > 5)
{
charts.Add(args[i]);
}
iarg++;
}
}
// Print header
if (showMan)
{
Print(@"Usage: excel [OPTIONS] ExcelFile PasteFile Cell1 Cell2 OutCell1 OutCell2 [Chart1 [Chart2 ..]]
or: excel =FORMULA
Opens ExcelFile and places the contents of PasteFile from position given.
Position is the range between Cell1 and Cell2. Unused cells are cleared.
Echos all filled rows in the range between OutCell1 and OutCell2.
Any charts named will be saved to <ExcelFile_ChartN>.png.
'Sheet.ChartN' can be used if multiple charts has the same name.
-p PasteSheet Select the sheet that should be pasted to.
-s OutSheet Select the sheet that should be outputted.
-m Macro Run macro after paste. If -m is used multiples
times, more than 1 macro can be executed.
-n Do not save workbook
-w display Excel dialogs. Default is to surpress.
-# Replace errors with blanks in output
-b [CHAR] Set cell-spacing character in output to
'space' or 'CHAR' (default is 'tab')
-t Insert PasteFile as text instead of numbers
If '~' is specified as PasteFile no file is loaded and Cell1 and Cell2
should not be specified.
Version 1.0. Report bugs to <*****@*****.**>");
Environment.Exit(0);
}
if (infile.StartsWith("="))
{
string result = "";
try
{
result = ExcelMath.Calc(infile);
}
catch (Exception ex) {
Console.Error.WriteLine("Error: " + ex.Message);
#if DEBUG
Console.ReadKey();
#endif
Environment.Exit(1);
}
Print(result);
#if DEBUG
Console.ReadKey();
#endif
Environment.Exit(0);
}
// open file
if (piped == false && infile == "")
{
Error("No file given.", 1);
}
System.IO.TextReader stream = new StringReader(pipedText);
if (!piped && paste.Length > 0)
{
try { stream = new StreamReader(paste); }
catch (Exception e) { Error("Unable to open file: " + paste + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
}
// number of lines and columns
int N = 0;
int C = 0;
// input data, as numbers and text. only one will be used
double[,] cells = null;
string[,] sCells = null;
// if pasted text
if (paste.Length > 0)
{
String line;
List <string[]> strings = new List <string[]>();
// trim all lines and split between words
while ((line = stream.ReadLine()) != null)
{
line = line.Replace(",", " ");
line = line.Replace("\t", " ");
line = line.Trim();
line = System.Text.RegularExpressions.Regex.Replace(line, @"\s+", " ");
if (asText)
{
//if (line.Length > 0)
strings.Add(line.Split(' '));
}
else if (line.Length > 0 && !line.StartsWith("#"))
{
strings.Add(line.Split(' '));
}
}
// set number of rows and columns
N = strings.Count();
C = 0;
foreach (string[] str in strings)
{
if (str.Length > C)
{
C = str.Length;
}
}
// convert input data to a format the Excel-interop understands: var[,]
if (asText)
{
sCells = new string[N, C];
}
else
{
cells = new double[N, C];
}
// parse all cells and add to array
for (int i = 0; i < N; i++)
{
for (int j = 0; j < strings[i].Length; j++)
{
try
{
if (asText)
{
sCells[i, j] = strings[i][j];
}
else
{
cells[i, j] = double.Parse(strings[i][j]);
}
}
catch (Exception e) { Error("Unable to parse number in paste file, line " + (i + 1) + ", column " + (j + 1) + ":\n" + strings[i][j] + "\n\n" + errLine + "\n\n" + e.ToString(), 1); }
}
}
}
Excel.Range startCell;
Excel.Range endCell;
try
{
// open excel app
oXL = new Excel.Application();
if (!warning)
{
oXL.DisplayAlerts = false;
}
try
{
// try to open the selected excel file
// we turn of errors, since excel prompts for macro-enabled files and other things
oXL.DisplayAlerts = false;
// we need the absolute file path, since excel defaults to the user home dir, not the current working dir :S
oWB = oXL.Workbooks.Open(Path.GetFullPath(infile));
// turn back on warnings if wanted
if (warning)
{
oXL.DisplayAlerts = true;
}
}
catch (Exception e) { throw new System.Exception("Unable to open file: " + Path.GetFullPath(infile) + "\n\n" + errLine + "\n\n" + e.ToString()); }
// set the active sheet
if (active.Length > 0)
{
try
{
oSheet = (Excel._Worksheet)oWB.Sheets[active];
}
catch (Exception e) { throw new System.Exception("Unable to select worksheet: " + active + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
else
{
oSheet = (Excel._Worksheet)oWB.Worksheets[1];
}
// insert data
if (paste.Length > 0)
{
// if only columns are specified, find the amount of rows used
if (cellA[0] == 0 && cellB[0] == 0)
{
string cell = GetExcelColumnName(cellA[1]) + ":" + GetExcelColumnName(cellB[1]);
Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
cellA[0] = 1;
cellB[0] = r.Rows.Count;
}
// select and paste values
try
{
startCell = (Excel.Range)oSheet.Cells[cellA[0], cellA[1]];
endCell = (Excel.Range)oSheet.Cells[cellB[0], cellB[1]];
oSheet.get_Range(startCell, endCell).Value = null;
endCell = (Excel.Range)oSheet.Cells[cellA[0] + N - 1, cellA[1] + C - 1];
if (asText)
{
oSheet.get_Range(startCell, endCell).Value2 = sCells;
}
else
{
oSheet.get_Range(startCell, endCell).Value2 = cells;
}
}
catch (Exception e)
{
string inputCell = GetExcelColumnName(cellA[1]) + cellA[0] + ":" + GetExcelColumnName(cellB[1]) + cellB[0];
throw new System.Exception("Unable to select input cells:\n\n " + inputCell + "\n\n" + errLine + "\n\n" + e.ToString());
}
}
// run macro
for (int i = 0; i < macro.Count; i++)
{
try
{
oXL.Run(macro[i]);
}
catch (Exception e) { throw new System.Exception("Unable to run macro: " + macro[i] + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
// force workbook refresh
oXL.Calculate();
// go to result sheet
if (sheet.Length > 0)
{
try
{
oSheet = (Excel._Worksheet)oWB.Sheets[sheet];
}
catch (Exception e) { throw new System.Exception("Unable to select output sheet:" + sheet + "\n\n" + errLine + "\n\n" + e.ToString()); }
}
// save charts
foreach (Excel.Worksheet cSheet in oWB.Worksheets)
{
// loop trough all charts
Excel.ChartObjects xlCharts = (Excel.ChartObjects)cSheet.ChartObjects(Type.Missing);
for (int i = 1; i <= xlCharts.Count; i++)
{
Excel.ChartObject oChart = (Excel.ChartObject)xlCharts.Item(i);
Excel.Chart chart = oChart.Chart;
string chartName = "";
if (charts.Contains(cSheet.Name + "." + oChart.Name))
{
chartName = cSheet.Name + "." + oChart.Name;
}
else if (charts.Contains(oChart.Name))
{
chartName = oChart.Name;
}
// if chart is specified for output, save it
if (chartName.Length > 0)
{
int id = charts.FindIndex(s => s == chartName);
charts.RemoveAt(id);
try
{
// we need full path name again.. excel defaults to user home dir...
string saveas = Path.GetFullPath(infile);
saveas = Path.GetDirectoryName(saveas) + "\\" + Path.GetFileNameWithoutExtension(saveas);
saveas = saveas + "_" + chartName + ".png";
chart.Export(saveas, "PNG");
}
catch (Exception e) { throw new System.Exception("Unable to save chart '" + chartName + "':\n\n" + errLine + "\n\n" + e.ToString()); }
}
}
}
// if any charts was not found; throw an error.
if (charts.Count > 0)
{
string list = "";
foreach (string s in charts)
{
list += s + ", ";
}
throw new Exception("Unable to find chart(s): " + list);
}
// if only columns are specified, find amount of rows to use
if (outCellA[0] == 0 && outCellB[0] == 0)
{
if (outCellB[1] == 0)
{
outCellB[1] = oSheet.UsedRange.Columns.Count;
}
string cell = GetExcelColumnName(outCellA[1]) + ":" + GetExcelColumnName(outCellB[1]);
Excel.Range r = (Excel.Range)oSheet.UsedRange.Columns[cell, Type.Missing];
outCellA[0] = 1;
outCellB[0] = r.Rows.Count;
}
// select the output cell range
try
{
startCell = (Excel.Range)oSheet.Cells[outCellA[0], outCellA[1]];
endCell = (Excel.Range)oSheet.Cells[outCellB[0], outCellB[1]];
}
catch (Exception e)
{
string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
throw new System.Exception("Unable to select output cells:\n " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
}
// get output from selected cells
object[,] arr = null;
try
{
Excel.Range r = (Excel.Range)oSheet.get_Range(startCell, endCell);
// if only 1 cell is selected, excel will return an object instead of object array!
if (r.Cells.Count == 1)
{
arr = new object[2, 2];
arr[1, 1] = r.Cells.Value2;
}
else
{
arr = r.Cells.Value2 as object[, ];
}
}
catch (Exception e)
{
string outcell = GetExcelColumnName(outCellA[1]) + outCellA[0] + ":" + GetExcelColumnName(outCellB[1]) + outCellB[0];
throw new System.Exception("Invalid OutCells given. Unable to retrieve data:\n " + outcell + "\n\n" + errLine + "\n\n" + e.ToString());
}
List <string> results = new List <string>();
int last = 0;
N = outCellB[0] - outCellA[0] + 1;
C = outCellB[1] - outCellA[1] + 1;
// loop trough output rows
for (int i = 1; i <= N; i++)
{
// loop trough output columns
string s = "";
for (int j = 1; j <= C; j++)
{
// check if cell contains an error
if (arr[i, j] is Int32)
{
if (outErr)
{
int eCode = (int)arr[i, j];
string e = "";
if (eCode == -2146826281)
{
e = "#DIV/0!";
}
else if (eCode == -2146826246)
{
e = "#N/A";
}
else if (eCode == -2146826259)
{
e = "#NAME?";
}
else if (eCode == -2146826288)
{
e = "#NULL!";
}
else if (eCode == -2146826252)
{
e = "#NUM!";
}
else if (eCode == -2146826265)
{
e = "#REF!";
}
else if (eCode == -2146826273)
{
e = "#VALUE!";
}
// no more error codes exists (?) as of 2013.. But to be sure / support future ones:
else
{
e = "#ERR" + eCode.ToString();
}
s = s + e + " " + cellSpacer;
}
else
{
s = s + " " + cellSpacer;
}
}
else if (arr[i, j] != null)
{
s = s + arr[i, j].ToString() + cellSpacer;
}
else
{
s = s + " " + cellSpacer;
}
}
// remove cellspacer from last column
if (C > 0)
{
results.Add(s.Remove(s.Length - 1).TrimEnd());
}
// record last row column with content
if (s.Replace(cellSpacer, " ").TrimEnd().Length > 0)
{
last = results.Count();
}
}
// write output to console
for (int i = 0; i < last; i++)
{
Console.WriteLine(results[i]);
}
// save file
if (save)
{
// if macros are enabled, excel would prompt about saving
oXL.DisplayAlerts = false;
oWB.Save();
}
}
// catch any exception
catch (Exception theException)
{
Error(errLine + "\n Error: " + theException.Message, 1);
}
finally
{
// clean up and exit
CleanUp();
}
#if DEBUG
Console.ReadKey();
#endif
}
public void save()
{
m_objBook.Save();
}
public string AddTeam(DiscordUser user, string teamName, string sheetName)
{
Excel.Application oXL = null;
Excel._Workbook oWB = null;
Excel.Workbooks workBooks = null;
Excel._Worksheet oSheet = null;
Excel.Sheets sheets = null;
Excel.Range oRng = null;
string returnString = null;
try
{
isProcessing = true;
oXL = new Excel.Application();
workBooks = oXL.Workbooks;
oWB = workBooks.Open(Program.excelFilePath);
sheets = oWB.Worksheets;
oSheet = (Excel._Worksheet)sheets[sheetName];
bool emptyCellFound = false;
bool teamNameFound = false;
bool hasTeam = false;
int count = 0;
string ownerTag = user.Username + " " + user.Mention;
while (!emptyCellFound && !teamNameFound && !hasTeam)
{
if (string.Equals(oSheet.Cells[startRow + (count * 2), 1].Value, null))
{
emptyCellFound = true;
}
else if (string.Equals(oSheet.Cells[startRow + (count * 2), 1].Value, teamName))
{
teamNameFound = true;
}
else if (string.Equals(oSheet.Cells[startRow + (count * 2), 2].Value, ownerTag))
{
hasTeam = true;
}
else
{
count++;
}
}
if (teamNameFound)
{
returnString = teamName + " has already been registered!";
}
else if (hasTeam)
{
returnString = user.Mention + " You have already registered a team!";
}
else if (emptyCellFound)
{
oSheet.Cells[startRow + (count * 2), 1].Value = teamName;
oRng = oSheet.get_Range("A2", "N2");
oRng.EntireColumn.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng.EntireColumn.Font.Bold = true;
oRng.EntireColumn.Font.Size = 15;
oRng.EntireColumn.AutoFit();
oRng = oSheet.Rows[startRow + (count * 2)];
Excel.Borders borders = oRng.Borders;
borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 4d;
oRng = oSheet.Cells[startRow + (count * 2), 1];
oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
oRng.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = 3d;
oSheet.Cells[startRow + (count * 2), 2].Value = ownerTag;
returnString = user.Mention + " " + teamName + " registered!";
oWB.Save();
}
}
catch (Exception ex)
{
string exceptionString = ex.ToString();
if (exceptionString.Contains("Microsoft.Office.Interop.Excel.Workbooks.Open"))
{
return(user.Mention + " I am processing another operation, please wait.");
}
return("Error: Something went wrong, please contact an admin.");
}
finally
{
if (oWB != null)
{
foreach (Excel.Workbook _workbook in oXL.Workbooks)
{
_workbook.Close();
}
oXL.Quit();
oXL = null;
var process = System.Diagnostics.Process.GetProcessesByName("Excel");
foreach (var p in process)
{
if (!string.IsNullOrEmpty(p.ProcessName))
{
try
{
p.Kill();
}
catch { }
}
}
}
}
switch (sheetName)
{
case "Overwatch":
teamCountOverwatch++;
break;
}
isProcessing = false;
return(returnString);
}
public string ChangeTeamName(DiscordUser user, string teamName, string sheetName, out string oldName)
{
Excel.Application oXL = null;
Excel._Workbook oWB = null;
Excel.Workbooks workBooks = null;
Excel._Worksheet oSheet = null;
Excel.Sheets sheets = null;
bool teamNameFound = false;
int teamRow = 0;
string returnString = null;
string ownerTag = user.Username + " " + user.Mention;
string oldTeamName = null;
try
{
isProcessing = true;
oXL = new Excel.Application();
workBooks = oXL.Workbooks;
oWB = workBooks.Open(Program.excelFilePath);
sheets = oWB.Worksheets;
oSheet = (Excel._Worksheet)sheets[sheetName];
for (int i = 0; i < teamCountOverwatch; i++)
{
if (ownerTag.Equals(oSheet.Cells[startRow + (i * 2), 2].Value))
{
teamNameFound = true;
teamRow = startRow + (i * 2);
break;
}
}
if (!teamNameFound)
{
returnString = user.Mention + " You do not own a team!";
}
else
{
oldTeamName = oSheet.Cells[teamRow, 1].Value;
oSheet.Cells[teamRow, 1].Value = teamName;
oWB.Save();
returnString = user.Mention + " Team's name changed from " + '"' + oldTeamName + '"' + " to " + '"' + teamName + '"';
}
}
catch (Exception ex)
{
string exceptionString = ex.ToString();
if (exceptionString.Contains("Microsoft.Office.Interop.Excel.Workbooks.Open"))
{
oldName = null;
return(user.Mention + " I am processing another operation, please wait.");
}
oldName = null;
return("Error: Something went wrong, please contact an admin.");
}
finally
{
if (oWB != null)
{
foreach (Excel.Workbook _workbook in oXL.Workbooks)
{
_workbook.Close();
}
oXL.Quit();
oXL = null;
var process = System.Diagnostics.Process.GetProcessesByName("Excel");
foreach (var p in process)
{
if (!string.IsNullOrEmpty(p.ProcessName))
{
try
{
p.Kill();
}
catch { }
}
}
}
isProcessing = false;
}
oldName = oldTeamName;
return(returnString);
}
public string AddPlayerToTeam(DiscordUser user, DiscordUser userToAdd, string sheetName)
{
Excel.Application oXL = null;
Excel._Workbook oWB = null;
Excel.Workbooks workBooks = null;
Excel._Worksheet oSheet = null;
Excel.Sheets sheets = null;
Excel.Range oRng = null;
string returnString = null;
try
{
isProcessing = true;
oXL = new Excel.Application();
workBooks = oXL.Workbooks;
oWB = workBooks.Open(Program.excelFilePath);
sheets = oWB.Worksheets;
oSheet = (Excel._Worksheet)sheets[sheetName];
bool emptyCellFound = false;
bool teamNameFound = false;
bool userFound = false;
int teamRow = 0;
string teamName = null;
string ownerTag = user.Username + " " + user.Mention;
string addedUserTag = userToAdd.Username + " " + userToAdd.Mention;
for (int i = 0; i < teamCountOverwatch; i++)
{
if (ownerTag.Equals(oSheet.Cells[startRow + (i * 2), 2].Value))
{
teamNameFound = true;
teamRow = startRow + (i * 2);
teamName = oSheet.Cells[startRow + (i * 2), 1].Value;
oRng = oSheet.Rows[teamRow];
break;
}
}
if (!teamNameFound || teamName == null)
{
returnString = "Your team was not found!";
}
else if (teamNameFound)
{
int foundColumn = 0;
for (int i = 1; i < 100; i++)
{
if (string.Equals(oSheet.Cells[teamRow, i].Value, null))
{
emptyCellFound = true;
foundColumn = i;
break;
}
else if (string.Equals(oSheet.Cells[teamRow, i].Value, addedUserTag))
{
userFound = true;
break;
}
}
if (userFound)
{
returnString = userToAdd.Mention + " is already in this team!";
}
else if (emptyCellFound)
{
oSheet.Cells[teamRow, foundColumn].Value = addedUserTag;
oWB.Save();
returnString = userToAdd.Mention + " added to team " + teamName;
}
}
}
catch (Exception ex)
{
string exceptionString = ex.ToString();
if (exceptionString.Contains("Microsoft.Office.Interop.Excel.Workbooks.Open"))
{
return(user.Mention + " I am processing another operation, please wait.");
}
return("Error: Something went wrong, please contact an admin.");
}
finally
{
if (oWB != null)
{
foreach (Excel.Workbook _workbook in oXL.Workbooks)
{
_workbook.Close();
}
oXL.Quit();
oXL = null;
var process = System.Diagnostics.Process.GetProcessesByName("Excel");
foreach (var p in process)
{
if (!string.IsNullOrEmpty(p.ProcessName))
{
try
{
p.Kill();
}
catch { }
}
}
}
isProcessing = false;
}
return(returnString);
}
static void MainTask(string[] args)
{
// display usage if no arguments
if (args.Length == 0)
{
Console.WriteLine(Usage());
return;
}
var xlApp = new Microsoft.Office.Interop.Excel.Application();
try
{
Microsoft.Office.Interop.Excel._Workbook wbk = null;
Microsoft.Office.Interop.Excel.Range rng = null;
xlApp.DisplayAlerts = false;
xlApp.Visible = true;
xlApp.Interactive = true;
// no-well established parsearg method
for (int i = 0; i < args.Length; i++)
{
var cmd = args[i];
while (cmd[0] == '-' || cmd[0] == '/')
{
cmd = cmd.Substring(1);
}
switch (cmd)
{
case "xlFileOpen":
case "xlFilePath":
var openPath = MakeFullPath(args[++i]);
Console.WriteLine($"> Open Workbook {openPath}");
if (wbk != null)
{
wbk.Close(SaveChanges: false);
}
wbk = xlApp.Workbooks.Open(openPath);
break;
case "xlFileNew":
Console.WriteLine($"> New Workbook");
if (wbk != null)
{
wbk.Close(SaveChanges: false);
}
wbk = xlApp.Workbooks.Add();
break;
case "xlFileSave":
Console.WriteLine($"> Save Workbook");
wbk.Save();
break;
case "xlFileSaveAs":
var savePath = MakeFullPath(args[++i]);
Console.WriteLine($"> Save Workbook as {savePath}");
wbk.SaveAs(savePath);
break;
case "xlEvalMacro":
var macro = args[++i];
Console.WriteLine($"> Evaluate macro {macro}");
xlApp.Evaluate(macro);
break;
case "xlRefreshLeftToRight":
Console.WriteLine($"> Refresh sheets left to right");
foreach (Microsoft.Office.Interop.Excel._Worksheet wsh in wbk.Worksheets)
{
if (wsh.Visible == XlSheetVisibility.xlSheetVisible)
{
wsh.Calculate();
}
}
break;
case "xlRngGet":
var rngGetAddr = args[++i];
Console.WriteLine($"> Get Range {rngGetAddr}");
rng = xlApp.Range[rngGetAddr];
Console.WriteLine(rng.Value);
break;
case "xlRngSet":
var rngSetAddr = args[++i];
var rngSetValue = args[++i];
Console.WriteLine($"> Set Range {rngSetAddr} {rngSetValue}");
rng = xlApp.Range[rngSetAddr];
rng.Value = rngSetValue;
break;
case "timeout":
i++;
break;
default:
throw new Exception($"> Unexpected command {cmd}");
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Environment.Exit(1);
}
finally
{
foreach (Microsoft.Office.Interop.Excel._Workbook wbk in xlApp.Workbooks)
{
wbk.Close(SaveChanges: false);
}
xlApp.Quit();
Console.WriteLine("Done.");
}
}
/// <summary>
/// 执行Excel中的宏
/// </summary>
/// <param name="excelFilePath">Excel文件路径</param>
/// <param name="macroName">Excel宏名称</param>
/// <param name="parameters">Excel宏参数组</param>
/// <param name="rtnValue">Excel宏返回值</param>
/// <param name="isShowExcel">执行时是否打开并显示Excel</param>
public void RunExcelMacro(string excelFilePath, string macroName, object[] parameters, out object rtnValue, bool isShowExcel)
{
#region 初始化对象
Excel.ApplicationClass oExcel = null;
Excel.Workbooks oBooks = null;
Excel._Workbook oBook = null;
#endregion
try
{
#region 检查参数
// 检查文件是否存在
if (!File.Exists(excelFilePath))
{
throw new System.Exception(excelFilePath + " 文件不存在");
}
// 检查是否输入宏名称
if (string.IsNullOrEmpty(macroName))
{
throw new System.Exception("请输入宏的名称");
}
#endregion
#region 调用宏处理
// 缺省参数对象
object oMissing = System.Reflection.Missing.Value;
// 根据参数组是否为空，准备参数组对象
object[] paraObjects;
if (parameters == null)
{
paraObjects = new object[] { macroName };
}
else
{
// 宏参数组长度
int paraLength = parameters.Length;
paraObjects = new object[paraLength + 1];
paraObjects[0] = macroName;
for (int i = 0; i < paraLength; i++)
{
paraObjects[i + 1] = parameters[i];
}
}
// 创建Excel对象
oExcel = new Excel.ApplicationClass();
// 判断是否要求执行时Excel可见
if (isShowExcel)
{
// 使创建的对象可见
oExcel.Visible = true;
}
// 获取Workbooks对象
oBooks = oExcel.Workbooks;
// 打开指定的Excel文件，赋值Workbook对象
oBook = oBooks.Open(
excelFilePath,
oMissing,
oMissing,
oMissing,
oMissing,
oMissing,
oMissing,
oMissing,
oMissing,
oMissing,
oMissing,
oMissing,
oMissing,
oMissing,
oMissing
);
// 执行Excel中的宏
rtnValue = this.RunMacro(oExcel, paraObjects);
// 保存更改
oBook.Save();
// 退出Workbook
oBook.Close(false, oMissing, oMissing);
#endregion
}
catch (Exception ex)
{
#region 异常处理
throw ex;
#endregion
}
finally
{
#region 释放对象
// 释放Workbook对象
if (oBook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
oBook = null;
}
// 释放Workbooks对象
if (oBooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
oBooks = null;
}
// 关闭Excel，并释放Excel对象
if (oExcel != null)
{
oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
oExcel = null;
}
// 调用垃圾回收
GC.Collect();
#endregion
}
}
public void CopyMacro(string path1, string path2)
{
Excel.Application app = new Excel.Application();
app.Visible = true;
Excel._Workbook wSource = app.Workbooks.Open(path1), wDestination = app.Workbooks.Open(path2);
bool IsSourceProtected = Convert.ToBoolean(wSource.VBProject.Protection);
bool IsDestinationProtected = Convert.ToBoolean(wDestination.VBProject.Protection);
if (IsSourceProtected)
{
if (IsDestinationProtected)
{
KeySendPassword pasike = new KeySendPassword();
pasike.Klucze();
}
else
{
KeySendPassword pasik = new KeySendPassword();
pasik.KluczS(ref app);
}
}
else
if (IsDestinationProtected)
{
KeySendPassword pasiks = new KeySendPassword();
pasiks.KluczD();
}
Boolean found;
found = false;
VBIDE.VBComponent dest;
foreach (VBIDE.VBComponent source in wSource.VBProject.VBComponents)
{
//Sprawdzamy czy nasz source ma jakis kod jezeli nie: koniec.
if (source.CodeModule.CountOfLines > 0)
{
//Sprawdzamy czy istnieje jakies makro w naszym destini pliku jezeli tak sprawdzamy i porwonujemy jego nazwe jezeli nie : dalej.
Hashtable pak = new Hashtable();
pak = GetMacros(wDestination);
if (pak.Count > 0)
{
//Sprawdzamy czy dany modul istnieje
//I czy jego nazwa jest taka sama jak z sourca jezeli tak to jest kasowana jezeli nie to zostaje.
foreach (VBIDE.VBComponent destNew in wDestination.VBProject.VBComponents)
{
if (destNew.Name == source.CodeModule.Name)
{
wDestination.VBProject.VBComponents.Remove(destNew);
found = false;
//Usuwamy ten sam module
}
}
}
if (found == false)
{
dest = wDestination.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
dest.CodeModule.AddFromString(source.CodeModule.get_Lines(1, source.CodeModule.CountOfLines));
dest.Name = source.Name;
wDestination.Save();
Marshal.FinalReleaseComObject(dest);
Marshal.FinalReleaseComObject(source);
dest = null;
}
}
}
wSource.Close();
wDestination.Close();
Marshal.FinalReleaseComObject(wSource);
Marshal.FinalReleaseComObject(wDestination);
app.Quit();
}
/// <summary>
/// 执行Excel中的宏
/// </summary>
/// <param name="excelFilePath">Excel文件路径</param>
/// <param name="macroName">宏名称</param>
public static void RunExcelMacro(string excelFilePath, string macroName)
{
try
{
#region 检查输入参数
//检查文件是否存在
if (!File.Exists(excelFilePath))
{
LogUtil.LogMessage(excelFilePath + " 文件不存在");
//return;
}
// 检查是否输入宏名称
if (string.IsNullOrEmpty(macroName))
{
LogUtil.LogMessage("请输入宏的名称");
//return;
}
#endregion
#region 调用宏处理
// 准备打开Excel文件时的缺省参数对象
object oMissing = System.Reflection.Missing.Value;
// 创建Excel对象示例
//Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
Excel.Application oExcel = new Excel.Application();
oExcel.Visible = false;
// 创建Workbooks对象
Excel.Workbooks oBooks = oExcel.Workbooks;
// 创建Workbook对象
Excel._Workbook oBook = null;
//System.AppDomain.CurrentDomain.BaseDirectory.ToString());
//System.Console.WriteLine(System.Environment.CurrentDirectory.ToString());
string appPath = System.Environment.CurrentDirectory;
excelFilePath = appPath + "\\" + excelFilePath;
LogUtil.LogMessage("执行Excel文件:" + excelFilePath);
// 打开指定的Excel文件
oBook = oBooks.Open(excelFilePath,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
object[] paraObjects = new object[] { macroName };
object rtnValue;
LogUtil.LogMessage("***********运行宏命令!");
// 执行Excel中的宏
rtnValue = RunMacro(oExcel, paraObjects);
LogUtil.LogMessage("***********结束宏命令!");
// 保存更改
oBook.Save();
// 退出Workbook
oBook.Close(false, oMissing, oMissing);
#endregion
#region 释放对象
// 释放Workbook对象
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
oBook = null;
// 释放Workbooks对象
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
oBooks = null;
// 关闭Excel
oExcel.Quit();
// 释放Excel对象
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
oExcel = null;
// 调用垃圾回收
GC.Collect();
#endregion
}
catch (Exception exp)
{
LogUtil.LogMessage(exp.Message);
}
}
public void RunExcelMacro(string excelFileName, string macro, bool visible)
{
//### improve & cleanup code
// Define Workbooks
Excel2.Application oExcel = null;
Excel2.Workbooks oBooks = null;
Excel2._Workbook oBook = null;
object oMissing = System.Reflection.Missing.Value;
try
{
//### test thread culture
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("fr-FR");
FileInfo fi = new FileInfo(excelFileName);
// Create an instance of Microsoft Excel
//Excel2.ApplicationClass oExcel = new Excel2.ApplicationClass();
oExcel = new Excel2.Application();
oExcel.Visible = visible;
oExcel.DisplayAlerts = true;
oBooks = oExcel.Workbooks;
//Open the file, using the 'path' variable
oBook = oBooks.Open(excelFileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
//### activate
//Object[] oRunArgs = { "'" + fi.Name + "'!FULL.FULL" };
Object[] oRunArgs = { "'" + fi.Name + "'!" + macro };
oExcel.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null,
oExcel, oRunArgs);
//oRunArgs = new object[] { fi.Name + "!exportFile" };
//oExcel.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null,
// oExcel, oRunArgs);
//save excel file
oBook.Save();
// Quit Excel and clean up.
oBook.Close(false, oMissing, oMissing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
oBook = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
oBooks = null;
oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
oExcel = null;
//Garbage collection
GC.Collect();
}
catch (Exception ex)
{
if (oBook != null)
{
try
{
oBook.Close(false, oMissing, oMissing);
}
catch (Exception ex2) { }
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
oBook = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
oBooks = null;
}
if (oExcel != null)
{
try
{
oExcel.Quit();
}
catch (Exception ex2) { }
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
oExcel = null;
}
log.Error("Error :: RunExcelMacro : " + ex.Message);
throw ex;
}
}
public void CrearExcel(System.Data.DataTable dt, string path)
{
object misvalue = System.Reflection.Missing.Value;
try
{
oXL = new Microsoft.Office.Interop.Excel.Application();
bool codescreenUpdateState = oXL.ScreenUpdating;
bool statusBarState = oXL.DisplayStatusBar;
bool eventsState = oXL.EnableEvents;
string pathDefinitivo = path + ".xlsx";
oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));
oWB.SaveCopyAs(pathDefinitivo);
//oWB.SaveAs(pathDefinitivo, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
// false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB = oXL.Workbooks.Open(pathDefinitivo, 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);
oXL.Calculation = XlCalculation.xlCalculationManual;
oXL.ScreenUpdating = false;
oXL.DisplayStatusBar = false;
oXL.Calculation = XlCalculation.xlCalculationManual;
oXL.EnableEvents = false;
oXL.Visible = false;
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;
//Cargar header
oSheet.Cells[1, 1] = "Empresa: ";
oSheet.Cells[1, 2] = "SURFACTAN S.A.";
oSheet.Cells[1, 11] = "Fecha: ";
oSheet.Cells[1, 12] = DateTime.Now.ToShortDateString();
oSheet.Cells[2, 7] = "Listado de Muestras";
oSheet.get_Range("L1", "L1").EntireColumn.NumberFormat = "mm/dd/yyyy";
oSheet.get_Range("A1", "L2").Font.Bold = true;
oSheet.get_Range("A1", "L1").Cells.Font.Size = 10;
oSheet.get_Range("A1", "L2").Cells.Font.Name = "Times New Roman";
oSheet.get_Range("G2", "G2").Cells.Font.Size = 16;
oSheet.get_Range("G2", "G2").HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
oSheet.get_Range("G2", "G2").Cells.Font.Size = 16;
oSheet.get_Range("A1", "L2").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("G2", "G2").Borders.LineStyle =
Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//Agrego los headers
oSheet.Cells[4, 1] = "Fecha";
oSheet.Cells[4, 2] = "Codigo";
oSheet.Cells[4, 3] = "Cantidad";
oSheet.Cells[4, 4] = "Nombre para el Cliente";
oSheet.Cells[4, 6] = "Cliente";
oSheet.Cells[4, 8] = "Observaciones";
oSheet.Cells[4, 10] = "Lote";
oSheet.Cells[4, 11] = "Cantidad";
oSheet.Cells[4, 12] = "Observaciones";
oSheet.get_Range("A4", "L4").Cells.Font.Size = 8;
oSheet.get_Range("A4", "L4").Cells.Font.Name = "Times New Roman";
oSheet.get_Range("A4", "L4").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A4", "L4").HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
int rango = 6;
for (int i = 0; i < dt.Rows.Count; i++)
{
string[,] valores = new string[dt.Rows.Count, 12];
DataRow dr = dt.Rows[i];
valores[0, 0] = dr["Fecha"].ToString();
valores[0, 1] = dr["Codigo"].ToString();
valores[0, 2] = dr["Cantidad"].ToString();
valores[0, 3] = dr["DescriCliente"].ToString();
valores[0, 4] = "";
valores[0, 5] = dr["Razon"].ToString();
valores[0, 6] = "";
valores[0, 7] = dr["Observaciones"].ToString();
valores[0, 8] = "";
valores[0, 9] = dr["Lote2"].ToString();
valores[0, 10] = dr["Cantidad2"].ToString();
valores[0, 11] = dr["Observaciones2"].ToString();
oSheet.get_Range("A" + rango.ToString(), "L" + rango.ToString()).Value2 = valores;
oSheet.get_Range("A" + rango.ToString(), "L" + rango.ToString()).Cells.Font.Size = 8;
oSheet.get_Range("A" + rango.ToString(), "L" + rango.ToString()).Cells.Font.Name = "Times New Roman";
rango += 1;
}
oXL.Visible = false;
oXL.UserControl = false;
oWB.Save();
oWB.Close();
oXL.ScreenUpdating = codescreenUpdateState;
oXL.DisplayStatusBar = statusBarState;
oXL.EnableEvents = eventsState;
MessageBox.Show("El archivo se genero con exito", "Generacion de Excel",
MessageBoxButtons.OK, MessageBoxIcon.None);
}
catch (Exception err)
{
MessageBox.Show(err.Message, "Error al generar archivo",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
