Beispiel #1
2
 public Export(string embedded)
 {
     string tmp = String.Empty;
     try
     {
         //получаем шаблон из прикладных ресурсов
         Stream template = GetResourceFileStream(embedded);
         //создаем временный файл
         tmp = System.IO.Path.GetTempFileName().Replace(".tmp", ".xlsx");
         //сохраняем шаблон во временный файл
         using (var fileStream = File.Create(tmp))
         {
             template.CopyTo(fileStream);
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
     // Создаем приложение и открываем в нём временный файл
     objApp = new Excel.Application();
     objBook = objApp.Workbooks.Open(tmp);
     objBook.Activate();
     objSheets = objBook.Worksheets;
     objSheet = (Excel._Worksheet)objSheets.get_Item(1);
 }
Beispiel #2
1
        public void CreateWorkbook()
        {
            var templateFile = Path.Combine(System.Windows.Forms.Application.StartupPath,
                @"Templates\TemplateWorkBook.xlsx");
            MainForm.LogWriter.WriteDebugMessage("Creating new workbook.");
            //mWorkbook = mApplication.Workbooks.Add(Missing.Value);
            mWorkbook = mApplication.Workbooks.Add(templateFile);

            mMeasurementDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[1];
            mMiscellaneousDataWorksheet = (Excel.Worksheet)mWorkbook.Sheets.Item[2];
            //var sheet3 = (Excel.Worksheet)mWorkbook.Sheets.Item[3];

            //Rename first and second worksheet, delete third worksheet
            //mMeasurementDataWorksheet.Name = "Measurement Data";
            //mMiscellaneousDataWorksheet.Name = "Miscellaneous Data";
            //sheet3.Delete();

            //Set measurement data worksheet as active worksheet
            mMeasurementDataWorksheet.Activate();

            //Display windows in splitview
            mWorkbook.Windows.Arrange(Excel.XlArrangeStyle.xlArrangeStyleHorizontal);

            MainForm.HeadersWritten = false;
            MainForm.LogWriter.WriteDebugMessage("Created new workbook.");
        }
Beispiel #3
0
        public void readExcel()
        {
            string valueString = string.Empty;
            objExcelApp = new Microsoft.Office.Interop.Excel.Application();
            objBooks = (Excel.Workbooks)objExcelApp.Workbooks;
            //Open the workbook containing the address data.
            objBook = objBooks.Open(@"C:\Temp\data\Test.xlsx", Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);
            //Get a reference to the first sheet of the workbook.
            objSheets = objBook.Worksheets;
            objSheet = (Excel._Worksheet)objSheets.get_Item(1);

            //Select the range of data containing the addresses and get the outer boundaries.
            rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
            long lLastRow = rngLast.Row;
            long lLastCol = rngLast.Column;

            // Iterate through the data and concatenate the values into a comma-delimited string.
            for (long rowCounter = 1; rowCounter <= lLastRow; rowCounter++)
            {
                for (long colCounter = 1; colCounter <= lLastCol; colCounter++)
                {
                    //Write the next value into the string.
                    Excel.Range cell = (Excel.Range)objSheet.Cells[rowCounter, colCounter];
                    string cellvalue = cell.Value.ToString();
                    //TODO: add your business logic for retrieve cell value
                }
            }
        }
        /**
         * if previous logs exist we open it and update it
         **/
        public bool OpenWorkBook()
        {
            if (!File.Exists(m_logName))
            {
                return false;
            }

            try
            {
                m_WorkBook = (Excel._Workbook)(m_XLApp.Workbooks.Open(m_logName));
                m_Sheet = (Excel._Worksheet)m_WorkBook.ActiveSheet;
            }
            catch (Exception e)
            {
                throw new LoggerException("Cannot open log file: " + e.Message);
            }

            if (m_Sheet == null)
            {
                return false;
            }

            m_columnsMap = new Dictionary<string, int>();

            string projectName;
            int lastRow = m_Sheet.UsedRange.Rows.Count;
            for (int column = 2; column < m_Sheet.UsedRange.Columns.Count + 1; column++)
            {
                projectName = GetStringFromCell(1, column);
                m_columnsMap.Add(projectName, column);
            }
            return true;
        }
Beispiel #5
0
 public Export()
 {
     // Instantiate Excel and start a new workbook.
     objApp = new Excel.Application();
     objBooks = objApp.Workbooks;
     objBook = objBooks.Add(Missing.Value);
     objSheets = objBook.Worksheets;
     objSheet = (Excel._Worksheet)objSheets.get_Item(1);
 }
 public ExcelManipulation()
 {
     _excelApp = new Excel.Application();
     _flagexcelapp = 1;
     _excelApp.Visible = true;
     //Создаём новую книгу
     _workBook = (Excel._Workbook)(_excelApp.Workbooks.Add(Missing.Value));
     _workSheet = (Excel._Worksheet)_workBook.ActiveSheet;
 }
Beispiel #7
0
        public void generatExcelNoSubmit(string f)
        {
            string file_Name = f;
            m_oExcelApp = new Excel.Application();
            m_oExcelApp.Visible = false;

            m_oExcelApp.UserControl = false;

            m_oSheet = null;
            excelRange = null;

            try
            {
                m_oBook = m_oExcelApp.Workbooks.Add(missing);
                Excel.Sheets sheets = m_oBook.Worksheets;

                //Add new 4 Sheet
                //sheets.Add(System.Type.Missing, m_oBook.Sheets[3], 1, Excel.XlSheetType.xlWorksheet);
                //Product Sheet [Sheet1]
                m_oSheet = (Excel._Worksheet)m_oBook.Sheets[1];
                m_oSheet.Activate();
                m_oSheet.Name = "รายการเสนอซื้อ";
                SetData_to_SheetNoSubmit();

                string template = Application.StartupPath;
                string strRunReport = file_Name;
                //string strPass = "******"; password

                m_oBook.SaveAs(strRunReport, Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null);

                m_oExcelApp.Visible = true;
            }
            catch (interop.COMException ex)
            {
                MessageBox.Show("Error accessing Excel: " + ex.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex.ToString());
            }
            finally
            {
                if (m_oExcelApp == null)
                {
                    m_oExcelApp.Quit();
                    m_oExcelApp = null;
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    GC.Collect();
                    GC.WaitForPendingFinalizers();

                }

            }
        }
        private void constructExcelObject(String sheetPath)
        {
            excelApp = new Excel.Application();
            excelWorkbook = excelApp.Workbooks.Open(@sheetPath);
            excelWorksheet = excelWorkbook.Sheets[1];
            Excel.Range excelRange = excelWorksheet.UsedRange;

            addressMap = new Dictionary<int, Utilities.ExcelRowBinder>();

            fillAddressList(excelWorkbook, excelWorksheet, excelRange);
        }
Beispiel #9
0
        public Logger(Table table)
        {
            _table = table;

            excel = new Excel.Application();
            excel.Workbooks.Add();

            _worksheet = (Excel.Worksheet)excel.ActiveSheet;

            _iteration = 1;
        }
Beispiel #10
0
 public ReportBuilder()
 {
     app = new Excel.Application();
     appBooks = app.Workbooks;
     currentBook = appBooks.Add(Missing.Value);
     sheets = currentBook.Worksheets;
     currentSheet = (Excel._Worksheet)sheets.get_Item(1);
     range = currentSheet.get_Range("A1", Missing.Value);
     charts = currentSheet.ChartObjects(Type.Missing);
     chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
 }
Beispiel #11
0
        public ExcelWorksheet()
        {
            excelApp.Visible = true;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            excelWorkBook = excelApp.ActiveWorkbook;
            if (excelWorkBook == null)
                excelWorkBook = (Excel._Workbook) excelApp.Workbooks.Add(Missing.Value);

            excelSheet = (Excel._Worksheet) excelWorkBook.ActiveSheet;
            excelSheet.Name = "SpaceClaim";
        }
Beispiel #12
0
 /// <summary>
 /// Метод создающий новый файл
 /// </summary>
 /// <param name="filename">Имя и путь файла</param>
 /// <param name="background">Создать в бэкграунде</param>
 /// <param name="overwrite">Флаг перезаписи файле, если таковой уже существует</param>
 public static void CreateNewFile(string filename, bool background, bool overwrite)
 {
     if (!background)
         excelApp.Visible = true;
     else
         excelApp.Visible = false;
     excelApp.Workbooks.Add();
     workSheet = excelApp.ActiveSheet;
     if (File.Exists(filename))
         if (overwrite)
             File.Delete(filename);
     workSheet.SaveAs(filename);
 }
Beispiel #13
0
        public override string Read()
        {
            try
            {
                excelApp = new Excel.Application();
                excelApp.Visible = false;

                object missingValue = System.Reflection.Missing.Value;
                object readOnly = true;
                string filePath = (string)FilePath;

                workBook = excelApp.Workbooks.Open(filePath,
                                                   missingValue,
                                                   readOnly,
                                                   missingValue,
                                                   missingValue,
                                                   missingValue,
                                                   missingValue,
                                                   missingValue,
                                                   missingValue,
                                                   missingValue,
                                                   missingValue,
                                                   missingValue,
                                                   missingValue,
                                                   missingValue,
                                                   missingValue);

                objectSheet = (Excel.Worksheet)excelApp.ActiveSheet;

                // Get full range which used.
                Excel.Range usedRange = objectSheet.UsedRange;
                string result = string.Empty;

                for (int i = 1; i <= usedRange.Rows.Count; i++)
                {
                    for (int j = 1; j <= usedRange.Columns.Count; j++)
                    {
                        result += usedRange.Cells[i, j].Text;
                    }
                }

                excelApp.Quit();

                return result;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
Beispiel #14
0
        public static void superswitch(int colSour, int colID, string[] cellsCol, string openingVar, string closingVar, bool Numericalcheck, bool VariableCheck)
        {
            for (int owbb = 0; owbb < WorkBooks.Count; owbb++)
            {
                oWB = (Excel._Workbook)WorkBooks[owbb];
                for (int owss = 1; owss <= oWB.Worksheets.Count; owss++)
                {
                    oWS = (Excel._Worksheet)oWB.Worksheets.get_Item(owss);
                    foreach (Excel.Range fila in oWS.UsedRange.Rows)
                    {
                        actualizarSID((string)(oWS.Cells[fila.Row, colSour] as Excel.Range).Value, (string)(oWS.Cells[fila.Row, colID] as Excel.Range).Value, openingVar, closingVar);
                        if (cellValueS != null)
                        {
                            variablesInString = getVariablesinString(cellValueS);
                            if (Numericalcheck == true)
                            {
                                numerosenSource = getNumbers(cellValueS, variablesInString);
                            }
                            switch (cellsCol.Length)
                            {
                            case 1:
                                if (cellsCol[0] != "")
                                {
                                    if (VariableCheck)
                                    {
                                        issuefounded = VarCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb);
                                    }
                                    if ((issuefounded == false) && (Numericalcheck == true))
                                    {
                                        NumCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb);
                                    }
                                }
                                else
                                {
                                    System.Windows.Forms.MessageBox.Show("Please make sure that you fill \nin all the mandatory fields properly.");
                                }
                                break;

                            case 2:
                                int[] columnass = { -1, -1 };
                                if ((cellsCol[0] != null) && (cellsCol[0] != ""))
                                {
                                    columnass[0] = int.Parse(cellsCol[0]);
                                }
                                if ((cellsCol[1] != null) && (cellsCol[1] != ""))
                                {
                                    columnass[1] = int.Parse(cellsCol[1]);
                                }

                                if ((columnass[0] != -1) && (columnass[1] != -1))
                                {
                                    if (columnass[0] > columnass[1])
                                    {
                                        int exc = columnass[0];
                                        columnass[0] = columnass[1];
                                        columnass[1] = exc;
                                    }
                                    for (int b = columnass[0]; b <= columnass[1]; b++)
                                    {
                                        if (VariableCheck)
                                        {
                                            issuefounded = VarCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb);
                                        }
                                        if ((issuefounded == false) && (Numericalcheck == true))
                                        {
                                            NumCompare((string)(oWS.Cells[fila.Row, b] as Excel.Range).Value, owbb);
                                        }
                                    }
                                }
                                else
                                {
                                    if ((columnass[0] == -1) && (columnass[1] == -1))
                                    {
                                        System.Windows.Forms.MessageBox.Show("Please make sure that you fill \nin all the mandatory fields properly.");
                                    }
                                    else
                                    {
                                        if (columnass[0] == -1)
                                        {
                                            columnass[0] = columnass[1];
                                        }
                                        if (VariableCheck)
                                        {
                                            issuefounded = VarCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb);
                                        }
                                        if ((issuefounded == false) && (Numericalcheck == true))
                                        {
                                            NumCompare((string)(oWS.Cells[fila.Row, columnass[0]] as Excel.Range).Value, owbb);
                                        }
                                    }
                                }
                                break;

                            default:
                                foreach (string cols in cellsCol)
                                {
                                    if ((cols != null) && (cols != ""))
                                    {
                                        if (VariableCheck)
                                        {
                                            issuefounded = VarCompare((string)(oWS.Cells[fila.Row, int.Parse(cellsCol[0])] as Excel.Range).Value, owbb);
                                        }
                                        if ((issuefounded == false) && (Numericalcheck == true))
                                        {
                                            NumCompare((string)(oWS.Cells[fila.Row, int.Parse(cols)] as Excel.Range).Value, owbb);
                                        }
                                    }
                                }
                                break;
                            }
                        }
                    }
                }
            }
        }
Beispiel #15
0
        private static string SaveDataToSingleExcel(List<DataTable> dataTables, List<ExcelInfo> excelInfos, ExcelInfo dataTableInfo = null)
        {
            try
            {
                OpenExcel(excelInfos[0], false);
                for (int n = 0; n < dataTables.Count; n++)
                {
                    _objSheet = OpenSheet(excelInfos[n]);
                    if (_objSheet == null)
                    {
                        return "没有指定页签";
                    }
                    SaveDataToExcelImpl(dataTables[n], excelInfos[n], dataTableInfo);
                }
                SaveAs(excelInfos[0].FilePath);
            }
            catch (Exception err)
            {
                CloseExcel();
                Console.WriteLine(err.Message);
                return err.Message;
            }
            return "";

        }
 public CellGetter(Excel._Worksheet worksheet)
 {
     this.worksheet = worksheet;
 }
Beispiel #17
0
        private void Form1_Load(object sender, EventArgs e)
        {
            Excel.Application xlApp       = new Excel.Application();
            Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(@"C:\Users\user\Downloads\monte-carlo\monte-carlo\monte-carlo\monte-carlo\bin\Debug\data1.xlsx");
            Excel._Worksheet  xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range       xlRange     = xlWorksheet.UsedRange;
            int k = 0;

            for (int i = 2; ; i++)
            {
                for (int j = 1; j <= 2; j++)
                {
                    //write the value to the console
                    if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
                    {
                        if (j % 2 == 1)
                        {
                            clist pnn = new clist();
                            pnn.val        = Convert.ToDouble(xlRange.Cells[i, j].Value2);
                            tot           += pnn.val;
                            pnn.cumulative = tot;

                            demand.Add(pnn);
                            dataGridView1.Rows.Add();
                            dataGridView1.Rows[k].Cells[0].Value = pnn.val;
                        }
                        else
                        {
                            clist pnn = new clist();
                            pnn.val        = Convert.ToDouble(xlRange.Cells[i, j].Value2);
                            tot2          += pnn.val;
                            pnn.cumulative = tot2;
                            freqency.Add(pnn);
                            dataGridView1.Rows.Add();
                            dataGridView1.Rows[k].Cells[1].Value = pnn.val;
                            dataGridView1.Rows[k].Cells[3].Value = pnn.cumulative;
                        }
                    }
                    else
                    {
                        stop = true;
                    }
                    //add useful things here!
                }
                k++;
                if (stop)
                {
                    break;
                }
            }
            tot = 0;
            for (int i = 0; i < freqency.Count; i++)
            {
                clist pnn = new clist();
                pnn.val = freqency[i].val / freqency[freqency.Count - 1].cumulative;
                tot    += pnn.val;
                pnn.relativecumulative = tot;
                relativefreq.Add(pnn);
                dataGridView1.Rows[i].Cells[2].Value = pnn.val;
                dataGridView1.Rows[i].Cells[4].Value = pnn.relativecumulative;
            }
            for (int i = 0; i < freqency.Count; i++)
            {
                if (i == 0)
                {
                    freqency[i].range    = 0;
                    freqency[i].rangeend = freqency[i].cumulative;
                    dataGridView1.Rows[i].Cells[5].Value = freqency[i].range + "-" + freqency[i].rangeend;
                }
                else
                {
                    freqency[i].range    = (freqency[i - 1].cumulative + 1);
                    freqency[i].rangeend = freqency[i].cumulative;
                    dataGridView1.Rows[i].Cells[5].Value = freqency[i].range + "-" + freqency[i].rangeend;
                }
            }
            xlWorkbook.Close(true);
            xlApp.Quit();
        }
Beispiel #18
0
        public string getStatusExcel(string fPath)
        {
            string strval = validation(fPath);

            try
            {
                if (strval == "true")
                {
                    addlog("Getting PR status....");
                    Excel.Application xlApp       = new Excel.Application();
                    Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(@fPath);
                    Excel._Worksheet  xlWorksheet = xlWorkbook.Sheets[1];
                    Excel.Range       xlRange     = xlWorksheet.UsedRange;

                    VssConnection connection2 = new VssConnection(new Uri($"https://dynamicscrm.visualstudio.com"), new VssAadCredential());
                    var           statsAPI    = new GetStatusAPI(connection2);

                    int rowCount = xlRange.Rows.Count;
                    int colCount = xlRange.Columns.Count;
                    int cCnt     = 1;
                    int csCnt    = 2;
                    if (rowCount > 1)
                    {
                        for (int rCnt = 1; rCnt <= rowCount; rCnt++)
                        {
                            if (rCnt == 1)
                            {
                                addlog("PR Number#               PR Status");
                            }
                            else
                            {
                                if (xlRange.Cells[rCnt, cCnt] != null && xlRange.Cells[rCnt, cCnt].Value2 != null)
                                {
                                    string PrLink = xlRange.Cells[rCnt, cCnt].Value2.ToString();
                                    strStatus = getStatus(PrLink.ToString(), statsAPI).Result.ToString();
                                    xlWorksheet.Cells[rCnt, csCnt] = strStatus;
                                    addlog(PRNum + "               " + strStatus);
                                    xlWorkbook.Save();
                                }
                                else
                                {
                                    strStatus = "Invalid PRLink";
                                    xlWorksheet.Cells[rCnt, csCnt] = strStatus;
                                    xlWorkbook.Save();
                                    addlog(strStatus);
                                }
                            }
                        }
                        xlWorkbook.Close();
                        strStatus = "The file in the same file path has been updated successfully";
                    }
                    else
                    {
                        xlWorkbook.Close();
                        strStatus = "No Data found in the given file";
                    }
                }
                else
                {
                    addlog(strval);
                }
            }
            catch (Exception ex)
            {
                strStatus = ex.Message;
                clsStatus.WriteLog("getStatusExcel()-->" + ex.Message);
            }
            return(strStatus);
        }
