private static void AwardExp(_Worksheet openSheet, List <string> invalidNameEntries, string gradeType, DateTime date, string timePeriod)
{
object[,] usernames = ExcelUtilities.Instance.GetUsernames(openSheet, gradeType);
string nameColumn = ExcelUtilities.Instance.GetNameColumn(gradeType);
int nameRowBeginning = ExcelUtilities.Instance.GetNameRowBeginning(gradeType);
string relevantDataColumn = ExcelUtilities.Instance.GetRelevantDataColumn(openSheet, gradeType, date);
for (int i = 0; i < usernames.GetLength(0); i++)
{
if (invalidNameEntries.IndexOf($"{usernames[i + 1, 1]}") == -1)
{
string username = openSheet.get_Range(string.Concat(nameColumn, nameRowBeginning + i), Missing.Value).Value;
switch (gradeType)
{
case "Mitarbeit":
string symbolGrade = openSheet.get_Range(string.Concat(relevantDataColumn, nameRowBeginning + i), Missing.Value).Value;
Selenium.Instance.AwardExp(username, symbolGrade);
break;
case "Schriftl. Leistung":
int expFlat = ((int)(openSheet.get_Range(string.Concat(relevantDataColumn, nameRowBeginning + i), Missing.Value).Value));
string expDescription = GetExpDescription(gradeType, timePeriod);
if (expFlat >= 0)
{
Selenium.Instance.AwardExp(username, expFlat.ToString(), expDescription);
}
break;
default:
break;
}
}
}
}
public static object WriteObjectArrayToExcel(object[,] oData, _Worksheet oSheet, object oStartingCell, bool bHeaderRow, bool bBorder)
{
int nRow = oData.GetLength(0);
int nCol = oData.GetLength(1);
Range oRange = oSheet.get_Range(oStartingCell, GetCellAtOffset(oStartingCell, nRow - 1, nCol - 1));
oRange.Value2 = oData;
// Sets interior and then exterior borders and line styles.
if (bBorder)
{
oRange.Borders.LineStyle = XlLineStyle.xlContinuous;
oRange.Borders.Weight = XlBorderWeight.xlThin;
oRange.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
}
if (bHeaderRow)
{
//Get Range for head column.
oRange = oSheet.get_Range(oStartingCell, GetCellAtOffset(oStartingCell, 0, nCol - 1));
oRange.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
}
return(GetCellAtOffset(oStartingCell, nRow - 1, nCol - 1));
}
/// <summary>
/// Writes the data.
/// </summary>
/// <typeparam name="T">The type of the items in the enumerable.</typeparam>
/// <param name="source">The enumerable.</param>
/// <param name="optionalValue">The optional value.</param>
/// <param name="strHeaderStart">The string header start.</param>
/// <param name="strDataStart">The string data start.</param>
/// <param name="sheet">The worksheet.</param>
/// <param name="objHeaders">The object headers.</param>
/// <returns>The Excel range.</returns>
private static Range WriteData <T>(IList <T> source, object optionalValue, string strHeaderStart, string strDataStart, _Worksheet sheet, Dictionary <string, string> objHeaders)
{
Range range;
var count = source.Count;
var objData = new object[count, objHeaders.Count];
for (var j = 0; j < count; j++)
{
var item = source[j];
var col = 0;
foreach (var entry in objHeaders)
{
var row = typeof(T).InvokeMember(entry.Key, BindingFlags.GetProperty, null, item, null);
objData[j, col++] = (row == null) ? string.Empty : row.ToString();
}
}
range = sheet.get_Range(strDataStart, optionalValue);
range = range.get_Resize(count, objHeaders.Count);
range.set_Value(optionalValue, objData);
range.BorderAround(Type.Missing, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Type.Missing);
range = sheet.get_Range(strHeaderStart, optionalValue);
range = range.get_Resize(count + 1, objHeaders.Count);
range.Columns.AutoFit();
return(range);
}
public override Range GetRangeForMerge(_Worksheet sheet, int rowCount)
{
if (rowCount != 4)
{
return(sheet.get_Range("A" + rowCount, "B" + rowCount));
}
return(sheet.get_Range("A" + rowCount, "F" + rowCount));
}
private void AddExcelRows(string startRange, int rowCount, int colCount, object values)
{
string first = ((char)(Encoding.ASCII.GetBytes(startRange.Substring(0, 1))[0] + colCount - 1)).ToString();
string last = (Int32.Parse(startRange.Substring(1, 1)) + rowCount - 1).ToString();
_range = _sheet.get_Range(startRange, string.Concat(first, last));
_range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
_range.Value2 = values;
}
///////////////////////////////////////////////////////////////////////////
//
// METHODS
//
///////////////////////////////////////////////////////////////////////////
#region METHODS
/// <summary>
/// 지정된 영역에 설정된 유효성 검사 규칙을 삭제합니다.
/// </summary>
/// <param name="startAddress"></param>
/// <param name="endAddress"></param>
public void Delete(RangeAddress rangeAddress)
{
Validation validation = _xlSheet.get_Range(rangeAddress.From.Address, rangeAddress.To.Address).Validation;
if (validation != null)
{
validation.Delete();
Marshal.ReleaseComObject(validation);
}
}
private void meniItemExporttoExel_Click(object sender, EventArgs e)
{
try
{
Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application();
application.Workbooks.Add((object)Missing.Value);
_Worksheet worksheet = (_Worksheet)(application.Sheets.Add(System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing) as Worksheet);
int num1 = 1;
for (int index = 0; index < this.dgvResult.RowCount; ++index)
{
if (this.dgvResult.Rows[index].Cells[1].Value == null)
{
worksheet.Cells[(object)num1, (object)1] = this.dgvResult.Rows[index].Cells[0].Value;
Range range = worksheet.get_Range((object)("A" + (object)num1), (object)("B" + (object)num1));
range.Merge(System.Type.Missing);
range.Font.Bold = (object)true;
++num1;
worksheet.Cells[(object)num1, (object)1] = (object)"Наименование";
worksheet.Cells[(object)num1, (object)2] = (object)"Значение";
}
else
{
string str = this.dgvResult.Rows[index].Cells[1].Value.ToString();
worksheet.Cells[(object)num1, (object)1] = this.dgvResult.Rows[index].Cells[0].Value;
if (str.IndexOf(',') > 0 && str.IndexOf(',') + 3 < str.Length)
{
worksheet.Cells[(object)num1, (object)2] = str.Substring(0, str.IndexOf(',') + 3);
}
else
{
worksheet.Cells[(object)num1, (object)2] = str;
}
++num1;
}
}
int num2 = num1 - 1;
Range range1 = worksheet.get_Range((object)"A1", (object)("B" + (object)num2));
range1.WrapText = (object)true;
range1.Font.Size = (object)14;
range1.VerticalAlignment = (object)XlVAlign.xlVAlignCenter;
range1.HorizontalAlignment = (object)XlHAlign.xlHAlignCenter;
range1.Borders.Weight = (object)2;
range1.Font.Name = (object)"Times New Roman";
range1.ColumnWidth = (object)35;
range1.RowHeight = (object)18;
application.Visible = true;
application.UserControl = true;
}
catch (Exception ex)
{
int num = (int)MessageBox.Show(ex.ToString());
}
}
public void readExcel()
{
int count = 1;
string raws = "A" + count;
myRange = mySheet.get_Range(raws);
Array.Resize(ref question, 1);
while (Convert.ToString(myRange.Value) != null)
{
label1.Text = Convert.ToString(question.Length);//載入題數
raws = "A" + count;
myRange = mySheet.get_Range(raws);
//string q = Convert.ToString(myRange.Value);由於50%會變成0.5 故改用myRange.Text
string q = myRange.Text;
raws = "B" + count;
myRange = mySheet.get_Range(raws);
//string a = Convert.ToString(myRange.Value);
string a = myRange.Text;
raws = "C" + count;
myRange = mySheet.get_Range(raws);
//string b = Convert.ToString(myRange.Value);
string b = myRange.Text;
raws = "D" + count;
myRange = mySheet.get_Range(raws);
//string c = Convert.ToString(myRange.Value);
string c = myRange.Text;
raws = "E" + count;
myRange = mySheet.get_Range(raws);
//string d = Convert.ToString(myRange.Value);
string d = myRange.Text;
raws = "F" + count;
myRange = mySheet.get_Range(raws);
//string ans = Convert.ToString(myRange.Value);
string ans = myRange.Text;
question[count - 1] = new MyExcelCollection(q, a, b, c, d, ans);
Array.Resize(ref question, question.Length + 1);
raws = "A" + ++count;
myRange = mySheet.get_Range(raws);
//label5.Text = Convert.ToString(count - 1);
}
Array.Resize(ref question, question.Length - 1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
myBook = null;
mySheet = null;
myRange = null;
myExcel = null;
GC.Collect();
}
public WorkbookExcel(IEnumerable <Order_Detail> data, string path)
{
this.path = path;
this.data = data;
oXL = new Application();
oXL.Visible = false;
oWB = oXL.Workbooks.Add("");
oSheet = (_Worksheet)oWB.ActiveSheet;
oSheet.Cells[1, 1] = "Номер заказа";
oSheet.Cells[1, 2] = "Дата заказа";
oSheet.Cells[1, 3] = "Артикул товара";
oSheet.Cells[1, 4] = "Название товара";
oSheet.Cells[1, 5] = "Количество реализованных единиц товара";
oSheet.Cells[1, 6] = "Цена реализации за единицу продукции";
oSheet.Cells[1, 7] = "Скидка,%";
oSheet.Cells[1, 8] = "Итоговая цена";
oSheet.get_Range("A1", "H1").Font.Bold = true;
oSheet.get_Range("A1", "H1").VerticalAlignment = XlVAlign.xlVAlignCenter;
oSheet.get_Range("A1", "H1").HorizontalAlignment = XlVAlign.xlVAlignCenter;
int row = 2;
foreach (var o in order_Detail)
{
oSheet.Cells[row, 1] = o.OrderID;
oSheet.Cells[row, 2] = o.Order.OrderDate;
oSheet.Cells[row, 3] = o.ProductID;
oSheet.Cells[row, 4] = o.Product.ProductName;
oSheet.Cells[row, 5] = o.Quantity;
oSheet.Cells[row, 6] = o.UnitPrice;
oSheet.Cells[row, 7] = o.Discount;
oSheet.Cells[row, 8].Formula = o.Discount == 0 ? $"=E{row}*F{row}" : $"=(E{row}*F{row})-(E{row}*F{row}*G{row})";
row++;
}
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
oXL.UserControl = false;
oWB.SaveAs(path, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close();
}
private void ExportToExcel(DataGridView oDgv, string filename)
{
int num2;
int num = 0;
object missing = System.Type.Missing;
string[] strArray = new string[oDgv.ColumnCount];
string[] strArray2 = new string[oDgv.ColumnCount];
for (num2 = 0; num2 < oDgv.ColumnCount; num2++)
{
strArray[num2] = oDgv.Rows[0].Cells[num2].OwningColumn.HeaderText.ToString();
strArray[num2] = oDgv.Rows[0].Cells[num2].OwningColumn.HeaderText.ToString();
num = num2 + 0x41;
strArray2[num2] = Convert.ToString((char)num);
}
try
{
Microsoft.Office.Interop.Excel.Application application = (Microsoft.Office.Interop.Excel.Application)Activator.CreateInstance(System.Type.GetTypeFromCLSID(new Guid("00024500-0000-0000-C000-000000000046")));
_Workbook workbook = application.Workbooks.Add(Missing.Value);
Sheets worksheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet)worksheets.get_Item(1);
for (num2 = 0; num2 < oDgv.ColumnCount; num2++)
{
worksheet.get_Range(strArray2[num2] + "1", Missing.Value).set_Value(Missing.Value, strArray[num2]);
}
strArray2 = new string[oDgv.ColumnCount];
for (int i = 0; i < (oDgv.RowCount - 1); i++)
{
if (!string.IsNullOrEmpty(oDgv.Rows[i].Cells[0].Value.ToString()))
{
for (int j = 0; j < oDgv.ColumnCount; j++)
{
num = j + 0x41;
strArray2[j] = Convert.ToString((char)num);
worksheet.get_Range(strArray2[j] + Convert.ToString((int)(i + 2)), Missing.Value).set_Value(Missing.Value, oDgv.Rows[i].Cells[j].Value.ToString());
}
}
}
application.Visible = false;
application.UserControl = false;
workbook.SaveAs(filename, XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
workbook.Close(false, missing, missing);
Cursor.Current = Cursors.Default;
MessageBox.Show("저장이 완료되었습니다.");
}
catch (Exception exception)
{
MessageBox.Show(("Error: " + exception.Message) + " Line: " + exception.Source, "Error");
}
}
private static object[,] GetKsgData()
{
try
{
_ows = (_Worksheet)_oxb.Sheets["КСГ"];
}
catch
{
throw new Exception("Лист с именем 'КСГ' не найден. Вероятно, указанный файл имеет неверный формат.");
}
int rowCnt = 2;
int shag = 10000;
while (shag > 0)
{
while (((Range)_ows.Cells[rowCnt, 1]).Value2 != null)
{
rowCnt += shag;
}
rowCnt -= shag;
shag /= 10;
}
var data = (object[, ])_ows.get_Range(_ows.Cells[2, 2], _ows.Cells[rowCnt, 3]).Value2;
return(data);
}
internal void MakeCostMatrix(_Worksheet worksheet)
{
Range range = null;
string cellName = null;
uint cost = 0;
foreach (TeleportPoint leftPoint in leftPoints.Values)
{
foreach (TeleportPoint topPoint in topPoints.Values)
{
cellName = topPoint.TableColumnPos + leftPoint.TableRowPos.ToString();
range = worksheet.get_Range(cellName, Missing.Value);
if (range == null)
{
Console.WriteLine("ERROR: range == null, Cell = {0}", cellName);
}
if (range.Value2 != null)
{
cost = Convert.ToUInt32(range.Value2);
leftPoint.SetCost(topPoint.ID, cost);
}
}
}
}
public object[,] GetUsernames(_Worksheet openSheet, string gradeType)
{
string nameColumn = GetNameColumn(gradeType);
int nameRowBeginning = GetNameRowBeginning(gradeType);
return(openSheet.get_Range(string.Concat(nameColumn, nameRowBeginning), string.Concat(nameColumn, 3 + 50)).Cells.Value);
}
public void Automation_CellByCell()
{
//startimi i nje workbooki ne excel
m_objExcel = new Application();
m_objBooks = (Workbooks)m_objExcel.Workbooks;
m_objBook = (Workbook)(m_objBooks.Add(m_objOpt));
//vendosja e te dhenave ne sheetin e pare
m_objSheets = (Sheets)m_objBook.Worksheets;
m_objSheet = (_Worksheet)(m_objSheets.Item[1]);
m_objRange = m_objSheet.Range["A1", m_objOpt];
m_objRange.set_Value(m_objOpt, "Value A1");
m_objRange = m_objSheet.Range["B1", m_objOpt];
m_objRange.set_Value(m_objOpt, "Value B1");
m_objRange = m_objSheet.Range["A2", m_objOpt];
m_objRange.set_Value(m_objOpt, "Value A2");
m_objRange = m_objSheet.Range["B2", m_objOpt];
m_objRange.set_Value(m_objOpt, "Value B2");
//Bold i cellsave
m_objRange = m_objSheet.get_Range("A1", "B1");
m_objFont = m_objRange.Font;
m_objFont.Bold = true;
//save and quite excel
m_objBook.SaveAs(m_strSampleFolder + "Book1.xls", m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
}
public void SortRanges(CellDescriptor cd1Top, CellDescriptor cd1Bottom, int SortColumnIdx1, bool sortAscending1, int SortColumnIdx2, bool sortAscending2)
{
try
{
_Worksheet ws = (_Worksheet)(this.worksheet);
ws.Activate();
Range sortRange1 = ws.get_Range(GetCellName(cd1Top), GetCellName(cd1Bottom));
sortRange1.Sort
(sortRange1.Columns[SortColumnIdx1, _MISSING_], ((sortAscending1) ? XlSortOrder.xlAscending : XlSortOrder.xlDescending),
sortRange1.Columns[SortColumnIdx2, _MISSING_], _MISSING_, ((sortAscending2) ? XlSortOrder.xlAscending : XlSortOrder.xlDescending),
_MISSING_, XlSortOrder.xlAscending,
XlYesNoGuess.xlNo, _MISSING_, _MISSING_,
XlSortOrientation.xlSortColumns,
XlSortMethod.xlPinYin,
XlSortDataOption.xlSortNormal,
XlSortDataOption.xlSortNormal,
XlSortDataOption.xlSortNormal);
/*
* SortRange(cd1Top, cd1Bottom, SortColumnIdx1, sortAscending1);
* SortRange(cd1Top, cd1Bottom, SortColumnIdx2, sortAscending2);*/
}
catch (Exception e)
{
}
}
private static void RangeBuild(_Worksheet oSheet, Range startcell, Range endcell, string value)
{
///创建一个区域对象。第一个参数是开始格子号，第二个参数是终止格子号。比如选中A1——D3这个区域。
Range range = (Range)oSheet.get_Range(startcell, endcell);
///合并方法，0的时候直接合并为一个单元格
range.Merge(0);
///合并单元格之后，设置其中的文本
range.Value = value;
//横向居中
range.HorizontalAlignment = XlVAlign.xlVAlignCenter;
//自动调整列宽
range.EntireColumn.AutoFit();
///字体大小
//range.Font.Size = 18;
///字体
// range.Font.Name = "黑体";
///行高
// range.RowHeight = 24;
//填充颜色
// range.Interior.ColorIndex = 20;
//设置单元格边框的粗细
// range.Cells.Borders.LineStyle = 1;
}
internal void GetHorizontalIDs(_Worksheet worksheet)
{
string currentColumn = idRowStart;
string cellName = currentColumn + idRow.ToString();
Range range = null;
byte id;
for (int i = 0; i < pointsCount; i++)
{
range = worksheet.get_Range(cellName, Missing.Value);
if (range == null)
{
Console.WriteLine("ERROR: range == null, Cell = {0}", cellName);
}
id = Convert.ToByte(range.Value2);
TeleportPoint point = leftPoints[id].Clone();
point.TableColumnPos = currentColumn;
point.TableRowPos = idRow;
if (!topPoints.ContainsKey(id))
{
topPoints.Add(id, point);
}
else
{
topPoints[id] = point;
}
currentColumn = GetNextColumnName(currentColumn);
cellName = currentColumn + idRow.ToString();
}
}
public void Automation_QueryTable(string fileAndPath, string connStr, string sqlQuery)
{
if (File.Exists(fileAndPath))
{
File.Delete(fileAndPath);
}
connStr = connStr.Replace("User Id", "Uid").Replace("Password", "Pwd");
m_objExcel = new Application();
m_objBooks = m_objExcel.Workbooks;
m_objBook = m_objBooks.Add(m_objOpt);
m_objSheets = m_objBook.Worksheets;
m_objSheet = (_Worksheet)m_objSheets.Item[1];
m_objRange = m_objSheet.get_Range("A1", m_objOpt);
m_objQryTables = m_objSheet.QueryTables;
m_objQryTable = m_objQryTables.Add(@"ODBC;DRIVER=SQL Server;" + connStr + "APP=Microsoft Office 2013;WSID=KEDS-13136;",
m_objRange, sqlQuery);
m_objQryTable.RefreshStyle = XlCellInsertionMode.xlInsertEntireRows;
m_objQryTable.Refresh(false);
m_objQryTable.Delete();
m_objBook.SaveAs(fileAndPath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
}
/// <summary>
/// 为Excel的指定单元格赋值并设置格式
/// </summary>
/// <param name="DistressData_sheet"></param>
/// <param name="aaa"></param>
/// <param name="Highwayname"></param>
public static void Setcell(_Worksheet DistressData_sheet, string aaa, string Highwayname)
{
MsExcel.Range name_PC = DistressData_sheet.get_Range(aaa);
name_PC.Value = Highwayname;//写入公路名称
name_PC.NumberFormatLocal = "@";
name_PC.ColumnWidth = 20;
}
private void PlaceColorLegend(_Worksheet oSheet, int startingCol, int startingRow)
{
string strRange = Report.GetColumnLetter(startingCol + 2) + startingRow.ToString() + ":" + Report.GetColumnLetter(startingCol + 5) + startingRow.ToString();;
Range oR = oSheet.get_Range(strRange, Missing.Value);
oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightGray);
oR.MergeCells = true;
oR.Value2 = "Legend";
oR.Font.Size = 11;
oR.Font.Bold = true;
oR.HorizontalAlignment = XlHAlign.xlHAlignCenter;
oR.Borders.LineStyle = XlLineStyle.xlContinuous;
oR.Borders.Weight = XlBorderWeight.xlThin;
oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
strRange = Report.GetColumnLetter(startingCol + 2) + (startingRow + 1).ToString() + ":" + Report.GetColumnLetter(startingCol + 5) + (startingRow + 1).ToString();
oR = oSheet.get_Range(strRange, Missing.Value);
oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightBlue);
oR.MergeCells = true;
oR.Value2 = "Feasible treatment not funded/selected";
oR.Borders.LineStyle = XlLineStyle.xlContinuous;
oR.Borders.Weight = XlBorderWeight.xlThin;
oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
strRange = Report.GetColumnLetter(startingCol + 2) + (startingRow + 2).ToString() + ":" + Report.GetColumnLetter(startingCol + 5) + (startingRow + 2).ToString();
oR = oSheet.get_Range(strRange, Missing.Value);
oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightGreen);
oR.MergeCells = true;
oR.Value2 = "Treatment selected per RoadCare logic";
oR.Borders.LineStyle = XlLineStyle.xlContinuous;
oR.Borders.Weight = XlBorderWeight.xlThin;
oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
strRange = Report.GetColumnLetter(startingCol + 2) + (startingRow + 3).ToString() + ":" + Report.GetColumnLetter(startingCol + 5) + (startingRow + 3).ToString();
oR = oSheet.get_Range(strRange, Missing.Value);
oR.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightSalmon);
oR.MergeCells = true;
oR.Value2 = "Treatment selected as Committed Project";
oR.Borders.LineStyle = XlLineStyle.xlContinuous;
oR.Borders.Weight = XlBorderWeight.xlThin;
oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
//strRange = Report.GetColumnLetter(startingCol + 2) + startingRow.ToString() + ":" + Report.GetColumnLetter(startingCol + 5) + (startingRow + 3).ToString();
//oR.Borders.LineStyle = XlLineStyle.xlContinuous;
//oR.Borders.Weight = XlBorderWeight.xlThin;
//oR.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
}
public void OpenWorksheet()
{
Range oRng;
object misvalue = System.Reflection.Missing.Value;
// try
/// {
//Start Excel and get Application object.
oXL = new Application();
oXL.Visible = true;
//Get a new workbook.
oWB = (_Workbook)(oXL.Workbooks.Add(""));
oSheet = (_Worksheet)oWB.ActiveSheet;
oSheet.get_Range("A2", "AZ2").Font.Bold = true;
oSheet.get_Range("A2", "AZ2").HorizontalAlignment =
XlVAlign.xlVAlignCenter;
}
/// <param name="p_startChars">指定单元范围起始列字母及组合索引</param>
/// <param name="p_endChars">指定单元范围结束列字母及组合索引</param>
public Range GetRange(int p_startRowIndex, string p_startColChars, int p_endRowIndex, string p_endColChars)
{
//矩形 Range("D8:F11").Select
Range range;
range = worksheet.get_Range(p_startColChars + p_startRowIndex.ToString(), p_endColChars + p_endRowIndex.ToString());
return(range);
}
public static void FormatHeaders(Range oRng, DataRow dr, _Worksheet oSheet, string strPrefix, string strRange)
{
string strFontProperty, strFieldName;
oRng = oSheet.get_Range(strRange, Missing.Value);
strFieldName = strPrefix + "MergeCells";
strFontProperty = dr[strFieldName].ToString();
if (strFontProperty == "True")
{
oRng.MergeCells = true;
}
strFieldName = strPrefix + "FontColor";
strFontProperty = dr[strFieldName].ToString();
if (strFontProperty != "")
{
oRng.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.FromName(strFontProperty));
}
strFieldName = strPrefix + "InteriorColor";
strFontProperty = dr[strFieldName].ToString();
if (strFontProperty != "")
{
oRng.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.FromName(strFontProperty));
}
strFieldName = strPrefix + "FontSize";
strFontProperty = dr[strFieldName].ToString();
if (strFontProperty == "")
{
strFontProperty = "11"; //default font size
}
oRng.Font.Size = Convert.ToInt32(strFontProperty);
strFieldName = strPrefix + "FontBold";
strFontProperty = dr[strFieldName].ToString();
if (strFontProperty == "True")
{
oRng.Font.Bold = true;
}
strFieldName = strPrefix + "HAlign";
strFontProperty = dr[strFieldName].ToString();
if (strFontProperty == "C")
{
oRng.HorizontalAlignment = XlHAlign.xlHAlignCenter;
}
else if (strFontProperty == "R")
{
oRng.HorizontalAlignment = XlHAlign.xlHAlignRight;
}
else if (strFontProperty == "L")
{
oRng.HorizontalAlignment = XlHAlign.xlHAlignLeft;
}
}
private static void MakeHeder(int row, _Worksheet sheet, int height = 27)
{
var sheetRow = sheet.get_Range(sheet.Cells[row, 1], sheet.Cells[row, 1]).EntireRow;
sheetRow.HorizontalAlignment = XlHAlign.xlHAlignLeft;
sheetRow.VerticalAlignment = XlVAlign.xlVAlignTop;
sheetRow.WrapText = true;
sheetRow.RowHeight = height;
}
/// <summary>
/// handle tricare report sheet
/// </summary>
/// <param name="generateDate"></param>
public string handleBenchmarkReportSheet(DateTime generateDate)
{
string errorMsg = string.Empty;
DateTime endDay = generateDate.AddDays(-1);
DateTime startDay = generateDate.AddDays(-7);
DateTime priorEndDay = generateDate.AddDays(-8);
try
{
string endDateStr = endDay.ToString("yyyyMMdd");
string startDateStr = startDay.ToString("yyyyMMdd");
string priorEndDateStr = priorEndDay.ToString("yyyyMMdd");
string priorGenerateDateStr = startDateStr;
string priorReportPath = trackWeekly4Path + priorEndDateStr + @"\Tricare Benchmark Report -" + startDateStr + ".xls";
string ReportPath = trackWeekly4Path + endDateStr + @"\Tricare Benchmark Report -" + generateDate.ToString("yyyyMMdd") + ".xls";
string reportTempPath = trackWeekly4Path + endDateStr + @"\TRICATE Team Weekly-" + startDateStr + "-" + endDateStr + ".xls";
File.Copy(priorReportPath, ReportPath, true);
_ExcelApp = new Microsoft.Office.Interop.Excel.Application();
_ExcelWBook = (_Workbook)(_ExcelApp.Workbooks.Open(ReportPath, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
_ExcelWBook.CheckCompatibility = false;//Add for Diable Compatibility for saving excel
_Worksheet worksheet = _ExcelWBook.Worksheets["Dashboard"];
DateTime originalDate = Convert.ToDateTime("2013-1-3");
int weekNum = (int)(generateDate - originalDate).Days / 7;
string weeklyMsg = "Week-" + weekNum.ToString() + ": " + startDay.ToString("MM/dd/yyyy") + "-" + endDay.ToString("MM/dd/yyyy");
worksheet.get_Range("D1", "D1").Value = weeklyMsg;
worksheet.get_Range("E3", "I58").Value = null;
worksheet.get_Range("D3", "D58").Copy(Type.Missing);
worksheet.get_Range("I3", "I58").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
Type.Missing, Type.Missing);
worksheet.get_Range("D3", "D58").Value = null;
_Workbook workbook2 = (_Workbook)(_ExcelApp.Workbooks.Open(reportTempPath, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value));
_Worksheet worksheet2 = workbook2.Worksheets["Benchmark Result"];
worksheet2.get_Range("D3", "H58").Copy(Type.Missing);
worksheet.get_Range("D3", "H58").PasteSpecial(Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues, Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone,
Type.Missing, Type.Missing);
worksheet.Activate();
worksheet.get_Range("A1", "A1").Select();
_ExcelWBook.Save();
_ExcelWBook.Close();
workbook2.Close();
_ExcelApp.Quit();
}
catch (Exception ex)
{
errorMsg = ex.Message;
}
return(errorMsg);
}
public void Automation_UseArray(System.Data.DataTable dt, string fileAndPath)
{
//start new workbook in excel
m_objExcel = new Application();
m_objBooks = m_objExcel.Workbooks;
m_objBook = m_objBooks.Add(m_objOpt);
m_objSheets = m_objBook.Worksheets;
m_objSheet = (_Worksheet)(m_objSheets.Item[1]);
int col = dt.Columns.Count,
row = dt.Rows.Count;
object[] objHeader = new object[col];
for (int i = 0; i < col; i++)
{
objHeader[i] = dt.Columns[i].ColumnName;
}
m_objRange = m_objSheet.get_Range("A1", ExcelColumnIndexToName(col) + "1");
m_objRange.set_Value(m_objOpt, objHeader);
m_objFont = m_objRange.Font;
m_objFont.Bold = true;
//Create an array with 3 columns and 100 rows and add it to the
//worksheet with starting at cell A2
object[,] objData = new object[row, col];
//Random rdm = new Random((int)DateTime.Now.Ticks);
//double nOrderAmt, nTax;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < col; j++)
{
objData[i, j] = dt.Rows[i][j].ToString();
}
}
m_objRange = m_objSheet.get_Range("A2", m_objOpt);
m_objRange = m_objRange.get_Resize(row, col);
m_objRange.set_Value(m_objOpt, objData);
m_objBook.SaveAs(fileAndPath, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
m_objBook.Close(false, m_objOpt, m_objOpt);
m_objExcel.Quit();
}
public void addLine(string description, string result, string exception)
{
oSheet.Cells[rowNumber, 1] = description;
oSheet.Cells[rowNumber, 2] = result;
oSheet.Cells[rowNumber, 3] = exception;
rowNumber++;
oRng = oSheet.get_Range("A1", "D1");
oRng.EntireColumn.AutoFit();
}
private static string GetPointName(_Worksheet worksheet, string cellName)
{
Range range = worksheet.get_Range(cellName, Missing.Value);
if (range == null)
{
Console.WriteLine("ERROR: range == null, Cell = {0}", cellName);
}
return(range.Value2.ToString());
}
//Método para obtener un valor de una celda; El nombre de la celda puede ser A1,A2, B1,B2 etc... en excel.
static string obtenerCelda(string cellname)
{
string value = string.Empty;
try{
value = wSheet.get_Range(cellname).get_Value().ToString();
}
catch {
value = "";
}
return(value);
}
public void createCsvFile(String testName)
{
try
{
oXL = new Application();
oXL.Visible = true;
oWB = oXL.Workbooks.Add("");
oSheet = (_Worksheet)oWB.ActiveSheet;
oSheet.Cells[1, 1] = "StepDescription";
oSheet.Cells[1, 2] = "Pass/Fail";
oSheet.Cells[1, 3] = "Exception";
oSheet.get_Range("A1", "C1").Font.Bold = true;
oSheet.get_Range("A1", "C1").VerticalAlignment = XlVAlign.xlVAlignCenter;
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
}
}
internal void MakeCostMatrix(_Worksheet worksheet)
{
Range range = null;
string cellName = null;
uint cost = 0;
foreach (TeleportPoint leftPoint in leftPoints.Values)
{
foreach (TeleportPoint topPoint in topPoints.Values)
{
cellName = topPoint.TableColumnPos + leftPoint.TableRowPos.ToString();
range = worksheet.get_Range(cellName, Missing.Value);
if (range == null)
Console.WriteLine("ERROR: range == null, Cell = {0}", cellName);
if (range.Value2 != null)
{
cost = Convert.ToUInt32(range.Value2);
leftPoint.SetCost(topPoint.ID, cost);
}
}
}
}
private KeyValuePair<int, int> GetCoords(_Worksheet worksheet, uint currentRow)
{
string cellName = coordsColumn + currentRow.ToString();
Range range = worksheet.get_Range(cellName, Missing.Value);
if (range == null)
Console.WriteLine("ERROR: range == null, Cell = {0}", cellName);
string coord = range.Value2.ToString();
string[] coords = coord.Split(new char[] { ' ' });
return new KeyValuePair<int, int>(Convert.ToInt32(coords[0]), Convert.ToInt32(coords[1]));
}
/// <summary>
/// Get range from a sheet.
/// </summary>
/// <param name="sheet">Sheet.</param>
/// <param name="leftCell">Left cell.</param>
/// <param name="rightCell">Right cell.</param>
/// <returns>Range.</returns>
public static Range GetRange(_Worksheet sheet, object leftCell, object rightCell)
{
Range range = sheet.get_Range(leftCell, rightCell) as Range;
if (range == null)
{
throw new InvalidCastException("Error in getting Excel range.");
}
return range;
}
internal void GetHorizontalIDs(_Worksheet worksheet)
{
string currentColumn = idRowStart;
string cellName = currentColumn + idRow.ToString();
Range range = null;
byte id;
for (int i = 0; i < pointsCount; i++)
{
range = worksheet.get_Range(cellName, Missing.Value);
if (range == null)
Console.WriteLine("ERROR: range == null, Cell = {0}", cellName);
id = Convert.ToByte(range.Value2);
TeleportPoint point = leftPoints[id].Clone();
point.TableColumnPos = currentColumn;
point.TableRowPos = idRow;
if (!topPoints.ContainsKey(id))
{
topPoints.Add(id, point);
}
else
{
topPoints[id] = point;
}
currentColumn = GetNextColumnName(currentColumn);
cellName = currentColumn + idRow.ToString();
}
}
internal void GetIDs(_Worksheet worksheet)
{
uint currentRow = idColumnStart;
string cellName = idColumn + currentRow.ToString();
Range range = null;
byte id;
int x;
int y;
for (int i = 0; i < pointsCount; i++)
{
range = worksheet.get_Range(cellName, Missing.Value);
if (range == null)
Console.WriteLine("ERROR: range == null, Cell = {0}", cellName);
id = Convert.ToByte(range.Value2);
KeyValuePair<int, int> coords = GetCoords(worksheet, currentRow);
x = coords.Key;
y = coords.Value;
string ruName = GetRuName(worksheet, currentRow);
string phName = GetPhName(worksheet, currentRow);
string intName = GetIntName(worksheet, currentRow);
string msMyName = GetMsMyName(worksheet, currentRow);
if (!leftPoints.ContainsKey(id))
{
TeleportPoint point = new TeleportPoint();
point.ID = id;
point.X = x;
point.Y = y;
point.SetName(new System.Globalization.CultureInfo("ru"), ruName);
point.SetName(new System.Globalization.CultureInfo("en-PH"), phName);
point.SetName(new System.Globalization.CultureInfo(""), intName);
point.SetName(new System.Globalization.CultureInfo("ms"), msMyName);
point.TableRowPos = currentRow;
point.TableColumnPos = idColumn;
leftPoints.Add(id, point);
}
else
{
TeleportPoint point = leftPoints[id];
point.ID = id;
point.X = x;
point.Y = y;
point.SetName(new System.Globalization.CultureInfo("ru"), ruName);
point.SetName(new System.Globalization.CultureInfo("en-PH"), phName);
point.SetName(new System.Globalization.CultureInfo(""), intName);
point.SetName(new System.Globalization.CultureInfo("ms"), msMyName);
point.TableRowPos = currentRow;
point.TableColumnPos = idColumn;
leftPoints[id] = point;
}
currentRow++;
cellName = idColumn + currentRow.ToString();
}
}
private static string GetPointName(_Worksheet worksheet, string cellName)
{
Range range = worksheet.get_Range(cellName, Missing.Value);
if (range == null)
Console.WriteLine("ERROR: range == null, Cell = {0}", cellName);
return range.Value2.ToString();
}
