Example #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);
 }
Example #2
0
        /// <summary>
        /// Creates a spreadsheet in the give xls filename. 
        /// </summary>
        /// <param name="filename">The complete filename with the absolute path.</param>
        /// <param name="sheetname">The name of the sheet e.g. Hidden</param>
        /// <returns>True if succeeded, false if failed.</returns>
        public static bool createWorksheet(String filename, String sheetname, bool needsToBeHidden = false)
        {
            successStatus = false;
            try
            {
                successStatus = openXlApp();
                CurrentSpreadSheet css = CurrentSpreadSheet.Instance;
                //checking if the call is being made for the currently open worbook. this is less expensive.
                if ((css.CurrentWorkBook != null) && (css.CurrentWorkBook.FullName == filename))
                {
                    xlSheets = css.CurrentWorkBook.Sheets as Excel.Sheets;
                }
                else
                {
                    xlWorkbook = openXlWorkBook(filename);
                    xlSheets = xlWorkbook.Sheets as Excel.Sheets;
                }

                xlSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[xlSheets.Count + 1]);
                xlSheet.Name = sheetname;

                if (needsToBeHidden) xlSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden;

                xlWorkbook.Save();
                successStatus = quitXlApp();
            }
            finally
            {
                garbageCollect();
            }

            return successStatus;
        }
Example #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
                }
            }
        }
Example #4
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);
 }
Example #5
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);
 }
        //for writing
        public ExcelOperator()
        {
            ExcelApp = new Excel.Application();
              workbooks = ExcelApp.Workbooks;
              workbook = workbooks.Add();
              sheets = workbook.Sheets;
              worksheet = sheets[1];
              worksheet.Select(Type.Missing);

              directoryPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
              fileName = "shift.xlsx";
        }
 public Workbook( Excel._Workbook oWB)
 {
     this.oWB = oWB;
     this.workbookSheets = oWB.Sheets;
     foreach (Excel._Worksheet openedWorkSheet in workbookSheets)
     {
         sheetCounter += 1;
         Sheet openedSheet = new Sheet(openedWorkSheet);
         setIndex(openedSheet);
         Sheets.Add(openedSheet);
         sheetDict[openedSheet] = openedWorkSheet;
     }
     this.ActiveSheet = Sheets[0];
 }
Example #8
0
 public void OpenDocument(string name)
 {
     try
     {
         xlApp = new Excel.Application();
         xlWorkbook = xlApp.Workbooks.Open(name);
         this.filename = name;
         xlWorksheets = xlWorkbook.Sheets;
         xlWorksheet = xlWorksheets[1] as Excel.Worksheet; // set first worksheet by default
     }
     catch (Exception e)
     {
         LogError(e.Message, "OpenDocument");
         Console.WriteLine(e.Message);
     }
 }
Example #9
0
        internal Workbook(
            ExcelApplication excelApplication,
            Excel.Workbook _workbook,
            string filePath,
            ObjectDisposedCallback disposeCallback)
        {
            ExcelApplication = excelApplication;
            this._workbook = _workbook;
            this.FilePath = filePath;
            worksheets = new List<Worksheet>();
            _sheets = _workbook.Worksheets;
            worksheetDisposeCallback = sender => worksheets.Remove((Worksheet)sender);
            this.disposeCallback = disposeCallback;
            for (var i = 1; i <= _sheets.Count; i++)
            {
                worksheets.Add(new Worksheet(ExcelApplication, this, _sheets[i], worksheetDisposeCallback, false));
            }

            worksheets[0].Activate();
        }
