C# Excel.Application类代码示例

        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 Export(string embedded)
 {
     string tmp = String.Empty;
     try
     {
         //получаем шаблон из прикладных ресурсов
         Stream template = GetResourceFileStream(embedded);
         //создаем временный файл
         tmp = System.IO.Path.GetTempFileName().Replace(".tmp", ".xlsx");
         //сохраняем шаблон во временный файл
         using (var fileStream = File.Create(tmp))
         {
             template.CopyTo(fileStream);
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
     // Создаем приложение и открываем в нём временный файл
     objApp = new Excel.Application();
     objBook = objApp.Workbooks.Open(tmp);
     objBook.Activate();
     objSheets = objBook.Worksheets;
     objSheet = (Excel._Worksheet)objSheets.get_Item(1);
 }

        public ExportData(System.Data.DataTable dt, string location)
        {
            //instantiate excel objects (application, workbook, worksheets)
            excel.Application XlObj = new excel.Application();
            XlObj.Visible = false;
            excel._Workbook WbObj = (excel.Workbook)(XlObj.Workbooks.Add(""));
            excel._Worksheet WsObj = (excel.Worksheet)WbObj.ActiveSheet;

            //run through datatable and assign cells to values of datatable

                int row = 1; int col = 1;
                foreach (DataColumn column in dt.Columns)
                {
                    //adding columns
                    WsObj.Cells[row, col] = column.ColumnName;
                    col++;
                }
                //reset column and row variables
                col = 1;
                row++;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //adding data
                    foreach (var cell in dt.Rows[i].ItemArray)
                    {
                        WsObj.Cells[row, col] = cell;
                        col++;
                    }
                    col = 1;
                    row++;
                }
                WbObj.SaveAs(location);
        }

        static void ExportToExcel(List<Car> carsInStock)
        {
            // Load up Excel, then make a new empty workbook.
            Excel.Application excelApp = new Excel.Application();
            excelApp.Workbooks.Add();

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

            // Establish column headings in cells.
            workSheet.Cells[1, "A"] = "Make";
            workSheet.Cells[1, "B"] = "Color";
            workSheet.Cells[1, "C"] = "Pet Name";

            // Now, map all data in List<Car> to the cells of the spread sheet.
            int row = 1;
            foreach (Car c in carsInStock)
            {
                row++;
                workSheet.Cells[row, "A"] = c.Make;
                workSheet.Cells[row, "B"] = c.Color;
                workSheet.Cells[row, "C"] = c.PetName;
            }

            // Give our table data a nice look and feel.
            workSheet.Range["A1"].AutoFormat(
                Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic2);

            // Save the file, quit Excel and display message to user.
            workSheet.SaveAs(string.Format(@"{0}\Inventory.xlsx", Environment.CurrentDirectory));
            excelApp.Quit();
            MessageBox.Show("The Inventory.xslx file has been saved to your app folder", "Export complete!");
        }

        private string GetExcelSheetName(string pPath)
        {
            //打开一个Excel应用

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

            ReleaseCOM(_sheet);
            ReleaseCOM(_sheets);
            ReleaseCOM(_book);
            ReleaseCOM(_books);
            _excelApp.Quit();
            ReleaseCOM(_excelApp);
            return sheetName;
        }

        private void btnGenerate_Click(object sender, EventArgs e)
        {
            //string filename = "Azure_Pass_Account" +/* datePicker.ToString() + */inputApplicant.Text;
            Excel.Application excel = new Excel.Application();
            excel.Visible = true;
            Excel.Workbook wb = excel.Workbooks.Add(1);
            Excel.Worksheet sh = wb.Sheets.Add();
            datePicker.Format = DateTimePickerFormat.Custom;
            datePicker.CustomFormat = "yyMMdd";

            sh.Name = "Azure Account";

            sh.Cells[1, "A"].Value2 = "Index";
            sh.Cells[1, "B"].Value2 = "Account";
            sh.Cells[1, "C"].Value2 = "Password";
            sh.Cells[1, "D"].Value2 = "Applicant";

            for(int index = 1; index <= Int32.Parse( inputAmount.Text); index   ++)
            {
                sh.Cells[index + 1 ,"A"].Value2 = index;
                sh.Cells[index + 1, "B"].Value2 = "MS" + datePicker.Text + index.ToString("000") + "@outlook.com";
                sh.Cells[index + 1, "C"].Value2 = "MS" + datePicker.Text;
                sh.Cells[index + 1, "D"].Value2 = inputApplicant.Text;
            }

            string filename = "Azure_Pass_Account_" + datePicker.Text + "_" + inputApplicant.Text + "_Auto_Generate";
            SaveFileDialog mySaveFileDialog = new SaveFileDialog();
            mySaveFileDialog.FileName = filename;
            mySaveFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
            mySaveFileDialog.ShowDialog();

            excel.Quit();
        }

        /// <summary/>
        public PluginManager( MSExcel.Application application )
        {
            myApplication = application;

            myPluginContext = new PluginContext( myApplication );
            myPlugins = new List<AbstractPlugin>();
        }

        public excel()
        {
            var excel = new Excel.Application();
            //OR
            /*
                Type ExcelType = Type.GetTypeFromProgID("Excel.Application");
                dynamic excel = Activator.CreateInstance(ExcelType);
             */

            var workbook = excel.Workbooks.Add();
            var sheet = excel.ActiveSheet;
            excel.Visible = true;

            excel.Cells[1, 1] = "Hi from Me";
            excel.Columns[1].AutoFit();

            excel.Cells[2,1]=10;
            excel.Cells[3, 1] = 10;
            excel.Cells[4, 1] = 20;
            excel.Cells[5, 1] = 30;
            excel.Cells[6, 1] = 40;
            excel.Cells[7, 1] = 50;
            excel.Cells[8, 1] = 60;

            var chart = workbook.Charts.Add(After:sheet);
            chart.ChartWizard(Source:sheet.Range("A2","A7"));
        }

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

        private void CopyToClipboardButton_Click(object sender, EventArgs e)
        {
            if (EmailResultTextBox.Text.Length == 0)
            {
                return;
            }
            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            excelApp.Visible = true;

            _Workbook workbook = (_Workbook)(excelApp.Workbooks.Add(Type.Missing));
            _Worksheet worksheet = (_Worksheet)workbook.ActiveSheet;

            TicketRepository ticketRepo = new TicketRepository();
            List<TicketResource> listOfTickets = ticketRepo.GetDistinctEmailAddressBetweenDates(StartingDatePicker.Value, EndingDatePicker.Value);

            for (int i = 0; i < listOfTickets.Count; i++)
            {
                TicketResource ticket = listOfTickets[i];
                int row = i + 1;
                worksheet.Cells[row, "A"] = ticket.LastName;
                worksheet.Cells[row, "B"] = ticket.FirstName;
                worksheet.Cells[row, "C"] = ticket.Email;
            }
            worksheet.Columns["A:C"].AutoFit();
        }

        public void CallMacro(string file)
        {
            Excel.Application xlApp;
            Workbook xlWorkBook;
            Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(file);//, 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);
            xlWorkSheet
            MessageBox.Show(xlWorkSheet.get_Range("A1", "A1").Value2.ToString());
            RunMacro(
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            //        Application.Run "PERSONAL.XLSB!CleanDocket"
            //Application.Run "PERSONAL.XLSB!Create_Upcoming_Docket"
            //Sheets("Upcoming Hearings").Select
            //Sheets("Upcoming Hearings").Move Before:=Sheets(1)

            /*
             *
             *

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);*/
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            try
            {
                SaveFileDialog sfd = new SaveFileDialog();
                Microsoft.Office.Interop.Excel.Application myExcelFile = new Microsoft.Office.Interop.Excel.Application();

                sfd.Title = "Save As";
                sfd.Filter = "Microsoft Excel|*.xlsx";
                sfd.DefaultExt = "xlsx";

                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    Workbook myWorkBook = myExcelFile.Workbooks.Add(XlSheetType.xlWorksheet);
                    Worksheet myWorkSheet = (Worksheet)myExcelFile.ActiveSheet;

                    // don't open excel file in windows during building
                    myExcelFile.Visible = false;

                    label1.Text = "Building the excel file. Please wait...";

                    // set the first row cells as column names according to the names of data grid view columns names
                    foreach (DataGridViewColumn dgvColumn in dataGridView.Columns)
                    {
                        // dataGridView columns is a zero-based array, while excel sheet is a 1-based array
                        // so, first row of excel sheet has index 1
                        // set columns of first row as titles of columns
                        myWorkSheet.Cells[1, dataGridView.Columns.IndexOf(dgvColumn) + 1] = dgvColumn.HeaderText;
                    }

                    // since, first row has titles that are set above, start from row-2 and fill each row of excel file.
                    for (int i = 2; i <= dataGridView.Rows.Count; i++)
                    {
                        for (int j = 0; j < dataGridView.Columns.Count; j++)
                        {
                            myWorkSheet.Cells[i, j + 1] = dataGridView.Rows[i - 2].Cells[j].Value;
                        }
                    }

                    // set the font style of first row as Bold which has titles of each column
                    myWorkSheet.Rows[1].Font.Bold = true;
                    myWorkSheet.Rows[1].Font.Size = 12;

                    // after filling, save the file to the specified location
                    string savePath = sfd.FileName;
                    myWorkBook.SaveCopyAs(savePath);
                }

                label1.Text = "File saved successfully.";

                if (MessageBox.Show("File saved successfully. Do you want to open it now?", "File Saved!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    myExcelFile.Visible = true;
                }
            }
            catch(Exception exc)
            {
                label1.Text = exc.Message;
            }
        }

        public ReportDataSet ReadDataSet(string file, int headerRowNumber)
        {
            ReportDataSet dataSet = new ReportDataSet();
            try
            {
                excel = new Excel.Application();
                Excel.Workbook tempBook = excel.Workbooks.Open(file);

                foreach (var item in tempBook.Sheets)
                {
                    Excel.Worksheet sheet = item as Excel.Worksheet;
                    ReportDataTable table = this.GetDataTable(sheet, headerRowNumber);
                    dataSet.Tables.Add(table.Name, table);
                }

                tempBook.Save();
                tempBook.Close();
                tempBook = null;
                excel.Application.Quit();
            }
            catch (Exception ex)
            {
                excel.Application.Quit();
            }

            return dataSet;
        }

        public DocSoc()
        {
            string reportsFile = System.Configuration.ConfigurationManager.AppSettings["ReportsFile"];
            string macrosFile = System.Configuration.ConfigurationManager.AppSettings["MacrosFile"];
            System.IO.FileInfo macrosFileInfo = new System.IO.FileInfo(macrosFile);
            Hashtable reportsHash = readReportsXML(reportsFile,macrosFileInfo.Name);
            string[] reportsSelected = getSelectedReports(reportsHash);

            Excel.Application xlApp;
            Excel.Workbook macroBook = null;

            xlApp = new Excel.Application();
            Console.Write("Excel opened");

            try
            {
                macroBook = xlApp.Workbooks.Open(macrosFileInfo.FullName);
                Console.WriteLine("MacroFile opened: " + macrosFileInfo.FullName);

                this.processReports(xlApp, reportsHash, reportsSelected);
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                System.Windows.Forms.MessageBox.Show("DocToSoc failed while processing reports.", "DocToSoc Failed", System.Windows.Forms.MessageBoxButtons.OK);
            }
            finally
            {
                macroBook.Close();
                Console.WriteLine("MacroWorkBook closed: " + macrosFile);
                xlApp.Quit();
                Console.WriteLine("Excel Closed");
            }
        }

        public void read(ref List<string> grpname , ref List<string> path)
        {
            createfile objcf = new createfile();
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            Excel.Range range;
            object missing = System.Reflection.Missing.Value;

            xlApp = new Excel.Application();

            xlWorkBook = xlApp.Workbooks.Open(@"d:\database\Group.xlsx", 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", true, true, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            range = xlWorkSheet.UsedRange;
            int i = 0;
            string str,str1;
            for (int rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
            {
                str = (string)(range.Cells[rCnt, 1] as Excel.Range).Value2;
                str1 = (string)(range.Cells[rCnt, 2] as Excel.Range).Value2;
                grpname.Add(str);
                path.Add(str1);

            }
            xlWorkBook.Close(true, null, null);
            xlApp.Quit();

            objcf.releaseObject(xlWorkSheet);
            objcf.releaseObject(xlWorkBook);
            objcf.releaseObject(xlApp);
        }