Esempio n. 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);
 }
Esempio n. 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;
        }
Esempio n. 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
                }
            }
        }
Esempio n. 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);
 }
Esempio n. 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";
        }
Esempio n. 7
0
 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];
 }
Esempio n. 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);
     }
 }
Esempio n. 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();
        }
 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];
 }
Esempio n. 11
0
        public void WriteToExcel_hisDetail(DataTable thisTable, string FileName, string sheetName)
        {
            string strFilePath = FileName;
            string XLSName     = System.IO.Directory.GetCurrentDirectory() + @"\templet\历史明细模板.xls";

            Excel.Application app = new Excel.Application();
            app.DisplayAlerts = false;
            Excel.Workbooks  wbks = app.Workbooks;
            Excel._Workbook  _wbk = wbks.Add(XLSName);
            Excel.Sheets     shs  = _wbk.Sheets;
            Excel._Worksheet _wsh = (Excel._Worksheet)shs.get_Item(1);
            try
            {
                int sheetRowsCount = _wsh.UsedRange.Rows.Count;
                int count          = thisTable.Columns.Count;

                //设置列名
                //foreach (DataColumn myNewColumn in thisTable.Columns)
                //{
                //    _wsh.Cells[0, count] = myNewColumn.ColumnName;
                //    count = count + 1;
                //}er
                //for (int i = 0; i < count; i++)
                //{
                //    _wsh.Cells[1, i + 1] = thisTable.Columns[i].ColumnName;
                //}

                //加入內容
                for (int i = 1; i <= thisTable.Rows.Count; i++)
                {
                    int intBlockRowsNum = 0;
                    for (int j = 1; j <= thisTable.Columns.Count; j++)
                    {
                        int[] intBlockList = { 1, 2, 8, 9, 10, 14, 15, 16, 17, 18, 19 };
                        if (Array.IndexOf(intBlockList, j) < 0 && j != 7)
                        {
                            _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = thisTable.Rows[i - 1][j - 1];
                        }
                        else if (j == 7)
                        {
                            switch ((int)thisTable.Rows[i - 1][j - 1])
                            {
                            case 1:
                                _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "人民币";
                                break;

                            case 2:
                                _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "美元";
                                break;

                            case 3:
                                _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "港币";
                                break;

                            case 4:
                                _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "澳元";
                                break;

                            case 5:
                                _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "新元";
                                break;

                            case 6:
                                _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "马币";
                                break;

                            case 7:
                                _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "英镑";
                                break;

                            case 8:
                                _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "欧元";
                                break;

                            case 9:
                                _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "日元";
                                break;

                            case 10:
                                _wsh.Cells[i + sheetRowsCount, j - intBlockRowsNum] = "台币";
                                break;
                            }
                        }
                        else
                        {
                            intBlockRowsNum++;
                        }
                    }
                }
                _wsh.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
                _wsh.Cells.Columns.AutoFit();
                _wsh.Cells.Rows.AutoFit();
                //若為EXCEL2000, 將最後一個參數拿掉即可
                _wbk.SaveAs(strFilePath, Excel.XlFileFormat.xlWorkbookNormal,
                            null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,
                            false, false, null, null, null);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                //關閉文件
                _wbk.Close(false, Type.Missing, Type.Missing);
                app.Workbooks.Close();
                app.Quit();

                //釋放資源
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_wsh);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk);
                _wsh = null;
                _wbk = null;
                app  = null;
            }
        }
Esempio n. 12
0
        public void WriteToExcel(DataTable thisTable, string FileName, string sheetName)
        {
            string strFilePath = FileName;
            string XLSName     = System.IO.Directory.GetCurrentDirectory() + @"\templet\报告模板.xls";

            Excel.Application app = new Excel.Application();
            app.DisplayAlerts = false;
            Excel.Workbooks  wbks = app.Workbooks;
            Excel._Workbook  _wbk = wbks.Add(XLSName);
            Excel.Sheets     shs  = _wbk.Sheets;
            Excel._Worksheet _wsh = (Excel._Worksheet)shs.get_Item(1);
            try
            {
                int sheetRowsCount = _wsh.UsedRange.Rows.Count;
                int count          = thisTable.Columns.Count;

                //设置列名
                //foreach (DataColumn myNewColumn in thisTable.Columns)
                //{
                //    _wsh.Cells[0, count] = myNewColumn.ColumnName;
                //    count = count + 1;
                //}er
                for (int i = 0; i < count; i++)
                {
                    _wsh.Cells[1, i + 1] = thisTable.Columns[i].ColumnName;
                }

                //加入內容
                for (int i = 1; i <= thisTable.Rows.Count; i++)
                {
                    for (int j = 1; j <= thisTable.Columns.Count; j++)
                    {
                        _wsh.Cells[i + sheetRowsCount, j] = thisTable.Rows[i - 1][j - 1];
                    }
                }
                _wsh.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
                _wsh.Cells.Columns.AutoFit();
                _wsh.Cells.Rows.AutoFit();
                //若為EXCEL2000, 將最後一個參數拿掉即可
                _wbk.SaveAs(strFilePath, Excel.XlFileFormat.xlWorkbookNormal,
                            null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,
                            false, false, null, null, null);
            }
            catch (Exception ex)
            {
            }
            finally
            {
                //關閉文件
                _wbk.Close(false, Type.Missing, Type.Missing);
                app.Workbooks.Close();
                app.Quit();

                //釋放資源
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_wsh);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(_wbk);
                _wsh = null;
                _wbk = null;
                app  = null;
            }
        }
Esempio n. 13
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);
                }
            }
Esempio n. 14
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++;
            }
        }
Esempio n. 15
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);
                }
            }
        }
        private void CreateInvoice(List <RentReportDto> rentList, string templateFilePath)
        {
            try
            {
                string            templatePath = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), templateFilePath);
                Excel.Application xlApp        = null;
                xlApp = new Excel.Application();
                Excel.Workbooks xlWorkbooks = xlApp.Workbooks;
                Excel.Workbook  xlWorkbook  = xlWorkbooks.Open(templatePath);
                Excel.Sheets    xlSheets    = xlWorkbook.Sheets;
                Excel.Worksheet xlWorksheet = xlSheets[1];

                //Make a copy
                xlWorksheet.Copy();
                int row = 4;

                foreach (RentReportDto rent in rentList)
                {
                    ++row;
                    int column = 0;

                    xlWorksheet.Cells[row, ++column] = rent.BuildingName + "    " + rent.ArabicName;
                    xlWorksheet.Range[xlWorksheet.Cells[row, column], xlWorksheet.Cells[row, column + 20]].Merge(Type.Missing);
                    xlWorksheet.Range[xlWorksheet.Cells[row, column], xlWorksheet.Cells[row, column + 20]].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    xlWorksheet.Range[xlWorksheet.Cells[row, column], xlWorksheet.Cells[row, column + 20]].Font.Bold           = true;
                    xlWorksheet.Range[xlWorksheet.Cells[row, column], xlWorksheet.Cells[row, column + 20]].Font.Size           = 20;
                    row++;
                    foreach (ContractDto contract in rent.contractDto)
                    {
                        column = 0;
                        xlWorksheet.Cells[row, ++column] = row + 1;
                        xlWorksheet.Cells[row, ++column] = contract.SpaceunitCombinedName;
                        xlWorksheet.Cells[row, ++column] = contract.ContractorName;
                        xlWorksheet.Cells[row, ++column] = contract.ContractStartDate;
                        xlWorksheet.Cells[row, ++column] = contract.ContractEndDate;
                        xlWorksheet.Cells[row, ++column] = contract.ContractAmount;
                        xlWorksheet.Cells[row, ++column] = contract.NoOfInstallments;
                        foreach (ContractPayment payment in contract.Contractpayments)
                        {
                            xlWorksheet.Cells[row, ++column] = payment.Amount;
                            xlWorksheet.Cells[row, ++column] = payment.DueDate.Value.ToString("MM/dd/yyyy");
                            xlWorksheet.Cells[row, ++column] = payment.Status;
                        }
                        row++;
                    }
                }



                //var activeWorkbook = ((Excel.Workbook) Application.ActiveWorkbook);
                string invoicePath = System.IO.Path.GetDirectoryName(templatePath);
                string file        = string.Format(@"{0}\Invoice_{1}.xlsx", "output", DateTime.Now.Year + "-" + DateTime.Now.Month + "-" +
                                                   DateTime.Now.Day);



                invoicePath = System.IO.Path.Combine(invoicePath, file);
                if (File.Exists(invoicePath))
                {
                    File.Delete(invoicePath);
                }
                if (!Directory.Exists(Path.GetDirectoryName(invoicePath)))
                {
                    Directory.CreateDirectory(Path.GetDirectoryName(invoicePath));
                }
                xlWorksheet.SaveAs(invoicePath);

                xlWorkbook.Close();

                //  ...clean up...
                Marshal.ReleaseComObject(xlWorksheet);
                Marshal.ReleaseComObject(xlSheets);
                Marshal.ReleaseComObject(xlWorkbook);
                Marshal.ReleaseComObject(xlWorkbooks);

                //Kill the provesds as Excel process is stubbon
                if (xlApp != null)
                {
                    xlApp.Quit();
                    int xIProcessId = 0;
                    GetWindowThreadProcessId(new IntPtr(xlApp.Hwnd), ref xIProcessId);

                    Process xlProc = Process.GetProcessById(xIProcessId);
                    if (xlProc != null)
                    {
                        xlProc.Kill();
                    }
                }
                Process.Start(invoicePath);
            }
            catch (Exception ex)
            {
                LogWriter.Write(ex);
            }
        }
Esempio n. 17
0
        public void ToExcel(List <Pesertaujian> PesertaUjian, Pesertaujian JadwalPeserta)
        {
            // ------------ to excel ---------------------
            // build excel apps
            Excel.Application app    = null;
            Excel.Workbooks   books  = null;
            Excel.Workbook    book   = null;
            Excel.Sheets      sheets = null;

            // create apps object
            var excelApp = new Excel.Application();

            excelApp.DefaultSaveFormat = Excel.XlFileFormat.xlOpenXMLWorkbook;

            try
            {
                excelApp.Visible       = false;
                excelApp.DisplayAlerts = false;

                excelApp.Workbooks.Add();
                Excel._Worksheet workSheet = excelApp.ActiveSheet;

                // text keterangan
                workSheet.Cells[1, "A"] = "DAFTAR PESERTA UJIAN " + JadwalPeserta.JenisUjian.ToUpper();

                workSheet.Cells[3, "A"] = "Mata Kuliah/Kelas";
                workSheet.Cells[4, "A"] = "Tahun/Semester";
                workSheet.Cells[5, "A"] = "Jadwal";
                workSheet.Cells[6, "A"] = "Dosen Pengampu";

                workSheet.Cells[3, "C"] = JadwalPeserta.Makul + "/" + JadwalPeserta.Kelas;
                workSheet.Cells[4, "C"] = JadwalPeserta.Tahun + "/" + JadwalPeserta.Semester;;
                workSheet.Cells[5, "C"] = JadwalPeserta.Jadwal;
                workSheet.Cells[6, "C"] = JadwalPeserta.Dosen;

                workSheet.Cells[8, "A"] = "NO";
                workSheet.Cells[8, "B"] = "NPM";
                workSheet.Cells[8, "C"] = "NAMA";
                workSheet.Cells[8, "D"] = "NILAI ANGKA";
                workSheet.Cells[8, "E"] = "NILAI HURUF";
                workSheet.Cells[8, "F"] = "TANDA TANGAN";

                // loop
                var row = 8;
                foreach (var mhs in PesertaUjian)
                {
                    row++;
                    workSheet.Cells[row, "A"] = "";
                    workSheet.Cells[row, "B"] = mhs.npm;
                    workSheet.Cells[row, "C"] = mhs.nama;
                }

                //tanda tangan
                workSheet.Cells[row + 3, "B"] = "Ketua Jurusan";
                workSheet.Cells[row + 7, "B"] = "...";
                workSheet.Cells[row + 2, "E"] = "Magelang, ...";
                workSheet.Cells[row + 3, "E"] = "Dosen Pengampu,";
                workSheet.Cells[row + 7, "E"] = JadwalPeserta.Dosen.Trim();

                // autofit
                workSheet.Columns[1].ColumnWidth = 3;
                workSheet.Columns[2].Autofit();
                workSheet.Columns[3].Autofit();
                workSheet.Columns[4].Autofit();
                //workSheet.Columns[5].Autofit();
                workSheet.Columns[6].Autofit();

                // text align left
                workSheet.get_Range("A1", "D" + row.ToString()).Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

                // border nilai
                var range = workSheet.get_Range("A8", "F" + row.ToString());
                range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).LineStyle         = Excel.XlLineStyle.xlContinuous;
                range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle        = Excel.XlLineStyle.xlContinuous;
                range.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
                range.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle   = Excel.XlLineStyle.xlContinuous;
                range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle          = Excel.XlLineStyle.xlContinuous;
                range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle       = Excel.XlLineStyle.xlContinuous;

                // save excel file
                //string excelFile = "C:\\PesertaUjian\\" + JadwalPeserta.Makul.Trim() + "-" + JadwalPeserta.Kelas.Trim() + "-" + JadwalPeserta.JenisKelas.Trim() + "-" + JadwalPeserta.Tahun.Trim() + JadwalPeserta.Semester.Trim();
                string excelFile = Server.MapPath("~/PesertaUjian/") + JadwalPeserta.Makul.Trim() + "-" + JadwalPeserta.Kelas.Trim() + "-" + JadwalPeserta.JenisKelas.Trim() + "-" + JadwalPeserta.Tahun.Trim() + JadwalPeserta.Semester.Trim();
                workSheet.SaveAs(excelFile, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);

                // quit excel apps
                excelApp.Workbooks.Close();
                excelApp.Quit();
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message.ToString());

                // quit excel apps
                excelApp.Workbooks.Close();
                excelApp.Quit();

                throw ex;
            }
            finally
            {
                // release excel object
                if (sheets != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
                }
                if (book != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                }
                if (books != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                }
                if (app != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                }
            }


            try
            {
                // FileInfo fileInfo = new FileInfo(Server.MapPath("~/Nilai/coba.xlsx"));
                // C:\inetpub\wwwroot\portal
                // FileInfo fileInfo = new FileInfo("C:\\PesertaUjian\\" + JadwalPeserta.Makul.Trim() + "-" + JadwalPeserta.Kelas.Trim() + "-" + JadwalPeserta.JenisKelas.Trim() + "-" + JadwalPeserta.Tahun.Trim() + JadwalPeserta.Semester.Trim() + ".xlsx");
                FileInfo fileInfo = new FileInfo(Server.MapPath("~/PesertaUjian/") + JadwalPeserta.Makul.Trim() + "-" + JadwalPeserta.Kelas.Trim() + "-" + JadwalPeserta.JenisKelas.Trim() + "-" + JadwalPeserta.Tahun.Trim() + JadwalPeserta.Semester.Trim() + ".xlsx");
                Response.Clear();
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileInfo.Name));//"attachment;filename=" + fileInfo.Name);
                Response.AddHeader("Content-Length", fileInfo.Length.ToString());
                Response.ContentType = "application/octet-stream";
                Response.Flush();
                Response.WriteFile(fileInfo.FullName);
                Response.End();

                // kill excel proccess
                Process[] pProcess;
                pProcess = Process.GetProcessesByName("Excel");
                pProcess[0].Kill();
            }
            catch (Exception ex)
            {
                Response.Write(ex.Message.ToString());
                throw ex;
            }
        }
Esempio n. 18
0
        public static void ExportToExcel(string fileName, string startDate, string EndDate)
        {
            B2CLInvoiceGenerate(startDate, EndDate);

            Excel.Application oXL = new Microsoft.Office.Interop.Excel.Application();
            oXL.DisplayAlerts = false;
            string filePath = fileName;

            Excel.Workbook xlWorkBook = oXL.Workbooks.Open(filePath, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
            Excel.Sheets   worksheets = xlWorkBook.Worksheets;
            Excel.Range    oRng;

            var oSheet = (Excel.Worksheet)worksheets.Add(worksheets[oXL.ActiveWorkbook.Worksheets.Count], Type.Missing, Type.Missing, Type.Missing);

            oSheet.Name = "B2CL";
            #region Content
            int i = 0;
            int j = 0;

            for (i = 0; i <= mDt.Rows.Count - 1; i++)
            {
                for (j = 0; j <= mDt.Columns.Count - 1; j++)
                {
                    oSheet.Cells[i + 1, j + 1] = mDt.Rows[i][j];// cell.Value;
                }
            }
            oXL.StandardFont = "Cambria";

            oSheet.get_Range("A1", "A1").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(((int)(((byte)(66)))), ((int)(((byte)(113)))), ((int)(((byte)(244))))));
            oSheet.get_Range("A2", "H2").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(((int)(((byte)(66)))), ((int)(((byte)(113)))), ((int)(((byte)(244))))));
            oSheet.get_Range("A1", "H2").Font.Color     = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
            oSheet.get_Range("A4", "H4").Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Bisque);



            oSheet.get_Range("C1", "C" + (mDt.Rows.Count + 1)).NumberFormat = "#,###,###0.00";
            oSheet.get_Range("E1", "G" + (mDt.Rows.Count + 1)).NumberFormat = "#,###,###0.00";


            oSheet.get_Range("A1", "H" + (mDt.Rows.Count + 1)).Font.FontStyle = "Cambria";
            oSheet.get_Range("A1", "H2").Font.Bold = true;
            oSheet.get_Range("A1", "H2").Font.Size = 12;
            oSheet.get_Range("A1", "B" + (mDt.Rows.Count + 1)).VerticalAlignment   = Excel.XlVAlign.xlVAlignCenter;
            oSheet.get_Range("A1", "B" + (mDt.Rows.Count + 1)).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

            //BorderLine
            oSheet.get_Range("A1", "H3").Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
            oRng = oSheet.get_Range("A1", "H3");
            oRng.EntireColumn.AutoFit();
            #endregion

            oSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            oSheet.Select();
            xlWorkBook.Save();
            xlWorkBook.Close();

            releaseObject(oSheet);
            releaseObject(worksheets);
            releaseObject(xlWorkBook);
            releaseObject(oXL);
        }
Esempio n. 19
0
        public static void ExportDataGridViewTo_Excel12(DataGridView myDataGridView)
        {
            try
            {
                Excel_12.Application oExcel_12   = null;              //Excel_12 Application
                Excel_12.Workbook    oBook       = null;              // Excel_12 Workbook
                Excel_12.Sheets      oSheetsColl = null;              // Excel_12 Worksheets collection
                Excel_12.Worksheet   oSheet      = null;              // Excel_12 Worksheet
                Excel_12.Range       oRange      = null;              // Cell or Range in worksheet
                Object oMissing = System.Reflection.Missing.Value;

                // Create an instance of Excel_12.
                oExcel_12 = new Excel_12.Application();

                // Make Excel_12 visible to the user.
                oExcel_12.Visible = true;

                // Set the UserControl property so Excel_12 won't shut down.
                oExcel_12.UserControl = true;

                // System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");

                // Add a workbook.
                oBook = oExcel_12.Workbooks.Add(oMissing);

                // Get worksheets collection
                oSheetsColl = oExcel_12.Worksheets;

                // Get Worksheet "Sheet1"
                oSheet = (Excel_12.Worksheet)oSheetsColl.get_Item("Sheet1");

                // Export titles
                for (int j = 0; j < myDataGridView.Columns.Count; j++)
                {
                    oRange        = (Excel_12.Range)oSheet.Cells[1, j + 1];
                    oRange.Value2 = myDataGridView.Columns[j].HeaderText;
                }

                // Export data
                for (int i = 0; i < myDataGridView.Rows.Count; i++)
                {
                    for (int j = 0; j < myDataGridView.Columns.Count; j++)
                    {
                        oRange        = (Excel_12.Range)oSheet.Cells[i + 2, j + 1];
                        oRange.Value2 = myDataGridView[j, i].Value.ToString();
                    }
                }

                // Release the variables.
                //oBook.Close(false, oMissing, oMissing);
                oBook = null;

                //oExcel_12.Quit();
                oExcel_12 = null;

                // Collect garbage.
                GC.Collect();
            }
            catch
            {
                MessageBox.Show("CAN NOT GENERATE EXCEL FILE", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
        }
Esempio n. 20
0
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                // Instantiate Excel and start a new workbook.
                ArrayList exInst   = new ArrayList(); //List of existing Excel
                Process[] P_CESSES = Process.GetProcessesByName("EXCEL");
                for (int p_count = 0; p_count < P_CESSES.Length; p_count++)
                {
                    exInst.Add(P_CESSES[p_count].Id);
                }
                objApp = new Excel.Application();

                objBooks  = objApp.Workbooks;
                objBook   = objBooks.Open("Y:\\Mechanical\\Inventor\\iLogic_Plug_Ins\\iLogic_Drawings_Plug_Ins\\TestXL.xlsx");
                objSheets = objBook.Worksheets;
                objSheet  = (Excel._Worksheet)objSheets.get_Item(1);



                objSheet.Cells[2, 1] = "Heyy";


                Boolean bEndFound = false;
                int     n         = 0;
                String  tempString;
                int     tempCount = 0;
                if (cats == null)
                {
                    cats = new Dictionary <string, int>();
                }
                else
                {
                    cats.Clear();
                }
                ComboBox _cbCats = cbCats;

                while (!bEndFound)
                {
                    objRange   = ((Excel.Range)objSheet.Cells[3 + n, 4]);
                    tempString = objRange.Text;
                    if (tempString.Equals(""))
                    {
                        bEndFound = true;
                    }
                    else
                    {
                        objRange  = ((Excel.Range)objSheet.Cells[3 + n, 5]);
                        tempCount = Convert.ToInt32(objRange.Value2);
                        cats.Add(tempString, tempCount);
                        n++;
                        _cbCats.Items.Add(tempString);
                    }
                }



                objBook.Save();
                objBook.Close(0);

                Boolean bFound = false;
                P_CESSES = Process.GetProcessesByName("EXCEL");
                for (int p_count = 0; p_count < P_CESSES.Length; p_count++)
                {
                    bFound = false;
                    foreach (int s in exInst)
                    {
                        if (s.Equals(P_CESSES[p_count].Id))
                        {
                            bFound = true;
                        }
                    }

                    if (!bFound)
                    {
                        P_CESSES[p_count].Kill();
                    }
                }

                while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objApp) != 0)
                {
                }
                while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objBooks) != 0)
                {
                }
                while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objBook) != 0)
                {
                }
                while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objSheets) != 0)
                {
                }
                while (System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objSheet) != 0)
                {
                }
                objApp    = null;
                objBooks  = null;
                objBook   = null;
                objSheets = null;
                objSheet  = null;


                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            catch (Exception myError)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, myError.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, myError.Source);

                MessageBox.Show(errorMessage, "Error");
            }
        }
