static void Main(string[] args) { string templateFileName = @"C:\temp\AddDataTesting\InvoiceTemplate.xlsx"; string newFile = @"C:\temp\AddDataTesting\InvoiceAdds.xlsx"; ExcelHelper.ExcelApp app = new ExcelHelper.ExcelApp(); app.OpenWorkBookTemplate(newFile, templateFileName); System.Data.DataTable dt = app.GetFirstOrDefault(); app.InsertRow(newFile, "Sheet1", 6); app.UpdateRange(newFile, "Sheet1", 1, 2, "newValue"); app.SaveWorkBooks(); }
static void ProcessFile(string filename) { using (ExcelHelper.ExcelApp app = new ExcelHelper.ExcelApp()) { using (ExcelHelper.ExcelApp app2 = new ExcelHelper.ExcelApp()) { string templateName = @"C:\temp\ConsoleTestExcelHelper\Template.xltx"; //string templateName = "Template.xltx"; //app.OpenWorkBook(@"C:\temp\Event #5 - FULL EVENT REPORT.xls"); app.OpenWorkBook(filename); System.IO.FileInfo fi = new System.IO.FileInfo(filename); app2.OpenWorkBook(@"C:\temp\EquipmentRate.xlsx"); //app.OpenWorkBook(@"C:\temp\ConsoleTestExcelHelper\TestFile1.xlsx"); //app.OpenWorkBook(@"C:\temp\ConsoleTestExcelHelper\TestFile2.xlsx"); //app.SaveWorkBook(@"C:\temp\ConsoleTestExcelHelper\TestFile1.xlsx"); //app.SaveWorkBook(@"C:\temp\ConsoleTestExcelHelper\TestFile2.xlsx"); //app.OpenWorkBookTemplate(@"C:\temp\ConsoleTestExcelHelper\TestFile3t.xlsx",templateName); //app.SaveWorkBooks(); //var results = from myRow in myDataTable.AsEnumerable() // where myRow.Field<int>("RowNo") == 1 // select myRow; StringBuilder sbLog = new StringBuilder(); var eventDescr = app.GetFirstOrDefault().AsEnumerable().FirstOrDefault()[0].ToString(); var runBy = app.GetFirstOrDefault().AsEnumerable().FirstOrDefault()[1].ToString(); System.Diagnostics.Trace.WriteLine(eventDescr.ToString()); var columnNames = from myRow in app.GetFirstOrDefault().Rows[5].ItemArray select myRow; string commaDelimitedColumnNames = ""; foreach (var columnName in columnNames) { commaDelimitedColumnNames += columnName.ToString() + ","; System.Diagnostics.Trace.WriteLine(columnName.ToString()); } commaDelimitedColumnNames += "LaborRate,EquipmentRate,SaltBagRate,SaltTonRate,LaborCalc,LaborNotes,ProductCalc,ProductNotes"; var data = from myRow in app.GetFirstOrDefault().AsEnumerable().Skip(8) where myRow[3] != null select myRow; System.Diagnostics.Trace.WriteLine(data.Count().ToString()); var subs = from subrow in data where subrow[3].ToString().Trim().StartsWith("SUB") orderby subrow[3].ToString() select subrow; System.Diagnostics.Trace.WriteLine(subs.Count().ToString()); string CurrentSub = ""; int LaborRate = 0; int EquipmentRate = 0; int SaltBagRate = 0; int SaltTonRate = 0; StringBuilder sb = new StringBuilder(); var rateColumnNames = from myRow in app2.GetFirstOrDefault().Rows[0].ItemArray select myRow; int iLaborColumnNumber = -1; int iSaltBagsColumn = -1; int iSaltTonsColumn = -1; int currentColumn = 0; foreach (var col in rateColumnNames) { if (col.ToString() == "Labor") { iLaborColumnNumber = currentColumn; } if (col.ToString() == "Salt Bags") { iSaltBagsColumn = currentColumn; } if (col.ToString() == "Salt Tons") { iSaltTonsColumn = currentColumn; } if (iLaborColumnNumber > 0 && iSaltBagsColumn > 0 && iSaltTonsColumn > 0) { break; } currentColumn += 1; } foreach (var DataItem in subs) { int ColCount = 0; if (CurrentSub != DataItem[3].ToString()) { if (CurrentSub != "") { WriteSbToCSV(eventDescr, CurrentSub, sb); } CurrentSub = DataItem[3].ToString(); sb = new StringBuilder(); sb.AppendLine(commaDelimitedColumnNames); LaborRate = 0; EquipmentRate = 0; SaltBagRate = 0; SaltTonRate = 0; } var getRates = from labor in app2.GetFirstOrDefault().AsEnumerable() where labor[0].ToString() == CurrentSub select labor; if (getRates.Count() == 1) { LaborRate = int.Parse(getRates.FirstOrDefault()[iLaborColumnNumber].ToString()); SaltBagRate = int.Parse(getRates.FirstOrDefault()[iSaltBagsColumn].ToString()); SaltTonRate = int.Parse(getRates.FirstOrDefault()[iSaltTonsColumn].ToString()); } string JobType = ""; float Duration = 0; float EmployeeCount = 0; float ProductQty = 0; string JobId = ""; foreach (var columnName in columnNames) { string searchFor = ","; string replaceWith = "_"; string[] equips; string[] equipRates; string columnValue; if (columnName.ToString().Contains("Equipment")) { columnValue = DataItem[ColCount].ToString().Trim(); } else { columnValue = DataItem[ColCount].ToString().Replace(searchFor, replaceWith).Trim(); } equips = columnValue.Split(','); equipRates = new string[equips.Length]; if (equips.Length > 1) { System.Diagnostics.Trace.WriteLine(columnValue); } if (columnName.ToString() == "Job ID") { JobId = columnValue; } if (columnName.ToString() == "Job Type Name") { JobType = columnValue; } if (columnName.ToString().Contains("Employees") && columnValue != "") { if (!float.TryParse(columnValue, out EmployeeCount)) { EmployeeCount = 0; LogIssue(sbLog, fi.Name, CurrentSub, JobId, "Invalid EmployeeCount", columnValue); } } if (columnName.ToString() == "Duration") { if (!float.TryParse(columnValue, out Duration)) { Duration = 0; LogIssue(sbLog, fi.Name, CurrentSub, JobId, "Invalid Duration", columnValue); } } if (columnName.ToString().Contains("SaltBags") && columnValue != "") { if (!float.TryParse(columnValue, out ProductQty)) { ProductQty = 0; LogIssue(sbLog, fi.Name, CurrentSub, JobId, "Invalid SaltBags", columnValue); } } if (columnName.ToString().Contains("SaltApplied") && columnValue != "") { if (!float.TryParse(columnValue, out ProductQty)) { ProductQty = 0; LogIssue(sbLog, fi.Name, CurrentSub, JobId, "Invalid SaltApplied", columnValue); } } sb.Append(columnValue + ","); if (columnName.ToString().Contains("Equipment") && columnValue != "") { if (getRates.Count() == 1) { int iEquipColumnNumber = -1; int equipCurrentColumn = 0; foreach (var col in rateColumnNames) { if (col.ToString() == columnValue.ToString()) { iEquipColumnNumber = equipCurrentColumn; break; } equipCurrentColumn += 1; } if (iEquipColumnNumber >= 0) { if (columnName.ToString().Contains("Haul")) { System.Diagnostics.Trace.WriteLine(CurrentSub + ": Sub has Haul Data"); } EquipmentRate = int.Parse(getRates.FirstOrDefault()[iEquipColumnNumber].ToString()); } } } ColCount += 1; } string LaborCalcNotes = ""; string ProductCalcNotes = ""; float LaborCalc = CalcLabor(JobType, Duration, EmployeeCount, LaborRate, EquipmentRate, ref LaborCalcNotes); if (LaborCalcNotes != "") { LogIssue(sbLog, fi.Name, CurrentSub, JobId, "Issues With Labor Calc", LaborCalcNotes); } float ProductCalc = CalcProduct(JobType, ProductQty, SaltBagRate, SaltTonRate, ref ProductCalcNotes); if (ProductCalcNotes != "") { LogIssue(sbLog, fi.Name, CurrentSub, JobId, "Issues With Product Calc", ProductCalcNotes); } sb.Append(LaborRate.ToString() + "," + EquipmentRate.ToString() + "," + SaltBagRate + "," + SaltTonRate + "," + LaborCalc.ToString() + "," + LaborCalcNotes + "," + ProductCalc.ToString() + "," + ProductCalcNotes); sb.AppendLine(); EquipmentRate = 0; } System.Diagnostics.Trace.WriteLine(sb.ToString()); WriteSbToCSV(eventDescr, CurrentSub, sb); if (sbLog.ToString() != "") { WriteSbToCSV(eventDescr, "IssueLog", sbLog); } Console.WriteLine("going to dispose now..."); } } }