C# Excel.Application类代码示例

 private void ExportarDataGridViewExcel(DataGridView grd)
 {
     SaveFileDialog fichero = new SaveFileDialog();
     fichero.Filter = "Excel (*.xls)|*.xls";
     if (fichero.ShowDialog() == DialogResult.OK)
     {
         Microsoft.Office.Interop.Excel.Application aplicacion;
         Microsoft.Office.Interop.Excel.Workbook libros_trabajo;
         Microsoft.Office.Interop.Excel.Worksheet hoja_trabajo;
         aplicacion = new Microsoft.Office.Interop.Excel.Application();
         libros_trabajo = aplicacion.Workbooks.Add();
         hoja_trabajo =
             (Microsoft.Office.Interop.Excel.Worksheet)libros_trabajo.Worksheets.get_Item(1);
         //Recorremos el DataGridView rellenando la hoja de trabajo
         for (int i = 0; i < grd.Rows.Count ; i++)
         {
             for (int j = 0; j < grd.Columns.Count; j++)
             {
                 hoja_trabajo.Cells[i + 1, j + 1] = grd.Rows[i].Cells[j].Value.ToString();
             }
         }
         libros_trabajo.SaveAs(fichero.FileName,
             Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal);
         libros_trabajo.Close(true);
         aplicacion.Quit();
     }
 }

 private void btn_Excel_Click(object sender, EventArgs e)
 {
     if (dgv_Info.Rows.Count == 0)//判断是否有数据
         return;//返回
     Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
     excel.Application.Workbooks.Add(true);//在Excel中添加一个工作簿
     excel.Visible = true;//设置Excel显示
     //生成字段名称
     for (int i = 0; i < dgv_Info.ColumnCount; i++)
     {
         excel.Cells[1, i + 1] = dgv_Info.Columns[i].HeaderText;//将数据表格控件中的列表头填充到Excel中
     }
     //填充数据
     for (int i = 0; i < dgv_Info.RowCount - 1; i++)//遍历数据表格控件的所有行
     {
         for (int j = 0; j < dgv_Info.ColumnCount; j++)//遍历数据表格控件的所有列
         {
             if (dgv_Info[j, i].ValueType == typeof(string))//判断遍历到的数据是否是字符串类型
             {
                 excel.Cells[i + 2, j + 1] = "'" + dgv_Info[j, i].Value.ToString();//填充Excel表格
             }
             else
             {
                 excel.Cells[i + 2, j + 1] = dgv_Info[j, i].Value.ToString();//填充Excel表格
             }
         }
     }
 }

        /*
         * The method getTable opens a file, gets the first worksheet in the workbook, and fills the datatable Table with its contents.
         */
        public void setup()
        {
            OpenFileDialog openFile = new OpenFileDialog();
            openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            openFile.Filter = "Excel Workbook|*.xls";

            if (openFile.ShowDialog() == true)
            {
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);

                wb = app.Workbooks.Open(openFile.FileName);
                Microsoft.Office.Interop.Excel.Worksheet ws = wb.Sheets.get_Item(1);

                if (wb != null) // If there is a worksheet
                {
                    if (ws.UsedRange != null) // if the worksheet is not empty
                    {
                        Table = toDataTable(ws);
                    }
                }

                app.Visible = true;
            }
        }

        void CreateExcelFile()
        {
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

            excel.Workbooks.Add();
            excel.Visible = true;
            excel.DisplayAlerts = false;

            excel.Range["A1"].Value = bankDataTable.Columns[0].ColumnName;
            excel.Range["B1"].Value = bankDataTable.Columns[1].ColumnName;
            excel.Range["C1"].Value = bankDataTable.Columns[2].ColumnName;
            excel.Range["D1"].Value = bankDataTable.Columns[3].ColumnName;
            excel.Range["E1"].Value = bankDataTable.Columns[4].ColumnName;
            excel.Range["F1"].Value = bankDataTable.Columns[5].ColumnName;
            // set date and time format for column F
            excel.Range["F1", "F99"].NumberFormat = "M/D/YYYY H:MM AM/PM";
            // set width for column F
            excel.Range["F1"].EntireColumn.ColumnWidth = 17;

            // shamelessly stolen from Terri
            int j = 2;
            foreach (DataRow row in bankDataTable.Rows)
            {
                int i = 65;
                foreach( var item in row.ItemArray)
                {
                    char c1 = (char)i++;
                    string cell = c1 + j.ToString();
                    excel.Range[cell].Value = item.ToString();
                }
                j++;
            }
        }

 private void btn_Gather_Click(object sender, EventArgs e)
 {
     object missing = System.Reflection.Missing.Value;//定义object缺省值
     string[] P_str_Names = txt_MultiExcel.Text.Split(',');//存储所有选择的Excel文件名
     string P_str_Name = "";//存储遍历到的Excel文件名
     List<string> P_list_SheetNames = new List<string>();//实例化泛型集合对象，用来存储工作表名称
     Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
     //打开指定的Excel文件
     Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
     Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表
     for (int i = 0; i < P_str_Names.Length - 1; i++)//遍历所有选择的Excel文件名
     {
         P_str_Name = P_str_Names[i];//记录遍历到的Excel文件名
         //指定要复制的工作簿
         Microsoft.Office.Interop.Excel.Workbook Tempworkbook = excel.Application.Workbooks.Open(P_str_Name, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
         P_list_SheetNames = GetSheetName(P_str_Name);//获取Excel文件中的所有工作表名
         for (int j = 0; j < P_list_SheetNames.Count; j++)//遍历所有工作表
         {
             //指定要复制的工作表
             Microsoft.Office.Interop.Excel.Worksheet TempWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)Tempworkbook.Sheets[P_list_SheetNames[j]];//创建新工作表
             TempWorksheet.Copy(missing, newWorksheet);//将工作表内容复制到目标工作表中
         }
         Tempworkbook.Close(false, missing, missing);//关闭临时工作簿
     }
     workbook.Save();//保存目标工作簿
     workbook.Close(false, missing, missing);//关闭目标工作簿
     MessageBox.Show("已经将所有选择的Excel工作表汇总到了一个Excel工作表中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
     CloseProcess("EXCEL");//关闭所有Excel进程
 }

        private void importButton_Click(object sender, RoutedEventArgs e)
        {
            var app = new Microsoft.Office.Interop.Excel.Application();
            var workbook = app.Workbooks.Add();
            var worksheet = workbook.Worksheets[1];
            worksheet.Cells[1, 1] = "Ім'я";
            worksheet.Cells[1, 2] = "Дата і час";
            worksheet.Cells[1, 3] = "Кількість помилок";
            worksheet.Cells[1, 4] = "Витрачений час";
            worksheet.Cells[1, 5] = "Результат";

            using (var context = new Model.DB())
            {
                var results = context.ConfusedLinesTestResults.ToList();
                for (int i = 0; i < results.Count(); i++)
                {
                    worksheet.Cells[i + 2, 1] = results[i].Name;
                    worksheet.Cells[i + 2, 2] = results[i].Date;
                    worksheet.Cells[i + 2, 3] = results[i].ErrorsCount;
                    worksheet.Cells[i + 2, 4] = results[i].Time;
                    worksheet.Cells[i + 2, 5] = results[i].Result;
                }
            }

            app.Visible = true;
        }

 public static void Init()
 {
     lock (Lock)
     {
         _excelApp = new Microsoft.Office.Interop.Excel.Application();
     }
 }

        public static void print(DataGridView dataGridView1,string tableName)
        {
            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();//Создание объекта Excel
            ExcelApp.Application.Workbooks.Add(Type.Missing);
            ExcelApp.Columns.ColumnWidth = 15;
            ExcelApp.Cells[1, 1] = tableName;//Передаём имя таблицы
            ExcelApp.Cells[1, 1].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
            for (int i = 1; i < dataGridView1.Columns.Count; i++)//Заполняем названия столбцов
            {
                ExcelApp.Cells[2, i] = dataGridView1.Columns[i].HeaderText;
                ExcelApp.Cells[2, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
            }

            for (int i = 1; i < dataGridView1.ColumnCount; i++)//Заполняем таблицу
            {
                for (int j = 0; j < dataGridView1.RowCount; j++)
                {
                    try
                    {
                        ExcelApp.Cells[j + 3, i] = (dataGridView1[i, j].Value).ToString();
                        ExcelApp.Cells[j + 3, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                    }
                    catch { }
                }
            }
            ExcelApp.Visible = true;//Открываем Excel
        }

        public void ConvertCsvToExcel_MicrosoftOfficeInteropExcel()
        {
            StringBuilder content = new StringBuilder();
            content.AppendLine("param1\tparam2\tstatus");
            content.AppendLine("0.5\t10\tpassed");
            content.AppendLine("10\t20\tfail");

            using (TemporaryFile xlsxFile = new TemporaryFile(".xlsx"))
            {
                Clipboard.SetText(content.ToString());
                Microsoft.Office.Interop.Excel.Application xlexcel;
                Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
                Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
                xlexcel = new Microsoft.Office.Interop.Excel.Application();
                // for excel visibility
                //xlexcel.Visible = true;
                // Creating a new workbook
                xlWorkBook = xlexcel.Workbooks.Add(misValue);
                // Putting Sheet 1 as the sheet you want to put the data within
                xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet) xlWorkBook.Worksheets.get_Item(1);
                // creating the range
                Microsoft.Office.Interop.Excel.Range CR = (Microsoft.Office.Interop.Excel.Range) xlWorkSheet.Cells[1, 1];
                CR.Select();
                xlWorkSheet.Paste(CR, false);
                xlWorkSheet.SaveAs(xlsxFile.FileName);                
                xlexcel.Quit();
                Console.WriteLine("Created file {0}", xlsxFile.FileName);
            }
        }

 private void btn_Read_Click(object sender, EventArgs e)
 {
     int P_int_Count = 0;//记录正在读取的行数
     string P_str_Line, P_str_Content = "";//记录读取行的内容及遍历到的内容
     List<string> P_str_List = new List<string>();//存储读取的所有内容
     StreamReader SReader = new StreamReader(txt_Txt.Text, Encoding.Default);//实例化流读取对象
     while ((P_str_Line = SReader.ReadLine()) != null)//循环读取文本文件中的每一行
     {
         P_str_List.Add(P_str_Line);//将读取到的行内容添加到泛型集合中
         P_int_Count++;//使当前读取行数加1
     }
     Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
     object missing = System.Reflection.Missing.Value;//获取缺少的object类型值
     //打开指定的Excel文件
     Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
     Microsoft.Office.Interop.Excel.Worksheet newWorksheet;//声明工作表对象
     for (int i = 0; i < P_str_List.Count; i++)//遍历泛型集合
     {
         P_str_Content = P_str_List[i];//记录遍历到的值
         newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//创建新工作表
         newWorksheet.Cells[1, 1] = P_str_Content;//直接将遍历到的内容添加到工作表中
     }
     excel.Application.DisplayAlerts = false;//不显示提示对话框
     workbook.Save();//保存工作表
     workbook.Close(false, missing, missing);//关闭工作表
     MessageBox.Show("已经将文本文件的内容分解到了Excel的不同数据表中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
 }

        private void button2_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;
            ////Книга.
            //ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
            ////Таблица.
            //ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);

            ExcelApp.Workbooks.Open(Application.StartupPath + "\\result.xlsx");
            for (int j = 0; j < dataGridView1.ColumnCount; j++)
            {
                ExcelApp.Cells[3, j + 1] = dataGridView1.Columns[j].HeaderText;
            }

            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                for (int j = 0; j < dataGridView1.ColumnCount; j++)
                {
                    ExcelApp.Cells[i + 4, j + 1] = dataGridView1.Rows[i].Cells[j].Value;
                }
            }
            //Вызываем нашу созданную эксельку.
            ExcelApp.Visible = true;
            ExcelApp.UserControl = true;
        }

 private void btn_Read_Click(object sender, EventArgs e)
 {
     int P_int_Count=0;//记录正在读取的行数
     string P_str_Line, P_str_Content = "";//记录读取行的内容及遍历到的内容
     List<string> P_str_List = new List<string>();//存储读取的所有内容
     StreamReader SReader = new StreamReader(txt_Txt.Text, Encoding.Default);//实例化流读取对象
     while ((P_str_Line = SReader.ReadLine()) != null)//循环读取文本文件中的每一行
     {
         P_str_List.Add(P_str_Line);//将读取到的行内容添加到泛型集合中
         P_int_Count++;//使当前读取行数加1
     }
     Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();//实例化Excel对象
     object missing = System.Reflection.Missing.Value;//获取缺少的object类型值
     //打开指定的Excel文件
     Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Open(txt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
     Microsoft.Office.Interop.Excel.Worksheet newWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);
     excel.Application.DisplayAlerts = false;//不显示提示对话框
     for (int i = 0; i < P_str_List.Count; i++)//遍历泛型集合
     {
         P_str_Content = P_str_List[i];//记录遍历到的值
         if (Regex.IsMatch(P_str_Content, "^[0-9]*[1-9][0-9]*$"))//判断是否是数字
             newWorksheet.Cells[i + 1, 1] = Convert.ToDecimal(P_str_Content).ToString("￥00.00");//格式化为货币格式，再添加到工作表中
         else
             newWorksheet.Cells[i + 1, 1] = P_str_Content;//直接将遍历到的内容添加到工作表中
     }
     workbook.Save();//保存工作表
     workbook.Close(false, missing, missing);//关闭工作表
     MessageBox.Show("已经将文本文件内容成功导入Excel工作表中！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
 }

        public ExcelWriter(string _fileName)
        {
            fileName = _fileName;

            if(File.Exists(FilePath))
                throw new ApplicationException("File already exists: " + FilePath);

            File.Create(FilePath);

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

            Console.Error.WriteLine("Connected to Excel");

            wbs = app.Workbooks;

            wb = wbs.Add(1);

            wb.Activate();

            wss = wb.Sheets;

            ws = (Microsoft.Office.Interop.Excel.Worksheet)wss.get_Item(1);

            Console.Error.WriteLine("Excel Worksheet Initialized");
        }

        public static void OpenExcelDocs2(string filename, double[] content)
        {

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //引用Excel对象
            Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Open(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);   //引用Excel工作簿
            Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets.get_Item(1); ;  //引用Excel工作页面
            excel.Visible = false;

            sheet.Cells[24, 3] = content[1];
            sheet.Cells[25, 3] = content[0];

            book.Save();
            book.Close(Type.Missing, Type.Missing, Type.Missing);
            excel.Quit();  //应用程序推出，但是进程还在运行

            IntPtr t = new IntPtr(excel.Hwnd);          //杀死进程的好方法，很有效
            int k = 0;
            GetWindowThreadProcessId(t, out k);
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
            p.Kill();

            //sheet = null;
            //book = null;
            //excel = null;   //不能杀死进程

            //System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);  //可以释放对象，但是不能杀死进程
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);


        }

        /// <summary>
        /// 加载数据同时保存数据到指定位置
        /// </summary>
        /// <param name="obj"></param>
        private void AddData(FarPoint.Win.Spread.FpSpread obj)
        {
            wait = new WaitDialogForm("", "正在加载数据, 请稍候...");
            try
            {
                //打开Excel表格
                //清空工作表
                fpSpread1.Sheets.Clear();
                obj.OpenExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\铜陵县发展概况.xls");
                PF.SpreadRemoveEmptyCells(obj);
                //this.AddCellChanged();
                //this.barEditItem2.Visibility = DevExpress.XtraBars.BarItemVisibility.Never;
                //S4_2_1.AddBarEditItems(this.barEditItem2, this.barEditItem1, this);
            }
            catch (System.Exception e)
            {
                //如果打开出错则重新生成并保存
                LoadData();
                //判断文件夹是否存在，不存在则创建
                if (!Directory.Exists(System.Windows.Forms.Application.StartupPath + "\\xls"))
                {
                    Directory.CreateDirectory(System.Windows.Forms.Application.StartupPath + "\\xls");
                }
                //保存EXcel文件
                obj.SaveExcel(System.Windows.Forms.Application.StartupPath + "\\xls\\铜陵县发展概况.xls", FarPoint.Excel.ExcelSaveFlags.NoFlagsSet);
                // 定义要使用的Excel 组件接口
                // 定义Application 对象,此对象表示整个Excel 程序
                Microsoft.Office.Interop.Excel.Application excelApp = null;
                // 定义Workbook对象,此对象代表工作薄
                Microsoft.Office.Interop.Excel.Workbook workBook;
                // 定义Worksheet 对象,此对象表示Execel 中的一张工作表
                Microsoft.Office.Interop.Excel.Worksheet ws = null;
                Microsoft.Office.Interop.Excel.Range range = null;
                excelApp = new Microsoft.Office.Interop.Excel.Application();
                string filename = System.Windows.Forms.Application.StartupPath + "\\xls\\铜陵县发展概况.xls";
                workBook = excelApp.Workbooks.Open(filename, 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);
                for (int i = 1; i <= workBook.Worksheets.Count; i++)
                {

                    ws = (Microsoft.Office.Interop.Excel.Worksheet)workBook.Worksheets[i];
                    //取消保护工作表
                    ws.Unprotect(Missing.Value);
                    //有数据的行数
                    int row = ws.UsedRange.Rows.Count;
                    //有数据的列数
                    int col = ws.UsedRange.Columns.Count;
                    //创建一个区域
                    range = ws.get_Range(ws.Cells[1, 1], ws.Cells[row, col]);
                    //设区域内的单元格自动换行
                    range.WrapText = true;
                    //保护工作表
                    ws.Protect(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, Missing.Value, Missing.Value);
                }
                //保存工作簿
                workBook.Save();
                //关闭工作簿
                excelApp.Workbooks.Close();
            }
            wait.Close();
        }