客运综合管理系统项目—附加（数据的导出）

2.数据的导出

                                                                                                                              2（图1）

数据库

1.      表与关系

相关的表有

表1.员工表(dbo.StaffList)

列名	数据类型	主键/外键	说明
StaffID	int	主键	员工ID
StaffNumber	char (100)		员工编号
StaffName	char (100)		员工姓名
Sex	char (100)		性别
IdentityCardNumber	char (100)		身份证号
HomeLocation	char (100)		家庭地址
Phone	char (100)		电话
StaffTypeID	int	外键	员工类型表.员工类型ID
OrganizationID	int	外键	机构表.机构ID
StationID	int	外键	站点表.站点ID
Remarks	char (100)		备注
LeaveOfficeNo	bit		离职否
InvokingNo	bit		调用否
Date	datetime		日期
Photo	nvarchar (3000)		相片

 

（1）与档次数据相关的存储过程

 IF 	@TYPE='frmWagesSettleAccountsSearch_SelectPayAccounts'--数据的绑定（所导出的数据）
    BEGIN
    SELECT     PayAccountsList.PayAccountsID,StaffList.StaffID, --LTRIM(RTRIM(***表.名称))AS 名称,--去空格
               LTRIM(RTRIM(StaffList.StaffNumber))AS StaffNumber, LTRIM(RTRIM(StaffList.StaffName))AS StaffName, 
               LTRIM(RTRIM(StaffList.Phone))AS Phone,StaffTypeList.StaffTypeID,
               LTRIM(RTRIM(StaffTypeList.StaffTypeName))AS StaffTypeName,OrganizationList.OrganizationID, 
               LTRIM(RTRIM(OrganizationList.OrganizationName))AS OrganizationName, StationList.StationID,
               LTRIM(RTRIM(StationList.StationName))AS StationName, PayAccountsList.AccountsDate,PayAccountsList.Rums, 
               PayAccountsList.AccountsOpenDate, PayAccountsList.AccountEndDate,PayAccountsList.DayPay,
               PayAccountsList.OvertimePay,PayAccountsList.AccountNo
    FROM       StaffList INNER JOIN
               PayAccountsList ON StaffList.StaffID = PayAccountsList.StaffID INNER JOIN
               StaffTypeList ON StaffList.StaffTypeID = StaffTypeList.StaffTypeID INNER JOIN
               OrganizationList ON StaffList.OrganizationID = OrganizationList.OrganizationID INNER JOIN
               StationList ON StaffList.StationID = StationList.StationID
END

（2）与档次数据相关的逻辑层(BLL)代码

 [OperationContract]
        public DataSet frmWagesSettleAccountsSearch_SelectPayAccounts()//dgv日常上班（导出数据）
        {
            SqlParameter[] mySqlParameter = { new SqlParameter("@Type", SqlDbType.Char) };
            mySqlParameter[0].Value = "frmWagesSettleAccountsSearch_SelectPayAccounts";
            DataTable myDataTable = myDALMethod.QueryDataTable("BusinessManage_frmWagesSettleAccountsSearch", mySqlParameter);
            DataSet myDataSet = new DataSet();
            myDataSet.Tables.Add(myDataTable);
            return myDataSet;
        }

（3）添加一个DataSet文件

                                     2（图2）

打开文件里面显示如图2（图3）所示

                                                                                                                             2（图3）

新建一个文件

                                                                                                                         2（图4）

                                                                                                                         2（图5）

                                                                                                                              2（图6）

                                                                                             2（图7）

                                                                                                    2（图8）

                                                                                    2（图9）

                                                                                              2（图10）

                                                                                                2（图11）

                                                                                                                 2（图12）

                                                                                                          2（图13）

                                                                                                             2（图14）

（4）与档次数据相关的界面层(UIL)代码

load事件添加：

System.Data.DataTable dtPayAccounts = myfrmWagesSettleAccountsSearchClient.frmWagesSettleAccountsSearch_SelectPayAccounts().Tables[0];
            dgvMerray.DataSource = dtPayAccounts;

            客运综合管理系统DataSet1.Tables["dt"].Merge(dtPayAccounts);//客运综合管理系统DataSet1（新建DataSet文件的名）

private void btnForm_Click(object sender, EventArgs e)//导出数据的按钮
        {
            DataSet myDataSet = new DataSet();//声明一个DataSet
            System.Data.DataTable dtSet = 客运综合管理系统DataSet1.Tables["dt"].Copy();//系统新建的数据集等于已经绑定好的数据集复制出来的数据
            EroprIoExcel("结算信息", dtSet);//导出得到文件夹的命名
        }
        public void EroprIoExcel(string filename, System.Data.DataTable dt)
        {
            if (dt == null) return;//如果没有数据就返回
            string saveFileName = "";
            bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();//初始化保存窗体对话框
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";//得到文件的格式
            saveDialog.FileName = filename;
            saveDialog.ShowDialog();//显示出数据信息
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return;//判断是否有文档软件
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();//声明Excel
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能你的机子没有安装Excel");
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//默认创建一个worksheet
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取消worksheet第一页
            Microsoft.Office.Interop.Excel.Range range;
            long totalCount = dt.Rows.Count;
            long rowRead = 0;
            float percend = 0;
            for (int i = 0; i < dt.Columns.Count; i++)//遍历提取到的报名数据
            {
                Cursor.Current = Cursors.WaitCursor;//获取等待光标
                worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;//获取列名
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 3;//单元格颜色
                range.Font.Bold = true;
            }
            //写入数据
            for (int r = 0; r < dt.Rows.Count; r++)
            {

                try
                {
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        worksheet.Cells[r + 2, j + 1] = "'" + dt.Rows[r][j];
                    }
                    rowRead++;
                    percend = ((float)(100 * rowRead)) / totalCount;
                    System.Windows.Forms.Application.DoEvents();
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);//系统提示
                }
            }
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    fileSaved = true;
                }
                catch (Exception ex)
                {
                    fileSaved = false;
                    MessageBox.Show("导出数据出错，文件可能被打开！\n" + ex.Message);
                }
            }
            else
            {
                fileSaved = false;
            }
            xlApp.Quit();
            GC.Collect();//行除去
            if (fileSaved && File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName);
        }

                                         仅供学习，禁止用于商业用途