//下载所有课程本月的记录
internal static byte[] ExportToExcelMon(DateTime dt)
{
using (var context = new TrainingSign_Entities())
{
var conn = context.Database.Connection as SqlConnection;
var sFmt = @"
select p.workid, p.name, p.department, p.organizer,
c.course_no, c.course_context,
t.venue, dbo.Get_training_lectors_en(t.id) as Lectors,
dbo.Get_Training_Time_str(t.plan_start_time,t.plan_end_time) as Training_Time, Convert(varchar(30),p.signinTime,120) as Signin_Time
from tbl_trainee p, tbl_training t, tbl_course c
where p.ref_training_id=t.id
and t.ref_course_id=c.id
and substring(CONVERT(varchar(30), p.signinTime, 120), 0, 8)='{0}'
order by c.course_no, p.signinTime
";
var sSql = string.Format(sFmt, dt.ToString("yyyy-MM"));
var dr = SqlServerHelper.ExecuteQueryReader(conn, sSql);
if (!dr.HasRows)
{
return(null);
}
return(EPPExcelHelper.BuilderExcel(dr));
}
}
private void btnExport_Click(object sender, EventArgs e)
{
//if(null== mLastQuery)
//{
// ClearGrid();
// DoQuery(textSql.Text.Trim());
//}
var sfn = GetSaveFileName();
if (string.IsNullOrEmpty(sfn))
{
return;
}
if (null != mLastQuery)
{
var bys = EPPExcelHelper.BuilderExcel(mLastQuery);
using (var fs = new FileStream(sfn, FileMode.Create))
{
fs.Write(bys, 0, bys.Length);
}
}
else
{
this.Cursor = Cursors.WaitCursor;
BuildExpByDataReader(sfn, textSql.Text.Trim());
this.Cursor = Cursors.Default;
}
}
private bool BuildExpByDataReader(string sfn, string sql)
{
var bRet = false;
switch (mConnectType.Type)
{
case DbType.SQLServer:
{
using (var dr = SqlServerHelper.ExecuteReader(mConnectType.ConnStr, sql, null))
{
bRet = EPPExcelHelper.BuilderExcel(sfn, dr);
}
}
break;
case DbType.Oracle:
{
using (var conn = new OracleConnection(mConnectType.ConnStr))
{
using (var dr = OraClientHelper.ExecuteReader(conn, CommandType.Text, sql, null))
{
bRet = EPPExcelHelper.BuilderExcel(sfn, dr);
}
}
}
break;
case DbType.MySql:
{
using (var conn = new MySqlConnection(mConnectType.ConnStr))
{
using (var dr = MySqlClientHelper.ExecuteReader(conn, CommandType.Text, sql, null))
{
bRet = EPPExcelHelper.BuilderExcel(sfn, dr);
}
}
}
break;
default: break;
}
return(bRet);
}
internal static byte[] GetTrainingLog(string sTrainingID)
{
using (var context = new TrainingSign_Entities())
{
var conn = context.Database.Connection as SqlConnection;
var sFmt = @"
select ROW_NUMBER() OVER(ORDER BY p.signinTime ASC) AS '#',
p.workid, p.name, p.department, p.organizer,
c.course_no, c.course_context,
t.venue, dbo.Get_training_lectors_en(t.id) as Lectors,
dbo.Get_Training_Time_str(t.plan_start_time,t.plan_end_time) as Training_Time, Convert(varchar(30),p.signinTime,120) as Signin_Time
from tbl_trainee p, tbl_training t, tbl_course c
where p.ref_training_id=t.id
and t.ref_course_id=c.id
and t.id='{0}';
";
var sSql = string.Format(sFmt, sTrainingID);
var dt = SqlServerHelper.ExecuteQuery(conn, sSql);
return(EPPExcelHelper.BuilderExcel(dt));
}
}
private void ImportSQLServer(string sTarTable, string sXlsFile)
{
try
{
var sErr = string.Empty;
var nItems = EPPExcelHelper.ReadExcel(new FileInfo(sXlsFile), sTarTable, ImpSQLServerHandler, out sErr);
if (!string.IsNullOrEmpty(sErr))
{
MessageBox.Show(sErr);
}
else
{
var str = string.Format("导入完成， {0}条记录", nItems);
MessageBox.Show(str);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Import error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
public static byte[] GetExcelBytes(DataTable dt)
{
var bys = EPPExcelHelper.BuilderExcel(dt);
return(bys);
}