Example #10
0
        private unsafe void CreateExcelItem()
        {
            // Создаем документ с 16 страницами
            excelapp = new Excel.Application();
            //excelapp.Visible=true;

            excelapp.SheetsInNewWorkbook=1;
            Excel.Workbook excelappworkbook = excelapp.Workbooks.Add(Type.Missing);

            String[] SheetsName = new String[16] { "Sword", "Axe", "MaceScepter", "Spear", "BowCrossbow", "Staff", "Shield", "Helm", "Armor", "Pants", "Gloves", "Boots", "Accessories", "Misc1", "Misc2", "Scrolls" };

            excelsheets = excelappworkbook.Worksheets;

            // определяем имена страницам и переходим на страницу
            excelworksheet = (Excel.Worksheet)excelsheets.get_Item(0 + 1);
            excelworksheet.Name = SheetsName[0];
            excelworksheet.Activate();
            excelworksheet.Application.ActiveWindow.SplitColumn = 3;
            excelworksheet.Application.ActiveWindow.SplitRow = 2;
            excelworksheet.Application.ActiveWindow.FreezePanes = true;

            // заполнение Index (0.1.2.3...)
            excelcells = excelworksheet.get_Range("B3", Type.Missing);
            excelcells.Value2 = 0;
            excelcells = excelworksheet.get_Range("B4", Type.Missing);
            excelcells.Value2 = 1;
            excelcells = excelworksheet.get_Range("B3", "B4");
            Excel.Range dest = excelworksheet.get_Range("B3", "B514");
            excelcells.AutoFill(dest, Excel.XlAutoFillType.xlFillDefault);

            // сворачиваем для увеличения скорости
            excelworksheet.Application.WindowState = Excel.XlWindowState.xlMinimized;
            excelworksheet.Application.Visible = false;

            // оцентровываем первую строку
            excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing];
            excelcells.HorizontalAlignment = Excel.Constants.xlCenter;

            // зажирняем и оцентровываем вторую строку
            excelcells = (Excel.Range)excelworksheet.Rows["2", Type.Missing];
            excelcells.Font.Bold = true;
            excelcells.HorizontalAlignment = Excel.Constants.xlCenter;

            // устанавливаем размер колонок
            excelcells = (Excel.Range)excelworksheet.Columns["A", Type.Missing];
            excelcells.ColumnWidth = 5;
            excelcells = (Excel.Range)excelworksheet.Columns["B", Type.Missing];
            excelcells.ColumnWidth = 5;
            excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing];
            excelcells.ColumnWidth = 30;
            for (int j = 0; j < MyItemColumns.Length; j++)
            {
                excelcells = (Excel.Range)excelworksheet.Columns[ColumnTempName[j + 3], Type.Missing];
                excelcells.ColumnWidth = MyItemColumns[j].Width;
            }

            // заполняем первую строку границами как называется не помню
            excelcells = excelworksheet.get_Range("C1", Type.Missing);
            excelcells.Value2 = "Char[30]";
            excelcells.Activate();
            for (int j = 0; j < MyItemColumns.Length; j++)
            {
                excelcells = excelapp.ActiveCell.get_Offset(0, 1);
                excelcells.Value2 = MyItemColumns[j].ColSize;
                excelcells.Activate();
            }

            // заполняем вторую строку названиями
            excelcells = excelworksheet.get_Range("A2", Type.Missing);
            excelcells.Value2 = "Type";
            excelcells = excelworksheet.get_Range("B2", Type.Missing);
            excelcells.Value2 = "Index";
            excelcells = excelworksheet.get_Range("C2", Type.Missing);
            excelcells.Value2 = "Item Name";
            excelcells.Activate();
            for (int j = 0; j < MyItemColumns.Length; j++)
            {
                excelcells = excelapp.ActiveCell.get_Offset(0, 1);
                excelcells.Value2 = MyItemColumns[j].Name;
                excelcells.Activate();
            }

            // обнуляем все ячейки кроме названия
            excelcells = excelworksheet.get_Range("D3", "AR514");
            excelcells.Value2 = 0;

            // number format 12 232 232 для zen
            excelcells = excelworksheet.get_Range("AB3", "AB514");
            excelcells.NumberFormat = "# ##0";

            // копируем листы
            for (int i = 0; i < 15; i++)
            {
                excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
                excelworksheet.Copy(Type.Missing, excelworksheet);
                excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 2);
                excelworksheet.Name = SheetsName[i + 1];
            }

            // заполняем ячейки
            for (int i = 0; i < 16; i++)
            {
                // выделяем нужный лист
                excelworksheet = (Excel.Worksheet)excelsheets.get_Item(i + 1);
                excelworksheet.Activate();

                excelcells = excelworksheet.get_Range("A3", "A514");
                excelcells.Value2 = i;

                progressBar3.Value = i;
                // поехали по строкам
                for (int j = 0; j < 512; j++)
                {
                    progressBar2.Value = j;
                    if (Items[i,j].ItemName[0] != '\0')
                    {
                        excelcells = (Excel.Range)excelworksheet.Cells[j + 3, 3];
                        excelcells.Value2 = Items[i, j].ItemName;
                        excelcells.Select();
                    }
                    fixed (Int64* buff = Items[i, j].Numbers)
                    {
                        for (int k = 0; k < MyItemColumns.Length; k++)
                        {
                            if (buff != null && *(buff + k) != 0.0f)
                            {
                                excelcells = (Excel.Range)excelworksheet.Cells[j + 3, k + 4];
                                excelcells.Value2 = *(buff + k);
                            }
                        }
                    }
                }
            }

            // показываем готовый файл
            excelapp.Visible = true;
            progressBar2.Value = 0;
            progressBar3.Value = 0;
            MessageBox.Show("All Done!");
        }
Example #11
0
 private void PrepareRecord()
 {
     objRecordExcel = new Excel.Application();
     objRecordBooks = objRecordExcel.Workbooks;
     if (File.Exists(txtStorage.Text))
         objRecordBook = objRecordBooks.Open(txtStorage.Text);
     else
         objRecordBook = objRecordBooks.Add(true);
     objRecordSheets = objRecordBook.Sheets;
     objRecordSheet1 = objRecordSheets[1];  // 收件箱
     objRecordSheet2 = objRecordSheets[1];  // 已发短信
     bool bSheet1Exists = false, bSheet2Exists = false;
     foreach (Excel.Worksheet objSheet in objRecordSheets)
     {
         if (objSheet.Name == "收件箱")
         {
             objRecordSheet1 = objSheet;
             bSheet1Exists = true;
         }
         else if (objSheet.Name == "已发短信")
         {
             objRecordSheet2 = objSheet;
             bSheet2Exists = true;
         }
     }
     if (!bSheet1Exists)
     {
         objRecordSheet1 = objRecordSheets.Add();
         objRecordSheet1.Name = "收件箱";
         objRecordSheet1.Cells[1, 1] = "发件人";
         objRecordSheet1.Cells[1, 2] = "短信内容";
         objRecordSheet1.Cells[1, 3] = "发送时间";
     }
     if (!bSheet2Exists)
     {
         objRecordSheet2 = objRecordSheets.Add();
         objRecordSheet2.Name = "已发短信";
         objRecordSheet2.Cells[1, 1] = "收件人";
         objRecordSheet2.Cells[1, 2] = "短信内容";
         objRecordSheet2.Cells[1, 3] = "发送时间";
         objRecordSheet2.Cells[1, 4] = "结果";
         objRecordSheet2.Cells[1, 5] = "余额";
     }
 }
