public void ExpotToExcel(DataGridView dataGridView1,string SaveFilePath)
        {
            xlApp = new Excel.Application();
               xlWorkBook = xlApp.Workbooks.Add(misValue);
               xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
               int i = 0;
               int j = 0;

               for (i = 0; i <= dataGridView1.RowCount - 1; i++)
               {
               for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
               {
                   DataGridViewCell cell = dataGridView1[j, i];
                   xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
               }
               }

               xlWorkBook.SaveAs(SaveFilePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
               xlWorkBook.Close(true, misValue, misValue);
               xlApp.Quit();

               releaseObject(xlWorkSheet);
               releaseObject(xlWorkBook);
               releaseObject(xlApp);

               MessageBox.Show("Your file is saved" + SaveFilePath);
        }
Ejemplo n.º 2
2
        /// <exception cref="FileNotFoundException"><c>FileNotFoundException</c>.</exception>
        /// <exception cref="Exception"><c>Exception</c>.</exception>
        public IList<IDataRecord> GetDataFromExcel(string filePath)
        {
            try
            {
                Log.Info("Начало импорта");
                if (!File.Exists(filePath))
                    throw new FileNotFoundException(string.Format("Файл не найден. [{0}]", filePath));

                Excelapp = new Application { Visible = false };
                Workbook = Excelapp.Workbooks.Open(filePath, 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);
                ReadDoc();
            }
            catch( Exception e)
            {
                Log.Error("Ошибка импорта", e);
                throw;
            }
            finally
            {
                Workbook.Close();
                Excelapp.Quit();
                Excelapp = null;
            }
            return DataRecords;
        }
Ejemplo n.º 3
0
        private void button1_Click(object sender, EventArgs e)
        {
            MyApp = new Excel.Application();
            MyApp.Visible = false;
            MyBook = MyApp.Workbooks.Open(path);
            MySheet = (Excel.Worksheet)MyBook.Sheets[1];
            lastrow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;

            BindingList<Dompet> DompetList = new BindingList<Dompet>();

            for (int index = 2; index <= lastrow; index++)
            {
                System.Array MyValues = 
                    (System.Array)MySheet.get_Range
                    ("A" + index.ToString(),"F" + index.ToString()).Cells.Value;
                DompetList.Add(new Dompet {
                    JmlPemesanan = MyValues.GetValue(1,1).ToString(),
                    JmlPekerja = MyValues.GetValue(1,2).ToString(),
                    Peralatan = MyValues.GetValue(1,3).ToString(),
                    JenisKulit = MyValues.GetValue(1,4).ToString(),
                    ModelDompet = MyValues.GetValue(1,5).ToString(),
                    Prediksi = MyValues.GetValue(1,6).ToString()
                });
            }
            dataGridView1.DataSource = (BindingList<Dompet>)DompetList;
            dataGridView1.AutoResizeColumns();
        }
Ejemplo n.º 4
0
        public LoadandMatchMetricsNames(String dashboardFile)
        {
            try
            {
                app = new Excel.Application();
                app.Visible = true;
                dashboard = app.Workbooks.Open(dashboardFile,
                    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);

              /*  for (int i = 0; i < metricsFiles.Length; i++)
                {
                    metrics = app.Workbooks.Open(metricsFiles[i],
                    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);

                    //this is where we read all the data into some sort of array
                    //ExcelScanInternal(metrics);
                    metrics.Close(false, metricsFiles[i], null);
                } */

                //cleanup
                //dashboard.Close(false, dashboardFile, null);

            }
            catch (Exception ex) { Console.WriteLine(ex.Message); }
        }
Ejemplo n.º 5
0
 public Workbook(Excel.Workbook wb, Excel.Application app, Action dispose_callback)
 {
     _app = app;
     _wb = wb;
     _wb_name = wb.Name;
     _dispose_callback = dispose_callback;
 }
Ejemplo n.º 6
0
 public ExcelHelper(string _filePath)
 {
     filePath = _filePath;
     excelApp = new Excel.Application();
     object objOpt = System.Reflection.Missing.Value;
     wbclass = (Excel.Workbook)excelApp.Workbooks.Open(_filePath, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
 }
Ejemplo n.º 7
0
        private static void RemovePageRecords(Workbook wb)
        {
            var ws = wb.Sheets[1] as Excel.Worksheet;
            if (ws == null)
                return;

            var va = ws.UsedRange.Value2 as object[,];
            if (va == null)
                return;

            var rows = va.GetUpperBound(0);
            var cols = va.GetUpperBound(1);
            var pageCol = -1;
            Range delRange = null;

            for (var row = 1; row <= rows; row++)
            for (var col = (pageCol < 0 ? 1 : pageCol); col <= (pageCol < 0 ? cols : pageCol); col++)
                if (IsPageTotal(va[row, col]))
                {
                    pageCol = col;
                    delRange = delRange == null ? ws.Rows[row] : _xl.Union(delRange, ws.Rows[row]) as Range;
                }

            if (delRange != null && delRange.Rows.Count > 0)
                delRange.EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);
        }
Ejemplo n.º 8
0
    /// <summary>
    /// Class constructor does all the work
    /// </summary>
    /// <param name="excelApp"></param>
    /// <param name="fileName"></param>
    public StudentWorkbook( Excel.Application excelApp, string fileName )
    {
      try  // to open the student's spreadsheet
      {
        excelWorkbook = excelApp.Workbooks.Open( fileName, 0,
            true, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true,  // read only
            false, 0, true, false, false );
      }
      catch ( Exception e )
      {
        Console.WriteLine( "error: " + e.Message );
        Console.WriteLine( "Could not open spreadsheet " + fileName );
      }

      Excel.Sheets excelSheets = excelWorkbook.Worksheets;  // get the Worksheets collection
      Excel.Worksheet excelWorksheet = excelSheets[ 1 ];    // get the first one

      // get the Team Number cell
      Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range( "B4", "B4" );

      // try to convert this cell to an integer
      if ( ( teamNumber = TryForInt( excelCell.Value ) ) == 0 )
      {
        Console.WriteLine( "\nTeam number invalid in " + fileName + "\n" );
      }

      // get the scores cells
      scores = excelWorksheet.get_Range( "B7", "B15" );

      // get the Additional Comments cell
      comments = excelWorksheet.get_Range( "B18", "B18" );

    }  // end of StudentWorkbook()
Ejemplo n.º 9
0
        private void Init()
        {
            xlApp = new Excel.Application();

            xlWorkBook = xlApp.Workbooks.Open(name, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlSh = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        }
Ejemplo n.º 10
0
        public getData readData()
        {
            getData.dateT.Clear();
            getData.prj.Clear();
            getData.descr.Clear();
            getData.taskTime.Clear();
            notes.Clear();
            ExcelApp = new Excel.Application();
            ExcelApp.Visible = false;
            WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу
            WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets[1]; //Получаем ссылку на лист 1

            //excelcells = excelworksheet.get_Range("D215", Type.Missing); //Выбираем ячейку для вывода A1
            // WorkSheetExcel.Cells[i, 1].Text.ToString() != ""

            for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++)
            {

                getData.dateT.Add(WorkSheetExcel.Cells[i, 1].Text.ToString());
                getData.prj.Add(WorkSheetExcel.Cells[i, 2].Text.ToString());
                getData.descr.Add(WorkSheetExcel.Cells[i, 3].Text.ToString());
                getData.taskTime.Add(WorkSheetExcel.Cells[i, 4].Text.ToString());
            }

            WorkBookExcel.Close(false, Type.Missing, Type.Missing); //закрыл не сохраняя
            ExcelApp.Quit();
            GC.Collect();

            getData.dateT.Reverse();
            getData.prj.Reverse();
            getData.descr.Reverse();
            getData.taskTime.Reverse();

            return getData;
        }
        //*************************************************************************
        //  Constructor: GroupByVertexAttributeDialog()
        //
        /// <overloads>
        /// Initializes a new instance of the <see
        /// cref="GroupByVertexAttributeDialog" /> class.
        /// </overloads>
        ///
        /// <param name="workbook">
        /// Workbook containing the graph contents.
        /// </param>
        //*************************************************************************
        public GroupByVertexAttributeDialog(
            Microsoft.Office.Interop.Excel.Workbook workbook
            )
        {
            InitializeComponent();

            m_oWorkbook = workbook;

            // Instantiate an object that saves and retrieves the user settings for
            // this dialog.  Note that the object automatically saves the settings
            // when the form closes.

            m_oGroupByVertexAttributeDialogUserSettings =
            new GroupByVertexAttributeDialogUserSettings(this);

            if ( ExcelUtil.TryGetTable(m_oWorkbook, WorksheetNames.Vertices,
            TableNames.Vertices, out m_oVertexTable) )
            {
            cbxVertexColumnName.PopulateWithTableColumnNames(m_oVertexTable);
            }

            cbxVertexColumnFormat.PopulateWithObjectsAndText(
            ExcelColumnFormat.Other, "Categories",
            ExcelColumnFormat.Number, "Numbers",
            ExcelColumnFormat.Date, "Dates",
            ExcelColumnFormat.Time, "Times",
            ExcelColumnFormat.DateAndTime, "Dates with times"
            );

            DoDataExchange(false);

            AssertValid();
        }
Ejemplo n.º 12
0
        public ClusteringManager()
        {
            //prepare Excel objects:
            m_ObjExcel = new Microsoft.Office.Interop.Excel.Application();
            m_ObjWorkBook = m_ObjExcel.Workbooks.Open(
                MethodInputResponse.INPUT_FILE_PATH,
                0,
                false,
                5,
                "",
                "",
                false,
                Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,
                "",
                true,
                false,
                0,
                true,
                false,
                false);
            m_ObjWorkSheet1 = (myExcel.Worksheet)m_ObjWorkBook.Sheets[1];

            myExcel.Sheets xlSheets = m_ObjWorkBook.Sheets as myExcel.Sheets;
            for (int i = 3; i < MAX_CLUSTERS_NUMBER; i++)
            {
                xlSheets.Add(Type.Missing, m_ObjWorkSheet1, Type.Missing, Type.Missing);
            }
        }
Ejemplo n.º 13
0
 /// <summary>
 /// Метод закрытия приложения Excel
 /// </summary>
 public void CloseExcelDoc()
 {
     excelBook.Close(falseObj, missingObj, missingObj);
     excelApp.Quit();
     excelBook = null;
     excelApp = null;
 }
Ejemplo n.º 14
0
        /// <summary>
        /// 构造函数,将一个已有Excel工作簿作为模板,并指定输出路径
        /// </summary>
        /// <param name="templetFilePath">Excel模板文件路径</param>
        /// <param name="outputFilePath">输出Excel文件路径</param>
        public ExcelBase(string templetFilePath, string outputFilePath)
        {
            if (templetFilePath == null)
                throw new Exception("Excel模板文件路径不能为空!");

            if (outputFilePath == null)
                throw new Exception("输出Excel文件路径不能为空!");

            if (!File.Exists(templetFilePath))
                throw new Exception("指定路径的Excel模板文件不存在!");

            this.templetFile = templetFilePath;
            this.outputFile = outputFilePath;

            //创建一个Application对象并使其可见
            beforeTime = DateTime.Now;
            app = new Excel.ApplicationClass();
            app.Visible = true;
            afterTime = DateTime.Now;

            //打开模板文件,得到WorkBook对象
            workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing,
             missing, missing, missing, missing, missing, missing, missing);

            //得到WorkSheet对象
            workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
        }
Ejemplo n.º 15
0
        public TaskPriority()
        {
            InitializeComponent();
            missing = System.Reflection.Missing.Value;
            config_data.ConfigFile = Environment.GetEnvironmentVariable("USERPROFILE")+"\\IntCallBack.xls";
            xlApp = new msexcel.Application();
            time_wasting = false;

            if (File.Exists(config_data.ConfigFile))
            {
                xlWorkBook = xlApp.Workbooks.Open(config_data.ConfigFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                xlWorkSheet = (msexcel.Worksheet) xlWorkBook.Worksheets.get_Item(1);

                double dummy = (double) (xlWorkSheet.Cells[1, 2] as msexcel.Range ).Value ;
                config_data.PopUp = ((int)dummy == 1) ? true : false;
                config_data.RFrequency = (int)(xlWorkSheet.Cells[2, 2] as msexcel.Range).Value;
                config_data.Urgent_Hrs = (int)(xlWorkSheet.Cells[3, 2] as msexcel.Range).Value;
                config_data.Urgent_Mins = (int)(xlWorkSheet.Cells[4, 2] as msexcel.Range).Value;
                config_data.task1 = (string) (xlWorkSheet.Cells[5, 2] as msexcel.Range).Value;
                config_data.task2 = (string)(xlWorkSheet.Cells[6, 2] as msexcel.Range).Value;
                config_data.task3 = (string)(xlWorkSheet.Cells[7, 2] as msexcel.Range).Value;
                config_data.task4 = (string)(xlWorkSheet.Cells[8, 2] as msexcel.Range).Value;
                re_load_flag = true;

            }
            else
            {

                xlWorkBook = xlApp.Workbooks.Add(missing);
                xlWorkSheet = xlWorkBook.Worksheets.get_Item(1);

                config_data.PopUp = true;
                config_data.RFrequency = 3;
                config_data.Urgent_Hrs = 8;
                config_data.Urgent_Mins = 0;
                config_data.task1 = config_data.task2 = config_data.task3 = config_data.task4 = "";

                xlWorkSheet.Cells[1, 1] = "PopUP";
                xlWorkSheet.Cells[2, 1] = "Frequency";
                xlWorkSheet.Cells[3, 1] = "Urgent Hrs";
                xlWorkSheet.Cells[4, 1] = "Urgent Mins";
                xlWorkSheet.Cells[5,1] = "Task 1";
                xlWorkSheet.Cells[6,1] = "Task 2";
                xlWorkSheet.Cells[7,1] = "Task 3";
                xlWorkSheet.Cells[8,1] = "Task 4";

                xlWorkSheet.Cells[1, 2] = (config_data.PopUp == true) ? "1" : "2";
                xlWorkSheet.Cells[2, 2] = config_data.RFrequency.ToString();
                xlWorkSheet.Cells[3, 2] = config_data.Urgent_Hrs.ToString();
                xlWorkSheet.Cells[4, 2] = config_data.Urgent_Mins.ToString();
                xlWorkSheet.Cells[5, 2] = config_data.task1;
                xlWorkSheet.Cells[6, 2] = config_data.task1;
                xlWorkSheet.Cells[7, 2] = config_data.task1;
                xlWorkSheet.Cells[8, 2] = config_data.task1;

                xlWorkBook.SaveAs(config_data.ConfigFile, msexcel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, msexcel.XlSaveAsAccessMode.xlShared, missing, missing, missing, missing, missing);
                //xlWorkBook.Close();
                re_load_flag = false;
            }
        }
Ejemplo n.º 16
0
        public void reporttoexcel_clients(List<string> station_name_list, List<int> station_turnover_list, List<int> station_avg_list)
        {



            Eapp.Visible = true;

            string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
            book = Eapp.Workbooks.Open(path + @"\отчет_клиента_шаблон.xlsx");

            excel.Worksheet sheet = (excel.Worksheet)book.Worksheets.get_Item(1);

            excel.Range range_sheet = sheet.UsedRange;

            for (int i = 2; i < station_name_list.Count + 2; i++)
            {

                excel.Range range_cur = range_sheet.Cells[i, 1];

                range_cur.Value2 = station_name_list[i - 2];

                range_cur = range_sheet.Cells[i, 2];
                range_cur.Value2 = station_turnover_list[i - 2];

                range_cur = range_sheet.Cells[i, 3];
                range_cur.Value2 = station_avg_list[i - 2];


            }

            book.SaveAs(path + @"\reports\отчет_клиента.xlsx");
            Eapp.Quit();

        }
Ejemplo n.º 17
0
        private void Form1_Load(object sender, EventArgs e)
        {
            app = new Excel.Application();
            app.Visible = false;

            workbook = app.Workbooks.Open(Application.StartupPath + "\\Дни рождения сотрудников.xls");
            worksheet = workbook.ActiveSheet;

            int i = 0;
            string today = DateTime.Now.ToString("dd.MM");
            string tomorrow = DateTime.Now.AddDays(1).ToString("dd.MM");

            for (i = 1; i <= worksheet.UsedRange.Rows.Count; i++)
            {
                if (worksheet.Cells[2][i].Value == "Іб та ПД" || worksheet.Cells[3][i].Value == "Іб та ПД" || worksheet.Cells[2][i].Value == "Інформаційної безпеки та передачі даних" || worksheet.Cells[3][i].Value == "Інформаційної безпеки та передачі даних")
                {
                    string birthday = worksheet.Cells[4][i].Value.ToString("dd.MM");
                    if (birthday == today)
                    {
                        label1.Text = "";
                        label1.Text += "Сегодня свой день рождения отмечает " + worksheet.Cells[1][i].Value;
                    }
                    if (birthday == tomorrow)
                    {
                        label1.Text = "";
                        label1.Text += "Завтра свой день рождения отмечает " + worksheet.Cells[1][i].Value;
                    }
                }
            }
            app.Quit();
        }
Ejemplo n.º 18
0
        public void export()
        {
            try
            {

                //excelApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
                //wb = excelApp.Workbooks.Add();
                wb = (Excel.Workbook)Globals.ThisAddIn.Application.ActiveWorkbook;
                ws = wb.Worksheets.get_Item(1) as Excel.Worksheet;

                // 데이타 넣기
                int r = 1;
                foreach (var d in this.exceptionList_)
                {
                    ws.Cells[r, 1] = d.Message;
                    r++;
                }

                //this.wb.Worksheets.Add(ws);
            }
            finally 
            {
                // Clean up
                ReleaseExcelObject(ws);
                ReleaseExcelObject(wb);
                ReleaseExcelObject(excelApp);
            }
        }
Ejemplo n.º 19
0
 public static void InitializeExcel()
 {
     _myApp = new Excel.Application {Visible = false};
     _myBook = _myApp.Workbooks.Open(ExcelPath);
     _mySheet = (Excel.Worksheet)_myBook.Sheets[1]; // Explict cast is not required here
     _lastRow = _mySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
 }
        /// <summary>
        /// Function for initializing access to and validating the resources found in the given directory.
        /// </summary>
        public void InitializePlanGenerator()
        {
            //A message of where the resource files are located
            Console.WriteLine("EventFiles: " + resourcepath);

            try
            {
                //Initialize excel accesser class
                excel = new Excel.Application();

                //Path to the excel sheet is created
                var excelSheetPath = Path.GetFullPath(resourcepath);

                //The excel arc found at the resource path is opened and a gateway is initialized
                //TODO: create a system for choosing the correct file
                CurrentWorkBook = excel.Workbooks.Open(excelSheetPath);

                //The plangenerationhandler function is called
                PlanGenerationHandler();
            }
            catch (Exception) { }
            finally
            {
                EvalMessage();
                //COM objects countermeasure - this is needed for the excel processes to be closed
                Process[] excelProcs = Process.GetProcessesByName("EXCEL");
                foreach (Process proc in excelProcs)
                {
                    proc.Kill();
                }
            }
        }
Ejemplo n.º 21
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;
        }