Esempio n. 21
0
        private void exportexcel(System.Data.DataTable dt, string filename)
        {
            Microsoft.Office.Interop.Excel._Workbook oWB;
            Microsoft.Office.Interop.Excel.Series    oSeries;
            //  Microsoft.Office.Interop.Excel.Range oResizeRange;
            Microsoft.Office.Interop.Excel._Chart oChart;
            //String sMsg;
            //int iNumQtrs;
            GC.Collect();//系统的垃圾回收

            //string filename = @"C:\Documents and Settings\tongxl\桌面\nnn.xls";
            //Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
            //Microsoft.Office.Interop.Excel._Workbook wb = ep.Workbooks.Add(filename);

            Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook   wb = ep.Workbooks.Add(true);

            ep.Visible = true;
            Microsoft.Office.Interop.Excel.Sheets     sheets = wb.Worksheets;
            Microsoft.Office.Interop.Excel._Worksheet ws     = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);// [System.Type.Missing];//.get.get_Item("xx");
            ws.UsedRange.Select();
            ws.UsedRange.Copy(System.Type.Missing);
            // wb.Charts.Add(System.Type.Missing, System.Type.Missing, 1, System.Type.Missing);

            int rowIndex = 1;
            int colIndex = 1;

            foreach (DataColumn col in dt.Columns)
            {
                ws.Cells[rowIndex, colIndex] = col.ColumnName;
                colIndex++;
            }

            for (int drvIndex = 0; drvIndex < dt.Rows.Count; drvIndex++)
            {
                DataRow row = dt.Rows[drvIndex];
                colIndex = 1;

                foreach (DataColumn col in dt.Columns)
                {
                    ws.Cells[drvIndex + 2, colIndex] = row[col.ColumnName].ToString();
                    colIndex++;
                }
            }
            oWB    = (Microsoft.Office.Interop.Excel._Workbook)ws.Parent;
            oChart = (Microsoft.Office.Interop.Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value,
                                                                           Missing.Value, Missing.Value);

            oChart.ChartWizard(ws.get_Range(ws.Cells[1, 1], ws.Cells[30, 30]), Microsoft.Office.Interop.Excel.XlChartType.xlLine, Missing.Value,
                               XlRowCol.xlColumns, true, true, true,
                               this.bandedGridView1.GroupPanelText, Missing.Value, Missing.Value, Missing.Value);
            oSeries = (Microsoft.Office.Interop.Excel.Series)oChart.SeriesCollection(1);

            oChart.PlotVisibleOnly = false;
            // oChart.HasDataTable = true;

            Microsoft.Office.Interop.Excel.Axis axis = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
                Microsoft.Office.Interop.Excel.XlAxisType.xlValue,
                Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);


            Microsoft.Office.Interop.Excel.Axis ax = (Microsoft.Office.Interop.Excel.Axis)oChart.Axes(
                Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);

            //ax.HasTitle = true;
            //ax.AxisTitle.Text = "Sales Figures";
            ax.HasMajorGridlines = true;

            //string filename = @"C:\Documents and Settings\tongxl\桌面\ccsb.xls";
            ws.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        }
Esempio n. 22
0
        private bool read_file(out string msg)
        {
            msg = null;

            Excel._Application app = new Excel.Application();

            try
            {
                //  string FilePath = Path.Combine(inpath, FileName);

                Excel._Workbook book = app.Workbooks.Open(FilePath, 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);
                Excel.Sheets     sheets = book.Worksheets;
                Excel._Worksheet sheet;

                sheet = (Excel._Worksheet)sheets.get_Item(1);

                Excel.Range range;

                data = new List <STRowUpdateContract>();

                int lastRow = sheet.UsedRange.Rows.Count;

                for (int i = 1; i <= lastRow; i++)
                {
                    range = sheet.get_Range(string.Format("A{0}", i), Missing.Value);
                    if (range.Value2 == null)
                    {
                        continue;
                    }
                    string s = Convert.ToString(range.Value2).Trim();

                    if (!is_d(s))
                    {
                        continue;
                    }
                    else
                    {
                        STRowUpdateContract item = new STRowUpdateContract();

                        range = sheet.get_Range(string.Format("A{0}", i), Missing.Value);
                        if (range.Value2 != null)
                        {
                            item.contrcode = Convert.ToInt32(range.Value2);
                        }
                        else
                        {
                            item.contrcode = -1;
                        }

                        range = sheet.get_Range(string.Format("B{0}", i), Missing.Value);
                        if (range.Value2 != null)
                        {
                            item.mdmcode = Convert.ToString(range.Value2).Trim();
                        }
                        else
                        {
                            item.mdmcode = null;
                        }

                        if (item.contrcode > 0 && !string.IsNullOrEmpty(item.mdmcode))
                        {
                            data.Add(item);
                        }
                    }
                }
                app.Quit();
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                log.LogLine("File wasn't read.");
                return(false);
            }
            finally { app.Quit(); }

            return(true);
        }
Esempio n. 23
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;
            }
        }
Esempio n. 24
0
        private void cmbJoblist_SelectedIndexChanged(object sender, EventArgs e)
        {
            Excel.Workbook excelWorkbook = null;
            rd              = null;
            resDB           = null;
            GOSTOP          = false;
            btnCalc.Enabled = false;
            try
            {
                txtTpn.Text  = "";
                txtTpsn.Text = "";
                txtPosn.Text = "";
                string str = txtCurDir.Text + "\\" + cmbJoblist.SelectedItem.ToString();
                if (!File.Exists(str))
                {
                    MessageBox.Show("File is not found, " + str, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                // Read File and display the file header.
                Excel.Application excelApp = new Excel.Application(); // Creates a new Excel Application
                excelApp.Visible = false;                             // Makes Excel visible to the user.

                // The following code opens an existing workbook
                string workbookPath = str;  // Add your own path here

                try
                {
                    excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
                                                            false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true,
                                                            false, 0, true, false, false);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("File open Error:  " + ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                // The following gets the Worksheets collection
                Excel.Sheets excelSheets = excelWorkbook.Worksheets;

                // The following gets Sheet1 for editing
                string          currentSheet   = "RawDB";
                Excel.Worksheet excelWorksheet = null;
                try
                {
                    excelWorksheet = (Excel.Worksheet)excelSheets.get_Item("RawDB");
                }
                catch
                {
                    try
                    {
                        excelWorksheet = (Excel.Worksheet)excelSheets.get_Item("Raw DB");
                    }
                    catch
                    {
                        MessageBox.Show("Error: There is not the Sheet that is named 'Raw DB'", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        if (excelWorkbook != null)
                        {
                            excelWorkbook.Close();
                        }
                        return;
                    }
                }

                // The following gets cell A1 for editing
                int tpn, tpsn, posn, RawStart, ColStart;
                try
                {
                    tpn           = (int)excelWorksheet.Cells[1, 2].Value2;
                    Common.TPN    = tpn;
                    txtTpn.Text   = tpn.ToString();
                    tpsn          = (int)excelWorksheet.Cells[2, 2].Value2;
                    Common.TSPN   = tpsn;
                    txtTpsn.Text  = tpsn.ToString();
                    posn          = (int)excelWorksheet.Cells[3, 2].Value2;
                    Common.POSN   = posn;
                    txtPosn.Text  = posn.ToString();
                    RawStart      = (int)excelWorksheet.Cells[4, 2].Value2;
                    ColStart      = (int)excelWorksheet.Cells[4, 3].Value2;
                    txtStart.Text = "Row:" + RawStart.ToString() + ",Col:" + ColStart.ToString();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Data Read Error, please check the input data(TPN,TPSN,POSN,Start Position) in 'RAW DB' sheet.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    if (excelWorkbook != null)
                    {
                        excelWorkbook.Close();
                    }
                    return;
                }

                // Read All Data and make a RawData Class
                rd = new RawData(posn, tpn, tpsn);
                int d = 0;
                for (int i = 0; i < posn; i++)
                {
                    for (int j = 0; j < tpn; j++)
                    {
                        rd.Set(i, j, 0);
                    }
                    for (int j = 0; j < tpn; j++)
                    {
                        try
                        {
                            d = Convert.ToInt32(excelWorksheet.Cells[RawStart + i, ColStart + j].Value2);
                            if (d > 0 && d <= tpn)
                            {
                                rd.Set(i, d - 1, 1);
                            }
                        }
                        catch
                        {
                            d = 0;
                        }
                        if (d == 0)
                        {
                            break;
                        }
                    }
                    if (i % 100 == 0)
                    {
                        Console.WriteLine("line: " + i.ToString());
                    }
                }
                excelWorkbook.Close();
                excelWorkbook = null;
                //rd.printRawData();
                btnCalc.Enabled      = true;
                StatusProgress.Value = 0;
                statusLabel1.Text    = "";
                timer1.Enabled       = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error:  unhandled error was raised. system message:" + ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                if (excelWorkbook != null)
                {
                    excelWorkbook.Close();
                }
            }
        }
Esempio n. 25
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();
        }
Esempio n. 26
0
        private void button4_Click(object sender, EventArgs e)
        {
            string FileName    = textBox1.Text;
            object rOnly       = true;
            object SaveChanges = false;
            object MissingObj  = System.Reflection.Missing.Value;

            Excel.Application app       = new Excel.Application();
            Excel.Workbooks   workbooks = null;
            Excel.Workbook    workbook  = null;
            Excel.Sheets      sheets    = null;

            workbooks = app.Workbooks;
            workbook  = workbooks.Open(FileName, MissingObj, rOnly, MissingObj, MissingObj,
                                       MissingObj, MissingObj, MissingObj, MissingObj, MissingObj,
                                       MissingObj, MissingObj, MissingObj, MissingObj, MissingObj);


            sheets = workbook.Sheets;

            Context db = new Context();

            toolStripStatusLabel1.Text = "Очистка таблицы 1 БД";
            var rows = from o in db.Table1
                       select o;

            foreach (var row in rows)
            {
                db.Table1.Remove(row);
            }
            db.SaveChanges();
            toolStripStatusLabel1.Text = "Очистка таблицы 1 БД завершена";

            toolStripStatusLabel1.Text = "Очистка таблицы 2 БД";
            var rows2 = from o in db.Table2
                        select o;

            foreach (var row2 in rows2)
            {
                db.Table2.Remove(row2);
            }
            db.SaveChanges();
            toolStripStatusLabel1.Text = "Очистка таблицы 2 БД завершена";

            toolStripStatusLabel1.Text = "Загрузка данных в таблицу 1 БД";
            foreach (Excel.Worksheet worksheet in sheets)
            {
                Excel.Range UsedRange = worksheet.UsedRange;
                Excel.Range urRows    = UsedRange.Rows;

                int RowsCount = urRows.Count;
                int step      = RowsCount / 50;
                int count     = 0;
                for (int i = 1; i <= RowsCount; i++)
                {
                    Excel.Range CellRange = UsedRange.Cells[i, 1];

                    string CellText = (CellRange == null || CellRange.Value2 == null) ? null :
                                      (CellRange as Excel.Range).Value2.ToString();

                    if (CellText != null)
                    {
                        var table1 = new Table1
                        {
                            Number1 = CellText
                        };

                        db.Table1.Add(table1);
                    }

                    toolStripStatusLabel1.Text = $"В таблицу 1 БД из {RowsCount} записей загружено: {i}";

                    count++;
                    if (count == step)
                    {
                        progressBar1.PerformStep();
                        count = 0;
                    }
                }
            }
            db.SaveChanges();
            toolStripStatusLabel1.Text = "Загрузка данных в таблицу 1 БД завершена";

            FileName = textBox2.Text;

            app       = new Excel.Application();
            workbooks = null;
            workbook  = null;
            sheets    = null;

            workbooks = app.Workbooks;
            workbook  = workbooks.Open(FileName, MissingObj, rOnly, MissingObj, MissingObj,
                                       MissingObj, MissingObj, MissingObj, MissingObj, MissingObj,
                                       MissingObj, MissingObj, MissingObj, MissingObj, MissingObj);


            sheets = workbook.Sheets;

            toolStripStatusLabel1.Text = "Загрузка данных в таблицу 2 БД";
            foreach (Excel.Worksheet worksheet in sheets)
            {
                Excel.Range UsedRange = worksheet.UsedRange;
                Excel.Range urRows    = UsedRange.Rows;

                int RowsCount = urRows.Count;
                int step      = RowsCount / 50;
                int count     = 0;
                for (int i = 1; i <= RowsCount; i++)
                {
                    Excel.Range CellRange = UsedRange.Cells[i, 1];

                    string CellText = (CellRange == null || CellRange.Value2 == null) ? null :
                                      (CellRange as Excel.Range).Value2.ToString();

                    if (CellText != null)
                    {
                        var table2 = new Table2
                        {
                            Number2 = CellText
                        };

                        db.Table2.Add(table2);
                    }

                    toolStripStatusLabel1.Text = $"В таблицу 2 БД из {RowsCount} записей загружено: {i}";

                    count++;
                    if (count == step)
                    {
                        progressBar1.PerformStep();
                        count = 0;
                    }
                }
            }
            db.SaveChanges();
            toolStripStatusLabel1.Text = "Загрузка данных в таблицу 2 БД завершена";

            toolStripStatusLabel1.Text = "Выполняется запрос к БД";
            var result = from c in db.Table1
                         join p in db.Table2 on c.Number1 equals p.Number2 into ps
                         from p in ps.DefaultIfEmpty()
                         select new { Number1 = c, Number2 = p == null ? c.Number1 : null };

            toolStripStatusLabel1.Text = "Запрос к БД выполнен";

            toolStripStatusLabel1.Text = "Вывод в файл";
            StreamWriter f = new StreamWriter(textBox3.Text + @"\result.txt");

            foreach (var p in result)
            {
                if (p.Number2 != null)
                {
                    f.WriteLine(p.Number2);
                    Console.WriteLine(p.Number2);
                }
            }
            f.Close();
            toolStripStatusLabel1.Text = "Вывод в файл завершен";
            toolStripStatusLabel1.Text = "Готово";
        }
        public List <string> GetWorkSheet(string fileName, int worksheetNumber, string rangeStart, string rangeEnd)
        {
            // ->>>>> int userRow, int userCol
            // Reference to Excel Application.

            Excel.Application app    = null;
            Excel.Workbooks   books  = null;
            Excel.Workbook    book   = null;
            Excel.Sheets      sheets = null;
            Excel.Worksheet   sheet  = null;
            Excel.Range       range  = null;

            string startupPath = System.IO.Directory.GetCurrentDirectory();

            try
            {
                app = new Excel.Application();
                app.DisplayAlerts = false;
                books             = app.Workbooks;
                //book = books.Open(Path.GetFullPath(Path.GetFullPath(@"TestData\" + fileName + ".xlsx")));

                book = books.Open(fileName);

                sheets = book.Sheets;
                sheet  = sheets.get_Item(1);

                range = sheet.get_Range(rangeStart, rangeEnd);
                //range.NumberFormat = "General";
                //range.Value2 = data;

                object[,] cellValues = (object[, ])range.Value2;
                //List<double> lst = cellValues.Cast<object>().ToList().ConvertAll(x=> Convert.ToDouble(x));

                List <string> lst = cellValues.Cast <object>().ToList().ConvertAll(x => Convert.ToString(x));

                book.Close();
                app.Quit();

                //Console.WriteLine(lst[0]);
                //Console.WriteLine(lst[1]);
                //Console.WriteLine(lst[2]);

                return(lst);
            }

            finally
            {
                if (range != null)
                {
                    Marshal.ReleaseComObject(range);
                }
                if (sheet != null)
                {
                    Marshal.ReleaseComObject(sheet);
                }
                if (sheets != null)
                {
                    Marshal.ReleaseComObject(sheets);
                }
                if (book != null)
                {
                    Marshal.ReleaseComObject(book);
                }
                if (books != null)
                {
                    Marshal.ReleaseComObject(books);
                }
                if (app != null)
                {
                    Marshal.ReleaseComObject(app);
                }
            }


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

            //Excel.Application xlApp = new Excel.Application();



            //Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(Path.GetFullPath(@"TestData\" + fileName + ".xlsx"));
            //Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets.get_Item(worksheetNumber);

            //Excel.Range xlRange = xlWorksheet.UsedRange;



            //

            //double[] valueArray = (double[])xlRange.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault);

            /*
             *
             * // Cleanup
             * xlWorkbook.Close(false);
             * xlApp.Quit();
             *
             * // Manual disposal because of COM
             * while (Marshal.ReleaseComObject(xlApp) != 0) { }
             * while (Marshal.ReleaseComObject(xlWorkbook) != 0) { }
             * while (Marshal.ReleaseComObject(xlRange) != 0) { }
             *
             * xlApp = null;
             * xlWorkbook = null;
             * xlRange = null;
             *
             * GC.Collect();
             * GC.WaitForPendingFinalizers();
             *
             *
             */
        }
Esempio n. 28
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sched"></param>
        /// <param name="doc"></param>
        /// <param name="pt"></param>
        /// <param name="contentOnly"></param>
        public void AddScheduleData(string filePath, ViewSchedule sched, Document doc, PathType pt, bool contentOnly)
        {
            string docPath;

            if (doc.IsWorkshared)
            {
                docPath = ModelPathUtils.ConvertModelPathToUserVisiblePath(doc.GetWorksharingCentralModelPath());
            }
            else
            {
                docPath = doc.PathName;
            }

            string fullPath;

            if (pt == PathType.Absolute)
            {
                fullPath = filePath;
            }
            else
            {
                fullPath = PathExchange.GetFullPath(filePath, docPath);
            }

            // Get the file path
            excelFilePath = fullPath;
            if (!File.Exists(excelFilePath))
            {
                return;
            }


            // read the Excel file and create the schedule
            Excel.Application excelApp   = new Excel.Application();
            Excel.Workbook    workbook   = excelApp.Workbooks.Open(excelFilePath);
            Excel.Sheets      worksheets = workbook.Worksheets;
            worksheet = null;
            foreach (Excel.Worksheet ws in worksheets)
            {
                if (ws.Name.Trim() == sched.Name.Trim())
                {
                    worksheet = ws;
                }
            }

            if (worksheet == null)
            {
                return;
            }

            //TaskDialog.Show("Test", "Worksheet found");
            // Find the ThinLine linestyle
            CategoryNameMap lineSubCats   = doc.Settings.Categories.get_Item(BuiltInCategory.OST_Lines).SubCategories;
            ElementId       thinLineStyle = new ElementId(-1);
            ElementId       hairlineStyle = new ElementId(-1);
            ElementId       thinStyle     = new ElementId(-1);
            ElementId       mediumStyle   = new ElementId(-1);
            ElementId       thickStyle    = new ElementId(-1);

            foreach (Category style in lineSubCats)
            {
                if (style.Name == "Thin Lines")
                {
                    thinLineStyle = style.Id;
                }

                if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.hairlineInt)
                {
                    hairlineStyle = style.Id;
                }
                else if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.thinInt)
                {
                    thinStyle = style.Id;
                }
                else if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.mediumInt)
                {
                    mediumStyle = style.Id;
                }
                else if (style.GetGraphicsStyle(GraphicsStyleType.Projection).Id.IntegerValue == Properties.Settings.Default.thickInt)
                {
                    thickStyle = style.Id;
                }
            }

            if (hairlineStyle.IntegerValue == -1)
            {
                hairlineStyle = thinLineStyle;
            }
            if (thinStyle.IntegerValue == -1)
            {
                thinStyle = thinLineStyle;
            }
            if (mediumStyle.IntegerValue == -1)
            {
                mediumStyle = thinLineStyle;
            }
            if (thickStyle.IntegerValue == -1)
            {
                thickStyle = thinLineStyle;
            }



            // Find out how many rows and columns we need in the schedule
            Excel.Range rng = ActualUsedRange(worksheet);

            Excel.Range range       = rng;
            int         rowCount    = range.Rows.Count;
            int         columnCount = range.Columns.Count;

            // Get the schedule body to set the overall width
            TableSectionData bodyData = sched.GetTableData().GetSectionData(SectionType.Body);

            if (!contentOnly)
            {
                double schedWidth = range.Columns.Width;
                try
                {
                    bodyData.SetColumnWidth(0, (schedWidth * pointWidthInches) / 12);
                }
                catch { }
            }

            // Get the header body to create the necessary rows and columns
            TableSectionData headerData = sched.GetTableData().GetSectionData(SectionType.Header);

            if (!contentOnly)
            {
                //TaskDialog.Show("Test: ", "Row Count: " + rowCount.ToString() + "\nColumn Count:  " + columnCount.ToString());
                for (int i = 0; i < columnCount - 1; i++)
                {
                    headerData.InsertColumn(1);
                }
                for (int i = 0; i < rowCount - 1; i++)
                {
                    headerData.InsertRow(1);
                }

                for (int i = 1; i <= headerData.NumberOfColumns; i++)
                {
                    try
                    {
                        Excel.Range cell = worksheet.Cells[1, i];
                        headerData.SetColumnWidth(i - 1, (cell.Width * pointWidthInches) / 12);
                    }
                    catch { }
                }

                for (int i = 1; i <= headerData.NumberOfRows; i++)
                {
                    try
                    {
                        Excel.Range cell = worksheet.Cells[i, 1];

                        headerData.SetRowHeight(i - 1, (cell.Height * pointWidthInches) / 12);
                    }
                    catch { }
                }
            }



            List <TableMergedCell> mergedCells = new List <TableMergedCell>();
            int errorCount = 0;

            for (int i = 1; i <= headerData.NumberOfRows; i++)        // Iterate through rows of worksheet data
            {
                for (int j = 1; j <= headerData.NumberOfColumns; j++) // Iterate through columns of worksheet data
                {
                    // Get the current cell in the worksheet grid
                    Excel.Range cell = worksheet.Cells[i, j];

                    // If adjusting the formatting or adding content is not necessary,
                    // just update the text content. This is via a UI switch.
                    if (contentOnly)
                    {
                        try
                        {
                            headerData.SetCellText(i - 1, j - 1, cell.Text);
                            continue;
                        }
                        catch {
                            errorCount++;
                            continue;
                        }
                    }

                    Excel.Font          font       = cell.Font;
                    Excel.DisplayFormat dispFormat = cell.DisplayFormat;

                    TableCellStyle cellStyle = new TableCellStyle();
                    TableCellStyleOverrideOptions styleOverride = cellStyle.GetCellStyleOverrideOptions();

                    Excel.Border topEdge    = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeTop];
                    Excel.Border bottomEdge = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeBottom];
                    Excel.Border leftEdge   = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeLeft];
                    Excel.Border rightEdge  = cell.Borders.Item[Excel.XlBordersIndex.xlEdgeRight];

                    // Determine Bottom Edge Line Style
                    if (bottomEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone)
                    {
                        cellStyle.BorderBottomLineStyle = new ElementId(-1);
                    }
                    else
                    {
                        switch (bottomEdge.Weight)
                        {
                        case (int)Excel.XlBorderWeight.xlHairline:
                            cellStyle.BorderBottomLineStyle = hairlineStyle;
                            break;

                        case (int)Excel.XlBorderWeight.xlThin:
                            cellStyle.BorderBottomLineStyle = thinStyle;
                            break;

                        case (int)Excel.XlBorderWeight.xlMedium:
                            cellStyle.BorderBottomLineStyle = mediumStyle;
                            break;

                        case (int)Excel.XlBorderWeight.xlThick:
                            cellStyle.BorderBottomLineStyle = thickStyle;
                            break;
                        }
                    }


                    // Determine Top Edge Line Style
                    if (topEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone)
                    {
                        cellStyle.BorderTopLineStyle = new ElementId(-1);
                    }
                    else
                    {
                        switch (topEdge.Weight)
                        {
                        case (int)Excel.XlBorderWeight.xlHairline:
                            cellStyle.BorderTopLineStyle = hairlineStyle;
                            break;

                        case (int)Excel.XlBorderWeight.xlThin:
                            cellStyle.BorderTopLineStyle = thinStyle;
                            break;

                        case (int)Excel.XlBorderWeight.xlMedium:
                            cellStyle.BorderTopLineStyle = mediumStyle;
                            break;

                        case (int)Excel.XlBorderWeight.xlThick:
                            cellStyle.BorderTopLineStyle = thickStyle;
                            break;
                        }
                    }

                    // Determine Left Edge Line Style
                    if (leftEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone)
                    {
                        cellStyle.BorderLeftLineStyle = new ElementId(-1);
                    }
                    else
                    {
                        switch (leftEdge.Weight)
                        {
                        case (int)Excel.XlBorderWeight.xlHairline:
                            cellStyle.BorderLeftLineStyle = hairlineStyle;
                            break;

                        case (int)Excel.XlBorderWeight.xlThin:
                            cellStyle.BorderLeftLineStyle = thinStyle;
                            break;

                        case (int)Excel.XlBorderWeight.xlMedium:
                            cellStyle.BorderLeftLineStyle = mediumStyle;
                            break;

                        case (int)Excel.XlBorderWeight.xlThick:
                            cellStyle.BorderLeftLineStyle = thickStyle;
                            break;
                        }
                    }

                    // Determine Right Edge Line Style
                    if (rightEdge.LineStyle == (int)Excel.XlLineStyle.xlLineStyleNone)
                    {
                        cellStyle.BorderRightLineStyle = new ElementId(-1);
                    }
                    else
                    {
                        switch (rightEdge.Weight)
                        {
                        case (int)Excel.XlBorderWeight.xlHairline:
                            cellStyle.BorderRightLineStyle = hairlineStyle;
                            break;

                        case (int)Excel.XlBorderWeight.xlThin:
                            cellStyle.BorderRightLineStyle = thinStyle;
                            break;

                        case (int)Excel.XlBorderWeight.xlMedium:
                            cellStyle.BorderRightLineStyle = mediumStyle;
                            break;

                        case (int)Excel.XlBorderWeight.xlThick:
                            cellStyle.BorderRightLineStyle = thickStyle;
                            break;
                        }
                    }
                    // Border Styles are always overridden
                    styleOverride.BorderBottomLineStyle = true;
                    styleOverride.BorderTopLineStyle    = true;
                    styleOverride.BorderLeftLineStyle   = true;
                    styleOverride.BorderRightLineStyle  = true;

                    if (styleOverride.BorderBottomLineStyle || styleOverride.BorderTopLineStyle ||
                        styleOverride.BorderLeftLineStyle || styleOverride.BorderRightLineStyle)
                    {
                        styleOverride.BorderLineStyle = true;
                    }

                    // Get Background color and font name
                    System.Drawing.Color backGroundColor = System.Drawing.ColorTranslator.FromOle((int)cell.Interior.Color);
                    cellStyle.BackgroundColor     = new Color(backGroundColor.R, backGroundColor.G, backGroundColor.B);
                    styleOverride.BackgroundColor = true;
                    cellStyle.FontName            = cell.Font.Name;
                    styleOverride.Font            = true;

                    // Determine Horizontal Alignment
                    // If its not set to left, right or center, do not modify
                    switch (dispFormat.HorizontalAlignment)
                    {
                    case (int)Excel.XlHAlign.xlHAlignLeft:
                        cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Left;
                        styleOverride.HorizontalAlignment = true;
                        break;

                    case (int)Excel.XlHAlign.xlHAlignRight:
                        cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Right;
                        styleOverride.HorizontalAlignment = true;
                        break;

                    case (int)Excel.XlHAlign.xlHAlignGeneral:     // No specific style assigned
                        // Check if it's a number which is typically right aligned
                        if (double.TryParse(cell.Text, out double alignTest))
                        {
                            cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Right;
                            styleOverride.HorizontalAlignment = true;
                        }
                        else     // Assume text and left align it
                        {
                            cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Left;
                            styleOverride.HorizontalAlignment = true;
                        }
                        break;

                    case (int)Excel.XlHAlign.xlHAlignCenter:
                        cellStyle.FontHorizontalAlignment = HorizontalAlignmentStyle.Center;
                        styleOverride.HorizontalAlignment = true;
                        break;
                    }

                    // Get the vertical alignment of the cell
                    switch (dispFormat.VerticalAlignment)
                    {
                    case (int)Excel.XlVAlign.xlVAlignBottom:
                        cellStyle.FontVerticalAlignment = VerticalAlignmentStyle.Bottom;
                        styleOverride.VerticalAlignment = true;
                        break;

                    case (int)Excel.XlVAlign.xlVAlignTop:
                        cellStyle.FontVerticalAlignment = VerticalAlignmentStyle.Top;
                        styleOverride.VerticalAlignment = true;
                        break;

                    default:
                        cellStyle.FontVerticalAlignment = VerticalAlignmentStyle.Middle;
                        styleOverride.VerticalAlignment = true;
                        break;
                    }

                    switch (dispFormat.Orientation)
                    {
                    case (int)Excel.XlOrientation.xlUpward:
                        cellStyle.TextOrientation     = 9;
                        styleOverride.TextOrientation = true;
                        break;

                    case (int)Excel.XlOrientation.xlDownward:
                        cellStyle.TextOrientation     = -9;
                        styleOverride.TextOrientation = true;
                        break;

                    case (int)Excel.XlOrientation.xlVertical:
                        cellStyle.TextOrientation     = 9;
                        styleOverride.TextOrientation = true;
                        break;

                    default:
                        int rotation = (int)cell.Orientation;
                        if (rotation != (int)Excel.XlOrientation.xlHorizontal)
                        {
                            cellStyle.TextOrientation     = rotation;
                            styleOverride.TextOrientation = true;
                        }
                        break;
                    }


                    // Determine Text Size
                    double textSize = Convert.ToDouble(font.Size);
                    //double newTextSize = (textSize / 72) / 12;
                    cellStyle.TextSize     = textSize;
                    styleOverride.FontSize = true;

                    // Determine Font Color
                    System.Drawing.Color fontColor = System.Drawing.ColorTranslator.FromOle((int)font.Color);
                    cellStyle.TextColor     = new Color(fontColor.R, fontColor.G, fontColor.B);
                    styleOverride.FontColor = true;

                    // NOTES: Bold  is a bool
                    //        Italic is a bool
                    //        Underline is an int
                    cellStyle.IsFontBold      = (bool)font.Bold;
                    cellStyle.IsFontItalic    = (bool)font.Italic;
                    cellStyle.IsFontUnderline = (int)font.Underline == 2;
                    styleOverride.Bold        = true;
                    styleOverride.Italics     = true;
                    styleOverride.Underline   = true;

                    cellStyle.SetCellStyleOverrideOptions(styleOverride);

                    if (cell.MergeCells == true)
                    {
                        TableMergedCell tmc = new TableMergedCell()
                        {
                            Left   = j - 1,
                            Right  = cell.MergeArea.Columns.Count - 1,
                            Top    = i - 1,
                            Bottom = (i - 1) + cell.MergeArea.Rows.Count - 1
                        };

                        // Check to see if the cell is already merged...
                        bool alreadyMerged = false;
                        foreach (TableMergedCell mergedCell in mergedCells)
                        {
                            bool left   = false;
                            bool right  = false;
                            bool top    = false;
                            bool bottom = false;

                            if (i - 1 >= mergedCell.Top)
                            {
                                top = true;
                            }
                            if (i - 1 <= mergedCell.Bottom)
                            {
                                bottom = true;
                            }
                            if (j - 1 >= mergedCell.Left)
                            {
                                left = true;
                            }
                            if (j - 1 <= mergedCell.Right)
                            {
                                right = true;
                            }

                            //TaskDialog.Show("MergedCell", string.Format("Top: {0}\nBottom: {1}\nLeft: {2}\nRight: {3}\ni-1: {4}\nj-1: {5}", mergedCell.Top, mergedCell.Bottom, mergedCell.Left, mergedCell.Right, i - 1, j - 1));
                            if (top && bottom && left && right)
                            {
                                alreadyMerged = true;
                                break;
                            }
                        }


                        if (!alreadyMerged)
                        {
                            try
                            {
                                headerData.MergeCells(tmc);
                                headerData.SetCellText(i - 1, j - 1, cell.Text);
                                headerData.SetCellStyle(i - 1, j - 1, cellStyle);
                                j += cell.MergeArea.Columns.Count - 1;
                                mergedCells.Add(tmc);
                                //    TaskDialog.Show("Test", string.Format("This cell [{0},{1}] is merged.\nMerged Area: [{2},{3}]", cell.Row - 1, cell.Column - 1, cell.MergeArea.Rows.Count.ToString(), cell.MergeArea.Columns.Count.ToString()));
                            }
                            catch
                            {
                            }
                        }
                    }
                    else
                    {
                        //TaskDialog.Show("Non Merged", string.Format("This cell is not merged with any others [{0}, {1}]", i - 1, j - 1));
                        try
                        {
                            headerData.SetCellText(i - 1, j - 1, cell.Text);
                            headerData.SetCellStyle(i - 1, j - 1, cellStyle);
                        }
                        catch { }
                    }
                }
            }

            if (errorCount > 0)
            {
                TaskDialog.Show("Warning", "Error reloading content for " + errorCount.ToString() + " cells.\n\nConsider unchecking the \"Content Only\" checkbox and reloading the schedule to force it to rebuild.");
            }

            // Write the Schema to the project
            Schema schema = null;

            try
            {
                schema = Schema.Lookup(schemaGUID);
            }
            catch { }

            ModifySchemaData(schema, sched.Id);



            workbook.Close(false);
            Marshal.ReleaseComObject(worksheets);
            Marshal.ReleaseComObject(worksheet);
            Marshal.ReleaseComObject(workbook);
            excelApp.Quit();
            Marshal.ReleaseComObject(excelApp);
        }
