private void button2_Click(object sender, EventArgs e) { if (checkBox1.Checked == true && checkBox2.Checked == true) { object hucre = ExcelSayfa.Cells[numericUpDown1.Value, numericUpDown2.Value]; Excel.Range bolge = ExcelSayfa.get_Range(hucre, hucre); bolge.Value2 = textBox1.Text; yenile(); } else if (checkBox1.Checked == false && checkBox2.Checked == true) { object hucre = ExcelSayfa.Cells[comboBox1.SelectedIndex + 1, numericUpDown2.Value]; Excel.Range bolge = ExcelSayfa.get_Range(hucre, hucre); bolge.Value2 = textBox1.Text; yenile(); } else if (checkBox1.Checked == true && checkBox2.Checked == false) { object hucre = ExcelSayfa.Cells[numericUpDown1.Value, comboBox1.SelectedIndex + 1]; Excel.Range bolge = ExcelSayfa.get_Range(hucre, hucre); bolge.Value2 = textBox1.Text; yenile(); } textBox1.Text = ""; try { ExcelProje.Save(); } catch (Exception) { MessageBox.Show("Düzenleme Yapılan Dosyanın Kapalı Olduğundan Emin Olunuz..."); } }
public void AddNewRecord <T>(T record) { var accountList = GetAllAccounts(); int newRowIndex = accountList.Count + 2; try { foreach (string propertyName in accountMapping.Keys) { Type accountType = typeof(Account); PropertyInfo myPropertyInfo = accountType.GetProperty(propertyName); string value = myPropertyInfo.GetValue(record, null)?.ToString() ?? string.Empty; int newColumn = accountMapping[propertyName]; xlWorksheet.Cells[newRowIndex, newColumn].Value = value; } xlWorkbook.Save(); } catch (Exception ex) { Console.WriteLine($"Failed to add new record. {ex.ToString()}"); this.Dispose(); throw ex; } }
// Replaces item name with name from inventory // to insure exact match // Gets passed dictionary and blank perpetual / order guide // No return static void orderConversion(Dictionary <string, SalesData> d, string file) { Excel.Application xl = new Excel.Application(); Excel.Workbook xlWorkbook = xl.Workbooks.Open(file); Excel.Worksheet xlSheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlSheet.UsedRange; foreach (var entry in d) { Console.WriteLine(entry.Key); if (entry.Value.Code == "584-10" || entry.Value.Code == "584-30" || entry.Value.Code == "555-00") { continue; } int row = Int32.Parse(Console.ReadLine()); if (row == 999) { continue; } if (row == -1) { xlWorkbook.Save(); xlWorkbook.Close(file); Console.WriteLine("Complete"); return; } xlRange.Cells[row, 1] = entry.Key; } xlWorkbook.Save(); xlWorkbook.Close(file); Console.WriteLine("orderConversion Finished."); }
public void UpdateKPMDocument(List<Dictionary<string, string>> LTicketItemList) { g_Util.DebugPrint("I'm updating Excel File..."); int nNumberCol = FindColumn(g_KPMCreate_Worksheet.get_Range("1:1"), "Number"); int nUploadCol = FindColumn(g_KPMCreate_Worksheet.get_Range("1:1"), "Re-upload Attachment"); int nCurKPMSheetRow = 2; for (int nIdx = 0; nIdx < LTicketItemList.Count; nIdx++) { Dictionary<string, string> TicketItem = LTicketItemList[nIdx]; string nTicketNum = TicketItem["Number"]; string Upload = TicketItem["Re-upload Attachment"]; ((Range)g_KPMCreate_Worksheet.Cells[nCurKPMSheetRow, nNumberCol]).Value = nTicketNum; ((Range)g_KPMCreate_Worksheet.Cells[nCurKPMSheetRow, nUploadCol]).Value = Upload; nCurKPMSheetRow++; } g_Util.DebugPrint("I'm saving Excel File..."); if (g_KPMWorkbook.ReadOnly == false) { g_KPMWorkbook.Save(); } }
protected override void CleanContentProperties() { Office.DocumentProperties docProperties = (Office.DocumentProperties)workbook.CustomDocumentProperties; foreach (Office.DocumentProperty prop in docProperties) { if (prop.Name.Equals(CONTENT_ID_NAME, StringComparison.InvariantCulture)) { prop.Delete(); break; } } docProperties = (Office.DocumentProperties)workbook.CustomDocumentProperties; foreach (Office.DocumentProperty prop in docProperties) { if (prop.Name.Equals(REPOSITORY_ID_NAME, StringComparison.InvariantCulture)) { prop.Delete(); break; } } workbook.Saved = false; try { workbook.Save(); } catch (Exception e) { Debug.WriteLine(e.Message); } }
private void ExcelReplace(Dictionary <string, string> replaceDic, string objFile, string newFile, Excel.Application app) { Excel.Workbook ew = app.Workbooks.Open(objFile, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); bool delete = false;//是否删除源文件 try { Excel.Worksheet ews; int iEWSCnt = ew.Worksheets.Count; Excel.Range oRange; foreach (var item in replaceDic) { for (int i = 1; i <= iEWSCnt; i++) { ews = (Excel.Worksheet)ew.Worksheets[i]; oRange = ews.UsedRange.Find( item.Key, Nothing, Nothing, Nothing, Nothing, Excel.XlSearchDirection.xlNext, Nothing, Nothing, Nothing); if (oRange != null && oRange.Cells.Rows.Count >= 1 && oRange.Cells.Columns.Count >= 1) { oRange.Replace(item.Key, item.Value, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); ew.Save(); } } } if (newFile == null || newFile.ToString() == objFile.ToString()) { ew.Save(); } else { ew.SaveAs(newFile); delete = true; } } catch (Exception ex) { Log.Error("保存失败!", ex); } finally { try { ew.Close(); } catch { } if (delete) { try { File.Delete(objFile.ToString()); } catch (Exception ex) { Log.Error("删除失败!", ex); } } } }
private void CallManagerData_To_Excel(Excel.Application ExcelApp, Excel.Workbook WB, string MailFrom, string MailTo, string MailCC, string SMTPServer) { try { string SQLConnString = @"Data Source=Testserver;Initial Catalog=testdb;Integrated Security=SSPI;"; string query = Dts.Variables["User::SQLQuery_Tesco"].Value.ToString(); using (SqlConnection connection = new SqlConnection(SQLConnString)) { SqlCommand command = new SqlCommand(query, connection); connection.Open(); SqlDataAdapter da = new SqlDataAdapter(command); DataTable dt = new DataTable(); da.Fill(dt); Excel.Worksheet WS = ((Excel.Worksheet)WB.Worksheets.get_Item("Data")); WS.Columns.ClearFormats(); // Clear cell format WS.Rows.ClearFormats(); // Clear cell format int firstRow = 0; int firstCol = 1; int lastRow = 0; int lastCol = 0; firstRow = WS.UsedRange.Rows.Count + 1; lastRow = WS.UsedRange.Rows.Count + dt.Rows.Count; lastCol = dt.Columns.Count; Excel.Range top = WS.Cells[firstRow, firstCol]; Excel.Range bottom = WS.Cells[lastRow, lastCol]; Excel.Range all = (Excel.Range)WS.get_Range(top, bottom); object[,] arrayDT = new object[dt.Rows.Count, dt.Columns.Count]; for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { arrayDT[i, j] = dt.Rows[i][j]; } } all.Value2 = arrayDT; WS.Columns.AutoFit(); WB.Save(); } } catch (Exception ex) { string MailBody, MailSub = "Team - Weekly - data to Excel failed"; MailBody = "Hello Team </BR></BR>"; MailBody += string.Format("Data load from data to Excel failed due to:</BR><b>Error message:</b>{0}", ex.Message); SendMail(MailFrom, MailTo, MailCC, MailSub, SMTPServer, MailBody); WB.Save(); WB.Close(0); ExcelApp.Quit(); GC.Collect(); Dts.TaskResult = (int)ScriptResults.Failure; } }
//Method to add details of new employee private void AddEmployee(object sender, RoutedEventArgs e) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook workBook = xlApp.Workbooks.Open(filePath); Excel.Worksheet workSheet = workBook.Worksheets[3]; Excel.Range range = workSheet.UsedRange.Columns["A", Type.Missing]; int rowCount = range.Rows.Count; int i = rowCount + 1; try { if (!string.IsNullOrWhiteSpace(txtName.Text) && !string.IsNullOrWhiteSpace(txtId.Text) && !string.IsNullOrWhiteSpace(txtUa.Text) && !string.IsNullOrWhiteSpace(txtPwd.Password) && (rbnYes.IsChecked == true || rbnNo.IsChecked == true)) { if (i > rowCount) { workSheet.Cells[i, "A"].Value = txtName.Text.ToString(); workSheet.Cells[i, "B"].Value = txtId.Text.ToString(); workSheet.Cells[i, "C"].Value = txtUa.Text.ToString(); workSheet.Cells[i, "D"].Value = txtPwd.Password.ToString(); if (rbnYes.IsChecked == true) { workSheet.Cells[i, "E"].Value = "Yes"; } else if (rbnNo.IsChecked == true) { workSheet.Cells[i, "E"].Value = "No"; } } MessageBox.Show("Details added successfully"); txtId.Text = ""; txtName.Text = ""; txtUa.Text = ""; txtPwd.Password = ""; rbnYes.IsChecked = false; rbnNo.IsChecked = false; workBook.Save(); workBook.Close(); xlApp.Quit(); GC.Collect(); empGrid.ItemsSource = helpers.BindEmployeeData(); } else { MessageBox.Show("Please enter all details"); workBook.Save(); workBook.Close(); xlApp.Quit(); GC.Collect(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
//Funcion que abre un documento Excel y añade //a la primera columna la pregunta proporcionada. public void AddPreguntaIneditaExcel(string pregunta) { Range range = workingSheet.UsedRange; int rowCount = range.Rows.Count; range.Cells[rowCount + 1, 1] = "2"; range.Cells[rowCount + 1, 2] = pregunta; activeWorkbook.Save(); }
private bool Write(int row, int column, dynamic value) { if (_statistic?.Cells[row, column] == null) { return(false); } ((Xl.Range)_statistic.Cells[row, column]).Value2 = value; _wb.Save(); return(true); }
/// <summary> /// This will save the settings if we want to change a building name /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void saveBTN_Click(object sender, EventArgs e) { Cursor.Current = Cursors.WaitCursor; //First check if the building text box has text in it. if (!string.IsNullOrWhiteSpace(newBuildingInit.Text.ToString())) { //Get the index of the selected building int selected = this.buildingComboBox.SelectedIndex; //Building was not selected correctly if (selected == 0) { MetroMessageBox.Show(this, "Please select a building!", "Invalid Building", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { //We get the index of the building we need to change Excel.Range changeBuilding = (Excel.Range)dataBaseSheet.get_Range("C" + (selected + 1), "C" + (selected + 1)); //Save the building name for further references string previousBuildingName = changeBuilding.Value2.ToString(); //Update the building name with the new one changeBuilding.Value2 = this.newBuildingInit.Text.ToString().ToUpper(); //Save and inform the user that the save was successful masterWorkBook.Save(); MetroMessageBox.Show(this, previousBuildingName + " was successfully changed to " + this.newBuildingInit.Text.ToString().ToUpper(), "Success!", MessageBoxButtons.OK, MessageBoxIcon.Information); changeBuilding = null; //Update the comobox again int lastRow = dataBaseSheet.UsedRange.Rows.Count; buildingRange = dataBaseSheet.get_Range("C1", "C" + (lastRow)); //Convert to an array buildingArray = (System.Array)buildingRange.Cells.Value2; //Clear the combo box this.buildingComboBox.Items.Clear(); this.newBuildingInit.Clear(); //Add the buildings and names to the drop down mean foreach (object s in buildingArray) { if (s != null) { this.buildingComboBox.Items.Add(s.ToString()); } } //Select the default value to display this.buildingComboBox.SelectedIndex = 0; } } Cursor.Current = Cursors.Default; }
private void button3_Click(object sender, EventArgs e) { //formulario_ws = formulario.Worksheets.get_Item(1); //Console.WriteLine(formulario_ws.Name); //formulario_ws.Cells.set_Item(3, 3, "JAVIER CIFUENTES"); //formulario_ws.Cells.set_Item(4, 3, 2048511650101); // return; int _formulario = 0; int rowCount = listado_rg.Rows.Count; for (int i = 1; i <= rowCount; i++) { var codigo_muni = listado_rg.Cells[i, 5] as Excel.Range; try { if (codigo_muni.Value != null) { if (codigo_muni.Value.ToString() == "713") { String nombre = listado_rg.Cells[i, 13].Value.ToString(); String dpi = listado_rg.Cells[i, 14].Value.ToString(); String sexo = listado_rg.Cells[i, 15].Value.ToString(); String comunidad = listado_rg.Cells[i, 8].Value.ToString(); String municipio = listado_rg.Cells[i, 6].Value.ToString(); String departamento = listado_rg.Cells[i, 4].Value.ToString(); for (int _sheet = 1; _sheet <= formulario.Sheets.Count; _sheet++) { llenarFormulario(_sheet, _formulario, departamento, municipio, comunidad, dpi, nombre, sexo); } _formulario++; } } } catch (Exception exc) { Console.WriteLine("EX:"); } } formulario.Save(); //llenar nombre GC.Collect(); GC.WaitForPendingFinalizers(); if (formulario_rg != null) { Marshal.ReleaseComObject(formulario_rg); } if (formulario_ws != null) { Marshal.ReleaseComObject(formulario_ws); } }
/// <summary> /// 查询excel表格数据 /// </summary> //public void SearchExcel() // { //for (int i = 1; i <= rowCount; i++) //{ // for (int j = 1; j <= colCount; j++) // { // if (j == 1) // Console.Write("\r\n"); // if (range.Cells[i, j] != null && range.Cells[i, j].Value2 != null) // { // Console.Write(range.Cells[i, j].Value2.ToString() + '\t'); // } // } //} //GC.Collect(); //GC.WaitForPendingFinalizers(); // } /// <summary> /// 添加excel表格数据 /// </summary> public void AddData(string[] data) { int count = data.Length; // rowCount += 1; for (int i = 1; i <= count; i++) { wsheet.Cells[rowCount, i] = data[i - 1]; } wbook.Save(); rowCount += 1; colCount = count; }
/*@ToDo save all excel data*/ public void Save_Excel_Handler() { for (int i = 1; i <= All_Pub_List.Count; i++) { Sheet_List[0].Cells[i + 1, 1] = All_Pub_List[i - 1].Nombre; Sheet_List[0].Cells[i + 1, 2] = All_Pub_List[i - 1].Publicaciones; Sheet_List[0].Cells[i + 1, 3] = All_Pub_List[i - 1].Videos; Sheet_List[0].Cells[i + 1, 4] = All_Pub_List[i - 1].Horas; Sheet_List[0].Cells[i + 1, 5] = All_Pub_List[i - 1].Revisitas; Sheet_List[0].Cells[i + 1, 6] = All_Pub_List[i - 1].Estudios; Sheet_List[0].Cells[i + 1, 7] = All_Pub_List[i - 1].Grupo; Sheet_List[0].Cells[i + 1, 8] = All_Pub_List[i - 1].Categoria.ToString(); } ExcelBooks.Save(); }
/* * TODO: (중) 현재 PCFData를 거치지 않고, 바로 Revit과 상호작용하여 정보를 처리하는 중이다. * 이는 데이터를 표현하는 방식이 2개가 되어 같은 정보를 두번 처리하는 비효율을 초래하게 된다. * 그러므로, 이미 데이터를 해석하여 저장한 PCFData를 활용하길 권고하는 바이다. */ //Excel에 현재까지 작업한 데이터를 쓰는 함수 public void Export(PCFData data) { this.UpdateExcelbyRevit(); Excel.Application excelApp = new Excel.Application(); Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(this.ExcelPath); foreach (DataTable table in this.DataSet.Tables) { Excel.Worksheet excelWorkSheet = (from Excel.Worksheet ws in excelWorkBook.Sheets where ws.Name.Equals(table.TableName) select ws).First(); if (excelWorkSheet == null) { excelWorkSheet = excelWorkBook.Sheets.Add(); excelWorkSheet.Name = table.TableName; } for (int i = 1; i < table.Columns.Count + 1; i++) { excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName; } for (int j = 0; j < table.Rows.Count; j++) { for (int k = 0; k < table.Columns.Count; k++) { excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString(); } } } excelWorkBook.Save(); excelWorkBook.Close(); excelApp.Quit(); }
/// <summary> /// open an excel file,then write the content to file /// </summary> /// <param name="FileName">file name</param> /// <param name="findString">first cloumn</param> /// <param name="replaceString">second cloumn</param> public void WriteToExcel(string filename, string findString, string replaceString) { //open string excelName; excelName = _path; object Nothing = System.Reflection.Missing.Value; var app = new Excel.Application(); app.Visible = false; Excel.Workbook mybook = app.Workbooks.Open(excelName, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing); Excel.Worksheet mysheet = (Excel.Worksheet)mybook.Worksheets[1]; mysheet.Activate(); //get activate sheet max row count int maxrow = mysheet.UsedRange.Rows.Count + 1; mysheet.Cells[maxrow, 1] = filename; mysheet.Cells[maxrow, 2] = findString; mysheet.Cells[maxrow, 3] = replaceString; mybook.Save(); mybook.Close(false, Type.Missing, Type.Missing); mybook = null; //quit excel app app.Quit(); }
private void Format(ref Excel.Application excel) { Excel.Workbook wb = excelApp.ActiveWorkbook; Excel.Worksheet ws = wb.ActiveSheet; ws.PageSetup.LeftMargin = 1; ws.PageSetup.RightMargin = 1; ws.PageSetup.TopMargin = 1; ws.PageSetup.BottomMargin = 1; ws.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape; ws.get_Range("A:A").EntireColumn.ColumnWidth = 3; ws.get_Range("B:B").EntireColumn.ColumnWidth = 4; ws.get_Range("C:C").EntireColumn.ColumnWidth = 7; ws.get_Range("D:D").EntireColumn.ColumnWidth = 9; ws.get_Range("E:E").EntireColumn.ColumnWidth = 17; ws.get_Range("F:F").EntireColumn.ColumnWidth = 17; ws.get_Range("G:G").EntireColumn.ColumnWidth = 17; ws.get_Range("H:H").EntireColumn.ColumnWidth = 10; ws.get_Range("I:I").EntireColumn.ColumnWidth = 10; ws.get_Range("J:J").EntireColumn.ColumnWidth = 3; ws.get_Range("K:K").EntireColumn.ColumnWidth = 13; ws.get_Range("L:L").EntireColumn.ColumnWidth = 12; ws.get_Range("M:M").EntireColumn.ColumnWidth = 10; excelApp.Visible = true; excelApp.DisplayAlerts = true; excelApp.Application.Interactive = true; wb.Save(); }
static void Main(string[] args) { string inputFile = @"D:\Test.xlsx"; Excel.Application ExcelApp = new Excel.Application(); Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(inputFile); Excel.Range formatRange; ExcelApp.Visible = true; foreach (Excel.Worksheet ExcelWorksheet in ExcelWorkbook.Sheets) { ExcelWorksheet.Select(Type.Missing); ExcelWorksheet.Columns[1].NumberFormat = ""; ExcelWorksheet.Columns[2].NumberFormat = "yyyy-MM-dd"; // convert format to date ExcelWorksheet.Columns[2].NumberFormat = ""; ExcelWorksheet.Columns[3].NumberFormat = "0.00000"; // convert format to decimal with 5 decimal digits ExcelWorksheet.Columns[3].NumberFormat = ""; ExcelWorksheet.Columns[4].NumberFormat = ""; } ExcelWorkbook.Save(); GC.Collect(); GC.WaitForPendingFinalizers(); ExcelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing); Marshal.FinalReleaseComObject(ExcelWorkbook); ExcelApp.Quit(); Marshal.FinalReleaseComObject(ExcelApp); }
public static void WriteDataToExcelUsingInterop(string exlDataSource, string sheetName, int rowIndex, int columnIndex, string val) { ExlInterop.Application exlApp = new ExlInterop.Application(); exlApp.Visible = true; ExlInterop.Workbook exlWb = null; ExlInterop.Worksheet exlSheet; try { exlWb = exlApp.Workbooks.Open(exlDataSource, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, false, false); exlSheet = (Microsoft.Office.Interop.Excel.Worksheet)exlWb.Worksheets[sheetName]; exlSheet.Cells[rowIndex, columnIndex] = val; exlWb.Save(); } catch (Exception ex) { throw ex; } finally { exlWb.Close(); exlApp.Quit(); releaseProcessObject(exlWb); releaseProcessObject(exlApp); } }
private void InsertBtn_Click(object sender, Win.RoutedEventArgs e) { int k = worksGrid.Items.Count; for (int i = 0; i < k; i++) { GridRows example = ((GridRows)worksGrid.Items[i]); string conc = example.Works.Trim().ToLower(); for (int row = 12; row < 376; row++) { work = wSheet.Cells[row, 3].Text; if (work.Trim() != "") { work = work.ToLower().Trim(); while (work.Contains(" ")) { work = work.Replace(" ", " "); } if (conc.Equals(work)) { wSheet.Cells[row, 5].Value = example.Materials; wSheet.Cells[row, 6].Value = example.Smr; } } } } wBook.Save(); Win.MessageBox.Show("Task completed", "System message: "); }
public void ExcelSave() { try { wb.Save(); ws.Delete(); wb.Close(); excelApp.Quit(); } catch (Exception ex) { Console.WriteLine(ex.Message); wb.Close(); excelApp.Quit(); } }
public void CreateExcel(string DB_PATH) { MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(DB_PATH); MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here int lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row; List <string> EmpList = new List <string> { }; for (int index = 2; index <= lastRow; index++) { System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "F" + index.ToString()).Cells.Value; String Firstname = MyValues.GetValue(1, 4).ToString(); String LastName = MyValues.GetValue(1, 5).ToString(); EmpList.Add(Firstname + " " + LastName); MyRange = MySheet.Cells[index, 6].value(Firstname + " " + LastName); } MyBook.Save(); }
public void Write(DataGrid dataGrid) { worksheet = (_Excel.Worksheet)workbook.Sheets.get_Item(1); for (int j = 0; j < dataGrid.Columns.Count; j++) { //Range myRange = (Range)worksheet.Cells[1, j + 1]; worksheet.Cells[1, j + 1].Font.Bold = true; worksheet.Columns[j + 1].ColumnWidth = 15; //myRange.Value2 = dataGrid.Columns[j].Header; worksheet.Cells[1, j + 1].Value2 = dataGrid.Columns[j].Header; } for (int i = 0; i < dataGrid.Columns.Count; i++) { for (int j = 0; j < dataGrid.Items.Count; j++) { if ((dataGrid.Columns[i].GetCellContent(dataGrid.Items[j]) as TextBlock) == null || (dataGrid.Columns[i].GetCellContent(dataGrid.Items[j]) as TextBlock).Equals("")) { worksheet.Cells[j + 2, i + 1].Value2 = "0"; } else { worksheet.Cells[j + 2, i + 1].Value2 = (dataGrid.Columns[i].GetCellContent(dataGrid.Items[j]) as TextBlock).Text; } } } workbook.Save(); }
/// <summary> /// This method takes DataSet as input paramenter and it exports the same to excel /// </summary> /// <param name="ds"></param> private void ExportDataSetToExcel(DataSet ds) { //Creae an Excel application instance Excel.Application excelApp = new Excel.Application(); //Create an Excel workbook instance and open it from the predefined location Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(@"C:\Users\DEMAR\Documents\Visual Studio 2012\Projects\exportExcel\filesTest\Test.xlsx"); foreach (DataTable table in ds.Tables) { //Add a new worksheet to workbook with the Datatable name Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(); excelWorkSheet.Name = table.TableName; for (int i = 1; i < table.Columns.Count + 1; i++) { excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName; } for (int j = 0; j < table.Rows.Count; j++) { for (int k = 0; k < table.Columns.Count; k++) { excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString(); } } } excelWorkBook.Save(); excelWorkBook.Close(); excelApp.Quit(); }
private void btnAddUser_Click(object sender, RoutedEventArgs e) { //inputları al string name = txtboxName.Text; string surname = txtboxSurname.Text; string telno = txtboxTelNo.Text; string cost = txtboxCost.Text; //uygulamayı aç var app = new Excel.Application(); Excel.Workbook workbook = app.Workbooks.Open(path_excel); Excel.Worksheet sheet = app.ActiveSheet as Excel.Worksheet; //satır sayısını bul Excel.Range usedrange = sheet.UsedRange; int rows = usedrange.Rows.Count; //yerleştir sheet.Cells[rows + 1, 1] = name; sheet.Cells[rows + 1, 2] = surname; sheet.Cells[rows + 1, 3] = telno; sheet.Cells[rows + 1, 4] = cost; sheet.Cells[rows + 1, 5] = durumText; //kapat workbook.Save(); workbook.Close(0); app.Quit(); RefreshDataTable(); }
private bool SetExcel(string size, string owner, int count) { Excel.Application app = null; Excel.Workbook wb = null; Excel.Worksheet ws = null; try { app = new Excel.Application(); wb = app.Workbooks.Open(savePath + @"/" + mainAddress + ".xlsx"); ws = wb.Worksheets.get_Item("Sheet1") as Excel.Worksheet; ws.Cells[count + int.Parse(startRow), int.Parse(ownerCol)].value = owner; ws.Cells[count + int.Parse(startRow), int.Parse(sizeCol)].value = size; } catch (Exception ex) { Console.WriteLine(ex); return(false); } finally { wb.Save(); wb.Close(); app.Quit(); } return(true); }
// Fills ending inventory in last weeks perpetual // Gets passed dictionary // No return static void fillLastPerpetual(Dictionary <string, SalesData> d) { string date = getLastSunday(); string file = @"C:\Dropbox\Liq Perpetuals\Perpetual-WE-" + date; Excel.Application xl = new Excel.Application(); Excel.Workbook xlWorkbook = xl.Workbooks.Open(file); Excel.Worksheet xlSheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlSheet.UsedRange; int rowCount = xlRange.Rows.Count; for (int i = 1; i < rowCount; i++) { string item = (string)(xlRange.Cells[i, 1] as Excel.Range).Value2; if (item == null) { continue; } if (d.ContainsKey(item)) { xlRange.Cells[i, 13].Value2 = d[item].OH; } } xlWorkbook.Save(); xlWorkbook.Close(file); Console.WriteLine("Last weeks perpetual copy completed."); }
private void ExportDataSetToExcel(DataSet ds) { //Creae an Excel application instance Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); //Create an Excel workbook instance and open it from the predefined location Microsoft.Office.Interop.Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(@"D:\test.xls"); foreach (System.Data.DataTable table in ds.Tables) { //Add a new worksheet to workbook with the Datatable name Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(); excelWorkSheet.Name = table.TableName; for (int i = 1; i < table.Columns.Count + 1; i++) { excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName; } for (int j = 0; j < table.Rows.Count; j++) { for (int k = 0; k < table.Columns.Count; k++) { excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString(); } } } excelWorkBook.Save(); excelWorkBook.Close(); excelApp.Quit(); }
/// <summary> /// Excel 转成 pdf 文件 /// </summary> /// <returns>错误信息,null 代表正常</returns> override protected string ExcelToPDF() { string result = null; var application = new excel.Application(); excel.Workbook document = null; try { application.Visible = false; document = application.Workbooks.Open(base._officeFileName); document.ExportAsFixedFormat(excel.XlFixedFormatType.xlTypePDF, base._pdfFileName); document.Save(); } catch (Exception e) { result = e.Message; } finally { document.Close(); uint processID; GetWindowThreadProcessId((IntPtr)application.Application.Hwnd, out processID); System.Diagnostics.Process.GetProcessById((int)processID).Kill(); } return(result); }
public void Save() { if (m_excelApp != null && m_book != null && m_docOpened) { m_book.Save(); } }
private Worksheet objExcelWorkSheet; //定义Workbook工作表对象 #endregion Fields #region Methods // Change the Mapping WorkFlow @2015-7-16 By CC public void EditWorkFlow(string StrTestData) { objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objExcelWorkBooks = objExcelApp.Workbooks; objExcelWorkbook = objExcelWorkBooks.Open(StrTestData, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["WorkFlow"]; //strSheetName is the Sheet Name of Exce,if there is no name then Defult is "1" objExcelWorkSheet.Select(Type.Missing); int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //get the Row Number int columnsint = objExcelWorkSheet.UsedRange.Cells.Columns.Count;//get the column number int rowNo = 0; int ParameterRowNo = 0; int columnNo = 0; int FlagColumnNo = 0; for (int j = 1; j <= columnsint; j++) { string strColumnsName = ((Range)objExcelWorkSheet.Cells[1, j]).Text.ToString(); if (strColumnsName == "Flag") { FlagColumnNo = j; } if (strColumnsName == "BusinessFlow") { rowNo = j; } if (strColumnsName == "ParameterValue") { ParameterRowNo = j; } } for (int i = 2; i <= rowsint; i++) { string strFlagValue = ((Range)objExcelWorkSheet.Cells[i, FlagColumnNo]).Text.ToString(); if (strFlagValue =="Y") { int RowNO = i; objExcelWorkSheet.Cells[RowNO, FlagColumnNo] = "N"; } string strBusinessFlow = ((Range)objExcelWorkSheet.Cells[i, rowNo]).Text.ToString(); if (strBusinessFlow == "HeaderMapping" || strBusinessFlow == "TaxMapping" || strBusinessFlow == "LineItemsMapping") { columnNo = i; string strParameterValue = ((Range)objExcelWorkSheet.Cells[i, ParameterRowNo]).Text.ToString(); if (strParameterValue == "IR" || strParameterValue == "TradeBilling" || strParameterValue == "TradeCredit" || strParameterValue == "TradeDebit" || strParameterValue == "TradeReturn") { objExcelWorkSheet.Cells[i, 1] = "Y"; } } } objExcelWorkbook.Save(); objExcelWorkbook.Close(false, StrTestData, false); objExcelApp.Quit(); NAR(objExcelApp); NAR(objExcelWorkbook); NAR(objExcelWorkSheet); }
private void dokument_beschreiben(String[] zuordnungen) { document_path = tbx_path.Text; try { wu_liste = myExcel.Workbooks.Open(@document_path); myExcel.Visible = false; slist = (Worksheet)wu_liste.Sheets[1]; klist = (Worksheet)wu_liste.Sheets[2]; slist_range = slist.UsedRange; klist_range = klist.UsedRange; add_item_to_log("Zuordnungen werden in Tabelle eingetragen"); for (int i = 0; i < zuordnungen.Length; i++) { string erstwahl = Convert.ToString(slist_range.Cells[i + 2, SPALTE_ERSTWAHL].Value); if (erstwahl != null) { if (erstwahl.Trim() == "") erstwahl = null; } if (zuordnungen[i] != null) { slist_range.Cells[i + 2, SPALTE_ZUORDNUNG].Value = zuordnungen[i]; slist_range.Cells[i + 2, SPALTE_ZUORDNUNG + 1].Value = null; slist_range.Cells[i + 2, SPALTE_WUSTUNDENNEU].Value = schueler_wustunden[i] + kurse_stunden[kurse_id.IndexOf(zuordnungen[i])]; } else if ((schueler_wustunden[i] < 5) || (erstwahl != null)) { slist_range.Cells[i + 2, SPALTE_ZUORDNUNG].Value = null; slist_range.Cells[i + 2, SPALTE_ZUORDNUNG + 1].Value = "!"; } else { slist_range.Cells[i + 2, SPALTE_ZUORDNUNG].Value = null; } } wu_liste.Save(); wu_liste.Close(); } catch (System.Runtime.InteropServices.COMException) { MessageBox.Show("Datei existiert nicht\noder andere COMException"); } finally { try { wu_liste.Save(); wu_liste.Close(); } catch (Exception) { } } }
private static void ProcessClassFolders() { string iRow = null; try { MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(@FileLocation); MySheet = (Excel.Worksheet)MyBook.Sheets["Folder Structure"]; MySheetV = (Excel.Worksheet)MyBook.Sheets["Variables"]; Console.WriteLine("Connected to Spread Sheet"); //Console.ReadLine(); Console.WriteLine("Processing, please wait. If its a large SS then go make a coffee."); ProcessfromRow = Convert.ToInt64(MySheetV.Cells[11, 2].Value); ProcesstoRow = Convert.ToInt64(MySheetV.Cells[12, 2].Value); string str1 = null, str2 = null, str3 = null, str4 = null, str5 = null; bool createnew = false; long fileconturi = 0, recuri = 0; //EmpList.Add(emp); List<Sheets> sheets = new List<Sheets>(); Sheets sheet = new Sheets(); sheet.SheetName = FileLocation; List<Rows> rows = new List<Rows>(); Rows r = new Rows(); for (var i = 2; i < ProcesstoRow + 1; i++) { if(i==350) { var dsd = "sfdsf"; } iRow = i.ToString(); //First child level bool bLevel3New = false; createnew = false; if (MySheet.Cells[i, 1].Value2 != null) { str1 = MySheet.Cells[i, 1].Value.ToString(); createnew = true; } if (MySheet.Cells[i, 2].Value2 != null) { str2 = MySheet.Cells[i, 2].Value.ToString(); createnew = true; } if (MySheet.Cells[i, 3].Value2 != null) { str3 = MySheet.Cells[i, 3].Value.ToString(); createnew=true; bLevel3New = true; } if(createnew) { r = new Rows(); r.level1 = str1; r.level2 = str2; r.level3 = str3; r.SheetLine = i; rows.Add(r); } // New if (MySheet.Cells[i, 4].Value2 != null) { if (bLevel3New == true) { List<Folder> lstFolder = new List<Folder>(); rows.LastOrDefault().Folders = lstFolder; } str4 = MySheet.Cells[i, 4].Value.ToString(); //int lvl4LoopCount = 0; //Check to see if the group identifyer is not blank and has a g if (MySheet.Cells[i, 12].value2 != null && MySheet.Cells[i, 12].value.ToString() == "g") { Console.WriteLine("Grouped string found in level 4"); List<string> lstLvl4 = str4.Split('/').ToList(); foreach (var kkk in lstLvl4.Where(x => x.Length > 2)) { Folder fol = new Folder(); fol.folder = kkk.Trim(); fol.SheetLine = i; rows.LastOrDefault().Folders.Add(fol); //lstFolder.Add(fol); fileconturi = recuri; if (MySheet.Cells[i, 5].Value2 != null) { str5 = MySheet.Cells[i, 5].Value.ToString(); List<Subfolder> lstsubfolder = new List<Subfolder>(); if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g") { List<string> lstLvl5 = str5.Split('/').ToList(); foreach (var lll in lstLvl5.Where(x => x.Length > 2)) { Subfolder sf = new Subfolder(); sf.subfolder = lll.Trim(); sf.SheetLine = i; lstsubfolder.Add(sf); } fol.SubFolders = lstsubfolder; } else { Subfolder sf = new Subfolder(); sf.subfolder = str5.Trim(); sf.SheetLine = i; lstsubfolder.Add(sf); fol.SubFolders = lstsubfolder; } } } } else { Folder fol = new Folder(); fol.folder = str4; fol.SheetLine = i; fileconturi = recuri; if (MySheet.Cells[i, 5].Value2 != null) { List<Subfolder> lstsf = new List<Subfolder>(); str5 = MySheet.Cells[i, 5].Value.ToString(); //r.Subfolder = str5; if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g") { List<string> lstLvl5 = str5.Split('/').ToList(); foreach (var lll in lstLvl5.Where(x => x.Length > 2)) { Subfolder sf = new Subfolder(); sf.subfolder = lll.Trim(); sf.SheetLine = i; lstsf.Add(sf); } } else { Subfolder sf = new Subfolder(); sf.subfolder = str5; sf.SheetLine = i; if (MySheet.Cells[i, 7].value2 != null) { sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString(); } lstsf.Add(sf); } fol.SubFolders = lstsf; } else { if (MySheet.Cells[i, 7].value2 != null) { fol.MigrateLoc = MySheet.Cells[i, 7].Value.ToString(); } } rows.LastOrDefault().Folders.Add(fol); } } else { if (MySheet.Cells[i, 5].Value2 != null) { if (rows.LastOrDefault().Folders.LastOrDefault().SubFolders==null) { List<Subfolder> lstsf = new List<Subfolder>(); rows.LastOrDefault().Folders.LastOrDefault().SubFolders = lstsf; } str5 = MySheet.Cells[i, 5].Value.ToString(); if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g") { List<string> lstLvl5 = str5.Split('/').ToList(); foreach (var lll in lstLvl5.Where(x => x.Length > 2)) { Subfolder sf = new Subfolder(); sf.subfolder = lll.Trim(); sf.SheetLine = i; if (MySheet.Cells[i, 7].value2 != null) { sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString(); } rows.LastOrDefault().Folders.LastOrDefault().SubFolders.Add(sf); } } else { Subfolder sf = new Subfolder(); sf.subfolder = str5.Trim(); sf.SheetLine = i; if (MySheet.Cells[i, 7].value2 != null) { sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString(); } //lstsubfolder.Add(sf); //fol.SubFolders = lstsubfolder; //rows.LastOrDefault().Folders.LastOrDefault().SubFolders = lstsubfolder; rows.LastOrDefault().Folders.LastOrDefault().SubFolders.Add(sf); } } } //End New } sheet.Rows = rows; sheets.Add(sheet); // string json = JsonConvert.SerializeObject(sheets.ToArray()); //FileLocation var loc = Path.GetDirectoryName(FileLocation); System.IO.File.WriteAllText(@loc + "\\SSprocessTIP.json", json); // MyBook.Save(); MyBook.Close(null, null, null); MyApp.Quit(); Console.WriteLine("Successful in building Json file."); Repeatmenu(); //} } catch (Exception exc) { MyBook.Save(); MyBook.Close(null, null, null); MyApp.Quit(); // Console.WriteLine("Error: " + exc.Message.ToString()+" - "+iRow); return; } }
// Insert Row in the Driver.xls public void InsertRowsValues(string StrDriverPath, string StrCaseName, string StrTestDataPath, string StrComCode, string StrAsset, string StrRunTime) { objExcelApp = new Microsoft.Office.Interop.Excel.Application(); objExcelWorkBooks = objExcelApp.Workbooks; objExcelWorkbook = objExcelWorkBooks.Open(StrDriverPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets["Driver"]; //strSheetName是指的Exce工作簿的Sheet名,如果没有命名则为"1" objExcelWorkSheet.Select(Type.Missing); int rowsint = objExcelWorkSheet.UsedRange.Cells.Rows.Count; //得到行数 if (rowsint > 1) { for (int i = 2;i <= rowsint; i++) { string strFlagValue = ((Range)objExcelWorkSheet.Cells[i, 1]).Text.ToString(); if (strFlagValue == "Y") { int RowNO = i; objExcelWorkSheet.Cells[RowNO, 1] = "N"; } } } if (rowsint == 1) { rowsint = 2; } objExcelWorkSheet.Cells[rowsint, 1] = "Y"; objExcelWorkSheet.Cells[rowsint, 2] = "fa_30_saptao_mec"; objExcelWorkSheet.Cells[rowsint, 3] = StrRunTime; objExcelWorkSheet.Cells[rowsint, 4] = StrRunTime; objExcelWorkSheet.Cells[rowsint, 5] = StrCaseName; objExcelWorkSheet.Cells[rowsint, 6] = StrTestDataPath; objExcelWorkSheet.Cells[rowsint, 7] = "Run"; objExcelWorkSheet.Cells[rowsint, 8] = StrComCode; objExcelWorkSheet.Cells[rowsint, 9] = StrAsset; objExcelWorkbook.Save(); objExcelWorkbook.Close(false, StrDriverPath, false); objExcelApp.Quit(); NAR(objExcelApp); NAR(objExcelWorkbook); NAR(objExcelWorkSheet); }
private static void processFolders() { try { MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(@FileLocation); MySheet = (Excel.Worksheet)MyBook.Sheets["Folder Structure"]; MySheetV = (Excel.Worksheet)MyBook.Sheets["Variables"]; Console.WriteLine("Connected to Spread Sheet"); Console.ReadLine(); ProcessfromRow = Convert.ToInt64(MySheetV.Cells[11, 2].Value); ProcesstoRow = Convert.ToInt64(MySheetV.Cells[12, 2].Value); string str1 = null, str2 = null, str3 = null, str4 = null, str5 = null; bool createnew = false; long fileconturi = 0, recuri = 0; int lvl1Value = 10, lvl2Value = 10; //EmpList.Add(emp); List<Sheets> sheets = new List<Sheets>(); Sheets sheet = new Sheets(); sheet.SheetName = FileLocation; List<Rows> rows = new List<Rows>(); Rows r = new Rows(); for (var i = 2; i < ProcesstoRow+1; i++) { //First child level if (MySheet.Cells[i, y].Value2 != null) { str1 = MySheet.Cells[i, y].Value.ToString(); createnew = true; lvl1Value = lvl1Value + 10; //lvl2Value = 10; //lvl3Value = 10; y = y + 1; } else { y = y + 1; } if (MySheet.Cells[i, y].Value2 != null) { str2 = MySheet.Cells[i, y].Value.ToString(); if (createnew) { lvl2Value = lvl2Value + 10; //lvl3Value = 10; } y = y + 1; } else { y = y + 1; } if (MySheet.Cells[i, y].Value2 != null) { str3 = MySheet.Cells[i, y].Value.ToString(); if (createnew) { r = new Rows(); r.level1 = str1; r.level2 = str2; r.level3 = str3; } y = y + 1; } else { y = y + 1; r = rows.LastOrDefault(); } List<Folder> lstFolder = new List<Folder>(); if (MySheet.Cells[i, 4].Value2 != null) { str4 = MySheet.Cells[i, 4].Value.ToString(); //Check to see if the group identifyer is not blank and has a g if (MySheet.Cells[i, 12].value2 != null && MySheet.Cells[i, 12].value.ToString() == "g") { Console.WriteLine("Grouped string found in level 4"); List<string> lstLvl4 = str4.Split('/').ToList(); foreach (var kkk in lstLvl4.Where(x => x.Length > 2)) { Folder fol = new Folder(); fol.folder = kkk.Trim(); lstFolder.Add(fol); fileconturi = recuri; if (MySheet.Cells[i, 5].Value2 != null) { str5 = MySheet.Cells[i, 5].Value.ToString(); List<Subfolder> lstsubfolder = new List<Subfolder>(); if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g") { List<string> lstLvl5 = str5.Split('/').ToList(); foreach (var lll in lstLvl5.Where(x => x.Length > 2)) { Subfolder sf = new Subfolder(); sf.subfolder = lll.Trim(); lstsubfolder.Add(sf); } fol.SubFolders = lstsubfolder; } else { Subfolder sf = new Subfolder(); sf.subfolder = str5.Trim(); lstsubfolder.Add(sf); fol.SubFolders = lstsubfolder; } } } } else { Folder fol = new Folder(); fol.folder = str4; fileconturi = recuri; if (MySheet.Cells[i, 5].Value2 != null) { List<Subfolder> lstsf = new List<Subfolder>(); str5 = MySheet.Cells[i, 5].Value.ToString(); //r.Subfolder = str5; if (MySheet.Cells[i, 13].value2 != null && MySheet.Cells[i, 13].value.ToString() == "g") { List<string> lstLvl5 = str5.Split('/').ToList(); foreach (var lll in lstLvl5.Where(x => x.Length > 2)) { Subfolder sf = new Subfolder(); sf.subfolder = lll.Trim(); lstsf.Add(sf); //recsuburi = CreateNewRMSubFolder(recuri, lll.Trim(), db, i); } } else { Subfolder sf = new Subfolder(); sf.subfolder = str5; if (MySheet.Cells[i, 7].value2 != null) { sf.MigrateLoc = MySheet.Cells[i, 7].Value.ToString(); } lstsf.Add(sf); //fol.SubFolders=lstsf; // //recsuburi = CreateNewRMSubFolder(recuri, str5, db, i); } fol.SubFolders = lstsf; } else { if (MySheet.Cells[i, 7].value2 != null) { fol.MigrateLoc = MySheet.Cells[i, 7].Value.ToString(); } } lstFolder.Add(fol); } r.Folders = lstFolder; } y = 1; if (str4 != null) { MySheet.Cells[i, 8] = fileconturi.ToString(); } else { //Remove for basic testing //long Newuri = CreateNewRMfolder(str1, str2, str3, str4, str5, str3Title); // //Console.WriteLine("New folder created (Uri: " + fileconturi.ToString() + ")"); } r.SheetLine = i; //7 //r.RMuri = fileconturi.ToString(); //r.Timestamp = DateTime.Now; rows.Add(r); } sheet.Rows = rows; sheets.Add(sheet); // string json = JsonConvert.SerializeObject(sheets.ToArray()); //FileLocation var loc = Path.GetDirectoryName(FileLocation); System.IO.File.WriteAllText(@loc+"\\SSprocessTIP.json", json); // MyBook.Save(); MyBook.Close(null, null, null); MyApp.Quit(); //} } catch (Exception exc) { MyBook.Save(); MyBook.Close(null, null, null); MyApp.Quit(); // Console.WriteLine("Error: "+exc.Message.ToString()); return; } }
private static void SynctoExcel(string excel, string json) { List<Sheets> myMessage = JsonConvert.DeserializeObject<List<Sheets>>(File.ReadAllText(@FileLocation)); MyApp = new Excel.Application(); MyApp.Visible = false; MyBook = MyApp.Workbooks.Open(@FileLocation); MySheet = (Excel.Worksheet)MyBook.Sheets["Folder Structure"]; // foreach (var file in myMessage) { foreach (Rows r in file.Rows) { var snumber = r.SheetLine; MySheet.Cells[snumber, 8] = r.RMuri; MySheet.Cells[snumber, 9] = r.OriginalFileCount; MySheet.Cells[snumber, 10] = r.RMFileCount; } } MyBook.Save(); MyBook.Close(null, null, null); MyApp.Quit(); }
private void dokument_auslesen() { document_path = tbx_path.Text; int schueler_anzahl = new int(); try { // Pfad wird ausgelesen wu_liste = myExcel.Workbooks.Open(@document_path); // Variablen init myExcel.Visible = false; slist = (Worksheet)wu_liste.Sheets[1]; klist = (Worksheet)wu_liste.Sheets[2]; slist_range = slist.UsedRange; klist_range = klist.UsedRange; schueler_id.Clear(); schueler_namen.Clear(); schueler_vornamen.Clear(); schueler_klasse.Clear(); schueler_klassenlehrer.Clear(); schueler_erstwahl.Clear(); schueler_zweitwahl.Clear(); schueler_drittwahl.Clear(); kurse_id.Clear(); kurse_maxpersonen.Clear(); kurse_minpersonen.Clear(); add_item_to_log("Schüler werden ausgelesen"); for (int i = 0; !String.Equals(slist_range.Cells[i + 2, SPALTE_NAME].Value, null); i++) { if (i % 25 == 24) { add_item_to_log(Convert.ToString(i + 1) + " Schüler wurden ausgelesen"); } schueler_id.Add(i + 2); String name = slist_range.Cells[i + 2, SPALTE_NAME].Value; String vorname = slist_range.Cells[i + 2, SPALTE_VORNAME].Value; String klasse = slist_range.Cells[i + 2, SPALTE_KLASSE].Value; String klassenlehrer = slist_range.Cells[i + 2, SPALTE_KLASSENLEHRER].Value; String erstwahl = slist_range.Cells[i + 2, SPALTE_ERSTWAHL].Value; String zweitwahl = slist_range.Cells[i + 2, SPALTE_ZWEITWAHL].Value; String drittwahl = slist_range.Cells[i + 2, SPALTE_DRITTWAHL].Value; String zuordnung = slist_range.Cells[i + 2, SPALTE_ZUORDNUNG].Value; String wustunde = Convert.ToString(slist_range.Cells[i + 2, SPALTE_WUSTUNDENALT].Value); if (!String.IsNullOrWhiteSpace(name)) schueler_namen.Add(name.Trim()); else schueler_namen.Add(null); if (!String.IsNullOrWhiteSpace(vorname)) schueler_vornamen.Add(vorname.Trim()); else schueler_vornamen.Add(null); if (!String.IsNullOrWhiteSpace(klasse)) schueler_klasse.Add(klasse.Trim()); else schueler_klasse.Add(null); if (!String.IsNullOrWhiteSpace(klassenlehrer)) schueler_klassenlehrer.Add(klassenlehrer.Trim()); else schueler_klassenlehrer.Add(null); if (!String.IsNullOrWhiteSpace(erstwahl)) schueler_erstwahl.Add(erstwahl.Trim()); else schueler_erstwahl.Add(null); if (!String.IsNullOrWhiteSpace(zweitwahl)) schueler_zweitwahl.Add(zweitwahl.Trim()); else schueler_zweitwahl.Add(null); if (!String.IsNullOrWhiteSpace(drittwahl)) schueler_drittwahl.Add(drittwahl.Trim()); else schueler_drittwahl.Add(null); if (!String.IsNullOrWhiteSpace(zuordnung)) schueler_zuordnung.Add(zuordnung.Trim()); else schueler_zuordnung.Add(null); if (!String.IsNullOrWhiteSpace(wustunde)) schueler_wustunden.Add(Convert.ToInt32(wustunde.Trim())); else schueler_wustunden.Add(0); schueler_anzahl = i; } add_item_to_log("Alle " + Convert.ToString(schueler_anzahl + 1) + " Schüler wurden ausgelesen"); add_item_to_log("Kurse werden ausgelesen"); for (int i = 0; !String.Equals(klist_range.Cells[i + 2, SPALTE_KURSID].Value, null); i++) { kurse_id.Add(klist_range.Cells[i + 2, SPALTE_KURSID].value); kurse_name.Add(klist_range.Cells[i + 2, SPALTE_KURSNAMEN].value); kurse_maxpersonen.Add(Convert.ToInt32(klist_range.Cells[i + 2, SPALTE_MAXPERSONEN].value)); kurse_minpersonen.Add(Convert.ToInt32(klist_range.Cells[i + 2, SPALTE_MINPERSONEN].value)); kurse_stunden.Add(Convert.ToInt32(klist_range.Cells[i + 2, SPALTE_KSTUNDEN].value)); kurse_size.Add(0); } add_item_to_log("Auslesen vollendet"); wu_liste.Save(); wu_liste.Close(); } catch (Exception e) { MessageBox.Show(e.Message); } finally { try { wu_liste.Save(); wu_liste.Close(); } catch (Exception) { } } }
public void ZvedVidomist(int semestr, string subject, string mount) { _subject = subject; Semestr = semestr; _arabSemestr = semestr == 1 ? ArgDataSet.Tables[CurrentGroupName].Rows[0]["Перше півріччя"].ToString() : ArgDataSet.Tables[CurrentGroupName].Rows[0]["Друге півріччя"].ToString(); string sT; if (mount.Equals("")) { sT = semestr == 1 ? "1-ше півріччя" : "2-ге півріччя"; } else sT = mount + " місяць"; var existsPath = Environment.CurrentDirectory.Substring(0, Environment.CurrentDirectory.Length - 9) + @"Data\" + "Empty book.xls"; var pathTogroup = Environment.CurrentDirectory.Substring(0, Environment.CurrentDirectory.Length - 9) + @"User Data\Зведена відомість успішності\" + "Зведена відомість успішності за " + sT + ".xls"; var pathToTamplateVidomist = Environment.CurrentDirectory.Substring(0, Environment.CurrentDirectory.Length - 9) + @"Data\" + "Зведена відомість.xls"; if (!System.IO.File.Exists(pathTogroup)) System.IO.File.Copy(existsPath, pathTogroup, true); _xlApp = new Excel.Application(); _xlApp.DisplayAlerts = false; _xlWorkBook = _xlApp.Workbooks.Open(pathTogroup); _xlWorkSheet = (Excel.Worksheet)_xlWorkBook.Worksheets.Item[1]; bool ifExist = true; for (int i = 1; i <= _xlWorkBook.Sheets.Count; i++) { _xlWorkSheet = (Excel.Worksheet)_xlWorkBook.Worksheets.Item[i]; if (_xlWorkSheet.Name.Contains("Sheet") || _xlWorkSheet.Name.Contains("Аркуш") || _xlWorkSheet.Name.Contains("Лист")) { _xlWorkSheet.Name = CurrentGroupName; ifExist = false; _xlWorkBook.Save(); break; } if (!_xlWorkSheet.Name.Equals(CurrentGroupName)) continue; if (MessageBox.Show(Resources.ExcelWork_ZvedVidomist_Для_поточної_групи__ + CurrentGroupName + Resources.ExcelWork_ZvedVidomist_, "Обережно, ви можете втратити дані", MessageBoxButtons.YesNo) == DialogResult.No) { _xlWorkBook.Close(); _xlApp.Quit(); return; } //xlWorkSheet.Delete(); ifExist = false; //xlWorkBook.Save(); break; } if (ifExist) { _xlWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); _xlApp.DisplayAlerts = false; _xlWorkBook.Worksheets.get_Item(1).Name = CurrentGroupName; _xlWorkBook.Save(); } _xlWorkSheet = _xlWorkBook.Worksheets.get_Item(CurrentGroupName); ReloadSheet(pathToTamplateVidomist); String[] forma = { "Іспит", "Д/З", "КП", "Залік", "КП" }; char startX = 'E'; char pos = ++startX; startX--; int count = 0; int otherCount = 0; for (int i = 0; i < forma.Length; i++) { bool bl = false; for (int row = 0; row < DsRobPlan.Tables[CurrentGroupName].Rows.Count; row++) { try { if (!DsRobPlan.Tables[CurrentGroupName].Rows[row][forma[i] + " [семестр " + _arabSemestr + "]"].ToString().Equals("0") && i != 2 && i != 4) { bl = true; startX++; _xlWorkSheet.Cells[9, startX.ToString()].Value = DsRobPlan.Tables[CurrentGroupName].Rows[row]["Назви навчальних дисциплін"].ToString(); _xlWorkSheet.Cells[43, startX.ToString()].Value = DsRobPlan.Tables[CurrentGroupName].Rows[row]["викладач"].ToString(); _xlWorkSheet.Cells[43, startX.ToString()].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; _xlWorkSheet.Cells[9, startX.ToString()].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; _xlWorkSheet.Cells[9, startX.ToString()].ColumnWidth = ColumnWidth(DsRobPlan.Tables[CurrentGroupName].Rows[row]["Назви навчальних дисциплін"].ToString()); count++; } } catch (ArgumentException) { continue; } if (row == DsRobPlan.Tables[CurrentGroupName].Rows.Count - 1 && i != 2 && i != 4 && bl) { _xlWorkSheet.Range[pos.ToString() + 8, startX.ToString() + 8].Merge(); _xlWorkSheet.Range[pos.ToString() + 8, startX.ToString() + 8].Value = forma[i]; _xlWorkSheet.Range[pos.ToString() + 8, startX.ToString() + 8].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; _xlWorkSheet.Range[pos.ToString() + 8, startX.ToString() + 8].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; pos = startX; pos++; bl = false; } if (!mount.Equals("")) continue; if (!DsRobPlan.Tables[CurrentGroupName].Rows[row][forma[i] + " [семестр " + _arabSemestr + "]"].ToString().Equals("0") && i == 2 && i != 4 && !DsRobPlan.Tables[CurrentGroupName].Rows[row][forma[i] + " [семестр " + _arabSemestr + "]"].ToString().Equals("-1")) { startX++; _xlWorkSheet.Cells[9, startX.ToString()].Value = DsRobPlan.Tables[CurrentGroupName].Rows[row]["Назви навчальних дисциплін"].ToString(); _xlWorkSheet.Cells[43, startX.ToString()].Value = DsRobPlan.Tables[CurrentGroupName].Rows[row]["викладач"].ToString(); _xlWorkSheet.Cells[9, startX.ToString()].HorizontalAlignment = Excel.XlLineStyle.xlContinuous; _xlWorkSheet.Cells[9, startX.ToString()].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; _xlWorkSheet.Cells[43, startX.ToString()].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; _xlWorkSheet.Cells[43, startX.ToString()].ColumnWidth = ColumnWidth(DsRobPlan.Tables[CurrentGroupName].Rows[row]["Назви навчальних дисциплін"].ToString()); count++; pos = startX; pos++; } if (i == 4 && DsRobPlan.Tables[CurrentGroupName].Rows[row][forma[i] + " [семестр " + _arabSemestr + "]"].ToString().Equals("-1")) { otherCount++; startX++; _xlWorkSheet.Cells[9, startX.ToString()].Value = DsRobPlan.Tables[CurrentGroupName].Rows[row]["Назви навчальних дисциплін"].ToString(); _xlWorkSheet.Cells[43, startX.ToString()].Value = DsRobPlan.Tables[CurrentGroupName].Rows[row]["викладач"].ToString(); _xlWorkSheet.Cells[9, startX.ToString()].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; _xlWorkSheet.Cells[43, startX.ToString()].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; _xlWorkSheet.Cells[43, startX.ToString()].ColumnWidth = ColumnWidth(DsRobPlan.Tables[CurrentGroupName].Rows[row]["Назви навчальних дисциплін"].ToString()); } } _xlWorkBook.Save(); } //добавлення та формування клітини з середнім балом startX++; _xlWorkSheet.Cells[9, startX.ToString()].Value = "Середній бал"; _xlWorkSheet.Cells[9, startX.ToString()].ColumnWidth = 6.2; _xlWorkSheet.Cells[9, startX.ToString()].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; pos = 'E'; for (int i = 0; i < count; i++) pos++; _xlWorkSheet.Range["F7", pos.ToString() + 7].Merge(); _xlWorkSheet.Range["F7", pos.ToString() + 7].Value = "Предмети"; _xlWorkSheet.Range["F7", pos.ToString() + 7].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; _xlWorkSheet.Range["F7", pos.ToString() + 7].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; _xlWorkBook.Save(); int startY = 11; int studCount = 0; foreach (DataRow dt in DsRobPlan.Tables["Студенти"].Rows.Cast<DataRow>().Where(dt => dt[3].ToString().Equals(CurrentGroupName))) { _xlWorkSheet.Range["D" + startY].Value = dt[1].ToString(); if (dt[4].ToString().Equals("п")) _xlWorkSheet.Range["E" + startY].Value = dt[4].ToString(); startY++; studCount++; } //куратор _xlWorkSheet.Range["K45"].Value = "/ " + CurrentCurator() + " /"; if (startY != 40) _xlWorkSheet.Range["A" + startY, "IV" + 40].Delete(); startY += 3; _xlWorkSheet.Range["C7", startX + startY.ToString()].Borders.LineStyle = Excel.XlLineStyle.xlContinuous; if (mount.Equals("")) { _xlWorkSheet.Range["C4"].Value = "спеціальності \"" + ArgDataSet.Tables[CurrentGroupName].Rows[0]["Спеціальність"] + "\""; int year = Convert.ToInt32(ArgDataSet.Tables[CurrentGroupName].Rows[0]["Рік"].ToString()) + 1; _xlWorkSheet.Range["D5"].Value = "групи " + CurrentGroupName + " за " + _arabSemestr + " семестр " + ArgDataSet.Tables[CurrentGroupName].Rows[0]["Рік"] + "-" + year + " навчального року"; } else { _xlWorkSheet.Range["D4"].Value = "спеціальності \"" + ArgDataSet.Tables[CurrentGroupName].Rows[0]["Спеціальність"] + "\""; int year = Convert.ToInt32(ArgDataSet.Tables[CurrentGroupName].Rows[0]["Рік"].ToString()) + 1; _xlWorkSheet.Range["C5"].Value = "за місяць " + mount + year + "р."; _xlWorkSheet.Range["C6"].Value = "група " + CurrentGroupName; } var pathToOblic = Environment.CurrentDirectory.Substring(0, Environment.CurrentDirectory.Length - 9) + @"User Data\Облік успішності\" + CurrentGroupName + ".xls"; _xlWorkBook.Save(); //занесення оцінок if (System.IO.File.Exists(pathToOblic) && mount.Equals("")) { //прохід через усі записані у зведену відомість предмети DataTable dataTable = GetThePas(pathToOblic); startX = 'E'; for (int i = 0; i < count + otherCount; i++) { startX++; string likeSheet = CutSheetName(_xlWorkSheet.Range[startX.ToString() + 9].Value.ToString(), "_" + _arabSemestr + "_"); //MessageBox.Show(likeSheet); int negatPasCount = 0, superNegativPasCount = 0; int currentRow = 0; for (int j = 1; j <= studCount; j++) { try { bool bl = dataTable.Rows[j - 1][likeSheet].ToString().Equals("0"); if (bl) continue; } catch (Exception) { break; } currentRow++; var pas = dataTable.Rows[currentRow - 1][likeSheet].ToString(); int cell = j + 10; _xlWorkSheet.Range[startX.ToString() + cell].Value = pas; if (pas == null || pas.Equals("") || pas.Equals(" ")) { continue; } if (pas.Contains("з")) continue; try { int number = Convert.ToInt32(pas); if (number <= 3) superNegativPasCount++; else if (number < 7) negatPasCount++; } catch (Exception) { // ignored } } _xlWorkSheet.Range[startX.ToString() + (studCount + 11)].Formula = "=" + (studCount - superNegativPasCount) + "/" + studCount; _xlWorkSheet.Range[startX.ToString() + (studCount + 12)].Formula = "=" + (studCount - (superNegativPasCount + negatPasCount)) + "/" + studCount; } _xlWorkBook.Save(); startX = 'E'; for (int i = 0; i < count + otherCount; i++) startX++; char begin = startX; char averageBal = ++startX; startX++; for (int i = 11; i < studCount + 11; i++) { _xlWorkSheet.Range[averageBal.ToString() + i].Formula = "=AVERAGE(" + "F" + i + ":" + begin + i + ") - 0.5"; _xlWorkSheet.Range[averageBal.ToString() + i].NumberFormatLocal = "##"; string s1 = _xlWorkSheet.Range["E" + i].Value; double s2; try { s2 = _xlWorkSheet.Range[averageBal.ToString() + i].Value; } catch (Exception) { continue; } if (s1 == null) { if (s2 > 7) { _xlWorkSheet.Range[startX.ToString() + i].Value = "1"; if (dataTable.Rows[i - 11]["підвищена стипендія"].ToString().Equals("1")) _xlWorkSheet.Range[startX.ToString() + i].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); } } } _xlWorkBook.Save(); } startX = 'E'; for (int i = 0; i < count + otherCount + 1; i++) startX++; char stypendia = ++startX; startX++; int xlIter = 10; foreach (DataRow dt in DsRobPlan.Tables["Студенти"].Rows.Cast<DataRow>().Where(dt => dt[3].ToString().Equals(CurrentGroupName))) { xlIter++; var whatType = dt[0]; if (whatType != null) { bool bl = true; var whatype2 = _xlWorkSheet.Range[stypendia.ToString() + xlIter].Value; if (whatype2 == null) bl = false; if (whatType.ToString().Contains("сир")) _xlWorkSheet.Range[startX.ToString() + xlIter].Value = "с"; else if (whatType.ToString().Contains("гір") && bl) _xlWorkSheet.Range[startX.ToString() + xlIter].Value = "г"; else if (whatType.ToString().Contains("інва")) _xlWorkSheet.Range[startX.ToString() + xlIter].Value = "і"; } } _xlWorkBook.Save(); _xlWorkBook.Close(); _xlApp.Quit(); }