Example #12
0
        public Form1()
        {
            InitializeComponent();

            double x, y, d, dx, dy, x0, y0;
            int y_step;

            excelapp = new Excel.Application();
            excelapp.Visible = true;
            excelapp.SheetsInNewWorkbook = 3;
            excelapp.Workbooks.Add(Type.Missing);
            excelapp.DisplayAlerts = true;
            excelappworkbooks = excelapp.Workbooks;
            excelappworkbook = excelappworkbooks[1];
            excelsheets = excelappworkbook.Worksheets;
            excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);

            excelcells_a1 = excelworksheet.get_Range("A1", Type.Missing);

            excelcells = excelcells_a1.get_Offset(0, 0);

            ///////////////////////////
            //////  Источники   ///////
            ///////////////////////////

            double sourceRadius = 0.2;

            sources[0].id = 0;
            sources[0].x = 0.27;
            sources[0].y = 2.00;
            sources[0].radius = sourceRadius;

            sources[1].id = 1;
            sources[1].x = 1.10;
            sources[1].y = 2.81;
            sources[1].radius = sourceRadius;

            sources[2].id = 2;
            sources[2].x = 2.22;
            sources[2].y = 2.64;
            sources[2].radius = sourceRadius;

            sources[3].id = 3;
            sources[3].x = 2.80;
            sources[3].y = 1.61;
            sources[3].radius = sourceRadius;

            sources[4].id = 4;
            sources[4].x = 2.39;
            sources[4].y = 0.51;
            sources[4].radius = sourceRadius;

            sources[5].id = 5;
            sources[5].x = 1.30;
            sources[5].y = 0.13;
            sources[5].radius = sourceRadius;

            sources[6].id = 6;
            sources[6].x = 0.36;
            sources[6].y = 0.81;
            sources[6].radius = sourceRadius;

            sources[7].id = 7;
            sources[7].x = 1.49;
            sources[7].y = 1.50;
            sources[7].radius = sourceRadius;

            ///////////////////////////
            //////  Приёмники   ///////
            ///////////////////////////
            /*

                        //  Вариант один к одному для ВОСЬМИ приёмников

                        double receiverRadius = 0.23;
                        double receiverInnerRadius = 0.13;
                        receiversCount = 8;

                        receivers[0].id = 0;
                        receivers[0].x = 0.23;
                        receivers[0].y = 1.97;
                        receivers[0].radius = receiverRadius;
                        receivers[0].innerRadius = receiverInnerRadius;

                        receivers[1].id = 1;
                        receivers[1].x = 1.08;
                        receivers[1].y = 2.76;
                        receivers[1].radius = receiverRadius;
                        receivers[1].innerRadius = receiverInnerRadius;

                        receivers[2].id = 2;
                        receivers[2].x = 2.23;
                        receivers[2].y = 2.62;
                        receivers[2].radius = receiverRadius;
                        receivers[2].innerRadius = receiverInnerRadius;

                        receivers[3].id = 3;
                        receivers[3].x = 2.82;
                        receivers[3].y = 1.63;
                        receivers[3].radius = receiverRadius;
                        receivers[3].innerRadius = receiverInnerRadius;

                        receivers[4].id = 4;
                        receivers[4].x = 2.44;
                        receivers[4].y = 0.54;
                        receivers[4].radius = receiverRadius;
                        receivers[4].innerRadius = receiverInnerRadius;

                        receivers[5].id = 5;
                        receivers[5].x = 1.31;
                        receivers[5].y = 0.2;
                        receivers[5].radius = receiverRadius;
                        receivers[5].innerRadius = receiverInnerRadius;

                        receivers[6].id = 6;
                        receivers[6].x = 0.35;
                        receivers[6].y = 0.81;
                        receivers[6].radius = receiverRadius;
                        receivers[6].innerRadius = receiverInnerRadius;

                        receivers[7].id = 7;
                        receivers[7].x = 1.49;
                        receivers[7].y = 1.50;
                        receivers[7].radius = receiverRadius;
                        receivers[7].innerRadius = receiverInnerRadius;
            */

            //  Полтное гексагональное покрытие приёмниками

            x0 = 1.5;                //  Центр шины - x
            y0 = 1.5;                //  Центр шины - y
            /*
            dx = 0.46;             //  Шаг по x для гексагональной разметки
            dy = 0.42;             //  Шаг по y для гексагональной разметки
            double receiverRadius = 0.23;
            double receiverInnerRadius = 0.0;
            */
            dx = 0.46;             //  Шаг по x для гексагональной разметки
            dy = 0.42;             //  Шаг по y для гексагональной разметки
            double receiverRadius = 0.20;
            double receiverInnerRadius = 0.10;

            y_step = 0;
            y = 0;
            receiversCount = 0;
            while (y < busRadius * 2)
            {

                //  Для гексагональной разметки координаты по x на каждом уровне смещаем по-разному
                if (y_step % 2 == 0)
                {
                    x = 0;
                }
                else
                {
                    x = dx / 2;
                }

                while (x < busRadius * 2)
                {
                    d = Math.Sqrt((x0 - x) * (x0 - x) + (y0 - y) * (y0 - y));
                    if (d < busRadius)
                    {
                        //  Канал в шине (точка в круге)

                        receivers[receiversCount].x = x;
                        receivers[receiversCount].y = y;
                        receivers[receiversCount].radius = receiverRadius;
                        receivers[receiversCount].innerRadius = receiverInnerRadius;

                        receiversCount++;
                    }

                    x = x + dx;
                }

                y = y + dy;
                y_step++;
            }

            ////////////////////////
            //////  Каналы   ///////
            ////////////////////////

            dx = 0.0429;             //  Шаг по x для гексагональной разметки
            dy = 0.0357;             //  Шаг по y для гексагональной разметки
            x0 = 1.5;                //  Центр шины - x
            y0 = 1.5;                //  Центр шины - y
            double channelRaduis = 0.021;    //  Радиус одного канала

            y_step = 0;
            y = 0;
            while (y < busRadius * 2)
            {

                //  Для гексагональной разметки координаты по x на каждом уровне смещаем по-разному
                if (y_step % 2 == 0)
                {
                    x = 0;
                }
                else
                {
                    x = dx / 2;
                }

                while (x < busRadius * 2)
                {
                    d = Math.Sqrt((x0 - x) * (x0 - x) + (y0 - y) * (y0 - y));
                    if (d < busRadius)
                    {
                        //  Канал в шине (точка в круге)

                        channels[channelsCount].x = x;
                        channels[channelsCount].y = y;
                        channels[channelsCount].radius = channelRaduis;
                        channels[channelsCount].isOk = true;

                        channelsCount++;
                    }

                    x = x + dx;
                }

                y = y + dy;
                y_step++;
            }
        }
