Excel com 方式操作

Excel 编程中常用的对象的层次关系

Excel Application   代表整个 Microsoft Excel 应用程序，

WorkBook            代表 Microsoft Excel 工作簿

Range                    代表某一单元格、某一行、某一列、某一选定区域（该区域可包含一个或若干连续单元格区域），或者某一三维区域。

Areas                       选定区域内的子区域或连续单元格块的集合。

Borders                 代表对象的边框。

Characters             代表包含文本的对象中的字符。可用 Characters 对象修改包含在完整文本字符串中的任意字符序列。

Font                        包含对象的字体属性（字体名称、字体大小、字体颜色等）。

ListRow                 代表列表对象中的一行。

Errors                      表示区域的电子表格错误。

 向Excel模板，指定的单元格写入数据，另存成一个excel文件

1.把Excel模板复制到最后生成Excel文件夹

  string runExeDir = Environment.CurrentDirectory;
            string excelTemplate = runExeDir + "\\ExcelTemplate\\CcbReport.xls";
            string excelReport = runExeDir + "\\CcBReport\\CcbReport.xls";
            if (File.Exists(excelReport))
            {
                File.Delete(excelReport);
            }
            File.Copy(excelTemplate, excelReport);

 

 2.读取模板的数据，读取模板数据要读取模板的Excel，如果读取复制过来的Excel模板会出现占用错误。（猜测：读完excel的时候资源不能立刻释放。）

   如果程序在Excel2003下使用，添加引用“Microsoft Excel 11.0 object library”，如果com里面没有此library，安装office2003再添加引用。

 

  private void DoExcel(double dGrossProfitPlasmaRatio, double dGrossProfitLaserRatio, string strExcelPath)
        {
           
           try
           {
            
            ShowPictureLoading(true);
            //Excel2007
           // string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" +
           // "Extended Properties=\"Excel 12.0 Xml;HDR=No;IMEX=1\";" +
           //"Data Source=" + strExcelPath;

           // //Excel97-2003
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";" +
           "data source=" + strExcelPath;

         

            OleDbConnection conn = new OleDbConnection(strConn);          
            conn.Open();
       

            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", conn);
            OleDbDataAdapter da = new OleDbDataAdapter();        
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();          
            da.Fill(ds, "myExcel");
            conn.Close();


            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
            ////打开一个现有的工作薄
            Microsoft.Office.Interop.Excel.Workbook excelBook = excelApp.Workbooks.Add(strExcelPath);
            Sheets shs = excelBook.Sheets;
            ////选择第一个Sheet页
            Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)shs.get_Item(1);   
                     
        

            int rows = ds.Tables[0].Rows.Count;
            int cols = ds.Tables[0].Columns.Count;
         
            SetExcelTitle(excelSheet, rows);

            string strContractPrice;//合同价格
            double dContractPrice;
            string strBottomPrice;
            double dBottomPrice;
            //发货款
            string strDeliveryMoney;
            double dDeliveryMoney;
            //合同号
            string strContractNo;
            //指定比例
            string strSpecifyRatio;
            double dSpecifyRatio;
            //质保日期
            string strGuaranteeDate;
            int iGuaranteeDate;
            //余额
            string strBalance;
            double dBalance;
            //应收款逾期
            string strCollectOverdueMonth;
            int iCollectOverdueMonth = 0;
            //机器发货逾期
            string strDeliveryMonth;
            int iDeliveryMonth = 0;

             //总的交货比例=预付比例+提货比例
            double dTotalDeliveryMoneyRatio;
            //预付 比例
            string strPrepayRatio;
            double dPrepayRatio;
            //提货 比例
            string strDeliveryMoneyRatio;
            double dDeliveryMoneyRatio;
            //质保金 比例
            string strGuaranteeMoneyRatio;
            double dGuaranteeMoneyRatio;

            double dOriginalMoney;
            double dContractPriceRatio;
          
           
            int iCollectOverdueRatio;
            int iDeliveryRatio;
            double dFinalMoney;
            double dGrossProfitRatio;
            int j = 5;
            for (int i = 5; i < rows; i++)
            {
                //合同价格
                strContractPrice = ds.Tables[0].Rows[i][11].ToString().Trim();
                if (strContractPrice != "")
                {                    
                  
                     dContractPrice = GetStringToDoubleValue(strContractPrice);
                     //指定比例
                     strSpecifyRatio = ds.Tables[0].Rows[i][32].ToString().Trim();
                     strSpecifyRatio = strSpecifyRatio.Replace("%", "");
                     if (strSpecifyRatio == "")
                     {
                         //注意资源释放先后
                         ReleaseCOM(excelSheet);
                         ReleaseCOM(shs);
                         ReleaseCOM(excelBook);
                         excelApp.Quit();
                         ReleaseCOM(excelApp);
                         GC.Collect();
                         ShowMsg("Excel报表缺少指定比例数据!");
                         break;
                     }
                     dSpecifyRatio = GetStringToDoubleValue(strSpecifyRatio);


                    //合同号
                     strContractNo = ds.Tables[0].Rows[i][0].ToString().Trim();
                     if (strContractNo.Substring(0, 2) == "19")
                     {
                         dGrossProfitRatio = dGrossProfitLaserRatio;
                     }
                     else
                     {
                         dGrossProfitRatio = dGrossProfitPlasmaRatio;
                     }
                    //合同底价
                    strBottomPrice = ds.Tables[0].Rows[i][12].ToString().Trim();
                    dBottomPrice = GetStringToDoubleValue(strBottomPrice);

                    //预付 比例
                    strPrepayRatio = ds.Tables[0].Rows[i][13].ToString().Trim();
                    strPrepayRatio = strPrepayRatio.Replace("%", "");
                    dPrepayRatio = GetStringToDoubleValue(strPrepayRatio);

                    //提货 比例
                    strDeliveryMoneyRatio = ds.Tables[0].Rows[i][14].ToString().Trim();
                    strDeliveryMoneyRatio = strDeliveryMoneyRatio.Replace("%", "");
                    dDeliveryMoneyRatio = GetStringToDoubleValue(strDeliveryMoneyRatio);

                  
               
                    //质保金 比例
                    strGuaranteeMoneyRatio = ds.Tables[0].Rows[i][16].ToString().Trim();
                    strGuaranteeMoneyRatio = strGuaranteeMoneyRatio.Replace("%", "");
                    dGuaranteeMoneyRatio = GetStringToDoubleValue(strGuaranteeMoneyRatio);   
 
                    //提货款
                    strDeliveryMoney = ds.Tables[0].Rows[i][25].ToString().Trim();
                    dDeliveryMoney = GetStringToDoubleValue(strDeliveryMoney);

                    //质保期
                    strGuaranteeDate = ds.Tables[0].Rows[i][21].ToString().Trim();
                    iGuaranteeDate = GetStringToInt(strGuaranteeDate);
                    //余额  质保金
                    strBalance = ds.Tables[0].Rows[i][29].ToString().Trim();
                    dBalance = GetStringToDoubleValue(strBalance);

                    //应收款逾期
                    strCollectOverdueMonth = ds.Tables[0].Rows[i][30].ToString().Trim();
                    iCollectOverdueMonth = GetStringToInt(strCollectOverdueMonth);

                    //机器发货逾期
                    strDeliveryMonth = ds.Tables[0].Rows[i][18].ToString().Trim();
                    iDeliveryMonth = GetStringToInt(strCollectOverdueMonth);

                  

                    //写入理论奖金,合同价格奖金比例，发货款价格奖金比例，质保金，应收账款，机器发货，实发奖金
                    dOriginalMoney = BonusType.GetOriginalMoney(dContractPrice, dSpecifyRatio);
                    dContractPriceRatio = BonusType.GetContractPriceRatio(dContractPrice, dBottomPrice, dGrossProfitRatio);
                    //发货款价格奖金比例
                    //dDeliveryMoneyRatio = BonusType.GetDeliveryMoneyRatio(dContractPrice, dDeliveryMoney);
                    dTotalDeliveryMoneyRatio = dPrepayRatio + dDeliveryMoneyRatio;
                    //质保金比列
                    //dGuaranteeMoneyRatio = BonusType.GetGuaranteeMoneyRatio(dContractPrice, iGuaranteeDate, dBalance);
                    //应收账款比例
                    iCollectOverdueRatio = BonusType.CollectOverdueRatio(iCollectOverdueMonth);
                    //机器发货比例
                    iDeliveryRatio = BonusType.DeliveryRatio(iDeliveryMonth);
                    //实发奖金比例
                    dFinalMoney = BonusType.GetFinalMoney(dOriginalMoney, dContractPriceRatio, dTotalDeliveryMoneyRatio, dGuaranteeMoneyRatio, iCollectOverdueRatio, iDeliveryRatio, dBalance, dDeliveryMoney);

                    excelSheet.Cells[i + 1, 35] = dOriginalMoney.ToString();

                    excelSheet.Cells[i + 1, 36] = dContractPriceRatio.ToString() + "%";

                    excelSheet.Cells[i + 1, 37] = dTotalDeliveryMoneyRatio.ToString() + "%";

                    excelSheet.Cells[i + 1, 38] = dGuaranteeMoneyRatio.ToString() + "%";

                    excelSheet.Cells[i + 1, 39] = iCollectOverdueRatio.ToString() + "%";

                    excelSheet.Cells[i + 1, 40] = iDeliveryRatio.ToString() + "%";
                    excelSheet.Cells[i + 1, 41] = dFinalMoney.ToString();

                    j++;
                }
                else
                {
                    break;
                }

            }//end for
       
            SetExcelCellFormat(excelSheet, j);          
        
              try
              {

              
                  excelApp.AlertBeforeOverwriting = false;
                  excelApp.DisplayAlerts = false;

                   //2007   Type.Missing 默认生成是2007 格式，要生成Excel2003格式  XlFileFormat.xlExcel8 
                  //excelBook.SaveAs(strExcelPath,
                  //    XlFileFormat.xlExcel8, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                  //    XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,
                  //    Type.Missing, Type.Missing); 

                  //用在只安装Excel2003版本使用着
                  excelBook.SaveAs(strExcelPath,
                    XlFileFormat.xlExcel9795, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                     XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing); 
                 
                   excelBook.Close(false, Type.Missing, Type.Missing);
           
                //注意资源释放先后
                ReleaseCOM(excelSheet);
                ReleaseCOM(shs); 
                ReleaseCOM(excelBook);              
                excelApp.Quit();
                ReleaseCOM(excelApp);
                GC.Collect();
                ShowPictureLoading(false);
                ShowMsg("处理完成！");
              }
              catch (System.Exception ex)
              {
                  //注意资源释放先后
                  ReleaseCOM(excelSheet);
                  ReleaseCOM(shs); 
                  ReleaseCOM(excelBook);                 
                  excelApp.Quit();
                  ReleaseCOM(excelApp);
                  GC.Collect();             
                  ShowMsg("Excel文件占用!请关闭Excel文件或通过任务管理器关闭Excel.exe进程.");
                  ShowPictureLoading(false);
              }
            }
            catch (System.Exception ex)
            {
              
               ShowMsg(ex.Message);
               ShowPictureLoading(false);
            }
        }
        //资源释放
        private static void ReleaseCOM(object pObj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
            }
            catch
            {
                throw new Exception("Release resource Error!");
            }
            finally { pObj = null; }
        }
        private void SetExcelTitle(_Worksheet myWorkSheet,int rows)
        {
            Range myRange1 = myWorkSheet.Range[myWorkSheet.Cells[4, 35], myWorkSheet.Cells[5, 35]];
            myRange1.MergeCells = true;

            Range myRange2 = myWorkSheet.Range[myWorkSheet.Cells[4, 36], myWorkSheet.Cells[5, 36]];
            myRange2.MergeCells = true;

            Range myRange3 = myWorkSheet.Range[myWorkSheet.Cells[4, 37], myWorkSheet.Cells[5, 37]];
            myRange3.MergeCells = true;

            Range myRange4 = myWorkSheet.Range[myWorkSheet.Cells[4, 38], myWorkSheet.Cells[5, 38]];
            myRange4.MergeCells = true;

            Range myRange5 = myWorkSheet.Range[myWorkSheet.Cells[4, 39], myWorkSheet.Cells[5, 39]];
            myRange5.MergeCells = true;

            Range myRange6 = myWorkSheet.Range[myWorkSheet.Cells[4, 40], myWorkSheet.Cells[5, 40]];
            myRange6.MergeCells = true;

            Range myRange7= myWorkSheet.Range[myWorkSheet.Cells[4, 41], myWorkSheet.Cells[5, 41]];
            myRange7.MergeCells = true;
          

            myWorkSheet.Cells[4, 35] = "基本奖金";
            myWorkSheet.Cells[4, 36] = "合同价格比例";
            myWorkSheet.Cells[4, 37] = "发货款比例";
            myWorkSheet.Cells[4, 38] = "质保金比例";
            myWorkSheet.Cells[4, 39] = "应收账款扣款比例";
            myWorkSheet.Cells[4, 40] = "机器发货扣款比例";
            myWorkSheet.Cells[4, 41] = "实发奖金";      
     

            Range myRange = myWorkSheet.Range[myWorkSheet.Cells[4, 35], myWorkSheet.Cells[5, 41]];         
            myRange.Font.Size = 10;
            myRange.Font.Bold = true;
            myRange.Font.Color = Color.Blue;
            myRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            myRange.Borders.LineStyle = 1;
           

        }
        private void SetExcelCellFormat(_Worksheet myWorkSheet, int rows)
        {
            Range myRange = myWorkSheet.Range[myWorkSheet.Cells[6, 35], myWorkSheet.Cells[rows, 41]];
            myRange.Font.Size = 10;
            myRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            myRange.Borders.LineStyle = 1;
             
            //保留小数位数为2，并使用千位分隔符
            Range myRange1 = myWorkSheet.Range[myWorkSheet.Cells[6, 35], myWorkSheet.Cells[rows, 35]];
            myRange1.NumberFormatLocal = "#,##0.00";
            //保留小数位数为2，并使用千位分隔符
            Range myRange2 = myWorkSheet.Range[myWorkSheet.Cells[6, 41], myWorkSheet.Cells[rows, 41]];
            myRange2.NumberFormatLocal = "#,##0.00";

        }

 

 

 

 3.写入数据 