Beispiel #19
0
        private void bwrkLoadVFDDefs_DoWork(object sender, DoWorkEventArgs e)
        {
            xlApp      = new XL.Application();
            xlWorkbook = xlApp.Workbooks.Open("C:\\Users\\sferry\\source\\repos\\V1000_Drive_Programmer\\V1000_Drive_Programmer\\data\\Parameter Defaults.dat");
            //xlWorkbook = xlApp.Workbooks.Open("C:\\Users\\steve\\source\\repos\\V1000_Drive_Programmer\\V1000_Drive_Programmer\\data\\Parameter Defaults.dat");
            xlWorksheet = xlWorkbook.Sheets[1];
            xlRange     = xlWorksheet.UsedRange;

            V1000_xlRead_List.Clear();

            ProgressArgs.VFDLoad_Total_Units = xlRange.Rows.Count - 1;

            for (int i = 2; i <= xlRange.Rows.Count; i++)
            {
                if (bwrkLoadVFDDefs.CancellationPending)
                {
                    e.Cancel = true;
                    bwrkLoadVFDDefs.ReportProgress(0);
                    return;
                }

                V1000_Param_Data ParamData = new V1000_Param_Data();

                if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null)
                {
                    ParamData.RegAddress = Convert.ToUInt16(xlRange.Cells[i, 1].Value2);
                }
                else
                {
                    ParamData.RegAddress = 0;
                }

                if (xlRange.Cells[i, 2] != null && xlRange.Cells[i, 2].Value2 != null)
                {
                    ParamData.ParamNum = xlRange.Cells[i, 2].Value2.ToString();
                }
                else
                {
                    ParamData.ParamNum = "0";
                }

                if (xlRange.Cells[i, 3] != null && xlRange.Cells[i, 3].Value2 != null)
                {
                    ParamData.ParamName = xlRange.Cells[i, 3].Value2.ToString();
                }
                else
                {
                    ParamData.ParamName = "0";
                }

                if (xlRange.Cells[i, 4] != null && xlRange.Cells[i, 4].Value2 != null)
                {
                    ParamData.DefVal = Convert.ToUInt16(xlRange.Cells[i, 4].Value2);
                }
                else
                {
                    ParamData.DefVal = 0;
                }

                if (xlRange.Cells[i, 5] != null && xlRange.Cells[i, 5].Value2 != null)
                {
                    ParamData.Multiplier = Convert.ToUInt16(xlRange.Cells[i, 5].Value2);
                }
                else
                {
                    ParamData.Multiplier = 1;
                }

                if (xlRange.Cells[i, 6] != null && xlRange.Cells[i, 6].Value2 != null)
                {
                    ParamData.NumBase = Convert.ToByte(xlRange.Cells[i, 6].Value2);
                }
                else
                {
                    ParamData.NumBase = 10;
                }

                if (xlRange.Cells[i, 7] != null && xlRange.Cells[i, 7].Value2 != null)
                {
                    ParamData.Units = xlRange.Cells[i, 7].Value2.ToString();
                }
                else
                {
                    ParamData.Units = "";
                }

                // Create a string version for display purposes of the actual default paramater value
                if (ParamData.NumBase == 16)
                {
                    ParamData.DefValDisp = "0x" + ParamData.DefVal.ToString("X4");
                }
                else
                {
                    ParamData.DefValDisp = ((float)ParamData.DefVal / ParamData.Multiplier).ToString() + " " + ParamData.Units;
                }

                VFD_Vals.Add(ParamData);

                ProgressArgs.VFDLoad_Unit     = i - 2;
                ProgressArgs.VFDLoad_Progress = (byte)(((float)ProgressArgs.VFDLoad_Unit / ProgressArgs.VFDLoad_Total_Units) * 100);
                bwrkLoadVFDDefs.ReportProgress((int)(((float)i / (xlRange.Rows.Count - 1)) * 100));
            }

            ProgressArgs.VFDLoad_Unit = ProgressArgs.VFDLoad_Total_Units - 1;
            e.Result = 0x02;
            bwrkLoadVFDDefs.ReportProgress(100);
        }
Beispiel #20
0
        public IEnumerable <Transaction> ParseFile(IFileService fileService)
        {
            int year   = int.MinValue;
            int period = int.MinValue;

            using (var frmGetData = new frmRequestData())
            {
                if (frmGetData.ShowDialog() == DialogResult.Cancel)
                {
                    return(null);
                }

                year   = frmGetData.Year;
                period = frmGetData.Period;
            }

            var transactions = new List <Transaction>();

            var excelApp = new Excel.Application();

            excelApp.Visible = false;

            excelApp.Workbooks.Open(fileService.FileName);

            Excel._Worksheet worksheet = excelApp.ActiveSheet;

            var account  = worksheet.Cells[1, "A"].Value2;
            var ibalance = worksheet.Cells[1, "C"].Value2;

            int currRow = 3;

            do
            {
                var     tdateTemp       = worksheet.Cells[currRow, "C"].Value2;
                var     transactionDate = DateTime.FromOADate(tdateTemp);
                var     GLCode          = worksheet.Cells[currRow, "B"].Value2;
                decimal Debit           = 0.0m;
                decimal Credit          = 0.0m;
                var     vendorName      = string.Empty;
                var     checkNumber     = string.Empty;
                var     sequence        = worksheet.Cells[currRow, "F"].Value2;

                if (GLCode == "AP-PY")
                {
                    decimal.TryParse(worksheet.Cells[currRow, "G"].Value2.ToString(), out Debit);
                    decimal.TryParse(worksheet.Cells[currRow, "H"].Value2.ToString(), out Credit);

                    if (decimal.Equals(Debit, 0.0m))
                    {
                        vendorName = worksheet.Cells[currRow, "D"].Value2;
                        currRow   += 1;
                        string temp = worksheet.Cells[currRow, "A"].Value2.ToString();
                        checkNumber = temp.Substring(0, temp.LastIndexOf("-")).TrimStart('0');
                    }
                    else
                    {
                        string temp = worksheet.Cells[currRow, "D"].Value2.ToString();
                        checkNumber = temp.Substring(0, temp.LastIndexOf("-")).TrimStart('0');
                        currRow    += 1;
                        vendorName  = worksheet.Cells[currRow, "A"].Value2;
                    }
                }

                if (GLCode == "GL-JE" || GLCode == "AP-IN")
                {
                    decimal.TryParse(worksheet.Cells[currRow, "G"].Value2.ToString(), out Debit);
                    decimal.TryParse(worksheet.Cells[currRow, "H"].Value2.ToString(), out Credit);

                    vendorName = worksheet.Cells[currRow, "D"].Value2;
                    if (GLCode == "AP-IN")
                    {
                        var tempVendorName = worksheet.Cells[currRow + 1, "A"].Value2;
                        vendorName = $"{(tempVendorName == null ? string.Empty : $"{tempVendorName}:")}{vendorName}";
                    }

                    if (worksheet.Cells[currRow + 1, "C"].Value2 == null)
                    {
                        currRow += 1;
                    }
                }

                transactions.Add(new Transaction()
                {
                    TransactionDate = transactionDate.ToString("M/d/yyyy"),
                    Period          = period.ToString(),
                    Year            = year.ToString(),
                    Acconut         = account.ToString(),
                    IBalance        = ibalance.ToString(),
                    GLCode          = GLCode.ToString(),
                    VendorName      = vendorName == null ? "" : vendorName.ToString(),
                    CheckNumber     = checkNumber,
                    Credit          = Credit.ToString(),
                    DCredit         = Credit,
                    Debit           = Debit.ToString(),
                    DDebit          = Debit,
                    Sequence        = sequence
                });

                currRow += 1;
            } while (!worksheet.Cells[currRow, "A"].Value2.ToString().StartsWith("Net Change"));
Beispiel #21
0
        public static List <Question> ReadFile(string excelFile)
        {
            int headersRow = 2;

            List <Question> qList = new List <Question>();

            // open excel file
            Excel.Application excel     = new Excel.Application();
            Excel.Workbook    workbook  = excel.Workbooks.Open(excelFile);
            Excel._Worksheet  worksheet = workbook.Sheets[1];

            int rowCount = worksheet.UsedRange.Rows.Count;
            int colCount = worksheet.UsedRange.Columns.Count;

            // find data indexes
            //main.ShowOutput("Finding data indexes....", false);
            int questionTextIndex = 0;
            int nameIndex         = 0;
            int categoryIndex     = 0;
            int answerIndex       = 0;
            int imageIndex        = 0;

            int[] optionIndexes = new int[] { 0, 0, 0, 0 };

            for (int col = 1; col <= colCount; col++)
            {
                string v = worksheet.Cells[headersRow, col].Value.ToString();

                if (v == "Question Stem")
                {
                    questionTextIndex = col;
                }
                if (v == "Syllabus")
                {
                    categoryIndex = col;
                }
                if (v == "Correct Answer")
                {
                    answerIndex = col;
                }
                if (v == "Question ID")
                {
                    nameIndex = col;
                }
                if (v == "Annex")
                {
                    imageIndex = col;
                }

                string[] answerFields = new string[] { "A", "B", "C", "D" };

                for (int i = 0; i < 4; i++)
                {
                    if (v == answerFields[i])
                    {
                        optionIndexes[i] = col;
                    }
                }
            }

            // check data indexes
            if (questionTextIndex > 0 &&
                optionIndexes[0] > 0 &&
                optionIndexes[1] > 0 &&
                optionIndexes[2] > 0 &&
                optionIndexes[3] > 0 &&
                answerIndex > 0 &&
                imageIndex > 0 &&
                categoryIndex > 0 &&
                nameIndex > 0)
            {
                //main.ShowOutput("OK");
            }
            else
            {
                Main.myThis.ShowOutput("INDEX ERROR!!");
                if (questionTextIndex == 0)
                {
                    Main.myThis.ShowOutput("questionTextIndex not found. Should be\"Question Stem\"");
                }
                if (optionIndexes[0] == 0)
                {
                    Main.myThis.ShowOutput("optionIndexes(A) not found. Should be\"A\"");
                }
                if (optionIndexes[0] == 0)
                {
                    Main.myThis.ShowOutput("optionIndexes(B) not found. Should be\"B\"");
                }
                if (optionIndexes[0] == 0)
                {
                    Main.myThis.ShowOutput("optionIndexes(C) not found. Should be\"C\"");
                }
                if (optionIndexes[0] == 0)
                {
                    Main.myThis.ShowOutput("optionIndexes(D) not found. Should be\"D\"");
                }
                if (answerIndex == 0)
                {
                    Main.myThis.ShowOutput("answerIndex not found. Should be\"Correct Answer\"");
                }
                if (imageIndex == 0)
                {
                    Main.myThis.ShowOutput("imageIndex not found. Should be\"Annex\"");
                }
                if (categoryIndex == 0)
                {
                    Main.myThis.ShowOutput("categoryIndex not found. Should be\"Syllabus\"");
                }
                if (nameIndex == 0)
                {
                    Main.myThis.ShowOutput("nameIndex not found. Should be\"Question ID\"");
                }

                workbook.Close();
                excel.Quit();
                return(qList);
            }

            // get question data
            for (int row = (headersRow + 1); row <= rowCount; row++)
            {
                Question q = new Question();
                q.name     = JwString.Clean(worksheet.Cells[row, nameIndex].Value.ToString());
                q.category = CleanCategory(JwString.Clean(worksheet.Cells[row, categoryIndex].Value.ToString()));
                q.text     = JwString.Clean(worksheet.Cells[row, questionTextIndex].Value.ToString());

                if (worksheet.Cells[row, imageIndex].Value != null)
                {
                    string imageName = CleanImage(JwString.Clean(worksheet.Cells[row, imageIndex].Value.ToString()));
                    q.AddImage(imageName, "");
                }

                q.type = "multichoice";

                string   answer       = worksheet.Cells[row, answerIndex].Value.Trim();
                string[] answerValues = new string[] { "A", "B", "C", "D" };

                for (int i = 1; i <= 4; i++)
                {
                    Option o = new Option();
                    o.text     = JwString.Clean(worksheet.Cells[row, optionIndexes[i - 1]].Value.ToString());
                    o.feedback = "";
                    o.grade    = 0;
                    if (answer == answerValues[i - 1])
                    {
                        o.grade = 100;
                    }
                    q.options.Add(o);
                }

                qList.Add(q);
            }

            // close excel and return questions
            workbook.Close();
            excel.Quit();

            return(qList);
        }
Beispiel #22
0
        public DataTable getExcelFile(string fileLocation)
        {
            DataTable  dt   = new DataTable();
            DataColumn clID = new DataColumn("serial", typeof(string));

            dt.Columns.Add(clID);

            //Create COM Objects. Create a COM object for everything that is referenced
            Excel.Application xlApp       = new Excel.Application();
            Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(fileLocation);
            Excel._Worksheet  xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range       xlRange     = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;



            //iterate over the rows and columns and print to the console as it appears in the file
            //excel is not zero based!!
            for (int i = 1; i <= rowCount; i++)
            {
                int           progree = i * 100 / rowCount;
                List <string> lt      = new List <string>();
                for (int j = 1; j <= colCount; j++)
                {
                    //new line
                    if (j == 1)
                    {
                        if (i != 1)
                        {
                            lt.Add((i - 1).ToString());
                        }
                        continue;
                    }
                    var data = xlRange.Cells[i, j].Value2 == null? "": xlRange.Cells[i, j].Value2.ToString();
                    if (i == 1)
                    {
                        string     whiteSapceRemove = Regex.Replace(data, @"[^0-9a-zA-Z]+", "");
                        DataColumn cl = new DataColumn(whiteSapceRemove, typeof(string));
                        dt.Columns.Add(cl);
                    }
                    else
                    {
                        lt.Add(data);
                    }
                }
                if (lt.Count > 0)
                {
                    dt.Rows.Add(lt.ToArray());
                    //dt.Rows.Add("1", "2", "3", "4");
                }

                toolStripProgressBar.Value = progree;
            }

            //cleanup
            GC.Collect();
            GC.WaitForPendingFinalizers();

            //rule of thumb for releasing com objects:
            //  never use two dots, all COM objects must be referenced and released individually
            //  ex: [somthing].[something].[something] is bad

            //release com objects to fully kill excel process from running in the background
            Marshal.ReleaseComObject(xlRange);
            Marshal.ReleaseComObject(xlWorksheet);

            //close and release
            xlWorkbook.Close();
            Marshal.ReleaseComObject(xlWorkbook);

            //quit and release
            xlApp.Quit();
            Marshal.ReleaseComObject(xlApp);

            return(dt);
        }
        public void ReadData()
        {
            // Setting the excel application to call it
            Excel.Application xlApp = new Excel.Application();

            // Which excel the code shell read
            Excel.Workbook xlWb = xlApp.Workbooks.Open(@"C:\Users\kiels00j\Desktop\test.xlsx");

            // Which sheets the code shall read from (in excel 1 is the first one and not 0 as in coding)
            Excel._Worksheet xlWs = xlWb.Sheets[1];

            // Getting all the data contain in the predefine setting above
            Excel.Range xlR = xlWs.UsedRange;

            // Getting how many Rows and Cloumns the excel file contain to the for loop
            int rowCount = xlR.Rows.Count;
            int colCount = xlR.Columns.Count;

            // Making a list to contain all the data from the excel file
            List <Data_Used_list> dul = new List <Data_Used_list>();

            // Going / loop through every row there is, if there is headers or the data is
            // not starting on line 1 set it to the line nr where it is
            for (int i = 1; i <= rowCount; i++)
            {
                // Use this if just want to read every single colums and combine them in string row
                //for (int j = 1; j <= colCount; j++)
                //{
                //    Console.Write(xlR.Cells[i, j].Value2.ToString() + "\t");
                //}
                //Console.WriteLine();


                // Here I add data to the list and know which space the headers are in
                // notice that it start with 1 and not 0, this is because in excel 1 is the first and not
                // 0 as in coding
                dul.Add(new Data_Used_list
                {
                    Phone_nr = Convert.ToString(xlR.Cells[i, 1].Value2),
                    Abo      = Convert.ToString(xlR.Cells[i, 2].Value2),
                    GID      = Convert.ToString(xlR.Cells[i, 3].Value2),
                    DataUsed = Convert.ToString(xlR.Cells[i, 4].Value2)
                });
            }

            // This is only to confirm and write out the list that
            foreach (Data_Used_list item in dul)
            {
                Console.WriteLine("Nr: " + item.Phone_nr + " - GID: " + item.GID + " - Data: " + item.DataUsed);
            }

            // Closing down the use off the excel file so the code/software do not lock it
            GC.Collect();
            GC.WaitForPendingFinalizers();
            Marshal.ReleaseComObject(xlR);
            Marshal.ReleaseComObject(xlWs);
            xlWb.Close();
            Marshal.ReleaseComObject(xlWb);
            xlApp.Quit();
            Marshal.ReleaseComObject(xlApp);
        }
Beispiel #24
0
        private void ImportData()
        {
            string ERPInDirectory = Convert.ToString(ConfigurationManager.AppSettings["ERPIN"].ToString().Trim());

            if (!Directory.Exists(ERPInDirectory))
            {
                log.Info("ERPIN路径不存在。");
                return;
            }
            string          ExcelPath  = ERPInDirectory;
            List <FileInfo> ExcelFiles = getFiles(ExcelPath, ".xls");

            if (ExcelFiles.Count == 0)
            {
                log.Info("没有检测到Excel文件。");
                return;
            }
            foreach (FileInfo fileInfo in ExcelFiles)
            {
                try
                {
                    string FilePath = fileInfo.FullName;
                    log.Info("正在处理:" + FilePath);
                    MSExcel.Application excelApp = new MSExcel.Application
                    {
                        Visible = false//是打开可见
                    };
                    MSExcel.Workbooks  _workbooks = excelApp.Workbooks;
                    MSExcel._Workbook  _workbook  = _workbooks.Add(FilePath);
                    MSExcel._Worksheet whs        = _workbook.Sheets[1];//获取第2张工作表 TODO:生产环境改回1

                    whs.Activate();

                    JArray        ModelArray   = new JArray();
                    JObject       ModelJson    = new JObject();
                    JArray        FOrderEntrys = new JArray();
                    List <string> ordenoList   = new List <string>();

                    int  excelIndex       = 2;
                    int  BillNoLineNumber = 0;     //表头单据编号所在行数
                    bool IsAllSuccess     = true;  //是否整个文件导入成功
                    bool HasSuccess       = false; //是否整个文件有导入成功的订单
                    bool HasError         = false; //是否整个文件有导入失败的订单

                    while (true)
                    {
                        try
                        {
                            MSExcel.Range rang = (MSExcel.Range)whs.Cells[excelIndex, 32];//ERP收料通知单单号
                            if (rang.Value != null)
                            {
                                string ERPBillNo      = Convert.ToString(rang.Value);
                                string PrevFBillNo    = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex - 1, 32]).Value); //上一行的ERP收料通知单单号
                                string NextFBillNo    = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex + 1, 32]).Value); //下一行的ERP收料通知单单号
                                string QISBillNo      = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 1]).Value);      //QIS检验单号
                                string FDate          = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 2]).Value);
                                string MaterialNumber = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 7]).Value);
                                string ComputerResult = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 15]).Value); //电脑判定
                                double CheckQty       = Convert.ToDouble(((MSExcel.Range)whs.Cells[excelIndex, 14]).Value); //批量数(报检数量)
                                double RealQty        = Convert.ToDouble(((MSExcel.Range)whs.Cells[excelIndex, 22]).Value); //实收数量(合格数量)
                                string CheckResult    = Convert.ToString(((MSExcel.Range)whs.Cells[excelIndex, 16]).Value); //检验结果
                                double unqualifiedQty = CheckQty - RealQty;                                                 //不合格数量

                                //ERP收料通知单单号和上一行不同,则是另一张单据
                                if (!PrevFBillNo.Equals(ERPBillNo))
                                {
                                    BillNoLineNumber = excelIndex;
                                    log.Info("正在读取:" + ERPBillNo);
                                }

                                string NeedPushEntryIds = "";
                                //excel的一行是相同物料合并数量的,在ERP查源单,查出来可能有多行物料
                                List <List <object> > Bills = PUR_ReceiveBill.GetAllBill(client, "FBillNo='" + ERPBillNo + "'" + " and FMaterialID.FNumber='" + MaterialNumber + "'");
                                for (int i = 0; i < Bills.Count; i++)
                                {
                                    double FQty           = 0;
                                    string SrcBillID      = Convert.ToString(Bills[i][0]);
                                    string SrcBillNo      = Convert.ToString(Bills[i][1]);
                                    string SrcEntryID     = Convert.ToString(Bills[i][8]);
                                    double FActReceiveQty = Convert.ToDouble(Bills[i][7]);  //交货数量
                                    double FCheckJoinQty  = Convert.ToDouble(Bills[i][9]);  //检验关联数量
                                    double NoCheckQty     = FActReceiveQty - FCheckJoinQty; //剩余未检验数量
                                    string SrcBillTypeID  = Convert.ToString(Bills[i][10]);
                                    string SrcSeq         = Convert.ToString(Bills[i][11]);
                                    if (NoCheckQty > CheckQty)
                                    {
                                        FQty = CheckQty;
                                    }
                                    else
                                    {
                                        FQty = NoCheckQty;
                                    }
                                    CheckQty = CheckQty - FQty;
                                    if (FQty <= 0)
                                    {
                                        continue;
                                    }
                                    NeedPushEntryIds = NeedPushEntryIds + SrcEntryID + ",";
                                    //一行下推成一单
                                    string NewBillId = PUR_ReceiveBill.PushToInspectBill(client, SrcEntryID);
                                    if (!"".Equals(NewBillId))
                                    {
                                        //修改数量
                                        List <List <object> > InspectBills = QM_InspectBill.GetAllBill(client, "FID=" + NewBillId);
                                        if (InspectBills.Count > 0)
                                        {
                                            string NewBillNo = Convert.ToString(InspectBills[0][1]);
                                            ordenoList.Add(NewBillNo);

                                            JArray  MultiLanguageTextArr  = new JArray();
                                            JObject MultiLanguageTextJson = new JObject();
                                            MultiLanguageTextJson.Add("Key", 2052);
                                            MultiLanguageTextJson.Add("Value", "QIS导入,单号" + QISBillNo);

                                            JObject Entry = new JObject();
                                            ModelJson.Add("FID", Convert.ToString(InspectBills[0][0]));
                                            ModelJson.Add("FEntity", FOrderEntrys);
                                            ModelJson.Add("FDescription", "QIS导入,单号" + QISBillNo);

                                            FOrderEntrys.Add(Entry);
                                            Entry.Add("FEntryID", Convert.ToString(InspectBills[0][2]));
                                            Entry.Add("FInspectQty", FQty);
                                            Entry.Add("FMemo", "QIS导入,单号" + QISBillNo);
                                            if (!"合格".Equals(ComputerResult))
                                            {
                                                Entry.Add("FInspectResult", "2");           //检验结果=不合格
                                                JArray  FPolicyDetailEntrys = new JArray(); //使用决策 子单据体
                                                JObject FPolicyDetailEntry1 = new JObject();
                                                JObject FPolicyDetailEntry2 = new JObject();
                                                switch (CheckResult)
                                                {
                                                case "退货":
                                                    break;

                                                case "让步接收":
                                                    Entry.Add("FPolicyDetail", FPolicyDetailEntrys);
                                                    FPolicyDetailEntrys.Add(FPolicyDetailEntry1);
                                                    FPolicyDetailEntry1.Add("FUsePolicy", "A");
                                                    FPolicyDetailEntry1.Add("FPolicyQty", RealQty);

                                                    FPolicyDetailEntrys.Add(FPolicyDetailEntry2);
                                                    FPolicyDetailEntry2.Add("FUsePolicy", "B");
                                                    FPolicyDetailEntry2.Add("FPolicyQty", unqualifiedQty);

                                                    break;

                                                case "挑选":
                                                    Entry.Add("FPolicyDetail", FPolicyDetailEntrys);
                                                    FPolicyDetailEntrys.Add(FPolicyDetailEntry1);
                                                    FPolicyDetailEntry1.Add("FUsePolicy", "A");
                                                    FPolicyDetailEntry1.Add("FPolicyQty", RealQty);

                                                    FPolicyDetailEntrys.Add(FPolicyDetailEntry2);
                                                    FPolicyDetailEntry2.Add("FUsePolicy", "E");
                                                    FPolicyDetailEntry2.Add("FPolicyQty", unqualifiedQty);
                                                    break;
                                                }
                                            }
                                            else if ("合格".Equals(ComputerResult) && "内部原因".Equals(CheckResult))
                                            {
                                                JArray  FPolicyDetailEntrys = new JArray();//使用决策 子单据体
                                                JObject FPolicyDetailEntry1 = new JObject();
                                                Entry.Add("FPolicyDetail", FPolicyDetailEntrys);
                                                FPolicyDetailEntrys.Add(FPolicyDetailEntry1);
                                                FPolicyDetailEntry1.Add("FDetailID", Convert.ToString(InspectBills[0][3]));
                                                FPolicyDetailEntry1.Add("FMemo1", "内部原因");
                                            }



                                            Dictionary <string, object> ImportResult = QM_InspectBill.SaveBill(client, new StringBuilder(ModelJson.ToString()), new List <string>(ordenoList));
                                            //提交审核检验单
                                            CommonOperate commonOperate = new CommonOperate();
                                            commonOperate.SubmitBill(client, "QM_InspectBill", new StringBuilder("\"" + NewBillNo + "\""));
                                            commonOperate.AuditBill(client, "QM_InspectBill", new StringBuilder("\"" + NewBillNo + "\""));
                                            ModelJson    = new JObject();
                                            FOrderEntrys = new JArray();
                                            if (!Convert.ToBoolean(ImportResult["IsSuccess"]))
                                            {
                                                HasError     = true;
                                                IsAllSuccess = false;
                                            }
                                            else
                                            {
                                                HasSuccess = true;
                                            }
                                        }
                                        else
                                        {
                                            log.Info("下推的单据已被删除。");
                                        }
                                    }
                                }
                                //移除掉最后一个","
                                if (NeedPushEntryIds.Length > 0)
                                {
                                    NeedPushEntryIds = NeedPushEntryIds.Remove(NeedPushEntryIds.LastIndexOf(","), 1);
                                }
                            }
                            else
                            {
                                break;
                            }
                        }
                        catch (Exception ex)
                        {
                            log.Error(ex);
                        }
                        finally
                        {
                            excelIndex++;
                        }
                    }
                    string ERPBackup       = Convert.ToString(ConfigurationManager.AppSettings["ERPBackup"].ToString().Trim());
                    string ResultDirectory = ERPBackup;     //处理完后文件保存到这里
                    if (!Directory.Exists(ResultDirectory)) //如果不存在就创建文件夹
                    {
                        Directory.CreateDirectory(ResultDirectory);
                    }
                    whs.SaveAs(Path.Combine(ResultDirectory, fileInfo.Name), 51);
                    //关闭对象
                    Marshal.ReleaseComObject(_workbook);
                    Marshal.ReleaseComObject(whs);
                    excelApp.Quit();
                    GC.Collect();

                    fileInfo.Delete();
                }
                catch (Exception ex)
                {
                    log.Error(ex);
                }
            }

            Executed = true;
        }