Ejemplo n.º 22
0
        static void Main(string[] args)
        {
            //GetSPList("http://sharepoint/sites/fim/ISV9-3", "v-jianzh", "isnes21)$MAM", "fareast.corp.microsoft.com");

            //foreach (ListItem listItem in listItems)
            //{
            //    Console.WriteLine("ID:{0} Application Name:{1}", listItem.Id, listItem["Application_x0020_Name"].ToString());
            //}
            xApp = new Excel.Application();
            xApp.Visible = true;
            xbook = xApp.Workbooks.Open("C:\\share\\temp.xlsx");

            GetVisibleFormExcel();

            //for (int i = 0; i < nameArr.Count;i++ )
            //{
            //    Console.WriteLine("Name:{0} IE:{1} FireFox:{2} Chrome:{3}", nameArr[i],ieArr[i],firefoxArr[i],chromeArr[i]);
            //}

            UpdateToMasterList();

            xbook = null; xApp.Quit(); xApp = null;

            Console.ReadLine();
        }
Ejemplo n.º 23
0
 public ComExportExcel(string templatePath)
 {
     xlApp = new ComExcel.Application();
     xlWorkBook = xlApp.Workbooks.Open(templatePath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, false, 0, true, 1, 0);
     //xlWorkBook = xlApp.Workbooks.Open(templatePath, ReadOnly:false, Editable:true );
     xlWorkSheet = (ComExcel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
 }
Ejemplo n.º 24
0
 public static void openXL(string xlp)
 {
     xla = new Excel.Application();
     xlw = xla.Workbooks.Open(xlp);
     xls = xlw.Worksheets.get_Item(1);
     xlr = xls.UsedRange;
 }
Ejemplo n.º 25
0
        public void ReadProcesses()
        {
            count = 0;
            ExcelApp = new Excel.Application();
            ExcelApp.Visible = false;
            WorkBookExcel = ExcelApp.Workbooks.Open(_filePath, false); //открываем книгу

            //Читаем данные по проектам
            WorkSheetExcel = (Excel.Worksheet)WorkBookExcel.Sheets["Processes"]; //Получаем ссылку на лист Processes

            List<string> row = new List<string>();
            int n = 6;

            for (int i = 2; WorkSheetExcel.Cells[i, 1].Text.ToString() != ""; i++)
            {
                row = new List<string>();
                for (int j = 1; j < n; j++) row.Add(WorkSheetExcel.Cells[i, j].Text.ToString()); //строка массива заполняется просто суммой i и j
                Mas.Add(row); //строка добавляется в массив
                count++;
            }
            //test = WorkSheetExcel.Cells[2, 1];

            WorkBookExcel.Close(false, Type.Missing, Type.Missing);
            ExcelApp.Quit();
            GC.Collect();
        }
Ejemplo n.º 26
0
        //加载
        private void Form1_Load(object sender, EventArgs e)
        {

            panel1.Visible = false;
            app = new MSExcel.Application();
            app.Visible = false;

            book = app.Workbooks.Open(@"D:\template.xls");
            sheet = (MSExcel.Worksheet)book.ActiveSheet;



            //串口设置默认选择项
            cbSerial.SelectedIndex = 1;         //note:获得COM9口,但别忘修改
            cbBaudRate.SelectedIndex = 5;
            cbDataBits.SelectedIndex = 3;
            cbStop.SelectedIndex = 0;
            cbParity.SelectedIndex = 0;
            //sp1.BaudRate = 9600;

            Control.CheckForIllegalCrossThreadCalls = false;    //这个类中我们不检查跨线程的调用是否合法(因为.net 2.0以后加强了安全机制,,不允许在winform中直接跨线程访问控件的属性)
            sp1.DataReceived += new SerialDataReceivedEventHandler(sp1_DataReceived);
            //sp1.ReceivedBytesThreshold = 1;

            radio1.Checked = true;  //单选按钮默认是选中的
            rbRcvStr.Checked = true;

            //准备就绪              
            sp1.DtrEnable = true;
            sp1.RtsEnable = true;
            //设置数据读取超时为1秒
            sp1.ReadTimeout = 1000;

            sp1.Close();
        }
        private void drawGraph()
        {

            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            try
            {
                xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //Get all the sheets in the workbook

                while (thread1.IsAlive)
                {
                    //son satır bulunuyor excel dosyasındaki
                    Excel.Range last = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
                    Excel.Range range = xlWorkSheet.get_Range("A1", last);
                    int lastUsedRow = last.Row;
                    int lastUsedColumn = last.Column;

                    string ReceiveData = myport.ReadLine(); // comdan degeri okuyuruz

                    // alınan degerdeki stringleri temizleyerek sadece double değeri yakalıyor
                    string[] HeatingData = ReceiveData.Split(':');
                    string[] HeatingData2 = HeatingData[1].Split('D');
                    var result = HeatingData2[0];
                    double heating = Convert.ToDouble(result);

                    theTime = DateTime.Now; // anlik olarak zamani ogreniyoruz!
                    string zaman = theTime.ToString("yyyy/MM/dd HH:mm:ss");

                    Thread.Sleep(1000); // ilk threadi anlik olarak durduruyor ve Invoke ile GUI threadini ulasip cizdiriyor! 
                    this.Invoke((MethodInvoker)delegate
                       {
                           chart1.Series["Series1"].Points.AddY(result);
                           // excel dosyasındaki son yazılan satırdan bir sonraki satıra sıcaklığı yazdırıyor
                           xlWorkSheet.Cells[lastUsedRow+1, 2] = (heating / 100);
                           xlWorkSheet.Cells[lastUsedRow + 1, 1] = zaman;
                       });
                }
            }
            catch
            {
                // MessageBox.Show("Dosya bulunamadı");
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                xlWorkSheet.Cells[1, 1] = "Zaman";
                xlWorkSheet.Cells[1, 2] = "Sıcaklık Celcius";

                xlWorkBook.SaveAs("C:\\Users\\DELL\\Desktop\\ReadFromSerial_DrawGraphic\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                MessageBox.Show("Dosya oluşturuldu , proje klasörünüzde bulunmaktadır");
            }
        }
Ejemplo n.º 28
0
        public void BackUpCreate(string pach)
        {
            IDbCommandBuilder bilder = database.GetDbCommandBuilder();

            DataTable dt = new DataTable();
            DbConnection connect = (DbConnection)bilder.Connection;
            var cmd = connect.CreateCommand();
            string FilePath = SearchFilePath(pach);
            excelApp = new Microsoft.Office.Interop.Excel.Application();
            workbook = excelApp.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);

            DataTable oriTable = new DataTable();
            connect.Open();

            try
            {
                foreach (Microsoft.Office.Interop.Excel.Worksheet ws in workbook.Sheets)
                {
                    originalTableName = ws.Name;
                    oriTable = originalData.Tables.Add(originalTableName);
                    var SearchSQL = new StringBuilder();
                    SearchSQL.Append("   SELECT * \n");
                    SearchSQL.Append("     FROM \n");
                    SearchSQL.Append(originalTableName);
                    cmd.CommandText = SearchSQL.ToString();
                    var reader = cmd.ExecuteReader();
                    originalData.Tables[originalTableName].Load(reader);
                    CreateXlsDs createXls = CreateXlsDs.GetInstance();
                    createXls.SetSchema(originalData.Clone());

                    reader.Dispose();
                }
            }
            catch (Exception ex)
            {

                ErrorMessages.Append(ex.Message);
            }
            finally
            {
                if (workbook != null)
                {
                    #region Clean Up Close the workbook and release all the memory.
                    workbook.Close(false, pach, Missing.Value);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                    #endregion
                }
                workbook = null;

                if (excelApp != null)
                {
                    excelApp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                }
                excelApp = null;
                connect.Close();
                connect.Dispose();
            }
        }
Ejemplo n.º 29
0
 public static void InitializeExcel()
 {
     MyApp = new Excel.Application();
     MyApp.Visible = false;
     MyBook = MyApp.Workbooks.Open(DB_PATH);
     MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explict cast is not required here
     lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
 }
Ejemplo n.º 30
0
        private void btnGenerate_Click(object sender, EventArgs e)
        {
            if (cbModel.SelectedIndex == 0)
            {
                MessageBox.Show("Select Model ...");
                return;
            }
            int dayofyr = dateTimePicker1.Value.DayOfYear;
            int model   = cbModel.SelectedIndex;

            //System.Data.OleDb.OleDbDataAdapter MyCommand,MyCommand1;
            System.Data.DataSet DtSet;
            DtSet = new System.Data.DataSet();
            DataSet dtt        = new DataSet();
            string  resultpath = "";

            switch (model)
            {
            case 1:    //LMS
                dtt.Clear();
                resultpath = Properties.Settings.Default.lsrresult;
                //MyCommand = GetData(dayofyr, resultpath);
                Excel.Application xlApp      = new Excel.Application();
                Excel.Workbook    xlWorkbook = xlApp.Workbooks.Open(resultpath, ReadOnly: false);
                xlApp.Visible = false;
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
                Excel.Range      xlRange     = xlWorksheet.UsedRange;
                System.Data.OleDb.OleDbConnection  MyConnection;
                System.Data.OleDb.OleDbDataAdapter MyCommand;
                MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + resultpath + "';Extended Properties=Excel 8.0;");
                MyCommand    = new System.Data.OleDb.OleDbDataAdapter("select * from [Results$] where Day_Number=" + dayofyr, MyConnection);
                MyCommand.Fill(dtt).ToString();
                MyCommand.Dispose();
                MyConnection.Close();
                xlWorkbook.Close();
                xlApp.Quit();
                break;

            case 2:    //SVM
                dtt.Clear();
                resultpath = Properties.Settings.Default.svmresult;
                Excel.Application xlApp1      = new Excel.Application();
                Excel.Workbook    xlWorkbook1 = xlApp1.Workbooks.Open(resultpath, ReadOnly: false);
                xlApp1.Visible = false;
                Excel._Worksheet xlWorksheet1 = xlWorkbook1.Sheets[1];
                Excel.Range      xlRange1     = xlWorksheet1.UsedRange;
                System.Data.OleDb.OleDbConnection  MyConnection1;
                System.Data.OleDb.OleDbDataAdapter MyCommand1;
                MyConnection1 = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + resultpath + "';Extended Properties=Excel 8.0;");
                MyCommand1    = new System.Data.OleDb.OleDbDataAdapter("select * from [Results$] where Day_Number=" + dayofyr, MyConnection1);
                MyCommand1.Fill(dtt).ToString();
                MyCommand1.Dispose();
                MyConnection1.Close();
                xlWorkbook1.Close();
                xlApp1.Quit();
                //MyCommand = GetData(dayofyr, resultpath);
                //MyCommand.Fill(dtt);
                break;

            case 3:    //Both
                dtt.Clear();
                resultpath = Properties.Settings.Default.lsrresult;
                Excel.Application xlApp2      = new Excel.Application();
                Excel.Workbook    xlWorkbook2 = xlApp2.Workbooks.Open(resultpath, ReadOnly: false);
                xlApp2.Visible = false;
                Excel._Worksheet xlWorksheet2 = xlWorkbook2.Sheets[1];
                Excel.Range      xlRange2     = xlWorksheet2.UsedRange;
                System.Data.OleDb.OleDbConnection  MyConnection2;
                System.Data.OleDb.OleDbDataAdapter MyCommand2;
                MyConnection2 = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + resultpath + "';Extended Properties=Excel 8.0;");
                MyCommand2    = new System.Data.OleDb.OleDbDataAdapter("select * from [Results$] where Day_Number=" + dayofyr, MyConnection2);
                MyCommand2.Fill(dtt).ToString();
                MyCommand2.Dispose();
                MyConnection2.Close();
                xlWorkbook2.Close();
                xlApp2.Quit();
                //MyCommand = GetData(dayofyr, resultpath);
                // MyCommand.Fill(dtt);
                resultpath = Properties.Settings.Default.svmresult;
                Excel.Application xlApp3      = new Excel.Application();
                Excel.Workbook    xlWorkbook3 = xlApp3.Workbooks.Open(resultpath, ReadOnly: false);
                xlApp3.Visible = false;
                Excel._Worksheet xlWorksheet3 = xlWorkbook3.Sheets[1];
                Excel.Range      xlRange3     = xlWorksheet3.UsedRange;
                System.Data.OleDb.OleDbConnection  MyConnection3;
                System.Data.OleDb.OleDbDataAdapter MyCommand3;
                MyConnection3 = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + resultpath + "';Extended Properties=Excel 8.0;");
                MyCommand3    = new System.Data.OleDb.OleDbDataAdapter("select * from [Results$] where Day_Number=" + dayofyr, MyConnection3);
                MyCommand3.Fill(dtt).ToString();
                MyCommand3.Dispose();
                MyConnection3.Close();
                xlWorkbook3.Close();
                xlApp3.Quit();
                //MyCommand = GetData(dayofyr, resultpath);
                //MyCommand.Fill(dtt);
                break;

            default:    //0 or else
                dtt.Clear();
                //MessageBox.Show("Select Model ...");
                break;
            }

            //MyCommand.Fill(dtt).ToString();
            dataGridView1.DataSource = dtt.Tables[0];
            dataGridView1.DataBindings.ToString();

            /*string pathname = Path.GetDirectoryName(path);
             * filePath1.ReadOnly = true;
             * filePath1.Text = path;
             * Excel.Application xlApp = new Excel.Application();
             * Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@path, ReadOnly: false);
             * xlApp.Visible = false;
             * Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
             * Excel.Range xlRange = xlWorksheet.UsedRange;
             * opLstCst2.Items.Clear();
             * System.Data.OleDb.OleDbConnection MyConnection;
             * System.Data.DataSet DtSet;
             * System.Data.OleDb.OleDbDataAdapter MyCommand, MyCommand1;
             * MyConnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + path + "';Extended Properties=Excel 8.0;");
             * MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection);
             * DtSet = new System.Data.DataSet();
             * MyCommand.Fill(dtt).ToString();
             * dataGridViewDisplayPanel.DataSource = dtt.Tables[0];
             * dataGridViewDisplayPanel.DataBindings.ToString();
             * for (int m = 0; m < dataGridViewDisplayPanel.Columns.Count; m++)
             * {
             *  opLstCst2.Items.Add(dataGridViewDisplayPanel.Columns[m].Name);
             * }
             * xlWorkbook.Close();
             * xlApp.Quit();
             * lblTotal.Text = "Total: " + dataGridViewDisplayPanel.Rows.Count;*/
        }
Ejemplo n.º 31
0
        public void ExportFileFromDataGridViewEmployee(System.Windows.Forms.DataGridView dgv, string fileName)
        {
            //set properties for SaveFileDilog
            SaveFileDialog sfdSave = new SaveFileDialog();

            sfdSave.Filter   = "Excel file(*.xls)|*.xls";
            sfdSave.Title    = "Save to Excel file";
            sfdSave.FileName = fileName;

            //proccess save file excel
            if (sfdSave.ShowDialog() == DialogResult.OK)
            {
                //create file infomation
                FileInfo f = new FileInfo(sfdSave.FileName);
                //delete if file exists
                if (f.Exists == true)
                {
                    f.Delete();
                }
                //Create excel file
                ExcelCOM.Application exApp   = new ExcelCOM.Application();
                ExcelCOM.Workbook    exBook  = exApp.Workbooks.Add(ExcelCOM.XlWBATemplate.xlWBATWorksheet);
                ExcelCOM.Worksheet   exSheet = (ExcelCOM.Worksheet)exBook.Worksheets[1];
                //ExcelCOM.Range chartRange;
                exSheet.Name = fileName;

                //list field will be exported
                List <string> field = new List <string> {
                    "EmployeeCode", "EmployeeName", "UserName", "Gender", "BirthDate", "PhoneNumber", "Email", "Address", "EmployeeTypeName", "CountryName", "EmployeeTypeName2", "CountryName2", "EmployeeTypeName3", "CountryName3", "HourlyWages", "CreatedDate", "UpdatedDate", "Notes"
                };

                //import header
                int col = 1;
                for (int i = 0; i < field.Count; i++)
                {
                    exSheet.Cells[1, col] = dgv.Columns[field[i]].HeaderText;
                    col++;
                }

                //import data
                for (int i = 0; i < dgv.Rows.Count; i++)
                {
                    col = 1;
                    for (int j = 0; j < field.Count; j++)
                    {
                        exSheet.Cells[i + 2, col] = dgv.Rows[i].Cells[field[j]].Value.ToString();
                        col++;
                    }
                }

                //(ExcelCOM.XlLineStyle.xlContinuous, ExcelCOM.XlBorderWeight.xlMedium, ExcelCOM.XlColorIndex.xlColorIndexAutomatic, ExcelCOM.XlColorIndex.xlColorIndexAutomatic);
                //chartRange.BorderAround(ExcelCOM.XlLineStyle.xlContinuous, ExcelCOM.XlBorderWeight.xlMedium, ExcelCOM.XlColorIndex.xlColorIndexAutomatic, ExcelCOM.XlColorIndex.xlColorIndexAutomatic);
                exApp.Visible = false;
                //Save file excel into dictionary that you choose
                exBook.SaveAs(sfdSave.FileName, ExcelCOM.XlFileFormat.xlWorkbookNormal,
                              null, null, false, false,
                              ExcelCOM.XlSaveAsAccessMode.xlExclusive,
                              false, false, false, false, false);

                //close and release object
                exBook.Close(false, false, false);
                exApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);

                CustomMessageBox.MessageBox.ShowCustomMessageBox(Common.clsLanguages.GetResource("CRM385"),
                                                                 Common.clsLanguages.GetResource("CRM11"),
                                                                 Common.Config.CUSTOM_MESSAGEBOX_ICON.Information,
                                                                 Common.Config.CUSTOM_MESSAGEBOX_BUTTON.OK);
            }
        }
