private void loadDay(String moneda, long fecha, Excel.Worksheet sh)
{
String[] headers = { "Moneda", "Epoch", "Fecha", "Open", "High", "Low", "Close" };
SrvCierres srvCierre = new SrvCierres();
Excel.Range rng = sh.Cells;
rng.ClearContents();
int row = 1;
for (int idx = 0; idx < headers.Length; idx++)
{
sh.Cells[row, idx + 1] = headers[idx];
}
row++;
foreach (Row r in srvCierre.getData(moneda, fecha))
{
int col = 1;
sh.Cells[row, col++] = r.get("Moneda");
sh.Cells[row, col++] = r.get("Epoch");
sh.Cells[row, col++] = r.get("Fecha");
sh.Cells[row, col++] = r.get("FOpen");
sh.Cells[row, col++] = r.get("FHigh");
sh.Cells[row, col++] = r.get("FLow");
sh.Cells[row, col++] = r.get("FClose");
row++;
}
}
private void ExcelApplication_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
{
Excel.Sheets excelSheets = Wb.Worksheets;
Excel.Worksheet excelWorksheet = HelperUtil.GetSheetNameFromGroupOfSheets(GlobalMembers.InstanceGlobalMembers.MetaDataSheetName, excelSheets);
Excel.Range excelRange = excelWorksheet.UsedRange;
excelRange.ClearContents();
excelRange.ClearFormats();
excelRange.ClearComments();
}
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(Microsoft.Office.Interop.Excel.Worksheet workSheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
Microsoft.Office.Interop.Excel.Range range = workSheet.Range[workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]];
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value2 = text;
range.WrapText = true;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value2 = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
private void BtnImport_Click(object sender, RibbonControlEventArgs e)
{
Excel._Worksheet worksheet = Globals.ThisAddIn.excelApplication.ActiveSheet;
Excel.Range oRange = worksheet.UsedRange;
oRange.ClearContents();
string moduleId = ExcelHelper.GetObjectIdFromText(dynamicMenuModules);
IHttpRequest httpRequest = GlobalMembers.InstanceGlobalMembers.Container.Resolve <IHttpRequest>();
ITranslationService _translationService = GlobalMembers.InstanceGlobalMembers.Container.Resolve <ITranslationService>(new TypedParameter(typeof(IHttpRequest), httpRequest));
TranslationController translationController = new TranslationController(_translationService);
ExcelHelper.PopulateTranslationFields(worksheet, moduleId, translationController);
}
internal void UpdateWSheet()
{
object[,] objData = new object[rowList.Count, 5];
Excel.Range tableIn = xlWsheet.Range["A2:E" + (rowList.Count + 1).ToString()];
Debug.WriteLine("output range = " + "A2:E" + (rowList.Count + 1).ToString());
for (int i = 0; i < rowList.Count; i++)
{
objData[i, 0] = rowList[i].mMfr; objData[i, 1] = rowList[i].mSerNo; objData[i, 2] = rowList[i].mOsPK; objData[i, 3] = rowList[i].mStatus; objData[i, 4] = rowList[i].mRsult;
}
tableIn.ClearContents();
tableIn.Value2 = objData;
userMessageCell.Font.Color = userMessageCell.Offset[0, 1].Font.Color;
userMessageCell.Font.Italic = false;
userMessageCell.Value2 = "Current Status";
}
/// <summary>
///
/// </summary>
/// <param name="colPaste"></param>
private void PasteTitleOffer11(int colPaste)
{
//int rowBottomTotal = ExcelHelper.FindCell(SheetUrv11, "ОБЩАЯ СУММА РАСХОДОВ (без НДС)").Row;
int rowBottomTotal = ExcelHelper.FindCell(SheetUrv12, "НДС, 20%").Row - 1;
Excel.Range rngTitle = SheetUrv11.Range["I10:K12"];
rngTitle.Copy();
SheetUrv11.Cells[10, colPaste].PasteSpecial(Excel.XlPasteType.xlPasteAll);
rngTitle = SheetUrv11.Range[$"I{_rowStart}:K{rowBottomTotal + 6}"];
rngTitle.Copy();
SheetUrv11.Cells[_rowStart, colPaste].PasteSpecial(Excel.XlPasteType.xlPasteAll);
Excel.Range rng = SheetUrv11.Range[SheetUrv11.Cells[_rowStart, colPaste],
SheetUrv11.Cells[rowBottomTotal - 1, colPaste + 2]];
rng.ClearContents();
}
private void ClearDataRng11()
{
int lastRow = GetLastRowUrv11();
if (lastRow <= 14)
{
return;
}
int lastColumn = SheetUrv11.UsedRange.Column + SheetUrv11.UsedRange.Columns.Count - 1;
Excel.Range dataRng = SheetUrv11.Range[SheetUrv11.Cells[14, 2], SheetUrv11.Cells[lastRow, lastColumn]];
dataRng.EntireRow.Delete();
dataRng = SheetUrv11.Range[SheetUrv11.Cells[_rowStart, 2], SheetUrv11.Cells[_rowStart, lastColumn]];
dataRng.ClearContents();
return;
}
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(Microsoft.Office.Interop.Excel.Worksheet workSheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
Microsoft.Office.Interop.Excel.Range range = workSheet.Range[workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]];
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value2 = text;
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
range.Interior.ColorIndex = 46;
range.Interior.Pattern = -4105;
range.Font.Bold = true;
range.Font.Size = 24;
range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
}
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value2 = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
/// <summary>
/// Clears formulas and values from the range.
/// </summary>
/// <param name="range">The Excel range.</param>
/// <param name="attempts">The number of attempts.</param>
/// <param name="intervalMS">The interval between attempts. [ms].</param>
public static void RangeClearContents(Excel.Range range, int attempts = 10000, int intervalMS = 100)
{
Retry.DoTrue(() => TryBool(() => range.ClearContents()), new TimeSpan(intervalMS), attempts);
}
/// <summary>
/// 合并单元格，并赋值，对每个WorkSheet操作
/// </summary>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
for (int i = 1; i <= this.WorkSheetCount; i++)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
}
/// <summary>
/// 結合されてない個人シフトを作成します
/// </summary>
/// 詳しいことはJobToShiftにかいてあります
public static void Run(Excel.Workbook book)
{
book.Application.ScreenUpdating = true;
MainForm._MainFormInstance.inProrgamUse = true;
int Rows = 0, Columns = 0; //Rowがy座標
Excel.Worksheet jobsheet; // 操作中のアプリケーション
Excel.Worksheet idvsheet;
Excel.Sheets sheets;
sheets = book.Worksheets;
jobsheet = (Excel.Worksheet)sheets.get_Item(sheets.getSheetIndex("仕事シフト"));
idvsheet = (Excel.Worksheet)sheets.get_Item(sheets.getSheetIndex("MySheet"));
Excel.Range current = idvsheet.Cells[1, 1]; //セル単体です
Excel.Range allJobRange = jobsheet.Cells[MainForm._MainFormInstance.startaddr_row, MainForm._MainFormInstance.startaddr_col];
allJobRange = allJobRange.get_Resize(MainForm._MainFormInstance.jobtype + 10, 90 + 10);
Excel.Range allIdvRange = idvsheet.Cells[1, 1];
allIdvRange = allIdvRange.get_Resize(MainForm._MainFormInstance.jobtype + 10, 90 + MainForm._MainFormInstance.startaddr_col + 1);
allIdvRange.Interior.ColorIndex = 2;
Excel.Range clear_cell = idvsheet.Cells[3, 5];
clear_cell = clear_cell.get_Resize(230, 90);
clear_cell.ClearContents();
//allIdvRange.ClearContents();
//allIdvRange.UnMerge();
string[,] allString = allJobRange.DeepToString(); //仕事シフトを入れる
string[,] allIdvString = allIdvRange.DeepToString(); //個人シフトを入れる
Excel.Range JobRange = jobsheet.Cells[MainForm._MainFormInstance.startaddr_row, MainForm._MainFormInstance.startaddr_col - 1];
JobRange = JobRange.get_Resize(MainForm._MainFormInstance.jobtype, 1);
string[,] jobString = JobRange.DeepToString(); //仕事名を入れる
for (Rows = 0; Rows < MainForm._MainFormInstance.jobtype; Rows++)
{
for (Columns = 0; Columns < MainForm._MainFormInstance.startaddr_col + 90; Columns++)
{
if (allString[Rows, Columns] == null || allString[Rows, Columns] == "")
{
continue;
}
else
{
for (int tmp = 0; tmp < MainForm._MainFormInstance.jobtype; tmp++)
{
if (allIdvString[tmp, 3] == null || allIdvString[tmp, 3] == "")
{
}
else if (allString[Rows, Columns] == allIdvString[tmp, 3])
{
//allIdvString[tmp, Columns + 4] = jobString[Rows, 0];
//以下緊急対応です，配列外参照の可能性を消しています
//要確認
if (Columns + 4 > 93)
{
break;
}
allIdvString[tmp, Columns + 4] = jobString[Rows, 0];
break;
}
//以下緊急対応です
if (tmp == 500)
{
MessageBox.Show(jobString[Rows, 0] + "in" + allString[Rows, Columns] + " is not entered by error");
}
}
}
}
}
allIdvRange.set_Value(Type.Missing, allIdvString);
/*
* book.Application.DisplayAlerts = false;
* for (Rows = 1; Rows < 500; Rows++)
* {
* for (Columns = 1; Columns < 100; Columns++)
* {
* cellCount = 1;
* value = allIdvString[Rows - 1, Columns - 1];
* if (value == null || value == "")
* continue;
* while (value == allIdvString[Rows - 1, Columns])
* {
* cellCount++;
* Columns++;
* }
*
* wholeRange = idvsheet.Cells[Rows, Columns - cellCount + 1];
* wholeRange = wholeRange.get_Resize(1, cellCount);
* wholeRange.Merge();
* wholeRange.Interior.ColorIndex = 35;
* wholeRange.BorderAround2();
* }
* }
*
*
*
*
* book.Application.DisplayAlerts = true;
*/
//
book.Application.ScreenUpdating = true;
MainForm._MainFormInstance.inProrgamUse = false;
}
//Exports a DataView to Excel. The following steps are carried out
//in order to export the DataView to Excel
//Create Excel Objects
//Create Column & Row Workbook Cell Rendering Styles
//Fill Worksheet With DataView
//Add Auto Shapes To Excel Worksheet
//Select All Used Cells
//Create Headers/Footers
//Set Status Finished
//Save workbook & Tidy up all objects
//@param dv : DataView to use
//@param path : The path to save/open the EXCEL file to/from
//@param sheetName : The target sheet within the EXCEL file
public void ExportToExcel(List <DataView> dvList, string path, string sheetName)
{
try
{
//Assign Instance Fields
this.dvList = dvList;
#region NEW EXCEL DOCUMENT : Create Excel Objects
//create new EXCEL application
EXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
//index to hold location of the requested sheetName in the workbook sheets
//collection
int indexOfsheetName;
#region FILE EXISTS
//Does the file exist for the given path
if (File.Exists(path))
{
//Yes file exists, so open the file
workbook = EXL.Workbooks.Open(path,
0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
//get the workbook sheets collection
sheets = workbook.Sheets;
//set the location of the requested sheetName to -1, need to find where
//it is. It may not actually exist
indexOfsheetName = -1;
//loop through the sheets collection
for (int i = 1; i <= sheets.Count; i++)
{
//get the current worksheet at index (i)
worksheet = (Worksheet)sheets.get_Item(i);
//is the current worksheet the sheetName that was requested
if (worksheet.Name.ToString().Equals(sheetName))
{
//yes it is, so store its index
indexOfsheetName = i;
//Select all cells, and clear the contents
Microsoft.Office.Interop.Excel.Range myAllRange = worksheet.Cells;
myAllRange.Select();
myAllRange.CurrentRegion.Select();
myAllRange.ClearContents();
}
}
//At this point it is known that the sheetName that was requested
//does not exist within the found file, so create a new sheet within the
//sheets collection
if (indexOfsheetName == -1)
{
//Create a new sheet for the requested sheet
Worksheet sh = (Worksheet)workbook.Sheets.Add(
Type.Missing, (Worksheet)sheets.get_Item(sheets.Count),
Type.Missing, Type.Missing);
//Change its name to that requested
sh.Name = sheetName;
}
}
#endregion
#region FILE DOESNT EXIST
//No the file DOES NOT exist, so create a new file
else
{
//Add a new workbook to the file
workbook = EXL.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
//get the workbook sheets collection
sheets = workbook.Sheets;
//get the new sheet
worksheet = (Worksheet)sheets.get_Item(1);
//Change its name to that requested
worksheet.Name = sheetName;
}
#endregion
#region get correct worksheet index for requested sheetName
//get the workbook sheets collection
sheets = workbook.Sheets;
//set the location of the requested sheetName to -1, need to find where
//it is. It will definately exist now as it has just been added
indexOfsheetName = -1;
//loop through the sheets collection
for (int i = 1; i <= sheets.Count; i++)
{
//get the current worksheet at index (i)
worksheet = (Worksheet)sheets.get_Item(i);
//is the current worksheet the sheetName that was requested
if (worksheet.Name.ToString().Equals(sheetName))
{
//yes it is, so store its index
indexOfsheetName = i;
}
}
//set the worksheet that the DataView should write to, to the known index of the
//requested sheet
worksheet = (Worksheet)sheets.get_Item(indexOfsheetName);
#endregion
#endregion
// Set styles 1st
SetUpStyles();
//Fill EXCEL worksheet with DataView values
FillWorksheet_WithDataView();
////Add the autoshapes to EXCEL
//AddAutoShapesToExcel();
//Select all used cells within current worksheet
SelectAllUsedCells();
#region Finish and Release
try
{
NAR(sheets);
NAR(worksheet);
workbook.Close(true, path, Type.Missing);
NAR(workbook);
EXL.UserControl = false;
EXL.Quit();
NAR(EXL);
//kill the EXCEL process as a safety measure
KillExcel();
// Show that processing is finished
ProgressEventArgs pe = new ProgressEventArgs(100);
OnProgressChange(pe);
//MessageBox.Show("Finished adding dataview to Excel", "Info", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (COMException cex)
{
MessageBox.Show("User closed Excel manually, so we don't have to do that");
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
#endregion
}
catch (Exception ex)
{
MessageBox.Show("Error " + ex.Message);
}
}
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="sheetIndex">WorkSheet索引</param>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
public void MergeCells(int sheetIndex, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
if (sheetIndex > this.WorkSheetCount)
{
this.KillExcelProcess();
throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！");
}
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex);
range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}
async Task HandleOptionChain(Task <List <object> > task)
{
List <object> options = await task;
if (options.Count > 0)
{
lock (excelLock)
{
string symbol = (options[0] as OptionStrike)?.Underlyer;
info("Locked Excel. Handling options quote symbol " + symbol);
try
{
Excel._Worksheet symbolSheet = (Excel._Worksheet)GetWorkBook().Sheets[symbol];
Excel.Range xlRange = symbolSheet.UsedRange;
xlRange.ClearContents();
object[,] data = new object[options.Count + 1, 18];
int row = 0;
data[row, 0] = "Symbol";
data[row, 1] = "Type";
data[row, 3] = "ExpirationDate";
data[row, 5] = "StrikePrice";
data[row, 9] = "Bid";
data[row, 10] = "Ask";
data[row, 12] = "Delta";
data[row, 13] = "Gamma";
data[row, 14] = "Theta";
data[row, 15] = "Vega";
data[row, 16] = "Rho";
data[row, 17] = "ImpliedVolatitily";
row++;
foreach (OptionStrike call in options)
{
if (!call.IsCall)
{
continue;
}
data[row, 0] = call.Symbol;
data[row, 1] = call.GetType().Name;
data[row, 3] = call.ExpirationDate.ToString("yyyy-MM-dd");
data[row, 5] = call.StrikePrice;
data[row, 9] = call.Bid;
data[row, 10] = call.Ask;
// data[row, 11] = option.ExpirationType;
data[row, 12] = call.Delta;
data[row, 13] = call.Gamma;
data[row, 14] = call.Theta;
data[row, 15] = call.Vega;
data[row, 16] = call.Rho;
data[row, 17] = call.ImpliedVolatitily;
row++;
}
xlRange = GetExcel().Range[symbolSheet.Cells[1, 1], symbolSheet.Cells[data.GetLength(0), data.GetLength(1)]];
xlRange.Value = data;
(GetMainWorkSheet().Cells[25, "C"] as Excel.Range).Value = DateTime.Today;
(GetMainWorkSheet().Cells[GetSymbolRow(symbol), "C"] as Excel.Range).Font.Color = ColorTranslator.ToOle(Color.Black);
}
catch (Exception ex)
{
error("Unable to handle option chain", ex);
}
}
info("Unlocked Excel. Done handling options quote");
}
}
private void ReceivedMessage(AMPS.Client.Message msg)
{
if (!_running)
{
return;
}
/*try
* {*/
string sowKey = msg.getSowKey();
// if an OOF, remove the row if we know of it
if (msg.Command == AMPS.Client.Message.Commands.OOF && _rows.ContainsKey(sowKey))
{
int theRow = _rows[sowKey];
vbaInvoke(() =>
{
Excel.Range range = _worksheet.Rows[theRow];
range.ClearContents();
_worksheet.Cells[theRow, _col] = "(deleted)";
}
);
_rows.Remove(sowKey);
_empty.Enqueue(theRow);
}
else if (msg.Command == AMPS.Client.Message.Commands.Publish || msg.Command == AMPS.Client.Message.Commands.DeltaPublish ||
msg.Command == AMPS.Client.Message.Commands.SOW)
{
int thisRow = 0;
// try and find the row
if (!_rows.TryGetValue(sowKey, out thisRow))
{
string findKey = "|" + sowKey + "|";
vbaInvoke(() =>
{
if (_empty.Count > 0)
{
thisRow = _empty.Dequeue();
}
else
{
thisRow = _lastrow++;
}
_rows[sowKey] = thisRow;
_worksheet.Cells[thisRow, _col].Value = findKey;
});
if (thisRow == -1)
{
return;
}
}
// row == the row we want
_shredded.Clear();
var dataField = msg.getDataRaw();
_messageType.PopulateDictionary(dataField.buffer, dataField.position, dataField.length, _shredded);
foreach (var x in _shredded.Keys)
{
int thisCol = 0;
if (!_columns.ContainsKey(x))
{
// find a spot for a new column. hopefully this happens rarely.
for (int i = _col + 1; i < 1024768; i++)
{
vbaInvoke(() =>
{
dynamic c = _worksheet.Cells[_row, i];
if (c.Text == x)
{
thisCol = i;
_columns.Add(x, thisCol);
}
else if (c.Text == null || c.Text == "")
{
c.Value = x;
c.Font.Bold = true;
thisCol = i;
_columns.Add(x, thisCol);
}
});
if (thisCol != 0)
{
break;
}
}
if (thisCol == 0)
{
return; // no room
}
}
else
{
thisCol = _columns[x];
}
vbaInvoke(() => _worksheet.Cells[thisRow, thisCol].Value = _shredded[x].ToString());
}
}
/*}
* catch (Exception e)
* {
* this.close();
* _running = false;
* }*/
}
/// <summary>
/// 合并单元格，并赋值，对指定WorkSheet操作
/// </summary>
/// <param name="beginRowIndex">开始行索引</param>
/// <param name="beginColumnIndex">开始列索引</param>
/// <param name="endRowIndex">结束行索引</param>
/// <param name="endColumnIndex">结束列索引</param>
/// <param name="text">合并后Range的值</param>
private void MergeCells(Excel.Worksheet sheet, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text)
{
if (sheet == null)
return;
range = sheet.get_Range(sheet.Cells[beginRowIndex, beginColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]);
range.ClearContents(); //先把Range内容清除，合并才不会出错
range.MergeCells = true;
range.Value = text;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
}