Beispiel #25
0
        private void outPutReciveBill()
        {
            List <List <object> > Bills = PUR_ReceiveBill.GetAllBill(client, "FDocumentStatus = 'C' and FCheckInComing = 1 and F_PAEZ_Exported=0");//F_PAEZ_Exported=0 未导出

            //List<List<object>> Bills = PUR_ReceiveBill.GetAllBill(client, "FBillNo='CGSL000328'");//F_PAEZ_Exported=0 未导出
            if (Bills.Count == 0)
            {
                log.Info("没有可以导出的收料通知单。");
                return;
            }

            MSExcel.Application excelApp = new MSExcel.Application
            {
                Visible = false//是打开可见
            };
            MSExcel.Workbooks _workbooks = excelApp.Workbooks;
            //MSExcel._Workbook _workbook = _workbooks.Add(AppDomain.CurrentDomain.BaseDirectory+"\\DD_20200901084512--ERP输出模板.XLS");
            MSExcel._Workbook  _workbook = _workbooks.Add(System.Reflection.Missing.Value);
            MSExcel._Worksheet whs       = _workbook.Sheets[1];//获取第1张工作表

            try
            {
                whs.Name = "Sheet1";
                //headline
                whs.Cells[1, 1]  = "进货单号";
                whs.Cells[1, 2]  = "产品编号";
                whs.Cells[1, 3]  = "物料名称";
                whs.Cells[1, 4]  = "物料规格";
                whs.Cells[1, 5]  = "厂商";
                whs.Cells[1, 6]  = "报检数量";
                whs.Cells[1, 7]  = "厂商编码";
                whs.Cells[1, 8]  = "物料分类名称";
                whs.Cells[1, 9]  = "实收数量";
                whs.Cells[1, 10] = "不良数";
                whs.Cells[1, 11] = "QIS报检单号";

                //同一单据内,相同物料合并数量
                Hashtable AllMaterialHT = new Hashtable();
                for (int i = 0; i < Bills.Count; i++)
                {
                    string BillNo         = Convert.ToString(Bills[i][1]);
                    string MaterialNumber = Convert.ToString(Bills[i][2]);
                    double Qty            = Convert.ToDouble(Bills[i][7]);
                    if (AllMaterialHT.ContainsKey(BillNo + MaterialNumber))
                    {
                        JObject jObject = AllMaterialHT[BillNo + MaterialNumber] as JObject;
                        double  OldQty  = Convert.ToDouble(Convert.ToString(jObject["Qty"]));
                        jObject["Qty"] = OldQty + Qty;
                    }
                    else
                    {
                        List <object> OtherInfo = Bills[i];
                        JObject       jObject   = new JObject();
                        jObject.Add("MaterialNumber", MaterialNumber);
                        jObject.Add("Qty", Qty);
                        jObject.Add("OtherInfoIndex", i); //原来的List的索引,保存以便在下方获取原来的其他数据
                        AllMaterialHT.Add(BillNo + MaterialNumber, jObject);
                    }
                }

                string DateTimeStr = DateTime.Now.ToString("yyyyMMddHHmmssf");
                Dictionary <string, string> BillIDsDic = new Dictionary <string, string>();
                StringBuilder sbBillNos = new StringBuilder();
                //for (int i = 0; i < Bills.Count; i++)
                int ExcelRow = 2;
                foreach (string BillAndMaterialNumber in AllMaterialHT.Keys)
                {
                    JObject       jObject        = AllMaterialHT[BillAndMaterialNumber] as JObject;
                    string        MaterialNumber = Convert.ToString(jObject["MaterialNumber"]);
                    double        Qty            = Convert.ToDouble(Convert.ToString(jObject["Qty"]));
                    int           i                  = Convert.ToInt32(Convert.ToString(jObject["OtherInfoIndex"]));
                    List <object> BillInfo           = Bills[i];
                    string        BillID             = Convert.ToString(Bills[i][0]);
                    string        BillNo             = Convert.ToString(Bills[i][1]);
                    string        FDetailEntity_FSeq = Convert.ToString(Bills[i][11]);
                    if (!BillIDsDic.ContainsKey(BillID))
                    {
                        BillIDsDic.Add(BillID, BillNo);
                        sbBillNos.Append("\"").Append(BillNo).Append("\",");
                    }
                    whs.Cells[ExcelRow, 1]  = BillNo;
                    whs.Cells[ExcelRow, 2]  = Bills[i][2];
                    whs.Cells[ExcelRow, 3]  = Bills[i][3];
                    whs.Cells[ExcelRow, 4]  = Bills[i][4];
                    whs.Cells[ExcelRow, 5]  = Bills[i][6];
                    whs.Cells[ExcelRow, 6]  = Qty;
                    whs.Cells[ExcelRow, 7]  = Bills[i][5];
                    whs.Cells[ExcelRow, 8]  = Bills[i][12];
                    whs.Cells[ExcelRow, 9]  = 0;
                    whs.Cells[ExcelRow, 10] = 0;
                    whs.Cells[ExcelRow, 11] = "'" + DateTimeStr + (i + 1).ToString("000");
                    ExcelRow++;
                }
                if (sbBillNos.Length > 0)
                {
                    sbBillNos.Remove(sbBillNos.Length - 1, 1);;  //移除掉最后一个","
                }

                string ERPOUTDirectory = Convert.ToString(ConfigurationManager.AppSettings["ERPOUT"].ToString().Trim());
                if (!Directory.Exists(ERPOUTDirectory))//如果不存在就创建文件夹
                {
                    Directory.CreateDirectory(ERPOUTDirectory);
                }
                string fileName = ERPOUTDirectory + "K3Cloud_DD_" + DateTimeStr + ".XLS";

                whs.SaveAs(fileName, 51);

                log.Info("正在更新该订单的同步状态。");
                PUR_ReceiveBill.ExportedToQIS(client, sbBillNos);
                //UpdateSyncStatus("PUR_ReceiveBill", BillIDsDic);
            }
            catch (Exception ex)
            {
                log.Error(ex);
            }
            finally {
                //关闭对象
                Marshal.ReleaseComObject(_workbook);
                Marshal.ReleaseComObject(whs);
                excelApp.Quit();
                GC.Collect();
            }
        }