Ejemplo n.º 32
0
        public string GetCellValue(
            string FileName,
            string SheetName,
            string CellAddress)
        {
            string CellValue = "";

            if (System.IO.File.Exists(FileName))
            {
                bool Proceed = false;

                Excel.Application xlApp        = null;
                Excel.Workbooks   xlWorkBooks  = null;
                Excel.Workbook    xlWorkBook   = null;
                Excel.Worksheet   xlWorkSheet  = null;
                Excel.Sheets      xlWorkSheets = null;
                Excel.Range       xlCells      = null;

                xlApp = new Excel.Application();
                xlApp.DisplayAlerts = false;
                xlWorkBooks         = xlApp.Workbooks;
                xlWorkBook          = xlWorkBooks.Open(FileName);

                xlApp.Visible = false;

                xlWorkSheets = xlWorkBook.Sheets;

                for (int x = 1; x <= xlWorkSheets.Count; x++)
                {
                    xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x];

                    if (xlWorkSheet.Name == SheetName)
                    {
                        Proceed = true;
                        break;
                    }

                    System.Runtime.InteropServices
                    .Marshal.FinalReleaseComObject(xlWorkSheet);

                    xlWorkSheet = null;
                }

                if (Proceed)
                {
                    xlCells = xlWorkSheet.Range[CellAddress];

                    try
                    {
                        Console.WriteLine("Bold: {0}", xlCells.Font.Bold);       //  bool
                        Console.WriteLine("Italic: {0}", xlCells.Font.Italic);   // bool
                        ForeColor = System.Drawing.ColorTranslator.FromOle((int)xlCells.Font.Color);
                        CellValue = Convert.ToString(xlCells.Value);
                    }
                    catch (Exception)
                    {
                        // Reduntant
                        CellValue = "";
                    }
                }
                else
                {
                    MessageBox.Show(SheetName + " not found.");
                }

                xlWorkBook.Close();
                xlApp.UserControl = true;
                xlApp.Quit();

                ReleaseComObject(xlCells);
                ReleaseComObject(xlWorkSheets);
                ReleaseComObject(xlWorkSheet);
                ReleaseComObject(xlWorkBook);
                ReleaseComObject(xlWorkBooks);
                ReleaseComObject(xlApp);
            }
            else
            {
                MessageBox.Show("'" + FileName +
                                "' not located. Try one of the write examples first.");
            }

            return(CellValue);
        }
Ejemplo n.º 33
0
        private void Generate()
        {
            var WordApp = new Word.Application();

            WordApp.Visible = false;

            var WordDocument = WordApp.Documents.Open(@"" + pathDoc);

            Excel.Application ObjWorkExcel = new Excel.Application();
            Excel.Workbook    ObjWorkBook  = ObjWorkExcel.Workbooks.Open(@"" + pathXls);
            Excel.Worksheet   ObjWorkSheet = (Excel.Worksheet)ObjWorkBook.Sheets[1];

            try
            {
                int nInLastRow = ObjWorkSheet.Cells.Find("*", System.Reflection.Missing.Value,
                                                         System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                                         Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious,
                                                         false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
                int nInLastCol = ObjWorkSheet.Cells.Find("*", System.Reflection.Missing.Value,
                                                         System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                                         Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious,
                                                         false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Column;

                string[,] list = new string[nInLastRow, nInLastCol];

                for (int i = 0; i < nInLastRow; i++)
                {
                    for (int j = 0; j < nInLastCol; j++)
                    {
                        list[i, j] = ObjWorkSheet.Cells[i + 1, j + 1].Text.ToString();
                    }
                }

                for (int i = 0; i < nInLastRow; i++)
                {
                    for (int j = 0; j < nInLastCol; j++)
                    {
                        string tmp = list[i, j];
                        ReplaceWordStub("{" + j + "}", tmp, WordDocument);

                        if (j == nInLastCol - 1)
                        {
                            string fName = Regex.Replace(list[i, j], "[\"«»]", "");
                            WordDocument.SaveAs(@"" + pathSave + "\\" + fName + ".docx");
                            WordDocument.Close(false, Type.Missing, Type.Missing);
                            WordDocument = WordApp.Documents.Open(@"" + pathDoc);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), "Ошибка", MessageBoxButton.OK, MessageBoxImage.Error);
            }
            finally
            {
                WordDocument.Close(false, Type.Missing, Type.Missing);
                WordApp.Quit();
                ObjWorkBook.Close(false, Type.Missing, Type.Missing);
                ObjWorkExcel.Quit();
                GC.Collect();
            }
        }
Ejemplo n.º 34
0
        public static void WriteTabSoFar(Tournament tournament, DataContext context, int roundNumber)
        {
            SqlHelper helper = new SqlHelper(tournament.Database);

            List <Round> rounds = helper.GetRounds().Result.Where(r => r.RoundNumber <= roundNumber).ToList();

            List <Tuple <int, List <Debate> > > debates = new List <Tuple <int, List <Debate> > >();

            foreach (Round round in rounds)
            {
                debates.Add(Tuple.Create(round.RoundNumber, helper.GetDebates(round.RoundId).Result.ToList()));
            }

            List <SpeakerDraw> draws = new List <SpeakerDraw>();

            foreach (Tuple <int, List <Debate> > debate in debates)
            {
                foreach (Debate d in debate.Item2)
                {
                    draws.AddRange(helper.GetSpeakerDrawsByDebate(d.DebateId).Result);
                }
            }

            List <Speaker> speakers = new List <Speaker>();

            speakers.AddRange(context.Speakers);
            speakers.AsParallel().ForAll(s => s.Draws = draws.Where(d => d.SpeakerId.Equals(s.SpeakerId)).ToList());

            speakers = speakers.OrderByDescending(s => s.Draws.Sum(sd => sd.Result.Points())).ThenByDescending(s => s.Draws.Sum(sd => sd.SpeakerPoints)).ToList();

            Excel.Application excelApplication = new Excel.Application()
            {
                Visible = false
            };

            Excel.Workbook excelWorkbook = excelApplication.Workbooks.Add();

            Excel.Sheets excelSheets = excelWorkbook.Worksheets;

            Excel.Worksheet sheet = (Excel.Worksheet)excelSheets.get_Item("Sheet1");

            Excel.Range cell = sheet.get_Range("A2");
            cell.Value2 = "Speaker";
            cell        = sheet.get_Range("B2");
            cell.Value2 = "Total Points";
            cell        = sheet.get_Range("C2");
            cell.Value2 = "Total Speaker Points";


            int columns = 1;

            int columnLetter = 'D';

            for (int i = 1; i <= roundNumber; i++)
            {
                cell        = sheet.get_Range(string.Format("{0}1", (char)columnLetter));
                cell.Value2 = string.Concat("Round ", i);
                cell        = sheet.get_Range(string.Format("{0}2", (char)columnLetter++));
                cell.Value2 = "Result";
                cell        = sheet.get_Range(string.Format("{0}2", (char)columnLetter++));
                cell.Value2 = "Speaker Points";
                columns    += 3;
            }

            int letter = 'A';
            int row    = 3;

            foreach (Speaker speaker in speakers)
            {
                letter      = 'A';
                cell        = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row));
                cell.Value2 = speaker.Name;
                cell        = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row));
                cell.Value2 = speaker.Draws.Sum(x => x.Result.Points());
                cell        = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row));
                cell.Value2 = speaker.Draws.Sum(x => x.SpeakerPoints);

                foreach (Round round in rounds)
                {
                    List <Debate> theseDebates = debates[round.RoundNumber - 1].Item2;
                    SpeakerDraw   speakerDraw  = speaker.Draws.First(sd => theseDebates.Select(d => d.DebateId).Contains(sd.DebateId));
                    cell        = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row));
                    cell.Value2 = speakerDraw.Result.Points();
                    cell        = sheet.get_Range(string.Format("{0}{1}", (char)letter++, row));
                    cell.Value2 = speakerDraw.SpeakerPoints;
                }
                row++;
            }

            string directory = Path.Combine(Path.GetDirectoryName(tournament.Location), string.Format("Round {0}", roundNumber));

            if (!Directory.Exists(directory))
            {
                Directory.CreateDirectory(directory);
            }

            string filename = Path.Combine(directory, string.Format("Round {0} - tab.xlsx", roundNumber));

            string endCell = string.Format("{0}{1}", (char)(letter - 1), row - 1);

            cell = sheet.get_Range("A1", endCell);
            cell.Columns.AutoFit();

            excelApplication.DisplayAlerts = false;
            excelWorkbook.SaveAs(filename);
            excelWorkbook.Close();
            excelApplication.DisplayAlerts = true;
            excelApplication.Quit();
        }
Ejemplo n.º 35
0
        private void XlsToResx(string xlsFile)
        {
            if (!File.Exists(xlsFile))
            {
                return;
            }

            string path = new FileInfo(xlsFile).DirectoryName;

            Excel.Application app = new Excel.Application();
            Excel.Workbook    wb  = app.Workbooks.Open(xlsFile,
                                                       0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                                                       true, false, 0, true, false, false);

            Excel.Sheets sheets = wb.Worksheets;

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

            bool hasLanguage = true;
            int  col         = 5;

            while (hasLanguage)
            {
                object val = (sheet.Cells[2, col] as Excel.Range).Text;

                if (val is string)
                {
                    if (!String.IsNullOrEmpty((string)val))
                    {
                        string cult = (string)val;

                        string pathCulture = path + @"\" + cult;

                        if (!System.IO.Directory.Exists(pathCulture))
                        {
                            System.IO.Directory.CreateDirectory(pathCulture);
                        }


                        ResXResourceWriter rw = null;

                        int row = 3;

                        string fileSrc;
                        string fileDest;
                        bool   readrow = true;

                        while (readrow)
                        {
                            fileSrc  = (sheet.Cells[row, 1] as Excel.Range).Text.ToString();
                            fileDest = (sheet.Cells[row, 2] as Excel.Range).Text.ToString();

                            if (String.IsNullOrEmpty(fileDest))
                            {
                                break;
                            }

                            string f = pathCulture + @"\" + JustStem(fileDest) + "." + cult + ".resx";

                            rw = new ResXResourceWriter(f);

                            while (readrow)
                            {
                                string key  = (sheet.Cells[row, 3] as Excel.Range).Text.ToString();
                                object data = (sheet.Cells[row, col] as Excel.Range).Text.ToString();

                                if ((key is String) & !String.IsNullOrEmpty(key))
                                {
                                    if (data is string)
                                    {
                                        string text = data as string;

                                        text = text.Replace("\\r", "\r");
                                        text = text.Replace("\\n", "\n");

                                        rw.AddResource(new ResXDataNode(key, text));
                                    }

                                    row++;

                                    string file = (sheet.Cells[row, 2] as Excel.Range).Text.ToString();

                                    if (file != fileDest)
                                    {
                                        break;
                                    }
                                }
                                else
                                {
                                    readrow = false;
                                }
                            }

                            rw.Close();
                        }
                    }
                    else
                    {
                        hasLanguage = false;
                    }
                }
                else
                {
                    hasLanguage = false;
                }

                col++;
            }
        }
Ejemplo n.º 36
0
        private void DataSetToXls(ResxData rd, string fileName)
        {
            Excel.Application app = new Excel.Application();
            Excel.Workbook    wb  = app.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);

            Excel.Sheets    sheets = wb.Worksheets;
            Excel.Worksheet sheet  = (Excel.Worksheet)sheets.get_Item(1);
            sheet.Name = "Localize";

            sheet.Cells[1, 1] = "Resx source";
            sheet.Cells[1, 2] = "Resx Name";
            sheet.Cells[1, 3] = "Key";
            sheet.Cells[1, 4] = "Value";

            string[] cultures = GetCulturesFromDataSet(rd);

            int index = 5;

            foreach (string cult in cultures)
            {
                CultureInfo ci = new CultureInfo(cult);

                sheet.Cells[1, index] = ci.DisplayName;
                sheet.Cells[2, index] = ci.Name;
                index++;
            }

            DataView dw = rd.Resx.DefaultView;

            dw.Sort = "FileSource, Key";

            int row = 3;

            foreach (DataRowView drw in dw)
            {
                ResxData.ResxRow r = (ResxData.ResxRow)drw.Row;

                sheet.Cells[row, 1] = r.FileSource;
                sheet.Cells[row, 2] = r.FileDestination;
                sheet.Cells[row, 3] = r.Key;
                sheet.Cells[row, 4] = r.Value;

                ResxData.ResxLocalizedRow[] rows = r.GetResxLocalizedRows();

                foreach (ResxData.ResxLocalizedRow lr in rows)
                {
                    string culture = lr.Culture;

                    int col = Array.IndexOf(cultures, culture);

                    if (col >= 0)
                    {
                        sheet.Cells[row, col + 5] = lr.Value;
                    }
                }

                row++;
            }

            sheet.Cells.get_Range("A1", "Z1").EntireColumn.AutoFit();

            // Save the Workbook and quit Excel.
            wb.SaveAs(fileName, m_objOpt, m_objOpt,
                      m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
                      m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
            wb.Close(false, m_objOpt, m_objOpt);
            app.Quit();
        }
Ejemplo n.º 37
0
        private void btnCreateBM_Click(object sender, EventArgs e)
        {
            grvVT.FocusedRowHandle = -1;
            if (grvVT.RowCount == 0)
            {
                return;
            }
            //string materialCode = TextUtils.ToString(grvModuleM.GetFocusedRowCellValue(colCodeM));
            //string materialName = TextUtils.ToString(grvModuleM.GetFocusedRowCellValue(colNameM));
            //if (materialName == "")
            //{
            //    MessageBox.Show("Tên vật tư không được để trống!", TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
            //    return;
            //}

            string path             = "";
            FolderBrowserDialog fbd = new FolderBrowserDialog();

            if (fbd.ShowDialog() == DialogResult.OK)
            {
                path = fbd.SelectedPath;
            }
            else
            {
                return;
            }

            string filePath    = Application.StartupPath + "\\Templates\\YCVT.xls";
            string currentPath = path + "\\YCVT." + DateTime.Now.ToString("ddMMyyy") + ".xls";

            try
            {
                File.Copy(filePath, currentPath, true);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Có lỗi khi tạo file yêu cầu cấp vật tư!" + Environment.NewLine + ex.Message,
                                TextUtils.Caption, MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return;
            }

            using (WaitDialogForm fWait = new WaitDialogForm("Vui lòng chờ trong giây lát...", "Đang tạo Yêu cầu cấp vật tư..."))
            {
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                Excel.Application app       = default(Excel.Application);
                Excel.Workbook    workBoook = default(Excel.Workbook);
                Excel.Worksheet   workSheet = default(Excel.Worksheet);
                try
                {
                    app = new Excel.Application();
                    app.Workbooks.Open(currentPath);
                    workBoook = app.Workbooks[1];
                    workSheet = (Excel.Worksheet)workBoook.Worksheets[1];

                    workSheet.Cells[6, 3]  = txtCustomer.Text.Trim();
                    workSheet.Cells[6, 5]  = txtCustomerCode.Text.Trim();
                    workSheet.Cells[6, 9]  = txtPhuTrachHD.Text.Trim();
                    workSheet.Cells[7, 3]  = txtKhachHangCuoi.Text.Trim();
                    workSheet.Cells[7, 5]  = TextUtils.ToString(grvContract.GetFocusedRowCellValue(colContractCode));
                    workSheet.Cells[13, 7] = "Tân Phát, ngày " + DateTime.Now.Day + " tháng " + DateTime.Now.Month + " năm " + DateTime.Now.Year;

                    for (int i = grvVT.RowCount - 1; i >= 0; i--)
                    {
                        workSheet.Cells[12, 1] = i + 1;
                        workSheet.Cells[12, 2] = grvVT.GetRowCellDisplayText(i, colMaName);
                        workSheet.Cells[12, 3] = grvVT.GetRowCellDisplayText(i, colMaCode);
                        workSheet.Cells[12, 4] = grvVT.GetRowCellDisplayText(i, colMaHang);
                        workSheet.Cells[12, 5] = grvVT.GetRowCellDisplayText(i, colUnit);
                        workSheet.Cells[12, 6] = grvVT.GetRowCellDisplayText(i, colMaQty);
                        workSheet.Cells[12, 7] = grvVT.GetRowCellDisplayText(i, colMaModule);
                        workSheet.Cells[12, 8] = grvVT.GetRowCellDisplayText(i, colDate);
                        workSheet.Cells[12, 9] = grvVT.GetRowCellDisplayText(i, colNote);
                        //workSheet.Cells[12, 10] = grvVT.GetRowCellDisplayText(i, colMaPrice);
                        //workSheet.Cells[12, 11] = grvVT.GetRowCellDisplayText(i, colMaTotal);
                        //workSheet.Cells[12, 12] = grvVT.GetRowCellDisplayText(i, colMaDes);
                        ((Excel.Range)workSheet.Rows[12]).Insert();
                    }
                    ((Excel.Range)workSheet.Rows[11]).Delete();
                    ((Excel.Range)workSheet.Rows[11]).Delete();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    if (app != null)
                    {
                        app.ActiveWorkbook.Save();
                        app.Workbooks.Close();
                        app.Quit();
                    }
                }
                Process.Start(currentPath);
            }
        }
