private void PopulateSheet(string filePath, TargetTableSheet sheet)
{
Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
ExcelHelper.InitSheet(filePath, sheet);
var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
Logger.Debug("Openning connction to " + filePath);
oleConn.Open();
var sql = string.Format("EXEC spX_FXDKTB_D '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
var dao = new SqlDbHelper();
Logger.Debug("Running " + sql);
var reader = dao.ExecuteReader(sql);
int rowCount = 0;
while (reader.Read()) {
rowCount++;
sql = GetInsertSql(reader, sheet);
try {
OleDbCommand cmd = new OleDbCommand(sql, oleConn);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
Logger.Error(ex);
oleConn.Close();
throw ex;
}
}
oleConn.Close();
Logger.DebugFormat("{0} records exported.", rowCount);
ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
}
public override string GenerateReport()
{
var fileName = "GF0102-081-境内汇总数据-月-人民币.xls";
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.F_GF0102_081_M);
var filePath = CreateReportFile(report.TemplateName, fileName);
var sql = string.Format("EXEC spGF0102_081 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
var dao = new SqlDbHelper();
Logger.Debug("Running " + sql);
var result = "";
var reader = dao.ExecuteReader(sql);
if (reader.Read())
{
result = ExcelHelper.PopulateGF0102_081(filePath, report.Sheets[0], this.AsOfDate, (decimal)reader[0], (decimal)reader[1], (decimal)reader[2], (decimal)reader[3], (decimal)reader[4]);
}
else
{
result = "Procedure returned zero rows";
}
return(result);
}
public override string GenerateReport()
{
var fileName = string.Format("五级分类预测{0}.xls", this.AsOfDate.ToString("M月d日"));
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.X_WJFLPRD_D);
var filePath = CreateReportFile(report.TemplateName, fileName);
var sheet = report.Sheets[0];
Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
ExcelHelper.InitSheet(filePath, sheet);
var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
Logger.Debug("Openning connction to " + filePath);
oleConn.Open();
var sql = string.Format("EXEC spX_WJFLPRD_D '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
var dao = new SqlDbHelper();
Logger.Debug("Running " + sql);
var reader = dao.ExecuteReader(sql);
int rowCount = 0;
while (reader.Read())
{
rowCount++;
sql = GetInsertSql(reader, sheet);
try {
OleDbCommand cmd = new OleDbCommand(sql, oleConn);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
Logger.Error(ex);
oleConn.Close();
throw ex;
}
}
oleConn.Close();
Logger.DebugFormat("{0} records exported.", rowCount);
ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
return(string.Empty);
}
private void PopulateSheet(string filePath, TargetTableSheet sheet)
{
Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
var sql = string.Format("EXEC spC_DQDKQK_M '{0}', '{1}', '{2}'", this.AsOfDate.ToString("yyyyMMdd")
, sheet.Index == 1 ? "ImportPublic" : "ImportPrivate"
, sheet.Index == 1 ? PublicColumns : PrivateColumns);
var dao = new SqlDbHelper();
Logger.Debug("Running: " + sql);
var reader = dao.ExecuteReader(sql);
var columnNames = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
columnNames.Add(reader.GetName(i));
}
int rowCount = 0;
ExcelHelper.InitSheet(filePath, sheet, columnNames);
Logger.Debug("Openning connction to " + filePath);
var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
oleConn.Open();
while (reader.Read())
{
rowCount++;
sql = GetInsertSql(reader, sheet, columnNames);
try {
OleDbCommand cmd = new OleDbCommand(sql, oleConn);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
Logger.Error(ex);
oleConn.Close();
throw ex;
}
}
oleConn.Close();
Logger.DebugFormat("{0} records exported.", rowCount);
ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
}
public override string GenerateReport()
{
var fileName = string.Format("五级分类预测{0}.xls", this.AsOfDate.ToString("M月d日"));
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.X_WJFLPRD_D);
var filePath = CreateReportFile(report.TemplateName, fileName);
var sheet = report.Sheets[0];
Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
ExcelHelper.InitSheet(filePath, sheet);
var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
Logger.Debug("Openning connction to " + filePath);
oleConn.Open();
var sql = string.Format("EXEC spX_WJFLPRD_D '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
var dao = new SqlDbHelper();
Logger.Debug("Running " + sql);
var reader = dao.ExecuteReader(sql);
int rowCount = 0;
while (reader.Read()) {
rowCount++;
sql = GetInsertSql(reader, sheet);
try {
OleDbCommand cmd = new OleDbCommand(sql, oleConn);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
Logger.Error(ex);
oleConn.Close();
throw ex;
}
}
oleConn.Close();
Logger.DebugFormat("{0} records exported.", rowCount);
ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
return string.Empty;
}
public override string GenerateReport()
{
var fileName = "GF0102-081-境内汇总数据-月-人民币.xls";
Logger.Debug("Generating " + fileName);
var report = TargetTable.GetById(XEnum.ReportType.F_GF0102_081_M);
var filePath = CreateReportFile(report.TemplateName, fileName);
var sql = string.Format("EXEC spGF0102_081 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
var dao = new SqlDbHelper();
Logger.Debug("Running " + sql);
var result = "";
var reader = dao.ExecuteReader(sql);
if (reader.Read()) {
result = ExcelHelper.PopulateGF0102_081(filePath, report.Sheets[0], this.AsOfDate, (decimal)reader[0], (decimal)reader[1], (decimal)reader[2], (decimal)reader[3], (decimal)reader[4]);
}
else {
result = "Procedure returned zero rows";
}
return result;
}
private void PopulateSheet(string filePath, TargetTableSheet sheet)
{
Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
var sql = string.Format("EXEC spC_DQDKQK_M '{0}', '{1}', '{2}'", this.AsOfDate.ToString("yyyyMMdd")
, sheet.Index == 1 ? "ImportPublic" : "ImportPrivate"
, sheet.Index == 1 ? PublicColumns : PrivateColumns);
var dao = new SqlDbHelper();
Logger.Debug("Running: " + sql);
var reader = dao.ExecuteReader(sql);
var columnNames = new List<string>();
for (int i = 0; i < reader.FieldCount; i++) {
columnNames.Add(reader.GetName(i));
}
int rowCount = 0;
ExcelHelper.InitSheet(filePath, sheet, columnNames);
Logger.Debug("Openning connction to " + filePath);
var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
oleConn.Open();
while (reader.Read()) {
rowCount++;
sql = GetInsertSql(reader, sheet, columnNames);
try {
OleDbCommand cmd = new OleDbCommand(sql, oleConn);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
Logger.Error(ex);
oleConn.Close();
throw ex;
}
}
oleConn.Close();
Logger.DebugFormat("{0} records exported.", rowCount);
ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate);
}
private void PopulateSheet(string filePath, TargetTableSheet sheet)
{
Logger.Debug("Initializing sheet " + sheet.EvaluateName(this.AsOfDate));
var columnNames = this.Columns.Select(x => x.MappingName).ToList();
ExcelHelper.InitSheet(filePath, sheet, columnNames);
Logger.Debug("Openning connction to " + filePath);
var oleConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0");
oleConn.Open();
var sql = string.Format("EXEC {0} '{1}', '{2}', '{3}', '{4}'", this.SPName, GetSheetSuffix(sheet), this.AsOfDate.ToString("yyyyMMdd"), this.AsOfDate2.ToString("yyyyMMdd"), DbColumnNames);
var dao = new SqlDbHelper();
Logger.Debug("Running: " + sql);
var reader = dao.ExecuteReader(sql);
int rowCount = 0;
while (reader.Read())
{
rowCount++;
sql = GetInsertSql(reader, sheet, columnNames);
try {
OleDbCommand cmd = new OleDbCommand(sql, oleConn);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
Logger.ErrorFormat("Error while inserting row #{0}:\r\n{1}", rowCount, sql);
Logger.Error(ex);
oleConn.Close();
throw ex;
}
}
oleConn.Close();
Logger.DebugFormat("{0} records exported.", rowCount);
ExcelHelper.FinalizeSheet(filePath, sheet, rowCount, this.AsOfDate, this.AsOfDate2);
}