Beispiel #26
0
        static void DisplayInExcelFull(IEnumerable <Account> accounts)
        {
            var excelApp = new Excel.Application();

            // Make the object visible.
            excelApp.Visible = true;

            // Create a new, empty workbook and add it to the collection returned
            // by property Workbooks. The new workbook becomes the active workbook.
            // Add has an optional parameter for specifying a particular template.
            // Because no argument is sent in this example, Add creates a new workbook.
            excelApp.Workbooks.Add();

            // This example uses a single workSheet.
            Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;

            //<Snippet5>
            // Establish column headings in cells A1 and B1.
            workSheet.Cells[1, "A"] = "ID Number";
            workSheet.Cells[1, "B"] = "Current Balance";
            //</Snippet5>

            //<Snippet6>
            // Establish column headings. Both parameters must be supplied in
            // Visual C# 2008 or earlier versions.
            //workSheet.get_Range("A1", Type.Missing).Value2 = "ID Number";
            //workSheet.get_Range("B1", Type.Missing).Value2 = "Current Balance";
            // Move to the next row.
            //workSheet.get_Range("A2", Type.Missing).Select();
            //</Snippet6>

            //<Snippet7>

            var row = 1;

            foreach (var acct in accounts)
            {
                row++;
                workSheet.Cells[row, "A"] = acct.ID;
                workSheet.Cells[row, "B"] = acct.Balance;
            }
            //</Snippet7>

            // Columns returns a Range object that represents all the columns
            // on the active worksheet. AutoFit fits the width of the columns
            // to their content.
            //ExcelApp.Columns.AutoFit();

            //<Snippet13>
            workSheet.Columns[1].AutoFit();
            workSheet.Columns[2].AutoFit();
            //</Snippet13>

            //<Snippet14>
            ((Excel.Range)workSheet.Columns[1]).AutoFit();
            ((Excel.Range)workSheet.Columns[2]).AutoFit();
            //</Snippet14>

            //<Snippet15>
            // Call to AutoFormat in Visual C# 2010.
            workSheet.Range["A1", "B3"].AutoFormat(
                Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
            //</Snippet15>

            //<Snippet16>
            // Call to AutoFormat in Visual C# 2010.
            workSheet.Range["A1", "B3"].AutoFormat(Format:
                                                   Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);
            //</Snippet16>

            //<Snippet12>
            // Put the spreadsheet contents on the clipboard. The Copy method has one
            // optional parameter for specifying a destination. Because no argument
            // is sent, the destination is the Clipboard.
            workSheet.Range["A1:B3"].Copy();
            //</Snippet12>

            //ExcelApp.get_Range("A5:C6").PasteSpecial( Transpose: true);
            //dynamic val = Type.Missing;
            //ExcelApp.get_Range("A5:C6").PasteSpecial(val, val, val, Transpose: true);
            //ExcelApp.get_Range("A5:C6").Copy();
        }
Beispiel #27
0
        private void WriteDataToExcel(IDictionary <string, IList <UserInvestIncomeEntity> > reportData, int deptId, string templateFilePath, string destinyFilePath)
        {
            if (reportData == null)
            {
                throw new NullReferenceException(nameof(reportData));
            }

            Excel.Application excelApp = new Excel.Application();

            Excel.Workbook workbook = excelApp.Workbooks.Open(templateFilePath);

            try
            {
                foreach (var item in reportData)
                {
                    var investorName           = item.Key;
                    Excel._Worksheet worksheet = null;

                    foreach (Excel.Worksheet sheet in workbook.Sheets)
                    {
                        if (sheet.Name == investorName)
                        {
                            worksheet = sheet;
                        }
                    }

                    if (worksheet == null)
                    {
                        continue;
                    }

                    WorksheetFormatting(worksheet);

                    var startRowIndex = 34;

                    var data = item.Value;

                    for (int i = 0; i < data.Count; i++)
                    {
                        var investInfo = data[i];

                        if (deptId != (int)EnumLibrary.AccountingDepartment.Day)
                        {
                            //周一(万元)
                            worksheet.Cells[startRowIndex + i, 3] = investInfo.MondayPositionValue / (int)EnumLibrary.NumericUnit.TenThousand;

                            //净资产(万元)
                            worksheet.Cells[startRowIndex + i, 4] = investInfo.CurrentAsset / (int)EnumLibrary.NumericUnit.TenThousand;

                            //持仓市值(万元)
                            worksheet.Cells[startRowIndex + i, 9] = investInfo.PositionValue / (int)EnumLibrary.NumericUnit.TenThousand;

                            //持仓仓位
                            worksheet.Cells[startRowIndex + i, 12] = investInfo.PositionRate;
                        }

                        //日期
                        worksheet.Cells[startRowIndex + i, 2] = investInfo.TradeTime;

                        //本年累计收益额(万元)
                        worksheet.Cells[startRowIndex + i, 5] = investInfo.AnnualActualProfit / (int)EnumLibrary.NumericUnit.TenThousand;

                        //当日收益率
                        worksheet.Cells[startRowIndex + i, 6] = investInfo.CurrentIncomeRate;

                        //日收益额(万元)
                        worksheet.Cells[startRowIndex + i, 7] = investInfo.CurrentActualProfit / (int)EnumLibrary.NumericUnit.TenThousand;

                        //本年累计收益率
                        worksheet.Cells[startRowIndex + i, 8] = investInfo.AnnualIncomeRate;
                    }
                }

                workbook.SaveAs(destinyFilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                workbook = null;
                excelApp.Quit();
                excelApp = null;
            }
        }
Beispiel #28
0
        public void getExcelFile()
        {
            while (!comboBoxSelectedIndexChanged)
            {
                this.Dispatcher.Invoke(() => { tbAction.Text = "Choose a Test Type!"; });
                Thread.Sleep(100);
            }

            this.Dispatcher.Invoke(() => { tbAction.Text = string.Empty; });

            Excel.Application xlApp       = new Excel.Application();
            Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(@"C:\Users\lazlo\Desktop\smokeTest.xlsx");
            Excel._Worksheet  xlWorksheet = xlWorkbook.Sheets[1];
            Microsoft.Office.Interop.Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;

            int row = 3;

            if (xlRange.Cells[2, 1] != null && xlRange.Cells[2, 1].Value2 != null)
            {
                this.Dispatcher.Invoke(() =>
                {
                    tbAction.Text = (xlRange.Cells[2, 1].Value2.ToString());
                });
            }
            if (xlRange.Cells[2, 2] != null && xlRange.Cells[2, 2].Value2 != null)
            {
                this.Dispatcher.Invoke(() =>
                {
                    tbExpected.Text = (xlRange.Cells[2, 2].Value2.ToString());
                });
            }
            while (!ButtonPressed)
            {
                Thread.Sleep(100);
            }
            ButtonPressed = false;

            while (row <= rowCount)
            {
                for (int col = 1; col <= colCount; col++)
                {
                    switch (col)
                    {
                    case 1:
                        if (xlRange.Cells[row, col] != null && xlRange.Cells[row, col].Value2 != null)
                        {
                            this.Dispatcher.Invoke(() =>
                            {
                                tbAction.Text = (xlRange.Cells[row, col].Value2.ToString());
                            });
                        }

                        break;

                    case 2:
                        if (xlRange.Cells[row, col] != null && xlRange.Cells[row, col].Value2 != null)
                        {
                            this.Dispatcher.Invoke(() =>
                            {
                                tbExpected.Text = (xlRange.Cells[row, col].Value2.ToString());
                            });
                        }

                        break;

                    default:
                        break;
                    }
                }
                while (!ButtonPressed)
                {
                    Thread.Sleep(100);
                }
                ButtonPressed = false;
                row++;
            }



            GC.Collect();
            GC.WaitForPendingFinalizers();

            Marshal.ReleaseComObject(xlRange);
            Marshal.ReleaseComObject(xlWorksheet);

            xlWorkbook.Close();
            Marshal.ReleaseComObject(xlWorkbook);

            xlApp.Quit();
            Marshal.ReleaseComObject(xlApp);
        }
Beispiel #29
0
        private void bwrkReadExcelFile_DoWork(object sender, DoWorkEventArgs e)
        {
            xlApp       = new XL.Application();
            xlWorkbook  = xlApp.Workbooks.Open("C:\\Users\\sferry\\source\\repos\\V1000_Param_Prog\\V1000_Param_Prog\\data\\Parameter List.xlsx");
            xlWorksheet = xlWorkbook.Sheets[1];
            xlRange     = xlWorksheet.UsedRange;

            V1000_xlRead_List.Clear();

            ProgressArgs.xlRead_Total_Units = xlRange.Rows.Count - 1;

            //for (int i = 2; i <= 21; i++)
            for (int i = 2; i <= xlRange.Rows.Count; i++)
            {
                ProgressArgs.xlRead_Unit     = i - 1;
                ProgressArgs.xlRead_Progress = (byte)(((float)ProgressArgs.xlRead_Unit / ProgressArgs.xlRead_Total_Units) * 100);

                bwrkReadExcelFile.ReportProgress((int)i);
                if (bwrkReadExcelFile.CancellationPending)
                {
                    e.Cancel = true;
                    ProgressArgs.xlRead_Stat = 0x03;
                    bwrkReadExcelFile.ReportProgress(0);
                    return;
                }

                V1000_File_Data ParamData = new V1000_File_Data();

                if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null)
                {
                    ParamData.RegAddress = xlRange.Cells[i, 1].Value2.ToString();
                }
                else
                {
                    ParamData.RegAddress = "0";
                }

                if (xlRange.Cells[i, 2] != null && xlRange.Cells[i, 2].Value2 != null)
                {
                    ParamData.ParamNum = xlRange.Cells[i, 2].Value2.ToString();
                }
                else
                {
                    ParamData.ParamNum = "0";
                }

                if (xlRange.Cells[i, 3] != null && xlRange.Cells[i, 3].Value2 != null)
                {
                    ParamData.ParamName = xlRange.Cells[i, 3].Value2.ToString();
                }
                else
                {
                    ParamData.ParamName = "0";
                }

                if (xlRange.Cells[i, 4] != null && xlRange.Cells[i, 4].Value2 != null)
                {
                    ParamData.DefVal = xlRange.Cells[i, 4].Value2.ToString();
                }
                else
                {
                    ParamData.DefVal = "0";
                }

                if (xlRange.Cells[i, 5] != null && xlRange.Cells[i, 5].Value2 != null)
                {
                    ParamData.Multiplier = xlRange.Cells[i, 5].Value2.ToString();
                }
                else
                {
                    ParamData.Multiplier = "1";
                }

                V1000_xlRead_List.Add(ParamData);
            }

            ProgressArgs.xlRead_Progress = 100;
            ProgressArgs.xlRead_Stat     = 0x02;
            e.Result = ProgressArgs.xlRead_Stat;
            bwrkReadExcelFile.ReportProgress(100);
        }
Beispiel #30
0
        public static void getExcelFile()
        {
            Excel.Application xlApp       = new Excel.Application();
            Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(@"E:\Json\TEST-DATA.xlsx");
            Excel._Worksheet  xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range       xlRange     = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;

            for (int i = 2; i <= rowCount; i++)
            {
                string fullName     = "null";
                string address1     = "null";
                string address2     = "null";
                string city         = "null";
                string stateCode    = "null";
                string zipCode      = "null";
                string countryName  = "null";
                string productName  = "null";
                string productType  = "null";
                string productColor = "null";
                string productSize  = "null";
                string quantity     = "null";
                string productID    = "null";

                if (xlRange.Cells[i, 1].Value2 != null)
                {
                    fullName = xlRange.Cells[i, 1].Value2.ToString().Replace("'", @"\'");
                }
                if (xlRange.Cells[i, 2].Value2 != null)
                {
                    address1 = xlRange.Cells[i, 2].Value2.ToString().Replace("'", @"\'");
                }
                if (xlRange.Cells[i, 3].Value2 != null)
                {
                    address2 = xlRange.Cells[i, 3].Value2.ToString().Replace("'", @"\'");
                }
                if (xlRange.Cells[i, 4].Value2 != null)
                {
                    city = xlRange.Cells[i, 4].Value2.ToString().Replace("'", @"\'");
                }
                if (xlRange.Cells[i, 5].Value2 != null)
                {
                    stateCode = xlRange.Cells[i, 5].Value2.ToString().Replace("'", @"\'");
                }
                if (xlRange.Cells[i, 6].Value2 != null)
                {
                    zipCode = xlRange.Cells[i, 6].Value2.ToString().Replace("'", @"\'");
                }
                if (xlRange.Cells[i, 7].Value2 != null)
                {
                    countryName = xlRange.Cells[i, 7].Value2.ToString().Replace("'", @"\'");
                }
                if (xlRange.Cells[i, 8].Value2 != null)
                {
                    productName = xlRange.Cells[i, 8].Value2.ToString().Replace("'", @"\'");
                }
                if (xlRange.Cells[i, 9].Value2 != null)
                {
                    productType = xlRange.Cells[i, 9].Value2.ToString().Replace("'", @"\'");
                }
                if (xlRange.Cells[i, 10].Value2 != null)
                {
                    productColor = xlRange.Cells[i, 10].Value2.ToString().Replace("'", @"\'");
                }
                if (xlRange.Cells[i, 11].Value2 != null)
                {
                    productSize = xlRange.Cells[i, 11].Value2.ToString().Replace("'", @"\'");
                }
                if (xlRange.Cells[i, 12].Value2 != null)
                {
                    quantity = xlRange.Cells[i, 12].Value2.ToString().Replace("'", @"\'");
                }
                if (xlRange.Cells[i, 13].Value2 != null)
                {
                    productID = xlRange.Cells[i, 13].Value2.ToString().Replace("'", @"\'");
                }

                List <Information> information = new List <Information>();
                information.Add(new Information(fullName, address1, address2, city, stateCode, zipCode, countryName, productName, productType, productColor, productSize, quantity, productID));
                addData(information, i);
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();

            Marshal.ReleaseComObject(xlRange);
            Marshal.ReleaseComObject(xlWorksheet);

            xlWorkbook.Close();
            Marshal.ReleaseComObject(xlWorkbook);

            xlApp.Quit();
            Marshal.ReleaseComObject(xlApp);
        }
Beispiel #31
0
 /// <summary>
 /// 为单元格设置公式
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="objCell">单元格</param>
 /// <param name="strFormula">公式</param>
 public void SetFormula(Excel._Worksheet CurSheet, object objCell, string strFormula)
 {
     CurSheet.get_Range(objCell, mValue).Formula = strFormula;
 }
Beispiel #32
0
        public List<AccountExcel> readExcel(string sExcelPath)
        {
            var lReturn = new List<AccountExcel>();
            string valueString = string.Empty;
            objExcelApp = new Microsoft.Office.Interop.Excel.Application();
            objBooks = (Excel.Workbooks)objExcelApp.Workbooks;
            //Open the workbook containing the address data.
            objBook = objBooks.Open(sExcelPath, Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value,
            Missing.Value, Missing.Value);
            //Get a reference to the first sheet of the workbook.
            objSheets = objBook.Worksheets;
            objSheet = (Excel._Worksheet)objSheets.get_Item(1);

            rngLast = objSheet.get_Range("A1").SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell);
            long lLastRow = rngLast.Row;
            long lLastCol = rngLast.Column;

            for (long rowCounter = 2; rowCounter <= lLastRow; rowCounter++) //FirstRow Has Headers - start at row 2
            {
                if (ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value) != "")
                {
                    var adAccount = new AccountExcel();

                    adAccount.sCustomerNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 1]).Value);
                    adAccount.sAccountName = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 40]).Value);
                    adAccount.sAddressLine1 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 2]).Value);
                    adAccount.sAddressLine2 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 5]).Value);
                    adAccount.sAddressLine3 = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 9]).Value);
                    adAccount.sPostCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 15]).Value);
                    adAccount.sTelephone = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 17]).Value);
                    adAccount.sVatNumber = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 18]).Value);
                    adAccount.sCountryCode = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 21]).Value);
                    adAccount.sEmail = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 37]).Value);
                    adAccount.sWeb = "";// ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 38]).Value);
                    adAccount.sKAM = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 31]).Value);
                    adAccount.sRegion = ToStringHandlesNulls(((Excel.Range)objSheet.Cells[rowCounter, 24]).Value);
                    lReturn.Add(adAccount);
                }
            }
              //Close the Excel Object
            objBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);

            objBooks.Close();
            objExcelApp.Quit();

            Marshal.ReleaseComObject(objSheet);
            Marshal.ReleaseComObject(objSheets);
            Marshal.ReleaseComObject(objBooks);
            Marshal.ReleaseComObject(objBook);
            Marshal.ReleaseComObject(objExcelApp);

            objSheet = null;
            objSheets = null;
            objBooks = null;
            objBook = null;
            objExcelApp = null;

            GC.GetTotalMemory(false);
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.GetTotalMemory(true);
            return (lReturn);
        }
Beispiel #33
0
        // Program entry point.
        static void Main(string[] args)
        {
            oXL = new Excel.Application();
            oXL.Visible = true;

            oWB = (Excel._Workbook)(oXL.Workbooks.Add(Type.Missing));
            oSheet = (Excel._Worksheet)oWB.ActiveSheet;

            Console.WriteLine("Excel worksheet created.");

            List<string> phonePages = GetListOfPhonePages();
            List<string> specPages = FindPhoneSpecPages(phonePages);

            int i = 1;
            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();
            foreach (string specPage in specPages)
            {
                // Specs are extracted on multiple threads to bypass
                // the time it takes to make web requests and load the DOM on the page.
                new Thread(() =>
                {
                    ExtractSpecs(specPage, i);
                }).Start();
                Random random = new Random();
                Thread.Sleep(random.Next(1000,3000));
                i++;
            }

            for (int j = 0; j < 1000; j++)
            {
                Thread.Sleep(1000);
                TimeSpan ts = stopWatch.Elapsed;
                // Format and display the TimeSpan value.
                string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
                    ts.Hours, ts.Minutes, ts.Seconds,
                    ts.Milliseconds / 10);
                Console.WriteLine("RunTime " + elapsedTime);
            }
        }
Beispiel #34
0
 /// <summary>
 /// Метдо установки темплейта для дальнейшего заполнения
 /// </summary>
 /// <param name="template_filename">Имя и путь файла</param>
 /// <param name="background">Создать в бэкграунде</param>
 public static void SetTemplate(string template_filename, bool background)
 {
     if (!background)
         excelApp.Visible = true;
     else
         excelApp.Visible = false;
     excelApp.Workbooks.Add(template_filename);
     workSheet = excelApp.ActiveSheet;
 }