Ejemplo n.º 38
0
        public void OutputAsExcelFile(DataGridView dataGridView)
        {
            if (dataGridView.Rows.Count <= 0)
            {
                MessageBox.Show("无数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning); return;
            }
            string         filePath = "";
            SaveFileDialog s        = new SaveFileDialog();

            s.Title       = "保存Excel文件";
            s.Filter      = "Excel文件(*.xls)|*.xls";
            s.FilterIndex = 1;
            if (s.ShowDialog() == DialogResult.OK)
            {
                filePath = s.FileName;
            }
            else
            {
                return;
            }

            //第一步:将dataGridView转化为dataTable,这样可以过滤掉dataGridView中的隐藏列

            DataTable tmpDataTable = new DataTable("tmpDataTable");
            DataTable modelTable   = new DataTable("ModelTable");

            for (int column = 0; column < dataGridView.Columns.Count; column++)
            {
                if (dataGridView.Columns[column].Visible == true)
                {
                    DataColumn tempColumn = new DataColumn(dataGridView.Columns[column].HeaderText, typeof(string));
                    tmpDataTable.Columns.Add(tempColumn);
                    DataColumn modelColumn = new DataColumn(dataGridView.Columns[column].Name, typeof(string));
                    modelTable.Columns.Add(modelColumn);
                }
            }
            for (int row = 0; row < dataGridView.Rows.Count; row++)
            {
                if (dataGridView.Rows[row].Visible == false)
                {
                    continue;
                }
                DataRow tempRow = tmpDataTable.NewRow();
                for (int i = 0; i < tmpDataTable.Columns.Count; i++)
                {
                    tempRow[i] = dataGridView.Rows[row].Cells[modelTable.Columns[i].ColumnName].Value;
                }
                tmpDataTable.Rows.Add(tempRow);
            }
            if (tmpDataTable == null)
            {
                return;
            }

            //第二步:导出dataTable到Excel
            long rowNum    = tmpDataTable.Rows.Count;    //行数
            int  columnNum = tmpDataTable.Columns.Count; //列数

            Excel.Application m_xlApp = new Excel.Application();
            m_xlApp.DisplayAlerts = false;//不显示更改提示
            m_xlApp.Visible       = false;

            Excel.Workbooks workbooks = m_xlApp.Workbooks;
            Excel.Workbook  workbook  = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

            try
            {
                string[,] datas = new string[rowNum + 1, columnNum];
                for (int i = 0; i < columnNum; i++) //写入字段
                {
                    datas[0, i] = tmpDataTable.Columns[i].Caption;
                }
                //Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
                Excel.Range range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
                range.Interior.ColorIndex = 15;//15代表灰色
                range.Font.Bold           = true;
                range.Font.Size           = 10;

                int r = 0;
                for (r = 0; r < rowNum; r++)
                {
                    for (int i = 0; i < columnNum; i++)
                    {
                        object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
                        datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
                    }
                    System.Windows.Forms.Application.DoEvents();
                    //添加进度条
                }
                //Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
                Excel.Range fchR = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
                fchR.Value2 = datas;

                worksheet.Columns.EntireColumn.AutoFit(); //列宽自适应。
                                                          //worksheet.Name = "dd";

                //m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
                m_xlApp.Visible = false;

                // = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
                range = m_xlApp.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];

                //range.Interior.ColorIndex = 15;//15代表灰色
                range.Font.Size           = 9;
                range.RowHeight           = 14.25;
                range.Borders.LineStyle   = 1;
                range.HorizontalAlignment = 1;
                workbook.Saved            = true;
                workbook.SaveCopyAs(filePath);
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出异常:" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            finally
            {
                EndReport();
            }

            m_xlApp.Workbooks.Close();
            m_xlApp.Workbooks.Application.Quit();
            m_xlApp.Application.Quit();
            m_xlApp.Quit();
            MessageBox.Show("导出成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
Ejemplo n.º 39
0
        private void saveFileToExcel(string filename)
        {
            try
            {
                Excel.Application xlApp      = new Excel.Application();
                Excel.Workbook    xlWorkbook = xlApp.Workbooks.Add(true);

                Excel.Worksheet xlWorksheet = (Excel.Worksheet)xlApp.ActiveSheet;
                Excel.Range     xlRange     = xlWorksheet.UsedRange;

                for (int i = 0; i < DataGridView1.ColumnCount; i++)
                {
                    xlRange        = (Excel.Range)xlWorksheet.Cells[1, i + 1];
                    xlRange.Value2 = DataGridView1.Columns[i].HeaderText;

                    xlRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                }

                for (int j = 0; j < DataGridView1.RowCount; j++)
                {
                    for (int i = 0; i < DataGridView1.ColumnCount; i++)
                    {
                        xlRange = (Excel.Range)xlWorksheet.Cells[j + 2, i + 1];

                        if (DataGridView1[i, j].Value == null)
                        {
                            xlRange.Value2 = "0.0";
                        }
                        else
                        {
                            xlRange.Value2            = DataGridView1[i, j].Value.ToString();
                            xlRange.Font.Color        = System.Drawing.ColorTranslator.ToOle(DataGridView1[i, j].Style.ForeColor);
                            DataGridView1[i, j].Value = "";
                        }

                        xlRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                    }
                }

                xlApp.DisplayAlerts = false;
                xlWorkbook.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //if (printer)
                //xlWorkbook.PrintOut(1, 1, 1, false, true, false, true, filename);

                if (xlApp != null)
                {
                    xlWorkbook.Close(Type.Missing, filename, Type.Missing);
                    xlApp.Quit();
                    Marshal.ReleaseComObject(xlWorksheet);
                    Marshal.ReleaseComObject(xlWorkbook);
                    Marshal.ReleaseComObject(xlApp);
                    xlWorksheet = null;
                    xlWorkbook  = null;
                    xlApp       = null;
                }

                GC.Collect();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        private void btnExcelExport_Click(object sender, EventArgs e)
        {
            MsExl.Application app = null;
            MsExl.Workbook    wb  = null;
            MsExl.Worksheet   ws  = null;

            if (MessageBox.Show("This process will take few minutes to generate file. \n Click OK to Continue.", this.Text, MessageBoxButtons.OKCancel, MessageBoxIcon.Information) != DialogResult.OK)
            {
                return;
            }

            try
            {
                panel1.Enabled = false;
                panel2.Enabled = false;
                panel3.Enabled = false;
                this.Cursor    = Cursors.WaitCursor;

                app         = new MsExl.Application();
                app.Visible = false;

                wb = app.Workbooks.Add(MsExl.XlWBATemplate.xlWBATWorksheet);
                ws = (MsExl.Worksheet)wb.ActiveSheet;

                // Headers.
                for (int i = 0; i < grdBusRouteList.Columns.Count; i++)
                {
                    ws.Cells[1, i + 1] = grdBusRouteList.Columns[i].Name;
                }

                // Content.
                for (int i = 0; i < grdBusRouteList.Rows.Count; i++)
                {
                    for (int j = 0; j < grdBusRouteList.Columns.Count; j++)
                    {
                        if (grdBusRouteList.Rows[i].Cells[j].Value != null)
                        {
                            ws.Cells[i + 2, j + 1] = grdBusRouteList.Rows[i].Cells[j].Value.ToString();
                        }
                    }
                }

                // Autofit Cells,Column and Rows Width.
                ws.Columns.AutoFit();
                ws.Rows.WrapText = true;

                ws.Range[ws.Cells[1, 1], ws.Cells[grdBusRouteList.Rows.Count + 1, grdBusRouteList.Columns.Count]].Borders.LineStyle = true;
                ws.Range[ws.Cells[1, 1], ws.Cells[1, grdBusRouteList.Columns.Count]].Font.Bold = true;

                wb.SaveAs(GetExportFileName(".xls"), MsExl.XlFileFormat.xlWorkbookNormal,
                          Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                          Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                          Missing.Value, Missing.Value, Missing.Value,
                          Missing.Value, Missing.Value);

                wb.Close(Missing.Value, Missing.Value, Missing.Value);
                app.Quit();
                app = null;
                wb  = null;
                ws  = null;

                MessageBox.Show("Export Excel file successfully created at below mentioned path. \n " + GetExportFileName(".xls"), this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);

                this.Cursor    = Cursors.Default;
                panel1.Enabled = true;
                panel2.Enabled = true;
                panel3.Enabled = true;
            }
            catch (Exception ex)
            {
                this.Cursor    = Cursors.Default;
                panel1.Enabled = true;
                panel2.Enabled = true;
                panel3.Enabled = true;

                if (app != null)
                {
                    wb = null;
                    ws = null;
                    app.Quit();
                    app = null;
                }

                MessageBox.Show(ex.Message, this.Text + " : btnExcelExport_Click : " + ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Ejemplo n.º 41
0
        /// <summary>
        /// Метод создания и сохранения документов
        /// в форматах Microsoft Word (doc, PDF),
        /// Excel (exls)
        /// </summary>
        /// <param name="type">Тип создаваемого документа
        /// отчёт или статистика</param>
        /// <param name="format">Формат сохранения
        /// документ или таблица</param>
        /// <param name="name">Название документа</param>
        /// <param name="table">Входная таблица с данными</param>
        public void Document_Create(Document_Type type,
                                    Document_Format format, string name,
                                    DataTable table)
        {
            //Получение данных о конфигурации документа
            Configuration_class configuration_Class
                = new Configuration_class();

            configuration_Class.Document_Configuration_Get();
            //Проверка на пустоту названия
            switch (name != "" || name != null)
            {
            case true:
                //Выбор формата либо Word либо Excel
                switch (format)
                {
                case Document_Format.Word:
                    //Запуск процесса в дистпечере задач
                    word.Application application
                        = new word.Application();
                    //создание документа в процессе
                    word.Document document
                    //Присвоение документа процессу, Visible: true
                    //возможность редактирования документа
                        = application.Documents.Add(Visible: true);
                    try
                    {
                        //Объявление дипапазона для формирования текста
                        word.Range range = document.Range(0, 0);
                        //89Настройка отступов в документе
                        document.Sections.PageSetup.LeftMargin
                            = application.CentimetersToPoints(
                                  (float)Configuration_class.
                                  doc_Left_Merge);
                        document.Sections.PageSetup.TopMargin
                            = application.CentimetersToPoints(
                                  (float)Configuration_class.
                                  doc_Top_Merge);
                        document.Sections.PageSetup.RightMargin
                            = application.
                              CentimetersToPoints((float)
                                                  Configuration_class.doc_Right_Merg);
                        document.Sections.PageSetup.BottomMargin
                            = application.CentimetersToPoints(
                                  (float)Configuration_class.
                                  doc_Bottom_Merge);
                        //Присвоение текстового знеачения в дипазон
                        range.Text =
                            "Ресторан";
                        //Настройка выравнивания текста
                        range.ParagraphFormat.Alignment =
                            word.WdParagraphAlignment.
                            wdAlignParagraphCenter;
                        //Настройка интервала после абзаца
                        range.ParagraphFormat.SpaceAfter = 1;
                        //Настройка интервала перед абзаца
                        range.ParagraphFormat.SpaceBefore = 1;
                        //Настройка межстрочного интервала
                        range.ParagraphFormat.LineSpacingRule
                            = word.WdLineSpacing.wdLineSpaceSingle;
                        //Настройка названия шрифта
                        range.Font.Name = "Times New Roman";
                        //Настройка размера шрифта
                        range.Font.Size = 12;
                        //Добавление параграфов
                        document.Paragraphs.Add();        //В конце текста
                        document.Paragraphs.Add();        //Свободный
                        document.Paragraphs.Add();        //Для будущего текста
                        //Параграф для названия документа
                        word.Paragraph Document_Name
                            = document.Paragraphs.Add();
                        //Настройка параграфа через свойство диапазона
                        Document_Name.Format.Alignment
                            = word.WdParagraphAlignment.wdAlignParagraphCenter;
                        Document_Name.Range.Font.Name = "Times New Roman";
                        Document_Name.Range.Font.Size = 16;
                        //Проверка на тип документа, отчёт или статистика
                        switch (type)
                        {
                        case Document_Type.Report:
                            Document_Name.Range.Text = "ОТЧЁТ";

                            break;

                        case Document_Type.Statistic:
                            Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ";
                            break;
                        }
                        document.Paragraphs.Add();
                        document.Paragraphs.Add();
                        document.Paragraphs.Add();
                        word.Paragraph statparg = document.Paragraphs.Add();
                        //Создание области таблицы в документе
                        word.Table stat_table
                        //Добавление таблицы в область документа
                        //Указывается параграф в котором документ создан
                        //Количество строк и столбцов
                            = document.Tables.Add(statparg.Range,
                                                  table.Rows.Count, table.Columns.Count);
                        //Настройка границ таблицы внутренние
                        stat_table.Borders.InsideLineStyle
                            = word.WdLineStyle.wdLineStyleSingle;
                        //Настройка границ таблицы внешние
                        stat_table.Borders.OutsideLineStyle
                            = word.WdLineStyle.wdLineStyleSingle;
                        //Выравнивание текста внутри ячеек по ширине
                        stat_table.Rows.Alignment
                            = word.WdRowAlignment.wdAlignRowCenter;
                        //Выравнивание текста внутри ячеек по высоте
                        stat_table.Range.Cells.VerticalAlignment =
                            word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
                        stat_table.Range.Font.Size = 11;
                        stat_table.Range.Font.Name = "Times New Roman";
                        //Индексация столбцов и строк в Word начинается с 1,1
                        for (int row = 1; row <= table.Rows.Count; row++)
                        {
                            for (int col = 1; col <= table.Columns.Count; col++)
                            {
                                stat_table.Cell(row, col).Range.Text
                                    = table.Rows[row - 1][col - 1].ToString();
                            }
                        }
                        document.Paragraphs.Add();
                        document.Paragraphs.Add();
                        //Парадграф с фиксациейц даты создания документа
                        word.Paragraph Footparg = document.Paragraphs.Add();
                        Footparg.Range.Text =
                            string.Format("Дата создания \t\t\t{0}",
                                          DateTime.Now.ToString("dd.MM.yyyy"));
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                    }
                    finally
                    {
                        switch (format)
                        {
                        case Document_Format.Word:
                            //Сохранение документа с названием из метода,
                            //и в формате doc
                            document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
                                             word.WdSaveFormat.wdFormatDocument);
                            break;

                        case Document_Format.PDF:
                            //Сохранение документа в формате PDF
                            document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
                                             word.WdSaveFormat.wdFormatPDF);
                            break;
                        }
                        //Закрываем документ
                        document.Close();
                        //Выходим из процесса с его закрытием
                        application.Quit();
                    }
                    break;

                case Document_Format.Excel:
                    //Создание процесса Excel
                    excel.Application application_ex
                        = new excel.Application();
                    //Создание книги
                    excel.Workbook workbook
                        = application_ex.Workbooks.Add();
                    //Создание страницы
                    excel.Worksheet worksheet
                        = (excel.Worksheet)workbook.ActiveSheet;
                    try
                    {
                        switch (type)
                        {
                        case Document_Type.Report:
                            //Название страницы
                            worksheet.Name = "Отчёт";
                            for (int row = 0; row < table.Rows.Count; row++)
                            {
                                for (int col = 0; col < table.Columns.Count; col++)
                                {
                                    //ЗАнесение данных в ячейку
                                    worksheet.Cells[row + 1][col + 1]
                                        = table.Rows[row][col].ToString();
                                }
                            }
                            //Указание диапазона работы с ячеёками листа
                            excel.Range border
                            //Начало диапазона
                                = worksheet.Range[worksheet.Cells[1, 1],
                                                  //Динамический конец диапазона в зависимости от
                                                  //выдодимых данных
                                                  worksheet.Cells[table.Rows.Count + 1]
                                                  [table.Columns.Count + 1]];
                            //Стиль линий границ ячеек
                            border.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
                            //Выравнивание во высоте
                            border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
                            //Выравнивание по ширине
                            border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
                            //Внесение даты создания документа
                            worksheet.Cells[table.Rows.Count + 3][2]
                                = string.Format("Дата создания {0}",
                                                DateTime.Now.ToString());
                            //Объединение ячеек
                            worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
                                            worksheet.Cells[table.Rows.Count + 2,
                                                            table.Columns.Count + 2]].Merge();
                            break;

                        case Document_Type.Statistic:
                            worksheet.Name = "Статистический отчёт";
                            for (int row = 0; row < table.Rows.Count; row++)
                            {
                                for (int col = 0; col < table.Columns.Count; col++)
                                {
                                    worksheet.Cells[row + 1][col + 1]
                                        = table.Rows[row][col].ToString();
                                }
                            }
                            excel.Range border1
                                = worksheet.Range[worksheet.Cells[1, 1],
                                                  worksheet.Cells[table.Rows.Count + 1]
                                                  [table.Columns.Count + 1]];
                            border1.Borders.LineStyle
                                = excel.XlLineStyle.xlContinuous;
                            border1.VerticalAlignment
                                = excel.XlHAlign.xlHAlignCenter;
                            border1.HorizontalAlignment
                                = excel.XlHAlign.xlHAlignCenter;
                            worksheet.Cells[table.Rows.Count + 3][2]
                                = string.Format("Дата создания {0}",
                                                DateTime.Now.ToString());
                            worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
                                            worksheet.Cells[table.Rows.Count + 2,
                                                            table.Columns.Count + 2]].Merge();
                            //Класс области графиков
                            excel.ChartObjects chartObjects
                                = (excel.ChartObjects)worksheet.ChartObjects(
                                      Type.Missing);
                            //Область размещения графиков: отступы слева сверху,
                            //размер ширина и высота
                            excel.ChartObject chartObject
                                = chartObjects.Add(300, 50, 250, 250);
                            //Объявление области графика
                            excel.Chart chart = chartObject.Chart;
                            //Объявление колекции построений графиков
                            excel.SeriesCollection seriesCollection
                                = (excel.SeriesCollection)chart.SeriesCollection(
                                      Type.Missing);
                            //Объявление посторения графика
                            excel.Series series = seriesCollection.NewSeries();
                            //Тип графика
                            chart.ChartType = excel.XlChartType.xl3DColumn;
                            //Диапазон значений по оси X
                            series.XValues =
                                worksheet.get_Range("B2", "B" + table.Rows.Count + 1);
                            //Диапазон значений по оси Y
                            series.Values =
                                worksheet.get_Range("C2", "C" + table.Rows.Count + 1);
                            break;
                        }
                    }
                    catch
                    {
                    }
                    finally
                    {
                        //Сохранение книги
                        workbook.SaveAs(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), application_ex.DefaultSaveFormat);
                        //Закрытие книги
                        workbook.Close();
                        //Завершение процесса
                        application_ex.Quit();
                    }
                    break;
                }
                break;

            case false:
                System.Windows.Forms.MessageBox.Show
                    ("Введите название документп");
                break;
            }
        }
Ejemplo n.º 42
0
        private void runModel(object sender, EventArgs e)
        {
            TextBox[] array    = { data1, data2, data3, data4, data5, data6, data7, data8, data9 };
            TextBox[] fireHole = { hole50, hole5 };

            notificationPanel.Show();

            Excel.Application excelApp          = null;
            Excel.Workbooks   books             = null;
            Excel.Workbook    inputFile         = null;
            Excel.Workbook    templateFile      = null;
            Excel.Worksheet   inputSheets       = null;
            Excel.Worksheet   fireBallSheet     = null;
            Excel.Worksheet   templateSheet     = null;
            Excel.Worksheet   wellLinesTransect = null;
            Excel.Sheets      templateSheets    = null;
            Excel.Range       dataRange         = null;
            Excel.Range       holeRange         = null;
            Excel.Range       fireBallRange     = null;

            try {
                excelApp               = new Excel.Application();
                excelApp.Visible       = false;
                excelApp.DisplayAlerts = false;

                books = excelApp.Workbooks;

                inputFile     = books.Open(Path.Combine(Environment.CurrentDirectory, @"Workbooks\well_lines.xlsx"));
                inputSheets   = inputFile.Sheets["Well lines input sheet"];
                fireBallSheet = inputFile.Sheets["Fire Ball"];

                dataRange = inputSheets.Range["B1", "B9"];
                int i = 0;
                foreach (Excel.Range cell in dataRange)
                {
                    cell.Value = array[i].Text;
                    i++;
                    Marshal.FinalReleaseComObject(cell);
                }

                holeRange = inputSheets.Range["B17", "B18"];
                int iter = 0;
                foreach (Excel.Range cell in holeRange)
                {
                    cell.Value = fireHole[iter].Text;
                    iter++;
                    Marshal.FinalReleaseComObject(cell);
                }

                int index = 0;
                fireBallRange = fireBallSheet.Range["E4", "E104"];
                foreach (Excel.Range cell in fireBallRange)
                {
                    double step5 = double.Parse(step5factor.Text);
                    double value = double.Parse(InitPage.GasLineValues.plantGasLine[index]) * double.Parse(step5factor.Text);

                    cell.Value = value;
                    index++;
                    Marshal.FinalReleaseComObject(cell);
                }

                templateFile   = books.Open(Path.Combine(Environment.CurrentDirectory, @"Templates\Well Results.xlsx"));
                templateSheet  = templateFile.Sheets["Well_Lines_Data"];
                templateSheets = templateFile.Sheets;

                inputSheets.Range["B1", "B11"].Copy(templateSheet.Range["B1", "B11"]);
                inputSheets.Range["B17", "B18"].Copy(templateSheet.Range["B16", "B17"]);

                wellLinesTransect      = templateSheets.Add();
                wellLinesTransect.Name = "Well_Lines_Transect";

                inputSheets.ChartObjects("Chart 3").Chart.CopyPicture();
                wellLinesTransect.Paste();

                templateFile.SaveAs(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), data1.Text));
            }
            finally
            {
                // Clean up sheets
                GC.Collect();
                GC.WaitForPendingFinalizers();

                Marshal.FinalReleaseComObject(wellLinesTransect);
                Marshal.FinalReleaseComObject(templateSheet);
                Marshal.FinalReleaseComObject(fireBallSheet);
                Marshal.FinalReleaseComObject(inputSheets);
                Marshal.FinalReleaseComObject(books);

                Marshal.FinalReleaseComObject(templateSheets);
                Marshal.FinalReleaseComObject(dataRange);
                Marshal.FinalReleaseComObject(holeRange);
                Marshal.FinalReleaseComObject(fireBallRange);

                wellLinesTransect = null;
                templateSheet     = null;
                fireBallSheet     = null;
                inputSheets       = null;
                books             = null;

                templateSheets = null;
                dataRange      = null;
                holeRange      = null;
                fireBallRange  = null;

                inputFile.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
                templateFile.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);

                Marshal.FinalReleaseComObject(inputFile);
                Marshal.FinalReleaseComObject(templateFile);
                inputFile    = null;
                templateFile = null;

                excelApp.Application.Quit();
                Marshal.FinalReleaseComObject(excelApp);
                excelApp = null;

                notificationPanel.Controls.Find("lblGenerating", true)[0].Hide();
                notificationPanel.Controls.Find("lblSuccess", true)[0].Show();
                notificationPanel.Controls.Find("btnClose", true)[0].Show();
            };
        }