/// <summary>
/// 向Excel写入数据
/// </summary>
/// <param name="rows"></param>
/// <param name="cols"></param>
/// <param name="itemType"></param>
/// <param name="pageQty"></param>
/// <param name="excelTemplate">Excel模板</param>
 /// <param name="excelPath">写入后的Excel保存路径</param>
        public static void WriteInExcel(int rows, int cols, string itemType, string pageQty, string excelTemplate,string excelPath)
        {

            Application excelApp = new Application();
            Workbooks excelBooks = excelApp.Workbooks;
            //打开一个现有的工作薄
            _Workbook excelBook = excelBooks.Add(excelTemplate);
            Sheets shs = excelBook.Sheets;
            //选择第一个Sheet页
            _Worksheet excelSheet = (_Worksheet)shs.get_Item(1);

            if (itemType == "2")
            {
                excelSheet.Cells[rows, cols + 2] = pageQty;
            }
            if (itemType == "4")
            {
                excelSheet.Cells[rows, cols + 3] = pageQty;
            }
            excelApp.AlertBeforeOverwriting = false;
            excelApp.DisplayAlerts = false;

          
            excelBook.SaveAs(excelPath,
               Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
               XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
               Missing.Value, Missing.Value);
            excelBook.Close(false, Missing.Value, Missing.Value);
            //注意资源释放先后
            ReleaseCOM(excelSheet);
            ReleaseCOM(shs);
            ReleaseCOM(excelBook);
            ReleaseCOM(excelBooks);
            excelApp.Quit();
            ReleaseCOM(excelApp);
            GC.Collect();        
        }
        //资源释放
        private static void ReleaseCOM(object pObj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
            }
            catch
            {
                throw new Exception("Release resource Error!");
            }
            finally { pObj = null; }
        }

 4. 

