Exemple #1
0
        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();
        }
Exemple #2
0
        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...");
                }
            }
        }