Esempio n. 29
0
        //Возвращает Коллекцию записей из БД
        public ArrayList GetData(bool rez)
        {
            ArrayList listZapisey = new ArrayList();
            int       count       = 1;
            string    FileName    = LocalBase;
            object    rOnly       = true;
            object    SaveChanges = false;
            object    MissingObj  = System.Reflection.Missing.Value;

            Excel.Application app       = new Excel.Application();
            Excel.Workbooks   workbooks = null;
            Excel.Workbook    workbook  = null;
            Excel.Sheets      sheets    = null;
            workbooks = app.Workbooks;
            workbook  = workbooks.Open(FileName, MissingObj, rOnly, MissingObj, MissingObj,
                                       MissingObj, MissingObj, MissingObj, MissingObj, MissingObj,
                                       MissingObj, MissingObj, MissingObj, MissingObj, MissingObj);

            // Получение всех страниц докуента
            sheets = workbook.Sheets;

            foreach (Excel.Worksheet worksheet in sheets)
            {
                Excel.Range UsedRange = worksheet.UsedRange;
                Excel.Range urRows    = UsedRange.Rows;
                Excel.Range urColums  = UsedRange.Columns;

                int RowsCount    = urRows.Count;
                int ColumnsCount = urColums.Count;

                int    id           = 0;
                string name         = null;
                string description  = null;
                string sourse       = null;
                string target       = null;
                bool   privacy      = true;
                bool   integrity    = true;
                bool   availability = true;

                if (rez)
                {
                    Zapis zapis;
                    for (int i = 3; i <= RowsCount; i++)
                    {
                        for (int j = 1; j <= ColumnsCount; j++)
                        {
                            Excel.Range CellRange = UsedRange.Cells[i, j];
                            // Получение текста ячейки
                            string CellText = (CellRange == null || CellRange.Value2 == null) ? null :
                                              (CellRange as Excel.Range).Value2.ToString();
                            if (count == 1)
                            {
                                id = Convert.ToInt32(CellText);
                                count++;
                            }
                            else if (count == 2)
                            {
                                name = CellText;
                                count++;
                            }
                            else if (count == 3)
                            {
                                description = CellText;
                                count++;
                            }
                            else if (count == 4)
                            {
                                sourse = CellText;
                                count++;
                            }
                            else if (count == 5)
                            {
                                target = CellText;
                                count++;
                            }
                            else if (count == 6)
                            {
                                if (CellText == "0")
                                {
                                    privacy = false;
                                }
                                else
                                {
                                    privacy = true;
                                }

                                count++;
                            }
                            else if (count == 7)
                            {
                                if (CellText == "0")
                                {
                                    integrity = false;
                                }
                                else
                                {
                                    integrity = true;
                                }

                                count++;
                            }
                            else if (count == 8)
                            {
                                if (CellText == "0")
                                {
                                    availability = false;
                                }
                                else
                                {
                                    availability = true;
                                }
                                count++;
                            }
                            else if (count == 9)
                            {
                                count++;
                            }
                            else if (count == 10)
                            {
                                zapis = new Zapis {
                                    Id = id, Name = name, Description = description, Sourse = sourse, Target = target, Privacy = privacy, Integrity = integrity, Availability = availability
                                };
                                listZapisey.Add(zapis);
                                count        = 1;;
                                id           = 0;
                                name         = null;
                                description  = null;
                                sourse       = null;
                                target       = null;
                                privacy      = true;
                                integrity    = true;
                                availability = true;
                            }
                        }
                    }
                }
                else
                {
                    ZapisMin zapis;
                    for (int i = 3; i <= RowsCount; i++)
                    {
                        for (int j = 1; j <= ColumnsCount; j++)
                        {
                            Excel.Range CellRange = UsedRange.Cells[i, j];
                            // Получение текста ячейки
                            string CellText = (CellRange == null || CellRange.Value2 == null) ? null :
                                              (CellRange as Excel.Range).Value2.ToString();
                            if (count == 1)
                            {
                                description = "УБИ." + Convert.ToInt32(CellText);
                                count++;
                            }
                            else if (count == 2)
                            {
                                name = CellText;
                                count++;
                            }
                            else if (count == 3)
                            {
                                count++;
                            }
                            else if (count == 4)
                            {
                                count++;
                            }
                            else if (count == 5)
                            {
                                count++;
                            }
                            else if (count == 6)
                            {
                                count++;
                            }
                            else if (count == 7)
                            {
                                count++;
                            }
                            else if (count == 8)
                            {
                                count++;
                            }
                            else if (count == 9)
                            {
                                count++;
                            }
                            else if (count == 10)
                            {
                                zapis = new ZapisMin {
                                    Id = description, Name = name
                                };
                                listZapisey.Add(zapis);
                                count        = 1;;
                                id           = 0;
                                name         = null;
                                description  = null;
                                sourse       = null;
                                target       = null;
                                privacy      = true;
                                integrity    = true;
                                availability = true;
                            }
                        }
                    }
                }
            }
            return(listZapisey);
        }
Esempio n. 30
0
        // Create a new schedule
        public ViewSchedule CreateSchedule(string filePath, UIDocument uidoc)
        {
            ViewSchedule sched = null;

            _doc = uidoc.Document;

            if (uidoc.Document.IsWorkshared)
            {
                docPath = ModelPathUtils.ConvertModelPathToUserVisiblePath(uidoc.Document.GetWorksharingCentralModelPath());
            }
            else
            {
                docPath = uidoc.Document.PathName;
            }


            excelFilePath = filePath;
            if (File.Exists(excelFilePath))
            {
                // read the Excel file and create the schedule
                Excel.Application excelApp   = new Excel.Application();
                Excel.Workbook    workbook   = excelApp.Workbooks.Open(excelFilePath, ReadOnly: true);
                Excel.Sheets      worksheets = workbook.Worksheets;

                List <WorksheetObject> worksheetObjs = new List <WorksheetObject>();
                foreach (Excel.Worksheet ws in worksheets)
                {
                    WorksheetObject wo   = new WorksheetObject();
                    string          name = ws.Name;
                    wo.Name = name;
                    Excel.Range range = ws.UsedRange;
                    try
                    {
                        range.CopyPicture(Excel.XlPictureAppearance.xlPrinter, Excel.XlCopyPictureFormat.xlBitmap);
                        if (Clipboard.GetDataObject() != null)
                        {
                            IDataObject data = Clipboard.GetDataObject();
                            if (data.GetDataPresent(DataFormats.Bitmap))
                            {
                                System.Drawing.Image img = (System.Drawing.Image)data.GetData(DataFormats.Bitmap, true);
                                wo.Image = img;
                            }
                        }
                    }
                    catch { }
                    worksheetObjs.Add(wo);
                }

                // Pop up the worksheet form
                WorksheetSelectForm wsForm = new WorksheetSelectForm(worksheetObjs, this, _doc);


                // Revit version
                int version = Convert.ToInt32(uidoc.Application.Application.VersionNumber);

                // Get the Revit window handle
                IntPtr handle = IntPtr.Zero;
                if (version < 2019)
                {
                    handle = System.Diagnostics.Process.GetCurrentProcess().MainWindowHandle;
                }
                else
                {
                    handle = uidoc.Application.GetType().GetProperty("MainWindowHandle") != null
                        ? (IntPtr)uidoc.Application.GetType().GetProperty("MainWindowHandle").GetValue(uidoc.Application)
                        : IntPtr.Zero;
                }
                System.Windows.Interop.WindowInteropHelper wih = new System.Windows.Interop.WindowInteropHelper(wsForm)
                {
                    Owner = handle
                };

                //Show the Worksheet Select form
                wsForm.ShowDialog();
                if (wsForm.DialogResult.HasValue && wsForm.DialogResult.Value)
                {
                    foreach (Excel.Worksheet ws in worksheets)
                    {
                        if (ws.Name == selectedWorksheet.Name)
                        {
                            worksheet = ws;
                            break;
                        }
                    }
                }
                else
                {
                    worksheet = null;
                }

                if (worksheet != null)
                {
                    workSheetName = worksheet.Name;
                    Transaction trans = new Transaction(_doc, "Create Schedule");
                    trans.Start();

                    // Create the schedule
                    sched      = ViewSchedule.CreateSchedule(_doc, new ElementId(-1));
                    sched.Name = worksheet.Name;

                    // Add a single parameter for data, Assembly Code
                    ElementId       assemblyCodeId = new ElementId(BuiltInParameter.UNIFORMAT_DESCRIPTION);
                    ScheduleFieldId fieldId        = null;
                    foreach (SchedulableField sField in sched.Definition.GetSchedulableFields())
                    {
                        ElementId paramId = sField.ParameterId;

                        if (paramId == assemblyCodeId)
                        {
                            ScheduleField field = sched.Definition.AddField(sField);
                            fieldId = field.FieldId;
                            break;
                        }
                    }

                    if (fieldId != null && sched.Definition.GetFieldCount() > 0)
                    {
                        ScheduleDefinition schedDef = sched.Definition;

                        // Add filters to hide all elements in the schedule, ie make sure nothing shows up in the body.
                        ScheduleFilter filter0 = new ScheduleFilter(fieldId, ScheduleFilterType.Equal, "NO VALUES FOUND");
                        ScheduleFilter filter1 = new ScheduleFilter(fieldId, ScheduleFilterType.Equal, "ALL VALUES FOUND");
                        schedDef.AddFilter(filter0);
                        schedDef.AddFilter(filter1);

                        // Turn off the headers
                        schedDef.ShowHeaders = false;

                        // Fill out the schedule from Excel data
                        AddScheduleData(filePath, sched, _doc, PathType.Absolute, false);
                    }



                    if (linkFile)
                    {
                        AssignSchemaData(sched.Id, workSheetName, _doc);
                    }

                    trans.Commit();
                }

                //workbook.Close();
                workbook.Close(false);
                Marshal.ReleaseComObject(worksheets);
                if (worksheet != null)
                {
                    Marshal.ReleaseComObject(worksheet);
                }
                Marshal.ReleaseComObject(workbook);
                excelApp.Quit();
                Marshal.ReleaseComObject(excelApp);
            }
            return(sched);
        }