Ejemplo n.º 43
0
        public void ExportFileFromDataTable_Dung(DataTable dt, string fileName, string title, string[] prm)
        {
            //set properties for SaveFileDilog
            SaveFileDialog sfdSave = new SaveFileDialog();

            sfdSave.Filter   = "Excel file(*.xls)|*.xls";
            sfdSave.Title    = "Save to Excel file";
            sfdSave.FileName = fileName;

            //proccess save file excel
            if (sfdSave.ShowDialog() == DialogResult.OK)
            {
                //create file infomation
                FileInfo f = new FileInfo(sfdSave.FileName);
                //delete if file exists
                if (f.Exists == true)
                {
                    f.Delete();
                }
                //Create excel file
                ExcelCOM.Application exApp   = new ExcelCOM.Application();
                ExcelCOM.Workbook    exBook  = exApp.Workbooks.Add(ExcelCOM.XlWBATemplate.xlWBATWorksheet);
                ExcelCOM.Worksheet   exSheet = (ExcelCOM.Worksheet)exBook.Worksheets[1];
                //Microsoft.Office.Interop.Excel.Range excelCellrange;
                ExcelCOM.Range excelCellrange;
                exSheet.Name = fileName;

                //import Caption
                excelCellrange = exSheet.Range[exSheet.Cells[1, 1], exSheet.Cells[1, dt.Columns.Count]];
                exSheet.get_Range((object)exSheet.Cells[1, 1], (object)exSheet.Cells[1, dt.Columns.Count]).Merge(false);
                excelCellrange                     = exSheet.get_Range((object)exSheet.Cells[1, 1], (object)exSheet.Cells[1, dt.Columns.Count]);
                excelCellrange.FormulaR1C1         = title;
                excelCellrange.HorizontalAlignment = 3;
                excelCellrange.VerticalAlignment   = 3;

                excelCellrange = exSheet.Range[exSheet.Cells[1, 1], exSheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]];
                excelCellrange.NumberFormat = "@";
                //import header
                int col = 1;
                for (int i = 1; i <= dt.Columns.Count; i++)
                {
                    exSheet.Cells[2, col] = dt.Columns[i - 1].Caption;
                    col++;
                }

                //import data
                //int row = 1;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //row++;
                    col = 1;
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (prm != null)
                        {
                            //exSheet.Cells[i + 3, col].NumberFormat = "@";
                            exSheet.Cells[i + 3, col] = dt.Rows[i][j].ToString();
                        }
                        col++;
                    }
                }

                // now we resize the columns
                excelCellrange = exSheet.Range[exSheet.Cells[1, 1], exSheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]];
                excelCellrange.EntireColumn.AutoFit();

                Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
                border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                border.Weight    = 2d;

                //excelCellrange = exSheet.Range[exSheet.Cells[1, 1], exSheet.Cells[1, dt.Columns.Count]];
                //FormattingExcelCells(excelCellrange, "#f5f5f5", System.Drawing.Color.DarkBlue, true);

                //excelCellrange = exSheet.Range[exSheet.Cells[2, 1], exSheet.Cells[2, dt.Columns.Count]];
                //FormattingExcelCells(excelCellrange, "#0488a1", System.Drawing.Color.White, true);

                exApp.Visible = false;
                //
                //exBook.CheckCompatibility = false;
                //Save file excel into dictionary that you choose
                exBook.SaveAs(sfdSave.FileName, ExcelCOM.XlFileFormat.xlWorkbookNormal,
                              null, null, false, false,
                              ExcelCOM.XlSaveAsAccessMode.xlExclusive,
                              false, false, false, false, false);

                //close and release object
                exBook.Close(false, false, false);
                exApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);

                CustomMessageBox.MessageBox.ShowCustomMessageBox(Common.clsLanguages.GetResource("ExcelExport"),
                                                                 Common.clsLanguages.GetResource("Information"),
                                                                 Common.Config.CUSTOM_MESSAGEBOX_ICON.Information,
                                                                 Common.Config.CUSTOM_MESSAGEBOX_BUTTON.OK);
            }
        }
Ejemplo n.º 44
0
        private ResxData XlsToDataSet(string xlsFile)
        {
            Excel.Application app = new Excel.Application();
            Excel.Workbook    wb  = app.Workbooks.Open(xlsFile,
                                                       0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                                                       true, false, 0, true, false, false);

            Excel.Sheets sheets = wb.Worksheets;

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

            ResxData rd = new ResxData();

            int row = 3;

            bool continueLoop = true;

            while (continueLoop)
            {
                string fileSrc = (sheet.Cells[row, 1] as Excel.Range).Text.ToString();

                if (String.IsNullOrEmpty(fileSrc))
                {
                    break;
                }

                ResxData.ResxRow r = rd.Resx.NewResxRow();

                r.FileSource      = (sheet.Cells[row, 1] as Excel.Range).Text.ToString();
                r.FileDestination = (sheet.Cells[row, 2] as Excel.Range).Text.ToString();
                r.Key             = (sheet.Cells[row, 3] as Excel.Range).Text.ToString();
                r.Value           = (sheet.Cells[row, 4] as Excel.Range).Text.ToString();

                rd.Resx.AddResxRow(r);

                bool hasCulture = true;
                int  col        = 5;
                while (hasCulture)
                {
                    string cult = (sheet.Cells[2, col] as Excel.Range).Text.ToString();

                    if (String.IsNullOrEmpty(cult))
                    {
                        break;
                    }

                    ResxData.ResxLocalizedRow lr = rd.ResxLocalized.NewResxLocalizedRow();

                    lr.Culture  = cult;
                    lr.Key      = (sheet.Cells[row, 3] as Excel.Range).Text.ToString();
                    lr.Value    = (sheet.Cells[row, col] as Excel.Range).Text.ToString();
                    lr.ParentId = r.Id;

                    lr.SetParentRow(r);

                    rd.ResxLocalized.AddResxLocalizedRow(lr);

                    col++;
                }

                row++;
            }

            rd.AcceptChanges();

            wb.Close(false, m_objOpt, m_objOpt);
            app.Quit();

            return(rd);
        }
Ejemplo n.º 45
0
        /// <summary>
        /// Export file excel from datagridview
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="fileName"></param>
        public void ExportFileFromDataGridView(System.Windows.Forms.DataGridView dgv, string fileName)
        {
            //set properties for SaveFileDilog
            SaveFileDialog sfdSave = new SaveFileDialog();

            sfdSave.Filter   = "Excel file(*.xls)|*.xls";
            sfdSave.Title    = "Save to Excel file";
            sfdSave.FileName = fileName;

            //proccess save file excel
            if (sfdSave.ShowDialog() == DialogResult.OK)
            {
                //create file infomation
                FileInfo f = new FileInfo(sfdSave.FileName);
                //delete if file exists
                if (f.Exists == true)
                {
                    f.Delete();
                }
                //Create excel file
                ExcelCOM.Application exApp   = new ExcelCOM.Application();
                ExcelCOM.Workbook    exBook  = exApp.Workbooks.Add(ExcelCOM.XlWBATemplate.xlWBATWorksheet);
                ExcelCOM.Worksheet   exSheet = (ExcelCOM.Worksheet)exBook.Worksheets[1];
                //ExcelCOM.Range chartRange;
                exSheet.Name = fileName;
                //get data from datagridview import into excel file
                //exSheet.Cells.FormatConditions

                //exSheet.get_Range("b2", "e3").Merge(false);

                //chartRange = exSheet.get_Range("b2", "e9");
                //chartRange.BorderAround(ExcelCOM.XlLineStyle.xlContinuous, ExcelCOM.XlBorderWeight.xlMedium, ExcelCOM.XlColorIndex.xlColorIndexAutomatic, ExcelCOM.XlColorIndex.xlColorIndexAutomatic);

                //xlWorkBook.SaveAs("csharp.net-informations.xls", ExcelCOM.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, ExcelCOM.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

                //import header
                int col = 1;
                for (int i = 1; i <= dgv.ColumnCount; i++)
                {
                    exSheet.Cells[1, col] = dgv.Columns[i - 1].HeaderText;
                    col++;
                    //chartRange = exSheet.Cells[1, col];
                    //Font.Bold = true;
                }

                //import data
                for (int i = 0; i < dgv.Rows.Count; i++)
                {
                    col = 1;
                    for (int j = 1; j < dgv.ColumnCount; j++)
                    {
                        exSheet.Cells[i + 2, col + 1] = dgv.Rows[i].Cells[j].Value.ToString();

                        col++;

                        //chartRange.Cells.Formula
                    }
                }

                //(ExcelCOM.XlLineStyle.xlContinuous, ExcelCOM.XlBorderWeight.xlMedium, ExcelCOM.XlColorIndex.xlColorIndexAutomatic, ExcelCOM.XlColorIndex.xlColorIndexAutomatic);
                //chartRange.BorderAround(ExcelCOM.XlLineStyle.xlContinuous, ExcelCOM.XlBorderWeight.xlMedium, ExcelCOM.XlColorIndex.xlColorIndexAutomatic, ExcelCOM.XlColorIndex.xlColorIndexAutomatic);
                exApp.Visible = false;
                //Save file excel into dictionary that you choose
                exBook.SaveAs(sfdSave.FileName, ExcelCOM.XlFileFormat.xlWorkbookNormal,
                              null, null, false, false,
                              ExcelCOM.XlSaveAsAccessMode.xlExclusive,
                              false, false, false, false, false);

                //close and release object
                exBook.Close(false, false, false);
                exApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(exBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(exApp);

                CustomMessageBox.MessageBox.ShowCustomMessageBox(Common.clsLanguages.GetResource("CRM385"),
                                                                 Common.clsLanguages.GetResource("CRM11"),
                                                                 Common.Config.CUSTOM_MESSAGEBOX_ICON.Information,
                                                                 Common.Config.CUSTOM_MESSAGEBOX_BUTTON.OK);
            }
        }
Ejemplo n.º 46
0
        private void button6_Click(object sender, EventArgs e)
        {
            int l    = 3;
            int cont = co.Matricula().Rows.Count;
            int pos  = 0;

            int[] num;
            num = new int[100000];

            progressBar1.Visible = true;
            progressBar1.Maximum = cont;


            for (int i = 0; i < cont; i++)
            {
                va.Matricula = i;
                int aux = co.ExportarBrancos(va).Rows.Count;

                if (aux == 0 && i != 0)
                {
                    num[pos] = i;
                    pos      = pos + 1;
                }
                progressBar1.Value++;
            }

            progressBar1.Value = 0;

            salvarArquivo.FileName   = "RMs em Branco";
            salvarArquivo.DefaultExt = "*.xls";
            salvarArquivo.Filter     = "Todos os Aquivos do Excel (*.xls)|*.xls| Todos os arquivos (*.*)|*.*";

            try
            {
                xlApp      = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Add(misValue);

                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 4]].Merge();
                xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, 4]].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                xlWorkSheet.Cells[1, 1]                     = "Lista de RMs em branco";
                xlWorkSheet.Cells[1, 1].ColumnWidth         = 10;
                xlWorkSheet.Cells[1, 1].Font.Size           = 16;
                xlWorkSheet.Cells[2, 1]                     = "RM";
                xlWorkSheet.Cells[2, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                //progressBar1.Visible = true;
                progressBar1.Maximum = pos;
                for (int i = 0; i < pos; i++)
                {
                    xlWorkSheet.Cells[l, 1] = num[i];
                    xlWorkSheet.Cells[l, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                    l = l + 1;
                    progressBar1.Value++;
                }
                progressBar1.Value   = 0;
                progressBar1.Visible = false;

                new System.Threading.Thread(delegate()
                {
                    Export();
                }).Start();
            }
            catch (Exception ex)
            {
                string      msg = "Erro : " + ex.Message;
                frmMensagem mg  = new frmMensagem(msg);
                mg.ShowDialog();
            }
        }