Example #13
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);
        }
Example #14
0
            public MockWorkbook()
            {
                // worksheet indices; watch out! the second index here is the NUMBER of elements, NOT the max value!
                var e = Enumerable.Range(1, 10);

                // new Excel instance
                app = new Excel.Application();

                // create new workbook
                wb = app.Workbooks.Add();

                // get a reference to the worksheet array
                // By default, workbooks have three blank worksheets.
                ws = wb.Worksheets;

                // add some worksheets
                foreach (int i in e)
                {
                    ws.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                }
            }
 public void Setup()
 {
     resourcePath = @"../../../../test.xlsx";
     app = new Excel.Application();
     var excelSheetPath = Path.GetFullPath(resourcePath);
     wbs = app.Workbooks;
     wb = wbs.Open(excelSheetPath);
     wss = wb.Worksheets;
     ws = (Excel.Worksheet)wss["Tasks"];
     excelController = new ExcelController(resourcePath);
 }
Example #16
0
        /// <summary>
        /// 持续打开Excel文件
        /// </summary>
        /// <returns></returns>
        public bool OpenFileContinuously()
        {
            try
            {
                app = new Microsoft.Office.Interop.Excel.Application();
                workbook = app.Workbooks.Open(FileName, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
                sheets = workbook.Worksheets;
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(SheetNumber);//读取一张表
                iRowCount = worksheet.UsedRange.Rows.Count;
                iColumnCount = worksheet.UsedRange.Columns.Count;
                booFileOpenState = true;

                return true;
            }
            catch (Exception ex)
            {
                strErrorString = ex.Message;
                return false;
            }
        }
        protected virtual void Dispose(bool disposing)
        {
            workbook.Close(false);
              ExcelApp.Quit();
              Marshal.ReleaseComObject(worksheet);
              Marshal.ReleaseComObject(sheets);
              Marshal.ReleaseComObject(workbook);
              Marshal.ReleaseComObject(workbooks);
              Marshal.ReleaseComObject(ExcelApp);

              range = null;
              worksheet = null;
              sheets = null;
              workbook = null;
              workbooks = null;
              ExcelApp = null;
        }
Example #18
0
		private void createReportButton_Click(object sender, EventArgs e)
		{

			if (Directory.Exists(Settings.Default.FilesDir))
			{
				createReportButton.Enabled = false;
				// Получаем список файлов в каталоге
				string[] filesArray = Directory.GetFiles(Settings.Default.FilesDir);

				foreach (string fileName in filesArray)
				{
					// Проверяем расширение файла
					FileInfo infoFile = new FileInfo(fileName);
					// Совпадает с датами формата: 1900-01-01 2007/08/13 1900.01.01 1900 01 01 1900-01.01
					//string fileNamePattern = @"(19|20)\d\d([- /.])(0[1-9]|1[012])([- /.])(0[1-9]|[12][0-9]|3[01])";
					string month;
					if (dateTimePicker1.Value.Month <= 9) { month = "0" + dateTimePicker1.Value.Month; }
					else { month = dateTimePicker1.Value.Month.ToString(); };
					string fileNamePattern;
					if (checkBox1.Checked)
					{
						fileNamePattern = "(" + dateTimePicker1.Value.Year + @")([- /.])(" 
							+ month	+ @")([- /.])(0[1-9]|[12][0-9]|3[01])";
					}
					else
					{
						fileNamePattern = ".";
					}
						
					// Обнуляем счётчик обработаных файлов
					filesCount = 0;
					if ((File.Exists(fileName)) && Regex.IsMatch(fileName, fileNamePattern) && (infoFile.Extension == ".dpm"))
					{
						filesCount++;
						// Создаём потоки чтения файлов.
						FileStream fileStream = new FileStream(
							fileName,
							FileMode.Open,
							FileAccess.Read
							);
						StreamReader streamReader = new StreamReader(
							fileStream,
							Encoding.GetEncoding(1251)
							);

						toolStripStatusLabel2.Text = "Обработка файла: " + infoFile.Name;
						// Считываем построчно файл до самого конца
						while (!streamReader.EndOfStream)
						{
							// Разделяем полученную строку
							// Структтура массива:
							//  info[0] - знак выхождения или выхода трека (">" или "<")
							//  info[1] - дата и время выхода пести
							//  info[2] - идентификатор трека при составлении плейлиста
							//  info[3] - псевдоним, который определяет положение треков на диске
							//  info[4] - имя файла трека относительно псевдонима
							//  info[5] - длительность трека
							string[] info = streamReader.ReadLine().Split('\t');

							// Считаем только вхождение файла
							// Т.е. проверяем на символ ">" и проверяем только определённый псевдоним
							string[] aliases = Properties.Settings.Default.Aliases.Split(';');
							foreach (string alias in aliases)
							{
								if ((info[0].Trim() == ">") && (info[3].Trim() == alias.Trim()))
								{
									if (!tableCount.Contains(info[4]))
									{
										// Если записи нет, создаём новую
										tableCount.Add(info[4], 1);
										tableDuration.Add(info[4], info[5]);
									}
									else
									{
										// Если запись есть, увеличиваем счётчик
										tableCount[info[4]] = (int)tableCount[info[4]] + 1;
									}
								}
							}
						}
						// Закрываем потоки чтения файлов.
						streamReader.Close();
						fileStream.Close();
					}	// End If File Exist
				} // End Foreach FileNames

				// Меняем статус
				toolStripStatusLabel2.Text = "Обработано файлов: " + filesCount;

				// Создаём нумератор для управление Хэш массивом
				IDictionaryEnumerator tableCountEnum = tableCount.GetEnumerator();

				toolStripStatusLabel2.Text = "Создание файла отчёта.";
				#region СОЗДАЁМ НОВЫЙ ДОКУМЕНТ EXCEL
				// Открываем приложение
				excelapp = new Excel.Application();
				// Прячем окно программы
				excelapp.Visible = false;
				// Создаём документ с одной книгой
				excelapp.SheetsInNewWorkbook = 1;
				// Добавляем книгу
				excelapp.Workbooks.Add(Type.Missing);

				//Запрашивать сохранение
				//excelapp.DisplayAlerts = true;
				excelapp.DisplayAlerts = false;
				//Получаем набор ссылок на объекты Workbook (на созданные книги)
				excelappworkbooks = excelapp.Workbooks;
				//Получаем ссылку на книгу 1 - нумерация от 1
				excelappworkbook = excelappworkbooks[1];
				#endregion

				toolStripStatusLabel2.Text = "Генерация отчёта.";
				#region РАБОТА С ЯЧЕЙКАМИ
				excelsheets = excelappworkbook.Worksheets;
				// Получаем ссылку на лист 1
				excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);

				#region Примеры: Выделение группы ячеек
				//excelcells = excelworksheet.get_Range("A1", "С10");
				// Тоже
				//excelcells = excelworksheet.get_Range("A1", "С10").Cells;
				//excelcells = excelworksheet.get_Range("A1", "С10").Rows;
				//excelcells = excelworksheet.get_Range("A1", "С10").Cells;
				// Одну ячейку
				//excelcells = excelworksheet.get_Range("A1", "A1");
				//excelcells = excelworksheet.get_Range("A1", Type.Missing);
				// Десять строк с 1 по 10ю
				//excelcells = (Excel.Range)excelworksheet.Rows["1:10", Type.Missing];
				// Три столбца
				//excelcells = (Excel.Range)excelworksheet.Columns["A:C", Type.Missing];
				// Одну строку
				//excelcells = (Excel.Range)excelworksheet.Rows["1", Type.Missing];
				// Один столбец
				//excelcells = (Excel.Range)excelworksheet.Columns["C", Type.Missing]; 
				#endregion

				// Выбираем первую ячейку
				excelcells = excelworksheet.get_Range("A1", Type.Missing).Cells;
				excelcells.Value2 = "Испольнитель";
				excelcells = excelworksheet.get_Range("B1", Type.Missing).Cells;
				excelcells.Value2 = "Трек";
				excelcells = excelworksheet.get_Range("C1", Type.Missing).Cells;
				excelcells.Value2 = "Длительность";
				excelcells = excelworksheet.get_Range("D1", Type.Missing).Cells;
				excelcells.Value2 = "Количество";

				excelcells = excelworksheet.get_Range("A1", "D1").Cells;
				excelcells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
				#endregion

				#region ЗАПИСЫВАЕМ ДАННЫЕ В ФАЙЛ
				// Сбрасываем счётчик Хэша в начало
				tableCountEnum.Reset();

				// В цикле перебираем все записи Хэша
				for (int i = 0; i < tableCount.Count; i++)
				{
					// Переход к следующей записи
					tableCountEnum.MoveNext();
					// Выделяем имя файла из пути к файлу
					string trackName = Regex.Match(tableCountEnum.Key.ToString(), @"[^\\]*$").Value;
					// Отрезаем расширение файла
					trackName = trackName.Substring(0, trackName.Length - 4);
					// Заменяем тире с пробелами на знак %
					trackName = trackName.Replace("-", "%");
					// Разделяем название группы и название трека
					string[] fullName = trackName.Split('%');

					// Работаем с документом
					// Начинаем запись исполнителей со второй строки
					int m = i + 2;
					excelcells = (Excel.Range)excelworksheet.Cells[m, 1];
					excelcells.Value2 = fullName[0].Trim();
					excelcells = (Excel.Range)excelworksheet.Cells[m, 2];
					if (fullName.Length > 1) {excelcells.Value2 = fullName[1].Trim(); }
					excelcells = (Excel.Range)excelworksheet.Cells[m, 3];
					excelcells.Value2 = tableDuration[tableCountEnum.Key];
					excelcells = (Excel.Range)excelworksheet.Cells[m, 4];
					excelcells.Value2 = tableCountEnum.Value.ToString();
					
				}
				#endregion

				toolStripStatusLabel2.Text = "Сохранение документа.";
				#region ЗАВЕРШАЕМ РАБОТУ С EXCEL
				//Ссылку можно получить и так, но тогда надо знать имена книг,
				//причем, после сохранения - знать расширение файла
				//excelappworkbook=excelappworkbooks["Книга 1"];
				//Запроса на сохранение для книги не должно быть
				excelappworkbook.Saved = true;
				// ФОрмат сохранения документа
				excelapp.DefaultSaveFormat = Excel.XlFileFormat.xlWorkbookNormal;
				// Сохраняем книгу
				try
				{
					// Определяем имя путь сохранения файла
					// Если каталог указан, проверяем его корректность и сохраняем файл
					// Если каталог не указан или не существует, сохраняем отчёт в папке с программой
					string saveFileName;
					if (Directory.Exists(Settings.Default.ReportDir) && Settings.Default.ReportDir != "")
					{
						saveFileName = Settings.Default.ReportDir + "\\Report " +
							DateTime.Now.ToShortDateString() + ".xls";
					}
					else
					{
						saveFileName = Application.StartupPath + "\\Report " +
							DateTime.Now.ToShortDateString() + ".xls";
					}

					//excelappworkbook.Save();
					excelappworkbook.SaveAs(saveFileName, Excel.XlFileFormat.xlWorkbookNormal);

					MessageBox.Show("Отчёт успешно сгенерирован и сохранён в файл: " + saveFileName, "Готово",
						MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
				}
				catch (Exception ex)
				{
					MessageBox.Show(ex.Message, "Ошибка сохранения файла отчёта.",
						MessageBoxButtons.OK, MessageBoxIcon.Error);
				}
				finally
				{
					// Закрываем приложение
					excelapp.Quit();
					createReportButton.Enabled = true;
				}
				#endregion

				toolStripStatusLabel2.Text = "";

			} // End if DirectoryExists
			else
			{
				// Выводим сообщение, если каталог отчётов не указан
				MessageBox.Show("Каталог с файлами отчётов не найден!",
					"Ошибка открытия каталога.", MessageBoxButtons.OK, MessageBoxIcon.Error);
			}
		}
 internal void UpdateLbSheets(ref ListBox lbWSheets)
 {
     lbWSheets.Items.Clear();
     xlWSheets = xlWBook.Sheets;
     foreach (Excel.Worksheet wsht in xlWSheets)
     {
         lbWSheets.Items.Add(wsht.Name);
     }
     int qry = 0;
     try
     {
         qry = lbWSheets.Items.Cast<string>().Select((item, i) => new { Ite = item, index = i }).FirstOrDefault(it => Regex.IsMatch(it.Ite, ".*inventory.*", RegexOptions.IgnoreCase)).index;
     }
     catch (Exception) { }
     lbWSheets.SetSelected(qry, true);
     xlWsheet = xlWSheets[qry + 1];
 }
Example #20
0
        private void forotborisfailov(string[] vsS, int a1, ref int z1, ref int z2, ref int z3, ref string s, string cilka)
        {
            
            excelapp = new Excel.Application();
            excelappworkbooks = excelapp.Workbooks;
            excelappworkbook = excelapp.Workbooks.Open(System.IO.Path.Combine(we, cilka),
             Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing);
            excelsheets = excelappworkbook.Worksheets;
            excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
            for (int i = 0; i < 350; i++)
            {
                if ((z1 < 26) && (z3 == 0)) s = vsS[z1] + a1.ToString();
                else
                {
                    z3++;
                    if (z3 == 1) z1 = 0;
                    s = vsS[z2] + vsS[z1] + a1.ToString();
                    if (z1 == 25) { z2++; z1 = 0; }
                }
                if (s == "IW" + a1.ToString()) break;
                excelcells = excelworksheet.get_Range(s, Type.Missing); 
                string excelnumber = excelcells.Value2;
                if ((excelnumber != null) && (excelnumber != "")) this.ponedelnic1flor.Add(excelnumber);
                z1++;
            }
            z1 = 0;
            z2 = 0;
            z3 = 0;
            excelworksheet = (Excel.Worksheet)excelsheets.get_Item(2);
            for (int i = 0; i < 350; i++)
            {
                if ((z1 < 26) && (z3 == 0)) s = vsS[z1] + a1.ToString();
                else
                {
                    z3++;
                    if (z3 == 1) z1 = 0;
                    s = vsS[z2] + vsS[z1] + a1.ToString();
                    if (z1 == 25) { z2++; z1 = 0; }
                }
                if (s == "IW" + a1.ToString()) break;
                excelcells = excelworksheet.get_Range(s, Type.Missing);
                string excelnumber = excelcells.Value2;
                if ((excelnumber != null) && (excelnumber != "")) this.ponedelnic1flor.Add(excelnumber);
                z1++;
            }
            z1 = 0;
            z2 = 0;
            z3 = 0;
            excelappworkbook.Close();
            excelappworkbooks.Close();
            excelapp.Workbooks.Close();

        }
Example #21
0
        private static void openExcel(String input, String output)
        {
            excelapp1 = new Microsoft.Office.Interop.Excel.Application();
            myworkbook1 = excelapp1.Workbooks.Open(input, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
            myworksheets1 = myworkbook1.Worksheets;
            myworksheet1 = myworksheets1.get_Item(1);
            range1 = myworksheet1.UsedRange;

            excelapp2 = new Microsoft.Office.Interop.Excel.Application();
            myworkbook2 = excelapp2.Workbooks.Open(output, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
            myworksheets2 = myworkbook2.Worksheets;
            myworksheet2 = myworksheets2.get_Item(1);
            range2 = myworksheet2.UsedRange;
        }
Example #22
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));
 }
