计算机维修服务系统, 系统科要我把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文件中,呵呵,我真是太有才了!