Ejemplo n.º 47
0
 void Excel_WorkbookEvent(Xl.Workbook Wb)
 {
     InvalidateRibbonUi();
 }
Ejemplo n.º 48
0
        protected void Excel_Click(object sender, ImageClickEventArgs e)
        {
            List <Data> data = GetData();

            System.Reflection.Missing missingValue = System.Reflection.Missing.Value;

            //создаем и инициализируем объекты Excel
            Excel.Application App = new Microsoft.Office.Interop.Excel.Application();
            //добавляем в файл Excel книгу. Параметр в данной функции - используемый для создания книги шаблон.
            //если нас устраивает вид по умолчанию, то можно спокойно передавать пустой параметр.
            Excel.Workbooks xlsWBs = App.Workbooks;
            Excel.Workbook  xlsWB  = xlsWBs.Add(missingValue);
            //и использует из нее
            Excel.Sheets    xlsSheets = xlsWB.Worksheets;
            Excel.Worksheet xlsSheet  = (Excel.Worksheet)xlsSheets.get_Item(1);

            string[] cols = { "№ события", "Источник",    "Диспетчер",  "дата/время", "Вид услуги", "Зона", "Событие", "Описание", "Принял", "дата/время",
                              "Исполнил",  "Комментарий", "дата/время", "Тайминг" };
            for (int i = 0; i < cols.Length; i++)
            {
                xlsSheet.Cells[1, i + 1] = cols[i];
            }
            for (int i = 0; i < data.Count; i++)
            {
                xlsSheet.Cells[i + 2, 1]  = data[i].Id;
                xlsSheet.Cells[i + 2, 2]  = data[i].Sourse;
                xlsSheet.Cells[i + 2, 3]  = data[i].IO;
                xlsSheet.Cells[i + 2, 4]  = data[i].Date1;
                xlsSheet.Cells[i + 2, 5]  = data[i].RegistrId;
                xlsSheet.Cells[i + 2, 6]  = data[i].LiftId;
                xlsSheet.Cells[i + 2, 7]  = data[i].TypeId;
                xlsSheet.Cells[i + 2, 8]  = data[i].EventId;
                xlsSheet.Cells[i + 2, 9]  = data[i].ToApp;
                xlsSheet.Cells[i + 2, 10] = data[i].DateToApp;
                xlsSheet.Cells[i + 2, 11] = data[i].Who;
                xlsSheet.Cells[i + 2, 12] = data[i].Comment;
                xlsSheet.Cells[i + 2, 13] = data[i].Date2;
                xlsSheet.Cells[i + 2, 14] = data[i].Timing;
            }

            string name = DateTime.Now.ToString("ddMMyyyy-hhmm") + User.Identity.Name + ".xls";

            xlsWB.SaveAs(Request.PhysicalApplicationPath + KOS.App_Code.ClearTemp._folder + "\\" + name,
                         Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8,
                         missingValue,
                         missingValue,
                         missingValue,
                         missingValue,
                         Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                         missingValue,
                         missingValue,
                         missingValue,
                         missingValue,
                         missingValue);
            //закрываем книгу
            xlsWB.Close(false, missingValue, missingValue);
            xlsWB     = null;
            xlsWBs    = null;
            xlsSheet  = null;
            xlsSheets = null;
            //закрываем приложение
            App.Quit();
            //уменьшаем счетчики ссылок на COM объекты, что, по идее должно их освободить.
            // System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheet);
            // System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsSheets);
            // System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWB);
            //  System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWBs);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(App);
            //  Download.NavigateUrl = "~/" + KOS.App_Code.ClearTemp._folder + "\\" + name;
            //  Download.Text = "Скачать документ";
            //  Download.Visible = true;
        }
