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