如果我们的工程中引用的是Excel 2007的DLL，那么缺省保存的文件格式为2007。

所以，如果我们期望保存为缺省格式（如2007的格式），则用   workbook.SaveAs(excelFullName, Type.Missing, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

如果想保存为2003的格式，则用 workbook.SaveAs(excelFullName, XlFileFormat.xlExcel8, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);

5.合并单元格和指定格式

 

  private void SetExcelTitle(_Worksheet myWorkSheet,int rows)
        {
            Range myRange1 = myWorkSheet.Range[myWorkSheet.Cells[4, 35], myWorkSheet.Cells[5, 35]];
            myRange1.MergeCells = true;

            Range myRange2 = myWorkSheet.Range[myWorkSheet.Cells[4, 36], myWorkSheet.Cells[5, 36]];
            myRange2.MergeCells = true;

            Range myRange3 = myWorkSheet.Range[myWorkSheet.Cells[4, 37], myWorkSheet.Cells[5, 37]];
            myRange3.MergeCells = true;

            Range myRange4 = myWorkSheet.Range[myWorkSheet.Cells[4, 38], myWorkSheet.Cells[5, 38]];
            myRange4.MergeCells = true;

            Range myRange5 = myWorkSheet.Range[myWorkSheet.Cells[4, 39], myWorkSheet.Cells[5, 39]];
            myRange5.MergeCells = true;

            Range myRange6 = myWorkSheet.Range[myWorkSheet.Cells[4, 40], myWorkSheet.Cells[5, 40]];
            myRange6.MergeCells = true;

            Range myRange7= myWorkSheet.Range[myWorkSheet.Cells[4, 41], myWorkSheet.Cells[5, 41]];
            myRange7.MergeCells = true;
          

            myWorkSheet.Cells[4, 35] = "理论奖金";
            myWorkSheet.Cells[4, 36] = "合同价格";
            myWorkSheet.Cells[4, 37] = "发货款";
            myWorkSheet.Cells[4, 38] = "质保金";
            myWorkSheet.Cells[4, 39] = "应收账款";
            myWorkSheet.Cells[4, 40] = "机器发货";
            myWorkSheet.Cells[4, 41] = "实发奖金";      
     

            Range myRange = myWorkSheet.Range[myWorkSheet.Cells[4, 35], myWorkSheet.Cells[5, 41]];         
            myRange.Font.Size = 10;
            myRange.Font.Bold = true;
            myRange.Font.Color = Color.Blue;
            myRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            myRange.Borders.LineStyle = 1;
           

        }