读写成excel

using System.Text.RegularExpressions;
using System;
using System.Reflection;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Collections;
using CommonCBase;
using las.foundation.Interfaces;
using las.foundation.SmartExcel;
using las.foundation.ExcelManager;
using System.Data.OleDb;
using System.Runtime.InteropServices;
//ItemNumber Find PluNumber
namespace FindPluNumber
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private Excel.Application m_objExcel = null;
        private Excel.Workbooks m_objBooks = null;
        private Excel._Workbook m_objBook = null;
        private Excel.Sheets m_objSheets = null;
        private Excel._Worksheet m_objSheet = null;
        private Excel.Range m_objRange = null;
        private object m_objOpt = System.Reflection.Missing.Value;
     

        CommonCBase.OracleXECBase bb = new OracleXECBase();
        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
               
                m_objExcel = new Excel.Application();
                m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
                m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
                m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
                m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));

                // Create an array for the headers and add it to cells A1:C1.
                //设置标题
                object[] objHeaders = { "品号", "条码" };
               // m_objRange = m_objSheet.get_Range("A1", "C1");
                //update
                m_objRange = m_objSheet.get_Range("A1", "B1");
                m_objRange.set_Value(m_objOpt, objHeaders);
                Object missing = Missing.Value;
                Microsoft.Office.Interop.Excel.Application excelap = new Microsoft.Office.Interop.Excel.Application();
                string yy = this.txtYuan.Text;
            
       
                Microsoft.Office.Interop.Excel.Workbook work = excelap.Workbooks.Open(@"" + yy + "",
             missing, missing, missing, missing, missing, missing, missing,
             missing, missing, missing, missing, missing, missing, missing);
                //excelap.Workbooks[1].Activate();//激活第一个工作簿
                Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)excelap.ActiveWorkbook.Sheets[1];//激活的工作簿的第一个表
                ws.Activate();
                //获得Excel的活动行
                int num = ws.UsedRange.Rows.Count+1;
             
                //设置要生成的单元格的数组 
                object[,] objData = new Object[num, 2];
                for (int i = 0; i < num - 1; i++)
                {
                    int a = i + 1;
                    Microsoft.Office.Interop.Excel.Range rng2 = ws.get_Range("A" + a.ToString(), missing);
                    string sql = "select  barcode from itembarcode where itemnumber='" + rng2.Value2.ToString() + "'";
                 
                    System.Data.DataTable dt = bb.ExeSQLdt(sql);
                    objData[i, 0] = rng2.Value2.ToString();
                    if (dt.Rows.Count > 0)
                    {
                        objData[i, 1] = dt.Rows[0][0];
                    }
                    //add new
            
                    rng2.Clear();

                }
              
                m_objRange = m_objSheet.get_Range("A2", m_objOpt);
                //m_objRange = m_objRange.get_Resize(num, 2);
                //update
                m_objRange = m_objRange.get_Resize(num, 2);
                m_objRange.NumberFormat = "@";
                m_objRange.set_Value(m_objOpt, objData);
                // Save the workbook and quit Excel.
              
                string yuanname =yy.Substring(yy.LastIndexOf('//') + 1);
                //object m_strSampleFolder = "D://test//";
                string mu = this.txtMubiao.Text + "//" + yuanname;

                m_objBook.SaveAs(@"" + mu + "", m_objOpt, m_objOpt,
                    m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
                //m_objBook.SaveAs(m_strSampleFolder + ""+yuanname+"", m_objOpt, m_objOpt,
                //    m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
                //    m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
                work.Close(false, m_objOpt, m_objOpt);
                m_objBook.Close(false, m_objOpt, m_objOpt);
               
                m_objExcel.Quit();
                //Clean-up

                m_objRange = null;
                m_objSheet = null;
                m_objSheets = null;
                m_objBooks = null;
                m_objBook = null;
                m_objExcel = null;
                GC.Collect();
                MessageBox.Show("完成");
            }
            catch {
                MessageBox.Show(e.ToString());
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            OpenFileDialog of = new OpenFileDialog();
            of.ShowDialog();
            this.txtYuan.Text = of.FileName;
      
        
        }

        private void button3_Click(object sender, EventArgs e)
        {
            folderBrowserDialog1.ShowDialog();
            this.txtMubiao.Text = folderBrowserDialog1.SelectedPath;
            string aa = this.txtMubiao.Text;
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
    }
}

 

 

 

 

 

 

 

