C# Excel.ApplicationClass类代码示例

        private void btnExport_Click(object sender, EventArgs e)
        {
            Excel.ApplicationClass excelSheet = new Excel.ApplicationClass();
            excelSheet.Application.Workbooks.Add(true);
            int columnIndex = 0;
            foreach (DataGridViewColumn column in dgridResult.Columns)
            {
                if (column.HeaderText.Trim() != "" && column.Visible == true)
                {
                    columnIndex++;
                    excelSheet.Cells[1, columnIndex] = column.HeaderText;
                }
            }
            int rowIndex = 0;
            foreach (DataGridViewRow row in dgridResult.Rows)
            {
                rowIndex++;
                columnIndex = 0;
                foreach (DataGridViewColumn column in dgridResult.Columns)
                {
                    if (column.HeaderText.Trim() != "" && column.Visible == true)
                    {
                        columnIndex++;
                        excelSheet.Cells[rowIndex + 1, columnIndex] = row.Cells[column.Name].FormattedValue;

                    }
                }
            }
            excelSheet.Visible = true;
            Excel.Worksheet workSheet = (Excel.Worksheet)excelSheet.ActiveSheet;
            Excel.Range titleRange = excelSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[1, columnIndex]);
            titleRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
        }

        public void Should_read_value_repeatedly()
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();
            //xlApp.Visible = true;
            //xlApp.UserControl = true;
            string excelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ratesource.xls");
            xlWorkBook = xlApp.Workbooks.Open(excelPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, true, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            //xlWorkSheet.Change += new Excel.DocEvents_ChangeEventHandler(xlWorkSheet_Change);

            for (int i = 0; i < 100; i++)
            {
                Console.WriteLine(xlWorkSheet.get_Range("B23", "B23").Value2.ToString());
                //xlWorkSheet.
                Thread.Sleep(1000);
            }

            xlWorkBook.Close(false, misValue, misValue);
            xlApp.Quit();

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

        // Read from the Excel workbook.
        private void btnRead_Click(object sender, EventArgs e)
        {
            // Get the Excel application object.
            Excel.Application excel_app = new Excel.ApplicationClass();

            // Make Excel visible (optional).
            excel_app.Visible = true;

            // Open the workbook read-only.
            Excel.Workbook workbook = excel_app.Workbooks.Open(txtFile.Text,
                Type.Missing, true, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                Type.Missing, Type.Missing);

            // Get the first worksheet.
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets[1];

            // Get the titles and values.
            SetTitleAndListValues(sheet, 1, 1, lblTitle1, lstItems1);
            SetTitleAndListValues(sheet, 1, 2, lblTitle2, lstItems2);

            // Close the workbook without saving changes.
            workbook.Close(false, Type.Missing, Type.Missing);

            // Close the Excel server.
            excel_app.Quit();
        }

        public void Should_read_used_ranges()
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();
            //xlApp.Visible = true;
            //xlApp.UserControl = true;
            string excelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ratesource.xls");
            xlWorkBook = xlApp.Workbooks.Open(excelPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, true, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            Range excelRange = xlWorkSheet.UsedRange;

            for (int i = 0; i < 10; i++)
            {
                object[,] valueArray = (object[,])excelRange.get_Value(
                XlRangeValueDataType.xlRangeValueDefault);

                Console.WriteLine(valueArray[22, 2]);
                Thread.Sleep(1000);
            }
        }

        public ExportResult DoExport(string fileName, int userId, ExportType exportType)
        {
            try
            {
                System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                excel.Application xlsApp = new excel.ApplicationClass();
                xlsApp.DisplayAlerts = false;
                excel.Workbook xlsWorkbook;
                excel.Worksheet xlsWorksheet;
                excel.Range rng = null;
                object missing = System.Reflection.Missing.Value;

                xlsWorkbook = xlsApp.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, @"Exports\WHO_EPIRF_PC_NATDAT.xls"),
                    missing, missing, missing, missing, missing, missing, missing,
                    missing, missing, missing, missing, missing, missing, missing);


                Country country = demo.GetCountry();
                int reportYear = DateTime.Now.Year;
                xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[1];
                AddInfo(xlsWorksheet, rng, country, exportType, ref reportYear);

                //xlsApp.Visible = true;       //set to 'true' when debbugging, Exec is visible
                //xlsApp.DisplayAlerts = true; //enable all the prompt alerts for debug. 
                DateTime start = new DateTime(reportYear, 1, 1);
                DateTime end = new DateTime(reportYear, 12, 31);
                xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[2];
                AddLfMm(xlsWorksheet, start, end);
                xlsApp.Run("Sheet13.UNIT_LF");
                xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[3];
                AddOncho(xlsWorksheet, start, end);
                xlsApp.Run("Sheet17.UNIT_ONCHO");
                xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[4];
                AddSth(xlsWorksheet, start, end);
                xlsApp.Run("Sheet15.UNIT_STH");
                xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[5];
                AddSch(xlsWorksheet, start, end);
                xlsApp.Run("Sheet16.UNIT_SCH");

                xlsWorkbook.SaveAs(fileName, excel.XlFileFormat.xlOpenXMLWorkbook, missing,
                    missing, false, false, excel.XlSaveAsAccessMode.xlNoChange,
                    excel.XlSaveConflictResolution.xlUserResolution, true,
                    missing, missing, missing);
                xlsApp.ScreenUpdating = true;
                xlsApp.Visible = true;
                rng = null;

                Marshal.ReleaseComObject(xlsWorksheet);
                Marshal.ReleaseComObject(xlsWorkbook);
                Marshal.ReleaseComObject(xlsApp);
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
                return new ExportResult { WasSuccess = true };

            }
            catch (Exception ex)
            {
                return new ExportResult(ex.Message);
            }
        }

        /// <summary>
        /// this functio create a excel file with the values from a table 
        /// </summary>
        public static string Export(string path, System.Data.DataTable dt)
        {
            string output = "OK";
            try
            {
                //create a new instance of excel file
                Excel.ApplicationClass excel = new Excel.ApplicationClass();
                excel.Application.Workbooks.Add(true);

                //first let's extract the columns and create a little header

                Excel.Worksheet worksheet = (Excel.Worksheet)excel.ActiveSheet;
                worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[65535, 6]).EntireColumn.NumberFormat = "@";
                //worksheet.get_Range(worksheet.Cells[1, 6], worksheet.Cells[65535, 6]).EntireColumn.NumberFormat = "#";
                worksheet.Activate();

                int horizontal = 1;
                int vertical = 1;
                foreach (DataColumn dc in dt.Columns)
                {
                    excel.Cells[vertical, horizontal] = dc.ColumnName;

                    horizontal++;
                }

                //go to next row
                vertical++;

                foreach (DataRow row in dt.Rows)
                {
                    horizontal = 1;
                    foreach (DataColumn column in dt.Columns)
                    {

                        excel.Cells[vertical, horizontal] = row[column];

                        horizontal++;
                    }
                    vertical++;
                }
                //save the excel file

                worksheet.Name = "PROROGHE Non Lavorate";
                //DirectoryInfo dr = new DirectoryInfo(path);
                //dr.Create();
                FileInfo fi = new FileInfo(path);
                if (fi.Exists == true)
                    fi.Delete();
                worksheet.SaveAs(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                excel.Workbooks.Close();
                excel.Quit();

            }
            catch (Exception ex)
            {
                output = ex.Message + "\n" + ex.StackTrace;
            }
            return output;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();
            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("csharp.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("Excel file created , you can find the file c:\\csharp.net-informations.xls");

        }

        private void button3_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            Int16 i, j;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);

            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

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

            xlWorkBook.SaveAs(@"C:\Users\Soroush\Desktop\HospitalDatabaseOutputDrug.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);
        }

        public void Should_detect_row_and_value_changed()
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();

            string excelPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ratesource.xls");
            xlWorkBook = xlApp.Workbooks.Open(excelPath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, true, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            //xlWorkSheet.Activate();
            xlWorkSheet.Change += new Excel.DocEvents_ChangeEventHandler(xlWorkSheet_Change);

            lock (this)
            {
                Monitor.Wait(this);
            }

            xlWorkBook.Close(false, misValue, misValue);
            xlApp.Quit();

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

        /// <summary> 
        /// 获取获得当前你选择的Excel Sheet的所有名字
        /// </summary> 
        /// <param name="filePath"></param> 
        /// <returns></returns> 
        public static string[] GetExcelSheetNames(string filePath)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            Workbooks wbs = excelApp.Workbooks;
            Workbook wb = wbs.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);
            int count = wb.Worksheets.Count;
            string[] names = new string[count];
            for (int i = 1; i <= count; i++)
            {
                names[i - 1] = ((Worksheet)wb.Worksheets[i]).Name;
            }
            wb.Close(null, null, null);
            excelApp.Quit();
            wbs.Close();

            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);

            excelApp = null;
            wbs = null;
            wb = null;
            return names;
        }

        private void btn_XCLOpen_Click(object sender, EventArgs e)
        {
            XCLFileDialog.ShowDialog();
            if (XCLFileDialog.FileName != "")
            {
                Microsoft.Office.Interop.Excel.ApplicationClass appEXCEL = new Microsoft.Office.Interop.Excel.ApplicationClass();
                // create the workbook object by opening  the excel file.
                //Workbook workBook = appEXCEL.Workbooks.Open(XCLFileDialog.FileName, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                this.workBook = appEXCEL.Workbooks.Open(XCLFileDialog.FileName, 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);
                // Get The Active Worksheet Using Sheet Name Or Active Sheet

                foreach (Worksheet mySheet in workBook.Sheets)
                {
                    //Worksheet mySheet = (Worksheet)workBook.Sheets.get_Item(i);
                    comboBox2.Items.Add(mySheet.Name);
                }

                Worksheet workSheet = (Worksheet)workBook.ActiveSheet;
                comboBox2.SelectedItem = workSheet.Name;

                lbl_messages.Text = ConfigurationSettings.AppSettings["sheetText1"] + workSheet.Name + ConfigurationSettings.AppSettings["sheetText2"];
                toolStripStatusLabel2.Text = ConfigurationSettings.AppSettings["changeSheetText"];
                appEXCEL.Quit();
                button1.Enabled = true;
                button2.Enabled = false;
                btCreateXMLFlux.Enabled = true;
                button3.Enabled = true;

                webBrowser1.DocumentText = ConfigurationSettings.AppSettings["excelReadyText"];
                webBrowser2.DocumentText = ConfigurationSettings.AppSettings["excelReadyText"];
            }
        }

    static void Main(string[] args)
    {
        Excel.Application app = new Excel.ApplicationClass();
        app.Visible = true;
        app.Workbooks.Add( System.Type.Missing );

        app.ActiveCell.set_Value( Excel.XlRangeValueDataType.xlRangeValueDefault, "Hello excel" );
    }

        public List<ResultRowCell> SearchByFile(string[] listOfWords, SupplierType supplierType, string priceFileName)
        {
            Application xlApp = new Excel.ApplicationClass();
            xlApp.ReferenceStyle = XlReferenceStyle.xlA1;
            Workbook xlWorkBook = xlApp.Workbooks.Open(priceFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

            var xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(supplierType.SheetNumber);
            int lastUsedRow = xlWorkSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Row;

            //начало поиска
            var searchResult = new List<ResultRowCell>();

            for (int currentRow = supplierType.ListCell[0].Row; currentRow <= lastUsedRow; currentRow++)
            {

                var resultRowCell = new ResultRowCell();
                resultRowCell.Clear();
                for (int iCell = 0; iCell < supplierType.ListCell.Count; iCell++)
                {
                    //для всех ячеек получаем их значения
                    int currentColumn = supplierType.ListCell[iCell].Col;

                    //проверка ячейки на существование.
                    var value = ((Range) xlWorkSheet.Cells[currentRow, currentColumn]).Value;
                    string result = "";
                    if (value != null)
                        result = value.ToString();
                    else
                        result = "";

                    resultRowCell.VSupplierName = supplierType.Name;
                    resultRowCell.Add(new ResultCell(currentRow, currentColumn, result,
                                                     supplierType.ListCell[iCell].Name));
                }

                //ищем по всем словам, по всем ячейкам
                int qty = 0;
                if ((qty = ParseRow(resultRowCell, listOfWords)) > 0)
                {
                    //MessageBox.Show(@"Current row: " + currentRow.ToString());
                    resultRowCell.qty = qty;
                    searchResult.Add(resultRowCell);
                }

            }

            //конец поиска
            xlWorkBook.Close(false, null, null);
            xlApp.Quit();

            ReleaseObject(xlWorkSheet);
            ReleaseObject(xlWorkBook);
            ReleaseObject(xlApp);

            return searchResult;
        }

        public List<string[]> parseExcel(string path)
        {
            List<string[]> parsedData = new List<string[]>();
            try
            {
                // string[] row =new string[5];
                string singlerow = string.Empty;
                //Excel.FillFormat xlfile = new Excel.FillFormat();
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Range range;

                string str;
                int rCnt = 0;
                int cCnt = 0;

                xlApp = new Excel.ApplicationClass();
               // xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                //xlWorkBook = xlApp.Workbooks.Open(path, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

                range = xlWorkSheet.UsedRange;

                for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
                {
                    //string[] row = new string[9];
                    string[] row = new string[range.Columns.Count];
                    for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                    {
                        try
                        {
                            str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString();
                            row[cCnt - 1] = str;
                        }
                        catch { }
                    }
                    parsedData.Add(row);
                }

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

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
                return parsedData;
            }
            catch
            {
                return parsedData;
            }

        }

        public void draw_charts()
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkbook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlApp = new Excel.ApplicationClass();
            xlWorkbook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);

            xlWorkSheet.Cells[1, 1] = "";
            xlWorkSheet.Cells[1, 2] = "Student1";
            xlWorkSheet.Cells[1, 3] = "Student2";
            xlWorkSheet.Cells[1, 4] = "Student3";

            xlWorkSheet.Cells[2, 1] = "Term1";
            xlWorkSheet.Cells[2, 2] = "80";
            xlWorkSheet.Cells[2, 3] = "65";
            xlWorkSheet.Cells[2, 4] = "45";

            xlWorkSheet.Cells[3, 1] = "Term2";
            xlWorkSheet.Cells[3, 2] = "78";
            xlWorkSheet.Cells[3, 3] = "72";
            xlWorkSheet.Cells[3, 4] = "60";

            xlWorkSheet.Cells[4, 1] = "Term3";
            xlWorkSheet.Cells[4, 2] = "82";
            xlWorkSheet.Cells[4, 3] = "80";
            xlWorkSheet.Cells[4, 4] = "65";

            xlWorkSheet.Cells[5, 1] = "Term4";
            xlWorkSheet.Cells[5, 2] = "75";
            xlWorkSheet.Cells[5, 3] = "82";
            xlWorkSheet.Cells[5, 4] = "68";

            Excel.Range chartRange;
            Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
            Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
            Excel.Chart chartPage = myChart.Chart;

            chartRange = xlWorkSheet.get_Range("A1", "d5");
            chartPage.SetSourceData(chartRange, misValue);
            chartPage.ChartType = Excel.XlChartType.xlColumnClustered;

            xlWorkbook.SaveAs("testgraph.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("Excel file created , you can find the file c:\\csharp.net-informations.xls");
        }