Ejemplo n.º 49
0
        private void keyisdown(object sender, KeyEventArgs e)
        {
            #region GameKeys
            if (e.KeyCode == Keys.Left)
            {
                goleft = true;
            }
            if (e.KeyCode == Keys.Right)
            {
                goright = true;
            }
            if (e.KeyCode == Keys.Up)
            {
                goup = true;
            }
            if (e.KeyCode == Keys.Down)
            {
                godown = true;
            }
            if (e.KeyCode == Keys.Enter)
            {
                tbMsg.Enabled = true; tbMsg.Focus();
            }
            #endregion
            #region DebugKeys
            if (e.KeyCode == Keys.P) //DEBUG
            {
                String x = Microsoft.VisualBasic.Interaction.InputBox("What's the desired port?", "DEBUG PORT", debugPort.ToString());
                try
                {
                    debugPort   = Int32.Parse(x);
                    client_port = x;
                    Clients.Remove(debugPort); // Removes ourselves from the list
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

            if (e.KeyCode == Keys.N)
            {
                String x = Microsoft.VisualBasic.Interaction.InputBox("What's your client name?", "NAME", "client" + debugPort.ToString());
                try
                {
                    PlayersID.Add(x, 1);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

            if (e.KeyCode == Keys.S)
            {
                InitChannel(client_port);
                initClientServer();


                try
                {
                    if (TEST_OFFLINE)
                    {
                        throw new Exception();
                    }
                    string myIp = new WebClient().DownloadString(@"http://icanhazip.com").Trim();
                    reqObj.Register(PlayersID.FirstOrDefault(x => x.Value == 1).Key, "tcp://" + myIp + ":" + debugPort + "/ClientService");
                }
                catch (Exception excpt) //Pokemon Exception Handling
                {
                    excpt.GetType();    //ESTA LINHA NAO FAZ NADA
                    reqObj.Register(PlayersID.FirstOrDefault(x => x.Value == 1).Key, "tcp://LOCALHOST:" + debugPort + "/ClientService");
                }
                reqObj.JoinGame(PlayersID.FirstOrDefault(x => x.Value == 1).Key);
            }
            if (e.KeyCode == Keys.C)
            {
                String x = Microsoft.VisualBasic.Interaction.InputBox("What's the server address?", "NAME", server_host);
                try
                {
                    server_host = x;
                    initClient();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            if (e.KeyCode == Keys.C)
            {
                //String x = Microsoft.VisualBasic.Interaction.InputBox("What's the server address?", "NAME", server_host
                MessageBox.Show(pmc.delay.ToString());
            }
            if (e.KeyCode == Keys.D)
            {
                String text = "";
                foreach (Tuple <string, CliChat> c in cc)
                {
                    text += c.Item1.ToString() + "\n";
                }
                foreach (KeyValuePair <String, int> c in chatClientsVectorClock)
                {
                    text += "Chat:" + c.ToString() + "\n";
                }
                text += "-----CLOCK-------\n";
                foreach (Tuple <string, Dictionary <string, int> > c in messagesVectorClock)
                {
                    foreach (KeyValuePair <string, int> d in c.Item2)
                    {
                        text += "VecClock[" + d.Key + "]:" + d.Value.ToString();
                    }
                    text += "\n";
                }

                MessageBox.Show(text);
            }
            if (e.KeyCode == Keys.E)
            {
                String text = "";
                foreach (Tuple <string, CliChat> c in cc)
                {
                    text += c.Item1.ToString() + "\n";
                }
                foreach (Control c in GetControlsByPartialName("Ghost"))
                {
                    text += c.ToString() + "\n";
                }
                MessageBox.Show(text);


                Excel.Application xlApp       = new Excel.Application();
                Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\..\\..\\moves.xlsx"));
                Excel._Worksheet  xlWorksheet = xlWorkbook.Sheets[1];
                Excel.Range       xlRange     = xlWorksheet.UsedRange;

                //excel is not zero based!!
                ArrayList a = new ArrayList();
                for (int i = 1; i <= 56; i++)
                {
                    a.Clear();

                    for (int j = 1; j <= 2; j++)
                    {
                        //new line
                        if (j == 1)
                        {
                            Console.Write("\r\n");
                        }

                        //write the value to the console
                        if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
                        {
                            a.Add(xlRange.Cells[i, j].Value2);
                        }
                    }
                    traceMoves.Add((int)Math.Floor((double)a[0]), (string)a[1]);
                }

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

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

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

                foreach (KeyValuePair <int, string> vp in traceMoves)
                {
                    Console.Write(vp.ToString());
                }
            }
            #endregion DEBUG
        }
Ejemplo n.º 50
0
        public static void CreateOverview(Tournament tournament, DataContext context, Guid roundId)
        {
            SqlHelper helper = new SqlHelper(tournament.Database);

            Round round = helper.GetRound(roundId).Result;

            List <Debate> debates = helper.GetDebates(roundId).Result;

            Excel.Application excelApplication = new Excel.Application()
            {
                Visible = false
            };

            Excel.Workbook excelWorkbook = excelApplication.Workbooks.Add();

            Excel.Sheets excelSheets = excelWorkbook.Worksheets;

            Excel.Worksheet sheet = (Excel.Worksheet)excelSheets.get_Item("Sheet1");

            debates = debates.OrderBy(d => context.Venues.First(v => d.VenueId.Equals(v.VenueId)).Name).ToList();

            int column = 1;
            int letter = 'A';

            Excel.Range range = sheet.get_Range("A1");

            range.Value2 = "Venue";

            foreach (Position p in Enum.GetValues(typeof(Position)))
            {
                if (p.Equals(Position.Invalid))
                {
                    continue;
                }

                range        = sheet.get_Range(string.Format("{0}{1}", (char)++letter, column));
                range.Value2 = p.ToString();
            }

            range        = sheet.get_Range(string.Format("{0}{1}", (char)++letter, column));
            range.Value2 = "Adjudicators";

            column++;

            foreach (Debate debate in debates)
            {
                letter = 'A';

                Venue venue = context.Venues.First(v => v.VenueId.Equals(debate.VenueId));

                range.Value2 = string.Format("{0}{1}", venue.SpecialNeedsVenue ? "[SN]" : string.Empty, venue.Name);

                range = sheet.get_Range(string.Format("{0}{1}", (char)++letter, column));

                List <SpeakerDraw> speakerDraws = helper.GetSpeakerDrawsByDebate(debate.DebateId).Result;

                foreach (SpeakerDraw speakerDraw in speakerDraws.OrderBy(sd => sd.Position))
                {
                    range        = sheet.get_Range(string.Format("{0}{1}", (char)++letter, column));
                    range.Value2 = context.Speakers.First(s => speakerDraw.SpeakerId.Equals(s.SpeakerId)).Name;
                }

                List <JudgeDraw> judgeDraws = helper.GetJudgeDrawsByDebate(debate.DebateId).Result.OrderBy(j => j.Number).ToList();

                List <Guid> judgeIds = judgeDraws.Select(j => j.JudgeId).ToList();

                List <Judge> judges = context.Judges.Where(j => judgeIds.Contains(j.JudgeId)).ToList();

                List <string> names = judges.Select(j => j.Name).ToList();

                range        = sheet.get_Range(string.Format("{0}{1}", (char)++letter, column));
                range.Value2 = string.Join(", ", names);

                column++;
            }

            string directory = Path.Combine(Path.GetDirectoryName(tournament.Location), string.Format("Round {0}", round.RoundNumber));

            if (!Directory.Exists(directory))
            {
                Directory.CreateDirectory(directory);
            }

            string filename = Path.Combine(directory, string.Format("Round {0} - overview.xlsx", round.RoundNumber));

            string endCell = string.Format("{0}{1}", (char)(letter), column - 1);

            range = sheet.get_Range("A1", endCell);
            range.Columns.AutoFit();

            excelApplication.DisplayAlerts = false;
            excelWorkbook.SaveAs(filename);
            excelWorkbook.Close();
            excelApplication.DisplayAlerts = true;
            excelApplication.Quit();
        }
Ejemplo n.º 51
0
        /// <summary>Función para Insertar el Indice.
        /// <para>Inserta el Indice en el archivo de Excel. Referencia: <see cref="InsertIndice(Excel.Worksheet, int, Excel.Range, bool, int)"/> se agrega la referencia ExcelAddIn.Generales para invocarla.</para>
        /// <seealso cref="InsertIndice(Excel.Worksheet, int, Excel.Range, bool, int)"/>
        /// </summary>
        public static void InsertIndice(Excel.Worksheet xlSht, int CantReg, Excel.Range currentCell, bool ConFormula, int NroPrincipal)
        {
            Worksheet sheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);

            Excel.Workbook wb         = Globals.ThisAddIn.Application.ActiveWorkbook;
            string         _NameFile  = wb.Name;
            string         _AnexoFile = sheet.Name;
            FileInfo       _Excel     = new FileInfo(Globals.ThisAddIn.Application.ActiveWorkbook.FullName);

            Excel.Range currentFind     = null;
            Excel.Range currentFindExpl = null;

            Excel.Range RangeLocked   = null;
            int         NroRow        = currentCell.Row;
            int         NroColumn     = currentCell.Column;
            string      IndicePrevio  = "";
            long        IndiceInicial = 0;

            int  iTotalColumns = 0;
            int  k             = 1;
            int  i             = 1;
            long indiceNvo     = 0;
            int  CantExpl      = 0;

            currentCell     = (Excel.Range)xlSht.Cells[NroRow, 1];
            IndicePrevio    = currentCell.get_Value(Type.Missing).ToString();
            currentFindExpl = (Excel.Range)xlSht.Cells[NroRow + 1, 1];

            if (currentFindExpl.get_Value(Type.Missing) != null)
            {
                if (currentFindExpl.get_Value(Type.Missing).ToString().ToUpper().Trim() == "EXPLICACION")
                {
                    NroRow++;
                }
            }

            IndiceInicial = Convert.ToInt64(IndicePrevio) + 100;
            int        rowexpl = 0;
            List <int> FilasExplicacion = new List <int>();
            int        CantRango = 0; long IndiceInicialx = IndiceInicial;

            foreach (Excel.Name cname in Globals.ThisAddIn.Application.Names)
            {
                if (cname.Name == "IA_0" + Convert.ToString(IndiceInicialx))
                {
                    CantRango++;
                    IndiceInicialx  = IndiceInicialx + 100;
                    rowexpl         = cname.RefersToRange.Cells.Row + 1;
                    currentFindExpl = (Excel.Range)xlSht.Cells[rowexpl, 1];

                    if (currentFindExpl.get_Value(Type.Missing) != null)
                    {
                        if (currentFindExpl.get_Value(Type.Missing).ToString().ToUpper().Trim() == "EXPLICACION")
                        {
                            CantExpl++;
                            if (!FilasExplicacion.Contains(rowexpl + CantReg))//los indices que tienen explicacion la fila actual + los registros que ingresó nvos
                            {
                                FilasExplicacion.Add(rowexpl + CantReg);
                            }
                        }
                    }
                }
            }

            currentFind = currentCell.Find(IndiceInicial, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
                                           Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
                                           Type.Missing, Type.Missing);

            int NroRowx = 0;

            CantRango = CantRango + CantExpl;
            int NroPrincipalAux = DameRangoPrincipal(NroPrincipal, xlSht);

            while (i <= CantReg)
            {
                indiceNvo = Convert.ToInt64(IndicePrevio) + 100;
                Excel.Range rangej = xlSht.get_Range(string.Format("{0}:{0}", NroRow + i, Type.Missing));
                rangej.Select();
                rangej.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

                var rangeall   = xlSht.get_Range(string.Format("{0}:{0}", NroPrincipalAux - 1, Type.Missing));
                var rangeaCopy = xlSht.get_Range(string.Format("{0}:{0}", NroRow + i, Type.Missing));
                iTotalColumns = xlSht.UsedRange.Columns.Count;
                rangeall.Copy();
                rangeaCopy.PasteSpecial(Excel.XlPasteType.xlPasteFormulas, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
                rangeaCopy.Locked = false;

                k = 1;

                while (k <= iTotalColumns)
                {
                    if (!(rangeaCopy.Cells[k].HasFormula))
                    {
                        rangeaCopy.Cells[k].Value = "";
                    }

                    k = k + 1;
                }

                xlSht.Cells[NroRow + i, 1] = "0" + Convert.ToString(indiceNvo);
                sheet.Controls.Remove("IA_0" + indiceNvo);
                AddNamedRange(NroRow + i, 1, "IA_0" + Convert.ToString(indiceNvo));
                currentCell  = (Excel.Range)xlSht.Cells[NroRow + i, 1];
                IndicePrevio = currentCell.get_Value(Type.Missing).ToString();
                //currentCell = xlSht.Range[xlSht.Cells[NroRow + i, 1], xlSht.Cells[NroRow + i, 3]];
                //currentCell.Font.Color = System.Drawing.Color.FromArgb(0, 0, 255);

                ((Excel.Range)xlSht.Cells[NroRow + 1, 2]).NumberFormat = "General"; // le doy formato text al concepto
                ((Excel.Range)xlSht.Cells[NroRow + 1, 2]).WrapText     = true;
                RangeLocked        = (Excel.Range)xlSht.Cells[NroRow + i, 1];
                RangeLocked.Locked = true; // con esto bloqueo solo la primera columna

                i++;
            }

            if (currentFind != null)
            {
                NroRowx      = NroRow + CantReg;
                currentCell  = (Excel.Range)xlSht.Cells[NroRowx, 1];
                IndicePrevio = currentCell.get_Value(Type.Missing).ToString();

                int j = 1;

                while (j <= CantRango)
                {
                    if (!FilasExplicacion.Contains(NroRowx + j))
                    {
                        indiceNvo = Convert.ToInt64(IndicePrevio) + 100;
                        xlSht.Cells[NroRowx + j, 1] = "0" + Convert.ToString(indiceNvo);
                        sheet.Controls.Remove("IA_0" + indiceNvo);
                        AddNamedRange(NroRowx + j, 1, "IA_0" + Convert.ToString(indiceNvo));
                        currentCell  = (Excel.Range)xlSht.Cells[NroRowx + j, 1];
                        IndicePrevio = currentCell.get_Value(Type.Missing).ToString();
                    }

                    j++;
                }
            }

            string           NombreHoja = xlSht.Name.ToUpper().Replace(" ", "");
            List <oSubtotal> ColumnasST = DameColumnasST((NombreHoja));

            Excel.Range Sum_Range = null;
            int         NroFinal  = NroRow + CantReg + CantRango;

            int    _Rango = 0;
            string _Renglon;
            string _Columna;
            string _rCelda = "";

            foreach (oSubtotal ST in ColumnasST)
            {
                Sum_Range         = xlSht.get_Range(ST.Columna + (NroPrincipalAux).ToString(), ST.Columna + (NroPrincipalAux).ToString());
                Sum_Range.Formula = "=sum(" + ST.Columna + (NroPrincipalAux + 1).ToString() + ":" + ST.Columna + (NroFinal).ToString() + ")";
                _Rango            = NroFinal - NroPrincipalAux; // nroprincipalaux siempre tiene el numero de la fila padre al restar te da la cantidad de rows insertadas
                _Renglon          = (Sum_Range.Row).ToString();
                _Columna          = Generales.ColumnAdress(Sum_Range.Column);
                _rCelda           = _Columna + "" + _Renglon;
                InsertarReferencia(_NameFile, _AnexoFile, _rCelda, _Rango, _Columna, _Renglon, CantReg);
            }

            Sum_Range = xlSht.get_Range("B" + (NroPrincipal).ToString(), "B" + (NroPrincipal).ToString());
            Sum_Range.Select();
        }
Ejemplo n.º 52
0
        /// <summary>
        /// This is the method that actually does the work.
        /// </summary>
        /// <param name="DA">The DA object can be used to retrieve data from input parameters and
        /// to store data in output parameters.</param>
        protected override void SolveInstance(IGH_DataAccess DA)
        {
            string defaultDir = "C:\\\\ant\\\\init\\\\ExcelDataset\\\\";

            process2.StartInfo.FileName         = "python.exe";
            process2.StartInfo.Arguments        = "doAllWork.py";
            process2.StartInfo.WorkingDirectory = @workingDir;
            process2.EnableRaisingEvents        = true;
            process2.StartInfo.WindowStyle      = System.Diagnostics.ProcessWindowStyle.Hidden;


            string workingDir2 = workingDir.Replace("\\", "\\\\");
            bool   openfile    = false;

            if (!DA.GetData(0, ref openfile))
            {
                return;
            }
            if (!openfile)
            {
                return;
            }

            openFileDialog1.InitialDirectory = "c:\\";
            openFileDialog1.Filter           = "Excel File (*.xlsx)|*.xlsx";
            openFileDialog1.FilterIndex      = 2;
            openFileDialog1.RestoreDirectory = true;

            if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                string data     = "[";
                string target   = "[";
                string features = "[";
                int    rCnt;
                int    cCnt;
                int    rw = 0;
                int    cl = 0;

                string thisfile = openFileDialog1.FileName;
                xlWorkBook  = xlApp.Workbooks.Open(@thisfile, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                range = xlWorkSheet.UsedRange;
                rw    = range.Rows.Count;
                cl    = range.Columns.Count;

                string strText = "N. of Features : " + (cl - 1) + "\t N. of Samples: " + (rw - 1) + "\t \n    ------------------------------ \n--- FEATURES  : \n    ";
                //MessageBox.Show(rw.ToString() + " rows , " + cl.ToString() + " Cols");
                string newstr = "[";

                for (cCnt = 1; cCnt <= cl - 1; cCnt++)
                {
                    if (cCnt > 1)
                    {
                        newstr   += ", ";
                        features += ", ";
                    }
                    newstr += "\"";
                    newstr += range.Cells[1, cCnt].Value2.ToString();
                    newstr += "\"";

                    features += "\"";
                    features += range.Cells[1, cCnt].Value2.ToString();
                    features += "\"";
                }
                features += "]";
                newstr   += "]\n    ------------------------------ \n--- DATA  : \n    ";
                for (rCnt = 2; rCnt <= rw; rCnt++)
                {
                    if (rCnt > 2)
                    {
                        data += ",";
                    }
                    data   += "[";
                    newstr += "\n        " + (rCnt - 1).ToString() + "- ";
                    newstr += "[";
                    for (cCnt = 1; cCnt <= cl - 1; cCnt++)
                    {
                        if (cCnt > 1)
                        {
                            data   += ",";
                            newstr += ", ";
                        }
                        newstr += range.Cells[rCnt, cCnt].Value2.ToString();
                        data   += range.Cells[rCnt, cCnt].Value2.ToString();
                    }
                    newstr += "]";
                    data   += "]";
                }
                data += "]";

                newstr += "]\n------------------------------ \n--- TARGETS  : \n        ";
                for (rCnt = 2; rCnt <= rw; rCnt++)
                {
                    if (rCnt > 2)
                    {
                        target += ",";
                    }
                    newstr += "[";
                    newstr += range.Cells[rCnt, cl].Value2.ToString();
                    target += range.Cells[rCnt, cl].Value2.ToString();
                    newstr += "] ";
                }
                target += "]";
                PythonFile(@data, @target, @features, workingDir);
                xlWorkBook.Close(true, null, null);
                xlApp.Quit();

                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp);
                process2.Start();

                DA.SetData(0, strText + newstr);
                DA.SetData(1, @workingDir2 + "data.txt");
                DA.SetData(2, @workingDir2 + "targets.txt");
            }
        }
Ejemplo n.º 53
0
        public static void ActualizarReferencia(string _Archivo, string _Anexo, string _Celda, int _Cantidad, string _Column, string _Row, int CantEliminar, string Accion)
        {
            string _Path = ExcelAddIn.Access.Configuration.Path;

            if (File.Exists(_Path + "\\references\\" + _Archivo + ".json"))
            {
                string       _jCadena = "";
                StreamReader _fJason  = new StreamReader(_Path + "\\references\\" + _Archivo + ".json");

                while (_fJason.Peek() >= 0)
                {
                    var      _json    = _fJason.ReadLine();
                    oIndices _Indices = JsonConvert.DeserializeObject <oIndices>(_json);

                    if (_Indices.Anexo + _Indices.Column + _Indices.Row == _Anexo + _Column + _Row)
                    {
                        if (Accion == "E")
                        {//Eliminar
                            _Indices.Cantidad -= CantEliminar;
                        }
                        else if (Accion == "A")
                        {//Agregar
                            _Indices.Cantidad += 1;
                        }
                        _json = JsonConvert.SerializeObject(_Indices);
                    }
                    if (_Indices.Column != _Column)
                    {
                        if (_Indices.Row == _Row)
                        {
                            if (Accion == "E")
                            {//Eliminar
                                _Indices.Cantidad -= CantEliminar;
                            }
                            else if (Accion == "A")
                            {//Agregar
                                _Indices.Cantidad += 1;
                            }
                            _json = JsonConvert.SerializeObject(_Indices);
                        }
                    }
                    if (_Indices.Anexo + _Indices.Row != _Anexo + _Row)
                    {
                        if (Convert.ToInt32(_Row) < Convert.ToInt32(_Indices.Row))
                        {
                            if (Accion == "E")
                            {//Eliminar
                                _Indices.Row = (Convert.ToInt32(_Indices.Row) - CantEliminar).ToString();
                            }
                            else if (Accion == "A")
                            {//Agregar
                                _Indices.Row = (Convert.ToInt32(_Indices.Row) + 1).ToString();
                            }
                            _json = JsonConvert.SerializeObject(_Indices);
                        }
                    }

                    _jCadena += _json;
                    _jCadena += string.Format(Environment.NewLine);
                }

                _fJason.Close();
                File.WriteAllText(_Path + "\\references\\" + _Archivo + ".json", _jCadena);
                Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
                wb.Save();
            }
        }
Ejemplo n.º 54
0
        public static void InsertarReferencia(string _Archivo, string _Anexo, string _Celda, int _Cantidad, string _Column, string _Row, int _Posicion)
        {
            string _Path   = ExcelAddIn.Access.Configuration.Path;
            var    Indices = new oIndices
            {
                Archivo  = _Archivo,
                Anexo    = _Anexo,
                Celda    = _Celda,
                Cantidad = _Cantidad,
                Column   = _Column,
                Row      = _Row
            };

            string jReference = JsonConvert.SerializeObject(Indices);

            if (Directory.Exists(_Path + "\\references"))
            {
                if (!File.Exists(_Path + "\\references\\" + _Archivo + ".json"))
                {
                    File.WriteAllText(_Path + "\\references\\" + _Archivo + ".json", jReference);
                }
                else
                {
                    string       _jCadena = "";
                    StreamReader _fJason  = new StreamReader(_Path + "\\references\\" + _Archivo + ".json");

                    while (_fJason.Peek() >= 0)
                    {
                        var      _json    = _fJason.ReadLine();
                        oIndices _Indices = JsonConvert.DeserializeObject <oIndices>(_json);

                        //if(Convert.ToInt32(_Row) < Convert.ToInt32(_Indices.Row))
                        //{
                        //    Match m = Regex.Match(_Indices.Celda, "(\\d+)");
                        //    string _num = "0";
                        //    if (m.Success){_num = m.Value;}

                        //    _Indices.Row = (Convert.ToInt32(_Indices.Row) + _Posicion).ToString();
                        //    _json = JsonConvert.SerializeObject(_Indices);
                        //}
                        if (_Indices.Column + _Indices.Row != _Column + _Row)
                        {
                            _jCadena += _json;
                            _jCadena += string.Format(Environment.NewLine);
                        }
                    }

                    _fJason.Close();
                    //_jCadena += string.Format(Environment.NewLine);
                    _jCadena += jReference;
                    File.WriteAllText(_Path + "\\references\\" + _Archivo + ".json", _jCadena);
                }
            }
            else
            {
                Directory.CreateDirectory(_Path + "\\references");
                if (!File.Exists(_Path + "\\references\\" + _Archivo + ".json"))
                {
                    File.WriteAllText(_Path + "\\references\\" + _Archivo + ".json", jReference);
                }
                else
                {
                    string       _jCadena = "";
                    StreamReader _fJason  = new StreamReader(_Path + "\\references\\" + _Archivo + ".json");

                    while (_fJason.Peek() >= 0)
                    {
                        _jCadena = _jCadena + _fJason.ReadLine();
                    }

                    _fJason.Close();
                    _jCadena = _jCadena + jReference;
                    File.WriteAllText(_Path + "\\references\\" + _Archivo + ".json", _jCadena);
                }
            }

            Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
            wb.Save();
        }
Ejemplo n.º 55
0
        /// <summary>
        /// 从table导出数据到Excel
        /// </summary>
        /// <param name="dt">要导出数据的table</param>
        /// <param name="name">表格名称</param>
        /// <param name="title">标题</param>
        /// <param name="staDate">统计起始日期</param>
        /// <param name="endDate">统计终止日期</param>
        /// <param name="staColumns">需要统计数据的列</param>
        /// <param name="pointTxt">指定为文本的列</param>
        /// <param name="countCoumn">合计行数列</param>
        /// <param name="formula">自定义公式("F,实载率,C{0}/D{1}|G,总班次,C{0}+E{1}")</param>
        /// <param name="remark">表格底部 要添加的备注</param>
        public static void Export(DataTable dt, string bookName, string title, string staDate, string endDate, string staColumns, string pointTxt, int countCoumn, string formula, string remark)
        {
            try
            {
                if (dt.Rows.Count < 1)
                {
                    return;
                }
                Excel.Application books = new Excel.Application();

                Excel.Workbook mybook = (Excel.Workbook)books.Workbooks.Add(1);
                ((Excel.Worksheet)mybook.Worksheets[1]).Name = bookName;
                Excel.Worksheet mysheet = (Excel.Worksheet)mybook.Worksheets[1];

                //第一行 标题
                mysheet.Cells[1, 1] = title;
                if (staDate.Length > 0 || endDate.Length > 0)
                {
                    //第二行 统计日期
                    mysheet.Cells[2, 1] = "统计日期:";//+staDate+"  -  "+endDate;
                    mysheet.Cells[2, 2] = staDate;
                    mysheet.Cells[2, 3] = endDate;

                    //mysheet.Cells[2, 10] = "检验员:";
                }

                //填标题
                int columns = 0;                                   //列数
                for (int j = 0, jj = 0; j < dt.Columns.Count; j++) //j 与jj不一定相等 只有 所有列的Visible属性全部都为true时才相等
                {
                    mysheet.Cells[3, jj + 1] = dt.Columns[j].ColumnName.ToString();
                    jj++;
                    columns = jj;
                }
                if (pointTxt.Length > 0)
                {
                    string[] points = pointTxt.Split(',');
                    foreach (string str in points)
                    {
                        int  i  = int.Parse(str);
                        int  ch = 'A' + i - 1;
                        char c  = (Char)ch;
                        mysheet.get_Range(c.ToString() + 4.ToString(), c.ToString() + (4 + dt.Rows.Count).ToString()).NumberFormatLocal = "@";
                    }
                }

                int rows = dt.Rows.Count;//行数
                //填  数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0, jj = 0; j < dt.Columns.Count; j++)
                    {
                        string tmp = dt.Rows[i][j].ToString();
                        mysheet.Cells[i + 4, jj + 1] = tmp;
                        jj++;
                    }
                }
                //添加 合计行

                if (rows > 0)
                {
                    mysheet.Cells[rows + 4, 1] = "合计:";
                    if (countCoumn == 1)
                    {
                        mysheet.Cells[rows + 4, 1] = "合计:" + rows.ToString();
                    }
                    if (countCoumn > 1)
                    {
                        mysheet.Cells[rows + 4, countCoumn] = rows.ToString();
                    }
                    string[] cloumns = staColumns.Split(',');
                    try
                    {
                        foreach (string str in cloumns)
                        {
                            int  i  = int.Parse(str);
                            int  ch = 'A' + i - 1;
                            char c  = (Char)ch;
                            mysheet.Cells[rows + 4, i] = "=SUM(" + c.ToString() + "4:" + c.ToString() + (rows + 3).ToString() + ")";
                        }
                    }
                    catch { }
                }

                //添加备注
                if (remark != "")
                {
                    mysheet.Cells[rows + 5, 1] = remark;
                    mysheet.get_Range(mysheet.Cells[rows + 5, 1], mysheet.Cells[rows + 5, columns]).Select();
                    mysheet.get_Range(mysheet.Cells[rows + 5, 1], mysheet.Cells[rows + 5, columns]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                }

                //自定义公式 格式"F,实载率,C{0}/D{1}|G,总班次,C{0}+E{1}"
                if (formula.Length > 0)
                {
                    string[] formulas = formula.Split('|');
                    try
                    {
                        foreach (string str in formulas)
                        {
                            string[] param = str.Split(',');
                            int      i     = char.Parse(param[0]);
                            if (param[1].Length > 0)
                            {
                                mysheet.Cells[3, i - 64] = param[1];
                            }
                            mysheet.Cells[rows + 4, i - 64] = "=" + string.Format(param[2], rows + 4, rows + 4);
                        }
                    }
                    catch { }
                }

                //设置格式
                try
                {
                    //设置 所有文字大小为9
                    mysheet.get_Range(mysheet.Cells[1, 1], mysheet.Cells[rows + 5, columns]).Font.Size = 12;
                }
                catch { }
                try
                {
                    //设置 表格为最适应宽度
                    mysheet.get_Range(mysheet.Cells[2, 1], mysheet.Cells[rows + 4, columns]).Select();
                    mysheet.get_Range(mysheet.Cells[2, 1], mysheet.Cells[rows + 4, columns]).Columns.AutoFit();
                }
                catch { }
                try
                {
                    //设置标题跨列居中
                    mysheet.get_Range(mysheet.Cells[1, 1], mysheet.Cells[1, columns]).Select();
                    mysheet.get_Range(mysheet.Cells[1, 1], mysheet.Cells[1, columns]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
                }
                catch { }
                try
                {
                    //加边框
                    //内部为虚线
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders.LineStyle = Excel.XlLineStyle.xlDash;        //设置虚线
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders.Weight    = Excel.XlBorderWeight.xlHairline; //设为极细
                    //设置左边线
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight    = Excel.XlBorderWeight.xlMedium;
                    //设置上边线
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight    = Excel.XlBorderWeight.xlMedium;
                    //设置右边线
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight    = Excel.XlBorderWeight.xlMedium;
                    //设置下边线
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight    = Excel.XlBorderWeight.xlMedium;
                }
                catch { }
                try
                {
                    //设置 纸张为A4 纵向
                    mysheet.PageSetup.PaperSize   = Excel.XlPaperSize.xlPaperA4;
                    mysheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait;
                }
                catch { }

                books.Visible = true;

                //mybook.SaveCopyAs("行包受理员营收汇总.xls");//.Save();
                mybook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                books = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出Excel表出错,可能您的机器未安装Excel!系统报错:" + ex.Message, "出错提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
                if (DialogResult.Yes == MessageBox.Show("您是否想保存Excel?", "保存提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
                {
                    //ExcelSaver.SaveExcel(dt, bookName, title, staDate, endDate, staColumns, pointTxt, countCoumn, remark);
                }
            }
            finally
            {
                GC.Collect();
            }
        }
Ejemplo n.º 56
0
        private void btnExcelReport_Click(object sender, EventArgs e)
        {
            if (!reportIsReady)
            {
                Control.Exclamation("Запрос не выполнен. Чтобы сгенерировать отчет, необходимо выполнить запрос.", "Ошибка при создании отчета");
                return;
            }
            Excel.Application excel = new Excel.Application();
            if (excel == null)
            {
                Control.Exclamation("На вашем компьютере отсутствует программа Excel.", "Ошибка при открытии Excel");
                return;
            }
            excel.SheetsInNewWorkbook = 1;
            excel.Workbooks.Add(Type.Missing);
            Excel.Workbook  excelWorkBook  = excel.Workbooks[1];
            Excel.Worksheet excelWorkSheet = excelWorkBook.Worksheets[1];
            for (int i = 0; i < 5; i++)
            {
                excel.Columns[i + 1].ColumnWidth = 20;
            }
            Excel.Range excelCells = excelWorkSheet.get_Range("A1", "E1");
            excelCells.Merge();
            excelCells.HorizontalAlignment = Excel.Constants.xlCenter;
            excel.Rows[1].Cells[1].Value   = "Запрос";
            excel.Rows[2].Cells[1].Value   = "Сущность";
            excel.Rows[2].Cells[2].Value   = "Атрибут";
            excel.Rows[2].Cells[3].Value   = "Условие";
            excel.Rows[2].Cells[4].Value   = "Значение";
            excel.Rows[2].Cells[5].Value   = "Связка";

            // Записываем запросы
            for (int i = 0; i < dgvQueries.Rows.Count; i++)
            {
                for (int j = 0; j < dgvQueries.Columns.Count; j++)
                {
                    excel.Rows[i + 3].Cells[j + 1].Value = dgvQueries.Rows[i].Cells[j].Value.ToString();
                }
            }

            if (cbEntities.Text == "Пользователи")
            {
                excelCells = excelWorkSheet.get_Range((char)65 + (5 + dgvQueries.Rows.Count).ToString(), (char)(65 + 1) + (5 + dgvQueries.Rows.Count).ToString());
            }
            else
            {
                excelCells = excelWorkSheet.get_Range((char)65 + (5 + dgvQueries.Rows.Count).ToString(), (char)(65 + 4) + (5 + dgvQueries.Rows.Count).ToString());
            }
            excelCells.HorizontalAlignment = Excel.Constants.xlCenter;
            excelCells.Merge();

            int shift = dgvQueries.Rows.Count;

            excel.Rows[5 + shift].Cells[1].Value = "Результаты поиска";
            if (cbEntities.Text == "Пользователи")
            {
                excel.Rows[6 + shift].Cells[1].Value = "ID";
                excel.Rows[6 + shift].Cells[2].Value = "Имя пользователя";
            }
            else
            {
                excel.Rows[6 + shift].Cells[1].Value = "ID";
                excel.Rows[6 + shift].Cells[2].Value = "Название";
                excel.Rows[6 + shift].Cells[3].Value = "Оисание";
                excel.Rows[6 + shift].Cells[4].Value = "Дата создания";
                excel.Rows[6 + shift].Cells[5].Value = "Средняя оценка";
            }

            // Записываем результаты запроса
            for (int i = 0; i < dgvResult.Rows.Count; i++)
            {
                for (int j = 0; j < dgvResult.Columns.Count; j++)
                {
                    object result = dgvResult.Rows[i].Cells[j].Value;
                    if (result is DateTime)
                    {
                        excel.Rows[i + 7 + shift].Cells[j + 1].Value = DateTime.Parse(dgvResult.Rows[i].Cells[j].Value.ToString()).Date;
                    }
                    else
                    {
                        excel.Rows[i + 7 + shift].Cells[j + 1].Value = dgvResult.Rows[i].Cells[j].Value == null ? "" : dgvResult.Rows[i].Cells[j].Value.ToString();
                    }
                }
            }

            excel.Visible = true;
        }
Ejemplo n.º 57
0
        /// <summary>
        /// 新控件gridview导出数据到Excel
        /// </summary>
        /// <param name="grid">要导出数据的datagridview</param>
        /// <param name="name">表格名称</param>
        /// <param name="title">标题</param>
        /// <param name="staDate">统计起始日期</param>
        /// <param name="endDate">统计终止日期</param>
        /// <param name="staColumns">需要统计数据的列</param>
        /// <param name="pointTxt">指定为文本的列</param>
        /// <param name="countCoumn">合计行数列</param>
        /// <param name="formula">自定义公式("F,实载率,C{0}/D{1}|G,总班次,C{0}+E{1}")</param>
        /// <param name="remark">表格底部 要添加的备注</param>
        public static void Export(DevExpress.XtraGrid.GridControl grid, string name, string title, string staDate, string endDate, string staColumns, string pointTxt, int countCoumn, string formula, string remark)
        {
            try
            {
                DevExpress.XtraGrid.Views.Grid.GridView dgv = (DevExpress.XtraGrid.Views.Grid.GridView)grid.MainView;
                if (dgv.RowCount < 1)
                {
                    return;
                }
                Excel.Application books = new Microsoft.Office.Interop.Excel.Application();

                Excel.Workbook mybook = (Excel.Workbook)books.Workbooks.Add(1);
                ((Excel.Worksheet)mybook.Worksheets[1]).Name = name;
                Excel.Worksheet mysheet = (Excel.Worksheet)mybook.Worksheets[1];
                Excel.Range     range;

                //第一行 标题
                mysheet.Cells[1, 1] = title;
                if (staDate.Length > 0 || endDate.Length > 0)
                {
                    //第二行 统计日期
                    mysheet.Cells[2, 1] = "统计日期:";//+staDate+"  -  "+endDate;
                    mysheet.Cells[2, 2] = staDate;
                    mysheet.Cells[2, 3] = endDate;

                    //mysheet.Cells[2, 10] = "检验员:";
                }

                //填标题
                int columns = 0;                                    //列数
                for (int j = 0, jj = 0; j < dgv.Columns.Count; j++) //j 与jj不一定相等 只有 所有列的Visible属性全部都为true时才相等
                {
                    if (dgv.Columns[j].Visible == true)
                    {
                        mysheet.Cells[3, jj + 1] = dgv.Columns[j].Caption.ToString();
                        jj++;
                        columns = jj;
                    }
                }
                if (pointTxt.Length > 0)
                {
                    string[] points = pointTxt.Split(',');
                    foreach (string str in points)
                    {
                        int  i  = int.Parse(str);
                        int  ch = 'A' + i - 1;
                        char c  = (Char)ch;
                        mysheet.get_Range(c.ToString() + 4.ToString(), c.ToString() + (4 + dgv.RowCount).ToString()).NumberFormatLocal = "@";
                    }
                }

                int rows = dgv.RowCount;//行数
                //填  数据
                for (int i = 0; i < dgv.RowCount; i++)
                {
                    for (int j = 0, jj = 0; j < dgv.Columns.Count; j++)
                    {
                        if (dgv.Columns[j].Visible == true)
                        {
                            string  columnName = dgv.Columns[j].FieldName;
                            DataRow row        = dgv.GetDataRow(i);
                            if (row[columnName] != null)
                            {
                                switch (row[columnName].GetType().ToString())
                                {
                                case "System.String":    //字符串类型

                                    break;

                                case "System.DateTime":    //日期类型
                                    System.DateTime dateV;

                                    break;

                                case "System.Boolean":    //布尔型
                                    bool boolV = false;

                                    break;

                                case "System.Int16":    //整型
                                case "System.Int32":
                                case "System.Int64":
                                case "System.Byte":
                                    int intV = 0;

                                    break;

                                case "System.Decimal":    //浮点型
                                case "System.Double":
                                    range = (Excel.Range)mysheet.Cells[i + 4, jj + 1];
                                    range.NumberFormatLocal = "0";
                                    break;

                                case "System.DBNull":    //空值处理

                                    break;

                                default:
                                    break;
                                }
                                string tmp = row[columnName].ToString();
                                tmp = tmp.ToLower() == "true" ? "√" : tmp;
                                tmp = tmp.ToLower() == "false" ? "" : tmp;
                                mysheet.Cells[i + 4, jj + 1] = tmp;
                            }
                            else
                            {
                                mysheet.Cells[i + 4, jj + 1] = "";
                            }
                            jj++;
                        }
                    }
                }
                //添加 合计行

                if (rows > 0)
                {
                    mysheet.Cells[rows + 4, 1] = "合计:";
                    if (countCoumn == 1)
                    {
                        mysheet.Cells[rows + 4, 1] = "合计:" + rows.ToString();
                    }
                    if (countCoumn > 1)
                    {
                        mysheet.Cells[rows + 4, countCoumn] = rows.ToString();
                    }
                    string[] cloumns = staColumns.Split(',');
                    try
                    {
                        foreach (string str in cloumns)
                        {
                            int  i  = int.Parse(str);
                            int  ch = 'A' + i - 1;
                            char c  = (Char)ch;
                            mysheet.Cells[rows + 4, i] = "=SUM(" + c.ToString() + "4:" + c.ToString() + (rows + 3).ToString() + ")";
                        }
                    }
                    catch { }
                }

                //添加备注
                if (remark != "")
                {
                    mysheet.Cells[rows + 5, 1] = remark;
                    mysheet.get_Range(mysheet.Cells[rows + 5, 1], mysheet.Cells[rows + 5, columns]).Select();
                    mysheet.get_Range(mysheet.Cells[rows + 5, 1], mysheet.Cells[rows + 5, columns]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                }

                //自定义公式 格式"F,实载率,C{0}/D{1}|G,总班次,C{0}+E{1}"
                if (formula.Length > 0)
                {
                    string[] formulas = formula.Split('|');
                    try
                    {
                        foreach (string str in formulas)
                        {
                            string[] param = str.Split(',');
                            int      i     = char.Parse(param[0]);
                            mysheet.Cells[3, i - 64]        = param[1];
                            mysheet.Cells[rows + 4, i - 64] = "=" + string.Format(param[2], rows + 4, rows + 4);
                        }
                    }
                    catch { }
                }

                //设置格式

                //设置 所有文字大小为9
                try
                {
                    mysheet.get_Range(mysheet.Cells[1, 1], mysheet.Cells[rows + 5, columns]).Font.Size = 9;
                }
                catch { }
                try
                {
                    //设置 表格为最适应宽度
                    mysheet.get_Range(mysheet.Cells[2, 1], mysheet.Cells[rows + 4, columns]).Select();
                    mysheet.get_Range(mysheet.Cells[2, 1], mysheet.Cells[rows + 4, columns]).Columns.AutoFit();
                }
                catch { }
                try
                {
                    //设置标题跨列居中
                    mysheet.get_Range(mysheet.Cells[1, 1], mysheet.Cells[1, columns]).Select();
                    mysheet.get_Range(mysheet.Cells[1, 1], mysheet.Cells[1, columns]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenterAcrossSelection;
                }
                catch { }
                try
                {
                    //加边框
                    //内部为虚线
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders.LineStyle = Excel.XlLineStyle.xlDash;        //设置虚线
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders.Weight    = Excel.XlBorderWeight.xlHairline; //设为极细
                    //设置左边线
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight    = Excel.XlBorderWeight.xlMedium;
                    //设置上边线
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight    = Excel.XlBorderWeight.xlMedium;
                    //设置右边线
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight    = Excel.XlBorderWeight.xlMedium;
                    //设置下边线
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
                    mysheet.get_Range(mysheet.Cells[3, 1], mysheet.Cells[rows + 4, columns]).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight    = Excel.XlBorderWeight.xlMedium;
                }
                catch { }
                try
                {
                    //设置 纸张为A4 纵向
                    mysheet.PageSetup.PaperSize   = Excel.XlPaperSize.xlPaperA4;
                    mysheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait;
                }
                catch { }
                books.Visible = true;

                //mybook.SaveCopyAs("行包受理员营收汇总.xls");//.Save();
                mybook = null;
                System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                books = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出Excel表出错,可能您的机器未安装Excel!系统报错:" + ex.Message, "出错提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
                if (DialogResult.Yes == MessageBox.Show("您是否想保存Excel?", "保存提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
                {
                    ExcelSaver.SaveExcel(grid, name, title, staDate, endDate, staColumns, pointTxt, countCoumn, remark);
                }
            }
            finally
            {
                GC.Collect();
            }
        }
Ejemplo n.º 58
0
        /// <summary>
        /// gridview导出到模板
        /// </summary>
        /// <param name="grid">数据源</param>
        /// <param name="templateURL">模板路径</param>
        /// <param name="staDate">起始日期</param>
        /// <param name="endDate">结束日期</param>
        /// <param name="insertPoint">第几行开始插入</param>
        /// <param name="remark">表格底部 要添加的备注</param>
        public static void Export(DataGridView grid, string templateURL, string staDate, string endDate, int insertPoint, string remark)
        {
            try
            {
                if (grid.Rows.Count < 1)
                {
                    return;
                }
                Object missing = System.Reflection.Missing.Value;

                Excel.Application books = new Microsoft.Office.Interop.Excel.Application();
                //加载模板
                Excel.Workbook mybook = books.Workbooks.Open(templateURL, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);//.Add(missing);
                //获取第一个工作薄
                Excel.Worksheet mysheet = (Excel.Worksheet)mybook.Worksheets[1];

                //第一行 标题
                //mysheet.Cells[1, 1] = title;
                if (staDate.Length > 0 || endDate.Length > 0)
                {
                    //第二行 统计日期
                    mysheet.Cells[2, 1] = "统计日期:";//+staDate+"  -  "+endDate;
                    mysheet.Cells[2, 2] = staDate;
                    mysheet.Cells[2, 3] = endDate;
                    //mysheet.Cells[2, 10] = "检验员:";
                }
                int columns = grid.Columns.Count; //列数
                int rows    = grid.Rows.Count;    //行数
                //填  数据
                for (int i = 0; i < grid.Rows.Count; i++)
                {
                    mysheet.get_Range(mysheet.Cells[i + insertPoint, 1], mysheet.Cells[i + insertPoint, 1]).EntireRow.Insert(Excel.XlInsertShiftDirection.xlShiftDown, missing);
                    for (int j = 0, jj = 0; j < grid.Columns.Count; j++)
                    {
                        if (grid.Columns[j].Visible == true)
                        {
                            if (grid.Rows[i].Cells[j].Value != null)
                            {
                                string tmp = grid.Rows[i].Cells[j].Value.ToString();
                                tmp = tmp.ToLower() == "true" ? "√" : tmp;
                                tmp = tmp.ToLower() == "false" ? "" : tmp;
                                mysheet.Cells[i + insertPoint, jj + 1] = tmp;
                            }
                            else
                            {
                                mysheet.Cells[i + insertPoint, jj + 1] = "";
                            }
                            jj++;
                        }
                    }
                }

                //删除模板空余行
                mysheet.get_Range(mysheet.Cells[rows + insertPoint, 1], mysheet.Cells[rows + insertPoint, 1]).EntireRow.Delete();

                //添加 合计行
                mysheet.Cells[rows + insertPoint, 1] = "合计:" + rows.ToString();

                //添加备注
                if (remark != "")
                {
                    mysheet.Cells[rows + insertPoint + 1, 1] = remark;
                    mysheet.get_Range(mysheet.Cells[rows + insertPoint + 1, 1], mysheet.Cells[rows + insertPoint + 1, columns]).Select();
                    mysheet.get_Range(mysheet.Cells[rows + insertPoint + 1, 1], mysheet.Cells[rows + insertPoint + 1, columns]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                }

                //保存
                SaveFileDialog saveFileDialog1 = new SaveFileDialog();
                saveFileDialog1.Filter     = "excel2007|*.xlsx";
                saveFileDialog1.FileName   = templateURL.Substring(templateURL.LastIndexOf('\\') + 1);
                saveFileDialog1.DefaultExt = ".xlsx";
                string fileName = "";
                if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    fileName = saveFileDialog1.FileName;
                    mybook.SaveAs(fileName, missing, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
                }

                //按顺序释放资源。
                NAR(mysheet);
                NAR(mybook);
                books.Quit();
                NAR(books);
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出Excel表出错,可能您的机器未安装Excel!系统报错:" + ex.Message, "出错提示", MessageBoxButtons.OK, MessageBoxIcon.Question);
                //if (DialogResult.Yes == MessageBox.Show("您是否想保存Excel?", "保存提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
                //{
                //    ExcelSaver.SaveExcel(dt, bookName, title, staDate, endDate, staColumns, pointTxt, countCoumn, remark);
                //}
            }
            finally
            {
                GC.Collect();
            }
        }
Ejemplo n.º 59
0
 /// <summary>
 /// EnterNameフォームのコンストラクタです。
 /// 引数として渡された、構成員名簿データと操作中のアプリケーションを自身のメンバに複製します。
 /// </summary>
 /// <param name="namelist">構成員名簿データ</param>
 /// <param name="app">操作中のExcelアプリケーション</param>
 public EnterName(string[,] namelist, Excel.Workbook book)
 {
     InitializeComponent();
     this.namelist = namelist;
     this.book     = book;
 }
Ejemplo n.º 60
-1
        private void Form1_Load(object sender, EventArgs e)
        {
            app = new Excel.Application();
            app.Visible = false;

            workbook = app.Workbooks.Open(Application.StartupPath + "\\Дни рождения сотрудников.xls");
            worksheet = (Excel.Worksheet) workbook.ActiveSheet;

            int i = 0;
            names = worksheet.get_Range("A1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count));
            departaments1 = worksheet.get_Range("B1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count));
            departaments2 = worksheet.get_Range("C1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count));
            birthdays = worksheet.get_Range("D1", "A" + Convert.ToString(worksheet.UsedRange.Rows.Count));
            string current_date = DateTime.Now.ToString("dd.MM");

            for (i = 1; i <= worksheet.UsedRange.Rows.Count; i++)
            {
                string departament1_value = Convert.ToString(departaments1.Cells[i]);
                string departament2_value = Convert.ToString(departaments1.Cells[i]);
                string birthday_value = Convert.ToString(departaments1.Cells[i]);
                string name_value = Convert.ToString(departaments1.Cells[i]);

                if (departament1_value == "Іб та ПД" || departament2_value == "Іб та ПД")
                {
                    if (birthday_value == current_date)
                        label1.Text = "Сьогодні свій день народження відзначає " + name_value;
                }
            }
            app.Quit();
        }