将C/S架构的导出改成B/S架构的导出
--------------------编程问答--------------------
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Diagnostics;
namespace StaffManagement
{
class ImpAndExpXls
{
const String DATABASE_MDB = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=StaffManagement.mdb;Jet OLEDB:Database Password=123456";
const String DATABASE_EXCEL = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0\"";
const String FILE_EXCEL_MODEL = "{1}ExpModel{0}.xls";
const String SQL_GET_COUNT = "Select Count(0) From vwEmp Where WorkID Like '{0}%' And {1} And {2};";
const String SQL_GET_DATA0 = "Select Top {0} *"+
"From vwEmp Where WorkID Like '{1}%' And {2} And {3} Order By ID Desc;";
const String SQL_GET_DATA1 = "Select A.* From " +
"(Select top {0} * From vwEmp Where WorkID Like '{2}%' And {3} And {4} Order By ID DESC) As A" +
" Left Join "+
"(Select top {1} * From vwEmp Where WorkID Like '{2}%' And {3} And {4} Order By ID DESC) As B On A.WORKID=B.WORKID Where B.WorkID Is Null;";
const String SQL_EXPORT_EXCEL = "Insert Into [EmpImport$]( 工号, 姓名, 部门, 性别, 职务, 身份证,户籍地址,入职时间,离职时间,指纹1,指纹2,身份证照片) "+
" Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}', '{7}', '{8}', '{9}','{10}','{11}');";
const String SQL_INSERT_WORKERINFO= "INSERT INTO [WorkerInfo] ([WorkID], [Name], [Dep], [Post], [CardID], [InDate])"+
" VALUES ( '{0}', '{1}', '{2}', '{3}', '{4}', {5});";
const String SQL_INSERT_INSTAFFINFO = "INSERT INTO [InStaffInfo] ([WorkID], [Name], [Dep],[Post],[Sex],[CardID],[Address],[InDate],[PrintFinger1],[PrintFinger2],[FilePath])"+
" VALUES ('{0}', '{1}', '{2}', '{3}','{4}','{5}', '{6}', {7}, '{8}', '{9}', '{10}');";
const String SQL_INSERT_OUTSTAFFINFO = "INSERT INTO [OutStaffInfo] ([WorkID], [Name], [Dep],[Post],[Sex],[CardID],[Address],[InDate],[OutDate],[PrintFinger1],[PrintFinger2],[FilePath])"+
" VALUES ('{0}', '{1}','{2}','{3}', '{4}', '{5}', '{6}', {7}, {8}, '{9}', '{10}', '{11}');";
const String SQL_UPDATE_WORKERINFO = "Update [WorkerInfo] Set [Name]='{1}',[Dep]='{2}', [Post]='{3}', [CardID]='{4}',[InDate]={5} Where [WorkID]='{0}';";
const String SQL_UPDATE_INSTAFFINFO = "Update [InStaffInfo] Set [Name]='{1}',[Dep]='{2}', [Post]='{3}',[Sex]='{4}', [CardID]='{5}',[Address]='{6}',[InDate]={7},[PrintFinger1]='{8}',[PrintFinger2]='{9}',FilePath='{10}' Where [WorkID]='{0}';";
const String SQL_UPDATE_OUTSTAFFINFO = "Update [OutStaffInfo] Set [Name]='{1}',[Dep]='{2}', [Post]='{3}',[Sex]='{4}', [CardID]='{5}',[Address]='{6}',[InDate]={7},[OutDate]={8},[PrintFinger1]='{9}',[PrintFinger2]='{10}',FilePath='{11}' Where [WorkID]='{0}';";
const String SQL_DELETE_INSTAFFINFO = "Delete From [InStaffInfo] Where [WorkID]='{0}';";
const String SQL_DELETE_OUTSTAFFINFO = "Delete From [OutStaffInfo] Where [WorkID]='{0}';";
const String SQL_IMPORT_EXCEL="Select * from [EmpImport$]";
const String ERROR_WORKID_NOTINPUT = "工号不能空!";
const String ERROR_INDATE_FORMAT = "入职时间格式错误应填写XXXX-XX-XX或XXXX-X-X!";
const String ERROR_OUTDATE_FORMAT = "离职时间格式错误应填写XXXX-XX-XX或XXXX-X-X!";
const String ERROR_INDATE_OUTDATE_NOT = "入职时间和离职时间不能同时填写！";
const String ERROR_CARDID_LENGTH = "身份证只能填写15位或18位！";
const String ERROR_ADDNEW_DATA = "添加新信息,工号,姓名和入职时间必填,离职时间不必填!";
const String ERROR_OPENFILE_TITLE = "导入批量员工";
--------------------编程问答--------------------
#region "ExpModel 导出模板"
public static bool ExpModel(){
String _xlsfile = String.Format(FILE_EXCEL_MODEL, "",Directory.GetCurrentDirectory()+"\\");
bool _isExp = false;
SaveFileDialog _savedialog;
try
{
if (!File.Exists(_xlsfile))
{
throw new Exception("ExpModel方法:ExpModel模板文件不存在!");
}
_savedialog = new SaveFileDialog();
_savedialog.Title = "导出【导入Excel模板】";
_savedialog.DefaultExt = "*.xls";
_savedialog.Filter = "xls files (*.xls)|*.xls";
_savedialog.RestoreDirectory = true;
_savedialog.FileName = String.Format(FILE_EXCEL_MODEL, DateTime.Now.ToString("yyyyMMddHHmmss"), Directory.GetCurrentDirectory() + "\\"); ;
if (DialogResult.OK == _savedialog.ShowDialog())
{
//Stream _stream = _savedialog.OpenFile();
//if (_stream != null)
//{
// _stream.Close();
//}
File.Copy(_xlsfile, _savedialog.FileName);
_isExp = true;
}
_savedialog.Dispose();
}
catch (IOException e0)
{
_isExp = false;
throw new Exception(e0.Message);
}
catch (Exception e1)
{
_isExp = false;
throw new Exception(e1.Message);
}
finally
{
}
return _isExp;
}
#endregion
#region "取出数据"--------------------编程问答--------------------
public static DataTable GetData(String pWorkID,UInt16 pIsIn,UInt16 pIsFinger,UInt32 pPageNumber,UInt32 pPageSize,out UInt32 pPageCount,out UInt32 pCount){
String _isIn = pIsIn == 1 ? "OutDate Is Null" :(pIsIn==0?"OutDate Is Not Null":"0=0");
String _isFinger = pIsFinger == 1 ? "PrintFinger1 Is Not Null" : (pIsFinger == 0 ? "PrintFinger1 Is Null" : "0=0");
String _workid = null == pWorkID ? "" : pWorkID;
OleDbConnection _connect = new OleDbConnection(DATABASE_MDB);
OleDbCommand _cmdcount = new OleDbCommand(String.Format(SQL_GET_COUNT, _workid, _isIn,_isFinger), _connect);
OleDbCommand _cmd = new OleDbCommand();
OleDbDataAdapter _adapter = new OleDbDataAdapter();
_cmd.Connection = _connect;
_adapter.SelectCommand = _cmd;
DataTable _dt = null;
pCount = 0;
pPageCount = 0;
try
{
_connect.Open();
//_cmdcount.CommandText = "SELECT Count(0) as iCount FROM vwEmp Where WorkID Like '%';";
pCount = (UInt32)(int)_cmdcount.ExecuteScalar();
pPageCount = (UInt32)Math.Ceiling(((double)pCount) / pPageSize);
if(1==pPageNumber){
_cmd.CommandText = String.Format(SQL_GET_DATA0, pPageSize, _workid, _isIn,_isFinger);
}
else if(pPageNumber>1)
{
_cmd.CommandText = String.Format(SQL_GET_DATA1, pPageNumber * pPageSize, (pPageNumber - 1) * pPageSize, _workid, _isIn, _isFinger);
}
else
{
_cmd.CommandText = String.Format(SQL_GET_DATA0, 100000, _workid, _isIn,_isFinger);
}
_dt = new DataTable("Emp");
_adapter.Fill(_dt);
}
catch (OleDbException e0)
{
if(null!=_dt){
_dt.Dispose();
_dt=null;
}
throw new Exception("GetData方法:" + e0.Message);
}
catch (Exception e1)
{
if(null!=_dt){
_dt.Dispose();
_dt=null;
}
throw new Exception("GetData方法:" + e1.Message);
}
finally
{
if (ConnectionState.Closed != _connect.State)
{
_connect.Close();
}
_connect.Dispose();
_adapter.Dispose();
_cmdcount.Dispose();
_cmd.Dispose();
}
return _dt;
}
#endregion
#region "导出数据"--------------------编程问答-------------------- 代码是写出来的，不是改出来的。
public static bool ExpData(String pWorkID, UInt16 pIsIn, UInt16 pIsFinger)
{
String _xlsfile = String.Format(FILE_EXCEL_MODEL, "", Directory.GetCurrentDirectory() + "\\");
bool _isExp = false;
SaveFileDialog _savedialog;
OleDbConnection _connect = new OleDbConnection();
OleDbCommand _cmd = new OleDbCommand();
OleDbTransaction _trans;
_cmd.Connection=_connect;
UInt32 _count = 0;
UInt32 _pagecount = 0;
DateTime _startdate = DateTime.MinValue, _enddate = DateTime.MinValue;
bool _isstart = false, _isend = false;
try
{
DataTable _dt = GetData(pWorkID, pIsIn, pIsFinger, 1, 100000, out _pagecount, out _count);
if (!File.Exists(_xlsfile))
{
throw new Exception("ExpData方法:ExpModel模板文件不存在!");
}
_savedialog = new SaveFileDialog();
_savedialog.Title = "导出员工数据到Excel";
_savedialog.DefaultExt = "*.xls";
_savedialog.Filter = "xls files (*.xls)|*.xls";
_savedialog.RestoreDirectory = true;
_savedialog.FileName = String.Format("{1}ExpData{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"), Directory.GetCurrentDirectory() + "\\");
if (DialogResult.OK != _savedialog.ShowDialog())
{
_savedialog.Dispose();
return _isExp;
}
String _filename = _savedialog.FileName;
_savedialog.Dispose();
File.Copy(_xlsfile, _filename);
if(null==_dt){
throw new Exception("ExpData方法:未取得数据.");
return _isExp;
}
_connect.ConnectionString = String.Format(DATABASE_EXCEL, _filename);
_connect.Open();
_trans = _connect.BeginTransaction();
_cmd.Transaction = _trans;
try
{
foreach (DataRow _row in _dt.Rows)
{
//_cmd.CommandText = String.Format(SQL_EXPORT_EXCEL, _row["WorkID"], _row["Name"], _row["Dep"],
// _row["Sex"] == null ? "-" : _row["Sex"].ToString(), _row["Post"], _row["CardID"],
// _row["InDate"] == null ? "-" : ((DateTime)_row["InDate"]).ToString("yyyy-MM-dd"),
// _row["OutDate"] == null ? "-" : ((DateTime)_row["OutDate"]).ToString("yyyy-MM-dd"),
// _row["PrintFinger1"] == null ? "-" : _row["PrintFinger1"].ToString(),
// _row["PrintFinger2"] == null ? "-" : _row["PrintFinger2"].ToString());
_enddate = _startdate = DateTime.MinValue;
_isstart = DateTime.TryParse(_row["InDate"].ToString(), out _startdate);
_isend = DateTime.TryParse(_row["OutDate"].ToString(), out _enddate);
_cmd.CommandText = String.Format(SQL_EXPORT_EXCEL, _row["WorkID"], _row["Name"], _row["Dep"],
_row["Sex"], _row["Post"], _row["CardID"],_row["Address"],
_isstart?_startdate.ToString("yyyy-MM-dd"):"",
_isend ? _enddate.ToString("yyyy-MM-dd") : "",
_row["PrintFinger1"],
_row["PrintFinger2"],
_row["FilePath"]
);
_cmd.ExecuteNonQuery();
}
_trans.Commit();
}
catch (OleDbException e3)
{
_trans.Rollback();
throw new Exception("ExpData方法:" + e3.Message);
}
}
catch (OleDbException e2)
{
_isExp = false;
throw new Exception("ExpData方法:" + e2.Message);
}
catch (IOException e0)
{
_isExp = false;
throw new Exception("ExpData方法:" + e0.Message);
}
catch (Exception e1)
{
_isExp = false;
throw new Exception("ExpData方法:" + e1.Message);
}
finally
{
if (ConnectionState.Closed != _connect.State)
{
_connect.Close();
}
_connect.Dispose();
}
return _isExp;
}
#endregion
不要以为代码是上帝发明的，程序员就是把它们粘贴来粘贴去。也不要以为，没有代码，就什么也做不了了。
自己写一个也不费事，何况这代码也没有什么修改的价值。 --------------------编程问答-------------------- 会改就改，不会改就闪，烦不烦人啊，唧唧歪歪的。。。 --------------------编程问答-------------------- 啥时候有写代码的水平 --------------------编程问答-------------------- 有这水平，谁高兴去改代码哦。。 --------------------编程问答-------------------- 会的人多着呢，为什么老板偏偏找你这样不会的？因为老板开不起让会的人满意的价码。所以不会的人将就顶替会的人，他们出工出不了力，他们天天在网上搜罗代码垃圾，然后用垃圾拼凑翻新出软件。这些软件再卖给买不起软件的人。 --------------------编程问答-------------------- 哈哈，胡乱说说而已。lz等高手吧。 --------------------编程问答-------------------- 你会那你改啊，有本事你就改出来啊，在这里光说，谁都会说。。
谁都是从看别人代码过来的，该代码也是学习的基础。每个公司都去用经验丰富的人，让我们新人何以立足。。 --------------------编程问答-------------------- 淡定......
补充：.NET技术 , ASP.NET