//写execl

        private void CreateExcel()
        {
            try
            {

                m_objExcel = new Excel.Application();
                m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
                m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
                m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
                m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));

                // Create an array for the headers and add it to cells A1:C1.
                //设置标题
                object[] objHeaders = { "门市编号", "门市名称", "品号", "品名", "售价", "规格", "条码", "产地", "单位", "等级" };
                m_objRange = m_objSheet.get_Range("A1", "J1");//,"C1","D1","E1","F1","G1","H1","I1","J1");
                m_objRange.set_Value(m_objOpt, objHeaders);
                Object missing = Missing.Value;
                Microsoft.Office.Interop.Excel.Application excelap = new Microsoft.Office.Interop.Excel.Application();

    
                int num = datatable.Rows.Count;
                //设置要生成的单元格的数组 
                object[,] objData = new Object[num, 10];
                string updatebegindate = "";
                for (int i = 0; i < num; i++)
                {
                     updatebegindate = datatable.Rows[i]["updatebegindate"].ToString();
                    string quyu = datatable.Rows[i]["work_area_desc"].ToString();
                    string name = datatable.Rows[i]["STORE_Name"].ToString();
                    string STOREID = datatable.Rows[i]["STOREID"].ToString();
                    string work_area_desc = datatable.Rows[i]["work_area_desc"].ToString();
                    string itemnuber = datatable.Rows[i]["ITEMNUMBER"].ToString();
                    string itemname = datatable.Rows[i]["ITEM_NAME"].ToString();
                    string STOREUNITPRICE = datatable.Rows[i]["STOREUNITPRICE"].ToString();
                    string PRINT_TIMES = datatable.Rows[i]["PRINT_TIMES"].ToString();
                    string P_NAME = datatable.Rows[i]["P_NAME"].ToString();
                    string P_CARD = datatable.Rows[i]["P_CARD"].ToString();
                    string P_PNAME = datatable.Rows[i]["P_PNAME"].ToString();
                    string P_TEL = datatable.Rows[i]["P_TEL"].ToString();
                    string PRODUCINGAREA = datatable.Rows[i]["PRODUCINGAREA"].ToString();
                    string UNIT_DESC = datatable.Rows[i]["UNIT_DESC"].ToString();
                    string WEIGHT_DESC = datatable.Rows[i]["WEIGHT_DESC"].ToString();
                    string PLUNUMBER = datatable.Rows[i]["PLUNUMBER"].ToString();
                    string face_qty = datatable.Rows[i]["face_qty"].ToString();

                    objData[i, 0] = STOREID;
                    objData[i, 1] = name;
                    objData[i, 2] = itemnuber;
                    objData[i, 3] = itemname;
                    objData[i, 4] = STOREUNITPRICE;
                    objData[i, 5] = WEIGHT_DESC;
                    objData[i, 6] = PLUNUMBER;
                    objData[i, 7] = PRODUCINGAREA;
                    objData[i, 8] = UNIT_DESC;
                    objData[i, 9] = "一等品";

                    // "门市编号", "门市名称","品号","品名","售价","规格","条码","产地","单位","等级" }

                }

                m_objRange = m_objSheet.get_Range("A2", m_objOpt);
                m_objRange = m_objRange.get_Resize(num, 10);
                m_objRange.NumberFormat = "@";
                m_objRange.set_Value(m_objOpt, objData);
               string tt = DateTime.Parse(updatebegindate).ToString("yyyyMMdd")+"商品卡";
               string path =  Application.StartupPath +"//"+ tt;
                m_objBook.SaveAs(path+".xls", m_objOpt, m_objOpt,
                    m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);

                m_objBook.Close(false, m_objOpt, m_objOpt);

                m_objExcel.Quit();

                m_objRange = null;
                m_objSheet = null;
                m_objSheets = null;
                m_objBooks = null;
                m_objBook = null;
                m_objExcel = null;
                GC.Collect();
                MessageBox.Show("Excel产生完成");
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }