C# Excel.Worksheet类代码示例

        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);
        }

        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();
        }

 public static void openXL(string xlp)
 {
     xla = new Excel.Application();
     xlw = xla.Workbooks.Open(xlp);
     xls = xlw.Worksheets.get_Item(1);
     xlr = xls.UsedRange;
 }

 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);
 }

 public Sheet(Excel.Worksheet _worksheet)
 {
     this.Base = _worksheet;
     this.CurrentRow = 1;
     this.CurrentCol = 1;
     this.Valid = ReadValues();
 }

 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;
 }

        //private void wrtBySht(List<string> workList)
        private void wrtBySht(string shtName)
        {

            xlWorkSht = xlWorkBookTar.Worksheets.get_Item(shtName);
            xlWorkSht.Activate();

            string idx = Util.TaskInfo.TaskSetting.insertPtInstData; // start point index
            Excel.Range rng = xlWorkSht.get_Range(idx, idx);

            DataTable dt;
            if (shtName == "InstrumentClassData")
                dt = Util.DbConn.SqlTsk.GetTable("procGetInstData");
            else
                dt = Util.DbConn.SqlTsk.GetTable("procGetSymData " + shtName);


            int j = 1;
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                for (i = 0; i < dt.Columns.Count; i++)
                {
                    rng[j + 1, i + 1].Value = row[i].ToString();

                }
                j++;
                if (j > dt.Rows.Count)
                {
                    break;
                }
            }
            rng[j + 1, 1].Value = "end";

        }

        //加载
        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();
        }

        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);
            }
        }

        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();
        }

        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;
            }
        }

        public ReportGenerator(Excel.Worksheet src_worksheet, Excel.Worksheet dest_worksheet)
        {
            _src_worksheet = src_worksheet;
            _dest_worksheet = dest_worksheet;

            writeProjectNamesToDestSheet();
        }

        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);
        }

        internal OrderingSheet(bool isUnscheduled)
        {
            if (!Globals.DataSet.IsLastDayComplete())
            {
                throw new ApplicationException(Globals.ThisWorkbook.IncompleteDataMessage);
            }

            this.orderDate = Globals.DataSet.MaxDate;

            string worksheetName;

            if (isUnscheduled)
            {
                worksheetName = ExcelHelpers.CreateValidWorksheetName(
                    String.Format(
                        CultureInfo.CurrentUICulture,
                        Properties.Resources.UnscheduledOrderSheetName,
                        this.orderDate.ToShortDateString()));
            }
            else
            {
                worksheetName = ExcelHelpers.CreateValidWorksheetName(
                    String.Format(
                        CultureInfo.CurrentUICulture,
                        Properties.Resources.WeeklyOrderSheetName,
                        this.orderDate.ToShortDateString()));
            }
            Excel.Worksheet worksheet = null;

            // 如果该名称已经存在，则在创建工作表时将引发异常。
            try
            {
                worksheet = Globals.ThisWorkbook.CreateWorksheet(worksheetName);
            }
            catch (Exception ex)
            {
                string message;

                if (isUnscheduled)
                {
                    message = String.Format(
                        CultureInfo.CurrentUICulture,
                        Properties.Resources.UnscheduledOrderSheetCreationError,
                        worksheetName);
                }
                else
                {
                    message = String.Format(
                        CultureInfo.CurrentUICulture,
                        Properties.Resources.WeeklyOrderSheetCreationError,
                        worksheetName);
                }

                throw new ApplicationException(message, ex);
            }

            this.worksheet = worksheet;

            CreateOrder(isUnscheduled);
        }

        /// <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;
        }