Esempio n. 31
0
        /// <summary>
        /// 英文版
        /// </summary>
        /// <param name="AppDT"></param>
        /// <param name="DetailDT"></param>
        /// <returns></returns>
        private bool CreateEnXLS(DataTable AppDT, DataTable DetailDT)
        {
            string  XLSName;
            decimal MoneyDiscont  = 1;
            decimal MaxMoney      = 0;
            string  strMoneyUnit  = "";
            double  douTotalPrice = 0.00;

            switch (AppDT.Rows[0]["MoneyUnit"].ToString())
            {
            case "1":
            default:
                MoneyDiscont = 1;
                break;

            case "2":
                MoneyDiscont = EmailControl.config.USrate;
                MaxMoney     = EmailControl.config.USD;
                strMoneyUnit = "US";
                break;

            case "3":
                MoneyDiscont = EmailControl.config.HKrate;
                MaxMoney     = EmailControl.config.HKD;
                strMoneyUnit = "HKD";
                break;

            case "4":
                MoneyDiscont = EmailControl.config.MOPrate;
                MaxMoney     = EmailControl.config.MOP;
                strMoneyUnit = "MOP";
                break;

            case "5":
                MoneyDiscont = EmailControl.config.SGDrate;
                MaxMoney     = EmailControl.config.SGD;
                strMoneyUnit = "SGD";
                break;

            case "6":
                MoneyDiscont = EmailControl.config.MYRrate;
                MaxMoney     = EmailControl.config.MYR;
                strMoneyUnit = "MYR";
                break;

            case "7":
                MoneyDiscont = EmailControl.config.GBPrate;
                MaxMoney     = EmailControl.config.GBP;
                strMoneyUnit = "GBP";
                break;

            case "8":
                MoneyDiscont = EmailControl.config.EURrate;
                MaxMoney     = EmailControl.config.EUR;
                strMoneyUnit = "EUR";
                break;

            case "9":
                MoneyDiscont = EmailControl.config.JPYrate;
                MaxMoney     = EmailControl.config.JPY;
                strMoneyUnit = "JPY";
                break;

            case "10":
                MoneyDiscont = EmailControl.config.TWDrate;
                MaxMoney     = EmailControl.config.TWD;
                strMoneyUnit = "TWD";
                break;
            }
            string strSalesDate;

            if (AppDT.Rows[0]["ApplicantsDate"].ToString() != null && AppDT.Rows[0]["ApplicantsDate"].ToString() != "")
            {
                strSalesDate = DateTime.Parse(AppDT.Rows[0]["SalesDate"].ToString()).ToShortDateString();
            }
            else
            {
                strSalesDate = DateTime.Now.ToShortDateString();
            }
            XLSName = System.IO.Directory.GetCurrentDirectory() + @"\templet\2015 Staff Purchase Form-in HK -templet .xls";
            Excel.Application app = new Excel.Application();
            app.DisplayAlerts = false;
            Excel.Workbooks  wbks = app.Workbooks;
            Excel._Workbook  _wbk = wbks.Add(XLSName);
            Excel.Sheets     shs  = _wbk.Sheets;
            Excel._Worksheet _wsh = (Excel._Worksheet)shs.get_Item(1);


            //写入
            _wsh.Cells[4, 3]   = AppDT.Rows[0]["ApplicantsName"].ToString();
            _wsh.Cells[4, 11]  = AppDT.Rows[0]["ApplicantsNo"].ToString();
            _wsh.Cells[5, 3]   = AppDT.Rows[0]["Location"].ToString();
            _wsh.Cells[5, 11]  = AppDT.Rows[0]["PurchaseLocation"].ToString();
            _wsh.Cells[22, 2]  = "For gift purchases of items with unit RSP exceeding " + strMoneyUnit + MaxMoney + ",  please complete the following :";
            _wsh.Cells[22, 11] = "    Total " + strMoneyUnit + ":";
            //_wsh.Cells[22, 12] = AppDT.Rows[0]["TotalPrice"].ToString();
            _wsh.Cells[24, 12] = AppDT.Rows[0]["TransNo"].ToString();
            //_wsh.Cells[23, 12] = strSalesDate;

            _wsh.Cells[31, 5] = AppDT.Rows[0]["ApplicantsName"].ToString();
            if (AppDT.Rows[0]["ApplicantsDate"].ToString() != null && AppDT.Rows[0]["ApplicantsDate"].ToString() != "" && AppDT.Rows[0]["ApplicantsName"].ToString() != "")
            {
                _wsh.Cells[31, 7] = DateTime.Parse(AppDT.Rows[0]["ApplicantsDate"].ToString()).ToShortDateString();
            }
            _wsh.Cells[32, 5] = AppDT.Rows[0]["ApprovalName2"].ToString();
            if (AppDT.Rows[0]["ApprovalDate2"].ToString() != null && AppDT.Rows[0]["ApprovalDate2"].ToString() != "" && AppDT.Rows[0]["ApprovalName2"].ToString() != "")
            {
                _wsh.Cells[32, 7] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate2"].ToString()).ToShortDateString();
            }
            _wsh.Cells[33, 5] = AppDT.Rows[0]["ApprovalName"].ToString();
            if (AppDT.Rows[0]["ApprovalDate"].ToString() != null && AppDT.Rows[0]["ApprovalDate"].ToString() != "" && AppDT.Rows[0]["ApprovalName"].ToString() != "")
            {
                _wsh.Cells[33, 7] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate"].ToString()).ToShortDateString();
            }
            _wsh.Cells[34, 5] = AppDT.Rows[0]["ApprovalName3"].ToString();
            if (AppDT.Rows[0]["ApprovalDate3"].ToString() != null && AppDT.Rows[0]["ApprovalDate3"].ToString() != "" && AppDT.Rows[0]["ApprovalName3"].ToString() != "")
            {
                _wsh.Cells[34, 7] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate3"].ToString()).ToShortDateString();
            }
            _wsh.Cells[35, 5] = AppDT.Rows[0]["StaffName"].ToString();
            _wsh.Cells[36, 2] = "Approved by HK Finance Manager:\r\n (If unit RSP exceeding " + strMoneyUnit + MaxMoney + ")";

            if (AppDT.Rows[0]["StaffName"].ToString() != "")
            {
                _wsh.Cells[35, 7]  = strSalesDate;
                _wsh.Cells[23, 12] = strSalesDate;
            }
            else
            {
                _wsh.Cells[35, 7]  = "";
                _wsh.Cells[23, 12] = "";
            }

            int j = 0;
            int i = 0;

            foreach (DataRow dr in DetailDT.Rows)
            {
                if (i < 6)
                {
                    _wsh.Cells[8 + 2 * i, 2] = dr["CodeID"].ToString();
                    _wsh.Cells[8 + 2 * i, 4] = dr["ItemID"].ToString();
                    _wsh.Cells[8 + 2 * i, 5] = dr["Detail"].ToString();
                    _wsh.Cells[8 + 2 * i, 7] = dr["Count"].ToString();
                    _wsh.Cells[8 + 2 * i, 8] = dr["Price"].ToString();
                    if (dr["SelforGift"].ToString() == "2")
                    {
                        _wsh.Cells[8 + 2 * i, 9] = "Gift";
                    }
                    else
                    {
                        _wsh.Cells[8 + 2 * i, 9] = "Self";
                    }

                    _wsh.Cells[8 + 2 * i, 10] = dr["ApprovalCount"].ToString();
                    _wsh.Cells[8 + 2 * i, 11] = dr["ApprovalDiscount"].ToString();
                    _wsh.Cells[8 + 2 * i, 12] = dr["FinalPrice"].ToString();
                    douTotalPrice             = douTotalPrice + Convert.ToDouble(dr["FinalPrice"].ToString());
                    i++;
                }
                if (j < 3)
                {
                    if (dr["SelforGift"].ToString() == "2")
                    {
                        _wsh.Cells[24 + j, 2] = dr["CodeID"].ToString();
                        _wsh.Cells[24 + j, 3] = dr["Recipient"].ToString();
                        _wsh.Cells[24 + j, 5] = dr["Relationship"].ToString();
                        _wsh.Cells[24 + j, 6] = dr["Reason"].ToString();
                        j++;
                    }
                }
            }
            _wsh.Cells[22, 12] = douTotalPrice;


            //保存
            string filePath = System.IO.Directory.GetCurrentDirectory() + @"\tempPDF\tempExcel.xls";

            app.AlertBeforeOverwriting = false;
            _wbk.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            //退出和释放
            _wbk.Close(null, null, null);
            wbks.Close();
            app.Quit();
            //释放掉多余的excel进程
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;

            return(true);
        }
        static void Main(string[] args)
        {
            Excel.Application app    = null;
            Excel.Workbooks   books  = null;
            Excel.Workbook    book   = null;
            Excel.Sheets      sheets = null;
            Excel.Worksheet   sheet  = null;

            string arquivo               = @"\\D5668m001e035\operacoes\Andre\201610_RELATORIO_ANALÍTICO_VENDAS__CREDSYSTEM_251020160123.xlsx";
            string arquivoCarga          = Path.Combine(@"\\D5668m001e035\OPERACOES\Andre\Carga\", Path.GetFileName(arquivo));
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + arquivoCarga + "; Extended Properties='Excel 12.0; HDR=NO'";
            string sqlConnectionString   = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbTeste_Carga;Data Source=D5668M001E035;Connect Timeout=0";

            // Copia o arquivo para o diretório de carga para preservar o original:
            File.Copy(arquivo, arquivoCarga, true);

            try
            {
                app   = new Excel.Application();
                books = app.Workbooks;
                book  = app.Workbooks.Open(arquivoCarga, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

                List <string> lista = new List <string>();

                // Pega os nomes das planilhas:
                foreach (Excel.Worksheet plan in book.Worksheets)
                {
                    lista.Add((string)plan.Name);
                }

                // Para cada planilha que não seja de TOTAIS:
                foreach (string nome in lista)
                {
                    if (!nome.Contains("TOTAIS"))
                    {
                        sheet = book.Worksheets.get_Item(nome);
                        // Deleta as 7 primeiras linhas que não fazem parte do processo:
                        Excel.Range range = sheet.get_Range("A1", "A7".ToString());
                        range.EntireRow.Delete(Excel.XlDirection.xlUp);
                        book.Save();

                        using (OleDbConnection oleDbConnection = new OleDbConnection(excelConnectionString))
                        {
                            oleDbConnection.Open();

                            using (DataTable dataTable = new DataTable())
                            {
                                using (OleDbCommand oleDbCommand = new OleDbCommand("SELECT * FROM [" + nome + "$]", oleDbConnection))
                                {
                                    using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(oleDbCommand))
                                    {
                                        oleDbDataAdapter.Fill(dataTable);
                                    }
                                }

                                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(sqlConnectionString))
                                {
                                    sqlBulkCopy.DestinationTableName = "tbBS_tmp_CredSystem";
                                    sqlBulkCopy.ColumnMappings.Add(0, "Semana");
                                    sqlBulkCopy.ColumnMappings.Add(1, "Data Inc Documento");
                                    sqlBulkCopy.ColumnMappings.Add(2, "Nome da Loja");
                                    sqlBulkCopy.ColumnMappings.Add(3, "Número Produto");
                                    sqlBulkCopy.ColumnMappings.Add(4, "Nome Campanha");
                                    sqlBulkCopy.ColumnMappings.Add(5, "Número de Parcelas");
                                    sqlBulkCopy.ColumnMappings.Add(6, "Data Emissão");
                                    sqlBulkCopy.ColumnMappings.Add(7, "Dia do Processamento");
                                    sqlBulkCopy.ColumnMappings.Add(8, "Número Apólice");
                                    sqlBulkCopy.ColumnMappings.Add(9, "Número Certificado");
                                    sqlBulkCopy.ColumnMappings.Add(10, "Prêmio Documento");
                                    sqlBulkCopy.ColumnMappings.Add(11, "Número Cliente");
                                    sqlBulkCopy.ColumnMappings.Add(12, "Chave Segurado").ToString(); // Se o campo for numérico e estiver perdendo formatação, utilizar o ToString()
                                    sqlBulkCopy.ColumnMappings.Add(13, "Nome Segurado");
                                    sqlBulkCopy.ColumnMappings.Add(14, "Número Campanha");
                                    sqlBulkCopy.ColumnMappings.Add(15, "Início de Vigência");
                                    sqlBulkCopy.ColumnMappings.Add(16, "Data Liberação");
                                    sqlBulkCopy.ColumnMappings.Add(17, "Fim Vigência");
                                    sqlBulkCopy.ColumnMappings.Add(18, "Número Forma Parcelamento");
                                    sqlBulkCopy.ColumnMappings.Add(19, "Número Ciclo Adm Cobrança");
                                    sqlBulkCopy.WriteToServer(dataTable);
                                }
                            }
                        }
                    }
                }

                book.Close(true, Type.Missing, Type.Missing);
                app.Quit();
                // Deleta o arquivo modificado ao final da carga:
                File.Delete(arquivoCarga);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                KillProcessoExcelEspecifico(arquivoCarga);
                //Console.ReadKey();
            }
        }
Esempio n. 33
0
        private bool CreateXLS(DataTable AppDT, DataTable DetailDT, string title)
        {
            double douTotalPrice = 0.00;
            string XLSName;

            XLSName = System.IO.Directory.GetCurrentDirectory() + @"\templet\2016国内员购申请表-模板.xls";
            Excel.Application app = new Excel.Application();
            app.DisplayAlerts = false;
            Excel.Workbooks  wbks = app.Workbooks;
            Excel._Workbook  _wbk = wbks.Add(XLSName);
            Excel.Sheets     shs  = _wbk.Sheets;
            Excel._Worksheet _wsh = (Excel._Worksheet)shs.get_Item(1);


            //写入
            _wsh.Cells[2, 2]   = title;
            _wsh.Cells[4, 3]   = AppDT.Rows[0]["ApplicantsName"].ToString();
            _wsh.Cells[4, 10]  = AppDT.Rows[0]["ApplicantsNo"].ToString();
            _wsh.Cells[5, 3]   = AppDT.Rows[0]["Location"].ToString();
            _wsh.Cells[5, 10]  = AppDT.Rows[0]["PurchaseLocation"].ToString();
            _wsh.Cells[22, 11] = AppDT.Rows[0]["TotalPrice"].ToString();
            _wsh.Cells[24, 11] = AppDT.Rows[0]["TransNo"].ToString();
            _wsh.Cells[23, 11] = DateTime.Now.ToShortDateString();

            _wsh.Cells[31, 4] = AppDT.Rows[0]["ApplicantsName"].ToString();
            _wsh.Cells[31, 6] = DateTime.Parse(AppDT.Rows[0]["ApplicantsDate"].ToString()).ToShortDateString();
            _wsh.Cells[32, 4] = AppDT.Rows[0]["ApprovalName2"].ToString();
            _wsh.Cells[32, 6] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate2"].ToString()).ToShortDateString();
            _wsh.Cells[33, 4] = AppDT.Rows[0]["ApprovalName"].ToString();
            _wsh.Cells[33, 6] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate"].ToString()).ToShortDateString();
            _wsh.Cells[34, 4] = AppDT.Rows[0]["ApprovalName3"].ToString();
            if (AppDT.Rows[0]["ApprovalName3"].ToString() != "")
            {
                _wsh.Cells[34, 6] = DateTime.Parse(AppDT.Rows[0]["ApprovalDate3"].ToString()).ToShortDateString();
            }
            else
            {
                _wsh.Cells[34, 6] = "";
            }
            int j = 0;
            int i = 0;

            foreach (DataRow dr in DetailDT.Rows)
            {
                if (i < 6)
                {
                    _wsh.Cells[9 + 2 * i, 2] = dr["CodeID"].ToString();
                    _wsh.Cells[9 + 2 * i, 3] = dr["ItemID"].ToString();
                    _wsh.Cells[9 + 2 * i, 4] = dr["Detail"].ToString();
                    _wsh.Cells[9 + 2 * i, 6] = dr["Count"].ToString();
                    _wsh.Cells[9 + 2 * i, 7] = dr["Price"].ToString();
                    if (dr["SelforGift"].ToString() == "2")
                    {
                        _wsh.Cells[9 + 2 * i, 8] = "送礼";
                    }
                    else
                    {
                        _wsh.Cells[9 + 2 * i, 8] = "自用";
                    }

                    _wsh.Cells[9 + 2 * i, 9]  = dr["ApprovalCount"].ToString();
                    _wsh.Cells[9 + 2 * i, 10] = dr["ApprovalDiscount"].ToString();
                    _wsh.Cells[9 + 2 * i, 11] = dr["FinalPrice"].ToString();
                    i++;
                    douTotalPrice += double.Parse(dr["FinalPrice"].ToString());
                }
                if (j < 3)
                {
                    if (dr["SelforGift"].ToString() == "2")
                    {
                        _wsh.Cells[24 + j, 2] = dr["CodeID"].ToString();
                        _wsh.Cells[24 + j, 3] = dr["Recipient"].ToString();
                        _wsh.Cells[24 + j, 4] = dr["Relationship"].ToString();
                        _wsh.Cells[24 + j, 5] = dr["Reason"].ToString();
                        j++;
                    }
                }
            }
            _wsh.Cells[22, 11] = douTotalPrice.ToString();

            //保存
            string filePath = System.IO.Directory.GetCurrentDirectory() + @"\tempPDF\tempExcel.xls";

            app.AlertBeforeOverwriting = false;
            _wbk.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            //退出和释放
            _wbk.Close(null, null, null);
            wbks.Close();
            app.Quit();
            //释放掉多余的excel进程
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            app = null;
            return(true);
        }
        public static void GravaRetorno()
        {
            #region Salva a lista de retorno no Excel
            Excel.Application xlsApp      = new Excel.Application();
            Excel.Workbook    xlsWorkbook = xlsApp.Workbooks.Open(Form1.path, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "", false, false, 0, false, false, false);
            Excel.Worksheet   newWorksheetErro;
            Excel.Worksheet   newWorksheetOk;

            try
            {
                //Add a worksheet to the workbook.
                newWorksheetErro = xlsApp.Worksheets.Add();
                newWorksheetOk   = xlsApp.Worksheets.Add();


                newWorksheetErro.Name = "WebServiceVipp - Erros";

                //Name the sheet.
                newWorksheetOk.Name = "WebServiceVipp - ok";

                //Get the Cells collection.
                Excel.Sheets xlsSheets = xlsWorkbook.Worksheets;

                //For que acessa todas as planilhas
                foreach (Excel.Worksheet xlsWorksheet in xlsSheets)
                {
                    //Acessa a aba da Planilha com o nome "WebServiceVipp"
                    if (xlsWorksheet.Name.Trim().Equals("WebServiceVipp - ok"))
                    {
                        Excel.Range xlsWorksRows = xlsWorksheet.Cells;
                        int         cont         = 0;
                        foreach (RetornoValida list in Retorno.lRetornoValida)
                        {
                            cont++;
                            xlsWorksRows.Item[cont, 1] = list.Observacao;
                            xlsWorksRows.Item[cont, 2] = list.Nome;
                            xlsWorksRows.Item[cont, 3] = list.Status;
                            xlsWorksRows.Item[cont, 4] = list.Etiqueta;
                        }
                    }

                    if (xlsWorksheet.Name.Trim().Equals("WebServiceVipp - Erros"))
                    {
                        Excel.Range xlsWorksRowss = xlsWorksheet.Cells;

                        int cont = 0;
                        foreach (RetornoInvalida list in Retorno.lRetornoInvalida)
                        {
                            cont++;
                            if (!list.Observacao.Equals(string.Empty) || !list.Observacao.Equals(null))
                            {
                                xlsWorksRowss.Item[cont, 1] = list.Observacao;
                                xlsWorksRowss.Item[cont, 2] = list.Nome;
                                xlsWorksRowss.Item[cont, 3] = list.Status;
                                xlsWorksRowss.Item[cont, 4] = list.Erro;
                            }
                        }
                    }
                }
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                MessageBox.Show("Não foi possivel gravar o retorno no arquivo processado, verifique se a planilha está bloqueada", "Erro", MessageBoxButton.OK, MessageBoxImage.Exclamation);
            }

            DateTime saveNow = DateTime.Now;
            string   sdf     = saveNow.ToString("dd-MM-yyyy_hh-mm");

            string nomeArquivo = Form1.caminhoArquivo + "\\" + Form1.nomeArquivo + " " + sdf + ".xlsx";
            xlsApp.ActiveWorkbook.SaveAs(nomeArquivo);
            xlsApp.Quit();
            #endregion
        }
Esempio n. 35
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);
        }