Example #23
0
        // Event Handler for the middle browse button.
        private void mBook2Load_Click(object sender, EventArgs e)
        {
            // Create a new OpenFileDialog object that will be used to select the second
            // workbook.
            System.Windows.Forms.OpenFileDialog fWin = new OpenFileDialog();

            // Set the extensions and default folder path to be used by the dialog.
            fWin.DefaultExt = "xlsx";
            fWin.Filter = "Spreadsheet Files (*.xlsx;*.xls;*.csv)|*.xlsx;*.xls;*.csv";
            fWin.InitialDirectory =
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);

            // Open the dialog and do addional processing if a valid result is returned.
            if (fWin.ShowDialog() == DialogResult.OK)
            {
                // If a workbook is currently open close it.
                mBook2Sheets = null;
                if (mBook2 != null)
                {
                    mBook2.Close(false);
                    mBook2 = null;
                }

                if (fWin.FileName.Equals(mBook1File.Text) && fWin.FileName.EndsWith(".csv"))
                {
                    MessageBox.Show("Cannot compare a csv file to itself.");
                    return;
                }

                // Open the workbook selected in the dialog.
                mBook2File.Text = fWin.FileName;
                mBook2 = (Excel._Workbook)(mExcel.Workbooks.Add(fWin.FileName));
                mBook2Sheets = (Excel.Sheets)(mBook2.Sheets);

                // Enable and clear the combobox for the second workbook's worksheets.
                mBook2SheetBox.Enabled = true;
                mBook2SheetBox.Items.Clear();

                // Populate the combobox for the second workbook.
                int numSheets = mBook2Sheets.Count;
                for (int i = 1; i <= numSheets; i++)
                {
                    mBook2SheetBox.Items.Add(((Excel._Worksheet)(mBook2Sheets.Item[i])).Name);
                }
            }
        }
