public void ExpotToExcel(DataGridView dataGridView1,string SaveFilePath) { xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int i = 0; int j = 0; for (i = 0; i <= dataGridView1.RowCount - 1; i++) { for (j = 0; j <= dataGridView1.ColumnCount - 1; j++) { DataGridViewCell cell = dataGridView1[j, i]; xlWorkSheet.Cells[i + 1, j + 1] = cell.Value; } } xlWorkBook.SaveAs(SaveFilePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Your file is saved" + SaveFilePath); }
/// <summary> /// Create an empty Excel spreadsheet, with no associated file /// </summary> public ExcelFile() { if (_xlApp == null) _xlApp = new Excel.Application(); var workBooks = _xlApp.Workbooks; _xlWorkbook = workBooks.Add(); _xlDefaultWorkSheet = _xlWorkbook.Worksheets[1]; }
//private void wrtBySht(List<string> workList) private void wrtBySht(string shtName) { xlWorkSht = xlWorkBookTar.Worksheets.get_Item(shtName); xlWorkSht.Activate(); string idx = Util.TaskInfo.TaskSetting.insertPtInstData; // start point index Excel.Range rng = xlWorkSht.get_Range(idx, idx); DataTable dt; if (shtName == "InstrumentClassData") dt = Util.DbConn.SqlTsk.GetTable("procGetInstData"); else dt = Util.DbConn.SqlTsk.GetTable("procGetSymData " + shtName); int j = 1; int i = 0; foreach (DataRow row in dt.Rows) { for (i = 0; i < dt.Columns.Count; i++) { rng[j + 1, i + 1].Value = row[i].ToString(); } j++; if (j > dt.Rows.Count) { break; } } rng[j + 1, 1].Value = "end"; }
private void button1_Click(object sender, EventArgs e) { MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(path); MySheet = (Excel.Worksheet)MyBook.Sheets[1]; lastrow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; BindingList<Dompet> DompetList = new BindingList<Dompet>(); for (int index = 2; index <= lastrow; index++) { System.Array MyValues = (System.Array)MySheet.get_Range ("A" + index.ToString(),"F" + index.ToString()).Cells.Value; DompetList.Add(new Dompet { JmlPemesanan = MyValues.GetValue(1,1).ToString(), JmlPekerja = MyValues.GetValue(1,2).ToString(), Peralatan = MyValues.GetValue(1,3).ToString(), JenisKulit = MyValues.GetValue(1,4).ToString(), ModelDompet = MyValues.GetValue(1,5).ToString(), Prediksi = MyValues.GetValue(1,6).ToString() }); } dataGridView1.DataSource = (BindingList<Dompet>)DompetList; dataGridView1.AutoResizeColumns(); }
public TaskPriority() { InitializeComponent(); missing = System.Reflection.Missing.Value; config_data.ConfigFile = Environment.GetEnvironmentVariable("USERPROFILE")+"\\IntCallBack.xls"; xlApp = new msexcel.Application(); time_wasting = false; if (File.Exists(config_data.ConfigFile)) { xlWorkBook = xlApp.Workbooks.Open(config_data.ConfigFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); xlWorkSheet = (msexcel.Worksheet) xlWorkBook.Worksheets.get_Item(1); double dummy = (double) (xlWorkSheet.Cells[1, 2] as msexcel.Range ).Value ; config_data.PopUp = ((int)dummy == 1) ? true : false; config_data.RFrequency = (int)(xlWorkSheet.Cells[2, 2] as msexcel.Range).Value; config_data.Urgent_Hrs = (int)(xlWorkSheet.Cells[3, 2] as msexcel.Range).Value; config_data.Urgent_Mins = (int)(xlWorkSheet.Cells[4, 2] as msexcel.Range).Value; config_data.task1 = (string) (xlWorkSheet.Cells[5, 2] as msexcel.Range).Value; config_data.task2 = (string)(xlWorkSheet.Cells[6, 2] as msexcel.Range).Value; config_data.task3 = (string)(xlWorkSheet.Cells[7, 2] as msexcel.Range).Value; config_data.task4 = (string)(xlWorkSheet.Cells[8, 2] as msexcel.Range).Value; re_load_flag = true; } else { xlWorkBook = xlApp.Workbooks.Add(missing); xlWorkSheet = xlWorkBook.Worksheets.get_Item(1); config_data.PopUp = true; config_data.RFrequency = 3; config_data.Urgent_Hrs = 8; config_data.Urgent_Mins = 0; config_data.task1 = config_data.task2 = config_data.task3 = config_data.task4 = ""; xlWorkSheet.Cells[1, 1] = "PopUP"; xlWorkSheet.Cells[2, 1] = "Frequency"; xlWorkSheet.Cells[3, 1] = "Urgent Hrs"; xlWorkSheet.Cells[4, 1] = "Urgent Mins"; xlWorkSheet.Cells[5,1] = "Task 1"; xlWorkSheet.Cells[6,1] = "Task 2"; xlWorkSheet.Cells[7,1] = "Task 3"; xlWorkSheet.Cells[8,1] = "Task 4"; xlWorkSheet.Cells[1, 2] = (config_data.PopUp == true) ? "1" : "2"; xlWorkSheet.Cells[2, 2] = config_data.RFrequency.ToString(); xlWorkSheet.Cells[3, 2] = config_data.Urgent_Hrs.ToString(); xlWorkSheet.Cells[4, 2] = config_data.Urgent_Mins.ToString(); xlWorkSheet.Cells[5, 2] = config_data.task1; xlWorkSheet.Cells[6, 2] = config_data.task1; xlWorkSheet.Cells[7, 2] = config_data.task1; xlWorkSheet.Cells[8, 2] = config_data.task1; xlWorkBook.SaveAs(config_data.ConfigFile, msexcel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, msexcel.XlSaveAsAccessMode.xlShared, missing, missing, missing, missing, missing); //xlWorkBook.Close(); re_load_flag = false; } }
public void export() { try { //excelApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); //wb = excelApp.Workbooks.Add(); wb = (Excel.Workbook)Globals.ThisAddIn.Application.ActiveWorkbook; ws = wb.Worksheets.get_Item(1) as Excel.Worksheet; // 데이타 넣기 int r = 1; foreach (var d in this.exceptionList_) { ws.Cells[r, 1] = d.Message; r++; } //this.wb.Worksheets.Add(ws); } finally { // Clean up ReleaseExcelObject(ws); ReleaseExcelObject(wb); ReleaseExcelObject(excelApp); } }
public ReportGenerator(Excel.Worksheet src_worksheet, Excel.Worksheet dest_worksheet) { _src_worksheet = src_worksheet; _dest_worksheet = dest_worksheet; writeProjectNamesToDestSheet(); }
public getData readData() { getData.dateT.Clear(); getData.prj.Clear(); getData.descr.Clear(); getData.taskTime.Clear(); notes.Clear(); ExcelApp = new Excel.Application(); ExcelApp.Visible = false; WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1 //excelcells = excelworksheet.get_Range("D215", Type.Missing); //Выбираем ячейку для вывода A1 // WorkSheetExcel.Cells[i, 1].Text.ToString() != "" for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++) { getData.dateT.Add(WorkSheetExcel.Cells[i, 1].Text.ToString()); getData.prj.Add(WorkSheetExcel.Cells[i, 2].Text.ToString()); getData.descr.Add(WorkSheetExcel.Cells[i, 3].Text.ToString()); getData.taskTime.Add(WorkSheetExcel.Cells[i, 4].Text.ToString()); } WorkBookExcel.Close(false, Type.Missing, Type.Missing); //закрыл не сохраняя ExcelApp.Quit(); GC.Collect(); getData.dateT.Reverse(); getData.prj.Reverse(); getData.descr.Reverse(); getData.taskTime.Reverse(); return getData; }
/// <summary> /// Creates a spreadsheet in the give xls filename. /// </summary> /// <param name="filename">The complete filename with the absolute path.</param> /// <param name="sheetname">The name of the sheet e.g. Hidden</param> /// <returns>True if succeeded, false if failed.</returns> public static bool createWorksheet(String filename, String sheetname, bool needsToBeHidden = false) { successStatus = false; try { successStatus = openXlApp(); CurrentSpreadSheet css = CurrentSpreadSheet.Instance; //checking if the call is being made for the currently open worbook. this is less expensive. if ((css.CurrentWorkBook != null) && (css.CurrentWorkBook.FullName == filename)) { xlSheets = css.CurrentWorkBook.Sheets as Excel.Sheets; } else { xlWorkbook = openXlWorkBook(filename); xlSheets = xlWorkbook.Sheets as Excel.Sheets; } xlSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[xlSheets.Count + 1]); xlSheet.Name = sheetname; if (needsToBeHidden) xlSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden; xlWorkbook.Save(); successStatus = quitXlApp(); } finally { garbageCollect(); } return successStatus; }
/// <summary> /// 构造函数,将一个已有Excel工作簿作为模板,并指定输出路径 /// </summary> /// <param name="templetFilePath">Excel模板文件路径</param> /// <param name="outputFilePath">输出Excel文件路径</param> public ExcelBase(string templetFilePath, string outputFilePath) { if (templetFilePath == null) throw new Exception("Excel模板文件路径不能为空!"); if (outputFilePath == null) throw new Exception("输出Excel文件路径不能为空!"); if (!File.Exists(templetFilePath)) throw new Exception("指定路径的Excel模板文件不存在!"); this.templetFile = templetFilePath; this.outputFile = outputFilePath; //创建一个Application对象并使其可见 beforeTime = DateTime.Now; app = new Excel.ApplicationClass(); app.Visible = true; afterTime = DateTime.Now; //打开模板文件,得到WorkBook对象 workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //得到WorkSheet对象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); }
public Matrix_Worksheet(Matrix_Workbook matrixWorkbook, Microsoft.Office.Interop.Excel.Worksheet workSheet) { _matrixWorkbook = matrixWorkbook; _worksheet = workSheet; InitializeControlData(); }
public void BadTaskSpecExtractionAndTaskFactoryParsingTest() { ws = (Excel.Worksheet)wss["TestTasks2"]; IEnumerable<Specification> actualTaskSpecs; Assert.IsFalse(excelController.ExtractData(out actualTaskSpecs)); }
private void Form1_Load(object sender, EventArgs e) { app = new Excel.Application(); app.Visible = false; workbook = app.Workbooks.Open(Application.StartupPath + "\\Дни рождения сотрудников.xls"); worksheet = workbook.ActiveSheet; int i = 0; string today = DateTime.Now.ToString("dd.MM"); string tomorrow = DateTime.Now.AddDays(1).ToString("dd.MM"); for (i = 1; i <= worksheet.UsedRange.Rows.Count; i++) { if (worksheet.Cells[2][i].Value == "Іб та ПД" || worksheet.Cells[3][i].Value == "Іб та ПД" || worksheet.Cells[2][i].Value == "Інформаційної безпеки та передачі даних" || worksheet.Cells[3][i].Value == "Інформаційної безпеки та передачі даних") { string birthday = worksheet.Cells[4][i].Value.ToString("dd.MM"); if (birthday == today) { label1.Text = ""; label1.Text += "Сегодня свой день рождения отмечает " + worksheet.Cells[1][i].Value; } if (birthday == tomorrow) { label1.Text = ""; label1.Text += "Завтра свой день рождения отмечает " + worksheet.Cells[1][i].Value; } } } app.Quit(); }
public static void InitializeExcel() { _myApp = new Excel.Application {Visible = false}; _myBook = _myApp.Workbooks.Open(ExcelPath); _mySheet = (Excel.Worksheet)_myBook.Sheets[1]; // Explict cast is not required here _lastRow = _mySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; }
private void Init() { xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Open(name, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlSh = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); }
public Sheet(Excel.Worksheet _worksheet) { this.Base = _worksheet; this.CurrentRow = 1; this.CurrentCol = 1; this.Valid = ReadValues(); }
static void GetVisibleFormExcel() { //Console.WriteLine("please choose Excel File"); //OpenFileDialog ofD = new OpenFileDialog(); ofD.ShowDialog(); //string fName = ofD.FileName; //Excel.Application xApp = new Excel.Application(); //xApp.Visible = true; //Excel.Workbook xbook = xApp.Workbooks.Open("C:\\share\\temp.xlsx"); xSheet = xbook.Sheets[2]; xSheet.Select(); Excel.Range xRange = null; string appKeyStr = null; string visibleStr = null; int i = 2; xRange = xSheet.Cells[i, 1]; appKeyStr = xRange.Value2; while (!String.IsNullOrEmpty(appKeyStr)) { appKeyArr.Add(appKeyStr.ToString().Trim()); xRange = xSheet.Cells[i, 3]; visibleStr = xRange.Value2.ToString().Trim(); VisibleArr.Add(visibleStr); //Console.WriteLine("Name:{0} IE:{1} FireFox:{2} Chrome:{3}", appName,ie,firefox,chrome); i++; xRange = xSheet.Cells[i, 1]; appKeyStr = xRange.Value2; } //Console.WriteLine("Done"); xSheet = null; //xbook = null; xApp.Quit(); xApp = null; }
public static void openXL(string xlp) { xla = new Excel.Application(); xlw = xla.Workbooks.Open(xlp); xls = xlw.Worksheets.get_Item(1); xlr = xls.UsedRange; }
public ComExportExcel(string templatePath) { xlApp = new ComExcel.Application(); xlWorkBook = xlApp.Workbooks.Open(templatePath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, false, 0, true, 1, 0); //xlWorkBook = xlApp.Workbooks.Open(templatePath, ReadOnly:false, Editable:true ); xlWorkSheet = (ComExcel.Worksheet)xlWorkBook.Worksheets.get_Item(1); }
public void ReadProcesses() { count = 0; ExcelApp = new Excel.Application(); ExcelApp.Visible = false; WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу //Читаем данные по проектам WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets["Processes"]; //Получаем ссылку на лист Processes List<string> row = new List<string>(); int n = 6; for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++) { row = new List<string>(); for (int j = 1; j < n; j++) row.Add(WorkSheetExcel.Cells[i, j].Text.ToString()); //строка массива заполняется просто суммой i и j Mas.Add(row); //строка добавляется в массив count++; } //test = WorkSheetExcel.Cells[2, 1]; WorkBookExcel.Close(false, Type.Missing, Type.Missing); ExcelApp.Quit(); GC.Collect(); }
//加载 private void Form1_Load(object sender, EventArgs e) { panel1.Visible = false; app = new MSExcel.Application(); app.Visible = false; book = app.Workbooks.Open(@"D:\template.xls"); sheet = (MSExcel.Worksheet)book.ActiveSheet; //串口设置默认选择项 cbSerial.SelectedIndex = 1; //note:获得COM9口,但别忘修改 cbBaudRate.SelectedIndex = 5; cbDataBits.SelectedIndex = 3; cbStop.SelectedIndex = 0; cbParity.SelectedIndex = 0; //sp1.BaudRate = 9600; Control.CheckForIllegalCrossThreadCalls = false; //这个类中我们不检查跨线程的调用是否合法(因为.net 2.0以后加强了安全机制,,不允许在winform中直接跨线程访问控件的属性) sp1.DataReceived += new SerialDataReceivedEventHandler(sp1_DataReceived); //sp1.ReceivedBytesThreshold = 1; radio1.Checked = true; //单选按钮默认是选中的 rbRcvStr.Checked = true; //准备就绪 sp1.DtrEnable = true; sp1.RtsEnable = true; //设置数据读取超时为1秒 sp1.ReadTimeout = 1000; sp1.Close(); }
internal OrderingSheet(bool isUnscheduled) { if (!Globals.DataSet.IsLastDayComplete()) { throw new ApplicationException(Globals.ThisWorkbook.IncompleteDataMessage); } this.orderDate = Globals.DataSet.MaxDate; string worksheetName; if (isUnscheduled) { worksheetName = ExcelHelpers.CreateValidWorksheetName( String.Format( CultureInfo.CurrentUICulture, Properties.Resources.UnscheduledOrderSheetName, this.orderDate.ToShortDateString())); } else { worksheetName = ExcelHelpers.CreateValidWorksheetName( String.Format( CultureInfo.CurrentUICulture, Properties.Resources.WeeklyOrderSheetName, this.orderDate.ToShortDateString())); } Excel.Worksheet worksheet = null; // 如果该名称已经存在,则在创建工作表时将引发异常。 try { worksheet = Globals.ThisWorkbook.CreateWorksheet(worksheetName); } catch (Exception ex) { string message; if (isUnscheduled) { message = String.Format( CultureInfo.CurrentUICulture, Properties.Resources.UnscheduledOrderSheetCreationError, worksheetName); } else { message = String.Format( CultureInfo.CurrentUICulture, Properties.Resources.WeeklyOrderSheetCreationError, worksheetName); } throw new ApplicationException(message, ex); } this.worksheet = worksheet; CreateOrder(isUnscheduled); }
public ClusteringManager() { //prepare Excel objects: m_ObjExcel = new Microsoft.Office.Interop.Excel.Application(); m_ObjWorkBook = m_ObjExcel.Workbooks.Open( MethodInputResponse.INPUT_FILE_PATH, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); m_ObjWorkSheet1 = (myExcel.Worksheet)m_ObjWorkBook.Sheets[1]; myExcel.Sheets xlSheets = m_ObjWorkBook.Sheets as myExcel.Sheets; for (int i = 3; i < MAX_CLUSTERS_NUMBER; i++) { xlSheets.Add(Type.Missing, m_ObjWorkSheet1, Type.Missing, Type.Missing); } }
private void drawGraph() { Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); try { xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //Get all the sheets in the workbook while (thread1.IsAlive) { //son satır bulunuyor excel dosyasındaki Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); Excel.Range range = xlWorkSheet.get_Range("A1", last); int lastUsedRow = last.Row; int lastUsedColumn = last.Column; string ReceiveData = myport.ReadLine(); // comdan degeri okuyuruz // alınan degerdeki stringleri temizleyerek sadece double değeri yakalıyor string[] HeatingData = ReceiveData.Split(':'); string[] HeatingData2 = HeatingData[1].Split('D'); var result = HeatingData2[0]; double heating = Convert.ToDouble(result); theTime = DateTime.Now; // anlik olarak zamani ogreniyoruz! string zaman = theTime.ToString("yyyy/MM/dd HH:mm:ss"); Thread.Sleep(1000); // ilk threadi anlik olarak durduruyor ve Invoke ile GUI threadini ulasip cizdiriyor! this.Invoke((MethodInvoker)delegate { chart1.Series["Series1"].Points.AddY(result); // excel dosyasındaki son yazılan satırdan bir sonraki satıra sıcaklığı yazdırıyor xlWorkSheet.Cells[lastUsedRow+1, 2] = (heating / 100); xlWorkSheet.Cells[lastUsedRow + 1, 1] = zaman; }); } } catch { // MessageBox.Show("Dosya bulunamadı"); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "Zaman"; xlWorkSheet.Cells[1, 2] = "Sıcaklık Celcius"; xlWorkBook.SaveAs("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Dosya oluşturuldu , proje klasörünüzde bulunmaktadır"); } }
public static void InitializeExcel() { MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(DB_PATH); MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explict cast is not required here lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; }
private void EnsureOneSheet() { if (String.IsNullOrEmpty(sheetName) || !hasSuch(sheetName)) { workSheet = Globals.ThisAddIn.Application.Worksheets.Add(); sheetName = workSheet.Name; } }
public ThreadData(ref Mutex mut, ref Excel.Worksheet activeWorksheet, long startIndex, long endIndex, int threadId) { _mut = mut; _activeWorksheet = activeWorksheet; _startIndex = startIndex; _endIndex = endIndex; _threadId = threadId; }
public ThreadData(ref Mutex mut, ref Excel.Worksheet activeWorksheet, long index, bool setTS, int threadId) { _mut = mut; _activeWorksheet = activeWorksheet; _index = index; _setTS = setTS; _threadId = threadId; }
/// <summary> /// 打开一个Excel文件 /// </summary> /// <param name="FileName"></param> public static void Open( string fileName )// { app = new Microsoft.Office.Interop.Excel.Application(); wbs = app.Workbooks; wb = wbs.Add( fileName ); ws = wb.Worksheets[ 1 ] as Worksheet; mFilename = fileName; }
void exportBaoGia() { if (treeData.AllNodesCount == 0) { return; } string localPath = ""; FolderBrowserDialog fbd = new FolderBrowserDialog(); if (fbd.ShowDialog() == DialogResult.OK) { localPath = fbd.SelectedPath + "\\SX." + Quotation.Code + ".xls"; } else { return; } string filePath = Application.StartupPath + "\\Templates\\PhongKinhDoanh\\BaoGia.xls"; try { File.Copy(filePath, localPath, true); } catch (Exception ex) { MessageBox.Show("Lỗi: " + Environment.NewLine + ex.Message); return; } using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo báo giá...")) { System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Excel.Application app = default(Excel.Application); Excel.Workbook workBoook = default(Excel.Workbook); Excel.Worksheet workSheet = default(Excel.Worksheet); try { app = new Excel.Application(); app.Workbooks.Open(localPath); workBoook = app.Workbooks[1]; workSheet = (Excel.Worksheet)workBoook.Worksheets[1]; DataTable dtItem = LibQLSX.Select("select * from vC_QuotationDetail_SX with(nolock) where ParentID = 0 and C_QuotationID = " + Quotation.ID); for (int i = dtItem.Rows.Count - 1; i >= 0; i--) { int id = TextUtils.ToInt(dtItem.Rows[i]["ID"]); string moduleCodeP = TextUtils.ToString(dtItem.Rows[i]["ModuleCode"]); string moduleNameP = TextUtils.ToString(dtItem.Rows[i]["ModuleName"]); decimal qtyP = TextUtils.ToDecimal(dtItem.Rows[i]["Qty"]); decimal priceP = TextUtils.ToDecimal(dtItem.Rows[i]["PriceHD"]); string hangP = TextUtils.ToString(dtItem.Rows[i]["Manufacture"]); DataTable dtC = LibQLSX.Select("select * from vC_QuotationDetail_SX with(nolock) where ParentID = " + id); if (dtC.Rows.Count == 0)//nếu thiết bị không có thiết bị con { #region Add Parent if (moduleCodeP.ToUpper().StartsWith("TPAD.") && moduleCodeP.Length == 10) { try { string spec = TextUtils.ToString(TextUtils.ExcuteScalar("select top 1 Specifications from Modules where Code = '" + moduleCodeP + "'")); if (spec.Length > 0) { string[] thongSo = spec.Split('\n'); for (int k = thongSo.Length - 1; k >= 0; k--) { workSheet.Cells[16, 3] = thongSo[k]; ((Excel.Range)workSheet.Rows[16]).Insert(); } } else { ((Excel.Range)workSheet.Rows[16]).Insert(); ((Excel.Range)workSheet.Rows[16]).Insert(); } } catch { } } else { ((Excel.Range)workSheet.Rows[16]).Insert(); ((Excel.Range)workSheet.Rows[16]).Insert(); } workSheet.Cells[16, 2] = (i + 1); workSheet.Cells[16, 3] = moduleNameP.ToUpper(); workSheet.Cells[16, 4] = moduleCodeP.ToUpper(); workSheet.Cells[16, 5] = hangP; workSheet.Cells[16, 6] = "BỘ"; workSheet.Cells[16, 7] = qtyP.ToString("n0"); workSheet.Cells[16, 8] = priceP.ToString("n0"); workSheet.Cells[16, 9] = qtyP * priceP; ((Excel.Range)workSheet.Rows[16]).Font.Bold = true; ((Excel.Range)workSheet.Rows[16]).Insert(); #endregion } else { decimal sumPrice = 0; #region Add child module for (int j = dtC.Rows.Count - 1; j >= 0; j--) { string moduleCode = TextUtils.ToString(dtC.Rows[j]["ModuleCode"]); string moduleName = TextUtils.ToString(dtC.Rows[j]["ModuleName"]); string qty = TextUtils.ToDecimal(dtC.Rows[j]["QtyT"]).ToString("n0"); string price = TextUtils.ToDecimal(dtC.Rows[j]["PriceHD"]).ToString("n0"); string hang = TextUtils.ToString(dtC.Rows[j]["Manufacture"]); //string total = TextUtils.ToDecimal(dtC.Rows[j]["TotalTPA"]).ToString("n0"); sumPrice += TextUtils.ToDecimal(qty) * TextUtils.ToDecimal(price); if (moduleCode.ToUpper().StartsWith("TPAD.") && moduleCode.Length == 10) { try { string spec = TextUtils.ToString(TextUtils.ExcuteScalar("select top 1 Specifications from Modules where Code = '" + moduleCode + "'")); if (spec.Length > 0) { string[] thongSo = spec.Split('\n'); for (int k = thongSo.Length - 1; k >= 0; k--) { workSheet.Cells[16, 3] = thongSo[k]; ((Excel.Range)workSheet.Rows[16]).Insert(); } } else { ((Excel.Range)workSheet.Rows[16]).Insert(); ((Excel.Range)workSheet.Rows[16]).Insert(); } } catch { } } else { ((Excel.Range)workSheet.Rows[16]).Insert(); ((Excel.Range)workSheet.Rows[16]).Insert(); } workSheet.Cells[16, 2] = (i + 1) + "." + (j + 1); workSheet.Cells[16, 3] = moduleName; //.ToUpper(); workSheet.Cells[16, 4] = moduleCode; //.ToUpper(); workSheet.Cells[16, 5] = hang; workSheet.Cells[16, 6] = ""; workSheet.Cells[16, 7] = qty; workSheet.Cells[16, 8] = price; //workSheet.Cells[16, 9] = total; ((Excel.Range)workSheet.Rows[16]).Font.Bold = true; ((Excel.Range)workSheet.Rows[16]).Insert(); } #endregion #region Add Parent workSheet.Cells[16, 3] = "'* Thông số kỹ thuật chi tiết"; ((Excel.Range)workSheet.Rows[16]).Font.Bold = true; ((Excel.Range)workSheet.Rows[16]).Insert(); for (int j = dtC.Rows.Count - 1; j >= 0; j--) { string moduleName = TextUtils.ToString(dtC.Rows[j]["ModuleName"]); string qty = TextUtils.ToDecimal(dtC.Rows[j]["QtyT"]).ToString("n0"); workSheet.Cells[16, 3] = qty + " " + moduleName; ((Excel.Range)workSheet.Rows[16]).Insert(); } workSheet.Cells[16, 3] = "'* Danh mục thiết bị"; ((Excel.Range)workSheet.Rows[16]).Font.Bold = true; ((Excel.Range)workSheet.Rows[16]).Insert(); ((Excel.Range)workSheet.Rows[16]).Insert(); ((Excel.Range)workSheet.Rows[16]).Insert(); workSheet.Cells[16, 3] = "'* Nội dung thực hành"; ((Excel.Range)workSheet.Rows[16]).Font.Bold = true; ((Excel.Range)workSheet.Rows[16]).Insert(); workSheet.Cells[16, 3] = "'- Xuất xứ: Việt Nam"; ((Excel.Range)workSheet.Rows[16]).Insert(); workSheet.Cells[16, 3] = "'- Hãng sản xuất: TPA"; ((Excel.Range)workSheet.Rows[16]).Insert(); workSheet.Cells[16, 3] = "'- Model: " + moduleCodeP; ((Excel.Range)workSheet.Rows[16]).Insert(); workSheet.Cells[16, 2] = (i + 1); workSheet.Cells[16, 3] = moduleNameP.ToUpper(); workSheet.Cells[16, 4] = moduleCodeP.ToUpper(); workSheet.Cells[16, 5] = hangP; workSheet.Cells[16, 6] = "BỘ"; workSheet.Cells[16, 7] = qtyP.ToString("n0"); workSheet.Cells[16, 8] = sumPrice.ToString("n0"); workSheet.Cells[16, 9] = qtyP * sumPrice; ((Excel.Range)workSheet.Rows[16]).Font.Bold = true; ((Excel.Range)workSheet.Rows[16]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);// Color.Yellow; ((Excel.Range)workSheet.Rows[16]).Insert(); #endregion } } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (app != null) { app.ActiveWorkbook.Save(); app.Workbooks.Close(); app.Quit(); } } if (File.Exists(localPath)) { Process.Start(localPath); } } }
private void BrokerOperation(FormPortfolio.OperType operation) { if (IsBrokerActiveSheet(this.Application.ActiveWorkbook)) { if (this.Application.Selection != null) { Excel.Range cells = this.Application.Selection; Excel.Worksheet BrokerSheet = this.Application.ActiveWorkbook.ActiveSheet; string ticker = ""; string account = ""; string name = ""; if (cells.Row > 1) { ticker = BrokerSheet.Cells[cells.Row, 2].Text; account = BrokerSheet.Cells[cells.Row, 1].Text; name = BrokerSheet.Cells[cells.Row, 3].Text; } FormPortfolio dlg1 = new FormPortfolio(); dlg1.thisAddIn = this; dlg1.ticker = ticker; dlg1.seektxt = name; dlg1.account = account; dlg1.operation = operation; if ((dlg1.ShowDialog() == DialogResult.OK) && (dlg1.count > 0)) { // добавляем строку в excel int max_rows = BrokerSheet.UsedRange.Rows.Count; int max_columns = BrokerSheet.UsedRange.Columns.Count; max_rows++; BrokerSheet.Cells[max_rows, 1] = dlg1.account; BrokerSheet.Cells[max_rows, 2] = dlg1.ticker; BrokerSheet.Cells[max_rows, 3] = dlg1.name; BrokerSheet.Cells[max_rows, 4] = dlg1.InstrumentTypeName; switch (dlg1.operation) { case FormPortfolio.OperType.Buy: BrokerSheet.Cells[max_rows, 5] = "Покупка"; BrokerSheet.Cells[max_rows, 7] = dlg1.count; BrokerSheet.Cells[max_rows, 11] = dlg1.summa; break; case FormPortfolio.OperType.PlanBuy: BrokerSheet.Cells[max_rows, 5] = "ПЛАН"; BrokerSheet.Cells[max_rows, 7] = dlg1.count; BrokerSheet.Cells[max_rows, 11] = dlg1.summa; break; case FormPortfolio.OperType.Sell: default: BrokerSheet.Cells[max_rows, 5] = "Продажа"; BrokerSheet.Cells[max_rows, 7] = -dlg1.count; BrokerSheet.Cells[max_rows, 11] = -dlg1.summa; break; } BrokerSheet.Cells[max_rows, 6] = DateTime.Today; if (dlg1.nominal > 0) { BrokerSheet.Cells[max_rows, 8] = dlg1.nominal; } BrokerSheet.Cells[max_rows, 9] = dlg1.currency; BrokerSheet.Cells[max_rows, 10].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 10].FormulaR1C1; BrokerSheet.Cells[max_rows, 13] = dlg1.price; BrokerSheet.Cells[max_rows, 14].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 14].FormulaR1C1; BrokerSheet.Cells[max_rows, 15].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 15].FormulaR1C1; BrokerSheet.Cells[max_rows, 16].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 16].FormulaR1C1; BrokerSheet.Cells[max_rows, 17].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 17].FormulaR1C1; BrokerSheet.Cells[max_rows, 18].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 18].FormulaR1C1; BrokerSheet.Cells[max_rows, 19].FormulaR1C1 = BrokerSheet.Cells[max_rows - 1, 19].FormulaR1C1; MSExcel.RangeBold(BrokerSheet.Range[BrokerSheet.Cells[max_rows, 1], BrokerSheet.Cells[max_rows, max_columns]], false); MSExcel.RangeBorder(BrokerSheet.Range[BrokerSheet.Cells[max_rows, 1], BrokerSheet.Cells[max_rows, max_columns]]); dlg1.Dispose(); } } } }
private void Button1_Click(object sender, EventArgs e) { if (dgv.Rows.Count > 0) //TH có dữ liệu để ghi { //Khai báo và khởi tạo các đối tượng Excel.Application exApp = new Excel.Application(); Excel.Workbook exBook = exApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet exSheet = (Excel.Worksheet)exBook.Worksheets[1]; //Định dạng chung Excel.Range tenCuaHang = (Excel.Range)exSheet.Cells[1, 1]; tenCuaHang.Font.Size = 14; tenCuaHang.Font.Bold = true; tenCuaHang.Font.Color = Color.Blue; tenCuaHang.Value = "BẢNG ĐIỂM "; Excel.Range dcCuaHang = (Excel.Range)exSheet.Cells[2, 1]; dcCuaHang.Font.Size = 14; dcCuaHang.Font.Bold = true; dcCuaHang.Font.Color = Color.Blue; dcCuaHang.Value = "Copyright-Cao Thi Luyên"; Excel.Range dtCuaHang = (Excel.Range)exSheet.Cells[3, 1]; dtCuaHang.Font.Size = 14; dtCuaHang.Font.Bold = true; dtCuaHang.Font.Color = Color.Blue; dtCuaHang.Value = "Điện thoại: 0912403345"; Excel.Range header = (Excel.Range)exSheet.Cells[5, 2]; exSheet.get_Range("B5:G5").Merge(true); header.Font.Size = 13; header.Font.Bold = true; header.Font.Color = Color.Red; header.Value = "DANH SÁCH ĐIỂM CÁC MÔN HỌC"; //Định dạng tiêu đề bảng exSheet.get_Range("A7:E7").Font.Bold = true; exSheet.get_Range("A7:E7").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; exSheet.get_Range("A7").Value = "Mã Lớp"; exSheet.get_Range("B7").Value = "Doanh Thu"; //In dữ liệu for (int i = 0; i < dgv.Rows.Count - 1; i++) { exSheet.get_Range("A" + (i + 8).ToString() + ":G" + (i + 8).ToString()).Font.Bold = false; exSheet.get_Range("A" + (i + 8).ToString()).Value = (i + 1).ToString(); exSheet.get_Range("B" + (i + 8).ToString()).Value = dgv.Rows[i].Cells[0].Value; } exSheet.Name = "Điểm thi"; exBook.Activate(); //Kích hoạt file Excel //Thiết lập các thuộc tính của SaveFileDialog dlgSave.Filter = "Excel Document(*.xls)|*.xls |Word Document(*.doc) |*.doc|All files(*.*)|*.*"; dlgSave.FilterIndex = 1; dlgSave.AddExtension = true; dlgSave.DefaultExt = ".xls"; if (dlgSave.ShowDialog() == System.Windows.Forms.DialogResult.OK) { exBook.SaveAs(dlgSave.FileName.ToString()); //Lưu file Excel } exApp.Quit(); //Thoát khỏi ứng dụng } else { MessageBox.Show("Không có danh sách hàng để in"); } }
private modelOrganization GetOrganization(MSExcel.Worksheet ws, int row) { string pic = ws.Cells[row, 1].Text; if (pic == "") { return(null); } string partnername = ws.Cells[row, 2].Text; string acronym = ws.Cells[row, 3].Text; string country = ws.Cells[row, 4].Text; string representative_firstname = ws.Cells[row, 5].Text; string representative_surname = ws.Cells[row, 6].Text; string representative_gender = ws.Cells[row, 7].Text; string representative_function = ws.Cells[row, 8].Text; string legal_status = ws.Cells[row, 9].Text; string regno = ws.Cells[row, 10].Text; string address = ws.Cells[row, 11].Text; string zip = ws.Cells[row, 12].Text; string city = ws.Cells[row, 13].Text; string vat = ws.Cells[row, 14].Text; string email = ws.Cells[row, 15].Text; string phone = ws.Cells[row, 16].Text; string partner_type = ws.Cells[row, 17].Text; string partner_description = ws.Cells[row, 18].Text; string org_description = ws.Cells[row, 19].Text; string activities = ws.Cells[row, 20].Text; string keystaff = ws.Cells[row, 21].Text; string contractnum = ws.Cells[row, 22].Text; var organization = new modelOrganization(); if (int.TryParse(pic, out int pc)) { organization.PIC = pc; } organization.LegalName = partnername; organization.LegalNameLatinChar = ""; organization.EnglishName = ""; organization.FriendlyName = partnername; organization.Acronym = acronym; organization.LegalForm = legal_status; organization.Address = address; organization.PostCode = zip; organization.City = city; organization.Country = Program.GData.dataLOV.getCountries.First(c => c.Country == country).ID; organization.Email = email; organization.Website = ""; organization.Phone = phone; organization.TypeOfOrganization = 1; organization.PublicBody = false; organization.NonProfit = false; organization.Accreditation = ""; organization.FirstName = representative_firstname; organization.SurName = representative_surname; organization.Gender = representative_gender; organization.RepresentativeFunction = representative_function; organization.LegalStatus = legal_status; organization.RegistrationNumber = regno; organization.VAT = vat; organization.GeneralDescription = partner_description; organization.Description = org_description; organization.Activities = activities; organization.KeyStaff = keystaff; organization.ContractNumber = contractnum; return(organization); }
private void button2_Click(object sender, RibbonControlEventArgs e) { Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet); string AktifSayfa = activeWorksheet.Name; if (IslemSayfasi == AktifSayfa) //Doğru sayfada olup olmadığı kontrol edilir. { //veriler sıfırlanır. bool Hata_Yakalama = false; EKOCT_TOPLAM = 0; EKCT_TOPLAM = 0; KCT_TOPLAM = 0; KOCT_TOPLAM = 0; NKCT_TOPLAM = 0; ONCT_TOPLAM = 0; CVPSZCT_TOPLAM = 0; ARADCT_TOPLAM = 0; //bütün soruları işaretlemeyen cevaplayıcılar tespit edilir. Bos_Satirlar = new ArrayList(); int Bos_Satir_Sayisi = 0; for (int i = 1; i <= Katilimci_Sayisi; i++) {//Bütün boş satırların sayısını ve indis numaralarını bulur. int sayac = 0; for (int j = 1; j <= Madde_Sayisi; j++) { string siradaki = Hucrelerden_Okuma_Metodu((i + 1), (j + 1)); if (siradaki == "" || siradaki == " " || siradaki == "0") { sayac++; } } if (sayac == Madde_Sayisi) { Bos_Satir_Sayisi++; Bos_Satirlar.Add(i + 1); } } Excel.Worksheet wsSil = Globals.ThisAddIn.Application.ActiveSheet; Excel.Range row; int[] rows = new int[Bos_Satirlar.Count]; int SilinenSayac = 0; foreach (var item in Bos_Satirlar) {//Bütün soruları işaretlemeyen cevaplayıcılar silinir. row = (Excel.Range)wsSil.Rows[Convert.ToInt32(item.ToString()) - SilinenSayac]; row.Delete(); SilinenSayac++; } Katilimci_Sayisi = Katilimci_Sayisi - Bos_Satir_Sayisi; Sonuclar = new double[Katilimci_Sayisi, 9]; for (int i = 1; i <= Katilimci_Sayisi; i++) { Hata_Yakalama = false; ArrayList Katilimci_Cevaplari = new ArrayList();//Her katılımcı için liste oluşturulur. for (int j = 1; j <= Madde_Sayisi; j++) { string siradaki = Hucrelerden_Okuma_Metodu((i + 1), (j + 1)); if (siradaki == "" || siradaki == " ") { Katilimci_Cevaplari.Add(siradaki); } else { try {//Doğru aralıkta veri girişi yapılmış ise katılımcının cevapları bir listeye eklenir. //yanlış veri girişi yapılmış ise program hata mesajı verir. if (Convert.ToInt32(siradaki) > Anket_Tipi || Convert.ToInt32(siradaki) < 0) { i = Madde_Sayisi + 1; Hata_Yakalama = true; MessageBox.Show("Hatalı Veri Girişi Yapılmıştır.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); break; } else { Katilimci_Cevaplari.Add(siradaki); } } catch (Exception ex) { i = Madde_Sayisi + 1; Hata_Yakalama = true; MessageBox.Show("Hatalı Veri Girişi Yapılmıştır.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); break; } } } //formüllerin hesaplanması için ilgili metod çağırılır. EKOCT_Metod(Katilimci_Cevaplari, i); EKCT_Metod(Katilimci_Cevaplari, i); ECT = EKOCT + EKCT;//Ekstrem Cevaplama Tarzı. Sonuclar[(i - 1), 0] = ECT; if (Anket_Tipi % 2 == 1) {//Orta Noktası Bulunan Anket Türleri İçin. KCT_Metod(Katilimci_Cevaplari, i); KCT_TOPLAM += KCT; KOCT_Metod(Katilimci_Cevaplari, i); KOCT_TOPLAM += KOCT; ONCT_Metod(Katilimci_Cevaplari, i); ONCT_TOPLAM += ONCT; } NKCT = KCT - KOCT; NKCT_TOPLAM += NKCT; Sonuclar[(i - 1), 5] = NKCT; CVPSZCT_Metod(Katilimci_Cevaplari, i); CVPSZCT_TOPLAM += CVPSZCT; ARADCT = 1 - ECT; ARADCT_TOPLAM += ARADCT; Sonuclar[(i - 1), 8] = ARADCT; EKOCT_TOPLAM += EKOCT; EKCT_TOPLAM += EKCT; } if (Hata_Yakalama == false)//bir hata ile karşılaşılmadı ise devam eder. { Excel.Worksheet ws1 = Globals.ThisAddIn.Application.ActiveSheet; Excel.Range range = (Excel.Range)ws1.Range[ws1.Cells[2, Madde_Sayisi + 3], ws1.Cells[Katilimci_Sayisi + 1, Madde_Sayisi + 11]]; range.Value2 = Sonuclar; //her formül için ortalama değerleri bulunur. EKOCT_TOPLAM = EKOCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 5, EKOCT_TOPLAM.ToString("0.#####")); EKCT_TOPLAM = EKCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 4, EKCT_TOPLAM.ToString("0.#####")); Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 3, (EKCT_TOPLAM + EKOCT_TOPLAM).ToString("0.#####")); KCT_TOPLAM = KCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 6, KCT_TOPLAM.ToString("0.#####")); KOCT_TOPLAM = KOCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 7, KOCT_TOPLAM.ToString("0.#####")); NKCT_TOPLAM = NKCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 8, NKCT_TOPLAM.ToString("0.#####")); ONCT_TOPLAM = ONCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 9, ONCT_TOPLAM.ToString("0.#####")); CVPSZCT_TOPLAM = CVPSZCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 10, CVPSZCT_TOPLAM.ToString("0.#####")); ARADCT_TOPLAM = ARADCT_TOPLAM / Katilimci_Sayisi; Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 11, ARADCT_TOPLAM.ToString("0.#####")); //Hücreleri kilitleme ve renk ayarı. ArrayList harfler = new ArrayList(); //kullanılmayan alanlar kullanıma kapatılır. harfler.Add("A"); harfler.Add("B"); harfler.Add("C"); harfler.Add("D"); harfler.Add("E"); harfler.Add("F"); harfler.Add("G"); harfler.Add("H"); harfler.Add("I"); harfler.Add("J"); harfler.Add("K"); harfler.Add("L"); harfler.Add("M"); harfler.Add("N"); harfler.Add("O"); harfler.Add("P"); harfler.Add("Q"); harfler.Add("R"); harfler.Add("S"); harfler.Add("T"); harfler.Add("U"); harfler.Add("V"); harfler.Add("W"); harfler.Add("X"); harfler.Add("Y"); harfler.Add("Z"); harfler.Add("AA"); harfler.Add("AB"); harfler.Add("AC"); harfler.Add("AD"); harfler.Add("AE"); harfler.Add("AF"); harfler.Add("AG"); harfler.Add("AH"); harfler.Add("AI"); harfler.Add("AJ"); harfler.Add("AK"); harfler.Add("AL"); harfler.Add("AM"); harfler.Add("AN"); harfler.Add("AO"); harfler.Add("AP"); harfler.Add("AQ"); harfler.Add("AR"); harfler.Add("AS"); harfler.Add("AT"); harfler.Add("AU"); harfler.Add("AV"); harfler.Add("AW"); harfler.Add("AX"); harfler.Add("AY"); harfler.Add("AZ"); harfler.Add("BA"); harfler.Add("BB"); harfler.Add("BC"); harfler.Add("BD"); harfler.Add("BE"); harfler.Add("BF"); harfler.Add("BG"); harfler.Add("BH"); harfler.Add("BI"); harfler.Add("BJ"); harfler.Add("BK"); harfler.Add("BL"); harfler.Add("BM"); harfler.Add("BN"); harfler.Add("BO"); harfler.Add("BP"); harfler.Add("BQ"); harfler.Add("BR"); harfler.Add("BS"); harfler.Add("BT"); string sutun = harfler[Madde_Sayisi + 10].ToString() + (Katilimci_Sayisi + 2).ToString(); Excel.Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet; Excel.Range fr; fr = ws.get_Range("A1", sutun); fr.Borders.Weight = 2; fr.Borders.Color = Color.Black; fr.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.LightBlue); Globals.ThisAddIn.Application.Cells.Locked = true; Globals.ThisAddIn.Application.get_Range("A1", sutun).Locked = false; Excel.Worksheet Sheet1 = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet; Sheet1.Protect(); } } else { MessageBox.Show("Hesaplama İşlemini '" + IslemSayfasi + "' İçinde Yapabilirsiniz.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
public void MissingTemizlemeMetodu() { MTemizlemeFormu.progressBar1.Maximum = Convert.ToInt32(Katilimci_Sayisi) * Convert.ToInt32(Madde_Sayisi); //progressbar ın maksimum değerini atar. if (Convert.ToInt32(Secilen_Liste.Count.ToString()) > 0) //Veri temizleme yapmak için en az bir tane soru seçilmesi gerekir. { MTemizlemeFormu.progressBar1.Value = 0; //işlem sürecini gösteren progressbar sıfırlanır. MTemizlemeFormu.Cursor = Cursors.No; Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet); string AktifSayfa = activeWorksheet.Name; //aktif sayfanın adına ulaşır. if (IslemSayfasi == AktifSayfa) //aktif sayfa işlem sayfası ise yani verilerin girildiği sayfa açık ise veri temizleme işlemine devam eder. { SilinecekIndisler = new ArrayList(); for (int i = 1; i <= Katilimci_Sayisi; i++) { //Her satır için. for (int j = 1; j <= Madde_Sayisi; j++) //Her Sütun için. { //Her hücredeki değer için tek tek bakar ve seçilen listede varsa indis numarasını tutar. string siradaki = Hucrelerden_Okuma_Metodu((i + 1), (j + 1)); if (siradaki == "0" || siradaki == "") { int kolon = j, flag = 0; foreach (var item in Secilen_Liste) { if (kolon.ToString() == item.ToString()) { flag++; } } if (flag > 0) { SilinecekIndisler.Add(i); break; } } try { MTemizlemeFormu.progressBar1.Value++; } catch { } } } MTemizlemeFormu.progressBar1.Maximum += SilinecekIndisler.Count * Madde_Sayisi;//işlem sürecini gösteren progressbarı günceller. int[,] sil = new int[Convert.ToInt32(SilinecekIndisler.Count), Madde_Sayisi]; for (int i = 0; i < SilinecekIndisler.Count; i++) { for (int j = 0; j < Madde_Sayisi; j++) {//silinecek verileri bir dizide tutar. sil[i, j] = Convert.ToInt32(Hucrelerden_Okuma_Metodu((Convert.ToInt32(SilinecekIndisler[i]) + 1), (j + 2))); try { MTemizlemeFormu.progressBar1.Value++; } catch { } } } MTemizlemeFormu.progressBar1.Maximum += SilinecekIndisler.Count; //işlem sürecini gösteren progressbarı günceller. Excel.Worksheet wsSil = Globals.ThisAddIn.Application.ActiveSheet; //aktif sayfaya ulaşır. Excel.Range row; int[] rows = new int[SilinecekIndisler.Count]; int SilinenSayac = 0;//bu değişkenin amacı excelde bir satır silinince yerine bir alttaki satır geldiği için listedeki silinecek satırdan //daha önce silinenlerin sayısını çıkartmamız gerekir. foreach (var item in SilinecekIndisler) {//silinecek indisleri bulup siler. row = (Excel.Range)wsSil.Rows[Convert.ToInt32(item.ToString()) + 1 - SilinenSayac]; row.Delete(); SilinenSayac++; try { MTemizlemeFormu.progressBar1.Value++; } catch { } } if (SilinenSayac > 0) //devam etmesi için en az 1 tane veri silinmesi gerekir. { Katilimci_Sayisi = Katilimci_Sayisi - SilinecekIndisler.Count; //katılımcı sayısı silinenler olduğu için güncellenir. Excel.Workbook wsTemizlenenler = Globals.ThisAddIn.Application.ActiveWorkbook; Excel.Worksheet x = wsTemizlenenler.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); x.Name = YeniTemizlenenlerSayfasiOlustur();//yeni bir temizlenenler sayfası oluşturulur. for (int i = 0; i < SilinecekIndisler.Count; i++) { for (int j = 0; j < Madde_Sayisi; j++) {//yeni temizlenenler sayfasına silinecek veriler taşınır. x.Cells[i + 1, j + 1] = sil[i, j].ToString(); try { MTemizlemeFormu.progressBar1.Value++; } catch { } } } if (MTemizlemeFormu.progressBar1.Value < MTemizlemeFormu.progressBar1.Maximum) { for (int i = MTemizlemeFormu.progressBar1.Value; i < MTemizlemeFormu.progressBar1.Maximum; i++) { MTemizlemeFormu.progressBar1.Value++; } } MTemizlemeFormu.Cursor = Cursors.Default; //Bilgi mesajı verir. MessageBox.Show(SilinecekIndisler.Count.ToString() + " Veri " + x.Name.ToString() + " Sayfasına Taşındı.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information); } else {//Hiç veri silinmedi ise bilgi verir. MTemizlemeFormu.Cursor = Cursors.Default; MessageBox.Show("Silinecek Hiç Veri Bulunamadı."); if (MTemizlemeFormu.progressBar1.Value < MTemizlemeFormu.progressBar1.Maximum) { for (int i = MTemizlemeFormu.progressBar1.Value; i < MTemizlemeFormu.progressBar1.Maximum; i++) { MTemizlemeFormu.progressBar1.Value++; } } } } else { MessageBox.Show("Veri Temizleme İşlemini '" + IslemSayfasi + "' İçinde Yapabilirsiniz.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information); } } else { MessageBox.Show("Madde Listesinden Seçim Yapınız.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
/// <summary> /// The sourcesheet and the destsheet need to be in the same workbook /// </summary> /// <param name="sourceSheet"></param> /// <param name="cellBegin"></param> /// <param name="cellEnd"></param> /// <param name="destSheet"></param> /// <param name="positionInsert"></param> public static void InsertRange(Excel.Worksheet sourceSheet, string cellBegin, string cellEnd, Excel.Worksheet destSheet, string positionInsert) { Excel.Range destRange = destSheet.Range[positionInsert]; Excel.Range sourceRange = sourceSheet.Range[cellBegin, cellEnd]; sourceRange.EntireColumn.Select(); destRange.EntireColumn.Insert(Excel.XlInsertShiftDirection.xlShiftToRight); Excel.Range destRange1 = destSheet.Range[positionInsert]; sourceRange.Copy(destRange1); ReleaseComObject(destRange); ReleaseComObject(sourceRange); ReleaseComObject(destRange1); }
private void montaCabeceras(int fila, ref Excel.Worksheet hoja, string nombreCabecera) { try { Excel.Range rango; int cantColumnas = tablaProcesadoresGeneracion.Rows.Count + 2; //** Montamos el título en la línea 1 ** hoja.Cells[fila, 1] = nombreCabecera; hoja.Range[hoja.Cells[fila, 1], hoja.Cells[fila, cantColumnas]].Merge(); hoja.Range[hoja.Cells[fila, 1], hoja.Cells[fila, cantColumnas]].Interior.Color = Color.FromArgb(196, 89, 19);//FromArgb(255, 132, 0) hoja.Range[hoja.Cells[fila, 1], hoja.Cells[fila, cantColumnas]].Style.Font.Bold = true; hoja.Range[hoja.Cells[fila + 1, 1], hoja.Cells[fila + 1, cantColumnas - 1]].Interior.Color = Color.FromArgb(196, 89, 19); hoja.Range[hoja.Cells[fila + 1, cantColumnas], hoja.Cells[fila + 1, cantColumnas]].Interior.Color = Color.FromArgb(130, 61, 10); hoja.Range[hoja.Cells[fila + 2, 1], hoja.Cells[fila + 2, cantColumnas]].Interior.Color = Color.FromArgb(252, 228, 215); hoja.Range[hoja.Cells[fila + 3 + cantModeloProcesador, 1], hoja.Cells[fila + 3 + cantModeloProcesador, cantColumnas]].Interior.Color = Color.FromArgb(252, 228, 215); //Centramos los textos rango = hoja.Rows[fila]; rango.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; for (int k = 0; k < cantGeneraciones; k++) { string genPro = tablaProcesadoresGeneracion.Rows[k]["descripcion"].ToString(); hoja.Cells[fila + 1, k + 2] = genPro; } hoja.Cells[fila + 1, cantGeneraciones + 2] = "Total en Almacen"; hoja.Cells[fila + 1, 1] = "Generación"; hoja.Cells[fila + 2, 1] = "LAPTOP"; for (int k = 0; k < cantModeloProcesador; k++) { string modPro = tablaProcesadoresModelos.Rows[k]["nombre"].ToString(); hoja.Cells[fila + 3 + k, 1] = modPro; } hoja.Cells[fila + 3 + cantModeloProcesador, 1] = "MACKBOOK"; for (int k = 0; k < cantModeloProcesador; k++) { string modPro = tablaProcesadoresModelos.Rows[k]["nombre"].ToString(); hoja.Cells[fila + 3 + cantModeloProcesador + 1 + k, 1] = modPro; } int fila2 = fila + 2; rango = hoja.Rows[fila2]; rango.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; for (int j = 0; j < cantColumnas + 2; j++) { rango = hoja.Columns[j + 1]; rango.ColumnWidth = 15; } rango = hoja.Rows[fila + 3 + cantModeloProcesador]; rango.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; for (int j = 0; j < cantColumnas + 2; j++) { rango = hoja.Columns[j + 1]; rango.ColumnWidth = 15; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Error de redondeo", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void btRecorder_Click(object sender, EventArgs e) //记录数据 { #region 获取网站的中的数据,并存到 List 中 webBrowser1.Url = new System.Uri("http://219.140.196.71/sq/data/sc.action?scid=cjh.sq"); //刷新网站 string strText = webBrowser1.Document.Body.InnerText; if (strText == null) { return; } int indexFront = strText.IndexOf("寸滩"); //int indexAfter = strText.IndexOf("防汛测报"); string text = null; if (indexFront > 0) { text = strText.Substring(indexFront); //获取有用的部分 } else { return; } string[] strLines = new string[20]; strLines = text.Split(new char[] { '\n' }); //将内容按回车分割成数组 List <Info> content = new List <Info>(); //获取数据的每一个部分 for (int i = 0; i < strLines.Length; i++) { string[] parts = new string[6]; //一般是六部分 if (strLines[i].Length < 6) { break; } parts = strLines[i].Split(new char[] { ' ' }); //分成三部分:名称+日期+数据 Info inner = new Info(); //名称内容 inner.name = parts[0]; if (inner.name == "茅坪(二)" || inner.name == "龙王庙") { continue; //这两个不加入,直接跳过! } //日期内容 inner.date = parts[1] + parts[2] + parts[3]; //水位数据,基本都有 inner.water = parts[4]; //流量数据,有时候没有为“-”,有时候存在负数,判断存在“-”,并且并且“-”后面没有内容时候为空值 if (!parts[5].Contains('-')) { inner.stream = parts[5]; } else { inner.stream = "0"; } if (inner.stream.Contains('(')) //包括(入) 和 (出) { inner.stream = inner.stream + strLines[i + 1]; //将下一行的(出)也加进来,然后i自加! i++; } else if (inner.name.Contains('-')) { inner.stream = "0"; } content.Add(inner); } #endregion int countDay = 0; int countNight = 0; foreach (Info info in content) { if (info.date.Contains("8时")) //获取8时的数据 { countDay++; } else if (info.date.Contains("20时")) { countNight++; } } #region 定义 lunarMonth 和 lunarDay Hashtable lunarMonth = new Hashtable(); lunarMonth.Add(1, "正"); lunarMonth.Add(2, "二"); lunarMonth.Add(3, "三"); lunarMonth.Add(4, "四"); lunarMonth.Add(5, "五"); lunarMonth.Add(6, "六"); lunarMonth.Add(7, "七"); lunarMonth.Add(8, "八"); lunarMonth.Add(9, "九"); lunarMonth.Add(10, "十"); lunarMonth.Add(11, "十一"); lunarMonth.Add(12, "十二"); Hashtable lunarDay = new Hashtable(); lunarDay.Add(1, "初一"); lunarDay.Add(2, "初二"); lunarDay.Add(3, "初三"); lunarDay.Add(4, "初四"); lunarDay.Add(5, "初五"); lunarDay.Add(6, "初六"); lunarDay.Add(7, "初七"); lunarDay.Add(8, "初八"); lunarDay.Add(9, "初九"); lunarDay.Add(10, "初十"); lunarDay.Add(11, "十一"); lunarDay.Add(12, "十二"); lunarDay.Add(13, "十三"); lunarDay.Add(14, "十四"); lunarDay.Add(15, "十五"); lunarDay.Add(16, "十六"); lunarDay.Add(17, "十七"); lunarDay.Add(18, "十八"); lunarDay.Add(19, "十九"); lunarDay.Add(20, "二十"); lunarDay.Add(21, "二十一"); lunarDay.Add(22, "二十二"); lunarDay.Add(23, "二十三"); lunarDay.Add(24, "二十四"); lunarDay.Add(25, "二十五"); lunarDay.Add(26, "二十六"); lunarDay.Add(27, "二十七"); lunarDay.Add(28, "二十八"); lunarDay.Add(29, "二十九"); lunarDay.Add(30, "三十"); #endregion if (countDay == 11) //countDay == 11 { #region 8时 string excelPath = saveFilePath; eWorkbook = ex.Workbooks.Open(excelPath); //打开文件,赋值到工作簿 ex.Visible = false; //程序显示 eWorksheet = (Excel.Worksheet)eWorkbook.Sheets[1]; //获取第一个工作表 //新格式 string date = DateTime.Today.Month + "月" + DateTime.Today.Day + "日"; ChineseLunisolarCalendar lunar = new ChineseLunisolarCalendar(); string dateLunar = lunarMonth[GetMonthFromYear(DateTime.Today.Date)] + "月" + lunarDay[lunar.GetDayOfMonth(DateTime.Today.Date)]; int row = ex.Application.get_Range("B65535", Type.Missing).get_End(Excel.XlDirection.xlUp).Row; eWorksheet.get_Range("A" + (row + 1).ToString()).Value = date; eWorksheet.get_Range("B" + (row + 1).ToString()).Value = DateTime.Now.Hour + "时"; eWorksheet.get_Range("C" + (row + 1).ToString()).Value = dateLunar; eWorksheet.get_Range("D" + (row + 1).ToString()).Value = Convert.ToSingle(content[0].water); if (content[0].stream != "0") { eWorksheet.get_Range("E" + (row + 1).ToString()).Value = Convert.ToSingle(content[0].stream); } else { eWorksheet.get_Range("E" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("F" + (row + 1).ToString()).Value = Convert.ToSingle(content[1].water); if (content[1].stream != "0") { eWorksheet.get_Range("G" + (row + 1).ToString()).Value = Convert.ToSingle(content[1].stream); } else { eWorksheet.get_Range("G" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("H" + (row + 1).ToString()).Value = Convert.ToSingle(content[2].water); if (content[2].stream != "0") { eWorksheet.get_Range("I" + (row + 1).ToString()).Value = Convert.ToSingle(content[2].stream); } else { eWorksheet.get_Range("I" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("J" + (row + 1).ToString()).Value = Convert.ToSingle(content[3].water); if (content[3].stream != "0") { eWorksheet.get_Range("K" + (row + 1).ToString()).Value = Convert.ToSingle(content[3].stream); } else { eWorksheet.get_Range("K" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("L" + (row + 1).ToString()).Value = Convert.ToSingle(content[4].water); if (content[4].stream != "0") { eWorksheet.get_Range("M" + (row + 1).ToString()).Value = Convert.ToSingle(content[4].stream); } else { eWorksheet.get_Range("M" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("N" + (row + 1).ToString()).Value = Convert.ToSingle(content[5].water); if (content[5].stream != "0") { eWorksheet.get_Range("O" + (row + 1).ToString()).Value = Convert.ToSingle(content[5].stream); } else { eWorksheet.get_Range("O" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("P" + (row + 1).ToString()).Value = Convert.ToSingle(content[6].water); if (content[6].stream != "0") { eWorksheet.get_Range("Q" + (row + 1).ToString()).Value = Convert.ToSingle(content[6].stream); } else { eWorksheet.get_Range("Q" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("R" + (row + 1).ToString()).Value = Convert.ToSingle(content[7].water); if (content[7].stream != "0") { eWorksheet.get_Range("S" + (row + 1).ToString()).Value = Convert.ToSingle(content[7].stream); } else { eWorksheet.get_Range("S" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("T" + (row + 1).ToString()).Value = Convert.ToSingle(content[8].water); if (content[8].stream != "0") { eWorksheet.get_Range("U" + (row + 1).ToString()).Value = Convert.ToSingle(content[8].stream); } else { eWorksheet.get_Range("U" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("V" + (row + 1).ToString()).Value = Convert.ToSingle(content[9].water); if (content[9].stream.Contains('(')) { int inFront = content[9].stream.IndexOf('('); int inBack = content[9].stream.IndexOf(')'); int outFront = content[9].stream.LastIndexOf('('); string streamIn = content[9].stream.Substring(0, inFront); string streamOut = content[9].stream.Substring(inBack + 1, outFront - inBack - 1); eWorksheet.get_Range("W" + (row + 1).ToString()).Value = Convert.ToSingle(streamOut); eWorksheet.get_Range("X" + (row + 1).ToString()).Value = Convert.ToSingle(streamIn); } else { eWorksheet.get_Range("W" + (row + 1).ToString()).Value = Convert.ToSingle(content[9].stream); } eWorksheet.get_Range("Y" + (row + 1).ToString()).Value = Convert.ToSingle(content[10].water); if (content[10].stream.Contains('(')) { int inFront = content[10].stream.IndexOf('('); int inBack = content[10].stream.IndexOf(')'); int outFront = content[10].stream.LastIndexOf('('); string streamIn = content[10].stream.Substring(0, inFront); string streamOut = content[10].stream.Substring(inBack + 1, outFront - inBack - 1); eWorksheet.get_Range("Z" + (row + 1).ToString()).Value = Convert.ToSingle(streamOut); eWorksheet.get_Range("AA" + (row + 1).ToString()).Value = Convert.ToSingle(streamIn); } else { eWorksheet.get_Range("Z" + (row + 1).ToString()).Value = Convert.ToSingle(content[10].stream); } eWorkbook.Save(); ex.Quit(); this.Focus(); timer2.Enabled = false; lbTimer2.Text = "计时器2关闭!"; timer1.Enabled = true; lbTimer1.Text = "计时器1开启!"; label4.Text = DateTime.Now.ToShortDateString() + " 8时 记录完毕!"; countRecorderDay++; #endregion } if (countNight == 11) { #region 20时 string excelPath = saveFilePath; eWorkbook = ex.Workbooks.Open(excelPath); //打开文件,赋值到工作簿 ex.Visible = false; //程序显示 eWorksheet = (Excel.Worksheet)eWorkbook.Sheets[1]; //获取第一个工作表 //新格式 string date = DateTime.Today.Month + "月" + DateTime.Today.Day + "日"; ChineseLunisolarCalendar lunar = new ChineseLunisolarCalendar(); string dateLunar = lunarMonth[GetMonthFromYear(DateTime.Today.Date)] + "月" + lunarDay[lunar.GetDayOfMonth(DateTime.Today.Date)]; int row = ex.Application.get_Range("B65535", Type.Missing).get_End(Excel.XlDirection.xlUp).Row; //eWorksheet.get_Range("A" + (row + 1).ToString()).Value = date; eWorksheet.get_Range("B" + (row + 1).ToString()).Value = DateTime.Now.Hour + "时"; //eWorksheet.get_Range("C" + (row + 1).ToString()).Value = dateLunar; eWorksheet.get_Range("D" + (row + 1).ToString()).Value = Convert.ToSingle(content[0].water); if (content[0].stream != "0") { eWorksheet.get_Range("E" + (row + 1).ToString()).Value = Convert.ToSingle(content[0].stream); } else { eWorksheet.get_Range("E" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("F" + (row + 1).ToString()).Value = Convert.ToSingle(content[1].water); if (content[1].stream != "0") { eWorksheet.get_Range("G" + (row + 1).ToString()).Value = Convert.ToSingle(content[1].stream); } else { eWorksheet.get_Range("G" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("H" + (row + 1).ToString()).Value = Convert.ToSingle(content[2].water); if (content[2].stream != "0") { eWorksheet.get_Range("I" + (row + 1).ToString()).Value = Convert.ToSingle(content[2].stream); } else { eWorksheet.get_Range("I" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("J" + (row + 1).ToString()).Value = Convert.ToSingle(content[3].water); if (content[3].stream != "0") { eWorksheet.get_Range("K" + (row + 1).ToString()).Value = Convert.ToSingle(content[3].stream); } else { eWorksheet.get_Range("K" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("L" + (row + 1).ToString()).Value = Convert.ToSingle(content[4].water); if (content[4].stream != "0") { eWorksheet.get_Range("M" + (row + 1).ToString()).Value = Convert.ToSingle(content[4].stream); } else { eWorksheet.get_Range("M" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("N" + (row + 1).ToString()).Value = Convert.ToSingle(content[5].water); if (content[5].stream != "0") { eWorksheet.get_Range("O" + (row + 1).ToString()).Value = Convert.ToSingle(content[5].stream); } else { eWorksheet.get_Range("O" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("P" + (row + 1).ToString()).Value = Convert.ToSingle(content[6].water); if (content[6].stream != "0") { eWorksheet.get_Range("Q" + (row + 1).ToString()).Value = Convert.ToSingle(content[6].stream); } else { eWorksheet.get_Range("Q" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("R" + (row + 1).ToString()).Value = Convert.ToSingle(content[7].water); if (content[7].stream != "0") { eWorksheet.get_Range("S" + (row + 1).ToString()).Value = Convert.ToSingle(content[7].stream); } else { eWorksheet.get_Range("S" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("T" + (row + 1).ToString()).Value = Convert.ToSingle(content[8].water); if (content[8].stream != "0") { eWorksheet.get_Range("U" + (row + 1).ToString()).Value = Convert.ToSingle(content[8].stream); } else { eWorksheet.get_Range("U" + (row + 1).ToString()).Value = ""; } eWorksheet.get_Range("V" + (row + 1).ToString()).Value = Convert.ToSingle(content[9].water); if (content[9].stream.Contains('(')) { int inFront = content[9].stream.IndexOf('('); int inBack = content[9].stream.IndexOf(')'); int outFront = content[9].stream.LastIndexOf('('); string streamIn = content[9].stream.Substring(0, inFront); string streamOut = content[9].stream.Substring(inBack + 1, outFront - inBack - 1); eWorksheet.get_Range("W" + (row + 1).ToString()).Value = Convert.ToSingle(streamOut); eWorksheet.get_Range("X" + (row + 1).ToString()).Value = Convert.ToSingle(streamIn); } else { eWorksheet.get_Range("W" + (row + 1).ToString()).Value = Convert.ToSingle(content[9].stream); } eWorksheet.get_Range("Y" + (row + 1).ToString()).Value = Convert.ToSingle(content[10].water); if (content[10].stream.Contains('(')) { int inFront = content[10].stream.IndexOf('('); int inBack = content[10].stream.IndexOf(')'); int outFront = content[10].stream.LastIndexOf('('); string streamIn = content[10].stream.Substring(0, inFront); string streamOut = content[10].stream.Substring(inBack + 1, outFront - inBack - 1); eWorksheet.get_Range("Z" + (row + 1).ToString()).Value = Convert.ToSingle(streamOut); eWorksheet.get_Range("AA" + (row + 1).ToString()).Value = Convert.ToSingle(streamIn); } else { eWorksheet.get_Range("Z" + (row + 1).ToString()).Value = Convert.ToSingle(content[10].stream); } eWorkbook.Save(); ex.Quit(); this.Focus(); //Application.Exit(); timer2.Enabled = false; lbTimer2.Text = "计时器2关闭!"; timer1.Enabled = true; lbTimer1.Text = "计时器1开启!"; label4.Text = DateTime.Now.ToShortDateString() + " 20时 记录完毕!"; countRecorderNight++; #endregion } }
/// <summary> /// The sourcesheet and the destsheet need to be in the same workbook /// </summary> /// <param name="sourceSheet"></param> /// <param name="cellBegin"></param> /// <param name="cellEnd"></param> /// <param name="destSheet"></param> /// <param name="positionInsert"></param> public static void CopyRange(Excel.Worksheet sourceSheet, string cellBegin, string cellEnd, Excel.Worksheet destSheet, string positionInsert) { Excel.Range destRange = destSheet.Range[positionInsert]; Excel.Range sourceRange = sourceSheet.Range[cellBegin, cellEnd]; sourceRange.Select(); sourceRange.Copy(destRange); ReleaseComObject(destRange); ReleaseComObject(sourceRange); }
/// <summary> /// !!!!!!!!!!!!!!!!!!! /// 注意:该函数存在问题!未进行完善 /// 写入excel数据 /// </summary> /// <param name="ent">预警规则实体</param> /// <param name="selectedRowID">选择行编号</param> /// <returns>是否插入成功?true:false</returns> public static bool updateInfoToExcelSheet(PreWarningRules ent, int selectedRowID, string sheetName) { bool ret = false; Excel.Application objExcelApp = null; Excel.Workbooks objExcelWorkBooks = null; Excel.Workbook objExcelWorkBook = null; Excel.Worksheet objExcelWorkSheet = null; try { objExcelApp = new Excel.ApplicationClass(); objExcelWorkBooks = objExcelApp.Workbooks; objExcelWorkBook = objExcelWorkBooks.Open(_excelFilePath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); objExcelWorkSheet = (Worksheet)objExcelWorkBook.Worksheets[sheetName]; objExcelWorkSheet.Select(Type.Missing); Worksheet objExcelWorkSheetTemp = (Worksheet)objExcelApp.ActiveSheet; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 1] = ent.RuleCode; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 2] = ent.RuleType; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 3] = ent.WarningType; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 4] = ent.WarningLevel; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 5] = ent.SuitableLocation; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 6] = ent.RuleDescription; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 7] = ent.IndicatorType; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 8] = ent.Operator; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 9] = ent.ModifyDate.ToString(); objExcelWorkSheetTemp.Cells[selectedRowID + 1, 10] = ent.Remarks;//MARK FIELD //objExcelApp.Visible = true; objExcelApp.DisplayAlerts = false; objExcelApp.SaveWorkspace(); ret = true; } catch (Exception ex) { Alert.alert(ex.Message); ret = false; } finally { if (objExcelApp != null) { objExcelApp.Quit(); GC.Collect();//不加该行代码Excel不会退出! } } return(ret); }
public CodeReviewSheet(ExcelInterop.Worksheet sheet) : base(sheet) { this.sheet = sheet; }
public void ExportarDataGridViewExcel(ref Excel.Worksheet hoja_trabajo, string nombreCabecera) { Excel.Range rango; int cantColumnas = tablaProcesadoresGeneracion.Rows.Count + 3; int filas = vista.RowCount; int k = cantColumnas - 1 + 64; char columF = (char)k; int fila2 = 3; string filaBorde = fila2.ToString(); char columI = 'A'; //Ponemos borde a las celdas rango = hoja_trabajo.Range[columI + fila2.ToString(), columF + fila2.ToString()]; rango.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; rango.Style.Font.Bold = false; for (int i = 1; i <= this.cantModeloProcesador * 2 + 4; i++) { rango = hoja_trabajo.Range[columI + i.ToString(), columF + i.ToString()]; rango.Borders.LineStyle = Excel.XlLineStyle.xlContinuous; } for (int i = 0; i < this.cantGeneraciones; i++) { int cantGenLCGeneral = 0; int cantGenLCApple = 0; for (int j = 0; j < this.cantModeloProcesador; j++) { string generacion = tablaProcesadoresGeneracion.Rows[i]["descripcion"].ToString(); string nombreProcesador = tablaProcesadoresModelos.Rows[j]["nombre"].ToString(); hoja_trabajo.Cells[fila2 + 1 + j, i + 2] = arregloLCGeneral[i][j]; hoja_trabajo.Cells[fila2 + 1 + cantModeloProcesador + 1 + j, i + 2] = arregloLCApple[i][j]; cantGenLCGeneral += arregloLCGeneral[i][j]; cantGenLCApple += arregloLCApple[i][j]; } hoja_trabajo.Cells[fila2, i + 2] = cantGenLCGeneral; hoja_trabajo.Cells[fila2 + cantModeloProcesador + 1, i + 2] = cantGenLCApple; } for (int j = 0; j < this.cantModeloProcesador; j++) { int cantProLCGeneral = 0; int cantProLCApple = 0; for (int i = 0; i < this.cantGeneraciones; i++) { cantProLCGeneral += arregloLCGeneral[i][j]; cantProLCApple += arregloLCApple[i][j]; } hoja_trabajo.Cells[fila2 + 1 + j, cantGeneraciones + 2] = cantProLCGeneral; hoja_trabajo.Cells[fila2 + cantModeloProcesador + 1 + 1 + j, cantGeneraciones + 2] = cantProLCApple; } var cantidadLCGeneral = new BindingList <LC>(laptops.Where(p => p.IdMarca != this.idMarcaAppleLC && p.IdMarca != this.idMarcaApplePC && p.Estado == this.estadoDisponible).ToList()); var cantidadLCApple = new BindingList <LC>(laptops.Where(p => p.IdMarca == this.idMarcaAppleLC && p.IdMarca != this.idMarcaApplePC && p.Estado == this.estadoDisponible).ToList()); hoja_trabajo.Cells[fila2, cantGeneraciones + 2] = cantidadLCGeneral.Count; hoja_trabajo.Cells[fila2 + cantModeloProcesador + 1, cantGeneraciones + 2] = cantidadLCApple.Count; montaCabeceras(1, ref hoja_trabajo, nombreCabecera); }
private void btnExeclReport_Click(object sender, EventArgs e) { string path = ""; FolderBrowserDialog fbd = new FolderBrowserDialog(); if (fbd.ShowDialog() == DialogResult.OK) { path = fbd.SelectedPath; } else { return; } string filePath = Application.StartupPath + "\\Templates\\PhongKeToan\\BangTheoDoiChungTu.xlsx"; string currentPath = path + "\\BangTheoDoiChungTu.xlsx"; try { File.Copy(filePath, currentPath, true); } catch (Exception ex) { MessageBox.Show("Có lỗi khi tạo báo cáo!" + Environment.NewLine + ex.Message, TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop); return; } using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo báo cáo...")) { System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Excel.Application app = default(Excel.Application); Excel.Workbook workBoook = default(Excel.Workbook); Excel.Worksheet workSheet = default(Excel.Worksheet); try { app = new Excel.Application(); app.Workbooks.Open(currentPath); workBoook = app.Workbooks[1]; workSheet = (Excel.Worksheet)workBoook.Worksheets[1]; for (int i = grvData.RowCount - 1; i >= 0; i--) { string number = TextUtils.ToString(grvData.GetRowCellValue(i, colNumber)); workSheet.Cells[6, 1] = i + 1; workSheet.Cells[6, 2] = number; workSheet.Cells[6, 3] = TextUtils.ToString(grvData.GetRowCellValue(i, colItemCode)); workSheet.Cells[6, 4] = TextUtils.ToString(grvData.GetRowCellValue(i, colItemName)); workSheet.Cells[6, 5] = TextUtils.ToString(grvData.GetRowCellValue(i, colVoucherName)); workSheet.Cells[6, 6] = TextUtils.ToString(grvData.GetRowCellValue(i, colUserName)); workSheet.Cells[6, 7] = TextUtils.ToString(grvData.GetRowCellDisplayText(i, colCreatedDate)); workSheet.Cells[6, 8] = TextUtils.ToString(grvData.GetRowCellDisplayText(i, colCompletedDateDK)); if (number != "") { ((Excel.Range)workSheet.Rows[6]).Insert(); } } ((Excel.Range)workSheet.Rows[5]).Delete(); ((Excel.Range)workSheet.Rows[5]).Delete(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (app != null) { app.ActiveWorkbook.Save(); app.Workbooks.Close(); app.Quit(); } } Process.Start(currentPath); } }
public static void GenerateInfoMapByName(Excel.Workbook book) { int rowIndex = 1; int sheetIndex = 1; Excel.Worksheet sheet = book.Sheets[sheetIndex]; while ((!sheet.Name.Contains("资料")) && (sheetIndex++ < book.Sheets.Count)) { sheet = book.Sheets[sheetIndex]; } int nameColumn = 0; int companyColumn = 0; int managerColumn = 0; int costProjColumn = 0; int bizProjColumn = 0; int workTypeColumn = 0; int rankColumn = 0; int departmentColumn = 0; int enterDateColumn = 0; int leaveDateColumn = 0; #region locate column index for (int i = 1; i < 20; i++) { string cellValue = sheet.Cells[1, i].Value != null ? sheet.Cells[1, i].Value.ToString() : string.Empty; if (cellValue == "姓名") { nameColumn = i; } else if (!string.IsNullOrEmpty(cellValue) && cellValue.Contains("供应商名称")) { companyColumn = i; } else if (!string.IsNullOrEmpty(cellValue) && cellValue.Contains("项目经理")) { managerColumn = i; } else if (!string.IsNullOrEmpty(cellValue) && cellValue.Contains("人力成本归集")) { costProjColumn = i; } else if (!string.IsNullOrEmpty(cellValue) && cellValue.Contains("参与项目")) { bizProjColumn = i; } else if (!string.IsNullOrEmpty(cellValue) && cellValue.Contains("外包形式")) { workTypeColumn = i; } else if (cellValue == "职级") { rankColumn = i; } else if (cellValue == "所属中心") { departmentColumn = i; } else if (cellValue == "入场时间") { enterDateColumn = i; } else if (cellValue == "离场时间") { leaveDateColumn = i; } } // for (int i = 1; i < 20; i++) #endregion locate column index // // get cell values List <PersonInfo> personList = new List <PersonInfo>(); for (rowIndex = 1; rowIndex < 5000; rowIndex++) { PersonInfo info = new PersonInfo(); // name info.Name = Convert.ToString(sheet.Cells[rowIndex, nameColumn].Value); if (string.IsNullOrWhiteSpace(info.Name)) { continue; } // company info.Company = Convert.ToString(sheet.Cells[rowIndex, companyColumn].Value); // manager info.Manager = Convert.ToString(sheet.Cells[rowIndex, managerColumn].Value); // cost project info.CostProject = Convert.ToString(sheet.Cells[rowIndex, costProjColumn].Value); // business project info.BizProject = Convert.ToString(sheet.Cells[rowIndex, bizProjColumn].Value); // work type info.WorkType = Convert.ToString(sheet.Cells[rowIndex, workTypeColumn].Value); // Rank - 初、中、高、专家 info.Rank = Convert.ToString(sheet.Cells[rowIndex, rankColumn].Value); // department info.Department = Convert.ToString(sheet.Cells[rowIndex, departmentColumn].Value); // enter date info.OnboardDate = Convert.ToString(sheet.Cells[rowIndex, enterDateColumn].Value); // != null ? sheet.Cells[rowIndex, 10].Value.ToString() : null; // leave date info.DimissionDate = Convert.ToString(sheet.Cells[rowIndex, leaveDateColumn].Value); // != null ? sheet.Cells[rowIndex, 11].Value.ToString() : null; personList.Add(info); } // for (rowIndex = 1; rowIndex < 5000; rowIndex++) // // sort on onboard date by ascend personList.Sort( (x, y) => { if (string.IsNullOrWhiteSpace(x.OnboardDate)) { return(-1); // x - y < 0, put x ahead } return(x.OnboardDate.CompareTo(y.OnboardDate)); } ); // // map by name, exclude duplicate records. the latest one will take effect personList.ForEach(delegate(PersonInfo info) { lock (InfoMap) { InfoMap[info.Name] = info; } }); // correct data and set owner system //CorrectData(InfoMap.Values); }
public void UpdateSEPValidationCheckList(string adjustedDataSheetName) { try { Globals.ThisAddIn.lstSEPValidationValues = new List <SEPValidationValues>(); Excel.Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook; ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Sheets[adjustedDataSheetName]).Select(Type.Missing); Excel.Worksheet adjustedDataModelSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet; Excel.ListObject LO = (Excel.ListObject)adjustedDataModelSheet.ListObjects.get_Item(2); for (int x = 1; x <= LO.ListColumns.Count; x++) { string col = LO.ListColumns[x].Name; if (!col.StartsWith("Col")) //Need to remove this hack { object[,] row1 = new object[1, 6]; int y = 0; foreach (Excel.ListRow row in LO.ListRows) { row1[0, y] = ((Excel.Range)row.Range.Cells[x]).Value2.ToString(); y++; } SEPValidationValues newVal = new SEPValidationValues(col, x, 0, Convert.ToDouble(row1[0, 2]), Convert.ToDouble(row1[0, 1]), Convert.ToDouble(row1[0, 0]), Convert.ToDouble(row1[0, 3]), Convert.ToDouble(row1[0, 4]), Convert.ToDouble(row1[0, 5]), string.Empty, false); Globals.ThisAddIn.lstSEPValidationValues.Add(newVal); } } foreach (SEPValidationValues sepVals in Globals.ThisAddIn.lstSEPValidationValues) { int count = 0; foreach (KeyValuePair <string, string> itm in Globals.ThisAddIn.SelectedSourcesBestModelFormulas) { if (itm.Value.Contains(sepVals.IndependentVariable)) { count++; } } if (count > 0) { if ((sepVals.MinModel < sepVals.AvgReportYr && sepVals.AvgReportYr < sepVals.MaxModel) || (sepVals.Minus3DevVal < sepVals.AvgReportYr && sepVals.AvgReportYr < sepVals.Plus3DevVal)) { sepVals.SEPValidationCheck = "Pass"; } else { sepVals.SEPValidationCheck = "Fail"; } } else { sepVals.SEPValidationCheck = "Not Included In Model"; } } } catch { //TODO: Log Implementation } }
public static void Ler(Excel.Worksheet wsPlanilha, string tipoGUID) { Repository <LV_PLANILHA> repositoryPLanilha = new Repository <LV_PLANILHA>(); string cell = getColuna(3, 1); var funcao = wsPlanilha.get_Range(cell, cell).Text; cell = getColuna(4, 1); var descricaoPlanilha = wsPlanilha.get_Range(cell, cell).Text; LV_PLANILHA planilha = new LV_PLANILHA() { GUID = Guid.NewGuid().ToString(), GUID_TIPO = tipoGUID, NOME = wsPlanilha.Name, FUNCAO = funcao, DESCRICAO = descricaoPlanilha }; //if(planilha.NOME.Equals("Prédios - BAS CONSOLIDADO")) //{ //string pare = "Prédios - BAS CONSOLIDADO"; //} repositoryPLanilha.Insert(planilha); Repository <LV_GRUPO> repositoryGrupo = new Repository <LV_GRUPO>(); LV_GRUPO grupo = null; int ordenarGrupoItem = 0; int ordenarItemItem = 0; //for (int colIndex = 1; colIndex < 5; colIndex++) //{ int colIndex = 1; for (int rowIndex = 7; rowIndex < 80; rowIndex++) { cell = getColuna(rowIndex, colIndex); string texto = wsPlanilha.get_Range(cell, cell).Text; if (texto != "") { int ordenadorGrupo = 0; if (int.TryParse(texto, out ordenadorGrupo)) { if (grupo == null || grupo.ORDENADOR < ordenadorGrupo) { cell = getColuna(rowIndex, colIndex + 1); string nomeGrupo = wsPlanilha.get_Range(cell, cell).Text; grupo = new LV_GRUPO() { GUID = Guid.NewGuid().ToString(), ORDENADOR = ordenadorGrupo, GUID_PLANILHA = planilha.GUID, NOME = nomeGrupo }; repositoryGrupo.Insert(grupo); } } else if (int.TryParse(texto.Split('.')[0], out ordenarGrupoItem) && int.TryParse(texto.Split('.')[1], out ordenarItemItem)) { if (grupo.ORDENADOR.Equals(ordenarGrupoItem)) { cell = getColuna(rowIndex, colIndex + 1); string descricaoRevisao = wsPlanilha.get_Range(cell, cell).Text; if (descricaoRevisao != "") { Repository <LV_ITEM_REVISAO> repositoryItemRevisao = new Repository <LV_ITEM_REVISAO>(); LV_ITEM_REVISAO itemRevisao = new LV_ITEM_REVISAO() { GUID = Guid.NewGuid().ToString(), GUID_GRUPO = grupo.GUID, DESCRICAO = descricaoRevisao, ORDENADOR = ordenarItemItem }; repositoryItemRevisao.Insert(itemRevisao); } } } } } //} }
public void KontrolSorusuMetodu() { //Bu metodun amacı kullanıcının seçtiği maddeleri işaretleyen kullanıcıları temizlemek. KSoruFormu.progressBar1.Maximum = Convert.ToInt32(Katilimci_Sayisi) * Convert.ToInt32(Madde_Sayisi); if (Secilen_Liste.Count > 0) //en az 1 tane madde seçilmesi gerekir. { KSoruFormu.progressBar1.Value = 0; KSoruFormu.Cursor = Cursors.No; Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet); string AktifSayfa = activeWorksheet.Name; if (AktifSayfa == IslemSayfasi)//Doğru Sayfada Hesaplama Yapması İçin Kullanılır. { SilinecekIndisler = new ArrayList(); for (int i = 1; i <= Katilimci_Sayisi; i++) { //Bütün satırlar için. for (int j = 1; j <= Madde_Sayisi; j++) { //Her bir satırın sütunları için. string siradaki = Hucrelerden_Okuma_Metodu((i + 1), (j + 1)); if (siradaki != "0") {//sıradaki hücre sıfırdan farklı ise yani kullanıcı soruyu işaretlemiş ise o satırın indis numarası tutulur. int flag = 0; foreach (var item in Secilen_Liste) { if (j.ToString() == item.ToString()) { flag++; } } if (flag > 0) { SilinecekIndisler.Add(i); break; } } try { KSoruFormu.progressBar1.Value++; } catch { } } } if (SilinecekIndisler.Count > 0)//silinecek satır var ise devam eder. { KSoruFormu.progressBar1.Maximum += SilinecekIndisler.Count * Madde_Sayisi + SilinecekIndisler.Count; int[,] sil = new int[Convert.ToInt32(SilinecekIndisler.Count), Madde_Sayisi]; for (int i = 0; i < SilinecekIndisler.Count; i++) { for (int j = 0; j < Madde_Sayisi; j++) {//silinecek satırların değerlerini tutar. sil[i, j] = Convert.ToInt32(Hucrelerden_Okuma_Metodu((Convert.ToInt32(SilinecekIndisler[i]) + 1), (j + 2))); try { KSoruFormu.progressBar1.Value++; } catch { } } } Excel.Worksheet wsSil = Globals.ThisAddIn.Application.ActiveSheet; Excel.Range row; int[] rows = new int[SilinecekIndisler.Count]; int SilinenSayac = 0; foreach (var item in SilinecekIndisler) {//silinmesi gereken satırları siler. row = (Excel.Range)wsSil.Rows[Convert.ToInt32(item.ToString()) + 1 - SilinenSayac]; row.Delete(); SilinenSayac++; try { KSoruFormu.progressBar1.Value++; } catch { } } Katilimci_Sayisi = Katilimci_Sayisi - SilinecekIndisler.Count;//katılımcı sayısını günceller. Excel.Workbook wsTemizlenenler = Globals.ThisAddIn.Application.ActiveWorkbook; Excel.Worksheet x = wsTemizlenenler.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); x.Name = YeniTemizlenenlerSayfasiOlustur();//yeni temizlenenler sayfası oluşturur. KSoruFormu.progressBar1.Maximum += SilinecekIndisler.Count * Madde_Sayisi; for (int i = 0; i < SilinecekIndisler.Count; i++) { for (int j = 0; j < Madde_Sayisi; j++) { x.Cells[i + 1, j + 1] = sil[i, j].ToString();//yeni oluşturulan sayfaya silinecek verileri yazdırır. try { KSoruFormu.progressBar1.Value++; } catch { } } } KSoruFormu.Cursor = Cursors.Default; if (KSoruFormu.progressBar1.Value < KSoruFormu.progressBar1.Maximum) { for (int i = KSoruFormu.progressBar1.Value; i < KSoruFormu.progressBar1.Maximum; i++) { KSoruFormu.progressBar1.Value++; } } //bilgi mesajı. MessageBox.Show(SilinecekIndisler.Count.ToString() + " Veri " + x.Name.ToString() + " Sayfasına Taşındı.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information); } else {//hiç veri silinmediyse. KSoruFormu.Cursor = Cursors.Default; MessageBox.Show("Silinecek Hiç Bir Veri Bulunamadı."); if (KSoruFormu.progressBar1.Value < KSoruFormu.progressBar1.Maximum) { for (int i = KSoruFormu.progressBar1.Value; i < KSoruFormu.progressBar1.Maximum; i++) { KSoruFormu.progressBar1.Value++; } } } } else { MessageBox.Show("Veri Temizleme İşlemini '" + IslemSayfasi + "' İçinde Yapabilirsiniz.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information); } } else { MessageBox.Show("Madde Listesinden Seçim Yapınız.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
public static void createNewExcel(DataSet1.PriProLieferscheinRechnungRow newRow) { string destination = getLieferscheinFilename(newRow); try { File.Copy(vorlageFile, destination, true); newRow.LieferscheinPfad = destination; } catch (Exception ex) { Helper.LogHelper.WriteDebugLog(ex.ToString()); } // das Excelsheet offnen if (File.Exists(destination)) { try { // die Excelanwendung excelApp = new Excel.Application(); excelApp.DisplayAlerts = false; // die Excelmappe excelWorkbook = excelApp.Workbooks.Open(destination, 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); int anzahlBlaetter = excelWorkbook.Sheets.Count; if (anzahlBlaetter > 0) { // das Excelblatt excelSheet = (Excel.Worksheet)excelWorkbook.Sheets[1]; diePlatzhalterBefuellen(excelSheet, newRow); excelWorkbook.Save(); } // alles beenden und freigeben excelWorkbook.Close(false, destination, null); excelApp.Quit(); Marshal.ReleaseComObject(excelWorkbook); Marshal.ReleaseComObject(excelApp); System.Diagnostics.Process.Start(destination); } catch (Exception ex) { Helper.LogHelper.WriteDebugLog(ex.ToString()); } } }
private void button1_Click(object sender, RibbonControlEventArgs e) { //ilk önce ölçek nokta sayısı madde sayısı ve cevaplayıcı sayısı doğru girilip girilmediği kontrol edilir. if (editBox2.Text != "" && Convert.ToInt32(editBox2.Text) >= 1) { if (editBox3.Text != "" && Convert.ToInt32(editBox3.Text) >= 1) { if (editBox4.Text != "" && Convert.ToInt32(editBox4.Text) >= 1) { Globals.ThisAddIn.Application.Cells.Clear();//sayfayı temizler Anket_Tipi = Convert.ToInt32(editBox2.Text); Madde_Sayisi = Convert.ToInt32(editBox3.Text); Secilecek_Liste.Clear(); Secilen_Liste.Clear(); Hucreye_Yazdirma_Metodu(1, 1, "ID"); for (int l = 1; l <= Madde_Sayisi; l++)//veri temizleme işleminde kullanılması için maddeler güncellenir. { Secilecek_Liste.Add(l); } int i; for (i = 1; i <= Madde_Sayisi; i++)//Veri giriş ekranı oluşturulur. { Hucreye_Yazdirma_Metodu(1, (i + 1), "Madde" + i.ToString()); } Hucreye_Yazdirma_Metodu(1, (i + 2), "ECT"); Hucreye_Yazdirma_Metodu(1, (i + 3), "EKCT"); Hucreye_Yazdirma_Metodu(1, (i + 4), "EKOCT"); Hucreye_Yazdirma_Metodu(1, (i + 5), "KCT"); Hucreye_Yazdirma_Metodu(1, (i + 6), "KOCT"); Hucreye_Yazdirma_Metodu(1, (i + 7), "NKCT"); Hucreye_Yazdirma_Metodu(1, (i + 8), "ONCT"); Hucreye_Yazdirma_Metodu(1, (i + 9), "CVPSZCT"); Hucreye_Yazdirma_Metodu(1, (i + 10), "ARADCT"); Katilimci_Sayisi = Convert.ToInt32(editBox4.Text); Hucreye_Yazdirma_Metodu(Katilimci_Sayisi + 2, Madde_Sayisi + 2, "Ortalamalar"); button2.Enabled = true; button8.Enabled = true; button9.Enabled = true; Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet); IslemSayfasi = activeWorksheet.Name;//hesaplama işleminde kullanılması için işlem sayfası tutulur. } else { MessageBox.Show("Cevaplayıcı Sayısı Boş Bırakılamaz Ve 0 ' dan Büyük Olması Gerekir.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Madde Sayısı Boş Bırakılamaz Ve 0 ' dan Büyük Olması Gerekir.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); } } else { MessageBox.Show("Ölçek Tipi Boş Bırakılamaz Ve 0 ' dan Büyük Olması Gerekir.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
/// <summary> /// EXCEL转PDF /// </summary> /// <param name="strSourceFile">要转换的EXCEL文件路径</param> /// <param name="strTargetFile">目标文件</param> /// <returns>转换结果:TRUE FALSE</returns> public bool ExcelConvertTOPDF(string strSourceFile, string strTargetFile) { bool flag = false; if (File.Exists(strSourceFile)) { //转换成的上档格式PDF EXCEL.XlFixedFormatType targetType = EXCEL.XlFixedFormatType.xlTypePDF; object targetFile = strTargetFile; object missing = Type.Missing; EXCEL.ApplicationClass excel = null; EXCEL.Workbook workBook = null; EXCEL.Workbooks workBooks = null; EXCEL.Worksheet sheet = null; try { excel = new EXCEL.ApplicationClass(); //excel.DisplayAlerts = true; workBooks = excel.Workbooks; workBook = workBooks.Open(strSourceFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); //设置格式,导出成PDF sheet = (EXCEL.Worksheet)workBook.Worksheets[1];//下载从1开始 //把sheet设置成横向 //sheet.PageSetup.Orientation = EXCEL.XlPageOrientation.xlLandscape; //可以设置sheet页的其他相关设置,不列举 sheet.ExportAsFixedFormat(targetType, targetFile, EXCEL.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing); flag = true; } catch (Exception ex) { classLims_NPOI.WriteLog(ex, ""); flag = false; } finally { if (sheet != null) { Marshal.ReleaseComObject(sheet); //Marshal.FinalReleaseComObject(sheet); sheet = null; } if (workBook != null) { workBook.Close(false, missing, missing); Marshal.ReleaseComObject(workBook); //Marshal.FinalReleaseComObject(workBook); workBook = null; } if (workBooks != null) { workBooks.Close(); Marshal.ReleaseComObject(workBooks); workBook = null; } if (excel != null) { excel.Quit(); Marshal.ReleaseComObject(excel); //Marshal.FinalReleaseComObject(excel); excel = null; //flag = KillSpecialExcel(excel); } GC.Collect(); GC.WaitForPendingFinalizers(); } } return(flag); }
public bool uploadInfo(int type, string vCode, string deviceCode) //type 0: 正常上传, type 1: 补充上传 { bool rtn = false; string code = ""; bool isCorrect = false; string errormsg = ""; string name = ""; if (File.Exists(testFile)) { if (File.Exists(sourceFile)) { File.Delete(sourceFile); } File.Copy(testFile, sourceFile); FileInfo fileInfo = new FileInfo(sourceFile); //有最新文件,开始上传数据 //先检查数据源中的校验码是否正确 Excel.ApplicationClass excel = new Excel.ApplicationClass(); //MessageBox.Show(excel.Version); excel.Visible = false; excel.Workbooks.Open(sourceFile, Type.Missing, Type.Missing, Type.Missing, "halleluja", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); Excel.Worksheet ws = (Excel.Worksheet)excel.Worksheets[2]; name = ((Excel.Range)ws.Cells[3, 3]).Text.ToString(); sourceFileName = name; excel.Quit(); IntPtr t = new IntPtr(excel.Hwnd); int k = 0; GetWindowThreadProcessId(t, out k); System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); p.Kill(); if (type == 0) { if (Patient.w_code != "") { code = Patient.w_code; verifyCode = code; try { string strOrder = webService.isExistOrder_v2(code, Patient.w_username, deviceCode); if (strOrder.Length > 2) { vjList = getValidate(strOrder); if (vjList.name.Length > 0) { isCorrect = true; } string pType = vjList.peopleType; //if (pType == "1") //{ // isAdult = false; //} //else //{ // isAdult = true; //} } else if (strOrder == "0") { errormsg = "诊疗人不存在,"; } else if (strOrder == "-1") { errormsg = "订单不存在,或者被使用"; } else if (strOrder == "-2") { errormsg = "门店用户未设置所属门店,"; } else if (strOrder == "-3") { errormsg = "订单预约门店与使用门店不同,"; } else if (strOrder == "-4") { errormsg = "订单状态不可用,"; } else if (strOrder == "-5") { errormsg = "设备不存在,"; } else if (strOrder == "-6") { errormsg = "设备未绑定服务,"; } else if (strOrder == "-7") { errormsg = "设备使用次数为0,"; } else if (strOrder == "-8") { errormsg = "设备服务未到开始可用时间,"; } else if (strOrder == "-9") { errormsg = "设备服务使用时间已到期,"; } } catch (Exception ex) { string er = ex.ToString(); LogHelper.WriteLog(ex.ToString()); } } else { errormsg = "验证码录入有误,"; } } else { verifyCode = vCode; isCorrect = true; } //上传文件到服务器 //verifyCode = "hahahaha"; if (type == 0) { FTPUpLoad(); } //有更新文件,但是文件有问题 if (!isCorrect) { File.Delete(sourceFile); //SetValue("ChangeTime", latestTime); LogHelper.WriteLog(sourceFileName + "-" + errormsg + "文件存在问题,云端处理数据失败!"); //XtraMessageBox.Show(sourceFileName + "-" + errormsg + "云端处理数据失败!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return(false); } //SetValue("ChangeTime", latestTime); LogHelper.WriteLog("开始云端处理数据"); //copyTemp(); string temp = System.Windows.Forms.Application.StartupPath + "/DevExpress.Map.v16.2.dll"; copyStream(reportFile, temp); //上传数据 //Thread thdSub = new Thread(new ThreadStart(ThreadFun)); //thdSub.Start(); rtn = ThreadFun(deviceCode); } return(rtn); }
// Generate an Excel worksheet with the results in reverse chronological order so the most // recent activity in the repository is shown on top. Returns true if the operation completed // successfully, false otherwise. COMException caught and logged in // %LOCALAPPDATA%\AcTools\Logs\LatestTransactions-YYYY-MM-DD.log on worksheet creation failure. private static bool report() { Excel.Application excel = new Excel.Application(); if (excel == null) { MessageBox.Show("Excel installation not found.", "LatestTransactions", MessageBoxButtons.OK, MessageBoxIcon.Hand); return(false); } bool ret = true; // assume success excel.DisplayAlerts = false; // don't display the SaveAs dialog box Excel.Workbook wbook = excel.Workbooks.Add(); Excel.Worksheet wsheet = (Excel.Worksheet)wbook.Worksheets.get_Item(1); Excel.Range rheader = wsheet.get_Range("A1", "F1"); rheader.Font.Bold = true; rheader.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; Excel.Range rdate = wsheet.get_Range("B:B"); rdate.NumberFormat = "mmm d, yyyy h:mm:ss am/pm"; Excel.Range rcomment = wsheet.get_Range("F:F"); rcomment.WrapText = true; wsheet.Cells[1, "A"] = "Depot"; wsheet.Columns["A"].ColumnWidth = 20; wsheet.Cells[1, "B"] = "Time"; wsheet.Columns["B"].ColumnWidth = 25; wsheet.Cells[1, "C"] = "Action"; wsheet.Columns["C"].ColumnWidth = 15; wsheet.Cells[1, "D"] = "User"; wsheet.Columns["D"].ColumnWidth = 15; wsheet.Cells[1, "E"] = "Trans_ID"; wsheet.Columns["E"].ColumnWidth = 10; wsheet.Cells[1, "F"] = "Comment"; wsheet.Columns["F"].ColumnWidth = 50; int row = 2; foreach (XElement trans in _transactions.OrderByDescending(n => n.acxTime("time"))) { string depot = trans.Annotation <string>(); int id = (int)trans.Attribute("id"); string action = (string)trans.Attribute("type"); DateTime?time = trans.acxTime("time"); string user = (string)trans.Attribute("user"); string comment = trans.acxComment(); wsheet.Cells[row, "A"] = depot; wsheet.Cells[row, "B"] = time; wsheet.Cells[row, "C"] = action; wsheet.Cells[row, "D"] = user; wsheet.Cells[row, "E"] = id; wsheet.Cells[row, "F"] = comment; row++; } string file = String.Empty; try { file = Path.Combine(_fileLocation, _fileName); wbook.SaveAs(Filename: file, ReadOnlyRecommended: true); wbook.Close(); } catch (COMException exc) { AcDebug.Log(exc.Message); MessageBox.Show(exc.Message); ret = false; } finally { excel.Quit(); } if (ret) { MessageBox.Show($"Latest transactions saved to {file}", "LatestTransactions", MessageBoxButtons.OK, MessageBoxIcon.Information); } return(ret); }
private void UpdateEnPISheet(SwitchModel sm) { bool outputPresent = false; Excel.Worksheet adjustedDataSheet = new Excel.Worksheet(); //Excel.Worksheet thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet; //TFS Ticket: 71242 Excel.Worksheet thisSheet = energySheet; //Update the SEP Validation Check values string sourceName = energySheet.Name; sourceName = sourceName.Substring(1, sourceName.Length - 1).ToString().TrimStart(); Globals.ThisAddIn.SelectedSourcesBestModelFormulas[sourceName] = sm.Formula; string adjustedDatasheetName = string.Empty; foreach (GroupSheetCollection gsc in Globals.ThisAddIn.masterGroupCollection) { bool matchingCollection = false; foreach (GroupSheet GS in gsc) { string GSname = GS.Name; if (GS.WS.Equals(thisSheet)) { matchingCollection = true; } if (GS.adjustedDataSheet && matchingCollection) { adjustedDataSheet = GS.WS; adjustedDatasheetName = GS.WS.Name; UpdateSEPValidationCheckList(adjustedDatasheetName); outputPresent = true; } if (GS.outputSheet) { populateModelData(thisSheet, GS.WS, sm.ModelNumber); } } } if (outputPresent) { string adjDataSheetName = adjustedDataSheet.Name; Excel.Workbook WB = Globals.ThisAddIn.Application.ActiveWorkbook; ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Sheets[adjustedDataSheet.Name]).Select(Type.Missing); thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet; //Update the latest values on Validation Check Table of Model Sheet try { for (int i = 0; i < Globals.ThisAddIn.lstSEPValidationValues.Count; i++) { Excel.Range sepValidationChk = thisSheet.get_Range("C13").get_Offset(0, i).get_Resize(1, 1); sepValidationChk.Value2 = Globals.ThisAddIn.lstSEPValidationValues[i].SEPValidationCheck; sepValidationChk.NumberFormat = "General"; } } catch (Exception ex) { } Excel.ListObject LO = (Excel.ListObject)thisSheet.ListObjects.get_Item(1); foreach (Excel.ListColumn col in LO.ListColumns) { //------------------------------ //modify substring so that regression runs past 9 can switch models string shortColName = col.Name; //since sheet names are limited to 29 characters -- see CreateValidWorksheetName in ExcelHelpers.cs if (col.Name.Length >= 36) { shortColName = col.Name.Substring(0, 35); } string shtname = sm.SheetName.Substring(2); if (shortColName.Equals("Modeled " + sm.SheetName.Substring(2))) //------------------------------ { col.DataBodyRange.Value2 = "=" + sm.Formula; //recalculate all of the List objects so numbers are updated. foreach (Excel.Worksheet WS in WB.Worksheets) { string wsname = WS.Name; foreach (Excel.ListObject ListObj in WS.ListObjects) { if (ListObj.DataBodyRange != null) { ListObj.DataBodyRange.Dirty(); ListObj.DataBodyRange.Calculate(); } } } } } Excel.Range negativeMessageHeader = thisSheet.get_Range("A2"); Excel.Range negativeMessageDescription = thisSheet.get_Range("A3"); if (Globals.ThisAddIn.NegativeCheck(LO, Globals.ThisAddIn.modeledSourceIndex)) { negativeMessageHeader.EntireRow.Hidden = false; negativeMessageDescription.EntireRow.Hidden = false; } else { negativeMessageHeader.EntireRow.Hidden = true; negativeMessageDescription.EntireRow.Hidden = true; } } }
public static void GetLastRowCol(Excel.Worksheet ws, ref int lastRow, ref int lastCol) { lastCol = ws.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Excel.XlSpecialCellsValue.xlTextValues).Column; lastRow = ws.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Excel.XlSpecialCellsValue.xlTextValues).Row; }
/// <summary> /// Delete one row /// </summary> /// <param name="sheet"></param> /// <param name="cellRange"></param> public static void DeleteRow(Excel.Worksheet sheet, string cellRange) { Excel.Range rangeDelete = sheet.Range[cellRange]; rangeDelete.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); ReleaseComObject(rangeDelete); }
private void ExportExcel(DataSet ds, string saveFileName) { try { if (ds == null) { MessageBox.Show("数据库为空"); } bool fileSaved = false; Microsoft.Office.Interop.Excel.Application elApp = new Microsoft.Office.Interop.Excel.Application(); if (elApp == null) { MessageBox.Show("无法创建Excel对象,您的电脑未安装Excel"); } Microsoft.Office.Interop.Excel.Workbooks workbooks = elApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //写入字段名称 for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { worksheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName; } //写入数据 for (int r = 0; r < ds.Tables[0].Rows.Count; r++) { for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { worksheet.Cells[r + 2, i + 1] = ds.Tables[0].Rows[r][i]; } System.Windows.Forms.Application.DoEvents(); } worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应 if (saveFileName != "") { try { workbook.Saved = true; workbook.SaveCopyAs(saveFileName); fileSaved = true; } catch (Exception ex) { fileSaved = false; MessageBox.Show("导出文件出错,文件可能正在被打开!\n" + ex.Message); } } else { fileSaved = false; } elApp.Quit(); GC.Collect(); //强制销毁 if (fileSaved && System.IO.File.Exists(saveFileName)) { System.Diagnostics.Process.Start(saveFileName); MessageBox.Show("成功保存到Excel"); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public static void InsertRow(Excel.Worksheet sheet, string cell) { Excel.Range range = sheet.Range[cell]; range.EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown); ReleaseComObject(range); }
private void PopulateModels(Excel.ListObject DataLO) { Excel.Worksheet thisSheet = (Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet; // if (DataLO != null) { int bestModelNumber; //check to see if model can be switched from current sheet. try { //TFS Ticket 71242 string strBestModel = ((Excel.Range)thisSheet.Cells[2, 1]).Value2.ToString(); string[] strArry = strBestModel.Split(new string[] { EnPIResources.bestModel }, StringSplitOptions.RemoveEmptyEntries); bestModelNumber = Convert.ToInt32(strArry[0]); //bestModelNumber = Convert.ToInt32(((Excel.Range)thisSheet.Cells[2, 1]).Value2.ToString().Substring(((Excel.Range)thisSheet.Cells[2, 1]).Value2.ToString().Length - 1)); } catch { this.label1.Text = "In order to switch a model, you must first be on the sheet that the model is listed on."; this.comboBox1.Enabled = false; return; } int currentModelNumber = 0; int varCount = 1; modelCount = 0; bool bestModel = false; foreach (Excel.ListRow row in DataLO.ListRows) { string[] variables = new string[DataLO.ListRows.Count]; double[] VarPvalue = new double[DataLO.ListRows.Count]; if (((Excel.Range)row.Range.Cells[1]).Value2 != null) { varCount = 1; modelCount++; //modelCount = row.Range.Count; currentModelNumber = Convert.ToInt32(((Excel.Range)row.Range.Cells[1]).Value2.ToString()); if (currentModelNumber.Equals(bestModelNumber)) { bestModel = true; } variables[0] = ((Excel.Range)row.Range.Cells[3]).Value2.ToString(); VarPvalue[0] = Convert.ToDouble(((Excel.Range)row.Range.Cells[6]).Value2.ToString()); //SwitchModel sm = new SwitchModel(currentModelNumber, variables, Convert.ToDouble(((Excel.Range)row.Range.Cells[7]).Value2.ToString()) // , ((Excel.Range)row.Range.Cells[13]).Value2.ToString(), bestModel, thisSheet.Name, modelCount // , VarPvalue, Convert.ToDouble(((Excel.Range)row.Range.Cells[8]).Value2.ToString()) // , Convert.ToDouble(((Excel.Range)row.Range.Cells[9]).Value2.ToString())); //Added by suman SEP Changes SwitchModel sm = new SwitchModel(currentModelNumber, variables, Convert.ToDouble(((Excel.Range)row.Range.Cells[8]).Value2.ToString()) , ((Excel.Range)row.Range.Cells[14]).Value2.ToString(), bestModel, thisSheet.Name, modelCount , VarPvalue, Convert.ToDouble(((Excel.Range)row.Range.Cells[9]).Value2.ToString()) , Convert.ToDouble(((Excel.Range)row.Range.Cells[10]).Value2.ToString())); sModCol.Add(sm); bestModel = false; } else { if (((Excel.Range)row.Range.Cells[3]).Value2 != null) { sModCol.Item(sModCol.Count - 1).VariableNames[varCount] = ((Excel.Range)row.Range.Cells[3]).Value2.ToString(); varCount++; } //if (((Excel.Range)row.Range.Cells[6]).Value2 != null) //{ // sModCol.Item(sModCol.Count - 1).VariablePvalues[varCount] = Convert.ToDouble(((Excel.Range)row.Range.Cells[6]).Value2.ToString()); // varCount++; //} if (((Excel.Range)row.Range.Cells[7]).Value2 != null) { sModCol.Item(sModCol.Count - 1).VariablePvalues[varCount] = Convert.ToDouble(((Excel.Range)row.Range.Cells[7]).Value2.ToString()); varCount++; } } } } this.label1.Text = "Select the model you wish to use to calculate the adjusted values on the EnPI Results and SEnPI Results sheets from the drop down below.";// +thisSheet.Name + ":"; int t = DataLO.ListColumns["Model is Appropriate for SEP"]._Default.Length; }
/// <summary> /// Delete columns /// </summary> /// <param name="sheet"></param> /// <param name="beginColumn"></param> /// <param name="endColumn"></param> public static void DeleteColumns(Excel.Worksheet sheet, string beginColumn, string endColumn) { sheet.Range[beginColumn + "1", endColumn + "1"].EntireColumn.Delete(); }
private void Form1_Load(object sender, EventArgs e) { app = new Excel.Application(); app.Visible = false; workbook = app.Workbooks.Open(Application.StartupPath + "\\Дни рождения сотрудников.xls"); worksheet = (Excel.Worksheet) workbook.ActiveSheet; int i = 0; names = worksheet.get_Range("A1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); departaments1 = worksheet.get_Range("B1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); departaments2 = worksheet.get_Range("C1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); birthdays = worksheet.get_Range("D1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count)); string current_date = DateTime.Now.ToString("dd.MM"); for (i = 1; i <= worksheet.UsedRange.Rows.Count; i++) { string departament1_value = Convert.ToString(departaments1.Cells[i]); string departament2_value = Convert.ToString(departaments1.Cells[i]); string birthday_value = Convert.ToString(departaments1.Cells[i]); string name_value = Convert.ToString(departaments1.Cells[i]); if (departament1_value == "Іб та ПД" || departament2_value == "Іб та ПД") { if (birthday_value == current_date) label1.Text = "Сьогодні свій день народження відзначає " + name_value; } } app.Quit(); }