Esempio n. 36
0
        public static HashSet <CellUpdate> GetAllCells()
        {
            Excel.Worksheet currSheet = null;
            Excel.Sheets    sheets    = null;
            Excel.Workbook  activeWb  = null;

            HashSet <CellUpdate> myCells = new HashSet <CellUpdate>();

            //System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
            //stopwatch.Start();

            try
            {
                activeWb = GlobalFunctions.FindActiveWorkbook();

                sheets = activeWb.Worksheets;
                foreach (Excel.Worksheet ws in sheets)
                {
                    currSheet = ws;

                    String       wsName = currSheet.Name.ToUpper();
                    Int32        LastCol;
                    Int32        LastRow;
                    WorksheetExt wsBoundary;
                    if (GlobalFunctions.worksheetBounds.Keys.Contains(wsName))
                    {
                        wsBoundary = GlobalFunctions.worksheetBounds[wsName];
                    }
                    else
                    {
                        wsBoundary = new WorksheetExt(wsName);
                        GlobalFunctions.worksheetBounds.Add(wsName, wsBoundary);
                    }
                    LastCol = wsBoundary.LastCol;
                    LastRow = wsBoundary.LastRow;


                    Excel.Range range = ws.Range["A1", GlobalFunctions.GetExcelColumnName(LastCol) + LastRow];

                    // FOR LOOP ON VALUES
                    dynamic formulaArrayD = range.Formula;

                    GlobalFunctions.WaitForApplicationReady();
                    Object[,] formulaArray = formulaArrayD as Object[, ];
                    String[,] commentArray = GetComments(wsName, range, LastRow, LastCol);

                    if (formulaArray != null)
                    {
                        for (int i = 1; i <= LastRow; i++)
                        {
                            for (int j = 1; j <= LastCol; j++)
                            {
                                if (formulaArray[i, j] != null)
                                {
                                    if (formulaArray[i, j].ToString() != "")
                                    {
                                        CellUpdate uc = new CellUpdate(i, j, wsName, formulaArray[i, j].ToString(), Enums.CellChangeType.Value, "", DateTime.MinValue);
                                        myCells.Add(uc);
                                    }
                                }
                            }
                        }
                    }

                    if (commentArray != null)
                    {
                        for (int i = 1; i <= LastRow; i++)
                        {
                            for (int j = 1; j <= LastCol; j++)
                            {
                                if (commentArray[i, j] != null)
                                {
                                    if (commentArray[i, j] != "")
                                    {
                                        CellUpdate uc = new CellUpdate(i, j, wsName, commentArray[i, j].ToString(), Enums.CellChangeType.Comment, "", DateTime.MinValue);
                                        myCells.Add(uc);
                                    }
                                }
                            }
                        }
                    }


                    if (range != null)
                    {
                        Marshal.ReleaseComObject(range);
                    }
                    if (ws != null)
                    {
                        Marshal.ReleaseComObject(ws);
                    }
                }
                return(myCells);
            }
            catch (Exception ex)
            {
                if (ex.Message == "Incorrect ActiveWB Name")
                {
                    Globals.ThisAddIn.DisableSync();
                }
                throw;
            }
            finally
            {
                if (currSheet != null)
                {
                    Marshal.ReleaseComObject(currSheet);
                }
                if (sheets != null)
                {
                    Marshal.ReleaseComObject(sheets);
                }
                if (activeWb != null)
                {
                    Marshal.ReleaseComObject(activeWb);
                }
                //GlobalFunctions.InfoLog(String.Format("GetAllCells: {0}", stopwatch.Elapsed));
            }
        }
Esempio n. 37
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);
			}
		}
        /// <summary>
        /// Определить есть ли такой файл Excel и попытаться открыть его
        /// Если все успешно, то разблокировать элементы настройки выбора данных из Excel
        /// Заполнить варианты выбора для excelLayoutComboBox
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void FileNameChanged(object sender, EventArgs e)
        {
            CloseUsingExcel();

            excelWorkSheets = new List <Excel._Worksheet>();
            if (!String.IsNullOrEmpty(ExcelPath) && File.Exists(ExcelPath))
            {
                //Start Excel and get Application object.
                OXL = new Excel.Application();
                Excel.Workbooks workbooks = null;
                Excel.Sheets    sheets    = null;
                try
                {
                    workbooks = OXL.Workbooks;
                    string ext = System.IO.Path.GetExtension(ExcelPath);
                    //if (ext != "csv")
                    //{
                    OWB = workbooks.Open(ExcelPath, 0, true);
                    //}
                    //else
                    //{
                    //    OWB = workbooks.Open(ExcelPath, 0, true, Excel.XlFileFormat.xlCSVMSDOS,
                    //        Type.Missing, Type.Missing, Type.Missing, Type.Missing, ";");
                    //}

                    if (OWB != null)
                    {
                        sheets = OWB.Worksheets;
                        foreach (Excel._Worksheet sheet in sheets)
                        {
                            excelWorkSheets.Add(sheet);
                        }
                    }
                }
                catch { }
                finally
                {
                    if (workbooks != null)
                    {
                        //System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                    }

                    if (sheets != null)
                    {
                        //System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
                    }
                }
            }

            excelSheetComboBox.ItemsSource = excelWorkSheets;
            if (excelWorkSheets.Count > 0)
            {
                excelSheetComboBox.IsEnabled    = true;
                excelRowNumericUpDown.IsEnabled = true;
                excelColComboBox.IsEnabled      = true;
            }
            else
            {
                excelSheetComboBox.IsEnabled    = false;
                excelRowNumericUpDown.IsEnabled = false;
                excelColComboBox.IsEnabled      = false;
            }


            //Заполнить значения по умолчанию
            try
            {
                //Для выбранного листа
                if (!String.IsNullOrEmpty(defaultSheetName) && excelWorkSheets != null && excelWorkSheets.Count > 0)
                {
                    Excel._Worksheet sheet = excelWorkSheets.Find(s => s.Name.Equals(defaultSheetName));
                    excelSheetComboBox.SelectedItem = sheet;
                }
            }
            catch { }
        }
Esempio n. 39
0
        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;
        }
Esempio n. 40
0
        static void Main(string[] args)
        {
            if (checkPing())
            {
                FileLocation fileLocation = null;
                try
                {
                    using (StreamReader sr = new StreamReader(Directory.GetCurrentDirectory() + @"\Input\Config.json"))
                    {
                        fileLocation = JsonConvert.DeserializeObject <FileLocation>(sr.ReadToEnd());
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }

                Console.WriteLine("Connection Successful");
                string str   = "";
                var    Files = Directory.EnumerateFiles(fileLocation.SourceLocation, "*.*", SearchOption.AllDirectories)
                               .Where(s => s.EndsWith(".xlsx"));
                Excel.Application xlApp = new Excel.Application();
                bool refreshStatus      = true;
                foreach (string filePath in Files)
                {
                    DirectoryInfo  file = new DirectoryInfo(filePath);
                    Excel.Workbook wb   = xlApp.Workbooks.Open(file.FullName);
                    xlApp.DisplayAlerts = false;
                    xlApp.Visible       = false;
                    Console.WriteLine("Refreshing : " + file.FullName);
                    Excel.Sheets excelSheets = wb.Worksheets;
                    foreach (Excel.Worksheet workSheet in excelSheets)
                    {
                        Console.WriteLine("SheetName: " + workSheet.Name);
                        Excel.PivotTables pivotTables = workSheet.PivotTables();
                        if (pivotTables.Count > 0)
                        {
                            foreach (Excel.PivotTable pivotTable in pivotTables)
                            {
                                Console.WriteLine(pivotTable.RefreshDate);
                                int attempts = 0;
                                Console.WriteLine("Refreshing " + pivotTable.Name);
                                for (; attempts < 3; attempts++)
                                {
                                    refreshStatus = pivotTable.RefreshTable();
                                    Console.WriteLine(refreshStatus);
                                    if (refreshStatus == true)
                                    {
                                        break;
                                    }
                                    else
                                    {
                                        Console.WriteLine("Failed!! Reattempting...");
                                    }
                                }
                                if (attempts == 3)
                                {
                                    Console.WriteLine("All attempts exhausted! Failed.");
                                }
                                else
                                {
                                    Console.WriteLine(pivotTable.RefreshDate);
                                }
                            }
                        }
                        else
                        {
                            Console.WriteLine("No Pivot Found in the Sheet!!");
                        }
                    }
                    if (refreshStatus)
                    {
                        Console.WriteLine("Refreshed :" + file.Name);
                        Console.WriteLine("Saving " + file.Name);
                        wb.SaveAs(fileLocation.DestinationLocation + file.Name);
                    }
                    wb.Close();
                    xlApp.Quit();
                    str = str + ", " + file.Name;
                }
                Console.WriteLine("Press Enter to Continue");
                Console.ReadLine();
            }
            else
            {
                Console.WriteLine("Connection Unsuccessful");
            }
        }
 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);
 }
Esempio n. 42
0
        static OutputUnits makePerfMonGraphs(string file)
        {
            Excel.Application  excelApp  = null;
            Excel.Workbook     workbook  = null;
            Excel.Sheets       sheets    = null;
            Excel.Worksheet    dataSheet = null;
            Excel.Worksheet    newSheet  = null;
            Excel.ChartObjects xlChart   = null;
            Excel.Range        dataY     = null;
            Excel.Chart        memChart  = null;
            Excel.Chart        diskChart = null;
            Excel.Chart        cpuChart  = null;
            Excel.Chart        netChart  = null;
            Excel.Axis         xAxis     = null;
            OutputUnits        csData    = null;
            bool   leaking  = false;
            bool   highcpu  = false;
            string exitFile = "";

            try {
                excelApp = new Excel.Application();
                string dir = file.Substring(0, file.LastIndexOf("\\") + 1);
                string fm  = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1);
                workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",",
                                                   true, false, 0, false, false, false);

                sheets         = workbook.Sheets;
                dataSheet      = sheets[1];
                dataSheet.Name = "data";
                dataSheet.get_Range("A2:A2", Type.Missing).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);//garbage row
                newSheet      = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
                newSheet.Name = "results";
                xlChart       = (Excel.ChartObjects)newSheet.ChartObjects(Type.Missing);

                memChart  = xlChart.Add(20, 100, 450, 175).Chart;
                diskChart = xlChart.Add(20, 280, 450, 175).Chart;
                cpuChart  = xlChart.Add(500, 100, 450, 175).Chart;
                netChart  = xlChart.Add(500, 280, 450, 175).Chart;
                int rowTotal = dataSheet.UsedRange.Rows.Count;
                int colTotal = dataSheet.UsedRange.Columns.Count;
                dataSheet.get_Range("A2", "A" + rowTotal).NumberFormat = "m/d/yyyy h:mm";
                string ttime     = dataSheet.Cells[2, 1].Value.ToString();
                Array  availMem  = (System.Array)dataSheet.get_Range("C2", "C" + rowTotal).Value;
                Array  cpuTotal  = (System.Array)dataSheet.get_Range("D2", "D" + rowTotal).Value;
                Array  diskTotal = (System.Array)dataSheet.get_Range("B2", "B" + rowTotal).Value;

                dataSheet.Cells[1, colTotal + 1] = "Total LAN (Bytes Total/Sec)";
                double[] netties = new double[rowTotal - 1];
                for (int i = 2; i <= rowTotal; i++)
                {
                    if (colTotal > 5)
                    {
                        Array  netLine      = (System.Array)dataSheet.get_Range(xlStr(5) + i, xlStr(colTotal) + i).Value;
                        double netLineTotal = 0;
                        for (int j = 1; j <= netLine.Length; j++)
                        {
                            netLineTotal += Convert.ToDouble(netLine.GetValue(1, j));
                        }
                        netties[i - 2] = netLineTotal;
                        dataSheet.Cells[i, colTotal + 1] = netLineTotal;
                    }
                    else
                    {
                        dataSheet.Cells[i, colTotal + 1] = "0";
                    }
                }

                #region BuildCounters
                double[] mems  = ColToDouble(availMem);
                double[] cpus  = ColToDouble(cpuTotal);
                double[] disks = ColToDouble(diskTotal);
                //netties[]
                double   avgCPUs = cpus.Average();
                PCounter CPU     = new PCounter(avgCPUs, cpus.Max(), cpus.Min());
                PCounter MEM     = new PCounter(mems.Average(), mems.Max(), mems.Min());
                PCounter DISK    = new PCounter(disks.Average(), disks.Max(), disks.Min());
                PCounter NETS    = new PCounter(netties.Average(), netties.Max(), netties.Min());
                if (avgCPUs > 40)
                {
                    highcpu = true;
                }
                #endregion

                #region leakCheck
                double[]      eqMB    = new double[2];
                List <double> memList = new List <double>();
                int           cX      = availMem.Length;
                for (int i = 1; i < rowTotal - 1; i++)
                {
                    memList.Add(Convert.ToDouble(availMem.GetValue(i, 1)));
                }
                eqMB = LeastSquares(memList);
                double        stdD1    = StandardDev(memList);
                List <double> memList2 = sigma(memList, stdD1, eqMB);
                cX   = memList2.Count();
                eqMB = LeastSquares(memList2);
                double        stdD2    = StandardDev(memList2) * 1.2;
                List <double> memList3 = sigma(memList2, stdD2, eqMB);
                eqMB = LeastSquares(memList3);

                if (eqMB[0] < 0)
                {
                    leaking = true;
                    newSheet.get_Range("E4", Type.Missing).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Tomato);
                }
                #endregion

                #region formatting
                string lan = xlStr(colTotal + 1);
                newSheet.get_Range("A1", Type.Missing).EntireColumn.ColumnWidth         = 12;
                newSheet.get_Range("A1", Type.Missing).EntireColumn.HorizontalAlignment = XlHAlign.xlHAlignRight;
                newSheet.get_Range("A2", Type.Missing).EntireRow.HorizontalAlignment    = XlHAlign.xlHAlignCenter;
                newSheet.Cells[4, 5] = eqMB[0];
                newSheet.Cells[2, 2] = "Avg";
                newSheet.Cells[2, 3] = "Min";
                newSheet.Cells[2, 4] = "Max";
                newSheet.Cells[2, 5] = "Slope(3Sigma)";
                newSheet.Cells[3, 1] = "CPU";
                newSheet.Cells[3, 2] = "=AVERAGE(data!D:D)";
                newSheet.Cells[3, 3] = "=MIN(data!D:D)";
                newSheet.Cells[3, 4] = "=MAX(data!D:D)";
                newSheet.Cells[4, 1] = "Avail.RAM";
                newSheet.Cells[4, 2] = "=AVERAGE(data!C:C)";
                newSheet.Cells[4, 3] = "=MIN(data!C:C)";
                newSheet.Cells[4, 4] = "=MAX(data!C:C)";
                newSheet.Cells[5, 1] = "LAN Usage";
                newSheet.Cells[5, 2] = "=AVERAGE(data!" + lan + ":" + lan + ")";
                newSheet.Cells[5, 3] = "=MIN(data!" + lan + ":" + lan + ")";
                newSheet.Cells[5, 4] = "=MAX(data!" + lan + ":" + lan + ")";
                newSheet.Cells[6, 1] = "Disk Usage";
                newSheet.Cells[6, 2] = "=AVERAGE(data!B:B)";
                newSheet.Cells[6, 3] = "=MIN(data!B:B)";
                newSheet.Cells[6, 4] = "=MAX(data!B:B)";

                #endregion

                #region memChart
                dataY = dataSheet.Range["C1", "C" + rowTotal];
                memChart.SetSourceData(dataY, Type.Missing);
                memChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
                memChart.HasLegend = false;
                xAxis = (Axis)memChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
                xAxis.MaximumScaleIsAuto = false;
                xAxis.MaximumScale       = rowTotal + 1;
                xAxis.MinimumScaleIsAuto = false;
                xAxis.MinimumScale       = 0;
                #endregion

                #region diskChart
                dataY = dataSheet.Range["B1", "B" + rowTotal];
                diskChart.SetSourceData(dataY, Type.Missing);
                diskChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
                diskChart.HasLegend = false;
                xAxis = (Axis)diskChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
                xAxis.MaximumScaleIsAuto = false;
                xAxis.MaximumScale       = rowTotal + 1;
                xAxis.MinimumScaleIsAuto = false;
                xAxis.MinimumScale       = 0;
                #endregion

                #region cpuChart
                dataY = dataSheet.Range["D1", "D" + rowTotal];
                cpuChart.SetSourceData(dataY, Type.Missing);
                cpuChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
                cpuChart.HasLegend = false;
                xAxis = (Axis)cpuChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
                xAxis.MaximumScaleIsAuto = false;
                xAxis.MaximumScale       = rowTotal + 1;
                xAxis.MinimumScaleIsAuto = false;
                xAxis.MinimumScale       = 0;
                #endregion

                #region netChart
                dataY = dataSheet.Range[xlStr(colTotal + 1) + "1", xlStr(colTotal + 1) + rowTotal];
                netChart.SetSourceData(dataY, Type.Missing);
                netChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
                netChart.HasLegend = false;
                xAxis = (Axis)netChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
                xAxis.MaximumScaleIsAuto = false;
                xAxis.MaximumScale       = rowTotal + 1;
                xAxis.MinimumScaleIsAuto = false;
                xAxis.MinimumScale       = 0;
                #endregion

                string host = Path.GetFileNameWithoutExtension(dir + fm);
                csData   = new OutputUnits(host, ttime + " time chunks: " + (rowTotal - 1), CPU, MEM, NETS, DISK, leaking, highcpu);
                exitFile = dir + fm;
                excelApp.DisplayAlerts = false;
                workbook.SaveAs(@exitFile, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);
                workbook.Close(true, Type.Missing, Type.Missing);
                excelApp.Quit();

                //releaseObject(sC);
                //releaseObject(myChart);
            } catch {
                Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?");
                //using (StreamWriter outfile = File.AppendText("output.txt")) {
                //  outfile.WriteLine("Did have issues interacting with Excel on " + file);
                //}
            } finally {
                releaseObject(xAxis);
                releaseObject(dataY);
                releaseObject(diskChart);
                releaseObject(memChart);
                releaseObject(cpuChart);
                releaseObject(netChart);
                releaseObject(xlChart);
                releaseObject(newSheet);
                releaseObject(dataSheet);
                releaseObject(sheets);
                releaseObject(workbook);
                releaseObject(excelApp);
            }
            return(csData);
        }
Esempio n. 43
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;
        }
Esempio n. 44
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] = "余额";
     }
 }
Esempio n. 45
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;
                }

            }
        }
Esempio n. 46
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();

        }
Esempio n. 47
0
        static void makeGraphs(string file)
        {
            Excel.Application excelApp   = null;
            Excel.Workbook    workbook   = null;
            Excel.Sheets      sheets     = null;
            Excel.Worksheet   dataSheet  = null;
            Excel.Worksheet   newSheet   = null;
            Excel.Worksheet   chartSheet = null;
            Excel.Range       range      = null;
            Excel.Range       dataR      = null;
            int rowC = 0;

            try {
                excelApp = new Excel.Application();
                string dir = file.Substring(0, file.LastIndexOf("\\") + 1);
                string fm  = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1);
                workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",",
                                                   true, false, 0, false, false, false);

                sheets          = workbook.Sheets;
                dataSheet       = sheets[1];
                dataSheet.Name  = "data";
                newSheet        = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
                newSheet.Name   = "table";
                chartSheet      = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
                chartSheet.Name = "graph";
                Excel.ChartObjects xlChart = (Excel.ChartObjects)chartSheet.ChartObjects(Type.Missing);
                dataR = dataSheet.UsedRange;
                rowC  = dataR.Rows.Count;

                range = newSheet.get_Range("A1");
                PivotCaches pCs = workbook.PivotCaches();
                PivotCache  pC  = pCs.Create(XlPivotTableSourceType.xlDatabase, dataR, Type.Missing);
                PivotTable  pT  = pC.CreatePivotTable(TableDestination: range, TableName: "PivotTable1");
                PivotField  fA  = pT.PivotFields("Time");
                PivotField  fB  = pT.PivotFields("Command");
                fA.Orientation = XlPivotFieldOrientation.xlRowField;
                fA.Position    = 1;
                fB.Orientation = XlPivotFieldOrientation.xlColumnField;
                fB.Position    = 1;
                pT.AddDataField(pT.PivotFields("%CPU"), "Sum of %CPU", XlConsolidationFunction.xlSum);

                ChartObject pChart = (Excel.ChartObject)xlChart.Add(0, 0, 650, 450);
                Chart       chartP = pChart.Chart;
                chartP.SetSourceData(pT.TableRange1, Type.Missing);
                chartP.ChartType       = XlChartType.xlLine;
                excelApp.DisplayAlerts = false;
                workbook.SaveAs(@dir + fm, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);
                workbook.Close(true, Type.Missing, Type.Missing);
                excelApp.Quit();
            } catch {
                Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?");
                using (StreamWriter outfile = File.AppendText("output.txt")) {
                    outfile.WriteLine("Did have issues interacting with Excel on " + file);
                }
            } finally {
                /*Excel.Application excelApp = null;
                 * Excel.Workbook workbook = null;
                 * Excel.Sheets sheets = null;
                 * Excel.Worksheet dataSheet = null;
                 * Excel.Worksheet newSheet = null;
                 * Excel.Worksheet chartSheet = null;
                 * Excel.Range range = null;
                 * Excel.Range dataR = null;*/
                releaseObject(dataR);
                releaseObject(range);
                releaseObject(chartSheet);
                releaseObject(newSheet);
                releaseObject(dataSheet);
                releaseObject(sheets);
                releaseObject(workbook);
                releaseObject(excelApp);
            }
        }