Example #24
0
        private void openWorkbook()
        {
            OpenFileDialog fileDialog = new OpenFileDialog();
            string filepath;

            fileDialog.Filter = "Excel|*.xls;*.xlsx|All Files|*.*";
            fileDialog.Multiselect = false;

            if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                filepath = fileDialog.FileName;

                m_xlWorkbooks = m_xlApp.Workbooks;
                m_xlWorkbook = m_xlWorkbooks.Open(filepath, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                m_xlSheets = m_xlWorkbook.Worksheets;
                m_xlWorksheets = new Dictionary<string, Excel.Worksheet>();
                foreach (Excel.Worksheet sheet in m_xlSheets)
                {
                    m_xlWorksheets.Add(sheet.Name, sheet);
                }

                comboBoxWorksheetSelector.DataSource = m_xlWorksheets.Keys.ToList<string>();

                labelExcelWorkbookName.Text = "\"" + Path.GetFileName(filepath) + "\"";
            }
        }
        private static void WriteResultsToExcel()
        {
            Console.WriteLine("Writing Results to Excel file...");
            oXL = new Microsoft.Office.Interop.Excel.Application { Visible = true, DisplayAlerts = false };

            string path = Path + "results.xls";
            if (File.Exists(path))
            {
                File.Delete(path);
            }

            mWorkBook = oXL.Workbooks.Add();
            mWorkSheets = mWorkBook.Worksheets;
            mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.Item["Sheet1"];
            ((Range)mWSheet1.Cells[1, 1]).EntireColumn.AutoFit();

            mWSheet1.Cells[1, 1] = "File Size (Bytes)";
            mWSheet1.Cells[1, 2] = "Encryption Time (microseconds)";
            mWSheet1.Cells[1, 3] = "Decryption Time (microseconds)";
            int index = 0;
            for (int i = 2; i < NoOfFiles + 2; i++)
            {
                mWSheet1.Cells[i, 1] = _testDetails.ElementAt(index).FileSize;
                mWSheet1.Cells[i, 2] = _testDetails.ElementAt(index).EncryptTime;
                mWSheet1.Cells[i, 3] = _testDetails.ElementAt(index).DecryptTime;
                index++;
            }
        }
