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(); }