Esempio n. 48
0
        /// <summary> Displays a 2DArray in excel. </summary>
        /// <remarks> Tplateus, 3/01/2018. </remarks>
        /// <param name="ListMatrix"> The 2DArray of entries. </param>
        private void DisplayInExcel(string[,] ListMatrix, string[,] TableMatrix)
        {
            Excel.Application app     = null;
            Excel.Application openApp = null;

            Excel.Workbooks books     = null;
            Excel.Workbooks openBooks = null;

            Excel.Workbook book = null;

            Excel.Sheets    sheets     = null;
            Excel.Worksheet sheetList  = null;
            Excel.Worksheet sheetTable = null;

            Excel.Range range = null;
            Excel.Range rows  = null;
            Excel.Range cols  = null;

            List <string> errors = new List <string>();

            try
            {
                try
                {
                    openApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");

                    openBooks = openApp.Workbooks;

                    for (int i = 1; i <= openBooks.Count; i++)
                    {
                        Excel.Workbook openBook = openBooks.Item[i];
                        Console.WriteLine(openBook.FullName);
                        if (openBook.FullName == outputfile)
                        {
                            errors.Add("The output file is already opened. Please close this file or choose a different output file.");
                        }

                        Marshal.ReleaseComObject(openBook);
                    }
                }
                catch
                {
                    Console.WriteLine("No active Excel instance found. Starting an Excel instance...");
                }
                finally
                {
                    if (openBooks != null)
                    {
                        Marshal.ReleaseComObject(openBooks);
                    }
                    if (openApp != null)
                    {
                        Marshal.ReleaseComObject(openApp);
                    }
                }


                app = new Excel.Application();

                if (app == null)
                {
                    errors.Add("No working instance of Excel could be found on this computer.");
                }

                if (errors.Count == 0)
                {
                    app.SheetsInNewWorkbook = 2;
                    app.DisplayAlerts       = false;

                    books      = app.Workbooks;
                    book       = books.Add();
                    sheets     = book.Sheets;
                    sheetList  = sheets.Item[1];
                    sheetTable = sheets.Item[2];

                    range = sheetList.Cells[1, 1];

                    sheetList.Name  = "List parameters";
                    sheetTable.Name = "Table parameters";

                    #region format Sheet
                    //int rowCount = Matrix.GetLength(0);
                    //int columnCount = Matrix.GetLength(1);

                    //range = range.Resize[rowCount, columnCount];

                    //range.Value = Matrix;

                    //rows = range.Rows;

                    //cols = range.Columns;

                    ////Set column widths to 300 pixels for each column in range.
                    //for (int iCol = 1; iCol <= columnCount; iCol++) //COMObject has a 1-based index.
                    //{
                    //    Excel.Range column = cols[iCol];
                    //    column.ColumnWidth = 32.56; //Translates to 300 pixels. Verify in Excel.

                    //    if (column != null) Marshal.ReleaseComObject(column);
                    //}

                    //for (int iRow = 2; iRow <= rowCount; iRow++) // COMObject index starts at 1, not 0. First row is ignored (headers) => iRow = 2
                    //{
                    //    Excel.Range row = rows.Item[iRow];

                    //    object[,] objRow = row.Value2;
                    //    List<string> listRow = objRow.Cast<string>().ToList(); //To utilize methods like findAll, the array is transformed to a list.

                    //    for (int i = nParams; i < listRow.Count; i++) //First 3 columns are ignored since they dont have values (only ids).
                    //    {
                    //        string cellValue = listRow[i];
                    //        List<string> allSameValues = listRow.FindAll(x => x == cellValue); //Search all cells with value == current cell value. If all values in the row are the same, its count should be the listRowCount -3 (ids).
                    //        List<string> allValuesWithNotFound = listRow.FindAll(x => x == "!NOT_FOUND"); //!NOT_FOUND value is manually added in the 'TransformInto2DArray' method.

                    //        if (allValuesWithNotFound.Count == 0)
                    //        {
                    //            if (allSameValues.Count < listRow.Count - nParams)
                    //            {
                    //                //Console.WriteLine(row.Cells[iRow, listRow.Count].Value2);
                    //                Excel.Range value = row.Cells[1, i + 1];
                    //                //Console.WriteLine(value.Value2);
                    //                value.Interior.Color = Color.Red;
                    //                value.Font.Color = Color.White;

                    //                if (value != null) Marshal.ReleaseComObject(value);
                    //            }
                    //        }
                    //        if (cellValue == "!NOT_FOUND")
                    //        {
                    //            Excel.Range cell = row.Cells[1, i + 1];
                    //            cell.Interior.Color = Color.Orange;
                    //            cell.Value2 = "";

                    //            if (cell != null) Marshal.ReleaseComObject(cell);
                    //        }
                    //    }

                    //    if (row != null) Marshal.ReleaseComObject(row);
                    //}
                    #endregion

                    FormatSheet(4, ListMatrix, sheetList);
                    FormatSheet(5, TableMatrix, sheetTable);

                    object missing = System.Reflection.Missing.Value;
                    book.SaveAs(outputfile, Excel.XlFileFormat.xlWorkbookDefault, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
                    book.Close(true, missing, missing);

                    app.SheetsInNewWorkbook = 3;
                    app.DisplayAlerts       = true;
                    app.Quit();

                    string message = "The file '" + OutputFilenameBox.Text + ".xlsx' was succesfully placed in " + OutputDirBox.Text + ".";
                    MessageBox.Show(message, "Succes!");
                }
                else
                {
                    DisplayErrors(errors);
                }
            }
            finally
            {
                if (cols != null)
                {
                    Marshal.ReleaseComObject(cols);
                }
                if (rows != null)
                {
                    Marshal.ReleaseComObject(rows); Console.WriteLine("COMObject 'rows' released.");
                }
                if (range != null)
                {
                    Marshal.ReleaseComObject(range); Console.WriteLine("COMObject 'range' released.");
                }
                if (sheetTable != null)
                {
                    Marshal.ReleaseComObject(sheetTable); Console.WriteLine("COMObject 'sheetTable' released.");
                }
                if (sheetList != null)
                {
                    Marshal.ReleaseComObject(sheetList); Console.WriteLine("COMObject 'sheetList' released.");
                }
                if (sheets != null)
                {
                    Marshal.ReleaseComObject(sheets); Console.WriteLine("COMObject 'sheets' released.");
                }
                if (book != null)
                {
                    Marshal.ReleaseComObject(book); Console.WriteLine("COMObject 'book' released.");
                }
                if (books != null)
                {
                    Marshal.ReleaseComObject(books); Console.WriteLine("COMObject 'books' released.");
                }
                if (app != null)
                {
                    Marshal.ReleaseComObject(app); Console.WriteLine("COMObject 'app' released.");
                }
            }
        }
Esempio n. 49
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!");
        }
Esempio n. 50
0
        private void save_excel_Click(object sender, EventArgs e)
        {
            excelWorkbook.Close(null, null, null);
            excelApp.Quit();

            Marshal.ReleaseComObject(excelApp);
            excelApp = null;
            GC.Collect();
            GC.WaitForPendingFinalizers();

            sv_name = save_name.Text;

            if (checkBox_multiple_run.Checked)
            {
                excelApp1         = new xc.Application();
                excelApp1.Visible = true;
                excelWorkbook1    = excelApp1.Workbooks.Open(Open_for_save.FileName);
                excelSheets1      = excelWorkbook1.Worksheets;
                excelWorkSheet1   = excelSheets1.get_Item("Sheet1");
                excelCell1        = excelWorkSheet1.UsedRange;



                for (int cc = 1; cc <= 16383; cc++)
                {
                    ref_save_column = Convert.ToString(excelCell1.Cells[2, cc].Value2);

                    if (ref_save_column == null)
                    {
                        start_column_save                = cc;
                        excelWorkSheet1.Cells[1, cc]     = sv_name;
                        excelWorkSheet1.Cells[2, cc]     = "Frame No.";
                        excelWorkSheet1.Cells[2, cc + 1] = "Flash period + Dark period";
                        excelWorkSheet1.Cells[2, cc + 2] = "Flash period";
                        break;
                    }
                }


                for (int bb = 1; bb <= box.GetUpperBound(0); bb++)
                {
                    excelWorkSheet1.Cells[bb + 2, start_column_save]     = (bb - 1) + 1;
                    excelWorkSheet1.Cells[bb + 2, start_column_save + 1] = box[bb - 1, 0];
                    excelWorkSheet1.Cells[bb + 2, start_column_save + 2] = box[bb - 1, 1];
                }

                excelWorkbook1.Save();
                excelWorkbook1.Close(null, null, null);


                excelApp1.Quit();
                Marshal.ReleaseComObject(excelApp1);
                excelApp1 = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();

                Array.Clear(box, 1, 51);
                Array.Clear(box, 2, 2);
                foreach (var series in show_freq_chart.Series)
                {
                    series.Points.Clear();
                }
                Application.Idle += multiple_file_run;
                Application.Idle -= save_excel_Click;
            }

            else
            {
                if (Open_for_save.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    excelApp1         = new xc.Application();
                    excelApp1.Visible = true;
                    excelWorkbook1    = excelApp1.Workbooks.Open(Open_for_save.FileName);
                    excelSheets1      = excelWorkbook1.Worksheets;
                    excelWorkSheet1   = excelSheets1.get_Item("Sheet1");
                    excelCell1        = excelWorkSheet1.UsedRange;



                    for (int cc = 1; cc <= 16383; cc++)
                    {
                        ref_save_column = Convert.ToString(excelCell1.Cells[2, cc].Value2);

                        if (ref_save_column == null)
                        {
                            start_column_save                = cc;
                            excelWorkSheet1.Cells[1, cc]     = sv_name;
                            excelWorkSheet1.Cells[2, cc]     = "Frame No.";
                            excelWorkSheet1.Cells[2, cc + 1] = "Flash period + Dark period";
                            excelWorkSheet1.Cells[2, cc + 2] = "Flash period";
                            break;
                        }
                    }


                    for (int bb = 1; bb <= box.GetUpperBound(0); bb++)
                    {
                        excelWorkSheet1.Cells[bb + 2, start_column_save]     = (bb - 1) + 1;
                        excelWorkSheet1.Cells[bb + 2, start_column_save + 1] = box[bb - 1, 0];
                        excelWorkSheet1.Cells[bb + 2, start_column_save + 2] = box[bb - 1, 1];
                    }

                    excelWorkbook1.Save();
                    excelWorkbook1.Close(null, null, null);


                    excelApp1.Quit();


                    Marshal.ReleaseComObject(excelApp1);
                    excelApp1 = null;
                    GC.Collect();
                    GC.WaitForPendingFinalizers();



                    Array.Clear(box, 1, 51);
                    Array.Clear(box, 2, 2);
                    foreach (var series in show_freq_chart.Series)
                    {
                        series.Points.Clear();
                    }
                }

                Application.Idle -= save_excel_Click;
            }
        }
Esempio n. 51
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;
        }
Esempio n. 52
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));
 }
Esempio n. 53
0
        static void Main(string[] args)
        {
            String[] Color        = new String[57];
            int      file_counter = 0;

            Color[1]  = "#000000";
            Color[2]  = "#FFFFFF";
            Color[3]  = "#FF0000";
            Color[4]  = "#00FF00";
            Color[5]  = "#0000FF";
            Color[6]  = "#FFFF00";
            Color[7]  = "#FF00FF";
            Color[8]  = "#00FFFF";
            Color[9]  = "#800000";
            Color[10] = "#008000";
            Color[11] = "#000080";
            Color[12] = "#808000";
            Color[13] = "#800080";
            Color[14] = "#008080";
            Color[15] = "#C0C0C0";
            Color[16] = "#808080";
            Color[17] = "#9999FF";
            Color[18] = "#993366";
            Color[19] = "#FFFFCC";
            Color[20] = "#CCFFFF";
            Color[21] = "#660066";
            Color[22] = "#FF8080";
            Color[23] = "#0066CC";
            Color[24] = "#CCCCFF";
            Color[25] = "#000080";
            Color[26] = "#FF00FF";
            Color[27] = "#FFFF00";
            Color[28] = "#00FFFF";
            Color[29] = "#800080";
            Color[30] = "#800000";
            Color[31] = "#008080";
            Color[32] = "#0000FF";
            Color[33] = "#00CCFF";
            Color[34] = "#CCFFFF";
            Color[35] = "#CCFFCC";
            Color[36] = "#FFFF99";
            Color[37] = "#99CCFF";
            Color[38] = "#FF99CC";
            Color[39] = "#CC99FF";
            Color[40] = "#FFCC99";
            Color[41] = "#3366FF";
            Color[42] = "#33CCCC";
            Color[43] = "#99CC00";
            Color[44] = "#FFCC00";
            Color[45] = "#FF9900";
            Color[46] = "#FF6600";
            Color[47] = "#666699";
            Color[48] = "#969696";
            Color[49] = "#003366";
            Color[50] = "#339966";
            Color[51] = "#003300";
            Color[52] = "#333300";
            Color[53] = "#993300";
            Color[54] = "#993366";
            Color[55] = "#333399";
            Color[56] = "#333333";

            char Context    = ' ';
            char SubContext = ' ';

            string fileName = "";
            string Path     = "";
            string Entity   = "";
            string Header   = "";
            string Footer   = "";

            StreamReader SR;
            string       S;

            SR = File.OpenText("excel2xslt.config");
            S  = SR.ReadLine();
            for (int i = 1; i <= 11; i++)
            {
                Console.WriteLine(S);
                S = SR.ReadLine();
                if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 5) == "Files")
                {
                    Path = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\"");
                }
                if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 5) == "Excel")
                {
                    fileName = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\"");
                }
                if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 6) == "Header")
                {
                    Header = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\"");
                }
                if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 6) == "Footer")
                {
                    Footer = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\"");
                }
                if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 7) == "Context")
                {
                    Context = Convert.ToChar(XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\""));
                }
                if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 3) == "Sub")
                {
                    SubContext = Convert.ToChar(XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\""));
                }
                if (S.Length != 0 && S.Substring(0, 1) != "[" && S.Substring(0, 6) == "Entity")
                {
                    Entity = XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(S, "\""), "\"");
                }
            }
            SR.Close();

            if (Context == ' ')
            {
                Console.WriteLine("Context not defined. Please update the value in excel2xslt.config");
            }
            ;
            if (SubContext == ' ')
            {
                Console.WriteLine("SubContext not defined. Please update the value in excel2xslt.config");
            }
            ;
            if (fileName == "")
            {
                Console.WriteLine("FileName not defined. Please update the value in excel2xslt.config");
            }
            ;
            if (Path == "")
            {
                Console.WriteLine("Path not defined. Please update the value in excel2xslt.config");
            }
            ;
            if (Entity == "")
            {
                Console.WriteLine("Entity not defined. Please update the value in excel2xslt.config");
            }
            ;
            if (Header == "")
            {
                Console.WriteLine("Header not defined. Please update the value in excel2xslt.config");
            }
            ;
            if (Footer == "")
            {
                Console.WriteLine("Footer not defined. Please update the value in excel2xslt.config");
            }
            ;


            Excel.Application excelApp = new Excel.ApplicationClass(); // Creates a new Excel Application
            excelApp.Visible = false;                                  // visible
            string workbookPath = Path + fileName;

            Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
                                                                   false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true,
                                                                   false, 0, true, false, false);

            Console.WriteLine();
            Console.WriteLine("Output files written to: " + Path);

            StreamWriter swXMLCustomCfg = new StreamWriter(Path + "MailCustomCfg_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml", false, System.Text.Encoding.GetEncoding("ISO-8859-1"));
            StreamWriter swXMLHeaderCfg = new StreamWriter(Path + "MailHeaderCfg_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml", false, System.Text.Encoding.GetEncoding("ISO-8859-1"));
            StreamWriter swXMLConstants = new StreamWriter(Path + "Constants_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml", false, System.Text.Encoding.GetEncoding("ISO-8859-1"));


            swXMLCustomCfg.WriteLine("<?xml version='1.0' encoding='ISO-8859-1' standalone='yes'?>");
            swXMLCustomCfg.WriteLine("<ResultSet xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>");

            swXMLHeaderCfg.WriteLine("<?xml version='1.0' encoding='ISO-8859-1'?>");
            swXMLHeaderCfg.WriteLine("<ResultSet>");

            swXMLConstants.WriteLine("<?xml version='1.0' encoding='ISO-8859-1'?>");
            swXMLConstants.WriteLine("<root>");

            // The following gets the Worksheets collection
            Excel.Sheets excelSheets = excelWorkbook.Worksheets;

            //Console.WriteLine("No of sheets"+excelSheets.Count);


            for (int s = 1; s <= excelSheets.Count; s++)
            {
                Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(s);

                StreamWriter sw = new StreamWriter(Path + excelWorksheet.Name + ".xsl", false, System.Text.Encoding.GetEncoding("ISO-8859-1"));

                swXMLConstants.WriteLine("    <Constant>");
                swXMLConstants.WriteLine("        <DictDetail_Name Type='String'>" + excelWorksheet.Name + "</DictDetail_Name>");
                swXMLConstants.WriteLine("        <DictDetail_ShortName Type='String'>" + excelWorksheet.Name + "</DictDetail_ShortName>");
                swXMLConstants.WriteLine("        <ElementOwner Type='Character'>C</ElementOwner>");
                swXMLConstants.WriteLine("        <DictCoherency_Name Type='String'>XSLTFileName</DictCoherency_Name>");
                swXMLConstants.WriteLine("        <ReturnType Type='Character'>S</ReturnType>");
                swXMLConstants.WriteLine("        <Description Type='String'/>");
                swXMLConstants.WriteLine("        <Sorter Type='String'>XSLT</Sorter>");
                swXMLConstants.WriteLine("        <StringValue Type='EmptyString'>" + excelWorksheet.Name + "</StringValue>");
                swXMLConstants.WriteLine("        <Context Type='Character'>" + Context + "</Context>");
                swXMLConstants.WriteLine("    </Constant>");


                swXMLHeaderCfg.WriteLine("	  <MailHeaderCfg>");
                swXMLHeaderCfg.WriteLine("        <MailHeaderCfg_Name type='KDB_FIELD_NAME_0032'>" + excelWorksheet.Name + "</MailHeaderCfg_Name>");
                swXMLHeaderCfg.WriteLine("        <Description type='string'/>");
                swXMLHeaderCfg.WriteLine("        <CFG_Receiver type='string'>EDD_ThirdParty_Id_Cpty</CFG_Receiver>");
                swXMLHeaderCfg.WriteLine("        <MailType_Name type='string'>Pdf Confirmation</MailType_Name>");
                swXMLHeaderCfg.WriteLine("        <CFG_Master type='string'>EmptyEvent</CFG_Master>");
                swXMLHeaderCfg.WriteLine("        <CFG_FaxAddress type='string'>DummyElement</CFG_FaxAddress>");
                swXMLHeaderCfg.WriteLine("        <CFG_EmailFrom type='string'>DummyElement</CFG_EmailFrom>");
                swXMLHeaderCfg.WriteLine("        <CFG_EmailTo type='string'>DummyElement</CFG_EmailTo>");
                swXMLHeaderCfg.WriteLine("        <CFG_EmailCC type='string'>DummyElement</CFG_EmailCC>");
                swXMLHeaderCfg.WriteLine("        <CFG_Bic type='string'>EVB_Bic_Id</CFG_Bic>");
                swXMLHeaderCfg.WriteLine("        <ToBePrinted type='YesNo_t'>Y</ToBePrinted>");
                swXMLHeaderCfg.WriteLine("        <NumberOfCopies type='KDB_FIELD_INTEGER'>1</NumberOfCopies>");
                swXMLHeaderCfg.WriteLine("        <CFG_ExternalRef type='string'>NONE</CFG_ExternalRef>");
                swXMLHeaderCfg.WriteLine("        <CFG_AddAddress type='string'>DummyElement</CFG_AddAddress>");
                swXMLHeaderCfg.WriteLine("        <CFG_TypeOfOper type='string'>TAG22A</CFG_TypeOfOper>");
                swXMLHeaderCfg.WriteLine("        <CFG_ReleaseDate type='string'>DummyElement</CFG_ReleaseDate>");
                swXMLHeaderCfg.WriteLine("        <CFG_DeadLine type='string'>DummyElement</CFG_DeadLine>");
                swXMLHeaderCfg.WriteLine("        <ValidityDateBegin type='KDB_FIELD_DATE'>2007/03/09</ValidityDateBegin>");
                swXMLHeaderCfg.WriteLine("        <ValidityDateEnd type='KDB_FIELD_DATE'>2100/03/09</ValidityDateEnd>");
                swXMLHeaderCfg.WriteLine("        <Priority type='BOPriority_t'>N</Priority>");
                swXMLHeaderCfg.WriteLine("        <CFG_Entity type='string'>EBO_Entity_Id</CFG_Entity>");
                swXMLHeaderCfg.WriteLine("        <AutoSending type='YesNo_t'>N</AutoSending>");
                swXMLHeaderCfg.WriteLine("        <CFG_XSLT type='string'>" + excelWorksheet.Name + "</CFG_XSLT>");
                swXMLHeaderCfg.WriteLine("        <Context type='BOContext_t'>" + Context + "</Context>");
                swXMLHeaderCfg.WriteLine("        <SubContext type='BOContext_t'>" + SubContext + "</SubContext>");
                swXMLHeaderCfg.WriteLine("        <CFG_Description type='string'>ModelDescripMails</CFG_Description>");
                swXMLHeaderCfg.WriteLine("        <Entity_Name type='string'>" + Entity + "</Entity_Name>");
                swXMLHeaderCfg.WriteLine("        <Validity type='BOValidity_t'>A</Validity>");
                swXMLHeaderCfg.WriteLine("    </MailHeaderCfg>");

                Excel.Range cells = (Excel.Range)excelWorksheet.get_Range("A1", excelWorksheet.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing));


                int[] actual_columns = new int[cells.Rows.Count + 1];

                for (int i = 1; i <= cells.Rows.Count; i++)
                {
                    actual_columns[i] = 0;
                    for (int j = 1; j <= cells.Columns.Count; j++)
                    {
                        Excel.Range current = (Excel.Range)cells[i, j];
                        if (current.Value2 != null)
                        {
                            actual_columns[i] = j;
                        }
                        ;
                    }
                    ;
                    //Console.WriteLine(actual_columns[i]);
                }
                ;



                sw.WriteLine("<?xml version='1.0' encoding='ISO-8859-1'?>");
                sw.WriteLine("<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/Transform' xmlns:fo='http://www.w3.org/1999/XSL/Format' xmlns:xs='http://www.w3.org/2001/XMLSchema' xmlns:java='http://xml.apache.org/xslt/java' exclude-result-prefixes='java' xmlns:date='http://exslt.org/dates-and-times'");
                sw.WriteLine(">");
                sw.WriteLine(" <xsl:include href='" + Header + "'/>");
                sw.WriteLine(" <xsl:include href='" + Footer + "'/> ");
                sw.WriteLine("");
                sw.WriteLine(" <xsl:template match='/'>");
                sw.WriteLine("<fo:root xmlns:fo='http://www.w3.org/1999/XSL/Format'>");
                sw.WriteLine("        <fo:layout-master-set>");
                sw.WriteLine("                <fo:simple-page-master master-name='single_page' page-height='297mm' page-width='210mm' >");
                sw.WriteLine("                        <fo:region-body region-name='xsl-region-body' ");
                sw.WriteLine("                                margin='20mm' />");
                sw.WriteLine("                </fo:simple-page-master>");
                sw.WriteLine("                <fo:page-sequence-master master-name='repeatable_master'>");
                sw.WriteLine("                        <fo:repeatable-page-master-reference master-reference='single_page' maximum-repeats='10'/>");
                sw.WriteLine("                </fo:page-sequence-master>");
                sw.WriteLine("        </fo:layout-master-set>");
                sw.WriteLine("");
                sw.WriteLine("        <fo:page-sequence master-reference='repeatable_master'>");
                sw.WriteLine("                <fo:flow flow-name='xsl-region-body'>");
                sw.WriteLine("                ");
                sw.WriteLine("        <fo:table font-family='Times New Roman' font-size='10.00pt' width='180.00mm'>");
                sw.WriteLine("                                <fo:table-column column-width='180mm'/>");
                sw.WriteLine("                                                        ");
                sw.WriteLine("                                <fo:table-body>");
                sw.WriteLine("                                        <fo:table-row height='55mm'>");
                sw.WriteLine("                                                <fo:table-cell   padding-before='3pt' padding-after='3pt'   padding-start='3pt' padding-end='3pt'>");
                sw.WriteLine("                                                <xsl:call-template name='HEADER'/> ");
                sw.WriteLine("                                                <fo:block height='20mm' text-align='left'></fo:block>");
                sw.WriteLine("                                                </fo:table-cell>");
                sw.WriteLine("                                        </fo:table-row>");
                sw.WriteLine("                                </fo:table-body>");
                sw.WriteLine("                </fo:table>");