Example #26
0
        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            // Refrences to the first workbook to be diffed.
            mBook1Sheets = null;
            if (mBook1 != null)
            {
                mBook1.Close();
                mBook1 = null;
            }

            // Refrences to the second workbook to be diffed.
            mBook2Sheets = null;
            if (mBook2 != null)
            {
                mBook2.Close();
                mBook2 = null;
            }

            // Refrence to the instance of Microsoft Excel being used.
            mExcel.Quit();
            mExcel = null;
        }
Example #27
0
 private void otkritie()
 {
     try
     {
        
         string s = Environment.GetFolderPath(Environment.SpecialFolder.UserProfile);
         string we = System.IO.Path.Combine(s, @"ProgramData\FreeRooms");
         System.IO.Directory.CreateDirectory(we);
         excelapp = new Excel.Application();
         excelappworkbooks = excelapp.Workbooks;
         excelappworkbook = excelapp.Workbooks.Open(System.IO.Path.Combine(we, numbernedeli),
          Type.Missing, Type.Missing, Type.Missing, Type.Missing,
          Type.Missing, Type.Missing, Type.Missing, Type.Missing,
          Type.Missing, Type.Missing, Type.Missing, Type.Missing,
          Type.Missing, Type.Missing);
         excelsheets = excelappworkbook.Worksheets;
         excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
         excelapp.Visible = true;
     }
     catch { MessageBox.Show("Файл не был найден, обновите базы данных"); }
 }
Example #28
0
        //This method wirtes the sim data to the excel file.
        public void WriteToExcel()
        {
            int iSimRunNumber = 0;
            int linenumber = 0;

            // Start Excel and get Application object.
            oXL = new Excel.Application();

            // Set some properties
            oXL.Visible = false;
            oXL.DisplayAlerts = false;

            // Get a new workbook.
            oWB = oXL.Workbooks.Add(Missing.Value);

            //Add a new sheets object.
            oXLSheets = oXL.Sheets as Excel.Sheets;

            foreach (DOE22SimFile simfile in DOESimFiles)
            {
                iSimRunNumber++;
                oSheet = (Excel.Worksheet)oXLSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                oSheet.Name = "RUN-" + iSimRunNumber.ToString();

                //oSheet.Name = Path.GetFileName(simfile.filepath);
                linenumber = 0;
                // Output BEPS to excel Sheet.
                oSheet.Cells[linenumber = 1, 1] = Path.GetFileName(simfile.filepath);
                linenumber++;
                oSheet.Cells[linenumber, 1] = "BEPS";
                linenumber++;
                //print bpes report.
                PrintTableToExcel(linenumber, simfile.bepsTable, oSheet);
                linenumber = linenumber + simfile.bepsTable.Rows.Count + 1;
                linenumber++;
                oSheet.Cells[linenumber, 1] = "ES-D";
                linenumber++;
                //Print es-d report.
                PrintTableToExcel(linenumber, simfile.esdTable, oSheet);

                // Resize the columns
                oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                                          oSheet.Cells[simfile.bepsTable.Rows.Count,
                                          simfile.bepsTable.Columns.Count]);
                oRange.EntireColumn.AutoFit();
            }

            //reset linenumber for All sheet.
            linenumber = 0;
            oSheet = (Excel.Worksheet)oXLSheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            oSheet.Name = "ALL";

            foreach (DOE22SimFile simfile in DOESimFiles)
            {
                linenumber++;
                // Output Filename to excel Sheet.
                oSheet.Cells[linenumber, 1] = Path.GetFileName(simfile.filepath);
                linenumber++;

                if (bWriteBEPS == true)
                {
                    // Output Filename to excel Sheet.
                    oSheet.Cells[linenumber, 1] = "BEPS";
                    linenumber++;
                    //print beps report.
                    PrintTableToExcel(linenumber, simfile.bepsTable, oSheet);
                    linenumber = linenumber + simfile.bepsTable.Rows.Count + 1;
                }

                //Print ES-D
                if (bWriteESD == true)
                {
                    linenumber++;
                    oSheet.Cells[linenumber, 1] = "ES-D";
                    linenumber++;
                    //Print es-d report.
                    PrintTableToExcel(linenumber, simfile.esdTable, oSheet);
                    linenumber = linenumber + simfile.esdTable.Rows.Count + 1;
                }

                //Print Zone Annual Data
                if (bWriteZoneAnnualData == true)
                {
                    linenumber++;
                    oSheet.Cells[linenumber, 1] = "Zone Annual Data";
                    linenumber++;
                    //Print Zone Annual Data report.
                    PrintTableToExcel(linenumber, simfile.ZoneAnnualTable, oSheet);
                    linenumber = linenumber + simfile.ZoneAnnualTable.Rows.Count + 1;
                }

                //Print System Annual Data
                if (bWriteSystemAnnualData == true)
                {
                    linenumber++;
                    oSheet.Cells[linenumber, 1] = "System Annual Data";
                    linenumber++;
                    //Print Zone Annual Data report.
                    PrintTableToExcel(linenumber, simfile.SystemAnnualTable, oSheet);
                    linenumber = linenumber + simfile.SystemAnnualTable.Rows.Count + 1;
                }

                // Resize the columns
                oRange = oSheet.get_Range(oSheet.Cells[1, 1],
                                          oSheet.Cells[simfile.bepsTable.Rows.Count,
                                          simfile.bepsTable.Columns.Count]);
                oRange.EntireColumn.AutoFit();
            }
            // Save the sheet and close
            oSheet = null;
            oRange = null;
            oWB.SaveAs(sFoldername + @"\test.xls", Excel.XlFileFormat.xlWorkbookNormal,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Excel.XlSaveAsAccessMode.xlExclusive,
                Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value);
            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
            oWB = null;

            // Clean up
            // NOTE: When in release mode, this does the trick
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }
Example #29
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;
        }
Example #30
0
        //1 - prvo učitamo stupce
        public void DobijStupce()
        {
            ExcelObj = new Excel.Application();
            excelApp = new Excel.Application();
            theWorkbook = ExcelObj.Workbooks.Open(dat, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);
            sheets = theWorkbook.Worksheets;
            worksheet = (Excel.Worksheet)sheets.get_Item(1);

            excelApp.Workbooks.Open(dat);

            for (int stupac = 1; stupac <= 25; stupac++)
            {
                var xlStupac = (worksheet.Cells[1, stupac] as Excel.Range).Value;
                string xlStupac_str = Convert.ToString(xlStupac);

                //za dobivanje stupca za kubike
                if (xlStupac_str == "Total Gross Volume")
                {
                    stupacCBM = stupac;
                    BrojStupca_CHR(stupacCBM);
                    textBoxCBM.Text = stupac_chr.ToString();

                }
                //else if (xlStupac_str.Contains("Volume"))
                //{
                //    stupacCBM = stupac;
                //}

                //za dobivanje stupca za ZIP
                else if (xlStupac_str == "Destination Postal Code")
                {
                    stupacZIP = stupac;
                    BrojStupca_CHR(stupacZIP);
                    textBoxZIP.Text = stupac_chr.ToString();
                }
               //else if (xlStupac_str.Contains("Postal"))
               // {
               //     stupacZIP = stupac;
               // }

                else if (xlStupac_str == null)
                {
                    stupacCijena = stupac;
                    BrojStupca_CHR(stupacCijena);
                    textBoxCijene.Text = stupac_chr.ToString();
                    break;
                }

            }
        }