Beispiel #35
0
        private void button1_Click(object sender, EventArgs e)
        {
            /// check if user has selected a file - if not then warn the user and return
            /// verify a file has been selected by observing the label lbl_1.
            if (lbl_1.Text == "Please Select a File")
            {
                warningLabel.ForeColor = Color.Red;
                warningLabel.Text      = "Please select a file before continuing";
                return;
            }

            warningLabel.Text = "";

            /// check if user has selected a number of files
            if (comboBox1.SelectedIndex == -1)
            {
                warningLabel.ForeColor = Color.Red;
                warningLabel.Text      = "Please select the number of samples in the file";
                return;
            }

            warningLabel.Text = "";

            string[] filePath = lbl_1.Text.Split('\n');
            foreach (string sFileName in filePath)
            {
                //https://coderwall.com/p/app3ya/read-excel-file-in-c
                //Create COM Objects. Create a COM object for everything that is referenced
                Excel.Application xlApp = new Excel.Application();
                xlApp.Visible = false;
                Excel.Workbook   xlWorkbook  = xlApp.Workbooks.Open(sFileName);
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
                Excel.Range      xlRange     = xlWorksheet.UsedRange;

                //create COM objects for copy of original document
                Excel.Application xlAppCopy = new Excel.Application();
                xlAppCopy.Visible = false;
                Excel.Workbook   xlWorkbookCopy  = xlAppCopy.Workbooks.Add();
                Excel._Worksheet xlWorksheetCopy = xlWorkbookCopy.Sheets[1];

                //Copy all contents from selected file to the new excel doc
                int         nRows = xlRange.Rows.Count;
                int         nEndDestinationCopy = nRows;
                string      endDestinationCopy  = "P" + nEndDestinationCopy.ToString(); // This will only work if all the docs are P columns wide!
                Excel.Range xlRangeCopy         = xlWorksheetCopy.get_Range("A1", endDestinationCopy);
                xlRangeCopy.Value2 = xlRange.Value2;
                //create COM objects for output file
                Excel.Application xlApp2 = new Excel.Application();
                xlApp2.Visible = false;
                Excel.Workbook   xlWorkbook2  = xlApp2.Workbooks.Add();
                Excel._Worksheet xlWorksheet2 = xlWorkbook2.Sheets[1];

                //insert HODS codes
                xlWorksheet2.Cells[1, 1] = box_0101.Text;
                xlWorksheet2.Cells[1, 2] = box_0102.Text;
                xlWorksheet2.Cells[1, 3] = box_0103.Text;
                xlWorksheet2.Cells[1, 4] = box_0104.Text;
                xlWorksheet2.Cells[1, 5] = box_0105.Text;
                xlWorksheet2.Cells[1, 6] = box_0106.Text;
                xlWorksheet2.Cells[1, 7] = box_0107.Text;
                xlWorksheet2.Cells[1, 8] = box_0108.Text;

                //LOOP THROUGH SAMPLES
                //for loop to repeat for each sample. Can reinstate this later.
                for (int sample = 1; sample < comboBox1.SelectedIndex + 2; sample++)
                {
                    //i is the row we're interested in
                    int i = sample + 2;
                    //https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range.autofilter?view=excel-pia

                    //PUT SAMPLE NAME IN EXCEL


                    ///SORT AND FILTER- i is column number
                    xlRangeCopy.Sort(xlRangeCopy.Columns[i], Excel.XlSortOrder.xlAscending, Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlYes);
                    xlRangeCopy.AutoFilter(i, "<100");

                    //COUNT FILTERED ROWS
                    //https://stackoverflow.com/questions/41731714/counting-rows-of-filtered-excel-range-in-c-sharp
                    //NOTE, this includes the first row in the count, so -1
                    //this counts visible cells
                    Excel.Range xlRange3 = xlRangeCopy.SpecialCells(Excel.XlCellType.xlCellTypeVisible);
                    // -1 because of headers
                    int    nFilteredRows = xlRange3.Rows.Count - 1;
                    string sampleLetter  = ((char)(sample + 64)).ToString();
                    //MOVE FILTERED ROWS- will need to change the values in get range to fit the sample and number of filtered genes
                    //-1 becaause to get 4 rows we need A2:A5 and  5-2 is 4-1
                    int         nStartSource      = 2;
                    int         nEndSource        = nStartSource + nFilteredRows - 1;
                    int         nStartDestination = 2;
                    int         nEndDestination   = nStartDestination + nFilteredRows - 1;
                    string      startSource       = "A" + nStartSource.ToString();
                    string      startDestination  = sampleLetter + nStartDestination.ToString();
                    string      endSource         = "A" + nEndSource.ToString();
                    string      endDestination    = sampleLetter + nEndDestination.ToString();
                    Excel.Range xlRange2          = xlWorksheet2.get_Range(startDestination, endDestination);
                    Excel.Range sourceRng         = xlWorksheetCopy.get_Range(startSource, endSource);
                    xlRange2.Value2 = sourceRng.Value2;
                    xlRange2.RemoveDuplicates(1, Excel.XlYesNoGuess.xlNo);

                    //REMOVE FILTER
                    xlRangeCopy.AutoFilter(i);

                    Marshal.ReleaseComObject(sourceRng);
                    Marshal.ReleaseComObject(xlRange2);
                    Marshal.ReleaseComObject(xlRange3);
                }


                //TRANSPOSE

                //Access range of data in worksheet, and dimensions
                Excel.Range xlRange2Used     = xlWorksheet2.UsedRange;
                int         rowsXlRange2Used = xlRange2Used.Rows.Count;
                int         colsXlRange2Used = xlRange2Used.Columns.Count;

                //formulate location for transposed range
                string      newRangeStart   = "A" + (rowsXlRange2Used + 2).ToString();
                string      newRangeEnd     = ((char)(rowsXlRange2Used + 64)).ToString() + (rowsXlRange2Used + colsXlRange2Used + 1).ToString();
                Excel.Range xlRange2Replace = xlWorksheet2.get_Range(newRangeStart, newRangeEnd);

                //transpose into new location
                xlWorksheet2.Range[newRangeStart + ":" + newRangeEnd].Value = xlApp2.WorksheetFunction.Transpose(xlRange2Used);

                //delete columns with untransposed information
                xlRange2Used.EntireRow.Delete();


                //SAVE OUTPUT DOCUMENT
                string fileName      = Path.GetFileName(sFileName);      //retreives the filename from the path
                string directoryName = Path.GetDirectoryName(sFileName); //retreives path of the directory of selected file
                xlWorkbook2.SaveAs(directoryName + "/" + "output_" + fileName);
                label_output.Text = "Output file is complete: " + directoryName + "/" + "output_" + fileName;

                //Close documents without displaying any prompt boxes
                xlApp2.DisplayAlerts = false;
                xlApp2.Quit();
                xlAppCopy.DisplayAlerts = false;
                xlAppCopy.Quit();
                xlApp.DisplayAlerts = false;
                xlApp.Quit();

                //cleanup
                GC.Collect();
                GC.WaitForPendingFinalizers();
                //rule of thumb for releasing com objects:
                //  never use two dots, all COM objects must be referenced and released individually
                //  ex: [somthing].[something].[something] is bad

                //release com objects to fully kill excel process from running in the background

                Marshal.ReleaseComObject(xlRange);
                Marshal.ReleaseComObject(xlRange2Replace);
                Marshal.ReleaseComObject(xlRange2Used);
                Marshal.ReleaseComObject(xlRangeCopy);
                Marshal.ReleaseComObject(xlWorksheet);
                Marshal.ReleaseComObject(xlWorksheetCopy);
                Marshal.ReleaseComObject(xlWorksheet2);
                Marshal.ReleaseComObject(xlWorkbook);
                Marshal.ReleaseComObject(xlWorkbook2);
                Marshal.ReleaseComObject(xlWorkbookCopy);
                Marshal.ReleaseComObject(xlApp);
                Marshal.ReleaseComObject(xlApp2);
                Marshal.ReleaseComObject(xlAppCopy);
            }
        }
 private void button1_Click(object sender, System.EventArgs e)
 {
 
 try
 {
 // Instantiate Excel and start a new workbook.
 objApp = new Microsoft.Office.Interop.Excel.Application();
 objBooks = objApp.Workbooks;
 objBook = objBooks.Add(Missing.Value);
 objSheets = objBook.Worksheets;
 objSheet = (Microsoft.Office.Interop.Excel._Worksheet)objSheets.get_Item(1);
 
 //Get the range where the starting cell has the address
 //m_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
 range = objSheet.get_Range("A1", Missing.Value);
 range = range.get_Resize(5, 5);
 
 //Create an array.
 double[,] saRet = new double[5, 5];
 
 //Fill the array.
 for (long iRow = 0; iRow < 5; iRow++)
 {
 for (long iCol = 0; iCol < 5; iCol++)
 {
 //Put a counter in the cell.
 saRet[iRow, iCol] = iRow * iCol * iCol;
 }
 }
 
 //Set the range value to the array.
 range.set_Value(Missing.Value, saRet);
 objApp.Visible = true;
 objApp.UserControl = true;
 
 }
 
 catch( Exception theException )
 {
 String errorMessage;
 errorMessage = "Error: ";
 errorMessage = String.Concat( errorMessage, theException.Message );
 errorMessage = String.Concat( errorMessage, " Line: " );
 errorMessage = String.Concat( errorMessage, theException.Source );
 
 MessageBox.Show( errorMessage, "Error" );
 }
 
 
 
 Microsoft.Office.Interop.Excel.Range currentFind = null;
 Microsoft.Office.Interop.Excel.Range firstFind = null;
 
 string A = "16";
 
 // You should specify all these parameters every time you call this method,
 // since they can be overridden in the user interface.
 currentFind = objSheet.Cells.Find(A, Type.Missing,
 Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues, Microsoft.Office.Interop.Excel.XlLookAt.xlWhole,
 Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Microsoft.Office.Interop.Excel.XlSearchDirection.xlNext, false,
 Type.Missing, Type.Missing);
 
 while (currentFind != null)
 {
 // Keep track of the first range you find.
 if (firstFind == null)
 {
 firstFind = currentFind;
 
 //textBox1.Text = currentFind.get_Address(true, true, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, false, Missing.Value);
 
 
 }
 
 // If you didn't move to a new range, you are done.
 else if (currentFind.get_Address(Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing)
 == firstFind.get_Address(Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing))
 {
 break;
 }
 
 currentFind.Font.Color = System.Drawing.ColorTranslator.ToOl
 (System.Drawing.Color.Red);
 currentFind.Font.Bold = true;
 
 
 currentFind = objSheet.Cells.FindNext(currentFind);
 }
 
 }
        private void BtnXuatExcel_Click(object sender, EventArgs e)
        {
            //export2Excel(dtgvTonKho, @"D:\", "xuatfileTonKho");
            //Tạo đối tượng lưu tệp tin
            SaveFileDialog fsave = new SaveFileDialog();

            //chỉ ra đuôi
            fsave.Filter = "(Tất cả các tệp) | *.*|(Các tệp excel)|*.xlsx";
            fsave.ShowDialog();
            //xử lý
            if (fsave.FileName != "")
            {
                //tạo excel app
                Excel.Application app = new Excel.Application();
                //tạo 1 workbook
                Excel.Workbook wb = app.Workbooks.Add(Type.Missing);

                //tạo sheet
                Excel._Worksheet sheet = null;

                try
                {
                    //đọc dữ liệu từ Listview export ra file Excel có đinh dạng
                    sheet      = wb.ActiveSheet;
                    sheet.Name = "Báo cáo tồn kho";
                    sheet.Columns.ColumnWidth = 25;
                    sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, dtgvTonKho.Columns.Count]].Merge();
                    sheet.Cells[1, 1].value = "BÁO CÁO TỒN KHO";
                    sheet.Cells[1, 1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    //sinh tiêu đề
                    for (int i = 1; i <= dtgvTonKho.Columns.Count; i++)
                    {
                        sheet.Cells[2, i] = dtgvTonKho.Columns[i - 1].HeaderText;
                    }
                    //sinh du lieu
                    for (int i = 0; i < dtgvTonKho.Rows.Count; i++)
                    {
                        for (int j = 0; j < dtgvTonKho.Columns.Count; j++)
                        {
                            if (dtgvTonKho.Rows[i].Cells[j].Value != null)
                            {
                                sheet.Cells[i + 3, j + 1] = dtgvTonKho.Rows[i].Cells[j].Value.ToString();
                            }
                        }
                    }
                    //ghi lại
                    wb.SaveAs(fsave.FileName);
                    MessageBox.Show("Xuất thành công", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                finally
                {
                    app.Quit();
                    wb = null;
                }
            }
            else
            {
                MessageBox.Show("Bạn không chọn tệp tin nào", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
        protected override void Execute(CodeActivityContext context)
        {
            //Load Input Fields unto local variables
            string filePath = FilePath.Get(context);

            //Validate that file path is not empty
            if (!String.IsNullOrEmpty(filePath))
            {
                if (File.Exists(filePath))
                {
                    try
                    {
                        //Initialize Excel Interop objects;
                        object            m           = Type.Missing;
                        Excel.Application xlApp       = new Excel.Application();
                        Excel.Workbooks   xlWorkbooks = xlApp.Workbooks;
                        Excel.Workbook    xlWorkbook  = xlWorkbooks.Open(filePath, m, true, m, m, m, m, m, m, m, m, m, m, m, m);
                        Excel._Worksheet  xlWorksheet = xlWorkbook.Sheets[1];
                        Excel.Range       xlRange     = xlWorksheet.UsedRange;

                        // Gather Row and Col counts
                        Int32 lastRow = xlRange.Rows.Count;
                        Int32 lCol    = xlRange.Columns.Count;
                        Int32 sCol    = lCol + 1;

                        // Create local variables for processing
                        int    i;
                        string vLstCol;

                        //iterate by the existing columns to determine if a current Status column exists
                        for (i = 1; i <= lCol; i++)
                        {
                            vLstCol = xlWorksheet.Cells[1, i].Value;
                            if (vLstCol == "Status")
                            {
                                sCol = i;
                                lCol = i - 1;
                            }
                        }

                        //Count all rows that Status Column has the value of "Complete"
                        int countComplete = (int)xlApp.WorksheetFunction.CountIf(xlRange.Columns[sCol, m], "Complete") + 1;
                        //Count all rows that are not empty on the first column (Item-Index column)
                        int countNotNull = (int)xlApp.WorksheetFunction.CountA(xlRange.Columns[1, m]);

                        //Validate that complete rows match the lastRow count
                        if (countComplete == countNotNull)
                        {
                            //Return True for Complete
                            isComplete.Set(context, true);
                        }
                        else
                        {
                            //Return False for not complete
                            isComplete.Set(context, false);
                        }

                        //DebugOut Output
                        DebugOut.Set(context, "CC: " + countComplete.ToString() + " | CA: " + countNotNull.ToString() + " | LR: " + lastRow.ToString());

                        //Close Workbook no Save
                        xlWorkbook.Close(false, m, m);

                        //CLOSE AND GARBAGE COLLECT
                        Marshal.ReleaseComObject(xlWorksheet);
                        xlWorksheet = null;
                        Marshal.ReleaseComObject(xlWorkbooks);
                        xlWorkbooks = null;
                        Marshal.ReleaseComObject(xlWorkbook);
                        xlWorkbook = null;

                        xlApp.Quit();

                        Marshal.ReleaseComObject(xlApp);
                        xlApp = null;

                        GC.Collect();                  //Garbage Collect
                        GC.WaitForPendingFinalizers(); //Wait until Garbage Collect completes
                    }
                    catch
                    {
                        throw;
                    }
                }
                else
                {
                    throw new FileNotFoundException();
                }
            }
            else
            {
                throw new ArgumentNullException();
            }
        }
Beispiel #39
0
 /// <summary>
 /// Метод открытия уже существующего файла
 /// </summary>
 /// <param name="template_filename">Имя и путь файла</param>
 /// <param name="background">Создать в бэкграунде</param>
 public static void OpenFile(string filename, bool background)
 {
     if (!background)
         excelApp.Visible = true;
     else
         excelApp.Visible = false;
     excelApp.Workbooks.Open(filename);
     workSheet = excelApp.ActiveSheet;
 }
Beispiel #40
0
        /// <summary>
        /// 根据Excel表格路径将该表格添加到总表中
        /// </summary>
        /// <param name="strFileName">原表格路径</param>
        public void OpenExcel(string strFileName)
        {
            object missing = System.Reflection.Missing.Value;

            Excel.Application excel = new Excel.Application();//lauch excel application

            if (excel == null)
            {
                textBoxOfResultForCreateExcel.AppendText("Can't access excel" + "  " + strFileName);
            }
            else
            {
                excel.Visible = false; excel.UserControl = true;
                // 以只读的形式打开EXCEL文件
                Excel.Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
                                                                     missing, missing, missing, true, missing, missing, missing, missing, missing);
                //取得第一个工作薄
                Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets.get_Item(1);
                //取得总记录行数    (包括标题列)
                int rowsint = ws.UsedRange.Cells.Rows.Count; //得到行数
                //int columnsint = mySheet.UsedRange.Cells.Columns.Count;//得到列数
                //取得数据范围区域   (不包括标题列)
                Excel.Range rng1 = ws.Cells.get_Range("A2", "A" + rowsint);
                Excel.Range rng2 = ws.Cells.get_Range("B2", "B" + rowsint);
                Excel.Range rng3 = ws.Cells.get_Range("C2", "C" + rowsint);
                Excel.Range rng4 = ws.Cells.get_Range("D2", "D" + rowsint);
                object[,] arry1 = (object[, ])rng1.Value2;   //get range's value ,学号
                object[,] arry2 = (object[, ])rng2.Value2;   //专业
                object[,] arry3 = (object[, ])rng3.Value2;   //get range's value  姓名
                object[,] arry4 = (object[, ])rng4.Value2;   //性别
                //将arry1赋给一个数组
                string classId = arry1[2, 1].ToString().Remove(arry1[2, 1].ToString().Length - 2);
                //新建Excelapp
                Excel.Application excelApp = new Excel.Application();
                excelApp.Visible = true;
                //设置不显示确认修改提示
                excelApp.DisplayAlerts = false;
                //得到workbook对象,打开总表
                Excel.Workbook excleBook = excelApp.Workbooks.Open(@"D:\总表.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                //指定要操作的sheet
                Excel._Worksheet excleSheet = (Excel.Worksheet)excelApp.Worksheets.get_Item(1);
                //获取总记录行数(包括标题列)
                int rowsCount = excleSheet.UsedRange.Cells.Rows.Count;
                //将分表的记录写入到总表中去
                for (int i = 1; i <= arry1.Length; i++)
                {
                    if (arry1[i, 1] == null)
                    {
                        excelApp.Cells[rowsCount + i, 1] = "";
                    }
                    else
                    {
                        excelApp.Cells[rowsCount + i, 1] = arry1[i, 1].ToString();
                    }
                    if (arry2[i, 1] == null)
                    {
                        excelApp.Cells[rowsCount + i, 2] = "";
                    }
                    else
                    {
                        excelApp.Cells[rowsCount + i, 2] = arry2[i, 1].ToString();
                    }
                    if (arry3[i, 1] == null)
                    {
                        excelApp.Cells[rowsCount + i, 3] = "";
                    }
                    else
                    {
                        if (arry3[i, 1].ToString().EndsWith("0001"))
                        {
                            excelApp.Cells[rowsCount + i, 3] = arry3[i, 1].ToString().Remove(arry3[i, 1].ToString().Length - 4);
                        }
                        else
                        {
                            excelApp.Cells[rowsCount + i, 3] = arry3[i, 1].ToString();
                        }
                    }
                    if (arry4[i, 1] == null)
                    {
                        excelApp.Cells[rowsCount + i, 4] = "";
                    }
                    else
                    {
                        excelApp.Cells[rowsCount + i, 4] = arry4[i, 1].ToString();
                    }

                    excelApp.Cells[rowsCount + i, 5] = classId;
                }
                //for (int i = 1; i <= rowsint - 1; i++)
                //    for (int i = 1; i <= rowsint - 2; i++)
                //    {

                //        arry[i - 1, 0] = arry1[i, 1].ToString();

                //        arry[i - 1, 1] = arry2[i, 1].ToString();

                //        arry[i - 1, 2] = arry3[i, 1].ToString();

                //        arry[i - 1, 3] = arry4[i, 1].ToString();
                //    }
                //    string a = "";
                //    for (int i = 0; i <= rowsint - 3; i++)
                //    {
                //        a += arry[i, 0] + "|" + arry[i, 1] + "|" + arry[i, 2] + "|" + arry[i, 3] + "\n";

                //    }
                //    this.label1.Text = a;
                //关闭打开的表

                wb.Close(false, Missing.Value, Missing.Value);
                excleBook.Close(true, Missing.Value, Missing.Value);
                //把excel,workbook,worksheet设置为null,防止内存泄漏
                wb        = null;
                excleBook = null;
                ws        = null;
                excleBook = null;
                //程序退出
                excel.Quit();
                excelApp.Quit();
                excel    = null;
                excelApp = null;
                GC.Collect();
            }
        }
Beispiel #41
0
        private void tmrData_Tick(object sender, EventArgs e)
        {
            int i;
            uint recLength;

            recLength = 25;
            // Get the Live data, to show in gauge
            txBuf[0] = (byte)CMD.GET_LIVE_DATA;
            txBuf[1] = 2;
            //USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
            string port = cbxPorts.SelectedItem.ToString();
            if (port == "")
                return;
            System.IO.Ports.SerialPort myPort = new System.IO.Ports.SerialPort(port);
            if (myPort.IsOpen == false) //if not open, open the port
                myPort.Open();
            //do your work here
            if (myPort.IsOpen == false) //if not open, open the port
                return;
            String data;
            data = myPort.ReadLine();
            myPort.Close();
            string [] dht=data.Split(',');
            //s.dataPtr = rxBuf[18];
            //i = 19;
            //convertData(ref currData, ref i);
            //gTemp.Value = currData.temp;
            //gPressure.Value = currData.pressure;
            //gHumid.Value = currData.humidity;
            if (dht.Length != 3)
                return;
            gHumid.Value = float.Parse(dht[0]);
            gTemp.Value = float.Parse(dht[1]);
            gPressure.Value = int.Parse(dht[2]);

            if (stt == 0)
            {
                //Start Excel and get Application object.
                oXL = new Excel.Application();
                oXL.Visible = true;

                //Get a new workbook.
                oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;
            }
                //Add table headers going cell by cell.
                oSheet.Cells[stt + 1, 1] = gTemp.Value.ToString();
                oSheet.Cells[stt + 1, 2] = gPressure.Value.ToString();
                oSheet.Cells[stt + 1, 3] = gHumid.Value.ToString();
                //oSheet.Cells[1, 4] = "Salary";

            DateTime timeNow = DateTime.Now;
            w[stt].humidity = gHumid.Value;
            w[stt].temp = gTemp.Value;
            w[stt].pressure = gPressure.Value;
            w[stt++].time = timeNow;
            if (stt == 999)
                stt = 0;
            Redraw(0);

            goLive = false;
            if (goLive) // Only load data to w if graph shows live data from PIC
            {
                recLength = 64;
                txBuf[0] = (byte)CMD.GET_FIRST_DATA;
                txBuf[1] = 2;
                USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
                loadData(10);
                for (i = 0; i < 5; i++)
                {
                    txBuf[0] = (byte)CMD.GET_NEXT_DATA;
                    txBuf[1] = 2;
                    USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
                    loadData(10);
                }

                txBuf[0] = (byte)CMD.GET_CURR_DATA;
                txBuf[1] = 2;
                USB.SendReceivePacket(txBuf, 2, rxBuf, ref recLength);
                loadData((int)s.dataPtr);
            }
        }
Beispiel #42
0
        public static List <Question> ReadFileGerman(string excelFile)
        {
            List <Question> qList = new List <Question>();

            // open excel file
            Excel.Application excel     = new Excel.Application();
            Excel.Workbook    workbook  = excel.Workbooks.Open(excelFile);
            Excel._Worksheet  worksheet = workbook.Sheets[1];

            int rowCount = worksheet.UsedRange.Rows.Count;
            int colCount = worksheet.UsedRange.Columns.Count;

            // find data indexes
            Main main = new Main();

            main.ShowOutput("Finding data indexes....", false);
            int questionTextIndex = 0;
            int nameIndex         = 0;
            int categoryIndex     = 0;
            int answerIndex       = 0;

            int[] optionIndexes = new int[] { 0, 0, 0, 0 };

            for (int col = 1; col <= colCount; col++)
            {
                string v = worksheet.Cells[1, col].Value.ToString();

                if (v == "Fragetext")
                {
                    questionTextIndex = col;
                }
                if (v == "Themenname")
                {
                    categoryIndex = col;
                }
                if (v == "Korrekte Antwort(en)")
                {
                    answerIndex = col;
                }
                if (v == "ID")
                {
                    nameIndex = col;
                }

                for (int i = 1; i <= 4; i++)
                {
                    if (v == "Antwort " + i.ToString())
                    {
                        optionIndexes[i - 1] = col;
                    }
                }
            }

            // check data indexes
            if (questionTextIndex > 0 &&
                optionIndexes[0] > 0 &&
                optionIndexes[1] > 0 &&
                optionIndexes[2] > 0 &&
                optionIndexes[3] > 0 &&
                answerIndex > 0 &&
                categoryIndex > 0 &&
                nameIndex > 0)
            {
                main.ShowOutput("OK");
            }
            else
            {
                main.ShowOutput("Error. Indexes not found.");
                workbook.Close();
                excel.Quit();
                return(qList);
            }

            // get question data
            for (int row = 2; row <= rowCount; row++)
            {
                Question q = new Question();
                q.name     = JwString.Clean(worksheet.Cells[row, nameIndex].Value.ToString());
                q.category = JwString.Clean(worksheet.Cells[row, categoryIndex].Value.ToString());
                q.text     = JwString.Clean(worksheet.Cells[row, questionTextIndex].Value.ToString());
                q.type     = "multichoice";

                int answer = Int32.Parse(worksheet.Cells[row, answerIndex].Value);

                for (int i = 1; i <= 4; i++)
                {
                    Option o = new Option();
                    o.text     = JwString.Clean(worksheet.Cells[row, optionIndexes[i - 1]].Value.ToString());
                    o.feedback = "";
                    o.grade    = 0;
                    if (answer == i)
                    {
                        o.grade = 100;
                    }
                    q.options.Add(o);
                }

                qList.Add(q);
            }

            // close excel and return questions
            workbook.Close();
            excel.Quit();

            return(qList);
        }
Beispiel #43
0
 public void SetSheet( bool bWrite=false )
 {
     if( bWrite )
     {
         excelApp.Workbooks.Add();
         workSheet = excelApp.ActiveSheet;
         excelApp.Visible = true;
     }
     else 
     {
         Excel.Workbook xlWorkBook = excelApp.Workbooks.Open( FileName, 0, true, 5, "", "", true, 
                             Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
         workSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
     }
 }
        private void buttonDupCheck_Click(object sender, EventArgs e)
        {
            fixDataSetNullsToEmptyStrings();
            if (xlWorkbook == null)
            {
                xlApp = new Excel.Application();
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Filter = "Excel Documents (*.xlsx)|*.xlsx";
                ofd.ShowDialog();

                xlWorkbook = xlApp.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
            }

            int checkStart = checkBoxDupeCheckAll.Checked ? 0 : Convert.ToInt32(textDupeCheckRowFrom.Text) - 1;
            int checkEnd = checkBoxDupeCheckAll.Checked ? dataCompanyList.Rows.Count - 1 : Convert.ToInt32(textDupeCheckRowTo.Text) - 1;
            fixDataSetNullsToEmptyStrings();

            finalDuplicateCheck(checkStart, checkEnd);
        }
Beispiel #45
0
        private static string OpenExcel(ExcelInfo excelInfo, bool isOpenSheet = true)
        {
            Console.WriteLine("Open File:【{0}】", excelInfo.FilePath);
            if (!File.Exists(excelInfo.FilePath))
            {
                return $"文件【{excelInfo.FilePath}】不存在";
            }

            _objExcel = new Excel.Application { Visible = false, DisplayAlerts = false, AlertBeforeOverwriting = false };

            _objBooks = _objExcel.Workbooks;
            if (excelInfo.FilePath.Equals(String.Empty) || !File.Exists(excelInfo.FilePath))
            {
                _objBook = _objBooks.Add(ObjOpt);
            }
            else
            {
                _objBook = _objBooks.Open(excelInfo.FilePath, ObjOpt, ObjOpt, ObjOpt, ObjOpt,
                                          ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt, ObjOpt);
            }
            if (isOpenSheet)
            {
                _objSheet = OpenSheet(excelInfo);
                if (_objSheet == null)
                {
                    return "没有指定页签";
                }
            }
            return "";
        }
        /// <summary>
        /// loading values from header of excel into comboboxes
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void menu_open_xlsx_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                var excelFile = new OpenFileDialog
                {
                    DefaultExt = ".xlsx",
                    Filter = "(.xlsx)|*.xlsx"
                };
                Nullable<bool> result = excelFile.ShowDialog();
                if (result != true) return;

                RtbProgress.AppendText("Loading Excell file and checking if APIs services are available.\r...\r");
                DoEvents();
                CbxUlice.Items.Clear();
                CbxCp.Items.Clear();
                CbxCo.Items.Clear();
                CbxObec.Items.Clear();
                CbxPsc.Items.Clear();

                _excelPath = excelFile.FileName;

                _excelApp = new Excel.Application();
                _excelWorkBook = _excelApp.Workbooks.Open(_excelPath);
                _excelWorkSheet = _excelWorkBook.Sheets[1];
                // counting rows and columns
                _rowCount = _excelWorkSheet.UsedRange.Rows.Count;
                _columnCount = _excelWorkSheet.UsedRange.Columns.Count;
                // add max rows into interval
                TxtInterval.Text = "[1 - " + _rowCount + "]";
                // Adding selections to comboboxes
                for (int i = 1; i < _excelWorkSheet.UsedRange.Columns.Count + 1; i++)
                {
                    CbxUlice.Items.Add(_excelWorkSheet.Cells[1, i].Value);
                    CbxCp.Items.Add(_excelWorkSheet.Cells[1, i].Value);
                    CbxCo.Items.Add(_excelWorkSheet.Cells[1, i].Value);
                    CbxObec.Items.Add(_excelWorkSheet.Cells[1, i].Value);
                    CbxPsc.Items.Add(_excelWorkSheet.Cells[1, i].Value);
                }
                // adding one null slot to comboboxes
                CbxUlice.Items.Add("");
                CbxCp.Items.Add("");
                CbxCo.Items.Add("");
                CbxObec.Items.Add("");
                CbxPsc.Items.Add("");

                EnableObjects(true);
                MenuOpen.IsEnabled = false;

                RtbProgress.AppendText(_excelPath + " is loaded!\r");
                RtbProgress.AppendText("For new excel file RESTART THE APP please.\r");
                RtbProgress.ScrollToEnd();
                RtbProgress.AppendText("---------------\r");
                CheckIfApisAreOnline();
                RtbProgress.AppendText("---------------\r");
            }
            catch (Exception ex)
            {
                MessageBox.Show("A handled exception just occurred: " + ex.Message, "Exception", MessageBoxButton.OK, MessageBoxImage.Warning);
            }
        }
Beispiel #47
0
        public static InnerResult GetExcelFile(string file)
        {
            //Create COM Objects. Create a COM object for everything that is referenced
            var xlApp     = new Excel.Application();
            var workbooks = xlApp.Workbooks;

            Excel.Workbook xlWorkbook;
            try
            {
                xlWorkbook = workbooks.Open(file, null, ReadOnly: true);
            }
            catch (Exception e)
            {
                MessageBox.Show("Exception Message: " + e.Message);
                if (e.InnerException != null)
                {
                    MessageBox.Show("InnerException Message: " + e.InnerException.Message);
                }
                MessageBox.Show("Exception Trace : " + e.StackTrace);
                return(new InnerResult {
                    Success = false, Message = "Error on open file."
                });
            }

            var sheets = xlWorkbook.Sheets;

            Console.WriteLine(sheets.Count);
            Excel._Worksheet xlWorksheet = null;
            if (sheets.Count == 1)
            {
                xlWorksheet = xlWorkbook.Sheets[1];
            }
            else
            {
                for (int i = 1; i <= sheets.Count; i++)
                {
                    Excel._Worksheet tmp = sheets[i];
                    if (tmp.Name != "PO Upload Download Template-APP")
                    {
                        continue;
                    }
                    xlWorksheet = tmp;
                    break;
                }
            }
            //check if sheet null
            if (xlWorksheet == null && sheets.Count > 0)
            {
                //try first one
                xlWorksheet = xlWorkbook.Sheets[1];

                //forgot quit
                //xlWorkbook.Close(false);
                //xlApp.Quit();
                //return new InnerResult { Success = false, Message = Wording.CantFindRightColumns };
            }

            Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount = xlRange.Rows.Count;
            int colCount = xlRange.Columns.Count;

            Debug.WriteLine($"row={rowCount} col={colCount}");

            #region get skip
            var target = xlWorksheet.UsedRange.Find("Issue Date:");
            if (target == null)
            {
                xlWorkbook.Close(false);
                xlApp.Quit();
                return(new InnerResult {
                    Success = false, Message = Wording.CantFindRightColumns
                });
            }
            var skip = target.Row;
            #endregion

            #region check header
            //Check header
            var heads = xlWorksheet.Range[target, xlWorksheet.Cells[skip, Models.AdidasHeader.Length]];
            var check = CheckHeader(heads, Models.AdidasHeader);
            if (!check.Success)
            {
                xlWorkbook.Close(false);
                xlApp.Quit();
                return(new InnerResult {
                    Success = false, Message = check.Message
                });
            }
            #endregion

            #region toObject
            object[,] data = xlRange.Value2;
            DataTable dt = new DataTable();
            // Create new Column in DataTable
            for (int cCnt = 1; cCnt <= colCount; cCnt++)
            {
                var column = new DataColumn
                {
                    DataType   = Type.GetType("System.String"),
                    ColumnName = cCnt.ToString()
                };
                dt.Columns.Add(column);

                // Create row for Data Table
                for (int rCnt = skip + 1; rCnt <= rowCount; rCnt++)
                {
                    string cellVal;
                    if (Models.DateTimeColume.Contains(cCnt))
                    {
                        try
                        {
                            cellVal = data[rCnt, cCnt] != null
                                ? DateTime.FromOADate(Convert.ToDouble(data[rCnt, cCnt])).ToShortDateString()
                                : null;
                        }
                        catch (Exception)
                        {
                            cellVal = data[rCnt, cCnt]?.ToString();
                        }
                    }
                    else
                    {
                        cellVal = data[rCnt, cCnt]?.ToString();
                    }

                    DataRow row;

                    // Add to the DataTable
                    if (cCnt == 1)
                    {
                        row = dt.NewRow();
                        row[cCnt.ToString()] = cellVal;
                        dt.Rows.Add(row);
                    }
                    else
                    {
                        row = dt.Rows[rCnt - 1 - skip];
                        row[cCnt.ToString()] = cellVal;
                    }
                }
            }
            #endregion

            //close and release

            xlWorkbook.Close(false);
            workbooks.Close();
            //quit and release
            xlApp.Quit();
            Marshal.ReleaseComObject(xlApp);

            Console.WriteLine(@"Load Success");
            return(new InnerResult {
                Success = true, Data = dt
            });
        }
Beispiel #48
0
        private string GetExcelSheetName(string pPath)
        {
            //打开一个Excel应用

            _excelApp = new Excel.Application();
            if (_excelApp == null)
            {
                throw new Exception("打开Excel应用时发生错误!");
            }
            _books = _excelApp.Workbooks;
            //打开一个现有的工作薄
            _book = _books.Add(pPath);
            _sheets = _book.Sheets;
            //选择第一个Sheet页
            _sheet = (Excel._Worksheet)_sheets.get_Item(1);
            string sheetName = _sheet.Name;

            ReleaseCOM(_sheet);
            ReleaseCOM(_sheets);
            ReleaseCOM(_book);
            ReleaseCOM(_books);
            _excelApp.Quit();
            ReleaseCOM(_excelApp);
            return sheetName;
        }
        static void Main(string[] args)
        {
            Excel.Application xlApp       = new Excel.Application();
            Excel.Workbook    xlWorkBook  = xlApp.Workbooks.Open(@"D:\Github\SSENSE_Price_checker\data.xlsx");
            Excel._Worksheet  xlWorksheet = xlWorkBook.Sheets[1];
            Excel.Range       xlRange     = xlWorksheet.UsedRange;
            int             rowCount      = xlRange.Rows.Count;
            int             colCount      = xlRange.Columns.Count;
            List <bontique> buylist       = new List <bontique>();

            for (int i = 1; i <= rowCount; i++)
            {
                bontique tmp = new bontique()
                {
                    row = i, url = xlRange.Cells[i, 1].Text, lowest = Convert.ToDouble(xlRange.Cells[i, 2].Text), highest = Convert.ToDouble(xlRange.Cells[i, 3].Text), last = Convert.ToDouble(xlRange.Cells[i, 4].Text), size = xlRange.Cells[i, 5].Text
                };
                buylist.Add(tmp);
            }
            IWebDriver driver = new FirefoxDriver(@"D:\Github\SSENSE_Price_checker");

            foreach (bontique tmp1 in buylist)
            {
                /*
                 * using (WebClient client = new WebClient()) // WebClient class inherits IDisposable
                 * {
                 *  System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls12;
                 *  string htmlCode = client.DownloadString(tmp1.url);
                 *  int position = htmlCode.IndexOf("class=\"price\">");
                 * }
                 */

                driver.Url = tmp1.url;
                IWebElement pricetag = driver.FindElement(By.XPath("//span[@class='price']"));
                string      ooo      = pricetag.GetAttribute("innerHTML").ToString();
                double      price    = Convert.ToDouble(ooo.Replace(" CAD", "").Replace("$", ""));
                //check if size exist
                if (driver.FindElements(By.XPath("//select[@id='size']")).Count() != 0)
                {
                    IWebElement sizeelement = driver.FindElement(By.XPath("//select[@id='size']"));
                    string      sizeString  = sizeelement.Text;
                    string[]    sizeList    = sizeString.Split(
                        new[] { "\r\n", "\r", "\n" },
                        StringSplitOptions.None
                        );
                    //only xx left, sold out, normal
                    foreach (string tmpsize in sizeList)
                    {
                        if (tmpsize.Contains(tmp1.size))
                        {
                            if (tmpsize.Contains("Out"))
                            {
                                //sold out send notification
                                email(tmp1, 3);
                            }
                            else if (tmpsize.Contains("left"))
                            {
                                //only serveral left buybuybuy!
                                email(tmp1, 2);
                            }
                            else
                            {
                                //do nothing
                            }
                        }
                    }
                }
                if (driver.FindElements(By.XPath("//span[@class='text-no-transform']")).Count() != 0)
                {
                    email(tmp1, 2);
                }
                xlRange.Cells[tmp1.row, 4].Value = price;
                if (price < tmp1.lowest)
                {
                    xlRange.Cells[tmp1.row, 2].Value = price;
                    tmp1.lowest = price;
                    email(tmp1, 1);
                }
                if (price > tmp1.highest)
                {
                    xlRange.Cells[tmp1.row, 3].Value = price;
                }
            }
            driver.Close();
            driver.Quit();
            xlWorkBook.Save();
            xlWorkBook.Close(0);
            xlApp.Quit();
        }
Beispiel #50
0
        public void ConvertToCsv()
        {
            string cellValu = "";
            string tempValu = "";

            Excel.Application xlApp       = new Excel.Application();
            Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(filePath + fileName); // + "VizientImportTest");//"Terumo.xls");
            Excel._Worksheet  xlWorksheet = xlWorkbook.Sheets[1];
            Excel.Range       xlRange     = xlWorksheet.UsedRange;
            rowCount = xlRange.Rows.Count;
            colCount = xlRange.Columns.Count;
            if (trace)
            {
                lm.Write("Convert.ConvertToCsv");
            }
            try
            {
                for (int r = 1; r <= rowCount; r++)
                {
                    for (int c = 1; c <= colCount; c++)
                    {
                        //new line
                        if (c == 1)
                        {
                            Console.Write("\r\n");
                            if (cellValu.Length > 0)
                            {
                                cellValu += Environment.NewLine;
                            }
                        }

                        //write the value to the console
                        if (xlRange.Cells[r, c] != null && xlRange.Cells[r, c].Value2 != null)
                        {
                            tempValu = RemoveCommas(xlRange.Cells[r, c].Value2.ToString());
                            //Console.WriteLine(xlRange.Cells[r, c].Value2.ToString() + "\t");
                            //tempValu = RemoveCommas(xlRange.Cells[r, c].Value2.ToString());
                            //if (tempValu.Length == 0)
                            //    tempValu = "x";
                            //cellValu += tempValu + "|";
                        }
                        else
                        {
                            tempValu = ".";
                        }

                        Console.WriteLine(tempValu + "\t");
                        tempValu  = RemoveCommas(tempValu);
                        cellValu += tempValu + "||";
                    }
                }
                fileName = "ImportFile.csv";
                File.WriteAllText(filePath + fileName, cellValu);
                xlApp.Workbooks.Close();
                GC.Collect();
                //GC.WaitForPendingFinalizers();
            }
            catch (Exception ex)
            {
                lm.Write("Convert.ConvertToCsv: " + ex.Message);
            }
        }
        /**
         * if no log file exists this method creates new spreadsheet
        **/
        private bool CreateWorkBook()
        {
            m_WorkBook = (Excel._Workbook)(m_XLApp.Workbooks.Add(Missing.Value));
            m_Sheet = (Excel._Worksheet) m_WorkBook.ActiveSheet;
            if (m_WorkBook == null || m_Sheet == null)
            {
                return false;
            }

            m_Sheet.Cells[1, 1] = "Date";
            m_columnsMap = new Dictionary<string, int>();
            foreach (KeyValuePair<string, ProjectData> entry in m_currProjectWarnings)
            {
                AddNewProjectColumn(entry.Key);
            }
            //addColumn("Total");

            ApplyStyle();

            return true;
        }
        public static bool DBDataToXls(string sqlstr, string path)
        {
            // Excel object references.
            Excel.Application m_objExcel  = null;
            Excel.Workbooks   m_objBooks  = null;
            Excel._Workbook   m_objBook   = null;
            Excel.Sheets      m_objSheets = null;
            Excel._Worksheet  m_objSheet  = null;
            Excel.Range       m_objRange  = null;
            Excel.Font        m_objFont   = null;
            //Excel.QueryTables m_objQryTables = null;
            //Excel._QueryTable m_objQryTable = null;

            // Frequenty-used variable for optional arguments.
            object m_objOpt = System.Reflection.Missing.Value;

            // Paths used by the sample code for accessing and storing data.
            object m_strSampleFolder = "C:\\ExcelData\\";

            m_objExcel  = new Excel.Application();
            m_objBooks  = (Excel.Workbooks)m_objExcel.Workbooks;
            m_objBook   = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
            m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
            m_objSheet  = (Excel._Worksheet)(m_objSheets.get_Item(1));

            OleDbConnection  conn = new OleDbConnection(DBHelper.OleConnectionString);
            OleDbCommand     cmd  = new OleDbCommand(sqlstr, conn);
            OleDbDataAdapter da   = new OleDbDataAdapter(cmd);
            DataSet          ds   = new DataSet();
            DataTable        dt   = null;
            int fieldCount_;
            int rowsCount_;

            object[] objHeader;
            object[,] objData;

            da.Fill(ds);
            dt          = ds.Tables[0];
            fieldCount_ = dt.Columns.Count;
            rowsCount_  = dt.Rows.Count;

            if (rowsCount_ == 0)
            {
                return(false);
            }

            m_objRange = m_objSheet.get_Range(m_objSheet.Cells[1, 1], m_objSheet.Cells[1, fieldCount_]);
            objHeader  = new string[fieldCount_];
            for (int i = 0; i < fieldCount_; i++)
            {
                objHeader[i] = dt.Columns[i].ColumnName;
            }
            m_objRange.set_Value(m_objOpt, objHeader);
            m_objFont      = m_objRange.Font;
            m_objFont.Bold = true;
            m_objFont.Size = 9;
            m_objFont.Name = "MS Sans Serif";
            //object[rowscount,columnscount]

            objData = new object[rowsCount_, fieldCount_];
            for (int i = 0; i < rowsCount_; i++)
            {
                for (int j = 0; j < fieldCount_; j++)
                {
                    objData[i, j] = dt.Rows[i][j];
                }
            }

            m_objRange = m_objSheet.get_Range("A2", m_objOpt);
            m_objRange = m_objRange.get_Resize(rowsCount_, fieldCount_);
            m_objRange.NumberFormatLocal = "@";
            m_objRange.set_Value(m_objOpt, objData);
            m_objFont      = m_objRange.Font;
            m_objFont.Size = 9;
            m_objFont.Name = "MS Sans Serif";


            m_objBook.SaveAs(path, m_objOpt, m_objOpt,
                             m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
                             m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
            m_objBook.Close(false, m_objOpt, m_objOpt);
            m_objExcel.Quit();
            return(true);
        }
        private void buttonSearch_Click(object sender, EventArgs e)
        {
            /* initiate duplicate checker with excel file */
            if (xlWorkbook == null)
            {
                xlApp = new Excel.Application();
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Filter = "Excel Documents (*.xlsx)|*.xlsx";
                ofd.ShowDialog();

                xlWorkbook = xlApp.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
            }
            /* Save settings */
            SaveSettings();
            checkListCategories.Items.Clear();

            statusBotStrip.Text = "Status: Searching";

            string[] countriesArray = new string[]{"United States", "Singapore", "Australia", "New Zealand"};

            if (searchCity.Lines.Length == 0)
                searchCity.Text = countriesArray[searchCountry.SelectedIndex];

            for (int locationline = 0; locationline < searchCity.Lines.Length; locationline++)
            {
                for (int categoryline = 0; categoryline < searchCategory.Lines.Length; categoryline++)
                {
                    if (searchGoogle.Checked)
                    {
                        /* Conduct first page search */
                        string searchCritera = generateSearchQuery(categoryline, locationline);
                        statusBotStrip.Text = "Status: Searching " + searchCritera + " on Google";
                        searchCritera = searchCritera.Replace(" ", "+");
                        string json = getMapList(searchCritera);
                        var jarray = JsonConvert.DeserializeObject<RootObjectPlaces>(json);

                        if (jarray.status == "OK")
                        {
                            /* Populate the company list */
                            populateGoogleCompanies(jarray, xlWorksheet);

                            /* Conduct next page search until reached end of search */
                            string nextPageToken = jarray.next_page_token;
                            while (nextPageToken != null)
                            {
                                json = getMapList(searchCritera + "&pagetoken=" + nextPageToken);
                                jarray = JsonConvert.DeserializeObject<RootObjectPlaces>(json);
                                /* Populate again */
                                populateGoogleCompanies(jarray, xlWorksheet);
                                nextPageToken = jarray.next_page_token;
                            }
                        }
                        else if (jarray.status == "ZERO_RESULTS")
                            textDebugger.AppendText("Google found no results searching:" + searchCritera + Environment.NewLine);
                        else
                            textDebugger.AppendText("Google Search Error: " + jarray.error_message + " when searching: "
                                + searchCritera + Environment.NewLine);
                    }
                    saveDataSet();  
                    if (searchYelp.Checked)
                    {
                        string searchCriteria = searchCategory.Lines[categoryline];
                        string search_Country = searchCountry.Text;
                        string searchArea = "";
                        searchArea = generateSearchQueryArea(locationline);
                        statusBotStrip.Text = "Status: Searching " + searchArea + " " + searchCriteria + " on Yelp";
                        var yelp = new Yelp(Config.Options);
                        var searchOpt = new SearchOptions();
                        searchOpt.GeneralOptions = new GeneralOptions() { term = searchCriteria, radius_filter = 50000, category_filter = "homeservices", sort = 1 };
                        searchOpt.LocationOptions = new LocationOptions() { location = searchArea };
                        searchOpt.LocaleOptions = new LocaleOptions() { cc = search_Country };
                        var task = yelp.Search(searchOpt);

                        int pages = (int)Math.Ceiling(task.Result.total / 20.0);
                        for (int p = 1; p <= pages; p++)
                        {
                            for (int i = 0; i <= 19; i++)
                            {
                                try
                                {
                                    Business business = task.Result.businesses[i];
                                    string companyName = business.name;
                                    string companyAddress = String.Join(" ", business.location.address);
                                    string companyCity = business.location.city == null ? "" : business.location.city;
                                    string companyState = business.location.state_code == null ? "" : business.location.state_code;
                                    string companyZip = business.location.postal_code == null ? "" : business.location.postal_code;
                                    string companyCountry = business.location.country_code == null ? "" : business.location.country_code;
                                    string companyPhone = business.phone == null ? "" : fixPhoneNumberFormat(business.phone);
                                    if (!checkIfExistInCurrentList(companyName, companyPhone, "No website yet", "No email on 1st check"))
                                    {
                                        string companyWebsite = findYelpCompanyWebsite(task.Result.businesses[i].url);
                                        bool companyHasPicturesOrPersonalWebsite = true;
                                        if (companyWebsite == "No company website")
                                        {
                                            companyHasPicturesOrPersonalWebsite = doesYelpCompanyHavePictures();
                                            companyWebsite = task.Result.businesses[i].url;
                                        }
                                        if (companyHasPicturesOrPersonalWebsite)
                                        {
                                            List<string> companyCategories = new List<string>();
                                            foreach (string[] category in task.Result.businesses[i].categories)
                                                companyCategories.Add(category[0]);
                                            string companyHouzzSearch = generateHouzzSearchLink(replaceCompanyNamePTE(companyName),
                                                searchArea.Remove(0, searchArea.LastIndexOfAny(new char[] { ',', ' ' }) + 1).Replace(" ", ""), true);
                                            AddtoList(companyName, companyAddress, companyCity, companyState, companyZip, companyCountry,
                                                companyPhone, "No email", "", companyWebsite, companyHouzzSearch, companyCategories);
                                        }
                                    }
                                }
                                catch { }
                            }
                            var searchOptions = new SearchOptions();
                            searchOptions.GeneralOptions = new GeneralOptions()
                            {
                                term = searchCriteria,
                                offset = (p * 20),
                                radius_filter = 50000,
                                category_filter = "homeservices",
                                sort = 1 //distance
                            };
                            searchOptions.LocationOptions = new LocationOptions() { location = searchArea };
                            searchOptions.LocaleOptions = new LocaleOptions() { cc = search_Country };
                            task = yelp.Search(searchOptions);
                        }
                    }
                    saveDataSet();
                    if (searchFacebook.Checked)
                    {
                        string searchCriteria = searchCategory.Lines[categoryline];
                        string searchArea = generateSearchQueryArea(locationline);

                        statusBotStrip.Text = "Status: Searching " + searchArea + " " + searchCriteria + " on Facebook";

                        string[] jsonFacebookSearchResults = new string[5];
                        jsonFacebookSearchResults[1] = getFacebookSearchList(searchCriteria, searchArea, "page", false);
                        jsonFacebookSearchResults[2] = getFacebookSearchList(searchCriteria, searchArea, "page", true);
                        jsonFacebookSearchResults[3] = getFacebookSearchList(searchCriteria, searchArea, "place", false);
                        jsonFacebookSearchResults[4] = getFacebookSearchList(searchCriteria, searchArea, "place", true);

                        for (int i = 1; i <= 4; i++)
                        {
                            var jarray = JsonConvert.DeserializeObject<RootObjectFacebook>(jsonFacebookSearchResults[i]);
                            if( jarray.data != null & jarray.data.Count > 0 )
                                populateFacebookCompanies(jarray);
                            else if( jarray.error != null)
                                textDebugger.AppendText("Facebook search error: " + jarray.error.message + Environment.NewLine);
                        }
                    }
                    saveDataSet();
                    if (searchYellowPages.Checked)
                    {
                        /* Conduct first page search */
                        string searchCriteria = searchCategory.Lines[categoryline].Replace(" ", "+");
                        string search_Country = searchCountry.Text;
                        string searchArea = generateSearchQueryArea(locationline);
                        statusBotStrip.Text = "Status: Searching " + searchArea + " " + searchCriteria + " on YellowPages";
                        for (int i = 1; i <= 30; i++) /* 30 pages even if they do not reach 30 */
                        {
                            string json = getYPList(searchCriteria, searchArea, i);
                            var jarray = JsonConvert.DeserializeObject<RootObjectYP>(json);

                            if (jarray.searchResult.metaProperties.errorCode == "")
                            {
                                // Populate the company list 
                                if (jarray.searchResult.metaProperties.listingCount > 0)
                                {
                                    populateYPCompanies(jarray, xlWorksheet);
                                }
                                else
                                {
                                    textDebugger.AppendText("Error on YP page " + i.ToString() + ": No results found searching: " 
                                        + searchArea + " " + searchCriteria + Environment.NewLine);
                                    break;
                                }
                            }
                            else
                                textDebugger.AppendText("Error on YP page " + i.ToString() + ": " + jarray.searchResult.metaProperties.message
                                    + Environment.NewLine);
                        }
                    }
                    saveDataSet();  
                    if( searchFactual.Checked )
                    {
                        string searchCriteria = searchCategory.Lines[categoryline];
                        string search_Country = searchCountry.Text;
                        string search_City = "", search_State = "";
                        Factual factual = new Factual(apiFactualKey, apiFactualSecret);
                        Query q = new Query().SearchExact(searchCriteria);
                        if (search_Country != "US")
                        {
                            q.And(q.Field("country").Equal(search_Country), q.Limit(50));
                            statusBotStrip.Text = "Status: Searching" + search_Country + " " + searchCriteria + " on Factual";
                        }
                        if (search_Country == "US")
                        {
                            q.And(q.Field("country").Equal(search_Country), q.Field("region").Equal(search_State), q.Field("locality").Equal(search_City), q.Limit(50));
                            search_City = searchCity.Lines[locationline];
                            search_State = searchState.Text;
                            statusBotStrip.Text = "Status: Searching " + search_Country + " " + search_City
                                + " " + searchCriteria + " on Factual";
                        }
                            /* if (searchCategoryFilters != "")
                            q.Field("category_labels").Includes(searchCategoryFilters); */
                        int page = 1;
                        try
                        {
                            var json = factual.Fetch("places", q);
                            var jarray = JsonConvert.DeserializeObject<RootObjectFactual>(json);

                            while (jarray.status == "ok" & jarray.response.data.Count > 0)
                            {
                                /* Populate the company list */
                                foreach (Datum item in jarray.response.data)
                                {
                                    string companyName = item.name;
                                    string companyAddress = item.address == null ? "" : item.address;
                                    string companyCity = item.locality == null ? "" : item.locality;
                                    string companyState = item.region == null ? "" : item.region;
                                    string companyCountry = item.country == null ? "" : item.country;
                                    string companyZip = item.postcode == null ? "" : item.postcode;
                                    string companyPhone = item.tel == null ? "" : fixPhoneNumberFormat(item.tel);
                                    string companyEmail = item.email == null ? "no email" : item.email;
                                    string companyWebsite = item.website;
                                    string companyHouzzSearch = generateHouzzSearchLink(replaceCompanyNamePTE(companyName), companyState, true);
                                    List<string> companyCategories = item.category_labels == null ? new List<string>() : item.category_labels[0];
                                    //textDebugger.AppendText(companyName + Environment.NewLine);
                                    AddtoList(companyName, companyAddress, companyCity, companyState, companyZip, companyCountry,
                                        companyPhone, companyEmail, "", companyWebsite, companyHouzzSearch, companyCategories);
                                }
                                page++;
                                int pageOffset = (page - 1) * 50;
                                Query qnew = new Query().SearchExact(searchCriteria);
                                if ( searchCountry.Text == "US" || searchCountry.Text == "AU")
                                {
                                    json = factual.Fetch("places", new Query()
                                        .SearchExact(searchCriteria)
                                        .Field("country").Equal(search_Country)
                                        .Field("region").Equal(search_State)
                                        .Field("locality").Equal(search_City)
                                        .Limit(50)
                                        .Offset(pageOffset));
                                }
                                else if (searchCountry.Text != "US" & searchCountry.Text != "AU")
                                {
                                    json = factual.Fetch("places", new Query()
                                        .SearchExact(searchCriteria)
                                        .Field("country").Equal(search_Country)
                                        .Limit(50)
                                        .Offset(pageOffset));
                                }
                                jarray = JsonConvert.DeserializeObject<RootObjectFactual>(json);
                            }
                        }
                        catch (FactualApiException ex)
                        {
                            textDebugger.AppendText("Factual Requested URL: " + ex.Url + Environment.NewLine);
                            textDebugger.AppendText("Factual Error Status Code: " + ex.StatusCode + Environment.NewLine); ;
                            textDebugger.AppendText("Factual Error Response Message: " + ex.Response + Environment.NewLine); ;
                            if (ex.StatusCode.ToString().Contains("RequestedRangeNotSatisfiable"))
                                textDebugger.AppendText("Factual reached end of results on page " + (page - 1).ToString() + Environment.NewLine);
                        }
                    }
                    saveDataSet();
                    if( searchSensis.Checked )
                    {
                        var searcher = new SsapiSearcher(searchEndPoint, apiSensisKey);
                        // Perform a search and check the response
                        var searchResponse = searcher.SearchFor(searchCategory.Text, searchCity.Text, searchState.Text, 1); /* page 1 */
                        if (searchResponse.code < 200 || searchResponse.code > 299)
                            textDebugger.AppendText("Search failed - Error " + searchResponse.code + ": " + searchResponse.message + Environment.NewLine);
                        else
                        {
                            textDebugger.AppendText("Total results found: " + searchResponse.totalResults.ToString() + Environment.NewLine);
                            textDebugger.AppendText("Total pages: " + searchResponse.totalPages.ToString() + Environment.NewLine);

                            for (int page = 1; page < searchResponse.totalPages; page++)
                            {
                                // Display the results
                                foreach (var result in searchResponse.results)
                                {
                                    string companyWebsite = getSensisContactComponents(result.primaryContacts, "URL");
                                    if (companyWebsite != "")
                                    {
                                        string companyName = result.name;
                                        string companyAddress = "", companyCity = "", companyState = "", companyZip = "";
                                        if (result.primaryAddress != null)
                                        {
                                            companyAddress = result.primaryAddress.addressLine == null ? "" : result.primaryAddress.addressLine;
                                            companyCity = result.primaryAddress.suburb == null ? "" : result.primaryAddress.suburb;
                                            companyState = result.primaryAddress.state == null ? "" : result.primaryAddress.state;
                                            companyZip = result.primaryAddress.postcode == null ? "" : result.primaryAddress.postcode;
                                        }
                                        string companyCountry = "AU";
                                        string companyEmail = getSensisContactComponents(result.primaryContacts, "EMAIL");
                                        string companyPhone = fixPhoneNumberFormat(getSensisContactComponents(result.primaryContacts, "PHONE"));
                                        if (companyPhone == "") companyPhone = fixPhoneNumberFormat(getSensisContactComponents(result.primaryContacts, "MOBILE"));
                                        string companyContactUs = companyEmail.Contains("@") ? "" : getSensisExternalLinksComponents(result.externalLinks, "Contact Us");
                                        string companyHouzzSearch = generateHouzzSearchLink(replaceCompanyNamePTE(companyName), companyState, true);
                                        List<string> companyCategories = result.categories == null ? new List<string>() : new List<string>(new string[] { result.categories[0].name });

                                        //textDebugger.AppendText(companyName + companyAddress + companyCity + companyState + companyZip + companyEmail + companyPhone + companyWebsite + Environment.NewLine);

                                        AddtoList(companyName, companyAddress, companyCity, companyState, companyZip, companyCountry, companyPhone,
                                            companyEmail, companyContactUs, companyWebsite, companyHouzzSearch, companyCategories);
                                    }
                                }
                                searchResponse = searcher.SearchFor(searchCategory.Text, searchCity.Text, searchState.Text, page);
                            }
                        }
                    }
                    /* At the end of each category, we save the data found into an xml file*/
                    saveDataSet();  
                }
            }

            statusBotStrip.Text = "Status: Done";
            /* Save settings */
            SaveSettings();
        }
Beispiel #54
0
        private void exportexcel()
        {
            DateTime dt       = DateTime.Now;
            string   Name     = cbthongke.Text + " (" + dt.Hour + "h" + dt.Minute + "  " + dt.Day + "." + dt.Month + "." + dt.Year + ")";
            string   LinkSave = System.Windows.Forms.Application.StartupPath + @"\..\Reports\";

            // tạo đối tượng
            SaveFileDialog fsave = new SaveFileDialog();

            fsave.InitialDirectory = LinkSave;
            fsave.Filter           = "(tất cả các tệp)|*.*|(các tệp tin)|*.xlsx";
            fsave.FileName         = Name + ".xlsx";
            if (fsave.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
            {
            }
            else
            {
                if (fsave.FileName != "")
                {
                    //khởi tạo excel
                    Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
                    //Khởi tại WorkBook
                    Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
                    //Khởi tạo Worksheet
                    Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
                    worksheet   = workbook.Sheets["Sheet1"];
                    worksheet   = workbook.ActiveSheet;
                    app.Visible = false;

                    DateTime time1 = datetimebd.Value;
                    DateTime time2 = datetimekt.Value;
                    string   dt1   = time1.ToString("dd/MM/yyyy");
                    string   dt2   = time2.ToString("dd/MM/yyyy");

                    //Đổ dữ liệu vào sheet
                    if (cbthongke.Text == "Báo cáo khách hàng")
                    {
                        //worksheet.Range["E4", "E100"].NumberFormat("Text");
                        worksheet.Cells[1, 1] = "BÁO CÁO KHÁCH HÀNG";
                        worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten;
                        worksheet.Cells[3, 1] = "MãKH";
                        worksheet.Cells[3, 2] = "Họ Và Tên";
                        worksheet.Cells[3, 3] = "Năm Sinh";
                        worksheet.Cells[3, 4] = "Giới Tính";
                        worksheet.Cells[3, 5] = "Số Điện Thoại";
                        worksheet.Cells[3, 6] = "Địa Chỉ";
                        worksheet.Cells[3, 7] = "Loại KH";
                        worksheet.Cells[3, 8] = "Tiền Tích Lũy";

                        for (int i = 0; i < dataviewthongke.RowCount; i++)
                        {
                            for (int j = 0; j < 8; j++)
                            {
                                worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value;
                                worksheet.Cells[i + 5, 2]     = "VIP 0 = " + txtvip0.Text + "";
                                worksheet.Cells[i + 6, 2]     = "VIP 1 = " + txtvip1.Text + "";
                                worksheet.Cells[i + 7, 2]     = "VIP 2 = " + txtvip2.Text + "";
                                worksheet.Cells[i + 8, 2]     = "VIP 3 = " + txtvip3.Text + "";
                                worksheet.Cells[i + 9, 2]     = "Tổng KH = " + txttongkh.Text + "";

                                //Kẻ Bảng
                                worksheet.Range["A3", "H" + (i + 4)].Borders.LineStyle = 1;
                                worksheet.Range["A4", "H" + (i + 4)].Borders.LineStyle = 1;
                                //Định dạng các  dòng text;
                                worksheet.Range["A4", "A" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["B4", "B" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["E4", "E" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["F4", "F" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["G4", "G" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["H4", "H" + (i + 4)].HorizontalAlignment = 3;
                            }
                        }
                        //Định dạng trang:
                        worksheet.PageSetup.Orientation  = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
                        worksheet.PageSetup.PaperSize    = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
                        worksheet.PageSetup.LeftMargin   = 0;
                        worksheet.PageSetup.RightMargin  = 0;
                        worksheet.PageSetup.TopMargin    = 0;
                        worksheet.PageSetup.BottomMargin = 0;
                        //Định dạng cột
                        worksheet.Range["A1"].ColumnWidth = 8.57;
                        worksheet.Range["B1"].ColumnWidth = 30;
                        worksheet.Range["C1"].ColumnWidth = 12;
                        worksheet.Range["D1"].ColumnWidth = 11.86;
                        worksheet.Range["E1"].ColumnWidth = 17.57;
                        worksheet.Range["F1"].ColumnWidth = 15;
                        worksheet.Range["G1"].ColumnWidth = 10.57;
                        worksheet.Range["H1"].ColumnWidth = 18;
                        //Định dạng font
                        worksheet.Range["A1", "H100"].Font.Name = "Times New Roman";
                        worksheet.Range["A1", "H1"].Font.Size   = 20;
                        worksheet.Range["A2", "H100"].Font.Size = 14;
                        worksheet.Range["A1", "H1"].MergeCells  = true;
                        worksheet.Range["A1", "H1"].Font.Bold   = true;
                        worksheet.Range["A3", "H3"].Font.Bold   = true;
                        //Định dạng các  dòng text:

                        int    abc = dataviewthongke.RowCount + 2;
                        string def = "H" + abc;

                        worksheet.Range["A1", "H1"].HorizontalAlignment = 3;
                        worksheet.Range["A3", def].HorizontalAlignment  = 3;
                    }

                    if (cbthongke.Text == "Báo cáo doanh thu, lợi nhuận")
                    {
                        worksheet.Cells[1, 1] = "BÁO CÁO DOANH THU, LỢI NHUẬN";
                        worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten;
                        worksheet.Cells[2, 6] = "Ngày: " + dt1 + " - " + dt2;
                        worksheet.Cells[3, 1] = "MãHĐ";
                        worksheet.Cells[3, 2] = "MãTK";
                        worksheet.Cells[3, 3] = "MãKH";
                        worksheet.Cells[3, 4] = "Ngày lập";
                        worksheet.Cells[3, 5] = "Tổng Tiền";
                        worksheet.Cells[3, 6] = "Chiết Khấu";
                        worksheet.Cells[3, 7] = "Thuế";
                        worksheet.Cells[3, 8] = "Tổng Tiền Gốc";


                        for (int i = 0; i < dataviewthongke.RowCount; i++)
                        {
                            for (int j = 0; j < 8; j++)
                            {
                                //worksheet.Cells[i + 8, 1] = i ;
                                worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value;
                                worksheet.Cells[i + 5, 1]     = "";
                                worksheet.Cells[i + 6, 1]     = "Tổng Doanh Thu :      " + txtdoanhthu.Text + " VND";
                                worksheet.Cells[i + 7, 1]     = "Lợi Nhuận :           " + txtloinhuan.Text + " VND";

                                //Kẻ Bảng
                                worksheet.Range["A3", "H" + (i + 4)].Borders.LineStyle = 1;
                                worksheet.Range["A4", "H" + (i + 4)].Borders.LineStyle = 1;
                                //Định dạng các  dòng text;
                                worksheet.Range["A4", "A" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["B4", "B" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["E4", "E" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["F4", "F" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["G4", "G" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["H4", "H" + (i + 4)].HorizontalAlignment = 3;
                            }
                        }
                        //Định dạng trang:
                        worksheet.PageSetup.Orientation  = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
                        worksheet.PageSetup.PaperSize    = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
                        worksheet.PageSetup.LeftMargin   = 0;
                        worksheet.PageSetup.RightMargin  = 0;
                        worksheet.PageSetup.TopMargin    = 0;
                        worksheet.PageSetup.BottomMargin = 0;
                        //Định dạng cột
                        worksheet.Range["A1"].ColumnWidth = 8.57;
                        worksheet.Range["B1"].ColumnWidth = 8.57;
                        worksheet.Range["C1"].ColumnWidth = 12;
                        worksheet.Range["D1"].ColumnWidth = 21.29;
                        worksheet.Range["E1"].ColumnWidth = 17.57;
                        worksheet.Range["F1"].ColumnWidth = 15;
                        worksheet.Range["G1"].ColumnWidth = 10.57;
                        worksheet.Range["H1"].ColumnWidth = 18.86;

                        //Định dạng font
                        worksheet.Range["A1", "H100"].Font.Name = "Times New Roman";
                        worksheet.Range["A1", "H1"].Font.Size   = 20;
                        worksheet.Range["A2", "H100"].Font.Size = 14;
                        worksheet.Range["A1", "H1"].MergeCells  = true;
                        worksheet.Range["A1", "H1"].Font.Bold   = true;
                        worksheet.Range["A3", "H3"].Font.Bold   = true;
                        //Định dạng các  dòng text:

                        int    abc = dataviewthongke.RowCount + 3;
                        string def = "H" + abc;

                        worksheet.Range["A1", "G1"].HorizontalAlignment = 3;
                        worksheet.Range["A3", def].HorizontalAlignment  = 3;
                    }

                    if (cbthongke.Text == "Báo cáo số lượng tồn")
                    {
                        worksheet.Cells[1, 1] = "BÁO CÁO SỐ LƯỢNG TỒN";
                        worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten;
                        worksheet.Cells[3, 1] = "MãG";
                        worksheet.Cells[3, 2] = "TênG";
                        worksheet.Cells[3, 3] = "LoạiG";
                        worksheet.Cells[3, 4] = "TLGao";
                        worksheet.Cells[3, 5] = "Giá Nhập";
                        worksheet.Cells[3, 6] = "Giá Bán";
                        worksheet.Cells[3, 7] = "Xuất Xứ";

                        for (int i = 0; i < dataviewthongke.RowCount; i++)
                        {
                            for (int j = 0; j < 7; j++)
                            {
                                //worksheet.Cells[i + 8, 1] = i ;
                                worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value;
                                worksheet.Cells[i + 5, 1]     = "";
                                //Kẻ Bảng
                                worksheet.Range["A3", "G" + (i + 4)].Borders.LineStyle = 1;
                                worksheet.Range["A4", "G" + (i + 4)].Borders.LineStyle = 1;
                                //Định dạng các  dòng text:
                                worksheet.Range["A3", "A" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["B3", "B" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["E3", "E" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["F3", "F" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["G3", "G" + (i + 4)].HorizontalAlignment = 3;
                            }
                        }
                        //Định dạng trang:
                        worksheet.PageSetup.Orientation  = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
                        worksheet.PageSetup.PaperSize    = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
                        worksheet.PageSetup.LeftMargin   = 0;
                        worksheet.PageSetup.RightMargin  = 0;
                        worksheet.PageSetup.TopMargin    = 0;
                        worksheet.PageSetup.BottomMargin = 0;
                        //Định dạng cột
                        worksheet.Range["A1"].ColumnWidth = 5.55;
                        worksheet.Range["B1"].ColumnWidth = 10;
                        worksheet.Range["C1"].ColumnWidth = 22;
                        worksheet.Range["D1"].ColumnWidth = 15;
                        worksheet.Range["E1"].ColumnWidth = 16;
                        worksheet.Range["F1"].ColumnWidth = 13.35;
                        worksheet.Range["G1"].ColumnWidth = 15;
                        //Định dạng font
                        worksheet.Range["A1", "G100"].Font.Name = "Times New Roman";
                        worksheet.Range["A1", "G1"].Font.Size   = 20;
                        worksheet.Range["A2", "G100"].Font.Size = 14;
                        worksheet.Range["A1", "G1"].MergeCells  = true;
                        worksheet.Range["A1", "G1"].Font.Bold   = true;
                        worksheet.Range["A3", "G3"].Font.Bold   = true;
                        //Định dạng các  dòng text:
                        int    abc = dataviewthongke.RowCount + 3;
                        string def = "G" + abc;

                        worksheet.Range["A1", "G1"].HorizontalAlignment = 3;
                        worksheet.Range["A3", def].HorizontalAlignment  = 3;
                    }
                    if (cbthongke.Text == "Báo cáo gạo sắp hết")
                    {
                        worksheet.Cells[1, 1] = "BÁO CÁO SỐ GẠO SẮP HẾT";
                        worksheet.Cells[2, 1] = "Nhân Viên: " + frmQLCHG.Ten;
                        worksheet.Cells[3, 1] = "MãG";
                        worksheet.Cells[3, 2] = "TênG";
                        worksheet.Cells[3, 3] = "LoạiG";
                        worksheet.Cells[3, 4] = "TLGạo";
                        worksheet.Cells[3, 5] = "Giá Nhập";
                        worksheet.Cells[3, 6] = "Giá Bán";
                        worksheet.Cells[3, 7] = "Xuất Xứ";

                        for (int i = 0; i < dataviewthongke.RowCount; i++)
                        {
                            for (int j = 0; j < 7; j++)
                            {
                                //worksheet.Cells[i + 8, 1] = i ;
                                worksheet.Cells[i + 4, j + 1] = dataviewthongke.Rows[i].Cells[j].Value;
                                worksheet.Cells[i + 5, 1]     = "";
                                //Kẻ Bảng
                                worksheet.Range["A3", "G" + (i + 4)].Borders.LineStyle = 1;
                                worksheet.Range["A4", "G" + (i + 4)].Borders.LineStyle = 1;
                                //Định dạng các  dòng text:
                                worksheet.Range["A3", "A" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["B3", "B" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["E3", "E" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["F3", "F" + (i + 4)].HorizontalAlignment = 3;
                                worksheet.Range["G3", "G" + (i + 4)].HorizontalAlignment = 3;
                            }
                        }
                        //Định dạng trang:
                        worksheet.PageSetup.Orientation  = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
                        worksheet.PageSetup.PaperSize    = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;
                        worksheet.PageSetup.LeftMargin   = 0;
                        worksheet.PageSetup.RightMargin  = 0;
                        worksheet.PageSetup.TopMargin    = 0;
                        worksheet.PageSetup.BottomMargin = 0;
                        //Định dạng cột
                        worksheet.Range["A1"].ColumnWidth = 5.55;
                        worksheet.Range["B1"].ColumnWidth = 10;
                        worksheet.Range["C1"].ColumnWidth = 22;
                        worksheet.Range["D1"].ColumnWidth = 15;
                        worksheet.Range["E1"].ColumnWidth = 16;
                        worksheet.Range["F1"].ColumnWidth = 13.35;
                        worksheet.Range["G1"].ColumnWidth = 15;
                        //Định dạng font
                        worksheet.Range["A1", "G100"].Font.Name = "Times New Roman";
                        worksheet.Range["A1", "G1"].Font.Size   = 20;
                        worksheet.Range["A2", "G100"].Font.Size = 14;
                        worksheet.Range["A1", "G1"].MergeCells  = true;
                        worksheet.Range["A1", "G1"].Font.Bold   = true;
                        worksheet.Range["A3", "G3"].Font.Bold   = true;
                        //Định dạng các  dòng text:
                        int    abc = dataviewthongke.RowCount + 3;
                        string def = "G" + abc;

                        worksheet.Range["A1", "G1"].HorizontalAlignment = 3;
                        worksheet.Range["A3", def].HorizontalAlignment  = 3;
                    }
                    worksheet.SaveAs(fsave.FileName);
                    String Message = "Xuất " + cbthongke.Text + " thành công";
                    MessageBox.Show(Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }
        private void button10_Click(object sender, EventArgs e)
        {
            if (xlWorkbook == null)
            {
                xlApp = new Excel.Application();
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Filter = "Excel Documents (*.xlsx)|*.xlsx";
                ofd.ShowDialog();

                xlWorkbook = xlApp.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[1];
            }
            finalDuplicateCheck(Convert.ToInt32(textDupeCheckRowFrom.Text)-1, Convert.ToInt32(textDupeCheckRowFrom.Text)-1);
        }
Beispiel #56
0
 /// <summary>
 /// 设置连续区域的字体名称
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="objStartCell">开始单元格</param>
 /// <param name="objEndCell">结束单元格</param>
 /// <param name="fontname">字体名称 隶书、仿宋_GB2312等</param>
 public void SetFontName(Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string fontname)
 {
     CurSheet.get_Range(objStartCell, objEndCell).Font.Name = fontname;
 }
Beispiel #57
0
 /// <summary>
 /// 创建一个Excel程序实例
 /// </summary>
 private void CreateExcelRef()
 {
     _excelApp = new Excel.Application();
     _books = (Excel.Workbooks)_excelApp.Workbooks;
     _book = (Excel._Workbook)(_books.Add(_optionalValue));
     _sheets = (Excel.Sheets)_book.Worksheets;
     _sheet = (Excel._Worksheet)(_sheets.get_Item(1));
 }
Beispiel #58
0
 /// <summary>
 /// 设置连续区域的字体为黑体
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="objStartCell">开始单元格</param>
 /// <param name="objEndCell">结束单元格</param>
 public void SetBold(Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
 {
     CurSheet.get_Range(objStartCell, objEndCell).Font.Bold = true;
 }
Beispiel #59
0
        //Method to initialize opening Excel
        private void ExcelInit(String path, out List<Product> idinfo)
        {
            xlApp = new Excel.Application();
            string dsSheet = "datasheet";
            string idinfoSheet = "idinfo";
            idinfo = null;
            if (System.IO.File.Exists(path)) {
                // then go and load this into excel
                xlWorkBook = xlApp.Workbooks.Open(path,
                0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t",
                false, false, 0, true, 1, 0);

                //read info datasheet first to generate product specs list
                info_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(idinfoSheet);
                idinfo = ReadIdInfoIntoDataTable(info_xlWorkSheet);
                //read datasheet and load the data into product specs list
                //ds_xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(dsSheet);
                //ReadDatasheetIntoDataTable(ds_xlWorkSheet);
            } else {
                MessageBox.Show("Unable to open excel file!");
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                xlApp = null;
                System.Windows.Forms.Application.Exit();
            }
        }
Beispiel #60
0
 /// <summary>
 /// 设置连续区域水平居右
 /// </summary>
 /// <param name="CurSheet">Worksheet</param>
 /// <param name="objStartCell">开始单元格</param>
 /// <param name="objEndCell">结束单元格</param>
 public void SetHAlignRight(Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
 {
     CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
 }