把计算机信息按照部门导入到excel表中

计算机维修服务系统， 系统科要我把pcinfo表中的计算机信息，按照部门，分别导入到不同的Excel表中。我一查，有30多个部门。于是决定写个简单的代码，来完成这个工作，哎，反正闲着也是闲着 

思路如下：

在bin/Debug/文件夹下建立了两个文件夹，分别为Dept和PCInfo。

在Dept文件夹下放DeptInfo.xls，记录PCInfo表中所有的部门信息，

序号 部门 1 修船事业部 2 生产管理部 3 其他 4 公司贯标小组 5 韩国

。。。。。。

把DeptInfo.xls中的部门信息放到一个DataTable变量中，依次循环，获得部门名称，在表PCInfo中查出该表的计算机信息，导出一个以本部门名称命名的excel文件中，保存在PCInfo文件夹下。

代码如下：

--------------------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
using System.IO;    //**
using System.Reflection;  //**
using System.Runtime.InteropServices;

namespace PCInfoExport
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btn_Export_Click(object sender, EventArgs e)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["NewPCRepairConnectionString"].ConnectionString;
            SqlConnection cn = new SqlConnection(connectionString);   //与数据库的连接
           

            string strConn= "Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Data Source="+Application.StartupPath+@"/Dept/DeptInfo.xls;" +
                            "Extended Properties=Excel 8.0;";
            OleDbConnection conn =new OleDbConnection(strConn);
            OleDbDataAdapter myDataAdapter= new OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn);
            DataSet myDataSet = new DataSet();
            myDataAdapter.Fill(myDataSet);
            dataGridView1.DataSource = myDataSet.Tables[0];
            DataTable myDataTable = myDataSet.Tables[0];
            for (int i = 0; i < myDataTable.Rows.Count; i++)
            {
                string strDept = myDataTable.Rows[i][1].ToString();
                string mysql = "select PCID,PCNo,Dept,subDept,UserMan,PCtype,IP from PCInfo where Dept='" + strDept + "'";
                SqlDataAdapter da = new SqlDataAdapter(mysql, cn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                DataTable dtPC = ds.Tables[0];

                //创建一个Excel文件
                Excel.Application myExcel = new Excel.Application();
                myExcel.Application.Workbooks.Add(true);
                myExcel.Visible = true;

                object missing = Missing.Value;
                Excel.Workbook myBook = myExcel.Workbooks[1];
                Excel.Worksheet mySheet = (Excel.Worksheet)myBook.Worksheets[1];

                myExcel.Cells[1, 1] = "内部编号";
                myExcel.Cells[1, 2] = "计算机编号";
                myExcel.Cells[1, 3] = "所属部门";
                myExcel.Cells[1, 4] = "所属科室";
                myExcel.Cells[1, 5] = "使用人";
                myExcel.Cells[1, 6] = "计算机型号";
                myExcel.Cells[1, 7] = "IP";

                int StartRow = 2;  //写入计算机信息数据的起始行

                foreach (DataRow rowPC in dtPC.Rows)
                {
                    for (int j = 1; j <= 7; j++)
                    {
                        myExcel.Cells[StartRow, j] = rowPC[j-1].ToString();                        
                    }
                    StartRow++;
                }
                myExcel.Caption = strDept+"计算机信息";

                string filePath = Application.StartupPath + @"/PCInfo/" + strDept + ".xls";
                //myBook.SaveAs(filePath, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing);
                //myBook.Close(true, filePath, missing);  //关闭并保存，故上面的一句myBook.SaveAs可以不用了

                //释放资源
                if (mySheet != null)
                {
                    Marshal.ReleaseComObject(mySheet);
                    mySheet = null;
                }
                if (myBook != null)
                {
                    myBook.Close(true, filePath, missing);
                    Marshal.ReleaseComObject(myBook);
                    myBook = null;
                }
                if (myExcel != null)
                {
                    myExcel.Quit();
                    Marshal.ReleaseComObject(myExcel);
                    myExcel = null;
                }
            }//for循环
        }
    }
}

-----------------------------------------------------------------------------------------------------------------------

运行程序，30秒左右的时间，把30多个部门的信息全部到出excel文件中，呵呵，我真是太有才了！