/* second table - body */
                sw.WriteLine("	<fo:table font-family='Times New Roman' font-size='10.00pt' width='180.00mm'>");


                for (int i = 1; i <= cells.Columns.Count; i++)
                {
                    sw.WriteLine("<fo:table-column column-width='" + 180 / cells.Columns.Count + "mm'/>");
                }

                sw.WriteLine("								<fo:table-body>");

                int[]    total_columns = new int[cells.Rows.Count + 1];
                string[] choose        = new string[cells.Rows.Count + 1];

                for (int i = 1; i <= cells.Rows.Count; i++)
                {
                    total_columns[i] = 0;
                    Excel.Range current = (Excel.Range)cells[i, 1];
                    if (current.Value2 != null)
                    {
                        if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 6) == "choose")
                        {
                            choose[i] = "<xsl:choose>";
                        }
                        else
                        {
                            if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 4) == "when")
                            {
                                choose[i] = "<xsl:when test=\"" + (string)XsltFunctions.SubstringAfter(current.Value2.ToString(), "when ") + "\">";

                                /*	if (XsltFunctions.Substring((string)XsltFunctions.SubstringAfter(current.Value2.ToString(),"when "),1,8) == "MailBody")
                                 *                      {
                                 *                      swXMLCustomCfg.WriteLine("	<MailCustomCfg>");
                                 *                      swXMLCustomCfg.WriteLine("<MailHeaderCfg_Name type='string'>"+excelWorksheet.Name+"</MailHeaderCfg_Name>");
                                 *                      swXMLCustomCfg.WriteLine("		<MailCustomTag type='string'>"+XsltFunctions.SubstringAfter(XsltFunctions.SubstringBefore((string)XsltFunctions.SubstringAfter(current.Value2.ToString(),"when ")," and "),"MailBody/")+"</MailCustomTag>");
                                 *                      swXMLCustomCfg.WriteLine("		<Comment type='string'>/</Comment>");
                                 *                      swXMLCustomCfg.WriteLine("		<CFG_Element type='string'>Empty</CFG_Element>");
                                 *                      swXMLCustomCfg.WriteLine("		<SortingNum type='KDB_FIELD_INTEGER'></SortingNum>");
                                 *                      swXMLCustomCfg.WriteLine("	</MailCustomCfg>");
                                 *                      };
                                 *      if (XsltFunctions.Substring((string)XsltFunctions.SubstringAfter(current.Value2.ToString()," and "),1,8) == "MailBody")
                                 *                      {
                                 *                      swXMLCustomCfg.WriteLine("	<MailCustomCfg>");
                                 *                      swXMLCustomCfg.WriteLine("<MailHeaderCfg_Name type='string'>"+excelWorksheet.Name+"</MailHeaderCfg_Name>");
                                 *                      swXMLCustomCfg.WriteLine("		<MailCustomTag type='string'>"+XsltFunctions.SubstringAfter((string)XsltFunctions.SubstringAfter(current.Value2.ToString()," and "),"MailBody/")+"</MailCustomTag>");
                                 *                      swXMLCustomCfg.WriteLine("		<Comment type='string'>/</Comment>");
                                 *                      swXMLCustomCfg.WriteLine("		<CFG_Element type='string'>Empty</CFG_Element>");
                                 *                      swXMLCustomCfg.WriteLine("		<SortingNum type='KDB_FIELD_INTEGER'></SortingNum>");
                                 *                      swXMLCustomCfg.WriteLine("	</MailCustomCfg>");
                                 *                      }
                                 */
                            }

                            else
                            {
                                if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 7) == "endwhen")
                                {
                                    choose[i] = "</xsl:when>";
                                }
                                else
                                {
                                    if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 9) == "endchoose")
                                    {
                                        choose[i] = "</xsl:choose>";
                                    }

                                    else
                                    {
                                        if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 9) == "otherwise")
                                        {
                                            choose[i] = "<xsl:otherwise>";
                                        }

                                        else
                                        {
                                            if ((string)XsltFunctions.Substring(current.Value2.ToString(), 1, 12) == "endotherwise")
                                            {
                                                choose[i] = "</xsl:otherwise>";
                                            }
                                            else
                                            {
                                                choose[i] = "";
                                            };
                                        };
                                    };
                                }
                            };
                        };
                    }
                    ;
                }
                ;



                for (int i = 1; i <= cells.Rows.Count; i++)
                {
                    int span         = 1;
                    int mergedcellno = 0;
                    int t            = 0;

                    if (choose[i] != "")
                    {
                        sw.WriteLine(choose[i]);
                    }
                    else
                    {
                        sw.WriteLine("<fo:table-row height='5mm'>");
                        for (int j = 1; j <= cells.Columns.Count && total_columns[i] < cells.Columns.Count; j++)
                        {
                            if (mergedcellno != 0 && t < mergedcellno && t != 0)
                            {
                                t = t + 1;
                            }
                            else
                            {
                                if (t >= mergedcellno)
                                {
                                    t            = 0;
                                    mergedcellno = 0;
                                }
                                ;
                            };

                            if (t == 0)
                            {
                                String      color;
                                Excel.Range current = (Excel.Range)cells[i, j];
                                //Console.WriteLine("Color "+current.Interior.ColorIndex);
                                if ((int)current.Interior.ColorIndex >= 1 && (int)current.Interior.ColorIndex <= 56)
                                {
                                    color = Color[(int)current.Interior.ColorIndex];
                                }
                                else
                                {
                                    color = "white";
                                };



                                if (current.Value2 != null)
                                {
                                    if ((bool)current.MergeCells)
                                    {
                                        //Console.WriteLine(current.get_Address("", "", Excel.XlReferenceStyle.xlR1C1,"", "")+"Row "+i+" Column "+j+" merged"+":"+current.MergeArea.get_Address("", "", Excel.XlReferenceStyle.xlR1C1,"", ""));
                                        span         = Convert.ToInt32(XsltFunctions.SubstringAfter(XsltFunctions.SubstringAfter(current.MergeArea.get_Address("", "", Excel.XlReferenceStyle.xlR1C1, "", ""), ":"), "C")) - Convert.ToInt32(XsltFunctions.SubstringBefore(XsltFunctions.SubstringAfter(current.MergeArea.get_Address("", "", Excel.XlReferenceStyle.xlR1C1, "", ""), "C"), ":")) + 1;
                                        mergedcellno = span;
                                    }
                                    else
                                    {
                                        span = 1;
                                    };

                                    sw.WriteLine("							<fo:table-cell number-columns-spanned='"+ span + "' background-color='" + color + "' border-color='black' border-width='0.2pt' padding-before='3pt' padding-after='3pt'   padding-start='3pt' padding-end='3pt'>");
                                    sw.WriteLine("									<fo:block height='5mm' text-align='left'>");



                                    if ((bool)XsltFunctions.Contains(current.Value2.ToString(), "MailBody") == true)
                                    {
                                        if ((bool)(XsltFunctions.Contains(current.Value2.ToString(), "Amount")) == true || (bool)(XsltFunctions.Contains(current.Value2.ToString(), "Rate")) == true)
                                        {
                                            sw.WriteLine("												<xsl:call-template name='Amount'>");
                                            sw.WriteLine("													<xsl:with-param name='Amount' select='"+ current.Value2.ToString() + "'/>");
                                            sw.WriteLine("												</xsl:call-template>");
                                        }
                                        else
                                        {
                                            if ((bool)(XsltFunctions.Contains(current.Value2.ToString(), "Date")) == true)
                                            {
                                                sw.WriteLine("												<xsl:call-template name='Date'>");
                                                sw.WriteLine("													<xsl:with-param name='Date' select='"+ current.Value2.ToString() + "'/>");
                                                sw.WriteLine("												</xsl:call-template>");
                                            }
                                            else
                                            {
                                                sw.WriteLine("									"+ "<xsl:value-of select = '" + current.Value2.ToString() + "'/>");
                                            }
                                        };
                                        swXMLCustomCfg.WriteLine("	<MailCustomCfg>");
                                        swXMLCustomCfg.WriteLine("<MailHeaderCfg_Name type='string'>" + excelWorksheet.Name + "</MailHeaderCfg_Name>");
                                        swXMLCustomCfg.WriteLine("		<MailCustomTag type='string'>"+ XsltFunctions.SubstringAfter(current.Value2.ToString(), "MailBody/") + "</MailCustomTag>");
                                        swXMLCustomCfg.WriteLine("		<Comment type='string'>/</Comment>");
                                        swXMLCustomCfg.WriteLine("		<CFG_Element type='string'>Empty</CFG_Element>");
                                        swXMLCustomCfg.WriteLine("		<SortingNum type='KDB_FIELD_INTEGER'></SortingNum>");
                                        swXMLCustomCfg.WriteLine("	</MailCustomCfg>");
                                    }
                                    else
                                    {
                                        sw.WriteLine("									"+ current.Value2.ToString());
                                    };

                                    //Console.WriteLine(current.Value2);
                                    sw.WriteLine("									</fo:block>");
                                    sw.WriteLine("							</fo:table-cell>");
                                }
                                else
                                {
                                    sw.WriteLine("							<fo:table-cell number-columns-spanned='"+ span + "' background-color='" + color + "' border-color='black' border-width='0.2pt' padding-before='3pt' padding-after='3pt'   padding-start='3pt' padding-end='3pt'>");
                                    sw.WriteLine("									<fo:block height='5mm' text-align='left'>");
                                    sw.WriteLine("									</fo:block>");
                                    sw.WriteLine("							</fo:table-cell>");
                                };

                                total_columns[i] = total_columns[i] + span;
                                span             = 1;
                            }



                            if (mergedcellno != 0 && t < mergedcellno)
                            {
                                t = t + 1;
                            }
                            ;
                        }
                        sw.WriteLine("						     		</fo:table-row>");
                        sw.WriteLine();
                    };
                }

                sw.WriteLine("								</fo:table-body>");
                sw.WriteLine("								</fo:table>");


                sw.WriteLine("                                                               /* Placeholder for footer */");
                sw.WriteLine("                                                                                                ");
                sw.WriteLine("                                                        <fo:table font-family='Times New Roman' font-size='10.00pt' width='120.00mm'>");
                sw.WriteLine("                                                                        <fo:table-column column-width='120mm'/>");
                sw.WriteLine("                                                        ");
                sw.WriteLine("                                                                <fo:table-body start-indent='0pt'>");
                sw.WriteLine("                                                                ");
                sw.WriteLine("                                                                <fo:table-row height='100mm'>");
                sw.WriteLine("                                                                        <fo:table-cell   padding-before='3pt' padding-after='3pt'   padding-start='3pt' padding-end='3pt'>");
                sw.WriteLine("                                                                        <xsl:call-template name='FOOTER'/>");
                sw.WriteLine("                                                                                        <fo:block height='20mm' text-align='left'>  </fo:block>");
                sw.WriteLine("                                                                        </fo:table-cell>");
                sw.WriteLine("                                                                </fo:table-row>");
                sw.WriteLine("                                                                </fo:table-body>");
                sw.WriteLine("                                                                </fo:table>                     ");
                sw.WriteLine("                </fo:flow>");
                sw.WriteLine("        </fo:page-sequence>");
                sw.WriteLine("</fo:root>");
                sw.WriteLine("");
                sw.WriteLine("</xsl:template>");
                sw.WriteLine("</xsl:stylesheet>");



                sw.Close();
                file_counter++;
                Console.WriteLine(file_counter + ". XSL file successfully written to: " + excelWorksheet.Name + ".xsl");
            }
            swXMLCustomCfg.WriteLine("</ResultSet>");
            swXMLCustomCfg.Close();
            file_counter++;
            Console.WriteLine(file_counter + ". MailCustomCfg file successfully written to: " + "MailCustomCfg_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml");

            swXMLHeaderCfg.WriteLine("</ResultSet>");
            swXMLHeaderCfg.Close();
            file_counter++;
            Console.WriteLine(file_counter + ". MailHeaderCfg file successfully written to: " + "MailHeaderCfg_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml");

            swXMLConstants.WriteLine("</root>");
            swXMLConstants.Close();
            file_counter++;
            Console.WriteLine(file_counter + ". Constants file successfully written to: " + "Constants_" + XsltFunctions.SubstringBefore(fileName, ".") + ".xml");
            excelWorkbook.Close(false, 0, 0);
        }
        public string convertFiles(IList <string> filePaths)
        {
            m_filePaths = filePaths;
            m_fileCount = m_filePaths.Count;

            int       fileIndex       = 0;
            ArrayList sheet1ValueList = new ArrayList();
            ArrayList sheet2ValueList = new ArrayList();


            Cross_TopTag topTag;

            Cross_Data[]          data;
            Cross_Paragraphs[][]  paragraphs;
            Cross_Qas[][][]       qas;
            Cross_Answers[][][][] answers;

            ETRI_TopTag EtopTag;

            object[,] sheet1ValueArray;

            int totalCountQas        = 0;
            int totalCountParagraphs = 0;
            int sheet1TotalRowCount  = 0;

            ArrayList splitedFileName = new ArrayList();

            foreach (var item in m_filePaths)
            {
                string[] temp;
                m_path = item;
                var missing = Type.Missing;

                temp = m_path.Split('_');
                splitedFileName.Add(temp);

                objApp       = new Excel.Application();
                objWorkbooks = objApp.Workbooks;

                int countParagraphs = 0;
                int countQas        = 0;
                int currentRow      = 0;

                bool excelOpen = false;

                try
                {
                    if (m_currentConvertingMode == convertingMode.CJSONToCExcel)
                    {
                        #region JSON -> Excel 변환

                        // ** name1 영역 파싱
                        topTag = JsonConvert.DeserializeObject <Cross_TopTag>(File.ReadAllText(m_path));

                        // name2 영역 파싱
                        data = new Cross_Data[topTag.data.Count];
                        for (int i = 0; i < data.Length; i++)
                        {
                            data[i] = JsonConvert.DeserializeObject <Cross_Data>(topTag.data[i].ToString());
                        }

                        // ** name3 영역 파싱
                        paragraphs = new Cross_Paragraphs[data.Length][];
                        for (int i = 0; i < data.Length; i++)
                        {
                            paragraphs[i] = new Cross_Paragraphs[data[i].paragraphs.Count];
                            for (int j = 0; j < data[i].paragraphs.Count; j++)
                            {
                                paragraphs[i][j] = JsonConvert.DeserializeObject <Cross_Paragraphs>(data[i].paragraphs[j].ToString());
                                countParagraphs++;
                                totalCountParagraphs++;
                            }
                        }

                        // ** name4 영역 파싱
                        qas = new Cross_Qas[data.Length][][];
                        for (int i = 0; i < data.Length; i++)
                        {
                            qas[i] = new Cross_Qas[paragraphs[i].Length][];
                            for (int j = 0; j < paragraphs[i].Length; j++)
                            {
                                qas[i][j] = new Cross_Qas[paragraphs[i][j].qas.Count];
                                for (int k = 0; k < paragraphs[i][j].qas.Count; k++)
                                {
                                    qas[i][j][k] = JsonConvert.DeserializeObject <Cross_Qas>(paragraphs[i][j].qas[k].ToString());
                                    countQas++;
                                    totalCountQas++;
                                }
                            }
                        }

                        // ** name5 영역 파싱
                        answers = new Cross_Answers[data.Length][][][];
                        for (int i = 0; i < data.Length; i++)
                        {
                            answers[i] = new Cross_Answers[paragraphs[i].Length][][];
                            for (int j = 0; j < paragraphs[i].Length; j++)
                            {
                                answers[i][j] = new Cross_Answers[qas[i][j].Length][];
                                for (int k = 0; k < qas[i][j].Length; k++)
                                {
                                    answers[i][j][k] = new Cross_Answers[qas[i][j][k].answers.Count];
                                    for (int m = 0; m < qas[i][j][k].answers.Count; m++)
                                    {
                                        answers[i][j][k][m] = JsonConvert.DeserializeObject <Cross_Answers>(qas[i][j][k].answers[m].ToString());
                                    }
                                }
                            }
                        }

                        // ** sheet1ValueArray & sheet2ValueArray 영역 크기 지정
                        sheet1RowCount = countQas;
                        //sheet2RowCount = countParagraphs;

                        sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount];
                        //sheet2ValueArray = new object[sheet2RowCount, sheet2ColCount];

                        // ** sheet1ValueArray & sheet2ValueArray에 데이터 입력
                        // * paragraph 순번 & name1 영역
                        for (int row = 0; row < sheet1RowCount; row++)
                        {
                            sheet1ValueArray[row, 0] = row + 1;
                            sheet1ValueArray[row, 1] = topTag.version;
                            sheet1ValueArray[row, 2] = topTag.creator;
                            sheet1ValueArray[row, 3] = topTag.progress;
                            sheet1ValueArray[row, 4] = topTag.formatt;
                            sheet1ValueArray[row, 5] = topTag.time;
                            sheet1ValueArray[row, 6] = topTag.check;
                            sheet1ValueArray[row, 7] = topTag.firstfile;
                            sheet1ValueArray[row, 8] = topTag.secondfile;
                        }

                        // * name2 & name3 영역
                        currentRow = 0;
                        for (int d = 0; d < data.Length; d++)
                        {
                            for (int p = 0; p < paragraphs[d].Length; p++)
                            {
                                sheet1ValueArray[currentRow, 9]  = data[d].title;
                                sheet1ValueArray[currentRow, 10] = paragraphs[d][p].context;
                                sheet1ValueArray[currentRow, 11] = paragraphs[d][p].context_en;
                                sheet1ValueArray[currentRow, 12] = paragraphs[d][p].context_tagged;

                                currentRow++;
                            }
                        }

                        // * name4 영역
                        currentRow = 0;
                        int currentParaNum = 1;
                        for (int d = 0; d < data.Length; d++)
                        {
                            for (int p = 0; p < paragraphs[d].Length; p++)
                            {
                                for (int q = 0; q < qas[d][p].Length; q++)
                                {
                                    sheet1ValueArray[currentRow, 13] = qas[d][p][q].id;
                                    sheet1ValueArray[currentRow, 14] = qas[d][p][q].confuseQt1;
                                    sheet1ValueArray[currentRow, 15] = qas[d][p][q].confuseQf1;
                                    sheet1ValueArray[currentRow, 16] = qas[d][p][q].confuseSat1;
                                    sheet1ValueArray[currentRow, 17] = qas[d][p][q].confuseLat1;
                                    sheet1ValueArray[currentRow, 18] = qas[d][p][q].question;
                                    sheet1ValueArray[currentRow, 19] = qas[d][p][q].question_en;
                                    sheet1ValueArray[currentRow, 20] = qas[d][p][q].question_tagged1;
                                    sheet1ValueArray[currentRow, 21] = qas[d][p][q].questionType1;
                                    sheet1ValueArray[currentRow, 22] = qas[d][p][q].questionFocus1;
                                    sheet1ValueArray[currentRow, 23] = qas[d][p][q].questionSAT1;
                                    sheet1ValueArray[currentRow, 24] = qas[d][p][q].questionLAT1;
                                    sheet1ValueArray[currentRow, 25] = qas[d][p][q].confuseQt2;
                                    sheet1ValueArray[currentRow, 26] = qas[d][p][q].confuseQf2;
                                    sheet1ValueArray[currentRow, 27] = qas[d][p][q].confuseSat2;
                                    sheet1ValueArray[currentRow, 28] = qas[d][p][q].confuseLat2;
                                    sheet1ValueArray[currentRow, 29] = qas[d][p][q].question_tagged2; //
                                    sheet1ValueArray[currentRow, 30] = qas[d][p][q].questionType2;    //
                                    sheet1ValueArray[currentRow, 31] = qas[d][p][q].questionFocus2;   //
                                    sheet1ValueArray[currentRow, 32] = qas[d][p][q].questionSAT2;     //
                                    sheet1ValueArray[currentRow, 33] = qas[d][p][q].questionLAT2;
                                    sheet1ValueArray[currentRow, 34] = qas[d][p][q].confuseQt3;
                                    sheet1ValueArray[currentRow, 35] = qas[d][p][q].confuseQf3;
                                    sheet1ValueArray[currentRow, 36] = qas[d][p][q].confuseSat3;
                                    sheet1ValueArray[currentRow, 37] = qas[d][p][q].confuseLat3;
                                    sheet1ValueArray[currentRow, 38] = qas[d][p][q].question_tagged3;
                                    sheet1ValueArray[currentRow, 39] = qas[d][p][q].questionType3;
                                    sheet1ValueArray[currentRow, 40] = qas[d][p][q].questionFocus3;
                                    sheet1ValueArray[currentRow, 41] = qas[d][p][q].questionSAT3;
                                    sheet1ValueArray[currentRow, 42] = qas[d][p][q].questionLAT3;

                                    sheet1ValueArray[currentRow, 55] = currentParaNum;
                                    currentRow++;
                                }

                                currentParaNum++;
                            }
                        }

                        // * name5 영역
                        currentRow = 0;
                        for (int d = 0; d < data.Length; d++)
                        {
                            for (int p = 0; p < paragraphs[d].Length; p++)
                            {
                                for (int q = 0; q < qas[d][p].Length; q++)
                                {
                                    if (qas[d][p][q].answers.Count > 3)
                                    {
                                        return("정답의 개수가 3개 초과인 문제가 있습니다.\r\n파일: " + m_path);
                                    }

                                    int answerStartColNum = 43;
                                    for (int a = 0; a < answers[d][p][q].Length; a++)
                                    {
                                        sheet1ValueArray[currentRow, answerStartColNum]     = answers[d][p][q][a].text;
                                        sheet1ValueArray[currentRow, answerStartColNum + 1] = answers[d][p][q][a].text_en;
                                        sheet1ValueArray[currentRow, answerStartColNum + 2] = answers[d][p][q][a].text_tagged;
                                        sheet1ValueArray[currentRow, answerStartColNum + 3] = answers[d][p][q][a].text_syn;
                                        sheet1ValueArray[currentRow, answerStartColNum + 4] = answers[d][p][q][a].answer_start;
                                        sheet1ValueArray[currentRow, answerStartColNum + 5] = answers[d][p][q][a].answer_end;

                                        answerStartColNum += 6;
                                    }
                                    currentRow++;
                                }
                            }
                        }
                        if ((++fileIndex) < m_fileCount)
                        {
                            sheet1ValueList.Add(sheet1ValueArray);
                            continue;
                        }

                        //마지막 파일 ADD
                        sheet1ValueList.Add(sheet1ValueArray);

                        // 여러 sheetValueArray들을 각 작업량의 따라 나눠 하나로 통합
                        string[] separator            = { "(", ")", "-", " " }; //제외할 요소들
                        int      totalRowCount_sheet1 = 0;
                        int      totalRowCount_sheet2 = 0;

                        for (int i = 0; i < fileIndex; i++)
                        {
                            string[] _temp   = (string[])splitedFileName[i];
                            string[] splited = _temp[2].Split(separator, StringSplitOptions.RemoveEmptyEntries);

                            //sheet1 작업
                            int startIndex = Convert.ToInt32(splited[0]);
                            int endIndex   = Convert.ToInt32(splited[1]);
                            int length     = endIndex - startIndex + 1;

                            totalRowCount_sheet1 += length;

                            int rowIndex_sheet1 = 0;
                            int rowIndex_sheet2 = 0;

                            object[,] temp_arrList    = (object[, ])sheet1ValueList[i];
                            object[,] tempSheet1Value = new object[length, sheet1ColCount];

                            for (int j = startIndex - 1; j < endIndex; j++)
                            {
                                for (int k = 0; k < sheet1ColCount; k++)
                                {
                                    tempSheet1Value[rowIndex_sheet1, k] = temp_arrList[j, k];
                                }
                                rowIndex_sheet1++;
                            }

                            /*
                             * //sheet2 작업
                             * startIndex = (int)tempSheet1Value[0, 37];
                             * endIndex = (int)tempSheet1Value[rowIndex_sheet1 - 1, 37];
                             * length = endIndex - startIndex + 1;
                             *
                             * totalRowCount_sheet2 += length;
                             *
                             * temp_arrList = (object[,])sheet2ValueList[i];
                             * object[,] tempSheet2Value = new object[length, sheet2ColCount];
                             *
                             * for (int j = startIndex - 1; j < endIndex; j++)
                             * {
                             *  for (int k = 0; k < sheet2ColCount; k++)
                             *      tempSheet2Value[rowIndex_sheet2, k] = temp_arrList[j, k];
                             *  rowIndex_sheet2++;
                             * }
                             * sheet1ValueList.RemoveAt(i);
                             * sheet2ValueList.RemoveAt(i);
                             * sheet1ValueList.Insert(i, tempSheet1Value);
                             * sheet2ValueList.Insert(i, tempSheet2Value);
                             */
                        }



                        sheet1RowCount = totalRowCount_sheet1;

                        sheet1ValueArray = new object[sheet1RowCount, sheet1ColCount];

                        int sheet1RowIndex = 0;
                        int sheet2RowIndex = 0;
                        int _sheet1RowCount;
                        int _sheet2RowCount;
                        for (int i = 0; i < sheet1ValueList.Count; i++)
                        {
                            object[,] tempSheet1Value = (object[, ])sheet1ValueList[i];
                            object[,] tempSheet2Value = (object[, ])sheet2ValueList[i];
                            _sheet1RowCount           = (int)(tempSheet1Value.Length / sheet1ColCount);

                            for (int j = 0; j < _sheet1RowCount; j++)
                            {
                                for (int k = 0; k < sheet1ColCount; k++)
                                {
                                    sheet1ValueArray[sheet1RowIndex, k] = tempSheet1Value[j, k];
                                }
                                sheet1RowIndex++;
                            }
                        }

                        //엑셀파일에 writting
                        excelOpen     = true;
                        objWorkbook   = objWorkbooks.Add(missing);
                        objWorksheets = objWorkbook.Worksheets;

                        // * sheet2 부분 적용
                        objWorksheet      = (Excel.Worksheet)objWorksheets.get_Item(1);
                        objWorksheet.Name = "Paragraphs";

                        range = objWorksheet.get_Range("A1", "M1");
                        range.HorizontalAlignment = HCENTER;
                        range.Interior.Color      = Color.FromArgb(142, 169, 219);

                        Marshal.ReleaseComObject(range);

                        Excel.Range c1 = objWorksheet.Cells[2, 1];
                        range = objWorksheet.get_Range(c1);
                        Marshal.FinalReleaseComObject(c1);
                        Marshal.FinalReleaseComObject(range);

                        Marshal.ReleaseComObject(objWorksheet);

                        // * sheet1 부분 적용
                        objWorksheet      = (Excel.Worksheet)objWorksheets.Add(missing, missing, missing, missing);
                        objWorksheet.Name = "CrossToEtri";

                        range = objWorksheet.get_Range("A1", "AL1");
                        range.HorizontalAlignment = HCENTER;
                        range.Interior.Color      = Color.FromArgb(142, 169, 219);
                        range.Value2 = sheet1ColHeader;
                        Marshal.ReleaseComObject(range);

                        c1          = objWorksheet.Cells[2, 1];
                        range       = objWorksheet.get_Range(c1);
                        range.Value = sheet1ValueArray;
                        Marshal.FinalReleaseComObject(c1);
                        Marshal.FinalReleaseComObject(range);

                        Marshal.FinalReleaseComObject(objWorksheet);
                        Marshal.FinalReleaseComObject(objWorksheets);


                        m_savePath = Path.ChangeExtension(m_path, "xlsx");
                        FileInfo fi = new FileInfo(m_savePath);
                        if (fi.Exists)
                        {
                            fi.Delete();
                        }

                        objWorkbook.SaveAs(m_savePath, Excel.XlFileFormat.xlOpenXMLWorkbook,
                                           missing, missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                                           Excel.XlSaveConflictResolution.xlUserResolution, true, missing, missing, missing);

                        objWorkbook.Close(false, missing, missing);
                        objWorkbooks.Close();
                        objApp.Quit();

                        Marshal.FinalReleaseComObject(objWorkbook);
                        Marshal.FinalReleaseComObject(objWorkbooks);
                        Marshal.FinalReleaseComObject(objApp);

                        objApp    = null;
                        excelOpen = false;
                        #endregion
                    }
                    else
                    {
                        #region Excel -> JSON 변환

                        // ** Excel 파일 불러와서 object 이중배열에 데이터 입력
                        excelOpen     = true;
                        objWorkbook   = objWorkbooks.Open(m_path);
                        objWorksheets = objWorkbook.Worksheets;

                        objWorksheet     = (Excel.Worksheet)objWorksheets[1];
                        range            = objWorksheet.UsedRange;
                        sheet1ValueArray = (object[, ])range.get_Value(missing);
                        Marshal.ReleaseComObject(range);
                        Marshal.ReleaseComObject(objWorksheet);

                        Marshal.FinalReleaseComObject(objWorksheets);

                        objWorkbook.Close(false, missing, missing);
                        objWorkbooks.Close();
                        objApp.Quit();

                        Marshal.FinalReleaseComObject(objWorkbook);
                        Marshal.FinalReleaseComObject(objWorkbooks);
                        Marshal.FinalReleaseComObject(objApp);

                        objApp    = null;
                        excelOpen = false;

                        // ** sheet1, sheet2 object 이중배열의 데이터를 JSON 태그 클래스의 객체에 입력
                        // * topTag 객체 데이터 입력
                        EtopTag         = new ETRI_TopTag();
                        EtopTag.version = sheet1ValueArray[2, 2] == null ? "" : sheet1ValueArray[2, 2].ToString();
                        EtopTag.creator = sheet1ValueArray[2, 3] == null ? "" : sheet1ValueArray[2, 3].ToString();

                        EtopTag.data = new List <object>();

                        // * topTag 객체 내의 Data 객체 리스트 입력
                        IList <object> titleList = new List <object>();
                        for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++)
                        {
                            object tempTitle = sheet1ValueArray[r, 10];
                            if (!titleList.Any())   // 리스트에 아무것도 없을때 (=맨처음)
                            {
                                titleList.Add(tempTitle);
                            }
                            else if (tempTitle == null)  // null 이거나 "" 일 때 tempTitle == ""
                            {
                                titleList.Add(tempTitle);
                            }
                            else if (titleList.Contains(tempTitle)) // 타이틀 이미 입력됨(통과)
                            {
                                continue;
                            }

                            if (!titleList.Contains(tempTitle))
                            {
                                titleList.Clear();
                                titleList.Add(tempTitle);
                            }
                            ETRI_Data tempData = new ETRI_Data();
                            tempData.title      = tempTitle == null ? "" : tempTitle.ToString();
                            tempData.paragraphs = new List <object>();

                            EtopTag.data.Add(tempData);
                        }

                        // * topTag->Data 객체 리스트 내의 Paragraphs 객체 리스트 입력
                        int              dataCount    = 0;
                        object           currentTitle = sheet1ValueArray[2, 10];
                        List <ETRI_Data> tempDataList = EtopTag.data.Cast <ETRI_Data>().ToList();
                        for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++)
                        {
                            ETRI_Paragraphs tempParagraphs = new ETRI_Paragraphs();
                            tempParagraphs.context        = sheet1ValueArray[r, 11] == null ? "" : sheet1ValueArray[r, 11].ToString();
                            tempParagraphs.context_en     = sheet1ValueArray[r, 12] == null ? "" : sheet1ValueArray[r, 12].ToString();
                            tempParagraphs.context_tagged = sheet1ValueArray[r, 13] == null ? "" : sheet1ValueArray[r, 13].ToString();
                            tempParagraphs.qas            = new List <object>();

                            if (sheet1ValueArray[r, 10] == null || sheet1ValueArray[r, 7].ToString() == "")
                            {
                                if (r != 2)
                                {
                                    dataCount++;
                                }
                                tempDataList[dataCount].paragraphs.Add(tempParagraphs);
                                currentTitle = sheet1ValueArray[r, 10] == null ? "" : sheet1ValueArray[r, 7].ToString();
                            }
                            else if (sheet1ValueArray[r, 10].Equals(currentTitle))
                            {
                                tempDataList[dataCount].paragraphs.Add(tempParagraphs);
                            }
                            else
                            {
                                dataCount++;
                                tempDataList[dataCount].paragraphs.Add(tempParagraphs);
                                currentTitle = sheet1ValueArray[r, 10].ToString();
                            }
                        }
                        EtopTag.data = tempDataList.Cast <object>().ToList();

                        // * topTag->Data->Paragraphs 객체 리스트 내의 Qas 객체 리스트 입력
                        dataCount = 0;
                        int paragraphCount   = 0;
                        int currentParagraph = 1;
                        tempDataList = EtopTag.data.Cast <ETRI_Data>().ToList();
                        List <ETRI_Qas> tempQasList = new List <ETRI_Qas>();
                        for (int r = 2; r <= sheet1ValueArray.GetLength(0); r++)
                        {
                            ETRI_Qas tempQas = new ETRI_Qas();
                            tempQas.id              = sheet1ValueArray[r, 2] == null ? "" : sheet1ValueArray[r, 2].ToString();
                            tempQas.question        = sheet1ValueArray[r, 7] == null ? "" : sheet1ValueArray[r, 7].ToString();
                            tempQas.question_en     = sheet1ValueArray[r, 8] == null ? "" : sheet1ValueArray[r, 8].ToString();
                            tempQas.question_tagged = sheet1ValueArray[r, 27] == null ? "" : sheet1ValueArray[r, 27].ToString();
                            tempQas.questionType    = sheet1ValueArray[r, 28] == null ? "" : sheet1ValueArray[r, 28].ToString();
                            tempQas.questionFocus   = sheet1ValueArray[r, 29] == null ? "" : sheet1ValueArray[r, 29].ToString();
                            tempQas.questionSAT     = sheet1ValueArray[r, 30] == null ? "" : sheet1ValueArray[r, 30].ToString();
                            tempQas.questionLAT     = sheet1ValueArray[r, 31] == null ? "" : sheet1ValueArray[r, 31].ToString();

                            int          ansStartColNum = 32;
                            ETRI_Answers tempAnswers    = new ETRI_Answers();
                            tempAnswers.text         = sheet1ValueArray[r, ansStartColNum] == null ? "" : sheet1ValueArray[r, ansStartColNum].ToString();
                            tempAnswers.text_en      = sheet1ValueArray[r, ansStartColNum + 1] == null ? "" : sheet1ValueArray[r, ansStartColNum + 1].ToString();
                            tempAnswers.text_tagged  = sheet1ValueArray[r, ansStartColNum + 2] == null ? "" : sheet1ValueArray[r, ansStartColNum + 2].ToString();
                            tempAnswers.text_syn     = sheet1ValueArray[r, ansStartColNum + 3] == null ? "" : sheet1ValueArray[r, ansStartColNum + 3].ToString();
                            tempAnswers.answer_start = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 4]);
                            tempAnswers.answer_end   = Convert.ToInt32(sheet1ValueArray[r, ansStartColNum + 5]);

                            List <ETRI_Answers> tempAnswersList = new List <ETRI_Answers>();

                            tempAnswersList.Add(tempAnswers);
                            tempQas.answers = tempAnswersList.Cast <object>().ToList();


                            tempQasList.Add(tempQas);
                            currentParagraph = Convert.ToInt32(sheet1ValueArray[r, 38]);                                                                                                         //36

                            if (r + 1 <= sheet1ValueArray.GetLength(0))                                                                                                                          // 다음 목표 row가 sheet1ValueArray의 1차 배열 길이를 넘지 않을때
                            {
                                if (currentParagraph != Convert.ToInt32(sheet1ValueArray[r + 1, 38]))                                                                                            // 현재 row의 소속 paragraph 값과 다음 row의 소속 paragraph값을 비교하여 같지 않다면
                                {
                                    EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Cast <ETRI_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList(); // Qas 리스트 삽입
                                    tempQasList = new List <ETRI_Qas>();
                                    if (paragraphCount < EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Count - 1)                                                               // paragraphCount 값이 현재 Data에서의 끝에 도달하기 전에는 이렇게 처리
                                    {
                                        paragraphCount++;
                                    }
                                    else    // 도달하고 난 후에는 이렇게 처리
                                    {
                                        dataCount++;
                                        paragraphCount = 0;
                                    }
                                }
                            }

                            if (r == sheet1ValueArray.GetLength(0))  // 현재 row가 마지막일때
                            {
                                EtopTag.data.Cast <ETRI_Data>().ToList()[dataCount].paragraphs.Cast <ETRI_Paragraphs>().ToList()[paragraphCount].qas = tempQasList.Cast <object>().ToList();
                            }
                        }

                        // ** JSON 파일로 저장
                        m_savePath = Path.ChangeExtension(m_path, "json");
                        FileInfo fi = new FileInfo(m_savePath);
                        if (fi.Exists)  // 파일이 이미 존재하면 삭제
                        {
                            fi.Delete();
                        }

                        string saveJSONText;
                        bool   m_EtoJNullRemoveCheck = false;
                        if (m_EtoJNullRemoveCheck)
                        {
                            saveJSONText = JsonConvert.SerializeObject(EtopTag, Formatting.Indented, new JsonSerializerSettings
                            {
                                NullValueHandling = NullValueHandling.Ignore    // Null값 객체 제거
                            }
                                                                       );
                        }
                        else
                        {
                            saveJSONText = JsonConvert.SerializeObject(EtopTag, Formatting.Indented, new JsonSerializerSettings
                            {
                                NullValueHandling = NullValueHandling.Include   // Null값 객체 포함
                            }
                                                                       );
                        }

                        using (StreamWriter sw = new StreamWriter(m_savePath))
                        {
                            sw.Write(saveJSONText);
                        }

                        #endregion
                    }
                }
                catch (Exception e)
                {
                    if (excelOpen)
                    {
                        Marshal.FinalReleaseComObject(range);
                        Marshal.FinalReleaseComObject(objWorksheet);

                        Marshal.FinalReleaseComObject(objWorksheets);

                        objWorkbook.Close(false, missing, missing);
                        objWorkbooks.Close();
                        objApp.Quit();

                        Marshal.FinalReleaseComObject(objWorkbook);
                        Marshal.FinalReleaseComObject(objWorkbooks);
                        Marshal.FinalReleaseComObject(objApp);

                        objApp = null;
                    }


                    return("예외처리 된 오류 발생.\r\n파일: " + m_path + "오류 이유:" + e.ToString());
                }
            }
            return("모든 파일 변환 성공");
        }
Esempio n. 55
0
        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++;
            }
        }
Esempio n. 56
0
        private static void excelFile(System.Data.DataTable dtTable, List <Dictionary <string, object> > list)
        {
            // File save dialog
            SaveFileDialog saveFileDialog = new SaveFileDialog();

            saveFileDialog.Filter = "Execl files (*.xls)|*.xls";

            saveFileDialog.FilterIndex      = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt     = true;
            saveFileDialog.FileName         = null;
            saveFileDialog.Title            = "Save path of the file to be exported";

            //string filepath = AppDomain.CurrentDomain.BaseDirectory;

            Excel.Application xlApp    = null;
            Excel.Workbooks   wkbooks  = null;
            Excel.Workbook    wkbook   = null;
            Excel.Sheets      wksheets = null;
            Excel.Worksheet   wksheet  = null;

            try
            {
                xlApp    = new Excel.Application();
                wkbooks  = xlApp.Workbooks;
                wkbook   = wkbooks.Add();
                wksheets = wkbook.Sheets;
                wksheet  = wksheets.Add();



                Console.WriteLine(list[0].Values);
                wksheet.Name = "APPLE";

                try
                {
                    Console.WriteLine("It is working.");
                    for (var i = 0; i < dtTable.Columns.Count; i++)
                    {
                        wksheet.Cells[1, i + 1] = dtTable.Columns[i].ColumnName;
                    }

                    //rows
                    for (var i = 0; i < dtTable.Rows.Count; i++)
                    {
                        for (var j = 0; j < dtTable.Columns.Count; j++)
                        {
                            wksheet.Cells[i + 2, j + 1] = dtTable.Rows[i][j];
                        }
                    }
                    //wkbook.SaveAs(saveFileDialog, XlFileFormat.xlExcel8,false,
                    //false, false,false, XlSaveAsAccessMode.xlNoChange, Type.Missing,
                    //Type.Missing, Type.Missing,Type.Missing, Type.Missing);

                    Console.WriteLine("Processing!");

                    Console.WriteLine("File saved.");

                    wkbooks.Close();
                    wkbook.Close(false, Missing.Value, Missing.Value);
                    xlApp.Quit();
                }
                catch (Exception exp)
                {
                }
            }
            catch (Exception e)
            {
                //Console.WriteLine(e.ToString());
            }

            finally
            {
                if (wksheets != null)
                {
                    Marshal.ReleaseComObject(wksheets);
                }
                if (wkbook != null)
                {
                    Marshal.ReleaseComObject(wkbook);
                }
                if (wkbooks != null)
                {
                    Marshal.ReleaseComObject(wkbooks);
                }
                if (xlApp != null)
                {
                    Marshal.ReleaseComObject(xlApp);
                }
            }
        }
Esempio n. 57
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("Файл не был найден, обновите базы данных"); }
 }
Esempio n. 58
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) + "\"";
            }
        }
Esempio n. 59
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;
        }
Esempio n. 60
0
        static void Main(string[] args)
        {
            Console.WriteLine("Interop Assemblies Performance Test - 15000 Cells.");
            Console.WriteLine("Write simple text, change Font, NumberFormat, WrapText and add a comment.");

            // start excel, and get a new sheet reference
            Excel.Application excelApplication = CreateExcelApplication();
            Excel.Workbooks   books            = excelApplication.Workbooks;
            Excel.Workbook    book             = books.Add(Missing.Value);
            Excel.Sheets      sheets           = book.Worksheets;
            Excel.Worksheet   sheet            = sheets.Add() as Excel.Worksheet;

            // do test 10 times
            List <MarshalByRefObject> comReferencesList = new List <MarshalByRefObject>();
            List <TimeSpan>           timeElapsedList   = new List <TimeSpan>();

            for (int i = 1; i <= 10; i++)
            {
                sheet.UsedRange.ClearComments();
                DateTime timeStart = DateTime.Now;
                for (int y = 1; y <= 15000; y++)
                {
                    string      rangeAdress = "$A" + y.ToString();
                    Excel.Range cellRange   = sheet.get_Range(rangeAdress);
                    cellRange.Value = "value";
                    Excel.Font font = cellRange.Font;
                    font.Name = "Verdana";
                    cellRange.NumberFormat = "@";
                    cellRange.WrapText     = true;
                    Excel.Comment sampleComment = cellRange.AddComment("Sample Comment");
                    comReferencesList.Add(font as MarshalByRefObject);
                    comReferencesList.Add(sampleComment as MarshalByRefObject);
                    comReferencesList.Add(cellRange as MarshalByRefObject);
                }
                TimeSpan timeElapsed = DateTime.Now - timeStart;

                // display info and dispose references
                Console.WriteLine("Time Elapsed: {0}", timeElapsed);
                timeElapsedList.Add(timeElapsed);
                foreach (var item in comReferencesList)
                {
                    Marshal.ReleaseComObject(item);
                }
                comReferencesList.Clear();
            }

            // display info & log to file
            TimeSpan timeAverage = AppendResultToLogFile(timeElapsedList, "Test3-Interop.log");

            Console.WriteLine("Time Average: {0}{1}Press any key...", timeAverage, Environment.NewLine);
            Console.Read();

            // release & quit
            Marshal.ReleaseComObject(sheet);
            Marshal.ReleaseComObject(sheets);
            Marshal.ReleaseComObject(book);
            Marshal.ReleaseComObject(books);

            excelApplication.Quit();
            Marshal.ReleaseComObject(excelApplication);
        }